September 2, 2014 at 4:10 pm
Thanks Lynn for all of your help.
Have a good evening. 🙂
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/
September 2, 2014 at 5:22 pm
Lynn,
Do you have an AS400 or a machine that has DB2 installed?
The syntax is identical to Oracle.
Thanks.
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/
September 2, 2014 at 5:57 pm
Nope.
September 3, 2014 at 11:08 am
The final solution should have the Current Date in the Where clause.
My query works until I try and add the WHERE Clause.
I finally got the real Table from the off shore Team that I will be querying against.
DECLARE @OracleTableName NVARCHAR (200)
DECLARE @CurrentDate Date
SET @CurrentDate= CONVERT(DATE, GETDATE());
SET @OracleSchemaName = 'RDB_DWH_POLICY'
SET @OracleTableName = 'STG_BATCH_STATUS'
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/
September 3, 2014 at 12:06 pm
Welsh Corgi (9/3/2014)
The final solution should have the Current Date in the Where clause.My query works until I try and add the WHERE Clause.
I finally got the real Table from the off shore Team that I will be querying against.
DECLARE @OracleTableName NVARCHAR (200)
DECLARE @CurrentDate Date
SET @CurrentDate= CONVERT(DATE, GETDATE());
SET @OracleSchemaName = 'RDB_DWH_POLICY'
SET @OracleTableName = 'STG_BATCH_STATUS'
Please post all your code one more time, both what works and what doesn't. Maybe we may see something that you are missing. Never hurts have a few extra eyes look at something.
September 3, 2014 at 3:04 pm
I completed the code two hours ago minutes before a meeting that I had.
Edit:
The off shore people did not create the table that I needed until today so I was using another table so I could get the syntax right.
This is the solution.
USE ETLLoggingR2
GO
CREATE PROCEDURE usp_Oracle_Batch_Status
AS
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + CAST(@CurrentDate as nvarchar(10)) + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
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/
September 3, 2014 at 3:39 pm
Welsh Corgi (9/3/2014)
I completed the code two hours ago minutes before a meeting that I had.Edit:
The off shore people did not create the table that I needed until today so I was using another table so I could get the syntax right.
This is the solution.
USE ETLLoggingR2
GO
CREATE PROCEDURE usp_Oracle_Batch_Status
AS
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + CAST(@CurrentDate as nvarchar(10)) + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
Note the one change I made where you are CAST to make @CurrentDate a string:
USE ETLLoggingR2
GO
CREATE PROCEDURE usp_Oracle_Batch_Status
AS
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @CurrentDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
September 3, 2014 at 3:46 pm
Lynn Pettis (9/3/2014)
Welsh Corgi (9/3/2014)
I completed the code two hours ago minutes before a meeting that I had.Edit:
The off shore people did not create the table that I needed until today so I was using another table so I could get the syntax right.
This is the solution.
USE ETLLoggingR2
GO
CREATE PROCEDURE usp_Oracle_Batch_Status
AS
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + CAST(@CurrentDate as nvarchar(10)) + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
Note the one change I made where you are CAST to make @CurrentDate a string:
USE ETLLoggingR2
GO
CREATE PROCEDURE usp_Oracle_Batch_Status
AS
-- Declare Variables
DECLARE @CurrentDate AS datetime2
DECLARE @StrSQL AS NVARCHAR(2000)
SET @CurrentDate = getdate()
-- Print out Date
PRINT @CurrentDate
SET @StrSQL = N'SELECT *
FROM OPENQUERY(LS_RDB_DWH_POLICY52,''SELECT count(*) FROM RDB_DWH_POLICY.STG_BATCH_STATUS
WHERE SBS_Batch_Date >= TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @CurrentDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') AND SBS_JOB_NAME = ''''RDB Policy''''''' + ')'
PRINT @StrSQL
EXECUTE sp_executesql @StrSQL;
Nice thank you Lynn. 🙂
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/
Viewing 8 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply