Automation Error

Hello,

Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.

Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!

probably user account problem - have you tried to run manually under the same user account as sql agent is running?

|||In reviewing all of the logs it made it through the entire process before reporting failure. The very last process to run is a DBCC CHECKDB task which shows up in the Server logs. I will double check though. Thank you for your input.

Automating Database Tuning Advisor

Hello all

Im looking for a way of automating the Database Tuning Advisor tool of SQL Server 2005 with the help of sql agent so that i can schedule it on a regular basis to scan a trace file and then make it export the results in a text file.

The system would need no human interaction at all.

Is it possible?

I just found out this morning that yes it is possible for someone to automate the analysis and i thought i would share that information with you.

You have to use the command "dta" wich run the analysis on the server.

It let you choose the file name (wich can be a trc,sql or xml), the session name, the file it will export the result to and a lot of other paremeter.

I just tried it and it work as fine as running the application directly.

To get more information about that command :

quote:

---

Dta -?

---

Dalesql

Automated backups in SQL Express?

I've realize that the SQL Agent service is no longer available now that i've upgraded from MSDE 2000 to SQL Server Express.

I would like to find out the easiest way I could go about creating an automated backup solution for SQL Server Express. Previously I created Jobs in the SQL Agent Service using SQL DMO which is not longer possible.

Has anyone any suggestions that would be easy to setup or any samples that I could use? Maybe using SMO or some other method.

Thanks.You could use the Windows Scheduled Tasks to execute a backup script using the SQLCMD program with a T-SQL script.

|||Automating Database maintenance in SQL 2005 Express Edition Part I

Automating Database maintenance in SQL 2005 Express Edition Part II

I should say that it does seem sqlmaint is included with SQLExpress so you could equally use that. Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.

|||

I suppose we have to build all of the error handling, alerting, logging now that MS has stripped the SQL Agent Scheduler from Express?

What will probably happen is nothing, leading to a general market trend of disliking any small application using mssql 2005 express since the agent features no longer exist and nobody wants to pay to create something like it, (without notifications, alerts, system event logging, etc., distrust and fear occur).

Automated backups in SQL Express?

I've realize that the SQL Agent service is no longer available now that i've upgraded from MSDE 2000 to SQL Server Express.

I would like to find out the easiest way I could go about creating an automated backup solution for SQL Server Express. Previously I created Jobs in the SQL Agent Service using SQL DMO which is not longer possible.

Has anyone any suggestions that would be easy to setup or any samples that I could use? Maybe using SMO or some other method.

Thanks.You could use the Windows Scheduled Tasks to execute a backup script using the SQLCMD program with a T-SQL script.

|||Automating Database maintenance in SQL 2005 Express Edition Part I

Automating Database maintenance in SQL 2005 Express Edition Part II

I should say that it does seem sqlmaint is included with SQLExpress so you could equally use that. Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.

|||

I suppose we have to build all of the error handling, alerting, logging now that MS has stripped the SQL Agent Scheduler from Express?

What will probably happen is nothing, leading to a general market trend of disliking any small application using mssql 2005 express since the agent features no longer exist and nobody wants to pay to create something like it, (without notifications, alerts, system event logging, etc., distrust and fear occur).

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIAHI
Use SQL-DMO:
'_______________________________________________________
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjelölve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
letöröljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'__________________________________________________________________
Andras Jakus MCDBA
"pk" wrote:
> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
> HI
> Use SQL-DMO:
> '_______________________________________________________
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjelölve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> letöröljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '__________________________________________________________________
> Andras Jakus MCDBA
> "pk" wrote:
> > In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> > I can manaully script out all exisiting sql agent jobs, and keep for backup.
> >
> > How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> >
> > TIA
> >

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIA
HI
Use SQL-DMO:
'_________________________________________________ ______
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjel?lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
let?r?ljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'_________________________________________________ _________________
Andras Jakus MCDBA
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>
|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> Use SQL-DMO:
> '_________________________________________________ ______
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjel?lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> let?r?ljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '_________________________________________________ _________________
> Andras Jakus MCDBA
> "pk" wrote:
|||Have look at
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It shows how to schedule scripting most objects in the database and save
them in sourcesafe (although I haven't got round to automating that yet -
suspect I will soon).
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>