Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Kicking off DTS package from T-SQL 1

Status
Not open for further replies.
ks1392

ks1392

Programmer
Dec 7, 2001
63
0
0
US
Hi, I've been using a stored procedure I found on databasejournal.com for some time. The stored procedure,
spExecutePKG, allows you to kick off a DTS package from withing a T-SQL statement. The procedure was working fine up until a few days ago. Now, when I try to run it I receive the following error:

*** Execute failed
OLE Automation Error Information
HRESULT: 0x80040428
sp_OAGetErrorInfo failed.

Not only is the spExecutePKG proc failing, but the sp_OAGHetErrorInfo proc is failing to retrieve any detailed information about the failure. Has anyone else come across this issue?
 
Sort by date Sort by votes
Hi,

I haven't seen this error before, but I would go about troubleshooting it in the following way:

Run the package manually, server-side, logging in via Terminal Services (or at the server itself) and execute the package from Enterprise Manager. This usually reveals errors that are obscured via your Transact-SQL call, or when you run it client-side.




Best Regards,
Joseph Sack, Author of "SQL Server 2000 Fast Answers for DBAs and Developers".
 
Upvote 0 Downvote
Since my post I have figured out the nature of the error -- the T-SQL call was structured incorrectly.

Thanks,
Kerr
 
Upvote 0 Downvote
Honestly, I can't remember exactly since it's been so long. Assuming you're using the scripts I linked to at the database journal site, my T-SQL successfully runs like this:

EXEC spExecutePKG @Server='ServerName',
@PkgName='PackageName', @IntSecurity=1


 
Upvote 0 Downvote
The only other item I can think of is to, if possible, change from @IntSecurity=1 to 0, and then use the optional user/pass parameters to see if you can run it that way.
 
Upvote 0 Downvote
I found the problem, it seems that my tempdb is having issue with the DTS package.

The error I get when running the DTS package is
Code: 
Could not allocate new page for database 'TEMPDB' There are not more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files or allowing file growth

So I need to figure out how to fix this.
Thanks anyway


George Oakes
Check out this awsome .Net Resource!
 
Upvote 0 Downvote
I've never seen that specific error, but is tempdb set up for a max file size or unrestricted growth? Also, is the filesystem it's on very close to full?
 
Upvote 0 Downvote
the tempdb is set to autogrow, with no max size set, there are 22 gb of disk space left.

currently the tempdb is set to 306 mb, and only 15% of that is in use, the transaction log is 15 megs. we tried to manualy increase the tempdb file size too 500 mb but it wouldn't take. so we moved the file size up a little at a time, and then we could finaly set the size to 500 mb. totaly bizare . we still cant figure out why it wasn't autogrowing. perhaps the server needs a swift re-boot in the butt?

Thanks


George Oakes
Check out this awsome .Net Resource!
 
Upvote 0 Downvote
It's quite possible. I've had issues with SQL Server and RAM resources where I could not run, coincidentally, this exact same SP due to the SQL Server service taking all 2GB of my system memory. I can't remember the exact error message now , but it was an indicator that new RAM could not be allocated to run the SP. In this case I had to stop and restart the service for it to work. I have since set a hard ceiling on SQL Server's memory usage, plus we do weekly reboots during a maintenance window.

We also run an AIX box with a *very* old Informix database on it. It's slow as hell but I've never seen it go down or give me the trouble that SQL Server does sometimes.
 
Upvote 0 Downvote
It seems the older the database system, the more stable it has been. lol.

well I now know the cause of the tempdb growing so big, and it is in my dts.

We have a customer invoice database, and we keep the invoices, the customer info and vehicle info. the customer info was relational, but the vehicle wasn't so I modified the tables to allow the vehicle table to be relational. and in my dts, I am assigning the vehicle id to the invoice.
But what the dts is trying to do is set ALL the vehicle ID to all the INvoices, and we have over 65 gigs of data. I will make an adjustment to the sql so it only trys to set the vehicle id on Invoices that are being inserted, and not the existing data. this way the dts wont create a huge temp db file. I will manual assign the vehicle ids late one night when the system is not in use

Thanks

George Oakes
Check out this awsome .Net Resource!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Truncated Excel Fields
Replies
0
Views
72
grnzbra
grnzbra
petart
  • Locked
  • Question
Problem Date MS SQL Server
Replies
0
Views
65
petart
petart
arodri
  • Locked
  • Question
MSVisual Studio - DTSX Package creating new text file
Replies
0
Views
65
arodri
arodri
Fsoumia
  • Locked
  • Question
DTS Logging
Replies
2
Views
70
Fsoumia
Fsoumia
Azzuri
  • Locked
  • Question
DTS To SQL Server 2008
Replies
1
Views
70
mrdenny
mrdenny

Part and Inventory Search

Sponsor

Back
Top