Run T-SQL in Parallel

Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
  • Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
  • Effective monitoring: People want to see which session is running what.
  • Adjustable maximum threads in the course of execution.

Continue reading “Run T-SQL in Parallel”

Get All Referenced Tables and Columns

I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that sys.sql_dependencies and sys.sql_expression_dependencies can tell. At the end of the day, I figured that depending on the complexity of the procedure, those 2 views couldn’t always give me accurate information as needed, dynamic SQLs for instance. Even worse, my customer also asked me if a table was accessed by anyone and what columns were referenced. I realized that I have to write something to get it done.
Continue reading “Get All Referenced Tables and Columns”

Print Long String

Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time

declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @line = replicate(@line, 3)
declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end
/*
012345678901234567890....
abc
012345678901234567890....
6789abc
012345678901234567890....
23456789abc
*/

Continue reading “Print Long String”

Changed SoundEx Disables Index While Upgrading to SQL 2012

Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256.

It does not seem very significant to our life. But it gives you surprise when this function is used for a calculated column where the value of it is persisted in the index. The indexes containing this column will be disabled automatically after restoring an SQL Server 2008 database to SQL Server 2012. Here is the result

RESTORE DATABASE successfully processed 0 pages in 2.094 seconds (0.000 MB/sec).
Warning: The index "PK_blablaView" on "blaSchema"."blablaView" was disabled because the implementation of "soundex" have changed.
Warning: Clustered index 'PK_blaView' on view 'blaView' referencing table 'blaTable' was disabled as a result of disabling the index 'PK__blaTable'.
Warning: The index "PK__blaTable" on "blaSchema"."blaTable" was disabled because the implementation of "soundex" have changed.

The solution is to rebuild indexes after restore.

Create Semaphore in Sybase

It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which is restored 5 minutes. The user account the ETL is using has very limited right on Sybase Server. The Sybase DBA does not have SQL Server driver on the Unix system. This means that I can’t put log restore as part of my ETL and Sybase DBA does not have connectivity to SQL Server side. We need to coordinate those 2 processes that when one is running the other must not run.
Continue reading “Create Semaphore in Sybase”

No More SQL Masters

It’s so sad to say that there will be no more Microsoft Certified SQL Server Master, Architect, and Solution Master any more after October 1, 2013. We all received this email from Microsoft. If you are still keen in getting the highest technology certification, September this year will be your last chance. It’s also your last chance to stay in this exclusive and highly technical community. Otherwise, waiting for evolved certification in the future and/or joining millions of MCSEs will be your choices. Although it will be retired very soon, I still believe all certified masters possess their expert level knowledge on SQL Server. They have been spent tremendous time on learning SQL Server, practicing, and resolving real life problems. Great experience attending 3 week training session at Microsoft Redmond. Thanks for all my instructors.

Protect Yourself from Using DBCC WritePage

I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…

Continue reading “Protect Yourself from Using DBCC WritePage”

Bring Offline File Online — Modify SQL Server Metadata

In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back online. In either situation, you will need to modify/correct SQL Server metadata. This is allowed in SQL Server 2000 with “Allow ad hoc update” option turned on. However, it’s deprecated in SQL Server 2005 and removed in SQL Server 2008 and upper versions.

Continue reading “Bring Offline File Online — Modify SQL Server Metadata”