Can I Call A Procedure In One Database From A Procedure In Another?
Oct 5, 2007
Hi,
I have an integration solution that backs into two databases. Database A is custom and Database B is integrated from a COTS product. The manufacturer of B supplies a formal interface as stored procedures (and I intend to honor that contract by limiting my access to B through those stored procs).
Now, I have a search that is going to involve a subselect in the form of the following pseudocode:
select a,b from dbA.user.TableX where exists (select a from dbB.user.TableY)
I am trying to avoid creating 2 temporary cursors in the integration application and doing things in-memory (b/c of performance concerns).
So, my q is:
How can I make this happen within database A?
View 3 Replies
ADVERTISEMENT
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Apr 30, 2008
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ostat_CDS_rpt47_2006]
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 23/04/2008 11:26:14 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 22/04/2008 14:35:33 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 21/04/2008 11:46:08 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 16/04/2008 17:18:04 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 04/04/2008 12:44:34 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 12:03:31 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 11:11:07 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 09/03/2008 14:31:30 ******/
-- ostat_CDS_rpt47_2006 2008,03,17,17
CREATE procedure ostat_CDS_rpt47_2006
@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int
as
begin
declare @sql varchar(4000)
declare @tb1 varchar(128)
declare @tb2 varchar(128)
set @tb1= 'margin..ncds0421'
set @tb2='Report.dbo.ob_apr08'
set @sql=
'
insert into '+@tb2+'
select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback,
cast(callcost as decimal (10,5)) cost,count(*) attempt,
sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime
from '+@tb1+'
where yyyy='+ltrim(str(@pYear))+'
and mm='+ltrim(Str(@pMonth)) +'
and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+'
and talktime >0 and trunkin is null
group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
'
--print (@sql)
exec (@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 2 Replies
View Related
Aug 1, 2007
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
View 5 Replies
View Related
Aug 29, 2007
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
View 5 Replies
View Related
Apr 26, 2008
dear all
i am using VS 2005 and sqlserver 2005....
i created one procedure [dbo].[Rptdailyactivity_jul](@cond Varchar(8000), @Type varchar(100),@groupname4fsumm varchar(100) ,@Fromdate4fsumm datetime, @todate4fsumm datetime)
i want to call this procedure from VS how???
i tried based on the following code...
usig the following code i can able to manipulate tables...but how i can able to call procedure based on this code??and how i will pass parameters???
************
Dim Conomandb As New System.Data.SqlClient.SqlConnection("Data Source=HJIOertsv;Initial Catalog=tr;Integrated Security=True") If Conomandb.State = 1 Then Conomandb.Close() Conomandb.Open()
Dim Comomandb1 As New System.Data.SqlClient.SqlCommand(strSQL, Conomandb) Dim rsOman1 As System.Data.SqlClient.SqlDataReader = Comomandb1.ExecuteReader()
If rsOman1.Read() Then fghghfghf
End If Comomandb1.Cancel() Comomandb1.Dispose() rsOman1.Close() ''*********************
View 4 Replies
View Related
Sep 22, 2004
I create a store procedure in sql, how do I call it?
thankss
View 2 Replies
View Related
Mar 31, 2006
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
View 2 Replies
View Related
Aug 7, 2001
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
View 2 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies
View Related
Jul 25, 2002
I have a SQL2000(sp2) database (ServerA) and a SQL7(sp2) database(ServerB)
From the SQL2000 database I want to call a remote stored procedure on ServerB and store the result set in a table on ServerB.
E,g
insert TableA
exec ServerB...sp_GetStuff
If I try this by making serverB a remote server,
I get the following error message
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
If I make ServerB a Linked server, I get the error
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server '' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Now the MSDTC on ServerB won't start - It returns error 3221229574 to which the solution seems to be to reinstall MSDTC which looks like a very messy job with registry hacks and also the threat of reformatting the hard drive So I don't want to do this if possible
I really don't want a distibuted transaction anyway so I tried to stop the transaction being promoted using
SET REMOTE_PROC_TRANSACTIONS OFF
But this has no effect
Any ideas would be appreciated"
View 1 Replies
View Related
Nov 9, 2005
Greetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
View 1 Replies
View Related
Aug 1, 2006
Hi,
I have two SQL SERVER server1 and server2. A server1's stored procedure should call server2's stored procedure. how can i do this.please explain me in detail.i am new to this concept.
VENKAT
View 4 Replies
View Related
Jul 4, 2006
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
View 5 Replies
View Related
Dec 11, 2007
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
View 3 Replies
View Related
Mar 12, 2008
Hi, how to call the shrink_file proc from shrink_file_caller proc as below??
CREATE PROCEDURE [dbo].[Shrink_File]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
...
GO
CREATE PROCEDURE [dbo].[Shrink_File_Caller]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(4000);
SET @nsql = '' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;
EXEC sp_executesql @nsql,N'@err INT OUTPUT', @err = @err OUTPUT;
GO
declare @err int;
EXEC Shrink_File_CALLER 'DATABASE1', @err
select @err as err;
I get the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATABASE1'.
(1 row(s) affected)
View 9 Replies
View Related
Oct 6, 2007
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
View 4 Replies
View Related
Apr 7, 2008
Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you
View 8 Replies
View Related
Nov 30, 2007
Hi I am trying to run a dll or exe from a procedure or trigger in the database.
I have tried EXEC master ..xp_cmdshell '\xx empdivConsoleApplication1.exe' and
use master exec dbo.StoredProcedure1
If I run the exe from explorer all is working, but from a trigger or procedure in the database nothing happens only executed successfully.
I loaded the assembly and procedure into the master database
Code Block
CREATE PROCEDURE [dbo].[ClassLibrary3] AS EXTERNAL NAME
[ClassLibrary3].[ClassLibrary3.Class1].[StoredProcedure1]
My C# code:
Code Block
public partial class StoredProcedures
{ [Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{ //------first a select to get some data String prosjekter = "";
String connString = @"Data Source=serverOFFICESERVERS;Initial Catalog=WSS_Content;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand sqlcmd = new SqlCommand("Select nvarchar3 prosjekt from dbo.AllUserData where tp_listId = @listid", conn);
sqlcmd.Parameters.AddWithValue("@listid", "xxx"); try { conn.Open(); SqlDataReader r = sqlcmd.ExecuteReader();
while (r.Read())
{ prosjekter += "" + r["prosjekt"] + "" + "";
}
r.Close(); conn.Close();
} catch (Exception)
{ conn.Close(); }
//-----write some changes back to a program pocketForm pf = new pocketForm();
try {
pf.LoadFromFile("C:/temp/div/xxx.xpf");
string xmlstring = pf.FormXML;
int id, item, items; id = xmlstring.IndexOf("xxxxx");
item = xmlstring.IndexOf("", id);
items = xmlstring.IndexOf("", item);
xmlstring = xmlstring.Remove(item, items - item);
xmlstring = xmlstring.Insert(item, prosjekter);
pf.FormXML = xmlstring;
pf.StatusModifiedLocally = true;
WebService.Login("http://xxxx.no", "xx", string.Empty, string.Empty);
WebService.Save(pf);
} catch (Exception)
{
}
What am I missing here ? Please answer this as I have tried everything with no success. Regards Ivar
View 5 Replies
View Related
Jul 10, 2006
Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks
View 3 Replies
View Related
Aug 9, 2006
I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
Help is much appreciated.
View 4 Replies
View Related
Oct 26, 2006
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
View 2 Replies
View Related
Jan 19, 2007
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
View 2 Replies
View Related
Feb 13, 2007
Dear Masters;
How can I call a stored procedure with VB code?
Thanks
View 2 Replies
View Related
Nov 12, 2007
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
View 2 Replies
View Related
Feb 15, 2000
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies
View Related
Jan 12, 2003
I have been trying to execute Oracle Stored Procedure From SQL Server using Linked Server without luck. The scenario is explained in detail below ..
Environment :
SQL Server Version : 2000
OS : Win NT 4.0 SP5
Oracle Version : 8.0
OS : Win NT 4.0 SP5
User : Scott
Oracle Procedure
Name : TEST_PROC
Parameters: None
The procedure look something like this ...
Create or Replace PROCEDURE TEST_PROC
AS
BEGIN
Insert into Table1 Values('A');
END;
Scenario One
Executing the stored procedure using Linked Server (JDEV), configured Using OLEDB For Oracle, results in the following error
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'TEST_PROC' on remote server 'JDEV'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Scenario One
Executing the stored procedure using Linked Server (SCOTT), Configured Using OLEDB for ODBC, results in the following error
Could not execute procedure 'TEST_PROC' on remote server 'SCOTT'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Could you please help me out with a workaround. In case SQL-DMO is the only workaround, can you provide some sample programs.
The bigger picture is, replicating Execution of a SQL Server Stored Procedure on Oracle.
View 3 Replies
View Related
Feb 20, 2003
I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):
-- Decalre variables
DECLARE @object INT
DECLARE @hr INT
DECLARE @property VARCHAR(255)
DECLARE @return VARCHAR(255)
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
-- Create object
EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT
-- Call method
EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT
-- Destroy object
EXEC @hr = sp_OADestroy @object
View 1 Replies
View Related
Jan 31, 2006
Hello;
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Here's my code;
Function LieuBen()
MyCalendar = CurrTSCalendar
Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")
End Function
Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String
Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
End Function
View 1 Replies
View Related
Sep 7, 2006
Hello all,
does anyone know if it's possible to call a stored procedure from a view.
Thnx,
Patrick
View 9 Replies
View Related
Sep 12, 2012
How to call a sql function in stored procedure using Sqlserver 2008?
View 4 Replies
View Related
May 30, 2013
I am trying to call a DB2 procedure in SQl server through a linked server object. I use the IBMDADB2 provider for the linked server. on the DB2 I have a simple procedure with 2 in and 1 out paramater. on DB2 it works no problem.but in SQL server I just cannot get it to work.
so my code:
DB2IBM is the name of the linked server.
declare @I_DIS smallint
declare @I_UID char(8)
declare @ID_TICK int
alternative 1:
Exec ('Call btp.GET_TICK(?,?,?)',@I_DIS, @I_UID, @ID_TICK OUTPUT) AT DB2IBM
alternative 2:
EXECUTE DB2IBM..BTP.GET_TICK 2,'19', @ID_TICK
in both cases I got the same error:
OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2IBM" returned message " CLI0100E Wrong number of parameters. SQLSTATE=07001".
so how can I call this procedure?
View 5 Replies
View Related
