SQL SP_EXECSQL @VAR to run a "dynamic" OpenQuery
I have an OpenQuery (Used in SQL2005 to run a query against a legacy database). I'm building the string to run so that I'll return the last 6 months of data.
The problem I'm having is with '||' to concat.
This works on the legacy system:
SELECT
EVENT_DATE || ' ' || EVENT_TIME as EVENTDateTime
FROM
EVENT
This Works in SQL2005 via Linked SQL Server:
Declare @Query nvarchar(MAX)
Set @Query = N'
SELECT
*
FROM
OPENQUERY(PATCOMLIVE,
''SELECT
*
FROM
root.ESDB_EVENT as EV
''
) as OpenQ';
exec sp_executesql @Query
This does NOT work:
Declare @Query nvarchar开发者_如何学运维(MAX)
Set @Query = N'
SELECT
*
FROM
OPENQUERY(PATCOMLIVE,
''SELECT
EVENT_DATE || '' '' || EVENT_TIME
FROM
root.ESDB_EVENT as EV
''
) as OpenQ';
exec sp_executesql @Query
Nor does this:
EVENT_DATE '|'| '' '' '|'| EVENT_TIME
I know I'm missing something simple... but the || for concating strings together is messing things up although it does work on the legacy system by itself.
You are right, it doesn't work cause its not sending a complete string to the OPENQUERY
, you can try two things. First, concatenating the right '
, it should be like this:
Set @Query = N'
SELECT
*
FROM
OPENQUERY(PATCOMLIVE,
''SELECT
EVENT_DATE || '''' '''' || EVENT_TIME
FROM
root.ESDB_EVENT as EV
''
) as OpenQ';
Or you can try concatenating those columns in SQL, like this:
Set @Query = N'
SELECT
*, EVENT_DATE + '' '' + EVENT_TIME
FROM
OPENQUERY(PATCOMLIVE,
''SELECT
*
FROM
root.ESDB_EVENT as EV
''
) as OpenQ';
精彩评论