| Topic |
 |
RE: SQL Server Multiple Database Restore
Invoke-SqlRestore is part of SQLPSX http://sqlpsx.codeplex.com/. The sqlserver module contains the invoke-sqlrestore function. The function is just wrapper around SMO restore. Even if you don't use the function and instead choose to use your own SMO ...
by Chad Miller in SQL Server on 15 May 2012 10:07 AM
|
|
|
RE: SQL Server Multiple Database Restore
I don't have a complete example, but I do have an example of using Powershell to restore databases here: http://sev17.com/2011/03/restore-and-relocate-database-files-using-powershell/ Also SQL 2012 has a backup-sqldatabase cmdlet you could use if ...
by Chad Miller in SQL Server on 15 May 2012 03:24 AM
|
|
|
RE: Insert Into SQL Table
You could still use your DataTable with a TVP. Then inside of your stored procedure create logic for inserting null values. Here's an example of a TVP parameter stored procedure with Powershell from my blog: http://sev17.com/2012/04/appending-new-ro ...
by Chad Miller in SQL Server on 15 May 2012 03:21 AM
|
|
|
RE: sql query converts binnary to numbers
Since I don't have your database or understand your schema here's an example of taking your byte array and converting to a hexadecimal string representation: [Byte[]] $bArray = 178,211,248,111,132,12,250,66,139,16,97,247,59,100,149,2 ...
by Chad Miller in SQL Server on 16 May 2012 03:39 AM
|
|
|
RE: sql query converts binnary to numbers
You could convert the byte array to hexadecimal string representation by doing something like: '0x' + [System.BitConverter]::ToString($bArray).replace('-','')
by Chad Miller in SQL Server on 11 May 2012 10:29 AM
|
|
|
RE: Get Difference between two tables data;
Me too. I like the specificity of these forums i.e. Powershell + SQL Server, but the format, inability to mark questions as answered, lack of moderators and general feeling of being a forum format from 5+ years ago when compared to SF and even techn ...
by Chad Miller in SQL Server on 03 May 2012 04:45 PM
|
|
|
RE: Get Difference between two tables data;
Looks like attachments didn't go through here's links: https://docs.google.com/open?id=0B0jZ_IPvd-dXQ0dmLTJrclNNZ00 https://docs.google.com/open?id=0B0jZ_IPvd-dXUVNseEo2Q1JucFU
by Chad Miller in SQL Server on 02 May 2012 02:25 PM
|
|
|
RE: Get Difference between two tables data;
Summing two columns in Powershell instead of T-SQL doesn't really offload anything from SQL Server. For example if we take at look at the query plans for these two SQL statements (see attachment):--Sum in SQLSELECT a.x - b.x AS x, a.y -b.y AS yFROM ...
by Chad Miller in SQL Server on 02 May 2012 02:26 PM
|
|
|
RE: Get Difference between two tables data;
This seems like more of a SQL problem than Powershell or at least its a lot easier to solve in SQL. Your example is missing a primary key. You need some way of joining table A and B. Then you can do subtraction in a set based manner as follows: ...
by Chad Miller in SQL Server on 02 May 2012 04:38 AM
|
|
|
RE: Capture results from powershell;
It looks like you are using the command-line sqlcmd.exe. This utility doesn't output objects, just plain text. It will not work for what you want to do. Either download Invoke-Sqlcmd2 or use invoke-sqlcmd. In SQL Server 2008/2008 R2 you can run sqlp ...
by Chad Miller in SQL Server on 01 May 2012 03:19 AM
|
|
|
RE: Capture results from powershell;
You could use the invoke-sqlcmd cmdlet which is part of SQL Server 2008 and higher or use this invoke-sqlcmd2 function: Download the function. http://poshcode.org/2279 [code] . ./invoke-sqlcmd2.ps1 $dt = invoke-sqlcmd2 -ServerInstance ...
by Chad Miller in SQL Server on 30 Apr 2012 03:50 PM
|
|
|
RE: SQL Server 2012 snapins
SQL Server 2012 uses modules instead snapins. Once you install SQL Server 2012 the installer modifies the $env:PSModulePath to look to the new sqlps module in those directories. You should be able to just run import-module sqlps or import-moduel sql ...
by Chad Miller in SQL Server on 27 Apr 2012 06:48 AM
|
|
|
RE: SQL Server 2012 snapins
Are you running Add-PSSnapin in one of your Powershell profiles?
by Chad Miller in SQL Server on 26 Apr 2012 03:41 AM
|
|
|
RE: Powershell to save object to file
This problem is difficult to approach if you're not familiar with SMO, so I can understand why haven't made much progress. I went ahead and created a complete solution and posted it on my blog: http://sev17.com/2012/04/backup-database-object/ I no ...
by Chad Miller in SQL Server on 17 Apr 2012 06:22 PM
|
|
|
More SPAM
Here's some more SPAM comments:http://www.powershellcommunity.org/...fault.aspxIt would be nice to be able to flag these items as SPAM or have forum moderators. If you need a moderator for the SQL Server forum sign me up.
by Chad Miller in Suggestion Box on 17 Apr 2012 03:46 AM
|
|
|
RE: Invoke-Sqlcmd issue with variable substitution
Ah, yes invoke-sqlcmd2 I tend to use that over invoke-sqlcmd also :)
by Chad Miller in SQL Server on 09 Apr 2012 05:31 PM
|
|
|
RE: Invoke-Sqlcmd issue with variable substitution
Its not really invoke-sqlcmd as much as it is the arcane sqlcmd style variable substitution. I ran few tests and I get same result. If a variable has an equal sign this messes up the sqlcmd-style variable substitution. Personally I don't use or like ...
by Chad Miller in SQL Server on 07 Apr 2012 12:10 PM
|
|
|
RE: Eventhandler Question;
I'm having trouble reproducing your issue. I've implemented event handlers in my invoke-sqlcmd2 function http://poshcode.org/2279 with the only difference being write-verbose instead of write-host. If I pipe serverinstance names I get back the ex ...
by Chad Miller in SQL Server on 09 Mar 2012 08:58 AM
|
|
|
RE: Capture Results from dmv;
If you want to simply capture the output of the DMV every 5 minutes you run the query, convert the output to CSV and skip the header line and append the information to a CSV file as follows: Note: You'll need to grab invoke-sqlcmd2 from here http:/ ...
by Chad Miller in SQL Server on 07 Mar 2012 01:06 PM
|
|
|
RE: Writeoutput of PRINT commands to text file;
If you move your code into a script file it would be as simple as this to redirect to a text file: ./set-database.ps1 | out-file ./set-database.txt
by Chad Miller in SQL Server on 06 Mar 2012 02:22 PM
|
|
|
RE: Creating a batch file for sqlcmd commands
Powershell ISE does use BigEndianUnicode by default. You could try saving your script to notepad to change the encoding.
by Chad Miller in SQL Server on 04 Feb 2012 04:59 AM
|
|
|
RE: Creating a batch file for sqlcmd commands
I ran quick test and I'm unable reproduce errors. Here's my test: [code] echo $env:computername\sql1 | out-file filelist.txt; echo $env:computername\sql1 | out-file filelist.txt -Append $server = "$env:computername\sql1" $datab ...
by Chad Miller in SQL Server on 01 Feb 2012 02:23 PM
|
|
|
RE: ExecuteNonQuery not returning errors
Have you set your $ErrorActionPreference to stop?
by Chad Miller in SQL Server on 30 Jan 2012 04:03 PM
|
|
|
RE: Invoke-Sqlcmd doesn't output all server error messages
Try adding the -verbose parameter. The invoke-sqlcmd cmdlet uses the verbose switch to control whether RAISERROR and PRINT messages are output.
by Chad Miller in SQL Server on 25 Jan 2012 03:41 PM
|
|
|
RE: Get SQL Version via Powershell and WMI
Here's another way: [code] Get-WmiObject sqlserviceadvancedproperty -namespace "root\Microsoft\SqlServer\ComputerManagement10" -computername $env:computername | Where {@("SKUNAME","VERSION") -contains $_.Pr ...
by Chad Miller in SQL Server on 25 Jan 2012 03:39 PM
|
|
|
RE: Supress Printing Input Parameter information when calling stored procedure;
My guess is this it do some method call. Try piping the Add() method calls to out-null: [code]$cmd2.Parameters.Add("@rtn",[System.Data.SqlDbType]"Int") | out-null[/code]
by Chad Miller in SQL Server on 19 Jan 2012 05:08 PM
|
|
|
RE: Invoke-sqlcmd Error;
@mg48 $db.name -- good catch!
by Chad Miller in SQL Server on 19 Jan 2012 12:13 PM
|
|
|
RE: Invoke-sqlcmd Error;
When I run the following I see I make a SQL auth connection: [code]invoke-sqlcmd -Query 'select loginame from sysprocesses where spid = @@spid' -ServerInstance Win7boot\sql1 -database master -Username sa -password "mypassword"&# ...
by Chad Miller in SQL Server on 19 Jan 2012 03:59 AM
|
|
|
Flag Comments as Off Topic
As an example, telling someone to use bash and install gentoo isn't helpful. See this thread:http://www.powershellcommunity.org/...fault.aspx
by Chad Miller in Suggestion Box on 17 Jan 2012 03:59 AM
|
|
|
RE: access to files through sql agent job
Have you tried running a regular Powershell console as the SQL Server Agent account and testing your code?
by Chad Miller in SQL Server on 16 Jan 2012 09:18 AM
|