Why isn't the connection manager managing this?
How can I trace the cause? The data flow component (insert SQL deals) is just a simple INSERT statment.You can always run SQL Profiler to see what's going on.
Hi,
I′m trying to make several distributed queries to a linked server, and for some of them I get this error message. I can′t find anytihing about it anywhere. Can anyone tell me what could be the reason of this error? The way I'm executing the query in my stored procedure is:
exec sp_executesql @.sqlinsert
@.sqlinsert being a nvarchar sentence that contains the distibuted query.
Thank you!
Mehdi
Guess the provider needs a return value. I don′t know if it needs a return status like return 1 or return 0, or he needs something to handle (just a dummy select or something else. Try setting the return value for the stored procedure.HTH, Jens Suessmeyer.
Hi all,
I am using a sp for show the external tables in a linked server ABC which via Ole DB for Visual Foxpro.
sp_tables_ex ABC
It returns error,
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "SQL Server" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.
When I used the same command to other linked server(with ole DB VFP also but with different path). And I tried 'select * from openquery(ABC, 'select * from product')'. Both work fine.
Kindly advise. Thanks in advance.
Hi Stephanie,
Are you by any chance running into the problem described in this KB article:
FIX: OLE DB Provider Improperly Enumerates DBSCHEMA_TABLES
http://support.microsoft.com/kb/314888
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I created a view refer to a visual foxpro with ole db for visual foxpro which was working fine before. But now, I received an error while select the view
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "ORST3".
Please advise. Thanks in advance
Thanks
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Warning)
Messages
Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)
- Setting Destination Connection (Success)
- Validating (Error)
Messages
Warning 0x80202066: Data Flow Task: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (117).
(SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [Telbill].[COLL_DB_TESTING].[SERVICE] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
I cant understand wat exactly is wrong....how can i deal with this problem....the data type i m copyin the same thats there in oracle...
y is it not working...can someone please tell me...
thanks in advance,
regards.
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||
Thanks for your support, Crispin,
I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)
Column 5
Error: Year, Month and day parameters describe an unrepresnatable date and time...
I doubt that the DateSerial function in the script component is unable to extract some formats..
So,
1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here
Thanks
Subhash Subramanyam
|||Hi Experts in SSIS forum,
Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:
(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)
Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:
[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.
Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues
Thanks
Subhash Subramanyam
|||
Dear Umachandar, Thanks for briefing up the approach.
I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?
Umachandar's Reply
Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.
Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.
Thanks
Subhash Subramanyam
|||I've put up an article for this on my blog.
Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:
Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm
s AM')) as REQUESTED_ETA_DATE_CUST
Or
Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||
Thanks for your support, Crispin,
I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)
Column 5
Error: Year, Month and day parameters describe an unrepresnatable date and time...
I doubt that the DateSerial function in the script component is unable to extract some formats..
So,
1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here
Thanks
Subhash Subramanyam
|||Hi Experts in SSIS forum,
Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:
(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)
Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:
[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.
Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues
Thanks
Subhash Subramanyam
|||
Dear Umachandar, Thanks for briefing up the approach.
I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?
Umachandar's Reply
Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.
Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.
Thanks
Subhash Subramanyam
|||I've put up an article for this on my blog.
Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:
Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST
Or
Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.
