Below is my test simple query and I need to change the date on the where clause to as a date parameter. Anyone know how to do it?
I am using CTE to query of Oracle data using OPENQUERY. The date range are currently hard coded but I want to be able to do it as a parameter.
;with Localdata1 as
(
SELECT BusDriverID
,BusDriverName
,routeID
FROM OPENQUERY (ServerName1, 'SELECT BusDriverID
,BusDriverName
FROM SchemaName1.Transportation_tbl
WHERE trunc(dr_time) between to_date(''01/01/2014'', ''mm/dd/yyyy'') and to_date(''03/31/2014'',''mm/dd/yyyy'') '
) a
LEFT JOIN OPENQUERY(ServerName2,'SELECT BusDriverID
,routeID
,RouteName
FROM SchemaName2.Roadmap_tbl
WHERE State in (''CA'')') b
on a.BusDriverID = b.BusDriverID
OPENQUERY does not accept variables for its arguments so you have to do the qhole thing as Dynamic SQL (Max Query parameter for OPENQUERY is 8K)
Because of the hassle of doubled-up-quotes I do the thing in multiple stages, and I use PRINT to preview the actual SQL until I get something that works (i.e. running it manually as a test) and only then do I use the EXECUTE command
Something like this:
DECLARE @strSQL nvarchar(4000),
@strSQL1 nvarchar(4000),
@strSQL2 nvarchar(4000),
@strStartDate nvarchar(10),
@strEndDate nvarchar(10)
--
SELECT @strStartDate = '01/01/2014',
@strEndDate = '03/31/2014'
--
SELECT @strSQL1 =
'SELECT BusDriverID, BusDriverName
FROM Transportation_tbl
WHERE trunc(dr_time) between to_date(''' + @strStartDate + ''', ''mm/dd/yyyy'')
and to_date(''' + @strEndDate + ''',''mm/dd/yyyy'')',
@strSQL2 =
'SELECT BusDriverID, routeID, RouteName
FROM Roadmap_tbl
WHERE State in (''CA'')'
--
PRINT '@strSQL1=' + @strSQL1
PRINT ''
PRINT '@strSQL2=' + @strSQL2
PRINT ''
--
SELECT @strSQL =
';with Localdata1 as
(
SELECT
FROM OPENQUERY (TransServer1, ''' + REPLACE(@strSQL1, '''', '''''') + '''
) a
LEFT JOIN OPENQUERY(TransServer2,''' + REPLACE(@strSQL2, '''', '''''') + ''') b
on a.BusDriverID = b.BusDriverID
-- Finaloutput
SELECT * FROM Localdata1'
--
PRINT '@strSQL=' + @strSQL
Sorry, looks like a Cut&Paste error somewhere along the way. I think this should be the complete SQL - when I run it here it (now) generates identical SQL to your original query (apart from formatting which I fiddled with a bit)
DECLARE @strSQL nvarchar(4000),
@strSQL1 nvarchar(4000),
@strSQL2 nvarchar(4000),
@strStartDate nvarchar(10),
@strEndDate nvarchar(10)
--
SELECT @strStartDate = '01/01/2014',
@strEndDate = '03/31/2014'
--
SELECT @strSQL1 =
'SELECT BusDriverID
, BusDriverName
FROM Transportation_tbl
WHERE trunc(dr_time) between to_date(''' + @strStartDate + ''', ''mm/dd/yyyy'')
and to_date(''' + @strEndDate + ''',''mm/dd/yyyy'')',
@strSQL2 =
'SELECT BusDriverID
, routeID
, RouteName
FROM Roadmap_tbl
WHERE State in (''CA'')'
--
PRINT '@strSQL1=' + @strSQL1
PRINT ''
PRINT '@strSQL2=' + @strSQL2
PRINT ''
--
SELECT @strSQL =
';with Localdata1 as
(
SELECT BusDriverID
, BusDriverName
, routeID
FROM OPENQUERY (TransServer1, ''' + REPLACE(@strSQL1, '''', '''''') + '''
) a
LEFT JOIN OPENQUERY(TransServer2,''' + REPLACE(@strSQL2, '''', '''''') + ''') b
on a.BusDriverID = b.BusDriverID
-- Finaloutput
SELECT *
FROM Localdata1'
--
PRINT '@strSQL=' + @strSQL
I am still getting an error on next the SELECT in both SELECT statement inside @strSQL1 and @strSQL2. Is that because the Schema issue? I forgot to add the Schema name on the FROM clause. So, here are two additional line have been updated:
FROM SchemaName1.Transportation_tbl
and
FROM SchemaName2.Roadmap_tbl
so, the query still wont compile and kept saying: Incorrect syntax near the keyword 'SELECT'.
I got it working. My query has the extra ",". It is working now. Thank you so much! You are so genius! How do I understand the Openquery concept? Any links can I read? Do you know?
I don't think there is any magic to OPENQUERY - the trick is to generate the right SQL to send to the Linked Server. IME that means outputting the generated SQL (e.g. using PRINT) and running it to check it is correct, if not adjust the manual SQL until you get the results that you want and once that works then make those changes back in the code that generates the SQL.
That's the reason that I use this two-step approach, using REPLACE to double-up the quotes, as it otherwise becomes a nightmare of doubled-up doubled-up quotes!
JOINs between multiple OPENQUERYs to remote tables (as distinct from JOINs between OPENQUERY and local tables) can have dreadful performance, so it is sometimes better to pull the results of the main OPENQUERY into a local, #TEMP, table and then construct a query for the 2nd OPENQUERY which ONLY references records actually referenced in the first resultset - but if your performance is OK you probably don't need to worry about that. Do watch out for how long it is taking / how many rows each individual OPENQUERY is bringing back (which SQL may then be "throwing away" when making the JOIN) because you could inadvertently bring the remote server to its knees!
QUOTENAME takes SYSNAME datatype as a parameter, so is restricted to 128 characters. It also returns the output as Nvarchar which may, or may not!, be desirable - e.g. then causing an implicit conversion of the rest of the expression to Nvarchar.(I'm a bit iffy about OPEN_QUERY but I think it will take 8,000 characters if the parameter is Varchar, but only 4,000 if it is Nvarchar). Big query to get to even 4,000 chars of course ...
If you don't like the multiple single-quotes and are going to use char(39) anyway then perhaps
(Max Query parameter for OPENQUERY is 8K)