SQL 200 VS SQL 7 Stored procedures and Access RT

[Don Fowler]

I have an Access app that uses SQL 7. When I changed the
ade to connect to SQL 2000, the stored procedures don't
work. When I run a trace on SQL, the stored procedures
execute with "select * from "spxxx"". With the runtime I
have developed ( in Access 2000) the sp's for SQL 7 run
with "Exec "spxxx"". Why the difference?

 

[BJ Freeman]

I usually get this when the login does not have permission to execute the
Stored procedure.

 

[Don Fowler]

I have the same permissions on the tables, sp's etc. It
is a restored backup of the original. I also have tried
installing Access from Office XP. When the client uses
that, it also works. I get a "Exec "spxxx"" in the
trace. It seems to be the version of the run time. All
the clients do not have Office pro, so I need to use a
runtime on them. Any help?

 

[Alick [MSFT]]

Hi Don,

Would you please post the following information?

1. What is the stored procedure? If you run a simple another stored
procedure, does it work?

2. I assume the problem is:

a. Access 2000 adp client (run time or full version installed on the
clients) connects to SQL 7.0, it works fine.

b. Change Access 2000 adp/ade to connect SQL 2000, no other things changed,
it doesn't work. What happens? Do you receive any error message?

3. When you run a trace on SQL Server side, you find the stored procedures
execute with "select * from "spxxx"" when adp connects to SQL 2000 (failed
one); However, when adp connects to SQL 7.0, it executes with "Exec
"spxxx"" (successful one). Right?

I look forward to hearing from you.


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------
| Content-Class: urn:content-classes:message
| From: "Don Fowler" <[email protected]>
|
| I have the same permissions on the tables, sp's etc. It
| is a restored backup of the original. I also have tried
| installing Access from Office XP. When the client uses
| that, it also works. I get a "Exec "spxxx"" in the
| trace. It seems to be the version of the run time. All
| the clients do not have Office pro, so I need to use a
| runtime on them. Any help?
| >-----Original Message-----
| >I usually get this when the login does not have
| permission to execute the
| >Stored procedure.
| >
| >| >> I have an Access app that uses SQL 7. When I changed
| the
| >> ade to connect to SQL 2000, the stored procedures don't
| >> work. When I run a trace on SQL, the stored procedures
| >> execute with "select * from "spxxx"". With the
| runtime I
| >> have developed ( in Access 2000) the sp's for SQL 7 run
| >> with "Exec "spxxx"". Why the difference?
| >
| >
| >.
| >
|

 

[BJ Freeman]

Have you checked the permissions since you restored from the back up?


the DB was on an SQL 7.0 with permissions.
you then used a back of the SQL 7.0 to install on SQL 2K
now the permissions don't work.

the key here the ID's, not the text for the login on the SQL 7.0 are not
the same for SQL 2k
so you may have the same login (Text) with Different Login ID, on the SQL
2k, but they will not match the logins ID from the Backup from SQL 7.0
so you may have to go in manually and reset all the permission on the DB.