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.
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;
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?
16 People are following this question.
