none
Executing a T-SQL command from Powershell

    Dotaz

  • I am currently using a powershell script to inventory machines on my network. 
    The script inventories each machine seperately, creates a PSObject, and then writes the object's properties to a single table in my database.  I then build two SQL views
    of that table; one relies upon the other to show only the recent "Last Contact" with each machine.

    I modify this inventory script regularly and therefore change the object's properties frequently.  I need a  tool to automate the process of restructuring the View as i do it many times in testing.  Most of the query was copied from the SQL Query Designer in SQL Studio; I am pretty sure that is solid.

    Most of this script works.  But I was trying to work through
    the "System.Data.SqlClient.SqlCommand" at the end, got frustrated, looked into doing this process with SMO, went down a couple of ratholes, etc.  If someone could just
    look at the final connection string and at least point me in the right direction, that would help.

    function Format-SQLRebuildView {
    $names = $obj.psobject.Properties | select Name
    $Columns = @()
    foreach ($Property.name in $Names) 
        {
            $column =  "dbo.Inventory.$($property.name),"
            $Columns += $column
        }
    $conn = New-Object System.Data.SqlClient.SqlConnection ("Data Source=SQLServer;Initial Catalog=Documentation;Integrated Security=SSPI")
    $conn.Open()
    $cmd1 = $conn.CreateCommand() 
    $cmd1.CommandText = "
    USE [SOI_Documentation]
    GO
    /****** Object:  View [dbo].[viewInventorySummary]    Script Date: 03/01/2012 20:00:50 ******/
    IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[viewInventorySummary]'))
    DROP VIEW [dbo].[viewInventorySummary]
    GO
    USE [SOI_Documentation]
    GO
    /****** Object:  View [dbo].[viewInventorySummary]    Script Date: 03/01/2012 20:00:50 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[viewInventorySummary]
    AS
    SELECT  
    $columns
    dbo.viewInventory_LC.CSName AS Expr1,
     dbo.viewInventory_LC.InventoryDateTime_LC
    FROM         dbo.Inventory INNER JOIN
                          dbo.viewInventory_LC ON dbo.Inventory.CSName = dbo.viewInventory_LC.CSName AND 
                          dbo.Inventory.InventoryDateTime = dbo.viewInventory_LC.InventoryDateTime_LC
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
    ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewInventorySummary'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewInventorySummary'
    GO
    "
    #change the sql statement before the quote
    $cmd2 = New-Object "System.Data.SqlClient.SqlCommand" -ArgumentList ($cmd1, $conn)	
    $cmd2.ExecuteNonQuery() | out-null	
    $conn.Close()
    }
    Forgot to add the Error

    Exception             : System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" with "0" argument(s): "Could not find server 'System' i
                            n sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server t
                            o sys.servers." ---> System.Data.SqlClient.SqlException: Could not find server 'System' in sys.servers. Verify that the correct server name was spe
                            cified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
                               at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                               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 dataStream, BulkCopySimpleResultSet bulkCop
                            yHandler, TdsParserStateObject stateObj)
                               at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
                               at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
                               at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                               at ExecuteNonQuery(Object , Object[] )
                               at System.Management.Automation.MethodInformation.Invoke(Object target, Object[] arguments)
                               at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Obje
                            ct[] originalArguments)
                               --- End of inner exception stack trace ---
                               at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Obje
                            ct[] originalArguments)
                               at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Obj
                            ect valueToSet)
                               at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
                               at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
                               at System.Management.Automation.PipelineNode.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)
    TargetObject          : 
    CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    FullyQualifiedErrorId : DotNetMethodException
    ErrorDetails          : 
    InvocationInfo        : System.Management.Automation.InvocationInfo
    PipelineIterationInfo : {}
    PSMessageDetails      : 


    2. března 2012 20:29

Odpovědi

  • two things
     
    1 - you should probably use a here-string for the command...
     
    $cmd1.CommandText = @"
    USE [SOI_Documentation]
    ...
    "@
     
    2 - that’s an error from SQL, it doesn’t like the query, part one might fix
    your problem but you'll want to verify that what you think is there, is
    actually there..
     
    $cmd1.commandtext
     
    because you are using double quotes, its evaluating things..
     
    but looking at the error
     
    Could not find server 'System' in sys.servers. Verify that the correct
    server name was specified. If necessary, execute the stored procedure
    sp_addlinkedserver to add the server to sys.servers
     
    its trying to use a SQL link to another server that the server isnt linked
    to... you need to validate your query..
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    2. března 2012 21:45
  • Jrich, thanks for the help.

    I finally got around to using Chad Miller's Invoke-SQLCMD2 function.

    http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894

    http://poshcode.org/2279

    If anyone else reads this, his example uses ` -ServerInstance "MyComputer\MyInstance" `.  If your server has only one instance, only the computer is specified.

    Thanks again.

    2. března 2012 23:55

Všechny reakce

  • two things
     
    1 - you should probably use a here-string for the command...
     
    $cmd1.CommandText = @"
    USE [SOI_Documentation]
    ...
    "@
     
    2 - that’s an error from SQL, it doesn’t like the query, part one might fix
    your problem but you'll want to verify that what you think is there, is
    actually there..
     
    $cmd1.commandtext
     
    because you are using double quotes, its evaluating things..
     
    but looking at the error
     
    Could not find server 'System' in sys.servers. Verify that the correct
    server name was specified. If necessary, execute the stored procedure
    sp_addlinkedserver to add the server to sys.servers
     
    its trying to use a SQL link to another server that the server isnt linked
    to... you need to validate your query..
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    2. března 2012 21:45
  • Jrich, thanks for the help.

    I finally got around to using Chad Miller's Invoke-SQLCMD2 function.

    http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894

    http://poshcode.org/2279

    If anyone else reads this, his example uses ` -ServerInstance "MyComputer\MyInstance" `.  If your server has only one instance, only the computer is specified.

    Thanks again.

    2. března 2012 23:55