How can I open an .MDF file in SQL Server? sql server 2008

Attaching .MDF file in SQL Server 

Let’s say someone wants to give you their SQL Server database. Maybe they want you to manage it, troubleshoot it, or learn from it. So they hand you a .MDF file and .LDF file and tell you that you need SQL server 2008 R2 to open them. So you install SQL Server 2008 R2 Express (which, to your relief, is free). You open SQL Server Management Studio(SSMS), and you try to open the .MDF file. You get the following error:




There is no editor available for 'C:\Temp\MyDatabase.mdf'. Make sure the application for file type (.mdf) is installed.
The Solution:

.MDF files are SQL Server database files and .LDF files are the associated log files. But you can’t really “open” them. Instead, you have to “attach” to them. Once you attach, you’ll see the database in the object explorer of SSMS.
If you’re thinking, “Why wasn’t the Backup and Restore process used instead of passing around these database files?” It’s a valid question, but it doesn’t help the reader staring at .MDF and .LDF files with no clue how to access them. However, if you need to move a database around, you should do a search on “backup restore attach detach sql server” (without the quotes) in your favorite search engine. You’ll get lots of opinions to help you decide the best approach.

How to Attach in a Perfect World:


Click here to watch a video on this...

There’s a couple of different methods. I’ll give you the “easy” way through SQL Server Management Studio (SSMS) and then point you to other methods using T-SQL queries.
Launch SSMS.
Connect to your SQL Server Instance.
Right-click on Databases in the Object Explorer.
Click Attach.
In the Attach Databases window, click the Addbutton.
Navigate to the directory containing the .MDF and .LDF files.
Select the .MDF file, and press OK.
Press OK again to attach the database.

You should see the database appear in the Databases node (press F5 if you don’t). You can now explore the data using SSMS. These same steps are documented in the MSDN here. I’m just summarizing to make it easier for you.

There are two ways to accomplish the same thing in a T-SQL script. First, you can use thesp_attach_db stored procedure. This is shown here. Or you can use the CREATE DATABASE command with the FOR ATTACH argument.
So, the World isn’t Perfect

If your database is successfully attached, then stop reading and go use it. But if you see any errors or unexpected behaviors, I’ll try to share some troubleshooting suggestions.

First, you may get a generic error like the following..


An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

You might be asking, “Where’s the hyperlink?” I’ll show you in the next screenshot (circled in red), and it will also be our first example.
1. Access Denied:
Attach database failed for Server ###. (Microsoft.SqlServer.Smo) Unable to open the physical file “####”. Operating system error 5: “5(Access is denied.)”. (Microsoft SQL Server, Error 5120)

This access denied error could have a few different causes. Basically, you don’t have the required access to the .MDF or .LDF files. This can happen if you get the file from someone else. When they detach the database file, the file permissions are changed to give only that user full control. Also, even if you see that the file has full control for the Administrators group, it may not be enough. Remember in Windows 7/Vista, those permissions are often only realized when you’re running an application as an Administrator.

So here are several choices to fix it:
The easiest solution is to close SSMS and then run it as an Administrator. Perform the attach as an Administrator, and it’s likely going to work.
Another solution is to explicitly grant full control to the .MDF and .LDF files to your user account. This can be done by right-clicking the files, selecting Properties, and modifying the Security tab.
A final solution is to copy the files to the default directory for your other database files. To find out what that is, you can use the sp_helpfile procedure in SSMS. On my machine it is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. By copying the files to this directory, they automatically get permissions applied that will allow the attach to succeed.

2. Access Denied Variation 2

There is another variation of the Access Denied message that has a simple solution:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '####'. (Microsoft SQL Server, Error: 5123)

This error means that the file is already opened exclusively by another application. The most likely cause is that this database file is already attached to an instance of SQL Server. Double-check your list of databases to see whether it is already in the list. It’s also possible for applications to use .MDF files directly with a feature called User Instances. If an application is using a .MDF file in this way, then it would have to close before you could attach the to that database file.

3. Database is Read-only

This is not so much an error as an undesired result. When you load the database, it is in a read-only state. You can tell by the gray shading of the databasee icon (not to mention the “Read-Only” label next to it).



The most probable cause is that the .MDF file you attached to is read-only. Just right-click on the .MDF and .LDF files, select properties, and then uncheck the Read-only check box.


4. Unable to Downgrade

