Thursday, December 27, 2007

Difference between Response.Write and Response.Output.Write

From classical ASP days if we want to print some string with formatting we used to make use of Response.Write and some function to do the string formatting for us. But in .NET we have Response.Output.Write which is equal to Response.Write + String.Format features.

Find below the basic sample explaining this feature!

private void Page_Load (object sender, System.EventArgs e)
{
DateTime dtTwoDays = DateTime.Now; // Get the current date
dtTwoDays = dtTwoDays.AddDays(2); // Add 2 days to it

Response.Write(”Classical way of doing the same thing”);

string strMessage = dispMessage(dtTwoDays); // Call a method which would return the formatted string.
Response.Write(strMessage); // Print that formatted string

Response.Write(”.NET way of doing the same thing:”);
Response.Output.Write(”{0} from today is {1:d}”, “Two days”, dtTwoDays);
}

Private string dispMessage(DateTime dtMVP)
{
// {0} and {1:d} are the place holders which would take up the values passed as parameters
// {0} will take “Two days“ and
// {1:d} will take the value of dtMVP
return String.Format(”{0} from today is {1:d}”, “Two days”, dtMVP);
}


While using this code don't forget to include “BR” tags where ever necessary. So that the output would be well formatted. If not, everything would be printed in a single line. Hope this helps!

Monday, December 24, 2007

2007 Internet Quiz.

I scored 100% in the 2007 internet quiz @ http://www.justsayhi.com/bb/internet



I need to admit I used google for one question as I wasn't clear on the answer. So you can count me as 99% :)

What's your score?

Sunday, November 25, 2007

Get TIME alone from a given date

I see this to be very frequently asked question in dotnetspider.com! Solution to this is to make use of CONVERT function in SQL Server.

--Query will fetch the time portion alone
Select Convert(Varchar, Getdate(), 108)

--Query will fetch the date portion alone.
Select Convert(Varchar, Getdate(), 101)

The last parameter of Convert function is StyleId. Go through books online to know the complete list of parameters and its corresponding output format.

Friday, November 23, 2007

Should we upgrade to SQL 2005 or wait for SQL Server 2008?

To my knowledge, there are companies which still works on SQL Server 7.0 and SQL Server 2000. But in the last quarter of 2005, Microsoft released SQL Server 2005. Though I have been using that product personally since that time and officially for more than one year now. I am not too sure whether everybody has migrated to SQL Server 2005!

That being a case SQL Server 2008 (code named: Katmai) is around the corner now :) Hmm we need to wait and see how many of them migrate immediately.

Wednesday, November 21, 2007

List tables which are dependent on a given table - SQL Server 2005

Option 1: Right-click on a table and choose 'View Dependencies'.

Option 2: For some reason if you want to do it programmatically check out the below code snippet

Select
S.[name] as 'Dependent_Tables'
From
sys.objects S inner join sys.sysreferences R

on S.object_id = R.rkeyid
Where
S.[type] = 'U' AND
R.fkeyid = OBJECT_ID('Person.StateProvince')


here, replace Person.StateProvince with your table name.

Monday, November 19, 2007

List the modified objects in SQL Server 2005

For viewing the modified date of Stored Procs and UDF alone:

Select
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Where
Routine_type in ('PROCEDURE', 'FUNCTION')
Order by
Last_altered desc, Routine_type, Routine_name


For viewing the modified date of Stored Procs, UDF and Views:

We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc., which have got modified.

Code snippet:
Select
[name] as 'Object Name',
[type] as 'Object Type',
create_date,
modify_date
From sys.objects
Where [type] in ('P', 'FN', 'TF', 'V')
Order by Modify_Date desc, [type], [name]


The above query will list all 'SPs', 'UDFs' and 'Views' in the current database with its 'Created date' and 'Modified date'. We can further finetune this code to match our exact need!

For triggers
Check out create_date and modify_date columns in sys.triggers.
select * from sys.triggers

Sunday, November 18, 2007

Codd's Rule and Current RDBMS Products ...

I was discussing with few of my friends on the topic 'Codds rule'. I heard them say that 'SQL Server' and 'Oracle' supports all of the 12 codd's rule but DB2 doesn't support few of them!! I was surprised and then later got confused myself :)

Actually to my knowledge there is no RDBMS product (be it, Microsoft SQL Server or Oracle or DB2) which satisfies all of the 12 rules of CODD.

Hopefully in few days i will write about my knowledge on this subject and leave it open for others comments :)

Expansion for the word CURSOR in SQL Server

Cursor = Current Set Of Records

Wednesday, November 14, 2007

SQLCMD -- Part IX (Batch files)

Using SQLCMD to execute script files easily in different environments

Assume we have few script files (.sql) which needs to be run in multiple SQL Servers. Hope you would accept that's real pain to connect into different servers from SQL Management Studio and then execute the scripts one after the other.

One of the easiest ways of doing it is by making use of the SQLCMD utility of SQL Server 2005.

Step 1: Lets create few dummy script files for demo purpose.

File1: 01TableCreation.sql

Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go

File2: 02InsertRecords.sql

set nocount on

Insert into tblTest (Fname) values ('alpha')
Insert into tblTest (Fname) values ('beta')


File3: 03StoredProcedures.sql

Create proc usp_GetAllTblTest
as
Select sno, fname from tblTest
go


Step 2: Create a batch file and call these .sql files in order.

File4: DBInstallationScripts.bat

sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"
sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"
sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"


Step 3: Execute the batch file

From the command prompt (Start >> Run >> Cmd) do the following:

c:> DBInstallationScripts DBUserName DBPassword DBServerName Databasename

Please note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.



So this way, same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.

Hope this helps!

Tuesday, October 23, 2007

Wednesday, October 10, 2007

SQLCMD -- Part VIII (:r and about concatenating string with spaces)

Theory:

:r -- parses additional T-SQL statements and sqlcmd commands from the file specified by into the statement cache.

In this article we would see the usage of :r as well as handling spaces in SQLCMD.

In SQL Mgmt Studio:

Step 1: 01VariableInitialization.sql

:setvar filepath "C:\Vadivel\SQL Related\Scripts\sqlcmd"
:r $(filePath)\02TableCreation.sql


Step 2: 02TableCreation.sql

Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go

:r $(filePath)\03InsertScripts.sql


Step 3: 03InsertScripts.sql

Insert into tblTest (Fname) values ('alpha')

Explanation of each file:

01VariableInitialization.sql -- In this file we create a scripting variable 'filePath' which will hold the path of the .sql files which we use for this demo. Then it executes 02TableCreation.sql.

02TableCreation.sql -- In this, we create tables of our choice. Also we make use of the scripting variable created in the previous file here to call another .sql file to insert records into this table.

It's advisable to check whether that table exists before creating it.

03InsertScripts.sql -- Insert records into our dummy table.

In Command Prompt:

Now open command prompt (start >> Run >> cmd) and connect to a DB server to execute the first .sql file alone.

Step 4: SQLCMD -i "C:\Vadivel\SQL Related\Scripts\sqlcmd\01VariableInitialization.sql"

Till now everything would have worked properly.

Step 5: If at all you have your 01VariableInitialization.sql as shown below:

:setvar filepath "C:\Vadivel"
:r $(filePath)\SQL Related\Scripts\sqlcmd\02TableCreation.sql


If you try this it would fail. Why? Because there is a space within the string which we have concatenated with our scripting variable. So if we have space then we need to enclose the string within quotes.

Is this the way to add quotes?
:r $"(filePath)\SQL Related\Scripts\sqlcmd\02TableCreation.sql"

No this would throw an syntax error!!

The correct way to add quotes is shown below:

:r $(filePath)"\SQL Related\Scripts\sqlcmd\02TableCreation.sql"

So the lesson learned is add quotes only for the string which is being concatenated with a scripting variable (that too if it has spaces in it).

Hope this helps!

Saturday, October 06, 2007

SQLCMD -- Part VII (Concatenating string with a Scripting Variable)

This example demonstrates the way to create a variable and make use of it for multiple purpose.

Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it.

---Code snippet which needs to be run in Mgmt Studio starts here---
Use master
go

Create Database DB1
go

Use DB1
go

Create table t1 (a int)
go

insert into t1 values (1)
insert into t1 values (2)
insert into t1 values (3)
go

Create database DB2
go

Use DB2
go

Create table t2 (Num int)
go

insert into t2 values (4)
insert into t2 values (5)
insert into t2 values (6)
go

--Code snippet to be run in Mgmt studio ends here---

Now open command prompt and connect into the DB.

Step 1: SQLCMD -U sa -P hot -S VADIVEL
Step 2: Press Enter

Now lets create a variable by name "dbname" and assign 'DB' as the string to it.

Step 3: :setvar dbname DB

Now lets make use of this variable and list out all records from table t1

Step 4: use $(dbname)1
Step 5: go
Step 6: select * from t1
Step 7: go

If you see along with the variable I have appended the value 1. So that it would take it as 'DB1'. Hope I have made the point!

Now lets make use of this variable and list out all records from table t2 which exists within DB2 database

Step 8: use $(dbname)2
Step 9: go
Step 10: Select * from t2
Step 11: go

Monday, October 01, 2007

Reclaiming the table space after dropping a column [without clustered index]

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index.

Create a table with non-clustered index in it:

Create Table tblDemoTable_nonclustered (
[Sno] int primary key nonclustered,
[Remarks] varchar(5000) not null
)
Go


Pump-in some data into the newly created table:

Set nocount on
Declare @intRecNum int
Set @intRecNum = 1

While @intRecNum <= 15000

Begin
Insert tblDemoTable_nonclustered (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109))
Set @intRecNum = @intRecNum + 1
End

Check the fragmentation info before dropping the column:

DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered')
GO

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 84
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 91.67% [11:12]
- Extent Scan Fragmentation ...................: 41.67%
- Avg. Bytes Free per Page.....................: 417.4
- Avg. Page Density (full).....................: 94.84%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Drop the Remarks column and reindex the table:

Alter table tblDemoTable_nonclustered drop column Remarks
go

DBCC DBREINDEX ( 'dbo.tblDemoTable_nonclustered' )
Go

Now check the Fragmentation info:

DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered')
GO

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 84
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 91.67% [11:12]
- Extent Scan Fragmentation ...................: 41.67%
- Avg. Bytes Free per Page.....................: 417.4
- Avg. Page Density (full).....................: 94.84%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you see there won't be any difference or rather the space hasn't reclaimed yet. Here, DBCC DBREINDEX won't work as nonclustered index are stored in heap. i.e., Heaps are tables that have no clustered index.

Solution:

Select * into #temp from tblDemoTable_nonclustered
Go

Truncate table tblDemoTable_nonclustered
Go

Insert into tblDemoTable_nonclustered select * from #temp
Go

Now check the fragmentation info to see that we have actually reclaimed the space!

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 3.6
- Scan Density [Best Count:Actual Count].......: 57.14% [4:7]
- Extent Scan Fragmentation ...................: 57.14%
- Avg. Bytes Free per Page.....................: 296.0
- Avg. Page Density (full).....................: 96.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hope this helps!

Reclaiming the table space after dropping a column - [With Clustered Index]

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index.

Create a table with clustered index in it:

Create Table tblDemoTable (
[Sno] int primary key clustered,
[Remarks] char(5000) not null
)
Go

Pump-in some data into the newly created table:

Set nocount on
Declare @intRecNum int
Set @intRecNum = 1

While @intRecNum <= 15000

Begin
Insert tblDemoTable (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109))
Set @intRecNum = @intRecNum + 1
End

If it's SQL 2000 or earlier:

DBCC SHOWCONTIG ('dbo.tblDemoTable') -- Displays fragmentation information for the data and indexes of the specified table
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 80
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 83.33% [10:12]
- Logical Scan Fragmentation ..................: 3.75%
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 33.7
- Avg. Page Density (full).....................: 99.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are using SQL 2005:

As that DBCC feature would be removed in the future version of SQL Server I would suggest the following code snippet instead of DBCC SHOWCONTIG.

Declare @object_id int;
Set @object_id = Object_ID(N'Testbed.dbo.tblDemoTable');

Begin
Select index_type_desc, index_depth, index_level,
avg_fragmentation_in_percent, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (Db_id(), @object_id, NULL, NULL , 'Detailed');
End
Go

Output:


Drop the column 'Remarks' from the table:

Alter table tblDemoTable drop column Remarks
Go


Now try out DBCC SHOWCONTIG or sys.dm_db_index_physical_stats as explained previously and verify that the details haven't changed a bit :)

Solution:

DBCC DBREINDEX ( 'dbo.tblDemoTable' )
Go

Now try out either the SHOWCONTIG or dm_db_index_physical_stats and see that you have reclaimed the space successfully.

DBCC SHOWCONTIG ('dbo.tblDemoTable')
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 80.00% [4:5]
- Logical Scan Fragmentation ..................: 8.00%
- Extent Scan Fragmentation ...................: 20.00%
- Avg. Bytes Free per Page.....................: 296.0
- Avg. Page Density (full).....................: 96.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

May be this is one another good example of why we need to have clustered index on a table :)

Similarly if you have run this DMV sys.dm_db_index_physical_stats then the output would be this:



BTW, in SQL 2005 though DBCC DBREINDEX would work, its better to start practicing ALTER INDEX syntax.

In the next post we would see how to reclaim the space in a table which doesn't have clustered index in it.

