MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools

Data conversion, translation, and mapping is by no means rocket science, but it is by all means tedious. Even a simple data conversion task (e.g., reading a CSV file into a list of class instances) can require a non-trivial amount of code. While all of these tasks share much in common, they are all “just different enough” to require their own data conversion methods.

How to Tune Microsoft SQL Server for Performance

To retain its users, any application or website must run fast. For mission critical environments, a couple of milliseconds delay in getting information might create big problems. As database sizes grow day by day, we need to fetch data as fast as possible, and write the data back into the database as fast as possible. To make sure all operations are executing smoothly, we have to tune our database server for performance.

Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object ''. The duplicate key value is ()

You might see the application guys coming out to you with the following error or you might encounter this error sometime when you are doing some insert or may be running a sp which is doing some insert.

"Violation of PRIMARY KEY constraint 'Constraintname'. Cannot insert duplicate key in object 'tablename'. The duplicate key value is (duplicatevalue)

As the name itself says, its pretty simple, the application or insert statement is trying to insert something into a column which is primary key and the value which is trying to insert is already existing in column

Cannot enable change tracking on table ''. Change tracking requires a primary key on the table.

Before going to error, let me first give a brief idea on what change tracking is.

As per Microsoft "Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. Typically, to enable applications to query for changes to data in a database and access information that is related to the changes, application developers had to implement custom change tracking mechanisms."

Error: The backup data at the end of "" is incorrectly formatted

Sometimes, your backup jobs or adhoc backup might fail with below error

"The backup data at the end of "" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. [SQLSTATE 42000] (Error 3266)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

T-SQL To Get Count Of Records In All Tables In All Databases In SQL Server

DECLARE @DatabaseName VARCHAR(100), @MySQLCmd VARCHAR(4000)
create table #MyTempTab ([servername] nvarchar(128), [db_name] nvarchar(128), [schema] nvarchar(128), [table] nvarchar(128), 
[record_count] int)
DECLARE MyDB_Cur CURSOR FOR select [name] from sys.databases order by [name]
OPEN MyDB_Cur
FETCH NEXT FROM MyDB_Cur into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN        
    SET @MySQLCmd = 'USE '+@DatabaseName+';
    INSERT INTO #MYTEMPTAB
    SELECT DISTINCT @@SERVERNAME, DB_NAME(), SCHEMA_NAME(T.[SCHEMA_ID]), T.[NAME], P.[ROWS] FROM SYS.TABLES AS T INNER JOIN 

How To Get the Publication Name of a Replicated Table

When replication is configured for too many tables, it might be sometime time taking to search for the publication name when you want to do some alteration.

Below is small piece of code which will give you the publication name for required table.

SELECT spub.name as [Publication_Name],
sart.name as [TableName],
sart.dest_table as [DestinationTable_Name] FROM
syspublications spub 
INNER JOIN
sysarticles sart ON spub.pubid = sart.pubid
WHERE sart.name ='YourTableName'

How To Change SQL Server Service Account Password

With continuation to my previous post, here are the step by step instruction to change SQL Server Service Account Password, It can be SQL Server Engine Service Account or Agent Account or SSIS account etc in case if you want to change password or change the account itself. The Procedure is same for you.

1. Open SQL Server Configuration Manager.

2. Click on SQL Server Services Tab on Left panel and In right side click on the SQL Server Service for which you want to change password/account and click on properties.

Pages