?
Solved

MSSQL Jobs in SQL

Posted on 2008-06-15
3
Medium Priority
?
375 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 1500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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