July 8, 2014 at 6:09 pm
Hi,
I am trying to access remote db server's stored procedure and am using openrowset for that. i am aware of that i can use linked server. but due to some reason my customer doesn't want to use that. Here is my sample code
server1
db: Test
declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
''
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert ' + @IdEmployee + ',' + @Name+ '
''
) AS a;')
server2 (170.30.149.34)
db: Employee
Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
as
BEGIN
Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name)
END
When i execute the query it throws an exception
The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object
any suggestion or correction please
When i execute the
July 8, 2014 at 11:07 pm
Few things to improve, firstly you must cast the @IdEmployee to a varchar
cast(@IdEmployee as varchar(12))
Next is the @Name, has to enclosed with apostrophe/single quote
char(39) + char(39) + @Name + char(39) + char(39)
Lastly, FMTONLY is a depreciated feature and according to MSDN, "Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)."
or in other words
declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
''
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert ' + cast(@IdEmployee as varchar(12)) + ','
+ char(39) + char(39) + @Name + char(39) + char(39) + '
''
) AS a;')
😎
July 9, 2014 at 8:04 am
Hi Eric,
Thakns for your reply and when i execute am getting sysntax error
declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
''
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert ' + cast(@IdEmployee as varchar(12)) + ','
+ char(39) + char(39) + @Name + char(39) + char(39) + '
''
) AS a;')
Error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'Test'.
also am getting syntax error in cast, char function
i squeezed my mind and am unable to find. any suggestion please
July 9, 2014 at 8:37 am
KGJ-Dev (7/9/2014)
Hi Eric,Thakns for your reply and when i execute am getting sysntax error
declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';EXEC('SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
''
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert ' + cast(@IdEmployee as varchar(12)) + ','
+ char(39) + char(39) + @Name + char(39) + char(39) + '
''
) AS a;')
Error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'Test'.
also am getting syntax error in cast, char function
i squeezed my mind and am unable to find. any suggestion please
Try running this code, copy the results and paste into a new query, what do you get?
declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
select 'SELECT *
FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
''
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert ' + cast(@IdEmployee as varchar(12)) + ','
+ char(39) + char(39) + @Name + char(39) + char(39) + '
''
) AS a;'
😎
July 9, 2014 at 12:32 pm
Hi Eric,
Soryy for the late reply i was into my customer meeting. Here is what i am getting from the last query,
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee', ' SET NOCOUNT ON; set fmtonly off exec Employee.[dbo].TestInsert 11,''Peter'' ' ) AS a;
Anything wrong with this code?
July 9, 2014 at 12:41 pm
When i execute the above query here is the error am getting
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert 11,'Peter'
". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
but insert is working fine. why it throws this error. how to avoid this error .
any sugestion please
July 9, 2014 at 12:56 pm
KGJ-Dev (7/9/2014)
When i execute the above query here is the error am gettingMsg 7357, Level 16, State 2, Line 1
Cannot process the object "
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert 11,'Peter'
". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
but insert is working fine. why it throws this error. how to avoid this error .
any sugestion please
Does the procedure dbo.TestInsert return any value? Is it on the same server?
Lastly, remove the "set fmtonly off", you don't need that.
😎
July 9, 2014 at 1:06 pm
Hi Eric
The procedure what i am calling is on different server and has only insert statement. am not returning. but i would like if it returns affected rows. Also if the remove
set fmtonly off then it doesn't inserting the record. if i have the set fmtonly off then it's inserting records with the below mentioned error.
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
SET NOCOUNT ON;
set fmtonly off exec Employee.[dbo].TestInsert 11,'Peter'
". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
any suggestion please
July 9, 2014 at 1:17 pm
Quick question, can you do a normal select with the same openrowset statement, something like select * from sysobject?
😎
July 9, 2014 at 1:20 pm
yes i could do. the normal select statement with openrowset is working fine for me
July 9, 2014 at 1:38 pm
What are the data types of the stored procedure's parameters? Is it possible that the @IdEmployee is a character type?
Better yet, can you post the procedure code (remove any sensitive things of course)
😎
July 9, 2014 at 3:09 pm
Hi Eric,
it is on my first post. Here is again,
Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
as
BEGIN
Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name);
END
Any help please
July 9, 2014 at 4:25 pm
I guess the point trying to be made is why would you do a SELECT * here? The remote store procedure returns no rows. It only does INSERTs.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2014 at 4:34 pm
Hi Jeff,
thanks for your time on this.
What could be the remedy for this issue, any help please
July 9, 2014 at 5:30 pm
Try this:
Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
as
BEGIN
Insert into Emp(IdEmployee,Name)
output inserted.IdEmployee, inserted.Name
values(@IdEmployee,@Name);
END
to output the inserted row.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply