MSDTC on server 'servername' is unavailable

By default, when a stand-alone instance of Microsoft SQL Server exists in a cluster environment, the SQL Server-based instance is set to start automatically. If the host node is rebooted, you may receive the following error message when you issue commands that are related to distributed transactions:

ERROR: MSDTC on server 'servername' is unavailable.

On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

Disable/Enable all trigger in a database

To Disable All the Triggers
sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

To Enable All the Triggers
sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

How to identify when a SQL Server database was restored

To know when a database is restored run the below query in the database.

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Meanings of the column

destination_database_name  -   The name of the database that has been restored.
restore_date   -    The time at which the restore command was started.
backup_start_date  -  The time at which the backup command was started.
backup_finish_date  -  The time at which the backup command completed.
source_database_name  -  The name of the database after it was restored.
backup_file_used_for_restore  -  The file(s) that the restore used in the RESTORE command.

Windows could not start the SQL Server (MSSQLSERVER) service on Local Computer.Error 1069: The service did not start due to a logon failure.

I was getting the same error since I had changed the password of my computer. I have changed in the services after that it is working fine.

Go to Run-->services.msc--> SQl Server(MSSQLSERVER) . Right click --> Properties -->  Click on "Log on" tab. I have changes the password and retype the current windows password again. It works.

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

While running a project I got this error:
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
Execution of user code in the .NET Framework inside the database is disabled.
Execute this TSQL:  "sp_configure 'clr enabled',1" and "RECONFIGURE"
So to fix this run this TSQL inside the database.
sp_configure 'clr enabled',1
GO
RECONFIGURE

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'AdventureWorks2008R2'. You should correct this situation by resetting the owner of database 'AdventureWorks2008R2' using the ALTER AUTHORIZATION statement.

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'

They should return you same SID values in the format of a GUID.

Now if the two SID's differ which they did in my case it means that you need to reset the database owner so that both values are the same. To do this you can run another ALTER statement and pass in the owner value you want to use e.g

use AdventureWorks2008R2

ALTER AUTHORIZATION ON Database::AdventureWorks2008R2 TO sa

or

DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO [<<LoginName>>]'
SELECT @Command = REPLACE(REPLACE(@Command,'<<DatabaseName>>',SD.Name), '<<LoginName>>', SL.Name)
FROM master..sysdatabases SD
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

PRINT @Command
EXEC(@Command)

The problem will be fixed.

How To Generate Scripts of all tables with data in sql server 2008

MS SQL Server 2008 has new Generate Scripts option which enables sql programmers to script data in SQL Server database tables. SQL developers can script data from sql tables into a script file, to the clipboard or script data on a new sql query window. Script data can be used to export and/or import table data from one database to another database.

The Script Data option creates INSERT statements foreach row in the table using the column data that the related table record has.

Requirement:
I have a database name as “sanjeet”. In that database some tables are there. My requirement is to generate scripts of all tables,tables data and all stored procedure present in that database.

Solution:
1. Open sql server. Right click on the database name and open the context menu, choose Tasks menu and open submenu. Select Generate Script submenu item from the displayed list.

2.      Click on Next.

3.     If you want to select particular table and stored procedure script then select "Select Specific Database Object" option.Then select your required table and procedure from below. Click on next.

4.     If you want to generate table structure script with data then click on advance option.

      

 5.    Choose "Schema and Object" option.

6.    Output Option screen in the Generate Script Wizard is the screen where a sql administrator or a programmer can make a selection among the existing output options. The script generator can create the desired scripts in the forms of a file, also can split the automatic generated script per object basis, or define the file as a unicode file or in ANSI text. A database developer can also select the file name and the output file folder for the script engine to create and place the script file. Click on Next.

7.     Click on Next.

  

8.      Generate Script Progress screen displays the status of the scripting operation. If an error occurs sql developers and administrators can find the error details on this screen. If everything runs without any error and does not fail, you will see Success status for each scripting action on the progress screen.

9.      Since as the output option for the scripting, the New Query Window is selected, the final script is displayed on the SQL Server Management Studio Query Editor window as shown below.

      

       

Find Column Name in Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE 'Date%'
ORDER BY schema_name, table_name

How to know any table/function is used any query

select * from sys.sql_modules where definition like '%tablename/funName%'

How to find the Procedure Created and Modified date

SELECT name, create_date, modify_date FROM sys.objects
WHERE type = 'P'
order by 3 desc

Import/Export data with SQL Server 2005 Express

Go to Run "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

Thats it.. Now you can transfer data using the DTS wizard.

Follow the below steps if you want to integrate it with the Sql server management studio express UI .
1. Open sql server management studio express.
2. Select Tools -> External Tools
3. Add a Title and Browse C:\Program Files\Microsoft SQL Server\90 \DTS\Binn\DTSWizard.exe for the Command field.
3. Click OK

Delete duplicate rows

SET ROWCOUNT 1
delete emp from emp a where (select count(*) from emp b where a.name=b.name) >1
WHILE @@rowcount > 0
delete emp from emp a where (select count(*) from emp b where a.name=b.name) >1
SET ROWCOUNT 0

or


DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

or



WITH empTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EmpName,Position ORDER BY EmpName) As RowNumber,* FROM emp
)
DELETE FROM  empTable  where RowNumber >1
SELECT * FROM emp order by Id asc

Non Clustered indexes per tabel

In Sql Server 2008 32 Bit addition ,following are the limit for Non Cluster Index and Cluster Index

Clustered indexes per table: 1

None clustered per table: 999

Nested sub queries: 32

Nested trigger levels: 32

Parameters per stored procedure: 2100

Parameters per user-defined function: 210

Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

In our example we will consider three main conditions while we merge this two tables.

1.    Delete the records whose marks are more than 250.

2.    Update marks and add 25 to each as internals if records exist.

3.    Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);

There are two very important points to remember while using MERGE statement.

·         Semicolon is mandatory after the merge statement.

·         When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

Store XML data into database using stored procedure :

XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:
3
6
15

Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.

Alter PROCEDURE SelectByIdList(@productIds xml) AS
begin
DECLARE @Products TABLE (ID int)

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
select * from @Products
end

To test the stored procedure run the below command:

     EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

In the page:
//To create a xml file write the below code

public static string BuildXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();
    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("<value>{0}</value>", values[i]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);
    return xmlString.ToString();
}

SQL Server Function to Split Comma in Text

create FUNCTION [dbo].[Fn_Split]
(
 @RowData nvarchar(max),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 While (Charindex(@SplitOn,@RowData)>0)
 Begin
  Insert Into @RtnValue (data)
  Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
  Set @Cnt = @Cnt + 1
 End
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 Return
END

To run the code type :

select * from  dbo.Fn_Split ('Item 1,Item 2,Item 3,Item 4,Item 5,Item 6',',' )

Hope this helps