OLE DB stored procedure problem

[sbw]

Hi.

I have an app which uses multiple databases and I would like to know if
there is a way to connect to different databases through OLEdb (or any
other connection string) which allows me to connect to a different
database.

My connection string connects to HelpDesk db.

For example
..ActiveConnection = xconn
..CommandType = adCmdStoredProc
..CommandText = "HR.dbo.proc_Get_Region"
..Parameters.Refresh
..Parameters("@iRegionID") = 0
..Parameters("@iUserID") = userid
set rsregion = .Execute

Using this syntax, I get the following error.
Procedure 'proc_Get_Region' expects parameter '@iRegionID', which was
not supplied.

Stored Procedure syntax is:
CREATE Procedure proc_Get_Region
(
@iRegionID int = 0,
@iUserID int
)
AS
BEGIN

SELECT iRegionID, tRegionDesc tName, tOpsMngr, iStaffID, tOpsMngr,
tFirstName OpsManFName, tSurname OpsManSName, tEmail OpsManEmail,
FROM HR.dbo.tblRegion R
INNER JOIN HR.dbo.tblStaff S ON S.tStaffID = R.tOpsMngr
WHERE iRegionID = @iRegionID
ORDER BY tName

-- I use the @iUserid variable elsewherr

END
GO

Let me give you some background. I want to do this so that I can
re-use my hr.dbo.<storedprocedures>. As you can understand, if I
create helpdesk.dbo.<storedprocedures>, I would have to create these
same sp's in every other db that connects to the HR db. However, if I
can re-use the hr.dbo.<storedprocedures> I only need to edit them in
one place and all others are updated also.

Any help would be greatly appreciated.

PS Permissions are fine. The same db login has permissions to both
databases.

 

[Bob Barrows [MVP]]

Hi.

I have an app which uses multiple databases and I would like to know
if there is a way to connect to different databases through OLEdb (or
any other connection string) which allows me to connect to a different
database.

The Connection object has a property called DefaultDatabase
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdprodefaultdatabase.asp)
which allows you to basically switch databases after the connection has been
opened.

My connection string connects to HelpDesk db.

For example
.ActiveConnection = xconn
.CommandType = adCmdStoredProc
.CommandText = "HR.dbo.proc_Get_Region"
.Parameters.Refresh

BAD BAD BAD
You are forcing a separate trip to the database to be made in order to
retrieve the parameter metadata. You should use CreateParameter to create
your parameters and append them to the Parameters collection. I wrote a free
tool to generate this code for you - it's available here:
http://www.thrasherwebdesign.com/downloads1/sp_parameters.zip. The
description is here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

.Parameters("@iRegionID") = 0
.Parameters("@iUserID") = userid
set rsregion = .Execute

Using this syntax, I get the following error.
Procedure 'proc_Get_Region' expects parameter '@iRegionID', which was
not supplied.

Stored Procedure syntax is:
CREATE Procedure proc_Get_Region
(
@iRegionID int = 0,
@iUserID int
)

Hmm - no output parameters, and you don't seem to need to read the Return
parameter ... you don't need to use an explicit Command object. You can do
this:

xconn.DefaultDatabase = "HR"
set rsregion=createobject("adodb.recordset")
xconn.proc_Get_Region 0, userid, rsregion
if not rsregion.eof then
'process the recordset
else
'handle the empty-recordset situation
end if

Don't forget to close and destroy your recordset and connection objects ...

HTH,
Bob Barrows

 

[sbw]

Brilliant. Thanks very much for the info.

I will make sure my stored procs are fashioned according to your
suggestion in future.