[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

xp_cmdshell cannot save files when SQL 2005 is running on SBS 2008

I have a stored procedure in SQL 2005 that uses XP_cmdshell to execute a VB Script.  The script creates an excel spreadsheet and emails it to users.

If I run the script manually from the command prompt it works perfectly.
If I use the stored procedure it fails with the error:
e:\programs\BillBouncer\MailException.vbs(138, 1) Microsoft Office Excel: SaveAs method of Workbook class failed

I just migrated from SBS 2003 to SBS 2008.  In 2003 this worked properly.  I thought it was a permissions issue and have tried changing the permissions on the directory and the script so that everyone has full access.  I have also changed the user that SQL runs as to Local System and a domain admin.

The problem persists.

I configured the account for the xp_cmdshell proxy user as well, no change.
To test is the problem was with SQL Server or something else I used xp_cmdshell to run an executable that also creates a file and saves it, this aslo fails through SQL but works when run outside of SQL.

Is there a setting withing SQL, or SBS 2008 to allow xp_cmdshell to save files on the server?

Any help would be greatly appreciated.  The migration was totally successfull with this one exception.
0
tomcahill
Asked:
tomcahill
  • 10
  • 6
  • 3
1 Solution
 
reb73Commented:
What is the saveas path?
0
 
tomcahillAuthor Commented:
E:\programs\BillBouncer\Exception Reports
0
 
reb73Commented:
It could be something to do with an alert being thrown up during the save..

Can you add the following property settings for the Excel.Application object?

.DisplayAlerts = False
.ScreenUpdating = False
.AlertBeforeOverwriting = False

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
tomcahillAuthor Commented:
I added them and the error still occurs.
0
 
reb73Commented:
Are you using a FileFormat parameter by any chance? Can you list the whole line of code relating to .saveas method??

0
 
tomcahillAuthor Commented:
Here is the entire script.

I think it has somethign to do with SQL Server 2005 running on Server 2008 though because when I run it from a command prompt it works perfectly. It also ran perfectly under Server 2003.
Const adUseClient = 3
Const xlLandScape = 2
 
 
''These should all be arguments
If WScript.Arguments.Count <> 4 Then
	WScript.Echo "Usage: " & Chr(10) & "  MailException <Trailer> <EmailList> <Message> <Subject> " & Chr(10) & Chr(10) & _
                      "EmailList is delimited with "";""s" & Chr(10) & "Message and Subject uses ""="" in place of spaces"	
	WScript.Quit
End If
Current_Trailer = Replace(WScript.Arguments(0), "=", " ")
sMailRcptToList = WScript.Arguments(1)
sMailMessage = Replace(WScript.Arguments(2), "=", " ")
sMailSubject = sMailMessage
 
 
sXLFilePath = "E:\programs\BillBouncer\Exception Reports\"
sXLFileName = CStr("CCD_EXR" & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now) & ".xlsx")
 
'aMailRcptToList = Split(sMailRcptToList, ";")
 
 
Set oCxn = CreateObject("ADODB.Connection")
Set oRS0 = CreateObject("ADODB.Recordset")
Set oXlA = CreateObject("Excel.Application")
Set objMessage = CreateObject("CDO.message")
 
oXlA.DisplayAlerts = False
oXlA.ScreenUpdating = False
oXlA.AlertBeforeOverwriting = False
 
Set oXlS = oXlA.WorkBooks.Add.Sheets("Sheet1")
oCxn.ConnectionTimeout = 0
oCxn.CommandTimeout = 0
 
oCxn.Open "server=a_server;trusted_connection=True;Driver={SQL Server};Database=a_database;Connect Timeout=100000"
 
oRS0.CursorLocation = adUseClient
Sql = "Select a.UCCN,a.MRGN,coalesce(a.INVN,'') as INVN,coalesce(CARN,'') as CARN,coalesce(NCAR,0) as NCAR,coalesce(a.TRLN,''),coalesce(a.LANE,0) From SerialMaster a INNER JOIN OrderHEader b on a.mrgn=b.mrgn Where (a.STSC='0050' and b.TRLN='" & Current_Trailer & "') or (b.TRLN='" & Current_Trailer & "' and a.TRLN<>'" & Current_Trailer & "') "
oRS0.Open Sql, oCxn
 
Set oRS0.ActiveConnection = Nothing
 
oXlS.Cells(1, 1) = "UCC Numbers Expected & Not Received on " & Current_Trailer & "."
oXlS.Cells(1, 1).Font.Size = 20
 
