SQL Server Download Links All Editions

With SQL Server 2012 RC0 recently announced and a plethora of previous editions already available, it can get tedious to keep track of all these downloads. In this post, I will attempt to share the download links of all SQL Server versions - SQL Server 2012, SQL Server 2008 & R2 and SQL Server 2005. I will update this post and the links as and when applicable.

SQL Server 2012 Download Links

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform.

SQL Server 2012 Release Candidate 0 (RC0)

SQL Server 2012 Express RC0

SQL Server 2012 PowerPivot for Microsoft Excel 2010 Release Candidate 0 (RC0)

SQL Server 2012 RC0 Master Data Services Add-in For Microsoft Excel

SQL Server 2012 Release Candidate 0 (RC0) Manageability Tool Kit

SQL Server Data Tools CTP4

SQL Server 2012 Developer Training Kit Web Installer Preview

SQL Server 2008 R2 Download Links

SQL Server 2008 R2 Evaluation

SQL Server 2008 R2 Service Pack 1

SQL Server 2008 R2 RTM – Express

SQL Server 2008 R2 SP1 - Express Edition

SQL Server 2008 R2 RTM - Management Studio Express

SQL Server 2008 R2 RTM - Express with Management Tools

SQL Server 2008 R2 RTM - Express with Advanced Services

SQL Server 2008 R2 VHD

SQL Server 2008 Download Links

SQL Server 2008 Trial

SQL Server 2008 Service Pack 3

SQL Server 2008 Express

SQL Server 2008 Express Edition Service Pack 3

SQL Server 2008 Management Studio Express

SQL Server 2008 Express with Tools

SQL Server 2005 Download Links

SQL Server 2005 Evaluation is no longer available. You can use the Express Edition

SQL Server 2005 SP4 RTM

SQL Server 2005 Express Edition

Microsoft SQL Server 2005 Express Edition Service Pack 3

SQL Server Management Studio Express

Other SQL Server Download Links

SQL Server Migration Assistant for Oracle v5.1
SQL Server Driver 2.0 for PHP 5

Any important links that I missed out? Share it in the comments section and I will update the post!

SQL Server: Test Linked Server Connection

If you want to have access to data stored in a different server, one of the ways to do so is to make that server as a Linked Server to the current server and then query using the linked server name. Let us assume that there is a server named test available over the network and you want to get some data from there. System stored procedure sp_addlinkedserver can be used to create a linked server and fire distributed queries.

The following code adds the server test as Linked server to the current server

EXEC sp_addlinkedserver test

If you want to know if the connection to the linked server is accessible, you can make use of system stored procedure sp_testlinkedserver as shown below

EXEC sp_testlinkedserver test

It simply checks if the connection is successful. If the result is "Command(s) completed successfully.", it means the connection is successful. This is the simplest way to check if the linked server is accessible

Also check Viewing Linked Server Information in SQL Server 2005/2008

Generate AlphaNumeric Identity Column in SQL Server

Let us suppose you have a requirement where you want to have an alphanumeric identity column that has characters and numbers and you want these numbers to be incremental. Eg: Customer ids like cus0000001, cus0000002, etc. You can use any of the following methods

Method 1 : Store alphanumeric data in column

declare @t table(id char(10), names varchar(100))
insert into @t
select 'cus0000001','ramesh' union all
select 'cus0000002','suresh'

declare @id int
select @id=MAX(substring(id,4,len(id)))*1 from @t

insert into @t
select 'cus'+RIGHT(REPLICATE('0',7)+cast(@id+1 as varchar(7)),7),'nilesh'
select * from @t

In the above code, @id is assigned the maximum value of the number excluding characters "cus", which is used again in the INSERT statement

Method 2 : Use only identity column of int datatype and append alphabets in select statement

declare @t table(id int identity(1,1), names varchar(100))
insert into @t (names)
select 'ramesh' union all
select 'suresh' union all
select 'nilesh'

select 'cus'+right(replicate('0',7)+cast(id as varchar(7)),7) as id,names from @t

In the above code, an alphanumeric string is built dynamically in the select statement

SQL Server: Find Common Rows in Tables using INTERSECT

Suppose you have a SQL Server database with tables having similar structures and you want to find out similar rows among these tables. The most common method is to use a JOIN on all the columns.

Consider the following tables

create table #t1 (id int, names varchar(100))
create table #t2 (id int, names varchar(100))
create table #t3 (id int, names varchar(100))

insert into #t1
select 1,'test1' union all
select 2,'test2'
insert into #t2
select 1,'test1' union all
select 3,'test2'
insert into #t3
select 1,'test1' union all
select 20,'test2'

select t1.* from #t1 as t1
inner join #t2 as t2 on t1.id=t2.id
inner join #t3 as t3 on t1.id=t3.id

OUTPUT

The above select statement joins all these three tables by all columns to get similar rows among these tables. Then these similar rows in all three tables are displayed.

The problem with this approach is that if the table has many columns, you will need to specify all the columns in the join statement, which will make maintaining these queries a nightmare.

Another easy method to find common rows without actually specifying any columns is to use the INTERSECT Operator

OUTPUT

SQL Server: Handling Multiple Result sets in a Procedure

Suppose you have a SQL Server stored procedure that returns multiple result sets and you want to store all these results into another table. I will show you how to do so. Consider the following stored procedure

create procedure test
as
select 1 as id, 'test1' as name
select 2 as id, 'test2' as name

When you execute this procedure, it returns two result sets. The following code will copy these two result sets in a table variable

declare @t table(id int, names varchar(100))
insert into @t
exec test
select * from @t

Note: In order to copy multiple result sets from a stored procedure, both result sets should have the same number of columns and datatype which are compatible with the target table. If not, an error message will be thrown for invalid number of columns/data type

Add Client Name in SQL Profiler Results

Often you may need to analyze results from the SQL Server profiler to know blocking CPU time, Reads etc. By default the profiler shows you a set of columns in the grid. One of the columns is the ClientProcessId. This column gives information about the Id of the client that accesses the server.

If you want to know the name of the client, you can add that column in the grid using the following steps

Start SQL Server profiler > Navigate to File > Templates > Edit template

A new window will open. Click on "Events Selection" Tab. At the bottom, check the option labeled "Show all columns"

In the window, check Hostname checkbox in the Audit Login event as shown above and click the Save button. Now Restart the Profiler. It will start showing the client name too in the results.

SQL Server File Location: Points to Consider

In SQL Server, file locations can be specified in some commands like during bulk insert, listing directory information, using OPENROWSET function, etc. When you specify file location, you should note the following points:

1. By default SQL Server searches for the file location in Server's directory. For example, in a Bulk insert command, if you specify D:\emp.txt, it is by default searched in the Server's
directory. If your server name is myServer, SQL Server will try to find out the file path in that server only.

2. SQL Server will not recognize client's file location until specified using the UNC path. For example say your Server name is myServer and the SQL Server client is installed in your local system named myClient that connects to Server. Say you have a file named emp.txt in your computer’s D drive and you want to specify that file location in the BULK INSERT command. In this case, the command should be

3. When UNC path is specified, the file should be given atleast a read access to the server, in which

the Query is executed. In the above example, D:\emp.txt file should be given atleast read only access on the server myServer. Otherwise an error "File not found error" will be thrown

The same applies to other functions as well which accept file locations - such as xp_cmdshell, OPENROWSET etc.

SQL Server: LPAD and RPAD functions equivalent

Oracle has two formatting functions LPAD and RPAD which formats the number by appending leading and trailing zeros respectively. SQL Server does not have direct equivalent functions.

However you can simulate these functions using other string functions available in SQL Server. Let us take a practical example. Suppose you want to export data to fixed format file and the requirement is that the number should be 10 digits long, in such a way that if the total number of digits is less than 10, the remaining digits should be filled with zeroes.

Consider the following example

declare @num int
set @num=872382
select
right(replicate('0',10)+cast(@num as varchar(15)),10) aS lpad_number,
left(cast(@num as varchar(15))+replicate('0',10),10) as rpad_number

The above code shows numbers in two formats. The first is left padded and second is right padded. The replicate function is used to replicate 0 for 10 times and the actual number is converted to string and then appended with zeroes.

The right function picks the last 10 digits from the result, so that it has 4 leading zeroes, as the original number shown in the code above has only 6 digits. We then reverse the same by converting a number to varchar and appending leading zeroes at the end. We then use the LEFT function which picks the number where last 4 digits are zeroes.

OUTPUT

SQL Server: Schedule Jobs every Alternate Week

SQL Server agent is used to schedule a job that has to run periodically. If you are a DBA, one of your admin tasks is to schedule jobs. Suppose you are in a situation to run a set of codes every alternate Saturday, you can do it using the following steps:

Open SQL Server Management Studio (SSMS). Under SQL Server agent, create a new job named test. Click on Schedules and button called New.

A new window will popup. Under frequency section, select weekly for "Occurs" option. Select 2 on "Recurs every" option. Under days option choose "Saturday". Click ok.

Now the job will run every alternate Saturday on the time specified.

SQL Server: Resolving DateTime Conversion Errors

Sometimes your queries may return errors when using DateTime values. Consider the following select statement

select cast('19/12/2000' as datetime)

This statement will fail with the following error message:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The reason is that SQL Server either does not recognize the input date format or the date is invalid. It is important to know the Server's date setting before using any date formats in your queries.

There is a DBCC command which can be used to determine the current date format

DBCC USEROPTIONS

When you run the code, the third row gives you the current dateformat. It is mdy on my machine, so you should input dates in mm-dd-yyyy format. If it is dmy, you should input it in dd-mm-yyyy format. The input format in most cases, should be based on the server's date format.

Now when you run the following code by formatting dates in mdy format (in sync with your machine format), it works fine:

select cast('12/19/2000' as datetime)



Note: It is also better to use an unambiguous date format YYYYMMDD HH:MM:SS which will work for all date settings.