ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
Paul
Hi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com

ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
PaulHi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com

Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um JunkWhen you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um Junk
When you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
Bala
Don't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:
>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>sql

Error--Urgent

I am geeting the following error in dts
"Duplicate key was ignored"
How to suppress this message in dts?
Version : Sql server 2000 service pack 3a
Thanks
BalaDon't think you can suppress the message. There are a few
ways to deal with it - one option is to import the data into
a staging table and then import the data into the real table
using SQL for whatever logic you need to import from the
staging table into the final table.
-Sue
On Wed, 16 Jun 2004 15:11:24 -0700, "Bala"
<Balak@.herbalife.com> wrote:

>I am geeting the following error in dts
>"Duplicate key was ignored"
>How to suppress this message in dts?
>Version : Sql server 2000 service pack 3a
>
>Thanks
>Bala
>

Errors with SP4 upgrade... Please Help

I am having problems accessing DTS after install SP4 and was wondering
if someone could offer some advice.

I installed SP4 and got the following error after it competed.

Unable to write to response file 'U:\WINDOWS\setup.iss' during
recording. Please ensure enough space is available on target drive.

I got the error 3 times (3 pop-ups).

After the install I could not access DTS. I was getting 2 error
pop-ups.
1. 'rebbrui.rll is missing'
2. Snap-In failed to initialize: Meta Data Services

I read the groups and tried several things.

I tried to reinstall MDAC and it did not help.

I tried to register several dlls and it did not help.

I found this post and it seemed to help.
Repair Script:
http://www.experts-exchange.com/Dat...Q_21445589.html

I was then able to access DTS using the profile (which is an
administrator) which
I used to install SP4 and run the above repair script. However, no
other profile can open or run DTS.

I tried running the repair script under the other profiles and it did
not help.

I was receiving 2 errors for the other profiles. I only documented one
and I am no longer getting the other. Now it just goes to a white
screen and hangs.

1st error:
DTS Designer Error
The specified module could not be found.

2nd error:
???

I tried reinstalling client tools and SP4 and it did not help. Now I
get one additional error.

Before I can expand the server node in enterprise manager I get the
following error.
Snap-in failed to initialize
Name: Meta Data Services
CLSID: {1DBA4DD4-EB97-4FD2-AB80-9D0D4BA74034}

Does anyone have a suggestion on how I might fix this?

I am running SQL Server 2000 Enterprise on Windows 2000 Server with 4
GB RAM.

I did notices that someone has set the /3GB switch. I know this is not
supported on this OS but it was not causing major problems before the
upgrade. Could it be the culprit?Finally got this fixed. It was a real pain!

The registry on this server looked all hacked up to me. It looks like
many of the keys referenced U:/ instead of C:/

Some of the necessary dlls were pointing to U: also.

I had to manually uninstall SQL Server. Then reinstall. Then uninstall
via add remove programs. Then I did another uninstall/reinstall
(because i got a few errors).

I reinstalled, upgraded to sp4, stopped the service, swapped my master,
msdb, tempdb with the originals and everything is working great.

Errors processing data from Oracle to SQL

hi here′s a new one.....
i′ve created a dts that is using an odbc source to connect to an oracle server, the conexion works just fine and i have no problems with it, then, i run this package from mi computer and the data transfer ends succesfully, when i upload it to my server in SQL 2005 and set it into a job.. i get errors like these:

Event Name: OnError
Message: Thread "WorkThread0" has exited with error code 0xC0047039.

Event Name: OnError
Message: 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.

Event Name: OnError
Message: The PrimeOutput method on component "table" (1) returned error code 0xC02090F5. 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.

Event Name: OnError
Message: The component "faccomitecedidos" (1) was unable to process the data.

Event Name: OnError
Message: The "component "table" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "diasperm" (1707)" specifies failure on error. An error occurred on the specified object of the specified component.

why is this happening , any solutions or ideas? the data i want to extract comes from an sql command not a table
this is the query:

SELECT
FCNSS ,
FITIPOSOLICITUD ,
FIFOLIO ,
RTRIM(FCNOMBRE)||' '||RTRIM(FCAPPATERNO)||' '||RTRIM(FCAPMATERNO) AS NOMBREAFILIADO,
fdfinicta as FAFIL ,
fdFecCedido as FCED ,
(fdFecCedido-fdfinicta) AS diasperm ,
FNSALARIOACTUAL AS SALAFIL ,
(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,
(SELECT DISTINCT (FISDI/48.60) FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS CalSalCED ,
FCNUMPROMOTOR AS cod_promotor,
(SELECT RTRIM(FCNOMBRES)||' '||RTRIM(FCAPEPATERNO)||' '||RTRIM(FCAPEMATERNO)
FROM gentec_own.prommaestro where FCNUMPROMOTOR = gentec_own.faccomite.FCNUMPROMOTOR AND ROWNUM = 1) AS NOMPROMOTOR,
FICVEENTCED as aforeorig ,
FCAFORECEDIDO as aforeced ,
FNINGCOMT ,
FNCTOPROMOCION ,
FNCTOADMON ,
FNCONTRIBUCION ,
fcCanal as Canal ,
FCDIVISION as Division ,
FCREGION as Gerencia
FROM gentec_own.FACCOMITE
WHERE FCCEDIDO = 1
and (to_char(fdFecCedido,'yyyymmdd')>=to_char(sysdate-8,'yyyymmdd') and to_char(fdFecCedido,'yyyymmdd')< to_char(sysdate,'yyyymmdd'))
order by fdFecCedido;

please!!!1 somebody

maybe the problem is here:

(fdFecCedido-fdfinicta) AS diasperm

|||

hi unfortunately this wasn′t the error, i tried changing the query for a view in the oracle server but the error is stil the same

Event Name: OnError

Message: The "component "DataReader Source" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "DIASPERM" (404)" specifies failure on error. An error occurred on the specified object of the specified component.

and the query is just :

select * from db.view

and that′s it.... any posible solutions? is there any complications if the server where i′m running the job is under 64 bits and the dts ′s been created under 32 bits? if so. where can i find a 32 bits odbc driver for oracle that i may install in the target server!!!! please!!!!!!!!!!

|||

It looks like a SQL syntax problem. If you try to issue your entire original statement DIRECTLY against your oracle database, do you not get an error?

It seems to me that you need to tie back your results to the main query as you did here:

(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,

|||

i don′t think this is a problem of syntax cause i run the query directly on the oracle db and it works, it seems more like a problem of windows version, since i run this dts on my 32 bits pc i get the results without problems.. the problem comes when uploading the package to the target server that is running under 64 bits, to do this i'm using an 32bits odbc oracle driver but the odbc that is used in the target server seems to be under 64 bits , because of this, i'd like to try using the same kind of odbc so, where can i get
one?

a 32 bits odbc driver for oracle that works in a server running in 64 bits mode

pleaseee

Errors on importing a text file

When a DTS fails on a Text Source input with an error like "DTS_Transformation encountered an invalid data value for 'Column1' destination"

Is there a way to get the line number of the textfile where the import failed? It is hard to determine where in my 40,000-line file it found the invalid value for my column.

Thanks,
AndrewSpecify the LOG file in Package Properties/Logging.|||WHere does it store the logs on the server?|||Wherever you would like it to be.|||I set the error log file to a text file on my esktop - it seems to repeat the same error message (without the line number) - am i missing something?

errors from a child dts package

I am executing a child dts package from an activex script. While I get no errors when executing the child package independently, I get errors when executing it thru an activex script. Can anyone help?

Also, can anyone give me a sample code to display error messages from each step in a log file ?
Thanks

Here is the activex script I use:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()

Main = DTSTaskExecResult_Success
Dim SQLConn
Dim DB2Conn
SQLStr = "DRIVER=SQL Server;SERVER=GPDSQLIRV01;DATABASE=DailySales"
DB2Str = "DSN=SYSHDB2P"

Set SQLConn = CreateObject("ADODB.Connection")
Set DB2Conn = CreateObject("ADODB.Connection")

SQLConn.Open SQLStr, "script", "script"
DB2Conn.Open DB2Str, "hgrprd1", "ddcsgr1"

db2sql = "SELECT MAX(INSERT_TIME) AS INSERT_TIME FROM H.SA48 WHERE REC_TYPE = 'D' "
set db2sqltxt48 = db2conn.execute(db2sql)

SSsql = "SELECT INSERT_TIME FROM SA48_date_tbl"
set SSsqltxt48 = sqlconn.execute(SSsql)

db2sql49 = "SELECT AS_OF_DT FROM H.SA49 "
set db2sqltxt49 = db2conn.execute(db2sql49)

SSsql49 = "SELECT AS_OF_DT FROM SA49_date_tbl"
set SSsqltxt49 = sqlconn.execute(SSsql49)



If db2sqltxt48("insert_time") <> SSsqltxt48("insert_time") And db2sqltxt49("as_of_dt") <> SSsqltxt49("as_of_dt") then

Dim oPkg, oStep
Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName, sErrMsg
Set oPkg = CreateObject("DTS.Package")

' Assign parameters
sServer = "GPDSQLIRV01"
sUID = "script"
sPWD = "script"
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName = "DTS_Load_ProductTables"

' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName


oPkg.Execute

For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
MsgBox "hello"
End If
Next

oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing


End If

db2sqltxt48.close
SSsqltxt48.close
db2sqltxt49.close
SSsqltxt49.close

db2conn.close
sqlconn.close

Set db2sqltxt48 = Nothing
Set SSsqltxt48 = Nothing
Set db2sqltxt49 = Nothing
Set SSsqltxt49 = Nothing

Set db2conn = Nothing
Set sqlconn = Nothing


End Function......and the reason most of this isn't a stored procedure is?

What's the p[ackage suppose to do?|||Originally posted by Brett Kaiser
......and the reason most of this isn't a stored procedure is?

What's the p[ackage suppose to do?

Brett

The child package has several steps. Each step is downloading data from a DB2 table on the mainframe to a corresponding table on the SQLServer

Thanks|||Are you doing a lot of manipulation?

Is the whole process built already?

I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

Just seems easier..

As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..|||Originally posted by Brett Kaiser
Are you doing a lot of manipulation?

Is the whole process built already?

I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

Just seems easier..

As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..

Brett
No, no processing at all. Just select from the table on the mainframe and insert into the one on the SQLSERVER.
Regarding error messages coming from each step of the child package, can you give some sample code to capture the error code and message, and echo it to a text file in the parent package?

Thanks
Anil

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.

>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>
>.
>|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...[vbcol=seagreen]
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
>
> connection due to
> windows error
> server - both SQL &
> available?
> failure or table /
> a dbcc checkdb on

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?
Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>
|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.

>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>
>.
>
|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...[vbcol=seagreen]
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
> connection due to
> windows error
> server - both SQL &
> available?
> failure or table /
> a dbcc checkdb on

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.
>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>> We have been getting an error on a DTS package
>> that is trying to load about 800,000 records.
>> It used to work without problems.
>> Now.. It will fail anywhere between record 8000
>> and record 160,000.
>> It is not one set record. Here is the error message.
>> [DBNETLIB][CONNECTIONWRITE (SEND()).]
>> GENERAL NETWORK ERROR
>> It will get this error even when the server itself
>> runs the package, and the file is on the server's
>> disk drive.
>> Does anyone have any ideas?
>
>.
>|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
> >--Original Message--
> >Looks to me like the target SQL Server has re-set the sql
> connection due to
> >severe error of some sort - either sql engine error or
> windows error
> >perhaps. Have you checked the server logs on the target
> server - both SQL &
> >Windows logs to see if there's any further information
> available?
> >
> >This could be such a wide variety of things - even disk
> failure or table /
> >index corruption within SQL Server. You might try running
> a dbcc checkdb on
> >the target server as well as part of your investigations.
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Scot" <sstyer@.alltel.net> wrote in message
> >news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> >> We have been getting an error on a DTS package
> >> that is trying to load about 800,000 records.
> >> It used to work without problems.
> >> Now.. It will fail anywhere between record 8000
> >> and record 160,000.
> >> It is not one set record. Here is the error message.
> >>
> >> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> >> GENERAL NETWORK ERROR
> >>
> >> It will get this error even when the server itself
> >> runs the package, and the file is on the server's
> >> disk drive.
> >>
> >> Does anyone have any ideas?
> >>
> >
> >
> >.
> >

Errors after installing Server 2000 DTS Designer Components

Hello,

After Having installed the SQL Server 2000 DTS Designer Components necessary
to edit legacy DTS packages on 2005 server, I keep getting the following message when trying to open SQL Server 200 Enterprise Manager:

"The procedure entry point
?ProcessExecute@.@.YAXPAUHWND_@.@.PBG1@.Z could not be located in teh dynamic link library SEMSFC.dll."

Is there a way to fix so that I can open Enterprise manager?

Thanks!

If you have both SQL 2000 and 2005 client tools installed, you do not need the "SQL Server 2000 DTS Designer Components" package installed.

First, uninstall them. Then figure out why 2005 is telling you to install them, when you don't need them.

Make sure you have run 2000 SP4 on your CLIENT machine to update the tools to the current version.

In my case, the error appears to be related to having SQL 7 client tools installed. When I uninstalled them the error in 2005 went away.

|||

Thanks for the info. I really appreciate it.

|||

Good info. Fixed what I thought was a very cryptic error message.

Thanks for the details!

|||

Tom,

How do you install SP4 on a client machine. How do you specify the instance and user id if I don't have one installed on my machine?

Thanks,
Joseph

Errors after installing Server 2000 DTS Designer Components

Hello,

After Having installed the SQL Server 2000 DTS Designer Components necessary
to edit legacy DTS packages on 2005 server, I keep getting the following message when trying to open SQL Server 200 Enterprise Manager:

"The procedure entry point
?ProcessExecute@.@.YAXPAUHWND_@.@.PBG1@.Z could not be located in teh dynamic link library SEMSFC.dll."

Is there a way to fix so that I can open Enterprise manager?

Thanks!

If you have both SQL 2000 and 2005 client tools installed, you do not need the "SQL Server 2000 DTS Designer Components" package installed.

First, uninstall them. Then figure out why 2005 is telling you to install them, when you don't need them.

Make sure you have run 2000 SP4 on your CLIENT machine to update the tools to the current version.

In my case, the error appears to be related to having SQL 7 client tools installed. When I uninstalled them the error in 2005 went away.

|||

Thanks for the info. I really appreciate it.

|||

Good info. Fixed what I thought was a very cryptic error message.

Thanks for the details!

|||

Tom,

How do you install SP4 on a client machine. How do you specify the instance and user id if I don't have one installed on my machine?

Thanks,
Joseph

Errors after installing Server 2000 DTS Designer Components

Hello,

After Having installed the SQL Server 2000 DTS Designer Components necessary
to edit legacy DTS packages on 2005 server, I keep getting the following message when trying to open SQL Server 200 Enterprise Manager:

"The procedure entry point
?ProcessExecute@.@.YAXPAUHWND_@.@.PBG1@.Z could not be located in teh dynamic link library SEMSFC.dll."

Is there a way to fix so that I can open Enterprise manager?

Thanks!

If you have both SQL 2000 and 2005 client tools installed, you do not need the "SQL Server 2000 DTS Designer Components" package installed.

First, uninstall them. Then figure out why 2005 is telling you to install them, when you don't need them.

Make sure you have run 2000 SP4 on your CLIENT machine to update the tools to the current version.

In my case, the error appears to be related to having SQL 7 client tools installed. When I uninstalled them the error in 2005 went away.

|||

Thanks for the info. I really appreciate it.

|||

Good info. Fixed what I thought was a very cryptic error message.

Thanks for the details!

|||

Tom,

How do you install SP4 on a client machine. How do you specify the instance and user id if I don't have one installed on my machine?

Thanks,
Joseph

Error=cantconvertdata

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:Hello, Can you tell me please how I can solve this problem?? Because I have the same problem. THanks.
Yolanda

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:

Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc

Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

The only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc