El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for the ‘SQL’ Category

“Consistency validation for SQL Server registry keys” failed. – SQL Server installation Error

Posted by kiquenet en 18 marzo 2014

The below error occurs when we install SQL Server 2012.

Rule “Consistency validation for SQL Server registry keys” failed. The SQL Server registry keys from a prior installation cannot be modified. To continue, see SQL Server Setup documentation about how to fix registry keys.


The issue occur when the installer unable to update the registry entries due to lack of permission. Please follow the below steps to resolved the issue.

1. Go to RUN –> Type regedit.

2. Locate HKEY_LOCAL_MACHINE -> SOFTWARE -> MICROSOFT -> Microsoft SQL Server in the registry

3. Right click and select Permissions…

4. Click on Advanced

5. Under Permission TAB, select “Replace permission entries on all child objects with entries shown here that apply to child objects”.

6. Click OK.

7. Now Re-run the SQL Server installer.


Posted in Soporte, SQL | Etiquetado: , , , | Leave a Comment »

Conexiones Activas en SQL Server

Posted by kiquenet en 12 agosto 2011

Muchas veces necesitamos saber cuántas conexiones están activas en nuestro servidor SQL Server. Para ello podemos utilizar el siguiente script que nos permitirá saber la cantidad de conexiones existentes por cada base de datos y el login utilizada para las mismas.

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame


fuente: mnoguera.com.ar

Posted in SQL, Trucos | Etiquetado: | Leave a Comment »

Depurar consultas Transact SQL

Posted by kiquenet en 18 septiembre 2010

Son muchas las veces que un programador necesita depurar consultas SQL (Transact SQL, T-SQL) desde Microsoft Visual Studio: mediante la inspección rápida de variables, obtenemos el string infinitamente largo que representa nuestra consulta, que habíamos tabulado con mimo en nuestro código, y que ahora aparece en una única línea. Si queremos resolver un fallo en esta consulta, o intentar optimizarla, tenemos que pegarla en el Management Studio de Microsoft SQL Server y volver a perder nuestro tiempo en tabularla para que sea más fácil entenderla.

Con este truco evitaremos malgastar nuestro precioso tiempo: Management Studio de Microsoft SQL Server formateará esta consulta de nuevo por nosotros.

Al capturar la consulta desde la inspección rápida de variables de Microsoft Visual Studio para depurarla, podríamos obtener un string como éste (no es muy largo en este caso, pero nos sirve como ejemplo):

Consulta T-SQL Original

Consulta T-SQL original en una única línea

Ahora, ejecutamos el editor de consultas de Microsoft SQL Server Mangement Studio pinchando en el icono que aparece en la siguiente imagen:

Microsoft SQL Server Mangement Studio - Diseñar consulta

Microsoft SQL Server Mangement Studio – Diseñar consulta

Aparecerá esta ventana que ignoraremos en este caso.

Microsoft SQL Server Mangement Studio - Agregar tabla

Microsoft SQL Server Mangement Studio – Agregar tabla

Pegamos la consulta que habiamos obtenido en la inspección rápida en el recuadro inferior de la pantalla (marcado en rojo en la siguiente imagen) y después quitamos el foco de ese recuadro haciendo clic en el superior, por ejemplo.

Pegamos la consulta T-SQL

Pegamos la consulta T-SQL

En este momento aparecerá en el recuadro superior una vista de diseño de todas las tablas que intervienen en nuestra consulta SQL, y la relación que existe entre ellas, y en la parte inferior podremos ver nuestra consulta SQL formateada automáticamente por Microsoft SQL Server Mangement Studio.

Consulta formateada

Consulta formateada

Ya sólo quedaría pulsar en “Aceptar” de la pantalla anterior para que la consulta nos sea devuelta y podamos ejecutarla.

Consulta formateada

Consulta formateada y lista para ejecutar

¡Y ya está! Esperamos que este pequeño truco para formatear o tabular consultas T-SQL os ayude a mejorar vuestro trabajo diario.

De Mr. Ramos

Posted in SQL | Etiquetado: | Leave a Comment »

osql.exe y encoding UTF8

Posted by kiquenet en 29 julio 2010

OSQL accepts ANSI and Unicode encoded files, BUT I tried opening my file with notepad++ changed the encoding to UTF-8 and I got similar errors.

MS SQL Server’s osql utility (SQL Server 2000) is deprecated.

Mejor usar SQLCMD.

I have been trying to run MS SQL Server’s osql utility (SQL Server 2000) to insert some default data. My SQLServer’s target database collation is Croatian_CI_AS. After some time I’ve realized a few things about the ways in which you can save the input file.

Using Notepad++ as my editor I thought that formatting a file as UTF-8 would cause everything to be wonderful and magically work.

Not so. In fact, osql will not even read the UTF-8 encoded file (this was a real surprise)! That was a weird thing but I kept on getting this error message about first line and some weird character displayed there. Turns out that osql accepts unicode or ANSI encoded files (and there’s no way to control the input file encoding from the command line so we’re stuck with that … which is not that bad after all).
So, no.1: don’t format your input file for osql as UTF-8. That’s a different animal from a unicode encoded file.

Notepad++ offers several formatting options. Turns out that UCS-2 Little Endian is the wizard. For those interested, read on UCS-2 on wikipedia. I just realized it was actually a 16-bit Unicode encoding. Just looking at it in the list of encodings in Notepad++ didn’t ring bells.

I’m not sure why UCS-2 Big Endian encoding does not play nice (I get gibberish for most of my special characters).

