SQL Tips & Tricks

While Loop in SQL Server

2009-09-05T19:48:00.001-07:00

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

'int' is not a recognized CURSOR option.

2009-09-05T19:47:00.001-07:00

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

Compound Operators in SQL Server 2008

2009-09-04T05:15:00.001-07:00

Compound operators execute some operation and set an original value to the result of the operation

Declare @temp tinyint

Select @temp = 1

Set @temp += @temp

print @temp

The following is a list of compound operators

Operator	Link to more information	Action
+=	+= (Add EQUALS) (Transact-SQL)	Adds some amount to the original value and sets the original value to the result.
-=	-= (Subtract EQUALS) (Transact-SQL)	Subtracts some amount from the original value and sets the original value to the result.
*=	*= (Multiply EQUALS) (Transact-SQL)	Multiplies by an amount and sets the original value to the result.
/=	/= (Divide EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the result.
%=	%= (Modulo EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the modulo.
&=	&= (Bitwise AND EQUALS) (Transact-SQL)	Performs a bitwise AND and sets the original value to the result.
^=	^= (Bitwise Exclusive OR EQUALS) (Transact-SQL)	Performs a bitwise exclusive OR and sets the original value to the result.
=	= (Bitwise OR EQUALS) (Transact-SQL)	Performs a bitwise OR and sets the original value to the result.

Date and Time Data Types in SQL Server 2008

2009-09-04T05:14:00.000-07:00

Some of the new data that are introduced in Microsoft SQL Server 2008 are :

Date
Time
Datetime2
Datetimeoffset

Here is a simple self-explanatory example:

declare @date date = getdate()

declare @time time = getdate()

declare @dt2 datetime2 = getdate()

declare @dt datetime = getdate()

declare @sdt smalldatetime = getdate()

print 'Date ' + cast(@date as char)

print 'Time ' + cast(@time as char)

print 'SmallDateTime ' + cast(@sdt as char)

print 'DateTime ' + cast(@dt as char)

print 'DateTime2 ' + cast(@dt2 as char)

Here is the output of the same:

Date 2009-09-04

Time 16:18:54.5730000

SmallDateTime Sep 4 2009 4:19PM

DateTime Sep 4 2009 4:18PM

DateTime2 2009-09-04 16:18:54.5730000

Modulo operator in SQL Server

2009-09-04T05:13:00.000-07:00

Modulo provides the integer remainder after dividing the first numeric expression by the second one.

print 'remainder ' + cast(112 % 17 as char)

ADO Connection String for Access 2007 Database

2009-02-07T17:41:00.000-08:00

ADO Connection String for Access 2007 Database

Here is the ADO connection string example for Access 2007 database.

Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"

If the database needs password then use the following string:

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb; Jet OLEDB:Database Password=MyDbPassword;"

The project that uses the code should have reference to ActiveX data objects library

ADO Connection String for Excel

2009-02-02T16:57:00.001-08:00

Excel Connection Strings

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"

ADO Connection String for Text Files

2009-02-02T16:33:00.000-08:00

ADO Connection String for Comma Separate File (CSV)

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

How to Convert CSV to XML using C#

2009-01-29T18:41:00.002-08:00

C# CSV to XML Conversion, CSV to XML Conversion using LINQ

Language Integrated Query (LINQ) can be used to convert a CSV file to XML. Here is the sample CSV file that needs to be converted

The following code reads the CSV file to an array. LINQ is used to loop through the array and the contents are written as XML using XElement (System.XML.Linq).

public void ConvertCSVToXML()

{

String[] FileContent = File.ReadAllLines(@"C:\Temp\vba.csv");

String XMLNS = "";

XElement Inv = new XElement("Invoice",

from items in FileContent

let fields = items.Split(',')

select new XElement("Item",

new XElement("ID", fields[0]),

new XElement("Name", fields[1]),

new XElement("Price", fields[2]),

new XElement("Availability", fields[3]),

new XElement("TotalPrice", fields[4])

)

);

File.WriteAllText(@"C:\Temp\vba.xml", XMLNS + Inv.ToString() );

}

Here is the Input Text File

The output XML looks like

LINQ and C#, C# Convert CSV To XML, .NET CSV To XML, LINQ CSV to XML, Convert Text files to XML using C#,

List files based on FileSize using C#

2009-01-29T18:41:00.001-08:00

C# Get Files Greater than Specific Size using Linq

C# and LINQ can be combined to do wonders; here is a sample of that. The following snippet extracts the files that are more thsn 345 KB

private static void GetFilesFromDirectory(string DirPath)

{

try

{

DirectoryInfo Dir = new DirectoryInfo(DirPath);

FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );

var query = from FI in FileList

where FI.Length > 340000

select FI.FullName + " " + FI.Length.ToString() ;

foreach (string s1 in query )

{

Console.WriteLine(s1);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message );

}

Extract files based on Size in C#, Extract files greater than 1GB using C#, C# List files based on Size, Filter by FileSize using C#, Extract files based on Size in .NET, Extract files greater than 1GB using .NET, .NET List files based on Size,

C# GetFiles with Date Filter

2009-01-29T18:40:00.001-08:00

How to Filter files that are modified in a specific day using C#

Here is a snippet using C# and LINQ, which will retrieve the files from a directory for a specific date

private static void GetFilesFromDirectory(string DirPath)

{

try

{

DirectoryInfo Dir = new DirectoryInfo(DirPath);

FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );

var query = from FI in FileList

where FI.LastWriteTime.Date == DateTime.Now.Date

select FI.FullName + " " + FI.LastWriteTime;

foreach (string s1 in query )

{

Console.WriteLine(s1);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message );

}

C# Get Read Only Files

2009-01-29T18:39:00.001-08:00

List all ReadOnly files in a directory using C# (.NET)

The following code will retrieve the read-only files using LINQ and C#

private static void GetFilesFromDirectory(string DirPath)

{

try

{

DirectoryInfo Dir = new DirectoryInfo(DirPath);

FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );

var query = from FI in FileList

where FI.IsReadOnly == true

select FI.FullName + " " + FI.LastWriteTime;

foreach (string s1 in query )

{

Console.WriteLine(s1);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message );

}

The above can (and mostly is) done by the following way

FileInfo[] FileList = Dir.GetFiles("*.*", SearchOption.TopDirectoryOnly );

foreach (FileInfo F1 in FileList)

{

if (F1.IsReadOnly == true)

{

Console.WriteLine(F1.FullName );

}

List ReadOnly files using C#, Retrieve Read-Only files using C#, Get Read Only Files using C#, List ReadOnly files using .NET, Retrieve Read-Only files using .NET, Get Read Only Files using .NET

Essential guide to learn what you need to do before implementing SQL Server 2008.

2009-01-29T18:38:00.000-08:00

Are you ready to deploy SQL Server 2008?

Many IT departments considering a migration to SQL Server 2008 are anxious about what may happen if the server does not perform well at deployment. This essential guide explains how analyzing your disk, memory, and processor requirements can help you develop a migration plan that will make your deployment an immediate success.

Download this essential guide to learn what you need to do before implementing SQL Server 2008.

Download SQL Server 2008 Upgrade Technical Reference Guide

2008-12-06T18:20:00.000-08:00

A free whitepaper called SQL Server 2008 Upgrade Technical Reference Guide that provides in-depth information on how to upgrade to SQL Server 2008. The guide can be downloaded from here

View Blog

Free Download SQL Server 2008 Enterprise 180-day evaluation

2008-12-06T18:14:00.000-08:00

SQL Server 2008 delivers on Microsoft’s Data Platform vision by helping your organization manage any data, any place, any time. Store data from structured, semi-structured, and unstructured documents, such as images and rich media, directly within the database. SQL Server 2008 delivers a rich set of integrated services that enable you to do more with your data such as query, search, synchronize, report, and analyze.

SQL Server 2008 provides the highest levels of security, reliability, and scalability for your business-critical applications. To take advantage of new opportunities in today's fast-moving business world, companies need the ability to create and deploy data-driven solutions quickly. SQL Server 2008 reduces time and cost of management and development of applications.

Access data from across your enterprise and provide control over your data no matter where it’s stored—from the largest servers within the data center to desktops to mobile devices. SQL Server 2008 provides a comprehensive platform that delivers intelligence where your users want it. Information workers can access data directly using the tools they use every day, such as the 2007 Microsoft Office system.

SQL Server 2008 enables data to be consumed from custom applications developed using Microsoft .NET and Visual Studio and from within service-oriented architectures (SOA) and business processes through Microsoft BizTalk Server.

Download SQL Server 2008 Enterprise 180-day evaluation

IT Professionals

Are you responsible for installing, configuring, administering, or troubleshooting database platforms and business intelligence applications? Do you manage IT resources and implement databases? Download SQL Server 2008 from the TechNet Trial Software Center today and experience the next generation of SQL Server.

Developers

Are you are responsible for designing, developing, integrating, or testing application software solutions for Microsoft Windows and the Web? Are you the architect for enterprise solutions and information systems? Download SQL Server 2008 from the MSDN Evaluation Center today experience the next generation of SQL Server.

Download SQL Server 2008 Feature Pack

The SQL Server 2008 Feature Pack is a separate download from SQL Server software. This collection of stand-alone install packages is designed to provide additional licensed functionality for SQL Server 2008. It contains new redistributable and backward compatibility components and add-on providers. For additional information on each of these components click the download button below.

For more info :http://www.microsoft.com/sqlserver/2008/en/us/overview.aspx

Learn SQL Server 2005--Free CD or Download

2007-10-28T19:49:00.000-07:00

Learn SQL Server 2005--Free CD or Download
  Get up to 3 hours of award-winning training from AppDev--FREE!
Choose SQL Server 2005, SSAS, SSIS, or SSRS. Includes step-by-
step instruction, hands-on labs, sample code, and printable
courseware (a $115 value). Get your free SQL Server 2005 training
at
  http://lists.sqlmag.com/t?ctl=69F7E:F21D603AB47E30601552C71C58F9D8E8

Now () Function in SQL Server

2007-06-28T19:14:00.000-07:00

Now () Function in SQL Server 2005

Logs, Status Reports and et al., Now() function is common. Here is the way to accomplish the same in SQL Server

Select @Today = GetDate() should give you the same

DatePart - Get Part of the Date

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date
Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

No of Records using Select Query

2007-05-09T19:13:00.001-07:00

-- No of Total Records using Select Query
Select count(*) from Purchasing.PurchaseOrderDetail

-- No of Records for each group using Select Query
Select PurchaseOrderID, count(*) NoOfRec from Purchasing.PurchaseOrderDetail Group By PurchaseOrderID

Identify Column in All Tables

2007-05-09T19:10:00.000-07:00

use AdventureWorks
Select * From sys.columns
select * from sys.tables

-- Select Columns From All Tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'EmployeeID'

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'PurchaseOrderID'

Write Log Files - Sybase

2007-05-07T19:46:00.000-07:00

Writing Logs - Text Files using Sybase

If you want to write to a Log file using Sybase use the following
code. Here the log file is not overwritten - it is appended with the
existing text. As the existing text is stored in a variable and
rewritten to the file there will be a gradual slowdown in performance

BEGIN
Declare sbuffer long varchar;
Declare sText long varchar;
Declare iRet integer;
Set sbuffer = xp_read_file('c:\temp\sample.sql');
Set sText = '\n';
Set sText = sText || 'Writing Text @ ' || Now(*);
Set iRet = xp_write_file('c:\temp\sample.sql', sbuffer || sText);
END;

Link Triggers & Tables

2007-05-07T19:44:00.000-07:00

Triggers and thier corresponding Tables

-- Sybase
select * from systrigger

-- SQL Server
select * from sys.triggers

-- Triggers and Tables
If you want to know the triggers for each table or trigger for a
corresponding table use the following:

-- Sybase
Select stab.table_name, strig.trigger_name from systable stab, systrigger strig
where stab.table_id = strig.table_id

-- SQL Server
Select stab.name, strig.name from sys.tables stab, sys.triggers strig
where stab.object_id = strig.parent_id

Stored Procedures & Parameters

2007-05-07T19:40:00.000-07:00

SQL Server / Sybase Stored Procedures & Parameters

-- Sybase Syntax to Select Parameters used in All Available Stored Procedures
select * from SYSPROCPARM

-- SQL Server Syntax to Select Parameters used in All Available Stored Procedures
select * from sys.parameters

-- Sybase Syntax to Select All Available Stored Procedures
select * from sysprocedure

-- SQL Server Syntax to Select All Available Stored Procedures
select * from sys.procedures

-- To get the list of both input and output parameters used in every
stored procedure use the following syntax

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
order by sp.proc_name

--SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
order by sp.name

-- Procedures and their Input Parameters

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_in = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 0
order by sp.name

-- Procedures and their Output Parameters
-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_out = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 1
order by sp.name

Get COMPUTER NAME from SQL Query

2007-04-28T19:07:00.000-07:00

Get the HOST NAME / COMPUTER NAME

Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

Get Computer Name, Get HOst Name

Here is the Way you can get the same in Visual Basic : http://vbadud.blogspot.com/2007/05/get-computer-name.html

Here is the Way you can get the same in .Net :
http://dotnetdud.blogspot.com/2007/06/get-computer-name-in-net.html

Coalesce Function

2007-04-28T18:54:00.000-07:00

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null
Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo

CASE Function Example - Database

2007-04-28T18:53:00.000-07:00

SQL Server CASE Function

Evaluates a list of conditions and returns one of multiple possible result expressions

Begin
Declare @Position Int;
Set @Position = 3
Select Case @Position
when 1 then 'Gold'
when 2 then 'Silver'
when 3 then 'Bronze'
Else cast(@Position as varchar)+ 'th'
End as Medal
End

You can change the value of @Position and analyse the output

SQL Server CASE Function, CASE Function, CASE Function Example