.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Execute system stored procedure

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

View Complete Post

More Related Resource Links

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

With Recompile and Execute as clause in SQL 2005 Stored Procedure



I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.

SQL Command Or Stored Procedure in Execute SQL Task


Hi all,

Which is a better way to do in Execute SQL Task : Direct SQL input or Create a stored proc in database and then use that.

In my opinion, Stored Procedure is a better and recommended way for following reasons:

  • Cached execution plan
  • More secure
  • Centralized code
  • Code reuse

Please let me know if this is not the case with SSIS Execute SQL Task.


System Stored Procedure permission



I am not able to see the permission for the System stored procedure( like sp_add_job) and extended system procedure like xp_execresultset in SQL 2005.  I could see the permission in SQL 2000(right click->properties-> permission tab). 

In SQL 2005, I don't see the Properties when I right click except Modif,custom reports and refresh?

Where to check the permission for the system stored procedures?

Pls let me know.




How to execute DELETE stored procedure programmatically (C#)


 I need to execute a stored procedure which is a simple delete (record) query. Programmaticaly, I want to pass in a parameter "ID1".

Assume I will pass in the ID1 parameter on a delete link click event froma GridView control. What would the code be to execute the existing stored procedure?

Also assume:

ID1 - the parameter and primary key of the source database for the record to be deleted

GridView1 - the GridView control

spDeleteRecord - the stored procedure needing the parameter ID1 and to be executed from C# code

Here's my start:

protected void GridView1_RowDeleting(object sender, EventArgs e)
TableCell cell = GridView1.Rows[e.RowIndex].Cells[1];
int ID1 = System.Convert.ToInt16(cell.Text);
??? - code here to call and execute parameter query

Need to execute ALTER DATABASE inside stored procedure


SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?


How to write Stored Procedure for Insert Data & Execute it in MS SQL?


How to write Stored Procedure for Insert Data & Execute it in MS SQL?

System and Extended stored procedure


Hello all.

I am trying to determine which of our applications makes use of system stored procedure or the extended stored procedures. How do I go about getting this information on my database. I use sql server 2000. 

*I have searched this forum but was unable to get help.

Thank you for all your responses.

Incorrect syntax near '%'. When trying to create/alter system stored procedure


Sql Server 2005 SP3

After finally figuring out that for whatever reason the sp [sys].[sp_refreshsqlmodule] was not loaded I found the procedure on another machine and tried to create it on the machine missing the sp. I get an error: Incorrect syntax near '%'. The error applies to 2 lines in the sp:


EXEC %%Object(MultiName = @name).LockMatchID

How to execute ssis package from stored procedure

how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.

Getting Output value from a Stored Procedure in Execute SQL



I have an Execute SQL task ,in which I am having an stored procedure.

My requirement is I have to map the output value of sp to the output value of my Execute SQl Task.

So that once after the Execution of ExecuteSQL Task.I can have a check in precedence constraints and based on that I can move forward.

Thanks, A2H

Unable to execute system stored procedures - SQL Server 2000, SP4

I am unable to execute system stored procedures on a SQL Server 2000 server, running Windows 2003, SP1. If I try to run sp_updatestat I recieve a 2758 error after it updates a few tables. If I run sp_recompile, it returns the same error. If I run DBCC CHECKDB on the master database, it reruns a blank screen. Database and T-Log dumps work without issue. This started all of a sudden. I have restarted SQL as well as rebooted the server. Everything came up clean. Need advice desperately.
Steve Jimmo

Unable to execute system stored procedures - SQL Server 2000, SP4

I have a SQL Server 2000, SP 4 running on a Windows 2003 server with SP1. All of a sudden I am unable to execute system stored procedures. When I try to run sp_updatestats 'RESAMPLE' it will update the stats of a couple of tables and then return a 2758 error. I have tried to run DBCC CHECKDB on the master database which returns a blank screen. Database and transaction log dumps are working without an issue. Everything on all other databases works without a problem. I have restarted SQL Server as well as rebooted the server. Everything comes up clean. Cannot find anything on this type of problem and am desperate.

Cannot find execute any query, stored procedure not found even if it is there


Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?

Ju Lian

How to execute a parameterized stored procedure using ssis dataflow script component transformation


Hi All,

Can any body send code for how to execute a parameterized stored procedure  using ssis dataflow script component transformation using .net

I know how to execute storedproc using control flow script task, but I need using ssis dataflow script component transformation.




Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:


I installed linked server using all this 3 providers

    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.

  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend