auto commit transaction

Auto commit Transaction

 

I have  many scripts files for a DB migration. My intension is to combine all scripts into a single file so any developer can easily run this file when they want to migrate database. So I kept all the scripts separating with batch (GO) into a single file. While testing, I ran that file by pressing F5). I got some error message and also some row effected messages.

Problem: Now the problem is ‘is Roll Backed all the statements of that BATCH where statement got failed? Or all remaining statements of that failed batch are committed?’ My database session is in Autocommit mode.

 

  Solution :

In auto commit mode, it sometimes appears as if an instance of the Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error. A compile error prevents the Database Engine from building an execution plan, so nothing in the batch is executed. Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. In the following example, none of the INSERT statements in the third batch are executed because of a compile error. It appears that the first two INSERT statements are rolled back when they are never executed.

 

 

 

In the following example, the third INSERT statement generates a run-time duplicate primary key error. The first two INSERT statements are successful and committed, so they remain after the run-time error.

 

 

 

 

The Database Engine uses deferred name resolution, in which object names are not resolved until execution time. In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

 

 

 

 

 

Refrence: MSDN 

 

 

 

Delete all the foreign key constraints that are refrenced to a specific table

 

       When I was working with one of my migration project I faced this problem, we have to drop one master table that associated with several child tables and we have to recreate this master table with new structure.  The script is already Witten by some developer, the process they are following is: Creating one temp table same as master table putting all the data into temp table from master table, dropping all constraints using hard code (directly mentioning the name of constraints) , then dropping the master table and recreating the master table, also recreating the all constraints and taking back the data from temp table to master table using some conversion process.

     Problem: Developer are created some new child tables and relations, so while dropping the table I faced problem, its not allowing me to drop the table due to new relations .

Solution: Now I am using following scripts to drop all the constraints dynamically. So even developer add some new constraints it will handle.

We can also create all dropped constraints dynamically.

  

Note : Please don't use this scripts on production database.

 

 

 

 

 

--Query to create drop constraints
select 'ALTER TABLE '+ OBJECT_NAME(fkc.parent_object_id)
 +'  DROP CONSTRAINT '+OBJECT_NAME(fkc.constraint_object_id)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.parent_object_id = fkc.parent_object_id and fk.referenced_object_id = fkc.referenced_object_id
inner join sys.tables tb on fk.parent_object_id = tb.object_id
inner join sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
inner join sys.columns col1 on fkc.referenced_object_id = col1.object_id and fkc.referenced_column_id = col1.column_id
where  fk.referenced_object_id = object_id('mastertable_name')


--Query to Add constraints
select 'ALTER TABLE '+ OBJECT_NAME(fkc.parent_object_id)
 +' ADD CONSTRAINT '+OBJECT_NAME(fkc.constraint_object_id)
 +' FOREIGN KEY ('
 + col.name 
 + ') REFERENCES ' + OBJECT_NAME(fkc.referenced_object_id)
 +'(' + col1.name +')'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.parent_object_id = fkc.parent_object_id and fk.referenced_object_id = fkc.referenced_object_id
inner join sys.tables tb on fk.parent_object_id = tb.object_id
inner join sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
inner join sys.columns col1 on fkc.referenced_object_id = col1.object_id and fkc.referenced_column_id = col1.column_id
where  fk.referenced_object_id = object_id('MasterTable_name')

 

 

 

Orphaned User

Orphaned Users

Definition: A user which is not mapped with any of the Logins is called Orphaned user.  

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Note : A SQL Server login cannot access a database in which it lacks a corresponding database user unless guest is enabled in that database.

  We have our database "sales" on our integration environment. This database has user with name
"mylogin" and this server has login "mylogin" which is mapped with this user i.e. (user: "mylogin") --> (login:"mylogin"). Application is using this login ( specified in web.config file) to use Sales database.

  Following script will give  SId for login and user : 
  SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323

SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323

Here both sid are same .

We got new backup of Sales database from production environment, we have to restore it on integration environment. After restore this on integration environment I try to use my application , Application giving exception, application is not able to access Sales database using "mylogin" user ID,
even we have both login and user with name "mylogin".

 I checked  the SID for login "mylogin"  and user "mylogin" using following script:


SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
  --sid :0xCBA915F7B9C23C4386E0918C13409323

SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0x260AA0D3041901478956EA9EFA377FC9
  Here we are getting different values for SID.

I ran the following script to shutout this problem:

Syntax: sp_change_users_login 'update_one' ,<login name >,<username> 
Example :
sp_change_users_login 'update_one' ,'mylogin','mylogin'



-- Now check once again after executing above query
SELECT * FROM sys.sql_logins WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323


SELECT * FROM sys.database_principals WHERE name = 'mylogin'
--sid :0xCBA915F7B9C23C4386E0918C13409323
  Here we are getting same SID. Now I am able to connect with my database.

•   Using following system sp we can list all the orphan users.

               sp_change_users_login 'report'