viernes, 28 de diciembre de 2012

Skip function if NULL parameter input is passed

You can avoid executing a function when NULL values are passed as parameters. To achieve this you need to create function with RETURNS NULL ON NULL INPUT

In example, this function concatenates two strings, if two arguments are passed, otherwise NULL result are returned. 

USE db
GO

CREATE FUNCTION dbo.ConcatenateSTR
(
      @string NVARCHAR(MAX),
      @string2 NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE     @retValue NVARCHAR(MAX)
SET         @retValue = @string+@string2
RETURN      @retValue
END
GO


Let's practice it:


USE db 
GO

SELECT [DB].[dbo].ConcatenateSTR(N'SQL', N'NCO')
SELECT [DB].[dbo].ConcatenateSTR(N'SQL', N'')
SELECT [DB].[dbo].ConcatenateSTR(N'SQL', NULL)
SELECT [DB].[dbo].ConcatenateSTR(NULL, N'NCO') 
GO

The final results are: 

SQLNCO 
SQL 
NULL 
NULL

Results are like expected.

viernes, 21 de diciembre de 2012

Feliz Navidad y Próspero Año 2013


martes, 18 de diciembre de 2012

Connect to local SQL Server with Shared Memory Disabled


If you've this scenario, probably it can help you.
  • SQL Server has Shared Memory Protocol disabled
There is a suggestion for improve better performance in Servers with Biztalk and SQL Server.
Under certain stress conditions (such as clients accessing SQL Server from the same computer), the SQL Server Shared Memory protocol may lower BizTalk Server performance.
You can resolve this problem by disabling the use of the Shared Memory network protocol in the SQL Server Client Network Utility.

On the other hand, disabling the protocol makes all of locally format connections to be down.
o        "<computer_name>"
o        "<computer_name>\<instance_name>" for a named instance
o        "(local)"
o        "(local)\<instance_name>" for a named instance
o        "Localhost"
o        "localhost\<instance_name>" for a named instance
o        A single period "."
o        ".\<instance_name>" for a named instance
  • It's needed to connect with SQL Server locally.
When you connect to the Database Engine from a local client (the client application and SQL Server are on the same computer), the connection uses shared memory protocol by default, but...we've DISABLED!.

If you still need to connect locally, with Disabled Shared Memory Protocol, write np: or tcp: before:

Prefix the computer name with the protocol (for example, "np:<computer_name>" or"tcp:<computer_name>").

jueves, 29 de noviembre de 2012

SSMS Shared SQL Templates, synchronized with Dropbox account

Actually, I've T-SQL scripts in my USB, but today I'm thinking about the possibility to store it in Template Explorer of Management Studio, it's more fast to get scripts than in USB.

First of all, Scripts of Template Explorer are stored in %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\SQL

100 for SQL Server 2008 R2, the number depends on your SQL Server version.

It's not possible to modify the path of Template Explorer, but it's possible to modify the content of the path.

For me, it's a good option to link the content with Dropbox Folder, to synchronize it.

Windows 7 has introduced a new shell command called MKLINK, which can create symbolic links.

Well, first of all we have to rename SQL folder:

  • Open command prompt and type
    • cd %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates
      For SQL2012: cd %AppData%\Microsoft\SQL Server Management Studio\11.0\Templates\SQL
    • ren SQL SQL_OLD

Now link Dropbox Folder to SQL Directory.


  • mklink /D Sql C:\Users\USER\Dropbox\SCRIPTS (Change it for your desired path).


Now, open SQL Server Management Studio a go to View Menu, Template Explorer (Ctrl+Alt+T).

If all goes ok, you have to view you Dropbox Folder with all .sql file extension.

Something like this.


SQL Server always creates his template scripts in folder, if you try to delete it, on next program execution it will re-created.
In example, non numbered directories, are default SQL Server Template Folders.

Note: If you doesn't find Template folder in  %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\, go to SSMS and open Template explorer, now folder will be created.
It's only necessary do this for first time after installation.

The path for SQL2012 is %AppData%\Microsoft\SQL Server Management Studio\11.0\Templates\SQL

miércoles, 28 de noviembre de 2012

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 )

The server network address “TCP://SQLServerName:5022″ cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418 ).

If this error occurs when you're trying to create a mirror, check this points:

  • Restore backup at secondary server has to be with NORESTORE option.
  • All log backup must be restored (Probably some job of SQL SERVER AGENT makes Transaction Log Backup, ensure that last log backup is restored in secondary too).
  • GRANT CONNECT ON ENDPOINT::Mirroring TO Public (I think with domain user that runs service account it's enough, but it doesn't work for me, so, PUBLIC, works).
  • Maybe Firewall block the Port Number, try to connect, with TELNET for example.

I hope it helps.

Cannot create, add, or edit steps for an SQL Server Agent job in SQL Server Management Studio 2008 R2

If you get some error description like this, "Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011." 




Probably this link to Microsoft's Fix can help you: http://support.microsoft.com/kb/2315727 



miércoles, 21 de noviembre de 2012

Errors inserting identity on table

Here we've different errors that can occurs trying to insert some rows into table that has Identity field.

I want to insert all data from Table Banca_Movimientos_TEMP to table Banca_Movimientos3

Sentence 1:


insert into dbo.Banca_Movimientos3 
select * from dbo.Banca_Movimientos_TEMP


On executing this, I get the error:


Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.Banca_Movimientos3' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This is because it's necessary to specify all fields in insert into statement.

Let's do it.


Sentence 2:

insert into dbo.Banca_Movimientos3([IdMovimiento] ,[Concepto] ,[Fecha_Operacion] ,[Fecha_Valor] ,[Descripcion] ,[Importe] ,[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

Well, now another error occurs.


Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Banca_Movimientos3' when IDENTITY_INSERT is set to OFF.

It's needed to set IDENTITY_INSERT ON because field IdMovimiento is IDENTITY.


Sentence 3:

SET IDENTITY_INSERT Banca_Movimientos3 ON;
GO

insert into dbo.Banca_Movimientos3 ([IdMovimiento],[Concepto],[Fecha_Operacion],[Fecha_Valor],[Descripcion],[Importe],[Observaciones])
select * from dbo.Banca_Movimientos_TEMP

SET IDENTITY_INSERT Banca_Movimientos3 OFF;
GO

(1385 row(s) affected)

Now, the insert statement is OK.

viernes, 9 de noviembre de 2012

Error Restoring Database


Today,

Trying to restore a database from other server Backup, 


RESTORE DATABASE DB
  FROM DISK = 'X:\Backup\DB_1.dmp'
  WITH 
    MOVE 'DB'         TO 'Y:\DATA\DB.mdf',
    MOVE 'DB_log'   TO 'Z:\LOG\DB_log.ldf',
    REPLACE,
    Recovery,  
    STATS = 10
go


I get this message:

Msg 3634, Level 16, State 1, Line 1

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'Z:\LOG\DB_log.ldf'.

Msg 3156, Level 16, State 8, Line 1

File 'DB_log' cannot be restored to 'Z:\LOG\DB_log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.


Previously I set the DB Database in single_user mode, and read_only mode, and it stills the same error.

I restarted SQL Server but the error continues.

Probably there are better forms to solve this issue, but this is what I did:

Restore the database with other log filename. SQL Server deletes old one.
If you want to have the original filename, try to restore again changing the log filename.



RESTORE DATABASE DB
  FROM DISK = 'X:\Backup\DB_1.dmp'
  WITH 
    MOVE 'DB'         TO 'Y:\DATA\DB.mdf',
    MOVE 'DB_log'   TO 'Z:\LOG\DB_log1.ldf',
    REPLACE,
    Recovery,  
    STATS = 10
go

RESTORE DATABASE DB
  FROM DISK = 'X:\Backup\DB_1.dmp'
  WITH 
    MOVE 'DB'         TO 'Y:\DATA\DB.mdf',
    MOVE 'DB_log'   TO 'Z:\LOG\DB_log.ldf',
    REPLACE,
    Recovery,  
    STATS = 10
go

domingo, 28 de octubre de 2012

Automatically Mapping a user to a login: sp_change_users_login

Sometimes we need to restore Databases in other servers, and logins probably doesn't exist on new server.
The restored Database contains Users and permissions but it aren't mapped with Logins.

Microsoft provides stored procedure to map user with login.
If login doesn't exist, Auto_Fix option will create it.

USE Database;
GO
EXEC sp_change_users_login 'Auto_Fix', 'USER', NULL, 'Password';
GO

Otherwise, if you only need to map USER with LOGIN, use the Update_One option:


USE Database;
GO
EXEC sp_change_users_login 'Update_One', 'USER', 'LOGIN';
GO


I hope that helps you.

Changing default database backup folder for SQL Server

If you don't modify the default paths for Data, Logs and Backup's on SQL Server Installation, you can modify it then.

To Modify SQL Data or Log Directory, go to Server Properties, Database Settings, Database Default Locations (marked in red).


But, where can we change SQL Backup directory?

In Windows Registry.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer

This is only an example, probably you must change Instance Name

Backup Directory (See image below).






Installation Error: The MOF compiler could not connect with the WMI server.



The MOF compiler could not connect with the WMI server.
This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.

If this error ocurred when you install SQL Server 2008 R2, try to do this one:

  • Open console command (Run->CMD with administrator privileges).
  •  net stop winmgmt
  • Rename folder %windir%\System32\Wbem\Repository to other one, for backup purposes (for example _Repository).
  • net start winmgmt


Now, when you try to install again, it will work OK.


lunes, 8 de octubre de 2012

Restore STATS


The syntax RESTORE…WITH STATS = X option allows you to see where is the restore process currently. The default display is 10%, but some value can be specified.

RESTORE DATABASE ‘Adventureworks’ FROM DISK = ‘C:\Adventureworks.bak’ WITH STATS = 5
GO

It refresh the restore status percentage from steps by 5%.

FullTextCatalog, RESTORE DATABASE is terminating abnormally


If you execute this sentence to restore database with FullTextCatalog,

RESTORE DATABASE YourDB
  FROM DISK = 'X:\ Backup\ DatabaseBackup.bak
  WITH
    MOVE 'YourDB_data'   TO 'Z:\DATA\YourDB.mdf',
    MOVE 'YourDB _log'   TO 'Z:\DATA\YourDB_log.ldf',
    REPLACE,
    Recovery, 
    STATS = 10
go


If you will have an error like this:

Msg 9953, Level 16, State 1, Line 1
The path 'Z:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\XXXXX_FullTextCatalog' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.
Msg 3156, Level 16, State 50, Line 1
File 'sysft_XXXXX_FullTextCatalog' cannot be restored to 'Z:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\XXXXX_FullTextCatalog'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

It’s because you’ve to add a move statement in restore sentence for FullTextCatalog.
To ensure that the Database has FullTextCatalog, and what’s his file path, use a sentence like this:

restore filelistonly from disk ='X:\Backup\DatabaseBackup.bak'


It shows the Data Files, Log Files, Full Text Catalog… (Type F is for FullTextCatalog).

Line 3 show FullTextCatalog information, use Logical and Physical Name to complete the move instruction in restore sentence:

RESTORE DATABASE YourDB
  FROM DISK = 'X:\ Backup\ DatabaseBackup.bak
  WITH
    MOVE 'YourDB_data'   TO 'Z:\DATA\YourDB.mdf',
    MOVE 'YourDB_log'   TO 'Z:\DATA\YourDB_log.ldf',
    MOVE 'sysft_FullTextCatalog' to 'Z:\Program
Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\XXXXX_FullTextCatalog',
    REPLACE,
    Recovery, 
    STATS = 10
go



martes, 21 de agosto de 2012

Allocation Pages


Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types.

Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.

viernes, 27 de abril de 2012

Restore database from UNC

This T-SQL, allows you to add a Backup device, from remote directory (Don't forgett to give permissions to source backup folder).

First of all, add de remote backup as Device

use master
exec sp_addumpdevice 'disk','RemoteBackupDevice','\\yourRemotePath\backup.bak'
go


Then restore de database from the Device created.

restore database yourdatabasename from [RemoteBackupDevice] with file=1,
move N'DATA_FILE_NAME' to N'c:\Data\DATA.mdf'
move N'DATA_LOG_NAME' TO N'C:\LogData\LOG.LDF'
, NOUNLOAD, STATS=5
go


or restore the backup databse without creatting a device

restore database yourdatabasename from disk ='\\yourRemotePath\backup.bak' with recovery;


go


That's all.

martes, 24 de abril de 2012

SSMA Declare variable when Sp doesn't have migrated code

When for some reason, a SP code can't be migrated, and beteewn BEGIN and END it doesn't appear something, SSMA createa

declaration variable to prevent SP creation error.

For example:

 

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

select @nombre = db_name (tempdb_id(@@spid))


END

GO

 

This SP has to be migrated to SQL Server, but SSMA and SQL Server doesn't have tempdb_id(spid) "function", and it comment the line code.

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

/*

*SSMA error messages:

* S2SS0020: The identifier 'db_name(UNKNOWN)' was not converted

select @nombre = (NULL)


*/

END

GO

 

If we try to execute this, in SQL Server, shows an error,  because the SP needs to have something between BEGIN and END.

This is the reason that SSMA generate a line code declaration:

 

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

/*


*SSMA error messages:


* S2SS0020: The identifier 'db_name(UNKNOWN)' was not converted


select @nombre = (NULL)


*/


DECLARE @db_null_statement3 int


END

GO

 



 

jueves, 19 de abril de 2012

Create User for Contained Database


Following with conained Databases in SQL Server 2012 (Enabling contained databases), we must create a User for login in.

The first step is to create the SQL user User with Password (userName: containedUser)



Make sure to set a membership role, for example db_owner.



Now, try to connect to Contained database, choose SQL Authentication and complete your Login and Password.



The most important!, don't forgett to specify the database Name, in connection properties.



This is the flow that SQL Server use to log in Database.



Thats's all

martes, 17 de abril de 2012

Enabling contained Databases in SQL Server 2012

First of all you've to allow to your instance to use Contained Databases.

There are two ways, with GUI, or T-SQL:

In Advanced options for Server properties



Executing this statements

EXEC sys.sp_configure N'contained database authentication', N'1' GO RECONFIGURE WITH OVERRIDE GO


Then can use contained databases.

For specifying a contained DB you can go to Option menu for the Database Properties.



Or execute the T-SQL

USE [master] GO ALTER DATABASE [XYZ] SET CONTAINMENT = PARTIAL WITH NO_WAIT GO


If you want to create a new Contained Database:

CREATE DATABASE XYZ CONTAINMENT = PARTIAL

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.)

viernes, 30 de marzo de 2012

Cannot resolve collation conflict for equal to operation

Collation in SQL Server can be set at column level.

If you have differente collation in two columns, and you try to make some kind of query with one of these things:

1) Join predicates

2) Where clauses

3) Use more than one database or temporal tables  (for example: TempDB with different collation than the other database).

4) Functions

The error message is:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Modern_Spanish_CI_AS" in the equal to operation.

(Latin1_General_CI_AI and Modern_Spanish_CI_AS are collation examples).

See the example to resolve collation column conflict:

Test1.Col1 ->Latin1_General_CI_AI (DATABASE_DEFAULT)

Test2.Col2 ->Modern_Spanish_CI_AS

You can set the COLLATE to Query:

 

SELECT ID FROM Test1


INNER JOIN Test2


WHERE Test1.Col1 = Test2.Col1 COLLATE DATABASE_DEFAULT (or COLLATE Latin1_General_CI_AI)



Another options are set the same Collation to any level column, database, or servers.

To see the collation database, go to properties database, or use this:

SELECT DATABASEPROPERTYEX('DATABASE', 'Collation') as DB;


miércoles, 28 de marzo de 2012

SSMA Bug Migration Sybase to SQL Server

I have been involved with a Sybase migration from Sybase to SQL Server
The migration wizard SSMA(version 5.2.1260) makes the next wrong conversion.

The scenario is:

  • Table named NCO

  • Table named TestConversion with colum name NCO

  • Any statement (Insert, Update, Select...)


Update TestConversion
SET NCO = 0
where NCO > 1


SSMA converted statement

Update TestConversion
SET NCO = 0
where dbo.NCO > 1


SSMA identifies NCO like a table (because the field name matches with Table name) and adds the prefix dbo. (or the name schema especified in the application).
If we want to execute this sentence, it returns an error.

The solution is to change the column name for another name that doesn't match with any table name, or correct all sentences, deleting dbo. in applied cases.

jueves, 22 de marzo de 2012

Change Server Collation

If you want to change Server collation, and tries to execute something like this:

ALTER DATABASE master COLLATE Latin1_General_CI_AI

An error ocurred:

Cannot alter the database 'master' because it is a system database.

The previous instruction only works with non-system database.

To change it in system database, go to console (cmd.exe), and to SQL Server DVD.
Execute something like this.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=YYYYYYY /SQL SYSADMINACCOUNTS=SA /SAPWD= XXXXX /SQLCOLLATION=Latin1_General_CI_AI

INSTANCENAME: your SQL server instance name.
SAPWD: Password of sa (if you choose sa User).
SQLCOLLATION: The collation to change, in this case Latin1_General_CI_AI.

I hope it helps.