Stored Procedure / SQL variable inside OPENQUERY string

Question

I'm having trouble using OPENQUERY (to fetch data from a linked server) within a stored procedure. I've been reading up on SQL variables but can't seem to get it working so maybe I'm misunderstanding variables/strings in SQL. It's throwing the error "Incorrect syntax near '+'." and I'm not sure what I'm doing wrong.

The code:

ALTER PROCEDURE [dbo].[sp_getPerson]
@myName nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON;

SELECT     *
    FROM         OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM myTable
WHERE (myName= ''' + @myName + ''')
') AS derivedtbl_1

END

Should this work, in theory?

jabs · Accepted Answer · 2012-06-01 21:29:47Z

1

I'd put the entire query into a variable and execute the variable.

declare @myName nvarchar(MAX)
declare @sqltext varchar(max)


select  @myName = 'Some Name'

select @sqltext='
select * from openquery(MY_LINKED_SERVER,''select * from database.schema.table
where myName = '''+@myName +'''
)'


exec (@sqltext)

If you use the LINKED_SERVER.DATABASE.SCHEMA.TABLE, you could have performance issues depending on table size, etc. because no indexes are used with this query.

answered Jun 1, 2012 at 21:29

jabs

1,8884 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RoMEoMusTDiE · Accepted Answer · 2012-05-10 10:15:30Z

0

all you have to do is add a linked server thru a script

http://msdn.microsoft.com/en-us/library/ms189811.aspx sp_addlinkedsrvlogin

if your linked server is TEST-SQL and database is test01 and table name is testtb

and....

query it from another server is something like this.

select * from [TEST-SQL].test01.dbo.testtb linktedTB where ....

and you can also join tables to linked server.

answered May 10, 2012 at 10:15

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

2 Comments

valoukh Over a year ago

Great! I'm just a bit unsure of the syntax - would the linked table need the full prefix as well? E.g. SELECT * FROM [LINKED_SERVER].MYDATABASE.dbo.MYTABLE1 LEFT OUTER JOIN [LINKED_SERVER].MYDATABASE.dbo.MYTABLE2 ON...

RoMEoMusTDiE Over a year ago

exec sp_addlinkedserver @server='test-sql01'; exec sp_addlinkedsrvlogin @rmtsrvname='test-sql01',@useself=false, @rmtuser='sa', @rmtpassword='sapassword'; yup it needs the whole prefix.. but you can alias the linkedserver-table as specified up top.

Collectives™ on Stack Overflow

Stored Procedure / SQL variable inside OPENQUERY string

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related