Re: SQL 2000 Stored Procedure Problem

Thanks again Russell and Erland,

99% of the time I can search google groups for any issue I have and rarely
do I need to actually post a question. There is usually and answer to
everything I have needed. I've got over 10 years with VB and SQL Server -
and this is the most baffling problem.

I'm ABSOLUTELY sure it's not in Master - I have a test database that I use
and as I indicated, I can copy the exact call the VB app makes into QA and
it works. I always use full path commands to ensure the right DB:

The VB code is identical in both cases except for the spTestTempTable vs.
spTestSimple :
Exec [999.23.17.99].[TestDatabase].[dbo].spTestTempTable

Exec [999.23.17.99].[TestDatabase].[dbo].spTestSimple

Also - this app has been running about 4 years on the old server - it still
does - we have 5 different companies using it and 3 are still on the old
server. I am tasked with converting the VB6 apps to .NET - and the first
task was to get rid of the SQL pass through code into Stored Procs. I
converted the 5 most intense ones over the past year - no problem. Code got
smaller and reports got faster!

At first I suspected Stored Procedures in general - but I wrote the 2 simple
ones (see prev post) to prove that assumption wrong - the SProc that just
returns a string (spTestSimple ) works 100% - but as soon as I added the
#MyTempTable (spTestTempTable) it fails to return anything to the VB app -
but ONLY on the New server.

I backed up the TestDatabase on the New Server - copied to my dev PC -
restored the DB in my SQL Server - since I had created spTestTempTable on
the Live New Server I verified that this SProc was indeed now in
TestDatabase on my dev PC - It was - ran the VB app - Works!

Conclusion : Something on the New Server prevents a successful query from
VB6/ADO to a SQL Stored Procedure when that Stored Procedure uses a Temp
Table.

Again thanks guys for your input.
Richard



"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:%23TIrRCtnHHA.4032@xxxxxxxxxxxxxxxxxxxxxxx
Richard,

I am certainly confused. There should be no rights issues that prevent
you from using a #temp table.

Could you check to make sure that you do not have a copy of your stored
procedure on master? (I know that it is unlikely, but the behavior sounds
like you are not executing the code that you think you should be
executing.)

RLF

"Richard" <rich@xxxxxxxxxxxxxxxx> wrote in message
news:uCJSX0lnHHA.3512@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Erland,

sp_configure 'user options' reports the same on both :
minimum=0, Maximum=32767, config_value=0, run_value=0

I added SET NOCOUNT ON as the first line in spTestTempTable - same
results. I had tried that yesterday while searching the groups for ideas
for 5 hours.

Any other suggestions are welcome.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns993B19CCA96CYazorman@xxxxxxxxxxxx
Richard (rich@xxxxxxxxxxxxxxxx) writes:
--- SProc 2
CREATE PROCEDURE spTestTempTable AS
CREATE TABLE #MyTempTable
(
ReturnValue varchar(100)
)
INSERT #MyTempTable
SELECT 'Test Temp Table'

SELECT ReturnValue FROM #MyTempTable
----- This works everywhere EXCEPT on the New Server from the VB/ADO
app.

Any ideas? My last resort is to undo all the SProcs back to the slow
inefficient Pass Through method. Yuk!

Do you loop over .NextRecordset? Or do you set SET NOCOUNT ON
when you connect? If you do neither, I think we are on to something.
Try adding SET NOCOUNT ON in the beginning of the procedure.

As for why it works on one server, but not another, what does

sp_configure 'user options'

report on the two machines?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx






.

Relevant Pages

  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)
  • Re: Adding date to parameter
    ... SqlClient provider which is specifically written for SQL Server. ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... I made the assumption that the stored procedure went by the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... table, if another inserting occurs at the nearly exactly the same moment, ... SQL Server will pass it a default value. ... assume that you do have a connection that can reach SQL Server Express. ... unless your stored procedure has some thing that only works ...
    (microsoft.public.dotnet.framework.aspnet)
  • Stored procedure does not complete until result set is retrieved from ODBC
    ... I have a SQL Server Stored procedure that I am executing via ODBC. ... -- Start Code without cursor ...
    (microsoft.public.sqlserver.odbc)