Re: SQL 2000 Stored Procedure Problem

Thanks Russell

No error messages. In VB6 / ADO - the recordset is just empty. Failed to
mention that all work fine in QA and on my dev system, that's why I suspect
permissions. BUT who, which and where ?? I swear - I tried so many things
yesterday. GRANTs, permissions on SProcs, permissions on DB, permissions
on tables - nothing helped. The VB/ADO code that is SQL Pass Through (works
everywhere) uses the same UserID/password to login to the SQL as the SProc
routines.

All objects are in dbo. Both servers are "supposed" to be near identical -
the new one is a Quad CPU with bigger HDs. I'm a programmer not an IT
expert by any means. These permissions issues drive me up a wall. I
insisted the migration go one thing at a time to minimize problems - good
thing!

I zeroed it down to the Temp tables with a simple test of 2 SProcs:

--- SProc 1
CREATE PROCEDURE spTestSimple AS
SELECT 'Test Simple' AS ReturnValue
------- This works everywhere 100%

--- 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!













"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:O43TXcinHHA.1244@xxxxxxxxxxxxxxxxxxxxxxx
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: Error "The system cannot find the file specified" on files
    ... I will see if my customer can do this for me as I cannot easily get physical ... both of these servers are domain controllers. ... command line utilities and GUI tools, tried to set permissions. ... Security tab in Windows Explorer doesn't appear. ...
    (microsoft.public.windows.file_system)
  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: Cant Mount Public Store
    ... Edit to the Exchange Domain Servers group to the servers that own the public ... This sounds like a permissions problem. ...
    (microsoft.public.exchange.setup)
  • Cant Mount the Public Store
    ... Edit to the Exchange Domain Servers group to the servers that own the public ... This sounds like a permissions problem. ...
    (microsoft.public.exchange.admin)
  • Re: public role question
    ... I didn't understand what Dan was asking me to do. ... Then I am able to create a stored procedure. ... system tables with select permissions. ... You ARE the special DBO user, and not the new user, which is why you have ...
    (microsoft.public.sqlserver.security)
Quantcast