Monday, October 31, 2005

Encryption in SQL Server 7.0

After a long time I visited CNUG (Chennai .NET User Group) . When I was going through the questions I saw a question posted by "Nitin" asking Is it possible to encrypt data within data server (SQL Server 7.0)

URL of that post can be seen here >> http://groups.msn.com/ChennaiNetUserGroup/general.msnw?action=get_message&mview=0&ID_Message=9327&all_topics=0

My response to that post:

There are two undocumented functions in SQL Server (since SQL Server 6.5). They are:

1. Pwdencrypt and
2. Pwdcompare.

Pwdencrypt -- It uses one way encryption. That is, it takes a string and returns a encrypted version of that string. Pls note that in one way encryption you can't get back the actual string (i.e., you can decrypt the encrypted data).

Pwdcompare -- It compares an unencrypted string to its encrypted representation to see whether they match.

Since it is undocumented functions there is a possibility that MS can remove or change it at anytime without prior notice. So use it at your own risk.

Code snippet:

---Sample table to hold the encrypted password
Create Table tstSQLEncryption(EmailId varchar(50),Pwd varbinary(255))

--Encrypt password and save it into the table
Insert Into tstSQLEncryption (EmailId, Pwd) Values ('vmvadivel@yahoo.com', pwdencrypt('test'))

--Compare the encrypted value and the value you give newly
Select Pwdcompare('test',Pwd ) from tstSQLEncryption where EmailId = 'vmvadivel@yahoo.com'

If the above select statement returns 1 then it means the value you entered and the value already encrypted are one and the same. Hope this is helps!

Sunday, October 30, 2005

Saving images as BLOB into SQL Server 2005

In this article we would look into the easiest way of importing an image as BLOB content into a SQL table.

1. Openrowset has new bulk features introduced in SQL Server 2005.

2. Openrowset supports bulk operations through a built-in bulk provider that allows data from a file to be read and returned as a rowset.

3. Using the BULK rowset provider you can load a file into a table's column using regular DML.

4. Unlike SQL Server 2000, instead of being limited to Text, NText and Image datatypes for large objects, in SQL Server 2005 we can also use Varchar(max), nvarchar(max) and Varbinary(max) datatypes. The new MAX option allows you to manipulate large objects the same way you manipulate regular datatypes

5. With OPENROWSET you'll be able to return a rowset from a file as a single varbinary(max), varchar(max) or nvarchar(max) data type value. We'll use "SINGLE_BLOB", "SINGLE_CLOB" or "SINGLE_NCLOB" to diffentiate what kind of single-row, single-column data is being read.

Sample table structure:


CREATE TABLE EmployeeProfile
(
EmpId INT,
EmpName VARCHAR(50) not null,
EmpPhoto VARBINARY(MAX) not null
)
GO


Import image into this table:

Openrowset with the Bulk option requires a correlation name (also known as a range variable or alias) in the FROM clause.


INSERT EmployeeProfile (EmpId, EmpName, EmpPhoto)
SELECT 1001, 'Vadivel', BulkColumn
FROM OPENROWSET( BULK 'C:\Sample.gif', Single_Blob) AS EmployeePicture
GO


I suggest to read http://msdn2.microsoft.com/en-us/library/ms175915.aspx to know more about "BCP, Bulk insert, and Openrowset (Bulk)".

Bill Gates in MTV!!

I came to know about "Notorious B.G on MTV" from my favourite bloggers blogspace >> http://scobleizer.wordpress.com/2005/10/29/why-do-i-work-at-microsoft/ I went through the complete transcript @ http://www.mtv.com/thinkmtv/features/education/gates_forum/ and the below QA is what I liked the most.

Yago: Before we reach that day, certainly I know a lot of people in high school and college are hearing a lot about how India and China will take over a lot of American jobs. What do you say to that generation of young people now that's in college, that's now in high school or approaching high school?

Gates: India and China advancing and getting rich is fantastic news. What that means is that people who have been living in poverty, had ill health and illiteracy, are now getting jobs that allow them to be educated and realize their potential. If we had a choice today where India and China would be as rich as the United States, we should all want that, because not only would it be great for them, but they'd be buying more of our products. ... Their advancing isn't taking away from a finite pool of jobs. What it does is it grows the global economy. It does mean that we have to renew our skills, renew our leadership, and that largely means investing in the education system. So it doesn't have to be a bad thing, it just highlights that we've underinvested in education and in fact other countries do a better job.

Hmm why did I like it .. might be because I am born and brought up in India.

Paging records using SQL Server 2005

In this article we would look into basics of paging records in SQL Server 2005. I have provided couple of methods with correspondng code snippets for our better understanding.

Code snippet for the sample table:

Create Table tstSQLPaging
(
Sno int,
FirstName varchar(50),
LastName varchar(50),
EmailId varchar(100),
Salary int
)
Go

Enter sample data into that table:

Insert into tstSQLPaging values (1, 'Vadivel','M','vmvadivel@yahoo.com',10000)
Insert into tstSQLPaging values (2, 'Sailakshmi','L','abc@yahoo.com',9000)
Insert into tstSQLPaging values (3, 'Raj','A','aRaj@yahoo.com',11000)
Insert into tstSQLPaging values (4, 'Dhina','B','bDhina@yahoo.com',25000)
Insert into tstSQLPaging values (5, 'Siddharth','s','itissiddhu@yahoo.com',6000)
Insert into tstSQLPaging values (6, 'Vicky','L','vicky@yahoo.com',19000)
Insert into tstSQLPaging values (7, 'Tejas','P','tp@yahoo.com',22000)
Insert into tstSQLPaging values (8, 'Revathy','L','rev@yahoo.com',4000)
Go

Method 1:

Extract from MSDN ::
Row_number() - Returns a running count of all rows in the specified scope.


With the help Row_Number function we get the running count of all the rows and using the between clause we filter the records as per our need.

With SQLPaging As
(
Select Row_number() Over (Order by Sno) as RunningNumber, FirstName, LastName, EmailId
from tstSQLPaging
)
Select * from SQLPaging
where RunningNumber between 2 and 3


Method 2:

This method is much more advanced (!!), we get the page size and page number as input and then with the help of Row_Number function filter out data pertaining that particular page.

Create Proc uspFetchPagedData
(
@pPageSize as int,
@pPageNum int
)
As

With SQLPaging2
As
(
Select Top(@pPageSize * @pPageNum) ROW_NUMBER() Over (Order by Sno) as RunningNumber,
FirstName, LastName, EmailId
From tstSQLPaging
)
Select * from SQLPaging2 where RunningNumber > ((@pPageNum - 1) * @pPageSize)

Testing:

i) Exec uspFetchPagedData 6,2
ii) Exec uspFetchPagedData 5,2

Saturday, October 29, 2005

Exposing SQL Server 2005 data via a Web Service

In this article we would see how SQL Server 2005 has been powered to create Webservices with ease. Implementing real webserices in SQL Server 2005 is officially called HTTP Endpoints. Ok without fasting any further time lets start to understand HTTP Endpoint by getting our hands dirty.

Open SQL Server 2005

Use Adventureworks
go


--Lets create a sample table populate it with few test data
Create Table WSDemo (Sno int, EmpName varchar(50), EmpMailId varchar(100))

Insert into WSDemo Values (1, 'Vadivel','vmvadivel@yahoo.com')
Insert into WSDemo Values (1, 'Velias','smart3a@yahoo.com')

--- Stored procedure which would return all employee name with their corresponding email address.
Create Procedure dbo.FetchWSDemo
As
Select EmpName, EmpMailId From WSDemo
Go

--The actual WS work happens here
Create EndPoint FetchWSDemo
State = Started
As HTTP
(
Path = '/SQLWSDemo',
Authentication = (INTEGRATED),
Ports = (Clear),
Site = 'localhost')
For Soap
(
Webmethod 'FetchEmployeeData'
(Name ='AdventureWorks.dbo.FetchWSDemo'),
Batches = Disabled,
Wsdl = DEFAULT,
Database = 'AdventureWorks',
Namespace = 'http://AdventureWorks/SQLWSDemo'
)
go

Points to note:

1) “State” can take one among the below three arguments:
Started— listening and responding
Disabled — neither listening nor responding
Stopped — listening, but returns errors to client requests.

2) Path – This clause specifies the URL on the server that clients will use to consume this Web service.
3) Authentication -- This clause specifies how clients will authenticate themselves to the SQL Server. For ex:
i) Integrated – most secure.
ii) Digest is not as secure as Integrated. You should use it only if Integrated authentication is not possible.
iii) Basic authentication is the least secure. It requires SSL as the Port value.

4) Ports – Clear
5) Site – The server name where the Web service is running

Open VS.NET 2005

Lets now create a client app to consume our webservice.

1. Create a new C# based windows application
2. Right click on the project name and choose Add >> Add web reference.
3. Type http://thinkingms.com/vadivel/ct.ashx?id=5e4247f6-13a0-4f44-a8cc-132e6e6fc816&url=http%3a%2f%2flocalhost%2fSQLWSDemo%3fwsdl and click on GO button. You will the webmethod come up in the list.

