[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

MSSQL Jobs in SQL

How do you make a SQL job using SQL not the GUI?
0
AceSG
Asked:
AceSG
  • 2
1 Solution
 
Serge FournierAnalyst ProgrammerCommented:
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
 
Serge FournierAnalyst ProgrammerCommented:
lots of script clean up to do tho

0
 
Scott PletcherSenior DBACommented:

EXEC msdb.dbo.sp_add_job ...

EXEC msdb.dbo.sp_add_jobstep ...

EXEC msdb.dbo.sp_add_jobschedule ...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now