Vadivel's blog

From Denis blog I came to know recently that we are able to use sp_executesql without declaring the variables! It really sounded strange to me. I thought it to be one another bug :) So thought would dig deep into this and see what best I can conclude. --Dummy table schema Create table tblQuestion1 ( sno int identity, fname varchar(50) ) Go --Lets add some dummy records into the table Insert into tblQuestion1 values ('Alpha') Insert into tblQuestion1 values ('Beta') Insert into tblQuestion1 values ('Gamma') --So-called strange script Declare @strFetchData nvarchar(100) Select @strFetchData = 'Select * from tblQuestion1' Exec sp_executesql @alpha = @strFetchData If you have noticed we haven't declared @alpha in the script but SQL hasn't complained about it! I thought the easiest way to understand would be to go through sp_executesql procedure. Sp_helptext sp_executesql go Result - "(server internal)" … so its not a stored proc. I tried a...

From the day I saw Google Finance i was thinking what tool they would be using to display those cool, user friendly charts! After hours of searching on the web I learned that they were using some sort of custom in-house build tool/logic. Fair enough, but that doesn’t solve my problem. I wanted to create some graphs similar to that for few of my projects. On further ‘googling’, just recently I stumbled upon amCharts which has the similar charts like Google finance. You can download and use all amCharts products for FREE . The only limitation of the free version is that a small link to this web site will be displayed in the top left corner of your charts. Btw, if you don’t want the link go ahead and purchase the product. del.icio.us Tags: amCharts , Google    Technorati Tags: amCharts , Google

1. Browser – IE 8.0 and/or Google Chrome 2. Anti-Virus – One of the lightweight software is AVG. Check it out @ http://free.grisoft.com/ 3. Offline thesaurus and dictionary - http://wordweb.info/free/ This is a cool app which sits on your task bar. For finding meaning of a word just block the word and press CTRL + Right click on it. 4. Photo Organizer – I love Picasa and am using it for quite sometime now without any issues. http://picasa.google.com/download/ 5. Creating / Editing blogposts - http://writer.live.com/ 6. Zip Compression utility – I was using Winzip then WinRar but offlate I have started using 7-zip an open source project . Download it here http://www.7-zip.org/download.html 7. Instant Messaging – I was using MSN and Yahoo. But offlate I am using only Gtalk or the Chat feature which comes embedded within Gmail. May be you might want to try out Skype if you love talking more than typing :) 8. Download Manager – I use Free download Manager for quite s...

Anders Haig talks about 10 ways by which we can save time in building a website (based on his freelancing experience). Points which he has covered are: [he has given a good description of each topic with reference links] 1. Dropdown menus 2. Image capture 3. Batch image resizing 4. Web forms 5. CSS text boxes 6. Vectorizing Images 7. Selecting color schemes 8. Creating a Patterned Background 9. Building a quick photo gallery 10. Testing your website in multiple browsers Read the full article here - http://reencoded.com/2008/08/04/10-ways-to-save-time-while-building-a-website/

Deefbox is a simple but very important tool from CQsight . This tool would help us (site owners) to understand in real time on how useful our site is to others. In other terms it would help us to understand the search pattern of the users and whether what they came looking for is available in our site or not. Setting up the tool is pretty simple. Just follow up the instructions given here http://www.cqsight.com/HowWork.aspx After you signup and setup the script in your site periodically watch out the dashboard which they would provide you in cQsight to understand your visitors usage pattern. I don't want to write a tutorial for this tool :) instead would like people to try it out themself and see how simple and powerful the tool is.

By this time you would have heard about ' Cuil ' - a search engine developed by ex-top engineers of Google. I tried our few searches in that and as of now I am not pleased with the search results. I came to know from their site that "Cuil is an old Irish word for knowledge" 1. Search Result looks like a news site. This is not a complaint :) May be I am used to the search results page template used by Google , Yahoo , Microsoft . But I like this Cuil result template as well. 2. As usual I tried searching for my name ( 'Vadivel' ) and found that Cuil wasn't able to find my blog (atleast it was not available within the first two pages) whereas Google was able to show my blogspot in the first page itself. I even tried changing the search string as Vadivel Blogspot still it wasn't able to pickup! 3. If a search string has multiple meanings they provide those options as a menu style. For ex: Find attached the screenshot which I took by searching SQL 4. Explo...

For some reason I am not interested in renewing my MVP award this year. So I haven't applied for it yet. That said, hopefully i can be called as a MVP till this month or next month end max :)

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...

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?

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.

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.

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.

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. sel...

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 :)

Cursor = Cur rent S et O f R ecords

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.s...

Bill Gates turns 52 today :) Happy Birthday BillG.

I removed all of my VZ friends blog from my blogroll and have added onto my bloglines account.

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 th...

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 l...