January 2011 - Posts

Developer Day Scotland Session Submissions
25 January 11 05:32 PM | MartinBell | with no comments

I got a reminder from Colin Angus Mackay that session submissions for DDD Scotland 2011 on Saturday 7th May at Glasgow Caledonian University is closing soon. Anyone thinking of speaking who has not already submitted a session, will need to do so before the deadline on Friday 6th February.

They are also looking for sponsorship so if you can help them out or know anyone who would like to sponsor the event, get in touch with Colin at colin@scottishdevelopers.com

Filed under: ,
Quirky Invoke-Sqlcmd?
25 January 11 12:48 PM | MartinBell | with no comments

One thing I was going to mention in my last post on Invoke-Sqlcmd was the fact that you need to return unique column names. This is not mentioned in Books online and I’ve not seen it anywhere else. For example, if you run the following Powershell script:

# load snap-in if not in SQLPS
Add-PSSnapin SqlServerCmdletSnapin100

Invoke-Sqlcmd -server localhost -database Adventureworks -query "
    SELECT t.object_id AS tableid
        , t.name
        , c.object_id AS columnId
        , c.name
    FROM sys.tables t
    JOIN sys.columns c ON c.object_id = t.object_id
    WHERE t.name = 'Employee'
    " ;

You will get the following errors:

Invoke-Sqlcmd : The pipeline has been stopped.
At line:3 char:14
+ Invoke-Sqlcmd <<<<  -server localhost -database Adventureworks -query "
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
 
Invoke-Sqlcmd : A column named 'name' already belongs to this DataTable.
At line:3 char:14
+ Invoke-Sqlcmd <<<<  -server localhost -database Adventureworks -query "
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

At first this may seem quite surprising, but if you remember that the cmdlet returns a datatable and datarow objects and each “column” is an attribute of the object, then it is sensible that you can’t have two attributes with the same name.

This can be an issue when running some of the system stored procedures e.g sp_who2 as described by Max Trinidad who creates his own version of the procedure to overcome it. Another solution to this would be to use a temporary table with uniquely named columns and INSERT…EXEC to get the output of the table. This could be an issue if you try to run your existing scripts or procedures using Invoke-Sqlcmd so make sure you test them first!!
 

Filed under:
Cursors in Powershell
24 January 11 10:39 AM | MartinBell | 2 comment(s)

Invoke-cmd is a cmdlet available in the SQL Server Powershell snap-ins. I talked about these snapins in my blog post Using SQL Server in Powershell outside of SQLPS

Whilst I have seem many examples of invoking invoke-sqlcmd to do DML I can’t remember seeing anything on how to use the results of a query inside a Powershell script i.e. the equivalent of using a cursor in T-SQL.

So here is an example:

# load snap-in if not in SQLPS
Add-PSSnapin SqlServerCmdletSnapin100

foreach ( $datarow in ( invoke-sqlcmd -server localhost -database Adventureworks -query "select t.object_id AS tableid, t.name as tablename, c.object_id as columnId, c.name as columnname FROM sys.tables t JOIN sys.columns c ON c.object_id = t.object_id WHERE t.name = 'Employee'" ) ) {
    write-host $datarow.tablename $datarow.columnname ;
}

Each row (object) returned by the invoke-sqlcmd object is assigned to $datarow and you can then use the members of this object within the script.

Filed under:
The underused OVER clause!
23 January 11 01:33 PM | MartinBell | 1 comment(s)

Seeing the Steve Hindmarsh’s blog post on the OUTPUT clause got me thinking about what other underused clauses may benefit from greater exposure!

You may already be familiar with the OVER clause because it is mandatory when using a ranking function i.e. ROW_NUMBER, RANK, DENSE_RANK and NTILE. The order I’ve listed these functions is probably the same order as their popularity.

The syntax for the OVER clause when used with a ranking function is as follows:

< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

The reason I think it is under-used is because you can also use an OVER clause with aggregate function. The format of the OVER function in this case the syntax for the OVER clause in this case is:

< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
)

The example in books online shows an example use of what this can be used for:

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664)
ORDER BY SalesOrderID, ProductID, OrderQty ;
GO

This is showing the aggregate value for each sales order against each sales order detail line.

If you were doing this in a version before SQL 2005 then you would need to use a sub-query.

SELECT M.SalesOrderID, M.ProductID, M.OrderQty
    ,( SELECT SUM(OrderQty) FROM Sales.SalesOrderDetail S WHERE S.SalesOrderID = M.SalesOrderID ) AS 'Total'
    ,( SELECT AVG(OrderQty) FROM Sales.SalesOrderDetail S WHERE S.SalesOrderID = M.SalesOrderID ) AS 'Avg'
    ,( SELECT COUNT(OrderQty) FROM Sales.SalesOrderDetail S WHERE S.SalesOrderID = M.SalesOrderID ) AS 'Count'
    ,( SELECT MIN(OrderQty) FROM Sales.SalesOrderDetail S WHERE S.SalesOrderID = M.SalesOrderID ) AS 'Min'
    ,( SELECT MAX(OrderQty) FROM Sales.SalesOrderDetail S WHERE S.SalesOrderID = M.SalesOrderID ) AS 'Max'
FROM Sales.SalesOrderDetail M
WHERE M.SalesOrderID IN(43659,43664)
ORDER  BY M.SalesOrderID, M.ProductID, M.OrderQty ;
GO

A more efficient way to do this would be:

SELECT M.SalesOrderID, M.ProductID, M.OrderQty
    ,S.[Total], S.[Avg], S.[Count], S.[Min], S.[Max]
FROM Sales.SalesOrderDetail M
JOIN (
        SELECT SalesOrderID
                , SUM(OrderQty) AS 'Total'
                , AVG(OrderQty) AS 'Avg'
                , COUNT(OrderQty) AS 'Count'
                , MIN(OrderQty) AS 'Min'
                , MAX(OrderQty) AS 'Max'
        FROM Sales.SalesOrderDetail
        WHERE SalesOrderID IN(43659,43664)
        GROUP BY SalesOrderID
    ) S ON S.SalesOrderID = m.SalesOrderID
WHERE M.SalesOrderID IN(43659,43664)
ORDER  BY M.SalesOrderID, M.ProductID, M.OrderQty ;
GO

If you look at this with STATISTIC IO set the least number of scans and logical reads are produced using the OVER clause, but the number of logical reads for the derived table is only a few more and the number of scans is the same (and as they seem to have the same query execution plan, under-the-hood they must translate to the same thing!).

If you are using sub-queries then switching to using the OVER clause could be very beneficial.

If doing this sort of thing has proven to be a performance bottleneck, traditionally the solution has been to de-normalise and store the totals against the master record and a mechanism to maintain them has to be implemented. If this is done using triggers then the overhead of doing this could impact the time taken to complete a transaction. Although it is reasonably easy to maintain a total using this method it is more difficult to calculate the other aggregates. If you are in this situation revisiting the OVER statement may be worthwhile.

Itzik Ben-Gan and Sujata Mehta have suggested several enhancements to T-SQL including changes to the Over clause, this is documented on connect and at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc

Filed under: ,
Powershell debugging II
22 January 11 10:24 PM | MartinBell | 5 comment(s)

In my previous Powershell blog post I talked about debugging Powershell scripts using Set-PSDebug and Set-PSBreakpoint, which is great if you can use them, but there are certain circumstances where you may not be able to do that. This is where writing to a log file may be useful.

Powershell has several Write-… cmdlets these include:

Name

Synopsis

Write-Host

Writes customized output to a host.

Write-Progress

Displays a progress bar within a Windows PowerShell command window.

Write-Debug

Writes a debug message to the console.

Write-Verbose

Writes text to the verbose message stream.

Write-Warning

Writes a warning message.

Write-Error

