Tag Info

Hot answers tagged

7

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


7

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 ...


7

A cleaner way to do this would be to get the table object from the database object directly, and then drop it if it returns non-null. This will ONLY run if the table exists. #drop the Table $tb = $db.Tables['listeningport'] IF ($tb) {$tb.Drop()} When I deal with things like this in SMO my preference is ALWAYS to get the child objects from the parent ...


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 ...


6

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 ...


6

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

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))) ;


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 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 ...


5

You have two options, both of which will require SQL Server Management Studio to be installed on the host you are running the commands from: SQLPS SMO I lean toward the latter because it includes a bit easier format to learn for basic task such as getting a list of objects in an instance. With SMO you have to load the appropriate assemblies you want to ...


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

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 ...


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.


4

Code gets backup history for all databases on a server and generates HTML report Step 1: DECLARE @Body VARCHAR(MAX), @TableHead VARCHAR(MAX), @TableTail VARCHAR(MAX) SET NoCount ON ; SET @TableTail = '</body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: ...


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

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

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found]. The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ...


3

If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with. The method with the SqlDataSourceEnumerator is flaky at times and although I will use it, not concrete evidence that instances are on the ...


3

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else ...


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

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 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

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 ...



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