CmdExec In A Nightly Job
Jul 3, 2007
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
View 1 Replies
ADVERTISEMENT
Sep 19, 2007
Please help.I have Database A (Archived data), Database B (Transactional data).Both A and B do not have any timestamps in any tables.I need to bring A (only once because it is archived data) and B withonly differentials into Database C for reporting purposes.Currently C is cleared before loading B with differentials every nightand A is not loaded since it takes 15 hours to load.Now I want to bring A once and leave it and then bring B every nightwith differentials without clearing C.Please suggest some solutions.Thank You
View 1 Replies
View Related
Jan 24, 2008
Do you guys do the DBCC CHECKDB on system tables TEMPDB, MASTER, MODEL, MSDB.
When do you run the DBCC UPDATEUSAGE
First i do the following at night
Check database integrity
Rebuild Indexes
Backup Full
Remove old bak
clean up history of all bak
clean up history logs
I was wondering when to put in dbcc updateusage
I look forward to your comments
View 11 Replies
View Related
Oct 1, 2001
I have tried to make a DTS task running a batc-file on the same server as the SQL program is running using the CmdExec command.
My Command is as follows:
CmdExec serverpathatchname.bat
The problem is that everytime the step runs it report the following error:
"The system cannot find the file specified."
In the bat file I am also trying the de-map/map a network drive with another loginname/password than the one used on the SQL-server -> is this i problem?
Hope to hear from you soon, this is a bit urgent!!
Please provide tips to this adresse: qtip@bigfoot.com
View 1 Replies
View Related
Oct 22, 2004
I am using SQL Server 2000 and trying to create a disaster recovery strategy that would run nightly and backup the database or at least the changes and would ftp these to a secure ftp site. For smaller database it is easy, I just take a full backup, zip up the file and ftp it to the secure backup site. This strategy does not work so well when the zipped up database is still close to 3GB. I have a pretty big window for doing everything but 3GB is just too much to ftp overnight. The recovery model is simple so the only other option seemed to be do a full backup once a month and take differentials nightly. The problem is I am offsight and the client may need to take a full backup during the day and my nightly differential would get screwed up.
There is a fairly low volume of transactions so the idea of just doing nightly backups on the data that has changed is the obvious choice but differentials don't seem to fit. Any ideas?
Thanks,
TH
View 2 Replies
View Related
May 20, 2008
I have a production database that I would like to have copied over to a backup database on a separate server every evening. I don't want to mirror, I just want the databases synced up every evening.
The servers are physically attached through a gigabit switch and the database is relatively small, so I don't think that speed will be an issue.
Could someone point me to an article about the best way to accomplish this?
Thanks.
http://www.dynamicajax.com
View 2 Replies
View Related
Apr 22, 2008
Quick question..
If I'm log-shipping data from a database can I still perform a full backup of it each night without it affecting the log sequence?
View 7 Replies
View Related
Nov 6, 2006
I know this is probably an easy thing to do but I've been struggling with this on and off for the past month or so. All I want to do is copy two databases (our app db's) to two other databases (for app testing and training) on the same server every night. The databases aren't used during this time so they can be taken down if necessary.
I've tried using the Copy Database wizard and scheduling the resulting job but that isn't working...
Thanks in advance for any direction/help!
View 8 Replies
View Related
Feb 4, 2000
Hi All,
In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in
one step, instead of creating several steps with a single DOS-cmd in each.
For example:
Step1: bcp sourcedb..sourcetbl out source.dat -n -T -Ssourceserver
Step2: isql -T -Stargetserver -Q "truncate table targettbl"
Step3: bcp targetdb..targettbl in source.dat -n -T -Stargetserver
If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow,
SQL doesn't 'understand' it should execute after the end of each command OR
I missed something here (apparently so!).
I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis.
Anyone has run into this same problem? Thanks in advance.
David Nguyen.
View 1 Replies
View Related
Feb 2, 1999
WE have a number of SQL 6.5 servers all of which run many scheduled tasks. However on one all tasks which use cmdexec to run batch files fail after 2-3 seconds. The history returns : 'No message' and the error logs just indicate that the tasks have faILED.
The identical tasks run on servers with what appears to be identical configurations and setup.
Tasks scheduled using Tsql run OK !
I would be grateful for any advice or help
Many thanks in advance
Tom
View 1 Replies
View Related
Nov 11, 1998
I would like to trap a return value from a cmdexec that is scheduled. The cmdexec returns 0 if it is a success and something other than 0 if it doesn't.
Can I raise an error from a command file. The command file calls a console application ( i.e. no interface ).
Any help is appreciated.
View 1 Replies
View Related
Aug 17, 2001
I have a Job that runs a cmdexec job step which executes a batch file in sql server 7.0 that runs fine
In sql 2000 when i try to run that job it gives the following error and fails
----
the process could not be created for step 1 of job 0x677EF599B13FA743AA2D501D4C211AC4 (reason: The system cannot find the file specified). The step failed.
------
In fact i am not able to execute any cmdexec job in sql 2000
The owner of the above job is sa
Does it have to do with SqlAgentCmdExec account which is set to corporate/administrator and has required permission.
Help will be greatly appreciated.
View 1 Replies
View Related
Aug 20, 2007
Thanks for the invitation to post a question, so I will post one.
I need to create a job step that uses cmdExec.
This is the command line I entering:
D:odbcTimeClockUpdatesinReleaseTimeKeepingNo nLogouts.exe
When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong?
Facts.
1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions.
2. I know the Sql Server agent is running, because I have other jobs that are run.
3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe.
4. Do I need to enclose my command in quotes i.e. “D:odbcTimeClockUpdatesinReleaseTimeKeepingN onLogouts.exe”
5. the path of the file I need to run is the path on the server and not the path on my local machine.
If you need any other information, please let me know
Thanks for you help
GEM
View 7 Replies
View Related
Nov 15, 2007
Hi all,
I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:
I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:
EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.
So, I changed the statement to point to dtexec.exe 32bits:
EXEC master.dbo.xp_cmdshell 'C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
But executing this statement it does not even run. The errors are:
'C:Program' is not recognized as an internal or external command
operable program or batch file
This was very wird to me, because this was very mentioned by the moderators.
So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.
Now my questions are:
1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?
2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.
Thanks in advance.
Marco Francisco (Portugal)
View 9 Replies
View Related
Mar 24, 2006
I have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error:
0x469A75B7998F8142A910FA7E9983CCDF
has caused an exception in the CmdExec subsystem and has been terminated.
the enterprise manager shows that the job failed, but the app still shows up in task manager.
any suggestions?
thanks
View 1 Replies
View Related
Feb 9, 1999
Does anyone see any negative consequences to stopping and starting SQL Server nightly?
View 2 Replies
View Related
Sep 13, 2007
How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
Kev
View 1 Replies
View Related
Oct 11, 2000
Hi:
I want to copy a backup from the production server to another server
periodically. The following is the DOS command and it works in DOS propmt
d: is the local drive and g: is the mapping server
---------------------------------------------------
"copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
I try to use the above command in the executive task with CmdExec as type,
if failed. Then try on
"cmd c/ copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
this time, it started to run with no error. But it run 40 miniutes and I cancelled it, since it should only run 10 minuts.
thanks for the help
David
View 1 Replies
View Related
Apr 12, 2008
I'm a bit confused. On the command line of the job step property I entered dtexec /SQL... and got an error saying file not found, I assumed dtexec itself couldnt be found. So I tried /SQL .... by itself and got something that looked more like a security error. If I make the step property type "ssis" job appears to run fine, I receive my pkg's on success (rather than on failure) email but I know everything isnt fine because even if no data is ETL'd, first executable is supposed to (and always has in client) insert a row into an audit table and it doesnt. If I set job step "type" to t-sql and simply db email myself with t-sql command, everything is fine.
The first question is "Wouldnt dtexec need to be specified, how else could sqlagent know what I'm trying to run?" If answer is yes, what's wrong with my syntax or environment?
View 9 Replies
View Related
Aug 23, 2002
I need some help. I'm running Win2000 w/SQL 2000 Active/Active cluster. I've started having problems where one node will fail over automatically every night. The times are different each night and the only thing that shows up in the log file is the SQL Server terminated due to stop request from the service control manager. I can't find any error to point me in any direction. Does anyone have any idea what could be causing this?
Thanks,
Jeff
View 1 Replies
View Related
Dec 10, 2005
I need some assistence here.I have two servers. One with MS SQL 2000 Standard edition and MSDE(MSdesktop engine).I need to sync my databases nightly, as a backup.Is there a cheap software that will do this? I don't want to justbackup the entire databases files b/c they are large and I'mtransfering them over DSL.I was thinking transaction logging, but I'm not sure. Is there a easyway to do this?I don't know how to accomplish this task.PLEASE HELP.
View 9 Replies
View Related
Aug 29, 2006
where in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies
View Related
Feb 4, 2008
I run a nightly full backup of the databases and then back up the transaction log. After completing the full database backup and the transaction log, I then update all existing statistics.
Is this a good practice to do on a nightly basis? I drop and re-create indexes on all the databases once a week. Does updating the statistics really help?
View 3 Replies
View Related
Mar 25, 2003
Can someone tell me how i can generate an exit code (Cmdexec). Ive tried running an .exe file in a job but when i do that the job hangs. When i check the history it says the exit code is somewhere in 27000. Now i'm wondering if it is possible for me to generate an exit code if the executable closes.
PS. When i run the file from a batch file it works great
View 1 Replies
View Related
Nov 15, 2006
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainNameSQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
D:
cd MSSQLBACKUPAPAP_Primary
xcopy *.* D:MSSQLBACKUPAP /s/y/d
When run as a job, with the owner of the job being DomainNameSQLAdmin the job fails with the following error message: Executed as user: DomainNameSQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?
View 4 Replies
View Related
Aug 15, 2007
Hi, I have the following setup:
- Win 2K Server, SP4
- SQL Srv 2K, SP4
- A 'DEV' domain, with an 'Administrator' account with all possible rights on the system
I need to configure a CmdExec proxy account in order to allow non-sys-admin users to execute the master.dbo.xp_CmdShell procedure.
When attempting to do this via QA as follows:
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'DEV', -- agent_domain_name
N'Administrator', -- agent_username
N'password' -- agent_password
...I get the following error:
"The system cannot find the path specified."
When attempting to do this via QA as follows (note: only change is adding domain to agent_username arg):
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'DEV', -- agent_domain_name
N'DEVAdministrator', -- agent_username
N'password' -- agent_password
...I get the following error:
"Error executing extended stored procedure: Specified user can not login"
I have tried this through Enterprise Manager and get identical results, of course.
I have also tried all of the following:
- different OS user accounts, including local system accounts with local admin rights;
- assigning the OS account to a SQL login with System Admin role/rights;
- specifically assigning the above SQL login with EXEC rights on the master.dbo.xp_CmdShell procedure;
- verifying local security policy settings, as per the following link: http://support.microsoft.com/?id=283811;
- pulling out my hair and banging my head against the wall.
Can anyone H E L P ? ! ! !
Thanks,
Joe
View 6 Replies
View Related
Oct 18, 2006
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine).
Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.
However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found.
What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.
Is there a way around this?
View 5 Replies
View Related
Feb 2, 2007
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
View 1 Replies
View Related
Apr 11, 2007
2:30:03 AM SQL Server Scheduled Job 'Our Job Name' (0x888B36086CCCC34FB353B408DEBE0432) - Status: Failed - Invoked on: 2007-04-11 02:30:00 - Message: The job failed. The Job was invoked by Schedule 30 (Our Job Name). The last step to run was step 1 (Our Job Name).
Where do you begin to discover the cause of the crash?
View 6 Replies
View Related
Aug 17, 2006
I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out. Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily. Currently this is all on the same machine. I have not moved it beyond where I am developing.
On the command line I am using the following command...
dtexec /F "S:connectionscontacts.dtsConfig" /DE "password"
Below is the output log...
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 6:59:40 PM
Progress: 2006-08-16 18:59:41.29
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.29
Source: Data Flow Task
Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.71
Source: Data Flow Task
Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.73
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.77
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.79
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.81
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.84
Source: Data Flow Task
Pre-Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.90
Source: Data Flow Task
Pre-Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.90
Source: Data Flow Task
Pre-Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Post Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.93
Source: Data Flow Task
Cleanup: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.95
Source: Send Mail Task
The SendMail task is initiated.: 0% complete
End Progress
Progress: 2006-08-16 18:59:42.09
Source: Send Mail Task
The SendMail task is completed.: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 6:59:40 PM
Finished: 6:59:42 PM
Elapsed: 1.984 seconds
When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...
Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig". End Info Warning: 2006-08-16 18:40:03.15 Code: 0x80012012 Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-08-16 18:40:03.15 Code: 0x80012059 Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Info: 2006-08-16 18:40:03.20 ... Process Exit Code 1. The step failed.
Thanks in advance for any help!!!
View 2 Replies
View Related
