Solved

MSSQL Jobs in SQL

Posted on 2008-06-15
3
365 Views
Last Modified: 2008-07-23
How do you make a SQL job using SQL not the GUI?
0
Comment
Question by:AceSG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
Serge Fournier earned 500 total points
ID: 21790590
just send the command to sql like normal

here is an example:
CODESNIPPET: .vbs script that will create a clusterized index for a list of database
you must set commandtimout very high if it's for a backup (in seconds)

backup string to send to sql server:
BACKUP DATABASE [FLINK_DATA2] TO  DISK = N'C:\DATASQL.BAK\FLINK_DATA2_BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'FLINK_DATA2 backup',  NOSKIP ,  STATS = 10,  NOFORMAT;
USE FLINK_DATA2;
BACKUP log FLINK_DATA2 WITH truncate_only DBCC shrinkfile(FLINK_DATA2_Log, 0);
DBCC shrinkfile(FLINK_DATA2, 10)
NOMLOG = "z_log_CREE_CLUSTERS.txt"
 
Set oShell = WScript.CreateObject("WScript.Shell")
Set con_02 = CreateObject("ADODB.Connection")
'DIM TAG(500)
 
'=== PATH OU ON EST EN CE MOMENT
Set objFSO = wscript.CreateObject("Scripting.FileSystemObject")
thepath=WScript.ScriptFullName
p=instrRev(thepath,"\")
basedir=left(thepath,p)
 
'=== SERVEUR SQL
sql1 = "10.0.4.8\sql_mpro"
'=== TABLE SOURCE SQL
SQLS = "FLINK_DATA2"
 
con_02.ConnectionString = "Driver={SQL Server};Server=" & sql1 & ";Uid=sa;Pwd="
con_02.Open
con_02.commandtimeout=1200
 
'=== FICHIER DE RAPPORT
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile = objFileSystem.OpenTextFile(base_dir & NOMLOG, 2, true)
 
'A="M1607"
 
 
TABLE01 = "ALLTAB01"
'=== REQUETE POUR LIRE LES ELEMENTS DE LA TABLE (NOMS DE TOUTES LES TABLES)
SQL = "SELECT NAME01 FROM " & SQLS &".DBO." & TABLE01 & " ORDER BY NAME01"
'=== dim tag as new recordset
Set tag01 = con_02.Execute(sql)
 
I2=0
MAXI2 = 2000
 
while not(tag01.eof)
'=== boucle pour en faire 40
 '=== ENLEVER REM POUR TEST
 WHILE I2<MAXI2
   '=== boucle pour le premier au dernier
   for each a in tag01.fields
   
      SQL = "CREATE CLUSTERED INDEX " & A & "_INDEX ON FLINK_DATA2.DBO." & A & "(fltime)"
      objOutputFile.WriteLine date & " " & time & "  " & SQL
      ON ERROR RESUME NEXT
      Set tag = con_02.Execute(sql)
      objOutputFile.WriteLine date & " " & time & " NOM TABLE  " & A
      If Err.number <> 0 Then   
         objOutputFile.WriteLine date & " " & time & " ERREUR DE CREATION D'INDEX " & A
         objOutputFile.WriteLine date & " " & time & ERR.DESCRIPTION
      END IF
      ON ERROR GOTO 0
 
   NEXT
   IF I2<MAXI2 THEN
      tag01.movenext
   END IF
   I2=I2+1
 '=== ENLEVE REM POUR TEST
 WEND
   IF I2>=MAXI2 THEN 
      tag01.movenext
   END IF
WEND
 
objOutputFile.WriteLine date & " " & time & " fin de la creation des indexs"
 
oShell.Run "notepad.exe " & base_dir & NOMLOG, 1, FALSE
 
WSCRIPT.QUIT
 
'------------------------------------------------------
'--- CODE A CONSERVER POUR COUPER DES STRING
'--- DE SOURCE DE DONNEES MONITOR PRO
 
If Err.number <> 0 Then   
 
   B = Err.Description
   'MsgBox Err.Source & "-->" & B & " " & SQL, , "Error"
   objOutputFile.WriteLine date & " " & time & "  " & Err.Description
 
   '===STRING ERROR
   '10/27/2006 11:21:12 AM  [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create more than one
   'clustered index on table 'M1607'. Drop the existing clustered index 'PK__M1607__5B0E7E4A' before
   'creating another.
   
   C = INSTR(1,B,"'") +1
   C = INSTR(C,B,"'") +1
   C = INSTR(C,B,"'") +1
   D = MID(B,C,INSTR(C,B,"'")-C)
   'MSGBOX(D)
   
   ON ERROR GOTO 0
   SQL = "DROP INDEX " & A & "." & D
   'SQL = "ALTER INDEX " & D & " ON FLINK_DATA2.DBO." & A & " RENAME TO TEST"
   'MSGBOX(SQL)
   objOutputFile.WriteLine date & " " & time & "  " & SQL
   Set tag = con_02.Execute(sql)
 
   err.clear
   wscript.quit
End If
 
SQL = "CREATE CLUSTERED INDEX " & A & "_INDEX ON FLINK_DATA2.DBO." & A & "(fltime)"
Set tag = con_02.Execute(sql)

Open in new window

0
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21790592
lots of script clean up to do tho

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21794580

EXEC msdb.dbo.sp_add_job ...

EXEC msdb.dbo.sp_add_jobstep ...

EXEC msdb.dbo.sp_add_jobschedule ...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question