Query Shortcuts in SQL Server 2005

There is a nice feature which I did not come accross until recently. That is Query Shortcuts. This can be very helpful when executing regularly used queries.

For example consider SELECT * statement that we use.

When ever we need to get the full record set of a table we are writing SELECT * FROM tblTable. So if we have 10 tables we need to write the same SELECT * FROM part 10 times or we may need to copy and paste that. Also mosstly, as developers we are using this statement for temporary purposes to just see what is there inside the table later we will delete the queries. In such cases the query shortcuts come in handy.


But to use them you have to set them up.
1. Open SQL Server Management Studio (SQL SMS) and go to Tools -> Options..., this will bring up the Options page.
2. In that, expand the Environment category and click on the Keyboard.
3. Now select a key combination which is easy for you to use (I selected Ctrl + 3.). Then in front of the key combination under Stored Procedure type SELECT * FROM and press ok.

Setup is done, now we'll use it.
4. Now open a new Query Window in SQL SMS. Then drag a table from the Object Explorer.
5. While the table name is selected, press the key combination (My case it is Ctrl + 3.).


See what happened?

It will run the SELECT * FROM tblTable query even though the query window is just having the table name.
Not only SELECT * FROM you can run what ever queries by using this method. But a limitation which I see is that we cannot create our own key combinations (I prefer assigning Ctrl + S or Ctrl + Space for SELECT * FROM rather than Ctrl + 3.).
I think this is useful to many developers to improve their productivity.

Nice Windows Shortcut

Do you know that you can use both sides of the title bar to close an application in Windows?
Obviously in the right side you have the Close button which you can left click to close the application.
What about the left side? Yes If you double click on the left side then also application will close.

• Windows XP and earlier

In the left corner of the title bar there will be a small icon , if you double click on this that will trigger the same action of application exit.

• Windows Vista

In vista there will be no icons, but if you click on the left corner that will still trigger the application exit.

• Office 2007

The new Office version will have a big circle on its top left corner . If you double click on or near this again the application exit will be triggered.

Wireless Electricity

Did you get to know that people are trying to send electricity wireless. Even though this is still at testing I think when this succeeds lot of people will enjoy true wirelessness. Since nowadays even we have wireless devices we need a cable when charging them. This will remove that disadvantage.

Read more at,
http://gizmodo.com/gadgets/notag/mit-dudes-develop-mind+boggling-wireless-electricity-267238.php.

Intel Core 2 Quad Processors





Intel has released Intel Core 2 Quad Processors, these are capable of handling 4 threads simultaneously.
Read and experience more at Intel.

http://www.intel.com/products/processor/core2quad/index.htm
http://www.intel.com/products/processor/core2quad/demo/demo.html

All recent processors.
http://www.intel.com/performance/desktop/dighome/index.htm

Debugging Web Services

I tried debugging some Web Methods (WM) in a Web Service (WS) using Microsoft Visual Studio 2005 and noted the following things.

When your WM having simple parameters (Integers, Strings) if you run your project internet explorer will open up with text boxes to enter your parameters. After entering there will be a button named Invoke to invoke the WM. That will invoke your WM with the parameters you have provided.

But the thing is if your WM is accepting complex types as parameters (Objects, Datasets, Tables {Even though it is not recomended to supply complex objects like datasets to WMs there may be scenarios that it will save lot of development time of yours.}) then you will not see either textboxes or an Invoke button. As a result you will not be able to debug the WM.

Do not worry because I found the way to debug.

First you have to publish your WS to Internet Information Services (IIS). To make things simple for you Visual Studio will do the publishing for you. Before that make sure that your IIS is running by typing http://localhost/ in your web browser.

Then right click on your WS project in the Solution Explorer and click on Publish.... This will open up the Publish Web form to you. In that enter the target location as shown. Remember to change the IP address used to your IP address or you can put your machine name or localhost instead using IP address.



Click on Publish. This will create a virtual folder inside IIS and your compiled WS will be copied in to the virtual folder. Now your WS is hosted. In some cases you may have to give permission to the WS. If you needs then open IIS Manager by typing 'InetMgr' in the run windows. Browse to your newly created virtual folder, right click on the folder and click on properties.



In WebService properties window check if 'Script Source Access' check box is checked, if not then check that and click Apply and click Ok.

Now we have published the WS, we'll setup debugging.

Right click the WS in the Solution Explorer and click Properties. In the project properties page click on the Web tab. In that as the default, WS will be set to run on a virtual server comming with Visual Studio. We have to change this because we are not been able to debug on virtual server. To do this select the radio button 'Use IIS Web Server' and set the project URL to the same location you entered in the Publish Web screen.



Now close the WS properties screen and press F5 or Debug -> Start Debugging. This will start the Internet explorer and will load your web service. Keep that as it is and put some breakpoints in the WS code.

Then use the web service. I did use the WS from a PDA application. When ever the call is made the web service will break at the breakpoints you have put then you will be able to debug as usual (using the windows as such Immediate, Watch, etc)

Conclusion
In Visual Studio normally it will start the WS in its virtual server. We have to point that to IIS to make it debuggable. That can be done by setting Web properties in the WS properties.

Making a normal Column, an Identity column in SQL Server CE

In SQL CE making a normal column an identity column is not possible. This is because the process involved in the making of identity columns. If you would like to know more about the Identity creation behaviour please read my earlier article.

http://arjunachith.blogspot.com/2007/08/making-normal-column-identity-column-in.html

In SQL CE the process mentioned in the article is not possible because that SQL CE does not support enabling and disabling IDENTITY INSERT.

If you did try it out, should have noted that even SQL CE table editor is not supporting this.

So if you really want to change a column in to an identity column (which already having unique values) then what you can do is, create a new table and copy the data your self. But the problem is that you will not get the old values in the identity column because there is no way to copy them without turning the IDENTITY INSERT off. So identity column data will be new from 1 to n, But all other information will be there in the table with the identity column in place. If you even wants to keep the old identity values you can keep that old identity column and create a new column for the Identity column.

Making a normal Column, an Identity column in SQL Server

Do you know that you cannot just change a normal column into an identity column in SQL Server? This is because that identity columns can only be created when creating new tables.

Now some people will say that how cannot that be? Because they are used to change using the SQL Server Management Studio (SQLSMS). What management studio will do is, it will create a temporary table with the Identity column and copy all your existing data there. Later it will delete the original table and will rename the temporary table to the original.

For example we will take Sales.Individual table in the AdventureWorks database.



Think that we need to add a column named Id as an identity column. What we will do is just type after ModifiedDate column and make the new column an identity column.

In such a case when we save the change SQLSMS will first create a temporary table which matches the new schema with the identity column.

Then it will set IDENTITY INSERT OFF and will copy the data from the original table to the newly created temporary table.

After this it will delete the original table and will rename the temporary table as the original table.

Did any one thought that the SQLSMS is doing this much of work for us without our knowledge?