January 17, 2018 at 2:45 pm
I'm a OPEN QUERY Newbie.
I have my link server set up and simple queries work for example:
select * FROM OPENQUERY (LinkserverName, 'select * from Policytable')
That works fine.
But select * FROM OPENQUERY (LinkserverName, 'select ' ' CUSTID ' ', ' ' Status' ' from Policytable WHERE ' 'CUSTID' ' = ' ' GUID ' ' ')
is returning Syntax errors.
I've been researching the issue and resolves some of the errors but not all.
I'm trying to do this to write more complex queries for my DTS Package.
January 17, 2018 at 3:13 pm
Would help if you provided the actual error messages.
January 17, 2018 at 3:24 pm
osgcurt - Wednesday, January 17, 2018 2:45 PMI'm a OPEN QUERY Newbie.
I have my link server set up and simple queries work for example:
select * FROM OPENQUERY (LinkserverName, 'select * from Policytable')That works fine.
But select * FROM OPENQUERY (LinkserverName, 'select ' ' CUSTID ' ', ' ' Status' ' from Policytable WHERE ' 'CUSTID' ' = ' ' GUID ' ' ')
is returning Syntax errors.
I've been researching the issue and resolves some of the errors but not all.I'm trying to do this to write more complex queries for my DTS Package.
The error message would help as well as what the LinkedServer is pointing to - SQL Server, Oracle, etc as the syntax needs to be one that works with that linked server type.
But try this and see if it works: 'select CUSTID, Status from Policytable WHERE CUSTID = ''GUID'' '
Sue
January 18, 2018 at 8:12 am
OPENQUERY accepts only a literal string query (review the syntax for OPENQUERY() to confirm this).
You have to use dynamic SQL if you want to build a dynamic OPENQUERY string.
DECLARE @sql varchar(8000)
SET @sql = 'select * FROM OPENQUERY (LinkserverName, ''' + <construct_query_here> + ''')'
EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply