PowerShell & SQL Client Tools (SQLPS & SSMS)

Organization

Members

Lists

Info

  1. Comments

    abhisabh on New SqlServer module v 21.0.17279 released on the gallery

    Release Notes

    21.0.17279

    Fixes:
    * Fixed issue in Invoke-ProcessASdatabase which was throwing an exception when
    processing large tables.

    Updates:
    Name parameter on Get-SqlDatabase has been aliased as Database. Added -ErrorLogSizeKb parameter to Set-SqlErrorLog cmdlet.

    abhisabh on New SqlServer module v 21.0.17279 released on the gallery

    This month I was focused mainly on fundamentals and engineering systems so this update is relatively light.
    New cmdlets will be added in the next month.
    Thanks,
    Abhi

  2. Comments

    abhisabh on Set-SqlInstance design discussion

    Get-SqlInstance simply gets the SMO Server object (as of the last release). So Set-SqlInstance should be something that sets properties on the SMO Server object IMO.

    Maybe I can look at dynamically generating the parameters. Thanks.

    Ben Miller Ben Miller on Set-SqlInstance design discussion

    I wrote cmdlets to accommodate all of sp_configure dynamically with a dynamic parameter. But it really doesn't do validation, so if that is a concern then I understand not leveraging it.
    https://github.com/dbaduck/SqlConfiguationPS

    But I think that there some key ones for things like EKM, Max/Min Server memory, Cost Threshold, MaxDOP, XpCmdShell, and probably a lot more, but it makes sense that you don't create a set of parameters for all the items you can possibly get.

    And why Set-SqlInstance, would you create a Get-SqlConfigurationItem for the Gets? You couldn't use Get-SqlInstance because it already does something different.

    abhisabh on Set-SqlInstance design discussion

    Yea basically this will be get the SMO Instance, set the properties you want to change and then the cmdlet will call alter() on the SMO Server

    Similar to a cmdlet for sp_configure

    Ben Miller Ben Miller on Set-SqlInstance design discussion

    Are you meaning things like Max Server Memory and stuff?

    abhisabh on Set-SqlInstance design discussion

    Hello folks. Working on Set-SqlInstance cmdlet there are some design decisions that need to be made.
    Adding parameters for all the SMO properties that can be altered in this cmdlet is not scalable and simply too huge.
    We can provide some basic/popular properties as parameters but for all properties would it be fine to accept as a parameter a dictionary of key value pairs that we can input to the cmdlet and validate and update the SMO instance.

    If anyone has better ideas please comment and we will take that into consideration.
    Thanks!

  3. Comments

    Aaron Nelson Aaron Nelson on SQL PowerShell module 21.0.17262 (May release) available now!

    @abhisabh Thanks for adding the details of the latest release here. Much appreciated!

    abhisabh on SQL PowerShell module 21.0.17262 (May release) available now!

    https://www.powershellgallery.com/packages/SqlServer/21.0.17262

    Release Notes

    21.0.17262

    Fixes:
    Added StatementTimeout parameter in Backup-SqlDatabase cmdlet with default
    as 0 (no timeout). This fixes the case where Backup operations running longer
    than 10 mins used to timeout.     Fixed issue with Always Encrypted cmdlets, where NewtonSoft.Json assembly did
    not load.
    * Fixes the issue where Instance name was showing up as empty string on directly
    creating a SMO.Server object after SqlServer module is imported.

    Updates:
    Updated Get-SqlInstance cmdlet. This cmdlet now utilizes SMO and supports
    all server instances exposed via SMO.
    This cmdlet is now supported on PowerShell 6 as well     Important: Get-SqlInstance no longer uses CloudAdapter for Sql IaaS instances
    as CloudAdapter stopped shipping from SQL 2016 onwards.
    Users that still use this cmdlet for Sql 2014 IaaS instances
    should revert to an older version of the SqlServer module.
    * Added the following Vulnerability Assessment cmdlets:
    Export-SqlVulnerabilityAssessmentBaselineSet
    Export-SqlVulnerabilityAssessmentScan
    Import-SqlVulnerabilityAssessmentBaselineSet
    Invoke-SqlVulnerabilityAssessmentScan
    New-SqlVulnerabilityAssessmentBaseline
    New-SqlVulnerabilityAssessmentBaselineSet

  4. Comments

    Ben Miller Ben Miller on Feedback required for Get-SqlInstance

    Aaron, actually it is in a container and it is not a named instance. It is a default instance with a port redirected using NAT in Docker. Inside the container the instance uses 1433 but I have another container that uses 1433->1433 so I had to do a different port (5050->1433) so on my localhost I have to get to it by using localhost,5050.

    Aaron Nelson Aaron Nelson on Feedback required for Get-SqlInstance

    Ben, no need for the \default on the end. If you're working with a named instance, I think you would do 'localhost\sql2017,5050'

    Ben Miller Ben Miller on Feedback required for Get-SqlInstance

    @abhisabh I am trying to use this with containers (windows and Linux). SQL is started on 5050 port and I can connect and use it in SSMS. When I go to use the SqlServer Provider and get to a path, it doesn't like the localhost,5050\default in the path. Is there a trick to using ports with the provider or Get-SqlInstance?

    Tobias Svenblad on Feedback required for Get-SqlInstance

    Hi!

    I'm very new to this cmdlet, but I have two things I want to ask, in hopes I will understand how to properly use it.

    1. I cannot connect to a server instance with my AD account from the cmdlet. The only thing that works is by setting the SQL Server instance to Mixed mode authentication and creating a local SQL Login. Is it supposed to work this way by design? If so, why? And when will AD integration be supported?

    2. You should probably update the documentation over at https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlinstance?view=sqlserver-ps because there is no such parameter with the name "MachineName" anymore.

    Thank you for listening on the feedback! I hope it'll be useful and that I'm not a rambling jerk who is too stupid to understand how this cmdlet is supposed to work :smile:

    Aaron Nelson Aaron Nelson on Feedback required for Get-SqlInstance

    @abhisabh Abhi,
    Thinking a release or two ahead, hopefully we can have a New-SqlRegistration cmdlet at some point, and we something along the lines of:

    Get-SqlInstance -ServerInstance SQL1, SQL2, SQL3 | New-SqlRegistration -RegistrationType CMS

    or

    Get-SqlInstance -ServerInstance SQL1, SQL2, SQL3 | New-SqlRegistration -RegistrationType RegisteredServers

    I'm not in a rush for a New-SqlRegistration cmdlet. It's just a use case for Get-SqlInstance that I thought you might want to know about right now.

    abhisabh on Feedback required for Get-SqlInstance

    Thanks Aaron, I am putting that on my to-do list

    Aaron Nelson Aaron Nelson on Feedback required for Get-SqlInstance

    @abhisabh Thanks for the update Abhi! :smile:

    I know that many SQL Server professionals are going to love having a functioning version of this cmdlet for SQL Server on-prem!

    Quick note: please make sure that if someone uses this cmdlet to load into a variable, that it always tells the use what the name of the instance is, without a lot of fuss. (I forwarded you an email where Matteo and I discussed this previously)

    e.g.

    $My2016Instance = Get-SqlInstance -Serverinstance localhost\SQL2017;

    "$My2016Instance";

    In the example above, I would like to see localhost\SQL2017 be what PowerShell spits out. Not SQL2017, and not an empty result like New-Object Microsoft.SqlServer.Management.Smo.Server localhost\SQL2017 would produce.
    Thanks in advance, because little details like this can really trip up newcomers to SQL PowerShell.

    abhisabh on Feedback required for Get-SqlInstance

    We are updating Get-SqlInstance to use SMO and return a SMO Server object.
    Earlier this cmdlet was only used to get Sql IaaS instances and relied on CloudAdapter for Sql Server which stopped shipping after Sql2014 and so it makes little sense in continuing this cmdlet in it's current form.

    A note to our users: If you are still using this cmdlet to target SQL IaaS 2014 instances, our current plan with the latest version will break you and we will ask that you install one of the prior versions of the SqlServer module.

    If I receive a lot of feedback about this causing considerable friction to a large number of users (I don't expect many to be using Sql IaaS 2014 instances at this point) we can continue to ship this cmdlet under a different name.

    So if this affects you please leave a comment below.

  5. Comments

    abhisabh on SQL PowerShell module 21.0.17240 (April release) available now!

    A new module has been pushed and is available from the PS Gallery:
    https://www.powershellgallery.com/packages/SqlServer/21.0.17240

    Release Notes

    21.0.17240

    Fixes:
    * Fixed issue where PowerShell was not able to find cmdlets in the module
    unless the user did an explicit ''Import-Module SQLServer''

    Updates:
    Added Get-SqlBackupHistory cmdlet Ported PS Provider to .NET Core for PowerShell 6 support
    Ported a subset of cmdlets to .NET Core for PowerShell 6 support Powershell 6 support on macOS and Linux in Preview.
    To use SqlServer provider on macOS and Linux mount it using a new PSDrive.
    Examples in documentation.     Removed restriction of 64-bit OS for this module. Note: Invoke-Sqlcmd
    cmdlet is the only cmdlet not supported on 32-bit OS.

  6. Comments

    Ben Miller Ben Miller on SQL PowerShell updates March

    Yes, that is a good one. I have one coded all up though your stuff is a little more structured than mine, but mine works great. I can put it up on GitHub or whatever works. But this one should be there in your cmdlets because it is something that is core to the SQL Server settings.

    Aaron Nelson Aaron Nelson on SQL PowerShell updates March

    @abhisabh That's great news! :smile:

    My next suggestion for a cmdlet is [Get|Set]-SqlConfiguration. Which would basically be a map to sp_configure.

    After that, my next suggestion would be [Get-Set]-SqlQueryStore so that you could see and change all the settings for Query Store across all of your databases.

    However, I will happily get behind anything the community votes up as more important! :thumbsup:

    abhisabh on SQL PowerShell updates March

    We are planning to publish a new version of the module early next month with a new cmdlet (Get-SqlBackupHistory), fixes, and dotnet core/PS6 support (though not for the entire surface area)

    We are planning on doing one/two cmdlets a month along with fixes and incremental updates to dotnet core support.
    So for the May release we are looking at implementing Get-SqlInstance (with the other cmdlet TBD) which would work against on-prem/azure but let me know if there are any suggestions for the Get-SqlInstance cmdlet and I will try accommodating those.

    Labels

    • Informational

  7. I am back from paternity leave and SSMS 17 has been released!

    Even better news, the SqlServer module is now available in the PowerShell Gallery!!
    http://sqlps.io/sqlserver

  8. Comments

    Aaron Nelson Aaron Nelson on The SQL Server team now has dedicated resources for PowerShell, and they want to hear from you!

    @abhisabh No worries at all Abhi, better to have you here being active and seeing lots of updates! :thumbsup: :smile:

    abhisabh on The SQL Server team now has dedicated resources for PowerShell, and they want to hear from you!

    Sorry for spamming, still learning how the trello board works!

    abhisabh on The SQL Server team now has dedicated resources for PowerShell, and they want to hear from you!

    I can attest to this :)
    We are currently working on new cmdlets, and PowerShell 6/ dotnet core support.
    As we plan new cmdlets I would post it here and hopefully the community can help us design the parameters and the functionality

  9. Comments

  10. Comments

    Dave Carnahan Dave Carnahan on VOTE! VOTE! VOTE! Anyone can vote on the List itself or individual Cards in the List. It looks like you do need a Trello account though. To upvote: Click on Item -> Vote icon

    Hey, Hi !

    Does a module exist to allow a person to stay inside of a PowerShell session and execute an SSIS package (being provided any/all) of the command line switches and returning the error codes and console feedback?

    Right now I've created a PowerShell variable to point to DTEXEC.Exe and feed it the startup parameters.

    Thanks !

    Aaron Nelson Aaron Nelson on VOTE! VOTE! VOTE! Anyone can vote on the List itself or individual Cards in the List. It looks like you do need a Trello account though. To upvote: Click on Item -> Vote icon

    Want to know which cards are in the lead? Run this:

    $sqlps = Invoke-RestMethod https://trello.com/b/NEerYXUU.json
    $lists = $sqlps.lists | Where {$.closed -eq $false -and $.name -ne 'Info' }
    $cards = $sqlps.cards | Where closed -eq $false

    $dt = New-Object System.Data.Datatable
    $dt.TableName = "CardVotes"
    [void]$dt.Columns.Add("List")
    [void]$dt.Columns.Add("Card")
    [void]$dt.Columns.Add("Votes",[int])

    foreach ($list in $lists) {

    #Write-Output $list.name $listcards = $cards| Where idList -eq $list.id | Sort-Object $_.badges.votes #$listcards.count

    foreach ($card in $listcards) {
    if ($card.badges.votes -gt 0) {
    $Row = @()
    $Row += $list.name
    $Row +=$card.name
    $Row +=$card.badges.votes
    $dt.Rows.Add($Row)
    }
    }
    }

    $dt | sort Votes -Descending | ogv

  11. Comments

    Devin Rich Devin Rich on If there is a topic that you want and don't see, just comment on this card.

    Sqlcmd.exe and Invoke-Sqlcmd do not have feature parity. In order to avoid making Invoke-Sqlcmd support these features that are different between various versions of sqlcmd and Invoke-Sqlcmd, add a param to Invoke-Sqlcmd that lets you pass arguments to sqlcmd.exe directly. Don't know if it should be called -SqlcmdArguments, -DirectArgs, or something else.

    Example: The v13+ of sqlcmd.exe can use -G to authenticate with Active Directory. Not many people use this -G, but being able to do -Direct "-G" would work for my use case and all of the others that are not properly exposed in Invoke-Sqlcmd.

    Brian Lalonde Brian Lalonde on If there is a topic that you want and don't see, just comment on this card.

    I just had a coworker ask whether we should worry about this article: Use Caution with SQL Server's MERGE Statement, and I'm not sure exactly how panicked I should be, since we're using MERGE more and more.

    Chrissy LeMaire Chrissy LeMaire on If there is a topic that you want and don't see, just comment on this card.

    @brianlalonde Great news! The unapproved verbs issue has been resolved (along with the slow loading and changing of directory). Read more here

    Brian Lalonde Brian Lalonde on If there is a topic that you want and don't see, just comment on this card.

    SQLPS' Encode-Sqlname and Decode-Sqlname should be idiomatically named ConvertTo-Sqlname and ConvertFrom-Sqlname (Encode & Decode aren't legit PowerShell verbs)
    https://connect.microsoft.com/SQLServer/feedback/details/1871154/sqlps-encode-sqlname-and-decode-sqlname-should-be-idiomatically-named-convertto-sqlname-and-convertfrom-sqlname
    (this may belong in SQLPS active Connect Bugs)

    Brian Lalonde Brian Lalonde on If there is a topic that you want and don't see, just comment on this card.

    Importing SQLPS the first time shouldn't unexpectedly change the current directory to SQLSERVER:\ (breaking all subsequent relative paths in the script)
    https://connect.microsoft.com/SQLServer/feedback/details/1871239/requires-module-sqlps-or-import-module-sqlps-changes-pwd-to-sqlserver
    (this may belong in SQLPS active Connect Bugs)

    Chrissy LeMaire Chrissy LeMaire on If there is a topic that you want and don't see, Tweet to @sqlpowershell or just comment on this card.

    @sqlvariant dangit, did it again. Card. Scroll to right. (btw, please drag wherever you think is appropriate.)

    Chrissy LeMaire Chrissy LeMaire on If there is a topic that you want and don't see, Tweet to @sqlpowershell or just comment on this card.

    @kevinmarquette Added to board Invoke-Sqlcmd. Also listed connect item URL and marked it as blue. Thanks so much!

  13. Comments

    Chrissy LeMaire Chrissy LeMaire on Founding members are a couple of SQL Server and PowerShell MVPs who want to help make SQLPS awesome. Board members are SQL and PowerShell pros who have volunteered to help. Want to join? Comment here (Thank you all!)

    @chrissommer awesome! Thanks for joining! Please upvote anything you would like to see sooner rather than later. You can have multiple votes per list. I usually do up to 3. Don't see something you want? Add a card to a list and upvote it.

  14. If you are the original author of a card labeled in purple, please file that as a Connect item and post the link in the Card.

    Labels

  15. Labels

  16. Labels

  17. How will this work?
    Work on the top 3 in each list and archive when they get delivered?
    Frequency of call for votes?
    Prioritisation?
    etc

    Discuss

    Comments

    Rob Sewell Rob Sewell on Process of Delivery

    Good ideas - I will try and come up with a good welcome card tomorrow and talk to people at PSConfEU for their ideas
    Blogs - yes definitely, spread the word, spread the blogs out to catch more people and lets see where this takes us

    Ken Van Hyning on Process of Delivery

    That sounds like a good start to me Aaron. I think it's more important to just get something going and iterate on it. We can see what kind of interest it generates and how the voting goes.

    A good welcome and instruction card for the casual voter would be helpful.

    I am excited and confident that no matter what we do it is worlds better than what we have now as guidance for our engineering priorities in SQLPS currently. :-)

    Aaron Nelson Aaron Nelson on Process of Delivery

    I say that we all blog about this Trello board.

    Tell people that if they have a specific need that's not listed they can tweet to one of us, or the @SQLPowerShell handle. If their need is already on a card they can vote for the card. If they don't use PowerShell for SQL Server yet but would like to & they don't have a specific idea then they can vote for the list that seems most useful to them.

    Once votes are in, then we'd see whether people are voting for Lists, Cards, or Both.

    Thoughts?

    Aaron Nelson Aaron Nelson on Process of Delivery

    Call for votes should probably align roughly with the monthly drops of SSMS, no? The purpose of this board is to organize our thoughts and clean them up, debate terminology, and have others say "Oh, if we're going to ask them to build a cmdlet for X let's make sure to ask a -ThisThing parameter".

    As we go through that process I'm sure it will become natural for us to know which things are ready to be queued up for the next "wave" of requests; but since anyone from the community can vote on these things, hopefully that will give us a better idea as to what people need next.

    Also there is the factor of us submitting things and then having to wait at least 4~5 weeks to see them show up in a build, and possibly needing to submit the next wave of things before seeing the previous ones in action yet (assuming they are accepted). So logical progression is important.
    [By logical progression I mean: create the Get- cmdlets before the Set- cmdlets etc...]

    I think we should we should pick a day for the voting deadline (like say the first Monday of the month). The day after the voting deadline we submit to Connect and reset the vote counts.

    Those are my broad assumptions, I can't wait to see how it actually sorts out!

    UPDATE: Maybe we can't "reset" the votes so we'll just have to come up with a PowerShell script to record what the votes are on say May 2nd, then come June 6th record the votes again and subtract from previous months. <--Dates are not locked in, just guesses used for example.

    Rob Sewell Rob Sewell on Process of Delivery

    Chrissy asked - Unsure how this works, I voted for my favorite two or three. What do you think guys think about votes per list? Top two or three then as they get addressed remove and call for votes on a regular basis or something entirely different? Still new to this. /cc @sqlvariant @sqltoolsguy_msft

  18. Comments

    Chrissy LeMaire Chrissy LeMaire on Naming Conventions

    Naming cmdlets is an art. It's tough, and of course Microsoft ultimately decides on the cmdlet names, but we should put thought into what we suggest. @poshoholic recently said this after I asked for advice about naming (SqlTable in particular), and I think it's sound advice:

    ... suddenly I have commands that are up to 57 characters long. I think command names like these hurt the UX, especially for people coming from environments like C# where using statements can provide context for the commands that are used in a particular file/class:

    PS C:\> gcm -module azurerm* | sort {$.Name.Length} -Desc | select-object -first 10 | ft Name,@{n='Length';e={$.Name.L
    ength}}

    Name Length

    Get-AzureRmSqlServerDisasterRecoveryConfigurationActivity 57
    Remove-AzureRmNotificationHubsNamespaceAuthorizationRules 57
    Get-AzureRmSqlDatabaseTransparentDataEncryptionActivity 55
    Get-AzureRmOperationalInsightsWorkspaceManagementGroups 55
    Get-AzureRmNotificationHubsNamespaceAuthorizationRules 54
    Set-AzureRmNotificationHubsNamespaceAuthorizationRules 54
    Remove-AzureRmSiteRecoveryStorageClassificationMapping 54
    New-AzureRmNotificationHubsNamespaceAuthorizationRules 54
    Reset-AzureRmVirtualNetworkGatewayConnectionSharedKey 53
    Remove-AzureRmSqlServerDisasterRecoveryConfiguration 52

    My preferences for the items you raised above are:

    1. Database, not Db; it reads much better, and doesn't necessarily result in overly long command names.

    2. Table, not DatabaseTable; I break this down like this: is the Table noun unique to databases within the SQL noun set? If so, then Table is enough. If not, then DatabaseTable to uniquely identify the type of table we're talking about.

  19. According to PowerShell Approved Verbs

    Add: Adds a resource to a container, or attaches an item to another item. For example, the Add-Content cmdlet adds content to a file. This verb is paired with Remove.

    Active Directory examples

    Add-ADCentralAccessPolicyMember
    Add-ADDomainControllerPasswordReplicationPolicy
    Add-ADFineGrainedPasswordPolicySubject
    Add-ADGroupMember
    Add-ADPrincipalGroupMembership

    New: Creates a resource. For this action, do not use verbs such as Create, Generate, Build, Make, or Allocate.

    Active Directory Examples

    New-ADAuthenticationPolicy
    New-ADComputer
    New-ADGroup
    New-ADObject
    New-ADOrganizationalUnit
    New-ADServiceAccount
    New-ADUser

    Even with these examples, we may still make mistakes. Please comment here or on the cmdlet name if you feel it was not named appropriately.

    Comments

    Chrissy LeMaire Chrissy LeMaire on Naming: New vs. Add

    @matteo437 haha! I'm the worst at naming. I was happy to see you guys decided on ConvertTo and ConvertFrom for encoding the sql name.

    Like @sqlvariant has pointed out, the SQL world has an additional battle with verb expectations. PowerShell likes Set whereas SQL people expect Alter. Jeffrey Snover said yes to Aaron's suggestion for Set with an Alter alias. I like that, too.

    Matteo Matteo on Naming: New vs. Add

    Note to self: every night before going to be, read at least 1 section of the "PowerShell Approved Verbs" topic (alternatively, shoot @ctrlb an e/mail :-P)

  20. According to PowerShell Approved Verbs

    The New verb is used to create a new resource. The Set verb is used to modify an existing resource, optionally creating the resource if it does not exist, such as the Set-Variable cmdlet.

  21. According to PowerShell Approved Verbs

    The Find verb is used to look for an object. The Search verb is used to create a reference to a resource in a container.

  22. According to PowerShell Approved Verbs

    The Get verb is used to retrieve a resource, such as a file. The Read verb is used to get information from a source, such as a file.

  23. According to PowerShell Approved Verbs

    The Invoke verb is used to perform an operation that is generally a synchronous operation, such as running a command. The Start verb is used to begin an operation that is generally an asynchronous operation, such as starting a process.

  24. https://msdn.microsoft.com/en-us/library/ms162209.aspx

    Comments

    Aaron Nelson Aaron Nelson on Reference: SMO Object Model Diagram

    Haha, no. I just wanted to give people a resource if they wanted to test something out before they posted about it.

    alzdba alzdba on Reference: SMO Object Model Diagram

    are you aiming for a "get-SQLSMOObjectModelDiagram" which projects the referenced url ?
    function get-SQLSMOObjectModelReference {
    explorer.exe 'https://msdn.microsoft.com/en-us/library/ms162209.aspx'
    }

    get-SQLSMOObjectModelReference

Wins!!!

  2. This should be able to return Objects from a specified table that relate to the Rows in the Table

    I would expect it to default to work like SELECT * FROM [Tablename] unless there was an applied filter via the following additional parameters

    -NumberToReturn [int]
    -ColumnsToReturn [String[]]

    And as this should be outputting a collection of Objects for each row this could then be Pipelined to other Core Cmdlets like Group-Object, Select-Object or Sort-Object which would be more intuitive for PowerShell people

    Comments

    Ayan Mullick Ayan Mullick on Read-SqlTableData

    The attributes should be accessible like when one uses import-excel with an excel file..

    Ayan Mullick Ayan Mullick on Read-SqlTableData

    It should have the mssql-cli intellisense
    https://youtu.be/C07udTA1loI?t=509

    Matteo Matteo on Read-SqlTableData

    @ctrlb Move to "Wins!!!", please :-)

    @michaelmaher11, @troy464: I've captured your comments/issues/etc and I'll look into them later, don't worry!

    Matteo Matteo on Read-SqlTableData

    @michaelmaher11 Hi Michael, I'll try to look into it tomorrow and see if I can repro (and fix, if needed). Thanks for reporting the issue...

    Michael Maher Michael Maher on Read-SqlTableData

    Switching one of my scripts over from using an Out-Datatable function to the new Write-SQLTableData. A lot of my data gets rejected with the message

    Write-SqlTableData : 'dataType' argument cannot be null.
    Parameter name: dataType
    At line:1 char:18
    + ... adResults | Write-SqlTableData -ServerInstance $kSQLSERVER -DatabaseN ...
    + ~~~~~~~~~~~~~
    + CategoryInfo : WriteError: ([dbo].[adAllUsers]:Table) [Write-SqlTableData], ArgumentNullException
    + FullyQualifiedErrorId : WriteToTableFailure,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

    Here a snippet of code to test - can you reproduce this?

    $adResults = Get-ADUser -Filter {SamAccountName -like $adScope} -properties * | select lastLogonTimestamp, LastLogonDate, Name, GivenName, SurName, SamAccountName, uidNumber, gidNumber, employeeID, ` extensionAttribute1, extensionAttribute2, extensionAttribute3, extensionAttribute6, @{L='manager'; E={(Get-ADUser $_.Manager).samaccountName }}, ` mail, CanonicalName, lastLogon, PasswordExpired, PasswordLastSet, Enabled, AccountExpirationDate, ` @{L='ExchangePrimarySMTP'; E={($_ | select -ExpandProperty ProxyAddresses | where {$_ -clike "SMTP:*"}) -replace "SMTP:",""}}

    $adResults | Write-SqlTableData -ServerInstance $kSQLSERVER -DatabaseName $kDBName -SchemaName dbo -TableName $kSQLTableAD

    Matteo Matteo on Read-SqlTableData

    @troy464 Good suggestions. I'll look into it for the next version of the cmdlet.

    Troy on Read-SqlTableData

    @matteo437 do you have a default value for Top parameter? If not, I think you should add the same that SSMS uses for consistency.

    Agreed on comments about only doing table/view and let the bigger stuff be done via invoke-sqlcmd.

    One killer feature though. Add datareader as an option and provide pipeline support by doing:
    Begin { $datareader.Open() }
    Process { run sql }
    End { $datareader.Close() }

    This allows us to pipeline the results of the SQL row-by-row to other cmdlets and have it close the connection when it's done with the last row. This exact scenerio was my a-ha moment on understanding the pipeline.

    This option would improve performance and allow for processing larger tables since it won't hold all the results in memory. I did this in a cmdlet called Export-SqlBlobs which allowed me to extract 50GB of data without burdening the client or server.

    Chrissy LeMaire Chrissy LeMaire on Read-SqlTableData

    @matteo437 Totally understand that, too, considering the whole monad thing. Your lack of being thrilled is justified, and your pragmatism is appreciated, even though I sometimes initially propose against it hahah.

    Matteo Matteo on Read-SqlTableData

    @sqlvariant , @ctrlb I think that Table/View might play nice with the current cmdlet model. It might be a more "dramatic" change to implement if we want to suppose SPs and UDFs... I'd have to look more carefully.

    In general, I'm not too thrilled at the idea of "uber-cmdlets" that do "everything"...

    Matteo Matteo on Read-SqlTableData

    @sqlvariant Yeah, I was investigating that class... I'l resume tomorrow...

    Chrissy LeMaire Chrissy LeMaire on Read-SqlTableData

    @sqlvariant Great name, actually! Perhaps it could auto detect?

    Aaron Nelson Aaron Nelson on Read-SqlTableData

    @ctrlb Chrissy, if you expand it to stored procs you also need to include UDF, at which point the name would need to change to Read-SqlObjectData (or something like that). I would hate to have to specify -Table or -View. I don't have to do that with a SELECT statement.

    Aaron Nelson Aaron Nelson on Read-SqlTableData

    @matteo437 Since the type is Smo.TableViewBase I just assumed you'd be able to query both tables & views with it.

    Chrissy LeMaire Chrissy LeMaire on Read-SqlTableData

    @matteo437 Totally agree on both points! I just want some data. Read-SqlData? -Table -View, etc? I'd also like the results from a stored procedure.

    Matteo Matteo on Read-SqlTableData

    @sqlvariant, @ctrlb and others: is there any interest in reading data from Views?

    It felt a little odd that I cannot query my favorite system views the same way I query the table.

    It would also feel weird to use Read-SqlTableData to get data from a View, to be honest.

    What do people think?

    Chrissy LeMaire Chrissy LeMaire on Read-SqlTableData

    @matteo437 -Top is great an aligns with SQL Server. -First is a nice alias, because it aligns with PowerShell ;) Agreed, NumberToReturn is not pretty.

    Overall, FREAKING AWESOME AND LOVE IT!

    Matteo Matteo on Read-SqlTableData

    @ctrlb, @sqlvariant, @dbaduck, @ryanyates6:

    I thought I'd share the v0.1 of the cmdlet (see attachment, Read-SqlTableData_v0.1.PNG).

    It shows a bunch of the parameters I've implemented + the fact that it is context-aware (=it plays nice with the SQL PS Provider).

    Current parameters:
    - TopN (aliased to NumberToReturn) - not a big fan of the "NumberToReturn", what do people think? Should I leave it there?
    - ColumnName (aliases to ColumnToReturn) - an array of column names (the cmdlet will escape/quote for you)
    - ColumnOrder (aliases to OrderBy) - an array of column names to sort by
    - ColumnOrderType - an array of things like ASC or DESC (it should match the length of ColumnOrder) to tell the cmdlet how to sort
    - OutputAs (aliases to As) - the preferred output type (DataRows, DataTable, or DataSet); by default, it is DataRows for consistency with Invoke-SqlCmd, but I'm open to suggestions

    That cover pretty much all that was asked... "and then some" :-)

    Send impressions and feedback!

    Chrissy LeMaire Chrissy LeMaire on Read-SqlTableData

    Happy to! Done.

    Matteo Matteo on Get-SqlTableData

    @ctrlb - could you kindly rename this item to "Read-SqlTableData"? Thanks!

    Matteo Matteo on Get-SqlTableData

    Quick update here... I finally started looking into this item and its companion, which I'm going to call Write-SqlTableData.

    I started putting together all the info I got from all the sources (Trello, private e/mails, twitter, etc...) to get myself a reasonable "plan of action" and making sure I'm not missing all your input!

    After that it's going to be the fun part: implementation!

    Matteo Matteo on Get-SQLTableData

    @sqlvariant I don't recall seeing one yet. I'll "fish" for connect items later today, in case something came up...

    Aaron Nelson Aaron Nelson on Get-SQLTableData

    Did anyone file a Connect item?

    Ben Miller Ben Miller on Get-SQLTableData

    I concur. Read sounds much more complimentary and I am good with that idea. I am excited to have a way to do this simply.

    Chrissy LeMaire Chrissy LeMaire on Get-SQLTableData

    For all who are wondering, Snover said in response to "I'm interested in why the PowerShell team picked Get instead of Read [for Get-Content]" It's because they initially looked to SNMP which uses Get/Set but later expanded the verbs. That seems to imply that if Get-Content were named after the verb additions were made, it would be Read-Content.

    Chrissy LeMaire Chrissy LeMaire on Get-SQLTableData

    Jeffrey hath spoketh. It's Write. Proper pairing seems most important :D

    https://twitter.com/jsnover/status/745986259256901633

    Chrissy LeMaire Chrissy LeMaire on Get-SQLTableData

    @matteo437 I also do like Read to accompany Write.

    Chrissy LeMaire Chrissy LeMaire on Get-SQLTableData

    @matteo437 It's interesting, like @dbaduck, Get felt natural to me, too, but upon looking it up, you're right!

    Get vs. Read
    The Get verb is used to retrieve a resource, such as a file. The Read verb is used to get information from a source, such as a file [or in this case, a table].

    That gets me thinking of Get-Content. I'm interested in why the PowerShell team picked Get instead of Read. Will ask on Twitter.

    Matteo Matteo on Get-SQLTableData

    @dbaduck Yep - I could be a nice companion of Write-SqlDataTable... I wonder if the name should be Read-SqlDataTable instead of Get-DataTable... uhmmmmmm @ctrlb, what do you think?

    Ben Miller Ben Miller on Get-SQLTableData

    The power of this one for me is to see the following:

    SQLSERVER:\SQL\server\default\Databases\DB1\Tables\dbo.Table1> Get-SqlDataTable

    Or Get-SqlDataTable -Path SQLSERVER:\SQL\server\default\Databases\DB1\Tables\dbo.Table1

    Then the parameters:
    -NumberToReturn [int]
    -ColumnsToReturn [string[]]
    -OrderBy [string[]]

    And other sundry things to control the output. I would imagine that you would want to have -ServerInstance, -Database, -Table, -Schema as well so that you can use it without a path.

    I don't think that the PowerShell DBA would want to use TSQL to do things, but to use Parameters, and the Pipeline.

    Technically I could have a SMO.Table object and do something like this:

    $tableobject | Get-SqlDataTable

    This would return my rows in a DataTable object.

    Matteo Matteo on Get-SQLTableData

    @ryanyates6 yeah, I think I see your point (and it is not too different from other existing/upcoming cmdlets). So, yeah, please go ahead and share your ideas here... We are definitely after all the "real world" scenarios where "the DBA guy meets the PowerShell guy to become just one super-productive persona" (wow - I start sounding like marketing folks, don't I? :-)). Feel free to sync up with @ctrlb and @sqlvariant - they know what I am talking about...

    Ryan Yates Ryan Yates on Get-SQLTableData

    @matteo437 - I see the flexibility POV and agree with you from the DBA mindset however thinking of those that are from a non-SQL background I think that they would find it easier not to have to delve into writing T-SQL when they just want to quickly get information from a table.

    I think it really should just be a thin wrapper around the Invoke-SqlCmd cmdlet that allows for quick and dirty returning of data in a way that is friendly to those with no T-SQL experience but likely have PowerShell Experience.

    I could put together a mockup of how I think that could work if that would help to visualise what I'm thinking?

    Aaron Nelson Aaron Nelson on Get-SQLTableData

    @matteo437 Nice, very nice. Yes, precisely. :clap:

    @ryanyates6 did you see that?!?

    Matteo Matteo on Get-SQLTableData

    @sqlvariant Is this what you had in mind?

    CD SQLSERVER:\sql\VLM00219948\DEFAULT\Databases\BBB\Tables\dbo.Table_1
    Invoke-SqlCmd -query "select * from $(get-item .)"

    $PWD is just a string, so it is not super useful. But the magic conversion from a table SMO object to a string seems to generate the table name!

    Aaron Nelson Aaron Nelson on Get-SQLTableData

    Taking @matteo437's example one step further, I wonder if something like this could be possible:

    CD SQLSERVER:\SQL\VLM00220377\DEFAULT\Databases\BBB\Tables\dbo.Employees
    Invoke-SqlCmd -Query 'select * from $PWD' | ... <your favorite pipeline>

    Might take some intelligent trimming with the Invoke-SqlCmd cmdlet to get the last node of the path.

    Matteo Matteo on Get-SQLTableData

    Hey @ryanyates6, I don't see this to be a lot different from something like:

    CD SQLSERVER:\SQL\VLM00220377\DEFAULT\Databases\BBB
    Invoke-SqlCmd -Query 'select * from [Table_1]' | ... <your favorite pipeline>

    which has the flexibility of allowing you to specify whatever T-SQL you want (i.e. add a TOP N to get your filtering on the # of rows, add a bunch of columns to select project on the columns you care about - plus a lot more that would have to be added/duplicated in the Get-SqlTableData cmdlet).

    What do you think?

    Labels

  3. https://trello.com/c/7HbLhAQH

    Labels

  4. Labels

  5. Comments

    Tom Roush on Get-SqlLogin and Remove-SqlLogin (August 2016)

    Cool... We were running into this recently - trying to remove a login only to have it own some sort of endpoint on the server (I don't remember exactly)
    does the cmdlet change the owners of those things to sa before trying to drop the user/login?
    [or have it say, "this user owns object x in <location>, do you want the owner of this object to be changed to SA <or service acct> and then have the original user dropped?>
    That's real life stuff... Thoughts?

    Labels

  6. Labels

  7. https://trello.com/c/reJfNNcu
    https://trello.com/c/SP3LcmQw

    Labels

  8. https://connect.microsoft.com/SQLServer/feedback/details/2551673

    From Matteo:

    Hi Mike,
    Thanks for filing this connect bug. It is indeed a good suggestion. I've added a new -ConnectionString parameter that allows you to pass your own custom connection string. Hopefully, this will be available in a near future (I'm going to update this connect bug when it happens).

    Here's an example of what you can will be able to do:

    PS C:\> Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM MyTable" -ConnectionString "Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly"

    Count

    127432

    Labels

  9. Source

    Comments

    Chrissy LeMaire Chrissy LeMaire on Microsoft mentions Trello boards at SQLBits

    Thanks, @ryanyates6, for the photo!

  10. https://trello.com/c/c3iPbVbU

    https://twitter.com/sqldbawithbeard/status/728954817327071234

    Comments

    Chrissy LeMaire Chrissy LeMaire on Alter-SqlTable

    Thanks, @robsewell1, for the photo!

  11. https://trello.com/c/Yey34lqP

    From the Connect Item

    "Hey Everyone,

    My name is Vin from the tooling team here in SQL Server, and wanted to let you know that we looking at this Connect item as a part of improving our command line (PS, sqlpackage, sqlcmd, etc.) experiences. Thanks for sharing this feedback with us, and if you have any feedback or questions, please continue to add to this thread or feel free to reach out to me directly at vinsonyu{at]microsoft.com.

    cheers,
    Vin"

  12. https://trello.com/c/tH2CpcLZ

    From the Connect Item

    "Hey Everyone,

    My name is Vin from the tooling team here in SQL Server, and wanted to let you know that we looking at this Connect item as a part of improving our command line (PS, sqlpackage, sqlcmd, etc.) experiences. Thanks for sharing this feedback with us, and if you have any feedback or questions, please continue to add to this thread or feel free to reach out to me directly at vinsonyu{at]microsoft.com.

    cheers,
    Vin"

  13. https://trello.com/c/lwIKNHrd

    From the Connect Item

    "Hey Everyone,

    My name is Vin from the tooling team here in SQL Server, and wanted to let you know that we looking at this Connect item as a part of improving our command line (PS, sqlpackage, sqlcmd, etc.) experiences. Thanks for sharing this feedback with us, and if you have any feedback or questions, please continue to add to this thread or feel free to reach out to me directly at vinsonyu{at]microsoft.com.

    cheers,
    Vin"

  14. https://trello.com/c/reJfNNcu/107-get-sqlerrorlog

  15. I actually hope we come up with a better idea that this one, as returning all properties is a bit much. However, the default ones that are currently returned is a bit much.

    Currently the Get-SqlDatabase cmdlet will only return Name, Status, Containment Type, Recovery Model, CompatLvl, Collation, & Owner properties for a database. Get-SqlDatabase should have a -force option to return all properties of the database.

    Get-SqlDatabase -ServerInstance localhost -force

    Comments

    Aaron Nelson Aaron Nelson on Get-SqlDatabase -ServerInstance localhost -force

    I'm calling this a win. Improvements have already been made, and the path to getting more improvements included is pretty simple.

    Labels

Overall Priorities

  1. Install-Module TrelloVoteCount
    Get-SqlPsVote

    Comments

    Chrissy LeMaire Chrissy LeMaire on Upvote items you'd like to see sooner. Up to 3 votes per list is ideal.

    I made a module for tallying votes and published it to the PowerShell Gallery.

    Install-Module TrelloVoteCount

    This gives you 3 functions
    - Get-SqlPsVote
    - Get-TrelloVoteCount
    - Get-SsmsVote

    Get-SqlPsVote shows the tally from this board in a gridview

    Get-TrelloVoteCount is a generic function that works with any public board.
    Example: Get-TrelloVoteCount -Id 0zgQjktX

    Get-SsmsVote shows the votes from the SSMS board: https://trello.com/b/M9NmFPfv/sql-server-management-studio-2016-enhancements

    Chrissy LeMaire Chrissy LeMaire on Upvote items you'd like to see sooner. Up to 3 votes per list is ideal.

    @robsewell1 changed name because other vote card is now more prominent with picture.

  2. Upvote connect item here, we are already at over 200 but need more!

    http://sqlps.io/opensource

    Comments

    Chrissy LeMaire Chrissy LeMaire on Open Source and put on GitHub / Gallery

    Gotta make that pic smaller when I'm not on mobile.

    Labels

  5. SSRS is the only major component that isn't included in the SQL Server Provider. This should be added to at least give us the same capabilities as in SQL Server Object Explorer.

    Furthermore, any settings you can configure via Reporting Services Configuration Manager you should be able to configure a cmdlet.

    This

    Comments

    Parth Shah on Reporting Services (SSRS)

    @seanmccown We recently published PowerShell scripts that would allow you to import and export encryption key. Please check it out at https://github.com/Microsoft/Reporting-Services/tree/master/Scripts/Admin

    Alfredo Araiza on Reporting Services (SSRS)

    This would be a useful enhancement.

    js0505 on Reporting Services (SSRS)

    Are you talking about something like how SSRS was implemented with the below?
    https://jesperarnecke.wordpress.com/2015/07/26/crm-server-scripted-installation/

    Lynn Sickler on Reporting Services (SSRS)

    Getting information via powershell would be a great help!

    Sean McCown Sean McCown on Reporting Services (SSRS)

    Hey Kevin, can you share that code with me? I tried a couple yrs ago to get the encryption key stuff done in PS and I couldn't get it to work. It should have been very easy, and it didn't fail, it just didn't do anything if IIRC. Like I said, it's been a while, but back then I couldn't get it.

    Kevin Marquette Kevin Marquette on Reporting Services (SSRS)

    Scripting these things via command line is currently a lot of work. I found ways to handle some things like encryption key import/export. But I still don't know how to change the main database connection string outside of this GUI. I do agree that all of these should be scriptable actions.

    Labels

  6. Please add your requests on the corresponding list!

    All I know for now is that we all want Tab-Completion to be sped up.

    Labels

  7. The things in Microsoft.SqlServer.SqlWmiManagement, like changing the SQL Server Service Account, make it easier than the code outlined here

    SQL Server Configuration Manager

    Labels

  8. Comments

    Dave Carnahan Dave Carnahan on Integration Services (SSIS)

    PowerShell and SSIS:

    Is there a module to enable a PowerShell user to stay inside of a PowerShell session and invoke an object to execute an SSIS package passing the different input parameters e.g. '/File', '/Config1,2,...' and '/Set'.

    Then after completion, return the error codes and console output?

    Right now, I've got PowerShell variables feeding into the CMD shell to an appropriate version of DTEXEC.EXE.

    If not, could a request be initiated to make this so?

    Thanks-

  9. Comments

  10. This topic is also being discussed on the https://trello.com/c/BlWkojrv Card.

    Comments

    Aaron Nelson Aaron Nelson on Desired State Config (DSC)

    @robsewell1 Up-Voted! :thumbsup:

    Rob Sewell Rob Sewell on Desired State Config (DSC)

    Awesome @gaelcolas I have added that to the Connect item https://connect.microsoft.com/SQLServer/feedback/details/2708334
    if that's ok?

    Chrissy LeMaire Chrissy LeMaire on Desired State Config (DSC)

    @gaelcolas That's actually pretty darn persuasive! I like the idea of using DSC as a guideline for the basics of what needs to be supported (which includes my favorite, New-SqlLogin/Get-SqlLogin ;))

    Chrissy LeMaire Chrissy LeMaire on Desired State Config (DSC)

    @johanljunggren Hey Johan, have you tried using a newer version of SQLPS? The path issue was addressed (at least partially, it doesn't CD to it on startup) in March 2016 Refresh of SSMS, and SQL Server 2016 RC2 (I believe). It now also loads faster and doesn't give verb warnings because the unapproved verbs have been aliased.

    gaelcolas gaelcolas on Desired State Config (DSC)

    If I may add my 2 cents, which might be specific and in a minority here.
    I'm not a DBA nor a Dev (in the traditional sense at least), but I still have to deploy SQL servers, and in a consistent way with other application, that is using DSC for me. A decent DSC Module would help improving the interface with the DBAs (saving them config time), while ensuring better integration and tests before production. Like other have said before, a good DSC resources starts with a good SQL module. Make it open source, get the base of a DSC resource to go along in github, and we'll contribute heavily!
    Did I mention that I'm working for a Cloud Provider, automating deployment for MSSQL as a Service, and we expect to deploy quite a few instances... that means $$$ in SQL licenses delayed by the painful automation... Is that an incentive that would help? :smile:

    Johan Ljunggren on Desired State Config (DSC)

    Hello, as in many comments I agree we need more focus on SQLPS. I have written a DSC module which can configure an AlwaysOn HA using a bunch of resources (you can fint it here http://bit.ly/1YITytP ). The common thing in these resources is that the are all using SQLPS. But in doing so I had a problem with just loading the module correctly. I you look in the Util module and the function Import-SQLPSModule you will se I have to remove the SQLPS module before importing it. I have to do this for every resource that is run, make sure it is removed and then import it again. Otherwise I'm getting strange errors like throwing errors that paths (SQLSERVER:\SQL....) doesn't exist (one command can work, and the next command throws errors).
    My point is. I think the SQLPS module isn't really modern in that aspect. We need a more stable module before we even start adding cmdlets to it.

    Braun Reyes Braun Reyes on Desired State Config (DSC)

    DSC resources related the SQL Server specifically would be awesome, especially for chef development related to SQL Server. Being able to do things like set lock pages in memeory and Instant File Initialization appropriately would be helpful.

    Ben Miller Ben Miller on Desired State Config (DSC)

    First, I can see the points of view expressed and I understand why each is made. From my perspective, I lean toward the sentiment to not let this get away while we fix the SQLPS stuff so that it is done in tandem, still more focus on SQLPS, but as has been said, confusion and lack of attention will drive lack of adoption of the resource. I frequently have need to change things or to make sure that a DBA database is created in an instance and would love to have that in a DSC resource for my clients. Yes, I can run a script to get this done, but to know whether things are not in the desired state would be very helpful on my side of the consulting engagements. So I hope that we at least focus some attention to this space so that we have a supported way to use DSC and build bridges between DBAs and IT people to work together. This seems like a great way to do that and to give me a tool in DSC to get some good things done. I will still probably build some custom resources, but to have plumbing there and available would just make things more consistent for those that are not going to build their own resources.

    Laerte Laerte on Desired State Config (DSC)

    IMHO,
    I´ve played with DSC and SQL (usually writing SQL resources using SMO) . To be honest I agree with @ctrlb . Right now we dont have a real PowerShell support for SQL in basic tasks. Another point is DBAs usually dont play the role where DSC would be useful as Cofig Management, Devops, Sysadmin..whatever.. It´s not the focus of the most of the SQL Server DBAs.
    Other point, I only know the basics of PBM, but what DSC could be more useful ,for DBA roles, instead of PBM ?
    My 2 cents :)

    Aaron Nelson Aaron Nelson on Desired State Config (DSC)

    Well that's what they did in v1 of DACPACs :smile: :facepalm: "data is important and cannot be just 'blown away and then made so' "

    Rob Sewell Rob Sewell on Desired State Config (DSC)

    @wshawnmelton @maximotrinidad we had a long and detailed discussion of this at PSConfEU between some DBAs and some developers who have created resources already

    There were two points of view

    One exactly as you said - leave it until the SQLPS is sorted and then use SQLPS for this. Don't muddy the waters, keep the focus on SQLPS

    The other is that there is benefit in moving both forward alongside each other as if we leave it the SQL DSC resources will become more fragmented, confusing people and frustrating them and may leave them to write their own resource for their own needs adding to the confusion.

    The SQL community experts really need to be involved in the creation of the resources now before production DSC resources make big mistakes with SQL as data is important and cannot be just 'blown away and then made so' We needed to explain how resolving issues with SQL would not be acceptable to DBAs using DSC to 'make it so' when the risk is losing data and that you cannot treat SQL the same as say a web server. Careful testing is required.

    Our conclusion was to suggest merging everything into one resource and to explain what we are doing, how to contribute and request features and carefully and iteratively release. This could be combined with the release of SQLPS features.

    A possible starting point is to first just provide a stable, best practice method of installing SQL to at least assist DevOps guys with less SQL knowledge a method of providing a SQL Server for their system

    This is my personal preferred option but I am not the expert in either field. I hope to get further feedback from others at SQLBits in a couple of weeks

    And I agree with your SMO comment

    Shawn Melton Shawn Melton on Desired State Config (DSC)

    I would say this is bottom of the totem poll with everything else that needs to even be put in place before SQLPS could be utilized for DSC. As well wouldn't it be more efficient to use SMO itself in DSC, all the examples I have seen in PS gallery are using it?

    Maximo Trinidad Maximo Trinidad on Desired State Config (DSC)

    Hum! I think including DSC issues on this board takes away the focus on SQLPS, You guys are creating another realm within this board. We should concentrate in identifying what cmdlets are missing in SQLPS. At least that's my opinion.

    ArieH on Desired State Config (DSC)

    As noted on the merge of the 2 resources:

    Make sure you move some settings outside of the resource or make sure you have a Test for them. At the moment the Test only looks for services and depending on them decides what features of the SQL are not in the desired state.

    Should leave settings that you do not intend to test outside of the resource itself.

    Ben Miller Ben Miller on Desired State Config (DSC)

    The main thing I would like to see with DSC is related to the configuration of SQL Server and its components. Having DSC there to just test the configuration to see if it is in desired state is huge from the health checks I do and then to be able to just make the changes with applying the DSC resources would be welcome from my side.

    Rob Sewell Rob Sewell on Desired State Config (DSC)

    @ctrlb And sure, I will happily show you. I am looking forward to spending time with people who don't roll their eyes when I say Powershell :D

    Rob Sewell Rob Sewell on Desired State Config (DSC)

    @ctrlb because they are mutually beneficial. As we as a community build the functionality in SQLPS we can add it to DSC and vice versa

    I don't have as much DSC knowledge as others but one of the latest projects I have been working on was delivered by 3rd party devs to be delivered by DSC. I tried and tried to get the xSQLPS resource to build an AG and just got weird errors. I ended up squishing my own AG PS scripts into a SetScript resource to get it to work after Ryan told me that was the best way for now. (Also I have Add-SQLogin functions already :-) )

    Ryan Yates Ryan Yates on Desired State Config (DSC)

    If we have the abilities in SQLPS then we can work build it into a decent working version of DSC with hopefully not too much additional work.
    Key is that the community needs a useful single DSC resource. But let's definitely chat in lots more detail at PSConfEU

    Chrissy LeMaire Chrissy LeMaire on Desired State Config (DSC)

    @ryanyates6 If both are desired, DB Engine will have to be upvoted as well since it contains logins. Unsure how this works, I voted for my favorite two or three. What do you think guys think about votes per list? Top two or three then as they get addressed remove and call for votes on a regular basis or something entirely different? Still new to this. /cc @sqlvariant @sqltoolsguy_msft

    Ryan Yates Ryan Yates on Desired State Config (DSC)

    @ctrlb - ideally we will want to have ability to do via both and I think tracking it together works well. But yes we will have opportunity at PSConfEU to discuss in person much further

    Chrissy LeMaire Chrissy LeMaire on Desired State Config (DSC)

    But, but, why do you guys want DSC when we can't even create a login using PowerShell yet? ;) Can someone show me at PSconfEU what I'm missing out on? /cc @ryanyates6 @robsewell1

    Labels

  11. So My suggestion with this card is to get a better steer from the SQL Team on development of the module going forward because as it stands it the SQLServer Module is a Binary Module however if it were to ever be open sourced it would have a bigger and stronger audience to add functionality from those that are more proficient in writing PowerShell as opposed to C#

    Also in my opinion the tooling that can be used to produce more stable releases in a CD manner is getting more stable and known in the PowerShell World (Pester, PSScriptAnalzyer & Appvayor - see the DSC Resources at https://github.com/powershell/ for examples)

    Perhaps moving to a combined Binary & Script module would be beneficial for the community to have quicker release cadence in future?

DB Engine Overall Priorities

  1. In July release of SSMS.

    Comments

    Rob Sewell Rob Sewell on Always Encrypted

    @georgecarlisle1 Could you expand on that please

    George Carlisle on Always Encrypted

    Key Vault PS integration

    Labels

  2. Comments

    abhisabh on Configuration (sp_configure)

    Thanks @dbaduck
    I've posted a Set-SqlInstance card on design discussion in the Info section. If you could take a look, any comments will be much appreciated :smile:

    Ben Miller Ben Miller on Configuration (sp_configure)

    @abhisabh - I will be posting my cmdlet code for the Set-SqlConfigurationItem and the Get-SqlConfigurationItem tonight to GitHub. (names I think are right)

    I don't mind if you leverage them. They work fine, but I know that your framework is much more involved than mine.

    abhisabh on Configuration (sp_configure)

    Thanks @sqlvariant for poking me about this. I will get the conversation started.

    Labels

  4. This topic is being discussed across multiple cards:
    https://trello.com/c/O2DZgaw6
    https://trello.com/c/IN8HGqx4
    https://trello.com/c/h6rlRnA9
    https://trello.com/c/QnHqfeWD
    https://trello.com/c/DWVGol0U

    If one of those doesn't cover your need, please create a new card.

    Comments

    Amanda Debler Amanda Debler on Logins

    This would be WONDERFUL for Skype for Business installs.

    Chrissy LeMaire Chrissy LeMaire on Logins

    Hey @maximotrinidad, this card is for the overall priorities, then the Lists have cmdlets within them. I moved it back and updated the list name so that its intention is clearer (I hope!)

    alzdba alzdba on Logins

    I would like an New-SQLLogin with as -Script option and/or an -Add2AG switch so the login gets replicated to all available Always-On AG if its default database is member of an Always-On AG

  6. You can Up-Vote this item here: http://bit.ly/GetSQLAgent

    Comments

    Ben Miller Ben Miller on SQL Server Agent

    @matteo437 I like that idea. Common typing avoidance is a great strategy.

    Chrissy LeMaire Chrissy LeMaire on SQL Server Agent

    @matteo437 Ain't gonna lie, I love that. /cc @sqlvariant

    Matteo Matteo on SQL Server Agent

    @ctrlb Agreed. However, I'm open to bake-in "typical usage patterns" into the cmdlet(s), if that saves some "really common typing". Bottom line: we can consider @sqlvariant's suggestion...

    Matteo Matteo on SQL Server Agent

    @sqlvariant Nobody did. I guess we'll see some "usability improvements/requests" once the first version of the cmdlets becomes available.

    Chrissy LeMaire Chrissy LeMaire on SQL Server Agent

    @sqlvariant Should be easy as is Get-SqlAgentJob | Where-Object { $_.CurrentRunStatus -ne 'Idle' } /cc @matteo437

    Aaron Nelson Aaron Nelson on SQL Server Agent

    @matteo437 I totally forgot to ask for something that would allow us to easily return only SQL Agent Jobs that were currently running. I'm hoping someone else did?

    Chrissy LeMaire Chrissy LeMaire on SQL Server Agent

    @matteo437 Ahhh I missed your comment on May 31. That looks frikken awesome!

    Matteo Matteo on SQL Server Agent

    This is coming along pretty nice, I think... :)

    Matteo Matteo on SQL Server Agent

    I thought I'd share some preliminary stuff I have on my machine... I kind of like it (but maybe I'm biased... :)

    PS C:\Users\matteot> Get-SqlAgent -ServerInstance VLM00219948 | Get-SqlAgentJob | Select Name,DateCreated,LastRunDate

    Name DateCreated LastRunDate

    AAA 4/21/2016 12:49:44 AM 4/21/2016 1:16:50 AM
    J1 5/23/2016 7:41:26 PM 1/1/0001 12:00:00 AM
    K32 4/14/2016 9:44:54 PM 5/31/2016 2:00:00 AM

    Matteo Matteo on SQL Server Agent

    @dbaduck Hey Ben, I just replied to the http://bit.ly/GetSQLAgent. Please, feel free to add specific cmdlets you'd love to have.

    Ben Miller Ben Miller on SQL Server Agent

    I would vote 1000 times if I could on this one. The love has to be shown for this one as it is not so intuitive to manage Agent through sp_ in msdb and SMO is great, but to have a better way through SQLPS, would be fantastic.

    Labels

  7. Should be able to search for certain words in the log file and return all occurrences.

    DEAR COMMUNITY,
    Please comment or tweet with additional needs for this. I assume the output should be a DataTable?

    Add your comments and Ideas to Nic's card: https://trello.com/c/reJfNNcu

    Labels

  14. Comments

    ArieH on Replication

    Would be nice to get a full replication script to transfer between sql servers

  15. Comments

    Chrisavwood on AlwaysOn Availability Groups

    We are using these more and more with the potential for having multiple AGs here.

  17. Comments

    Chrisavwood on Extended Events

    Extended events are being developed more and more.

  18. This cmdlet is now fixed. Please continue to use this card to request enhancements and report issues with the Get-SqlInstance cmdlet.

    When you run Get-SqlInstance and point it to a local instance of SQL Server, instead of getting the instance you get an error message.

    Get-SqlInstance should be similar to Get-Item SQLSERVER:\SQL\localhost\sql2016.

    Connect: https://connect.microsoft.com/SQLServer/feedback/details/3005019

    Comments

    Ben Miller Ben Miller on Get-SqlInstance doesn't work.

    @tobiassvenblad you are close to the end. To see all the instances on a machine you need to use a different technique. Get-SqlInstance will give you 1 instance. If you do this you will see the instances.

    Dir SQLSERVER:\SQL\machinename

    This will return the instance names and you will need to use them to get the instance with MACHINE\INSTANCE with Get-SqlInstance or you can use

    Get-Item SQLSERVER:\SQL\machinename\instancename

    That will get a specific instance as well.

    Tobias Svenblad on Get-SqlInstance doesn't work.

    @dbaduck Thank you! Now it works with Windows Authentication. However, I cannot seem to list all the instance names from a server with -ServerInstance, only the instance name I provide (e.g. -ServerInstance "SQL1" will only provide the default instance name, despite having five other instances installed on it). What should I use to list all instances on a machine?

    Ben Miller Ben Miller on Get-SqlInstance doesn't work.

    @tobiassvenblad That explains it. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login. -Credential is ONLY used for SQL Logins. Try it without using Credential.

    Tobias Svenblad on Get-SqlInstance doesn't work.

    @dbaduck This is the error I receive when I try to connect to an instance:

    PS C:\WINDOWS\system32> Get-SqlInstance -Credential $Credential -ServerInstance CBSWSQLMGT01 Get-SqlInstance : Failed to connect to server CBSWSQLMGT01. At line:1 char:1 + Get-SqlInstance -Credential $Credential -ServerInstance CBSWSQLMGT01 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (CBSWSQLMGT01:String) [Get-SqlInstance], ConnectionFailureException + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.GetSqlInstanceCommand

    The credential $Credential is my Active Directory domain username. If I check the SQL Server Error log of CBSWSQLMGT01, I can clearly see that the login has failed (even though I am a sysadmin on that SQL Server Instance), with only having Windows Authentication mode enabled on the Instance.

    If I instead enable SQL Server and Windows Authentication mode, and create a user with some other login credential, I can successfully connect.

    If I run a -Debug operation on the query, I see this:

    PS C:\WINDOWS\system32> Get-SqlInstance -Credential $Credential -ServerInstance "CBSWSQLMGT01" -Debug DEBUG: (Get-SqlInstance) In method ProcessRecord. Resolving targets. DEBUG: (Get-SqlInstance) Resolving targets: Parameter Set = 'ByName' DEBUG: (Get-SqlInstance) Connecting to server CBSWSQLMGT01 with SQL Authentication. Login = DOMAIN\myusername DEBUG: (Get-SqlInstance) Attempting to connect to server... Get-SqlInstance : Failed to connect to server CBSWSQLMGT01. At line:1 char:1 + Get-SqlInstance -Credential $Credential -ServerInstance "CBSWSQLMGT01 ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (CBSWSQLMGT01:String) [Get-SqlInstance], ConnectionFailureException + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.GetSqlInstanceCommand

    So there's no question about it that the module is using SQL Authentication by default, and I can see no way to change this to use Windows Authentication mode?

    Ben Miller Ben Miller on Get-SqlInstance doesn't work.

    @tobiassvenblad what do you mean? I use it to connect to SQL instances using Windows Auth mostly. Against Linux it has to be SQL Auth but I am not seeing problems. Happy to help if I can. ben@benmiller.net.

    Tobias Svenblad on Get-SqlInstance doesn't work.

    Great module! However, it would be nice to see that Windows/AD Integrated Authentication worked as well. Currently, only SQL Authentication is working when connecting to a SQL Server instance, which is a bummer for those who have it disabled due to security concerns.

    Labels

  19. Can we get these fixed now that Get-SqlInstance is fixed? The Stop-SqlInstance and Start-SqlInstance should be pretty simple to do, but with them fixed, we can keep people from expecting them to start and stop the instance retrieved by Get-SqlInstance and be presented with the error that indicates that they need the Cloud Adapter in place.

Databases

  1. Or Add-SqlLogin?

    Comments

    abhisabh on Add-SqlDatabaseUser

    Haven't had a chance to look at this yet.
    Currently focusing on more engineering systems work for PS. But will probably triage this for next month maybe.

    Ben Miller Ben Miller on Add-SqlDatabaseUser

    Any traction here? This is a very good one for augmenting the Add-SqlLogin

    K. Brian Kelley K. Brian Kelley on Add-SqlDatabaseUser

    I'd like to see this consistent between Add/New.

    Braun Reyes Braun Reyes on Add-SqlDatabaseUser

    I like it...I am trying to automate as much as possible with SQL Server using powershell, this abstraction would be useful.

    Labels

  3. Also support adding to specific filegroup

    Comments

    Ryan Bartram Ryan Bartram on Add-SqlDatabaseFile

    hmm....well you learn something new everyday. I will have to build logic in to skip if someone trys to request additional files for the model db.

    Ben Miller Ben Miller on Add-SqlDatabaseFile

    You actually cannot add a filegroup to model, it is a restriction.

    Ryan Bartram Ryan Bartram on Add-SqlDatabaseFile

    This would be really useful to have as well as the filegroup and logfile commands because I am experiencing an issue where by on server 2016 with sql 2016 I am unable to create database files for example the model db. I have been using SMO up until now but somewhere it isn't working in the configuration. Using get-sqldatabase and editing existing files works a charm though.

  6. ALTER TABLE equivalent. More info coming soon.

    This cmdlet will need to be named Set-SqlTable but should have an alias named Alter-SqlTable for discoverability. https://twitter.com/jsnover/status/725310729516011520

    You should be able to change the File Group of the Table, change the name, add columns, [Rebuild WITH] DATA_COMPRESSION setting (PAGE or ROW), what else?

    The SQL team probably should prioritize accuracy, speed, & feature coverage, in that order.

    Looks like the SQL team will be working on this soon! https://twitter.com/sqldbawithbeard/status/728954817327071234

    Labels

  7. Two small ideas for Backup-SqlDatabase
    • Backup-SqlDatabase – We talked before about having it completely skip any attempt to backup TempDB.
    • Backup-SqlDatabase – When doing a Log Backup of databases that are in Simple mode, can we just write a warning that the ‘Log Backup is being skipped because the database is in Simple recovery’ instead of the current message?
    Backup-SqlDatabase -ServerInstance localhost\SQL2016 -Database AirStats -BackupAction Log -BackupContainer /

    My use case for this is when someone knows/thinks their server is about to go down, doesn’t have the time to do a Full Backup of every database, but wants to at least do a Log Backup of every database they can. Similar to this:
    PS SQLSERVER:\SQL\localhost\SQL2016\databases> dir | Backup-SqlDatabase -BackupAction Log -BackupContainer /

    While I wouldn’t consider either of these two backup tweaks ‘urgent’ at all, they would help the introduction experience for new users be a little smoother. Especially since demoing Backup-SqlDatabase seems to be rather common in introductory sessions.

    Comments

    abhisabh on Backup-SqlDatabase small tweaks

    Hi Ben, so the default set in SMO ServerConnection object for statement timeout is 600 seconds. That is why if nothing is specified the operation timesout after 10mins.
    I have added a -StatementTimeout parameter as part of this cmdlet and also set the default to 0 when the above parameter is not specified.
    You should see this change in the next release.
    Thanks

    abhisabh on Backup-SqlDatabase small tweaks

    Thanks Ben, I will take a look and hopefully address the issue in the next release!

    Ben Miller Ben Miller on Backup-SqlDatabase small tweaks

    And now I just tested the use of the Provider for the Server Connection. In the Provider the StatementTimeout = 0.

    So the only scenario that needs to be fixed is when I use the server object from a New-Object -TypeName Microsoft.SqlServer.Management.smo.Server

    It works in the Provider when using it while in the directory of the instance as well as if I just Get-Item from the provider for a Database or Server objects.

    Thanks and if there is anything else that you need, I am happy to test and try.

    Ben Miller Ben Miller on Backup-SqlDatabase small tweaks

    I just tested another scenario. When I use the pipeline with a Database object and I had set the $server objects ConnectionContext.StatementTimeout=0 then the Backup-SqlDatabase honors the StatementTimeout in the server object. I have a couple of other scenarios to try as well.

    Ben Miller Ben Miller on Backup-SqlDatabase small tweaks

    BUG: Just encountered an error in backing up a database that takes longer than 10 minutes. We need a statementTimeout parameter or to have this set to 0 in the cmdlet.

  8. This is part of my earlier Connect submission. Please UpVote there!
    https://connect.microsoft.com/SQLServer/feedback/details/2554415/sqlpackage-exe-needs-to-be-made-into-at-least-3-cmdlets

    The SQL Team is working on this item. Go comment/vote on the Connect Item!

    Labels

  9. Invoke-SQLCMD should accept PSCredential objects instead of asking for username & password

    Comments

    Aaron Nelson Aaron Nelson on Invoke-SqlCmd -PSCredential

    @abhisabh Done!

    Any chance you might be able to make [Get|Set]-SqlConfiguration become a reality? :smile:

    abhisabh on Invoke-SqlCmd -PSCredential

    update: it should be available in the 'next' release.
    @sqlvariant : I'll be working on Sql Powershell can you add me as a member to this Trello board?

    Aaron Nelson Aaron Nelson on Invoke-SqlCmd -PSCredential

    @abhisabh :thumbsup:

    abhisabh on Invoke-SqlCmd -PSCredential

    The changes for supporting PSCredential in Invoke-Sqlcmd have been checked-in.
    It should be available in the release. Thanks

  10. Enable-QueryStore should be a cmdlet which includes parameters for all of the Configuration Options for Query Store. Of course this will probably lead to needing Get- & Set- cmdlets for Query Store so that you can later modify the options that had been set.

    Enable-QueryStore -OperationMode
    -CleanupPolicy
    -DataFlushIntervalSeconds
    -MaxStorageSizeMb
    -IntervalLengthMinutes
    -SizeBasedCleanupMode
    -QueryCaptureMode
    -MaxPlansPerQuery

    Configuration Options

    From MSDN: https://msdn.microsoft.com/en-us/library/dn817826.aspx#Enabling

    OPERATION_MODE
    Can be READ_WRITE or READ_ONLY.
    CLEANUP_POLICY
    Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store.
    DATA_FLUSH_INTERVAL_SECONDS
    Determines the frequency at which data written to the query store is persisted to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS.
    MAX_STORAGE_SIZE_MB
    Configures the maximum size of the query store. If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data.
    INTERVAL_LENGTH_MINUTES
    Determines the time interval at which runtime execution statistics data is aggregated into the query store. To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. This fixed time window is configured via INTERVAL_LENGTH_MINUTES.
    SIZE_BASED_CLEANUP_MODE
    Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size.
    QUERY_CAPTURE_MODE
    Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries.
    MAX_PLANS_PER_QUERY
    An integer representing the maximum number of plans maintained for each query.

  11. Similar to mysqldump, export schema and data by default to a .sql file.

    This is part of my earlier Connect submission. Please UpVote there!
    https://connect.microsoft.com/SQLServer/feedback/details/2554415/sqlpackage-exe-needs-to-be-made-into-at-least-3-cmdlets

    The SQL Team is working on this item. Go comment/vote on the Connect Item!

    Comments

    Chrissy LeMaire Chrissy LeMaire on Export-SqlDatabase

    User also asked: "can you support custom operations during migration (like data scrubbing in columns)? ie. Flag this data, Delete this column?"

    Labels

  12. Currently you must reside to SMO to get the actual backup file information ( header, file list, ... )
    However this is crucial to assemble the list of backup files to use to perform point-in-time recovery
    I have attached the function I use.
    Keep in mind the first one doesn't yet support direct URL access.
    The most recent one does support URL access

    Comments

    Shawn Melton Shawn Melton on Get-SqlBackupFileInformation

    @alzdba A suggested name change: Get-SqlDatabaseBackup.

    This could then open up what information could be returned. Whether it be on "-File" parameter to pass in a file and it returns the information. If you don't pass in a file parameter, it defaults to just pulling out the backup history for that database. It could have a default view of maybe just date taken, backup type, and phyiscal file name; piping it to "Format-List -Force" causes it to spit out everything about the backup(s).

  13. Comments

    abhisabh on Get-SqlBackupHistory

    This Cmdlet is in the works. Look for it in the next release of Sql Powershell

    Ben Miller Ben Miller on Get-SqlBackupHistory

    This is just like Get-SqlAgentJobHistory in value. To be able to interrogate the backup history from the cmdlet would be great. I have had many scenarios in the last month that show this to be a great value. The trick is which columns are going to be returned. THere are a lot of items that could be retrieved and returned.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlBackupHistory

    @sqlvariant Ahh yes, Get-SqlBackupFileInformation

    Chrissy LeMaire Chrissy LeMaire on Get-SqlBackupHistory

    from twitter "with filters like get-sqlbackuphistory -database [db1,db2,*] -type log -datefrom (get-date).adddays(-2) dateto (get-date) 2/2"

    Aaron Nelson Aaron Nelson on Get-SqlBackupHistory

    I thought we had something similar proposed by Johan. I will look and combine if so.

  14. Get-SqlDatabase doesn't have a -Database parameter which can be confusing when looking for commands which accept a database as an input parameter.
    (Get-Command -Module SqlServer -ParameterName Database)
    My proposal is for a -Database parameter to replace the existing -Name parameter on the Get-SqlDatabase cmdlet, and then an alias for -Name be mapped to the -Database parameter.

  16. This would be for SQL Server Database-Level triggers, not Table-Level triggers.

  17. Would like something similar to what is available in SQLPSX but even better if possible.
    Also, I ( @sqlvariant ) personally am ok with thing becoming available in advance of a corresponding Set-SqlDataFile but I'd like to hear what the community has to say?

    #

    <#
    .SYNOPSIS
    Gets an SMO DataFile object.
    .DESCRIPTION
    The Get-SqlDataFile function gets a collection of SMO DataFile objects for the database.
    .INPUTS
    Microsoft.SqlServer.Management.Smo.Database
    You can pipe SMO database objects to Get-SqlDataFile
    .OUTPUTS
    Microsoft.SqlServer.Management.Smo.DataFile
    Get-SqlDataFile returns a Microsoft.SqlServer.Management.Smo.DataFile object.
    .NOTES
    Additional properties including the database, server and extended properties are included in the output.
    .EXAMPLE
    Get-SqlDatabase "Z002\sql2k8" "pubs" | Get-SqlDataFile
    This command gets a collection of SMO DataFile objects for all data files in the pubs database.
    .LINK
    Get-SqlDataFile

    >

    function Get-SqlDataFile
    {
    param(
    [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Database]$database
    )

    process { foreach ($dataFile in $database.FileGroups | % {$_.Files}) { #Return DataFile Object $dataFile | add-Member -memberType noteProperty -name timestamp -value $(Get-SessionTimeStamp) -passthru | add-Member -memberType noteProperty -name FileGroup -value $dataFile.parent.Name -passthru | add-Member -memberType noteProperty -name FreeSpace -value $($dataFile.Size - $dataFile.UsedSpace) -passthru | add-Member -memberType noteProperty -name Server -value $database.parent.Name -passthru | add-Member -memberType noteProperty -name dbname -value $database.Name -passthru } }

    } #Get-SqlDataFile#######################
    <#
    .SYNOPSIS
    Gets an SMO DataFile object.
    .DESCRIPTION
    The Get-SqlDataFile function gets a collection of SMO DataFile objects for the database.
    .INPUTS
    Microsoft.SqlServer.Management.Smo.Database
    You can pipe SMO database objects to Get-SqlDataFile
    .OUTPUTS
    Microsoft.SqlServer.Management.Smo.DataFile
    Get-SqlDataFile returns a Microsoft.SqlServer.Management.Smo.DataFile object.
    .NOTES
    Additional properties including the database, server and extended properties are included in the output.
    .EXAMPLE
    Get-SqlDatabase "Z002\sql2k8" "pubs" | Get-SqlDataFile
    This command gets a collection of SMO DataFile objects for all data files in the pubs database.
    .LINK
    Get-SqlDataFile

    >

    function Get-SqlDataFile
    {
    param(
    [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Database]$database
    )

    process { foreach ($dataFile in $database.FileGroups | % {$_.Files}) { #Return DataFile Object $dataFile | add-Member -memberType noteProperty -name timestamp -value $(Get-SessionTimeStamp) -passthru | add-Member -memberType noteProperty -name FileGroup -value $dataFile.parent.Name -passthru | add-Member -memberType noteProperty -name FreeSpace -value $($dataFile.Size - $dataFile.UsedSpace) -passthru | add-Member -memberType noteProperty -name Server -value $database.parent.Name -passthru | add-Member -memberType noteProperty -name dbname -value $database.Name -passthru } }

    } #Get-SqlDataFile

    Comments

    Chrissy LeMaire Chrissy LeMaire on Get-SqlDataFile

    Like the underlying SQL Table, this should include LogFiles as well.

    $sql = "SELECT db.name AS dbname, type_desc AS FileType, mf.name, Physical_Name AS filename FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id"

  18. Would like something similar to what is available in SQLPSX but even better if possible.
    Also, I ( @sqlvariant ) personally am ok with thing becoming available in advance of a corresponding Set-SqlDataFile but I'd like to hear what the community has to say?

    #

    <#
    .SYNOPSIS
    Gets an SMO LogFile object.
    .DESCRIPTION
    The Get-SqlLogFile function gets a collection of SMO LogFile objects for the database.
    .INPUTS
    Microsoft.SqlServer.Management.Smo.Database
    You can pipe SMO database objects to Get-SqlLogFile
    .OUTPUTS
    Microsoft.SqlServer.Management.Smo.LogFile
    Get-SqlLogFile returns a Microsoft.SqlServer.Management.Smo.LogFile object.
    .NOTES
    Additional properties including the database, server and extended properties are included in the output.
    .EXAMPLE
    Get-SqlDatabase "Z002\sql2k8" "pubs" | Get-SqlLogFile
    This command gets a collection of SMO LogFile objects for all data files in the pubs database.
    .LINK
    Get-SqlLogFile

    >

    function Get-SqlLogFile
    {
    param(
    [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [Microsoft.SqlServer.Management.Smo.Database]$database
    )

    process { foreach ($logFile in $database.LogFiles) { #Return LogFile Object $logFile | add-Member -memberType noteProperty -name timestamp -value $(Get-SessionTimeStamp) -passthru | add-Member -memberType noteProperty -name FreeSpace -value $($logFile.Size - $logFile.UsedSpace) -passthru | add-Member -memberType noteProperty -name Server -value $database.parent.Name -passthru | add-Member -memberType noteProperty -name dbname -value $database.Name -passthru } }

    } #Get-SqlLogFile

    Comments

    Aaron Nelson Aaron Nelson on Get-SqlLogFile

    Log Files don't have File Groups, I wonder if that's why they're different in SMO?

    Aaron Nelson Aaron Nelson on Get-SqlLogFile

    @ctrlb Maybe different FileTypes should just be a switch parameter? I would think regular SQL Server Data File byt default personally.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogFile

    If we bust this part out, we should also include cmdlets for all the other filetypes, which could grow all the time (like you and I mentioned, @sqlvariant) with Heckaton files, FullText, FileStream, etc.

    This should be part of Get-SqlDataFile as a column of type = log, like underlying SQL tables.

  20. Returns all dependencies of a given database object, basic list of the object name and type maybe. Could include switch for referencing "Objects that depend on <object>" or "Objects on which <object> depends"

    Comments

    Chrissy LeMaire Chrissy LeMaire on Get-SqlObjectDependencies

    YES! Like a dependency walker. I'd love this.

    Lynn Sickler on Get-SqlObjectDependencies

    A Get-Dependencies cmdlet could provide a list of "depends on" and "is depended on" objects to help guide a dba when refactoring databases or even making small changes to an object. This is especially true when there are a large number of dependent sprocs. In my vision this could be much more efficient and useful than the current gui approach.

    Rob Sewell Rob Sewell on Get-SqlObjectDependencies

    @lynnsickler1 thanks for commenting - would you mind expanding on some use cases you would have for this

    Lynn Sickler on Get-SqlObjectDependencies

    This would be very helpful.

  23. I have already submitted this one on Connect to help get the ball rolling but I'm sure that y'all have better ideas to add than just the few that I listed.

    https://connect.microsoft.com/SQLServer/feedback/details/2589763/sqlps-lacks-cmdlets-to-get-common-sql-server-object-like-get-sqltable

    Comments

    Aaron Nelson Aaron Nelson on Get-SqlTable

    Or IndexedView?

    I frequently work with objects below the table level but query the table properties to find which of those objects. I don't like doing this in the provider because it's not as clean and can produce additional WMI errors depending on permissions.

    If your comments were directed simply at the naming of Get-SqlTable, then please completely ignore this comment.

    Shawn Melton Shawn Melton on Get-SqlTable

    I do agree with Sean on this one as what would the purpose be of this cmdlet. Is it just to read out information on an object (e.g. exec sp_help 'tableName')? If so why not just create one cmdlet like Get-DatabaseObject (or Get-SqlObject) with parameter for object in question (e.g. Get-DatabaseObject -Table MyTable or Get-DatabaseObject -proc MyProcedure).

    Sean McCown Sean McCown on Get-SqlTable

    Yeah, I don't see the need for DatabaseTable. What would be the point? Only DBs have tables so of course it would be a DB table. It would be like Get-TableIndex... As opposed to a login index or an Xevent index?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTable

    Kirk's response that makes sense: Table, not DatabaseTable; I break this down like this: is the Table noun unique to databases within the SQL noun set? If so, then Table is enough. If not, then DatabaseTable to uniquely identify the type of table we're talking about.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTable

    This should be Get-SqlDatabaseTable. I think that people will expect Get-SqlTable, though, so this would be one of the exceptions where we request an alias. So Set-Alias -Name Get-SqlTable -Value Get-SqlDatabaseTable.

    Need more input from community.

    Labels

  24. Sean Says: "Or you could say Get-SQLTrigger -type {Database|Server|Table} and then other params to use based off of type."

    Comments

    Sean McCown Sean McCown on Get-SqlTableTrigger

    I don't know. I've never used Trello before.

    Aaron Nelson Aaron Nelson on Get-SqlTableTrigger

    @seanmccown How do I "Like" you comment?

    Sean McCown Sean McCown on Get-SqlTableTrigger

    Or you could say Get-SQLTrigger -type {Database|Server|Table} and then other params to use based off of type.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTableTrigger

    Whoops, Get-SqlTableTrigger, good call.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTrigger

    Get-SqlTrigger suggests Server level trigger. This should be renamed to Get-SqlDatabaseTrigger.

  26. Comments

    Maximo Trinidad Maximo Trinidad on Get-SqlView

    I voted definitely for this one. I get tired to doing a create view or doing a modify view to examine a SQL view.

    A Get-SQLView with an option to create (to modify) the view will be beneficial to the SQL Admin and SQL Developer.

  27. This is part of my earlier Connect submission. Please UpVote there!

    https://connect.microsoft.com/SQLServer/feedback/details/2554415/sqlpackage-exe-needs-to-be-made-into-at-least-3-cmdlets

    The SQL Team is working on this item. Go comment/vote on the Connect Item!

    Comments

    Troy on Import-SqlDatabase

    I favor New-SqlSchema, Get-SqlSchema, and Compare-SqlSchema for the naming convention. SqlSchema or SqlDacPac. Either is valid here.

    Export and Import are data verbs intended to move data in and out of a persistent store. So, Import-SqlDatabase to me would be importing data from a SQL Server Database into PSObjects.

    Also, please, please give me Compare-SqlSchema that can be passed to ConvertTo-Html easily. This is the biggest pain point I'm having with DacPacs and writing my own custom scripts to do.

    Labels

  28. Comments

    Chrissy LeMaire Chrissy LeMaire on New-SqlDatabaseSnapshot

    If user does not specify path, please just use SQL Default Data directory.

  29. Users should be able to Pass in a DataTable object to the New-SqlTable cmdlet and the cmdlet will figure out all of the column names and SQL Server data types based on the definition of the DataTable.

    If a -TableName parameter is not specified, it should inherit the name of the DataTable. I guess this should have traditional PS parameters like -Force an -NoClobber to wipe out or not wipe out an existing table in the database.

    The cmelet should also be able to go ahead and write any rows that are present in the .Net DataTable. Cmdlet should also be able to receive a table for Get-SqlTable and maybe even Exp-SqlDatabase.

    The idea for this is base on Chad Miller's Add-SqlTable https://gallery.technet.microsoft.com/scriptcenter/c193ed1a-9152-4bda-b5c0-acd044e68b2c/view/Reviews

  30. Comments

    Chrissy LeMaire Chrissy LeMaire on New-SqlDatabase

    @sqlvariant Seems like it. I wonder how other modules handle complex stuff. It also very much needs to determine default paths and not burden the end user with figuring that all out. Tho that may already be built in to SMO. Don't recall.

    Aaron Nelson Aaron Nelson on New-SqlDatabase

    This would need to accept splatting for file names, filegroups, etc..
    No?

  32. Comments

    Chrissy LeMaire Chrissy LeMaire on Restore-SqlDatabaseSnapshot

    Autopopulate snapshot names & make user specify database name to confirm? I wish PS could do two dynamic params.

  33. Allow specifying one or more databases, by default all.

  34. Turns a regular old SQL database into a partially contained one.

  36. SQLPS lacks a simple way to write rows from a .Net DataTable into a SQL Server table

    For inserting to a SQL Server table from a .Net DataTable.

    From Chad Miller's function on Script Center:
    https://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

    Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance..

    .EXAMPLE
    $dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select * from authors"
    Write-DataTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -Data $dt
    This example loads a variable dt of type DataTable from query and write the datatable to another database.

    filed: https://connect.microsoft.com/SQLServer/feedback/details/2685363

    Comments

    Adriano Nebbia Adriano Nebbia on Write-SqlTableData

    Dear Ben, thanks for the reply.
    Yes, there is the Svr schema in place. The issue was that I changed the result set, and the variable that points to the result set, somehow had the values related to the old result set. Thus I opened another PS session, executed the PS script and it worked.

    Thanks anyway for your follow up!

    Best regards,
    Adriano Nebbia

    Sent from my iPhone

    Ben Miller Ben Miller on Write-SqlTableData

    Is there a schema called Svr?

    Adriano Nebbia Adriano Nebbia on Write-SqlTableData

    Dear all,

    I am new in this trello community and it's since few months I am praticing a bit with PS combined with t-sql.
    In few words I am trying to gather audit file information from my SQL Server List and this is the PS script I developed for the purpose:

    ---------------------------------------Script begin-----------------------------------------
    $params = @{'server'='XXXXXXX;'Database'='XXX'}
    $ResultSet=Invoke-Sqlcmd @params -Query "SELECT distinct SI.InstanceName, Hostnames, is_current_owner,replace(LEFT(SI.BackupDirectory,2),':','$') as BackupDrive, replace(AI.log_file_path,':','$') as AuditPath FROM Svr.ServerInfo SI join [dbo].[v_DBEnvironments] vdb on SI.InstanceName=vdb.InstanceName join Svr.Audit_Information AI on SI.InstanceName=AI.InstanceName
    where is_current_owner=1 and SI.InstanceName not in (select distinct Instance from InstancesNotReachable)"

    foreach ($object in $ResultSet)
    {
    $folder=$object.AuditPath
    $InstanceName=$object.InstanceName
    $Hostname=$object.Hostnames
    write-output "\$Hostname\$folder"
    $AuditFileList=Get-ChildItem \$Hostname\$folder -Include *.sqlaudit -Recurse
    foreach($AuditFile in $AuditFileList)
    {
    $Obj = New-Object PSObject -Property @{ AuditFileName=$AuditFile.Name;InstanceName=$InstanceName ;Size=$AuditFile.Length;Folder=$folder ; LastTimeModified=$AuditFile.CreationTime; }
    Write-SqlTableData -ServerInstance "XXXXXX" -DatabaseName "XXX" -SchemaName "Svr" -TableName "AuditFiles_List" -InputData $Obj -force
    }
    }
    ---------------------------------------Script End-------------------------------------------

    But Write-SqlTableData gave me the following error message:

    Write-SqlTableData : Failed to connect to server XXXXXXX
    At N:\Scripts\PS\StoreAuditFilesInformationIntoTable.ps1:18 char:9
    + Write-SqlTableData -ServerInstance "XXXXXX" -Databa ...
    + ~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (XXXXXXX:String) [Write-SqlTableData], ConnectionFailureException
    + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

    So from the error to me it looks like powershell is not able to find the object within the DB, but the -force option is enalbed (so PS should anyway create the object if does not exist). Why I am getting then this error? Did I forget something in the code?

    Thanks in advance for your feedback and help!

    Matteo Matteo on Write-SqlTableData

    @michaelmaher11 Yeah - I suspected something like that. I'll look into it as soon as I get a chance. Thanks for doing the investigation!

    Michael Maher Michael Maher on Write-SqlTableData

    @matteo437 Think I know what was happening. I was retrieving the uidnumber from AD for a collection of users. This was being detected as an int. I cast this to a string and it inserts fine now in to a varchar field in my DB.

    I read the help ' the schema of the table is inferred by inspecting the data'. The first few accounts retrieved from AD for me are guest and krbtgt which both have null values in uidNumber. This may have caused a problem when the cmdlet tried to infer the datatype

    Matteo Matteo on Write-SqlTableData

    @michaelmaher11 Yes, the handling of PSObject (which may be wrapper inside other PSObjects or array of PSObject) is a little tricky. So, it is likely that I missed some cases like the one you ran into.

    I haven't had time to investigate yet, but it is definitely on my radar.

    The difference between the behaviors you are observing is expected: the cmdlet tries to optimize the insertion (bulk-insert) if it sees an input that looks like a "collection". In that case, it tries to build a DataTable with the input and send the whole thing to SQL... In the "one-by-one" things are simpler and they work, thought if you have a HUGE # of records you'll soon regret doing the "one-by-one" :-)

    I don't think we need a connect item yet...

    Michael Maher Michael Maher on Write-SqlTableData

    @matteo437 I know this is on your radar already but just to give you some extra info.

    I can retrieve a recordset from LDS and send to SQL with this cmdlet but having problems with same records from AD.

    $foundUsers = Get-ADUser -Filter 'objectclass -eq "user"' -searchbase 'DC=us,DC=company,DC=local' –properties SamAccountName,uid,DisplayName,loginShell,Name,uidNumber,unixHomeDirectory, mail |
    Select -Property SamAccountName,@{L='uid'; E={$_.uid[0]}},DisplayName,loginShell,Name,uidNumber,unixHomeDirectory, mail

    Fails

    Write-SqlTableData -DatabaseName $kDBName -SchemaName $kSchema -TableName $kdb -InputData $foundUsers -ServerInstance $kDBServer

    <# Error message: Write-SqlTableData : The given value of type PSObject from the data source cannot be converted to type nvarchar of the specified target column.
    At line:1 char:5

    >

    Bizarrely no errors when I step through each record individually, all insert

    foreach ($u in $foundUsers){
    Write-Output $u.samaccountname
    Write-SqlTableData -DatabaseName $kDBName -SchemaName $kSchema -TableName $kdb -InputData $u -ServerInstance $kDBServe
    }

    If this should be filed in the SQLServer Connect page instead let me know.

    Matteo Matteo on Write-SqlTableData

    @ctrlb Can we move this to the "Wins!!!" column? I understand that it may not be perfect, but we can iterate over it later (e.g. incorporate @codykonior's suggestions below).

    Matteo Matteo on Write-SqlTableData

    @codykonior Good suggestions/tips. This cmdlet was a v1 thing, so I was curious to get this kind of feedback.

    I wonder if I can improve on #1 and #2. I'll look into that...

    As for #3, do you feel like there should be a "-ConvertToString" parameter (or something like that) in case the the cmdlet does not know what to do with the type?

    Could you elaborate on "writing data vs insert"?

    Cody Konior Cody Konior on Write-SqlTableData

    After using this, it's okay for writing stuff out, if that's all you need. Some tips for other people:

    • If you have an identity column which isn't part of the data you're writing, put the column at the end of the table definition (otherwise the routine will barf).
    • If you have temporal tables it works fine if the valid from/to dates are hidden and at the beginning of the table definition.
    • It doesn't support simple conversion of many common .NET data types to string types. For example if you had a [Version] field that can be converted to ToString() it will simply fail the write rather than do the conversion - you need to do it yourself before passing it in. That's just how it is and it's perfectly understandable.

    For me, the gap is that I'm not just writing out data, I often need to upsert. And because Invoke-SqlCmd variable substitution isn't parameterised and safe I still have to use all of my own read/check/upsert/write routines.

    Also my comparable function to this writes out multiple data tables in a data set plus creates all the defined foreign keys, and the string conversions. So for me I can't switch over, but, this is fine for simple writes.

    Troy on Write-SqlTableData

    @matteo437, I might be a bit late in adding my feedback but here are my thoughts.

    I think Write, Read, or Out may not be the best verb choice based on PS's guidance on verbs at https://msdn.microsoft.com/en-us/library/ms714428(v=vs.85).aspx.

    • Out-DataTable
      Out "dends data out of the environment". This cmdlet is meant to convert a PSObject to a DataTable not output or send data somewhere else. Changing to ConvertTo-DataTable better describes the intent here. Also, a ConvertFrom-DataTable is needed to convert back to a PSObject. I believe these are .NET conversions and not SQL Server specific so it should be pulled into a separate module really with sqlps adding a nugget dependency to it.

    • Write-DataTable and Read-DataTable.
      Read and Write are communication verbs. The data verbs make more sense for SQL Server. Import and Export take data in and out of a persistent store using PSObjects and SQL Server is a persistent store. Plus, Import and Export are common terms in dba world anyway.

      Write-DataTable becomes Export-SqlTable and Read-DataTable is Import-SqlTable. In this case, re-use the ConvertFrom/ConvertTo-DataTable functions to natively convert so the scripter can just work with PSObjects and not worry about conversions.

    Aaron Nelson Aaron Nelson on Write-SqlTableData

    @matteo437 I like needing to use -Force to be able to create the table. Sounds like a great idea to me :thumbsup: :smile:

    Matteo Matteo on Write-SqlTableData

    @sqlvariant Yes, we can eventually have an Add-SqlTable (probably much richer, with a lot more control on what you can do) - but this seemed to be a convenient thing to have. And you'd get a quick and dirty schema for free... which you can massage manually.

    Well, isn't the parameter you suggested what I called "-Force" (basically, if you don't have it the cmdlet is NOT going to create missing objects). Do you need more than that?

    Is the "oh wow" more along the "oh, I like it" or "oh, cr*p, what is Matteo thinking"? :-)

    Aaron Nelson Aaron Nelson on Write-SqlTableData

    @matteo437 Oh wow, I would have expected to need to use something like Add-SqlTable and then send the same DataTable object to Write-DataTable.

    My gut feel is that you should have to specify (throw a parameter) if you want to add a table, that way you don't end up with extra tables because you had a typo.

    It would be pretty awesome to have it all in one command.

    We would still want a separate Add-SqlTable so that we can modify the table before we write rows to it (partition it, etc...).

    Matteo Matteo on Write-SqlTableData

    Implementing this cmdlet seemed a lot simpler than I thought. Well, maybe because I thought that the Write-DataTable example was a little too crude: for example, I did not like the fact that the target db had to exist already and have the right schema (it did not feel very flexible and dynamic to me)

    So, the current version I'm playing with allows me to:
    1) create the database, the schema and the table (if they are missing and I pass the -Force parameter)
    2) it has a simple logic to infer the table schema (in the sense of column types) from the input data.

    So, without much effort, I can get my SQL Error logs imported into a table that did not exist in a database that did not exist (the "dbo" schema existed, so that was not created):

    PS C:\> $logs = Get-sqlerrorlog -ServerInstance VLM00220019 -Since Yesterday
    PS C:\> Write-SqlTableData -TableName "MyLogs[Today]" -SchemaName dbo -DatabaseName Logs -ServerInstance VLM00220462\CSAS -Force -InputData $logs

    Thoughts?

    Aaron Nelson Aaron Nelson on Write-SqlTableData

    @matteo437 :thumbsup:

    Laerte Laerte on Write-SqlTableData

    @matteo437 Awesome !!!!

    Chrissy LeMaire Chrissy LeMaire on Write-SqlTableData

    @matteo437 yes! Awesome. Perfect.

    Matteo Matteo on Write-SqlTableData

    @laerte5 Yes, perf is a legit concern.

    I was thinking of making the cmdlet "generic" enough. So, if the input is a "collection of rows", the insert is going to be sequential; however if the input is a datatable (or maybe a dataset with 1 table in it) [in the sense of .Net types], the cmdlet would switch to bulk-insert.

    Matteo Matteo on Write-SqlTableData

    @sqlvariant Yup - accepting input types other than a table seemed like a nice thing to do (either in v1, or just to give us the option to extend the cmdlet in v2)

    Laerte Laerte on Write-SqlTableData

    I was reading some comments and I saw some examples as $something | out-whatevername | write-whatevername .. I guess , by the example , probably the write-xxxx will accept the data by pipeline input. The most useful feature in the original write-datatable is the bulk insert, so accepting pipeline input :
    If the The bulk insert will be performed in the process block, will miss the bulk insert feature, processing and inserting line by line.
    It oculd be added in the end block (hum.....)

    In both cases not sure the implications in performance..so I guess that if this cmdlet will have data as pipeline input,some extra help would be added : hey if you use pipeline input, you will miss the bulk insert feature

    Aaron Nelson Aaron Nelson on Write-SqlTableData

    @matteo437 The request for Write-DataTable was just for a simple way to insert data from a .Net DataTable into SQL Server. If you expand the scope to include other forms of input (Arrays, CSVs, etc...) then a name like Write-SqlTableData would make sense. Not sure what you coded it to do, but that's my feedback.

    Matteo Matteo on Write-SqlTableData

    @ctrlb @psam Oh, I get it. You just want an alias that does not have the "Sql" part in it... I'm not super-convinced myself too. But if that is what folks want, I can add it.

    Speaking of Out-DataTable, these two may look weird:
    $mycollection | Out-DataTable | Write-SqlTableData ...
    $mycollection | Out-DataTable | Write-TableData ...
    (the inversion of Table and Data... is a little odd, at first sight)

    I thought about "TableData" vs "DataTable" for a bit... and to me "TableData" makes more sense in a context of a database read/write operation: I am reading/writing "data" from/into a "table" - and my input may not necessarily be a DataTable.

    I do see arguments in favor of "DataTable" (it's the .Net type System.Data.DataTable), so in Out-DataTable is 100% legit.

    What do people think about it? Are we all in agreement that the SQL Cmdlets should be
    - Write-SqlTableData
    - Read-SqlTableData

    Renaming before we ship is easy... so I can do it at any time, but once the cmdlets are in the wild, there is no turning back :)

    Chrissy LeMaire Chrissy LeMaire on Write-SqlTableData

    @matteo437 Hey Matteo, I totally agree with the naming convention! This is a one off alias request (that I'm not even convinced of myself ;)) to also make the cmdlet a counterpart to this UserVoice request for a "global" Out-DataTable to be built into PowerShell.

    Peter Samuelsson on Write-SqlTableData

    The command is Write-SqlDataTable then in the module you can make an alias to the Write-SqlDataTable that is named Write-DataTable.

    Example the command Get-Childitem has the aliases gci, ls and dir

    Matteo Matteo on Write-DataTable

    @ctrlb I kind of like following the <verb>-Sql<rest_of_the_noun> format - it looks like most of the SQLPS cmdlets do that. So, this cmdlet should be Write-SqlTableData instead of "Write-DataTable", I think.

    I'm not sure I follow the comment on the alias. Elaborate a little, please...

    Chrissy LeMaire Chrissy LeMaire on Write-DataTable

    @matteo437 Indeed, just did! We're hoping the PowerShell team creates Out-DataTable natively. Can we can get an alias perhaps, to Write-DataTable? /cc @sqlvariant

    Matteo Matteo on Write-DataTable

    @ctrlb Based on the conversation here, can we rename this cmdlet to be "Write-SqlTableData"?

    Matteo Matteo on Write-DataTable

    Quick update here... I finally started looking into this item and its companion, Read-SqlTableData.

    I started putting together all the info I got from all the sources (Trello, private e/mails, twitter, etc...) to get myself a reasonable "plan of action" and I'm not missing too many details along the way.

    After that it's going to be the fun part: implementation!

    Matteo Matteo on Write-DataTable

    On my list...

    Chrissy LeMaire Chrissy LeMaire on Write-DataTable

    @sqlvariant That may be scope creep. Writing is different from Out, IMO.

    Aaron Nelson Aaron Nelson on Write-DataTable

    @ctrlb @ryanyates6 Is that something that Out-DataTable should be able to do as well? https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/13735221-out-datatable

    Chrissy LeMaire Chrissy LeMaire on Write-DataTable

    @ryanyates6 Love the idea! SQL Server requires DataTables pretty much (I'm assuming they'll be using SqlBulkCopy), so it's likely it'd have to be transformed.

    Aaron Nelson Aaron Nelson on Write-DataTable

    This card has been filed as a Connect item, please go Up-Vote it there: https://connect.microsoft.com/SQLServer/feedback/details/2685363

    Ryan Yates Ryan Yates on Write-DataTable

    Ideally this should support PSCustomObjects/HashTables as inputs for the Data parameter

    Also If you have already ran Get-SQLDatabase / Get-SQLTable then this should be able to reuse the credentials passed along with Database & table objects

    Hopefully that makes sense

    Labels

  39. Comments

    Matteo Matteo on Clear-DatabaseTable

    @maximotrinidad Interesting suggestion... :)

    Maximo Trinidad Maximo Trinidad on Clear-DatabaseTable

    I have a PowerShell script that use SMO to truncate either individual tables or all tables in the database. Today, I came up with an error message that SMO .truncatedata() is not available in SQL Server 2014. For mi surprise, it's available in SQL Server 2016. A cmdlet for this purpose will be beneficial.

  40. Comments

    Maximo Trinidad Maximo Trinidad on Get-SqlProcedure

    Following same comment I did for Aaron on Get-SqlView, A Get-SqlProcedure will be also beneficial to the SQL Admin and SQL Developer.

    Also, I get tired to doing a create view or doing a modify a stored-procedure to examine it.

  41. Comments

    Maximo Trinidad Maximo Trinidad on Remove-SqlTable

    After the dilema I went through doing SMO script to use the .truncatedata() method in SQL Server 2014 (and didn't). I ended creating the SMO .drop() to ge rid a selected list of tables.

    It would be useful to provide a Remove-SqlTable cmdlet with the ability to provide a list of object to remove or in database.

    Sometime third parties application need help cleaning up some tables before installation.

  42. I would like to suggest that functionality be added to SMO to allow the command Restore Database name With Recovery. In migrations or log shipping it is a common thing to do. But the restore object requires devices to restore. If you could leave off devices and the sqlrestore object would just do the restore with recovery that would be awesome.

    I have to write my own script to do it now but I have always wished that it would be a function of SMO.

    Comments

    Daniel Steffen Daniel Steffen on Restore database with recovery

    I recently had to deal with that problem in my scripts too... Ended up using invoke-sqlcmd just to get the database online.

    How about a cmdlet to verify the backup (restore verifyonly from disk)?

Security

  1. This would have 5 parameters, blah blah blah see SQLPSX for more info.

    Edit, so as it turns out, this precise item has already been filed on Connect: https://connect.microsoft.com/SQLServer/feedback/details/2588952/

    Comments

    Matteo Matteo on New-SqlLogin

    Added green label. We are looking into this...

    Aaron Nelson Aaron Nelson on New-SqlLogin

    @alzdba & @wshawnmelton Johan & Shawn, can you comment on this Connect item? https://connect.microsoft.com/SQLServer/feedback/details/2588952/

    If that doesn't cover what you need, would starting another Card make sense?

    alzdba alzdba on New-SqlLogin

    @wshawnmelton If a native cmdlet that can be piped to will do that functionality, the -add2ag may be skipped. The need is fulfilled in that case.

    Shawn Melton Shawn Melton on New-SqlLogin

    @alzdba I would like to see the "-Script" parameter in any cmdlet that is scriptable in SMO.

    I would disagree with the "Add2AG", with the other cmdlets being suggested you would use the Export-SqlLogin and Import-SqlLogin to perform what you are referring to, or similar "Get-Login | New-Login".

    alzdba alzdba on New-SqlLogin

    I would like an New-SQLLogin with as -Script option and/or an -Add2AG switch so the login gets replicated to all available Always-On AG members

    Labels

  2. The current SMO export is not useful at all. It disables the login, changes the password, and it doesn't copy any properties. This cmdlet has to be useful or people will use dbatools, which even though I'm the author, I do not want.

    dbatools' Copy-SqlLogin copies the following: SIDs, passwords, server/database roles, server/database permissionsets & securables, default database and login attributes. Please make these properties (and more) exportable/importable.

    Comments

    Chrissy LeMaire Chrissy LeMaire on Export-SqlLogin*

    Btw everyone, I added Export-SqlLogin to dbatools in the interim.

    Chrissy LeMaire Chrissy LeMaire on Export-SqlLogin*

    @michaelwells11 sp_help_revlogin has a lot missing, though it did do that part well. Should have been baked into SMO from the beginning.

    Michael Wells Michael Wells on Export-SqlLogin*

    I would love it if this was functionally equivalent to sp_help_revlogin - exporting SIDs and hashed passwords for SQL Authentication accounts

  3. Make it similar to Get-SqlDatabase

    Sample usage of what I'd do with it.
    Get-SqlLogin -ServerInstance sql2012-scale | Out-GridView -PassThru | Copy-SqlLogin -Destination sqlserver

    Comments

    Aaron Nelson Aaron Nelson on Get-SqlLogin

    @matteo437 I think users would want just a message on the screen. I think we should ask around see what others think.

    I also worry about the error action / next step. Example:
    A SQL-person would naturally apply this type of logic, I run Get-SqlLogin to see if the Login already exists, the next step is to Add-SqlLogin if it doesn't exist. As a SQL-person I would expect something like a Null or a 0 back from Get-SqlLogin to let me know that I need to progress to Add-SqlLogin. I'm not expecting that an error from Get-SqlLogin would be the indicator to progress to Add-SqlLogin. After all, maybe it erred about something else.

    Matteo Matteo on Get-SqlLogin

    @sqlvariant I see. That behavior was kind of intentional (similar to when you do "get-item I_DO_NOT_EXIST.txt")

    Are you saying users would expect just a message on the screen and no errors?

    Aaron Nelson Aaron Nelson on Get-SqlLogin

    @matteo437 Matteo, I filed a bug against this cmdlet. I like the cmdlet and think lots of people are going to love it. However, it yells at you. By default I don't think PowerShell is supposed to yell at you.

    The Connect items is: https://connect.microsoft.com/SQLServer/feedback/details/3054308/

    "The new Get-SqlLogin cmdlet comes with great parameters -Locked & -Disabled for returning only the logins which are Locked & Disabled, respectively. Unfortunately, when the cmdlet does not find any Locked or Disabled logins, it throws an error instead of simply returning a message that it didn't find any logins of that particular type/status.

    Furthermore, it appears that an error message is also returned if you use the -LoginName parameter and it doesn't find that particular login."

    Matteo Matteo on Get-SqlLogin

    Added green label. We are looking into this one...

    Klaas Vandenberghe Klaas Vandenberghe on Get-SqlLogin

    @matteo437 I understand, but I think there's only profit in that if we know the strengths and weaknesses of those different 'layers'. Very often speed will be the decisive factor. That's why I would love to know which is faster and why, since there will always be tasks I'll have to handle using the basic building blocks. That's why I'm interested in the comparison between those methods.
    @ctrlb I agree, and I wonder what this new cmdlet Get-SqlLogin adds to that. The same goes for other proposed cmdlets.
    $server = New-Object ...
    $server.logins
    We can do almost anything in two lines.
    Is there no greater gain in improving Invoke-SqlCmd and, if possible, in making 'server side filtering' in SMO as @laerte5 explained?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogin

    @klaasv agreed with Matteo. What I really like about SMO is that it takes care of all the syntax and table names for the SQL Server. Since table names differ from version (think 2000 - 2016), you dont have to know them with SMO. It's jut $server.logins and it figures it out itself.

    Ben Miller Ben Miller on Get-SqlLogin

    I think that putting the -Database on the backlog is probably the best for now. I am not sure that it would be used heavily, but would certainly have edge cases. But I really LIKE the idea of a consistency with Get-SqlLogin DBA for a wildcard search. When using Get-Service mssql it is very nice to get those services that begin with that. So consistency would be my vote for sure.

    Matteo Matteo on Get-SqlLogin

    @klaasv You are not wrong in saying that, at the end of the day, deep down in the stack you "gotta talk T-SQL" :-).

    But that is true for all software: there are advantage in using abstractions over "lower layers" of the stack, like reusing code, not having to worry about the little details, avoid to reinvent the wheel every time (and getting it square instead of round), relying on libraries and well-estabilished patterns, etc...

    Aaron Nelson Aaron Nelson on Get-SqlLogin

    @matteo437 & @ctrlb I'm fine with putting the -Database parameter on the backlog for now. Let the Get-SqlLogin cmdlet get out the door and then we'll see how many people ask for it. We can come back to it later if there's enough demand. Maybe we'll find out there's high demand for a filter we didn't think of. :thumbsup:

    For the record, I wasn't thinking of people trying to retrieve 1,000 users, I was thinking of people who have 100+ instances spread across multiple data centers and needing to check some, but not all, of the users across all of those instances. Hope that helps paint the picture a little clearer. :smile:

    Klaas Vandenberghe Klaas Vandenberghe on Get-SqlLogin

    @ctrlb something like that. Am I wrong in presuming that all methods (SMO, SQL provider, .NET,...) are actually T-SQLwrappers? If so it would be nice if the 'fastest way' could be used everywhere possible. And since Invoke-SqlCmd is the one cmdlet that could execute every thinkable task I would think re-engineering this one should have high priority.
    Or is that nonsense?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogin

    @klaasv Sorry, not sure what you mean. You want me to execute a select * from logins where whatever using Invoke-sqlcmd to see if it beats 24 ms?

    Klaas Vandenberghe Klaas Vandenberghe on Get-SqlLogin

    @ctrlb can you compare that to the invoke-sqlcmd method?

    Laerte Laerte on Get-SqlLogin

    @matteo437 Just checked de sqlserver provider and it not supports filter.. so as I said, not sure how it works internally and if it can be implemented to be faster than pipe to where-object

    Laerte Laerte on Get-SqlLogin

    @matteo437 Hey Matteo, Actually I will need to filter by myself in both cases, but what I ment to say was filtering in the cmdlet itself, avoiding use pipeline (not the server-side concept in SQL Server). I dont know how it works and if it can be implemented in C# inside the cmdlet, but my idea was the same as "Get-WmiObject -Class win32_service -Filter "state='running'"
    is much better than "Get-WmiObject -Class win32_service | Where-Object State -eq 'running'"

    Matteo Matteo on Get-SqlLogin

    @ctrlb Thanks for checking. I'll pass the info to @eduardovacaguerra (he'll be relieved not having to mess with T-SQL... yet :P)

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogin

    @matteo437 I just went tested 1000 logins with no issues (24 milliseconds to search by name) -- fast as hell. "just filter the data before you hand it over to me" is perfect.

    Matteo Matteo on Get-SqlLogin

    @laerte5 Re: filtering "server side". Do people really envision having so many logins that a "server side" filtering would be beneficial (or noticeable)?

    I'm asking because SMO does not seem to expose any "server side" filtering on Logins, so a true server-side filtering would force us to go straight to T-SQL (a little extra complexity).

    But, if by "server-side" you just mean "just filter the data before you hand it of to me, so I don't have to filter it myself", that's a simpler story...

    Cláudio Silva Cláudio Silva on Get-SqlLogin

    Definitely +1 for @robsewell1 wildcard suggestion

    +1 for @michaelwells11 not mixing Logins and Users concepts.

    @sqlvariant seems usefull. But at same time if we had a "Get-SqlUser -Login something " this can also go through all databases and return the users using that login. Thoughts?

    Matteo Matteo on Get-SqlLogin

    @michaelwells11 Totally agree on not mixing "Logins" (a server concept) and "Users" (a database concept). I'd stick to returning (server) logins only - after all the cmdlet is called "Get-SqlLogin".

    My interpretation of @sqlvariant's remark is that a -DatabaseName parameter would just act as a filter on the login objects returned: it would simply go to the given database, check all users in that database, and returns the (server) logins associated to the db user (if any).

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogin

    Ahh, I'm so 60/40 with @michaelwells11 and @sqlvariant, here. I see the benefits and the potential issues. (And we totally need the wildcards per @robsewell1 !!)

    Klaas Vandenberghe Klaas Vandenberghe on Get-SqlLogin

    @michaelwells11 I agree with both remarks.

    Aaron Nelson Aaron Nelson on Get-SqlLogin

    @michaelwells11 Not if you wanted to manually filter on the fly :wink:

    Michael Wells Michael Wells on Get-SqlLogin

    I would caution against mixing the concept of server logins and database users. Also, would it be more consistent to Get-SqlLogin ... | ?{} | New-SqlLogin?

    Laerte Laerte on Get-SqlLogin

    makes tottaly sense.. everything that could be filtering server-side it´s very very welcome (avoiding where-object, where()..) whatever

    Rob Sewell Rob Sewell on Get-SqlLogin

    wildcards - get-sqllogin TheBeard* should return the login TheBeardedOne

    Aaron Nelson Aaron Nelson on Get-SqlLogin

    What Parameters would people like to see this cmdlet have?

    Does a -Database parameter make sense for getting logins only that exist in a specific database [or list of databases]?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlLogin

    Yes! I've already added support to dbatools for when this exists ;)

    Labels

  4. dbatools' Copy-SqlLogin copies the following: SIDs, passwords, server/database roles, server/database permissionsets & securables, default database and login attributes. Please make these properties (and more) exportable/importable.

  5. Pretty straightforward, should accept an array of login names and remove them if it finds them.

    Comments

    Aaron Nelson Aaron Nelson on Remove-SqlLogin

    @thomasrushton Haha, Thomas. PLEASE don't wander off! :smile: It was a great question because the initial version of the documentation was unclear to me if that was what the parameter actually did, so I had to email Matteo to verify (and suggest some more detailed wording be used). That's how I knew for sure.

    Thomas Rushton on Remove-SqlLogin

    @sqlvariant Ooh, sorry about that.

    <fx: wanders off, hanging head in shame>

    Aaron Nelson Aaron Nelson on Remove-SqlLogin

    @thomasrushton That is actually a feature already. Just throw the -RemoveAssociatedUsers parameter and the Remove-SqlLogin cmdlet will do that for you today.

    Thomas Rushton on Remove-SqlLogin

    Would this also be able to iterate through all the databases and remove users associated with that login?

    Currently having to script this for a tidy-up job here...

    Tom Roush on Remove-SqlLogin

    Cool... We were running into this recently - trying to remove a login only to have it own some sort of endpoint on the server (I don't remember exactly)
    does the cmdlet change the owners of those things to sa before trying to drop the user/login?
    ...or have it say, "this user owns object x in <location>, do you want the owner of this object to be changed to SA <or service acct> and then have the original user dropped?>
    That's real life stuff... Thoughts?

    Aaron Nelson Aaron Nelson on Remove-SqlLogin

    @klaasv If a switch is never seen, did it ever exist? (tree falls in a forest with no one around...)

    Your points actually dovetail with a conversation I had @matteo437 earlier this summer about -ParameterNames. Long story, but the pieces are this:
    :ballot_box_with_check: We have our own DSL (Domain Specific Language) and we're going to do PowerShell a little differently than the rest of the PowerShell world, and that is both OK, and an already known fact.
    :ballot_box_with_check: We need to be smart about the DRY pieces everywhere we possibly can.
    :ballot_box_with_check: Parameter names are searchable (Get-Command -Module SQLServer -ParameterName Database), we need a parameter name to mean the same thing everywhere you see it.

    There's more to it, but those are the basics. Glad you joined the conversation with us! :smile:

    Klaas Vandenberghe Klaas Vandenberghe on Remove-SqlLogin

    @sqlvariant Nice, great minds ... But I'm still afraid this is just one of 125 switches eliminated.

    Aaron Nelson Aaron Nelson on Remove-SqlLogin

    @klaasv :thumbsup: It's like you're reading my mind! :smile: I had virtually the same idea (just was thinking to use Export-SqlDbSchema as a catch all) and I really like your Script-Object idea.

    Klaas Vandenberghe Klaas Vandenberghe on Remove-SqlLogin

    @sqlvariant @matteo437 that's a valid argument, Aaron; DRY is important. I don't know if -scriptaction can become a common parameter like -erroraction or a separate cmdlet?

    What about:
    Get-SqlLogin -Filter 'Name -LIKE "apple"' | Script-Object -PassThru | Remove-SqlLogin

    Matteo Matteo on Remove-SqlLogin

    @sqlvariant, @klaasv : ok, let's think more about this...

    Aaron Nelson Aaron Nelson on Remove-SqlLogin

    @matteo437 @klaasv I agree with the spirit of what KlaasV is saying, 700 cmdlets is too much for SQL People to also have to know.

    I have an idea for how to handle this situation, but I need to test it to see if the idea is viable.

    In the meantime, I think we're (the royal 'we') going to want to soon see an Export-SqlDatabase or Export-SqlDbSchema cmdlet with lots of robust -Switches so that we can have a single way to remember how to backup / script out our objects before we drop them. That way we don't have to reinvent that wheel inside of every other cmdlet that gets created.

    Thoughts?

    Klaas Vandenberghe Klaas Vandenberghe on Remove-SqlLogin

    @matteo437 I'm not sure. On one hand, having all those options would be the only reason for me to use it. On the other hand, we would need to see a complete list of useful parameters and switches before you can decide if it's worth the trouble and if it will be manageable.

    I'm not even sure about the shorter name :-s.
    If using a createscript switch, do we want orphaned users or not? Do we want grant statements, add-rolemember? Is the logic different for ADuser vs ADgroup vs sql login? What if it's a schema owner? When a list of logins, should it be one transaction that rolls back if one login can't be removed? Do we want a single script or a script for each login?

    If those choices are fixed in the cmdlet, I can't use it as soon as there's one divergence from what I want in a certain case. If those choices are all to be made by parameters and switches, it will be a very complicated syntax with lots of similar names. If those choices are to be made outside of the cmdlet, there's no gain in using it anyway. :-(

    Matteo Matteo on Remove-SqlLogin

    @klaasv I think I see your point. So, adding that option to the "critical" cmdlets (like this one) could make sense [maybe with a shorter name? :-)]

    Klaas Vandenberghe Klaas Vandenberghe on Remove-SqlLogin

    @matteo437 I am very much in doubt. I still don't see the advantage of having 700 cmdlets when they each replace only two lines of code. We can either navigate through the SQLSERVER: provider or instantiate a SMO object and call the desired method, or use the passe partout cmdlet Invoke-SqlCmd.

    As soon as there is extra logic involved, as in this example, ( a very primitive way of source control / DR. If the 'DROP USER' would appear to cause problems, I can recreate it in a second), we can either write a script with somewhere in the middle this new cmdlet, replacing only two lines, or use a new cmdlet with all logic built in, which would indeed mean a lot of parameters and switches, one being the -createscript. Multiplied by a truck load of cmdlets, half of the Powershell for SQL experience will become 'get-help'. I'm afraid Powershell, or at least the SqlServer module will become bloatware, suffer from more bugs, more conflicts, dependencies and lots of disadvantages we now see in GUI's and wizards.

    I think improving Invoke-Sqlcmd and Out-Datatable | Write-Datatable are the efforts that will have the greatest ROI. As for Remove-SqlLogin and most other cmdlets, I can only think of those as useful if they handle all possible scenarios on themselves. If not, I prefer a module with 20 cmdlets over one with 700.

    Matteo Matteo on Remove-SqlLogin

    @ctrlb Can this be moved to the "Wins!!!" column? :-)

    Matteo Matteo on Remove-SqlLogin

    @klaasv (sorry for the delay - oops!) Are you saying you want to script out the login before it is deleted? Are you sure the parameter belong to this cmdlet? Somehow, it does not feel natural to me...

    What do people think?

    Klaas Vandenberghe Klaas Vandenberghe on Remove-SqlLogin

    a switch -SaveCreateScriptWithRolememberShipsAndPermissions would be really nice

    Matteo Matteo on Remove-SqlLogin

    Added Green Label (i.e. we are looking into this... stay tuned!)

    Labels

  6. For enabling/disabling logins as well as syncing SID's.
    Set default database

    Comments

    Ben Miller Ben Miller on Set-SqlLogin

    Update password for a SqlLogin, even maybe rename the login?

    Aaron Nelson Aaron Nelson on Set-SqlLogin

    Set default database

    K. Brian Kelley K. Brian Kelley on Set-SqlLogin

    This would enable automated managed of SQL Server logins via script outside of SQL Server. Much needed when building new servers, etc., for automation/DevOps.

Configuration (sp_configure)

  1. Auto-populated parameter with name like

    Get-SqlConfiguration -Name MaxMemory

    Connect Item

    Comments

    Chrissy LeMaire Chrissy LeMaire on Get-SqlConfiguration

    Would so love this to help 1.) Give a nice overview when used with Out-GridView and 2.) Would let me -Passthru to Copy-SqlSpConfigure :D

    Labels

  2. The dbatools module has this. It exports all of the configs to SQL.

  3. Similar to DBCC TRACESTATUS, but being exposed via a cmdlet will make it easier for Trace Flags to be identified & inventoried.

    A corresponding Set-SQLTraceFlags would obviously be important as well.

    Comments

    alzdba alzdba on Get-SqlTraceFlags

    Problem with TF Dictionary is that there are still TF's that are undocumented (= unsafe / veryspecific usecases / hazardous /.. )

    Determining if a TF isStartup = the get-equivalent of @mikefal Set-SqlStartupParameters

    Will it show TF's that have been stopped/removed, but will be activated at SQLInstance startup time ?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTraceFlags

    @matteo437 Attached :) Based off of a list updated in January. Anyone else wanna clean this up or have more input? I converted the list from sqlservice.se. /cc @dbaduck @mikefal @sqlvariant

    Matteo Matteo on Get-SqlTraceFlags

    @ctrlb Yes, please.

    Ben Miller Ben Miller on Get-SqlTraceFlags

    @ctrlb I am going to think about this in context of what is there and I will reply back.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTraceFlags

    @sqlvariant @mikefal @dbaduck Can anyone out there give a mockup of what this should look like or is the goal the attached photo?

    Would it be useful for me to create a Dictionary of TraceFlag to Definition? so that there's a fifth column that has a summary of the Trace Flag. I know it'd help me; I don't have them memorized. @matteo437, would that help you to plunk in the c# code?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTraceFlags

    Would it be beneficial to have a dictionary of Trace flags to Traceflag definitions? If so, I can create one in C# and give to matteo or would that be constant maintenance?

    What would you guys like the output to look like? Do you have a screenshot?

    Michael Fal Michael Fal on Get-SqlTraceFlags

    I've got a couple functions for Get/Set-SqlStartupParameters that I use a lot. After talking with @ctrlb , it seems like this would be a good place to share and see if they would at least serve some of this purpose.

    https://github.com/MikeFal/PowerShell/blob/master/Set-SqlStartupParameters.ps1

    Ben Miller Ben Miller on Get-SqlTraceFlags

    This is on my list too. In SMO you can only set a trace flag for the current session and you can't set it globally. I would love to have this functionality in the SQLPS:

    Get trace flags globally and have a status whether they are in the -T of SQL startup and status of global, and status of session.
    Set a trace flag for the session.
    Set a trace flag globally.
    Add trace flag to the startup of SQL Server -T options.

    Sean McCown Sean McCown on Get-SqlTraceFlags

    Well you can differentiate between start-up TFs and set TFs. You may want to know which TFs are active on the box globally, and which ones are set to startup with sql.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTraceFlags

    @sqlvariant I think you're right. Misc? Should there be a differentiation for diff levels?

    Aaron Nelson Aaron Nelson on Get-SqlTraceFlags

    @ctrlb Well, not all Trace Flags have to be instance-level IIRC?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlTraceFlags

    Should this go under configuration or the newer category Instance management?

    Labels

  4. If Export comes from diff version/edition that has features that aren't supported, Import-SqlConfiguration should detect and warn, but not throw a terminating exception.

  5. Comments

    Nic Cain Nic Cain on Set-SqlConfiguration

    @ctrlb this hasn't really been all that well thought out on the MS side I think.

    For example, sys.configurations in 2016 is the soft numa is disabled option. This cannot be set using sp_configure, rather it has to be done with alter server configuration. Conversely, something like maxdop has to be done with sp_configure.

    The first thing might to be have the team fix things up so that there is a common syntax, which would ultimately make it easier to plumb into.

    As regards reconfigure. Adding a reconfigure switch would be a good option, with a warning if not used that a reconfigure is required if the switch is not passed along with the set-sqlconfiguration call.

    Chrissy LeMaire Chrissy LeMaire on Set-SqlConfiguration

    @niccain My original intent was sp_configure. Is the name too vague? If a cmdlet is made for ALTER SERVER CONFIGURATION, what could its name be? I think a reconfigure by default would be convenient with a -NoReconfigure switch. But I may be too informal with my approach. What were your thoughts?

    Also, love the idea of a warning if the change requires a restart of the service.

    Nic Cain Nic Cain on Set-SqlConfiguration

    And would this be based around ALTER SERVER CONFIGURATION, or sp_configure? And would it implicitly do RECONFIGURE in the case of sp_configure?

    Nic Cain Nic Cain on Set-SqlConfiguration

    It should also output whether the change requires a restart of the SQL service to take effect.

SSAS (SQL Server Analysis Services) -Provider Enhancements

  1. Add LastProcessed to Dimensions folder in SQLAS-SQL PowerShell Provider.
    https://connect.microsoft.com/SQLServer/feedback/details/3111879/add-lastprocessed-to-dimensions-folder-in-sqlas-sql-powershell-provider

Reporting Services

  1. I've been in the position that I need to move a bunch of reports. There are some tools out there that do the job but they're outdated and no longer updated.
    It would be good to have an export cmdlet to export the reports next to the add cmdlet to import the reports.

    It probably needs to have a parameter for the source folder (either root folder or specific folder), source server, destination folder (either root folder or specific folder) and destination server.
    It would probably good to include an override parameter to override specific items when they exist on the destination.

  2. Comments

    Seddryck on Add-RsDataSource

    Pay attention beacuse the format of the data source file supported by Visual Studio is not the format supported by SSRS itself. Will you support both types of files ? The more useful IMO is the one supported by Visual Studio but in this case you'll need to perform a conversion (doesn't sound impossible)

  3. I have gone ahead and filed a Connect item to get this on Microsoft's radar: https://connect.microsoft.com/SQLServer/feedback/details/2999931

    Please continue to add comments, suggestions, here and/or on the Connect item.

    Labels

  4. Comments

    Parth Shah on Export-RsEncryptionKey

    @ctrlb Unfortunately, we do not have any plans for writing C# cmdlets at the moment.

    Chrissy LeMaire Chrissy LeMaire on Export-RsEncryptionKey

    @parthshah44 thanks for letting us know. Are there plans to write C# cmdlets for Reporting Services?

  6. Comments

  9. From Meagan Longoria on Slack: "
    It would be magnificent to just have a PowerShell script that I can run on any new server with SSRS installed to configure everything and know that I have safely stored my encryption key somewhere and that I have the correct/expected settings. That would give me so much piece of mind. DBAs and consultants around the world might hug you.
    "

  11. Comments

    Seddryck on New-RsFolder

    Could be weird to have the cmdlets New-RsFolder and Add-RsReport, both of them are children of a parent folder. On the other hand, you could argue that Add-RsReport is more the deployment of a report than the creation of a blank report.

  16. This appears to require a simple editing of the xml file. Also, a restart may be required after. Perhaps -Restart option or a warning that the service must be restarted.

    Comments

    Sergio Penteado Sergio Penteado on Set-RsAuthentication

    Hi, by authentication you mean the Security permissions for the folder ? If yes, this could have the parameters to select one or more roles.
    :)

  21. Code from thom schumacher

Roles

  2. Comments

    Aaron Nelson Aaron Nelson on Get-SqlServerRoleMember

    @petersamuelsson4 Peter, I really like your idea here, thanks for sharing your thoughts. :thumbsup: I think what you've explained also works nicely with discovering commands by searching for Parameter name.

    Peter Samuelsson on Get-SqlServerRoleMember

    Dont think it will add much complexity to the command this way

    Peter Samuelsson on Get-SqlServerRoleMember

    I would like to use it like this
    Get-SqlRoleMember -server servername -role rolename to get server roles

    Get-SqlRoleMember -server servername -database db -role to get database roles

    Set-SqlRoleMember -server servername -role rolename
    Set-SqlRoleMember -server servername -database db -role

    If -server isnt given its should look in localhost
    So Get-SqlRoleMember -role db_xxxx would get those users that are in the the role db_xxxx on the localhost

    And

    So Get-SqlRoleMember -database yyyy -role db_xxxx would get those users that are in the the role db_xxxx on the localhost in database yyyy

    Chrissy LeMaire Chrissy LeMaire on Get-SqlServerRoleMember

    The name would have to change, if so. Merging could potentially introduce complexity with the Set counterpart.

    Aaron Nelson Aaron Nelson on Get-SqlServerRoleMember

    I like that idea, It would be great to have less cmdlets to have to remember

    Niels Grove-Rasmussen Niels Grove-Rasmussen on Get-SqlServerRoleMember

    I think that the CmdLet should give info on both server roles and database roles.

SQL Server Agent

  1. Allows you to edit properties of one or many SQL Agent Jobs.

  2. Please add any issues you are aware of to this Car/Connect item:
    https://connect.microsoft.com/SQLServer/feedback/details/2592456

    In the past, when trying to run a PowerShell script via SQL Agent, selecting Type of PowerShell in your SQL Agent Job Step would impose additional restrictions that running the Job Step as a Type of Operating System (CmdExec) and then simply calling powershell.exe would not.

    Would it be possible to get additional documentation around what additional restrictions might be expected when running PowerShell Type step in a SQL Agent Job?

    Labels

  3. Essential Job support is missing from the SQLPS module; Get-SQLAgentJob & Get-SQLAgentJobHistory etc...

    This has already been submitted on Connect. Please go Up-Vote it there.
    https://connect.microsoft.com/SQLServer/feedback/details/2584782/

    A great capability that could easily be added to SQLPS module would be the ability to check the status of SQL Agent Jobs via a cmdlet. For this functionality it would be best to be able to pass in a list of SQL instances and get job status back for all of them.

    The SQLPSX CodePlex project has already implemented some PowerShell Functions for working with SQL Agent, I have included some of them here as one example of how this might be approached. Obviously your approach might be different but this model seems to work cleanly with the SMO. (SQLPSX.CodePlex.com > Download > Open file: Agent.psm1)

    My primary example for this would be the scenario where a DBA wants to check all of his or her SQL instances for any failed jobs in the last 24 hours, and report back only those (ignoring jobs that completed successfully). The SQLPSX would approached this like so:
    Get-AgentJobHistory "Z002\sql2k8" $(Set-AgentJobHistoryFilter -outcome 'Failed');

    However I would be in favor of something more like Get-SQLAgentJobHistory "Z002\sql2k8" -Status Failed;

    My intent is to get the conversation started with this Connect item, and hopefully other community folks can contribute even better ideas/examples on top of this.

    If you were to run a cmdlet like Get-SQLAgentJob -jobserver "Z002\sql2k8"; with no additional parameters, I would expect it to tell me about all jobs on that SQL Agent service. The cmdlet Get-SQLAgentJob should have a -Script parameter that would generate and return the T-SQL syntax to create the job(s), similar to the functionality of the existing Backup-SqlDatabase cmdlet.

    <#
    Subset of functions included in this example:
    Get-AgentJobServer
    Get-AgentJob
    Get-AgentJobStep
    Get-AgentSchedule
    Set-AgentJobHistoryFilter
    Get-AgentJobHistory
    Get-AgentJobSchedule

    >

    Comments

    Ben Miller Ben Miller on Get-SqlAgentJob

    Sorry, I missed this one. I was speaking of SQLPSX. But the SqlAgent cmdlets coming in the SqlPS module (now SQLServer) one should help circumvent the need for SQLPSX.

    Aaron Nelson Aaron Nelson on Get-SqlAgentJob

    @dbaduck Are you talking about updating the SQLPSX module? Or SQLPS?

    Ben Miller Ben Miller on Get-SqlAgentJob

    With this out there (we need to get the module updated BTW, it loads 10 or 9 which is a little outdated) it means that getting it in SQLPS would just formalize it and give us a way that is loading one module and getting functionality surrounding SQL. This is a good add.

    Labels

  4. Comments

    Chrissy LeMaire Chrissy LeMaire on Find-SqlAgentFailedJobs

    @sqlvariant and @matteo437

  8. Ability to drop, or offer "-script" parameter that allows you to script and then drop a job. Offer ability of "-Filter" that can handle array of properties that are visible for the job SMO object.

    Comments

    Aaron Nelson Aaron Nelson on Remove-SqAgentJob

    @wshawnmelton Great idea, I have recently filed a Connect item requesting a Remove-SQLAgentJob cmdlet (as well as a couple others). If you think it makes sense to add your suggestion to that Connect item, please do.
    (Seems logical to me?)

    https://connect.microsoft.com/SQLServer/feedback/details/3110028/

Miscellaneous

  1. -Detailed shows OS restart as well?

    SELECT sqlserver_start_time FROM sys.dm_os_sys_info

    Comments

    Chrissy LeMaire Chrissy LeMaire on Get-SqlStartupTime

    @mikefal Fair enough. Updated the name!

    Michael Fal Michael Fal on Get-SqlUptime

    I don't think this should show "uptime". I like the name Get-SqlStartupTime. Have it return the startup time and let the user do with it what feels right. Which could be (Get-Date) - (Get-SqlStartupTime -ServerInstance localhost). Or (Get-SqlStartupTime).ToUniversalTime().

  2. Ability to execute PS code via T-SQL. Similar fashion to the xp_cmdshell.

    Comments

    Shawn Melton Shawn Melton on xp_psshell

    Another point of discussion is the output options of the procedure. The cmdshell only returns as text, but PowerShell has the ability to return in multiple formats such as XML, or Chrissy suggested JSON. Could add a parameter to the procedure that optimally would give you options that PowerShell supports (HTML, XML, text, JSON, etc.)

    Shawn Melton Shawn Melton on xp_psshell

    A security consideration being that PowerShell can execute remote commands (PS Remoting) would we want it restricted to local cmdlets or just SQLPS?

  3. Scripts an SMO object. Previously named Get-SqlScripter in SQLPSX.

  4. Creates a new Microsoft.SqlServer.Management.Smo.ScriptingOptions object to work in conjunction with Invoke-SqlScripter

    Comments

    Brian Lalonde Brian Lalonde on Set-SqlScriptingOptions

    It may be more idiomatic to PowerShell to remove the need for the scripting object as an object, and just allow a list of values. That's what I did in some SQL export scripts:
    https://github.com/brianary/scripts/blob/master/Export-DatabaseObjectScript.ps1 &
    https://github.com/brianary/scripts/blob/master/Export-DatabaseScripts.ps1

    Chrissy LeMaire Chrissy LeMaire on Set-SqlScriptingOptions

    I agree that Set feels better than New. Updated!

    Aaron Nelson Aaron Nelson on New-SqlScriptingOptions

    You are basically setting a bunch of variables so Set-SqlScriptingOptions seems to align with Set-Variable to me.

    Chrissy LeMaire Chrissy LeMaire on New-SqlScriptingOptions

    @sqlvariant Yeah, I don't like New either. Set or Add or Get or other?

    Microsoft says: The New verb is used to create a new resource. The Set verb is used to modify an existing resource, optionally creating the resource if it does not exist, such as the Set-Variable cmdlet.

    Aaron Nelson Aaron Nelson on New-SqlScriptingOptions

    Shouldn't this be a Set- that you load to a hashtable and then pass to Invoke-SqlScripter?

  6. Install-SqlServer needs to be a cmdlet. It should accept a hashtable as input for all of the choices.

    See SQLSPADE for ideas: http://sqlspade.codeplex.com/

    Today we are introducing the new Basic Installer experience for SQL Server 2016 Express, SQL Server 2016 Developer, and SQL Server 2016 Evaluation Editions. With three clicks, you can get a default installation of these editions and have SQL Server installed in a manner of minutes.
    https://blogs.msdn.microsoft.com/sqlreleaseservices/the-sql-server-basic-installer-just-install-it-2/

    Comments

    Daniel T on Install-SqlServer

    Doesn't chocolatey handle SQL Server installation for SQL express? something like: choco install mssqlserver. But I couldn't find one for SQL Server 2016 express. Also since now developer is essentially free we can create a chocolatey package so that users can have that apt-get like experience.

    Aaron Nelson Aaron Nelson on Install-SqlServer

    Today we are introducing the new Basic Installer experience for SQL Server 2016 Express, SQL Server 2016 Developer, and SQL Server 2016 Evaluation Editions. With three clicks, you can get a default installation of these editions and have SQL Server installed in a manner of minutes.

    https://blogs.msdn.microsoft.com/sqlreleaseservices/the-sql-server-basic-installer-just-install-it-2/

    Aaron Nelson Aaron Nelson on Install-SqlServer

    Think of the Millions of SQL-People who would like an easy way to setup SQL Server. Database Developers & B/I People may not have any familiarity with PowerShell.

    Aaron Nelson Aaron Nelson on Install-SqlServer

    @arungopinath When it comes to installing SQL Server at the cmdline @michaelwells11 is my goto-guy. He might know a work around for that password issue.

    Arun Gopinath Arun Gopinath on Install-SqlServer

    @ctrlb @mikefal @sqlvariant Not sure if this is the right place to raise this issue (a connect request would be more appropriate?). Anyway, it looks like when we install sql from powershell, setup.exe doesn't seem to accept the passwords correctly especially when it involves special characters. For instance, if the following password is enclosed within single quotes 'Abc'def$', I'll have to escape the second quote using a back-tick. But if I do that, the setup always fails with invalid credentials error (note that I'm accepting credentials using read prompt securely and not hard-coding passwords in the config file or anywhere else). According to https://msdn.microsoft.com/en-us/library/ms144259.aspx, double quotes are recommended but we cannot use that either, because now I have to escape the $ sign in my password. It would be awesome if we could make everything consistent, maybe a new cmdlet is the way to go after all?

    Chrissy LeMaire Chrissy LeMaire on Install-SqlServer

    Discoverability and standardization since PowerShell will be the go to admin tool for command line.

    Michael Fal Michael Fal on Install-SqlServer

    I'm going to be a bit of a trouble maker here. What would an Install-SqlServer cmdlet offer over the current command line install path for SQL Server?

    Chrissy LeMaire Chrissy LeMaire on Install-SqlServer

    @sqlvariant @ryanyates6 It's true. I feel both ways. Perhaps install sql server could automate D.Sc.?

    Aaron Nelson Aaron Nelson on Install-SqlServer

    @ryanyates6 Understood. But I'm sure the vast majority of SQL people couldn't tell you what a DSC is. Also, whatever happens, it would be nice if you could call the same command whether you were on Windows or Linux.

    Ryan Yates Ryan Yates on Install-SqlServer

    This crosses over with the DSC Resource xSQLServerInstall and I would be confused why there would be an Install-SQLServer Cmdlet in the SQLServer Module at all as this doesn't match up with the other Microsoft Server products (not including built in features like AD, DHCP, DNS etc)

    Chrissy LeMaire Chrissy LeMaire on Install-SqlServer

    @michaelwells11 if an automated installer does need its own board, let me know and suggest a title. I'm happy to unarchive and add as an item to Overall Priorities.

    Aaron Nelson Aaron Nelson on Install-SqlServer

    @michaelwells11 You should probably take the lead on this one given your extensive work on the subject.

  7. Comments

    Ben Miller Ben Miller on Extended Events Cmdlets

    I would advocate for at least a Get-SqlExtendedEventSession or Get-SqlXESession if you don't want it to be too long. But right now you only have the option to do something really funky to get to the XE session, so anything in that space would be much appreciated. Corresponding Start-SqlXESession and Stop-SqlXESession and Remove-SqlXESession would be great. The complexity of creating a session would probably be too much, but at least Management functions would be great.

    Shawn Melton Shawn Melton on Extended Events Cmdlets

    @warrenframe I would say most we could get was management around XEvent sessions. It could be possible to get some cmdlets for reading the session data; there are .NET classes that can be used to read session data via a stream reader but I've never been able to figure it all out. MS probably could.

    Warren Frame Warren Frame on Extended Events Cmdlets

    This is a bit outside my comfort zone, apologies if there's not enough info. IT professionals (and presumably DBAs) can get great value out of seeing what's going on under the hood.

    On my side, using profiler or extended events to see what T-SQL queries an application or person is making was quite valuable.

    It might be helpful to have a command, or series of commands (given the complexity of extended events), that could enable that scenario, among others, on demand.

    Folks like myself would likely need to follow a recipe, rather than understanding the different events, predicates, targets, etc.

    Several PowerShell modules illustrate how you might design this if you used multiple commands: e.g. Scheduled Tasks https://technet.microsoft.com/en-us/library/jj649816.aspx.

    That being said, given the potential impact of using extended events incorrectly, there could be some risk on simplifying setting these up : )

    Cheers!

  8. We are marking the existing https://trello.com/c/reJfNNcu as complete and will eventually archive it. We are starting this new card to capture ideas on how to make Get-SqlErrorLog better.

    Here is one suggestion to get us started, please add yours in the commens:
    I would like to see something along the lines of a -Search parameter. This parameter should accept an array of terms, and should automatically put the wildcards around the search term ('%%').
    Example of idea in action: Get-SqlErrorLog -ServerInstance localhost, localhost\SQL2016 -Since LastWeek -Search 'Memory', 'Deadlock'

    Comments

    Hristo Musulanov Hristo Musulanov on Get-SqlErrorLog Enhancements

    As requested by Aaron, moving earlier comments on Get-SqlErrorlog to this card:

    Suggestions:
    1. Ability to handle "null/empty" messages (most likely encountered via badly coded user defined messages) in Sql Errorlog without raising casting exception on 'System.DBNull' to 'System.String'.
    2. Investigate possibility of filtering at source via Include/Exclude parameters whether through cmdlet or SMO if it makes more sense. This can make error log scraping more efficient by reducing network traffic and processing of result sets when suddenly a SQL Server generates large number of repeated errors in error log (for example login failures).

  9. A standard method for getting the SQL Errorlog which negates opening management studio, or crawling through multiple files.

    Should accept parameters

    -Timespan <minutes> to pull last <n> minutes of logs
    -StartTime pull logs from specified value to either now, or -EndTime
    -EndTime pull logs up to specified value from start of logs or -StartTime

    Should also have Set-SqlErrorLog which has single parameter to set the retention count of logs
    Set-SqlErrorLog -MaxLogCount [int]

    Connect Item:
    https://connect.microsoft.com/SQLServer/feedback/details/2811832

    Comments

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    I would love to see a -MaxErrorLogSize parameter added to the Set-SqlErrorLog cmdlet. This parameter should accept standard PowerShell options for size, like "100MB"

    Drew Furgiuele Drew Furgiuele on Get-SqlErrorLog

    Okay guys, I just published a blog post on my time with (and attempt at improving) Get-SQLErrorLog. You can read about it here... would love to hear your feedback: http://port1433.com/2016/10/25/building-a-better-get-sqlerrorlog/

    Matteo Matteo on Get-SqlErrorLog

    @dfurgiuele Cool - looking forward to it.

    Drew Furgiuele Drew Furgiuele on Get-SqlErrorLog

    So, I've been playing with the cmdlet too. I was working on a couple enhancements. I'm almost done and when I am, I'll share it with you guys. I think I have some good ideas...

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @hristomusulanov Hristo Would you mind moving your excellent comment about the issue you have encountered over to the new https://trello.com/c/BiwfiSYG card?

    It doesn't look like I have the ability to do that.

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 @ctrlb The I's have it.
    We'll start a new card and see what folks think about that approach.

    Matteo Matteo on Get-SqlErrorLog

    @hristomusulanov Great. Thanks for the detailed repro.

    Matteo Matteo on Get-SqlErrorLog

    @sqlvariant Yes, a new "improvement" card would be good.

    Hristo Musulanov Hristo Musulanov on Get-SqlErrorLog

    Matteo - thank you for your reply.

    Please note: my comment has no bearing on whether to put the cmdlet in the wins or not - please do as it is useful as it is.

    Wrt issue, here’s a repro to assist with debugging:

    Start by checking no issues (note using SQL 2016 RTM - haven't tested other versions) PS > Get-SQLErrorLog -ServerInstance '(local)\SQL2016' -Since Midnight | measure | select count

    Count

    1

    1 record in errorlog before we start - no errors. Good.

    Now run following SQL code on instance // can imagine something like this could happen with bad coding DECLARE @errormsg varchar(1024)

    DECLARE @optype varchar(7)

    SET @optype = NULL
    SET @errormsg = 'Test ' + UPPER(@optype)

    RAISERROR (@errormsg, 16, 1) WITH LOG

    Lets grab errorlog again

    PS > Get-SQLErrorLog -ServerInstance '(local)\SQL2016' -Since Midnight | measure | select count

    Count

    3

    No issues although not sure why since we do get the problem on the second iteration ### Run the SQL Code again

    Now when you run the code you get the casting issue - see below

    PS > Get-SQLErrorLog -ServerInstance '(local)\SQL2016' -Since Midnight | measure | select count Get-SQLErrorLog : Unable to cast object of type 'System.DBNull' to type 'System.String'. At line:1 char:1
    + Get-SQLErrorLog -ServerInstance '(local)\SQL2016' -Since Midnight | m ... + ~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Get-SqlErrorLog], InvalidCastException + FullyQualifiedErrorId : System.InvalidCastException,Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog

    PS > $Error[0] | fl *

    PSMessageDetails :
    Exception : System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'. at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.<>cDisplayClass39.<GetLogs>b26(<>fAnonymousType2`2 <>hTransparentIdentifier1a) at System.Linq.Enumerable.<>cDisplayClass7_0`3.<CombineSelectors>b0(TSource x) at System.Linq.Enumerable.<>cDisplayClass7_0`3.<CombineSelectors>b0(TSource x) at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Linq.Enumerable.<SelectManyIterator>d__223.MoveNext() at System.Linq.Lookup2.Create[TSource](IEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer) at System.Linq.GroupedEnumerable3.GetEnumerator() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.OrderedEnumerable1.<GetEnumerator>d__1.MoveNext() at System.Linq.Enumerable.<SelectManyIterator>d__223.MoveNext() at System.Management.Automation.MshCommandRuntime._WriteObjectsSkipAllowCheck(Object sendToPipeline) at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state) at System.Management.Automation.MshCommandRuntime.WriteObject(Object sendToPipeline, Boolean enumerateCollection) at System.Management.Automation.Cmdlet.WriteObject(Object sendToPipeline, Boolean enumerateCollection) at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.GetLogs(Server srv, DateTime afterDate, DateTime beforeDate, Boolean ascending) at Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog.ProcessTarget(Server target, SmoRecordContext context) at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord() at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ProcessRecord() at System.Management.Automation.CommandProcessor.ProcessRecord() TargetObject :
    CategoryInfo : NotSpecified: (:) [Get-SqlErrorLog], InvalidCastException FullyQualifiedErrorId : System.InvalidCastException,Microsoft.SqlServer.Management.PowerShell.GetSqlErrorLog ErrorDetails :
    InvocationInfo : System.Management.Automation.InvocationInfo ScriptStackTrace : at <ScriptBlock>, <No file>: line 1
    PipelineIterationInfo : {}

    PS >

    Wrt filtering - if implementing in SMO makes more sense it’s fine as we can always create a wrapper function. The functionality would be very useful for errorlog scraping.

    Thanks,
    Hristo

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 Matteo, This cmdlet seems pretty stable so I would like to mark it with gold/orange colored label (we'll call it gold) and start a new card called "Get-SqlErrorLog Enhancements". The thought being that since this one has so many comments that have already been implemented, I don't want to overwhelm new people.

    Sound Reasonable?

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 Matteo, I do think this can be moved to "Wins" but I would like to see some enhancements on it.

    I would like to see something along the lines of a -Search parameter. This parameter should accept an array of terms, and should automatically put the wildcards around the search term ('%%').

    Example of idea in action:
    Get-SqlErrorLog -ServerInstance localhost, localhost\SQL2016 -Since LastWeek -Search 'Memory', 'Deadlock'

    Matteo Matteo on Get-SqlErrorLog

    @hristomusulanov Very cool. Do you happen to have more details on the error message (ideally: do you have a way for me reproduce the issue? Or, do you have a callstack? Maybe hidden under $Error[0] or dumped on the screen?)

    As for the filtering, I think it would be a nice addition. However, I'd rather look into improving SMO to allow that then expose that functionality in the cmdlet (as opposed to writing a bunch of T-SQL in the cmdlet itself) - kind of an implementation detail to the PowerShell user, I know...

    I've added it to the backlog...

    Hristo Musulanov Hristo Musulanov on Get-SqlErrorLog

    @matteo437: firstly - thank you for the great work you've been doing!

    I've been meaning to comment on this one but wanted to write a more prepared comment -maybe not now :-)

    Anyway, probably an edge case, but have found an issue pulling back errorlog content when there is a "null/blank entry" in the errorlog (user defined message gone wrong ) so you get unable to cast System.DBNull message.

    I've not had time to investigate workarounds and maybe there are simple ones but would be nice if it was handled out of the box?

    Also, don't know if feasible but like the idea of include/exclude parameters as per Aaron's Trello card to enable filtering on target to reduce network traffic.

    My 2 cents.

    Cheers
    Hristo

    Matteo Matteo on Get-SqlErrorLog

    @ctrlb : is the existing Get-SqlErrorLog good enough? If so, can we move to "Wins!!!", please? Thanks!

    Cláudio Silva Cláudio Silva on Get-SqlErrorLog

    @niccain UpVoted!

    Chrissy LeMaire Chrissy LeMaire on Get-SqlErrorLog

    @matteo437 :smile: :smile: :smile: :smile:

    Matteo Matteo on Get-SqlErrorLog

    @sqlvariant I just added it. :)

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 Matteo, please share the " -Since parameter" code with the guy doing the Get-SqlAgentJobHistory cmdlet! :smile:

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @niccain UpVoted!

    Matteo Matteo on Get-SqlErrorLog

    @niccain Thanks!

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 Yes, for iteration #1 that would be great. If we start with something basic and build on it and make it better, as long we strive to not take away parameters, I think that everyone can live with that.

    I definitely think that if we get basic cmdlets out into peoples hands we'll start getting a bunch of feedback asking for things to be added. So start simple and build on as demand grows works for me! :thumbsup:

    Matteo Matteo on Get-SqlErrorLog

    @sqlvariant Currently I'm using 2 or 3 methods offered by SMO. I did not see anything obvious to do the filtering on the server side when I was looking at the simple filtering based on the time range (the SMO methods return DataTable objects, so you get the whole deal on the wire/in-memory... which is kind of sucky, if you ask me). I'll look harder and also ask around... I was hoping I could avoid going to T-SQL...

    Maybe for iteration #1, we can call it good as it is? We can look for improvements later on...

    Time to hit the hay for me... :-)

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 OMG, OMG, OMG!!! That's amazing! :thumbsup: :star2: :fireworks:

    So.....
    I want to go ahead and ask about some filters that may be easier for you to implement than it would be for us:

    Is it possible to have a parameter like -IgnoreBackups or something like that which would pre-filter the results of the log file and remove any [successful] database backup messages from the log file data before you send it over to us? The thought here being that SQL Error Log files can sometime get really big if a maintenance cycle isn't setup for them. So if you can easily filter out groups of messages before returning them to us, that would be very helpful.

    Most SQL-people I know don't work in the same building as their production SQL instances. So anything we can do to pre-filter the results would be very helpful for some folks. Anything that's easy for you to identify and filter out might be useful.

    Matteo Matteo on Get-SqlErrorLog

    @ctrlb Yep. I was contemplating passing multiple servernames, etc... I can include that info so one know where the logs are coming from.

    Matteo Matteo on Get-SqlErrorLog

    @sqlvariant I'll do a little "usability" testing later today to see how it flows... For now I focused on getting the "core" functionality right.

    Btw, I added a -Since parameter that can take a few known values like "Midnight", "Yesterday", "LastWeek", "LastMonth" and would compute the corresponding Timespan (typing a timespan or a datetime is not fun, so I thought a little "tabbing" with canned values may be handy)

    js0505 on Get-SqlErrorLog

    Line 131 should be updated to reference $SQLServer not $Sqlinstance, as that parameter is not declared anywhere in the script.

    Chrissy LeMaire Chrissy LeMaire on Get-SqlErrorLog

    @sqlvariant @matteo437 Cool, yah archive num, too. Aaron, that's sorta what I was thinking. If multiple servers' error logs are returned, then include server name. Otherwise, looks perfect.

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 That looks awesome. Let's say you wanted to search through multiple instances at the same time, but with the same filters. Would we pass an array of instance names into this cmdlet or would you want us to call it multiple times (possibly in a loop)?

    Matteo Matteo on Get-SqlErrorLog

    @ctrlb For now, I'm returning: Date/Source/Text (same as SSMS) + the ArchiveNo which I think make be useful. I'm open to suggestions if you think I should add more if they make sense to people...

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @matteo437 :heart_eyes:

    Chrissy LeMaire Chrissy LeMaire on Get-SqlErrorLog

    @matteo437 this is beautiful 😍 what are all of the fields you will return if I can ask?

    Matteo Matteo on Get-SqlErrorLog

    @ctrlb @sqlvariant, @niccain: hey guys, I've been playing around with this cmdlet today... Here's how it looks right now (still a prototype, but just to get the feeling of it). What do you think? https://trello.com/1/cards/5717c390df1ff35c45fb4c2e/attachments/575e8e602a3b1302d49ff839/download/image.png

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @niccain Can you go ahead and file a Connect item for this and then add the link for the Connect item to this card please?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlErrorLog

    @niccain added as attachment, thanks so much!

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    @niccain Oh yes, separate option is totally what I meant. Agreed.

    Nic Cain Nic Cain on Get-SqlErrorLog

    @sqlvariant I find the whole lognumber thing to be a major PITA, that's why I wanted to use date ranges. Of course log number could always be an option. I will see if I can post something over the weekend up on git that I have that does this already.

    Aaron Nelson Aaron Nelson on Get-SqlErrorLog

    This should accept an array of log numbers. Bonus points if you can accept the range operator directly.

    In addition to Nic's well thought out example, another expected use case should be something like this:

    Import-Module SQLServer; <# Of SQLPSX fame #>
    $GenericList = New-Object System.Collections.Generic.List[object]
    1..6 | ForEach-Object { $GenericList.AddRange($(Get-SqlErrorLog -sqlserver LOCALHOST -lognumber $_))}
    $GenericList | OGV

    Ben Miller Ben Miller on Get-SqlErrorLog

    This would be great. Especially when the log is hidden in the Management part of SSMS and the log viewer is not as great as it could be. This is a good addition that should not be very hard to do, but the value is huge.

    Nic Cain Nic Cain on Get-SqlErrorLog

    A standard method for getting the SQL Errorlog which negates opening management studio, or crawling through multiple files.

    Should accept parameters

    -Timespan <minutes> to pull last <n> minutes of logs
    -StartTime pull logs from specified value to either now, or -EndTime
    -EndTime pull logs up to specified value from start of logs or -StartTime

    Should also have Set-SqlErrorLog which has single parameter to set the retention count of logs
    Set-SqlErrorLog -MaxLogCount [int]

    Labels

  10. Get-SqlErrorLog is great.

    It would be very helpful to have something along the lines of -Include & -Exclude parameters to filter messages before they are sent from the server across the wire to the client.

    An example of -Include might be: Get-SqlErrorLog -Include 'Deadlock'
    Another example of -Include might be: Get-SqlErrorLog -Include 'memory'

    An example of -Exclude might be Get-SqlErrorLog -Exclude "backup"

  11. Comments

    Ben Miller Ben Miller on New-SqlScriptingOptions

    I think that this option would lend well to creating a new ScriptingOptions object for use with SMO using parameters, instead of trying to create a new ScriptingOptions object and then setting properties.

    $options = New-SqlScriptingOptions -ScriptOwner -ScriptDrops, etc.

    I don't think it is that hard to create, but if you use a few options usually when scripting, it beats all the syntax to create one and then start setting properties.

  12. https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/13735221-out-datatable

    Creates a DataTable based on an objects properties. Useful for converting any Powershell object into a DataTable which then can be bulk imported into a SQL Server table.

    .EXAMPLE
    $dt = Get-PSDrive | Out-DataTable
    This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable

    This idea comes from Chad Miller's Out-DataTable: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content

    Comments

    Aaron Nelson Aaron Nelson on Not SQLPS per se, but we need this as part of the ecosystem: Out-DataTable

    Please go to User Voice and Up-Vote this item so that we can have an easier way of preparing data before we bring it into PowerShell

    Labels

  13. Enhance Set-SqlErrorLog cmdlet to add -Max[File]Size parameter.

    It would be great to add the functionality mentioned in this KB (https://support.microsoft.com/en-us/kb/2199578) in both SSMS and the new Set-SqlErrorLog cmdlet.

    It has been pointed out to me that -MaxSize is potentially confusing/not as clear as it could be. -MaxFileSize might be a better parameter name or even -MaxLogFileSize.

    Connect: https://connect.microsoft.com/SQLServer/feedback/details/2878016/set-sqlerrorlog-maxsize

    Labels

  14. .SYNOPSIS
    Tests if file or directory exists from the perspective of the SQL Server

    .DESCRIPTION
    Uses master.dbo.xp_fileexist to determine if a file or directory exists

    $sql = "EXEC master.dbo.xp_fileexist '$path'" $fileexist = $server.ConnectionContext.ExecuteWithResults($sql) if ($fileexist.tables.rows['File Exists'] -eq $true -or $fileexist.tables.rows['File is a Directory'] -eq $true) { return $true } else { return $false }

  15. A few of us were talking and would like to have SqlServer load up all the assemblies we'll need. LIke this, perhaps others?

    Microsoft.SqlServer.Management.Sdk.Sfc
    Microsoft.SqlServer.SqlEnum
    Microsoft.SqlServer.Management.RegisteredServers
    Microsoft.SqlServer.Management.XEvent
    Microsoft.SqlServer.Dmf
    Microsoft.SqlServer.ConnectionInfo
    Microsoft.SqlServer.RMO

    Comments

  17. In SQL 2016 there's an "Error and Usage Reporting" executable which shows a table of each instance, component, and configuration checks for telemetry and feature usage.

    I'd like a function to Get this data, and Set this data. It may require one more field which is whether the checkboxes can be altered or not (because some editions like Developer may not let you override).

    It should work remotely using standard registry calls (as that's what it's controlling) NOT just locally or through Invoke-Command.

    This is for Enterprise farm-wide validation and configuration of telemetry settings.

PowerShell Provider

  1. Comments

    Ryan Yates Ryan Yates on Installation of SQLPS with the Engine.

    Also I would look at the benefit of extracting SQLPS from the Engine install and moving to a model where SQLPS is installed via the PowerShell Gallery instead. Or at least can be made to be updated via the PowerShell Gallery.

    Some of this would require that SQLPS is installed in C:\Program Files\WindowsPowerShell\Modules when installed under the AllUsers Scope

    Considering from PowerShell v3 onwards we have had Module Autoloading we should really not have an issue with a movement of the SQLPS modules unless there is a requirement elsewhere in the engine install for them to be installed at the currently defined location?

    Also the Benefit of pulling the SQLPS module away from the engine/SSMS installs is that it would allow it to gain a quicker release cadence and can then be put forward for an open source development - which should really be a realistic end goal here.

    Ben Miller Ben Miller on Installation of SQLPS with the Engine.

    Thanks everyone. When teaching people about the SQLPS module and having it load a lower level version it gets pretty interesting helping them understand all the steps they need to take to ensure that things work well. I look forward to having this one put to rest and having a consistent story that works with future versions as we go along.

    Thanks all.

    Matteo Matteo on Installation of SQLPS with the Engine.

    @ryanyates6 Yep. That's precisely the direction I am going with my changes. Unfortunately, we need to be careful and keep in mind backward compatibility, etc... We need few small steps to get there...

    Ryan Yates Ryan Yates on Installation of SQLPS with the Engine.

    SQLPS should be installed in C:\Program Files\WindowsPowerShell\Modules and not the current location it is installed to with this new location then added to $env:PSModulePath

    Aaron Nelson Aaron Nelson on Installation of SQLPS with the Engine.

    One of my biggest concerns in this area is that I am worried that people who develop against a local instance are going to have different results when they go to deploy their new PowerShell script to a SQL Agent job.

    What can we do to make it easy/clear/unambiguous to people which precise version of SQLPS they are running, especially when they are running it inside of SQL Agent?

    Matteo Matteo on Installation of SQLPS with the Engine.

    Yeah, I hear you!

    There are definitely a few issues here:
    - SxS story is definitely sub-optimal.
    - I am not a big fan of somebody (read: SQL Installer or SSMS installer) messing with my PSModulePath
    - The location of the module is a little... bizarre

    I'm looking into these issues as we speak and hope to have a nice story moving forward...

    PowerShell 5.0+ (available on Win10 and later, but also available on most downlevel OSes) has a lovely SxS story: how would people feel about taking a dependency on that?

    Ben Miller Ben Miller on Installation of SQLPS with the Engine.

    When installing the SQL Server 2016 engine it installs the x86 version of SQLPS. It is not as big of a deal when there is only one version of SQLPS installed. But when there are more, it not only puts the x86 version of it but it puts the PSModulePath at the end which means that when I use Import-Module SQLPS it loads the first one in the list. Using Get-Module -ListAvailable SQLPS shows that the earlier versions are first as well.

    Please install the Platform specific module when installing a x64 tool, install the x64 version of the SQLPS.

    I know that all the assemblies are all MSIL so platform independent, but when automating the loading so that you get the specific version and I want the x64 version because it is a 64 bit OS, I should be able to automate that to not have to look in the (x86) directory for a module.

    Happy to provide more information on this if necessary.

  2. Currently, the speed is terribly slow at time. Many community members have commented on this being a huge problem. If we could consolidate of places in the Provider where the slowness occurs I bet that would be helpful to the SQL tools team.

  3. It looks like SQLPS is always reporting version 1.0.
    See http://port1433.com/2016/03/28/why-your-powershell-scripts-break-when-you-install-sql-server-management-studio-2016/

    I am currently using this bit of code to get around this issue:

    Import-Module -FullyQualifiedName 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS'

    Comments

    alzdba alzdba on Version SQLPS so PowerShell can distinguish and resolve a specific version.

    I ended up unistalling SSTDVS2015 to get SQLPS back to work without object type mismatches between SQL2014 an dSQL2016

    Ryan Yates Ryan Yates on Version SQLPS so PowerShell can distinguish and resolve a specific version.

    SQLPS should be versioned under Semver practices and as mentionsed in the Installation of SQLPS with the Engine card this would not occur if it was installed in one of the default PSModulePaths and not adding a new one.

    Also @sqlvariant is correct that versioning as v1.1 or even as v1.0.1 would have resolved this issue.

    alzdba alzdba on Version SQLPS so PowerShell can distinguish and resolve a specific version.

    What if PSModulePath doesn't contain the most recent SQLPS version ?
    Case is I ran the SSTDVS2015 installer and didn't yet install SSMS2016 on my client holding a SQL2014 client.
    Still I can see the assemblies for SQL2016 in the loaded assembly list.
    How can I avoid loading assemblies that don't match the installed SQLPs?

    Aaron Nelson Aaron Nelson on Version SQLPS so PowerShell can distinguish and resolve a specific version.

    I wanted to add: I don't know that it's necessary to jump the version all the way up to 2.0 just yet, if calling it version 1.1 resolves the issue then I'm fine with that. The thought here is that we could save the major version # until we have some more cmdlets in SQLPS for people to use. Just my $.02

    js0505 on Version SQLPS so PowerShell can distinguish and resolve a specific version.

    YUP, seen the same thing, thanks for reminding us! Great blog post!

  4. Comments

    Maximo Trinidad Maximo Trinidad on Update-Help failing when trying to get the help doc for the new SQLServer module.

    Hi Matteo,

    Here's the link:
    https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/15211857-get-help-cmdlet-should-skip-error-and-continue-upd

    The general question is... if this Update-Help failure will stop updating other module help documentation.

    Thanks,
    Max

    Matteo Matteo on Update-Help failing when trying to get the help doc for the new SQLServer module.

    @maximotrinidad Do you have a link to the feedback in the Windows forum? I'm hearing that the documentation has not been pushed out yet. Let me follow-up and understand why.

    Maximo Trinidad Maximo Trinidad on Update-Help failing when trying to get the help doc for the new SQLServer module.

    No worries! At least is documented. I also open a feedback in the Windows forum because this failure affected the rest of the module help docs by abruptly stopping the update-help process. :)

    Matteo Matteo on Update-Help failing when trying to get the help doc for the new SQLServer module.

    @maximotrinidad Also, there should be nothing to update right now. So, just ignore for now. What you have on the machine is the latest version.

    Matteo Matteo on Update-Help failing when trying to get the help doc for the new SQLServer module.

    @maximotrinidad This should be a temporary issue due to the fact that the documention folks are still working on it. It should be resolved once they upload the new stuff. Keep your faith... :)

Linked Servers

  1. Like the comment on Export-SqlLogin, this cmdlet should be useful and include the security/logins along with their encrypted passwords. Otherwise, people will use dbatools Copy-SqlLinkedServer which digs into the registry to get the password and make migrations genuinely automated.

Configuration Manager (Instance Management)

  1. Probe server to find locally installed instances (2000 and above)

  2. Comments

    Nic Cain Nic Cain on Get-SqlPort

    It would have to be multiple properties given multiple listening ports, or a property set

    Sean McCown Sean McCown on Get-SqlPort

    I'm not sure this needs a separate cmdlet. It could easily be just a property of Get-SQLInstance.

  3. Currently the Get-SqlInstance is designed to work with Azure VMs and is confusing when it comes to other Get- cmdlets that return SMO objects. Get-SqlInstance returns a Microsoft.SqlServer.Management.Powershell.IaaS.SqlInstanceInfo object and relies on the CloudAdapter service (installed by default on Azure VMs).

    This cmdlet should either be modified OR another cmdlet created that returns an SMO server object.

  5. As of 2010 this is what you had to do, we'd like a cmdlet instead:

    https://blogs.technet.microsoft.com/heyscriptingguy/2010/11/03/use-powershell-to-change-sql-server-service-accounts/
    <#
    This script will change the startup account of the specified SQL Service account on
    the machine you specify. This script requires to be run in an elevated PowerShell session
    For Details on how to elevate your shell go to: http://bit.ly/anogNt

    This script can be run to just tell you about the SQL services that you have running on a given machine by highlighting until you get down to the first $ChangeService Make sure to change: “MyServerName”, “MSSQLSERVER”(if that’s not the one you want) , “DomainName\UserName”, “YourPassword” This script may not work against a SQL Server that is not running PowerShell (c) Aaron Nelson

    Warning: I have not tested this with SSRS yet.

    >

    Load the SqlWmiManagement assembly off of the DLL

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

    $SMOWmiserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer’) “WIN7NetBook” #Suck in the server you want

    These just act as some queries about the SQL Services on the machine you specified.

    $SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

    Same information just pivot the data

    $SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List

    Specify the “Name” (from the query above) of the one service whose Service Account you want to change.

    $ChangeService=$SMOWmiserver.Services | where {$_.name -eq “MSSQLSERVER”} #Make sure this is what you want changed!

    Check which service you have loaded first

    $ChangeService

    $UName=”DomainName\UserName”

    $PWord=”YourPassword”

    $ChangeService.SetServiceAccount($UName, $PWord)

    Comments

    Sean Decker Sean Decker on Set-SqlServiceAccount

    Needs to support TDE and clusters. That is our biggest pain right now with service accounts resets. Anything but in Configuration manager break TDE.

  7. Comments

    Chrissy LeMaire Chrissy LeMaire on Set-SqlStartupParameters

    Code via @mikefal. Would also be good to have a built-in check to see if path exists, even when being set remotely.

Invoke-Sqlcmd

  1. Right now it is useless for connecting to listeners that use multisubnetfailover. This capability exists in sqlcmd, so should be in Invoke-SqlCmd

    Comments

    Aaron Nelson Aaron Nelson on Invoke-SqlCmd multisubnetfailover support

    Nic, were you saying this in the context of running Invoke-SqlCmd while navigated within the Provider? Or running Invoke-SqlCmd anywhere?

  2. Connect item submitted here

    "Invoke-SQLCmd should support parameterized queries and calling of stored procedures. Right now the only way to do it is to manually craft valid TSQL that does the parameterization. It is a advanced TSQL syntax that is generally not understood by the general Powershell admin.

    I am growing increasingly concerned with widespread use of concatenated SQL statements in Powershell scripts as a source of SQL injection." @KevinMarquette

    Comments

    Chrissy LeMaire Chrissy LeMaire on Parameterized Query support

    @jaykul agreed, there's a bit of buzz on Slack about this topic, too. Would be awesome for the ~Oct release ;D

    Joel Bennett Joel Bennett on Parameterized Query support

    Yeah, building strings for the -Variable parameter is a pain and doesn't make me feel safe. This is long overdue.

    Nic Cain Nic Cain on Parameterized Query support

    @maximotrinidad haven't seen that since 2012 was released

    Maximo Trinidad Maximo Trinidad on Parameterized Query support

    Hum! What about the issue Invoke-sqlcmd(x) had with timing out in the middle of execution. There is an SMO property that fix the issue. I know the code is not SMO but I don't see it been included. The timeout will happen after 20 minutes executing. Just an FYI!

    Warren Frame Warren Frame on Parameterized Query support

    Chrissy mentioned it might be worth pointing out Invoke-Sqlcmd2. Latest version that I'm aware of is here: https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1.

    I tend to recommend this over invoke-sqlcmd in all cases, given the lack of parameterized queries, among other features (/grumbles about dbnull).

    Cheers!

    Labels

  3. Add parameter to support the "-K" option that sqlcmd (command line) offers.

    Invoke-Sqlcmd -ServerInstance MyListener -ApplicationIntent ReadOnly

    Found a Connect item actually already posted for this functionality:
    https://connect.microsoft.com/SQLServer/Feedback/Details/1088152

    Labels

  4. Connect Item

    Comments

    Chrissy LeMaire Chrissy LeMaire on Meaningful support for -Verbose and -Debug switches

    @brianlalonde thanks for the request! I've added, please upvote :)

    Labels

  5. There's a good idea for alternate return values at Next Fix for SQLPS module – Invoke-Sqlcmd – Object Output | SQLvariations: SQL Server, a little PowerShell, maybe some Tableau. Aaron also entered it in Connect: Invoke-SQLcmd should be able to return results as an Object Type other than just System.Array | Microsoft Connect.

    Optional DataTable results in particular would allow more operations on the result set as a batch, serialization, &c.

    Comments

    Matteo Matteo on Return results as DataRow[], DataTable, or DataSet

    This should be coming very soon... :-)

    Labels

  6. Comments

    Matteo Matteo on Extend OutputAs support to include PSObject, SingleValue

    Hi @warrenframe, could you elaborate a little bit on the "SingleValue" option? What would be a typical scenario where this is useful?

    As for the "PSObject", wouldn't it make more sense to have an option parameter (switch) like "-ConvertDBNullToNull"?

    Warren Frame Warren Frame on Extend OutputAs support to include PSObject, SingleValue

    Hi!

    A previous suggestion was to extend Invoke-Sqlcmd to support 'As' types from the original Invoke-Sqlcmd2.

    Those types have been extended over time. The current iteration of Invoke-SqlCmd2 (https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1 AFAIK) supports two other potentially helpful types:

    SingleValue: Returns the actual data value from the first column in the first row of the returned DataSet.

    PSObject: Scrubs the returned data for DBNull, converts these to $Null. Yes, this goes against a SQL purist viewpoint, but it's more in line with typical PowerShell behavior. More details here: https://connect.microsoft.com/PowerShell/feedback/details/830412/provide-expected-comparison-handling-for-dbnull

    Cheers!

  7. I was troubleshooting an error earlier today which I had seem pop up before - where if you nest Invoke-SqlCmd | %{ ... Invoke-SqlCmd } you can see random failures with the error:

    ErrorRecord : The WriteObject and WriteError methods cannot be called from outside the overrides of the
    BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only be called from within the
    same thread. Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer
    Support Services.
    Message : The WriteObject and WriteError methods cannot be called from outside the overrides of the
    BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only be called from within the
    same thread. Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer
    Support Services.
    Data : {}
    InnerException :
    TargetSite : Void WriteError(System.Management.Automation.ErrorRecord, Boolean)
    StackTrace : at System.Management.Automation.MshCommandRuntime.WriteError(ErrorRecord errorRecord, Boolean
    overrideInquire)
    at System.Management.Automation.Cmdlet.WriteError(ErrorRecord errorRecord)
    at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(String str, Int32 num)
    at BatchParser.ThunkCommandExecuter.ProcessBatch(ThunkCommandExecuter , UInt16 batch, Int32 num)
    at BatchParser.ExecutionContext.ProcessWhatsLeftInBatch(ExecutionContext )
    at BatchParser.BatchParserInternal.Parse(BatchParserInternal     , ParserState* , Boolean
    flushBatchBuffer)
    at ManagedBatchParser.Parser.Parse()
    at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)
    at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
    HelpLink :
    Source : System.Management.Automation
    HResult : -2146233079

    There has been discussion of this bug a year ago at the bottom here where it claims you can separate the workloads into a holding variable to fix the problem: https://social.technet.microsoft.com/Forums/office/en-US/ef35781e-0c68-4442-89f1-e602120aded8/central-management-server-powershell-query-written-to-sql-server-database?forum=winserverpowershell

    In my case that doesn't seem to be working, and I only come across the issue during heavily runspace'd code where it's hard to reproduce the root cause.

    Comments

    Andrew Wickham Andrew Wickham on Random failures in nested pipelines

    I am using runspaces to run a script that makes several Invoke-Sqlcmd calls. Randomly (seemingly) I get failures with this same error.

    Matteo Matteo on Random failures in nested pipelines

    Hi @codykonior Interesting error... I'll try to take a look. Is the nesting a common scenario? I'm asking because Invoke-SqlCmd has a few other "high profile" issues that I would like to look into first... :)

