SQLAgent 2005 SP1 Failed To Run .bat File.
Mar 26, 2007
Following is our test steps.
1. Create directory E:firefly in E:.
2. Create a test file a.txt in E:firefly.
3. Create E:firefly est.bat file with the following commands:
cd E:firefly
copy a.txt b.txt
4. Create a new job in SQL Server Management Studio with only one
step, this only step's command
type is set to "Operating system(CmdExec)", and click the "Open"
button to choose E:firefly est.bat
file, then the commands in test.bat are copyed to the command editor.
5. Save the job and run it, Management Studio tells the job faild, but
did not tell an error message.
If I only use the following command :copy E:firefla.txt Efirefly
.txt, and SQLAgent can
run the command successfully via the job.
The real functionality of our job is to backup database to a file,
then use our source control tool's
command line tool to submit the backup file to the server.
View 5 Replies
ADVERTISEMENT
Apr 20, 2007
My overnight production job has been running for months. Duration is typically 40 minutes.
Last night it started and without doing anything, it stopped. There was nothing in the SSIS log.
SQLAgent task log reported
The return value was unknown. The process exit code was -10737418.
Usually, I can find something on Google when I enter the error code; not today.
Any ideas?
All suggestions welcomed.
IanO
View 7 Replies
View Related
Nov 9, 2006
We are seeing the following in our SQLAgent log every minute. I cannot find any information anywhere about this error message.
[298] SQLServer Error: 599, WRITE: The length of the result exceeds the length limit (2GB) of the target large type. [SQLSTATE 42000] (LogToTableWrite)
View 3 Replies
View Related
Aug 15, 2007
I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR]
,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME]
,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM])
SELECT
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
View 4 Replies
View Related
Jun 15, 2006
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
View 11 Replies
View Related
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
View 1 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Jan 31, 2007
Hi,
I use the DTS 2000 Migration Wizard to migrate one of the DTS 2000 packages to SSIS. The migration failed with the following error message:
LogID=17
#Time=6:31 PM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".
---> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:Documents and SettingsfuMy DocumentsVisual Studio 2005ProjectsKORTONKORTONProcessCubesMF.dtsx" with error 0x80070002 "The system cannot find the file specified.".
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)
Looking at the call stack, it looks like COM wrapper fails on SaveToXML. Can someone tell me how I should workaround this problem?
Thanks,
Bobby Fu
View 1 Replies
View Related
Sep 16, 2007
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
View 1 Replies
View Related
Mar 24, 2006
I know allot of folks are having this problem and I tried lots of things but nothing works. I understand the problem is coping the SQL Express on another server is the problem - I just not sure what to do?
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
This is the last statement on the Stack Trace:
SqlException (0x80131904): An attempt to attach an auto-named database for file e:wwwdata81d0493fwwwApp_DataTestDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
I checked my server forum and they said I had to name a database:
Example: Database=(unique name);
But this didn't work either.
I just tried a simple web project that has only one database and one table in SQL Express with one sqldatasource and one datagrid. It works fine on my pc but when I use the copy function in Visio Studio 2005 Pro - I can't run the site on the remote server: www.myjewelrydirect.com
I tried coping the database manually. I tried disconnecting the database before I copy it. Below is my connection statement:
<connectionStrings>
<add name="TestDB" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|TestDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
With all the comments in these forums - this must be a bug.
I have been working on this problem for over 2 weeks - HELP!
View 1 Replies
View Related
May 30, 2008
Just installed SQL Server 2005 and tested my old VS 2005 ASPnet websites, which were connected toSQL Server 2000 databases before. All my websites were created for local HTTP applications, coded with a connection string with User ID and Password. The SQL Server 2000 was configured as mixed authentication (i.e. with User ID and Password). These websites worked very well with SQL Server 2000 database server.Testing my old VS 2005 websites with SQL Server 2005 database, showed a 'Login failed for a User ''.' error msgalthough the 'Test Connection' on VS 2005 design showed a perfect query for e.g. a GridView control. I have assured that the UserID/Password were correct for Management Studio Security object'slogin and Database Security's user. The server instance was configured with mixed authentication mode. One thing I am not sure of is, when clicking the Management Studio icon, a Connection dialog showed up, asking for a Windows or MIxed authentication? I just stayed with the Windows authentication. What does the authenticationmode have anything to do with the VS 2005 website connection? Should I change to mixed authetnication mode?TIA,Jeffrey
View 3 Replies
View Related
Feb 11, 2001
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 2 Replies
View Related
Dec 19, 2005
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 5 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Apr 17, 2007
Hi,all BI experts here,
Thank you very much for your kind attention.
I am having a problem with connecting to SQL Server 2005 database from Office Business Scorecard Manager 2005 (the connection string i am using is: provider=SQLOLEDB;Data source=server_name; Initial catalog=database_name). But the connection failed. Would please any experts here shed me any light on what is the problem and how to fix it then? As there is no any forum for Office Business Scrorecard manager 2005. Therefore I post my thread here as it is related to the connection to SQL Server 2005 database engine, thought here is the best place for this question.
Thank you very in advance for your kind advices and help. And I am looking forward to hearing from you soon.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Aug 17, 2000
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 1 Replies
View Related
Feb 12, 2002
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 1 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
Nov 12, 2007
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 1 Replies
View Related
May 9, 2008
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 4 Replies
View Related
Feb 9, 2006
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 5 Replies
View Related
Jan 11, 2007
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 27 Replies
View Related
Apr 9, 2001
I'm going to be setting up SQLAgent to automatically send notifications on failure. My question is are there any "gotcha's" I might need to no about when doing this in a clustered environment? If the server were to failover, does the agent peform as it should?
Thanks in advance!
View 2 Replies
View Related
Jun 5, 2001
If you are transferring to a new server is there a good way to transfer SQLAgent jobs in bulk. Scripting out and in is one way, is there a better way
Jim
View 1 Replies
View Related
Oct 11, 2000
how can i start automatically sqlagent if it doesn't on W98 ?
thanks for answer
View 2 Replies
View Related
Aug 28, 2000
2000-08-28 15:03:15.06 ods Starting SQL Mail session...
2000-08-28 15:03:15.18 ods Error: 17903, Severity: 18, State: 1
2000-08-28 15:03:15.18 ods MAPI login failure..
2000-08-28 15:03:15.18 ods Error: 17951, Severity: 18, State: 1
2000-08-28 15:03:15.18 ods Failed to start SQL Mail session
You know where to find the MAPI settings in NT?
View 1 Replies
View Related
Apr 1, 1999
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 2 Replies
View Related
May 18, 2007
I have several target servers defined, i have a job that needs to run in a serial fashion (ie.. 1 target at at time) if every target kicks off the job at the same time it's going to bog down the disk drives... can this be serialized? or do i need to write a special routine...
View 3 Replies
View Related
Nov 6, 2007
Hello,
How can i change the location of the Sql Agent errorlog file, sqlagent.out
When i choose Properties from the Sql Agent page, i can see the location and filename, but i am not able to change it.
Is there some starup parm? And how can i apply it?
Any help is appreciated.
Best regards,
hvdtol
View 6 Replies
View Related
Jan 30, 2008
Hi all,
Configuration : MSSQL2005 SP2 on Windows 2003 server
I have a strange issue when scheduling SSIS Job.
When I schedule it, I set the owner as the domain administrator. The scheduled job can be run manually without any problem. The scheduled job run automatically at scheduled time wihtout problem.
But , for an unknow reason, after some cycle, the job failed with the following error :
The job failed. The owner () of <name of the job> does not have server access
As you can see, the owner name is blank.
If I look to the job settings : the owner is still the domain administrator.
If I try to run it manually, the job fails with the same error.
now if I just rename the job name from <name of the job> to <name of the job2> then all problems are solved.
If we let the job failing everyday, sometimes, it starts again to work properly.
We have this issue on 2 jobs.
If anyone has faced this issue : thank you for advise.
View 3 Replies
View Related
Nov 15, 2007
After you've created an SSIS package or multi-package solution, the next step is usually to deploy it to a production environment and schedule it to run at regular intervals. We took this approach at our company and scheduled the master package as a step in a SqlAgent Job. I import the dtsx packages into the server by connecting to the Integration Services connection and importing them into Stored Packages > MSDB. Next, I create a SqlAgent job via the Database Engine connection under SQL Server Agent > Jobs. I then add a step and select "SQL Server Integration Services Package" under the step Type menu. This brings up a powerful dialog that lets you set your production runtime environment parameters for the connection strings, SSIS package variables, config file path, and logging parameters.
As part of my deployment process, I wanted to run the job one time at the end of the build and check the status before deeming the build a complete success. The script is long running (several minutes) so I had to deal with the issue of polling. I had been recently using the WHILE loop in T-SQL and I also found the Waitfor Delay command. I discovered how to get to the job activity via the sp_help_jobactivity extended stored proc. The source for this proc indicates that you can get to the most recent execution of the job, including the currently running job. If the job is running, the run_status column of the resultset is null. Once the job completes, this flag is 0 for failure and 1 for success. I wanted to query the underlying tables directly so I extracted the bare minimum queries to get at the fields that I needed. I provided timeout variables so you can set your desired polling interval and timeout.
I am not sure whether this while/waitfor loop approach is very CPU-intensive or can cause locking problems. I'm hoping it does not. But it's a convenient way to use T-SQL to test for a SqlAgent job status if your build tool supports running a query against a database, which most of them do. You can always use WMI or a .Net app to achieve the same result. If someone has done this already, perhaps you can post your code sample or a link to it as a reply to this thread.
I'm assuming that other DBAs and developers responsible for production deployment and monitoring will find this script useful. I also wouldn't mind if I scored a couple MVP points . One confusion I had to deal with was that sp_help_jobactivity returns a return code of 0 or 1 with the opposite meaning as the run_status column that is in the result set. It took me a little while to figure this out since possible values for run_status were not well-documented.
Enjoy!
Norm Katz
www.ipconsulting.com
/************************************************************************************************
**
** Script Name: CheckJobStatus.sql
** Description: Checks the status of the last instance of a SqlAgentJob
** Author: Norm Katz
** Date: 11-15-2007
************************************************************************************************/
USE MSDB
GO
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_name sysname
DECLARE @jobStatus int
DECLARE @message varchar(1000)
DECLARE @jobid int
DECLARE @jobEndTime varchar(32)
DECLARE @runtime int
DECLARE @timeout int
DECLARE @delay datetime
DECLARE @delayIncr int
DECLARE @session_id int
DECLARE @jobCheckTimeoutOccurred bit
SET @job_name = 'MySqlAgentJobName'
-- initialize @delay using the datetime string, e.g., "00:00:05" for 5 seconds
-- initialize @timeout to seconds you want to wait, e.g. 300 for 5 minutes.
-- initialize @delayIncr to the integer number of seconds for @delay
SET @runtime = 0
SET @timeout = 300
SET @delay = '00:00:05'
SET @delayIncr = 5
SET @jobCheckTimeoutOccurred = 0
SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC
exec msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
print 'Checking status of ' + @job_name
WHILE @jobStatus is null
BEGIN
SELECT @job_id = ja.job_id,
@message = jh.message,
@jobStatus = jh.run_status,
@jobEndTime = stop_execution_date
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE
ja.job_id = @job_id
AND
ja.session_id = @session_id
IF @jobStatus is null
BEGIN
SET @runtime = @runtime + @delayIncr
IF @runtime > @timeout
BEGIN
SET @jobCheckTimeoutOccurred = 1
SET @jobStatus = -1
BREAK
END
Waitfor Delay @delay
END
ELSE
BREAK
END
IF @jobCheckTimeoutOccurred = 1
print 'Check for status of Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' timed out after ' + cast(@timeout as varchar(5)) + ' seconds'
ELSE
BEGIN
print 'Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' completed on ' + @jobEndTime
IF @jobStatus = 1
print 'Job Succeeded'
ELSE
print 'Job Failed with Message: ' + @message
END
SELECT @jobStatus
-- The final select query will return one of three values:
-- -1: Script timed out
-- 0: Job failed
-- 1: Job Succeeded
View 1 Replies
View Related
May 23, 2007
Help!
I installed Windows Update KB927891 for Windows Server 2003 today. Following the required reboot my three instances of SQL no longer work! Monitoring, BackUpExec and Sophos have stopped working due to SQLAgent being unable to start. Both SQL Server and DTC start successfully.
Event Viewer reports error id 103, unable to connect to server.
Looking at the logs in the SQL folders they show:
! [298] SQLServer Error: 18, SSL Security error [SQLSTATE 08001]
! [298] SQLServer Error: 772, ConnectionOpen (SECDoClientHandShake()). [SQLSTATE 01000]
! [000] Unable to connect to server 'XXXXBKUPEXEC'; SQLServerAgent cannot start
? [098] SQLServerAgent terminated (normally)
Thanks for your help in advance!
Stephen
View 1 Replies
View Related
