using a stored procedure to connect to a different database on the same server

alex.angus

Hello all, here is what i am trying to do. I am developing a side program to work along side a main program which i have no control over. I can not edit or create stored procedures on the database i am trying to query. It would cause the program that uses that database to not function :P. Anyway i have created my own database that will contain its own data but it also requires the data from this other database. I need to create a stored procedure to access a separate database. The code to do so would be the best for me. Also would i just be better off using a perameterized query with ado.net? thnx

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

alex.angus21y ago
I thank you for this code, I may need to use this in the future. however this may be the wrong forum for this. I need code for an SQL server 2000 Stored Procedure. So that would be sql code. I am going to be calling this stored procedure from a vb.net app. but the stored procedure on database 2 has to access data on database 1. I need the sql to make the stored procedure access a database that is not its own. I heard it is possible. Now maybe the code provided by satishpaul can be used to accomplish this, if so i ask that how it works could be explained.
0
pioneerpauls21y ago
Hi Alex ..Here below i have written a function that would get procedure name,Parameters list and their values as input.... ******************************** Code Example:- Public Shared Function ExecInsProc(ByVal procName As String, ByVal ArrySPParam As ArrayList, ByVal ArrySPValue As ArrayList) As Double Dim sqlCon As SqlConnection = "Data Source=ServerName;Initial Catalog=DB Nmae;User ID=xx;Password=xx" '''[Give ur own db name password & etc...] Dim id As Double sqlCon.Open() Dim sqlCmd As SqlCommand = New SqlCommand(procName, sqlCon) sqlCmd.CommandType = CommandType.StoredProcedure Dim i As Integer = 0 While i < ArrySPParam.Count If ArrySPValue(i).ToString().Trim() = "" Then sqlCmd.Parameters.Add(ArrySPParam(i), DBNull.Value) Else sqlCmd.Parameters.Add(ArrySPParam(i), ArrySPValue(i)) End If i += 1 End While id = sqlCmd.ExecuteNonQuery() sqlCon.Close() Return id End Function ********************************************** Code to call the function Dim SPParam As ArrayList = New ArrayList'''[intialising an array list] Dim SPValue As ArrayList = New ArrayList SPParam.Clear() SPValue.Clear() SPParam.Add("@Parameter Name")'''[Giving values to the procedures Parameters] SPValue.Add(Parameter Value)'''[Note '@' Sign is important] ExecInsProc("Procedure Name", SPParam, SPValue) '''[Calling the function] ************************** Hope this code may help u...Take care all d best wit prayers Satish
0