Deadlock problem? 3 way conditional split of data from one table to another never completes

I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.

Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.

Aren't you using a union all transformation before the destination to bring your streams back together?|||

If your situation allows it simply uncheck the destination table option "Table Lock" and it will work.

Philippe

|||That was it! Thanks.|||Did not try that. Is that the recommeded technique to use in this situation?|||

Great, just make sure that the union all task is not better appropriate.

I use the do not lock table option only on tables that I kow for sure no other process is trying to update and or insert into.

And I do this at a time of night when nothing is accessing the table. Preferably against a staging table that will replace the production table using either sp_rename or things like that.

Philippe

|||

Jeff-B wrote:

Did not try that. Is that the recommeded technique to use in this situation?

If you were previously using separate destination connectors for the same table, then yes, that would be the recommended technique.

|||

I'd agree with Phil and Philippe - a UNION ALL component is the better way to go. It will be more performant too because there is only one insertion operation.

-Jamie

|||Would this still be the case if you were using different derived fields or different source table fields for each source to populate the fields of the target table. Does the UNION ALL allow for mapping of each source to the target or does each source to the UNION ALL have to have the same set of fields?|||You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.|||

Phil Brammer wrote:

You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.

I also want to clarify that if your different data flows were going to the same physical table, then yes, a union all transformation is what you want. It'll work, trust me! Come back here if you have issues with it.|||Thanks Phil. I think I understand how to use this feature now. I'll experiment and see if I achieve the same result with the 4 independent paths to the same table.

DBTYPE_DBTIMESTAMP OLE DB question

Folks,
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||

Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||

Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||Thaks that′s the solution I was traying for two hours really thanks!!!!

DBTYPE of 130 at compile time and 5 at run time

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

What were you trying to do when you encountered this error?|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

I have got the same error trying to populate a table on SQL 2005 for datawarehousing purposes from a view in Oracle 9i.

I found that the problem is usually in the data type of the columns defined in the Oracle view: for example SQL 2005 doesn't like a oracle INTEGER, but if you convert the integer columns in a NUMBER the error will disappear.
The same with the Oracle VARCHAR2.

I tryed also to set the 'lazy schema validation' option using sp_serveroption as suggested here
http://www.dbforums.com/archive/index.php/t-815652.html
but it says that this option is not available in this version of SQL...|||

Hi,

I encountered the same problem.

It occured with Oracle >= 9.2.6 (w. Sql2000 and Sql2005)

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

regards

Hans

|||

Hans G. wrote:

