Solved

MSSQL Jobs in SQL

Posted on 2008-06-15
3
337 Views
Last Modified: 2008-07-23
How do you make a SQL job using SQL not the GUI?
0
Comment
Question by:AceSG
  • 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:ScottPletcher
ID: 21794580

EXEC msdb.dbo.sp_add_job ...

EXEC msdb.dbo.sp_add_jobstep ...

EXEC msdb.dbo.sp_add_jobschedule ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

943 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now