Using OpenQuery on LinkServer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using OpenQuery on LinkServer

Author

Topic

wildref
Starting Member

3 Posts

Posted - 2010-06-01 : 15:09:22

Hi All,

I am trying to retrieve data from a link server. The issue i am running into is running the query with an input parameter. The error i get is can not convert varchar value to an int column. I have tried doing an implicit conversion to an int value and it still does not work...any help would be appreciated.

@iJobNo is an int.

set @sql = N'SELECT desc_1 FROM OPENQUERY(linkServer, ''SELECT desc_1
from boxes where job_no = ''''' + @iJobNo + ''''' and box_no = 2 '')'

exec(@sql)

The error is

Syntax error converting the nvarchar value 'SELECT desc_1 FROM OPENQUERY(linkServer, 'SELECT desc_1
from boxes where job_no = ''' to a column of data type int.

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 15:48:03

What does print @sql shows?

PBUH

wildref
Starting Member

3 Posts

Posted - 2010-06-01 : 16:00:03

I have tried to take out as much as possible trying to get to the problem. this is where i am on both samples it never gets to execute the print statement.

set @sql = 'SELECT desc_1 FROM OPENQUERY(linkServer, ''SELECT desc_1
from boxes where job_no = ''''' + @iJobNo + ''''' and box_no = 2 '')'

print @sql

exec(@sql)

If i replace the sql to just to see if i can get data via hard coded values it does return the proper data...

set @sql = 'SELECT desc_1 FROM OPENQUERY(linkServer, ''SELECT desc_1
from boxes where job_no = 2 and box_no = 2 '')'

print @sql

exec(@sql)

thanks for reading my post.

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 16:18:21

Try this


declare @iJobNo  int=''
set @sql = 'SELECT desc_1 FROM OPENQUERY(linkServer, ''SELECT desc_1
from boxes where job_no = ' + convert(varchar(10),@iJobNo) + ' and box_no = 2 '')'

PBUH

wildref
Starting Member

3 Posts

Posted - 2010-06-01 : 16:31:47

Thank you very much that worked. I really appreciate the help.

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 03:13:26

quote:
Originally posted by wildref

Thank you very much that worked. I really appreciate the help.

You are welcome.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources