Hot answers tagged

13

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


13

As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, ...


10

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters. If you only want one result set, don't turn on @ExpertMode. If you want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in: ...


8

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


7

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


7

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


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

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


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


7

Not to discount Brent's answer (although he does give some good ones on his training at times)... In PowerShell you can execute queries that return multiple datasets, you just cannot do this using Invoke-Sqlcmd as it is not built for it currently. You would have two options of doing this with either .NET native code (e.g. System.Data.SqlClient) or using ...


6

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 Microsoft.SqlServer.Management.Smo....


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

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.


6

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, $event)...


6

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


6

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager... Configuration manager is also used to control instances, etc etc but the function of starting and stopping a service is the same no matter which method you choose. Some people have their own ways of doing things, for example ...


5

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, ` @{...


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 Microsoft.SqlServer.Management.Smo.Server($...


5

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


5

The casing of the instance name does not matter. You need to try: sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01 The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole ...


5

SSIS is well equipped to handle CSV files and load them into SQL Server. You can have a very simple package using the Flat File Source. The dialogue and setup is a familiar windows "wizard" like process, and most of it is automated... what you need to pay attention to is that it has correctly guessed your file for lengths and data types. You can either ...


5

Here's one method that doesn't require a looping construct: DECLARE @sql nvarchar(MAX) = N''; SELECT @sql += N'ALTER DATABASE ' + QUOTENAME(name) + N' SET RECOVERY FULL; ' FROM sys.databases WHERE database_id > 4 AND name NOT IN ( N'distribution', N'SSISDB' ); PRINT @sql; --EXEC(@sql); I agree with wBob's answer in that one need not be ...


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


4

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


4

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 $( )...


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

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


4

Apparentely, after some testing...there is a way to speed up SQLPS load time (tremendously) and prevent the WMI warnings (if you don't need the SQLAS module loaded. How I tested this on my laptop, was simply by shutting down the WMI service: Stop-Service winmgmt -Force. I then proceeded to edit the module files for SQLPS for my SQL Server 2012 local ...


4

Since I don't know exactly what error you are getting, I will just let you know what error I get based on your code and the database I chose to test against. I will note a good portion of the properties you are using, are not valid in SQL Server 2012 SMO (at least on my machine) The end result I used for the script is at the end. I got a few different ...



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