/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Foot Ball World Cup 2014 Live stream

Friday, June 27, 2014

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 »

Wednesday, June 18, 2014

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 »

Recent Posts

My Blog List