2

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?

j0k
  • 22,600
  • 28
  • 79
  • 90
valoukh
  • 541
  • 1
  • 8
  • 19

2 Answers2

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.

jabs
  • 1,694
  • 4
  • 19
  • 36
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.

RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26
  • 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... – valoukh May 10 '12 at 10:30
  • 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. – RoMEoMusTDiE May 10 '12 at 20:49