Execute DTS Package Using A Stored Procedure From Button Push

Jul 5, 2006

I have a site that will run a DTS package from a stored procedure when a user pushes a button. I know the stored procedure works and exectures. I need the .net code on how to execute this particular type of SQL statement.

Any help would be AWESOME.

THX

This is the stored procedure stored in EM:

CREATE PROCEDURE  spExportData
                       
AS

Begin Transaction
Set nocount on
EXEC master..xp_cmdshell 'dtsrun /Ssql-06-dba  /NAdultExport -E'
Commit
GO


The code below is what I am currently using and is not working.Protected Sub btnUpdateList_Clicked(Sender As Object, E As System.EventArgs)
     Dim strProc As String = "spExportData"  
     Dim MyConnClass as New SQLConns
     Dim sqlConn as SqlConnection
     Dim myCommand As New SqlCommand
    
     sqlConn = MyConnClass.OpenConn("ConnAdultEd")
     MyCommand.CommandType = CommandType.StoredProcedure
     MyCommand.CommandText = strProc
     MyCommand.Connection = sqlConn   
     MyCommand.ExecuteNonQuery()
     MyConnClass.CloseConn(sqlConn)
    
     statuslbl.text = "File updated"
  
 End Sub

 

 

View 1 Replies


ADVERTISEMENT

Button That Execute Stored Procedure

Jul 17, 2005

I have a button in a form that I want to execute a simple stored procedure to insert a single value into a database table. The Stored Procedure is:=================CREATE PROCEDURE bplog_insert_invoice_detail (@invo_Id Int) ASINSERT INTO Invoice_Detail   (Invo_Id)   VALUES(@Invo_Id)GO=================How do i pass the value from and text field (@invo_id) and execute the stored procedure when a button is clicked.Regards.

View 1 Replies View Related

Execute DTS Package From A Stored Procedure?

Jun 27, 2001

When I run the code microsoft give to launch a DTS package from a stored procedure, the procedure runs continuously, never ending.

When I run the DTS package manually, or from a dtsrun utility, it only takes seconds.

I'm trying to automate this package so it will run after a field in a table has been updated.

Any suggestions are more than welcome.

PS. Here's the code I've been using (that doesn't seem to work):

--- Declare Variables
Declare @hr int
Declare @oPkg int

--- Create Package Object
Exec @hr = sp_OACreate 'DTS.Package', @oPkg OUT
If @hr <> 0
Begin
Print '*** Create Package Object Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Load Package
---DTSSQLStorageFlags :
---DTSSQLFlag_Default = 0
---DTSSQLStgFlag_UseTrustedConnection = 256

Exec @hr = sp_OAMEthod @oPkg,'LoadFromSqlServer("JENNSERVER", "", "", 256, , , , "RunMS")',null
If @hr <> 0
Begin
Print '*** Load Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Execute Package
Exec @hr = sp_OAMethod @oPkg, 'Execute'
If @hr <> 0
Begin
Print '*** Execute Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

--- Clean Up Package
Exec @hr = sp_oadestroy @oPkg
If @hr <> 0
Begin
Print '*** Destroy Package Failed'
Exec sp_oageterrorinfo @oPkg, @hr
Return
End

View 2 Replies View Related

How To Execute DTS Package From Stored Procedure

Jan 6, 2004

Hi everybody,
I would like to ask that how can I execute a package from a stored procedure.
Laci

View 2 Replies View Related

How To Execute Ssis Package From Stored Procedure

Jul 22, 2005

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

View 23 Replies View Related

How Do You Execute An Oracle Stored Procedure From A SQL Server DTS Package?

Jul 23, 2005

I've tried several different way to execute a oracle storedprocedure from a DTS package but to no avail.I have a Linked Server setup which does bring back Oracle tables from theserver when I click on the Tables icon.Here's my DTS statement:exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);omsd is the linked serveroms_teco is the owner of the oracle stored procedureSP_Callback_Update_Pkg is the oracle stored procedure(116) is the parameter passed to the oracle stored procedureI put the above exec statement in a DTS Execute SQL Task using a Connectionthat I tried using several OLE and ODBC Data Sources. I can't seem to findthe right combination.Please Help!!!!!!!!--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Call A Stored Procedure For Data Push

May 2, 2007

Is it possible to call a stored procedure on the remote sql server to do a push (insert the data onto the main sql server)?



View 1 Replies View Related

Linking A Stored Procedure To A Button

Mar 16, 2004

Hi,

I have a web form linked to a SQL database.

I have created some stored procedures to Select, Update, Insert, and Delete records in the database.

Please could some tell me how to link buttons to call the stored procedures

I am new to ASP.NET.

Thank you.

View 2 Replies View Related

Running A MS SQL Stored Procedure On Button Click

Mar 14, 2008

I am new to ASP.NET so please excuse what may seem like a dumb question.
I have a stored procedure that I need to run when the user clicks on our submit button.  I am using Visual Studio 2005 and thought I could use the SqlDataSOurce Control.  IS it possible to us the control or do I need to create a connection and call the stored procedure in the the button_click sub?
Thanks in advance
MF

View 6 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Is It Possible To Execute Package Through Stored Proc

Jul 7, 2006

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

View 10 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

HOW TO EXECUTE THE PACKAGE IN STORED PROC PLZZZZZZZZZZZZZZZZZZZZZZZZZ

Aug 6, 2007

My Package Exist in


C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code

Regards
koti


View 1 Replies View Related

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

How To Execute SSIS Package With Configuration Stored In SQL Server?

Jun 29, 2006

Hi,

I have a SSIS package called "MyExport" stored on the SQL server 2005 standard SP1. I have created an SSIS package configuration stored in a SQL table msdb.dbo.SSIS Configurations, with the configurationFilter = "Export2" with some configuration values which I can change programmatically as needed.

How do I execute this package with the configuration in the SQL table? I don't seem to have any problem when the package configuration is stored in an .xml file. The documentation is very poor or non-existant on trying to do execute the package with configurations stored in SQL server. I just can't seem to get the proper syntax.

Can someone give me an example of a dtexec command for the above or maybe some c# code?

In Execute Package Utility, when you select the Configurations option, it pops up a dialog box for a FILE based configuration file (*.dtsconfig). There is NO WAY to access a configuration stored in the [dbo].[SSIS
Configurations] table - which is where the SQL based configurations are stored. If you could pick a SQL based configuration, you would then need to pick the ConfigurationFilter for the configuration you want to use.

It's the same when you try to create a new job in SQL Server Agent - you can't select the package configuration stored in SQL server. When you get to the configurations tab, you can only add a file based configuration.

I've checked BOL as well, and there are no examples or discussion of this that I can find.

If anyone can point me in the proper direction, I'd appreciate it.

Thanks.

View 8 Replies View Related

Using Sql Server Agent Stored Procedures To Execute A Package

Aug 15, 2006

hi everyone,

awhile back someone (i think it was darren) submitted a post which had a code example of using sql server agent stored procedures to execute a package.  i searched for the post, but i didn't find it.

anyway, can someone please re-post that example or provide a similar one?

thanks.

View 7 Replies View Related

Trying To Execute An Update Query From A Button

Jun 16, 2006

I've got a sqldatasource with a update query in it.  Now I'm trying to execute that query on button click.  How do I go about doing so?
 
Here's my ASPX code:
 
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Data_Verification_Editor.aspx.vb" Inherits="Core_Data_Verification_Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDS_Valid" runat="server"
ConnectionString="<%$ ConnectionStrings:Test%>"
SelectCommand="Data_Validation_sp" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
<asp:SqlDataSource ID="Test" runat="server"
ConnectionString="<%$ ConnectionStrings:Test%>"
UpdateCommand="UPDATE [Data_Valid_Current_tbl] SET ID = '@selected_id', SET Title = '@selected_text' WHERE PrimID = '1'">
<UpdateParameters>
<asp:ControlParameter Name="selected_id" ControlID="Data_Ver_ddl" PropertyName="SelectedValue" />
<asp:ControlParameter Name="selected_text" ControlID="Data_Ver_ddl" PropertyName="SelectedText" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width: 320px; background-color: menu; border-right: menu thin ridge; border-top: menu thin ridge; border-left: menu thin ridge; border-bottom: menu thin ridge; left: 3px; position: absolute; top: 3px;">
<tr>
<td colspan="2" style="font-family: Tahoma; font-size: 10pt;">
Testing:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Data_Ver_ddl" runat="server" DataSourceID="SqlDS_Valid" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
</table>
</form>
</body>
</html>
 

View 1 Replies View Related

How To Execute A Selectcommand On Button Click?

Jan 9, 2007

Hello friends
Can anyone tell me the means by which I can populate a label or a datagrid by executing a selectcommand statement on the form of my webpage. And what is the best way to retrieve data from a SQL datasource, is it by using separate sqldatasources for each different parts of a page that requires different data from different tables in the same database or by using a tableadapter to retrieve the data for the best performance?
Thanks for replying for my previous questions. It was helpful and great in my program implementation.
Thank you

View 1 Replies View Related

How To Execute Dts Packge By Button Control?

May 22, 2007

Hi, I have a Button control on web form, and want to execute dts package instead of procedure.
How can I do that?  I am using vb.
 
Thanks.
Michelle

View 2 Replies View Related

Execute URL From Stored Procedure!

Apr 14, 2004

Hi,

Can anybody please tell me if it is possible to execute an url from stored procedure.

thanks in advance,
preeth

View 1 Replies View Related

Execute Stored Procedure

Aug 10, 2000

Hello:

I have created a stored procedure that will backup and restore a database.
I would like to be able to execute this sp using public rights - any ideas?

Thanks

View 1 Replies View Related

Execute Stored Procedure

Jun 14, 2008

How can I use in stored procedure the faction β€˜β€™in’’ to select values, using execute and the values must not be specified before in the stored procedure

View 6 Replies View Related

Execute A Stored Procedure

Jul 23, 2005

How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.

View 2 Replies View Related

Execute Stored Procedure

Mar 29, 2006

I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!

View 3 Replies View Related

Execute SSIS Package Stored In Remote SQL Server From A Client Machine.

Mar 3, 2006

I have written a VB component to execute a SSIS package stored in SQL server.

I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand

and executereader and everything works perfect on the local machine. This is descibed in a msdn site .

But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.

My question is: Can you still use dtsclient or do you have to do in some other way?

rose-marie

View 4 Replies View Related

Execute Sp_start_job From Stored Procedure

Oct 27, 2006

I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job?  This is all run from a web page and NOT the Query Window.

View 1 Replies View Related

How Do I Execute A Stored Procedure Using SSIS

Oct 17, 2007

Please anyone help me with my question
How do I execute a stored Procedure using SSIS ?
I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in  Visual studio 2005
 
Thanks,
George

View 2 Replies View Related

Execute Insert Stored Procedure

Jan 22, 2008

Hi,
 I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a  temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
 
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
 
please can someone point me in the right direction

View 13 Replies View Related

How Can I Execute A N Exe File From Stored Procedure?

Aug 24, 2004

hi to all,

i have a stored procedure and i want to run a program from it..
i think that i need use api functions but how can i do that..
if there is a nother ways please tell me..

thanx..

View 3 Replies View Related

Dynamically Execute Stored Procedure

Oct 31, 2005

Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1(   id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that?  I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.

View 1 Replies View Related

Simply Execute A Stored Procedure

Dec 20, 2005

Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong.
Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test")
Dim cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "cssp_family"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cn.closed

View 6 Replies View Related

How To Execute Asynchronously Within A Stored Procedure

Apr 30, 2001

Within a stored procedure, is it possible to call multiple other stored procedures asychronously? For example, I'd like to execute both local and remote stored procedures, but don't want/need to wait for the output while the original stored procedure continues to execute each subsequent command.

View 1 Replies View Related

Can Stored Procedure Execute A Dos Command?

Jul 14, 2001

Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?

Thanks,
Ben

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved