Trucos SQL. Por David Lozano. 11 de mayo de 2003. Nombre Servicios que pueden interferir con la instalación Descripción Servicios que pueden hacer que salga el error: "unable to write to mailslot" durante la instalación: - Oracle - DBWeb - Personal Web Server - IIS - Microsoft SMTP - Microsoft NNTP - Exchange Server - SNA Server - BackupExec - Disk Keeper - Servicios SNMP. SELECT @@SERVERNAME Cambiar el hostname de Windows. -d: Ubicación de la base de datos master. -e: Ruta del registro de error. -l: Ruta del archivo de registro de master. -c Hace que el servidor no sea un servicio de Windows. -f Inicia en modo mínimo. -g Reserva un bloque de memoria en megas para uso de los procesos de SQL Server que no son componentes principales. -m Inicia el servidor en modo único usuario y desactiva el proceso CHECKPOINT. -n Desactiva la inscripción de eventos en Windows NT/2000 -s Para iniciar una instancia por nombre. Por ejemplo Sinstancia1 -T Activa los indicadores de traza. SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1 RECONFIGURE sp_helpdb Northwind ALTER DATABASE Northwind SET SINGLE_USER ::::::::::::::::::::::::::::::::::::::::::::::::: Modo único usuario y echa a todos: ALTER DATABASE Northwind SET SINGLE_USER with ROLLBACK IMMEDIATE ::::::::::::::::::::::::::::::::::::::::::::::::: Modo único usuario y echa a todos después de x segundos: ALTER DATABASE Northwind SET SINGLE_USER with ROLLBACK AFTER 60 SECONDS ::::::::::::::::::::::::::::::::::::::::::::::::: Para volver al estado normal: SET MULTI_USER Variable del servidor Cómo cambiar el nombre de un servidor Parámetros de inicio Cómo configurar opciones avanzadas Propiedades de cualquier base de datos Modo único usuario Dejar la base de datos como sólo lectura ALTER DATABASE Northwind SET READ_ONLY with ROLLBACK IMMEDIATE para volver a dejar igual: SET READ_WRITE Página 1 de 31 Nombre Reducción automática Cómo dejar fuera de lína las bases de datos Descripción ALTER DATABASE Northwind SET AUTO_SHRINK ON ALTER DATABASE Northwind SET OFFLINE Para poner en línea: SET ONLINE Cómo cerrar automáticamente una base de datos Cómo cerrar automáticamente una base de datos cuando no tenga ningún usuario conectado: ALTER DATABASE Northwind SET AUTO_CLOSE ON ALTER DATABASE Northwind SET AUTO_CREATE_STATISTICS ON Es el 1433. xp_regwrite xp_regread xp_regremovemultistring xp_addmultistring xp_regdeletevalue xp_regnumvalues sp_OACreate sp_OADestroy sp_OAStop sp_OAGetProperty sp_OASetProperty sp_OAMethod sp_OAGetErrorInfo Sp_procoption 'sp_procedurename', 'startup', 'true' Si los procedimientos almacenados no se ejecutaran en el inicio habría que comprobar la opción "Scan For Startup Procs". Debe tener el valor "1" Cómo crear estadísticas automáticamente Puerto por defecto del SQL Server Procedimientos almacenados para modificar el reg. Procedimientos almacenados para automatización Ejecutar procedimientos almacenados en el inicio Requesitos para configurar bien SQLMail con - Para que funcionen bien los perfiles MAPI, la versión de Outlook debe ser superior a la 5.5.1960.0 - Asegurarse de iniciar el Servicio de MSSQLServer con la cuenta que usó para crear su perfil de correo. - Se puede iniciar SQL Mail con: xp_startmail y detenerlo con: xp_stopmail EXEC xp_sendmail @recipients = '
[email protected];
[email protected]', @query = 'sp_who', @subject = 'SQL Server Connections', @message = 'Resultado atachado de la consulta sp_who', @attach_results = 'TRUE', @width = 250 Más parámetros en la ayuda. Enviar correo con SQLMail Uso de variables Declarar variables: DECLARE @variable varchar (56) Introducir valores en las variales: SET @variable = 'SELECT tmpcolumn FROM ##workaround' Código VBS para enviar correo SMTP Set M = CreateObject ("CDONTS.NewMail") M.To = "
[email protected]" M.From = "
[email protected]" M.Subject = "Aquí va el asunto" M.body = "Aquí el body" M.Send Set M = nothing Página 2 de 31 Nombre Archivo de registro de error del SQL Server Agent Descripción Está en: C:\Archivos de programa\Microsoft SQL Server\MSSQL\LOG\SQLAGENT.OUT Cada vez que se para y se arranca el servicio se crea una entrada en el registro. Introducción de direcciones d mail en Procedimiento extendido para línea de comando Proveedores OLE DB Hay quen encerrarla entre corchetes y con SMTP. Así: [SMTP:
[email protected]] xp_cmdshell Tipo de Conexión - Dónde encontrarla SQL Server - Instalada con SQL Server Microsoft Access - Instalada con SQL Server Microsoft Excel - Instalada con SQL Server Dbase - Instalada con SQL Server HTML File - Instalada con SQL Server 2000 únicamente Paradox - Instalada con SQL Server Text File - Instalada con SQL Server Oracle - Instalada con SQL Server Data Link (UDL) - Instalada con SQL Server DB2 - Necesita Host Integration Services o un tercero Sybase - Necesita software de cliente Sybase VSAM - Necesita Host Integration Services o un tercero - Localmente en SQL Server: Es el método más común. Se almacentan en la base de datos msdb y la tabla sysdtspackages. - En Meta Data Services: También se almacenan en la base de datos msdb. Es más lento de guardar y de ejecutar. - Como archivo de almacenamiento estructurado COM: Son ficheros con extensión .dts. Para ejecutarlo hay que pulsar con el botón derecho en el grupo Servicios de tranformación de datos y seleccionar Abrir paquete. - Como archivo VB: Se guarda con extensión .bas y luego Se utiliza DTSRUN desde la línea de comando. Para lanzarlo desde el analizador de consultas sería con xp_cmdshell + DTSRUN Formas de guardar un paquete DTS Lanzar DTS desde la línea de comando Asistente para crear instrucciones del DTSRUN.EXE Asistente para crear instrucciones del DTSRUN.EXE de forma gráfica. Se llama DTSRUNUI.EXE y está en c:\Archivos de programa\Microsoft SQL Server\80\Tools Solución de error al ejecutar un DTS programado Los paquetes DTS programados los lanza el SQL Server Agent, con el usuario con el que haya sido iniciado SQL Server Agent. El usuario debe tener los permisos necesarios para ejecutar el paquete. Archivo - Descripción Axscphst.dll - Maneja la elaboración de secuencias de comandos ActiveX Axscphst.rll Dtsffile.dll - Proveedor de archivos planos Dtsffile.rll Dtspkg.dll - Maneja el paquete Dtspkg.rll Dtspump.dll - Maneja la tarea de bombeo de datos Dtspump.rll Dtsrun.exe - Ejecuta el paquete Dtsrun.dll Custtask.dll - Maneja la tarea de personalización Sqlwoa.dll - Traducción ANSI para SQL Server SQLwid.dll SQLresld.dll - Carga archivos de recursos Lista de archivos para hacer DTS independientes Página 3 de 31 Nombre Administración de mensajes de error predefinidos Descripción Los mensajes se guardan en la tabla sysmessages dela base de datos Master. Se pueden agregar mensajes de error definidos por el usuario agregándolos directamente en la tabla o desde: "Administrar mensajes de SQL Server" en el menú "Herramientas". Cuando crees un nuevo mensaje de error te asignará el número a partir del 50001. Los números anteriores están reservados para el sistema. El nivel de gravedad de 2 a 6 también está reservado para el sistema. Cuanto más alto sea el número, más grave es el mensaje. También se puede utilizar el procedimiento almacenaso sp_addmessage: USE master EXEC sp_addmessage @msgnum = 50008, @severity = 10, @msgtext=N'Ha tardado mucho, reenvía tu query.', @with_log = 'true' La síntasis es: RAISERROR (ERROR #, Severity Level, State) Ejemplo: RAISERROR (50001, 10, 1) Para que se registre en el servidor: RAISERROR (50001, 10, 1) WITH LOG Nota: Cualquier error con nivel de gravedad 20 o superior es fatal y en cuanto aparece el usuario es desconectado. Cómo generar un error El monitor de sistema de Windows 2000 Cuando instalas SQL Server se instalan contadores adicionales en el monitor del sistema de Windows 2000. Nota: Si no aparecen los contadores después de instalar SQL Server, habría que descargarlos: unlodctr.exe MSSQLServer y volverlos a cargar: lodctr.exe \BINN\sqlstr.ini Nota: Es mejor monitorizar desde un equipo remoto. Nota: Los datos para los contadores de SQL Server están almacenados en la tabla del sistema sysperfinfo en la base de datos master. Sólo las primeras 99 bases de datos son almacenadas en la tabla. Indicadores de traza Permiten activar o desactivar temporalmente una función de una base de datos. Se activa con: DBCC TRACEON(1807) Para inhabilitar: DBCC TRACEOFF(1807, 3604) En las dos opciones puedes utilizar comas "," para usar múltiples trazas. Para conocer el estado de las trazas: DBCC TRACESTATUS(3604, 1807) Para la lista completa de trazas actias: DBCC TRACESTATUS(-1) Puedes iniciar trazas automáticamente en el inicio del servidor SQL con el modificador -T en los parámetros de inicio: -T1807 Página 4 de 31 Nombre Trazas más útiles Descripción - Información de interbloqueo (1204) - Información detallada de interbloqueo (1205) - Envía información detallada acerca del bloqueo al registro de errores. - Desactivar comprobación en parelelo (2528) - Inhabilita el uso de culaquier procesador que no sea el primaro a SQL Server cuando realice revisiones de consistencia. - Archivos de bases de datos en la red (1807) - Permite crear archivos de bases de datos o de registros en unidades de red. - Enviar salida de traza al cliente (3604) - Enruta los mensajes hacia el cliente en lugar del registro de errores. - Enviar salida de traza a registro de errores (3605) - Igual que la anterior pero envía los resultados al registro de errores. - Omitir recuperación automática (3607) - Omite la recuperación de bases de datos en el inicio de SQL Server y borra el TempDB. Permite superar ciertas caídas. - Omitir recuperación automática excepto Master (3608) - La diferencia con la anterior es que no borra el TempDB y sólo se recupera la base de datos Master. - Registrar las conexiones (4013) - Escribe una nueva entrada en el registro de errores de SQL Server cada ver que se establece una nueva conexión. - Omitir procedimientos almacenados de inicio (4022) Obliga a SQL Server a omitir procedimientos almacenados Para crear una alerta, asegurarse de que el Agente SQL Server esté iniciado. En el Administrador corporativo, ir a "Agente SQL Server" y dentro elegir "Alertas". Las alertas sólo saltan si el error se registra. 1. Crear una bases de datos de auditoría o crear las tablas en una bases de datos existente: CREATE Table DatabaseSizeReport ( Database_Name Varchar(32), Database_Size int, CreateDt datetime) 2. Programar el trabajo para que se ejecute una vez al día, a la semana o al mes, según las necesidades: INSERT INTO DatabaseSizeReport EXEC usp_databases El procedimiento almacenado usp_databases se puede bajar de http://www.admin911.com como una alternativa para sp_databases. Esta consulta genera el nombre y tamaño (en kilobytes) de cada base de datos del sistema. 3. Utilizar Excel para analizar los datos recopilados. En Excel seleccionar Datos/Obtener datos externos y usar la siguiente consulta: SELECT Database_Name, Database_Size, CreateDT from DatabaseSiceReport Order by Database_Name Luego puedes crear gráficas Alertas Pronóstico del crecimiento de las bases de datos Página 5 de 31 Nombre Recopilación d diversa información con Descripción La utilidad SQLDIAG.EXE recopila información de diversas fuentes y saca el resultado a un archivo de texto SQLDIAG.TXT, que está en \Logs. La utilidad SQLDIAG.EXE está en \BINN Esta es la información del fichero de texto: - Últimos cinco registros de error de SQL Server. - Información del Registro. - Información y versiones de librería de vínculos dinámicos (Dynamic-link Library, DLL) - Resultados de un informe sp_configure - Resultados de sp_who y sp_lock - Inventario de todos los procedimientos almacenados extendidos del sistema - Información de la confiuración del sistema operacional, red y hardware. - Las últimas 100 consultas que se ejecutan si la caja negra está activada La caja negra registra información de consultas en un archivo de traza denominado blackbox.trc en el directorio \Data. Escribe el archivo de traza en bloqu3es de 128 K. Para habilitar la caja negra, ejecutar el procedimiento almacenado sp_trace_create con el parámetro 8 así: DECLARE @TraceID int EXEC sp_trace_create @traceid output, 8 EXEC sp_trace_setstatus @traceid, 1 La traza registra la siguiente información: - Cuándo se ejecutó la consulta - Nombre de inicio de sesión - Aplicación - Nombre del host - Números de error con gravedad - Nombre de usuario y de dominio Windows NT - Base de datos a la que se está conectando La traza sedetiene y se cierra automáticamente cuando SQL Server se detiene. Puedes detener y cerrar la traza manualmente usando el procedimiento almacenado sp_trace_setstatus: EXEC sp_trace_setstatus 1,0 La caja negra Página 6 de 31 Nombre Comandos de OSQL Descripción -? Enumera las opciones disponibles -L Enumera los servidores configurados localmente -S Servidor al cuál conectarse -U Nombre de inicio de sesión -P Contraseña -E Usar conexión confiable -H Nombre de la estación de trabajo que debe aparecer en sp_who -d Nombre de la base de datos en la que se debe iniciar -I Tiempo inactivo de inicio de sesión en segundos -t Tiempo inactivo de comando en segundos -h Número de filas entre títulos de columnas -s Separador de columnas -w Ancho de columna (predeternminado en 80 caracteres) -a Tamaño del paquete de red -e Muestra lo escrito -l Activa los identificadores entre comillas -D Nombre DSN al cual se debe conecar -c Terminador del comando -q Ejecuta consulta entre comillas dobles y permanece en osql -Q ejecuta consulta entre comillas dobles y sale de osql -n Elimina numeración de los resultados -m Personaliza los mensajes de error -I Archivo de entrada para consultas -o Archivo de salida para resultados -p Genera estadísticas de rendimiento Vienen algunas plantillas predefinidas en la pestaña de al lado del analizador de objetos. Para utilizarlas se arrastran hacia la derecha y aparece con unos valores por defecto entre corchetes angulares "< >" Los valores se pueden cambiar desde Editar/Reemplazar parámetros de plantilla. Para crear plantillas propias: 1. Copiar el archivo de plantilla en la carpeta: c:\Archivos de programa\Microsoft SQL server\80\Tools\Templates\SQL Query Analyzer 2. La entrada de valores en el fichero de plantilla debe escribirse así: Ejemplo: -- ===================== -- Author: -- Purpose: -- Created: -- Company: -- ===================== Uso y creación de plantillas en SQL Query Espacio utilizado de una base de datos sp_spaceused Página 7 de 31 Nombre Agregar un procedimiento almacenado extendido Descripción 1. Copiar el archivo DLL del procedimiento en la ruta \Archivos de programa\Microsoft Sql Server\Mssql\BINN 2. Abrir el Administrador corporativo y buscar el grupo Procedimientos almacenados extendidos de la base de datos master. 3. Pulsar el botón derecho del ratón en el grupo Procedimientos almacenados extendidos y seleccionar Nuevo procedimiento almacenado extendido. 4. Asignar un nombre al procedimiento almacenao, y especificar el archivo DLL 5. Pulsar en Aceptar, y luego para otorgar los pormisos indicados, abrir de nuevo el procedimiento almacenado extendido y pulsar en Permisos. También se pueden agregar a través de procedimientos almacenados extendidos a través del procedimiento almacenado del sistema SP_ADDEXTENDEDPROC. Sólo requiere dos parámetros: SP_ADDEXTENDEDPROC 'XP_PROCNM', 'XP_PROC.DLL' Uso de XP_CMDSHELL Ejecuta programas desde la línea de comando: master..xp_cmdshell "DIR c:\" Para hacerlo con retardo: master..xp_cmdshell "net send MAQUINA Esto es un mensaje de prueba", no_output WAITFOR DELAY '00:00:30' Procedimientos para tratar ficheros Leer el registro de error con T-SQL xp_fileexist xp_getfiledetails SET NOCOUNT ON CREATE TABLE #error_lg ( errortext varchar (500), continuerow int) INSERT INTO #error_lg exec .master..xp_readerrorlog SELECT TOP 10 errortext from #error_lg WHERE errortext like '%severity%' order by 1 desc Usar: DBCC CHECKDB con sus opciones Se utiliza: DBCC OPENTRAN Para cerrarlas: KILL 54 (número de transacción) Chequear y reparar una base de datos Ver transacciones abiertas Uso de cadenas de caracteres LEFT RIGHT SUBSTRING + Ejemplos: 1. SELECT LEFT ('ABCDEFG',3) AS Ejemplo 2. SELECT SUBSTRING ('91546876546',4,3) + SELECT RIGHT('87956',2) Detener los servicios de automatización OLE EXEC sp_Oastop Página 8 de 31 Nombre Listado de servidores SQL en el registro de Descripción La clave de registro donde está la lista de servidores que aparecen a elegir es: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\Clients\PrefServer Con estas dos entradas: - ServerN (donde N representa el número del servidor), que contiene el nombre, el alias o la dirección IP del servidor. - UserN (donde N corresponde a la entrada ServerN), que contiene el ninicio de sesión predeterminado para el servidor cuando usted selecciona desde la caja desplegable en la interfaz gráfica de usuarios, GUI de herramientas de SQL Server. Página 9 de 31 Nombre Instalar SQL Server en un clúster Descripción HOWTO: Configure MSDTC in a Windows 2000 Cluster Environment [iis] ID: Q290624 CREATED: 23-FEB-2001 MODIFIED: 15SEP-2001 :2000,2000 SP1,2000 SP2,5.0,7.0 PUBLIC | =================================================== =================== ------------------------------------------------------------------------------The information in this article applies to: - Microsoft Internet Information Services version 5.0 - Microsoft Cluster Server - Microsoft Windows versions 2000, 2000 SP1, 2000 SP2 Advanced Server - Microsoft SQL Server, Enterprise Edition, version 7.0 - Microsoft SQL Server 2000 Enterprise Edition ------------------------------------------------------------------------------SUMMARY ======= After you install Cluster Server on Windows 2000 Advanced Server and configure Internet Information Services (IIS) or SQL Server, you may need to manually install Microsoft Distributed Transaction Coordinator (MSDTC) as a cluster instance. By default, MSDTC installs into the cluster group and sets its log file on the Quorum disk. However, because you want MSDTC to move from node to node (as the IIS or SQL group moves), you should configure MSDTC in a Windows 2000 cluster. NOTE: This article assumes that the cluster has more than one shared disk. SQL Server users should use the following SQL specific Qarticle: Q294209 INF: Rebuilding or Moving MSDTC Used with a Failover Clustered SQL Server MORE INFORMATION ================ To configure MSDTC in a Windows 2000 cluster, follow these steps: 1. On node A, open Cluster Administrator and select the cluster group. 2. In the cluster group, create the first IP resource for your first Web or SQL resource. NOTE: The IP resource should have no dependencies. 3. Create a network name resource for your first Web or SQL resource. The only dependency for this resource is the IP resource that you created in step 2. 4. Right-click the quorum disk resource, click Change Group, and then select Disk Group 1. A warning dialog box appears. Click Yes to Página 10 de 31 Nombre Descripción dismiss the warning, then click Yes in the Move Resource dialog box. NOTE: Make sure that you note the disk letter before you move the disk resource. 5. Locate the disk group that will hold your IIS or SQL resources. Right-click the disk resource, click Change Group, and then select Cluster Group. A warning dialog box appears. Click Yes to dismiss the warning, then click Yes in the Move Resource dialog box. NOTE: If this is Disk Group 1, be sure to right-click the IIS or SQL disk and not the quorum disk that you just moved to this group. 6. Close Cluster Administrator. 7. On node A, at a command prompt type "comclust" (without the quotation marks) and press ENTER. After Comclust completes, you are returned to the DOS prompt. 8. Close the DOS prompt and go to node B. 9. At a command prompt, type "comclust" (without the quotation marks), and then press ENTER. After Comclust completes, you are returned to the DOS prompt. 10. Close the command prompt and return to node A. 11. Open the Cluster Administrator and select the cluster group. 12. Take the MSDTC instance offline. 13. Double-click the MSDTC instance to open the Properties sheet, click the Dependencies tab, and then click Modify. 14. In the Modify Dependencies dialog box, remove the cluster network name and add the network name that you created in step 3, and then click OK. In the MSDTC Properties dialog box, click OK. 15. Right-click the MSDTC instance, click Change Group, and then select your Web or SQL group. A warning dialog box appears. Click Yes to dismiss the warning, then click Yes in the Move Resource dialog box. 16. Select the disk group that you moved the quorum disk to in step 4. 17. Right-click the quorum disk resource, click Change Group, and then select Cluster Group. A warning dialog box appears. Click Yes to dismiss the warning, then click Yes in the Move Resource dialog box. 18. Select your Web or SQL group. 19. Bring the MSDTC instance online. Página 11 de 31 Nombre Descripción 20. Move your Web or SQL group to node B and back to test whether MSDTC is configured correctly on both nodes. You should have an active MSDTC instance in your Web or SQL group. Additional query words: iis 5 iis5 fail failure =================================================== =================== Keywords : Technology : kbwin2000AdvServ kbwin2000AdvServSearch kbwin2000Search kbSQLServSearch kbiisSearch kbAudDeveloper kbClustServSearch kbiis500 kbSQLServ700 kbSQLServ2000Search kbWinAdvServSearch kbWin2000AdvServSP2 kbWin2000AdvServSP1 Version : :2000,2000 SP1,2000 SP2,5.0,7.0 Issue type : kbinfo =================================================== ============================= Cambio del juego d caracteres en una bd de Matar procesos 1. Backup a la BD a cambiar el juego de caracteres 2. Crear BD nueva con el juego de caracteres deseado 3. Generar un script completo de la BD a cambiar 4. Reemplazar en dicho script, en los campos alfanuméricos, el juego de caracteres antiguo por el nuevo 5. Crear en la BD nueva sólo las tablas (no incluir PK, FK, índices...) 6. Restringir el acceso a la BD a cambiar el juego de caracteres 7. Transferir SÓLO los datos de la BD a cambiar a la BD nueva 8. Ejecutar en la BD nueva, el resto del script de creación de objetos de la BD 9. Borrar BD antigua 10. Renombrar la BD nueva con el nombre de la antigua declare @spid smallint declare @comando varchar(100) select @comando = 'kill ' set nocount on -- La consulta que rellena el cursor habra que personalizarla -- para cada caso al igual que la declaracion de variables y -- el comando a realizar declare terminator cursor for select spid from sysprocesses where hostname = 'ssmaaomc' and login_time < convert(datetime,'02-20-2001') and last_batch < convert(datetime, '02-20-2001') order by 1 open terminator fetch next from terminator into @spid while (@@fetch_status -1) begin select 'Matando proceso spid=' + convert(varchar, @spid) select @comando = @comando + convert(varchar,@spid) exec (@comando) select @comando = 'kill ' fetch next from terminator into @spid end close terminator deallocate terminator set nocount off Página 12 de 31 Nombre Desactivar constraints y triggers rápidamente Descripción When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you know your data is fine but the constraints are too prohibitive for a mass initial load. Here's a technique I use to disable the constraints and triggers temporarily during a mass load. To do this, we can use the sp_msforeachtable system stored procedure to loop through each table and issue an ALTER TABLE statement to dsable the constraint and trigger. The ALL clause at the end of the command will disable all constraints and triggers. There is no need to drop referential integrity to load data. If you turn on NOCHECK, constraints will not be checked. Here's the code to perform this: sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all" To enable them back, you can reverse the function. Let's get a little more creative this time. We'll explicitly set the parameters and list each table before enabling them so we can verify any errors easily. sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all" sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all" As I mentioned before, you never want to drop constraints during development. Instead disable them temporarily until EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW' select name,user_name(sid) from master..sysdatabases Cambiar el propietario de un objeto de una base de Para ver los propietarios de las bases de datos Página 13 de 31 Nombre Procedimiento almacenado para desconectar Descripción CREATE PROCEDURA USP_KILLUSERS @dbname varchar(50) as SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '----------------' CREATE table #tmpUsers( spid int, eid int, status varchar(30), loginname varchar(50), hostname varchar(50), blk int, dbname varchar(30)) INSET INTO #tmpUsers EXEC sp_who DECLARE LoginCursos CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @|dbname2 varchar(40) OPEN LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status -1) BEGIN IF (@@Fetch_status -2) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers PRINT 'Done' go (el procedimiento se puede descargar desde http://www.admin911.com en el enlace "Book Download para Admin911:SQLServer 2000) Para ejecutarlo: USP_KILLUSERS 'Northwind' Página 14 de 31 Nombre Tipos de datos Descripción - BigInt: Puede almacenar números entre 9.223.372.036.854.755.807 y 9.223.372.036.854.755.807. Este tipo usa 8 bytes de almacenamiento. - Int: Puede almacenar números entre -2.147.843.648 y 2.147.843.647. Este tipu usa 4 bytes de almacenamiento. - SmallInt: Puede almacenar números entre -32.768 y 32.767. Este tipu usa 2 bytes de almacenamiento. - TinyInt: Puede almacenar números entre 0 y 255. Este tipu usa 1 byte de almacenamiento. - Varchar(n) - Char(n) La diferencia entre varchar y char, es que varchar trunca los espacios en blanco, así que un varchar(10) almacena "Pepe" en 4 bytes. Sin embargo, el beneficio que obtiene en espacio de almacenamiento, por lo general ecede en unos 8 bytes el acierto en el rendimiento. En otras palabras, para un campo de caracteres menor de 8, use un campo char; para un campo de caracteres superior a 8, use un varchar. - Nvarchar: Se usa para almacenar datos Unicode. Ocupan 2 veces lo que un char y se ejecutan más lentamente. - Text o image. Puenen almacenar hasta 2 gigabytes de datos. Una tabla no puede tener másd de 8060 caracteres de ancho. El único método para solucionar esto es dividir su tabla en Los datos text, ntext e image por lo general no están almacenados directamente en la página principal de la fila de su tabla. En lugar de eso, la columna señala un lugar aparte dentro de la base de datos donde se encuentra el campo más grande. El apuntador que dirige la solicitud a las otras páginas es de 16 bytes. Puesto que este tipo de datos no es almacenado directamente en la fila, no te restringe el límite de ancho de la fila a 8Kb (8060 caracteres) SELECT Max (Len (CompanyName)) MaximunLength from Customers Genera el siguiente resultado: MaximunLength ------------36 Longitud máxima de las tablas Campos text, ntext e image Para conocer el campo más largo de un campo Verificar la existencia de registros duplicados Una manera es esta: SELECT FROM GROUP BY HAVING count(*) > 1 Ejemplo: SELECT Headline, Lead From Articles GROUP BY Headline, Lead HAVING count(*) > 1 Esto devuelve todos los registros duplicados de su tabla, y devuelve cada registro duplicado sólo una vez. También puede calcular cuántas vces se ha repetido la fila,, con la siguiente sintaxis: SELECT Headline, Lead, count (*) as Occurrences From Articles GROUP BY Headline, Lead HAVING count(*) > 1 Si desea calcular cuáles registros están repetidos más de una vez, incremente el número 1 al número de ocurrencias que sea adecuado. Página 15 de 31 Nombre Saber qué parámetros acepta un procedimiento Liberar procedimientos almacenados de la caché Descripción sp_help Se puede hacer de tres formas: 1. DBCC FREEPROCACHE: Vacía la caché de procedimientos. 2. DECLARE @databaseid int SET @databaseid = DB_ID('Northwind') DBCC FLUSHPROCINDB (@databaseid) 3. DBCC DROPCLEANBUFFERS: Desocupará su caché de CREATE table @tmpUsers ( spid int, eid int, status varchar(30), loginname varchar(50), hotname varchar(50), blk int, dbname varchar(50), cmd varchar(30)) INSET INTO #tmpUsers EXEC sp_who El signo número (#) antes del nombre de la tabla significa que es una tabla temporal local y por lo tanto sólo es visible desde la sesión que la creó. Un dobre signo número antes del nombre de la tabla significa que eusted está usando una tabla temporal global. Las tablas temporales globales son accesibles desde cualquier sesión. Las tablas temporales usan la base de datos tempdb para conservar sus datos, lo cual permite un acceso más rápido a ellos.. Para evitar que las tablas locaqles de sesión se superpongan unas a otas, internamente se agrega un sufijo numérico al final del nombre. No es necesario conocer el nombre completo de las tablas en sus consultas. Una tabla temporal local se mantiene con vida mientras la conexión que la creó esté en su ámbito, y es elimitana automáticamente cuando ocurre cualquiera de las siguientes acciones: - El procedimiento almacenado se commpleta. - La sesión que creó la tabla se cierra - Una instrucción de elminar es emitida manualmente. Las tablas temporales globales se pueden ver desde cualquier sesión de la base dedatos. Se mantienen vivas mientras la sesión que las creó siga con vida y no haya otras sesiones usando las tablas. Uso de tablas temporales Tipos de datos table para tablas temporales Si usas el tipo de datos "table", las tablas no se almacenan en tempdb, sino en una variable. Ejemplo: SET NOCOUNT ON DECLARE @tmpdata table ( categoryid int, categorynm varchar(50)) INSET INTO @tmpdata SELECT CategoryID, CategoryName FROM Categories SELECT * FROM @tmpdata Ejemplo con la tempdb: USE master go ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'f:\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'f:\templog.ldf') go Cambiar una base de datos de disco Página 16 de 31 Nombre Cambiar el nivel de compatibilidad de una BBDD Descripción Hay veces que un comando no funciona porque el nivel de compatibilidad de la base de datos está configurado por debajo de 70. Por ejemplo, si ejecutas la siguiente consulta: SELECT TOP 10 * FROM Orders y recibes el siguiente mensaje de error: Servidor: mensaje 170, nivel 15, estado 1, línea 1 Línea 1: sintaxis incorrecta cerca de '10'. Para ver el nivel de compatibilidad: SP_DBCMPTLEVEL 'Northwind' Para cambiarlo puedes utilizar: SP_DBCMPTLEVEL 'Northwind', '80' El número representa la versión: 65 --> Sql Server 6.5 70 --> Sql Server 7.0 80 --> Sql Server 2000 Una vista no se puede ordenar de forma predeterminada. Por ejemplo: CREATE View vw_ProductCategories as SELECT dbo.Products.ProductName, dbo.Categories.CategoryName, dbo.Products.ProductID FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID =dbo.Products.CategoryID ORDER BY dbo.Categories.CategoryName (este código da error). La solución es usar la cláusula TOP, seleccionando TOP 100 PERCENT. Por ejemplo: CREATE View vw_ProductCategories as SELECT TOP 100 PERCENT dbo.Products.ProductName, dbo.Categories.CategoryName, dbo.Products.ProductID FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID =dbo.Products.CategoryID ORDER BY dbo.Categories.CategoryName - www.admin911.com :web del libro de SOS SQL - www.sqlservercentral.com :web del autor de libro - www.sqlpass.org :grupo de usuarios de SQL Serer - www.swynk.com :comunidad para administradores y desarrolladores de la serie de productos Microsoft - www.sqlwire.com :portal de conocimientos sobre contenido de SQL Server - www.sql-server-performance.com :cantidades de artículos sobre optimización del rendimiento de SQL Server. - support.microsoft.com/support/tshoot/default.asp :un asistente que te ayuda a diagnosticar tu problema, paso a paso - search.support.microsoft.com/kb/c.asp :ofrece una base de datos para búsqueda de problemas y soluciones en toda la serie de productos Microsoft - support.microsoft.com/directory/faqs.asp :lista actualizada de problemas comunes y artículos de soporte. - msdn.microsoft.com/sqlserver :artículos y charlas mensuales sobre problemas de desarrollo de SQL Server. - www.microsoft.com/technet/sql :microsoft technet Sql Server Technology Center - www.microsoft.com/sql :SQL Server Official Homepage Ordenar vistas Recursos web para la solución de problemas Página 17 de 31 Nombre Crear un dispositivo de Backup "virtual" Descripción Se usa el procedimiento almacenado sp_addumpdevice. Por ejemplo: EXEC sp_addumpdevide 'disk', 'NorthwindDevice', 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\NorthwindBakDevice.dat' Para hacer la copia de seguridad en el nuevo dispositivo, en lugar de usar la opción DISK, usaríamos: BACKUP DATABASE Northwind TO NorthwindDevice Uso de sp_change_users_login Este procedimiento almacenado hace que los usurarios vuelvan a corresponder con los inicios de sesión apropiados. Para determinar cuáles inicios de sesión no corresponden, puede ejecutar el procedimiento almacenado con el parámetro 'report': sp_change_users_login 'report' Corrija los nombres de los usuarios con el parámetro 'Auto_Fix': sp_change_users_login 'auto_fix', Pepe También creará los inicios de sesión en el sistema secundario si aún no existen. Es mejor tener a los usuarios ya creados. Otra manera de usar el procedimiento almacenado es con 'update_one'. Con este parámetro usted designa explícitamente a cuál usuario en la base de datos restaurada sp_detach_db 'Admin911', 'false' Primero hay que echar a todos los usuarios. Por ejemplo con usp_killusers Para detachar (separar) una base de datos Página 18 de 31 Nombre Tablas del sistema (en la Master) Descripción - sysaltfiles: contiene una lista de los archivos que hay en el servidor - syscacheobjects: muestra cómo SQL Server está usando su memoria caché - syscharsets: designa qué conjuntos de caracteres y órdenes de clasificación están disponibles en SQL Server. - sysconfigures: contiene una entrada por cada confunto de opciones de configuración en SQL Server. Estas opciones son leídas por el procedimiento almacenado sp_configure. Una vez que cambie la configuración en el servidor, esta tabla es ajustada y no es transferida a la tabla syscurconfigs hasta que se reinicie el servidor o se ejecute el comando RECONFIGURE. - syscurconfigs: es idéntica a la tabla sysconfigures. La única diferencia es que la tabla syscorconfigs muestra qué configuraciones está ejecutando actualemente el servidor , mientras la tabla sysconfigures muestra para qué ha sido configurado el servidor una vez que el servidor es reiniciado o cuando el comando RECONFIGURE es emitido. Sea que deba reiniciar o emitir el commando RECONFIGURE, hágalo basado en el indicador de estado - sysdatabases: contiene una lista de las bases de datos instaladas en su SQL Server. - syslanguages: contiene una lista de idiomas instalados en el SQL Server. - syslockinfo: le permite dar un vistazo a los bloqueos otorgador por el administrador de bloqueo del SQL Server. Contiene información acerca de todos los bloqueos otorgados, en espera y de conversión. - syslogins: contiene una lista de los inicios de sesión asignados al servidor. - sysmessages: contiene la lista de los mensajes de error e informativos disponibles en SQL Server. A través de sp_addmessage puede agrefgar un mensaje nuevo en esta tabla. - sysoledbusers: contiene un registro por cada usuario, utilizado por los servidores vinculados. - sysperfinfo: contiene una lista de contadores de rendimiento, utilizada para monitorear SQL Server en el Monitor del sistema. - sysprocesses: contiene una lista de losp rocesos del sistema y de los usuarios que están ejecutándose actualmente en elservidor. Usted también puede ver en esta tabla cuántos recursos de sus sitema están siendo ocupados por el proceso individual. - sysremotelogins: contiene información acerca de los ussuarios a los que se permite ejecutar procedimientos almacenados remotos. - sysservers: contine un registro para el servidor local así como para cualquier servidor o servidores vinculados que estén prarticipando en la duplicación. La mayoría de las Página 19 de 31 Nombre Tablas del sistema en todas las bases de datos Descripción - syscolumns: en esta tabla se genera una entrada para cada columna de la base de datos. Estas entradas de hecho incluyen columnas de las tablas del sistema. - syscomments: es una tabla importante que contiene todos los procedimientos almacenados, vistas, reglas, desencadenadores, restricciones DEFAULT y restricciones CHECK para una base de datos dada. Es importante no editar nunca esta tabla directamente porque depende mucho de la secuencia. Si usted elimina una entrada, todas las demás que están vinculadas al procedimieno almacenado eliminado dejarán de funcionar. - sysconstraints: contiene una lista de las restricciones en una base de datos. - sysfilegroups: incluye una tabla para cada grupo e archivos usado por su base de datos. - sysfiles: contiene un registro por cada archivo de daos y registros usado en una base de datos dada. - sysforeignkeys: contiene informaicón para cualquier clave foránea que pueda usar una tabla. - sysfulltextcatalogs: enumera todosl os catálogos de texto completo en una base de datos dada. - sysindexes: contiene losíndices instalados en una tabla dada. - sysindexkeys: estipula qué índices están siendo utilizados y por cuáles tablas. - sysmembers: asigna los nombres de usuario a la función de la base de datos - sysobjects: es una de las más importantes del catálogo. Contiene un registro por cada objeto de la base de datos. - syspermissions: proporciona una matriz de permisos asignados a los usuarios, grupos o funciones de una base de datos dada. En la tabla sysprotects hay más información acerca de los permisos. - sysprotects: se extiende sobre la tabla syspermissions y especifica qué tipo de permisos tiene el usuario. - sysreferences: le muestra las relaciones entre las tablas. - systypes: contiene una lista de los tipos de datos, definidos por sistema y por usuario. - sysusers: contiene una lista de usuarios y funciones en Página 20 de 31 Nombre Procedimientos almacenados extendidos Descripción - xp_availablemedia: enumera los discos disponibles para leer y escribir datos. - xp_cmdshell: Es de uso común para la ejecución de programas con T-SQL. Si no desea que el procedimiento genere ninguna salida, use el parámetro no_output - xp_dirtree: Reporta todos los subdirectorios bajo el directorio raíz especificado - xp_enum_activescriptengines: Enumera todos los lenguajes de secuencia de comandos instalados en el servidor (por ejemplo: XML, vbscript Jscrip, etc). - xp_enum_oldb_providers: Genera todos los proveedores OLE DB instalados en el servidor. - xp_enumcodepages: Enumera todas las páginas de códigos instaladas en el servidor. - xp_enumdsn: Genera una lista de los DSN (Data Source Name, nombre de origen de datos) configurados en el servidor. - xp_enumerrorlogs: Genera una lista de los registros de errores de SQL Server en el servidor, junto con sus tamaños y fechas de creación. - xp_enumgroups: eumera todos los grupos Windows que hay en el servidor. - xp_fileexist: determina si un archivo específico existe en el directorio. También le puede decir si la variable especificada es un directorio. Puede usar esto para desencadenar ciertos suscesos. Por ejemplo, puede usar este procedimiento para probar si existe un archivo antes de lanzar un paquete DTS. - xp_fixeddrives: Muestra los discos fijos que hay en el servidor así como el espacio en magabytes disponible para cada uno. - xp_getfiledetails: Muestra información acerca del archivo especificado, incluyendo la fecha de creación y el tamaño. - xp_getnetname: Muestra el nombre del equipo SQL Server al que está conectado - xp_loginconfig: Enumera la configuración de seguridad e inicio de sesión para SQL Server. - xp_logevent: Registra sucesos en el Visualizador de sucesos desde T-SQL. Para usar el procedimiento, usted debe proporcionar un número de error, seguido por el mensaje de error, y finalmente la gravedad. Las gravedades disponibles son INFORMATIONAL, WARNING, o ERROR. Los números de error definidos por usuario empiezan en 50.001. - xp_logininfo: Produce información valiosa acerca de cuáles usuarios de Windows tienen derecho a su SQL Serer y los tipos de permisos con que cuentan. - xp_ntsec_enumdomains: Indica el dominio del cual es miembro su servidor Windows. - xp_readerrorlog: Devuelve el contenido del registro de errores actual. - xp_regdeletekey: Le permite eliminar una clave desde su registro. Sea muy cuidadoso al utilizarlo pues no le advertirá antes de eliminar la clae completa. Para ejecutar el procedimiento, use el parámetro @rootkey par aespecificar la clave del registro raíz, y luego el parámegro @key para designar la clave que desea eliminar. - xp_regdeletevalue: También puede eliminar entradas de datos individuales dentro de una clave de registro. - xp_regenumvalues: enumera todas lsa entradas de datos del registro y los valores en una clave dada. - xp_regread: Lee una clave de registro individual para determinar su existencia o leer una entrada de datos en la Reducción del registro de transacciones en todas las bases de datos de un servidor: exec master..sp_msforeachdb 'use "?";dbcc shrinkfile (2,truncateonly)' Reducción del registro de transacciones en todas Página 21 de 31 Nombre Cambiar el registro de transacciones de unidad Descripción This tip is one of a series from SQL Server Magazine. For additional tips, visit the SQL Server Tips and Tricks Center. Q. How can I move a database's transaction log to a different drive? A. You can move the transaction log by using SQL Server stored procedures for detaching and attaching the database. You need to detach the database, move the log file, then reattach the database. For example, to move the pubs database from drive C to drive D, first use the following sp_detach_db command to detach the database: EXEC sp_detach_db 'pubs' Next, copy the pubs.mdf and pubs_log.ldf files to the destination drive. The following example shows how to copy these two files to the d:\mssql7\data directory: copy c:\mssql7\data\pubs.mdf d:\mssql7\datacopy c:\mssql7\data\pubs_log.ldf d:\mssql7\data Finally, after you copy the files, you can use the sp_ attach_db stored procedure to reattach the data to SQL Server: EXEC sp_attach_db 'pubs', 'd:\mssql7\data\pubs.mdf', 'd:\mssql7\data\pubs_log.ldf' Página 22 de 31 Nombre Procedimiento almacenado para enviar mail por Descripción if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_SQLSMTPMail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_SQLSMTPMail] GO SET QUOTED_IDENTIFIER OFF GO Create Procedure dbo.sp_SQLSMTPMail @vcTo varchar(2048) = null, @vcBody varchar(8000) = '', @vcSubject varchar(255) = null, @vcAttachments varchar(1024) = null, @vcQuery varchar(8000) = null, @vcFrom varchar(128) = null, @vcCC varchar(2048) = '', @vcBCC varchar(2048) = '', @vcSMTPServer varchar(255) = 'relay.tvguideinc.com', -- put local network smtp server name here @cSendUsing char(1) = '2', @vcPort varchar(3) = '25', @cAuthenticate char(1) = '0', @vcDSNOptions varchar(2) = '0', @vcTimeout varchar(2) = '30', @vcSenderName varchar(128) = null, @vcServerName sysname = null As /*************************************************************** ****/ --Name : sp_SQLSMTPMail --Server : Generic --Description : SQL smtp e-mail using CDOSYS, OLE Automation and a -network smtp server; For SQL Servers running on -windows 2000. ---Note : Be sure to set the default for @vcSMTPServer above to -the company network smtp server or you will have to -pass it in each time. ---Comments : Getting the network SMTP configured to work properly -may require engaging your company network or -server. -server people who deal with the netowrk SMTP Some errors that the stored proc returns relate to SET ANSI_NULLS ON -incorrect permissions for the various SQL Servers to -use the SMTP relay server to bouce out going mail. -Without proper permissions the SQL server appears as -a spammer to the local SMTP network server. ---Parameters : See the 'Syntax' Print statements below or call the -sp with '?' as the first input. ---Date : 08/22/2001 --Author : Clinton Herring Página 23 de 31 Nombre Descripción ---History : /*************************************************************** ****/ Set nocount on -- Determine if the user requested syntax. If @vcTo = '?' Begin Print 'Syntax for sp_SQLSMTPMail (based on CDOSYS):' Print 'Exec master.dbo.sp_SQLSMTPMail' Print ' @vcTo (varchar(2048)) - Recipient email address list separating each with a '';'' ' Print ' or a '',''. Use a ''?'' to return the syntax.' Print ' @vcBody (varchar(8000)) - Text body; use embedded char(13) + char(10)' Print ' for carriage returns. The default is nothing' Print ' @vcSubject (varchar(255))) - E-mail subject. The default is a message from' Print ' @@servername.' Print ' @vcAttachments (varchar(1024)) - Attachment list separating each with a '';''.' Print ' The default is no attachments.' Print ' @vcQuery (varchar(8000)) - In-line query or a query file path; do not ' Print ' use double quotes within the query.' Print ' @vcFrom (varchar(128)) - Sender list defaulted to @@ServerName.' Print ' @vcCC (varchar(2048)) - CC list separating each with a '';'' or a '',''' Print ' The default is no CC addresses.' Print ' @vcBCC (varchar(2048)) - Blind CC list separating each with a '';'' or a '',''' Print ' The default is no BCC addresses.' Print ' @vcSMTPServer (varchar(255)) - Network smtp server defaulted to your companies network' Print ' smtp server. Set this in the stored proc code.' Print ' @cSendUsing (char(1)) - Specifies the smpt server method, local or network. The' Print ' default is network, a value of ''2''.' Print ' @vcPort (varchar(3)) - The smtp server communication port defaulted to ''25''.' Print ' @cAuthenticate (char(1)) - The smtp server authentication method defaulted to ' Print ' anonymous, a value of ''0''.' Print ' @vcDSNOptions (varchar(2)) - The smtp server delivery status defaulted to none,' Print ' a value of ''0''.' Print ' @vcTimeout (varchar(2)) - The smtp server connection timeout defaulted to 30 seconds.' Print ' @vcSenderName (varchar(128)) - Primary sender name defaulted to @@ServerName.' Print ' @vcServerName (sysname) - SQL Server to which the query is directed defaulted' Print ' to @@ServerName.' Print '' Print '' Print 'Example:' Print 'sp_SQLSMTPMail '''', ''This is a test'', @vcSMTPServer = ' Print '' Print 'The above example will send an smpt e-mail to from @@ServerName' Print 'with a subject of ''Message from SQL Server '' and a' Print 'text body of ''This is a test'' using the network smtp server specified.' Print 'See the MSDN online library, Messaging and Collaboration, at ' Print 'http://www.msdn.microsoft.com/library/ for details about CDOSYS.' Print 'subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>' Print 'Reference>Fields>http://schemas.microsoft.com/cdo/config uration/>smtpserver field' Print '' Print 'Be sure to set the default for @vcSMTPServer before compiling this stored procedure.' Print '' Return End -- Declare variables Declare @iMessageObjId int Declare @iHr int Declare @iRtn int Declare @iFileExists tinyint Declare @vcCmd varchar(255) Declare @vcQueryOutPath varchar(50) Declare @dtDatetime datetime Declare @vcErrMssg varchar(255) Declare @vcAttachment varchar(1024) Declare @iPos int Declare @vcErrSource varchar(255) Declare @vcErrDescription varchar(255) -- Set local variables. Set @dtDatetime = getdate() Set @iHr = 0 -- Check for minimum parameters. If @vcTo is null Begin Set @vcErrMssg = 'You must supply at least 1 recipient.' Goto ErrMssg End -- CDOSYS uses commas to separate recipients. Allow users to use -- either a comma or a semi-colon by replacing semi-colons in the -- To, CCs and BCCs. Select @vcTo = Replace(@vcTo, ';', ',') Select @vcCC = Replace(@vcCC, ';', ',') Select @vcBCC = Replace(@vcBCC, ';', ',') -- Set the default SQL Server to the local SQL Server if one -- is not provided to accommodate instances in SQL 2000. If @vcServerName is null Set @vcServerName = @@servername -- Set a default "subject" if one is not provided. If @vcSubject is null Set @vcSubject = 'Message from SQL Server ' + @vcServerName Página 25 de 31 Nombre Descripción -- Set a default "from" if one is not provided. If @vcFrom is null Set @vcFrom = 'SQL-' + Replace(@vcServerName,'\','_') -- Set a default "sender name" if one is not provided. If @vcSenderName is null Set @vcSenderName = 'SQL-' + Replace(@vcServerName,'\','_') -- Create the SMTP message object. EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT IF @iHr 0 Begin Set @vcErrMssg = 'Error creating object CDO.Message.' Goto ErrMssg End -- Set SMTP message object parameters. -- To EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "To".' Goto ErrMssg End -- Subject EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "Subject".' Goto ErrMssg End -- From EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "From".' Goto ErrMssg End -- CC EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @vcCC IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "CC".' Goto ErrMssg End -- BCC EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @vcBCC IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "BCC".' Goto ErrMssg End Página 26 de 31 Nombre Descripción -- DSNOptions EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "DSNOptions".' Goto ErrMssg End -- Sender EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "Sender".' Goto ErrMssg End -- Is there a query to run? If @vcQuery is not null and @vcQuery '' Begin -- We have a query result to include; temporarily send the output to the -- drive with the most free space. Use xp_fixeddrives to determine this. -- If a temp table exists with the following name drop it. If (Select object_id('tempdb.dbo.#fixeddrives')) > 0 Exec ('Drop table #fixeddrives') -- Create a temp table to work with xp_fixeddrives. Create table #fixeddrives( Drive char(1) null, FreeSpace varchar(15) null) -- Get the fixeddrive info. Insert into #fixeddrives Exec master.dbo.xp_fixeddrives -- Get the drive letter of the drive with the most free space -- Note: The OSQL output file name must be unique for each call within the same session. -Apparently OSQL does not release its lock on the first file created until the session ends. -Hence this alleviates a problem with queries from multiple calls in a cursor or other loop. Select @vcQueryOutPath = Drive + ':\TempQueryOut' + ltrim(str(datepart(hh,getdate()))) + ltrim(str(datepart(mi,getdate()))) + ltrim(str(datepart(ss,getdate()))) + ltrim(str(datepart(ms,getdate()))) + '.txt' from #fixeddrives where FreeSpace = (select max(FreeSpace) from #fixeddrives ) -- Check for a pattern of '\\*\' or '?:\'. -- If found assume the query is a file path. If Left(@vcQuery, 35) like '\\%\%' or Left(@vcQuery, 5) like '_:\%' Begin Select @vcCmd = 'osql /S' + @vcServerName + ' /E /i' + convert(varchar(1024),@vcQuery) + ' /o' + @vcQueryOutPath + ' -n -w5000 ' Página 27 de 31 Nombre Descripción End Else Begin Select @vcCmd = 'osql /S' + @vcServerName + ' /E /Q"' + @vcQuery + '" /o' + @vcQueryOutPath + ' -n -w5000 ' End -- Execute the query Exec master.dbo.xp_cmdshell @vcCmd, no_output -- Add the query results as an attachment if the file was successfully created. -- Check to see if the file exists. Use xp_fileexist to determine this. -- If a temp table exists with the following name drop it. If (Select object_id('tempdb.dbo.#fileexists')) > 0 Exec ('Drop table #fileexists') -- Create a temp table to work with xp_fileexist. Create table #fileexists( FileExists tinyint null, FileIsDirectory tinyint null, ParentDirectoryExists tinyint null) -- Execute xp_fileexist Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath -- Now see if we need to add the file as an attachment If (select FileExists from #fileexists) = 1 Begin -- Set a variable for later use to delete the file. Select @iFileExists = 1 -- Add the file path to the attachment variable. If @vcAttachments is null Select @vcAttachments = @vcQueryOutPath Else Select @vcAttachments = @vcAttachments + '; ' + @vcQueryOutPath End End -- Check for multiple attachments separated by a semi-colon ';'. If @vcAttachments is not null Begin If right(@vcAttachments,1) ';' Select @vcAttachments = @vcAttachments + '; ' Select @iPos = CharIndex(';', @vcAttachments, 1) While @iPos > 0 Begin Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1))) Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos) EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @vcAttachment IF @iHr 0 Begin EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) + char(13) + char(10) + 'Error adding attachment: ' + char(13) + char(10) + Página 28 de 31 Nombre Descripción @vcErrSource + char(13) + char(10) + @vcAttachment End Select @iPos = CharIndex(';', @vcAttachments, 1) End End -- TextBody EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @vcBody IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message parameter "TextBody".' Goto ErrMssg End -- Other Message parameters for reference --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False --EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True -- Set SMTP Message configuration property values. -- Network SMTP Server location EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/smtpserver").Value', @vcSMTPServer IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "smtpserver".' Goto ErrMssg End -- Sendusing EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/sendusing").Value', @cSendUsing IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "sendusing".' Goto ErrMssg End -- SMTPConnectionTimeout EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SMTPConnectionTimeout").Value', @vcTimeout IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "SMTPConnectionTimeout".' Goto ErrMssg End -- SMTPServerPort EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SMTPServerPort").Value', @vcPort IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message configuraton Página 29 de 31 Nombre Descripción field "SMTPServerPort".' Goto ErrMssg End -- SMTPAuthenticate EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SMTPAuthenticate").Value', @cAuthenticate IF @iHr 0 Begin Set @vcErrMssg = 'Error setting Message configuraton field "SMTPAuthenticate".' Goto ErrMssg End -- Other Message Configuration fields for reference --EXEC @iHr = sp_OASetProperty @iMessageObjId, -'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SMTPUseSSL").Value',True --EXEC @iHr = sp_OASetProperty @iMessageObjId, -'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/LanguageCode").Value','en' --EXEC @iHr = sp_OASetProperty @iMessageObjId, -'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SendEmailAddress").Value', 'Test User' --EXEC @iHr = sp_OASetProperty @iMessageObjId, -'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SendUserName").Value',null --EXEC @iHr = sp_OASetProperty @iMessageObjId, -'Configuration.Fields("http://schemas.microsoft.com/cdo/con figuration/SendPassword").Value',null -- Update the Message object fields and configuration fields. EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update' IF @iHr 0 Begin Set @vcErrMssg = 'Error updating Message configuration fields.' Goto ErrMssg End EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update' IF @iHr 0 Begin Set @vcErrMssg = 'Error updating Message parameters.' Goto ErrMssg End -- Send the message. EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send' IF @iHr 0 Begin Set @vcErrMssg = 'Error Sending e-mail.' Goto ErrMssg End Else Print 'Mail sent.' Página 30 de 31 Nombre Descripción Cleanup: -- Destroy the object and return. EXEC @iHr = sp_OADestroy @iMessageObjId --EXEC @iHr = sp_OAStop -- Delete the query output file if one exists. If @iFileExists = 1 Begin Select @vcCmd = 'del ' + @vcQueryOutPath Exec master.dbo.xp_cmdshell @vcCmd, no_output End Return Página 31 de 31