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


ADVERTISEMENT

Execute Stored Procedure (with Parameters) With An Exec Command

Feb 21, 2004

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.

View 9 Replies View Related

How To Execute Stored Procedure By Command Line In Client's Computer ?

Nov 28, 2006

I've a S.P. that need to execute in local PC, how can I execute ?

View 1 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

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

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

Stored Procedure Or Command In Sql

Oct 23, 2007

Hi fellows
I am working in a formula about drilling machine. This machine sends the inofrmation to the server basically the name of a blasthole, revolution,pulldown and so on. I have another database with information of the same blasthole, but with different items such as drill diameter,site,area and so on, so i have two tables with information, but both of them have the same blasthole that means that i can relate with a join and identify each blasthole. but teh main point is that i have a specific column con a distance that the machine is drilling for example in a certain moment this machine is drilling 1 feet 3 feet 4 feet until to reach the bottom of a limit. his
My problem is that i want to create a from to for those distances.

table 1
pk diameter bench

dh1 12.5 4050



table 2

blasthole depth
dh1 1.2
dh1 3.5
dh1 4.7
dh1 5.4
dh1 8.3
dh1 15



with a join i can obtain something like this

view 1
blasthole diameter bench depth
dh1 12.5 4050 1.2
dh1 12.5 4050 3.5
dh1 12.5 4050 4.7
dh1 12.5 4050 5.4
dh1 12.5 4050 8.3
dh1 12.5 4050 15

the most difficult part is to include a new colum taht can start with 0 and to obtain teh previus value from depth something like this


view 1-A
blasthole diameter bench from depth
dh1 12.5 4050 0 1.2
dh1 12.5 4050 1.2 3.5
dh1 12.5 4050 3.5 4.7
dh1 12.5 4050 4.7 5.4
dh1 12.5 4050 5.4 8.3
dh1 12.5 4050 8.3 15

I was thinking to create a pk2 in order to know how many values have each blasthole and create a loop and maybe i can organize the column from something like this

pk2 blasthole diameter bench from depth
1 dh1 12.5 4050 0 1.2
2 dh1 12.5 4050 1.2 3.5
3 dh1 12.5 4050 3.5 4.7
4 dh1 12.5 4050 4.7 5.4
5 dh1 12.5 4050 5.4 8.3
6 dh1 12.5 4050 8.3 15


I hope that somebody can understand my question, but i think is a hard one for the forum
cheers
Edwin





View 4 Replies View Related

ORDER BY Command In SQL Stored Procedure

Nov 5, 2003

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%',
@Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanks

View 5 Replies View Related

BCP Command Hangs Stored Procedure

Sep 30, 2013

I have a problem where bcp command is called and executed successfully sometimes but fails randomly.My stored procedure looks like this:

select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'
execute(@SQL)
set @Count = @@ROWCOUNT
if @Count > 0
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

[code]....

The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.

View 2 Replies View Related

ADODB Command (Stored Procedure)

Jun 4, 2007

Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.

View 8 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

How To Call A Stored Procedure In An Insert Command

Feb 22, 2006

hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish

View 1 Replies View Related

OLE DB Command And Stored Procedure That Returns Value And/or Error

May 23, 2006

Guys,

could someone please tell me : am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?



What I am hoping to do:



I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.



If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.



In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)



So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?



Thanks

PJ

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

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

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

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

Stored Procedure To Execute DTS Packages

Apr 26, 2004

Hi, hoping someone may be able to shed some light on this.

I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.

Here's the stored procedure....

CREATE PROCEDURE exportLists
AS

DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT

select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid

select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''

Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" '
Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" '
end as cmd
,Identity(int,1,1) as rowno
,phone
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid

Set @max=@@rowcount
set @id = 1

While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phone > 2500
then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End

Return
GO

View 1 Replies View Related

Permission To Execute Stored Procedure

Feb 8, 2006

Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.

View 1 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

Cant Get Stored Procedure To Execute Properly

Jun 12, 2008

Hi,

I am trying to execute the following simple stored procedure. The procedure runs but doesn't create the table I am trying to create and doesn't insert the records into the table I created. But again, the procedure runs without any errors.

Can someone please correct the syntax so my procedure will run properly. It pretty basic.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PremierData1]

AS

IF EXISTS(SELECT 1 FROM sys.sysojects where type='U' AND name='PremierTest') DROP TABLE dbo.[PremierTest]

BEGIN

CREATE TABLE dbo.[PremierTest] (
[AYQ] nvarchar(6) null ,
[CYQ] nvarchar(6) null ,
[Description] nvarchar(50) null,
[PIP] [decimal] (17,2) ,
[BI] [decimal](17,2) Not null,
[PD] [decimal](17,2) Not null,
[COLL] [decimal](17,2) not null,
[COMP] [decimal](17,2) not null,
[DCRAT] [nvarchar](2) null ,
[Agent][nvarchar](3) null ,
) ON [Primary]



insert into dbo.premiertest
select *
from dbo.[new agt type tri 0804]
where dir_ceded_ind = 'c'



END


Thank you for any help provided.

View 7 Replies View Related

Automatically Execute A Stored Procedure

Dec 13, 2006

I don't know if this is a newbie question or not, I want SQL Server to automatically execute a stored procedure at the first of each month, how can I do this ?

Regards,
AJ

Be nice to nerds. Chances are you'll end up working for one. (Bill Gates)

View 1 Replies View Related

? Execute SSIS Pkg From Stored Procedure

Feb 2, 2007

Is there a programatic way to execute an SSIS package (passing the appropriate parameters) from within a stored procedure. I've seen some examples using xp_CmdShell, but would rather not go that route if possible

View 1 Replies View Related

Execute Stored Procedure In A Resultset[:D]

Feb 21, 2007

I have a sql server resultset that returns productid numbers. I would like to loop thru this resultset and run a stored procedure using the productid as the input parameter. Any samples would be helpful. thanks

View 3 Replies View Related







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