Calling of Store Procs on SQL Server from MS Access

Hi everyone,
This is my first cut-through in developing store procs on SQL server, i've
got them created and put in the master db on SQL server. on the Access front
end I have code that passes in an update statement which calls the store pro
c
on SQL server, i've checked the syntax and there are no errors. I've
checked the SQL statement being passed in to the store proc and there are no
errors too, using SQL query analyzer.
When I try to run the code, it does not update the records I want on SQL
server, I'm thinking there's something wrong with the connectionstring? Can
anybody give me some insight or alternative to get around this problem?
AlbertAlbert
I don't know that you want to put stored procedures in the master database.
You should build an application database.
If you are using ADPs in Access, does the interactive Test Connection button
work? If so it is not your connection string. Even if you are using
MDBs, you should be able to test your connection interactively to check the
string (say, if you are using an ODBC object).
Other than that we would need to see your code (including the DDL) to
understand what the problem might be.
Joe|||I still can't pin point the problem, but here is some more information to
provide you on my problem.
On the SQL server enterprise, i've created an store proc the following in
the master db:
CREATE PROCEDURE SP_SQLSTRING
@.sqlstring varchar(50)
AS
DECLARE @.InsertString NVARCHAR(500)
if exists(select * from sysobjects where name = @.sqlstring)
Set @.InsertString = @.sqlstring
execute sp_executesql @.InsertString
GO
Now in the front end access, the code i have supplies an query as an
parameter...
eg strSQL = "Update..."
then i make a call to the function that is suppose to call the store proc:
SP_SQLSTR (strSQL)
Within the function SP_SQLSTR it has the following partial code:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=xxxxx;Initial Catalog=" xxxxxxxxx ";Data Source=xxxxxx"
cn.Open
Dim rstSQLStr As ADODB.Command
Set rstSQLStr = New ADODB.Command
rstSQLStr.ActiveConnection = cn
rstSQLStr.CommandText = "SP_SQLSTRING" 'name of the store proc on SQL
server
rstSQLStr.CommandType = adCmdStoredProc
SP = xSQLstr
DoCmd.SetWarnings False
'This is where I execute it, but i don't get any errors, and it does
nothing to the database.
rstSQLStr.Execute , Array(SP)
I don't know where i went wrong, and there are no errors from the system.
So i'm a bit to where to approach? If you can provide some guidanc
e
that would be very greatful..
Albert
"J. M. De Moor" wrote:

> Albert
> I don't know that you want to put stored procedures in the master database
.
> You should build an application database.
> If you are using ADPs in Access, does the interactive Test Connection butt
on
> work? If so it is not your connection string. Even if you are using
> MDBs, you should be able to test your connection interactively to check th
e
> string (say, if you are using an ODBC object).
> Other than that we would need to see your code (including the DDL) to
> understand what the problem might be.
> Joe
>
>|||I urge you to take a good look at the Books Online, but not before you get a
good book on the basics of SQL.
ML|||On Fri, 12 Aug 2005 08:19:13 -0700, "Albert Chan" <Albert
Chan@.discussions.microsoft.com> wrote:

>Hi everyone,
>This is my first cut-through in developing store procs on SQL server, i've
>got them created and put in the master db on SQL server. on the Access fron
t
>end I have code that passes in an update statement which calls the store pr
oc
>on SQL server, i've checked the syntax and there are no errors. I've
>checked the SQL statement being passed in to the store proc and there are n
o
>errors too, using SQL query analyzer.
>When I try to run the code, it does not update the records I want on SQL
>server, I'm thinking there's something wrong with the connectionstring? Ca
n
>anybody give me some insight or alternative to get around this problem?
>Albert
Hi Albert,
1. Create the procedure in the application database, not in the master
database.
2. Test the procedure first by calling it from Query Analyzer. That must
works without errors before you even attempt to call the procedure from
Access (or any other client).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 13:25:04 -0700, Albert Chan wrote:

>CREATE PROCEDURE SP_SQLSTRING
>@.sqlstring varchar(50)
>AS
>DECLARE @.InsertString NVARCHAR(500)
>if exists(select * from sysobjects where name = @.sqlstring)
>Set @.InsertString = @.sqlstring
> execute sp_executesql @.InsertString
>GO
Hi Albert,
First of all, I fail to see the point of this procedure. If the
procedure does nothing but dynamically executing the string passed to
it, why not simply execute the SQL from your front end? Why use a stored
procedure at all?

>Now in the front end access, the code i have supplies an query as an
>parameter...
>eg strSQL = "Update..."
That means that the first statement will test for the existance of a
system object in the master database with the name "Update...". I guess
(and hope!) that you have no tables, triggers, or procedures with that
name, so the next statement is not executed.
The last statement will be executed (the conditional execution is only
for the first statement after an IF, unless you use a BEGIN / END block)
but since @.InsertString is still NULL, it won't do anything.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql