Solved

MSSQL Jobs in SQL

Posted on 2008-06-15
3
327 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 11

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 11

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
Need Counts 11 43
Sort by Month and Year - SQL 3 23
combine an MS SQL string in Idera DM 9 32
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

12 Experts available now in Live!

Get 1:1 Help Now