If backups are taken in silence can a recovery still take place?

SQL Studies

T-SQL Tuesday 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

SSMS’s Clipboard Manager

SQL Studies

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

Common mistake using try/catch constructs

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

Happy Learning!

How to use insert query to store images


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 *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;

 

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:

image_tbl