We help IT Professionals succeed at work.

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

Medium Priority
1,042 Views
Last Modified: 2012-05-06
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.
Comment
Watch Question

Commented:
What is the saveas path?

Author

Commented:
E:\programs\BillBouncer\Exception Reports

Commented:
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

Author

Commented:
I added them and the error still occurs.

Commented:
Are you using a FileFormat parameter by any chance? Can you list the whole line of code relating to .saveas method??

Author

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

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
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'

Author

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

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
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 ?

Author

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

Author

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.
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
< 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

Author

Commented:
i run it on the server.

Author

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
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
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
 

Author

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.
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
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\*.*'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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!
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
could you try to use  UNC name as path ?
xp_cmdshell  " cscript.exe  \\yourservername\c$\programs\BillBouncer\Ma
ilException.vbs'
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.