Friday, December 02, 2005

Recursive function to display hierarchial data ...

One of the sql newsgroup member asked this question:

Guys, I have a table by name "TblRecursive" which has following data

ID, Name, ParentID
1, A, 0
2, B, 1
3, C, 2
4, D, 2
5, E, 1

Using the above data I just want to generate a result as below
A
A\B
A\B\C
A\B\D
A\E

Can you help in writing a query for this?

My Solution:

We can achieve this by calling a "User Defined Function (UDF) recursively". Let me show how to do that with a working example.

--Table creation
Create table tblEmployeeInfo
(
EmpId int primary key,
EmpName varchar(30),
MgrId int
)

--Insert test data into it
Insert into tblEmployeeInfo values(1, 'Director', null)
Go
Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
Go
Insert into tblEmployeeInfo values(3, 'Secretary', 2)
Go
Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
Go
Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
Go

-- User defined function for your requirement
Create function GetEmpPath ( @pEmpid int ) Returns varchar(8000)
As
Begin
Declare @intMgrid int
Declare @strEname varchar(500)
Declare @strPath varchar(500)

Select @intMgrid = mgrid, @strEname = empname From tblEmployeeInfo where
EmpId = @pEmpid

If (@intMgrid is null)
Begin
Set @strPath = @strEname
End
Else
Set @strPath = dbo.GetEmpPath(@intMgrid) + '\' + @strEname
return @strPath
End
Go

--Test the code
Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from tblEmployeeInfo

Wednesday, November 30, 2005

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all.

XmlHttp -- Extensible Markup Language Hypertext Transfer Protocol

An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page.

Source code:

<html>
<head>
<script language="vbscript">
Dim objXmlHttp
Set objXmlHttp = CreateObject("Msxml2.XMLHttp")

Function ScreenScrapping()
URL == "UR site URL comes here"
objXmlHttp.Open "POST", url, False
objXmlHttp.onreadystatechange = getref("HandleStateChange")
objXmlHttp.Send
End Function

Function HandleStateChange()
If (ObjXmlHttp.readyState = 4) Then
msgbox "Screenscrapping completed .."
divShowContent.innerHtml = objXmlHttp.responseText
End If
End Function
</script>
<head>

<body>
<input id="divResult" onclick="ScreenScraping()" type="button" value="Click here to start screen scraping" name="btnScreenScraping">
<div id="divShowContent" />
</body>
</html>

Points to note:

1. Many sites have policies against screen scraping ... so before trying to screen scraping any particular site .. do check / respect their policy.

2. I have checked for readyState = 4 property value. It means, we have received complete data in responseText. If we don't do this check we might end up getting incomplete data .. if the site takes longer time to process our request.

3. In objXmlHttp.Open the third parameter takes boolean value. True means, scripts run without waiting for a response from the site/server which we are hitting. False means, if would wait for server response before starting its processing.

For understanding the basics of XmlHttp object, check ...
1. http://www.w3schools.com/dom/dom_http.asp
2. http://jibbering.com/2002/4/httprequest.html

Tuesday, November 22, 2005

List of my SQL Articles / Tips ...

Last Updated on October 10, 2007

* Latest articles are added at the end of this post.

Articles relating to SQL Server 7.0 / 2000

1. [MSDN] Database documentation
2. Returning comma seperated details from a table ...
3. Quick search within ALL stored procedures ...
4. Find whether a column is identiy or not
5. Encryption in SQL Server 7.0
6. About sp_readerrorlog
7. Useful TSQL code snippets for beginners
8. Copying database diagrams ...
9. Query to display Null values at the bottom ...
10. Alternate rows ...
11. Running number !!
12. Doing case sensitive searches
13. Easiest way to add comments to your SQL 2k code ...
14. Listing records from 10 to 15 (for ex) without using where clause
15. Is sorting possible in Views?
16. Creating thumbnails from binary content
17. Saving an image as binary data into SQL Server
18. Reclaim Unused Table Space
19. Encrypting ALL SP's ...
20. Database Compatibility ...
21. About TimeStamp datatype of SQL Server ...
22. Grouping Stored Procedures
23. Creating SQL Based RSS Feed
24. About TSEqual function (SQL 2K)
25. Text functions in SQK 2k
26. Avoid using sp_rename ...
27. Delete Vs Truncate Statement
28. UDF's in Constraints ...
29. Registry manipulation from SQL
30. Comparing tables ...
31. Sp_refreshView explained ...
32. Recursive function to display hierarchial data ...
33. Primary keys without Clustered Index ...
34. Sorting decimal values within a varchar field
35. SPLIT function in SQL Server -- Method 1
36. SPLIT function in SQL Server -- Method 2
37. Padding Leading Zero's
38. Finding product of a column

39. Relation between Triggers and sp_dbcmptlevel
40. Faster way to fetch row count of a table
(Solution for SQL 2005 is also added)
41. Easiest / fastest way to Delete ALL records in a [development] Database (Works in SQL 2005 as well)
42. How to find whether a decimal number is divisible by another decimal number?
43. Find out the second (2nd) highest salary from employee table.
44. Rolling back a truncate operation!
45. Fun with SQL Server

46. Rolling back a truncate Operation
47. Sp_executesql() vs Execute() [both SQL 2k and 2005]
48. Don't start user defined SP's with "SP_"
49. Find tables which doesn't have Primary Key [both SQL 2k and 2005]
50. Find number of days in a given month
51. List tables that doesn't participate in any relationships [both SQL 2k and 2005]

52. Removing unwanted spaces within a string ...
53. Workaround for 'Divide by zero error encountered'
54. GRANT permission to ALL stored procedures
55. Query to find out indexes created dynamically by SQL Server 2000

Articles relating to SQL Server 2005

1. Saving images as BLOB into SQL Server 2005
2. Paging records using SQL Server 2005
3. Exposing SQL Server 2005 data via a Web Service
4. Sending custom resultset -- SQL Server 2005
5. SP to rename a directory using CLR in SQL Server 2005
6. Encrypt and decrypt data in SQL Server 2005
7. Basics of DDL Triggers in SQL Server 2005
8. Creating a DML trigger using CLR in SQL Server 2005
9. Simple SP written using CLR in SQL Server 2005
10. Cross Apply and Outer Apply in SQL Server 2005
11. SQL Server 2005 and GO operator ...
12. Sql 2k -- Yukon -- Acadia
13. Try/Catch block in Sql Server 2005
14. Enhancements to Top Keyword in Yukon
15. XML Data type in Yukon
16. About large value data types
17. Creating reports using Pivot operator
18. Row_Number function in Sql Server 2005
19. Viewing the source code of SP in Yukon
20. Memory management in Yukon
21. Copy file from source to destination using CLR in Yukon
22. SP to create directory using CLR in SQL Server 2005
23. Synonyms in SQL Server 2005
24. About use="required" attribute in Yukon
25. Rank function in SQL Server 2005 ...
26. Creating XML Schema in SQL Server 2005
27. XML data type and Quoted Identifier
28.
SQL Server Configuration Manager ...
29.
About 'RESOURCE' database in SQL Server 2005
30.
Paging in SQL Server 2000 and 2005?
31.
Accessing a table in SQL Server 2005 (Schema related)
32. Listing / Dropping ALL SPs from a database in SQL Server 2005
33. Brief Theoretical Knowledge about Table Partitioning
34. Example for Creating and using Partitions in SQL Server 2005
35. Different Types of Partitioning Operations in SQL Server 2005
36. Max limit of Varchar, nvarchar, varbinary datatypes ...
37. Export data from SQL Server to Excel without using SSIS or DTS
38. Arithmetic overflow error converting expression to data type int.
39. Find the missing numbers (GAPS) within a table...
40. How to find out recently run queries in SQL Server 2005?
41. Finding missing indexes in SQL 2005
42. NEWID vs NEWSEQUENTIALID
43. Reclaiming the table space after dropping a column - [with clustered index]
44. Reclaiming the table space after dropping a column - [without clustered index]
45. SQLCMD -- Part I (Basics, Connectivity)
46. SQLCMD -- Part II (Interactive Mode)
47. SQLCMD -- Part III (Non-Interactive Mode)
48. SQLCMD -- Part IV (Set your favorite editor)
49. SQLCMD -- Part V (Setting startup scripts)
50. SQLCMD -- Part VI (Scripting Variables)
51. SQLCMD -- Part VII (Concatenating string with a scripting variable)
52. SQLCMD -- Part VIII (:r and about concatenating string with spaces)

Technorati tags: , , ,

Monday, November 21, 2005

Returning comma seperated details from a table ...

I saw an question in one of the SQL newsgroup which I visit frequently (offlate). That person is having a problem with retriving data from a table. Let me explain it in detail.

Sample table structure:

Create table empTest
(
[Id] int identity,
Contact varchar(100),
Employee_Id int
)
Go


Let us populate few records into the table:

Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@gmail.com', 101)
Insert into empTest (Contact, Employee_Id) values ( '04452014353', 101)
Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@yahoo.com', 102)
Insert into empTest (Contact, Employee_Id) values ( '9104452015000', 102)
Go

And now, as you could see each employee has more than one contact details. So if you query the table as Select * from EmpTest it would list couple of records for each employee. Instead of this won't it be nice if we could generate comma seperated contact details for each employee. i.e., There would be only one record for an employee.

Something like this, (Employee_Id, Contact )

101 vmvadivel@gmail.com, 04452014353
102 vmvadivel@yahoo.com, 9104452015000
etc.,


Solution:

-- Temp variable
Declare @strContact varchar(8000)

-- Build the comma seperated contact list
Select @strContact = Coalesce(@strContact + ', ', '') + ET.Contact From empTest ET where ET.Employee_Id = 101

--Display the comma seperated contact list
Select @strContact


This above code snippet would work for a given employee id. Now this can generalized to work for all employee id as shown below:

Create function dbo.GetEmpDetails(@EmpID int)
Returns Varchar(8000)
As
Begin
Declare @Contact varchar(8000)
Select @Contact = Coalesce(@Contact + ', ', '') + ET.Contact From empTest ET
Where ET.Employee_Id = @EmpID

Return @Contact
End
Go


Select distinct Employee_ID, dbo.GetEmpDetails(Employee_Id) as ListOfContacts From empTest
Go


To be frank I got the base logic from an article in 4guysfromrolla.com and customized it according to our need here.

Friday, November 18, 2005

Quick search within ALL stored procedures ...

This article would explain in detail the methods involved in searching strings within ALL stored procedures.

I am sure there might have been situation where you want to find out a stored procedure where you remember writing some complex logic. Won't it be nice if we can find out that stored procedure where we have already written that important piece of code .. so that we can reuse? If your answer is "yes" read on.

Points to note before executing this SP:

1. I have written 2 methods for this purpose. If we want this SP to be in the MASTER database then set @method =1. If not set it to 2

2. If @method is set to 2 then it is advisable to change the SP name. As you know only SP's which exist in MASTER database needs to be prefixed with "SP_" (for performance reason).

The Stored Procedure:

Create Procedure sp_searchForStoredProc
(
@searchString varchar(100)
)
As
/**************************************************
Stored Procedure: sp_searchForStoredProc CreatiOn Date: 11/18/2005
Written by: Vadivel Mohanakrishnan

Purpose: List out all SPs where the particular search string exists.
1)If you want to create this SP in MASTER database then choose Method 1.
2)If you want to create in Individual database then Go for Method 2

Since most of us would prefer having it in MASTER DB i have prefixed the SP with "sp_". If you
plan to install in some user created DB .. I strongly suggest to remove the prefix.

Output Parameters: none
Return Status: 0-Sucess, 1-Failure

Called By:
Component:
Calls:

Data ModificatiOns:

Database:
System Tables: SysComments, SysObjects
Views : InformatiOn_Schema.Routines
FunctiOns :

Updates:
Date Author Purpose
11/18/2005 Vadivel Mohanakrishnan Create
**************************************************/

Set nocount on

Declare @method int
Set @method = 1

If (@method = 2) and (upper(db_name()) = upper('Master'))
Begin
RaisError 60001 'Error in Installation!! Since you are running from MASTER database, Set @method=1.'
Return 0
End

--Append the wildcard % before and after the search string
Set @searchString = '%' + @searchString + '%'

If @method = 1
Begin


/* Method 1 :: For Master Database */

Select
distinct(SO.[name]) as 'Stored Procedure Name'
From
SysComments SC,
SysObjects SO
Where
SO.ID = SC.ID and
cateGory <> 2 and
PatIndex( @searchString, text) > 0
Order By
[name]
End
Else
Begin

/* Method 2 :: For Individual Database
Double check whether you have removed the prefix "sp_" from the SP name.
*/


Select
Routine_Name as 'Stored Procedure Name'
From
InformatiOn_Schema.Routines
Where
Routine_type = 'procedure' and
ObjectProperty(Object_Id(Routine_Name),'IsMsShipped') = 0 and
PatIndex( @searchString, Routine_DefinitiOn) > 0
End

If @@Error <> 0
Return (1)
Return 0
Go

Syntax for checking this Stored Procedure:

Exec usp_searchForStoredProc 'SearchString Comes here'

Examples:
1. If the SP has been created in MASTER database then
Exec sp_searchForStoredProc 'delete'

2. If the SP has been created in some other DB then,
Exec usp_searchForStoredProc 'delete'

Clean Up:
Drop proc sp_searchForStoredProc

Friday, November 11, 2005

Find whether a column is identity or not ...

In one of the newgroup somebody was asking the way to find whether a column is identity or not. I thought I would write my answer there as an article for the benefit of those who have the same doubt.

I know of two ways of finding whether a given column is an identity column or not. Let me try and explain it ...

Sample Table structure:

Create a sample table with an identity column in it.

Create table [order_details]
(
OrderId int identity,
OrderName varchar(10),
UnitPrice int
)


Method 1: [Easiest way]

Select ColumnProperty(Object_id('order_details'), 'OrderId', 'IsIdentity')

Method 2:

For some reasons if you don't want the above method!! then try this one

Declare @colName varchar(100)
Declare @RetColName varchar(100)

Set @colName = 'OrderId' -- Specify the column name for which you want to check

--Status column = 128 means its an identity column
Select @RetColName=[name] from syscolumns where (status & 128) = 128 and id=(select id
from sysobjects where name='order_details')

If @colName = @RetColName
Print 'Its Identity'
Else
Print 'Not an identity column'

Wednesday, November 02, 2005

Sp_refreshView explained ...

Often people ask me "I have a table and there are few views based on that table. When I make a structural change to my table it invalidates all those views. So we are left out with no other option than to drop those views and recreate it. But is there any alternate way for this?". For all those people who have this doubt in mind .. read on.

i) Create this sample table for demo purpose

Create table tstTestingUpdateView
(
Sno int identity,
[Name] varchar(10),
Mail varchar(50)
)

ii)Insert some dummy records

Insert into tstTestingUpdateView Values('Vadivel','smart3a@yahoo.com')

iii) Create a view based on that table

Create view tstView1
As
Select * from tstTestingUpdateView


iv) Execute the newly created view and have a look at the output

Select * from tstView1

v) Now add a new column to the table

Alter table tstTestingUpdateView add ContactNumber Varchar(20)

--Now if you execute the view it won't list the newly added column in it
Select * from tstView1

vi) Solution

--For that make use of the below system stored procedure
sp_refreshview tstView1

--Now exceute the view to see the newly added column
Select * from tstView1

vii) Clean up

Drop view tstView1
Drop table tstTestingUpdateView

Double click on any word in this site ....

Try double clicking any word within my blogspace. For example, if you double click on a word "response" then you would get an definition of that word in detail. Try it out for yourself and do let me know whether this would be helpful for you guys.

Cast your vote from home!!

Estonia has sucessfully conducted an national election with online voting option.

A tiny Baltic nation last week became what appears to be the first country to open its local elections to Internet voting on a nationwide level--although only about 1 percent of the votes were cast online. Check out the full article here Estonia pulls off nationwide Net voting

Needless to say, internet voting would save lot of time and energy for almost everybody. But I seriously donno whether in near future it would be possible in India! I personally feel that we need to improve a lot in the following fields "Security", "Infrastructure" and "Computer awareness". I don't think this would be possible here in India or Tamil nadu for atleast next 10 years.

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!

Monday, September 19, 2005

Sending custom resultset -- SQL Server 2005

This article would explain in detail (with complete code sample) the way to return custom resultsets to the end user using CLR in SQL Server 2005.

Things to know before we get started:

1) SqlPipe() -- Its the job of the SqlPipe object to send results back to the stored proc or for that matter UDF etc.,

2) SqlDataRecord -- If we want to return resultset (more than one record) make use of this new object which is introduced in ASP.NET 2.0. For that we need to first create a schema using SqlMetaData objects.

With this small introduction lets get our hands dirty by writing a small SP which would return more than records.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure >> name the file as SPReturningResultSet

4. Copy paste the below code into that file

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.Data.SqlClient;

public partial class StoredProcedures
{
[SqlProcedure]
public static void SPReturningResultSet()
{
SqlMetaData[] objMetaDataCols = new SqlMetaData[3];
objMetaDataCols[0] = new SqlMetaData("Sno", SqlDbType.Int);
objMetaDataCols[1] = new SqlMetaData("FirstName", SqlDbType.VarChar, 50);
objMetaDataCols[2] = new SqlMetaData("EmailID", SqlDbType.VarChar, 50);

SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

SqlDataRecord objRows = new SqlDataRecord(objMetaDataCols);

objRows.SetInt32(0, 1);
objRows.SetString(1, "Vadivel Mohanakrishnan");
objRows.SetString(2, "vmvadivel@gmail.com");

// New result-set starts here
objPipe.SendResultsStart(objRows, true);

// In-between add as many rows as you want
objRows.SetInt32(0, 2);
objRows.SetString(1, "Maruthiraja");
objRows.SetString(2, "mars@gmail.com");
objPipe.SendResultsRow(objRows);

objRows.SetInt32(0, 3);
objRows.SetString(1, "Sriram");
objRows.SetString(2, "nilapenn@gmail.com");
objPipe.SendResultsRow(objRows);

// End of the result-set code
objPipe.SendResultsEnd();
}
};

Build the project (Ctrl + shift + B) in VS.NET 2005 and then open up SQL Server 2005 and do the following:

Create Assembly ResultSetDemoFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll
'WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

Create Proc dbo.uspResultSetDemo
As External name ResultSetDemo.StoredProcedures.SPReturningResultSet
Go

Test our SP:

Exec dbo.uspResultSetDemo

If at all it throws an error like the one shown below .. then execute the execute the two lines of code beneath it:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

sp_configure 'clr enabled', 1
Reconfigure with override

Clean Up:

drop proc dbo.uspResultSetDemo
go

drop assembly ResultSetDemo
go

For more reading on this concept, I recommend you to go through this MSDN link >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp

Saturday, September 17, 2005

SP to rename a directory using CLR in SQL Server 2005

This article would explain in detail about the way to rename a physical directory using CLR in SQL Server 2005. The complete source code is attached with this article.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure
4. Copy paste the below code into that file

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
[SqlProcedure]
public static void RenameDirectory(String strOldName, String strNewName)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
//Note: Since it is for demo purpose I haven't done the complete validation
if (Directory.Exists(strNewName))
{
objPipe.Send("Specified directory already exists.");
}
else // Rename the Directory
{
//Directory.CreateDirectory(strNewName);
Directory.Move(strOldName, strNewName);
objPipe.Send("Directory has been successfully renamed.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

After Building this database project in VS.NET 2005 open up SQL 2005 and do the following:

Create Assembly RenameDIRFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

The assemblies imported with EXTERNAL_ACCESS allows access to external sources such as file system using the .NET Framework class library. Since the above code needs to access the file system, we have set EXTERNAL_ACCESS permission while creating the assembly.

Create Proc dbo.RenamePhysicalDirectory
(
@strOldPath nvarchar(1024), @strNewPath nvarchar(1024)
)
As External name RenameDIR.StoredProcedures.RenameDirectory
Go

If we try to test our work by executing the below code:
Exec dbo.RenamePhysicalDirectory 'c:\vadivel', 'c:\vadivelTesting';

It would throw an error as follows:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

The work around execute the below code snippet and then try to test our work ..

sp_configure 'clr enabled', 1
Reconfigure with override

Sunday, August 28, 2005

Encrypt and decrypt data in SQL Server 2005

In this article we would examine how to encrypt a column data in SQL Server 2005. Needless to say we would also look into the decryption part. The complete source code is also provided below for your better understanding.

Script for creating an EmployeeInfo table

Create table tblEmployeeInfo
(
EmpId int primary key,
Firstname varchar(50),
Age varbinary(300), -- this is the field we would encrypt while storing dataEmailID varchar(50)
)

Generate a key to protect the AGE of the employee:

Create symmetric key symKeyemployees
with algorithm = TRIPLE_DES
encryption by password = 'smart3a?'

Other encryption algorithm which we could use instead of TRIPLE_DES are:

1. DES,
2. RC2,
3. RC4,
4. DESX,
5. AES_128,
6. AES_192 and
7. AES_256

Decrypt the key and makes it available for use:

Open symmetric key symKeyemployees using password = 'smart3a?'

In order to know whether the key has been opened or not query sys.openkeys table as follows:

Select * from sys.openkeys

Result of running the above query in our case is as follows:

database_id = 8
database_name = AdventureWorks
key_id = 256
key_name = symKeyemployees
key_guid = A383AB00-E692-49EE-9252-E6AD4371F865
opened_date = 2005-08-27 09:52:07.693
status = 1

Since the ground work has been done, let us now insert some sample data into the table.

Insert into tblEmployeeInfo values ( 1, 'Vadivel', encryptbykey(key_guid('symKeyemployees'), '29', 1, '1'), 'vmvadivel@yahoo.com')

The 3rd parameter of encryptbykey is an integer which helps us to specify whether we are going to use an authenticator value or not.

Value 1 means = an authenticator value would be supplied.
Value 0 means = an authenticator value would not be supplied. By the way, this is the Default.

The next parameter is the actual authenticator data. In our case it is '1' because we are using EmpID as our authenticator value. FYI, default is NULL.

Query the table to find that the value of AGE field is encrypted.

Select * from tblEmployeeInfo

Result of running the above query would be something like this (Note that I have formatted the output for better understanding):

EmpID = 1
Firstname = Vadivel

Age (Encrypted value) = x00AB83A392E6EE499252E6AD4371F86533996F7339B9E43B8D6477142A785FB6292
D0683C5ABFA06734429C37BBDFB43C80A4EAA458678E328F4250A24AAEC74

EmailID = vmvadivel@yahoo.com

Decrypt the data:

Select EmpId, Firstname, emailid,
convert(varchar(10), decryptbykey(Age, 1, convert(varchar(30), EmpId))) as Age
from tblEmployeeInfo

Close the symmetric Key:

Close symmetric key symKeyemployees

Run the below script to clean up:

Drop table tblEmployeeInfo
Drop symmetric key symKeyemployees

Friday, August 26, 2005

Basics of DDL Triggers in SQL Server 2005

This article would explain in detail about the new feature "DDL triggers" in SQL Server 2005. I have explained it with an example whose complete code snippet is also attached below.

Till SQL Server 2000 one can create DML triggers only. i.e., triggers which would fire on Insert, Update or Delete of a table or a view.

In SQL Server 2005 (Yukon) we can create triggers for DDL (Data definition Language) statements. i.e., we can write triggers which would react to CREATE, ALTER & DROP statements as well.

Syntax for creating DDL statements:

Create Trigger trigger_name
ON { ALL SERVER DATABASE }
[ WITH ENCRYPTION ]
{ FOR AFTER } { event_type event_group } [ ,...n ]
AS { sql_statement [ ...n ]
EXTERNAL NAME <> }
[ ; ]
::=
assembly_name.class_name[.method_name]

Major points which I thought might interest you are:

i) The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE).

ii) Event_type argument is used to define the event for which the trigger would fire.

iii) Inside a trigger, you can access information related to the event by calling EventData() function. EventData() function would return a value of type XML. The base XML schema returned by the eventdata() function depends on the scope and event type.

iv) On Database: Applies the scope of a DDL trigger to the current database. If specified, the trigger fires whenever event_type or event_group happens in the current database.

v) All Server : Applies the scope of a DDL trigger to the current server. If specified, the trigger fires whenever event_type or event_group happens anywhere in the current server.

Lets create a sample trigger which would fire when ever an "Alter table" statement is executed in our database.

Script to Create a sample table

Create table test
(
sno int identity,
firstname varchar(25)
)
Go

Code snippet to create a sample DDL trigger

Create trigger trgAlterTableForDatabase
On database
For alter_table As
Select eventdata()
Print 'You are not authorized to execute Alter Table statement in this database.'
Rollback
Go

Try and alter the table structure

Alter table test alter column firstname varchar(50)

Result:

On executing the above "Alter table" statement we would be welcomed with the below error message.

(1 row(s) affected)
You are not authorized to execute Alter Table statement in this database.
Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

As said earlier eventdata() would return a XML string and in our case it would like this:


ALTER_TABLE
2005-08-26T07:46:48.880
58
VadivelDB
VadivelDB\Administrator
dbo
AdventureWorks
dbo
test
TABLE


Alter table test alter column firstname varchar(50)



Clean up

drop trigger trgAlterTableForDatabase on database
drop table test

Creating a DML trigger using CLR in SQL Server 2005

In this article, we will take a look at the new CLR integration feature and learn how to create triggers in SQL Server using a managed code. We would create a simple DML delete trigger for a table and access the deleted value from our C# code.

Visual Studio .NET 2005

1. Open a C# -- Database project
2. Create a new trigger file into it.
3. The .NET class that will implement the functionalities of the trigger is given below just copy-paste it into the new file.

GetTriggerContext() :: Get reference to the current trigger context by invoking this method of the SqlContext class. This help us in fetching the data from the virtual table which would be created during a execution of a trigger.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// [SqlTrigger (Name="SampleCLRTrigger", Target="EmployeeInfo", Event="FOR Delete")]
public static void SampleCLRTrigger()
{
SqlTriggerContext triggerContext = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand command = SqlContext.GetCommand();

if (triggerContext.TriggerAction == TriggerAction.Delete)
{
command.CommandText = "Select * from Deleted";
sqlPipe.Execute(command);
}
}
}

Build and compile the .NET class to create a .NET assembly out of it. In VS.NET 2005 it is as simple as Ctrl+Shift+B. The DLL would have been created in the BIN directory. From the properties of that DLL get to know the path of the created DLL.

SQL Server 2005

Then open SQL Server Management Studio and create a new query. Register the assembly in SQL Server using the below code snippet.

Create Assembly assTestCLRTrig
FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

Script to create a table for demo purpose:

Create table EmployeeInfo
(
EmpId int identity(1,1),
EmpName varchar(50),
EmpAddr varchar(100),
EmpContactNo varchar(20)
)

Insert a sample record

Insert into employeeinfo values ('vadi','address comes here','5201')

Script to create a trigger

Create trigger trgDeleteEmployeeInfo
On
EmployeeInfo
For Delete
As External Name
assTestCLRTrig.Triggers.SampleCLRTrigger

The syntax used above for External Name is "AssemblyName.NameOfTheClass.MethodName"

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.

sp_configure 'clr enabled', 1
Reconfigure with override

Lets test our work:

Delete from employeeinfo where empid=1

It would display the record deleted in a tabular fashion. So far, we have seen the steps involved in creating the trigger and executing that trigger from Yukon .. hope it was useful to you!

Tuesday, August 23, 2005

Be careful with Ctrl+C

We do copy various data by ctrl+c for pasting elsewhere. This copied data is stored in clipboard and is accessible from the net by combination of Javascripts and ASP.

You don't trust me :) then just try the below steps:

1) Copy any text by Ctrl+c or Edit >> Copy or anyother method which you are comfortable with!

2) After that just visit this Link: http://www.friendlycanadian.com/applications/clipboard.htm

3) You will see the text you copied on the screen right there in that page.

Hope you would realise the seriousness now. The thumb rule is do not keep sensitive data (like passwords, creditcard numbers, etc.,) in the clipboard while surfing the web. It is extremely easy to extract the text stored in the clipboard to steal your sensitive information.

Its always advisable to either copy useless piece of information onto the clipboard before leaving a shared computer OR logoff / restart the machine so that clipboard content would be erased.

Monday, August 22, 2005

SP to create directory using CLR in SQL Server 2005

This article would explain in detail about the way to create a physical directory using CLR in SQL Server 2005. Lets get started.

1. Open a C# - Database project in Visual Studio.NET 2005
2. Create a new Stored Procedure file in it.
3. Copy-Paste the below code snippet into that file.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;


public partial class StoredProcedures
{
[SqlProcedure]
public static void CreatePhysicalDirectory(String strPath)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
if (Directory.Exists(strPath))
{
objPipe.Send("Specified directory already exists.");
}
else // Directory creation
{
Directory.CreateDirectory(strPath);
objPipe.Send("Directory has been successfully created.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

4. Deploy the stored procedure. Deployment is as simple as right clicking on the project and choosing "Deploy".
5. After deploying this via VS.NET 2005, go to SQL Server 2005 and run the below code snippet :

Exec CreatePhysicalDirectory 'c:\testFolder';

The code would fail miserably throwing the following error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The work around:

In order to make the code work follow the below steps.

Create Assembly CreateDIR
From 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject2\SqlServerProject2\bin\Debug\SqlServerProject2.dll'
With Permission_Set = External_Access;
Go


The assemblies imported with External_Access allows access to external sources such as file system using the .NET FCL (Framework class library).

As you know the above code needs access to the file system, we have set External_Access permission while creating the assembly.

Create Proc dbo.CreatePhysicalDirectory
(
@strPath nvarchar(1024)
)
As External name CreateDIR.StoredProcedures.CreatePhysicalDirectory
Go


Now we could test the code by executing the below code block:

Exec dbo.CreatePhysicalDirectory2 'c:\testFolder';

Copy file from source to destination using CLR in Yukon

This article would explain an utility function to copy a source file into destination. Lets get started.

Part I - Visual Studio.NET 2005

1. Open a C# -- Database project
2. Create a new stored procedure file into it.
3. Add the below code into it ...

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
[SqlProcedure]
public static void CopyFile(String strFileSource, String strFileDestination)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
if (File.Exists(strFileSource))
{
//Third parameter is to say whether the destination
//can be overwritten or not.
File.Copy(strFileSource, strFileDestination, true);
objPipe.Send("Successfully copied from source to destination");
}
else
{
objPipe.Send("Source file does not exist.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

Then build the application once.

Part II -- SQL SERVER 2005

Open SQL Server Management Studio

1. Create a new query and copy paste the below code snippet into it.

--Create an assembly
Create Assembly UtilityFunction_FileCopy
From 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
With Permission_Set = External_Access;
Go

--Create the stored procedure
Create proc dbo.CopyFile
(
@strFileSource nvarchar(1024),
@strFileDestination nvarchar(1024)
)
as external name UtilityFunction_FileCopy.StoredProcedures.CopyFile
Go

2. Try and execute this SP (after creating a source file src.txt in c drive)

Exec dbo.CopyFile 'C:\src.txt', 'C:\dest.txt';

It would most probabaly throw an error like this:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Now try to execute the SP it would work fine.

Creating thumbnails from binary content ...

This article would explain the way to create thumbnail images from the binary data in SQL Server. Lets get started.

I) Sample table structure:

Create table [dbo].[ImgTable] (
[ImgId] [int] IDENTITY (1, 1) NOT NULL ,
[Photo] [image] NULL
) ON [Primary] Textimage_on [Primary]
GO

II) Code snippet to create thumbnails

private void Button1_Click(object sender, System.EventArgs e)
{
DataRow oDRow;
int arraySize = new int();

System.Drawing.Image thumb;
System.Drawing.Image originalimg;

SqlConnection oSqlConn = new SqlConnection("Server=dbserver;uid=username;pwd=password;database=northwind");
SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn);

SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA);
DataSet oDS = new DataSet("TblBinaryImages");
byte[] bytImgData = new byte[0];

oSqlDA.Fill(oDS, "TblBinaryImages");
oDRow = oDS.Tables["TblBinaryImages"].Rows[0];
bytImgData = (byte[])oDRow["Photo"];

MemoryStream stream = new MemoryStream((byte[])oDRow["Photo"]);
originalimg = System.Drawing.Image.FromStream(stream);

//Vadivel :: You can pass the width and height as Querystring.
thumb = originalimg.GetThumbnailImage(100, 100, null, new System.IntPtr());

// Sending Response JPEG type to the browser.
Response.ContentType = "image/jpeg";
thumb.Save(Response.OutputStream,System.Drawing.Imaging.ImageFormat.Jpeg);

// Disposing the objects.
originalimg.Dispose();
thumb.Dispose();
}

Saving an image as binary data into SQL Server ...

This article would explain the different ways of storing an image as binary content into a SQL Server database. Lets get started.

1. Open a C# - web project using Visual Studio.NET
2. Drag and drop a Server side button on to the webform.
3. Copy paste the following namespace into your code behind file.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;

Method 1:

4. Copy-paste the content within the button click event shown below into your page.

private void Button1_Click(object sender, System.EventArgs e)
{

DataRow oDRow;
SqlConnection oSqlConn = new SqlConnection("Server=YourDbServername;uid=username;pwd=password;database=northwind");

SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn);
SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA);
DataSet oDS = new DataSet("TblBinaryImages");

FileStream oFStream = new FileStream(Server.MapPath("DummyImage.jpg"), FileMode.OpenOrCreate, FileAccess.Read);

byte[] bytImgData = new byte[oFStream.Length];
oFStream.Read(bytImgData, 0, Convert.ToInt32(oFStream.Length));
oFStream.Close();

oSqlDA.Fill(oDS,"TblBinaryImages");
oDRow = oDS.Tables["TblBinaryImages"].NewRow();
oDRow["Photo"] = bytImgData;
oDS.Tables["TblBinaryImages"].Rows.Add(oDRow);
oSqlDA.Update(oDS, "TblBinaryImages");
oSqlConn.Close();
}

Method 2:

4. We can store the binary content as a base64string also. If your need is one such then use the below code snippet.

string data = null;
Bitmap bmp = new Bitmap(@"C:\YourPicture.bmp");

using (MemoryStream mem = new MemoryStream())
{
bmp.Save(mem, System.Drawing.Imaging.ImageFormat.Bmp);

//Convert the bytes to a string.
data = Convert.ToBase64String(mem.ToArray());
}

Now in the string variable "data" the base64string would be there. Use it to insert it into the database.

Sample table structure:

Create Table [dbo].[ImgTable]
(
[ImgId] [int] IDENTITY (1, 1) NOT NULL ,
[Photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Go