jump to navigation

Ejemplo Update e Insert Masivo September 3, 2012

Posted by juanpablo1manrique in SQL SERVER 2008.
Tags:
add a comment

El códgio dice más que mil palabras

Update dbo.RRHH
  SET JefeDirectoTelefono = R2.Telefono,
  JefeDiretoNombre = R2.Nombres + ‘ ‘ + R2.Apellidos  
  from dbo.RRHH
  INNER JOIN dbo.RRHH R2
  ON dbo.RRHH.JefeDirecto = R2.UserName

–Actualizar datos en tabla de verdad
UPDATE FactBroadBand
SET FactBroadBand.TotalMonth = Temp.TotalMonth
FROM #Carrusel Temp
WHERE   FactBroadBand.SourceKey = Temp.SourceKey AND
FactBroadBand.TimeKey = Temp.TimeKey AND
FactBroadBand.UnitKey = Temp.UnitKey AND
FactBroadBand.BroadBandKey = Temp.BroadBandKey

–Insertar datos en tabla de verdad
INSERT INTO  dbo.FactMonthBalance (TimeKey,BalanceCategoryKey,SourceKey,UnitKey,TotalMonth)
SELECT
Temp.TimeKey,Temp.BalanceCategoryKey,

Temp.SourceKey,Temp.UnitKey,Temp.TotalMonth
FROM #TempFactBalance Temp
LEFT JOIN  FactMonthBalance Fact
ON Fact.TimeKey = Temp.TimeKey
AND  Fact.BalanceCategoryKey = Temp.BalanceCategoryKey
AND  Fact.SourceKey = Temp.SourceKey
AND  Fact.UnitKey = Temp.UnitKey
WHERE Fact.BalanceCategoryKey is null

SQL SERVER 2008 Enterprise VS Standard VS WorkGroup VS Web VS Express August 29, 2012

Posted by juanpablo1manrique in SharePoint, SQL SERVER 2008.
Tags:
3 comments

Recordando a mi buen amigo SQL SERVER 2008 …

SQL SERVER 2008 Enterprise Standard Work Group Web Express
Scalability & Performance
Number of CPUs 8 CPU 4 CPU 2 CPU 4 CPU (Web Workloads Only) 1 CPU
Memory Utilization 2 TB Ram 64 GB Ram 4 GB Ram OS Maximum 1 GB Ram
Database Size 524PB 524PB 524PB 524PB 10GB
x32 Hardware Support Full Full Full Full Full
x64 Hardware Support Full Full Full Full Full
Data Compression Full
Resource Governor Full
IA64 hardware support Full
Table and Index Partitioning Full
Parallel Index Operations Full
Parallel Consistency Checks (DBCC) Full
Scalable Shared Databases Full
Indexed views Full
Enhanced Read-ahead and Scan Full
High Availability (Always On)
Database Mirroring Yes (full) Yes (Single Safety Full Only) Witness only Witness only Witness only
Online System Changes Full Full Full Full Full
Log Shipping Full Full Full Full
Clustering 16-node failover clustering 2-node failover clustering
Automatic Corruption Recovery from Mirror Full
Log Stream Compression Full
Online Indexing Full
Mirrored Backups Full
Resource Governor Full
Backup Compression Full
Hot-add Memory and CPU Support Full
Database Snapshots Full
Fast Recovery Full
Online Page and File Restore Full
Replication Full
Replication
SQL Server Change Tracking Full Full Full Full Full
Merge Replication Full Full Subscriber only ** Subscriber only Subscriber only
Transactional Replication Full Full Subscriber only ** Subscriber only Subscriber only
Snapshot Replication Full Full Subscriber only ** Subscriber only Subscriber only
Heterogeneous Subscribers Full Full
Oracle Publishing Full
Enterprise Security
C2 Complaint Tracing Full full Full Full Full
SQL Audit Full
Transparent Database Encryption Full
Extensible Key Management Full
Enterprise Manageability
Hypervisor Support Full Full Full Full Full
Database Migration Tools Full Full Full Full Full
Policy-Based Management Full Full Full Full Full
SQL Server Management Studio Tools Full Full Full Full Full
* Database Mail Full Full Full Full
Performance Data Collection Full Full Full Full
SQL Server Agent Full Full Full Full
Database Tuning Advisor Full Full Full Full
Plan Guides Full Full Full
SQL Profiler Full Full Full
Standard Performance Reports Full Full
Distributed Partition Views Full
Parallel Index Operations Full
Automatic Query-to-index-View Matching Full
Unlimited Virtualization Full
Data Warehousing
Create Cubes Without a Database Full Full
Auto-generate Staging and Datawarehouse Schema Full Full
Attribute Relationship Designer Full Full
Efficient Aggregation Designers Full Full
Scalable read-only AS Full
Partitioned Cubes Full
Distributed Partitioned Cubes Full
Data Compression Full
Star Join Query Optimizations Full
Change Data Capture (CDC) Full
Integration Services
SQL Server Import and Export Wizard Full Full Partial Partial Partial
Log Providers and Logging Full Full
XML Source Full Full
SSIS Run-time Full Full
Basic Data Profiling Tools Full Full
SSIS Package Designer & Service Full Full
Data Mining Query Transformation Full
Data Mining Model Training Destination Adapter Full
Fuzzy Grouping Transformation Full
Fuzzy Lookup Transformation Full
Term Extraction Transformation Full
Dimension Processing Destination Adapter Full
Partition Processing Destination Adapter Full
Reporting Services
Reporting Services Memory Limits OS Max OS Max 4GB 4GB 4GB
Report Designer Full Full Full Full Full
Report Manager Full Full Full (report manager) (report manager)
Role-based Security Full Full (fixed roles) (fixed roles) (fixed roles)
Basic Data Profiling Tools Full Full Full Full Full
Export to Excel, Word, PDF and Images Full Full Full Full Full
Report Server Application Embedding Full Full Full Full Full
Enhanced Gauges and Charting Full Full Full Full
Custom Authentication Full Full Full Full
Ad-hoc Reporting (Report Builder) Full Full Full
SharePoint Integration Full Full
Email and File Share Delivery Full Full
Report History, Scheduling, Subscriptions and Caching Full Full
Data Source, Delivery and Rendering Extensibility Full Full
Scale-out Operational Report Configuration Full
Data-driven Report Subscriptions Full
Report Scale-out Deployment Full
Infinite Click-through in Ad-hoc Reports Full
Analysis Services
Analysis Services Backup Full Full
Dimension, Attribute Relationship, Aggregate & Cube Design Full Full
Personalization Extensions Full Full
Analysis Services Scalable Shared Databases Full
Account Intelligence Full
Linked Measures and Dimensions Full
Perspectives Full
Semi-additive Measures Full
Writeback Dimensions Full
Partitioned Cubes and Distributed Partitioned Cubes Full
Custom Rollups Full
Data Mining
Excel 2007 and Visio 2007 Add-in Support Full Full
Comprehensive Set of DM Algorithms Full Full
Integrated Data Mining Tools (Wizards, Editors, Model Viewers, Query Builder) Full Full
Parallel Model Processing Full
Cross Validation Full
Support for Plug-in Algorithms Full
Advanced Configuration and Tuning Options for Data Mining Algorithms Full
Support for Pipeline Data Mining and Text Mining with Integration Services Full
Sequence Prediction Full
Programmability
MERGE and Upsert Capabilities Full Full Full Full Full
New Date and Time Data Types Full Full Full Full Full
Internationalization Support Full Full Full Full Full
Full Text Search Full Full Full Full Full
* Common Language Runtime (CLR) Integration Full Full Full Full Full
Native XML Support Full Full Full Full Full
XML Indexing Full Full Full Full Full
FILESTREAM Support Full Full Full Full Full
Entity Framework Support Full Full Full Full Full
Spatial Support Full Full Full Full Full
Development Tools
Microsoft Visual Studio Integration Full Full Full Full Full
Intellisense (Transact-SQL-and MDX) Full Full Full Full Full
MDX Edit, Debug and Design Tools Full Full Full Full Full
Spatial and Location Services
Spatial And Location Indexes
Spatial Indexes Full Full Full Full Full
Geodetic Data Type Full Full Full Full Full
Advanced Spatial Libraries Full Full Full Full Full
Standards-based Spatial Support Full Full Full Full Full

* Available in SQL Server 2008 Express with Advanced Services Download vs. SQL Server 2008 Express Download.
** If an instance of Workgroup is used as a Publisher, it supports a maximum of 25 publications to all merge subscriptions. It supports an unlimited number of subscriptions to snapshot publications.

Base de datos en estado Suspect SQL SERVER 2008 September 5, 2011

Posted by juanpablo1manrique in SQL SERVER, SQL SERVER 2008.
Tags:
6 comments

Que hacer cuando una base de datos de SQL SERVER 2008 reporta que esta en estado SUSPECT, (Sin muchas explicaciones … )

  • Apagar servicios de SQL
  • Sacar copia de los archivos .mdf y ldf
  • Subir los servicios de SQL
  • Borrar la base de datos
  • Crear nueva base de datos completamente nueva pero con el mismo nombre de la anterior
  • Detener servicios de SQL
  • Sobreescribir el archivo .mdf con la versión anterior de la base de datos
  • Correr el comando: ALTER DATABASE SharePoint_Config SET EMERGENCY;
  • Correr el comando: ALTER DATABASE SharePoint_Config SET SINGLE_USER;
  • Correr el comando: DBCC checkdb (‘SharePoint_Config’, repair_allow_data_loss);
  • Correr el comando: ALTER DATABASE SharePoint_Config SET ONLINE;
  • Correr el comando: ALTER DATABASE SharePoint_Config SET MULTI_USER;

Si desean ver la versión del procedimiento para SQL SERVER 2005 pueden ingresar a:

http://www.myitforum.com/articles/18/view.asp?id=7381

Migrar usuarios SQL SERVER September 6, 2010

Posted by juanpablo1manrique in DAtabase, Seguridad, SQL SERVER, SQL SERVER 2008, Uncategorized.
Tags:
add a comment

Para migrar usuarios entre servidores de SQL SERVER se Puede seguir este maravilloso Post que encontre:

http://support.microsoft.com/kb/918992

Hacer backups que incluyan los usuarios de base de datos y recuperarlo en la instancia destino, esto solo aplica de 2005 a 2008.

Crear este SP en el Master:

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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ”
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
        — obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        — obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Luego ejecutar desde el master  el procedimiento EXEC sp_help_revlogin, el resultado de este procedimiento ejecutarlo en la instancia destino. Y Listo !!

 

The log shipping primary database INSNAME\DBNAME has backup threshold of 60 minutes and has not performed a backup log operation for 60459 minutes. Check agent log and logshipping monitor information. July 13, 2010

Posted by juanpablo1manrique in Alto Desempeño, Best Practices, Cluster, SQL SERVER, SQL SERVER 2008.
Tags:
add a comment

Realizando estrategias de Logshipping sobre las bases de datos se registró el siguiente error sobre el log de eventos,

The log shipping primary database INSNAME\DBNAME has backup threshold of 60 minutes and has not performed a backup log operation for 60459 minutes. Check agent log and logshipping monitor information.

La base de datos en cuestión yo la borre a mano, así que se perdió y el Job se siguió ejecutando a una base de datos que no existe. Una manera que encontré para solucionar este problema es acceder a las tablas de sistema y eliminar el registro.

La tabla a borrar es,

select * from msdb.dbo.log_shipping_monitor_primary

Asegúrese de borrar solo los registros correctos o eliminara toda la configuración de logshipping de su servidor.

Lo correcto es que antes de borrar la base de datos se elimine la configuración de logshipping

Saludos

Cargar un Archivo de Excel en SQL Server 2008 x64 March 27, 2010

Posted by juanpablo1manrique in Business Inteligent, Developer, Excel 2007, Excel Services, Office 2010, SQL SERVER 2008.
Tags: ,
30 comments

En estos días he estado desarrollando un procedimiento para cargar un archivo de Excel en SQL, todo estaba funcionando perfecto hasta que toco subir el archivo a un servidor de SQL server x64.

En el ambiente de desarrollo estaba utilizando Microsoft.Jet.OLEDB.4.0 pero al subirlo al servidor aparece un error que dice:

  • The OLE DB provider “Microsoft.Jet.OLEDB.4.0” has not been registered.

Buscando en internet encontré que para el servidor de x64 el nuevo proveedor es Microsoft.ACE.OLEDB.12.0, pero igualmente salió un error que decía:

  • The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

Buscando en Internet encontré que se debían ejecutar las siguientes sentencias:

  • EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
  • EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
  • exec sp_configure ‘Advanced’, 1
  • RECONFIGURE
  • exec sp_configure ‘Ad Hoc Distributed Queries’, 1
  • RECONFIGURE
  • exec sp_configure ‘xp_cmdshell’, 1
  • RECONFIGURE
  • GO
  • sp_configure ‘show advanced options’, 1
  • GO
  • RECONFIGURE WITH OverRide
  • GO
  • sp_configure ‘Ad Hoc Distributed Queries’, 1
  • GO
  • RECONFIGURE WITH OverRide
  • GO

Pero seguía presentándose el error:

  • Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Esto se debe a que Microsoft en el lanzamiento de SQL SERVER 2008 x64 no hizo un proveedor para poder cargar archivos de Excel, el COLMO NO. Menos mal este problema lo tuve hace poco y descubrí que los componentes de conectividad de Office 2010 ya traen este proveedor los cuales se pueden descargar desde:

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

Con lo cual se instala el proveedor y la sentencia:

  • SELECT * INTO SourceDimBalance FROM OPENROWSET(
  • ‘Microsoft.ACE.OLEDB.12.0’,
  • ‘Excel 12.0;Database=c:\TEMP\DimBalance.xlsx;HDR=YES’,
  • ‘SELECT * FROM [Hoja1$]’)

 

Funciona correctamente!!!!

Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Failed to open the explicitly specified database. March 26, 2010

Posted by juanpablo1manrique in DAtabase, event viewer, IT, SQL SERVER, SQL SERVER 2008, Windows 2008.
Tags:
add a comment

A veces por alguna razon todo esta bien y pumm uno no se conecta y sale un error come este en el eventviewer

Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Failed to open the explicitly specified database. [CLIENT: x.x.x.x]

Entonces toca iniciar una sesión por consola, y cambiar el dafault_database

 – Sql autentication

 sqlcmd -S ISQLSHP -d TableroControlDW -U sa -P *******

 – windows autentication

sqlcmd -S CSQLSHP\ISQLSHP -d TableroControlDW

Luego ejecutar

1>ALTER LOGIN sa WITH DEFAULT_DATABASE = availableDB

2>GO

ó

1>ALTER LOGIN [domain\user] WITH DEFAULT_DATABASE = availableDB

 2>GO

Luego Enter Cuando se ejecuta el comando se le cambia la clave a sa Entonces en bueno ejecutar la sentencia

ALTER LOGIN sa WITH PASSWORD = ”

Cannot drop database “CompanyProducts” because it is currently in use March 12, 2010

Posted by juanpablo1manrique in DAtabase, SQL SERVER 2008.
Tags:
1 comment so far

Por lo general cuando se intenta borrar una base de datos en SQL 2008 no se puede porque existen procesos iniciados, en SQL 2008 no es tan facil darle Kill a los procesos que se encuentren iniciados que impiden borrar una base de datos, entonces es necesario crearse su propio SP que mate los procesos por uno, asi se ejecute el DROP DATABASE desde la consola aparece el mismo error. Por lo general uno intentaria buscar el Activity Monitor y matar estos procesos, pero desafortuandamente esto no es posible, desde el activity monitor actual, porque por alguna extraña razon no aparecen todos los procesos, y el campo de la Base de datos en el listado esta vacio.

Cannot drop database because it is currently in use. error 3702

CREATE PROCEDURE dbo.clearDBUsers 
    @dbName SYSNAME
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255)
 
    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE dbid = DB_ID(@dbname)
        AND spid != @@SPID
 
    PRINT ‘Starting to KILL ‘+RTRIM(@cnt)+’ processes.’
    
    WHILE @spid IS NOT NULL
    BEGIN
        PRINT ‘About to KILL ‘+RTRIM(@spid)
 
        SET @sql = ‘KILL ‘+RTRIM(@spid)
        EXEC(@sql)
 
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE dbid = DB_ID(@dbname)
            AND spid != @@SPID
 
        PRINT RTRIM(@cnt)+’ processes remain.’
    END
END
GO

Tomado de http://sqlserver2000.databases.aspfaq.com/how-do-i-drop-a-sql-server-database.html

 

Activity Monitor SQL SERVER 2008 March 12, 2010

Posted by juanpablo1manrique in Activity Monitor, Developer, SQL SERVER, SQL SERVER 2008.
Tags:
add a comment

Para inicar el ACTIVITY MONITOR en SQL SERVER 2008, se da click derecho sobre el SERVER NAME y luego se da click sobre el activity monitor.

Creo que SQL 2008 se olvido de los developers ya que la administración de nosotros es bastante diferente.

Tomado de:

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/288d6f28-5c29-4f2d-8abe-a1ca9a57fc3c