| Author |
Topic |
|
dundealing
Starting Member
12 Posts |
 Posted - 2007-02-19 : 15:49:55
|
| Hi,I have a stored procedure written by a co-worker which I am trying to access through a VB6 component. When I call it, the program hangs for about 3 minutes, then throws a timeout error.The SP simply takes in the parameters I supply and builds a command string which is then executed (after a few checks to tests for nulls, etc). This is the problem:If I run the stored procedure through SQL Query Analyzer, it returns the row I am looking for;If I extract the command string and run it through a test harness (as the CommandText, with the CommandType set to adCmdText) it gives me the result I am looking for;When I run the stored procedure through the program (calling the actual SP), it hangs and fails.I have stepped into the debugger and the parameters for the failing code are correct (the same as are being passed by the test harness). I know I am pointing at the correct server (many tests done to confirm this), yet I cannot see how it can give results through one method call but not through another.I will investigate the way the command object is being built, but if anyone has any other ideas I would be most grateful to hear them.Regards,Dundealing.PS. I apologise if this isn't the correct forum for this issue, but I think it is definately an SQL problem. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-19 : 16:11:51
|
| You should be setting the CommandType to adCmdStoredProc if your command text is the name of the stored procedure. |
 |
|
|
dundealing
Starting Member
12 Posts |
Posted - 2007-02-20 : 03:01:42
|
| When I call the stored procedure, I do indeed use adCmdStoredProc. I only use adCmdText when I send down the string which the SP creates. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-20 : 03:09:47
|
| Extend the command timeout property to 5 minutes (300 seconds)?Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-20 : 03:11:42
|
| How much time does the SP takes when run through QA? or through Command object with Command text?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
dundealing
Starting Member
12 Posts |
Posted - 2007-02-20 : 11:47:03
|
| I have narrowed it down now. When I run my VB component with transactions set to 1 (No Transactions) it returns the values I want. however, these components NEED transactions for other methods, and so turning transactions off is not an option.The message I am getting is: the operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.Microsoft have a workaround for this: (http://support.microsoft.com/kb/839279) but this hasn't helped at all. Any other ideas would be much appreciated. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-02-20 : 23:11:00
|
Step 1. Smack the co-worker  Step 2. Modify the proc to contain SET XACT_ABORT ON (yeah, I know... listed in that work around... just wanna make sure you actually did that.)--Jeff Moden |
|
|
|
dundealing
Starting Member
12 Posts |
Posted - 2007-02-22 : 07:18:32
|
| Despite my efforts, it still doesn't want to work.One problem might be that we are using an old Windows 2000 OS for one SQL server and Windows 2003 for the other. I can configure the 2003 server the way it should be, but I don't have the same options on the 2000 box.Has anyone come across anything similar regarding distrubuted transactions in their time? |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-23 : 02:41:47
|
quote:
Originally posted by dundealing
Despite my efforts, it still doesn't want to work.One problem might be that we are using an old Windows 2000 OS for one SQL server and Windows 2003 for the other. I can configure the 2003 server the way it should be, but I don't have the same options on the 2000 box.Has anyone come across anything similar regarding distrubuted transactions in their time?
if platform compatibily issue is there, the try SP (service packs for win 2k - sp4, and then tryMahesh |
|
|
|
|
Stored procedure doesn't work properly