SQL Server General DBA

Errors when SQL Server Snapshot Replication is Running

The following errors may occur when SQL Server Snapshot replication is running: Cannot DROP TABLE <Table Name> it is being referenced by object <View Name>. Cannot TRUNCATE TABLE <Table Name>because it is being referenced by object <View Name>. Data changes are not tracked for snapshot replication, each time a snapshot is applied, it completely overwrites the […]

Error messages when you try to process a database or a cube in SQL Server 2005 Analysis Services: “The attribute key cannot be found” and “The record was skipped because the attribute key was not found”

The may receive the following error messages when you try to process a database or a cube in SQL Server 2005 Analysis Services: “The attribute key cannot be found” and “The record was skipped because the attribute key was not found”. Solution This issue occurs because a fact table for a cube has one or […]

Database Mirror Certificate Expired

When certificates are created, if you didn’t specify the EXPIRY_DATE parameter with an expiry date, it will be set to one year.  Hence the certificate will expire in one year.  The following method can be used to specify an expiry date for a certificate. CREATE CERTIFICATE cert_Mirror WITH SUBJECT = ‘database mirroring certificate’, EXPIRY_DATE = ’12/31/2030′;  Although […]

How to replicate more than 256 articles in Merge Replication?

In merge replication there is a limitation in the maximum number of articles that you can publish, which is 256. However, this limitation is 32,767 articles for snapshot and transactional replication.  This is a very small number of articles for large systems, however the following workaround can be used to publish additional articles. 1. Create several publications so that […]

SQL Server 2005 – clients using native client, should clients be upgrade as well

As you may be aware that SQL Server 2005 is steady and for the DBAs the task is to upgrade the database, in this scenario there will be a big question that whether the clients should be upgraded to use SQL Native client on the server. The backward compatibility of SQL Server 2005 engine will […]

How to Configure Multiple SQL Server Reporting Service Instances on the Same Computer

When a second instance of SQL Server Reporting Services is installed the second instance will connect to the default instance.  This is because the default behavior of a Reporting Services instance is to redirect its requests to the localhost. To reconfigure this behaviour you need to change the settings in the RSReportServer.config, RSWebApplication.config config files […]

Error When Configuring SSRS Database

When trying to configure the SQL Server Reporting Services (SSRS) Database Setup from the Reporting Service Configuration Manager you may receive the following error: The database version (C.0.8.40) does not match your reporting services installation. You must upgrade your reporting services database. Couldn’t generate the upgrade script. There is no upgrade script available for this […]

How to Access a Map Drive from SSIS ?

I have an SSIS package which accesses a map drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error. What is the reason for this? You should assign the necessary permission to the map drive for the user account which […]

How can I force SQL Server 2005 logins to change their passwords periodically?

As you might be aware, SQL Server 2005 includes a new way to enforce password policy & expirations for SQL Server login IDs. This was not the case in previous SQL Server versions. In the past, the only option you had was to use Windows Authentication if you wanted to enforce a password policy, but […]

What permission does the user need to run PROFILER in SQL Server 2005?

Question: I have a team that would like to use the PROFILER to monitor the underlying TSQL statements to fine tune the database. How to enable this for the users without granting any Administrative privileges such as DBOon that database. Solution: Running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures […]