By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

OLE DB stored procedure problem

P: n/a
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.

Jul 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
sb*@sarinarusso.com.au wrote:
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...ltdatabase.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/dow...parameters.zip. The
description is here:
http://www.thrasherwebdesign.com/ind...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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2

P: n/a
sbw
Brilliant. Thanks very much for the info.

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

Jul 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.