Hi

I am trying to get the following powershell code to work in the SQL Server Powershell window but I get the error 'Invoke-Sqlcmd : Must declare variable "@" ' .

>$audits = Invoke-Sqlcmd -ServerInstance DBSVR -Database Test -Query "SELECT RUNCODE FROM AUDIT WHERE RUNDATE = '2010-04-13 06:07:00' " | Select-object RUNCODE

>foreach ($runcode in $audits) {Invoke-Sqlcmd -ServerInstance DBSVR -Database Test -Query "SELECT PRACTICECODE FROM AUDIT WHERE RUNCODE = $runcode" | Select-object PRACTICECODE}

Once I have this working I would then like to use the $runcode variable with a stored procedure using Invoke-Sqlcmd.

What am I doing wrong? Once this is working how would I run the code in a Powershell script?

It looks like this question was asked and answered in the Windows PowerShell forum:

http://social.technet.microsoft.com...5bda28a3e9

Hi

The suggestion in the Windows Powershell forum didn't work. I am still looking for a solution to my problem.

Is RUNCODE a string datatype?

I'm able to get results running a test against the pubs sample database using your code with slight modifications:

$audits = Invoke-Sqlcmd -ServerInstance sql2k8 -Database pubs -Query "Select TOP 10 au_id from authors"

foreach($runcode in $audits) {Invoke-Sqlcmd -ServerInstance sql2k8 -Database pubs -Query "Select au_lname from authors where au_id = '$($runcode.au_id)'"}

Hi

Runcode is nvarchar datatype.

Thanks for your help. That worked.

I needed to add the quotes round $($runcode.au_id) as you did in your example.

'$($runcode.au_id)'

Could you please tell me how I now add this code to a Powershell script so I don't need to run it in the SQL Server Powershell window. Do I need to add some extra code to the start of the script to register DLLs?

Just so I undertand are trying to run it in regular PowerShell and not sqlps? Do you want parameterize 2010-04-13 06:07:00?

I wanted to be able to schedule the code to run on a regular basis or run it manually without opening sqlps or the regular Powershell window. It would be great to be able to parameterize the date in the queries.

Am I better to do any of this with SQLPSX?

Thanks

If you're using SQL Server 2008 or 2008 R2 you can create a SQL Server Agent job with a PowerShell jobstep and directly paste your working script into the command text box.

If you're using SQL Server 2005 or 2000. Create a SQL Server Agent job and create an Operating System job step. In the command text box specify:

powershell -command "your commands go here"

Alternativley you can save you script as ps1 text file and specify:

powershell -command "C:\pathtoscriptfile\yourscriptfile.ps1"

If you want the date to be driven by the current date time you can do something like this:

WHERE RUNDATE BETWEEN '$((Get-Date).ToShortDateString())' AND '$((Get-Date).AddDays(1).ToShortDateString())'

Note: Make sure Powershell's execution policy is not set to restricted:

Get-ExecutionPolicy
Set-ExecutionPolicy RemoteSigned

Thanks for you help.

We are using SQL Server 2008 so I will create a SQL Server Agent job.

Rob

Hi

I worked out how to run the code as a Powershell script. When I run the script I get the following error but the script has run correctly:

Invoke-Sqlcmd : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Is there a way to extend the Timeout period?

Unfortunately the built-in cmdlet invoke-sqlcmd has bug where the timeout value cannot be extended, however it is trivial to implement your own function. See this blog post and my comments for details:

http://scarydba.wordpress.com/2010/...o-problem/

Thanks Chad.