Posts tagged TipsNTricks

Renaming a Column in a Temporary Table in SQL Server

0

For a lot of weird yet necessary reasons, you may need to create a temp table in your query. But for a very rare weird reason you may need to then rename one of the columns. Perhaps you’re creating the temp table in one stored procedure, and modifying it in another. I’m not going to tell you that’s a silly thing to do, or you should just create it with the right names in the first place – I saw those kind of responses online, and it’s very unhelpful! Sometimes you just need to do this type of thing.
It’s not a straightforward thing to do, though. When you just run the below script that calls sp_rename:

if object_id(‘tempdb..#Test123’) is not null drop table #Test123
create table #Test123 (field1 int)
exec sp_rename ‘#Test123.Field1’, ‘Field2’, ‘COLUMN’

…you get this error: "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."
The key is to call sp_rename from the tempdb, like so:

if object_id(‘tempdb..#Test123’) is not null drop table #Test123
create table #Test123 (field1 int)
exec tempdb..sp_rename ‘#Test123.Field1’, ‘Field2’, ‘COLUMN’
select * from #Test123

And – success! Note that you’ll still have problems referencing the specific fieldname that was renamed. I solved this by doing a select into another temp table. Also, the procedure you’re doing this in will probably have to do a recompile – that wasn’t a problem for me, either. Overall, it was a better solution than the alternatives.

How to AutoLogon in Windows 7 on startup

0

I am sure most of you would be using Windows 7 by now. Its one hefty upgrade from XP (Vista doesn’t count :D).

One thing which I miss the most is the TweakUI plugin. It was a nice little app which lots of features to customize your Windows Experience. One of them was AutoLogon.

What basically it used to do was, in Windows when you have enabled the Welcome screen (enabled by default), and if your Windows have only one user account then it directly boots to the desktop but as when you have multiple users, the Welcome screen starts showing up on boot.

This gets irritating mainly for developers and IT pros, as they create accounts for various apps. Like I had to create a user with sufficient privileges for SQL Server. If you have multiple users and do not want the boot to stuck up on Welcome screen then follow this simple trick for Windows 7.

  1. Click on Start and then enter the following command in the search box:

    netplwiz

    Press the ENTER key.

    This command will load the Advanced User Accounts control panel applet.

AutoLogon-Windows7-1

  1. In the Users tab, uncheck the box next to Users must enter a user name and password to use this computer.

  2. Click on the Apply button at the bottom of the User Accounts window.

  3. When the Automatically Log On dialog box appears, enter the user name you wish to automatically login to Windows 7 with. Then enter your account password in the two fields where it’s asked.

AutoLogon-Windows7-2

  1. Click the OK button.

  2. Click OK on the User Accounts window to complete the process.

    From now on, when your PC starts up, Windows 7 will logon automatically.

Finding Duplicates with SQL

0

Here’s a handy query for finding duplicates in a table. Suppose you want to find all names in a table that exist more than once:

SELECT name,
COUNT(name) AS namecount
FROM users
GROUP BY name
HAVING ( namecount > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT name
FROM users
GROUP BY name
HAVING ( COUNT(name) = 1 )

Also see How to remove duplicate rows from a table – Microsoft Knowledge base article.

How To Enable Hibernate Option In Windows 7

0

Dear Reader,

You all must have heard of Windows 7 by now, and also heard of ‘rave reviews’ about it. Windows 7 truly is a great product which does what it says ‘Your PC, Simplified’. You can read my blog entry ‘Windows 7 : Just GO for IT!!!‘ to know more about Windows 7.

With every version of Windows comes a new set of Tips and Tricks which allows you to get the most out of your Windows. Infact, its this little feature which makes Windows so lovable, the ability to ‘customize’. And Windows 7 has loadzzz of them to offer. Here is one of them.

In Windows XP enabling Hibernate option was a very easy task, but we have to follow a different approach to do the same job in Vista and Windows 7.

If you are not aware of Hibernate feature, Hibernation is a power-saving state designed primarily for laptops. While sleep puts your work and settings in memory and draws a small amount of power, hibernation puts your open documents and programs on your hard disk and then turns off your computer. Of all the power-saving states in Windows, hibernation uses the least amount of power. On a laptop, use hibernation when you know that you won’t use your laptop for an extended period and won’t have an opportunity to charge the battery during that time.

So if you are really going to use this feature then you need to enable it by doing a simple procedure as mentioned below:

1. Open Command Prompt with Administrator rights. To open Command Prompt, type CMD in Start menu and then hit Ctrl + Shift + Enter to open the Command Prompt with Admin rights.

2. Next, type the below command and hit enter:
powercfg /hibernate on

3. Type exit and hit enter to close the Command Prompt.

4. If you still can’t see the Hibernate option in Start menu then do the following tasks:

A. Type Power Options in Start menu and hit enter.

B. In the left pane, open the link labeled “Change when the computer sleeps” and then open the link “Change advanced power settings”.

C. Under the Advanced Sleep options, expand the Sleep tree and turn off Hybrid Sleep.

D. Now go back to Start menu to see the new Hibernate entry.

Viewing Shared Folders Info

0

Here’s how to view details about your shared folders in Windows XP, including who’s connected and what files they are accessing.

– Open the Start menu, click ‘Run’, and type “fsmgmt.msc“. A windows titled “Shared Folders” opens which lists the shared folders and their details.
Shares lists the currently shared folders.
Sessions lists the currently connected users.
Open Files lists the files being accessed by connected users.

Go to Top