Writes an object to the error stream.

Write-Output

Sends the specified objects to the next command in the pipeline. If the command is the last command in the pipeline, the objects are displayed in the console.

Write-EventLog

Writes an event to an event log.


Output from Write-Progress, Write-Debug, Write-Verbose and Write-Warning can be controlled by setting the following environment variables:

Name

Synopsis

Default Value

Write-Progress

$ProgressPreference

Continue

Write-Debug

$DebugPreference

SilentlyContinue

Write-Verbose

$VerbosePreference

SilentlyContinue

Write-Warning

$WarningPreference

Continue


The current values of these variables can be obtained by either examining them directly or using the Get-Variable cmdlet e.g. Get-Variable *Preference
If the variable is set to "SilentContinue" then the cmdlet will not produce any output.

You can therefore optionally choose to write output by setting the variable before running your script.

For example adding debug messages to my previous script:

# load assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
 
function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        write-debug $conn ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            write-debug $j.name ;
            write-host $j.name ;
        } ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-debug "Error caught: " ;
        write-debug $err ;
        write-host "Error caught: " $err.Message ;
        continue ;
    } ;
}

Calling the procedure as normal will not be any different but using the following commands:

set-variable DebugPreference Continue
fn_getdbs "localhost" "validuser" "validpassword"

produces the following output:

DEBUG: server='localhost';uid='validuser';password='va;lidpassword';Pooling=false;multipleactiveresultsets=false
DEBUG: AdventureWorks
AdventureWorks
DEBUG: AdventureWorks2008
AdventureWorks2008
DEBUG: AdventureWorksDW
AdventureWorksDW
DEBUG: AdventureWorksDW2008
AdventureWorksDW2008
DEBUG: AdventureWorksLT
AdventureWorksLT
DEBUG: AdventureWorksLT2008
AdventureWorksLT2008
DEBUG: master
master
DEBUG: model
model
DEBUG: msdb
msdb
DEBUG: ReportServer
ReportServer
DEBUG: ReportServerTempDB
ReportServerTempDB
DEBUG: tempdb
tempdb


and the command:

fn_getdbs "localhost" "validuser" "invalidpassword"

produces:

DEBUG: server='localhost';uid='validuser';password='invalidpassword';Pooling=false;multipleactiveresultsets=false
DEBUG: Error caught:
DEBUG: System.Management.Automation.ExtendedTypeSystemException: The following exception was thrown when tryi
ng to enumerate the collection: "Failed to connect to server localhost.". ---> Microsoft.SqlServer.Management
.Common.ConnectionFailureException: Failed to connect to server localhost. ---> System.Data.SqlClient.SqlExce
ption: Login failed for user 'validuser'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataS
tream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPasswor
d, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean
redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectio
nString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionSt
ring connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirect
edUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGro
upProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, D
bConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFa
ctory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIde
ntity)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.get_DatabaseEngineType()
   at Microsoft.SqlServer.Management.Smo.Server.GetExecutionManager()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_ServerVersion()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer()
   at Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringComparer()
   at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.InitInnerCollection()
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.get_InternalStorage()
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator()
   at System.Management.Automation.LanguagePrimitives.GetEnumerator(Object obj)
   --- End of inner exception stack trace ---
   at System.Management.Automation.LanguagePrimitives.GetEnumerator(Object obj)
   at System.Management.Automation.foreachStatementNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
   at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
Error caught:  The following exception was thrown when trying to enumerate the collection: "Failed to connect to server localhost.".

Unfortunately to write this debug information to a file you can’t just pipe it to the Out-File cmdlet but you can write your own code using the Add-Content cmdlet:

# load assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
 
function create-log ( [string] $logoutputfile ) {

    if ( $DebugPreference -ne "SilentlyContinue" ) {
        if ( $logoutputfile -eq "" ) {
            $global:logfile = "C:\temp\audit_" + (get-date -format "yyyyMMddHHmmss") + ".log" ;
        } else {
            $global:logfile = $logoutputfile ;
        }
        new-item $global:logfile -type File -value "Powershell Log file: "  -force ;
        add-content -path $global:logfile -value (get-date) ;
    }
}

function write-log ( [string] $logitem ) {

    if ( $DebugPreference -ne "SilentlyContinue" ) {
        add-content -path $global:logfile -value $logitem ;
        write-debug $logitem ;
    }
   

}

function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
 
    try {

    create-log  ;
    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        write-log $conn ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            write-log $j.name
            write-host $j.name ;
        } ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-log "Error caught: " ;
        write-log $err ;
        write-host "Error caught: " $err.Message ;
        continue ;
    } ;
}

This still checks the variable $DebugPreference to determine if debug is to be output and only creates or writes to the log file if it is not set to “SilentContinue”

Filed under:
T-SQL Fundamentals Course
21 January 11 12:20 PM | MartinBell | 2 comment(s)

SQLBits is known for it’s high quality SQL Server training. If you look at the courses available on the SQLBits Website one may stick out as being different to the other in-depth courses; that is T-SQL Fundamentals with Martin Bell. You may ask why I’m putting on an introductory course like this, and there are several reasons and here are a few:

  • SQLBits as an organisation wishes to embrace all members of the SQL Server community at all levels.

  • If someone’s first experience of SQL Server is a good one then they are more likely to continue their development and progress to a higher level.

  • If someone receives an early experience of SQL Server through a community event, they are more likely to participate in the community in the future


Putting on this level of course is very much a test to gauge the demand, and if you are reading this blog it is probably not of interest to you! But if you do know anyone who would benefit, please get them to
sign up.

Filed under: ,
"SQLBits 8 - beside the seaside" registration opened
19 January 11 12:00 PM | MartinBell | 1 comment(s)

Today we opened registration for SQLBits 8 Big Smile To register go to http://www.regonline.com/Register/Checkin.aspx?EventID=925948.

We have a great line up, possibly the best we've had;  and the agenda for conference Friday day is currently being finalized.

For a list of courses offered on the Training day see http://sqlbits.com/information/TrainingDay.aspx

You have until March 11th to get the earlybird discount, for pricing check out http://sqlbits.com/information/Pricing.aspx

Filed under:
SQL Server 2008 R2 Developers Training Kit Updated
19 January 11 10:19 AM | MartinBell | with no comments

A new release of the SQL 2008 R2 Developers Training kit for developers has been released. It you have not seen this kit before you may be surprised at the great resources it contains. You can find out more and download it at:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

Powershell debugging
18 January 11 04:15 PM | MartinBell | 4 comment(s)

I have to admit that I am still quite stick in my Powershell 1.0 ways when it comes to writing scripts. That is I write the script in textpad and paste it into the command prompt window. Many times I have pasted a script and wondered why I still have a continuation prompt then realise I have missed out a closing quote.

When it comes to debugging this makes using Powershell a bit of a pain! For instance with the following script I have missed out a “-“ in write-host:

# load assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
 
function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            writehost $j.name ;
        } ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-host "Error caught: " $err.Message ;
        continue ;
    } ;
}

Because Powershell is interpreted I will not get an error if there are no databases on the instance or if I don’t type in the credentials correctly. The error message I do get is:

Error caught:  The term 'writehost' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

If the code was longer or more complex finding just where the error is could be difficult, but as I have written it in textpad I can search for “writehost”.

To step through the script you can use the cmdlet set-PSdebug. There are two methods of calling this cmdlet, one to turn debugging on:

Set-PSDebug [-Step] [-Strict] [-Trace <int>] [<CommonParameters>]

and one to turn it off:

Set-PSDebug [-Off] [<CommonParameters>]

Using the –step option will allow you to step through a script and you can see the how the script is executed. In the above example this would show where the exception that caused the catch block to be invoked originated and you would then be able to find the line and change the script. I used this in conjunction with SQL Profiler to monitor the login attempts when investigating my previous blog post. Even the initial call to set-PSdebug will be traced!! After each step you will be prompted for a response, this can get a bit tedious:

PS C:\Users\Martin> set-psdebug -step

Continue with this operation?
   2+         $foundSuggestion = <<<<  $false
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):?
Y - Continue.
A - Continue and do not inquire to continue again for this session.
N - End the operation with an error.
L - End the operation with an error and do not inquire to continue again for
this session.
S - Pause the current operation and enter a command prompt. Type "exit" to
resume the paused operation.
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):

You may be tempted to use the Yes to All option, but remember this is scoped at the current debug session and subsequent commands will not be stepped through unless you set-PSdebug –step again.

Using the –S option allows you to enter another shell and examine and (sometimes) set variables e.g. With the script:

function fn_setgetvar ([string] $value)  {
    $var = $value
    write-host $var
}

I can do the following:

PS C:\Users\Martin> fn_setgetvar "Hello World"

Continue with this operation?
   1+  <<<< fn_setgetvar "Hello World"
[Y ]  Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    1+  <<<< fn_setgetvar "Hello World"

Continue with this operation?
   2+     $var = <<<<  $value
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    2+     $var = <<<<  $value

Continue with this operation?
   3+      <<<< write-host $var
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):S
PS C:\Users\Martin>>> $var
Hello World
PS C:\Users\Martin>>> $var = "What is this?"
PS C:\Users\Martin>>> $var
What is this?
PS C:\Users\Martin>>> exit

Continue with this operation?
   3+      <<<< write-host $var
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    3+      <<<< write-host $var
What is this?

Continue with this operation?
   2+         $foundSuggestion = <<<<  $false
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    2+         $foundSuggestion = <<<<  $false

Continue with this operation?
   4+         if <<<< ($lastError -and
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    4+         if <<<< ($lastError -and

Continue with this operation?
  15+         $foundSuggestion <<<<
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:   15+         $foundSuggestion <<<<

The highlighted line shows the change value of the variable $var. This is the output from looking at the $conn before creating the server object in the fn_getdbs function. Unfortunately for a connection object you can’t change the values after the connection has been made so if I tried to change the ServerInstance you get an error:

PS C:\Users\Martin>>> $conn.ServerInstance = "(local)"
Exception setting "ServerInstance": "Connection properties cannot be changed af
ter a connection has been established."
At line:1 char:7
+ $conn. <<<< ServerInstance = "(local)"
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

The –trace parameter can take one of three values:

-Trace <int>
    Specifies the trace level:
     0 - Turn script tracing off
     1 - Trace script lines as they are executed
     2 - Trace script lines, variable assignments, function calls, and scripts.

To avoid having to step through all the lines in the fn_getdbs function I can use the cmdlet set-PSbreakpoint to stop the script at a given point. An example to break on the assignment to $srv variable can be found here. Once you hit the breakpoint you can examine/set the values of variables. There are companion commands get-PSbreakpoint, enable-PSbreakpoint, disable-PSbreakpoint and remove-PSbreakpoint. These were introduced in Powershell 2.0 which also has the GUI Powershell ISE interface that allows you to set/clear breakpoints using F9 in the script pane






If I run the script (F5) and follow the previous example of examining and changing $var to “What is this?” you get the following in the Output window

In Powershell ISE when you reach the breakpoint, you can view the value of a variable by placing your cursor over the variable in the script window and it will be displayed as a tooltip.

Hopefully this post will be useful when you want to fathom out what your Powershell scripts are doing! In my next Powershell post I will look at how to log debug information.

Filed under:
SMO doesn't always raise an error when handling login failures
17 January 11 09:28 AM | MartinBell | 2 comment(s)

In my last blog post I mentioned that SMO will not always raise an exception when a login fails. I've now raise it as a connect item for this

Filed under:

This Blog

SQL Blogs

Syndication