Related Article: Reclaim Unused Table space. This article was written couple of years back and was tested with SQL 2000 at that time.

Wednesday, September 26, 2007

SQLCMD -- Part VI (Scripting Variables)

To list all available SQLCMD Scripting variables, do the following:

Step 1: Go to DOS prompt and open up SQLCMD
Step 2: type :listvar which would list all SQLCMD scripting variables.

In the screenshot you can see that the SQLCMDEditor and SQLCMDINI variable which we overwrote in the previous posts here and here are displayed.


Almost all of the other variables are self-explanatory :)

Creating our own local variables:

:setvar DB1 testBed
:setvar DB2 Adventureworks

use $(DB1)
go

Select getdate();
go

use $(DB2)
go

select top 5 city from person.address
go

Point to note:

1. If you execute :listvar again you can find these newly created variables getting listed there now. But please note that these local variables would be alive only till the life of the current session. i.e., If you exit out of SQLCMD once and come back and type :listvar these variables would be missing there.

2. Always the variables which we create will be in Uppercase only. Just try creating something like this:

:setvar NaMe Vadivel
:listvar


You can see the variable got created as NAME in uppercase.

Check out these other options to get your hands dirty:

1. :help --> This would list out all the commands available for SQLCMD


2. -o --> this is for specifying output file path
3. :listvar --> I have talked about few of the variables in it. Just tryout the other variables yourself.

Hope this series of 6 posts would have given a general understanding of SQLCMD for the readers!

Tuesday, September 25, 2007

SQLCMD -- Part V (setting startup scripts)

There are instances where we might need to run some default scripts on a specified server once SQLCMD gets connected. It can be achieved in just three steps as explained below:

Step 1: Create a script file which you wanted to fire when SQLCMD gets connected to your SQL Server.

For keeping the example simple, I used the following line and saved it as SqlCmdStartUpScripts.sql

print 'Welcome today''s date is: ' + (convert(varchar, getdate()))

Step 2: Open DOS prompt and type set sqlcmdini=c:\vadivel\sql related\scripts\SqlCmdStartUpScripts.sql

Step 3: Then type SQLCMD and press Enter.

Refer the below screenshot for the sample output.

SQLCMD -- Part IV (set your favourite editor)

From my previous posts one can understand that it is possible to write SQL queries directly in command prompt with the help of SQLCMD utility.

Now let's assume we have typed a 'big' query and there is a typo there! Instead of going back and forth to edit it in command prompt won't it be easy if we are able to open the query in an editor and make the corrections there?

Yes its possible in SQLCMD. All we need to do is type ed and it will open up the last command/query in a text editor. FYI the default editor is Edit (the command line editor of MS DOS).

Step 1: Open up SQLCMD and connect to your SQLServer
Step 2: type any query of your choice
Step 3: type ed
Step 4: The query would have opened in the 'EDIT' utility of DOS. Once you are done with the change, save and exit from that.
Step 5: type go and press enter

Can I make 'ed' to open up notepad or any editor of my choice?
Yes it's possible.

Step 1: Open DOS prompt
Step 2: Type set sqlcmdeditor=notepad
Step 3: Open up SQLCMD and connect to your SQLServer
Step 4: type any query of your choice
Step 5: type ed
Step 6: The query would get opened in notepad. Once you are done with the change, save and exit from that.
Step 5: type go and press enter

Monday, September 24, 2007

SQLCMD -- Part III (Non-Interactive or batch Mode)

If you haven't gone through the first two posts about SQLCMD I would strongly recommend to go over it here and here before proceeding further :)

1. Executing a script file from SQLCMD...

i. Create a script file by typing in the following line and save it as Message.sql
print 'Welcome today''s date is: ' + (convert(varchar, getdate()))

ii. Now goto command prompt and type: SQLCMD -i Message.sql
here, -i is the switch to specify the input file name.


2. Executing series of script files (sample)

Lets create couple of .sql files and then see how to execute them in order from command prompt. Please note that i am just showing an example here :) there are better methods of doing the same which I would explain later in the series!

i) Copy paste the below script and name it as 01TableCreation.sql

Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go

ii) Copy paste the below script and name it as 02InsertRecords.sql
Insert into tblTest (Fname) values ('a')

iii) Copy paste the below two lines and name it as test.bat

sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL
Related\Scripts\sqlcmd\01TableCreation.sql"
sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL
Related\Scripts\sqlcmd\02InsertRecords.sql"

here,
-U is SQL User name
-P is SQL Password
-S is the SQL Server name
-d is the SQL database name
-i is the input file to execute

iv) Execute the batch file

Now goto command prompt (Start >> Run >> cmd) and execute this batch file. The batch file would have created a table and inserted a record into it. If you want to deploy some DB scripts on a remote box for which you don't have access from Mgmt Studio you can follow this batch file route. So that the ppl who are having access to that SQL box can just run this batch file (after changing the values of the different switches, if need be)

SQLCMD -- Part II (Interactive Mode)

First get connected into the DB Server using SQLCMD using either the windows authentication or SQL authentication as explained in the previous post.

Then on the prompt you can type in the TSQL queries directly and press enter. In the next line, say Go and press enter to execute the query and see the result. See below an example to display the current datetime.


After displaying the result the cursor would stand on the prompt expecting for further queries from us :) Once you are done you can 'exit' out of sqlcmd utility like shown below:


By default, it get's connected to the default database of that login only. One can make use of -d switch to connect to a DB of their choice. Please refer the below sample where I make use of Adventureworks DB and query a table.


What is the difference between -q and -Q switch?

-q is for running queries from SQLCMD.
-Q is for exitting from SQLCMD immediately after executing a given query.

SQLCMD -q "Select getdate()"

This query will print the current datetime and then return to SQLCMD prompt.

SQLCMD -Q "Select getdate()"

This query will print the current datetime and then exit from SQLCMD automatically.

Sunday, September 23, 2007

SQLCMD -- Part I (Basics, Connectivity)

“SQLCMD” is a command line tool which was shipped by Microsoft along with SQL Server 2005. Previously SQL Server was having ISQL and OSQL as its command line utility. SQLCMD is replacing both of them (i.e., ISQL is not there in SQL Server 2005 RTM version. OSQL would also be eventually removed!!).

When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider to execute the given queries.

SQLCMD has batch and interactive modes. 'Batch mode' can be used mainly for scripting and automation tasks, while 'Interactive mode' is for firing ad-hoc querys.

i) To list all the parameters supported by SQLCMD utility, run the following in command prompt
SQLCMD /?




ii) To connect to SQL Server using SQL Authentication
SQLCMD -U username -P yourpassword -S Servername

Once we press the enter key. If the UID/PWD is valid it would prompt you to enter the t-sql query to execute.