The next error happens when you try to attach a database of a higher version of SQL Server (like SQL Server 2008 R2) to a SQL Server Instance of a lower version (like SQL Server 2005).


Attach database failed for Server '####'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The database '####' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported. Could not open a new database '####'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

This has never been supported. You can attach a database of an older version of SQL Server to a newer version, but you can’t go the other direction.

Read More »

Using SP_MSFOREACHTABLE in sql server for updating common column in all tables

--------------- Query for updating in the existing table common column in all the tables -----------------

EXEC SP_MSFOREACHTABLE'
DECLARE @TBLNAME VARCHAR(255);
SET @TBLNAME =  PARSENAME("?",1);
DECLARE @SQL NVARCHAR(1000);

IF EXISTS(
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @TBLNAME AND COLUMN_NAME = ''ISDELETED''
)
BEGIN      
SET @SQL = N''ALTER TABLE '' +  @TBLNAME + N'' ALTER COLUMN ISDELETED BIT NOT NULL;''      
EXEC SP_EXECUTESQL @SQL
END'

Read More »

Using Top n, WITH TIES, and PERCENT in sql server

DECLARE @TEST TABLE(
ID int, Amount int)
INSERT INTO @TEST VALUES
(1,10),(2,30),(3,60),
(4,50),(5,50),(6,70),
(7,20),(8,70),(9,70),
(10,44),(11,80),(12,90)

select * from @TEST

select Top 3 ID, Amount from @TEST ORDER BY Amount desc

select Top 3 With TIES ID, Amount from @TEST ORDER BY Amount desc

select Top 3 PERCENT ID, Amount from @TEST ORDER BY Amount desc

Read More »

Example using merge query in sql server in 2008

