Comma Separated Values in SQL Server

Comma Separated Values in SQL Server

select substring(

(select ','+j.jobsdescription from jobdetails J

order by j.jobid

FOR XML PATH('')),2,500) AS CDM

Date import from excel sheet to SQL Server

                                    Insert Excel sheet values to SQL Server 2005

First of all Turn on the adhoc Distributed Quries in your SQL Server using below statement

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

SELECT * 

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

                'Excel 8.0;Database=D:\SQL\JobDetails.xls',

                'SELECT * FROM [Sheet1$]')

Now u can view the excel data in your SQL SERVER DB.

If u want to insert values as table use as same below

SELECT * into jobdetails

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

                'Excel 8.0;Database=D:\SQL\JobDetails.xls',

                'SELECT * FROM [Sheet1$]')

select * from jobdetails

How do we change db schema to dbo?

How do we change any database schema owner to dbo?

select * from [Production].[Location]

ALTER SCHEMA DBO TRANSFER Production.Location

select * from Location----------return rows

select * from dbo.Location------return rows

select * from [Production].[Location]-----no rows return  due to schema changed from production  to dbo

To Understand the SQL Server NOLOCK hint

Example

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)

Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20

Let's say another user runs the below query in a transaction. The query completes and updates the records, but it is not yet committed to the database so the records are locked.

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.

-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20

If I run sp_who2 I can see that the SELECT statement is being blocked. I will need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete. For this example I am going to cancel the SELECT query.

To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

If you notice below the Suffix column now has "B" for all records. This is because the UPDATE in window 1 updated these records. Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data. If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1.

If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.

-- run in query window 1
ROLLBACK

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database. If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

---

Locks

So what kind of locking is used when the NOLOCK hint is used.

If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock. (To get the lock information I ran sp_lock in another query window while this was running.)

SELECT * FROM Person.Contact WHERE ContactID < 20

If we do the same for our SELECT with the NOLOCK we can see these locks.

SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

The differences are that there is a "S" shared access lock that is put on the page (PAG) that we are reading for the first 19 rows of data in the table when we don't use NOLOCK. Also, we are getting a Sch-S lock versus an IS lock for the table (TAB).

So another thing to point out is that even when you just SELECT data SQL Server still creates a lock to make sure the data is consistent.

These are the lock types and the lock modes that are used for the above two queries.

Lock Types

• MD - metadata lock
• DB - database lock
• TAB - table lock
• PAG - page lock

Mode

• S - Shared access
• Sch-S - Schema stability makes sure the schema is not changed while object is in use
• IS - Intent shared indicates intention to use S locks

---

READUNCOMMITED

The NOLOCK hint is the same as the READUNCOMMITED hint and can be used as follows with the same results.

SELECT * FROM Person.Contact WITH (READUNCOMMITTED)

---

SELECT statements only

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

---

Schema Change Blocking

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.

-- run in query window 1
BEGIN TRAN
ALTER TABLE Person.Contact ADD column_b VARCHAR(20) NULL ;

If we try to run our SELECT statement it will be blocked until the above is committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

---

Issues

We mentioned above how you can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

• Dirty Reads - this occurs when updates are done, so the data you select could be different.
• Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
• Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

To learn more about these issues read this article: http://msdn.microsoft.com/en-us/library/ms190805.aspx

---

Isolation Level

You can also set the Isolation Level for all queries instead of using the NOLOCK or READUNCOMMITTED hint. The isolation level will apply the READUNCOMMITTED to all SELECT statements that are performed from when this is turned on until it is turned off.

In the example below, the two SELECT statements will use the READUNCOMMITED or NOLOCK hint and the UPDATE will still function as normal. This way you can set a whole batch of statements instead of modifying each query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

SELECT * FROM Person.Contact WHERE ContactID < 20

UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1

SELECT * FROM Person.Contact WHERE ContactID < 20

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Important T-SQL commands to know LDF and MDF file name and shrinking File

Important T-SQL commands to know LDF and MDF file name and shrinking 

file

---To know the size of Database and free space in MB------------

SELECT DB_NAME(database_id) AS DatabaseName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)[Free Space in MB]
FROM sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)

