Tag Info

Hot answers tagged

7

Try this $db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};


6

I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell. Then you also have SQL Server PowerShell Extenstions project over on ...


6

Aaron Bertrand wrote a good post on it that is pretty detailed...How I use PowerShell to collect Performance Counter data. Then Laerte Junior has an excelent walk through on how he finds the counters he wants in a Simple-Talk article: Gathering Perfmon Data with Powershell. This might be where you want to start. It has some cmdlets that he uses to capture ...


6

If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL. http://msdn.microsoft.com/en-us/library/ms175575.aspx So, if you have a SQL 2008 ...


5

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations. I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older ...


5

You cannot loop through objects in the way you have coded. Here's the working code: Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; $ServerName = 'xyzabc123' $DatabaseName = 'test1' $TableName = 'main' $TableSchemaName = 'dbo' $Server = New-Object ...


5

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use. Your code would ...


5

I see from the comments that you've already given Compare-Object a try, but that really should be working. Below is a working example showing this: $SqlServer1Name = "SQL1" $SqlServer2Name = "SQL2" $SqlServer1 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer1Name) $SqlServer2 = New-Object ...


4

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine. #SQL 2008 R2 #add-type -AssemblyName ...


4

Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell The trick as Jonathan points out is that you need to have a lister attached to the connection manager listing for the print or error actions. $cd.Parameters.Add("@f", $bak.FullName) | Out-Null $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, ...


4

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results: $assemblies = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.


3

Figured it out! The key here was piping it out to an array successfully. I've had a lot of drawbacks here but you've got to work thru them. $pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and $_.path -like $filter} | select cookedvalue | ForEach ...


3

If you want to use PowerShell you can easily create task scheduler jobs in Windows to run the PowerShell scripts which would dump the output into a table or log file. If you want to use SQL you can easily create SQL Server Agent jobs to pull the data from the DMVs or DBCC and dump them in a table. It is difficult to understand exactly what you would want ...


3

You will have to use tokens in your job steps to get your own job id. Details here: Using Tokens in Job Steps. At the end of the article there's one example with jobid: SELECT * FROM msdb.dbo.sysjobs WHERE @JobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) ;


3

The lengths are found at <Column>.Properties['Length'].Value, so you can select it like: #Get column names $colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' | Where-Object {$_.DisplayName -match "dbo.MYTABLE"} | ForEach-Object {$_.Columns} | Select-Object Name, DataType, ` ...


3

Making the columns non-nullable and removing the filegroup code worked for me: # To Load SQL Server Management Objects into PowerShell [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) | out-null $server= new-Object ...


3

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet: Set-Location c: This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.


3

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name. You could either do this by accessing the Server.ConnectionContext property: $ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;" $SqlServer = ...


3

If you have a working maintenance plan, which is really just an SSIS package, you can export that .dtsx file out of the database and redeploy to the next one. For 2008/2012, those packages are stored in msdb.dbo.sysssispackages which you can verify with a query ; WITH FOLDERS AS ( -- Capture root node SELECT cast(PF.foldername AS ...


2

I have been thinking about a similar solution in our own environment. My ideas have been gearing towards the last two options (ssis & powershell). By having a single instance of your admin database you will be able to generate one report for all servers and do comparisons among your different instances. The reason I have been leaning towards ssis or ...


2

You need to try/catch the exception. $OutputFile="C:\output.txt" $HostName="MyServer" try { $con="server=$HostName;database=master;Integrated security=sspi" $da=New-Object System.Data.SqlClient.SqlDataAdapter (" declare @ServerRestartDate datetime set @ServerRestartDate = (select crdate from master..sysdatabases where ...


2

For SQL server 2008 you can use the new Data Collector feature. More on this at this link. You may need the Disk usage collection set. Nice graphical reports can be created if you use the feature that is already built in and free. To see how to create reports on Disk usage collection set, check out this link. You asked and Microsoft provided (joke).


2

4 for new work, 5 for reusing existing scripts or places T-SQL makes more sense than object-based/posh style code. I like those best because they are clear and simple.


2

sql sentry Quest spotlight for sql server enterprise Microsoft Operations Manager Various RedGate tools Your own scripts, data collectors and jobs Windows Performance monitor


2

You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this: $e = $error[0] $e.Exception $e.Exception.InnerException $e.Exception.InnerException.InnerException ... Until I ...


2

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs. # Get database files from where ever they are $databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf" $sqlCmds = @() # Parse db names from file ...


2

Sad to say I have not done much with PowerShell scripts being called inside SQL Server. Nor am I at a computer that I could play with it right now. I believe though instead of using the PowerShell type step that if you used CmdExec and just call your script as you would from a command line "powershell 'MyScript.ps1'" you could then pass a parameter that has ...


2

I'm going to outline a T-SQL way that uses most of what you already have. Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column) Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a ...


2

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little. Add this after the line setting $size = $_.Size $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)" $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate ...



Only top voted, non community-wiki answers of a minimum length are eligible