Posted on

Read and Write BLOB objects on SQL Server

I had faced couple of cases where I need to store files inside the SQL Server rather than storing these files on the file system. for the cases I faced, I compared the value of storing the files inside SQL server versus storing those files on the file system and start interact with the file system and I found going with storing these files in SQL server is much valuable for me which may be the same case for you as well.

To store the files on sql server do the followings:

SELECT TOP 1 *

FROM OPENROWSET (BULK N’<FilePath>’, SINGLE_BLOB) TblName

the above statement will read the file contents and convert it into Binary format (which is the format of BLOB on DB). Make sure to replace <FilePath> with the correct path at your DB Server. meaning this path should be a valid path on DB Server.

after reading the file contents; you can push this content into your DB Tables using ordinary Insert Statement.

Insert into Tbl (FileContent)

SELECT TOP 1 *

FROM OPENROWSET (BULK N’<FilePath>’, SINGLE_BLOB) TblName

To Read file Contents from DB follow the following steps:
1. Open Command Prompt on SQL Server

2. Type the following Command:

 

C:>bcp “<SQL STATMENT>” queryout <OutputFilePath> -T –d <DBNAME>

make sure to replace the following placeholders:

  • <SQL STATEMENT> by your SQL Statement that retrieving the file contents
  • <OutputFilePath> the destination file path including filename
  • <DBNAME> your DB Name

3. There will be couple of questions , answer them as below
     Enter the file storage type of field Media [image]: <Enter>
        Enter prefix-length of field Media [4]: 0
        Enter length of field Media [0]: <Enter>
        Enter field terminator [none]: <Enter>
    Do you want to save this format information in a file? [Y/n] y

 

 

I hope you will find this article helpful… Please let me know what do you think by posting your comments

Posted on

Enable Remote Access for MS SQL Express Edition

By Default when you install SQL Server express edition on a machine you will not be able to connect to it  from another machine by using Microsoft SQL Server Management Studio or application. You need to do specific steps to enable SQL Server Express to accept connections from remote clients.

Recently I read a nice article posted to CodeKicks site about this issue, I would strongly recommend reading that article, to read it click hear

Posted on

How to Know the Execution Time of SQL Statements in Simple Way

When client report to you a performance issue in certain area on your system, you start troubleshooting and tackling the issue. the performance bottleneck might be everywhere on your system, it might be on front-end, business layer, data access layer, … etc. The scope of this article is to check the execution time taken for certain SQL statement till you get the result out from the DB. this would  very helpful if the performance issue is at  data retrieval.  after figuring out the performance bottleneck is at data retrieval, the solution might be as easy as adding index for some of table’s columns. but in most cases you might get into a trouble of changing the body of SQL statements to have better performance. so you need a tool to assess your changes. SQL 2008 IDE shipped with a built-in tool that I found so much helpful and made my life much easier.

if you notice there is a small icon on tool bar called “Include Client Statistics”, it is by default not enabled. if you enable it and try to execute a sql statement, you will get a new tab at the result panel. below is snapshot of the new tab on the result panel

 

building

 

SQLClientStaticsResultPanel

 

Notice the statics between different trials. it will keep the statics up to 10 trials. The statics including with some visual graphics to let you note the effect of your changes weather it increase the execution time or it reduced it.
Try it and you will find it so useful!

Posted on

Date different in SQL server Vs Oracle

 

While setting back in my chair today and thinking of the power that MS tools giving to us , I decided to do a very small compare of things I can do in SQL server and how can I do the same thing in Oracle.

I decided to do a very primitive operation on both DBs… something is very silly. Just to get the differences between 2 dates in Weeks. This is very easy thing

In SQL Server I could do that in single line of SQL Statement

SELECT DATEDIFF (ww, ’03/20/1983′, ’11/03/2010′) DiffInWeeks_SQLServer

While I could do the same operation in Oracle by a long equation comparing with the one I have for SQL Serer:

SELECT   (to_date(’03/20/1983′,’mm/dd/yyyy’)  – to_date(’11/03/2010′,’mm/dd/yyyy’) 

             )  / 7.0) DiffInWeeks_Oracle

FROM DUAL;

 

I still like working on Microsoft platforms.