Replies

Hi Joe,

Yes I did try, and have switched to, using the powershell command (instead of calling powershell from a batch file). I mentioned it (it was hiding between a couple of quotes — sorry I didn’t make it clearer) a couple of threads back and was questioning the first line in that article you referred to me……wondering if it implied that since this script is acting locally, and not externally, that it would not work in this case?

I’m still hoping I can find a workaround because I’d really like to have these scripts run via the Task Scheduler (rather than using SQL Server Agent jobs) on a machine that is not tasked with SQL Server as well.

Thank you again.

gdr

Hi Joe,
I did find an answer to the “conundrum” I had with the following:

It may also be worth looking at Windows PowerShell Tip: Getting Rid of a COM Object (Once and For All).

The trick was to release each object in turn….as in the code below:

Code:

$xl = New-Object -ComObject Excel.Application
$Wkbk = $xl.Workbooks.Add()
$WkSh = $Wkbk.Worksheets.Item(1)
$xl.Visible = $True
$file = "C:JobsScriptsExcelFilesTest.xlsx"
$Wkbk.SaveAs($file)
Start-Sleep 5
$Wkbk.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WkSh) | out-null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Wkbk) | out-null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | out-null
[GC]::Collect()
Remove-Variable WkSh
Remove-Variable Wkbk
Remove-Variable xl

Thanks for your help. I’m still searching for the Task Scheduler solution though……

gdr

Hi Joe,

Do you use the noprofile option on the pwoershell command. See Running PowerShell Scripts as a Service – Power Tips – PowerShell.com.

No I hadn’t. I’ve been trying it out…I’ve gotten rid of the batch file and setup a powershell only task in Task Scheduler, with no change in results.

Code:

powershell.exe -noprofile -command "& 'C:JobsScriptsScriptFilesAgentJobStatusEmail.ps1' "

Also, the first line in the article states:

You should call powershell.exe with the options -noprofile -command like this to run a PowerShell script externally

So I wonder, is this implying that since this script is acting locally this would not work?

It may also be worth looking at Windows PowerShell Tip: Getting Rid of a COM Object (Once and For All).

Thanks for this! I’ve actually noticed this, but had not paid any attention to it. I’ve found that this works

Code:

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

but when I add

Code:

$Wkbk = $x.Workbooks.Add()
$St = $Wkbk.Worksheets.Item(1)

to make

Code:

$x = New-Object -com Excel.Application
$Wkbk = $x.Workbooks.Add()
$St = $Wkbk.Worksheets.Item(1)
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

Excel still remains resident. I can still see the Excel process running using Task Manager. Further testing found that with only the line $St = $Wkbk.Worksheets.Item(1) commented out

Code:

$x = New-Object -com Excel.Application
$Wkbk = $x.Workbooks.Add()
#$St = $Wkbk.Worksheets.Item(1)
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

The Excel object would be released and Excel closed. So adding a worksheet makes that code fail.
I’m still plugging away…..

Thank you very much.

Hi Joe,
For this I set the execution policy to unrestricted.
Thanks.

Hi Peter,

Thank you.
I do format, highlight, etc. during the excel workbook creation, but a csv file is not totally out of the question….it’s just not as pretty and harder to find the failures and/or did not runs. I also am bouncing about the idea of just gathering the data, shoving it into a table and using  Reporting Services for a scheduled, delivered report.

Thanks Hans,
I knew it had to something rather simple I’d overlooked. I didn’t even look at how I called the form. I have been out of touch for a while.

gdr

Hi Hans,
The popform in question is popfrmViews, command button . The popform that works is popfrmTables, command button . both are on the sbfrmServerDatabases.
The original db is in Acc2007 (I know I forgot to note that in the original post — been out of touch for a while), but I converted this into Acc2003 and the inability to make a selection is still present.
Thank you.

gdr

Here’s a bunch more carved melons!

They were seen here first: New Zealand

Hi Pat,
Sorry for the misunderstanding.
I’ve just now been able to test this on a virtual computer using SQL Server 2005 Express (from previous posts I am assuming this is what you are using) and Acc2003 and I am not getting the same results as you are. (right clicking to filter on the field). (btw, it seems to not matter, when right clicking on a field, entering your filter criteria into the textbox for “Filter for:” and using or not using # for dates or using, or not, double quotes around a string).
Since you’ve mentioned that you are composing a query I checked that out as well. There also I do not receive the mixed results as you are seeing. So I guess I don’t understand what is happening. What are the “sprinkling of other records as well” when filtering with LIPITOR? Are they similar or way out, such as returning ASPIRIN?
Thanks.

Hi Pat,
On further thought, right clicking on a field I am dimly remembering it does not require delimiters; i.e. quotation marks, #, *, etc. I think the odbc connector should handle the correct method to present the filter to SQL Server. The rest of the previous reply does hold though for ADO and ADP’s.

Hi Pat,
It has been some time since I’ve used Acc03 but “I think” what you are doing requires the same delimiters/symbols as does SQL Server. Dates are delimited by single quotes……’7/8/08′ (without a time part SQL will assume 12:00 midnight) as dates are string values in SQL Server and the Wild Card character is a % not the *……..”LIPITOR%”. I know SQL requires these when using ADO or an ADP, but as I said it has been some time (well a long time really) since using 2003. Acc07 changed the right-click filter on a table field significantly concerning dates. Here are a couple of links that may help you a bit. TechRepublic and Utter Access

Hi Pat,
Open Internet Information Services Manager through Administrative Tools (there is either a short cut to Administrative Tools via the Start Menu or through the Control Panel). Open the tree on the left until you see Default Web Site. Then if it is stopped you will see (Stopped) and if so, right click on Default Web Site and choose Start. Actually, it might be a good idea to restart IIS. You can do that through the same right click menu or open a Command Prompt and type iisreset.
This may all be moot if you’re unable to restart SQL Server. Can you restart it by opening SQL Configuration Manager? While you’re there what condition does it show for Reporting Services?

Glad to hear you’ve got a handle on the problem. What was the permissions issue, if I may ask?

Hi Pat,
I apologize, I’ve been at the busy state as well and haven’t been able to make the time to answer your previous reply.
If you open the SQL Server Configuration Manager () under SQL Server 2005 Services are SQL Server Reporting Services listed as “Running”? If not right click and select Start. Also, since that Run command is an IIS command, check and see if your Default Web Site is running. If it isn’t you will see (Stopped) beside it, so right click it and select Start.

As a last resort
If all this, including the previous suggestions, haven’t worked, I’m tempted to suggest to uninstall it all and try, try again and start from as close to scratch as you possibly can. I’ve had to do that as well until I’ve gotten the proper precedure down. Rather than chase down permissions errors, etc. I just blow everything away. I remove everything related to SQL Server through Add or Remove Programs and delete it’s folders (C:Program FilesMicrosoft SQL Server) which will also delete the 2 Reporting Services databases, then delete the 2 Reporting Services’ virtual directories in IIS and I’ve even uninstalled IIS and re-installed it. I know this can seem as a rather harse “fix”, but when I’m finished and it does work, I am reasonably certain that I’ve got a clean install and should not have any further problems.