question

markafisher92 avatar image
markafisher92 asked

Passing parameter across Linked Servers

Hello All, I am having trouble passing a param using OpenQuery. I have all of the proper permissions. I am getting an error when trying to pass an int through an Open query.Here is just one error message out of the three methods that I tried: Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'CAST(@Wonum as int)' to data type int. Here is a code sample of my situation: /* Create test tables */ CREATE Table #tbl1(TID Int Identity(1,1) NOT NULL, wo_num INT,) CREATE Table #tbl2(TID Int Identity(1,1) NOT NULL, wo_ID INT, notes Text) /* Insert test data */ insert into #tbl1 (wo_num) values(12345) insert into #tbl2 (wo_ID, notes) values(12345, 'Some text here to pull from OpenQuery') /* Declare and set param*/ DECLARE @Wonum INT SET @Wonum = 12345 /* Query my linked server to return the notes*/ /* Method 1 - failed */ SELECT wo_num,notes FROM OPENQUERY(LINKED_SERVER,' SELECT T.wo_num ,TN.NoteText FROM [myDataBase].[dbo].[tbl1] T JOIN [myDataBase].[dbo].[tbl2] TN ON T.wo_num = TN.wo_ID WHERE T.wo_num = ''CAST(@Wonum as int)'' ') /* Method 2 - failed */ SELECT wo_num,notes FROM OPENQUERY(LINKED_SERVER,' SELECT T.wo_num ,TN.NoteText FROM [myDataBase].[dbo].[tbl1] T JOIN [myDataBase].[dbo].[tbl2] TN ON T.wo_num = TN.wo_ID WHERE T.wo_num = ''+@Wonum+'' ') /* Method 3 - failed */ SELECT wo_num,notes FROM OPENQUERY(LINKED_SERVER,' SELECT T.wo_num ,TN.NoteText FROM [myDataBase].[dbo].[tbl1] T JOIN [myDataBase].[dbo].[tbl2] TN ON T.wo_num = TN.wo_ID WHERE T.wo_num = ''12345'' ') -- What is the correct way to pass this int across the linked server using open query? Any help would be great. Thank you very much.
openqueryintcast
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Apart from the mistake of selecting a column called "notes" when it should be "NoteText", can I ask why it has to be OpenQuery? If you use four part naming, you don't need all that bother. If you can use Execute ... AT, then you can use a parameter in the query... EXECUTE('select some stuff from atable where id=?',@myparam) AT remoteServer;
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Because CAST(@Wonum as int) references a local variable, it has to be added to the string using the value of @Wonum, not the literal string. Try changing this line: WHERE T.wo_num = ''CAST(@Wonum as int)'' to this: WHERE T.wo_num = ' + CAST(@Wonum as varchar(10)) + ' I changed the CAST to varchar since you are building a string. When it's parsed on the remote server, it will be seen as the integer value. Method 3 looks like it should work. Can you post the error message for that one?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.