SQL Server & Vb.net Tips

Software for Catching Flash(.swf) from Websites
1) Flash catcher
2) Flash Saver
----------------------------------------------------------------------------------------------------------------------------------
1. How to enable the mnemonics (underline) being displayed when an application is launched?
Usually the underline appears only after you press the Alt Key, but you can enable it by changing the Operating System Settings. On Windows XP, Right Click Desktop to bring up the Display Properties Dialog and then choose Appearance tab and then the Effects Button and uncheck the checkbox "Hide Underlined letters for keyboard navigation until I press the ALT Key".
----------------------------------------------------------------------------------------------------------------------------------
2. An easy way to build connection string.
Though this in not related to .NET directly but it is useful while working with ADO.NET
Collapse1) Open a New notepad and save it with "udl" extension, suppose "New.udl".2) Now you will see that it's icon is changed.3) Open it, you will find Data Link properties dialog box.4) For SQl Server connection string select Microsoft OLE DB Provider For SQL Server in Provider Tab.5) Click button "Next" or select Connection Tab6) Here you can select all connection details and press button Test Connection. If it is successful close this dialog box.7) Now open this file using "Notepad", you will find the connection string. Though it is built for OLE DB type of connection, you can use for SQL Server connection by removing Provider attribute. NOTE: If you are using SQL Authentication with password, then check the checkbox Allow Saving Password.This is necessary so that password appears in connection string.
----------------------------------------------------------------------------------------------------------------------------------
3. How to add a custom or destination folder to SendTo menu?
Every one knows about SendTo menu that appears after right click on any file.By default there are 4 options or destinations in this menu. But you can add custom destinations to this menu. Adding other locations to the Send To menu is convenient if you frequently perform the same file management tasks. For example, if you back up files on another network computer or on same machine where you have to navigate through a deep path every day, having the computer on the Send To menu can save you time.
To add a destination to the Send To menu follow the steps given below:
Open My Computer.
Double-click the drive where Windows is installed (usually drive C, unless you have more than one drive on your computer). If you can't see the items on your drive when you open it, under System Tasks, click Show the contents of this drive.
Double-click the Documents and Settings folder.
Double-click the folder of a specific user.
Double-click the SendTo folder. The SendTo folder is hidden by default. If it is not visible, on the Tools menu, click Folder Options. On the View tab, click Show hidden files and folders.
On the File menu, point to New, and then click Shortcut.
Follow the instructions on your screen.
----------------------------------------------------------------------------------------------------------------------------------
4)Read a text file
The following sample code uses a StreamReader class to read the System.ini file. The contents of the file are added to a ListBox control. The try...catch block is used to alert the program if the file is empty. There are many ways to determine when the end of the file is reached; this sample uses the Peek method to examine the next line before reading it.

Dim reader As StreamReader = _ New StreamReader(winDir & "\system.ini") Try Me.ListBox1.Items.Clear() Do Me.ListBox1.Items.Add(reader.ReadLine) Loop Until reader.Peek = -1 Catch Me.ListBox1.Items.Add("File is empty") Finally reader.Close() End Try
----------------------------------------------------------------------------------------------------------------------------------

5)Write a text file
This sample code uses a StreamWriter class to create and write to a file. If you have an existing file, you can open it in the same way.
Dim writer As StreamWriter = _ New StreamWriter("c:\KBTest.txt") writer.WriteLine("File created using StreamWriter class.") writer.Close()
----------------------------------------------------------------------------------------------------------------------------------

6) View file information
This sample code uses a FileInfo object to access a file's properties. Notepad.exe is used in this example. The properties appear in a ListBox control.
Dim FileProps As FileInfo = New FileInfo(winDir & "\notepad.exe") With Me.ListBox1.Items .Clear() .Add("File Name = " & FileProps.FullName) .Add("Creation Time = " & FileProps.CreationTime) .Add("Last Access Time = " & FileProps.LastAccessTime) .Add("Last Write Time = " & FileProps.LastWriteTime) .Add("Size = " & FileProps.Length) End With FileProps = Nothing
----------------------------------------------------------------------------------------------------------------------------------
7) List disk drives
This sample code uses the Directory and Drive classes to list the logical drives on a system. For this sample, the results appear in a ListBox control.
Dim dirInfo As Directory Dim drive As String Me.ListBox1.Items.Clear() Dim drives() As String = dirInfo.GetLogicalDrives() For Each drive In drives Me.ListBox1.Items.Add(drive) Next
----------------------------------------------------------------------------------------------------------------------------------

8)List subfolders
This sample code uses the GetDirectories method of the Directory class to get a list of folders.
Dim dir As String Me.ListBox1.Items.Clear() Dim dirs() As String = Directory.GetDirectories(winDir) For Each dir In dirs Me.ListBox1.Items.Add(dir) Next
----------------------------------------------------------------------------------------------------------------------------------

9)List files
This sample code uses the GetFiles method of the Directory class to get a list of files.
Dim file As String Me.ListBox1.Items.Clear() Dim files() As String = Directory.GetFiles(winDir) For Each file In files Me.ListBox1.Items.Add(file) Next
----------------------------------------------------------------------------------------------------------------------------------
SQL Server
10) Using add linked server for connecting one server to another external Database server

Connecting Another DB with windows authentication
exec sp_addlinkedserver [intranetbd]

Connecting Another DB with User defined authentication Syntax:

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'
Ex:
exec sp_addlinkedsrvlogin [intranetbd],false,'qteam','tsms','Hr'

Accessing the DB server
select DISTINCT c_id from [intranetbd].Hr.dbo.projects

Found under SQL Server 2005
Server objects -> Linked server
----------------------------------------------------------------------------------------------------------------------------------

11. Sub Query – Query within Query
Ex : select * from EMP Where Id not in (SELECT Max(Id) from EMP group by Name) order by name
----------------------------------------------------------------------------------------------------------------------------------

12. Write query to get 10 records (random wise) from Table, with out use DESC and TOP Command.Query :
select (select count(*) from Empwhere Name <= t.Name) as SRNo,* from Emp t where 11<=(select count(*) from Emp where Name <= t.Name)and 20>=(select count(*) from Emp where Name <= t.Name)order by Name ---------------------------------------------------------------------------------------------------------------------------------- 13. Write query to using Having Statement: ->
It can be used with group by and aggregate function like avg, sum, max, min, etc,.
Exampleselect count(Name) from Emp group by Name having count(Name)>1
----------------------------------------------------------------------------------------------------------------------------------
14) using Substring and charindex
CHARINDEX SYNTAX CHARINDEX( text to find, text ) => 11 Example CHARINDEX('SQL', 'Microsoft SQL Server') => 11
SUBSTRING SYNTAX
SUBSTRING ( expression , start , length )
Example
SELECT x = SUBSTRING('abcdef', 2, 3) => bcd

Combined Example – 1 :
SELECT SUBSTRING(email, 1, CHARINDEX('@', email) - 1) AS Email
FROM Active_employee_list
Combined Example – 2 :
select left(mail_id,charindex('@',mail_id)-1) from emp_personal

Result : jayavelcs@gmail.com -> jayavelcs
----------------------------------------------------------------------------------------------------------------------------------

15) using Stored Procedure
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance.

Ex 1:CREATE PROCEDURE sp_myStoredProcedureASSelect column1, column2 From Table1

Ex2:CREATE PROCEDURE sp_myStoredProcedure @myInput intASSelect column1, column2 From Table1Where column1 = @myInput

Ex3:CREATE PROCEDURE sp_myStoredProcedure @myInput int, @myString varchar(100), @myFloatAS Ex4:CREATE PROCEDURE sp_myInsert @FirstName varchar(20), @LastName varchar(30)AsINSERT INTO Names(FirstName, LastName)values(@FirstName, @LastName)

Alter Procedure
alter procedure

Drop procedure
Drop procedure

Executing
Exec sp_myStoredProcedure 0, 'This is my string', 3.45
----------------------------------------------------------------------------------------------------------------------------------
16) Conversion format : Here is the output from the above script:
Type 1:
select * from audit_table where convert(datetime,left(actual_audit_date,11))='09/06/2007'
Type 2:
1) CONVERT(CHAR(19),GETDATE()) ==>Feb 5 2003 5:54AM
2) CONVERT(CHAR(8),GETDATE(),10 ) ==>02-05-03
3) CONVERT(CHAR(10),GETDATE(),110) ==>02-05-2003
4) CONVERT(CHAR(11),GETDATE(),106) ==>05 Feb 2003
5) CONVERT(CHAR(9),GETDATE(),6) ==>05 Feb 03
6) CONVERT(CHAR(24),GETDATE(),113) ==>05 Feb 2003 05:54:39:56
----------------------------------------------------------------------------------------------------------------------------------
17) Using Temp table

Creating
create table #tableTmp (empid varchar(50))

Inserting
insert into #tableTmp
select emp_id from Bats_Log_Emp_Revenue where txn_name = 'Add Partner Employee'

Using
select @NoTrans = count(distinct empid) from #tableTmp

Deleting
drop table #tableTmp
----------------------------------------------------------------------------------------------------------------------------------
18) Importing data from Excel to SQL Server

Select Database --> Right mouse click -- > Import data.
The wizard starts and it self-driven.

Through programmatically

SELECT * INTO db1.dbo.table1FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls', 'SELECT * FROM [sheet1$]')
----------------------------------------------------------------------------------------------------------------------------------
19) Executing exe from SQL Server

declare @cmd varchar(8000)
set @cmd = 'cmd.exe /C "D:\Scheduled Tasks\LinuxBackup.exe "'
EXEC xp_cmdshell @cmd
----------------------------------------------------------------------------------------------------------------------------------
20) Shrink Database Log File Size

USE wss_content;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE wss_content
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 100 MB.
DBCC SHRINKFILE (wss_content_Log, 100);
GO

-- Reset the database recovery model.
ALTER DATABASE wss_content
SET RECOVERY FULL;
GO
----------------------------------------------------------------------------------------------------------------------------------
21) String Functions
LEFT(S , N):
Returns the first N characters of string S from the left.
Example: LEFT('Function',6)='Functi'

RIGHT(S , N) : Returns the last N characters of string S from the right.
Example: RIGHT('Function',6)='nction'

LEN (S): Returns the number of characters in string S.
Example: LEN ('Function',6)=8

LOWER (S): Return string S after converting all characters to lower case.
Example: LOWER ('Function')='function'

UPPER (S): Return string S after converting all characters to upper case.
Example: UPPER ('Function')='FUNCTION'

LTRIM (S): Return string S after removing all blank characters from the left.
Example: LTRIM (' Function')='Function'

RTRIM (S): Return string S after removing all blank characters from the right.
Example: RTRIM ('Function ')='Function'

REPLACE ( S1 ,S2 ,S3 ): Return S1 after replacing all occurrence of S2 in it with S3
Example: REPLACE ('Function','n','123')='Fu123ctio123'

REPLICATE ( S , N): Return a repetition of string S, N times
Example: REPLICATE ('abc',3)='abcabcabc'

REVERSE ( S) : Return string S after reversing the order of all characters.
Example: REVERSE ('Function')=' noitcnuF'

SPACE(N): Return a string of repeated spaces N times.
Example: SPACE(4)=' '

STUFF ( S , I, N, S1): Return string S after deleting N characters from index I and inserting S1 at position I.
Example: STUFF ('Function', 3, 4,'abc' )='Fuabcon'

SUBSTRING ( S, I, N): Return a portion of S from index I of N characters.
Example: SUBSTRING ('Function', 3, 4)='ncti'
------------------------------------------------------------------------------------------------------------------------------------------
1) Aggregate Functions
AVG ( [ ALL DISTINCT ] E ) : Return the average of not null values of expression E. If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value.
Example: SELECT HOURS FROM Employee returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12) SELECT AVG(HOURS) FROM Employee returns (8) SELECT AVG(DISTINCT HOURS) FROM Employee returns (7)

COUNT ( [ ALL DISTINCT ] E ]) : Return the number of item in the group of expression E. If E=* the function will return the number of record in the data source. If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value. The parameter ALL and DISTINCT can not be use with *.
MAX(E): Return the maximum value of expression E.
Example: SELECT HOURS FROM Employee returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12) SELECT MAX(HOURS) FROM Employee return (12)

MIN(E): Return the minimum value of expression E.
Example: SELECT HOURS FROM Employee returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12)
SELECT MIN(HOURS) FROM Employee return (4)
SUM ( [ ALL DISTINCT ] E): Return the SUM of not null values of expression E. If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value. SUM can be use numeric columns only.
Example: SELECT HOURS FROM Employee returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12) SELECT SUM(HOURS) FROM Employee returns (88) SELECT SUM(DISTINCT HOURS) FROM Employee returns (63)
------------------------------------------------------------------------------------------------------------------------------------------
29) Using Data type Conversations

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
CAST ( expression AS data_type )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Ex:
CONVERT(decimal(10,5), @myval)
------------------------------------------------------------------------------------------------------------------------------------------
27) Try –Catch

BEGIN TRY
EXEC dbo.sp_bcr_import_data_report
END TRY
BEGIN CATCH
PRINT 'Test';
RETURN;
END CATCH
------------------------------------------------------------------------------------------------------------------------------------------
28) Begin Tran – End Tran

go
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
go