SQL Server 2008 R2 SSMS failed to install and complaining about Visual Studio 2008 SP1 should be installed

Recently, I have a developer asked me to help to install management studio on her laptop. Sure! How hard could it be? So I start as usual and installing the SSMS from the installation disc image.

In the middle of the installation, it stops. With the following error:

Given that, who on earth would be still using Visual Studio 2008!? Everyone moved forward to Visual Studio 2010 already. Something does make sense here... So, Yay!! It~~ is~~~ Google time!!

Did some research. This is, in fact, quite an old issue dated back to 2011 from most of the post I read. Note that most of the post reported that they haven't installed Visual Studio 2008 on their machine. Anyway, the solution is involved with registry key change, so make sure you have extract the registry for rollback purpose.

Firstly, you need to determine if you have a 32-bit or 64-bit OS. That will determine if you need to carry on with an additional step.

Next, let's check the following registry keys with the regedit. Make sure that the SP and SPIndex values are "1" and that the SPName is "SP1":

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0\RED\1033

Now, if you have a 64-bit OS, this is extra that you need to go further. Similar deal, make sure that the SP and SPIndex values are "1" and that the SPName is "SP1":

• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0
• HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0\VSR\1033

After all of this, just restart the installation again and this time the installation should be able to be completed. To credit the solution, here is the URL for reference: SQL Server 2008 R2 Management Studio install fails telling me to install Visual Studio 2008 SP1!. Thanks, Eric!!

Testing query permission with EXECUTE AS command

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

It is embarrassing to announce that you have created a super duper database object for your manager and then found out that they don't have permission to run it.

There is a good and easy way to check if your intended user have enough permission to call the newly created object you crafted for them. On the other hand, of course you can use this way to make sure those who should not be able to access is really cannot access to the object.

Simply, run the EXECUTE AS command before the query like following:

Then try to query a view or running a stored procedure, it will or not return result based on the database user permission.

Once you are finished with your testing, you can use REVERT command to get back to last credential before the EXECUTE AS command

SQL Trick KB: Function - How to check the datatype for a field or variable with T-SQL

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Warning: This is not necessary useful - until you need it or in my case, to be honest, when you are getting too bored.

One day, I try to verify what datatype of a field that is used by a stored proceduce. Clicking through the GUI is too hard and un-cool. So, Google tells me how to achieve it. There is a built in function called SQL_VARIANT_PROPERTY and this is the reference to it: MSDN - SQL_VARIANT_PROPERTY (Transact-SQL)

How-to: Check cluster size configuration on an existing drive

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

One of the best practice that, when preparing for hard drives which SQL server is going to utilise for storing the data file, log file and tempdb files, DBA should ensure that these drives are format with cluster size to be set to 64kB instead of the default NTFS value, which is 4kB.  Here is the background: Disk Partition Alignment Best Practices for SQL Server.

OK, how about if you just start a new job and would like to do a sanity check on what are you facing?To check the cluster size of an existing drive, this is the command:

fsutil fsinfo ntfsinfo C:

This is what the output look like:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
C:\Windows\system32>fsutil fsinfo ntfsinfo C:
NTFS Volume Serial Number :      0x3cc4654e561810d1
Version :                         3.1
Number Sectors :                  0x000000001116389f
Total Clusters :                  0x000000000222c713
Free Clusters  :                  0x0000000000c2cccb
Total Reserved :                  0x0000000000000040
Bytes Per Sector  :               512
Bytes Per Physical Sector :       <Not Supported>
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000005e40000
Mft Start Lcn  :                  0x0000000000000004
Mft2 Start Lcn :                  0x0000000001116389
Mft Zone Start :                  0x0000000000005e40
Mft Zone End   :                  0x0000000000011020
RM Identifier:        B9CC8B49-4513-11E0-8641-806E6F6E6963
C:\Windows\system32>

The highlighted row is exactly what we are looking for, the cluster size of my C: drive is 4kB ( ie. 4 x 1024 btyes). I wish I would not see the same number when I run the command against the data and log file drive.....

Sanpshot replication dropped all my index

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Just find out that the snapshot replication dropped all the index in the subscription database for some reason. I don't think this is intended, though it would make sense that if index is in place, it will slow down the bulk insert.

Update
Yes, snapshot replication will do that. Still trying to find out where Microsoft did document this information. It is making sense in every way excepts if it is documented somewhere, it will feel much better.

Monitoring replication with T-SQL

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

A developer asked that if there is anyway to monitor replication via T-SQL. I found a forum post which pointed me to Microsoft book online page - How to: Programmatically Monitor Replication (Replication Transact-SQL Programming). There are a number of system stored procedures which would cover the aspect related to Replication monitor.

