Re: SQL 2000 Stored Procedure Problem

Richard,

Are you getting any error messages? And, if so, what do they say?

Things to think about: Are all objects in the dbo schema? On both servers?
Are there different rights granted on the two servers? (By temp tables, I do
understand you to mean #tables in tempdb.)

Since it works on one server not another, I would not expect execution
context to be the problem with temp tables.

Not much help, but any extra information you have could assist.
RLF

"Richard" <rich@xxxxxxxxxxxxxxxx> wrote in message
news:eiTtZUhnHHA.2296@xxxxxxxxxxxxxxxxxxxxxxx
Yesterday I spent hours searching the groups for a solution to this problem
with no luck.

The issue is that a VB6 app I inherited has been running fine for years on
SERVER1 (Server 2003 and SQL Server 2000). We moved the Database to a new
SERVER2 (Server 2003 and SQL Server 2000) . The app has a mix of pass
through SQL statements and I was in the process of converting them all to
Stored Procedures. Five of the 14 routines have been converted to Stored
Procedures. On SERVER1 all work fine.

On SERVER2 only the pass through routines work. Any that use Stored
Procedures failed. I then wrote a simple Stored Procedure that returned
just a text string - that worked - so it is not just a Stored Procedure
problem . But as soon as I added a temp table to that "simple" Stored
Procedure - it fails to return anything. All 5 of the production routines
that fail use Temp tables.

I have tried every combination of permissions settings I could find and
nothing seemed to work.

Anyone have any ideas here ?

Thanks!!


.

Relevant Pages

  • Re: Hot Swapping CPUs?
    ... Modern motherboards -- things made in the past 2-3 years at least, ... Nagios plug-in that interrogates the data (temp, fan, power, etc) from the ... IBM POWER4 servers do so through a service processor that actively snoops ... all CPU and memory transactions to determine when a CPU has died, ...
    (freebsd-current)
  • Re: how to surpress a server error msg?
    ... i'd rather not to change the system stored procedure since that would ... mean i have to change that particular sp on all servers! ... i was calling the sp thru SQL pass thru in Access and return the query ... >> following msg in query analyzer messages tab. ...
    (microsoft.public.sqlserver.server)
  • Insert SQL does not work in stored procedure.
    ... On one of our development servers I've run into a problem with Insert ... command. ... The stored procedure works on other servers and used to work on ... (uopLogicalTagId, uopTimeStart, uopTimeEnd, uopFltValue, uopStrValue) ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert SQL does not work in stored procedure.
    ... You're experiencing 'implicit transaction' mode. ... > On one of our development servers I've run into a problem with Insert ... > command. ... The stored procedure works on other servers and used to work on ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the minimum temperature for a server room
    ... > I'd like to know what room temperature is considered too warm/hot to ... > servers, and it is pretty warm in there, but I don't know at what point I ... Generally there's no safe vs non safe cutoff temp. ...
    (microsoft.public.windows.server.sbs)