Executing a stored procedure from Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Executing a stored procedure from Access

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2011-04-13 : 04:06:19

Hi

(I'm not sure the subject belongs here, or I have to post in MS Office newsgroup)

I have a database on SQL Server 2005 (p.e. MyDatabase), with Access front-end (mdb). Database tables are linked to front-end using ODBC connection p.e. MyConnection (ODBC driver is SQL Native Client, Integrated Windows Authentication is used).

Database permissions are given through according Active Directory Security Group, which is also defined as login (DomainName\SecurityGroupName) in SQL database.

I tried a procedure like this:
---
Private Sub cbTestSP_Click()
On Error GoTo Err_cbTestSP_Click
Dim strConnect As String
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
strConnect = "ODBC;DSN=MyConnection;DATABASE=MyDatabase;"

Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect
strSQL = "exec dbo.RefreshData"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 200
qdf.ODBCTimeout = 150
qdf.Execute
DoCmd.Hourglass False
Exit_cbTestSP_Click:
Exit Sub
Err_cbTestSP_Click:
MsgBox Err.Description
Resume Exit_cbTestSP_Click
End Sub
---

, but I get an error 'ODBC--call failed'. I suspect the problem is with connection string - I never found a good source which explains what you need and for what there.

Any good advice is appreciated!
Thanks in advance!

Arvi Laanemets

ArviL
Starting Member

25 Posts

Posted - 2011-04-14 : 09:36:31

I have now ALMOST working solution.
---
Private Sub cbTestSP_Click()
On Error GoTo Err_cbTestSP_Click

Dim strConnect As String
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef

Dim ConnStr As String
Dim DsnStr As String
Dim FoundPos As Integer
Dim Continue As Boolean

Set dbs = CurrentDb

Continue = True
' A table where application defaults are stored, is used to identify current datasource, used for linking database tables
ConnStr = Left(dbs.TableDefs.Item("dbo_appdata").Connect, 250)
Do While Continue
Continue = (Left(Replace(UCase(ConnStr), " ", ""), 4) <> "DSN=")
FoundPos = InStr(ConnStr, ";")
If Continue Then
ConnStr = Mid(ConnStr, Nz(FoundPos, 0) + 1, 250)
Else
DsnStr = Left(ConnStr, FoundPos)
End If
Loop

strConnect = "ODBC;" & _
DsnStr & _
"Trusted_Connection=Yes;" & _
"DATABASE=MyDatabase"

Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect
strSQL = "exec dbo.StoredPreocedure_Test"
qdf.ReturnsRecords = False
qdf.SQL = strSQL
dbs.QueryTimeout = 400
qdf.ODBCTimeout = 200
qdf.Execute
DoCmd.Hourglass False

Exit_cbTestSP_Click:
Exit Sub

Err_cbTestSP_Click:
MsgBox Err.Description
Resume Exit_cbTestSP_Click

End Sub
---

It works fine, so long as dbo.StoredProcedure_Test doesn't contain any queries to other databases. But whenever the stored procedure queries other database (Like: INSERT INTO dbo.MyTable SELECT * FROM [OtherDB].dbo.MyTable; where OtherDB is another database on same server, and the current user is the member of Activ Directory Security Group, which has login for all databases on this server), an error 'ODBC--call failed' is returned again.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-14 : 15:38:38

On SQL Server, when you have a procedure that accesses multiple databases - the user must be a user in each database with access to the tables (generally, select access is only needed).

There are a couple of ways around this requirement. The best option is probably:

Sign the procedure with a credential that has access. This is a bit involved, but once set up - that procedure has all the permissions it needs and the user calling the procedure only needs to be able to execute the procedure.

Jeff

ArviL
Starting Member

25 Posts

Posted - 2011-04-16 : 11:35:51

quote:
Originally posted by jeffw8713

On SQL Server, when you have a procedure that accesses multiple databases - the user must be a user in each database with access to the tables (generally, select access is only needed).

But the user I tested the procedure with HAS access to other database too!

Can it be something like:
When p.e. ThisUser executes a simple SP in 1st DB, then ThisUser is identified as member of p.e. ServerAccessAllowed security group, and the access is allowed;
When ThisUser executes a simple SP in 2nd DB, then ThisUser is also identified as member of ServerAccessAllowed security group, and the access is allowed;
When ThisUser executes a SP in 1st DB, and this calls a SP in 2nd DB, or queries data from 2nd DB, then for 2nd DB the membership of ServerAccessAllowed security group isn't tested, and for ThisUser the access is restricted - because there is'nt any login for ThisUser in 2nd DB (and in 1st DB either).

ArviL
Starting Member

25 Posts

Posted - 2011-04-18 : 03:06:00

I was wrong all along! The reason the procedure returns an error isn't the query to another database at all! It is TRUNCATE TABLE command!

The procedure creates replicas of some tables from another database. Some of them are exact replicas, some are modified. To minimize the time the data is written into destination tables, I use following logic:
1) The data from source table(s) in another database are read into table variable(s) (like INSERT INTO @TargetTable SELECT * FROM [OtherDB].dbo.SourceTable);
2) All nessecary modifications (p.e. calculating new fields, or updating values previously changed in current database) are made and results are written into table variable(s);
3) The target table in current database is truncated (like TRUNCATE TABLE dbo.TargetTable);
4) New data is inserted into target table from table variable(s) (like INSERT INTO dbo.TargetTable SELECT * FROM @TargetTable).

(The time needed to truncate all data from table and insert full new set of data is much shorter, that the amount of time needed to update all existing data + add new data + delete extinct data, And there aren't any indexes or relations defined in current database - they are all imported from another one.)

So when the target table is truncated, the procedure executed from Access front-end returns an error. And I can't figure out, how to allow this operation - I tried to give DELETE rights at database level to according security group, but this didn't help.

ArviL
Starting Member

25 Posts

Posted - 2011-04-18 : 05:30:24

From [url]http://www.experts-exchange.com/Database/Miscellaneous/Q_26730034.html[/url] :

"dqmq:
Truncate table rights are granted by default to db_owner, db_ddladmin, and sysadmin and cannot be granted to other users or roles..."

So in DatabaseProperties.Permissions I set Control = Grant for user group, and now the procedure works. Of-course it's not the best practice to grant ownership to every user, but as there will be only 2-3 users which will work with this application, I can probably cope with this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources