Execute Stored Procedure Via Batch File

Jan 20, 2012

I have a stored procedure that generates some data and dumps it into a table. I need to export data using bcp based on the data that this procedure creates. So I know how to use bcp but don't know how to execute the procedure and pass it the two variables that it needs. I googled it and sqlcmd looks promising but can get the syntax right. The two variables are the current year and school number ie. 1112, 0021.

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

Running A Batch File From A Stored Procedure

Jun 27, 2001

Is there a way to run/call a batch file from a stored procedure?

Or, is there a way to run/call a batch file from a trigger?

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

Execute Batch File

Mar 31, 2000

Hi,
I would like to ask someone who could help me with one small problem:

I would like to run SQL server task on one of my servers, that will connect
to all other servers (including ones that are not SQL servers, ie: Exchange,
Test Server etc) one at the time, and that will execute one batch file (update.bat) that is residing on each server under C: drive.

Batch file will copy some things from all of the servers to one server.

Thanks, in advance.

Regards,
Vilko

View 1 Replies View Related

Execute Batch File As Sql Job

Apr 5, 2006

I'm trying schedule a batch file to run as a job from sql server agent. The batch file copies files from one server directory to a directory on another server. The batch file works properly when executed directly. The job is being executed under the sql service account login. I've given the service account access to both the source and destination directories.

When I try to run the job it fails with an "Access is denied" error on both the source and destination directories (as read from job history).

Any ideas?

View 4 Replies View Related

Execute A Batch File

Jul 25, 2007

Hi All,
I would like to embed some stored procedures in a batch file and execute it from the command prompt on windows.
I have no idea of how to embed a stored proc in a .bat file.
Can you please redirect me to the solution to this?

Thanks in advance
vishu

View 1 Replies View Related

Using Sql To Execute A Batch File

Aug 9, 2007

Is it possible to use sql to execute a batch file? I would like toexecute the following "C:BTWartend.exe /f=C:BTWToolboxFormatscarnum.btw /p", 6Thanks,Matt

View 6 Replies View Related

Execute Stored Procedure From Code Behind File

Mar 15, 2008

hi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
 i appreciate u r help
 
 
 

View 2 Replies View Related

Execute A Batch File From Within Sql Server

Jul 25, 2001

Hi, and thanks for your help. I have created a simple batch that xcopy files from one directory to another shared directory (in another server). Here is the code:
xcopy c:OUT_TRANSIT E:BACKUP_CESWEB /y

the E:BACKUP_CESWEB is in another server where I mapped the BACKP_CESWEB folder and make it shared.

the location of the batch file is in c:code .
When I double click the batch, the files are moved into E:BACKUP_CESWEB But when I use the window 2000 schedule or try to run the batch code from within sql server analyser, I get no results.

Any idea how to solve this.
The bottom line is that I want to copy files from one server to another server.Thanks for your help

Ali

View 5 Replies View Related

How To Execute SQL Scripts Using Batch File?

Oct 10, 2007

Hi,How can we execute SQL Scripts using Batch file???i think Batch file should contain Username,Password,Database andScripts...Using that file scripts should run...How can i give UserName,Password,Database and all those things?Plz send me details how to do that...if possible with example...Thanx in advance,RR...

View 2 Replies View Related

How To Execute A Batch File Using SQL Server 2005?

Dec 23, 2005

Hi all,As the topic describes, I'd like to run a batch file using sql server.How do i do it?Thanks in advance

View 1 Replies View Related

How Do I Execute A Batch File From Sqlserver 2000 T-sql?

Nov 6, 2007

I tried the following but file dir.rpt is never created. in fact, the exec statement appears to lock-up forever.


exec xp_cmdshell 'c: empjunkdir.bat'


dir.bat Contains
dir c: > c: empjunkdir.rpt

This should be easy!

View 3 Replies View Related

Ugrent Help: How To Write Code To Execute A Batch File

Apr 15, 1999

hi, I need to run a batch file in specific time of the week. can anyone show me the code to run a batch file with both ways: window nt schedualer and ms sql server task manger.... I do appreciate your help

Ali

View 5 Replies View Related

%1 Is Not A Valid Win32 Application - Trying To Execute A Batch File

Feb 12, 2008

Today I ran into the error (see title of this post) while trying to execute a batch file using SSIS. If anyone else runs into this problem I want to point them to my post on the issue, as I did not easily find a solution when doing the search myself: http://blog.lyalin.com/2008/02/1-is-not-valid-win32-application-ssis.html

Summary (So you dont need to visit my blog):

I was trying to execute a totally empty placeholder batchfile and this produces the "%1 is not a valid win32 application" error when using Execute Process Task. Simply add something to your placeholder file (like DIR command) and you can continue running your package without error

I hope this saves someone time in the future.


Comments? If anything in my post is inaccurate I hope you guys post some corrections.

Regards,

Dmitry
http://blog.lyalin.com

View 1 Replies View Related

Execute Command Process - Remote Batch File?

Feb 23, 2006

Good afternoon-

Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.

BATCH FILE:

cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment


I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.

SSIS:

I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.


Any help would be appreciated.

My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP

View 7 Replies View Related

Calling Remote Batch File Using 'Execute Process Task'

Oct 11, 2006

I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A. The batch file uses pgp software and encrypts a file sitting on machine B itself. The reason why my batch file is sitting on machine B, is because the PGP software is on machine B.

If I execute the batch file by itself from machine B, the script runs fine. I refer the same batch file as a UNC path from my package on machine A. But that does not work since the 'Working directory' is still machine A. I can not set machine B's folder as the working dir because it does not accept UNC path. So I say, ok , let me map a path to that UNC location and map it as drive 'Z:'. Certainly if I do so, I will be running the process on machine A and the batch file will look for the pgp software on machine A, and hence fail.

I have tried third party remote batch execution tools (PSEXEC) but have not had success, not because of SSIS limitations, but simply because the PGP executable when run through the PSEXEC tool, does not identify the location of the public keys on machine B and hence gives an encrytion failure.

How do I get the remote batch file to execute such that it executes with its own env? Is there a better remote execution tool I can try or are there any other features of SSIS I can use to get around this issue? I need the results of the batch file and hence do not want to make it an asyncronous process.

Thanks

Sumeet

View 3 Replies View Related

SQL 2012 :: Calling Remote Batch File Using (Execute Process Task)

Jan 7, 2015

I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A.

How Can I achieve this....

View 2 Replies View Related

SQL Server 2008 :: Use Folder Name As Database From Batch File To Execute Files

Jul 17, 2015

I've been struggling with this issue,

1) Test--FolderName (This Test folder name should use as a database name for below sub folders)

a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql

[Code] .....

The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..

Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?

The databases that they need to run against have the name of the database at the beginning of the name of the folder.

View 0 Replies View Related

Fail Execute Process Task Based On Batch File ERRORLEVEL?

Jun 9, 2006

I am new to this, but have scoured the web and not found an answer to my question...

I have an execute process task that runs a simple batch file. When this batch file completes with an ERRORLEVEL greater than 0, I would like the task to fail. I thought this simply meant setting the "FailTaskIfReturnCodeIsNotSuccessValue" property to true, and setting the SuccessValue to 0. However, this does not appear to work.

Even with a simple batch file forcing the errorcode to 1 as follows, the task still completes "successfully".

SET ERRORLEVEL = 1

Any ideas? Thanks!

View 8 Replies View Related

How To Assign Execute Permission To Batch Of Stored Procs To A User/role

Mar 25, 2008

Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.

Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".

Any info would be helpful! TIA.

View 3 Replies View Related

Passing Parameters To Batch File And Executing Batch File Loop

Aug 7, 2007

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil

View 4 Replies View Related

Batch Update In Stored Procedure

Feb 26, 2004

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?


CREATE PROCEDURE dbo.MySP
@userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@userIDs)

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF

View 8 Replies View Related

Transact SQL :: Difference Between Batch And Stored Procedure?

Jun 19, 2015

What is the difference between Batch and Stored Procedure?

View 5 Replies View Related

SQL Search :: Catching Batch ID From Running Stored Procedure

May 5, 2015

how to catch batch id from a running stored procedure. My intention is that when we run store procedures in batch we are running a lot of procedures and I would like to log each run and if the same procedure is running several times per day I need to separate the runs by a "batch id" for the specific run. I have created a logtable and a logprocedure that logs the start and end of a procedure run and also some values for the run. So I'm trying to find a way of fetching the "batch id" that the sp is running so I can separate the runs when analyzing the logtable. I have looked at metadata tables and also in the table sys.sysprocesses but I cannot find BATCH ID.

View 11 Replies View Related

Variable Names Must Be Unique Within A Query Batch Or Stored Procedure.

May 4, 2007

hi ,

i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...



The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.

thanx in advance

raj

View 5 Replies View Related

SQLCMD Batch File With Script In Batch File

Dec 5, 2006

I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:

sqlcmd -S (local)SQLExpress -i C:CreateDB.sql

This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.

Thanks,

Dave

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

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







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