Archive for December, 2013

Unable to drop the index

December 31, 2013 Leave a comment

Issue: There is a table with an index on several columns. The table is Schema bound and makes up part of an indexed view. User getting the below error

Drop failed for Index ‘UNIQUE_RECORD’.

Workaround: ALTER INDEX is the best option for this case. Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index. We can disable it .DISABLE Marks the index as disabled and unavailable for use by the SQL Server 2005 Database Engine. Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data. Disabling a clustered index prevents user access to the underlying table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING. For more information, see Disabling Indexes.

Reference link:

Categories: Index

Rollback of SSIS

December 31, 2013 Leave a comment

Issue: I have a SSIS that insert a lot of comments that we received, because our pdn server is offsite, when I am loading the comments to pdn server, my concern is if the internet dropped, I need to roll back the transaction, is there a way to do this? or if there is a better way?


We can put the steps in the package inside of a transaction to roll back the entire transaction if any problem occurred …here is a video on how to do it….

To watch the video just need to register on the site and after confirmation you can watch it.

Note about Transaction:

Transactions in SSIS allow you to protect your data and envelope any data change that occurs in the package in that protection. Transactions can be either on the entire package, a container, or an individual task. They can be nested just like in TSQL. What is important to note about transactions is that only data-related tasks are protected. If your package were to archive a file, for example, that file would not be un-archived upon a package failure (unless you explicitly created some kind of compensating action to perform such duties).

Letus take the previous package you created and wrap the package in a transaction. First, lets disable the checkpoint from the previous example by changing the Checkpoint Usage package property to Never and the Save Checkpoints property to False. Next, delete all the records from the RestartabilityExample table.

With the example now reset back to its original state, you’re ready to enable transactions on the package. In the Properties window for the package, change the Transaction Option at the bottom of the window to Required. By changing this from Supported to Required, you have created a transaction that envelopes the package. Any task or container that has this same property set to Supported will join the transaction. By default, each task is set to join the transaction with the Transaction Option of Supported set.

For you to enable this type of transaction, though, you need the Microsoft Distributed Transaction Coordinator (MSDTC) started on each server that you want to participate in the transaction. So, if you have a server that changes data on two servers and runs on a third server, you would need MSDTC running on all three servers. Transactions can then protect data nearly from any database like SQL Server, Oracle, and UDB, since you have externalized the transaction-handling to another service. The caveat with this, though, is the database must be running on a Windows machine, since MSDTC is a Microsoft service.

Before executing a package that has transactions enabled, you must ensure that MSDTC is started. You can start the MSDTC service in the Services applet, or you can type the following command from a command prompt: NET START MSDTC. If you did not have the service started, you would receive the following error when executing the package:

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B “The Transaction Manager is not available.”. The DTC transaction failed to start. This could occur because the MSDTC Service is not running. After having the service started, lets break the package yet again. Select the last task, which is named Insert Record 2, and change the ForceExecutionResult package property to Failure. Execute the package again and you should see the results as shown in Figure 7-11. This time, if you were to view the records in the Adventure Works database, you should see no new rows. This is because the purging of the table occurred, then the first record was inserted, and when the final task failed, both it and the first two operations (within the same transaction) were rolled back.

Categories: SQL SSIS

Send email using SQL Mail without specifying a profile

December 31, 2013 Leave a comment

Issue: User is experimenting with sending email via sp_send_dbmail. If he set the @profile_name to NULL I get the following error:
No global profile is configured. Specify a profile name in the @profile_name parameter.
So, he would like to send email using SQL Mail without specifying a profile while at the same time passing in the @from_address?
As per the problem posted two thoughts
Frist Thought:
When @profile is not specified, sp_send_dbmail uses a default profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile. If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

sp_send_dbmail does not support e-mail messages with no content. To send an e-mail message, you must specify at least one of @body, @query, @file_attachments, or @subject. Otherwise, sp_send_dbmail returns an error.
Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.
If both @query and @file_attachments are specified and the file cannot be found, the query is still executed but the e-mail is not sent.
When a query is specified, the result set is formatted as inline text. Binary data in the result is sent in hexadecimal format.
The parameters @recipients, @copy_recipients, and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. At least one of these parameters must be provided, or sp_send_dbmail returns an error.
Reference Link:

Second thought:
How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account–

Categories: Mail Profile

Cannot access the SQL server database using Windows Authentication (on a domain)once BitLocker ON

December 31, 2013 Leave a comment

Issue: Client are trying to use Bitlocker to secure an SQL 2012 Standard installation, but whenever they turn BitLocker ON, they cannot access the database using Windows Authentication (on a domain).
Workaround: You need to use the SQL server service account to enable Bitlocker and initialize the TPM

Installation and initialization

BitLocker is installed automatically as part of the operating system installation. However, BitLocker is not enabled until it is turned on by using the BitLocker setup wizard, which can be accessed from either the Control Panel or by right-clicking the drive in Windows Explorer.

At any time after installation and initial operating system setup, the system administrator can use the BitLocker setup wizard to initialize BitLocker. There are two steps in the initialization process:

1. On computers that have a TPM, initialize the TPM by using the TPM Initialization Wizard, the BitLocker Drive Encryption item in Control Panel, or by running a script designed to initialize it.

2. Set up BitLocker. Access the BitLocker setup wizard from the Control Panel, which guides you through setup and presents advanced authentication options.

When a SQL service account initializes BitLocker, the administrator should also create a recovery password or a recovery key. Without a recovery key or recovery password, all data on the encrypted drive may be inaccessible and unrecoverable if there is a problem with the BitLocker-protected drive.

Reference link:

Categories: Encryption

Could not delete the subscription at Subscriber

December 31, 2013 Leave a comment

Issue: My server “SVR1” runs a SQL Server with a transactional publication. On the second server “SVR2” I had a subscription.
Now the SVR2 does not exist anymore and I want to delete the replication on SVR1. (in the SQL Management Studio, the subscription is still visible)

But if I want to delete the SVR2-subscription on the SVR1, I get the error message 20032 “Could not delete the subscription at Subscriber ‘SVR2’ in database ‘ICSDB’.”How can I delete this subscription (and afterwards also the publication)

I’m using SQL Server 2008 R2 on Windows Server 2008R2


Right click on the publication; go to properties then to subscriptions. Click Delete. This is the EM way. For the QA way, generate the delete statement from EM.

For future use, I was having the same problems described above – but I also had database snapshots on the database that I was trying to remove replication from but that was not mentioned anywhere in the error messages.

Reference Link:

Once I removed my database snapshots, the following script cleans things up well:

Example came from here:

DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N’distribution’;
SET @publisher = N’S-ESN-SQL1\SQL1′;
SET @publicationDB = N’AdventureWorks2008R2′;

— Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;

— Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

— Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

— Remove the local server as a Distributor.
EXEC sp_dropdistributor;

Categories: Replication

The easiest way to recover of your replication

December 31, 2013 Leave a comment

Issue: User has database replication from one server to another over a rather thin link. They are out of sync so he would prefer to re-snapshot. Unfortunately the link is thin and performing a snapshot would incur huge transfer delays. He would not like to perform a snapshot but create a backup that can be hand carried. He would like to do ASAP on exiting replication model


Once you have replication configured, you should be able to re-create it easily in case of a disaster or if you want to deploy it to a client’s location. In Enterprise Manager, the easiest way to generate a SQL Script for replication is to right-click on the publication in the Replication group and selects Generate SQL Script. You are asked if you’d like to create a script for the removal of replication or the creation of replication. The script does the following:
• Set up the database for replication
• Create the publication
• Assign the appropriate permissions to the publication
• Add articles to the publication
• Create the subscription

If a disaster occurs on your publisher or subscriber servers, the easiest way to recover is to use the following steps:

1. Unsubscribe from the publisher.
2. Restore the most recent backup or backups.
3. Subscribe to the publisher.
4. Resynchronize the system.

Categories: Replication

Problem loading RDL file in SSRS 2005

December 31, 2013 Leave a comment

Issue: Problem loading RDL file in SSRS 2005.When User open a up VS Studio  2005 and double click on a report to open it. He got a dialog box to display some tmp file and on the main screen and  see a message displaying:

“Navigation to the webpage was canceled”


Based on my knowledge, your problem was because IE settings or network settings.

Then I found related explanation on this page

“After you install security update 961260 (MS09-002) on a computer that is running Windows Vista or Windows Server 2008, you may experience a problem when you visit some Web pages that are in different Internet Explorer security zones. When the problem occurs, you receive a message that resembles the following:

Navigation to the webpage was canceled
This problem occurs if the navigation causes Protected Mode settings to change from On to Off. You can determine which Internet Explorer security zone a Web page is in by looking at the status bar at the bottom of the Internet Explorer 7 window.

So please show us your IE version, we may fix this by solution listed on that page.

For example,
“Undo workaround method 3: Disable Protected Mode for the “Trusted Sites” zone

1.Click Start

Collapse this imageExpand this image

Start button , type Internet Options in the Start Search box, and then click Internet Options in the Programs list.
2.Click the Security tab, and then click Trusted Sites in the Select a zone to view or change security settings area.
3.Click to clear the Enabled Protected Mode (requires restarting Internet Explorer) check box, and then click OK.
4.Restart Internet Explorer.

Reference Link:

Categories: SSRS