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.
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
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 RelatedI 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 RelatedI 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.
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 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!
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() );
}
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
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
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!
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.
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
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Hi,
I want to schedule a daily job using sql server to update the info. in a sql server table. This is very new to me. Could you please forward me some helpful resources.
Thanks,
QuestionsI need to batch a set of update commands. Can that be done and if so what are the possible ramfications? Can one mix / match Deletes/Inserts and Updates into a SQL command via the semi-colon in a batch set? (Is this the appropriate forum?)
WHYI am unable to use the
dynamic sql building found in the dataset due to a multiple table
contstraints. I have built classes that will extract the differences
from two datasets and report on the updates/deletes and inserts
required. Since I have multiple SQL statements, it would be nice to combine them into one statement.
Platform.Net 2C#IIS6SQL Server 2000 is the eventual target database for the generated SQL
advTHANKSance
Hey guys, I periodically have to update maybe 300 records of the same column.. However, I've been udpating each record one by one.
Is there a way I can do something the following.ID Name1 john2 chris3 adam4 ben5 steveupdate table1 set name = name where id = ? but have it run through an entire set of these? Is there any way of automating that rather than having to update it manually? Do i need another table with just the id numbers and the column I want to change? or do i need a txt file?
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