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

Všechny reakce