In my situation, the SQL-error 7356 does not happen using the OPENQUERY-format. (select * from OPENQUERY(ORASRV, "ora-select-stmnt.....")

This is may be because the OPENQUERY does not perform a validation of the query you submit before execution time.

Hans G. wrote:

It only occurs on Querys in the format of "select * from ORASRV..USER.TABLE"; and thereby only; if the oracle-table has fields of type "number".

When I alter the oracle-number-fields to a more precise type of e.g. number(10), the problem is solved. So, I altered all "number" to "number(38)" (the maximal allowed number format) and the problem was bypassed.

This probably has to do with the implicit conversion that the OLE DB provider performs importing data from Oracle.
I wasn't able to translate the codes provided in the error to the corresponding datatype, but looking at "Data Type Mapping with Distributed Queries" in BOL it looks like that the NUMBER type in Oracle does not correspond to any of the DBTYPE implicitly converted to numeric(p,s)...

HTH
IgorB|||

Faiz Farazi wrote:

If any one can help

OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time. [SQLSTATE 42000] (Error 7356) OLE DB error trace [Non-interface error: Column 'ATP' (compile-time ordinal 1) of object '"IGS"."ABCD"' was reported to have a DBTYPE of 130 at compile time and 5 at run time]. [SQLSTATE 01000] (Error 7300). The step failed.

Thanks

www.databasetimes.net

MR Alam (Lascomp)

Thanks Mr Alam for your support . the link you have send to me it help my problem . Thanks gain http://www.lascomp.com

Faiz Farazi

www.databasetimes.net

|||

Thanks For all the answer-

Faiz Farazi

MCDBA,OCA,A+

www.databasetimes.net

|||

this is an pain of an error

we have it too, when converting data from Oracle 9.2 running financials across to SQL 2000 tables

"openquery" seems to work best - also we found that upgrading OLE/DB and MDAC drivers had no effect !!

also pulling the data from Visual Basic seems to work better than from SQL Server directly - which is odd ?

It appears, from reading forums, that a field defined as "TEST NUMBER" is typeless

whereas a field defined as TEST NUMBER(10,2) is not - hence ODBC or OLEDB drivers get confused as they dont know what to convert these things to.

any further comments welcome

regards

KD

|||Thaks that′s the solution I was traying for two hours really thanks!!!!

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!

DBSTATUS_UNAVAILABLE error

I have a pretty complex Data Flow that finishes with OLE DB Command. This calls a Stored Procedure with parameters (60-70 of them). The packgaes compiles OK and in run time, I get error below. When I go ahead and edit the field - remove it - save the package - add it back and re-run the package - it generates same error for a different field. When I re-assign all fields and re-run it again - it starts over to give me same error.

Help!

Error: 0xC0202009 at Load Divisions, Insert New Division Data record [82749]: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC020901C at Load Divisions, Insert New Division Data record [82749]: There was an error with input column "exists_divid" (86315) on input "OLE DB Command Input" (82754). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Load Divisions, Insert New Division Data record [82749]: The "input "OLE DB Command Input" (82754)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (82754)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Load Divisions, DTS.Pipeline: The ProcessInput method on component "Insert New Division Data record" (82749) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0xC0209029.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load Divisions, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

heh...it works now. It was varchar source column and target INT column. The issue is it never errored this column - but told me about some other issue not even related.

Once I manually matched each field and fixed it - it started going through.

It's gotta be fixed...

|||Also - the same error message appears when your OLE DB command invalidates constraints - for example I experienced this error when I was trying to do UPDATE of a Column with a NULL value - and the column had not null constraint.

Thanks Microsoft for such a "helpful" error message!

DBPROPSET-> ???

I am doing some low level OLE DB programming to extract data out of MsSql 20
00 and need to find out what the DBPROPSET_DBINIT
value such as DBPROP_AUTH_USERID would be for the ADODB connectionstring equ
ivilant of 'Application name=' is. I can not find
matching property ID to be used in the DBPROPSET constants. It seems like it
should be DBPROP_APP_NAME but I can not find
anything described as such.
Does anyone have any idea what I need to use to have the 'ApplicationName' c
olumn in the Sql Profiler display the value for
a native OLE DB connection?
Dennis PassmoreI guess I am talking to myself again.
I found a answer that seems to work for now at least. Using the
DBPROP_INIT_PROVIDERSTRING identifier and setting the value to the
syntax of 'APP=myprogramname' is working.
Dennis Passmore

DB-Library vs. OLE DB

Are there any benefits in choosing OLE DB interface instead of using the native DB-Library connectivity?
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...
> Are there any benefits in choosing OLE DB interface instead of using the
> native DB-Library connectivity?
> What is recommended?
> Thanks,
> Adam|||Thanks Brian! Any known loss of available functionality in 2000 that can't be accessed DB-Library?
Adam|||Adam,
> Any known loss of available functionality in 2000
> that can't be accessed DB-Library?
Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX® Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?url=/library/en-us/bldgapps/ba_highprog_56pc.asp?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
--
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||=?Utf-8?B?QWRhbSBUaWNrdGlu?= (anonymous@.discussions.microsoft.com) writes:
> Thanks Brian! Any known loss of available functionality in 2000 that can't
> be accessed DB-Library?
Quite a bit.
I have a list on
http://www.sommarskog.se/mssqlperl/mssql-sqllib.html#restrictions_with_new_datatypes
This text is in the context of two Perl modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

DB-Library vs. OLE DB

Are there any benefits in choosing OLE DB interface instead of using the nat
ive DB-Library connectivity?
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...

quote:

---

> Are there any benefits in choosing OLE DB interface instead of using the
> native DB-Library connectivity?
> What is recommended?
> Thanks,
> Adam

---

|||Thanks Brian! Any known loss of available functionality in 2000 that can't b
e accessed DB-Library?
Adam|||Adam,

quote:

---

> Any known loss of available functionality in 2000
> that can't be accessed DB-Library?

---

Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?...>
p?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
Alan Brewer [MSFT]
Lead programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||examnotes (anonymous@.discussions.microsoft.com) writes:

quote:

---

> Thanks Brian! Any known loss of available functionality in 2000 that can't
> be accessed DB-Library?

---

Quite a bit.
I have a list on
es" target="_blank">http://www.sommarskog.se/mssqlperl/...tatyp
es
This text is in the context of two PERL modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp