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

Question

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

alex.angus · Answer

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.

pioneerpauls · Answer

Hi Alex ..Here below i have written a function that would get procedure nameParameters 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