oXlS.Cells(3, 1) = "UCC#"
oXlS.Cells(3, 2) = "Merge#"
oXlS.Cells(3, 3) = "Invoice#"
oXlS.Cells(3, 4) = "Carrier"
oXlS.Cells(3, 5) = "# pcs"
oXlS.Cells(3, 6) = "Act Trailer"
oXlS.Cells(3, 7) = "Diverted To"
oXlS.Cells(3, 8) = "Comments"
oXlS.Range("A3:F3").Font.Bold = True
 
oXlS.Columns("A").ColumnWidth = 20.14
oXlS.Columns("B").ColumnWidth = 13.57
oXlS.Columns("B").NumberFormat = "00000"
oXlS.Columns("C").ColumnWidth = 9.29
oXlS.Columns("D").ColumnWidth = 15.57
oXlS.columns("E").ColumnWidth = 4.86
oXlS.Columns("F").ColumnWidth = 13.29
oXlS.Columns("G").ColumnWidth = 10.71
oXlS.Columns("H").ColumnWidth = 27
 
 
oXlS.PageSetup.Orientation = xlLandscape
 
LowestRow = 4
 
If oRS0.EOF Then
    oXlS.Cells(5, 1) = "NO EXCEPTIONS"
    OXlS.Cells(5, 1).Font.Bold = True
End If
Do Until oRS0.EOF
    For i = 0 to oRS0.Fields.Count - 1
        oXlS.Cells(LowestRow, i + 1) = oRS0.Fields(i)
    Next
    oRS0.MoveNext
    LowestRow = LowestRow + 1
Loop
 
oRS0.Close
 
oXlS.Name = "Expcected, Not Received"
 
Set oXlS = oXlA.WorkBooks(1).Sheets("Sheet2")
 
oRS0.Open "Select a.UCCN,a.MRGN,coalesce(a.INVN,'') as INVN,coalesce(CARN,'') " & _
             "as CARN,coalesce(NCAR,'') as NCAR,coalesce(b.TRLN,'') as TRLN,coalesce(a.LANE,'') as LANE " & _
           "From SerialMaster a LEFT JOIN OrderHeader b ON a.MRGN=b.MRGN " & _ 
           "Where a.STSC in('0100','0101') AND a.TRLN='" & Current_Trailer & _
           "' AND (b.TRLN<>a.TRLN or b.TRLN is Null) ", oCxn
Set oRS0.ActiveConnection = Nothing
 
oXlS.Cells(1, 1) = "UCC Numbers Received & Not Expected on " & Current_Trailer & "."
oXlS.Cells(1, 1).Font.Size = 20
 
oXlS.Cells(3, 1) = "UCC#"
oXlS.Cells(3, 2) = "Merge#"
oXlS.Cells(3, 3) = "Invoice#"
oXlS.Cells(3, 4) = "Carrier"
oXlS.Cells(3, 5) = "# pcs"
oXlS.cells(3, 6) = "Exp Trailer"
oXls.Cells(3, 7) = "Diverted To"
oXlS.Cells(3, 8) = "Comments"
oXlS.Range("A3:H3").Font.Bold = True
 
oXlS.Columns("A").ColumnWidth = 20.14
oXlS.Columns("B").ColumnWidth = 13.57
oXlS.Columns("B").NumberFormat = "00000"
oXlS.Columns("C").ColumnWidth = 9.29
oXlS.Columns("D").ColumnWidth = 15.57
oXlS.columns("E").ColumnWidth = 4.86
oXlS.Columns("F").ColumnWidth = 13.29
oXlS.Columns("G").ColumnWidth = 10.71
oXlS.Columns("H").ColumnWidth = 27
 
oXlS.PageSetup.Orientation = xlLandscape
 
LowestRow = 4
 
If oRS0.EOF Then
    oXlS.Cells(5, 1) = "NO EXCEPTIONS"
    OXlS.Cells(5, 1).Font.Bold = True
End If
Do Until oRS0.EOF
    For i = 0 to oRS0.Fields.Count-1
	oXlS.Cells(LowestRow, i + 1) = oRS0.Fields(i)
    Next
    oRS0.MoveNext
    LowestRow = LowestRow + 1
Loop
 
oRS0.Close
 
oXlS.Name = "Received, Not Expected"
 
 
 
oXlA.Workbooks(1).SaveAs sXLFilePath & SxLFileName
 
Set oRS0 = Nothing
oCxn.Close
Set oCxn = Nothing
Set oXlS = Nothing
oXlA.Quit
Set oXlA = Nothing
 
 
 
 
 
	objMessage.Subject = CStr(sMailSubject)
	objMessage.From = "tomcahill@domain.com"
	objMessage.To = CStr(sMailRcptToList)
	objMessage.TextBody = CStr(sMailMessage)
	objMEssage.AddAttachment  CStr(SxLFilePath) & cstr(SxLFileName)
 
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
 
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp_server"
 
 
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 3
 
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "sqluser@domain.local"
'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
 
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
 