Double hopping issue with the SSRS

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Trying to troubleshoot double hopping issue with the SSRS. The problem is complicated when report services app server separated from the data source database. The authentication only fulfil at the client to the reporting server but then stop there, while another authentication is still necessary to authenticate between the app server to the database.

Got the integrated security works on SSRS. The after the work done on: (1) setting up the SPN for report server and SQL server, (2) setting up the delegation cross apply against the services account, (3) change the authentication method to Kerberos as first considered method in RsReportServer.config. It is then amazingly works.

Error alert - SSPI handshake issue

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Try to look at the problem on the SSPI handshake issue. The following error message was reported from time to time:

“"SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed"”

Checked on Google, there are a number of forum posts pointing the problem may resulted from that 0x80090311 error refers to "No authority could be contacted for authentication" which means the user cannot contact AD to get a Kerberos ticket. Let check with my network admin buddy to see if anything to do with AD and Kerberos. It is not necessary something you can rectify here, because it could be the user just ignore the password policy in regards to password change and Kerberos just has enough with their neglect and kick them off.

Here are some articles on Kerberos troubleshooting:
Forum post on SQL Server 2005 connection problems from SQL Server Central
Blog post on Kerberos Adventures - Useful Information Links from Michael Stephenson's blog
Blog post on Troubleshoot Connectivity Issue with SQL Server Express 2005 from MSDN Blog - SQL Protocols

Printing from SSRS. The administrator is scatching his head until this is found...

SSRS printing? Why does it has to be that troublesome? The problem is when user needs to print the rendered report, they need to install an ActiveX component from the SSRS, namely the RSClientPrint.cab. To install this module, you need, of course, administrator right. {facepalm, nice work MS}. Here is a silent deployment guide, found on the blog Kodyaz Development Resources, which takes care of it this way (it is more detail on Eralper's blog, the following is summarised for my env.):

RSClientPrint.cab is a Microsoft ActiveX control that provides client side printing for Microsoft SQL Server Reporting Services reports.

If the print command is being run for the report server by that user's client machine then the client request the RSClientPrint.cab download from the related Report Server. While downloading the ActiveX software the browser will promt the user whether the software will be installed or not considering the security issues. Again, it is necessary to note that administration permissions are required for downloading and installing the rsclientprint ActiveX. For RSClientPrint install, RSClientPrint.cab download is the first step in installing the ActiveX on IE browser.

In most companies the Information Technology policies do not grant the admin privileges to the end users. So the end users running on the client computers can not download and install the ActiveX print client file RSClientPrint.cab. So it is a need for the IT departments to distribute this client side printing ActiveX control in order to support their clients to work with SQL Server Reporting Services (SSRS) without a problem.

The default bin folder of the ReportServer 2008 where new RSClientPrint-x86.cab, RSClientPrint-x64.cab or RSClientPrint-ia64.cab ActiveX file download can be found at :

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin

For a silent deployment of RSClientPrint.cab, you only need to distribute RSClientPrint.dll file and the .rll files. If you copy the files listed below under the folder C:\WINNT\system32 for client computers.

RSClientPrint.dll
RSClientPrint_1028.rll
RSClientPrint_1031.rll
RSClientPrint_1033.rll
RSClientPrint_1036.rll
RSClientPrint_1040.rll
RSClientPrint_1041.rll
RSClientPrint_1042.rll
RSClientPrint_1043.rll
RSClientPrint_1046.rll
RSClientPrint_1053.rll
RSClientPrint_2052.rll
RSClientPrint_3082.rll

Register the RSClientPrint.dll dll file by running the regsvr32.exe command on the command prompt shown as below.

c:\regsvr32.exe /s
c:\winnt\system32 \rsclientprint.dll

So, if a script or an MSI file that copies the listed files above and registers the rsclientprint.dll will be enough to deploy the Reporting Services Client Side Printing functionality.

After the installation of the RSClientPrint is successfully done, the Print dialog box is displayed as shown below. You will realize that the Print options is very similar to standart Print dialog boxes that run on Windows systems including preview, number of copies, print range, printer selecting from available printers, etc.

How-To: Linked Server

This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"

Thanks to Derek Dieter, he puts up a post on How to Add a Linked Server. Very simple and straight forward.

What my problem is that I keep forgetting that when you are setting up a linked server which is another SQL Server, make sure that "Other Data Source" is chosen for the "Server Type" option instead of "SQL server". Rare, isn't it? I have so many times thought let's use the option "SQL Server" since it is natively a SQL server target. But it is always failed on me, until I refered back to Derek's post.