jueves, 12 de abril de 2012

GO and WAITFOR

A feature introduced since SQL Server 2005 is the ability to execute the same statement multiple times.

GO X: This example shows for ten times the current date and time (Note that it can differ in miliseconds)

SELECT getdate();
GO 10


WAITFOR can be used to wait for a specific amount of time or until a specific time has been reached

-- example 1
WAITFOR DELAY ’00:00:15’-- it wait's for 15 seconds


-- example 2
WAITFOR TIME ’09:00:01’ -- it wait's until 09:00:01

miércoles, 11 de abril de 2012

Get Started with SQL Server Certification

With the SQL Server 2012 launching, two new MCSE certifications will be introduced: Data Platform and Business Intelligence.

Here you've some interesting links on Microsoft Site, about these cetifications:

SQL Server Overview

MCSE DataPlattform

MCSE Businness Intelligence

MCSA SQL Server 2012

martes, 10 de abril de 2012

Advanced SQL Queries Migration, SQL Query Editor convert Queries than SSMA can't

In current Migration Project, from Sybase to SQL Server, we use SSMA for Sybase (Sql Server Migration Assistant).

There are some "complex" queries with *= and =* operators, and sometimes SSMA can't transform queries correctly.

We want to change it for LEFT/RIGHT  JOIN and so on, because it seems that Microsoft only will have compatibility with two previous versions, for example:

  • SQL 2008, supports 2005 and 2000.

  • SQL 2012, will support 2008 and 2005 (I supose).


In fact, it affects to NON-ANSI join operators, because this compatibility (for SQL Server 2000), takes no longer in SQL 2012.

First of all we test the query as is in SQL Server (2008 with database compatibility set to 2000) :

Error: Query contains an outer-join request that is not permitted.


 


The more usual is to use SSMA, so we insert it like a statement in SSMA Project Migration.



Some queries with unions, sub-selects NON-ANSI JOIN and so more, can't be transformed correctly.

TIP: 

When it occurs, try to copy the original statement to New Query Window, in SQL Server, select it, and go to Design Query in Editor. It convert the statement better than SSMA. It's a good solution for some statements, but in a migration project, with thousand's of statements, it's not a good practice, because we can't do it massively.



 

(For business confidentiality, I can't post the statements, but As soon as I retrieve a "big" query with this conversion problem, I'll try to post here.)