Un poco de humor...

...sobre bases de datos

Problema con la instalación del Ms SQL Server Express 2005

En ocasiones la instalación se queda atascada una vez llega a "Setting File Security". Puede estar así incluso hasta varias horas. La causa parece ser la resolución de seguridad con el dominio del programa de instalación. La solucción más rápida a este problema es desconectar el cable de red; a partir de ahí se acaban los problemas.

En este foro (está en inglés) aparecen todos los detalles. Microsoft tiene disponible un parche que podéis encontrar aquí

Comprobar que los servicios SQl Server están corriendo

Os pongo un script en Perl que comprueba si los servicios SQL Server están funcionando.

use Win32;
use Win32::Service;
use Time::localtime;

$tp = localtime; #for current
$variable= sprintf("%04d-%02d-%02d-%02d\n", $tp->year+1900, ($tp->mon)+1, $tp->mday);
$variable=~s/\s//g;
$user = Win32::LoginName();
$node = Win32::NodeName();

print "***********************************\n";
print " SQL SERVICES REPORT \n";
print " \n";
print "Creation Date: $variable\n";
print "User: $user\n";
print "Node: $node\n";
print "**********************************\n";
print "\n";

%state = (
0 => 'unknown',
1 => 'stopped',
2 => 'starting',
3 => 'stopping',
4 => 'running',
5 => 'resuming',
6 => 'pausing',
7 => 'paused',
8 => 'undefined', # used only by Show_Service.pl
);

@computers = (

" ServidorA",
" ServidorB",
" ServidorC",
" ServidorD"


);

sub ltrim($)
{
my $string = shift;
$string =~ s/^\s+//;
return $string;
}


foreach $Server (@computers) {
$Server= ltrim($Server);
#$key = shift;
$services= shift;
%status = shift;
%services = shift;


my ($key, $services, %status );


print "\nStatus on $Server:\n";
print "==========================================\n";
Win32::Service::GetServices($Server,\%services) or print "***ERROR: Can't retrieve the info from $Server\n";

foreach $key (sort keys %services){

if ($services{$key} =~ /SQL/){
Win32::Service::GetStatus( $Server, $services{$key}, \%status);

print "SERVICE: $key ,STATUS: $state{$status{CurrentState}} \n";

push @MyServices, $services{$key};


}}};

Nueva versión de las herramientas RML

En el blog de PPS engineers Microsoft anuncian que SQL Server support Team pone a disposición pública la nueva versión de algunas de herramientas que ellos emplean internamente (curioso que en el blog del 'supporte team' de momento no aparezca noticia alguna sobre esto)

Esta es la entrada donde se pueden encontrarse más detalles de las 'RML Utilities'
Para descargar las utilidades aquí para 32bits x86
o en este para la versión de 64bits 64 bits

Arreglar usuarios huérfanos

Este script simplemente ejecuta sp_change_users_login con la opción 'auto_fix' de manera automática para cada uno de los usuarios huérfanos de una bbdd


declare @usr varchar(100)
declare @cmd varchar(100)

declare cur insensitive cursor for

select name as 'Usuario' from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

for read only

open Cur

fetch next from Cur into @usr
while @@fetch_status=0

begin

select @cmd=' sp_change_users_login ''auto_fix'', '''+@usr+''' '

exec(@cmd)

fetch next from Cur into @usr

end

close Cur

deallocate Cur

Transferir usuarios y passwords en SQL Server

Todo un clásico: sp_help_revlogin.
El script original fue creado por Microsoft. La versión que pongo es una modificación de un compañero (gracias Javier) que añade la bbdd por defecto del usuario e introduce la posibilidad de de seleccionar sólo los usuarios de una bbdd .

Para correr este script:

Ejecutar en servidor origen. Se crearán dos procedimientos almacenados: sp_hexadecimal and sp_help_revlogin (en caso que existieeran se borrarían y se volverían a crear)
Una vez creadas, ejecutar en QA sp_help_revlogin con las opciones deseadas. EL procedimiento generará un script con los usuarios a transferir. Ya sólo queda lanzar el script resultante en el servidor destino (copiar y pegar en QA destino)


EXEC master..sp_help_revlogin -- todos los logins
EXEC master..sp_help_revlogin 'myDB' -- logins para bbdd
EXEC master..sp_help_revlogin 'myDB', 'myUser' -- usuario en my bbdd

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
DECLARE @dbid int --jgs

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password,dbid FROM master..sysxlogins --jgs, added dbid field
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password,dbid FROM master..sysxlogins --jgs, added dbid field
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd,@dbid --jgs added dbid variable
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN

Generar informes en Excel desde SQL Server

Excel reconoce las etiquetas HTML. Así pues, para generar informes en este formato basta sólo darle formato a la salida de nuestra consulta. Puede usarse directamente desde QA o bien crear un 'Job' que genere un archivo *.xls y luego automáticamente lo envíe por correo.

Dejo un ejemplo de un script que genera un informe sobre incidencias ocurridas durante la semana de guardia. Los comentarios están en inglés ( idioma oficial de mi empresa)

El script se invoca desde un 'job' que luego envía el informe por correo usando xp_smtp_sendmail

/*

File: oncall.sql
Desc: Script to generate a xls report of the oncall records
Author: Luis González
@@bof_revsion_marker
revision history

yyyy/mm/dd by description
========== ======= ========================================================
2006/10/11 luis v1.0.0.0 created
@@eof_revsion_marker
***************************************************************************
*/
/*
Instructions to run this script on query analizer:

Copy and paste this script on Query Analyzer
On the Menu, go to TOOLS-OPTIONS and on the RESULTS tab, on the MAXIMUM CHARACTER PER COLUMN set the value to 1000
On Menu, go to QUERY and click RESULTS ON FILE
Run the query
*/

use DBMONITOR
GO
SET NOCOUNT ON

print '<BR>'
print '<P ALIGN ="left" ><B>DBCOE ON CALL LOG REPORT</B></P>'
print '<P ALIGN ="left"><I> from ' + convert(char(20),getdate()-8) +'to '+ convert(char(20),getdate())+'</I></P>'
print '<BR>'

IF (SELECT COUNT(*) FROM oncalllog WHERE datediff(day,dateoccurred,getdate())<8 )= 0 /*checks only the last seven days*/


select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>THERE ARE NOT ONCALL RECORDS</B></A> </TD></TR>'

ELSE

BEGIN
select '<DIV ALIGN="center"><TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="7" ALIGN="center" VALIGN="top"><A><B>REPORT </B></A> </TD></TR>'
union all
select ' <TR BGCOLOR="EEEEEE">
<TD ALIGN= "left" WIDTH= "5%"><B>Dba Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Date Occurred</B></TD>
<TD ALIGN= "left" WIDTH= "5%"><B>PQR #</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Server Name</B></TD>
<TD ALIGN= "left" WIDTH= "7%"><B>Instance Name DB</B></TD>
<TD ALIGN= "left" WIDTH= "29%"><B>Problem Description</B></TD>
<TD ALIGN= "left" WIDTH= "40%"><B>Resolution Description</B></TD>
</TR>'
union all



select '<TD VALIGN="top"> '+ rtrim(dbaname)+ ' </TD>
<TD VALIGN="top"> '+ convert(char(10),dateoccurred,121)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(pqr#)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(servername)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(instancename)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(problemdesc)+ ' </TD>
<TD VALIGN="top"> '+ rtrim(resolutiondesc)+ ' </TD>
</TR>' from oncalllog where datediff(day,dateoccurred,getdate())<8 -- the last seven days

union all


SELECT '</TABLE>'

print '<BR>'
print '<P ALIGN ="left" ><B>END OF REPORT</B></P>'
END



SET NOCOUNT OFF

SQL Server Agent Tokens

Los token son de gran utilidad y sin embargo su uso no es tan generalizado como debiera. En el siguiente enlace tenéis muy buena información sobre ellos. A partir de Ms SQL 2005 Microsoft
incluye xp_smtp_sendmail como correo por defecto. Asimismo ofrece la posibilidad de usar de una manera fácil los tokens del Agente para enviar información personalizada de los errores.

Pero para las versiones 7 y 2000, aún hay que seguir usando una 'manera más artesanal'. Aquí os pongo un ejemplo que utilizo para notificar errores en jobs o alertas que tengo configuradas (otro día comentaré algo sobre ellas)



declare @msg varchar(4000)
declare @srvr varchar(80)

set @srvr = '[SRVR] Error on [A-DBN]'

set @msg = ' Error on [SRVR]' + char(10)
set @msg = @msg + 'Error: [A-ERR]' +char(10)
set @msg = @msg + 'Date: [STRTDT], Time: [STRTTM]' + CHAR(10)
set @msg = @msg + 'Database: [A-DBN]' + CHAR(10)
set @msg = @msg + 'Message: ' + REPLACE("[A-MSG]", '''','') + CHAR(10) + CHAR(10)
set @msg = @msg + 'Please, verify Error Log for details'