If at all the UID/PWD combination is wrong it would throw an error similar to the one shown below:

Msg 18456, Level 14, State 1 .......
Login Failed for user 'sa'

iii) To connect to SQL Server using Windows authentication
SQLCMD -S servername

By default, SQLCMD uses the trusted connection only. So no need of specifying -E parameter which is meant for this purpose.

iv) To connect to a named instance using the portnumber and the hostname

In the command prompt type Hostname and press enter. It would display the hostname of your PC.

SQLCMD -U sa -P hotmail -S tcp:VADIVEL,1433

Note: I have just mentioned 1433 here for example. Actually its not needed as its the default port for SQL Server.

v) How to know what is the port number configured in my box?

SQL Server's works by default with Port 1433. If it has been configured earlier to work on a different port then follow the below steps to find it out!

1) Open up 'SQL Server Configuration Manager'

2) Then double click on 'Protocols for MSSQLServer'

NEWID vs NEWSEQUENTIALID

Some pointers:

1. NewSequentialID() and NewID() both generates the GUID of datatype of uniqueidentifier.
2. NewID() generates the GUID in random order
3. NewSequentialID() generates the GUID in sequential order.
4. NewSequentialID() can be used ONLY in default clause of a table.
5. NewSequentialID() is easily predictable
6. So if security is a major concern then go for NewID() instead of NewSequentialID().

Example to demonstrate NEWSEQUENTIALID():

Create table #tblDemoSequentialID
(
Column1 uniqueidentifier default NewID(),
Column2 uniqueidentifier default NewSequentialID(),
Fname varchar(30)
)


Pump-in few dummy records:

Insert into #tblDemoSequentialID (Fname) values ('Vadivel')
Insert into #tblDemoSequentialID (Fname) values ('Rajinikanth')
Insert into #tblDemoSequentialID (Fname) values ('Sivaji')


In this query 'Column1' would demonstrate that the 'NEWID' has generated GUID in random fashion. Also 'Column2' would contain GUID in Sequential Order (refer the below screenshot).

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID



Flush the table and proceed to next demo:

Delete from #tblDemoSequentialID

Create another temp table:

Create table #tblDemoTWO
(
LName varchar(20),
Column_SeqID uniqueidentifier default NewSequentialID()
)


Lets insert data into both these tables alternatively. This is to prove that the NewSequentialID would be in sequential order :)

Insert into #tblDemoSequentialID (Fname) values ('VDSI')
Insert into #tblDemoTWO (Lname) values ('Yuvaraj')

Insert into #tblDemoSequentialID (Fname) values ('Verizon')
Insert into #tblDemoTWO (Lname) values ('India')

Insert into #tblDemoSequentialID (Fname) values ('Dhoni')
Insert into #tblDemoTWO (Lname) values ('Mahendra')

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID where [Fname] in ('VDSI', 'Verizon', 'Dhoni')

Select Lname as [Last Name], Column_SeqID as [NewSequentialID] from #tblDemoTWO


Just go through the output of both the above queries. You could find it for yourself that the NewSequentialID columns in both the tables got incremented sequentially.

Clean up :

Drop table #tblDemoSequentialID
Drop table #tblDemoTWO
Go

Wednesday, July 18, 2007

Finding missing indexes in SQL Server 2005

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables.

Query: select * from sys.dm_db_missing_index_details
Fields to note: equality_columns, inequality_columns, included_columns, statement

So by querying this DMV and having a look at these 4 columns we can decide on the types of indexes which needs to be created for our tables. Each of this column has a meaning. Either find it yourself :) or drop a line in the comment section.

One important point to keep in mind is the result you see out of running the above DMV is just a 'suggestion' on the way to improve the performance of your query. But its not 100% guaranteed that on creating all these indexes would help.

We cannot access the DMV using accounts which doesn't have permission for 'View Server State'. I would write more on these in upcoming posts.

Hope this helps!

Wednesday, July 11, 2007

Renominated as MVP again this year :)

I am glad to know that I have been renominated as a MVP in SQL Server category this year (2007 - 2008). Thanks everyone for your support and encouragement!

Query to find out indexes created dynamically by SQL Server 2000

When queries / SP's hits those tables where appropriate indexes are not there then the database engine would automatically create indexes to be used by its execution plan.

The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column.

That said, normally till SQL Server 2000 I used to execute the below statement to list down those dynamically created indexes. So that i can analyze and create indexes appropriately.

Select [name] from sysindexes where [name] like '_WA_%'

I would write more on this shortly.

Friday, May 04, 2007

Ctrl + Alt + Del in remote desktop

If you are connected to a remote desktop (Using Remote Desktop Connection) and for some reason you want to use "Ctrl" + "Alt" + "Del" it would open up the local machines dialog box only.

In order to initiate the dialog box of the remote machine to which you are connected to make use of "Ctrl" + "Alt" + "End"

Hope this helps!

Tuesday, May 01, 2007

How to find out recently run queries in SQL Server 2005?

Prior to SQL Server 2005 if we want to find out the list of recently run queries we need to depend on SQL Profiler.

Now in SQL Server 2005 the life has become more easier(!). With the help of of an Dynamic Management Views (DMV) and a table valued function we can list the required result.

Points to note:

1. sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

2. sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle

Solution:

Select
dmStats.last_execution_time as 'Last Executed Time',
dmText.text as 'Executed Query'
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc

Hope this helps!

GRANT permission to ALL stored procedures

This is one of the other very common question which I get from many of my blog readers / dotnetspider users.

Hi Vadivel,

I have created a fresh login in my SQL Server 2005 database. Now I want to grant that newly created login permission to execute any / all stored procedure within that database. Can you tell me how to do this in one shot? As of now, I am writting GRANT statement for all individual Stored procedures name manually :(

Regards
xxxxxx

Find above one of the recent mail which I received from one of dotnetspider user. I thought I would write a sample and blog it for benefit of all those people who are having similar requirement. So is this post :)

Solution:

Declare @strUserName sysname
Set @strUserName = 'Support'
Select 'Grant exec on [' + Routine_Schema + '].[' + Routine_Name + '] TO [' + @strUserName + ']' from Information_Schema.Routines Where Routine_Type = 'Procedure'


Now in the result pane the GRANT statement for all stored procedure would have been created something like this:

Grant exec on [dbo].[SampleSp_UsingDynamicQueries] TO [Support]

Just copy the whole result pane and execute it at one shot.

Find the missing numbers (GAPS) within a table...

In this post I have given one of the way to find out the missing numbers within a table. Please note the you need SQL Server 2005 to execute this example and test it yourself.

Sample table creation

Create table tblFindGaps
(
Sno int not null
)
Go

Populate dummy records in the table:

