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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-06-13 : 10:53:07
|
| Ryan writes "I have a stored procedure that executes in SQL Query Analyzer and other environments, returning records without raising a single error, but it fails when called from ASP (the recordset is closed). Any idea why this is occuring? I solved my problem by just re-writing the SP, but I still can't figure out what the issue was." |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-13 : 12:46:12
|
| Probably permissions on the ASP user as opposed to the QA user.Hard to tell, could be how you called the procedure from ASP.Post some of the calling code and / or SP next time you have a problem.Sam |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-13 : 12:47:54
|
| You could have ran SQL Profiler to determine what exactly was occurring, such as what parameters it was passing, what errors it was getting, etc...Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-13 : 20:30:30
|
Actually, I think you are both off base  If the stored proc does any inserting or updating it sends back the "n Records Affected" message and ADO thinks that is a recordset. Your data is returned in another recordset and you need to move to the next recordset in ADO to access it.Or the better way, is include SET NOCOUNT ON inside your procedure, then the messages won't get returned.Damian |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-13 : 22:54:34
|
Damian, Damian, Damian....  If SET NOCOUNT ON was the problem then rewriting the stored procedure wouldn't fix Ryan's problem.Ryan - (wonder if he'll be back) - if you would post your SP we could settle this.Sam |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-14 : 00:18:43
|
| Unless he rewrote it with the NOCOUNT statement Damian |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-15 : 00:48:07
|
| Another shot in the dark: if the SP is being called with a command object, be sure that the parameters specified are correct, in the correct order, the correct size, etc. And adParamReturnValues need to be specified first.That's my contribution to the speculation. Though really, permissions are what get me the most.Cheers-b |
|
|
|
|
|
|
|
|
SP works in Query Analyzer but not in ASP