stanl
04-15-2008, 04:54 AM
I have a situation with 2 db's on an SQL Server [2005] - assume db1 and db2
I have a stored procedure that is basically a SELECT query that joins data from the two dbs. In the stored proc, I use references such as [the stored proc resides on db2]
SELECT db1..[table].[field], db2..[table].[field]
and the stored proc can be called from vba by setting up a connection string to db2 [oConn] and
oConn.Execute("exec [mystoredproc]")
However, the connection string has a userid that has EXECUTE privileges on the server.
Other users, connect with Windows authentication and cannot run the stored proc. As a Plan-B, I allow them to use the same SELECT syntax as in the stored proc, only they execute it as an SQL statement [cSQL]
oConn.Execute(cSQL)
This, however, returns an empty Recordset whereas if the same SQL is run as the stored proc n-ROWS are returned.
???? I'm lost:banghead: :banghead:
Stan
I have a stored procedure that is basically a SELECT query that joins data from the two dbs. In the stored proc, I use references such as [the stored proc resides on db2]
SELECT db1..[table].[field], db2..[table].[field]
and the stored proc can be called from vba by setting up a connection string to db2 [oConn] and
oConn.Execute("exec [mystoredproc]")
However, the connection string has a userid that has EXECUTE privileges on the server.
Other users, connect with Windows authentication and cannot run the stored proc. As a Plan-B, I allow them to use the same SELECT syntax as in the stored proc, only they execute it as an SQL statement [cSQL]
oConn.Execute(cSQL)
This, however, returns an empty Recordset whereas if the same SQL is run as the stored proc n-ROWS are returned.
???? I'm lost:banghead: :banghead:
Stan