My friend Andy Mallon (b/t) is hosting T-SQL Tuesday this month. In case you aren’t aware T-SQL Tuesday is a blog party started by Adam Machanic (b/t) almost 7 years ago. Each month someone selects a topic and hosts the “party”. Then whoever is interested posts a blog on that topic. It can be a great way to get a good grounding on a subject as seen by a bunch of different bloggers or start your own blog.
Regardless, Andy’s topic this month is We’re still dealing with the same problems. The idea is that we’ve been dealing with the same problems for 20 years or more. Of course this being T-SQL Tuesday he wants a database spin.
So let’s talk backups.
We take backups for multiple reasons. One of the big reasons is to help us fix day to day…
View original post 219 more words
The other day Richie Rump (b/t) mentioned something called a clipboard manager on twitter. I’ll admit I had to ask what exactly they meant, but once it was described to me I realized it was something I’d wanted off and on for years. Basically it’s a tool that stores multiple copies in an extended clipboard. So you can copy several pieces of text over time and then paste the one you want. Justin Dearing (b/t) and Richie mentioned a Clipboard Manager called Ditto Clipboard Manager. Kendal Van Dyke (b/t) however mentioned one built into SSMS! Now, it only covers what’s currently in the clipboard and what’s been copied in SSMS but since I spend most of my time in SSMS (I even frequently use it as a text editor) that’s just fine. Once you’ve copied several…
View original post 28 more words
As per BOL, A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. This is a common thing which a developer ignores and get into a trap. Let’s try to understand with an example code.
Here is a sample code, where exception raised by select statement executed in try block will never be caught by catch block:
create table test_tbl (id int); go begin try select * from test_t; -- mistakenly typed wrong table name end try begin catch print 'In catch block - exception handled!'; end catch -- #Output -- Msg 208, Level 16, State 1, Line 2 -- Invalid object name 'test_t'.
Here is another example, showing the correct usage of try/catch construct – using divide-by-zero error.
begin try select 1/0; -- divide by zero error end try begin catch print 'In catch block - exception handled!'; end catch -- #Output -- (0 row(s) affected) -- In catch block - exception handled!
Please refer below link for more details: Books On-Line
Here is the quick tip to export schema without data. This is doable with option “CONTENT=METADATA_ONLY”.
Here is the sample example for the same.
EXPDP user_name/password DIRECTORY=backup_directory SCHEMAS=schema_name TABLESPACE=tablespace_name CONTENT=metadata_only
Go thru the following link for more details -> EXPDP (CONTENT)
I have been usually asked the question on how to insert images using insert statements. Here is the way to do that…
Let’s create a table to store images having two columns one using IMAGE data type and other using VARBINARY data type. My intention here is to demonstrate that image can be stored in either of the two types in same manner.CREATE TABLE image_tbl ( img_varbinary VARBINARY(max), img_image IMAGE ); GO
We shall now use OPENROWSET with BULK mode to read the image data. BULK mode allows us to interpret and read the image data in a binary stream. This is where we take the advantage and insert the data as a binary stream into our table.
In the first example we shall look at queries inserting in column with VARBINARY data type using JPEG and PNG image.INSERT INTO image_tbl(img_varbinary) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA; INSERT INTO image_tbl(img_varbinary) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;
Next, we shall look at queries inserting data in column with IMAGE data type using same JPEG and PNG images.INSERT INTO image_tbl(img_image) SELECT *
INSERT INTO image_tbl(img_image) SELECT * FROM OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;
Here is how the output looks like:
Come and join me on 29th June, 2013 [Saturday] @ C# Corner Chapter Meet. This is the first ever C# Corner Chapter event being organized at Chandigarh “The City Beautiful”.
I shall be taking a session on New Functions in SQL Server 2012. There would be interesting session on jQuery and WCF conducted by my fellow speakers Anil Kumar and Raghavendra respectively.
Date: 29th June,2013 [Saturday]
Mansa Devi Complex, Sector-4,
(Near Rajiv Gandhi IT Park, Chandigarh)
Locate on Google Map
This event is sponsored by:
SSS & Educational Society ,Gorakhpur and C#Corner.
Finally!! SQL Server 2014 has been released after the recent announcements earlier this month. Community Technology Preview 1 [CTP1] is available for download, link below.
Download >> SQL Server 2014 CTP1
This release has been targeted around In-Memory computing capabilities along with Cloud Computing.
Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) installation limitations:
- Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
- To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
- The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
- The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
- The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
- The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
- The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
- The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.
Today we shall try to see how to upgrade database from SQL Server 2008 R2 to SQL Server 2012 using Detach and Attach T-SQL commands.
Let’s first create a sample database say “DB_SQL2008R2” using SQL 2008 and create a test table (with some values)
USE master GO -- creating sample datbase CREATE DATABASE DB_SQL2008R2 GO USE DB_SQL2008R2 GO -- creating test table with values CREATE TABLE Test_table (col1 VARCHAR(10), col2 VARCHAR(10)); INSERT INTO dbo.Test_table ( col1, col2 ) VALUES ('A1','B1'), ('A2','B2'), ('A3','B3'), ('A4','B4'), ('A5','B5'); GO
Take note of the data file and log file created and the location (this is required as we are going to attach these files to SQL Server 2012 later). In my test server, they are created at:
Data File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf
Log File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf
How do I get the data and log file name?
Go to “Object Explorer” window in Sql Server management studio. Expand the database folder and look for “DB_SQL2008R2”. Right-click and choose “Properties”. Database properties window will open. Select the “Files” page on the left. On the right, you will find two files DB_SQL2008R2 and DB_SQL2008R2_log. The location of these files is mentioned under the “Path” column header.
So, we are all set for upgrade!
First, step is to detach the database. Open SSMS for SQL Server 2008R2 and execute the following code snippet to detach the database
USE master; GO EXEC sp_detach_db @dbname = N'SQLTraining'; GO
Now login to SSMS for SQL Server 2012 and create the database using the data and log files we have taken not of earlier.
USE master; GO CREATE DATABASE DB_SQL2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf'), (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf') FOR ATTACH; GO
So finally, we have been successful in upgrading a database from SQL Server 2008R2 to SQL Server 2012
For further reading please refer MSDN: Upgrade a Database Using Detach and Attach (Transact-SQL)
Whilst, we are still exploring SQL Server 2012, Microsoft has announced features of SQL Server 2014. With most fingers in Cloud (Cloud computing), the the new product combines together to deliver “In-the-box” in-memory capabilities, hybrid cloud capabilities for mission critical applications and permits faster data insights.
One of the core components of SQL Server 2014 is built to make most of “Cloud computing”. This helps in scalability (nearly infinite) and also reduces the computational and storage cost by many folds.
In-memory computing has been in the market for some time now, “In-memory in the box” takes the game to next level. Unlike other in-memory products which require separate dedicated software and hardware; SQL Server 2014 comes with Hekaton’s in-memory OLTP which is delivered in the box with the product.
Here are few links to explore more on the product..
- SQL Server Blog “SQL Server 2014: Unlocking Real-Time Insights“
- Blog by Brent Ozar (its always interesting to read his blogs) “Everything You Need to Know About SQL Server 2014”
- Microsoft Official Link for “SQL Server 2014” – register yourself here to get notification on trial version of the product