Duplicate Events

The nature of the event provider for my SQLNS application is to produce
exact duplicate rows into the event table. Obviously, I am not
interested in receiving duplicate notifications. What are some
strategies for having SQLNS produce one notification for several exact
rows being sent to the events table.

I have an event chronicle that retrieves distinct rows from the event
table and puts them into the chronicle. My thoughts were that I could
use the chronicle table in my matching statement. But the timing
doesn't seem to be working out. The matching is apparently done before
the chronicle is updated.

Any thoughts on strategies for having SQLNS produce one notification
for several exact rows being sent to the events table.

There is probably a more direct method to accomplish your goals. I'd suggest you really consider filtering the duplicate events in the event provider before they ever get to the events table.

However, if that's the route you want to take, the matching rule is
really just a T-SQL statement. Version 2.0 uses a UDF to create the
resultset while v2005 uses a new view to do the same. So you can try using a combination of the chronicle table with the distinct keyword to filter out the dups.

BTW - matching rules are processed in the following order:
- Event chronicle rules
- Subscription event rules
- Subscription scheduled rules

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

Duplicate Events

The nature of the event provider for my SQLNS application is to produce
exact duplicate rows into the event table. Obviously, I am not
interested in receiving duplicate notifications. What are some
strategies for having SQLNS produce one notification for several exact
rows being sent to the events table.

I have an event chronicle that retrieves distinct rows from the event
table and puts them into the chronicle. My thoughts were that I could
use the chronicle table in my matching statement. But the timing
doesn't seem to be working out. The matching is apparently done before
the chronicle is updated.

Any thoughts on strategies for having SQLNS produce one notification
for several exact rows being sent to the events table.

There is probably a more direct method to accomplish your goals. I'd suggest you really consider filtering the duplicate events in the event provider before they ever get to the events table.

However, if that's the route you want to take, the matching rule is
really just a T-SQL statement. Version 2.0 uses a UDF to create the
resultset while v2005 uses a new view to do the same. So you can try using a combination of the chronicle table with the distinct keyword to filter out the dups.

BTW - matching rules are processed in the following order:
- Event chronicle rules
- Subscription event rules
- Subscription scheduled rules

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

DTSing Oracle data into SQL Server

I need to import some data from Oracle into Sql Server.
I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).
Do I need to install anything else on the SQL Server so that I can import the data?
Cheers
NickOriginally posted by riggers
I need to import some data from Oracle into Sql Server.

I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).

Do I need to install anything else on the SQL Server so that I can import the data?

Cheers

Nick
I don't think so|||linked server|||I've tried just installing the OleDb provider but it appears from furthering reading on the web that I need to install the oracle client tools aswell as the OleDb provider.

I've been told that we are running Oracle 10.7 and I can not appear to find the client tools for this on Oracle's website.

Does anyone know where the client tools for this version of Oracle are?

Cheers

Nick

Dts.log in script task

Hi

Can someone tell me where I can find the log file created by the Dts.log method in script task.

I have created a log provider file as Mylog.xml, but the messages recorded are from the Dts.Event.FireInformation method and not the Dts.log method.

I don't know where the messages are filed.

Regards

Baldev

To log the output of Dts.Log() to a Log Provider, go to the "Configure SSIS Logs" dialog (e.g. SSIS/Logging...). Change the Logging Mode on the Script Task from the default of "UseParentSetting" to "Enabled". That is , click on the check box next to the Script task until it is checked ( LoggingMode = "Enabled") and not checked and greyed out (LoggingMode = "UseParentSetting") or unchecked (LoggingMode = "Disabled").

With the Script Task node selected, navigate to the Details tab, and select the "ScriptTaskLogEntry" event. Dts.Log() calls will now be sent to whatever log providers are enabled for the Script Task itself. Also, make sure to select the log provider for the script task "again", since this is effectively overriding the parent containers logging settings.

|||

Great, that's exactly what I wanted.

Thanks a lot

Baldev

DTS wont work with Ole DB provider but will with odbc

I started another thread thinking this migh have something to do with an upgrade from trial version to enterprise version

So everything seems to be working (haven't tested alot) but now I can't run DTS!

I get a weird error.

so then I tried ODBC and it works :[sp3

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Truncates data to destination

Hi all
I am using SQL Server 2000, since I dont have OLEDB provider for
Postgres, and SSIS does not support ODBC connection for migartion of
data.
I want to move data from my source tables to other database, for about
1000 tables.
Problem is that size of columns in source is large while the size of
columns in destination is small. I dont have control over desing of
destination database.
When I migrate data from source to destination, DTS truncate data to
size of dstination column and migarate data, but does not show any
warning that data is truncated.
If I will get warning it will be definately good for us to find out
tables in which data is truncated.
Because this we are not able to find out for which columns data is
truncated, its a big headache for the developers.
RegardsHi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:

> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>|||Hi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:

> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>

DTS Truncates data to destination

Hi all
I am using SQL Server 2000, since I dont have OLEDB provider for
Postgres, and SSIS does not support ODBC connection for migartion of
data.
I want to move data from my source tables to other database, for about
1000 tables.
Problem is that size of columns in source is large while the size of
columns in destination is small. I dont have control over desing of
destination database.
When I migrate data from source to destination, DTS truncate data to
size of dstination column and migarate data, but does not show any
warning that data is truncated.
If I will get warning it will be definately good for us to find out
tables in which data is truncated.
Because this we are not able to find out for which columns data is
truncated, its a big headache for the developers.
RegardsHi
http://pgfoundry.org/projects/oledb seems to be the project that is building
an OLEDB driver for progress! You may want to try using an interim file
format exported using SSIS (say Excel) or possibly use a linked server
instead.
John
"shahdharti@.gmail.com" wrote:
> Hi all
> I am using SQL Server 2000, since I dont have OLEDB provider for
> Postgres, and SSIS does not support ODBC connection for migartion of
> data.
> I want to move data from my source tables to other database, for about
> 1000 tables.
> Problem is that size of columns in source is large while the size of
> columns in destination is small. I dont have control over desing of
> destination database.
> When I migrate data from source to destination, DTS truncate data to
> size of dstination column and migarate data, but does not show any
> warning that data is truncated.
> If I will get warning it will be definately good for us to find out
> tables in which data is truncated.
> Because this we are not able to find out for which columns data is
> truncated, its a big headache for the developers.
>
> Regards
>