objMessage.Configuration.Fields.Update
 
'==End remote SMTP server configuration section==
 
	objMessage.Send
 
'Next
 
 
 
Set oSmk = Nothing

Open in new window

0
 
Eugene ZCommented:
check the sql server service account permission that used is used to run xp_cmdshell  (make surre the accout has local admin and sql  sa rights)
also post all code line that you use to run vbs from xp_cmdshell
try
xp_cmdshell  " cscript.exe  e:\programs\BillBouncer\MailException.vbs'
0
 
tomcahillAuthor Commented:
The account has both sa and local admin rights.

DECLARE @DistList NVARCHAR(255)
DECLARE @cmdString NVARCHAR(700)
DECLARE @DistList2 NVARCHAR(255)
DECLARE @cmdString2 NVARCHAR(700)
DECLARE @trln Char(25)
 
 
SET @trln = '00WSTCST0807'
 
SELECT @DistList=RcptList FROM DistributionList WHERE Report='ExceptionReport'
SET @cmdString='cscript e:\programs\BillBouncer\MailException.vbs ' + Replace(@trln, ' ', '=') + ' ' + @DistList + ' CCD=Exception=Report=For=Trailer=' + Replace(@trln, ' ', '=') + '  Please=find=exception=reports=attached'
exec master.dbo.xp_cmdshell @cmdString
 
SELECT @DistList2=RcptList FROM DistributionList WHERE Report='SeasonalHoldReport  '
SET @cmdString2='cscript e:\programs\BillBouncer\SeasonalHold.vbs ' + Replace(@trln, ' ', '=') + ' ' + @DistList2 + ' CCD=Seasonal=Hold/Trailer#=' + Replace(@trln, ' ', '=') + ' Seasonal=Hold/Trailer#=' + Replace(@trln, ' ', '=')
exec master.dbo.xp_cmdshell @cmdString2

Open in new window

0
 
Eugene ZCommented:
did you get an error?
if "yes "
try ;

exec master.dbo.xp_cmdshell  'dir e:\programs\BillBouncer\*.*'

BTW: is "e:\programs\BillBouncer\" your PC folder or sql server ?

0
 
tomcahillAuthor Commented:
the folder is on the sql server.

so you are saying add the line exec master.dbo.xp_cmdshell  'dir e:\programs\BillBouncer\*.*'
before exec master.dbo.xp_cmdshell @cmdString
0
 
tomcahillAuthor Commented:
the error I get is from the .vbs, it says throws an exception when trying to save the file.

However, if I run it manually, not from a stored procedure, it works perfectly.
0
 
Eugene ZCommented:
< if I run it manually...

how do you run? did you login  on the box and ran from cmd or you did it from your pc?
you may not have Excel versions (on server and your PC) related problems : not sql server ...

                         SaveAs method of Workbook class failed
http://www.petri.co.il/forums/showthread.php?t=27586
0
 
tomcahillAuthor Commented:
i run it on the server.
0
 
tomcahillAuthor Commented:
I don't think this is a problem with SQL btw, I think it is something in Server 2008 that is the problem.

I ran this without problem on server 2003 running SQL 2008.

I was not able to run this on server 2008 running SQL 2008 or SQL 2005 I tried both 64bit and 32bit
0
 
Eugene ZCommented:
Did you login on the server (Box)  where you got the error and did you run the VBS from CMD there ? Did you get error?
did you read link about Excel different version?

                         SaveAs method of Workbook class failed
http://www.petri.co.il/forums/showthread.php?t=27586 
 
 -- it is probably your errors a source of problem -- > Microsoft Office Excel: SaveAs method of Workbook class failed
 
0
 
tomcahillAuthor Commented:
Yes I run it from the server.  I did read about the excel versions as well.  This is Excel 2007 and always has been, that is not differerent.

I installed a server 2003 box runniing sql 2008 and this runs without problems, without changing any code.

The only difference between a working configuration and a non-working one is Server 2008.
0
 
Eugene ZCommented:
Hmm
as you said - it looks like some win2008 security settings that prevent usage of the method
--
Did you try to run the  just - by using same login that you used for main task?
--just
exec master.dbo.xp_cmdshell  'dir e:\programs\BillBouncer\*.*'
0
 
tomcahillAuthor Commented:
Yup.
For now I am going to leave it running on a Server 2003 member server.
One more box (well virtual box) to admin but at least the reports work!
0
 
Eugene ZCommented:
could you try to use  UNC name as path ?
xp_cmdshell  " cscript.exe  \\yourservername\c$\programs\BillBouncer\Ma
ilException.vbs'
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 10
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now