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 proc
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?

Albert

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 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 confused to where to approach?  If you can provide some guidance
that would be very greatful..

Albert

Show quoteHide quote

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 13:25:04 -0700, Albert Chan wrote:

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?


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

On Fri, 12 Aug 2005 08:19:13 -0700, "Albert Chan" <Albert
C***@discussions.microsoft.com> wrote:

Show quoteHide quoteHi 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

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)