DTS Execute SQL Task failing in SQLServer 2005

Hello All,
We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
Enterprise Edition 32-bit SP2.
The new version is working fine and the performance is also comparable. For
the DTS packages, I download and installed the DTS Runtime components. All
the DTS packages work except for one. The package, calls a SP residing in the
user database in "Execute SQL" Task and then copies the data into a flat file
via DataPump Task on a remote share. The remote share is available all the
time.
Last night the Execute SQL task had failed and this morning I troubleshooted
it and found out that this is happening randomly and whenever it fails I see
this in log that it writes to. I tested it once after the migration and I ran
it once again now and in both the cases it worked fine.
This process never used to fail in SQLServer 2000. So, I was wondering if
this has anything to do with SQLServer 2005.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks,
rgnHi
Error number 80004005 is a generic error and has quite a wide range of
causes for instance:
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
http://tinyurl.com/2s9qqm
In the long term you should be considering moving over to SSIS.
John
"rgn" wrote:
> Hello All,
> We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
> SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
> Enterprise Edition 32-bit SP2.
> The new version is working fine and the performance is also comparable. For
> the DTS packages, I download and installed the DTS Runtime components. All
> the DTS packages work except for one. The package, calls a SP residing in the
> user database in "Execute SQL" Task and then copies the data into a flat file
> via DataPump Task on a remote share. The remote share is available all the
> time.
> Last night the Execute SQL task had failed and this morning I troubleshooted
> it and found out that this is happening randomly and whenever it fails I see
> this in log that it writes to. I tested it once after the migration and I ran
> it once again now and in both the cases it worked fine.
> This process never used to fail in SQLServer 2000. So, I was wondering if
> this has anything to do with SQLServer 2005.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
> Thanks,
> rgn

DTS- excel option missing

I am a senior DBA using SQL Server 2000.

I just did a clean install of SQL 2000 Personal edition. I am trying to DTS an excel file in but the excel icon is not there!!!!! There are the other excel ODBC sources but they each require me to setup a DSN (not normal!!!)

Anyone had this problem before? I am uninstalling and reinstalling.

-KevinI don't understand...DTS in or out?

Are you using the wizard?

When open the menu option connection, what do you see?|||I am trying to import with DTS.
When I choose the "source" drop down list I am expecting to see a little excel icon with Excel 97-2000 next to it.

I see a bunch of data direct closed icons and the excel treiber , and the WINSQL excel workbook. All of those options require me to define a DSN.

The normal excel option just lets you choose the excel file.

?!?!?!?!?|||So you're in EM and you right click on DTS and SELECT

>>ALL Tasks >>IMPORT DATA...

Then you use the wizard and change the data source drop down...

and you don't see the teal "X" for Excel?

It's half way down the list...

What if you try to build one from scratch?|||No luck.

I am going to try to do it with Informatica.
I applied mdac 2.8 and SP3a.|||Do you have excel installed on the local machine?|||Yes. Thanks for your help. I found another work-around using Erwin instead of SQL Server.
Thanks anyway.

-Kevinsql

DTS oracle Jobs hang

hello:
My situation is
(1) I upgraded MS SQL 7.0 from original edition to SP 4
(2) DTS packages which download data from an oracle server work fine both on a remote machine and on the Server
(3) When these packages are scheduled to run as jobs they do not succeed and do not fail they just keep running e.g for 58 hours over the weekend
(4) even if I start the job on the server I still get the same behaviour:

Can anyone help please?I have lots of experience with running DTS packages to Oracle, but none in a SQL 7 SP4 environment. These are the things that I would do:

1. Check your application error log; look for any warnings and/or errors that are related to Oracle.
2. Open of SQL*Plus on the server; attempt to connect. Look for any error messages or warnings.
3. Delete and re-initialize the Oracle connection objects in your DTS packages; this can be a pain. Try creating the new connection first and then changing the connection name in the connection objects that are "in the flow".
4. Save the DTS package as a VB file and open (and execute) the package in VB. I dunno if this is an option with SQL 7.0 SP4; it is with 2000.

Sorry, that's not very much to go one for right now. I have seen this issue with AS/400 connections (frequently). It boiled down to an application pop-up window which was not visible when the DTS job ran as a job (but which was visible when executed from the DTS pacakge maintenance window).

Best of luck.

Regards,

hmscott|||Thank you HMScott

I've investigated these and still could'nt find the problem. Then I noticed that the jobs actually work but are not marked as finished. This seems identical to http://www.dbforums.com/t943042.html except that this SQL 7.0 and that was SQL 2000. I still cant get the jobs to mark themselves as finished. ??|||Out of curiousity, where are you seeing that the jobs are not marked "finished". If you are looking at the jobs through the job monitor (under Management/SQL Server Agent/Jobs), are you remembering to periodically refresh the screen (press F5)?

Regards,

hmscott|||I sure do HM (F5 that is) The jobs normally take a minute but when I cancelled them they had been running for 58 hours plus. I'm begining to wonder if it's something to do with the mail client. This machine was also hit by the Lovebug virus which is mail related I believe.

I'm no expert but I was wondering if there was anyway I could get a hint as to whats wrong. I looked in sqlagent.out but could see nothing.
I ran a trace and then started the job. Could'nt see anything obvious but to be honest was a bit lost in there in Trace World.|||Can take help of this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule dts as a job.

And also this Winnet mag article (http://www.winnetmag.com/Article/ArticleID/14399/Windows_14399.html) for reference.

HTH|||Thanks to all for their input:

Current situation
The jobs are now working.
I went into the Package properties and disabled the "USE OLE DB service components" and now the jobs work and mark themselves as completed. If I enable this then the Oracle jobs wont mark themselves as completed.
This weird as the connection still uses the "Oracle provider for OLE DB"

Nevertheless it is progress

dtexec

I have saved a copy of a package to a 64-bit server running Windows 2003 Enterprise Edition SP1. I changed the Run64BitRuntime to true before saving the package. When I try to use DTExec to execute the package which does have a script task in it I receive the below error.

Error: 2005-08-17 16:52:32.89
Code: 0x00000005
Source: Script Task 1
Description: The script files failed to load.
End Error

Any help is appreciated.

Thanks.In your script task set PreCompileScriptIntobinaryCode=TRUE

This is a known issue with 64-bit machines. The default setting for this property (as of the next CTP) has been set to TRUE because of it.

-Jamie|||Jamie is correct that the default value of this property has been changed from False to True. The Integration Services team opted for 64-bit convenience at the cost of increased package size. 32-bit users may want to measure the impact of this setting if package size is ever a concern.

-Doug|||Thanks you guys - I have been able to get the packages scheduled.