May 8, 2009 at 1:42 pm
Greetings everyone.
I have been presented with a strange request that I am trying to figure out how to implement without installing SSMS or training the person in SQL Server who is making the request.
Our manager in the Finance department at month end needs to update the data in our data warehouse several times a day. Right now I created a localized process that I can execute at will to do this. Problem is, I am not available 24/7, and there are times they need to do this when I am not around.
So my question is; Is there some sort of trick I can use to allow this person to execute this job without them having to install SSMS on their machine?
Link to my blog http://notyelf.com/
May 8, 2009 at 2:05 pm
I have used trigger files for things like this in the past. Not sure if it is the best way, but it works. You can run your job (say every 30 minutes). The first step in the job will look for a file. If the file is there, the job will move on the the next step. If the file is not there, the job will quit with success.
Set the Step Type to (CmdExec) and enter the Dir command follwed by the file path...
Dir C:\Filename.txt
If a file is there the step will succeed; if not it will fail.
You can also use sp_startjob if you have a way for the user to execute it.
May 8, 2009 at 2:13 pm
Open VBA in Excel draw a command button and in the sub routine insert this code:
Sub ExecuteSQLJOB()
Dim Chan As Variant
'Open a channel to a SQL Server data source
Chan = SQLOpen("DSN=SQLServer") --you must create this entry in ODBC admin.
'Execute the stored procedure "sp_who" and return the
'results to the activecell on the active sheet.
SQLExecQuery Chan, "EXEC msdb.dbo.sp_start_job N'YOUR SQL JOB';"
SQLRetrieve Chan, ActiveCell
'Terminate the channel
SQLClose Chan
End Sub
Make sure you assign proper permissions.
http://support.microsoft.com/kb/124218
May 8, 2009 at 2:22 pm
Alex, I really like that idea.
The only problem is, I have no idea how to create an ODBC connection or how to set up those privileges to it 🙂
Link to my blog http://notyelf.com/
May 8, 2009 at 2:34 pm
shannonjk (5/8/2009)
Alex, I really like that idea.The only problem is, I have no idea how to create an ODBC connection or how to set up those privileges to it 🙂
Not a problem Here is DSN-less connection string.
Private Sub CommandButton1_Click()
Call ExecuteSQLJOB
End Sub
Sub ExecuteSQLJOB()
Dim c As ADODB.Connection
Set c = New ADODB.Connection
c.Open "Driver={SQL Server};" & _
"Server=YOURSQLSERVERIPADDRESS;" & _
"Database=master;" & _
"Uid=sa;" & _
"Pwd=blahblah"
c.Execute "EXEC msdb.dbo.sp_start_job N'YOURJOBNAME';"
c.Close
Set c = Nothing
End Sub
May 8, 2009 at 4:34 pm
Works like a charm. You da man!
Link to my blog http://notyelf.com/
February 3, 2010 at 7:15 am
Are you aware of the security risks? You could be exposing the system-administrator credentials to an end-user. Besides that, this solution works only when your instance is running in mixed-mode authentication. If you are worried about any of this, please let me know.
Kind Regards,
Wim
February 3, 2010 at 10:02 am
Wow resurrection from the dead 🙂
Yes I am aware of the security risks which is why I locked the excel workbook down hard and passworded the code (eventhough no one here can program in vb but just in case). I also made a windows user account on the server with limited privileges that this connection connects to. I have it locked down very tight for this reason so no worries 🙂
Link to my blog http://notyelf.com/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply