Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection

Oct 23, 2012 at 6:07 PM

Okay, so I think there might be an issue with how a connection is being used/passed.

Version SQLPSX 2.3.2.1

Powershell 2.0

 

Here is what I'm doing.

 Set-StrictMode -Version latest

[String] $Server = $null;

[String] $DbName = $null;

$Server = ;SomeServer';

$DbName = 'SomeDatabaseName';

$WorkConn = new-connection $Server -database $DbName;
Which creats a connection using integrated security, which is what I want.


invoke-storedprocedure -storedProcName 'Proc' -connection $WorkConn -parameters @{} -timeout 3600

When the invoke-storedprocedure is fired executes $cmd=new-sqlcommand $PSBoundParameters, which then returns the following error:

The variable '$database' cannot be retrieved because it has not been set. 

I do not understand what is going on here as I've set a connection with a database but it is not being passed.  Any help would be great.

Jim

 

 

Coordinator
Oct 26, 2012 at 4:37 PM

the problem is in line 291 of the adolib.psm1 file, in the new-sqlcommand function.

 

In 2.3.1, it reads as follows:

   $conn=get-connection -conn $conn -server $server -database $database -user $user -password $password

 

Unfortunately, the parameter name is $connection, not $conn, so the connection isn't propogating down to the function.

The fix is to replace that line with this:

     $conn=get-connection -conn $connection -server $server -database $database -user $user -password $password

This has been corrected in subsequent releases.

 

Jan 31, 2013 at 7:52 PM
Just getting back to this and unfortunatly the posted fix did not work. I made the suggested change and unfortunaly I'm still recieving the error
database : The variable '$database' cannot be retrieved because it has not been set.

When I step through the code it executes this statement:
$conn = Get-Connection -conn $connection -server $server -database $database -user $user -password $password
and then throws the error. One more thing that might help from the Invoke-StoredProcedure function this is how New-SQLCommand is called:

$cmd=new-sqlcommand @PSBoundParameters

This is the function that appears to be having an issue:
function New-SQLCommand{
param([Parameter(Position=0, Mandatory=$true)][Alias('storedProcName')][string]$sql,
  [Parameter(ParameterSetName="SuppliedConnection",Position=1, Mandatory=$false)][System.Data.SqlClient.SQLConnection]$connection,
  [Parameter(Position=2, Mandatory=$false)][hashtable]$parameters=@{},
  [Parameter(Position=3, Mandatory=$false)][int]$timeout=30,
  [Parameter(ParameterSetName="AdHocConnection",Position=4, Mandatory=$false)][string]$server,
  [Parameter(ParameterSetName="AdHocConnection",Position=5, Mandatory=$false)][string]$database,
  [Parameter(ParameterSetName="AdHocConnection",Position=6, Mandatory=$false)][string]$user,
  [Parameter(Position=7, Mandatory=$false)][string]$password,
  [Parameter(Position=8, Mandatory=$false)][System.Data.SqlClient.SqlTransaction]$transaction=$null,
  [Parameter(Position=9, Mandatory=$false)][hashtable]$outparameters=@{})

$dbconn=Get-Connection -conn $connection -server $server -database $database -user $user -password $password

$conn = Get-Connection -conn $connection -server $server -database $database -user $user -password $password

$close=($conn.State -eq [System.Data.ConnectionState]'Closed')
if ($close) {
    $conn.Open()
}   
$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
$cmd.CommandTimeout=$timeout
foreach($p in $parameters.Keys){
    $parm=$cmd.Parameters.AddWithValue("@$p",$parameters[$p])
    if (Is-NULL $parameters[$p]){
       $parm.Value=[DBNull]::Value
    }
}
put-outputparameters $cmd $outparameters

if ($transaction -is [System.Data.SqlClient.SqlTransaction]){
$cmd.Transaction = $transaction
}
return $cmd
Coordinator
Jan 31, 2013 at 8:23 PM
I was not able to reproduce your results. I downloaded the 2.3.2.1 release (the latest) and used the script you provided and got the appropriate results.

Can you repost the script and specific error you're getting?
Jan 31, 2013 at 8:41 PM
I downloaded the latest again and it is working now. I'm not quite sure what happened as I did a compare between what I had and what was downloaded and I was unable to see any difference. Oh well working now, just have to figure out how to pass a null as a value to a parameter, is-null funcition blowing up now. Thank you again for your time.
Coordinator
Jan 31, 2013 at 8:52 PM
I can help with passing null.

Here's a test stored procedure:
create procedure sp_testnull
@parm1 varchar(100)
as
select name from sys.databases
where @parm1 is null
And here's the code to call it (assuming you have a connection to the database which has this procedure in it in $c)
invoke-storedprocedure -storedProcName sp_testnull -parameters @{parm1=[System.DBNull]::Value} -connection $c
Jan 31, 2013 at 8:59 PM
Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.
Coordinator
Jan 31, 2013 at 9:04 PM
Glad to help. Did you figure out what your problem with is-null was?<br>
<br>
On Thursday, January 31, 2013, JimDorame wrote:<br>
<blockquote style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex"> <div> <p>From: JimDorame</p> <div>Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.</div> <div> <p>Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post994893" target="_blank"> full discussion online</a>.</p> <p>To add a post to this discussion, reply to this email (<a href="" target="_blank">SQLPSX@discussions.codeplex.com</a>)</p> <p>To start a new discussion for this project, email <a href="" target="_blank">SQLPSX@discussions.codeplex.com</a></p> <p>You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</p> <p>Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></p> </div> </div> </blockquote> <br>
<br>
-- <br>
<a href="http://powershellstation.com">http://powershellstation.com>
Feb 1, 2013 at 6:16 PM
<div> <p><span style="font-size:11.0pt; font-family:">Yes, the is-null issue had to deal with I was passing an empty string not a DBNull.</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">Jim</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <div> <div style="border:none; border-top:solid #B5C4DF 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:10.0pt; font-family:">From:</span></b><span style="font-size:10.0pt; font-family:"> MikeShepard [email removed] <br> <b>Sent:</b> Thursday, January 31, 2013 3:05 PM<br> <b>To:</b> James Dorame<br> <b>Subject:</b> Re: Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection [SQLPSX:400490]</span></p> </div> </div> <p></p> <p><span style="font-size:9.0pt; font-family:">From: MikeShepard</span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt"> <p><span style="font-size:9.0pt; font-family:">Glad to help. Did you figure out what your problem with is-null was?<br> <br> On Thursday, January 31, 2013, JimDorame wrote:</span></p> <div> <p><span style="font-size:9.0pt; font-family:">From: JimDorame</span></p> <div> <p><span style="font-size:9.0pt; font-family:">Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.</span></p> </div> <div> <p><span style="font-size:9.0pt; font-family:">Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post994893" target="_blank"> full discussion online</a>.</span></p> <p><span style="font-size:9.0pt; font-family:">To add a post to this discussion, reply to this email (<a href="" target="_blank">SQLPSX@discussions.codeplex.com</a>)</span></p> <p><span style="font-size:9.0pt; font-family:">To start a new discussion for this project, email <a href="" target="_blank">SQLPSX@discussions.codeplex.com</a></span></p> <p><span style="font-size:9.0pt; font-family:">You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</span></p> <p><span style="font-size:9.0pt; font-family:">Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></span></p> </div> </div> <p><span style="font-size:9.0pt; font-family:"><br> <br> -- <br> <a href="http://powershellstation.com">http://powershellstation.com</a></span></p> </div> </div>
Feb 4, 2013 at 4:51 PM
<div> <p><span style="font-size:11.0pt; font-family:">Mike,</span></p> <p><span style="font-size:11.0pt; font-family:">I finally figured out what was going on with the “<i>Variable”</i> not defined error that I was getting sometimes. </span></p> <p><span style="font-size:11.0pt; font-family:">In my PowerShell scripts I use this line:</span></p> <p><b><span style="font-size:10.0pt; font-family:">Set-StrictMode</span></b><span style="font-size:10.0pt; font-family:"> </span><i><span style="font-size:10.0pt; font-family:">-Version</span></i><span style="font-size:10.0pt; font-family:"> </span><span style="font-size:10.0pt; font-family:">latest</span><span style="font-size:10.0pt; font-family:">;</span></p> <p><span style="font-size:10.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">I believe that not all the variables in SQLPSX are declared before use, which would cause the issue I was seeing. </span></p> <p><span style="font-size:11.0pt; font-family:">I tested this by removing the above line and all work as expected.</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">Again thank you for your help.</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">-JD</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <div> <div style="border:none; border-top:solid #B5C4DF 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:10.0pt; font-family:">From:</span></b><span style="font-size:10.0pt; font-family:"> MikeShepard [email removed] <br> <b>Sent:</b> Thursday, January 31, 2013 3:05 PM<br> <b>To:</b> James Dorame<br> <b>Subject:</b> Re: Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection [SQLPSX:400490]</span></p> </div> </div> <p></p> <p><span style="font-size:9.0pt; font-family:">From: MikeShepard</span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt"> <p><span style="font-size:9.0pt; font-family:">Glad to help. Did you figure out what your problem with is-null was?<br> <br> On Thursday, January 31, 2013, JimDorame wrote:</span></p> <div> <p><span style="font-size:9.0pt; font-family:">From: JimDorame</span></p> <div> <p><span style="font-size:9.0pt; font-family:">Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.</span></p> </div> <div> <p><span style="font-size:9.0pt; font-family:">Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post994893" target="_blank"> full discussion online</a>.</span></p> <p><span style="font-size:9.0pt; font-family:">To add a post to this discussion, reply to this email (<a href="" target="_blank">SQLPSX@discussions.codeplex.com</a>)</span></p> <p><span style="font-size:9.0pt; font-family:">To start a new discussion for this project, email <a href="" target="_blank">SQLPSX@discussions.codeplex.com</a></span></p> <p><span style="font-size:9.0pt; font-family:">You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</span></p> <p><span style="font-size:9.0pt; font-family:">Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></span></p> </div> </div> <p><span style="font-size:9.0pt; font-family:"><br> <br> -- <br> <a href="http://powershellstation.com">http://powershellstation.com</a></span></p> </div> </div>
Coordinator
Feb 4, 2013 at 8:57 PM
I found the issue.<br>
<br>
In the new-connection function, the variable $dbclause isn't initialized if there isn't a database passed on the command-line.<br>
<br>
I'll add $dbclause='' as the first line of the function and it will be all better.<br>
<br>
I'll try to get the rest of the tests done with strict mode turned on (in fact, I'll put the strict mode switch in the module).<br>
<br>
Thanks for being patient and persistent!<br>
<br>
Mike<br>
<br>
<br>
<br>
<div>On Mon, Feb 4, 2013 at 10:51 AM, JimDorame <span dir="ltr">&lt;<a href="mailto:notifications@codeplex.com" target="_blank">notifications@codeplex.com</a>&gt;</span> wrote:<br> <blockquote style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex"> <div> <p>From: JimDorame</p> <div> <div> <p><span>Mike,</span></p> <p><span>I finally figured out what was going on with the “<i>Variable”</i> not defined error that I was getting sometimes. </span></p> <p><span>In my PowerShell scripts I use this line:</span></p> <p><b><span>Set-StrictMode</span></b><span> </span><i><span>-Version</span></i><span> </span><span>latest</span><span>;</span></p> <p><span></span></p> <p><span>I believe that not all the variables in SQLPSX are declared before use, which would cause the issue I was seeing. </span></p> <p><span>I tested this by removing the above line and all work as expected.</span></p> <p><span></span></p> <p><span>Again thank you for your help.</span></p> <p><span></span></p> <p><span>-JD</span></p> <div> <div> <p><span></span></p> <div> <div style="border:none; border-top:solid #b5c4df 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span>From:</span></b><span> MikeShepard [email removed] <br> <b>Sent:</b> Thursday, January 31, 2013 3:05 PM<br> <b>To:</b> James Dorame<br> <b>Subject:</b> Re: Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection [SQLPSX:400490]</span></p> </div> </div> <p></p> <p><span>From: MikeShepard</span></p> <div style="margin-bottom:24.0pt"> <p><span>Glad to help. Did you figure out what your problem with is-null was?<br> <br> On Thursday, January 31, 2013, JimDorame wrote:</span></p> <div> <p><span>From: JimDorame</span></p> <div> <p><span>Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.</span></p> </div> <div> <p><span>Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post994893" target="_blank"> full discussion online</a>.</span></p> <p><span>To add a post to this discussion, reply to this email (<a>SQLPSX@discussions.codeplex.com</a>)</span></p> <p><span>To start a new discussion for this project, email <a>SQLPSX@discussions.codeplex.com</a></span></p> <p><span>You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</span></p> <p><span>Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></span></p> </div> </div> <p><span><br> <br> -- <br> <a href="http://powershellstation.com" target="_blank">http://powershellstation.com</a></span></p> </div> </div> </div> </div> </div> <div> <div> <div> <p>Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post996216" target="_blank"> full discussion online</a>.</p> <p>To add a post to this discussion, reply to this email (<a href="mailto:SQLPSX@discussions.codeplex.com?subject=[SQLPSX:400490]" target="_blank">SQLPSX@discussions.codeplex.com</a>)</p> <p>To start a new discussion for this project, email <a href="mailto:SQLPSX@discussions.codeplex.com" target="_blank"> SQLPSX@discussions.codeplex.com</a></p> <p>You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</p> <p>Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></p> </div> </div> </div> </div> </blockquote> </div> <br>
<br clear="all">
<br>
-- <br>
<a href="http://powershellstation.com">http://powershellstation.com>
Feb 4, 2013 at 11:11 PM
<div> <p><span style="font-size:11.0pt; font-family:">No thank you for the work you have done. I appreciate it greatly.</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <p><span style="font-size:11.0pt; font-family:">-JD</span></p> <p><span style="font-size:11.0pt; font-family:"></span></p> <div> <div style="border:none; border-top:solid #B5C4DF 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:10.0pt; font-family:">From:</span></b><span style="font-size:10.0pt; font-family:"> MikeShepard [email removed] <br> <b>Sent:</b> Monday, February 04, 2013 2:57 PM<br> <b>To:</b> James Dorame<br> <b>Subject:</b> Re: Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection [SQLPSX:400490]</span></p> </div> </div> <p></p> <p><span style="font-size:9.0pt; font-family:">From: MikeShepard</span></p> <div id="ThreadNotificationPostBody" style="margin-bottom:24.0pt"> <p style="margin-bottom:12.0pt"><span style="font-size:9.0pt; font-family:">I found the issue.<br> <br> In the new-connection function, the variable $dbclause isn't initialized if there isn't a database passed on the command-line.<br> <br> I'll add $dbclause='' as the first line of the function and it will be all better.<br> <br> I'll try to get the rest of the tests done with strict mode turned on (in fact, I'll put the strict mode switch in the module).<br> <br> Thanks for being patient and persistent!<br> <br> Mike<br> <br> <br> </span></p> <div> <p><span style="font-size:9.0pt; font-family:">On Mon, Feb 4, 2013 at 10:51 AM, JimDorame &lt;<a href="mailto:notifications@codeplex.com" target="_blank">notifications@codeplex.com</a>&gt; wrote:</span></p> <div> <p><span style="font-size:9.0pt; font-family:">From: JimDorame</span></p> <div> <div> <p><span style="font-size:9.0pt; font-family:">Mike,</span></p> <p><span style="font-size:9.0pt; font-family:">I finally figured out what was going on with the “<i>Variable”</i> not defined error that I was getting sometimes. </span></p> <p><span style="font-size:9.0pt; font-family:">In my PowerShell scripts I use this line:</span></p> <p><b><span style="font-size:9.0pt; font-family:">Set-StrictMode</span></b><span style="font-size:9.0pt; font-family:"> <i>-Version</i> latest;</span></p> <p><span style="font-size:9.0pt; font-family:">I believe that not all the variables in SQLPSX are declared before use, which would cause the issue I was seeing. </span></p> <p><span style="font-size:9.0pt; font-family:">I tested this by removing the above line and all work as expected.</span></p> <p><span style="font-size:9.0pt; font-family:">Again thank you for your help.</span></p> <p><span style="font-size:9.0pt; font-family:">-JD</span></p> <div> <div> <div> <div style="border:none; border-top:solid #B5C4DF 1.0pt; padding:3.0pt 0in 0in 0in"> <p><b><span style="font-size:9.0pt; font-family:">From:</span></b><span style="font-size:9.0pt; font-family:"> MikeShepard [email removed] <br> <b>Sent:</b> Thursday, January 31, 2013 3:05 PM<br> <b>To:</b> James Dorame<br> <b>Subject:</b> Re: Invoke-Storedprocedure/Invoke-SQL Issue when passing a connection [SQLPSX:400490]</span></p> </div> </div> <p><span style="font-size:9.0pt; font-family:">From: MikeShepard</span></p> <div style="margin-bottom:24.0pt"> <p><span style="font-size:9.0pt; font-family:">Glad to help. Did you figure out what your problem with is-null was?<br> <br> On Thursday, January 31, 2013, JimDorame wrote:</span></p> <div> <p><span style="font-size:9.0pt; font-family:">From: JimDorame</span></p> <div> <p><span style="font-size:9.0pt; font-family:">Thanks alot Mike. I forgot I needed to invoke System.DBNull. All is working as expected.</span></p> </div> <div> <p><span style="font-size:9.0pt; font-family:">Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post994893" target="_blank"> full discussion online</a>.</span></p> <p><span style="font-size:9.0pt; font-family:">To add a post to this discussion, reply to this email (<a href="mailto:SQLPSX@discussions.codeplex.com">SQLPSX@discussions.codeplex.com</a>)</span></p> <p><span style="font-size:9.0pt; font-family:">To start a new discussion for this project, email <a href="mailto:SQLPSX@discussions.codeplex.com">SQLPSX@discussions.codeplex.com</a></span></p> <p><span style="font-size:9.0pt; font-family:">You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</span></p> <p><span style="font-size:9.0pt; font-family:">Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></span></p> </div> </div> <p><span style="font-size:9.0pt; font-family:"><br> <br> -- <br> <a href="http://powershellstation.com" target="_blank">http://powershellstation.com</a></span></p> </div> </div> </div> </div> </div> <div> <div> <div> <p><span style="font-size:9.0pt; font-family:">Read the <a href="http://sqlpsx.codeplex.com/discussions/400490#post996216" target="_blank"> full discussion online</a>.</span></p> <p><span style="font-size:9.0pt; font-family:">To add a post to this discussion, reply to this email (<a href="mailto:SQLPSX@discussions.codeplex.com?subject=[SQLPSX:400490]" target="_blank">SQLPSX@discussions.codeplex.com</a>)</span></p> <p><span style="font-size:9.0pt; font-family:">To start a new discussion for this project, email <a href="mailto:SQLPSX@discussions.codeplex.com" target="_blank">SQLPSX@discussions.codeplex.com</a></span></p> <p><span style="font-size:9.0pt; font-family:">You are receiving this email because you subscribed to this discussion on CodePlex. You can <a href="https://sqlpsx.codeplex.com/subscriptions/thread/project/edit" target="_blank"> unsubscribe or change your settings</a> on codePlex.com.</span></p> <p><span style="font-size:9.0pt; font-family:">Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at <a href="http://codeplex.com" target="_blank">codeplex.com</a></span></p> </div> </div> </div> </div> </div> <p><span style="font-size:9.0pt; font-family:"><br> <br clear="all"> <br> -- <br> <a href="http://powershellstation.com">http://powershellstation.com</a></span></p> </div> </div>