Can't open script file in SQL Server 2008 R2 job

Question

When I try to open a SQL script file on my local machine from the SQL Server Job configuration window, I get the following error:

Access to the path 'C:\temp\tbl_MiscInfo.sql' is denied. (mscorlib)

Program Location:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.IO.File.Open(String path, FileMode mode) at Microsoft.SqlServer.Management.SqlManagerUI.TSQLJobSubSystemDefinition.openFile_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

To be clear, here is a screen shot of the action I'm trying to perform:

This seems to be a permissions issue, but nothing I try seems to work.

The sqlagent on my machine is using a user that has admin privileges, I've given the login for the job a user credential that has admin privileges, and I've set the "run as user" option to a user mapped to the login with admin privileges.

Any suggestions?

Answer 1

It doesn't work that way. When you click "Open..." all it does is take the contents of the file you specify and dump them into the Command: dialog - this means the script is static and will not reflect any changes you make after you save the job.

If you want it to use a .sql file at runtime you'll need to look at using a CmdExec step type (where you can call SQLCmd outside the SQL Server process) or a PowerShell command. Alternatively, the job step could use xp_cmdshell to read in the contents of the .sql script (using the type command) and execute the contents as dynamic SQL.