SQL Server General DBA

Error when Inserting data to Replicated Databases

In a database previosly enabled for replication, upon attempting to insert records the following error is raised. Invalid object name ‘dbo.MSmerge_contents’. To resolve this, when replication is enabled, additional columns and triggers are created. When replication is removed, there are times that newly added tables are not removed. To overcome this run: sp_removedbreplication  @dbname =  […]

How to Perform Multiple lookups to the same table

In a SQL Server Integration Service (SSIS) package, when lookups are performed on tables inserted in the same package by a previous task, the error “could not match” is raised.    Above is the failed package layout. From the insert data, two records will be inserted and at the lookup those values are used to get […]

How to run SQL Profiler without sysadmin rights?

In SQL Server 2000, to run SQL Profiler the sysadmin needs to grant permissions to the user running the trace. In SQL Server 2005 and 2008, the option exists of granting permissions to Profiler so that users do not need to be a member of the sysadmin group by using the code below: USE master GO GRANT […]

How to import an Excel file which has columns with more than 255 characters to SQL Server using SSIS?

When the driver determines that an Excel column contains text data, the driver selects the data type based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a […]

How to Manually Uninstall SQL Server 2005

Sometimes, you may find that, though you can’t see any SQL Server 2005 instance in the programs list and when you try to install SQL Server 2005, you get a messages saying that there is already a SQL Server instance or some components such as SQL Server Management Studio installed. The reason for this is the existence of corrupted SQL […]

How to send a SSRS report from SSIS?

Often there is a requirement to be able to send a SSRS report in Excel, PDF or another format to different users from a SSIS package one it has finished performing a data load. In order to do this, first you need to create a subscription to the report. You can create a SSRS report […]

How to alter a User Defined Data Type?

The only way to alter a User Defined Data Type is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously.  The problem is that you can’t drop the UDDT […]

How to view previous query plans?

How do you view previous query plans? You can view previously run queries’execution plan using sys.dm_exec_query_plan dmv and joining it with other two dmvs in SQL Server 2005. select qs.execution_count       , qs.total_elapsed_time, qs.last_elapsed_time      , qs.min_elapsed_time, qs.max_elapsed_time      , substring(st.text, (qs.statement_start_offset/2)+1                         , ((case qs.statement_end_offset                               when -1 then datalength(st.text)                                else qs.statement_end_offset                            end – […]

Cannot Start SQL Server Service

When you try to start the SQL Server service it may fail with the following errors logged in Application log in Event Viewer. TDSSNIClient initialization failed with error 0x7e, status code 0×1. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately […]

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 […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |