Search Results

Keyword: ‘Multiple Select’

How to find multiple/duplicate rows with the same value

June 27th, 2008 Rahmansaher No comments

Just a short SQL script which will allow you to find the mutiple rows with the same values in the same table. I am not going to explain what the query does since its a very straight forward query

SQL:
  1. SELECT [column_name]
  2. FROM [tablename]
  3. GROUP BY [column_name]
  4. HAVING count(*)> 1
  5. --  OR
  6.  
  7. SELECT * FROM [TableName] WHERE [ColumnName] IN (
  8. SELECT [ColumnName] FROM (
  9. SELECT count([ColumnName]) AS N, [ColumnName] FROM [TableName]
  10. -- Where Clause If needed
  11. GROUP BY [ColumnName]
  12. )a
  13. WHERE N> 1
  14. )

There is a similar article which describes more techniques to follow for managing duplicate rows from a table .

http://support.microsoft.com/kb/139444

Sphere: Related Content

How to write a CURSOR loop over multiple databases in MSSQL 2000

September 27th, 2007 Rahmansaher No comments

I sure lot of people might want to use the script where they would want to loop over multiple databases sitting on a Single server. In my case, I had to check for  a Login Record  in all the databases since the person left the company and we wanted to disable his/her access. SO I wrote simple CURSOR script in MSSQL 2000. Hope this help to someone.  

SQL:
  1. USE master
  2.  
  3. DECLARE db_names CURSOR FOR
  4. SELECT name FROM sysdatabases
  5. -- Where Clause, Enable if you want to filter out some databases.
  6. --where name like 'databaseIwantName_%' and name not like 'databaseIdontwantName'
  7.  
  8. DECLARE @db_name varchar(100)
  9. DECLARE @QueryString NVARCHAR(500)
  10. DECLARE @UpdateString NVARCHAR(500)
  11.  
  12. OPEN db_names
  13. FETCH NEXT FROM db_names INTO
  14. @db_name
  15. WHILE @@FETCH_STATUS = 0
  16. BEGIN
  17. --PRINT @db_nam
  18. --In My case I used tbl_logins to do multiple select .
  19. SET @QueryString = 'Select * from ' + @db_name + '..logins where login like ''%loginname%'' '
  20. EXEC sp_executesql @QueryString
  21.  
  22. IF @@RowCount> 0
  23. BEGIN
  24. -- This gives the database name if there are any record found with the select state ran above
  25. PRINT @db_name
  26. END
  27.  
  28. --Similarly you can also have any Insert/Update Query IN a Loop.See example below
  29. --In My case I used tbl_logins to do multiple Update At Once.
  30. --SET @UpdateString = 'update ' + @db_name + '..tbllogins set password = ''account_blocked_out'', security_level = 0, approved = 0 where login like ''%loginname%'''
  31. --PRINT @UpdateString
  32. --EXEC sp_executesql @UpdateString
  33.  
  34. FETCH NEXT FROM db_names INTO
  35. @db_name
  36. END
  37.  
  38. CLOSE db_names
  39. DEALLOCATE db_names

 This is something written quick. Here is the Code for your Download. I am sure you can tune it better and if you do, please let me know . I would love to see your Ideas.

 Thanks

Sphere: Related Content

Some Windows Shortcut Tips !

May 10th, 2006 Rahmansaher No comments

Press Win+L to switch to the Welcome screen.

Press Win+L to lock your workstation.

You can switch users without going through the Welcome screen:  From Task Manager, go to the Users tab, right-click a user, and select Connect.

Hold down the shift key in the shutdown dialog to change "Stand By" to "Hibernate".  Or just press H to hibernate instantly.  You can even use the Power Control Panel to configure your power button to hibernate.

To disable the password when resuming from standby or hibernation, open the Power Control Panel and uncheck "Prompt for password after returning from standby" on the Advanced tab.

You can rename multiple files all at once: Select a group of files, right-click the first file, and select "Rename". Type in a name for the first file, and the rest will follow.

Hold down the shift key when switching to thumbnail view to hide the file names.  Do it again to bring them back.

When dragging a file in Explorer, you can control the operation that will be performed when you release the mouse button:

Hold the Control key to force a Copy.

Hold the Shift key to force a Move.

Hold the Alt key to force a Create Shortcut.

If you create a file called Folder.jpg, that image will be used as the thumbnail for the folder.  What's more, that image will also be used as the album art in Windows Media Player for all media files in that folder.

From the View Menu, select "Choose Details" to select which file properties should be shown in the Explorer window. To sort by a file property, check its name in the "Choose Details" in order to make that property available in the "Arrange Icons by" menu.

To display the volume control icon in the taskbar, go to the Sounds and Audio Devices Control Panel and select "Place volume icon in the taskbar".

Hold down the shift key when deleting a file to delete it immediately instead of placing it in the Recycle Bin. Files deleted in this way cannot be restored.

If you hold down the shift key while clicking "No" in a Confirm File Operation dialog, the response will be interpreted as "No to All".

To save a document with an extension other than the one a program wants to use, enclose the entire name in quotation marks.  For example, if you run Notepad and save a file under the name Dr.Z it will actually be saved under the name Dr.Z.txt.  But if you type "Dr.Z" then the document will be saved under the name Dr.Z.  Note that a document so-named cannot be opened via double-clicking since the extension is no longer ".txt".

Put a shortcut to your favorite editor in your Send To folder and it will appear in your "Send To" menu. You can then right-click any file and send it to your editor.

Ctrl+Shift+Escape will launch Task Manager.

To arrange two windows side-by-side, switch to the first window, then hold the Control key while right-clicking the taskbar button of the second window.  Select "Tile Vertically".

To close several windows at once, hold down the Control key while clicking on the taskbar buttons of each window.  Once you have selected all the windows you want to close, right-click the last button you selected and pick "Close Group".
-----
You can turn a folder into a desktop toolbar by dragging the icon of the desired folder to the edge of the screen. You can then turn it into a floating toolbar by dragging it from the edge of the screen into the middle of the screen.  (It helps if you minimize all application windows first.)

You can turn a folder into a taskbar toolbar.

First, unlock your taskbar.

Next, drag the icon of the desired folder to the space between the taskbar buttons and the clock. (Wait for the no-entry cursor to change to an arrow. It's a very tiny space; you will have to hunt for it.)

You can rearrange and resize the taskbar toolbar you just created.

You can even turn the taskbar toolbar into a menu by resizing it until only its name is visible.

In the Address Bar, type "microsoft" and hit Ctrl+Enter.  Internet Explorer automatically inserts the "http://www/." and ".com" for you.

To remove an AutoComplete entry from a Web form, highlight the item in the AutoComplete dropdown and press the Delete key.

To remove all Web form AutoComplete entries, go to the Internet Explorer Tools menu, select Internet Options, Content, AutoComplete, then press the "Clear Forms" button.

To organize your Favorites in Explorer instead of using the Organize Favorites dialog, hold the shift key while selecting "Organize Favorites" from the Favorites menu of an Explorer window.

You can organize your Favorites by dragging the items around your Favorites menu.

Alternatively, you can open the Favorites pane and hold the Alt key while pressing the up and down arrows to change the order of your Favorites.

To run Internet Explorer fullscreen, press F11. Do it again to return to normal mode.

If your "Printers and Faxes" folder is empty, you can hide the "Printers and Faxes" icon when viewed from other computers by stopping the Print Spooler service.

To add or remove columns from Details mode, select Choose Details from the View menu, or just right-click the column header bar.

In Internet Explorer, hold the Shift key while turning the mouse wheel to go forwards or backwards.

In Internet Explorer, hold the Shift key while clicking on a link to open the Web page in a new window.

In Internet Explorer, type Ctrl+D to add the current page to your Favorites.

This and many more keyboard shortcuts can be found by going to Internet Explorer, clicking the Help menu, then selecting Contents and Index.  From the table of contents, open Accessibility and click "Using Internet Explorer keyboard shortcuts".

In some applications (such as Internet Explorer), holding the Control key while turning the mouse wheel will change the font size.

To shut down via Remote Desktop, click the Start button, then type Alt+F4.        Copyright @ Microsoft

Read more...

Sphere: Related Content

Categories: Technical Tags: