| Author |
Topic |
|
kloknox
Starting Member
4 Posts |
 Posted - 2002-01-15 : 05:42:54
|
| Hi Guys & Gals,I have a number of nested stored proceedures going through 6 levels, the DTS package imports a text file, manipulates it into the correct format and then passes the data line by line through to the tables. When it gets to the processing using stored procedures the transactions and cursors open then close without running any of the code.Can anyone help me understand what might be happening.Number of Stored procedures used 15.AliEdited by - kloknox on 01/15/2002 05:59:14 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-15 : 06:56:35
|
| You haven't supplied much info to go on but something isn't right. Having 6 levels of stored procedures, DTS, transactions and cursors sounds way too complicated and ripe for failure.If you post some more details and code, someone might be able to help. |
 |
|
|
kloknox
Starting Member
4 Posts |
Posted - 2002-01-15 : 07:10:13
|
| Thanks for this, but due to the size of the code it will be a problem for me to add it in.I'm not sure what the problem is, as it doesn't even error, just opens and closes the Transactions/cursors etc etc...... without processing the code.Thank you anyway,Ali |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-15 : 07:57:21
|
| When the Stored Procedure is getting executed, closely follow its execution path with Sql Profiler. you might be able to catch the bug in it.I agree with Scott, 6 levels of Stored procedure, Cursors , DTS packes will be taking helluva resources , you need to seriously look at the code and organize it properly. if you need help in organizing and making your code efficent, esp from curosor based to Set based. lot of extremely talented people are here to help you.----------------------------------"True love stories don't have endings."Edited by - Nazim on 01/15/2002 07:59:05 |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-15 : 08:21:15
|
| Another possible suggestion is to break up the code for testing to see what works and what doesn't. You can put some RETURNS in the stored procedures to do this. Agree with Nazim to watch it in profiler to see what variables are being passed, etc.Good luck |
|
|
|
kloknox
Starting Member
4 Posts |
Posted - 2002-01-17 : 08:44:00
|
| Thanks Guys,I've ran the profiler with all attributes switched on, this is how I knew that the code wasn't being executed just openning the cursors / transactions and then closing them.When I ran the Stored Procs individually the code runs perfectly but when it ran together with the DTS package it gives me the old chestnut "It works when I test it", well falls over anyway.I'm now residing to the fact that it should be broken down into a number of smaller groups of procedures.Thanks for your help.AliI maybe ugly but I'm good |
|
|
|
kloknox
Starting Member
4 Posts |
Posted - 2002-01-17 : 08:44:12
|
| Thanks Guys,I've ran the profiler with all attributes switched on, this is how I knew that the code wasn't being executed just openning the cursors / transactions and then closing them.When I ran the Stored Procs individually the code runs perfectly but when it ran together with the DTS package it gives me the old chestnut "It works when I test it", well falls over anyway.I'm now residing to the fact that it should be broken down into a number of smaller groups of procedures.Thanks for your help.AliI maybe ugly but I'm good |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-17 : 08:48:48
|
| Anyhow you are breaking the code, you can post your cursor based solutions here there are lot of team members who will help you to turn them into set based.----------------------------------"True love stories don't have endings." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-18 : 13:15:01
|
quote:
When I ran the Stored Procs individually the code runs perfectly but when it ran together with the DTS package it gives me the old chestnut "It works when I test it", well falls over anyway.
Based on this little bit of information, it sounds like the error is in your DTS package. This is not unusual. You have to watch out for a few different issues with DTS packages such as file paths being dependent on your client station as opposed to relevant tot the server. If this is run in a job, then you may run into permissions issues where the account that SQL Agent logs in with does not have permissions to the file paths, but when you test it with your login, you do.I'd suggest creating a simple stored procedure that you can drop in place of all the complexity in order to test the method you're using to call the DTS and see if it works.--------------------------------There's a new General in town... |
|
|
|
|
Stored Proceedures not processing PLEASE PLEASE