-- Create test table and primary key
CREATE TABLE test (col1 INT NOT NULL, col2 VARCHAR(100))
go
ALTER TABLE dbo.test ADD CONSTRAINT
 PK_test PRIMARY KEY CLUSTERED
 (
 col1
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
                ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- Populate table
DECLARE @val INT
SELECT @val=1
WHILE @val < 100000
BEGIN
   INSERT INTO dbo.test(col1, col2) VALUES(@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END
GO

-- test SELECT record then INSERT or UPDATE based on result of SELECT statement (record exists)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DECLARE @cnt INTEGER
SELECT @cnt=COUNT(1) FROM test WHERE col1=2
IF @cnt > 0
  UPDATE test SET col2='update2' WHERE col1=2
ELSE
  INSERT INTO test VALUES (2,'update2')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test SELECT record then INSERT or UPDATE based on result of SELECT statement (record does not exist)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DECLARE @cnt INTEGER
select @cnt=COUNT(1) from test WHERE col1=100001
IF @cnt > 0
  UPDATE test SET col2='update100001' WHERE col1=100001
ELSE
  INSERT INTO test VALUES (100001,'update100001')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test UPDATE record then SELECT update rowcount and INSERT based on result of SELECT statement (record exists)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DECLARE @cnt INTEGER
UPDATE test SET col2='update4' WHERE col1=4
SELECT @cnt=@@ROWCOUNT
IF @cnt < 1
  INSERT INTO test VALUES (4,'update4')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test UPDATE record then SELECT update rowcount and INSERT based on result of SELECT statement (record does not exist)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DECLARE @cnt INTEGER
UPDATE test SET col2='update100004' WHERE col1=100004
select @cnt=@@ROWCOUNT
IF @cnt < 1
  INSERT INTO test VALUES (100004,'update100004')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test DELETE record then INSERT (record exists)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DELETE FROM test WHERE col1=40000
INSERT INTO test VALUES (40000,'update40000')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test DELETE record then INSERT (record does not exist)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
DELETE FROM test WHERE col1=100002
INSERT INTO test VALUES (100002,'update100002')
GO
sp_lock
GO
COMMIT TRANSACTION

-- test MERGE record (record exists)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
MERGE test AS TARGET
USING (SELECT 50000 as col1,'update50000' as col2) AS SOURCE
ON (TARGET.col1=SOURCE.col1)
WHEN MATCHED THEN
  UPDATE SET TARGET.col2=SOURCE.col2
WHEN NOT MATCHED THEN
  INSERT (col1,col2) VALUES (SOURCE.col1,SOURCE.col2);
GO
sp_lock
GO
COMMIT TRANSACTION

--select * from test

-- test MERGE record (record does not exist)
DBCC DROPCLEANBUFFERS
GO
BEGIN TRANSACTION
MERGE test AS TARGET
USING (SELECT 100003 as col1,'update100003' as col2) AS SOURCE
ON (TARGET.col1=SOURCE.col1)
WHEN MATCHED THEN
  UPDATE SET TARGET.col2=SOURCE.col2
WHEN NOT MATCHED THEN
  INSERT (col1,col2) VALUES (SOURCE.col1,SOURCE.col2);
GO
sp_lock
GO
COMMIT TRANSACTION
-- cleanup
DROP TABLE test
GO

Read More »

Simple site search from sql server stored procedures

GO
/****** Object:  StoredProcedure [dbo].[Ameriprise_SP_Get_Data_Search]    Script Date: 08/12/2013 11:24:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Create proc [dbo].[SP_Get_Data_Search]
 @SearchText varchar(4000)
as
begin
(
select
ac.MenuCode as pageId,
ac.Displayname as Title,
acd.MetaDescription as [Description],
case when ac.ParentMenuCode=0 and ac.Module=0
then
'L1.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid=0&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.MenuCode=1 then 'index.aspx' else case when ac.ParentMenuCode<>0 and ac.Module=0
then
'L2.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.ParentMenuCode=0 and ac.Module=1
then
'AboutUs.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
else
case when ac.ParentMenuCode<>0 and ac.Module=1
then
'AU.aspx?menuid='+CAST(ac.MenuCode AS VARCHAR)+'&parentmenuid='+CAST(ac.ParentMenuCode AS VARCHAR)+'&rootlevel='+CAST(ac.Rootlevel AS VARCHAR)
end
end
end
end
end as [Path],
convert(int,ac.ParentMenuCode) as MatchCount,
convert(decimal,ac.ParentMenuCode) as  Size
from Contentmenu ac inner join ContentDescription acd
on ac.ContentID = acd.ContentID
where ContentHeading like '%'+ @SearchText +'%' or  ContentDescription like '%'+ @SearchText +'%')
 end

GO

Read More »

Drop all stored procedures tables Views in sql server database

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Read More »

TRIGGERS IN SQL SERVER

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

After Insert Trigger
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'
GO

insert into Employee_Test values('yogesh',2500);

exec InsertEmployeeDetails 'Naresh','4000'
create proc InsertEmployeeDetails
@empname varchar(100),
@empsal decimal(10,2)
as
begin
insert into Employee_Test values(@empname,@empsal)
end
select * from Employee_Test_Audit

AFTER UPDATE Trigger
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'
GO

select * from Employee_Test_Audit
update Employee_Test set Emp_Sal=6600 where Emp_ID=6

AFTER DELETE Trigger
CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'
GO

select * from Employee_Test_Audit
delete Employee_Test where Emp_Id = '7'

Instead Of Triggers
ALTER TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;

BEGIN
if(@emp_sal>4000)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO

delete from Employee_Test where Emp_ID=8

Read More »

Using table value parameter in stored procedure in sql server

Table Valued Parameter In SQL SERVER and .NET (C#):

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.
Table-valued parameters are a new parameter type in SQL Server 2008. It allows you to pass read-only table variables into a stored procedure. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

We can apply a common example to bulk insert data using one round trip.

User-Defined Table Type

When first taking a look at the new table-valued parameters, I thought that using this feature is a bit complicated. There are several steps involved. The first thing to do is to define a table type. If you look at the Types section under Programmability in the 2008 Management Studio, you will see the new “User-Defined Table Types” (Image 1).



Creating a table

CREATE TABLE [dbo].[tblUsingTableValuedParameter](

[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL,
CONSTRAINT [PK_tblUsingTableValuedParameter] PRIMARY KEY CLUSTERED
([ID] ASC
)) ON [PRIMARY]
Creating a table value parameter

CREATE TYPE [dbo].[tvp_tblUsingTableValuedParameter] AS TABLE(
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL
)

Using Table Value Parameter in T-SQL

The following is an quick example that declares an instance of the table type an populates it with some values. Later we will use C# to create a data table that will populate an instance of a table type.

Declare instance of tvp_tblUsingTableValuedParameter Table Type

DECLARE @tvp tvp_tblUsingTableValuedParameter
-- Add some sample values into our instance of Table Type.
INSERT INTO @tvp (ID, Name, Address)
VALUES
(1, 'Harish Bagdwal', 'Almora Uttarakhand'),
(2, 'Pankaj Paneru', 'Haldwani Uttarakhand'),
(3, 'Pankaj Upadhyay', 'Ramnagar Uttarakhand'),
(4, 'Dilip Rawat', 'Gurgaon Haryana'),
(5, 'Kamal Kant Joshi', 'Shrinagar Uttarakhand'),
(6, 'Narendra Mehta', 'Almora Uttarakhand') 

Show values that exist in table type instance.

SELECT * FROM @tvp

Using table value parameter in stored procedures

In order to use table value parameters in a stored procedure you have to declare an instance of the table type and specify it as read-only. It is mandatory for the table value parameter to be read-only so you cannot modify the data inside of the table type variable inside the stored procedure.

CREATE PROCEDURE [dbo].[uspInserttblUsingTableValuedParameter]
@tvp tvp_tblUsingTableValuedParameter READONLY
AS
BEGIN
INSERT INTO dbo.tblUsingTableValuedParameter (ID, Name, Address)
SELECT ID, Name, Address
FROM @tvp
END

Calling Table Valued Parameter from a .Net Application. How can we use this table value parameter in C Sharp dot net C# ?

Table Value Parameters in .NET (C#)

The following code below generates a data table in C#. A data table is a common data type used to simulate a table. This data table will be used as our table value parameter and will be used by the stored procedure created above. The data table is not the only type that can be used for table value parameters in C#. The DataReader and list types are also acceptable.

DataTable dtTvp; // create data table to insert items

dtTvp= new DataTable("Items");

dtTvp.Columns.Add("ID",typeof(string));

dtTvp.Columns.Add("Name", typeof(string));

dtTvp.Columns.Add("Address", typeof(string));

dtTvp.Rows.Add(1, "Name1", "Address1"); 

dtTvp.Rows.Add(2, "Name1", "Address1"); 

dtTvp.Rows.Add(3, "Name1", "Address1"); 

dtTvp.Rows.Add(4, "Name1", "Address1"); 

dtTvp.Rows.Add(5, "Name1", "Address1"); 

Creating sql connection to pass table value parameter

SqlConnection con; // modify connection string to connect to your database
string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;";
con = new SqlConnection(conStr);
con.Open();
using (con)
{
// Configure the SqlCommand and SqlParameter.
SqlCommand sqlCmd = new SqlCommand("dbo.uspInserttblUsingTableValuedParameter", con);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@tvp", dtTvp); //Needed TVP tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP sqlCmd.ExecuteNonQuery();
}
con.Close();

Conclusion

The table-valued parameter feature introduced with SQL Server 2008 has been sorely needed. It will allow developers to write much better performing applications by decreasing round-trips to the server and by letting SQL Server work how it does best – on sets of data. It is not difficult to use once all the pieces are put into place.

Few Example:

SQL Server (2008) Passing Table Parameter to Stored Procedure

1. Define Custom Data Type Matches the table structure

Let's name this custom data type NewMemberTable. We declare it like this.

CREATE TYPE NewMemberTable AS TABLE
(
UsrName nvarchar(50) PRIMARY KEY
, FullName nvarchar(50) NOT NULL
, EmailAddy nvarchar(254) NOT NULL
);

Execute that command and you will get a new data type named NewMemberTable.

2. Create Stored Procedure Accepting Table-valued Parameter

CREATE PROCEDURE [dbo].[ImportMembers]

@NewMembers NewMemberTable READONLY

AS

BEGIN

DECLARE @UsrName nvarchar(50);
DECLARE @FullName nvarchar(50);
DECLARE @EmailAddy nvarchar(50);
DECLARE @MemberID int;
DECLARE @EmailID int;

--Declaring cursor for each row in table value parameter

DECLARE NewMembersCursor CURSOR FAST_FORWARD
FOR SELECT UsrName, FullName, EmailAddy FROM @NewMembers;

OPEN NewMembersCursor;
FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy

WHILE @@FETCH_STATUS=0
BEGIN

INSERT INTO dbo.Members(UsrName, FullName) VALUES(@UsrName, @FullName); 

SELECT @MemberID=SCOPE_IDENTITY();

SET @EmailID=(SELECT [ID] FROM dbo.EmailAddresses WHERE [Address]=@EmailAddy);

IF @EmailID IS NULL
BEGIN

INSERT INTO dbo.EmailAddresses([Address], RefCount) VALUES(@EmailAddy, 0);

SELECT @EmailID=SCOPE_IDENTITY();
END;

UPDATE dbo.EmailAddresses SET RefCount=RefCount+1 WHERE [ID]=@EmailID;

INSERT INTO dbo.MemberEmailAddresses(Member, EmailAddy, Active) VALUES(@MemberID, @EmailID, 1);

FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy
END;

CLOSE NewMembersCursor;
DEALLOCATE NewMembersCursor;
END;
___________________________________________________________________________
Example-

CREATE TYPE [dbo].[tvpProductMaster] AS TABLE(
[Id] [int] NOT NULL,
[ProductVariantName] [nvarchar](150) NULL,
[ProductVariantId] [nvarchar](150) NULL,
[ProductName] [nvarchar](150) NULL,
[ProductId] [nvarchar](150) NULL,
[active] [bit] NULL,
[createdon] [nvarchar](50) NULL,
[createdby] [nvarchar](50) NULL,
[updatedon] [nvarchar](50) NULL,
[updatedby] [nvarchar](50) NULL
)
GO

DECLARE @tvpParam AS tvpProductMaster
INSERT @tvpParam  
SELECT 253,  
  N'255/55 R 17 104V IIII' ,  
  N'13b07409-cd6a-e211-a3f1-d4ae528ba060',  
  N'LATITUDE TOUR HP',  
  N'f471a4b9-c96a-e211-a3f1-d4ae528ba060',  
  0,  
   '03/20/2014',  
   'Yogesh',  
   '03/20/2014',  
   'Yogesh'
   exec SP_InsertUpdateProductMaster @tvpParam
  set identity_insert ProductMaster on
  select * from @tvpParam
  select * from ProductMaster

__________________________________________________________________________
Example 2

SQL Server (2008) Passing Table Parameter to Stored Procedure

1. Define Custom Data Type Matches the table structure

Let's name this custom data type NewMemberTable. We declare it like this.


CREATE TYPE NewMemberTable AS TABLE
(
UsrName nvarchar(50) PRIMARY KEY
, FullName nvarchar(50) NOT NULL
, EmailAddy nvarchar(254) NOT NULL
);

Execute that command and you will get a new data type named NewMemberTable.

2. Create Stored Procedure Accepting Table-valued Parameter

CREATE PROCEDURE [dbo].[ImportMembers]
@NewMembers NewMemberTable READONLY
AS
BEGIN
DECLARE @UsrName nvarchar(50);
DECLARE @FullName nvarchar(50);
DECLARE @EmailAddy nvarchar(50);
DECLARE @MemberID int;
DECLARE @EmailID int;

DECLARE NewMembersCursor CURSOR FAST_FORWARD
FOR SELECT UsrName, FullName, EmailAddy FROM @NewMembers;

OPEN NewMembersCursor;
FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy

WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO dbo.Members(UsrName, FullName)
VALUES(@UsrName, @FullName);
SELECT @MemberID=SCOPE_IDENTITY();

SET @EmailID=(SELECT [ID] FROM dbo.EmailAddresses WHERE [Address]=@EmailAddy);

IF @EmailID IS NULL
BEGIN
INSERT INTO dbo.EmailAddresses([Address], RefCount)
VALUES(@EmailAddy, 0);
SELECT @EmailID=SCOPE_IDENTITY();
END;

UPDATE dbo.EmailAddresses SET RefCount=RefCount+1 WHERE [ID]=@EmailID;

INSERT INTO dbo.MemberEmailAddresses(Member, EmailAddy, Active)
VALUES(@MemberID, @EmailID, 1);

FETCH NEXT FROM NewMembersCursor
INTO @UsrName, @FullName, @EmailAddy
END;

CLOSE NewMembersCursor;
DEALLOCATE NewMembersCursor;
END;

Read More »

A history back to stored procedures in sql server

A complete list what we needed to create a stored procedures ?

Creating a database for test :

CREATE DATABASE StoredProcDemo
GO
USE StoredProcDemo
GO

Create Table Customer
(
CustomerID int Identity(1,1) NOT NULL,
Name Varchar(100) Not NULL,
DateOfBirth DateTime,
City Varchar(50),
State Varchar(50)
)
GO

Inserting records in tables:

INSERT INTO Customer
VALUES('Customer1','06/18/2000','Bangalore','Karnataka')

INSERT INTO Customer
VALUES('Customer2','06/10/1972','Pune','Maharastra')

INSERT INTO Customer
VALUES('Customer3','01/18/1975','Mysore','Karnataka')

INSERT INTO Customer
VALUES('Customer4','06/06/1974','Chennai','TamilNadu')

INSERT INTO Customer
VALUES('Customer5','06/18/2001','Bangalore','Karnataka')

GO

Creating a simple stored procedure:

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
Select * FROM CUSTOMER
END
----------------------------------------------

How to execute stored procedure ?

GetAllCustomers
OR
Exec GetAllCustomers
-------------------------------------

To check the content of an existing stored procedure:
sp_helptext  GetAllCustomers
--------------------------------------------------------

Stored procedure with parameters: 

CREATE PROCEDURE GetCityCustomers
@CityName varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
GO
------------------------------------

Execute/call a Stored Procedure with Parameter:

EXEC GetCityCustomers 'Bangalore'
--OR
GetCityCustomers 'Bangalore'
--OR
GetCityCustomers @CityName = 'Bangalore'
---------------------------------------------------

--Default Parameter Values:---
ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = 'BANGALORE'
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
----------------------------

Return all the customer detail:

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = NULL
AS
BEGIN
SELECT *
FROM Customer
WHERE (@CityName IS NULL OR City = @CityName)
END
----------------------------------

Store Procedure with Multiple Parameters:

Create PROCEDURE GetCustomersByCityAndState
@CityName varchar(50),
@State Varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE State = @State AND City = @CityName
END
----------------------------------

EXEC GetCustomersByCityAndState 'Bangalore','Karnataka'
--OR--
EXEC GetCustomersByCityAndState @CityName = 'Bangalore',@State ='Karnataka'
--OR--
EXEC GetCustomersByCityAndState @State ='Karnataka',@CityName = 'Bangalore'

--How to Drop an Existing Stored Procedure:---
DROP PROCEDURE GetCustomersByCityAndState
---------------------------------------------

Stored procedure with OutPut Parameters:

Create PROCEDURE GetNoOfCustomersByCity
@CityName varchar(50),
@NoofCustomers int OutPut
AS
BEGIN
SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName
END
GO
--------------------------------------------

Executing output parameter: 

Declare @cnt INT
EXEC GetNoOfCustomersByCity 'Bangalore',@cnt OUTPUT
PRINT @cnt
------------------------------

RETURN Parameter:

Create PROCEDURE GetNoOfCustomersByCityWithReturnParam
@CityName varchar(50)
AS
BEGIN
DECLARE @NoofCustomers INT

SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName

RETURN @NoofCustomers
END
GO
---------------------

Declare @cnt as int
EXEC @cnt  = GetNoOfCustomersByCityWithReturnParam 'Bangalore'
PRINT @cnt

Stored Procedure for Data Manipulation:

Create PROCEDURE AddCustomer
@Name Varchar(50),
@CityName varchar(50),
@DOB DATETIME
AS
BEGIN
INSERT INTO CUSTOMER (NAME,City,DateOfBirth)
VALUES (@Name,@CityName,@DOB)
END
GO
-------------------------------------------

Let us use the below statement to insert record in the Customer table:

EXEC AddCustomer 'Raj','Bangalore','10/23/1988'

Enjoy!

Read More »

Pagination using sql server database OR Server side paging using sql server

Pagination using sql server database
OR
Paging from sql server database
OR
Server side paging using sql server

First i am creating a table for testing purpose:

CREATE TABLE [dbo].[tblCustomers](

[CustomerID] [nchar](5) NOT NULL,

[CompanyName] [nvarchar](40) NOT NULL,

[ContactName] [nvarchar](30) NULL,

[ContactTitle] [nvarchar](30) NULL,

[Address] [nvarchar](60) NULL,

[City] [nvarchar](15) NULL,

[Region] [nvarchar](15) NULL,

[PostalCode] [nvarchar](10) NULL,

[Country] [nvarchar](15) NULL,

[Phone] [nvarchar](24) NULL,

[Fax] [nvarchar](24) NULL,

CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED
(

[CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


Insering records in this tables:

DECLARE @max AS INT, @rc AS INT;

SET @max = 100;

SET @rc = 1;

WHILE @rc <= @max

BEGIN

INSERT INTO tblCustomers

(CustomerID,CompanyName,ContactName,ContactTitle,

Address,City,Region,PostalCode,Country,Phone,Fax)

VALUES

(@rc,'Company'+ CAST(@rc AS VARCHAR),'Name'+CAST(@rc AS VARCHAR),'Title'+CAST(@rc AS VARCHAR),

'Address'+CAST(@rc AS VARCHAR),'Cuty'+CAST(@rc AS VARCHAR),'Region'+CAST(@rc AS VARCHAR),

'pin'+CAST(@rc AS VARCHAR),'Country'+CAST(@rc AS VARCHAR),'Mobile'+CAST(@rc AS VARCHAR),

'FAX'+CAST(@rc AS VARCHAR))

SET @rc = @rc + 1;

END    
Checking out all the rows effected:


SELECT * FROM tblCustomers ORDER BY CAST(CustomerID AS INT) DESC
Creating stored procedures for the pagination:

CREATE PROCEDURE [dbo].[GetCustomersPageWise]

@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT

AS

BEGIN

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER(

ORDER BY [CustomerID] ASC

)AS RowNumber

,[CustomerID]

,[CompanyName]

,[ContactName]

,[City]

,[Country]

,[PostalCode]

,[Phone]

,[Fax]

INTO #Results

FROM [tblCustomers]

DECLARE @RecordCount INT

SELECT @RecordCount = COUNT(*) FROM #Results

SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))

PRINT @PageCount

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

END

Executing stored procedures:

For paging 10 records per page 

exec GetCustomersPageWise '1','10',''

For paging 20 records per page

exec GetCustomersPageWise '1','20',''

Read More »

Using XML Path in sql server 2008 for merge duplication rows in one row

Using XML Path with two different SQL function function REPLACE and STUFF
or

STUFF AND REPLACE FOR XML PATH 

String Concatenation

or 
Using XML PATH MERGE DUPLICATE IN ONE ROW

First i am creating a test table:

create table tblSubjects (id int identity(1,1), PersonID int, Unit varchar(10))

Inserting record in to the table:

insert into tblSubjects values (1,'Che')
insert into tblSubjects  values (1,'Mat')
insert into tblSubjects  values (1,'Phy')
insert into tblSubjects values (2,'Che2')
insert into tblSubjects  values (2,'Mat2')
insert into tblSubjects  values (2,'Phy2')
insert into tblSubjects  values (3,'Phy3')

Checking out record inserted:

select * from tblSubjects

Using XML PATH with REPLACE(path, 'oldstring', 'newstring') 

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

We will discuss about these two sql function REPLACE and STUFF and more about XML PATH in my upcoming post .

SELECT t1.PersonID,
Units =REPLACE(
(SELECT Unit AS [data()] FROM tblSubjects t2
WHERE t2.PersonID = t1.PersonID ORDER BY Unit
FOR XML PATH(''))
, ' '
, ','
 )
FROM tblSubjects t1
GROUP BY PersonID ;

Using XML PATH with STUFF ( character_expression , start , length , replaceWith_expression )

SELECT DISTINCT PersonID,
STUFF(
(SELECT ',' + t.Unit FROM tblSubjects AS t
WHERE t.PersonID = tblSubjects.PersonID FOR XML PATH(''))
, 1
, 1
, ''
) AS Unit
FROM tblSubjects
ORDER BY PersonID

RESULT:

Read More »

Useful sql server user defined functions split function

User defined function :-

Split function

Creating user defined function for splitting string with any delimiter like comma(,) colon(:) or any special characters separated.

CREATE FUNCTION [udf_Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
        declare @idx int
        declare @slice varchar(8000)
 

        select @idx = 1
                if len(@String)<1 is="" nbsp="" null="" or="" p="" return="" tring=""> 

        while @idx!= 0
        begin
                set @idx = charindex(@Delimiter,@String)
                if @idx!=0
                        set @slice = left(@String,@idx - 1)
                else
                        set @slice = @String
             

                if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)


                set @String = right(@String,len(@String) - @idx)
                if len(@String) = 0 break
        end
return
end

How to use ?

SELECT items FROM dbo.udf_Split('Yogesh,Chandra,Upreti', ',')

Read More »