OpenRowset Issue

Question

Post reply

OpenRowset Issue

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 1

Hi mister,

Thanks for the reply. i tried the way you suggested, no hope,

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near 'Employee'.

any suggestion please

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

What is it that you actually want to do? Are you just trying to insert data or do you want it to insert the data and then tell you what it inserted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

Hi Jeff,

The action what am performing is i need to do some operations on remote server(db) from my production db. The operation will have select, insert,update and delete.

for example,i will be writing a stored procedures in my production db to call the stored procedures in the remote database. my remote database procedures will have select,insert,update, delete statements.

To select the data i am already done. but calling the procedure which has insert statement fails as i posted in my previous posts. i should not be using linked server for this as my client requirement.

so i chosen openrowset concept. The reason i have select statement is i need to pass the no of rows affected to the calling procedure because based on that i will be showing success/failure message on the front end(c# .net).

Any help please

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

KGJ-Dev (7/9/2014)
Hi mister,
Thanks for the reply. i tried the way you suggested, no hope,
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Employee'.
any suggestion please

Were you able to test that the remote Stored Procedure works when called locally?

Is the code you posted the real code or just a mockup?

What is your current call to OPENROWSET looking like?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Hi Mister,

I tested the remote procedure on it and it is working perfectly. Also my actual procedure will looks likes the same in my post.i just changed the name.

in order to test, i removed the parameter passing and kept the hard coded values.

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;

really it's strange.

mickyT SSChampion Points: 10360 More actions · Answer 6

To build on mister magoo's suggestion you could try

CREATE PROCEDURE [dbo].[TestInsert](@IdEmployee int, @Name varchar(20))

AS

BEGIN

DECLARE @o TABLE (IdEmployee INT ,Name VARCHAR(20));

INSERT INTO Sandbox.dbo.Emp(IdEmployee,Name)

OUTPUT inserted.IdEmployee, inserted.Name

INTO @o VALUES(@IdEmployee,@Name)

SELECT * FROM @o

END

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 7

Hi Micky,

I tried that way an hour before and not sure the procedure called twice and the insert statement did twice inserting the records.

any suggestion...

mickyT SSChampion Points: 10360 More actions · Answer 8

KGJ-Dev (7/10/2014)
Hi Micky,
I tried that way an hour before and not sure the procedure called twice and the insert statement did twice inserting the records.
any suggestion...

Do you mean that if you run the statement twice with the same parameters it will only insert once?

I noticed that behaviour when I tried it here. It seems that subsequent runs of the statement on the target server do not get a sp_prepare done.

As Jeff asked earlier, is getting the values back important or will just an insert do. If so you do it like this

DECLARE @IdEmployee int = 10,@Name varchar(20) = 'Peter';

INSERT INTO OPENROWSET('SQLNCLI',

'Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee',

'SELECT IdEmployee,Name FROM Employee..Emp'

)

VALUES (@IdEmployee,@Name);

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 9

Hi Micky,

Appreciate your time on this and you sample works fine. but i need to call the procedure instead of this logic because before inserting i will have some logic. so calling SPC will be the best idea.

but by seeing these many constraints, i will propose my client to change this methodology to lined server. is possible to block other developers to use this linked server? because not of the developers should have execute access to the linked server. is it possible to do? any samples please how to achieve this impersonation.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

KGJ-Dev (7/10/2014)
Hi Micky,
Appreciate your time on this and you sample works fine. but i need to call the procedure instead of this logic because before inserting i will have some logic. so calling SPC will be the best idea.
but by seeing these many constraints, i will propose my client to change this methodology to lined server. is possible to block other developers to use this linked server? because not of the developers should have execute access to the linked server. is it possible to do? any samples please how to achieve this impersonation.

I have just tried using OPENROWSET to call a remote stored procedure and it worked just fine - inserted the row and returned the results.

Here is what I created on the remote server:

Created SQL login called test, default database = ssc with public role on ssc.

create table Emp(IdEmployee int,Name sysname);

go

Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))

as

Insert into Emp(IdEmployee,Name)

output inserted.IdEmployee, inserted.Name

values(@IdEmployee,@Name);

go

grant exec on TestInsert to test;

And on the local server I ran this:

SELECT *

FROM OPENROWSET('SQLNCLI','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',

'set nocount on set fmtonly off exec ssc.dbo.TestInsert 11,''Peter'''

) AS a;

It worked.

I also ran these variants that all worked.

SELECT *

FROM OPENROWSET('SQLNCLI','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',

'exec ssc.dbo.TestInsert 11,''Peter'''

) AS a;

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',

'exec ssc.dbo.TestInsert 11,''Peter'''

) AS a;

SELECT *

FROM OPENROWSET('MSDASQL','Driver={SQL Server};Server={FOO\BAR};Trusted_Connection={Yes};Database={ssc};',

'exec ssc.dbo.TestInsert 11,''Peter'''

) AS a;

Are you sure your sql login has permission to execute the stored proc?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 11

Hi mister,

thank you so much for your time on this post and am not sure whats happening on my side as you proved it is working perfectly for you. but this weekend i will be trying exact your sample and post you back with the output. please stay tuned.

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 12

Hi Mister,

Finally i got green flag to go with Linked Server from my client. But for my self learning i will need to try your sample. Thank again for your wonderful time on this post.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

KGJ-Dev (7/14/2014)
Hi Mister,
Finally i got green flag to go with Linked Server from my client. But for my self learning i will need to try your sample. Thank again for your wonderful time on this post.

Just be careful... having hardcoded UIDs and Passwords is something to be totally avoided especially when it comes to any type of security audit and changing passwords according to policy. Even very low prived logins are seriously frowned upon. I'd stick with Magoo's final example, which uses a "Trusted Connection". It takes a little coordination with the Windows folks but it's worth every second spent.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)