Executing a T-SQL command from Powershell

• 2. března 2012 20:29

   

   
  

  0

  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      : 

  
  

  
  

  • Upravený Brett Osiewicz 2. března 2012 20:50 Added the Error
  •  
  • Reagovat
  • Citovat