[WAT] Fun length issue with REPLACE in SSMS "Results to"

Just had this burn me, and don't remember having seen this issue before.

REPLACE changes the length of your columns, in Results to Text/Results to File.

Run this piece of code in SSMS. Query->Results To->Grid.

SELECT TOP 100 REPLACE(REPLACE(name,'&',';'),'/','#'), name FROM sysdatabases

Copy and paste on successive lines.  The same.
Now do the same thing, in Results to Text...
And now the same, Results to File...

Note that they're no longer the same length.  Seems the case at least since 2005.
The length of the header, which dictates the length (if doing fixed-width stuff) is twice as long.  That occurs even when the replaced characters are the same length.

Now let's see what our 2012++ friend, which gives us the metadata for a query, says.

SELECT * FROM sys.dm_exec_describe_first_result_set('
SELECT TOP 100 replace(replace(name,''&'','';''),''/'',''$'') as rname, name
FROM sysdatabases', NULL, 0)

Which gets you this fun nugget...



Hey look!  It went from nvarchar(128) (aka sysname) to nvarchar(4000).  Interesting!

Maybe this is normal/expected behavior.  But it made life difficult today, and experience is gained from doing it wrong, so here's your experience for the day.

[Powershell] Running sp_blitz against multiple servers in parallel, saved to table

(update 2013/06/13: I've superceded this with a new framework that will run almost any piece of code, powershell or sql, saving the results to a table, and running with multiple threads. Please give it a look!
http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html )

(update 2013/02/09: for some reason, running with only 6 threads doesn't bring back all the servers.  Using 20, however, does.  Odd, and not expected, and obviously I need a better fix)

(update 2013/02/08: if results from sp_blitz aren't saved for a particular server, run the invoke-sqlcmd2 command for sp_blitz against just that server, manually. For sp_blitz we've found a couple bugs and are sending them to Brent Ozar to fix.  Right now, if there are any errors thrown, even if there are results, then invoke-sqlcmd2 doesn't work right.)


So, I recently found out WHY sp_blitz is awesome, and why "30 second takeover" is a horrid subtitle for it. (I always thought that it meant "break into a SQL server in 30 seconds", so I never went - NOPE).

Anyhow, sp_blitz itself is handy as heck - gets a list of common problems that a particular server has, when it gets handed to you and you basically "takeover" and become its DBA in a matter of 30 seconds.

So, the next thing I wanted to do was save the results to a centralized table.  Actually raced a coworker to do it - he did one in SSIS, naturally I wanted it in Powershell.  Later we plan to do "server thunderdome" and see whose runs fastest.

NOTE: this is almost exactly the same code as in my earlier post http://thebakingdba.blogspot.com/2012/12/powershell-run-query-against-multiple.html . The biggest change is the addition of a ServerName field (which was a lot harder to figure out than you'd think, so many thanks to Chad Miller for the fix, and Graimer for a shorter version).  Given it wasn't a simple thing (for me) to add, and what I'm doing with it, I figure it'd be worth another post.

Future changes: I think I'm going to modify it so it looks at a folder, running each script from that folder and saving it to a table named for that script.  Also getting it to return errors; between invoke-sqlcmd2 and the scriptblock, it basically eats any errors and you never see them.  Need to get that working better.

So, first, create a table to hold the data:

CREATE TABLE [dbo].[BlitzResults](
[Priority] [tinyint] NULL,
[FindingsGroup] [varchar](50) NULL,
[Finding] [varchar](200) NULL,
[DatabaseName] [varchar](50) NULL,
[URL] [varchar](200) NULL,
[Details] [nvarchar](4000) NULL,
[QueryPlan] [xml] NULL,
[QueryPlanFiltered] [nvarchar](max) NULL,
[CheckID] [int] NULL,
[ServerName] [varchar](50) NULL
)



Make sure to save the invoke-sqlcmd.ps1 and the write-datatable.ps1 scripts to a folder (c:\sql_tools, change it however you want).

Now create this script:

#Run SQL query against multiple servers in parallel, saving results to a central table
# 1.00 2012/12/13 mdb / TBD.  Initial release
# 1.10 2012/12/13 mdb / TBD.  Adding throttling scheduler
# 1.11 2012/12/13 mdb adding comment on WHY you want the wait-job
# 1.20 2012/02/07 mdb adding a ServerName field to the datatable. Now we can do sp_blitz w/o modifying it!
# Code cribbed and lessons learned courtesy of: Hey Scripting Guy,
# Aaron Bertrand (the bit with $args[0], his "sad panda face" post)
# Kendra Little
# Throttling scheduler is from "start-automating" Stackoverflow.
# Additional Servername field from Chad Miller and Graimer (stackoverflow)
# Errors are mine, not theirs.
# Please keep this header and let me know how it works. thebakingdba.blogspot.com
# Prerequisite: invoke-sqlcmd2 and write-datatable, courtesy of Hey Scripting Guy & Chad Miller
# Long header courtesy of .... um, me.
clear
. C:\sql_tools\invoke-sqlcmd2.ps1;
#get list of servers that meet our criteria; our code will run against these
$serverlist = invoke-sqlcmd2 -serverinstance "ftw-test-08" `
-query "SELECT server FROM yourlistofservershere WHERE active = 1 and version >=9 order by server"
foreach ($server in $serverlist)
{
    #job running code; allows us to multithread.  -le 8 means 8 jobs run at once. As one drains, the next picks up
    $jobsrunning = @(Get-Job | Where-Object { $_.JobStateInfo.State -eq 'Running' })
    if ($jobsrunning.Count -le 8)
    {
        start-job -argumentlist $server.server -scriptblock `
        {
        #a scriptblock is a wholly separate 'environment'; have to reinvoke functions and reintroduce variables
        . C:\sql_tools\invoke-sqlcmd2.ps1;
        . C:\sql_tools\write-datatable.ps1;
        $server2 = $args[0]

        $quer = invoke-sqlcmd2 -serverinstance $server2 -database "master" `
            -query "exec master.dbo.sp_blitz" -As 'DataTable'

        $quer.Columns.Add("ServerName")

        $quer | %{$_.ServerName = $server2}

        Write-DataTable -ServerInstance "repositoryserver" -Database "dba_stuff" -TableName "BlitzResults" -Data $quer
        }
    }
    else
    {
        $jobsrunning | Wait-Job -Any  #as soon as any job finishes, push the next up
    }
}

#Now that we're finished, cleanup and get rid of any errant jobs  
get-job | wait-job -timeout 120         #wait 120 seconds or until all jobs are done, whichever comes first

get-job|Remove-Job -force              #cleanup and remove the jobs

[statistics] Find last update time for all statistics for a table

(tbd 2015/09/09 - added truncate table to the loop.  Doh!)

Statistics matter. So you want to find when they were last updated.  You usually see this one:


SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('pad_tx')

Which works - for all statistics tied to an index.  But SQL Server has more than just those: there are also the _WA_Sys stats, which begin with that phrase and are created by the engine.  (IIRC, you can also see DTA stats, built by the Database Tuning Advisor) 

In theory you shouldn't have many of them, because they're only generated if you're querying a field that isn't part of an index.  Theoretically you should clean them out (that may be another post), since a stat could be in there twice - once for an index and once because you queried the table (creating the stat) before you created the index.

This should list all of them.  

DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @statname NVARCHAR(128)

declare @listofstats TABLE (id int identity, NAME sysname)

INSERT INTO @listofstats (name) 

SELECT name FROM sys.stats WHERE object_id = object_id('yourtablenamehere')

if object_id('tempdb..#stats_info') is not null

    DROP TABLE #stats_info

CREATE TABLE #stats_info (updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

if object_id('tempdb..#stats_info2') is not null

    DROP TABLE #stats_info2

CREATE TABLE #stats_info2 (stat_name VARCHAR(128), updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))

SELECT @MIN = MIN(ID), @max = MAX(id) FROM @LISTOFSTATS

DBCC TRACEON (2388)

WHILE @min <= @max

begin

TRUNCATE TABLE #stats_info
 SELECT @sql = NULL, @statname = null

SELECT @statname = NAME FROM @listofstats WHERE id = @min

SELECT @sql = N'DBCC SHOW_STATISTICS (''yourdatabasenamehere..yourtablenamehere'','''+ @statname +''')'

INSERT INTO #stats_info 

EXEC master..sp_executesql @sql

INSERT INTO #stats_info2 SELECT @statname, * FROM #stats_info

SET @min = @min + 1

end

DBCC TRACEOFF (2388)

SELECT #stats_info2.stat_name, #stats_info2.updated FROM #stats_info2 INNER JOIN (SELECT stat_name, MAX(updated) AS max_date FROM #stats_info2 GROUP BY stat_name)a 

ON #stats_info2.stat_name = a.stat_name AND #stats_info2.[updated] = a.max_date

ORDER BY #stats_info2.stat_name

[ETL] Importing UNIX files

Coworker had a problem importing a data file - one column per line, but was running into problems and had to invoke Code Page 65001 in his SSIS script. Took forever to fix, and forever to process. Looked at the file - UNIX.

So, the easy T-SQL way to do it:

CREATE TABLE deleteme (resultant VARCHAR(MAX))



BULK INSERT deleteme

   FROM '\\server\path\file.rpt'

   WITH 

      (

         ROWTERMINATOR = '0x0a'

      )