Failure to call stored procedure from SQL Server

[pthalacker]

I am converting a bunch of VFP code to SQL Server stored procedures so they can be called from Asp.Net. I can call the stored procedure in the Enterprise Manager and get the appropriate result set. I have a valid connection handle to the database containing the stored procedure, but when run the following code:

lcSql = [Execute GetAcctBalance '20030509AG', '12/31/2006']
lnResult = SQLExec(oSpi.hConn, m.lcSql, 'csrBalance')

I get an ODBC error: Could not find stored procedure 'GetAcctBalance'

Can you assist me in troubleshooting this?

pamela

 

[bborissov]

Are you sure your connection is open the right DataBase? What happens if you call SP that way:

lcSql = [Execute DataBaseNameHere.dbo.GetAcctBalance '20030509AG', '12/31/2006']
lnResult = SQLExec(oSpi.hConn, m.lcSql, 'csrBalance')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pthalacker]

Are you sure your connection is open the right DataBase?

Yes. I am using the same connection string that I have been using to successfully connect to 4 other databases on the same server. Just change the database name.

This works:

lcSQL = [Select * from Sysobjects]

This gets me the same error:

lcSql = [Execute DataBaseNameHere.dbo.GetAcctBalance '20030509AG', '12/31/2006']

pamela

 

[bborissov]

Then you are created that SP in some other DataBase. Try:

lcSql = [Execute master.dbo.GetAcctBalance '20030509AG', '12/31/2006']

Run this in QA:

sp_MSForEachDB 'SELECT ''?'' AS DBase WHERE object_id(''?..GetAcctBalance'') IS NOT NULL'

and you will get the list of the DataBases where that SP is defined

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pthalacker]

Actually it wasn't the wrong database. It was the wrong server.

pamela

 

[bborissov]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP