September 2, 2014 at 1:08 pm
Lynn Pettis (9/2/2014)
Welsh Corgi (9/2/2014)
Ed Wagner (9/2/2014)
Good. Now all you have to do is look at the format mask of your TO_DATE function.TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
Thanks.
I need TO_DATE(@CurrentDate,''YYYY.MM.DD'') to use a variable as opposed to a hard coded value.
I'm having trouble with the syntax.
So this (what I had posted further up above doesn't help? If not why not?
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH_POLICY52, '
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''2014.01.05'',''YYYY.MM.DD'') ORDER BY 1'
)
OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY52" returned message "[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY52".
@CurentDate is in format 'YYYY-MM_DD'
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 1:12 pm
You guys have better code than I.
I can get it to execute without the WHERE Clause.
What would it take to add the WHERE with a Date variable to my code please?
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 1:15 pm
Welsh Corgi (9/2/2014)
Lynn Pettis (9/2/2014)
Welsh Corgi (9/2/2014)
Ed Wagner (9/2/2014)
Good. Now all you have to do is look at the format mask of your TO_DATE function.TO_DATE(''2014-09-02'',''YYYY.MM.DD'')
The date string doesn't match the format string.
Thanks.
I need TO_DATE(@CurrentDate,''YYYY.MM.DD'') to use a variable as opposed to a hard coded value.
I'm having trouble with the syntax.
So this (what I had posted further up above doesn't help? If not why not?
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH, ''
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
--exec sp_executesql @SQLCmd;
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH_POLICY52, '
SELECT
*
FROM
RDB_DWH_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''2014.01.05'',''YYYY.MM.DD'') ORDER BY 1'
)
OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY52" returned message "[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY52".
@CurentDate is in format 'YYYY-MM_DD'
Based on the error message:
OLE DB provider "MSDASQL" for linked server "LS_RDB_DWH_POLICY52" returned message "[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
".
The table, RDB_DWH_ASSOCIATE_ENTITY, does not exist.
September 2, 2014 at 1:32 pm
Look at the username you're using to make the connection to Oracle. That username must have permission to select from the table/view you're trying to select from. If you have the table/view name correctly in your query, have the Oracle DBA grant select permissions on it to the username you're using to connect, which you should already know.
September 2, 2014 at 1:32 pm
I don't have access to a an Oracle system so there is no way for me to try and sandbox this thing. Someone with access to a SQL Server system and an Oracle system is going to have to help you figure this one out.
Or, you are just going to have get you learn on and figure it out.
It has been 4 years since I touched Oracle and then it was exclusively Oracle, no SQL Server involved.
September 2, 2014 at 1:34 pm
Thanks for all of the help.
It works Lynn.
Ed you code worked as well.
Thank you very much.
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 1:37 pm
Welsh Corgi (9/2/2014)
Thanks for all of the help.It works Lynn.
Ed you code worked as well.
Thank you very much.
I'm glad we could help and that it's working for you.
September 2, 2014 at 1:46 pm
The following is the code and it works.
I need a variation of this query that return the record count.it to just return the record COUNT as in the second query.
--SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
--FROM OPENQUERY(LS_RDB_DWH_POLICY52, 'SELECT * FROM RDB_DWH_POLICY.COVERAGE_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE > TO_DATE(''2014-09-02'',''YYYY.MM.DD'') ORDER BY 1')
declare
@SQLCmd nvarchar(max),
@MyDate DATE;
set @MyDate = '20140808';
select @SQLCmd = N'
SELECT
CAST(CAE_RDB_ENTRY_DATE as Date),
*
FROM OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
*
FROM
RDB_DWH_POLICY.COVERAGE_ASSOCIATE_ENTITY
WHERE
CAE_RDB_ENTRY_DATE > = TO_DATE(''''' + REPLACE(CONVERT(nvarchar(10), @MyDate, 111),'/','.') + ''''',''''YYYY.MM.DD'''') ORDER BY 1''
)';
print @SQLCmd;
exec sp_executesql @SQLCmd;
CREATE PROCEDURE [dbo].[usp_GetRecordCountsOracle]
@SQLSchemaName VARCHAR(20) ,
@OracleSchemaName VARCHAR(60) ,
@SourceSystem VARCHAR(30),
@SQLTableName VARCHAR(200) ,
@OracleTableName VARCHAR(200)
AS
---------------------------------------------------------
-- Start Temp Vaiables - Test
--DECLARE @SQLSchemaName NVARCHAR(20)
--DECLARE @OracleSchemaName NVARCHAR(60)
--DECLARE @SQLTableName NVARCHAR(60)
--DECLARE @OracleTableName NVARCHAR (200)
--SET @SQLSchemaName = 'RDB_pol'
--SET @OracleSchemaName = 'RDB_DWH_POLICY'
--SET @SQLTableName = 'DATE_DIMENSION'
--SET @OracleTableName = 'POLICY_TRANSACTION_DIMENSION'
-- End Temp Vaiables - Test
---------------------------------------------------------------
DECLARE @sqlquery NVARCHAR(255)
DECLARE @OpenQuery NVARCHAR(4000)
DECLARE @OpenQueryPrefix NVARCHAR(4000)
DECLARE @OpenQuerySuffix NVARCHAR(200)
DECLARE @OracleRowCount INT
DECLARE @LinkedServerPre VARCHAR(200)
DECLARE @LinkedServerPost VARCHAR(200)
DECLARE @MyCnt INT;
DECLARE @SQLRowCount INT
DECLARE @ParmDefinition NVARCHAR(500);
SET @sqlquery = 'SELECT @cnt = COUNT(*) FROM ' + @SQLSchemaName + '.'
+ @SQLTableName
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @sqlquery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;
SET @LinkedServerPre = 'LS_'
SET @OpenQueryPrefix = 'SELECT @cnt = OracleTableCount FROM OPENQUERY(' -- << here
SET @OpenQueryPrefix = @OpenQueryPrefix + @LinkedServerPre
+ @OracleSchemaName --+ @LinkedServerPost
SET @OpenQueryPrefix = @OpenQueryPrefix
+ ', ''SELECT COUNT(*) OracleTableCount FROM ' -- << and here
SET @OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @OracleSchemaName + '.' + @OracleTableName + @OpenQuerySuffix
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
SELECT @SQLRowCount AS SQLRowCount ,
@OracleRowCount AS OracleRowCount;
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 1:53 pm
Out of curiosity, what Oracle version/edition/OS are you on?
😎
September 2, 2014 at 2:02 pm
Eirikur Eiriksson (9/2/2014)
Out of curiosity, what Oracle version/edition/OS are you on?😎
The Server is owned by a software Vendor. I'm not sure what it is. I only have read permissions so I can't query the system tables.
All that I do is ETL and they are one of the sources of data.
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 2:26 pm
This work? You will need to the Parms piece from your code to this.
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max);
set @OracleSchemaName = N'OracleSchema';
set @OracleTableName = N'OracleTable';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
September 2, 2014 at 3:03 pm
Thanks Lynn, you are awesome.
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max),
@ParmDefinition NVARCHAR(500);
set @OracleSchemaName = N'OracleSchema';
set @OracleTableName = N'OracleTable';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
I added @ParmDefinition NVARCHAR(500);. I get an error that I must DECLARE @cnt. I try and add @cnt and I get a message that it is already defined.:crazy:
Must declare the scalar variable "@cnt".
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 3:12 pm
Welsh Corgi (9/2/2014)
Thanks Lynn, you are awesome.
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max),
@ParmDefinition NVARCHAR(500);
set @OracleSchemaName = N'OracleSchema';
set @OracleTableName = N'OracleTable';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
I added @ParmDefinition NVARCHAR(500);. I get an error that I must DECLARE @cnt. I try and add @cnt and I get a message that it is already defined.:crazy:
Must declare the scalar variable "@cnt".
Sorry, but I don't see where you populated @ParmDefinition. Try this:
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max),
@ParmDefinition NVARCHAR(500);
set @ParmDefinition = N'@cnt int output';
set @OracleSchemaName = N'OracleSchema';
set @OracleTableName = N'OracleTable';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
print @ParmDefinition;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
September 2, 2014 at 3:40 pm
I changed the script to specify the actual Schema and Table Name.
It close but not quite there.
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max),
@ParmDefinition NVARCHAR(500);
set @ParmDefinition = N'@cnt int output';
set @OracleSchemaName = N'RDB_DWH_POLICY';
set @OracleTableName = N'COVERAGE_ASSOCIATE_ENTITY';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
print @ParmDefinition;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
It does not return the Record Count. It just says that it was executed successfully.
It prints the following but when I execute it I get an error that @cnt must be declared.
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, '
SELECT
COUNT(*) OracleTableCount
FROM
RDB_DWH_POLICY.COVERAGE_ASSOCIATE_ENTITY');
@cnt int output
[/code]
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 4:05 pm
Welsh Corgi (9/2/2014)
I changed the script to specify the actual Schema and Table Name.It close but not quite there.
declare @cnt int,
@OracleRowCount int,
@OracleSchemaName nvarchar(max),
@OracleTableName nvarchar(max),
@SQLCmd nvarchar(max),
@ParmDefinition NVARCHAR(500);
set @ParmDefinition = N'@cnt int output';
set @OracleSchemaName = N'RDB_DWH_POLICY';
set @OracleTableName = N'COVERAGE_ASSOCIATE_ENTITY';
select @SQLCmd = N'
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, ''
SELECT
COUNT(*) OracleTableCount
FROM
' + @OracleSchemaName + N'.' + @OracleTableName +
''');';
print @SQLCmd;
print @ParmDefinition;
EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @OracleRowCount OUTPUT;
It does not return the Record Count. It just says that it was executed successfully.
It prints the following but when I execute it I get an error that @cnt must be declared.
SELECT
@cnt = OracleTableCount
FROM
OPENQUERY(LS_RDB_DWH_POLICY52, '
SELECT
COUNT(*) OracleTableCount
FROM
RDB_DWH_POLICY.COVERAGE_ASSOCIATE_ENTITY');
@cnt int output
[/code]
Sorry, but I am at a loss. Using the same code, with a minor modification to use a SQL Server linked server and database, the code works just as it is supposed to work. I don't have an Oracle server to try this against so I can't duplicate your environment.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply