Hi,
How can we execute SQL Scripts using Batch file???
i think Batch file should contain Username,Password,Database and
Scripts...
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...
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
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?
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
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 RelatedHi, 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
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 View RelatedHi 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 RelatedI 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!
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
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
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
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
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....
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?
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.
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!
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
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
Hi
I'm running into an odd problem. I have set a backup process on a user installation, and invoke the backup using a CMD file.
When the CMD file is executed from a DOS box, or windows GUI the SQLCMD executes, but, when the same batch file gets spawned by the AT command, the SQLCMD does not exeute.
The odd part is that I have the same exact setup on another SQL Express installation on another server and it works OK.
Ideas?
David L.
I have separate scripts for Creation of Tables, Stored Procedures, Views, Triggers in different .sql files.
I have to execute thses scripts in batch in different servers and different databases without opening SSMS.
All .sql files to be executed in single command.
I am running a SQL Server 2012 Express.
Due that I dont have SSIS and can not plan tasks. So I have to write a script to do the following:
1. Delete Data in Database1 - Table1 on Server 1, Database1 - Table2 on Server 1,Database1 - Table3 on Server 1
2. Delete Data in Database2 - Table1 on Server 1, Database2 - Table2 on Server 1,Database2 - Table3 on Server 1
3. Connect to Server 2
4.Copy Data from Database1 - Table1 on Server 2 to Database1 - Table1 on Server 1 ... and so on
5.Send me a message @xy.com when a error appears
Then I have to run the batch file every day from monday to friday night.
My main problem is the part with the connection. Iam able to write the connection string but I dont know how to execute the string.
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query
Statement stmt = null;
try
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
for ( String sql : sqlList )
{
stmt.addBatch( sql );
}
results = stmt.executeBatch();
stmt.clearBatch();
conn.commit(); //throws the exception
}
catch ( SQLException e )
{
try
{
conn.rollback();
}
catch ( SQLException e1 )
{
throw new DataSourceException( e1 );
}
throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() );
}
Hello,
I have an "execute process" task which executes a perl script. When I run the task, it shows a prompt with the message "the publisher can not be verified". It gives the option to continue the task or to cancel.
The problem for me is that I want to schedule this package to run automatically, and I don't want the automated process to show this dialog. I need to override the security setting and have SSIS execute my batch scripts without prompting. Assuming I don't want to rewrite the Perl code into a script task, is there a way to do acomplish this?
Thanks!
Arkadiy
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.
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
hi,
I need some help in accomplishing this task.
I want to design a DTS task which will:
a)copy a certain given files from one directory to another
b)import the files into the tables
c)upon successful import delete the files from the original directory.
I done know much about scripting and need help in figuring out steps a) and c).
thanks
Zoey
Do u know how to write a batch file
for example i will need to type the sql server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
Do u know how to write a batch file
for example i will need to type the server name it has to connect to the server and run a script that I have
let me know if u have any ideas of doing it
Can someone show me an example of the syntax required to execute multiple BCP commands within the same batch (*.bat) file?
Sorry if this is a bit of a basic question, but not being a programmer by profession, I need to plead ignorance. I've tried a few things, but I just can't seem to figure it out.
Thanks!
Rich
Hi pals,
I need a small help from u all.
I need a Windows batch file script which does the ftp to remote machine and "puts" a file
say "data.xls" from a predefined dir say "c:uploads" and puts on the database server "d:dumps".
Regards,
Franky
I have a windows batch file that executes a SQL Server bcp command. Iwould like to obtain a return code if the bcp command fails. However,I cannot seem to find the return code (if any) for bcp. For example,if the bcp command is improperly formatted, or has a bad password, Iwant the batch file to return an error. Right now, my batch filesimply executes and returns success, even when the bcp command fails.Has anyone run into this before?Thanks!
View 3 Replies View Related