Availability Groups

  1. Most of the AG cmdlets currently take a -Path parameter for a provider path to refer a specific object. This is cumbersome. Many times these objects can be referenced by using the server instance name, so the cmdlets should support that.

  3. Comments

    Chrissy LeMaire Chrissy LeMaire on Support MultiSubnetFailover for all cmdlets when connecting to an AG listener

    @mikefal I feel strongly about noooo! I don't like having to look up syntax, and love that PowerShell is all about findability. I love the idea of the params, and even a connection string bonus, but excluding params in favor of strings goes against nature of PS.

    Michael Fal Michael Fal on Support MultiSubnetFailover for all cmdlets when connecting to an AG listener

    I guess my take on it is I'd rather just have the connection string param instead of individual arguments, but I don't feel strongly about it.

    Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2551673/add-a-parameter-to-invoke-sqlcmd-to-accept-a-connection-string

    Chrissy LeMaire Chrissy LeMaire on Support MultiSubnetFailover for all cmdlets when connecting to an AG listener

    @mikefal So you are saying you can update the Connect to say in addition to a .NET connection string, also support each part of a string as a param? Like -MultiSubnetFailover? If so, please update and add in desc, then we can add a Blue label.

    Michael Fal Michael Fal on Support MultiSubnetFailover for all cmdlets when connecting to an AG listener

    I've got a connect item that can handle this. My suggestion was that Invoke-SqlCmd should accept a parameter for a .Net connection string. Should be a fairly simple change and support this along with other connection settings.

  4. I believe the SqlServer module has race conditions as the Test cmdlets will throw errors when more than one is running concurrently against the same object; I've recorded this below for the Availability Replica and Database Replica State tests; though it may also occur on the Availability Group test I simply haven't seen it.

    I came across this because I verify the health of our environment by piping a few dozen AG nodes into a framework which splits the workload across concurrently running jobs. Each job enumerates the availability groups on that node and then runs all of the tests against whatever node is determined to be the primary for each - this is because the tests often can only be used on primaries, and it's essential to test the primary in order to determine whether the secondary can be considered healthy or not.

    As a result the same tests can and do run on the primary twice at the same time, triggering the failure.

    Here is one recorded stack trace

     PSMessageDetails : Exception : Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered in callback while evaluating policy 'AlwaysOnDbrDataSynchronizationState'. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializationException: Serialization operation on EvaluationDetail[@ID='1'] has failed. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Management.Sdk.Sfc.Metadata.SfcMetadataDiscovery.get_ReadOnlyKeys() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcUtility.GetSmlUri(Urn urn, Type instanceType, Boolean useCache) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.WriteInternal(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.WriteAllInstances() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter xmlWriter) at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationResultsWriter.WriteEvaluationHistory(Ev aluationHistory history) at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluationDetails.CreateXmlString(Evaluati onHistory history) at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluationDetails..ctor(Policy policy, TargetProcessedEventArgs targetProcessedArgs, EvaluationHistory history) at Microsoft.SqlServer.Management.PowerShell.Hadr.AlwaysOnTestCommand`1.OnEvaluationFinished( Object sender, PolicyEvaluationFinishedEventArgs e) --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Delegate.DynamicInvokeImpl(Object[] args) at Microsoft.SqlServer.Management.Dmf.Policy.FireEvent(Delegate del, Object[] args) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Dmf.Policy.FireEvent(Delegate del, Object[] args) at Microsoft.SqlServer.Management.Dmf.Policy.Evaluate(AdHocPolicyEvaluationMode evaluationMode, Object[] targetObjects) at Microsoft.SqlServer.Management.HadrDMF.AlwaysOnPolicyStore.Evaluate(KeyValuePair`2 policy, Type targetType, SqlSmoObject[] evaluatedObjects) at Microsoft.SqlServer.Management.HadrDMF.AlwaysOnPolicyStore.Evaluate(SqlSmoObject[] objects) at Microsoft.SqlServer.Management.PowerShell.Hadr.AlwaysOnTestCommand`1.ProcessTarget(T target, SmoRecordContext context) at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord() at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ProcessRecord() at System.Management.Automation.CommandProcessor.ProcessRecord() TargetObject : CategoryInfo : NotSpecified: (:) [Test-SqlDatabaseReplicaState], PolicyEvaluationException FullyQualifiedErrorId : Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException,Microsoft.SqlServer.Management.Powe rShell.Hadr.TestSqlDatabaseReplicaStateCommand ErrorDetails : InvocationInfo : System.Management.Automation.InvocationInfo

    Another which is the same but in a different Test cmdlet:

     Message : Exception has been thrown by the target of an invocation. Data : {} InnerException : Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializationException: Serialization operation on EvaluationHistory[@ID='1'] has failed. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Management.Sdk.Sfc.Metadata.SfcMetadataDiscovery.get_ReadOnlyKeys() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcUtility.GetSmlUri(Urn urn, Type instanceType, Boolean useCache) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter instanceWriter, Object instance, Dictionary`2 namespaces) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.WriteAllInstances() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcSerializer.Write(XmlWriter xmlWriter) at Microsoft.SqlServer.Management.Dmf.PolicyEvaluationResultsWriter.WriteEvaluationHistory(Evaluatio nHistory history) at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluationDetails.CreateXmlString(EvaluationHistory history) at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluationDetails..ctor(Policy policy, TargetProcessedEventArgs targetProcessedArgs, EvaluationHistory history) at Microsoft.SqlServer.Management.PowerShell.Hadr.AlwaysOnTestCommand`1.OnEvaluationFinished(Object sender, PolicyEvaluationFinishedEventArgs e) TargetSite : System.Object InvokeMethod(System.Object, System.Object[], System.Signature, Boolean) StackTrace : at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Delegate.DynamicInvokeImpl(Object[] args) at Microsoft.SqlServer.Management.Dmf.Policy.FireEvent(Delegate del, Object[] args) HelpLink : Source : mscorlib HResult : -2146232828

    This is another weird one:

     Message : Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index ActualValue : ParamName : index Data : {} InnerException : TargetSite : Void Set(Int32, Boolean) StackTrace : at System.Collections.BitArray.Set(Int32 index, Boolean value) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.FillPropertyCollectionFromDataReader(SfcProper tyCollection properties, IDataReader reader) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcObjectFactory.Create(SfcInstance parent, IPropertyCollectionPopulator populator, SfcObjectState state) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CreateNewObjectFromRow(ISfcCollection childColl, IDataReader reader) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.InitObjectsFromEnumResultsRec(ISfcCollection childColl, IDataReader reader, Int32 columnIdx, Object[] parentRow) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.InitObjectsFromEnumResults(ISfcCollection childColl, IDataReader reader) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.InitChildLevel(ISfcCollection childColl) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcCollection`3.InitializeChildCollection(Boolean refresh) at Microsoft.SqlServer.Management.Sdk.Sfc.SfcCollection`3.EnsureCollectionInitialized() at Microsoft.SqlServer.Management.Sdk.Sfc.SfcCollatedDictionaryCollection`3.GetEnumerator() at Microsoft.SqlServer.Management.HadrDMF.AlwaysOnPolicyStore.LoadPolicies(List`1 categories) at Microsoft.SqlServer.Management.HadrDMF.AlwaysOnPolicyStore.LoadAvailaiblityDatabasePolicies() at Microsoft.SqlServer.Management.PowerShell.Hadr.AlwaysOnTestCommand`1.ProcessTarget(T target, SmoRecordContext context) at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord() at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ProcessRecord() at System.Management.Automation.CommandProcessor.ProcessRecord() HelpLink : Source : mscorlib HResult : -2146233086

Desired State Config (DSC)

  1. Connect Item Raised here. Please go and vote and add comments

    https://connect.microsoft.com/SQLServer/feedback/details/2708334

    Comments

    Johan Ljunggren on Merge of xSQLServer & xSQLPS into 1 module and gracefully retire both

    xSQLServer is now renamed to SqlServerDsc. xSQLPS is deprecated. I think this card can be closed?

    Johan Ljunggren on Merge of xSQLServer & xSQLPS into 1 module and gracefully retire both

    We need to complete integration tests to be able to move xSQLServer towards HQRM (and then hopefully get it to be renamed to SQLServerDsc depending)

    Rob Sewell Rob Sewell on Merge of xSQLServer & xSQLPS into 1 module and gracefully retire both

    As soon as @ryanyates6 has confirmed wording I will file a connect for this tonight or tomorrow

    Aaron Nelson Aaron Nelson on Merge of xSQLServer & xSQLPS into 1 module and gracefully retire both

    @ryanyates6 or @robsewell1 Can one of you file a Connect item for this card and post the link to the Connect item in the Description when you're done?

    ArieH on Merge of xSQLServer & xSQLPS into 1 module and gracefully retire both

    Make sure you move some settings outside of the resource or make sure you have a Test for them. At the moment the Test only looks for services and depending on them decides what features of the SQL are not in the desired state.

    Should leave settings that you do not intend to test outside of the resource itself.

    Labels

  2. Should DSC enforce "Best Practices" ?
    Or should this be done with a Pester Test?
    What about Applyandautocorrect? Does that add risk?

    Please add to your thoughts, knowledge and experience to this discussion to help make DSC resources work in the right way

    It started on twitter - read this thread
    https://twitter.com/cl/status/739168026747142144

    Comments

    Aaron Nelson Aaron Nelson on Discussion point to lead DSC resource creation - Should DSC enforce "Best Practices" ?

    "Enforce"? Absolutely not. "Offer", "Default to"? Yes, that would be great! SQL Server 2016 includes a bunch of changes to remove things like Trace Flags and turns on those features by default. Any PowerShell cmdlets should certainly default to a best practice where applicable. My only quibble is with the use of the word "enforce".

    Best Practices are not perfect practices, otherwise they would be called perfect practices. I'm just trying to point out that people must be allowed to do non-standard/non-best-practice things if they want to specify that.

    Rob Sewell Rob Sewell on Discussion point to lead DSC resource creation - Should DSC enforce "Best Practices" ?

    There are a lot of good valid points in the twitter thread - link above

    Chrissy LeMaire Chrissy LeMaire on Discussion point to lead DSC resource creation - Should DSC enforce "Best Practices" ?

    I'm really leaning to either the strong push for Pester (strong as in by default?) along with DSC , but in absence of that, then Best Practice by Default within DSC. We all know what happens if you let things slide. They slide.

  3. Comments

    Johan Ljunggren on New-AvailabilityGroup Resource

    This exist in xSQLServer resource module now. Anything else that should be added?

  4. Comments

    Johan Ljunggren on Add-DatabasesToAvailabilityGroup Resource

    This exist in the xSQLServer resource module. Anything else hat should be added?

  5. The xSQLServerSetup resource is very poor and needs replacing.

    Comments

    Johan Ljunggren on SQLServerInstall

    xSQLServerSetup in xSQLServer resource module has been improved during the last half year. Anything else that should improve?

    Rob Sewell Rob Sewell on SQLServerInstall

    Tor mentioned the lack of error handling on the Slack channel

    Rob Sewell Rob Sewell on New-SqlInstall

    @sqlvariant I think it would

    Aaron Nelson Aaron Nelson on New-SQLInstall

    Would this be different than https://trello.com/c/6A4avSPQ ?

Registered Servers & Central Management Server

  1. Comments

    Chrissy LeMaire Chrissy LeMaire on Export-SqlRegisteredServer

    @mrpaulandrew it's true, but it requires the GUI and often fails for me w/o modifying the XML to remove the destination server name :|

    Paul Andrew Paul Andrew on Export-SqlRegisteredServer

    FYI. You can already do this from SSMS giving you can XML file to import else where.

  2. Comments

    Chrissy LeMaire Chrissy LeMaire on Get-SqlRegisteredServer

    @peturthorgretarsson hey petur, did you mean to post on dbatools?

    Petur Thor Gretarsson on Get-SqlRegisteredServer

    Is there a way to get the distinct list of server names using Get-SqlRegisteredServer?

    Chrissy LeMaire Chrissy LeMaire on Get-SqlRegisteredServer

    @mrpaulandrew Ignore my earlier comment. I thought i was on the dbatools board!

    Paul Andrew Paul Andrew on Get-SqlRegisteredServer

    Would be nice to have this in PowerShell, currently have to execute this query as CommandText: SELECT DISTINCT [server_name] FROM [dbo].[sysmanagement_shared_registered_servers_internal]

    Labels

  3. Import/Export SSMS settings via Script

  4. For Registered Servers. Should this also encompass Central Management Server or should that be another cmdlet?

    This cmdlet should accept a -Color parameter

    Comments

    Chrissy LeMaire Chrissy LeMaire on Create Registered Server groups via Script

    Hey @robvolk, love the suggestion! Moving this to the board dedicated to PowerShell.

    Also you can check out my CMS SMO recipes page in the interim for this functionality.

    Oh, wait. That's CMS, not RS. Hmm, I'll look into adding something for local registered servers.

  6. When I do this I see my CMS in the gci output

     Import-Module SqlPs Get-ChildItem SQLSERVER:\SQLRegistration\Central Management Server

    When I do this I DON'T see my CMS in the gci output

     Import-Module SqlServer Get-ChildItem SQLSERVER:\SQLRegistration\Central Management Server

Management Studio (SSMS)

  1. Adding the ability to execute commands via SQLPS, similar to SQLCMD mode.

    Comments

    Ben Miller Ben Miller on Add "sqlps" mode to SSMS

    That would be very cool.

    Aaron Nelson Aaron Nelson on Add "sqlps" mode to SSMS

    Should they also add this to SSDT?
    (like how SSDT Database Projects uses SQLCMD)

  2. It would be nice to have a decent PowerShell editor in SSMS.

    https://connect.microsoft.com/SQLServer/feedback/details/2594117

    Comments

    Greg Wojan Greg Wojan on Add a PowerShell Editor to SSMS

    Actually I think it would be better if SSMS allowed extensions that way we could use already available extensions like PowerShell Tools for Visual Studio and for the masochists among us VsVim. :sunglasses:

    twwilliams twwilliams on Add a PowerShell Editor to SSMS

    This would be a perfect place to take advantage of the new PowerShell Editor Services: https://github.com/PowerShell/PowerShellEditorServices

    Labels

  3. From My previous interview question:

    "Nearly every action inside of SSMS that allows you to "Script Action to New Query Window" should also have an option to "Script Action to PowerShell". Something like this will greatly help the people getting started with PowerShell to find a task in SQL Server they want to automate and have a ready-made example of how to script that in PowerShell. Will this type of functionality be added soon? If so, hopefully a lot of those Actions eventually become new cmdlets or functions in the SQLPS module, and not just SMO code."

    Based on Ken's answer, we need to figure out what dialogs are the top priority to ask them to do this to. Please comment below with which ones are your top priority.

    QUESTION:
    Where can they do with the existing cmdlets?
    The first one that comes to my mind is Backup-SqlDatabase / Restore-SqlDatabase.

    Maybe some of the Availability Group cmdlets?

    Start-SqlInstance / Stop-SqlInstance seems too basic, am I wrong?

    Filed:
    https://connect.microsoft.com/SQLServer/feedback/details/2676447

    Comments

    Aaron Nelson Aaron Nelson on Add "Script Action to New Window AS PowerShell"

    I reviewed the current list of available cmdlets and the #1 place I could see them starting to do this is in the Database Backup/Restore popup/dialogue in SSMS.

    Does anyone have a better place to start adding "Script Action to New Query Window"?

    Again, my gut tells me some action which can be done with an existing cmdlet would be the easier for #1. They can add this to more actions later after more cmdlets are added.

    Ben Miller Ben Miller on Add "Script Action to New Window AS PowerShell"

    I have a connect item out there https://connect.microsoft.com/SQLServer/feedback/details/642059/add-scripting-options-for-smo-and-powershell-in-ssms-tools marked as Won't fix. But maybe now they will look at it. I am not sure how to reopen one of them but you should certainly open a new connect item if you haven't already. This is common in VMM and exchange stuff. I like the idea still to get this to happen.

    Labels

  4. Comments

    Nic Cain Nic Cain on Add a button in the toolbar to enable/disable word wrap

    @sqlvariant no, this is independent of that. Right now you have to go deep into the options menu for something that should be a default button.

  5. Comments

    Klaas Vandenberghe Klaas Vandenberghe on If you have other ideas for improving SSMS (outside of just PowerShell) then please join us on our other board http://sqlps.io/ssms

    can backward compatibility be improved?
    in SSMS 16.4.1 I still get "class not registered" when I try to connect to a SSIS 2008R2.

    Aaron Nelson Aaron Nelson on If you have other ideas for improving SSMS (outside of just PowerShell) then please join us on our other board http://sqlps.io/ssms

    If you have other ideas for improving SSMS (outside of just PowerShell) then please join us on our other board http://sqlps.io/ssms

  6. Comments

    Matteo Matteo on SQLRegistration:\Central Management Server Group - Why is it case sensitive?

    Hey Drew - I looked into this one. As far as I can tell, this is "by design". See my comment in the Connect bug. Also look at the attached screenshot, which for some reason I could not attach to the Connect bug...

    Let me know what you think...

    Drew Furgiuele Drew Furgiuele on SQLRegistration:\Central Management Server Group - Why is it case sensitive?

    I submitted this as a connect item already, but if you try to use the SQLRegistration path of the provider, central management server names appear to be case sensitive. Why? See screenshot for details.

    https://connect.microsoft.com/SQLServer/feedback/details/2402919/sqlps-provider-sqlregistration-for-central-management-servers-is-case-sensitive

    Labels

  7. In the table context menu, it would be nice if there were a MERGE option for scripting table data from SSMS, updating records in-place, deleting extra records, and inserting missing ones, to restore the table's data to the state when it was scripted!

SQLPS Active Connect Bugs

  2. "SQL Server SQLPS PowerShell module fails connection to SQL 2012 instance"
    Full item can be found here: https://connect.microsoft.com/SQLServer/feedback/details/1138754/

    Comments

    Bob Klimes Bob Klimes on SQL Server SQLPS PowerShell module fails connection to SQL 2012 instance

    this is fixed in the most recent release of the sqlserver module in the psgallery

    Cody Konior Cody Konior on SQL Server SQLPS PowerShell module fails connection to SQL 2012 instance

    Quick recap of the issue: There are SMO DLLs (also used by SQLPS) which use WMI to get some information from SQL Server (locally and remotely). This information is mostly anything in SQL Server Configuration Manager including getting back the services, adding trace flags, and pulling back endpoint, alias, and IP information.

    In SQL 2014 it was broken so that it could no longer see SQL 2012. In SQL 2016 it's broken further so that it cannot see SQL 2012 or SQL 2014. It's responsible for the annoying extraneous SQLPS WMI errors you sometimes see when connecting to a server. The code that does this has been decompiled and provided to Microsoft, it's an obvious bug, easily fixed, and they just haven't done it.

    Without it we cannot use SQLPS or SMO to reliably gather or alter this information from one server on multiple remote servers. Vote for this and help get it fixed. It needs attention.

  4. https://connect.microsoft.com/SQLServer/feedback/details/3104338

    Comments

    Matteo Matteo on You can't update help files with Update-Help for SqlServer. Connect item created

    As I wrote in the connect item, this was a bit my fault for not understanding exactly what the "Doc" folks told me. I'm looking into removing the offending line from the manifest right now.

    Matteo Matteo on You can't update help files with Update-Help for SqlServer. Connect item created

    Let me follow up with the "Doc" folks. I also need them to publish the latest documentation.

    By the way, you do not need to update. The current module installed with SSMS has the latest documentation.

    Thanks for reporting the issue, @mikefal

    Labels

Holding Pattern

  2. Please see discussion below. This card will be removed on May 1.

    Comments

    Chrissy LeMaire Chrissy LeMaire on Azure - removal from board on 5/1.

    This conversation should probably be quoted and added to Process of Delivery. I do like the idea of a holding pattern list tho.

    Chrissy LeMaire Chrissy LeMaire on Azure - removal from board on 5/1.

    @alzdba, good idea! @sqlvariant can you please rename this card and put somewhere? My brain isn't working.

    alzdba alzdba on Azure - removal from board on 5/1.

    For one: it is great to get confirmed that on-premise and cloud code bases are separate. That doesn't only confirm the difference in both ms project lifecycles, but als kind of explains why it is an add-on to SQLPS ( I really dislike it is implemented as an add-on ).

    Please put a reference for this decision on the board, or maybe split the board and put a cross reference on both on-premise and could version.

    Maximo Trinidad Maximo Trinidad on Azure - removal from board on 5/1.

    Awesome! Let's do this. :)

    Chrissy LeMaire Chrissy LeMaire on Azure

    @sqltoolsguy_msft Thanks Ken! This card will be removed within 48 hours after everyone has seen your response. @maximotrinidad, please see :)

    Ken Van Hyning on Azure

    Currently they are different code bases and run by different teams. The stacks are almost entirely different. We have discussed that we should eventually rationalize these two but for now my team's focus is to get SQLPS (targeted primarily at on-prem) whipped into shape. So I would encourage that to be the primary focus.

    If there is growing interest in SQL Azure scenarios and such I can go work with that team and see what it will take to rationalize the two. It's not on my short-term radar though.

    Chrissy LeMaire Chrissy LeMaire on Azure

    I think this is something to consider. I lean to one board for all, but am happy to reorganize the boards. @sqltoolsguy_msft, Max also mentioned this in DSC. If you would like to give direction, we can act on it. Otherwise, I'll encourage all others to continue the discussion.

    Maximo Trinidad Maximo Trinidad on Azure

    Remember that as soon as you open SQLPS command prompt then you can just type the azure command the will execute (as long as you had it installed). Its a separate group of cmdlets loaded differently. The SQL PowerShell prompt could execute them. Now, if you want to create more azure cmdlets/functions, then you can try first route them to the Azure group. By the way, whatever we create under SQL PowerShell should handle Azure Database transparently specially when using SMO. At least that is my take!

    Chrissy LeMaire Chrissy LeMaire on Azure

    @maximotrinidad I very rarely use Azure, but SSMS has some support for Azure and when I added this, I was sorta modeling SQLPS after SSMS. Does the Azure module do the stuff in SSMS? (again, never really use it, genuine question.)

    Maximo Trinidad Maximo Trinidad on Azure

    Hum! I don't think Azure should be part of this SQLPS project. They are already handle by a different product group and set of cmdlets.

Home | Templates | Pricing | Apps | Jobs | Blog | @trello | Trello API | About | Help | Legal | Privacy | Integrations | Contact us | Terms