Nov 192015

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 »

Nov 192015

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)
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000

Continue reading »

Dec 162013

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.

Nov 072013

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 »

Aug 312013

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.

May 142013

I blogged DBCC WritePage a year ago here 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 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 »

May 092013

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 »

May 072013

I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They created a new file group with files to host some temporary data for data fixing or testing. New files were not created under the folder as usual and they thought they would remove them right after tasks complete. But the latter part got forgotten afterwards.

Continue reading »


Get every new post on this blog delivered to your Inbox.

Join other followers: