My requirements have changed.
What I need to do is have a .NET Script execute a Stored Procedure against an Oracle Table.
It looks for a record with the current Date that indicates an Oracle Job has completed.
It executes every 15 minutes until it finds the record.
I'm leaning in favor of an OpenQuery embedded in a Stored Procedure.
In the stored Procedure I get the current Date and store it in a variable
DECLARE @CurrentDate Date
SET @CurrentDate = CONVERT(DATE, GETDATE());
SELECT @CurrentDate AS CurrentDate
Then I need to convert the date a string that will work with an Oracle Date format. I'm not sure what the syntax would be?
The following works:
FROM OPENQUERY (LS_RDB_DWH, 'SELECT * FROM RDB_DWH.ENTITY_DIMENSION')
My intent is to create dynamic SQL and pass the Oracle Date in the where clause.
I tried querying the table based on the Entry Date using the following:
FROM OPENQUERY (LS_RDB_DWH, 'SELECT * FROM RDB_DWH.ENTITY_DIMENSION WHERE PD_RDB_ENTRY_DATE = '2012-10-31 03:10:37.0053840'')
But I get the following error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2012'.
Any ideas would be greatly appreciated. :-)
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/