Formatting a file as ANSI in Notepad++ and then running through osql was a bit of a surprise that it did not work (special characters like diacritics became gibberish, although osql did execute everything). I even changed my Windows code page to Croatian in hope that it will all somehow sort itself out but it didn’t. From what I can tell,osql will read the ANSI encoded file as probably English Latin encoding and loose the special characters in the process.

So, bottom line, UCS-2 Little Endian is our friend. Use that for encoding files that are to be executed with osql


osql.exe and unicode files – how to save your sql scripts with encoding

osql.exe is a great application for running sql scripts in a batch.  I use a batch file to execute multiple sql scripts that I use to rebuild my current application database from scratch.  When developing a brand new application, deploying a database in this way makes it really easy to recreate the database just like it will be created on Day 1 when you build out the Production environment. This requires scripting out all of your sql objects and then also having a way to execute all of those sql scripts easily.  That is where osql.exe comes in handy.

But osql.exe does have one issue that I ran into this week where it does not like UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded files.  And sometimes you need to include unicode characters in your sql scripts.  At first I thought osql just did not support unicode but that is not the case… it just does not like the UTF-8 or UTF-7 encoding.

Trying to run a UTF-8 (codepage 65001) or UTF-7 (codepage 65000) encoded file with osql.exe will give you errors such as:

Incorrect syntax near ‘+’.
Incorrect syntax near ‘ ‘.
Incorrect syntax near ‘ï’.

Saving the same file with Unicode Encoding (codepage 1200) will work just fine.  Here is how to save sql scripts in Microsoft SQL Server Management Studio with a particular encoding (you can also use this method to see what type of encoding the file is saved in in the first place).  One other thing to note is that Visual Studio has this same type of Save As… functionality.

From the Microsoft SQL Server Management Studio (or Visual Studio) File menu chooseSave [FILENAME] As…


Then when the Save File As dialog appears you will see a little black arrow (inverted triangle) as part of the Save button.


Clicking the just the inverted triangle portion of the button will give you a menu.


Choosing the Save with Encoding… option will then present you with an Advanced Save Options dialog.



Here is where you can specify the encoding to use for the file.  For osql.exe make sure you specify either Western European (Windows) – Codepage 1252 or Unicode – Codepage 1200.  Do not select UTF-8 (codepage 65001) or UTF-7 (codepage 65000) or osql.exe will give errors when trying to parse the file.



Posted in Comandos, SQL | Etiquetado: , , | Leave a Comment »

Copias de Seguridad Sql Server 2005

Posted by kiquenet en 1 abril 2010

Una forma de realizar copias de seguridad de una base de datos de SqlServer 2005 (Express).

Se puede crear un fichero BAT y programarlo diariamente con el programador de tareas de windows.

El bat tiene lo siguiente:

SQLCMD -U sa -P password -S serverSQL1\SQLEXPRESS -d BaseDatosDeNominas -Q "EXEC dbo.EjecutaBackup"

Y el procedimiento almacenado ejecuta la instrucción BACKUP DATABASE …

ALTER PROCEDURE [dbo].[EjecutaBackup]
    Declare @sNomBD varchar(50)
    Declare @sSufijoBD varchar(10)

    –select @sSufijoBD = substring(convert(varchar,getdate(),126),1,10)
    select @sSufijoBD = convert(varchar,getdate(),126)
    set @sNomBD = ‘C:\bck_up\Nominas_’ + @sSufijoBD + ‘.BAK’


    BACKUP DATABASE BaseDatosDeNominas TO DISK = @sNomBD


Posted in SQL | Etiquetado: | Leave a Comment »

Obtener índices de una tabla

Posted by kiquenet en 24 marzo 2010

Podemos utilizar consultas de este tipo:

select * from USER_IND_COLUMNS b

select * from USER_INDEXES a

WHERE a.index_name = b.index_name;

select * from ALL_INDEXES where index_name like ‘INX_SITCORE_%’

Algunos comentarios más:

Also dba_indexes is not the dba’s indexes. It’s a view.
There are three catagories
DBA_INDEXES – shows all the indexes of all schemas
(View Accessable by DBA)

ALL_INDEXES – All indexes of that schema querrying and other schemas (those accesible by him)

USER_INDEXES – All indexes of that schemaquerrying the view.

So there is no redundancy in creating an index with same names by different schema as long as the objects belong to them.

Posted in SQL | Etiquetado: | Leave a Comment »

Multiple SQL inserts: Oracle, Mysql, Mssql

Posted by kiquenet en 27 agosto 2009

Mysql supports this:

INSERT INTO tblName (column1,column2)

Oracle does not, but can be achieved with:
INSERT INTO tblName (column1,column2)
select ‘Value1′,’Fernando’ from dual
union all
select ‘Value2′,’Carlos’ from dual
union all
select ‘Value3′,’Vincent’ from dual

DUAL is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record.

In MS SQL the union all technique also works:

INSERT INTO [TABLE] (column1,column2)
select ‘Value1′,’Fernando’
select ‘Value2′,’Carlos’
select ‘Value3′,’Vincent’

This technique is probably a lot faster than using multiple inserts in coldfusion and certainly helps reduce the round-trips that would need to be done.

Posted in SQL | Etiquetado: | Leave a Comment »

SQL SELECT TOP N equivalent in ORACLE and MySQL

Posted by kiquenet en 26 agosto 2009

SQL SELECT TOP N equivalent in ORACLE and MySQL

SQL Server:

SELECT TOP 10 product, descr, email
FROM products


SELECT product, descr, email
FROM products


SELECT product, descr, email
FROM products

Posted in SQL | Etiquetado: | Leave a Comment »