4. By default the name would be "localhost" change it to SQLWebServiceDemo
5. On the Form1 drag and drop a button and a Listview control.
6. Double click on the Button control and copy-paste the below code into it.

private void button1_Click(object sender, EventArgs e)
{
// Let us create a new instance of our web service
SQLWebServiceDemo.FetchWSDemo objWS = new SQLWebServiceDemo.FetchWSDemo();

/* We have to pass in credentials to authenticate into our service. Lets use the same credentials we have logged into our computer.*/
objWS.Credentials = System.Net.CredentialCache.DefaultCredentials;
DataSet objDS = new DataSet();
object[] objEmpData = objWS.FetchEmployeeData();

// iterate over the object array with a foreach statement,
// testing for the first object of type DataSet
foreach (object objArr in objEmpData)
{
if (objArr is DataSet) objDS = (DataSet)objArr;
}
listBox1.DataSource = objDS.Tables[0];
listBox1.DisplayMember = "EmpName";
listBox1.ValueMember = "EmpMailId";
}

Test:

Now we are all set to test our work. Just press F5 and click on the button to see the data being populated into the Listbox through our webservice/webmethod.

Monday, October 24, 2005

Why can't we create a folder by name "CON", "prn" etc., in Windows?

Many a times in the last couple of months I got mails or rather spams :) saying we can't create folder in the name of "CON", "PRN" etc., Instead of responding to them individually (that is what I was doing all this while) I have created this blog post.

Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/naming_a_file.asp to understand what are all the reserved words using which you can't create folders in Windows.

That said, go to dos prompt and try out md\\.\c:\con ... to your surprise a folder by name "con" would have been created in your c drive!!

Sunday, October 23, 2005

Cross Apply and Outer Apply in SQL Server 2005

In this article I would talk about the New APPLY operator in SQL Server 2005. As usual I would provide a working sample / code snippet for better understanding.

Extract from BOL:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Lets see this in action:

Let us create couple of tables and populate with some test data.

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)Go

Create Table Loan
(
LoanId int,
EmpId int,
LoanAmount int,
Month varchar(3)
)
Go

Insert Test data:

Insert into EmployeeMaster values (1,'Vadivel',29,'I')
Insert into EmployeeMaster values (2,'Sai',28,'I')
Insert into EmployeeMaster values(3, 'Velias',20, 'II')

Insert into Loan values(1,1,2000,'Jan')
Insert into Loan values(1,2,1000,'Feb')
Insert into Loan values(1,1,1000,'Feb')
Insert into Loan values(1,1,100,'Mar')
Insert into Loan values(1,2,1700,'Jun')
Insert into Loan values(1,1,800,'Aug')

Cross Apply:

Let see how to make use of Cross Apply operator in the below code snippet

Select E.EmpName, A.LoanAmount from EmployeeMaster E
CROSS APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000


Outer Apply:

Let see how to make use of Outer Apply operator in the below code snippet

Select C.EmpName, A.LoanAmount from EmployeeMaster E
OUTER APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000
Velias NULL

About sp_readerrorlog

In this article let us understand the system stored procedure Sp_readerrorlog in detail. Sp_readerrorlog is a system stored procedure which can be used to read the SQL Server error logs from within T-SQL.

Syntax of sp_readerrorlog

Parameter 1 : Non zero integer value
Parameter 2 : File name
Parameter 3 : Line number in the file
Parameter 4 : Search string

1. Code snippet to display the content of the current event log
Execute sp_readerrorlog

2. Code snippet to display the content of eventlog.1
EXEC sp_readerrorlog 1, 'C:\Program Files\Microsoft SQL Server\MSSQL$Velias\log\ERRORLOG.1'

Or

EXEC sp_readerrorlog 1

3. Code snippet to search a string --Would search the second row of the specified text file for the number 2000.
EXEC sp_readerrorlog 1, 'C:\SendToTextFile.txt',2, '2000'

Yep you got it right ... this system stored proc can be used not only on log file but on any file (as shown in the above code snippet).

Script to import the SQL Error Logs, format the results and then allow querying using TSQL: http://www.replicationanswers.com/Downloads/ErrorLogs.txt

Saturday, October 22, 2005

SQL Server 2005 and GO operator ...

In this article we would see an interesting feature which was introduced in SQL Server 2005. Do you know that you can execute a T-SQL batch "any number" of times just by providing an integer value along with GO.

As we all know we used to end a command or a script block by typing a command terminator. i.e, GO.

GO :: Signals the end of a batch of Transact-SQL statements to the MS SQL Server utilities.

Extract from BOL:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. In SQL Server 2005, we can follow the command terminator (GO) with an integer value to specify how many times the command should be run. Cool isn't it?

Test it for yourself:

Print getDate()
Go 10

If you execute the above script it would print the current date 10 times.

Wednesday, October 19, 2005

Cool search utility ...

Find below the script which would help you all in searching contents within "msdn", "dictionary", "msn", "yahoo", "netscape", "Google" and within my blogspace with ease.

Script:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\dic]
@="http://dictionary.reference.com/search?q=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\google]
@="http://www.google.com/search?hl=en&lr=&q=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msdn]
@="http://search.microsoft.com/search/results.aspx?qu=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\net]
@="http://search.netscape.com/ns/search?fromPage=NSCPTop&query=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msn]
@="http://search.msn.com/results.aspx?q=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\yahoo]
@="http://search.yahoo.com/search?ei=UTF-8&p=%s"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\vadi]
@="http://thinkingms.com/vadivel/SearchView.aspx?q=%s"

Step 1: Copy - Paste the above script into a notepad. Save the file as "SearchUtility_IE.reg".
Step 2: Double click on the .reg file.
Step 3: Open Internet explorer and try this dic test
Step 4: Try msdn vista
Step 5: Try net vadivel

Also try the following if you have direct access to internet:

Step 6: google vadivel
Step 7: msn Linq
Step 8: yahoo vadivel
Step 9: vadi sql

If you feel this tip would be useful do feel free to spread the word around .... if at all by any chance you run into any issues write back to me.

Disclaimer: Normally it is not advisable to edit the registry keys directly. So if at all you are going to try it ... please be advised that you doing it at your own risk. Don't hold me responsible for anything :)

Good luck!

Friday, October 14, 2005

This guy is interesting!

For quite sometime I am reading Tejas blogs and I should say it is really informative. Keep up the good work Tejas.

Wanna know more about him .. check out "Who is Tejas Patel?"

Wednesday, October 12, 2005

I started a blog; now what?

Rajesh Shetty author of Beyond Code gives us pointers of what to do when you start a blog.

This is one of my favorite questions. A few months ago, the question used to be "How do I start a blog?" That's no longer the popular question. Almost everyone knows how to start a blog. In fact, one of the easiest things about the blog is starting one. With so many free blogging services available, it should be a snap to setup one. Of course, if it's easy for you to start a blog, it's easy for several million people out there. So, for obvious reasons, starting a blog is no longer a differentiator. You got to do something more. So, what are those additional things that you can do?

Over the last few months I have a learnt a thing or two about blogging from several of my friends who are my role models in the blogging world.

Here's my $.02 for those of you who are starting to blog (partial list)

1. Focus on providing high ROII (Return on Investment for an Interaction)

Every time a reader visits your blog, remember that you are taking his or her precious time and the only way you can get a reader to return to your blog is if you can ensure that the reader gets the highest return on their investment of interaction time with your blog.

2. Write something worth linking to

Unlinke newspapers, blogosphere is full of conversations. Of course, you will link to something that you find interesting. It's the same with others - they will link to you only if you write something that may be useful to their readers. There are no favors here.. sometimes may be :)

3. Participate in other conversations (Thanks to Dave Taylor for the suggestion)

To make the most out of your blog, you need to participate in conversations outside of your blog too

4. Update your blog frequently

Frequency is subjective and all I can say is work hard to maintain a reasonable frequency. It takes a lot of commitment to write quality content at regular frequency.

5. Provide reasons for why you are the right person to say what you are saying

With the amount of information available on the internet, you can write brilliantly about almost anything.However what you write will gain more credibility if you are qualified to say what you are saying. For example, if you are writing about entrepreneurship, it helps if you are one. This means that

Last but not the least:

6. Blog is not your brand

There is a confusion that you can build a brand through your blog. While in some remote cases it is a possibility, in most cases, you have to work online and offline to build a powerful brand and use your blog to extend it. "Who you are" matters a lot. Think about some of your favorite bloggers and you will see that they are all working on exciting things apart from writing interesting blog entries. They speak at conferences, write books, contribute to other magazines, participate in charitable causes etc.

In summary, starting a blog is the easiest thing to do. Maintaining one is hard. Not because you won't get enough content to write but because while maintaining your blog, you need to invest in yourself to keep growing to continue to enhance credibility for your blog.

Good luck!