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.
Recent blog posts
- MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools
- How to Tune Microsoft SQL Server for Performance
- sql database tuning for developers
- Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object ''. The duplicate key value is ()
- Cannot enable change tracking on table ''. Change tracking requires a primary key on the table.
- Error: The backup data at the end of "" is incorrectly formatted
- T-SQL To Get Count Of Records In All Tables In All Databases In SQL Server
- How To Create SQL Server Audit Feature
- How To Get the Publication Name of a Replicated Table
- How To Change SQL Server Service Account Password
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.
Database tuning can be an incredibly difficult task, particularly when working with large-scale data where even the most minor change can have a dramatic (positive or negative) impact on performance.
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
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."
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).
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
As DBA Its very important to Audit your SQL Server Instance as much as administrating and monitoring it.
Below is the Piece of Code which will create a SQL Server Level Audit and also code to how to read the Audit logs.
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'
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.