exec master.dbo.xp_smtp_sendmail @From=N'dbalert@domain.com',
@to=N'sqlast@sqlast.com',
@Subject=@srvr ,
@Message= @msg ,
@server=N'emailserver.com'

Error 3205:Restaurar bbdd de SQL 2005 a SQL 2000

Cuando se intenta restaurar desde SQL 2005 a 2000 se produce el siguiente error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

Si se intenta hacer un 'attach' de la base de datos aparece el siguiente error:

Error 602 Severity Level 21
Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes

Estos errores se deben a que SQL 2005 tiene una estructura interna diferente de versiones anteriores, haciéndolo inteligible a versiones previas. Esto es común a todos los sistemas de bbdd; una nueva versión suele conllevar cambios estructurales.

Por el momento, la única manera posible es crear una bbdd vacía en SQL 2000, crear los objetos y luego exportar los datos bien por bcp o SSIS

En este foro, aparece un tutorial muy bueno ( en inglés) que indica los pasos a seguir

Simplificando y de modo rápido :

En Managment Studio, boton derecho en bbdd , tareas, genera scripts. Esto abririá un asistente.
Seleccionar bbd, siguiente.
En General seleccionar:

a. Script Collation , a TRUE

b. Script Database Create, a TRUE

c. Script of SQL Version, a SQL SERVER 2000

d. Script foreign keys, a FALSE

e. Script Triggers, a FALSE

Seleccionar siguiente y elegir cómo queréis salvar los scripts y finalizar.
con los scripts ya creados, usando el QA crearlos en la version 2000. Revisad los script que SQL genera porque podréis encontraros las vistas, en el caso que las tengáis puestas sin ningún orden.

El paso siguiente, para mover los datos, de nuevo botón derecho- tareas- exportar. Seguir las instrucciones del asistente (origen-destino). El tutorial advierte de un posibles problemas cuando se trata de transferir más de 12 elementos a la vez (personalmente no me ha pasado, igual era en SP1) Si os pasa, no queda más remedio que hacerlo en varias veces.

Por último, habrá que generar los scripts para los Triggers y las claves foráneas. Volved al Managment Studio, botón derecho en bbdd- tareas- Generar scripts. Aseguraros que el botón de 'Script todos los objetos' no está seleccionado, siguiente y

a. Incluir IF NOT EXISTS , a TRUE

b. Script Owner, a TRUE

c. Script of SQL Version, a SQL SERVER 2000

d. Script foreign keys, a TRUE

e. Script Triggers, a TRUE

Tras leer los elementos de la bbdd, aparece una nueva lista y un cuadro de diálogo. Seleccionar sólo las tablas y darle a siguiente. sólo queda seleccionar cómo queréis salvarlo.

Revisar los scripts y sustituir SYS.OBJECTS por SYSOBJECTS OBJECT_ID por ID

Ejemplo

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))

sería entonces

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))




SQL Server 2005, significado de Error 18456

Los fallos de 'login' genéricamente tiene asignado el número 18456, seguido de un'severity' y un 'state' en SQL Server
Recientemente el equipo SQL Protocols de Microsoft, ha publicado en su blog el significado de los 'state'

ERROR STATE DESCRIPCIÓN
2 y 5 Inválido userid
6 Intento de usar login de windows con autentificación SQL
7 Login deshabilitado y contraseña no coincide
8 Contraseña no coincide
9 Contraseña inválida
11 y 12 Login válido pero fallo en acceso al servidor
13 Servicio SQL Server en pausa
16 Usuario no tienen permisos para conectarse a la bbdd
18 Requerido cambio de contraseña

Nota: A partir de Windows Vista, el error 18456 puede ocurrir en conexiones locales debido a cómo Windows maneja las cuentas de usuario. En este post más información

Error: 17806, Severity: 20, State: 2

En los últimos días he comenzado a ver este error en SQL 2005: Error: 17806, Severity: 20, State: 2, seguido de esta otra entrada SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: named pipes ]

El problema era que un usuario intentaba conectarse al servidor de SQL 2005 desde otro dominio con el que no existía relación de confianza.

El usuario se mapeaba a una unidad del servidor SQL, dominioA, usando sus credenciales del dominioA, pero desde una máquina en dominioB.

Esta fórmula que funionó perfectamente para versiones anteriores de SQL ya no es válida desde que Micorosoft consideró que utilizar un canal de seguridad existente supone un alto riesgo.

La solucción fue ejecutar el 'managment studio' con las credenciales del dominioA ('runas' o ejecutar como')

Podéis encontrar más información en el blog de sql_protocols

Extraer SQl Server Logs

Funciona en SQL 7, 200 y 2005. Extae los logs de los últimos dos días (para más o menos días podéis modificar la tabla temporal que recoge la información de xp_enumerrorlog)



use MASTER
go

SET NOCOUNT ON
DECLARE @archive int
if (SELECT @@VERSION ) like '%Microsoft SQL Server 2005%'

goto yukon

else

goto twokey

twokey:




CREATE TABLE #Errors (Text varchar(255), ID int)
CREATE INDEX idx_msg ON #Errors(ID, Text)


CREATE TABLE #tmp
(
archive int
,[date]datetime
,[size] int -- no retrieved by xp_enumerrorlogs 7.0
)
--SO

IF (SELECT @@version ) like '%7.00%'

INSERT INTO #tmp(archive,[date])
EXEC xp_enumerrorlogs

ELSE
INSERT INTO #tmp EXEC xp_enumerrorlogs



DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp
INSERT #Errors EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date1 nvarchar(11)
declare @date2 nvarchar(11)

select @date1 = convert(char(10),getdate(),121)
select @date2 = convert(char(10),getdate()-1,121)

set @date1 = @date1 +'%'
set @date2 = @date2 +'%'


PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT Text FROM #Errors WHERE Text NOT LIKE '%Log backed up%' AND
Text NOT LIKE '%.TRN%' AND Text NOT LIKE '%Database backed up%' AND
Text NOT LIKE '%.BAK%' AND Text NOT LIKE '%Run the RECONFIGURE%' AND
Text NOT LIKE '%Copyright (c)%'AND
Text NOT LIKE '%Login succeeded%'AND
(Text like @date1 or Text like @date2)

print '==========================================================================================================='


DROP TABLE #Errors
goto fin

yukon:





create table #errors2 (logdate datetime, process varchar(16), texto varchar (800))
CREATE TABLE #tmp2
(
archive int
,[date]datetime
,[size] int
)

insert into #tmp2 exec xp_enumerrorlogs

DECLARE dbcursor cursor FOR SELECT archive
FROM #tmp2 WHERE archive <>0 AND
datediff(day,[date],getdate())< 3 -- errorlogs from two days old
ORDER BY archive DESC
OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @archive
WHILE @@fetch_status = 0

BEGIN
INSERT #Errors2 EXEC xp_readerrorlog @archive

FETCH NEXT FROM dbcursor INTO @archive
END

CLOSE dbcursor

DEALLOCATE dbcursor

DROP TABLE #tmp2
INSERT #Errors2 EXEC xp_readerrorlog

-- to retrieve from the last errorlog
-- since with 0 as parameter won't return data but an error

declare @date3 datetime
declare @date4 varchar(12)

select @date3 = getdate()-1


select @date4= convert(varchar(11),@date3,121)





PRINT ' '
PRINT ' '
SELECT 'SQL SERVER ERROR LOG. CREATION DATE: '+ CONVERT(char(20),getdate(),120)
print '=========================================================================================================='
SELECT LogDate,Process, rtrim(Texto) as 'Mssge' FROM #Errors2 WHERE Texto NOT LIKE '%Log backed up%' AND
Texto NOT LIKE '%.TRN%' AND Texto NOT LIKE '%Database backed up%' AND
Texto NOT LIKE '%.BAK%' AND Texto NOT LIKE '%Run the RECONFIGURE%' AND
Texto NOT LIKE '%Copyright (c)%'AND
Texto NOT LIKE '%Login succeeded%'AND
logdate > @date4
order by logdate

print '==========================================================================================================='


DROP TABLE #Errors2

fin: