Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Using CmdExec To Execute Dtexec In Sqlagent


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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SQlAgent Jobs With Cmdexec Steps
What are the best ways to get these to return a status to test if it has got an error or not.

View Replies !   View Related
Dtexec - Can Run In A Job (CmdExec) But Not From Xp_cmshell
 

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 Replies !   View Related
Unable To Execute Package In SQLAgent
I have created a logging test package that I am attempting to execute in SQLAgent.
It uses an environment variable in Package Configurations to set a variable which is the expression value for the ConnectionManager's 'ole_src_Admin' ConnectionString.
The next three Package Configurations use that connection to retrieve SQL Server configuration types/
  
Using the same account I can execute the package succesfully from cmd line
The execution string is
dtexec /SQL "Parent Package" /SERVER "BLAHSQL-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V
 
Attempting to execute this via SQLAgent (type CmdExec) I receive the following error:
Date  9/26/2007 9:47:45 AM
Log  Job History (Logging Test)
Step ID  3
Server  QRISQL-DEV
Job Name  Logging Test
Step Name  Execute 2005 Package
Duration  00:00:01
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0
Message
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started:  9:47:45 AM
Info: 2007-09-26 09:47:46.28
   Code: 0x40016038
   Source: Parent Package
   Description: The package is attempting to configure from the environment variable "SQL_Parameter_Connect_String".
End Info
Info: 2007-09-26 09:47:46.28
   Code: 0x40016040
   Source: Parent Package
   Description: The package is attempting to configure from SQL Server using the configuration string ""ole_src_Admin";"[dbo].[Parameter]";"sv_ssis_package_store_connectionstring";".
End Info
Error: 2007-09-26 09:47:46.30
   Code: 0xC0202009
   Source: Parent Package Connection manager "ole_src_Admin"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
End Error
Info: 2007-09-26 09:47:46.30
   Code: 0x40016040
   Source: Parent Package
   Description: The package is attempting to configure from SQL

I have also succeffuly executed this package in the same manner locally using both the cmd line and SQLAgent.
 
Any clues would be appreciated.
 

View Replies !   View Related
Active Script, SQLAgent Job, Execute DTS, Permission Denied
I'm using SQL 7.0/SP1 on NT/SP4. I created and saved a DTS Package that I want to execute every 15 minutes to check for the existence of some comma delimited text files, and import them with the DTS Package if they exist.

I'm using a SQLAgent Active Script (vbscript) job that uses the FileSystemObject to check for the files. The Active Script ran fine during construction from a remote NTWS machine. Once I scheduled the job on the server the job fails with:

"Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Permission denied Error on Line 9. The step failed."

The Active Script (main function where containing line 9) is below:

Option Explicit
Function Main()
Print "Sub Main::Entry Point"
Dim oFileSystem, oFolder, oFile, bDTSSuccess
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
(line 9 is next)
Set oFolder = oFileSystem.GetFolder("serverTransferFromHMP") <--- line 9
For Each oFile In oFolder.Files
Print "Sub Main::Call ExecuteDTS"
bDTSSuccess = ExecuteDTS(oFile.Name)
If Not bDTSSuccess Then Exit For
Rem Move file to Archive directory
oFile.Move "serverTransferFromHMPArchive" & oFile.Name
Next
Set oFile = Nothing
Set oFolder = Nothing
Set oFileSystem = Nothing
End Function

I've researched the other postings on this site and some newsgroups regarding SQLAgent and have checked SQLAdmin (the SQLAgent account) and SQLAgentCmdExec rights, advanced rights, share rights, directory rigths, file rights, roles, sysadmin rights, local administrator group, domain user account, etc. I've logged in at the server as SQLAdmin and can open and move the import files. When I log in at the server as SQLAdmin the same failure occurs. SQLAgent properties are set to use NT authentication.

When I shutdown the SQLAgent service and start it up using the command line (logged in as Administrator) sqlagent -c -v, two things happen. First, I get the following message twice:

"[000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: a required privledge is not held by the client)"

Then, the SQLAgent actually runs the job, performs the FileSystemObject operations, but the DTS Package doesn't import the data. The job completes successfully. Note, I've tested the import files and manually they import fine and do not raise any constraint issues.

The function ExecuteDTS is as follows:Private Function ExecuteDTS(sFileName)
Dim oDTSPackage, sPackName, oConn, i
On Error Resume Next
ExecuteDTS = False
If Len(sFileName) < 3 Then Exit Function
sPackName = ""
If UCase(Left(sFileName, 3)) = "PCL" Then sPackName = "DTS_Posted_Claim_From_HMP"
If UCase(Left(sFileName, 3)) = "PCN" Then sPackName = "DTS_Posted_Claim_Line_From_HMP"
If sPackName = "" Then Exit Function
Set oDTSPackage = CreateObject("DTS.Package")
If Err.Number Then
Err.Raise Err.Number, Err.Source, Err.Description
Exit Function
End If
oDTSPackage.LoadFromSQLServer ".", , ,256 , , , , sPackName
Rem Set Source Connection
For Each oConn In oDTSPackage.Connections
If oConn.Name = "Comma Delimited Text File" Then
oConn.DataSource = "serverTransferFromHMP" & sFileName
End If
Next
Rem Set other package properties
With oDTSPackage
.LogFileName = "serverTransferFromHMPLog" & Left(sFileName, 3) & ".log"
.WriteCompletionStatusToNTEventLog = True
End With
Rem Execute package
oDTSPackage.Execute
ExecuteDTS = True
For i = 1 To oDTSPackage.Steps.Count
If oDTSPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
ExecuteDTS = False
End If
Next
Set oConn = Nothing
Set oDTSPackage = Nothing
End Function

When I run the Active Script remotely it works fine (of course), and I'm use a login account that belongs to the Administrators group (as does SQLAdmin, and SQLAgentCmdExec). I have also checked that the DTS RLL files and the scripting DLL are on the server and they are. Any help would be appreciated.

Thanks,

Brian O'Neil
bmoneil@aol.com

View Replies !   View Related
DTEXEC For Remote Execute
I have a legacy extraction ("E") .NET 1.1 application that is still going to be the driving force for our ETL process. We are going to be utilizing SSIS for "T" and "L". Now, the "E" phase is running on an application server where we have .NET 1.1 framework installed and working. The SSIS packages are running on a separate SQL Server. The problem here is - how do we call the SSIS package and be able to pass in the right parameters from this .NET app that runs on a separate box? We would like to use DTEXEC to call the remote SSIS packages through Integrated Security. The SSIS packages are stored as File System packages.

View Replies !   View Related
Execute DTExec Without Installing SSIS !!!!!!!
Hello :
 
On my server,  I did not install  the SQL Server  2005, I have SQL  server 2000, thus I did not install  Integration Services.
 
I want to execute one package by line of command  DTExec.
 
I need what to execute this command without installed SSIS?
 
Thank you.

View Replies !   View Related
Package.Execute/IDTSEvents Vs DTExec
I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events,  but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
   Code: 0x40016041
   Source: DWB02130
   Description: The package is attempting to configure from the XML file "D:SSISConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
   Code: 0x40016040
   Source: DWB02130
   Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

 

View Replies !   View Related
Execute Process Task - Warnings When Running DTExec
I have a manager SSIS package that calls a generic loader SSIS package, via the 'Execute Process Task'. The manger package (amongst other things) creates a config file and and then executes DTExec passing the config specification in the Arguments property, i.e. '

 

'/FILE "D:AudiencePackagesUKDW_Core_Loader_Persist.dtsx" /CONFIGFILE "D:AudienceDataSCPmystery.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF"

 

It works as expected, but, I always get the warning message 'File/Process "DTExec" is not in path.', I know that it is because the exe runs and loads the correct package which then performs the correct actions.

 

Have I dropped the ball here? I cannot see any reason for the warning as DelayValidation==True, I could set RequireFullFileName==True, but I do not know where SQL is installed on the various nodes.

 

Any input would be warmly received

 

Thanks

View Replies !   View Related
Dtexec Problem For Excel Source -- Runs Fine Using Execute Package Utility
I have this problem executing an SSIS pkg using dtexec.



dtexec /f d:smappsssis_pkgsdts_SOAR.dtsx /REP E

 



Code Snippet

Started:  10:28:58 PMEnd Error



Error: 2008-03-03 22:28:59.00

   Code: 0xC0202009

   Source: dts_SOAR Connection manager "SourceConnectionExcel"

   Description: An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

End Error

Error: 2008-03-03 22:28:59.00

   Code: 0xC020801C

   Source: Data Flow Task Source - CHANNEL_DATA [1]

   Description: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.


Code Snippet






 
However, it runs fine when I double click the package and execute it using the Execute package utility. That really puzzles me.

 
The package transfers data from worksheets in excel to SQL Server 2005 DB.
 
Both source and destination are on the same system.
Please advise.

View Replies !   View Related
Problem: SSIS Package Failure Using 32-bit DTExec And 64-bit DTExec
Hi all,
 

I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
 

Here are the details:
 
Environment:

Windows Server 2003 64-bit (Build 3790: Service Pack 2)
SSIS 32-bit & 64-bit installed
SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
 
SSIS Package details (compiled in 64 bit)

Script tasks only
Microsoft Visual Basic .NET (using TRY...CATCH block)
PreCompileScriptIntoBinaryCode = TRUE
Run64BitRunTime = TRUE
 
Execution

Batch file that uses DTExec to execute the Package.
 
SCENARIO
I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
 

Wrong connection String using 32-bit Execution

While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
 
Wrong connection String using 64-bit Execution

While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
 
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
 
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
 
2. Shall I go ahead and use 32-bit DTExec to execute the package?  If so, is there any other major issue...like performance or anyother bug?
 

P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
 
Sorry for the lengthy one and Thanks very much for you help in advance .

 

Thanks again!
 
Suresh

 

 


 

View Replies !   View Related
Execute &&"dtexec /sql Package /server Server&&" Launches Sqldumper
I have a SSIS package with a Configuration value reading a config file from environment variable. In development environment it is able to find the environment variable. But Once i deploy on server machine. Manually create the Environment variable. Deploy the package and execute as a job in sql agent job account. The package fails stating cound not find the environment variable. When i went and saw, environment variable is still there. What is wrong with it.

 

I tried to debug using running dtexec

When I run the package using "dtexec /sql package /server server " sqldumper comes and i am not able to debug it.

Finally I have to run the package as sql job only.

View Replies !   View Related
Cmdexec Job
I have created a VB 6.0 executable with the purpose of reviewing email sent to a certain MAPI mailbox, and if the mail has a specific attachment, that attachment (a text file) is saved to the hard drive. The program then runs (using a shell command) a DTS package on my SQL Server (7.0) to import the text file into an existing table, and then deletes the hard disk copy of the file. The imported information is then processed using ADO calls to a stored procedure on the same SQL Server.

My goal is to schedule this EXE to run on a regular basis using SQL Server Agent. However...

When I run the EXE from the system with the SQL Server Agent that I would like to use (not the same server as the SQL Server the code is being run against, but it has an ODBC DSN set up for the SQL Server with the data and the SQL Server Agent user has full permissions on that SQL Server) it runs fine. But when I add the exe as a cmdexec job in SQL Server and start it, it just hangs there. It says it is executing, and it allows me to stop execution, but it doesn't actually run the program itself, as far as I can tell.

Anyone have any thoughts? Any input would be very much appreciated!

Thanks!

Heather Witt

View Replies !   View Related
CmdExec Task
Hello everybody.
I scheduled task to run daily with dbcc.
I run this task manually to test, task was running saccesfully and already 30 minuts is active and running. Our Lab test server is not busy and I don't understand what is problem, why it's running so long time.
Are you experienced with problem like that?

Thank you.
Alona.

View Replies !   View Related
DTS Scheduling (CmdExec)
I have a DTS local package that consists of a single Process Task. I call a locally stored VB executable which opens a local Excel file and deletes certain rows. When I run the package manually, it runs fine. When I schedule the package for execution the package says it executed successfully, but it doesn't actually invoke the executable. Any suggestions on how to correct this situation would be greatly appreciated.

View Replies !   View Related
CmdExec Errors!
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 Replies !   View Related
CmdExec In A Nightly Job
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 Replies !   View Related
CmdExec Job Step In Sql Server2000
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 Replies !   View Related
Trapping Cmdexec Errors
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 Replies !   View Related
Tasks Using Cmdexec Failing
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 Replies !   View Related
Cmdexec Job Fails In Sql 2000
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 Replies !   View Related
Jobs - Using CmdExec (OSQL)
Hi Guys,

Ive created a job in sqlserver 2000 (Management, SQL Server Agents, Jobs).
One of my steps execs a script (.sql) using OSQL (thru CmdExec) and outputs to another file (using the 'o-' parameter in the same command line.

Ok heres the problem:
On execution - sometimes that process get deadlocked. It then raises the errormsg & writes to the output file. HOWEVER, the SQL Server Agent reports back that the STEP completed successfully, when it is really not successful at all.

Is this the way it actually behaves when using CmdExec?

Do you think if I change the step from 'CmdExec' to use 'Transact SQL'
and output to a file (inside that step, and not through OSQL), And if it gets deadlocked again - Would the SQL SErver Agent report back the ERROR, instead of returning 'Successful' ?

OR IS there any other way around this problem.


thnx for ur help. Ill be trying to find another way as well.

View Replies !   View Related
Creating Cmdexec Type Job
Hi everybody,

I have a bcp job in .cmd file. But it is the d drive like 'd:foldernamefile.cmd' I use Cmdexec type and have used the following syntax:
start d:foldernamefile.cmd
or
start "d:foldernamefile.cmd"
or
d:foldernamefile.cmd

But non of these syntax work. The job either failed or job keep executing but no output file being seen. Something wrong with the syntax? But I can type: start d:foldernamefile.cmd in command promp it works. Can any body tell me what is wrong here?

By the way I have parameter for the .cmd file I put it after the path e.g.,
start d:foldernamefile.cmd SQLSERVERNAME. Could this parameter cause the problem. If so how I put the parameter?

Thanks for the help.

Wilson

View Replies !   View Related
JOBS Using Steps/CmdExec
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 Replies !   View Related
Cmdexec Subsystem Failure
 

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 Replies !   View Related
CmdExec &#34;COPY&#34; Command Fail To Run
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 Replies !   View Related
Identifying Failure Of CmdExec Jobs
I have a job that executes an Operating System batch script which in turn executes a number of osql tasks. i.e.

osql [various options] -i query1.sql
osql [various options] -i query2.sql
osql [various options] -i query3.sql

If for example query2 fails, the job still reports success. Is there a simple method to report failure. I have tried splitting the above into 3 steps eaching executing a batch file and specifying an action of quit with failure for each, but it still reports as successful!!

Any ideas?

View Replies !   View Related
Operationg System Command (CmdExec) Job Failing
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 Replies !   View Related
Problem With Process Exit Code (Cmdexec)
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 Replies !   View Related
Configuration Files Do Not Work In CmdExec Mode
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 Replies !   View Related
SQL2K CmdExec Proxy Account Setup
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 Replies !   View Related
Sqlagent.out
Hello,I have the following error message in the sqlagent.out file, loopingeach minute.2005-12-19 10:58:54 - ! [298] SQLServer Error: 14262, The specified@job_id ('254D5C3B-CB1F-4B02-AD79-FF5AFE343E3B') does not exist.[SQLSTATE 42000] (ConnExecuteCachableOp)I restarted the sqlagent and ms-sqlserver service, but did not fix theproblem.ThanksDiane Lavoie

View Replies !   View Related
SQLAgent Can't Up
I have two questions:(1)After I stopped SQLAgent, I couldn't start it up, even restart theserver.Why?(2) I may need to reinstall the server, if I do, do I need touninstall? or just override the current one?Thanks!Saiyou

View Replies !   View Related
SQLAgent Can't Up
Hi! I got an interesting situation. When I start SQL Agent, it seemsfine from the icon and the status bar. But actually it's not working,althogh the icon shows a green arrow. The message I got is "Can notperform this operation while SQLServerAgent is starting". the message# is 14258. Does anyone know how to solve the problem?Thanks!Saiyou

View Replies !   View Related
SQLAgent
Hi:

I hope somebody can give me a clue for this problem. Our SQL Server was installed with default option. The SQL Server account is .SQL-Cmdexec. I tried to create two backup jobs to save daily backup files into another server. The SQL Server could not detect that server drive during the database backup set up. I knew that was because the SQL Agent account could not access to that server. I had the following questions:

1. How to grant write right to current SQL Agent account to another backup server?

2. It looks like .SQL-cmdexec is created by SQL Server default installation. If I try to change this account to another account, the Enterprise Manager is the only place to be changed or there is another place keeps SQL Agent account info.

3. If I try to give SQL Agent account access to the other servers, what is the relationship between NT User manager and SQL Agent accounts.

Thanks.

Joan

View Replies !   View Related
SSIS Package That Runs Fine Through Command Prompt, XML Error When Run Through SQL Server Agent Job CmdExec
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 Replies !   View Related
Can't Start The SQLAGENT
Hi Guru,I'm tired of trouleshoot one of my sql box that the sqlagent can'tstarted. Here is the message:SQLServerAgent could not be started (reason: Unable to connect toserver '(local)'; SQLServerAgent cannot start)·SQLSERVER runs on LocalSystem account with started up typeautomatic and successful·Connect to SQL Analyzer: LocalHost, . (period), (local) and blankall successful·Registry key and MDAC are good safe·BUILTINAdministrators is present and SYSADMIN rights·From AD, domain accounts passwords are in synch.·Win2000, SQL2000 SP3a and build 800.00.760·Client and network utility used both name pipe and TCP[color=blue]>From enterprise manager on the server itself, I am able to register the[/color]server by supplying the server name. However, when I tried to registerwith the (local), I was never able to do so.I also tried to restart the SQL agent with localsystem, domain andlocal admin accounts none of them can start the service. These accountsare local admin to the box and have complete sysdmin rights to sqlserver. From query analyzer, these accounts can connect to server withblank, LocalHost, (local), and period. So, this tells me that theserver does not experience of resolving (local) connection.I tried to log on to the server with both local and domainaccounts to start agent but nothing is good for me. I tried to changestartup account from enterprise manager to make sure security is insynch but still the same.However, the last thing that I've not done is reboot the OS.Please help,Thanks,Silaphet,

View Replies !   View Related
SQLAgent Crash
Hi all, I have some trouble with SQLAgent...when I try to schedule a jobthat invokes a script (vbs, java, cmdshell,...), SQLAgent crashes at themoment of scheduled execution. If I try to execute it manually, all worksfine. This installation runs on a cluster Win2003 and the node initiate afailover if I don't delete the job and then restart Agent (I can't start theservice before deleting that job!!!). Event viewer logs something like:"LOG] Exception 5 caught at line 191 of file ..src
efreshr.c.SQLServerAgent initiating self-termination".I've already tried to debug the service but my dump did not help me.Any suggestion?Thankscamau

View Replies !   View Related
Error From SQLAGENT.1
I am getting the following errors from my sqagent log file.

1.
2/19/2004 6:51:59 PM - ! [192] Date calculation spin detected for Schedule 30

2.
2/19/2004 6:51:59 PM - + [162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 30

3.
2/19/2004 6:52:32 PM - + [191] Warning [1]: Possible date calculation spin for Schedule 32

Couls someone please explain each as I am very concerned about number 2 in particular. I think it is causing one of my job not to be rescheduled.

Thanks for the help.

View Replies !   View Related
SQLAgent Not Starting
The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...

SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).

I modified the properties for SQLAgent back to login as local account and I still get the same problem.

Any suggestions would be appreciated.

Sidney Ives
Database Administrator
Sentara Healthcare.

View Replies !   View Related
SQLAgent Login
SQL Server name: BOProd
Domain: BODomain
I have an NT User 'SQLAgent' with domain Admin previleges.
SQL server has 'SQLAgent' user with NT authentication and system admin previledges.
I am in process of setting up mail services for sqlmail and sql agent mail.
As a part of this process, when I log on to the server computer as SQLAgent, it doesn't let me access Enterprise manager saying
'Login could not be established to BOProd-Login failed for user 'BODomainSQLAgent'. It does allow me to connect to query analyser by providing 'user name/password'.

Any idea why connection to enterprise manager fails?

View Replies !   View Related
SQLAgent - AlertEngine
Hello All,

In the current activity I am seeing a process which is SQLAgent - Alert Engine.
It is showing that it is using lot of disk IO .
Does it has anything to do with performance of the server?
Please help!!!

Thanks in advance


MR

View Replies !   View Related
SQLAGent Error
Hi,
 
Can someone explain me this error I encountered, is this an application error or script error? How can i resolve
this one?

 

"The description for Event ID ( 208 ) in Source ( SQLAgent$TSESMSDBS ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: [0230] GET MA_SMSCODE, 0x523D92CBAACD304E88CF720580B18357, Failed, 2007-11-10 02:30:00, The job failed. The Job was invoked by Schedule 2 (MA_SMSCODE - Every 2.30AM). The last step to run was step 5 (NSCSI - MODEL SMS SHORT CODE).."
 
Hope to hear from anyone as soon as possible.
Thanks.
 
Russell

View Replies !   View Related
SQLAGENT CAN't START
SQLAGENT CAN't START

this log:

 

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

 

View Replies !   View Related
Assistance With SQLAgent
All,
 
   I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
 
Any ideas where I'm failing here?
 
Thanks in advance,
 
JB
 

View Replies !   View Related
SQLAgent Is Not Allowed To Run
Hi 

Keep getting this message in the Application Event Viewer.





Source:        SQLAgent$SHAREPOINT

Category:     Alert Engine

Event ID:       324

Description: SQLAgent is not allowed to run.





Can't find any help online. Anyone out there got any ideas?

TIA

 

View Replies !   View Related
SQLAgent Permissions Problem
I am at a loss on this one. I having been trying to get a DTS package to run as a job step with no luck.
I have them working on a machine with Beta3 release, also had a job working on the released
version before I went on vacation. I have been unsucessful since my return about 3 weeks ago.
I am receiving the following error:

DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)
Error string: Login failed for user 'sa'.
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.

The services are starting using the Admin account, job is set up using sa account.
The DTS package runs fine when executed from Local Packages.
Any ideas?

View Replies !   View Related
SQLAGENT Xp_sqlmaint Security Bug???
On my account we have very separate authority between the NT system administrators and the SQL DBAs. Routinely in SQL 6.5 we would remove sa authority from the NT administrators group and add this authority to a NT database administrators group which we were members of using SQL Security manager.
We have tried to apply this same method to our implementation of SQL 7.0. We removed the sysadmin role from the BUILTINAdministrators group and added sysadmin to the DatabaseAdmin NT group which the DBAs and SQLAGENT account are members of. This appeared to work fine until we tried to run our database maintenance plans in SQLAGENT. When sysadmin role is revoked from BUILTINAdministrators the jobs in SQLAGENT using xp_sqlmaint fail with "select permission denied to sysdbmaintplans in database 'msdb' owner 'dbo'"

The SQLAGENT login is part of an NT group that has been granted sysadmin role in SQL Server. This should grant full access to SQLAGENT. What's up? Is the xp_sqlmaint using hardcoded security of some kind? I am confused. Any suggestions? We really need to revoke sysadmin from the BUILTINAdministrators group.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved