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.