Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jdoering
Starting Member
32 Posts |
 Posted - 2003-06-19 : 16:04:34
|
Has anyone ever seen a stored procedure work in Query Analyzer and NOT in the DTS package? I have a stored procedure within a DTS package that is failing. The error message reads "SQL Server Unspecified Error".In query analyzer the stored procedure works just fine. The DTS pacakge is pointing to all the correct data sources. We have ruled out that it is a security or a permissions issue. We are running SQL Server Enterprise Edition SP3.Any ideas would be greatly appreciate! I:Thanks,Julie |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-19 : 16:08:23
|
| We need more information in order to help you out. Please describe your DTS package. Describe how you are calling the stored procedure.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-19 : 16:26:28
|
| dts is a client application so it has to connect to the server then run the SP.query analyser is also a client application and has to do the same.Obviously there is something different about the connection/call.Try comparing them using the profiler.There are a lot of properties to do with the connaction that can cause failures.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-20 : 00:10:31
|
| How did you rule out permissions?Did you login as the SQLServer/SQLAgent Service account, and run the SP?-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
jdoering
Starting Member
32 Posts |
Posted - 2003-06-20 : 09:59:32
|
quote:
Has anyone ever seen a stored procedure work in Query Analyzer and NOT in the DTS package? I have a stored procedure within a DTS package that is failing. The error message reads "SQL Server Unspecified Error".In query analyzer the stored procedure works just fine. The DTS pacakge is pointing to all the correct data sources. We have ruled out that it is a security or a permissions issue. We are running SQL Server Enterprise Edition SP3.Any ideas would be greatly appreciate! IWe ruled out permissions because we used the same logon ID to login to query anaylzer as we did in the DTS package. In addition, we know that the stored procedure inside the DTS package which gets called by the SQL Query Icon is calling the stored procedure, however it fails half way through on an 'Update' statement within the query. Throwing out an unspecified error on the update portion. Keep in mind that this same stored procedure executes just fine in Query Analyzer. That is what is so bizarre.Thanks,Julie
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-23 : 12:40:22
|
| Well...How do you know you're pointing to the correct data source.If for example you have dts set up, and let's say it's pointing to file at location d:\temp, when you run it will look at your local client side mappings, but if you schedule and let dts run, it will look at the server side mapping...1. Where's the data source you're looking at.2. I've never heard of DTS calling a sproc, what are you doing3. Don't use DTS...plain and simple...Brett8-) |
|
|
|
|
|
|
Ultimate BrainTeazer