sp_helpdb 'northwind'

SELECT 12480/1024 as GB


--------------To know the logical file name like MDF and LDF file----------------------

select * from sys.databases
where name='northwind'

SELECT * FROM sys.database_files

sp_helpfile 'northwind'


----------------------------To take the backup of db and log backup---------------------

BACKUP DATABASE northwind
TO DISK = 'F:\northwind_bk.bak'
--
BACKUP LOG northwind
TO DISK = 'F:\northwind_Log.bak'

----------To alter the Database Recovery model/switch the recovery model---
ALTER DATABASE northwind
SET RECOVERY SIMPLE;
GO
 --Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Northwind_log, 1);
GO
 --Reset the database recovery model.
ALTER DATABASE northwind
SET RECOVERY FULL;

-------------------------------Shrinking whole DB -------------------

DBCC SHRINKDATABASE (northwind)

------------To know db status----------------------------


select name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases 
where name='northwind'
--where log_reuse_wait_desc<>'NOTHING'
--where log_reuse_wait_desc='REPLICATION'
----where log_reuse_wait_desc='LOG_BACKUP'
--where log_reuse_wait_desc='CHECKPOINT'

Join SQL Server tables where columns include NULL values

When building database tables you are faced with the decision of whether to allow NULL values or to not allow NULL values in your columns.  By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set.  This is not necessarily a bad thing, but dealing with NULL values especially when joining tables can become a challenge.  Let's take a look at this issue and how this can be resolved.

Solution
In most cases your columns in your tables probably allow NULL values unless you consciously changed them when you created a table or changed the default setting for your database by using the SET ANSI_NULL_DEFAULT OFF and SET ANSI_NULL_DFLT_ON OFF option.  These settings change the behavior of how a table is created if you do not specify NULL or NOT NULL when creating a table.

The following examples show you how a table would get created differently with each of these options, but by default SQL Server sets columns to allow nulls.

Example 1: When these two options are OFF the default nullable value is set to no, meaning no null values are acceptable.

ALTER DATABASE AdventureWorks SET ANSI_NULL_DEFAULT OFF; GO SET ANSI_NULL_DFLT_ON OFF; GO CREATE TABLE Table1 (a TINYINT); GO sp_help Table1 GO

The screen shot below shows that the column does not allow NULLs.

---

Example 2: When these two options are ON the default nullable value is set to yes, meaning null values are acceptable.

ALTER DATABASE AdventureWorks SET ANSI_NULL_DEFAULT ON; GO SET ANSI_NULL_DFLT_ON ON; GO CREATE TABLE Table2 (a TINYINT); GO sp_help Table2 GO

The screen shot below shows that the this column does allow NULLs.

---

So now that we have that covered let's get down to the issue at hand.  So based on how tables and columns are setup and how data is stored you may run into an issue where you have data stored in tables that have NULL values and you need to join on these values.  Sounds pretty easy, but let's see what happens when this occurs.

Setup Test

First let's create two tables and load some sample data.  (This is not a very practical example, but it does help illustrate the issue.)

CREATE TABLE [dbo].[CarModels]( [Make] [varchar](50) NULL, [Model] [varchar](50) NULL, [Trim] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Orders]( [Make] [varchar](50) NULL, [Model] [varchar](50) NULL, [Trim] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO dbo.Orders VALUES ('Nissan','Altima','2-door 2.5 S Coupe') INSERT INTO dbo.Orders VALUES ('Nissan','Altima','4-door 3.5 SE Sedan') INSERT INTO dbo.Orders VALUES ('Nissan','Altima','') INSERT INTO dbo.Orders VALUES ('Nissan','Altima',NULL) INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','2-door 2.5 S Coupe') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','2-door 3.5 SE Coupe') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 2.5 S Sedan') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 3.5 SE Sedan') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 3.5 SL Sedan') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door HYBRID Sedan') INSERT INTO dbo.CarModels VALUES ('Nissan','Altima',NULL)

---

Selecting data

The first thing we will do is show the data in these two tables.  The idea here is that we want to join the tables based on Make, Model and Trim.  For most of the records there is some value, but there are a few records where the Trim value is NULL.

SELECT * FROM dbo.Orders a SELECT * FROM dbo.CarModels b

The first query does a straight join of these tables based on all three columns.

SELECT * FROM dbo.Orders a INNER JOIN dbo.CarModels b ON a.Make = b.Make AND a.Model = b.Model AND a.Trim = b.Trim

The result of the above query matches only three of the four records from the Orders table. The records that have a NULL value for Trim do not show up in the resultset.

This next example introduces the ISNULL function in the join clause.  The ISNULL function takes two parameters, the first is the value to interrogate to see if it is NULL and it if is NULL the second parameter specifies what the value should be converted to.  So in this example if any value is NULL it will be converted to a space ''.

SELECT * FROM dbo.Orders a INNER JOIN dbo.CarModels b ON a.Make = b.Make AND a.Model = b.Model AND isnull(a.Trim,'') = isnull(b.Trim,'')

In the results below we can see that we now have more rows, but one of the issues we are facing is that there are also values of space in the table and therefore we are picking up additional joins that we do not want.  The rows that should not be included are highlighted below.

To take this a step further we are using the ISNULL function again, but this time we are converting the NULL values to '999999'.  This is a value that we know does not exist in our table and therefore will not cause any unwanted joins.

SELECT * FROM dbo.Orders a INNER JOIN dbo.CarModels b ON a.Make = b.Make AND a.Model = b.Model AND isnull(a.Trim,'999999') = isnull(b.Trim,'999999')

Here is our final result with the four rows we are expecting.

---

Summary

As we have seen from the above examples joining NULL values does not work.  Even though you have two NULL values SQL Server does not treat these as the same value.  Internally a value of NULL is an unknown value and therefore SQL Server does not equate an unknown value being equal to another unknown value.  Another design decision is to not allow NULL values and therefore you will not run into these issues.

Next Steps

• When dealing with NULL values don't forget about the ISNULL function
• Be aware when creating tables whether you want to allow NULL values or not.  By allowing NULL values you introduce other issues that you may need to face at a later time.
• Although the ISNULL function is handy, if you are doing large joins having this function in the join clause will slow down the query. A lot of this will also depend on the indexes you have setup and how the indexes are used for the join.  In general it is not a good idea to use functions in your joins or on the left side of your WHERE clause, because SQL Server needs to interrogate each value and therefore may negate the use of the index.   Although in some cases there are no other options, so you need to do what you need to do.

How To Fix “MSDTC on server ‘server name’ is unavailable” Error

How To Fix “MSDTC on server ‘server name’ is unavailable” Error

These errors are happening when u install or upgrade latest .Net Framework 4 or higher from lower.

So after installation of .Net Framework kindly go to see event viewer in your server

Right click-->My Computer and click manage to see event viewer

And see the installation start time and end time and if MSDTC shows error then follows following steps to fix that error

When I used the .NET TransactionScope object to control a transaction in a web page the following error message greeted me

————————————————————————————————————————

MSDTC on server ‘GopinathM’ is unavailable.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: MSDTC on server ‘GopinathM’ is unavailable.

————————————————————————————————————————

This error occurs when the “Distributed Transaction Coordinator” service is not running. To start a distributed transactions with TransactionScope object, the windows service “Distributed Transaction Coordinator” should be running.

To fix the problem just start the service “Distributed Transaction Coordinator” using Windows Service manager.

Here are the detailed steps for starting the service

1. Click on Start–>Control Panel->Administrative Tools->Services (or simply type services.msc in the run command box and hit enter); display “Services” manager
2. Scroll through the list and identify the service “Distributed Transaction Coordinator”
3. Right on the service and choose “Start”

When SQL Server and the Application Server are on different boxes

The above said solution work well when SQL Server and the Application are on the same box. If they are deployed on different boxes then additionally you should follow these steps to correct the problem

1. Click Start–>Control Panel –> Adminstrative Tools –> Component Services –> Computers
2. Right click on My Computer and choose  Properties option
3. Switch to MS DTC tab and check allow remote access

Repeat these steps on Application Server and Database server