Different results from stored procedure: Query Analyzer vs. ADO
Posted on 2004-03-29
I have a stored procedure that is creating a temporary table then providing a select query for output based off of the temporary table. A weird thing happened in Access when I tried to call the SP by a pass-through query: the procedure took a long time, then returned blank rows and zeroes where there should have been data.
I then opened the Query Analyzer and ran the SP manually. It returned the information as it was intended to do. Funny. I went back to Access and now the pass-through query was working normally. Arrgh! All that day I could not get the SP to break.
Next morning I tried to run the pass-through query in Access, and it puked on my shoes yet again. I decided not to "fix" it by running the SP in the Query Analyzer, but to instead experiment. I ran the same pass-through on 3 different machines including my computer with no success. I then blamed Access, my default whipping-boy program. Onward to VB and ADO!
Surely pulling in the SP through ADO and creating a simple CSV file (for use in Excel) would be the simplest thing to do, right? Same problem!!! Yet everytime I run it from the Query Analyzer, it works and fixes the pass-through and ADO connections.
I've rebooted the server, which is MS SQL Server 2000 running on Windows 2000 Server SP3. Thanks in advance!