I have this VBA Code to run and script SQL Server jobs on my local machine. How can I modify it to connect to a network SQL Server from my workstation and script the jobs on the network SQL server? I do have Administrative permissions. I know I need a connection string, but I dont have much expierence and need to start learning. You help is greatly appreciated
Public Sub SQLScriptJobs()
Dim oSS
Set oSS = CreateObject("SQLDMO.SQLServer"
Dim lcstring As String
Dim lcServer As String
Dim oJob As Object
Set oJob = CreateObject("SQLDMO.Job"
Dim lcFile As String
lcServer = "LocalServer"
oSS.LoginSecure = True
oSS.Connect lcServer
lcFile = "<My Computer here >\My Documents\DB_JobScripts\ScriptJobs" & "_" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".sql"
For Each oJob In oSS.JobServer.Jobs
lcstring = lcstring & "-- ***** " & oJob.Name & " ****** " & vbCrLf & vbCrLf
lcstring = lcstring & oJob.Script
Next
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject"
Set txtfile = fso.CreateTextFile(lcFile, True)
txtfile.Write (lcstring)
txtfile.Close
Set oSS = Nothing
Set fso = Nothing
End Sub
Public Sub SQLScriptJobs()
Dim oSS
Set oSS = CreateObject("SQLDMO.SQLServer"
Dim lcstring As String
Dim lcServer As String
Dim oJob As Object
Set oJob = CreateObject("SQLDMO.Job"
Dim lcFile As String
lcServer = "LocalServer"
oSS.LoginSecure = True
oSS.Connect lcServer
lcFile = "<My Computer here >\My Documents\DB_JobScripts\ScriptJobs" & "_" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".sql"
For Each oJob In oSS.JobServer.Jobs
lcstring = lcstring & "-- ***** " & oJob.Name & " ****** " & vbCrLf & vbCrLf
lcstring = lcstring & oJob.Script
Next
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject"
Set txtfile = fso.CreateTextFile(lcFile, True)
txtfile.Write (lcstring)
txtfile.Close
Set oSS = Nothing
Set fso = Nothing
End Sub