Insert tblFindGaps values (1)
Insert tblFindGaps values (10)
Insert tblFindGaps values (3)
Insert tblFindGaps values (5)
Insert tblFindGaps values (9)
Insert tblFindGaps values (11)
Insert tblFindGaps values (15)
Insert tblFindGaps values (18)
Insert tblFindGaps values (22)
Insert tblFindGaps values (100)
Go

Solution to find the missed out numbers:

Declare @intMaxNum int
Select @intMaxNum = max(Sno) from tblFindGaps;

With tempData (result) as
(
Select distinct FG.Sno + 1 from tblFindGaps FG where not exists
(
Select 1 from tblFindGaps FGP where FGP.Sno = FG.Sno + 1
) and FG.Sno < @intMaxNum

Union All

Select TD.result + 1 from tempData TD where not exists
(
Select 1 from tblFindGaps FGP where FGP.Sno = TD.result + 1
) and TD.result < @intMaxNum
)
Select result as 'Missing Numbers' from tempData order by result;

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.

I am working on SQL Server 2005 for quite sometime now. For past couple of weeks I am facing a strange error often but not always!!

Refer the screenshot below to know the actual error:


Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

My system configuration:

I am using Windows XP Media center edition with SP2 and 1 GB RAM. I have SQL Server 2005 (version 9.00.1399.00)

What's the solution?

I came across this KB article - http://support.microsoft.com/kb/899436. As per the KB article it looks like this error occurs because of a ACL issue here.

If it's an ACL issue I presume that SQL 2005 should not work for me always. I am wondering how it works for me once I restart my laptop couple of times.

Bottomline is I haven't yet found a solution for this. If at all you have run into this issu,e and have solved it, do write back to me.

Wednesday, April 25, 2007

Workaround for 'Divide by zero error encountered'

Today I just want to write a sample explaining the workaround for 'Divide by zero error encountered.' error in SQL Server.

Sample Table Structure for demo purpose

Create Table dbo.TestDivideByZero
(
WebSite varchar(50),
NumOfHits int,
Income int
)
Go


Insert dummy records

Insert into dbo.TestDivideByZero values ('a.com', 100, 20)
Insert into dbo.TestDivideByZero values ('b.com', 10, 0)
Insert into dbo.TestDivideByZero values ('c.com', 300, 25)

Insert into dbo.TestDivideByZero values ('d.com', 1300, 225)
Go


Query to produce 'Divide by zero error encountered.' error

Select WebSite, NumOfHits / Income from dbo.TestDivideByZero
Go

This would throw the below error:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Workaround

Make use of NULLIF function. Like, if the value is 0 then return NULL. For better understanding execute the below query and see it for yourself.

Select WebSite, NumOfHits / NullIf(Income,0) as ColumnName from dbo.TestDivideByZero
Go


Hope this helps!

Tuesday, April 24, 2007

Arithmetic overflow error converting expression to data type int.

Today I was trying to join two table each having approximately 5 Lakhs records in one of my SQL Server 2005 database. I was trying to find the count of some field when this arithmetic overflow error was thrown.
Arithmetic overflow error converting expression to data type int.

I understood that the calculation has exceeded the maximum INT range. For better understand on this error check out this SQL Books online article and/or this KB article.

The work around which did the trick for me is, instead of "Count" I changed it as "Count_big".

Monday, April 23, 2007

Top 20 things programmers say to testers!

I was going through the "Top 20 things Programmers says to Testers!". Its really funny and brought back lot of memories.

Out of the 20, i need to admit I use these excuses very often :)

18. "It worked yesterday!"
12. "You must have a wrong version"
1. "It works on my machine"

The other excuses which I use or heard people using it are:

1. The issue isn't reproducable.
2. It's designed to work that way!
3. You know we have done it as a value add ourself! there is no spec for this.
4. It's been fixed long time back. May be you are looking into a wrong version!
5. You know its not because of our layer! It's an error in the "Service Layer" which another team needs to look at.
6. I don't think its a show stopper. How about fixing it in the next release?

Do you have a say on this?

Sunday, April 22, 2007

A Saturday with Zoho Writer!

Today I thought of trying my luck with Zoho Writer and write my feedback about that. So is this post :)

Just a thought: Actually I am surprised that almost all of zoho's product have different UI's. Won't it look good if they create a uniform look and feel across their products?

Most of the Products have Single Sign On facility:

It's really nice to see that they have done SSO (single sign on) authentication for their products. It means, previously I have registered with Zoho Sheets and now to work with Zoho Writer I can make use of the same login.

How to enable SSO for a zoho product?

1. First login to any of their product (zoho sheet, zoho writer, zoho show, zoho project, zoho creator, zoho planner, zoho wiki and zoho chat). Yes you need to register once in any of their product.

2. In the header of the application you can see "My Account" link. Click on it to see a screenshot as shown below for your account information.




3. Now lets assume that you need to enable SSO for "Zoho Planner". All you need to do is click on "Add New" link besides it. Easy isn't it?

Just a thought: I feel that the text "Add new" is confusing. I initially thought that it would create a new account when its clicked.


Template Library

Refer below the screenshot of the initial screen after login. In the header you can find the link "Template Library".



As of now when this blog post is made there are 51 pre-build templates available which one can use to create their document. There are template like "Resume covering letter", "Different resume template" etc.,

Extract from their site:


This is a place where you can find readymade templates for your use. Whoever you are - job seeker, businessman, accountant, designer, salesman, marketer, home user - you can find a template which you can use to build your document on. And what's more, you can upload your templates here too - if you are an expert in your area of interest and have useful templates to share with others, please feel free to do so.

This feature is really cool and people who have used MS Word templates would appreciate this as well.

Search within Template Library:

I tried searching for keywords "Resume", "sheet" etc., but it didn't work for any of them. It always shows up a screen as shown in the below screenshot. May be a minor glitch which they might already be working on as well which I am not sure.


Mailing Options:


  • Any document, you worked on, can be sent to an Email ID of your choice using the "Email Out" option. We could make use of this option to take a backup of our work :)

  • There is an 'Email In' option which would be really useful. For each user they give an Email Id to which they can send in any documents which would be upload to their zoho writer account.

Most of 'MS Word' shortcut keys work here too:

I tried 'Ctrl+B', 'Ctrl+I', 'Ctrl+U', 'Ctrl+N', 'Ctrl+S' and all of them worked. Wow its really cool. I can create documents online with the same speed I used to do with my MS Word :) Out of all those, 'Ctrl+S' and 'Ctrl+N' only surprised me. The first one is for saving a document while the second one creates a new tab/document.

Source control!!

The best feature which I liked within "Zoho Writer" is maintaining document "History". I assume that each time when we edit the content and save they internally create a version of the document. Anytime we can compare between two versions and if need be we can revert back to an older version as well. This is a real cool feature and I guess "Zoho Writer" team have done their homework really well.



Just a thought: I guess "Show Diff" has some issues. Most of the time nothing happened when I clicked on it. May be the team wants to go through the code base of that functionality once again!

It also has an option to export the document into various format like, "PDF", "RTF", "Text file", "HTML" file etc., Over all the product looks really impressive. The interface is cool and I felt like working in a client based software rather than an online word processor.

That said, I also want to convey that I faced quite a few alignment issues and in few places it threw Javascript errors as well. But those aren't show stoppers and more over it is still in Beta. Hopefully all these minor issues would be fixed before they remove the Beta tag. Btw, if you run into any issues you can file it here.

Don't forget to check out my Awards / Recognition / Achievements document which I created using Zoho Writer :) I plan to update this document when ever I achieve something(!). I would also put it up on the right navigation for easier access.

Related Posts:

1. Interview with Product Manager of Zoho Sheet
2. [Product Review] SwisSQL SQLOne Console 3.0

Technorati tags: , ,
kick it on DotNetKicks.com

Thursday, April 19, 2007

AutoEventWireup




<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="true" Inherits="Sample2003Application.WebForm1" %>



AutoEventWireup -- It's a boolean field. By default in VS.NET 2003 it would be "false". More over this attribute is applicable only for applications which are created via VS.NET 2003.

If it's set to "True", the ASP.NET runtime does not require events to specify event handlers like Page_Load etc.,

Once you create a new webform in VS.NET the 'AutoEventWireup' attribute of that page would be false. Open the code behind file and check out the 'InitializeComponent' it would be something like this:

private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}


Within Page_Load even print a message into the screen.

private void Page_Load(object sender, System.EventArgs e)
{
Response.Write("We are in Page Load event.");
}

This message "We are in Page Load event." would be printed on this screen.

Now set the autoeventwireup attribute to "True" and then comment the below line:
this.Load += new System.EventHandler(this.Page_Load);

When the application is run we can still find the message getting printed on the screen. i.e., when it is true VS.NET automatically wires up the event for you.

It would be good to have a look at this article on Event Handling in ASP.NET

Saturday, April 14, 2007

Export data from SQL Server to Excel without using SSIS or DTS

Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on:

Step 1: Execute the code snippet

Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go

Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns.

Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts

Points which might interest you:

1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).

2. Instead of "ProductInfo" replace it with your excel worksheet name.

Sunday, April 01, 2007

ToonDoo from Jambav ... 2

I came to know about ToonDoo from D. Rajendran (who is a Product Manager in Jambav) and I am glad he introduced this to me. I created couple of toons and have blogged about it here. I really liked this toonDoo concept and i am playing with it for past 2 days like a kid :)

Actually I wanted to write about "Jambav" in June '06 itself. But as I was bit busy couldn't do that (believe me i was busy only lol). Now somebody has beaten me and has interviewed "Rajendran Dandapani" about him and Jambav. He has spoken about "What is Jambav and why that name?", "about the motivation to start Jambav", "About him and his responsibilities", "Key technologies used", "their business model" etc., I strongly recommend everybody to go through it without fail.

That said, there are few things which I am not sure about this tool ...

1. How much load can their server take? Because very often I find that the server is down (more than thrice last night). {My internet connection had no issues :) as other sites were working fine}.

2. I don't see a FAQ section? I actually wanted to know "how a toon is choosen to be listed under Editor's Pick", "How to change my password?", "What is the Dooers section in the home page?", "How to delete a toon?" etc.,

3. After publishing one of my toon, I edited it twice and when I republished the alignment of the pictures and text went for a toss. All I did was to republish it few more times to solve it :)

4. I tried pasting "Flash based scroller" into my blogger account but it throwed an error saying "Error Loading... Sorry! www.toondoon.com". May be it doesn't work with Blogger?

5. From their "About Us" section I understand that "Jambav is devoted to creating a unique array of free and customizable online games of educational value for children of all abilities". They say its for kids but they allow "Adult" category to be created! {I saw toons tagged to adult, blowjob!!!, sex etc.,}. Am I missing something?

Technorati tags: , ,

ToonDoo from Jambav ...

Jambav (backed by Adventnet) has launched another cool tool few days back by name "ToonDoo" (Cartoon Strip Creator).

You can check my really exciting works below :)

Sivaji
[Updated the copy: As usual there were lots of typo in my Indian Cricket toon which I have changed it now].
Indian Cricket

BTW, at present there is a contest at ToonDoo. Create any number of toon and show the world what you are thinkinng abt it. What are you waiting for? Go get registered and start playing with it.

Techcrunch has a good write up on ToonDoo here

Technorati tags: , , ,

Tuesday, March 20, 2007

Autocomplete features of ASP.NET

Most of us would be very much familiar with "Autocomplete" feature of various browsers. After turning on that feature if you try filling any textboxes the browser provides you with the values which you have already typed in that field before.

What if the "autocomplete" feature is turned ON in the end users browser but you don't want the data to be catched by the browser?

Its quite simple. All we need to do is make use of the attribute "autocomplete" within the pages form tag.

Example 1: Disabling Autocomplete for the whole form

1. Create a sample web form and replace that default form tag with the code snippet below

<form id="form1" runat="server" autocomplete="off">
<asp:TextBox ID="txtOne" runat=server></asp:TextBox>
<asp:TextBox id="txtTwo" runat="server" ></asp:TextBox>
<asp:Button ID="btnGo" runat="server" Text="Submit" />
</form>

2. Now enable autocomplete in the browser. If its Internet Explorer do this: Tools >> Internet Options >> Content >> Autocomplete >> Check "Forms".

3. Run the default page and you could test it for yourself.

Example 2: Disabling autocomplete for a particular textbox in a form

1. Lets add "Autocomplete" feature to second textbox and set it as "off"

<form id="form1" runat="server" autocomplete="off">
<asp:TextBox ID="txtOne" runat=server></asp:TextBox>
<asp:TextBox id="txtTwo" runat="server" Autocomplete="off" ></asp:TextBox>
<asp:Button ID="btnGo" runat="server" Text="Submit" />
</form>


2. Run the page and you should get the autocomplete dropdown values for txtOne and for txtTwo it won't appear.

[I have tested it with ASP.NET 1.1 as well as 2.0]

Hope this helps!

Friday, March 16, 2007

Restarting a remote computer via Remote Desktop ...

If you want to restart a computer which you have connected via Windows Remote Desktop do this.

Start >> Run >> shutdown -r -f

The machine would be restarted after 30 seconds automatically

There are lots of other options available with shutdown command. You can view all the available options by typing "shutdown /?" in the command window.

Sunday, March 11, 2007

Server Application Unavailable

