Get RowCount from a Dynamically Executed Statement

 

--  Get RowCount from a Dynamically Executed Statement
 
       DECLARE 
		  @Period VARCHAR (8) = '' 
    ,     @DEDFYXXRecords BIGINT = 0 
    ,     @EXECFYXX NVARCHAR (MAX) = ''
    ,     @RowCount BIGINT = 0;  
  
    SELECT @EXECFYXX = ' INSERT Data( ' 
    +    ' Period ' 
    +    ',   CostCenter '     
    +    ') ' 
    +    'SELECT ' 
    +    ' FYXX.Period ' 
    +    ',   FYXX.CostCenter '     
    +    'FROM DataFY'
    +    LEFT (@Period,    2) 
    +    ' FYXX ' 
    +    ' JOIN DataB da ' 
    +    '     ON FYXX.Account = da.Acct ' 
    +    ' WHERE da.GroupID = 99 '
    +    '    AND    FYXX.Period = '''
    +    @Period 
    +    '''; SELECT @RowCount = @@ROWCOUNT; ';
 
    -- EXEC sp_executesql @EXECFYXX, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT
 
    SELECT @DEDFYXXRecords = @RowCount
    
    SELECT @DEDFYXXRecords 

Tool for Scripting View Generation

 

-- Tool for Scripting View Generation
 
       -- Given two databases, source and dest, this script will generate a READ 
       -- ONLY view in dest for each table or view in source. 
 
       -- http://joeskb.blogspot.com/2013/08/tool-for-scripting-view-generation.html

SSIS/SQLAgent - Enable User to See and Start Jobs

 

-- SSIS/SQLAgent - Enable User to See and Start Jobs
 
       -- To see, the user must be in the SQLAgentReaderRole or SQLAgentUserRole,      
       -- to start them the user must be in the SQLAgentOperaterRole. As these 
       -- only exist in MSDB then the user must also be a member there.

Find tables with foreign keys

 

-- Find tables with foreign keys 
-- Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server 
 
       SELECT 
              t.name TableWithForeignKey
       ,      fk.constraint_column_id FK_PartNo 
       ,      c.name ForeignKeyColumn 
       FROM   sys.foreign_key_columns fk
       JOIN   sys.tables t 
              ON     fk.parent_object_id = t.[object_id] 
       JOIN   sys.columns c 
              ON     fk.parent_object_id = c.[object_id] 
              AND fk.parent_column_id = c.column_id
       JOIN   sys.tables t1
              ON     fk.referenced_object_id = t1.[object_id]  
       ORDER BY
              TableWithForeignKey
       ,      FK_PartNo

What are the sizes of my db's?

 

-- What are the sizes of my db's?
 
       GO
       
       WITH fs
       AS (
              SELECT 
                     m.database_id
              ,      m.[type]
              ,      m.size * 8.0 / 1024 size
              FROM   sys.master_files m 
       )
       SELECT
              name
       ,      (SELECT SUM (size) FROM fs WHERE [type] = 0 AND fs.database_id = db.database_id) DataFileSizeMB
       ,      (SELECT SUM (size) FROM fs WHERE [type] = 1 AND fs.database_id = db.database_id) LogFileSizeMB
       FROM   sys.databases db
 

Delete old backups

 

-- Delete old backups 
 
--     EXECUTE master.dbo.xp_delete_file 0,N'\\Myapp4\SomeSQLBACKUP\MyCoSQL-TEST','N',N'2017-01-05T11:14:40',1

File tools

 

-- File tools 
 
       -- Find the BAK files:
       SELECT TOP 100 
              m.physical_device_name
       ,      b.backup_start_date
       ,      b.backup_finish_date
       ,      b.backup_size/1024.0 BackupSizeKB
       FROM   msdb.dbo.backupset b
       JOIN   msdb.dbo.backupmediafamily m 
              ON     b.media_set_id = m.media_set_id
       WHERE  /* database_name like '%Mysmco%' */ 
              m.physical_device_name LIKE '%bak%' 
       ORDER BY 
              b.backup_finish_date DESC
 
       -- Find the MDF's and LDF's:
       DECLARE @command varchar(1000)
       SELECT @command = 'SELECT * FROM sys.database_files'
       EXEC sp_MSforeachdb @command
 
       -- Or ... easier
       EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename,  SF.size FROM sys.sysfiles SF'
 
       -- Or ... even easier
       SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'
       FROM master.dbo.sysdatabases 
 

Force the db offline

 

-- Force the db offline 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE 
 
-- Same but rollback pending transactions 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET OFFLINE WITH ROLLBACK IMMEDIATE
 

Set to multi user

 

-- Set to multi user 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER;
 
-- Same but rollback pending transactions 
 
--     ALTER DATABASE [MyDB-DEV-NEW] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
 

Set to read only

 

-- ALTER DATABASE [MyDB-DEV-NEW] SET READ_ONLY;

Rollback any transactions and set db to single user

-- Rollback any transactions and set db to single user mode 

-- ALTER DATABASE [MyDB-DEV-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The restore operation halted prematurely.

-- The restore operation halted prematurely.

-- Repeat the original RESTORE statement specifying WITH RESTART.

-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak' WITH RESTART

Restore a full database backup

-- Restore a full database backup 

-- RESTORE DATABASE [MyDB-DEV] FROM DISK = 'C:\MyDB.bak'

Recover, NOT Restore, the db

-- Recover, NOT Restore, the db

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

-- RESTORE DATABASE [MyDB-DEV-NEW] WITH RECOVERY 

Most recent Myweb_user

 -- Most recent Myweb_user 

       SELECT TOP 33 wu.* 

       FROM   dbo.Myweb_user wu

       ORDER BY wu.MODDATE DESC 

What is the status of the backups and restores?

 -- What is the status of the backups and restores? 

 SELECT 

   r.session_id  

 , r.command

 , CONVERT(NUMERIC(6,2), r.percent_complete) [Percent Complete]

 , CONVERT(VARCHAR(20), DATEADD(ms,r.estimated_completion_time,GetDate()),20) [ETA Completion Time]

 , CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) [Elapsed Min]

 , CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) [ETA Min]

 , CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) [ETA Hours]

 , CONVERT(VARCHAR(1000)

 , (SELECT SUBSTRING( text,r.statement_start_offset/2, 

     CASE 

      WHEN r.statement_end_offset = -1 

       THEN 1000 

      ELSE (r.statement_end_offset-r.statement_start_offset)/2 

     END)

    FROM sys.dm_exec_sql_text(sql_handle)

   )

  )

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Kill the active database connections

 -- Kill the active database connections 

-- Note: Uncomment the 'exec' 

 

       DECLARE       @execSql VARCHAR (1000) = '' 

       ,      @databaseName VARCHAR (100) = ''  

       ,      @dbid INT = 0 

 

       SET @execSql = '' 

       SET @databaseName = 'MyDB-DNN-TMAR'  

       SET @dbid = db_id(@databaseName) 

 

       IF (ISNULL (@dbid,   '') = '') PRINT 'Bad DB Name' 

 

       SELECT @execSql = @execSql + 'kill ' + CONVERT (CHAR(10), sp.spid) + '; '  

       FROM   master.dbo.sysprocesses  sp 

       WHERE   dbid = @dbid 

              AND    sp.DBID <> 0  

              AND sp.spid <> @@spid  

       SELECT @execSql  

--     EXEC (@execSql)

 

       GO

 

-- kill 59

 

Renaming Databases

 -- Renaming Databases 

 

sp_who2 

 

-- Optional but should be done for MyDB-DEV-NEW

EXEC Utility.dbo.BACKUP_APP_INTERFACE_PARAMETER_TBL_sp

GO 

-- kill 88

 

SP_WHO2 

 

-- Optional but should be done for MyDB-DEV-NEW

ALTER DATABASE [MyDB-TMAR] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO 

ALTER DATABASE [MyDB-TMAR-NEW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO 

 

USE master;

GO

ALTER DATABASE [MyDB-TMAR]

Modify Name = [MyDB-TMAR_OLD];

GO

ALTER DATABASE [MyDB-TMAR-NEW]

Modify Name = [MyDB-TMAR];

GO

 

EXEC Utility.dbo.RESTORE_APP_INTERFACE_PARAMETER_TBL_sp 'MyDB-TMAR'

GO 

 

-- ALTER DATABASE [MyDB-TMAR] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

-- ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

 

 

--use utility 

--go 

--select * from sysobjects where type = 'p' 

 

-- EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'

 

EXEC Utility.dbo.TMARDBRestore_RemoveTRSJobs 'MyJob'

GO 

 

USE [MyDB-TMAR]

GO

 

DROP FULLTEXT INDEX ON MyDB_product_search 

GO 

--DROP FULLTEXT CATALOG MyDB_product_search 

--GO 

 

ALTER DATABASE [MyDB-TMAR] SET MULTI_USER;

GO

ALTER DATABASE [MyDB-TMAR_OLD] SET MULTI_USER;

GO 

USE [MyDB-TMAR] 

GO

IF EXISTS (

       SELECT * 

       FROM sys.database_principals

       WHERE name = 'MyDB-'

       )

DROP USER [MyDB-]

CREATE USER [MyDB-] FOR LOGIN [MyDB-]

-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]

EXEC sp_addrolemember N'db_owner', N'MyDB-'

 

-- NOTE: remember to add in user "Myweb" to prod1 and prod2 

USE [MyDB-TMAR] 

GO

IF EXISTS (

       SELECT * 

       FROM sys.database_principals

       WHERE name = 'Myweb'

       )

DROP USER [Myweb]

CREATE USER [Myweb] FOR LOGIN [Myweb]

-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]

EXEC sp_addrolemember N'db_owner', N'Myweb'

 

GO 

 

-- NOTE: remember to add in user "MyAdmin" to prod1 and prod2  

USE [MyDB-TMAR] 

GO

IF EXISTS (

       SELECT * 

       FROM sys.database_principals

       WHERE name = 'MyAdmin'

       )

DROP USER [MyAdmin]

CREATE USER [MyAdmin] FOR LOGIN [MyAdmin]

-- ALTER ROLE [db_owner] ADD MEMBER [MyDB-]

EXEC sp_addrolemember N'db_owner', N'MyAdmin'

 

What text is the sp executing?

 -- What text is the sp executing? 

       

       DECLARE @sqltext VARBINARY(128)

       SELECT @sqltext = sql_handle

       FROM sys.sysprocesses

       WHERE spid = 339 

       SELECT TEXT

       FROM sys.dm_exec_sql_text(@sqltext)

       GO

What is the spid doing?

 -- What is the spid doing? 

 

       DBCC INPUTBUFFER (86)

What users are active db x?

 -- What users are active db x? 

USE master 

GO 

 

       IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS

 

       CREATE TABLE #dbSPIDS (

              SPID INT      

       ,      [Status] VARCHAR (256)     

       ,      [Login] VARCHAR (256)      

       ,      HostName  VARCHAR (256)    

       ,      BlkBy VARCHAR (256) 

       ,      DBName VARCHAR (256)       

       ,      Command VARCHAR (MAX)      

       ,      CPUTime INT   

       ,      DiskIO INT    

       ,      LastBatch VARCHAR (256)    

       ,      ProgramName VARCHAR (256) 

       ,      SPID2 INT     

       ,      REQUESTID INT 

       )

       INSERT #dbSPIDS EXEC sp_who2

 

--     SELECT d.* 

       SELECT 'KILL ',      d.SPID,       d.* 

       FROM   #dbSPIDS d 

       where login not like '%sa%'

--     WHERE  d.dbname LIKE '%MyDB%dnn%TMAR'

      --WHERE       d.dbname = 'MyDB-d-TMAR'

      -- and (d.dbname LIKE 'MyDBTMAR' OR d.dbname LIKE 'MyDBTMAR_new' )

       and (d.dbname LIKE '%760%' )

 

-- kill 54

 

Try/catch syntax

 -- Try/catch syntax 

 

       BEGIN TRY 

              SELECT 2/0

       END TRY 

       BEGIN CATCH 

              SELECT Utility.dbo.fnGetErrorTryCatch () 

       END CATCH 

        

Find routine with text (wraps 4000 N)

 -- Find routine with text (wraps 4000 N) 

-- http://www.sqltact.com/2012/04/syssqlmodules-vs-informationschemarouti.html

       DECLARE @searchString NVARCHAR (4000) = '' 

       SET @searchString = 'product'

       SELECT s.name 

       +      '.'    

       +      o.name

       ,      o.type_desc

       ,      m.[definition]

       FROM   sys.sql_modules m

       JOIN   sys.objects o 

              ON     m.object_id = o.object_id

       JOIN   sys.schemas s 

              ON     s.schema_id = o.schema_id

       WHERE  m.[definition] like '%' + @searchString + '%'

       ORDER BY o.name

Find tables with column named ...

 EXEC Utility.dbo.findTWithC 'MyDB-tmar', 'product_code'

-- Common commands, settings

  

       SET NOEXEC ON 

       -- SET NOEXEC OFF 

 

       -- SET NOCOUNT ON 

       -- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

 

       -- (the 'recommended' way to look at sysobjects)

       -- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Tables 

       -- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Columns 

       -- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Views 

       -- SELECT TOP 33 * FROM INFORMATION_SCHEMA.Routines  

 

       -- drop temp table 

       -- IF OBJECT_ID('tempdb..#dbSPIDS') IS NOT NULL DROP TABLE #dbSPIDS

 

Linked Server Refering to Self

Likely a problem with DNS tables, this will fix it with a valid SQL user.

-- EXEC sp_addlinkedserver [MyServer], '', 'SQLOLEDB', '1.2.3.4'

sp_send_dbmail solution for attachments

When using the @query parameter with sp_send_dbmail via the SQL Agent to send an attachment in the email you may get the following error:

“Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.”

Apparently this is the sp_send_dbmail equivalent of the nearly useless Visual Basic “Object variable or With block variable not set.” error.

Note that the syntax was correct and everything worked fine when operating from a query session.

One way to get around it is to preface the execution context with a power user:

EXECUTE AS LOGIN = 'ElmerFudd'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alert Stuff',

@recipients = 'bugz@bunny.org; ',

@subject = 'Favorite vacations',

@query = N'

use msdb;

go

select top 10 ...

This took quite an effort to solve so I decided to share …

2¢

FYI: SS2016 Requires .Net FW 3.5 for DBMail

This was painful enough to figure out that I figured I would share the information with the entire group.

Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard (will not install from a exe).

 Detailz …

 DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).

Essentially there was no error information available, mail items would just sit in the queue.

Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server: “The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'” ~ which, after a lot of time with The Google, led to the answer.

Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring. 2¢, J++

Tools:

This was painful enough to figure out that I figured I would share the information with the entire group.

Instructions for installs on Server 2012+: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/enable-net-framework-35-by-using-the-add-roles-and-features-wizard  (will not install from a exe).

Detailz …

DatabaseMail.exe (in the instance Binn directory) which is invoked by the Service Broker which is part of the Core database engine since 2005 requires .Net FW 3.5, likely as 3.5 came out in 2002 and is a dependency. Why it will not work with a more recent version of .Net is a mystery, perhaps they are not all backwards compatible (https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/version-compatibility).

Essentially there was no error information available, mail items would just sit in the queue.

Starting the exe itself on the server or invoking dbmail via a sql interface puts this in the application log of the server:

“The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following:  'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 14001.'”

~

Which, after a lot of time with The Google, led to the answer. Don’t fall for missing DatabaseMail.exe.config threads as that is a red herring.

2¢, J++

Tools:  

SELECT

    CASE sent_status

        WHEN 0 THEN 'Unsent'

        WHEN 1 THEN 'Sent'

        WHEN 2 THEN 'Failed'

        WHEN 3 THEN 'Retrying'

        END AS sent_status_desc

FROM msdb..sysmail_mailitems

SELECT *

FROM   msdb..sysmail_event_log

ORDER BY log_id DESC

SELECT @@VERSION

SELECT sent_status, send_request_date a, *

FROM msdb.dbo.sysmail_mailitems

ORDER BY a DESC

SELECT *

FROM msdb.dbo.sysmail_sentitems

USE msdb

SELECT sent_status, *

FROM sysmail_allitems

SELECT is_broker_enabled

FROM sys.databases

WHERE [name] = 'msdb';

EXECUTE msdb.dbo.sysmail_help_status_sp

-- sp_configure 'Database Mail XPs'

SELECT *

FROM msdb.dbo.sysmail_event_log

ORDER BY log_date DESC

-- select @@VERSION

SELECT *

FROM   Msdb.dbo.sysmail_profile

SELECT *

FROM   Msdb.dbo.sysmail_event_log order by log_date desc

SELECT *

FROM   Msdb.dbo.sysmail_faileditems

EXEC Msdb.dbo.sysmail_help_queue_sp

SCRIPTING SQL SERVER OBJECTS WITH DBATOOLS – BEYOND DEFAULT OPTIONS

https://claudioessilva.eu/2019/05/15/scripting-sql-server-objects-with-dbatools-beyond-default-options/ 

Probably you had the need to script out some objects from a SQL Server instance/database and this is quite easy. You just need to right click on the object (well…not on every single one, try it with an Availability Group :-), no script option available) select “Script XXXX as” and you have it. But have you realized that this option doesn’t bring all the stuff? Let’s say you are scripting a table and you have a Non-Clustered index or a trigger…using this option some of the objects under the table will not be scripted out. 

I understand this as each one of this is a different object, which means if you go to each one you can right click and script just that one. SSMS – “Generate Scripts…” option. This is a tedious work and you can easily miss some objects. What can we do on SSMS to make this task easier? You can accomplish this task by using the “Generate Scripts…” option under “Tasks” when you right-click on the database: This will open the wizard and at the “Set Scripting Options” you can just click on the “Advanced” button and there you can change the properties. Here you can see that some defaults are in place, and “Script Indexes” is one of them. This is much easier right? All-in-one in a single scripting operation. What about automating this task? You want to script out multiple objects from different instances/databases. Enter dbatools’ “Export-” commands To search for commands within dbatools we can use the Find-DbaCommand. 1 Find-DbaCommand -Tag Export This command give to us a nice Synopsis text that help to find which command is the one we want. From the output we can see that we have (as of v0.9.824) 5 commands tagged as Export. To replicate our SSMS example using PowerShell we will use the Export-DbaScript. Don’t forget to use Get-Help cmdlet to find the available parameters and get some examples on how you can use the command. 1 Get-Help Export-DbaScript -Detailed Tip: Try other switches like -Examples or even -ShowWindow (won’t work on PSCore but dbatools does!) switches. Example using our “MyTable” object Here is how MyTable looks like: 3 Columns 1 Default constraint 1 Non-Clustered Index 1 Get-DbaDbTable -SqlInstance SQL1 -Database DB1 -Table MyTable | Export-DbaScript -Passthru Note: I’m using -PassThru parameter to output the script to the console, by default it will create a SQL file. The output of this execution is even more incomplete when comparing with SSMS. Here, we dont even get the default constraint scripted. Using “New-DbaScriptingOption” command dbatools has a command that makes it possible to create an object of type ScriptingOptions. Then we can change the properties like we have done before on the “Generate Scripts…” option on SSMS. 1 2 $options = New-DbaScriptingOption $options | Get-Member Use Get-Member so you can see what properties the object offers. Here we start seeing what we need. By default what are the values of properties like NonClusteredIndexes and DriDefaults 1 2 3 $options = New-DbaScriptingOption $options.NonClusteredIndexes $options.DriDefaults False! That explains why they are “missing” from our default Export-DbaScript output. NOTE: Export-DbaUser comamnd can also leverage on this object. Try it. Let’s change this options to $true and pass our $options object as the value of the -ScriptingOptionsObject parameter and run the command again. 1 2 3 4 $options = New-DbaScriptingOption $options.NonClusteredIndexes = $true $options.DriDefaults = $true Get-DbaDbTable -SqlInstance SQL1 -Database DB1 -Table MyTable | Export-DbaScript -Passthru -ScriptingOptionsObject $options Nice! Now we can see all the stuff. Try it yourself See the other options available, change the values, rerun and analyse the output. Do you need to export it to run on a lower SQL Server version? Change the TargetServerVersion option 1 2 #Will script the code like SQL Server 2014 $options.TargetServerVersion = "Version120" You want to include the “IF NOT EXISTS” statement? Change the “IncludeIfNotExists” option. Here is an example to script out a list of tables (add more to the $TableName variable): 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 $SourceServer = "SQL1"; $SourceDB = "DB1"; $TableName = "MyTable", 'YourTable'; $options = New-DbaScriptingOption $options.DriPrimaryKey = $true $options.DriForeignKeys = $true $options.DriNonClustered = $true $options.DriChecks = $true $options.DriDefaults = $true $options.Indexes = $true $options.IncludeIfNotExists = $true $TableName | Foreach-Object { Get-DbaDbTable -ServerInstance $SourceServer -Database $SourceDB -Table $_ | Export-DbaScript -ScriptingOptionsObject $options -Passthru; } Availability Groups example using dbatools Try it yourself: 1 Get-DbaAvailabilityGroup -SqlInstance SQL1 -AvailabilityGroup SQL1_AG1 | Export-DbaScript -Passthru Summary We have seen how we can leverage on some dbatools commands to generate T-SQL scripts from objects. This way we can versioning or just run them on other instace/database. We have seen what default options it (doesn’t) brings and how to add more options to it. We also saw that, for some objects, the script option is not available. As example Availability Groups, but dbatools can help you here too. Thanks for reading!

A modified and hardened process that updates statistics

SET NOCOUNT ON DECLARE @name VARCHAR(128) = '' -- database name , @update_stats varchar(max) = 'EXECUTE {DBNAME}..sp_updatestats' , @sql_script varchar(max) = '' , @errorMessage varchar(max) = '' , @Recips VARCHAR (1024) = 'foo@foo.org; ' -- , @Subject VARCHAR (1024) = 'TESTING: Failure of maintenance plan for updating statistics ' , @Subject VARCHAR (1024) = 'Failure of maintenance plan for updating statistics ' -- DEV -- , @mailProfile VARCHAR (64) = 'Mail' -- QA , @mailProfile VARCHAR (64) = 'Mail' -- Prod -- , @mailProfile VARCHAR (64) = 'Mail' /* SELECT @Recips += ISNULL (ap.pValue, 'Elmer; ') FROM dbo.params ap WHERE ap.pName = 'Elmer' */ DECLARE db_cursor CURSOR FOR -- Force an error for testing ... -- SELECT name SELECT QUOTENAME (name) FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','AspNetSqlSessionState') OPEN db_cursor BEGIN TRY FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @name SELECT GETDATE () [Start] SET @sql_script = REPLACE(@update_stats, '{DBNAME}', @name) EXEC (@sql_script) FETCH NEXT FROM db_cursor INTO @name SELECT GETDATE () [End] END END TRY BEGIN CATCH SELECT @errorMessage += @name + ': ' + someDB.dbo.fnGetErrorTryCatch () END CATCH CLOSE db_cursor DEALLOCATE db_cursor SELECT @errorMessage IF ISNULL (@errorMessage, '') != '' BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @mailProfile , @recipients = @Recips , @body_format = 'HTML' , @body = @errorMessage , @subject = @Subject END

How to tell if statistics are actually being updated

Even if everything 'looks' good: SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id) FROM sys.objects o JOIN sys.indexes i ON o.name = 'ORDERs' AND o.object_id = i.object_id; GO

Deceptively simple ... and potentially evil

drop table tNUM CREATE TABLE dbo.tNUM ( NUM INT PRIMARY KEY ); WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), -- 2 L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 4 L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 16 L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 256 L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 L6 AS (SELECT 1 AS c FROM L5 AS A CROSS JOIN L5 AS B), -- 18,446,744,073,709,551,616 ... ---- Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L2) Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5) --L6) INSERT INTO dbo.tNUM ( NUM ) SELECT -- TOP (1000) n FROM Nums --SELECT TOP (1000) -- n --FROM Nums ORDER BY n; SELECT COUNT (*) FROM tNum

FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server

Context: FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server FT_MON, FT_SEARCH, FT_CRAWL Resolution: a.) Verify that FT is the issue with sp_who2 b.) Put the database in single user mode and drop the index. ALTER DATABASE [foobar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP FULLTEXT INDEX ON foo; GO ALTER DATABASE [foobar] SET MULTI_USER WITH ROLLBACK IMMEDIATE; USE [Shared] GO ALTER -- CREATE PROC dbo. NoBotFindFullTextIndices ( @TableName VARCHAR (256) = '' , @DatabaseName VARCHAR (256) = '' , @found INT = 0 OUTPUT ) AS BEGIN /*************************************************************** Author 2018-02-28 15:42:50.917 1968.95 Source: https://stackoverflow.com/questions/16280918/how-to-find-full-text-indexing-on-database-in-sql-server-2008 Execution: DECLARE @foundIt INT = 0 EXEC [Shared].dbo.sp_NoBotFindFullTextIndices 'OLS_PRODUCT_SEARCH' , 'SMCO-PERSONIFY-DEV' , @found = @foundIt OUTPUT Note - a simpler way but with less utility ... SELECT DISTINCT object_name(fic.[object_id])as table_name , [name] FROM [foobar].sys.fulltext_index_columns fic JOIN [foobar].sys.columns c on c.[object_id] = fic.[object_id] and c.[column_id] = fic.[column_id] ****************************************************************/ SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @execMe NVARCHAR (2048) = '' SET @execMe = ' SELECT COUNT (t.name) FROM [' + @DatabaseName + '].sys.tables t JOIN [' + @DatabaseName + '].sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] JOIN [' + @DatabaseName + '].sys.fulltext_index_columns ic ON ic.[object_id] = t.[object_id] JOIN [' + @DatabaseName + '].sys.columns cl ON ic.column_id = cl.column_id AND ic.[object_id] = cl.[object_id] JOIN [' + @DatabaseName + '].sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id JOIN [' + @DatabaseName + ']. sys.indexes i ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id] WHERE t.name = ''' + @TableName + ''';' EXEC @found = sp_executeSQL @execMe END

Power Shell - Get UNC Path Directories

http://stackoverflow.com/questions/23574653/running-get-childitem-on-unc-path-works-in-powershell-but-not-in-powershell-run cd env: $foo = @{Name = "Foo"} $foo.Path = "\\serverPath\someDir\" $bar = @{Name = "Bar"} $bar.Path = "\\serverPath\someDir\" cd c: #THIS IS THE CRITICAL LINE @( $foo, $bar ) | ForEach-Object { $item = Get-ChildItem $_.Path # Do things with item Write-Host $item }

Could not load file or assembly or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

If all the subprojects are on the right version check the app pool.

Find other users on the box using the terminal

c:\query user /server: servername

SQL Server Last Restore Time

-- Last Restore time WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name ) SELECT * FROM [LastRestores] WHERE [RowNum] = 1

ASPState DB

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ aspnet_regsql.exe -ssadd -sstype p -S server\instance -U sa -P somePass

Entity Framework (EF) 6 (or at least 6.13) Error - Missing Primary Keys

In the past EF would blow up when trying to make an *.edmx file on a table with no PK. As of EF 6 (or at least 6.13) it allows them but the result set returned when performing a join on a table will this version can/will be incorrect.

A deadly, but silent, error.

SQL Server Finding the Object Alter Date

select TOP 1 * from sys.procedures order by create_date desc 
select TOP 1 * from sys.tables  order by create_date desc 
select TOP 1 * from sys.views  order by create_date desc 

/*

CREATE PROC jkAlterProcTest 
AS 
SELECT 1 

CREATE TABLE jkAlterTableTest (a int) 

CREATE VIEW jkAlterViewTest 
AS 
SELECT a
FROM jkAlterTableTest 

ALTER PROC jkAlterProcTest 
AS 
SELECT 2

ALTER TABLE jkAlterTableTest ADD b int

ALTER VIEW jkAlterViewTest 
AS 
SELECT a, b 
FROM jkAlterTableTest 

*/

Enable CLR for db / user

MAKE SURE YOU ARE TARGETING THE CORRECT DB For instance - DNN and not Person

USE dbFoo;

GO;

EXEC sp_changedbowner 'sa'

GO;

ALTER DATABASE dbFoo SET TRUSTWORTHY ON;

GO;

USE MASTER;

GO;

GRANT EXTERNAL ACCESS ASSEMBLY TO [domain\userBar]; -- note: server-wide

GO; 

USE dbFoo;

GO;

sp_configure 'show advanced options', 1;

GO;

RECONFIGURE WITH OVERRIDE;

GO;

sp_configure 'clr enabled', 1;

GO;

RECONFIGURE WITH OVERRIDE;

GO;

Disable Aero Shake, Win 7; All Windows Automagically Minimizing

http://www.howtogeek.com/howto/windows-7/disable-aero-shake-in-windows-7/

Disable Aero Shake in Windows 7

One of the interesting new features in Windows 7 is the way you can grab a window by the title bar and “shake” it back and forth to minimize everything else. It’s a fun feature, but just in case you want to disable it we’ve got the solution for you.

Disable Aero Shake Manual Registry Hack

Open up regedit.exe through the start menu search or run box, and then navigate down to the following key:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows

Once you are there, right-click on the Windows key and create a new key called Explorer.

Now right-click on the right-hand side, create a new 32-bit DWORD with the following settings:

• Name: NoWindowMinimizingShortcuts
• Value: 1

Once you’ve created this, you should log off and back on for the change to take effect.

Downloadable Registry Hack

Simply download, extract, and double-click on DisableAeroShake.reg to enter the information into the registry. To re-enable use the other file.

Prevent RDC from Logging Out Your Active Session When Logging In from Another Box

Credit: Brian Trexler

"I think I found the incredibly annoying setting that logs you out (and closes your active work) when disconnecting a Remote Desktop Session on Windows 7!  I’ve been determined to slay this dragon.

I’m going to keep an eye on this to ensure it sticks and never logs me out.  Let me know what you guys find.

Here is the maximum setting for “MaxDisconnectionTime”…"

Back up the registry, navigate to:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\Terminal Services\  

Change (decimal) value for MaxDisconnectionTime to 4294967295

VBA Pattern Matching with the Levenshtein Algorithm

Public Sub match()
    'MsgBox ("asdfs")
    For i = 3 To 200
    
        If (Cells(i, 2).Value <> "") Then
        
            If (Trim(UCase(Cells(i, 2).Value)) = Trim(UCase(Cells(i, 3).Value))) _
            And (Trim(UCase(Cells(i, 4).Value)) = Trim(UCase(Cells(i, 5).Value))) _
            And (Trim(UCase(Cells(i, 6).Value)) = Trim(UCase(Cells(i, 7).Value))) _
            And (Trim(UCase(Cells(i, 8).Value)) = Trim(UCase(Cells(i, 9).Value))) _
            Then
                Cells(i, 13).Value = "Y"
            Else
                Cells(i, 13).Value = "N"
            End If
            Cells(i, 14).Value = Levenshtein(Cells(i, 2).Value, Cells(i, 3).Value)
            Cells(i, 15).Value = Levenshtein(Cells(i, 4).Value, Cells(i, 5).Value)
            Cells(i, 16).Value = Levenshtein(Cells(i, 6).Value, Cells(i, 7).Value)
            Cells(i, 17).Value = Levenshtein(Cells(i, 8).Value, Cells(i, 9).Value)
            Cells(i, 18).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value + Cells(i, 17).Value
        Else
            Cells(i, 13).Value = ""
            Cells(i, 14).Value = ""
            Cells(i, 15).Value = ""
            Cells(i, 16).Value = ""
            Cells(i, 17).Value = ""
            Cells(i, 18).Value = ""
        End If
        
        ' Write sql
        ' Good ones
         If ((Cells(i, 13).Value = "Y") Or (Cells(i, 18).Value < 5)) And (Cells(i, 2).Value <> "") Then
        ' Likely good
        ' If (Cells(i, 18).Value > 4) (Cells(i, 18).Value < 10) Then
            writeSQL (i)
        ' the names are an exact match but there is not city/state/zip information
        ' this is the case with a lot of the SMO_2015_Voting_Member_List ones
        ElseIf (UCase(Trim(Cells(i, 2).Value)) = UCase(Trim(Cells(i, 3).Value))) _
            And UCase(Trim((Cells(i, 4).Value)) = UCase(Trim(Cells(i, 5).Value))) _
            And UCase(Trim((Cells(i, 6).Value)) = "NULL") _
            And UCase(Trim((Cells(i, 8).Value)) = "NULL") _
            And UCase(Trim((Cells(i, 10).Value)) = "NULL") Then
            writeSQLNULL (i)
        Else
            Cells(i, 20).Value = ""
            Cells(i, 21).Value = ""
        End If
        
    Next i
    
End Sub


Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
    'https://en.wikipedia.org/wiki/Levenshtein_distance
    'http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel

    string1 = UCase(Trim(string1))
    string2 = UCase(Trim(string2))

    Dim i As Long, j As Long
    Dim string1_length As Long
    Dim string2_length As Long
    Dim distance() As Long
    
    string1_length = Len(string1)
    string2_length = Len(string2)
    ReDim distance(string1_length, string2_length)
    
    For i = 0 To string1_length
        distance(i, 0) = i
    Next
    
    For j = 0 To string2_length
        distance(0, j) = j
    Next
    
    For i = 1 To string1_length
        For j = 1 To string2_length
            If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
                distance(i, j) = distance(i - 1, j - 1)
            Else
                distance(i, j) = Application.WorksheetFunction.Min _
                (distance(i - 1, j) + 1, _
                 distance(i, j - 1) + 1, _
                 distance(i - 1, j - 1) + 1)
            End If
        Next
    Next
    
    Levenshtein = distance(string1_length, string2_length)
    
End Function

Function writeSQL(ByVal i As Integer)

    Dim customer As String, firstName As String, lastName As String, _
    city As String, state As String, zip As String, _
    _id As String, sqlSelect As String, sqlUpdate As String
        
    customer = RTrim(Cells(i, 1).Value)
    firstName = RTrim(Cells(i, 3).Value)
    lastName = RTrim(Cells(i, 5).Value)
    city = RTrim(Cells(i, 6).Value)
    state = RTrim(Cells(i, 8).Value)
    zip = RTrim(Cells(i, 10).Value)
    _id = RTrim(Right("0000000000000" & Cells(i, 12).Value, 12))
    
    sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
    firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
    "' AND [state] = '" + state + "' AND zip = '" + zip + "'"

    sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca. = '" + _id + _
    "' FROM ca WHERE ca.firstName = '" + firstName + "' AND " + _
    "ca.lastName = '" + lastName + "' AND city = '" + city + "' AND [state] = '" + state + _
    "' AND zip = '" + zip + "' AND customer = '" + customer + "'"

    If (firstName = "firstName") Then
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = ""
        Rows(i).Interior.Color = 45535
    Else
        'Cells(i, 20).Value = sqlSelect
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = sqlUpdate
        Rows(i).Interior.Color = 65535
    End If
    
End Function

Function writeSQLNULL(ByVal i As Integer)

    Dim customer As String, firstName As String, lastName As String, _
    city As String, state As String, zip As String, _
    ship_master_customer_id As String, sqlSelect As String, sqlUpdate As String
        
    customer = Cells(i, 1).Value
    firstName = Cells(i, 3).Value
    lastName = Cells(i, 5).Value
    city = Cells(i, 6).Value
    state = Cells(i, 8).Value
    zip = Cells(i, 10).Value
    _id = Right("0000000000000" & Cells(i, 12).Value, 12)
    
    sqlSelect = "-- SELECT * FROM  ca WHERE ca.firstName = '" + _
    firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
    "' AND [state] = '" + state + "' AND zip = '" + zip + "'"

    sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca.= '" + _id + _
    "' FROM ca ca WHERE ca.firstName = '" + firstName + "' AND " + _
    "ca.lastName = '" + lastName + "' AND city IS NULL AND [state] IS NULL AND zip IS NULL AND customer = '" + customer + "'"

    If (firstName = "firstName") Then
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = ""
        Rows(i).Interior.Color = 45535
    Else
        'Cells(i, 20).Value = sqlSelect
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = sqlUpdate
        Rows(i).Interior.Color = 65535
    End If
    
End Function