Getting query text from SSMS into a CSV with PowerShell (SQLServerCentral 3/23/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Getting query text from SSMS into a CSV with PowerShell]( - [Thinking about microservices? You need DevOps first]( - [Scripting the Description of Database Tables Using Extended Properties]( - [Allowing Azure Service to Connect to your Azure SQL server.]( (From the SQLServerCentral Blogs) - [T-SQL Tuesday #100: What’s New With Scalability Groups in SQL Server 2026]( (From the SQLServerCentral Blogs) The Voice of the DBA Ad Hoc Logs A long time ago I started working in a team of people as a general systems administrator. I worked in a team of six people managing a large, 1000+ node network with a number of servers. In my first exposure to SQL Server, we had a very unstable application that forced us to provide 24x7 support. With a couple of contractors, we had to ensure someone was on-site all the time, often working with Microsoft and our corporate developers to stabilize the applications. We were constantly trying new techniques to fix our application, and with staff stretched thin, we struggled to understand what might have happened in the previous 12 hours when we reported for work. At the time, I suggested leaving a text document on each server's desktop, updated with a note for each change. That worked well and I brought that technique with me to future positions. In another job, we constantly remotely connected to systems, and having a standard file on the desktop was helpful. As we became more security conscious, and stopped using shared logins, we moved our logging to Exchange public folders. Every action taken by an admin needed to be cut and pasted into a new post. That wasn't a perfect system, but we built habits over time and we had an audit trail that helped us in understanding the changes we've made and assisted in troubleshooting. Today I'm curious. I want to ask the question about the data corrections, those quick changes, those fixes that get production working. Do you log everything? Is there some system in place to ensure you know what's happening? I've been wondering about this and thinking hard as the date for GDPR enforcement approaches. One of the items that I'd glean from the law's text is that any change to correct data, any quick fix made, needs to have an audit trail. We need to prove that we know who, when, and why this change occurred. This is especially important if a data subject requested some correction. You'll need to prove you actually performed the action. I've never worked anywhere that some admin (including myself) completely avoided connecting to a production machine and making some change. Sometimes we've had great auditing, often not, but ad hoc fixes and changes, especially in the heat of an issue, are a fact of life. I've learned to deal with it and try to build lightweight habits to help me capture those changes. Let me know today. How bulletproof is your auditing? Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 4.0MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( [SQL in the City]( Free data protection and privacy livestream Many of you will be aware that the new GDPR legislation comes into effect in May and, in light of this, Redgate recently hosted a livestream that included sessions to help you become best equipped to deal with the challenges GDPR brings to compliant database management. [Watch the recording]( Featured Contents  [] [Getting query text from SSMS into a CSV with PowerShell]( Shawn Melton from [SQLServerCentral.com]() You have times where you need to copy/paste something out of SSMS' grid view results pane that has a carriage return in it. Trying to copy/paste that data into Excel can be a headache and cause you to waste precious time reformatting. This just offers a tidbit of PowerShell code to help.[More »]( ---------------------------------------------------------------  [] [Thinking about microservices? You need DevOps first]( With the rise of new virtualization technologies using containers, people are starting to think more and more about using microservices in their organizations. They’ve been getting a lot of attention and companies like Netflix, Amazon, Uber, and Spotify have attributed some of their recent success to using them. [More »]( ---------------------------------------------------------------  [] [Scripting the Description of Database Tables Using Extended Properties]( Additional Articles from [SimpleTalk]() Stored procedures, for example, are very easy to document. The comment block at the beginning stays with the code and a CREATE or ALTER script contains everything to reproduce the proc. SQL Server tables, however, are more difficult to document. You can use Extended Properties to document columns and constraints, but working with Extended Properties is difficult at best. Phil Factor demonstrates ways to easily add Extended Properties to your build scripts.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Allowing Azure Service to Connect to your Azure SQL server.]( David Postlethwaite from [SQLServerCentral Blogs]( Allowing Azure Service to Connect to your Azure SQL server. In this video David will look at the impact of allowing...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [T-SQL Tuesday #100: What’s New With Scalability Groups in SQL Server 2026]( Robert Davis from [SQLServerCentral Blogs]( Welcome to T-SQL Tuesday #100. In a community filled with ADD people like the SQL community (myself included), who would...[More »]( Question of the Day Today's Question (by Steve Jones): I had someone delete all the data in a table of my Sales database. This database has CDC enabled inside of it. If I restore a full backup of this database to the same instance with the same name, what happens with CDC? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: Change Data Capture (CDC). We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Securing SQL Server: DBAs Defending the Database]( tag=redgatsof-20 linkCode=as2 camp=1789 creative=9325 creativeASIN=1484222644) Protect your data from attack by using SQL Server technologies to implement a defense-in-depth strategy, performing threat analysis, and encrypting sensitive data as a last line of defense against compromise. The multi-layered approach in this book helps ensure that a single breach doesn't lead to loss or compromise of your data that is confidential and important to the business. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): Which counters do you capture on your on-premises SQL instance to help you estimate your DTU usage in Azure SQL Database? Answer: % Processor Time, Log Bytes Flushed/sec, Disk Writes/sec, and Disk Reads/sec Explanation: The Azure DTU Calculator asks you to capture these metrics for upload: - Processor - % Processor Time - Logical Disk - Disk Reads/sec - Logical Disk - Disk Writes/sec - Database - Log Bytes Flushed/sec ?Ref: Azure SQL Database DTU Calculator - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Query for Multiple DB]( - I need to execute this query for Multiple DB to check Index and store in a table, below query only... [Dynamic Column]( - I am trying to figure out how to take the following and dynamically generate a new column for every person... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [SQL Server Master Data Services not available in SQL Server 2016 Standard edition with SP1?]( - Hello, After converting my SQL Server Instance from 2012 to SQL Server 2016 (with SP1): Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0... [unable to shrink log file]( - Hi, log_reuse_wait_desc is OLDEST_PAGE for a database in sys.databases. Please suggest how to shrink the log file. Thanks [AlwaysOn Availability Group]( - I have always-on availability group on two nodes cluster with read intent routing. When I stop availability group role in WSFC at... [SSAS]( - Hi, Can you please provide What are the hardware ,software and licence cost details to build SSAS server. Thanks --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Number of Trades]( - I need to write a query to extract the region of the trader, his name & the number of trades he/she... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [SEQUENCE object or NEWSEQUENTIALID()]( - I'm studying for my 70-461 exam and have received a corker of a question in my practice test: "You develop a... [Best way of concatenating values and returning an empty for null values]( - I've taken an online practice exam for my 70-461 exam and I cannot work out why concatenating using '+' is better... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Using dynamic sql, how can I rearrange columns]( - I have a stored procedure that uses dynamic sql to create a pivot table. I need to rearrange the columns like... [Setting up SQL Server 2012 AlwaysOn, can't get error msgs to clear]( - I'm trying to set up an SQL 2012 Availability Group between 'ServerA' and 'ServerB' On the source server (ServerA) I get... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [How to write this query?]( - Hi, I have this dataset: create --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [how to modify another jobs without sa?]( - Hello, is any way how to do rights for user, and he will be possible modify another than only his jobs... [Transactional Replication with one or multiple publications]( - Hi, I have a existing transactional replication configured and running: one publication on server A and one subscriber on server B.... [Having error backing up database]( - we have a BIG database (over 2T) was failing on a drive which had a data (tables) filegroup on it, moved... [Rebuilding Indexes very often is good?]( - Hi Friends, I was about to work on the production server all day last week. I was doing ETL process. Every... [Can you create an index on a system table?]( - I've been looking into Change Data Capture and it doesn't even look like a Primary Key gets created for the... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [History Snapshot Reports - Where are they stored?]( - I am wanting to check that we are backing up sufficient data to restore SQL and have been able to... [Adding a detail row when none exist]( - I initially thought I would not need detail rows in my VS 2008 BIDS report, and deleted them while keeping... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [SSISDB in SQL 2016]( - Team, I'm trying to figure out if I can import packages in SSISDB catalogue and run bat script Is this still possible... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com