Error Message:

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note: An error message detailing the cause of this specific request failure can be found in the system event log of the web server. Please review this log entry to discover what caused this error to occur.

Error in Event Viewer: aspnet_wp.exe could not be started. The error code for the failure is 80004005. This error can be caused when the worker process account has insufficient rights to read the .NET Framework files. Please ensure that the .NET Framework is correctly installed and that the ACLs on the installation directory allow access to the configured account.


OR

aspnet_wp.exe could not be launched because the username and/or password supplied in the processModel section of the config file are invalid.

Solution: Check out this microsoft support article >> http://support.microsoft.com/default.aspx?scid=kb;en-us;811320

For me, particularly after running this command line script it started working.

cacls C:\WINDOWS\assembly /e /t /p domain\ASPNET:R

Friday, March 09, 2007

[Non Tech] Want to know the recipe for Omelette :)


Fed up with Bread - Jam and Curd Rice, today i wanted to eat Omelette. Interesting part is I wanted to cook it myself :)

So in the first picture you see all the items which are needed for preparing an Omelette.
When I had a closer look at the eggs I see that almost all the eggs are broken. But believe me when I bought it couple of days back it was in perfect condition!


I was wondering whether the eggs have become rotten or pretty old to consume! I tried taking an egg and break it but couldn't break it at all :)

Since I have kept in the freezer all the eggs have frozen and looked like a iron ball :)

After trying for few minutes of trying i removed the shell of the egg and then kept that iron ball :) into a bowl and placed it within Oven. I heated it for 1 minute and checked. It melted only to a limit. So i just set it for another 2 minutes and checked it later. It has melted but the part of the egg white has become a Omelette :(

I didn't leave it there. I took the bowl out of the oven and beated it as much as possible in that bowl. Then i added a chopped (half) onion and a tomato into that bowl. Then a tablespoon of salt and black pepper was also added into that.

After that, I poured little oil into the omelette maker (blue box which is in the first image) before pouring the egg mixture and placed it inside the Microwave Oven for 4 minutes.
After 4 minutes the final product was ready (see the below image).

Lesson Learned: Don't keep the eggs within the "Freezer" box of the fridge :)

Wednesday, March 07, 2007

Max limit of Varchar, nvarchar, varbinary datatypes ...

As you would be knowing by now in SQL Server 2005 they have enhanced varchar, nvarchar & varbinary as varchar(max), nvarchar(max) & varbinary(max). So now they can hold upto 2^31-1 bytes (it would come to approx 2 GB).

That said, don't think that just because MAX has been introduced you can declare those datatypes for any arbitary number. If you want to specify a limit yourself for those datatype still the maximum is 8000 characters. When you think that you need to store content whose lenght would be more than 8000 characters then you need to declare it as MAX.

For example, Try executing the below code snippet

Create table testVarchar
(
fName varchar(8001)
)
Go

It would throw an error something like this: The size (8001) given to the column 'fName' exceeds the maximum allowed for any data type (8000).

Monday, February 26, 2007

Calling Cards for India ...

For past 2 days I am using USP long distance phone Card to speak with my wife (Sai). The call time got over today morning. Most of the people here in the hotel said that they are using "Reliance India Calling Card". More importantly reliance is running an offer now and its named as "Valentine Offer". i.e., Pay USD 5 and talk for 120 minutes. It's a cool offer. So thought I would register in Reliance and get hold of that offer today.

Their registration page is this https://www.relianceindiacall.com/US/fp_reg_step1.asp but one can't sign-up if we don't have a mobile /Land Line here in US!

They themself say "Please do not register with a hotel/hostel/public phone numbers to avail Reliance India Call service. Instead you can use an auto-generated number for registration under Prepaid plan.". The last statement which talks about auto-generated number doesn't hold good till Feb 28th (thats when this offer ends!).

I called up their customer care (1-866-373-5426) and I was informed the same! I was curious and told them that people were able to sign-up with their "India Mobile" number till yesterday night. She made me hold the line and after a while the response she gave me was "though people could have resigtered it won't work for them properly!!!". What the hell is it?!?!?!

I was really disappointed and am still wondering why they can't create a PIN number and send it for those people who doesn't have a "Land Line" or "Mobile number" in US. That way, the user can call to their home from any phone and all they need to do is enter this PIN first for validating that they are the owner of this account.

Anyway people who have registered from the same hotel till yesterday night are really lucky (as it is working for them well till now) lol.

So I went back to USP Card itself. My friend who was here previously was using this and he is one who recommened me this card. If you are in US and want to make calls to your dear one's in India or other countries sign up here http://www.uspcard.com/sign_up.php?rcode=PDJ1CUFS6

Tuesday, February 13, 2007

Javascript: Proper case validation

I am just posting a Javascript sample which I created for answering a question in dotnetspider.

<html>
<head>
<title>Proper Case Validation -- Sample by Vadivel</title>
<script>
<!--

function ConvertToProperCase()
{
strTemp = document.form1.txtString.value
strTemp = strTemp.toLowerCase()

var test=""
var isFirstCharOfWord = 1

for (var intCount = 0; intCount < strTemp.length; intCount++)
{

var temp = strTemp.charAt(intCount)
if (isFirstCharOfWord == 1)
{
temp = temp.toUpperCase()
}

test = test + temp
if (temp == " ")
{
isFirstCharOfWord = 1
}
else isFirstCharOfWord = 0
}
document.form1.txtString.value = test
}
// -->
</script>
</head>

<body>

<form name=form1>
<input size=50 name=txtString>
<input type=button onClick=ConvertToProperCase() value="Convert It Now">
</form>

</body>
</html>

Update: I have tested it with IE 6.0 alone. May be for working in other browsers you need to do little bit of tweaking. For ex: need to put semi-colons at the end of each statements.

Monday, January 22, 2007

Are you looking for loans in Chennai?

My sister (Sumathi Maraimallai) was working with Citi Shelters for approx 3 years as a Manager for Personal Loans section. After that she moved to another company for better career growth and now she has become a DSA (Direct selling agent) of few MNC banks along with her friend Mr. Roshan (who was also working previously with Citi Shelters for approx 6 years).

On the other day she was asking for some referals. I said, I don't want to put people in trouble by giving away their mobile numbers :) Rather I thought I would put a word across here for the benefit of others.

If at all you are looking for Personal loans then you might want to make a note of their contact numbers [Feel free to refer my name so that they would realize that I am also capable of giving them business lol]:

Mobile - 9841283226
Land Line - 664561901 / 02 /03
E–Mail: corporateloans (at) airtelbroadband.in

Couple of questions which I thought people would ask by default:

1. What is the duration for getting a loan approved?
The loan process tentatively takes about 7 working days for sanction.

2. What are the basic details one need to furnish while applying for loans?

These are the documents required:
(a) 1 Photograph
(b) 2 Latest Payslips
(c) Last 3 months bank statements
(d) Address Proof (Ration Card, Passport, utility bill, Driving licence, Phone Bill, Voters ID, Letter from your organizations HR) -- This should reflect the current residential address.
(e) ID Proof (PAN card, Passport, Voters ID, Driving licence)
(f) Signature Proof (PAN card, Passport, Driving License, Bank Verification)

Apart from "Personal Loans" if at all you are looking for any other types of loans feel free to discuss with them. Based on their network they can accommodate your need on a case to case basis.

If possible, just spread the word around and help them succeed in their business.

Wednesday, January 17, 2007

Removing unwanted spaces within a string ...

Removing leading and trailling spaces is pretty easy. All you need to do is make use of Ltrim and Rtrim function respectively. But there are times when you want to remove unwanted spaces within a string. Check out the below code snippet to know how to do it.

--Declaration and Initialization
Declare @strValue varchar(50)
Set @strValue = ' I Love you ! ' -- Here between each word leave as many spaces as you want.

--Remove the leading and trailing spaces
Set @strValue = Rtrim(Ltrim(@strValue))

--Loop through and remove more than one spaces to single space.
While CharIndex(' ',@strValue)>0
Select @strValue = Replace(@strValue, ' ', ' ')

--Final output :)
Select @strValue

Monday, January 08, 2007

An error has occurred while establishing a connection to the server.

Error Description :: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Solution 1:

Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area Configuration for Services and connections.

Within this check whether "Local and remote connections" is choosen. If not choose it :)

Solution 2:

Check this URL http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1 :)

Technorati tags:

Sunday, January 07, 2007

Why isn’t there any official message from Microsoft?

Microsoft announced a “BlogStar” contest last year and the winners were announced in the first week of November 2006.

How do I know that I am one among the winners?

On November 5 or 6th I got a call from one Ms. Bharathi claiming to be working in Microsoft. The number from which she called is 91-80-65605725. She said that I have won a prize and needed my size and full contact address to ship a jerkin. As the line wasn't clear I couldn't hear properly for what exactly is this gift for? But at that time Blogstar was the only Microsoft competition I was participating so I presumed it to be that.

So, you got a call from Microsoft employee and hope you have received your prize as well! What else are you asking for?

Excuse me :) As the female's communication was not that professional I thought its some spam caller and asked her to mail me the reason for requesting my contact address so that I can communicate my address back to her official ID. That said, it’s almost two months now and I am yet to hear from her :)

More over I was bit puzzled as Microsoft already has my shipping address (as I am a MVP)! May be there are some confusion in the way client/customer informations are stored within Microsoft!! I thought based on my email ID they should be in a position to fetch the contact details with ease! Am I missing something here?

Few days later I saw the winners list announced here: http://www.microsoft.com/india/blogstars/winners.aspx

I am really wondering why there isn't any official mail communication from Microsoft regarding this which would have avoided lots of confusion!! I even checked with few other winners on whether they have got any official communication from Microsoft? The response is big NO :)

Below is the extract from Microsoft site

“Over the next few weeks, the Top 20 BlogStars will meet with Tarun Gulati, General Manager - Developer and Platform Evangelism, Microsoft India and Microsoft IDC Products teams, and discuss the future roadmap of Microsoft technologies. That's not all - these Top 20 blogstars go on to form the first ever - Microsoft Blogging Leadership Cell (MBLC).” --- This too hasn’t happened yet. Hope this would occur sometime in future :)

Related Posts: http://vadivel.blogspot.com/2006/11/i-am-one-of-microsoft-blogstar-winner.html

Technorati tags: ,

Saturday, January 06, 2007

Google has decided to dedicate a page for me :)

Due to my enormous presence in the online world, Google has decided to dedicate a separate home page for me. You can check it out in the following url : http://googlemyway.com/Vadivel

Technorati tags: ,

Thursday, January 04, 2007

List tables that doesn't participate in any relationships

This query returns those tables which satisfy the below two conditions:

1. Tables that do not contain any Foreign Key referencing other tables.
2. Tables that are not referenced by other tables using foreign key constraints.

Solution:

Till SQL Server 2000 days we used to write the below scripts [This still works with SQL Server 2005 also].

Select [name] as "Orphan Tables" from SysObjects where xtype='U' and id not in
(
Select fkeyID from SysForeignKeys
union
Select rkeyID from SysForeignKeys
)

Solution which works only with SQL Server 2005:

Method 1:

Select [name] as "Orphan Tables" from Sys.Tables where object_id not in
(
Select parent_object_id from Sys.Foreign_Keys
union
Select referenced_object_id from Sys.Foreign_Keys
)

Method 2:

Select ST.[Name] as "Orphan Tables"
from Sys.Foreign_Keys as SFK Right Join Sys.Tables as ST
On ST.object_id = SFK.parent_object_id Or
ST.object_id = SFK.referenced_object_id
Where SFK.type is null

Technorati tags: ,

Wednesday, January 03, 2007

How to find the number of days in a month

This seems to be one another frequently asked question in the discussion forums. So thought would write a small post on this today.

With the help of built in SQL Server functions we can easily achieve this in one single T-SQL statement as shown below.

Select Day(DateAdd(Month, 1, '01/01/2007') - Day(DateAdd(Month, 1, '02/01/2007')))

Generalized Solution:

We can generalize it by creating a "User defined Stored Procedure" as shown below:

Create Function NumDaysInMonth (@dtDate datetime) returns int
as
Begin
Return(Select Day(DateAdd(Month, 1, @dtDate) - Day(DateAdd(Month, 1, @dtDate))))
End
Go

Test:

Select dbo.NumDaysInMonth('20070201')
Go


Technorati tags: ,

Monday, January 01, 2007

Find tables which doesn't have Primary Key

The below queries would list down the tables which doesn't have Primary Key in it.

In SQL Server 2000 :

Solution 1:

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and
Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0 and
Objectproperty (Object_id(Table_name), 'TableHasPrimaryKey') = 0


Solution 2:

SysObjects :: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. No prizes for guessing 'U' refers to user tables, and 'PK' refers to Primary Keys :)

Select [name] as "Table Name without PK"
from SysObjects where xtype='U' and
id not in
(
Select parent_obj from SysObjects where xtype='PK'
)


SQL Server 2005:

Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views. [sys.tables is one of many catalog views introduced in SQL Server 2005]

Solution 1:

Select [name] AS table_name
from sys.tables
Where Objectproperty(object_id,'TableHasPrimaryKey') = 0

Solution 2; [Display with schema name]

Select Schema_Name(schema_id) AS schema_name,
[name] AS table_name from sys.tables
Where Objectproperty(object_id,'TableHasPrimaryKey') = 0
Order by schema_name, table_name


Technorati tags:,