El TecnoBaúl de Kiquenet

Kiquenet boring stories

Posts Tagged ‘sqlserver’

Indexing Server and Sql Server

Posted by kiquenet en 21 junio 2016


EXEC sp_addlinkedserver Web,’Index Server’,’MSIDXS’,’CarenetWeb’

where CarenetWeb is a catalog on the local server.

You can’t build a linked server to a remote IS catalog, but you can query it like this

Select * from openquery(LinkedServerName,’Select DocTitle, Vpath, Characterization from RemoteServerName.RemoteCatalogName..Scope()
where FileName like ”%.txt”’)

Using Query analyzer connected to the Sql server I executed a sp_addlinked server:

EXEC sp_addlinkedserver FileSystem, ‘Index Server’, ‘MSIDXS’, ‘CatalogName’

In the Sql Management console, under Security –> Linked Servers –>Properties, I set the Security tab option to "Be made using this security context:" and assigned a domain account that is a member of the local Administrators group on the remote server where the queried catalog resides.

Then constructed a query like this:

SELECT DocTitle, vpath, Filename, Size, Contents, characterization, PATH, URL, A_Href, rank, write
FROM RemoteServer.CatalogName..SCOPE(”" + this.Scope + "”)
Where FREETEXT(Contents,”" SearchPhrase"”)

Which I was able to then execute while connected with a sql account that was a db_owner.

The Sql Server Agent runs under a domain account, which is a member of the remote server’s Administrators group.

1) the IS server is running locally.
2) the catalog is indexing directories, expand the directories folder and verify there are directories there
3) the directories indexed are both real and virtual (the virtual directories have blue globes on them),
4) there are documents in the physical directories.

Also can you try this code agains your remote catalog – make sure you change this entry to reflect your actual remote server name and catalog

@objIxsso, ‘catalog’, ‘query://remoteservername.remotecatalogname’

create procedure sp_indexServer @input char(255)
declare @HResult int
declare @objIxsso int
declare @RecordSet int
declare @recordCount int
declare @Count int
declare @fields int
declare @path varchar(100)
declare @vpath varchar(100)
declare @doctitle varchar(100)
declare @size varchar(100)
declare @filename varchar(100)
declare @write varchar(100)
declare @rank varchar(100)
declare @hitcount varchar(100)

EXEC @HResult = sp_OACreate ‘Ixsso.query’, @objIxsso Out
EXEC @HResult = sp_OASetProperty @objIxsso, ‘Query’, @input
EXEC @HResult = sp_OASetProperty
@objIxsso, ‘catalog’, ‘query://remoteservername.remotecatalogname’
EXEC @HResult = sp_OASetProperty @objIxsso, ‘Columns’, _
‘path, vpath, doctitle,size,filename,write,rank,hitcount’
EXEC @HResult = sp_OASetProperty
@objIxsso, ‘MaxRecords’, ‘200’
EXEC @HResult = sp_OASetProperty @objIxsso, ‘SortBy’, ‘rank
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso
exec @HResult =sp_OAMethod @objIxsso,_
‘CreateRecordSet("nonsequential")’,@recordSet OUTPUT
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso
EXEC @HResult = sp_OAGetProperty
@recordSet, ‘RecordCount’,_
@RecordCount output
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @recordset
if @recordcount =0
print ‘No matches found’
print convert (varchar(5),@recordcount) +’ matches found’
SET @Count = 1
WHILE ( @Count <= @RecordCount )
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,0
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@path output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,1
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@vpath output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,2
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@doctitle output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,3
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@size output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’, _
@fields out,4
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, _
@filename output
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,5
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, @write
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,6
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’, @rank
EXEC @HResult = sp_OAGetProperty @RecordSet, ‘fields’,
@fields out,7
EXEC @HResult = sp_OAGetProperty @fields, ‘Value’,
@hitcount output
print @path +’, ‘ +@vpath+’, ‘+@doctitle+’, ‘+@size +’, ‘+_
@filename +’, ‘+@write+’, ‘+@rank+’, ‘+@hitcount
EXEC @HResult =sp_OAMethod @RecordSet, ‘MoveNext’
select @count=@Count+1
print @count
EXEC @HResult = sp_OADestroy @fields
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @fields
EXEC @HResult = sp_OADestroy @recordset
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @recordset
EXEC @HResult = sp_OADestroy @objIxsso
IF @HResult <> 0
EXEC sp_OAGetErrorInfo @objIxsso

Posted in .NET, SQLServer | Etiquetado: , , | Leave a Comment »

Inserciones con autonuméricos y batch

Posted by kiquenet en 20 abril 2016

Tabla temporal



Id int,

Nombre varchar(50)


Inserción Batch con OUTPUT (inserted) INTO

INSERT INTO Personas(Nombre)

OUTPUT inserted.Id, inserted.Nombre

INTO @Temp


INSERT INTO Mascotas(Nombre, PersonaId)

SELECT 'Mascota de ' + Nombre, Id

FROM @Temp;


Posted in SQLServer | Etiquetado: , , | Leave a Comment »

DateTime .NET and SQL Server

Posted by kiquenet en 19 septiembre 2014

DateTime Extensions




Date Formatting in WPF Datagrid

<tk:DataGridTextColumn Binding="{Binding StartDate, StringFormat=\{0:dd.MM.yy HH:mm:ss\}}" />

If you have some custom format for every culture then you better move that logic to Converter or property getter. Otherwise you can look into some more options with custom date formatting

You can do it by setting the AutoGeneratingColumn handler in XAML:

<DataGrid AutoGeneratingColumn="OnAutoGeneratingColumn"  ..etc.. />

And then in behind code do something like this:

private void OnAutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    if (e.PropertyType == typeof(System.DateTime))
        (e.Column as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";


Try setting/binding this as your Converter:

[ValueConversion(typeof(DateTime), typeof(String))]
public class DateConverter : IValueConverter
    private const string _format = "dd-MM-yy";

    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        DateTime date = (DateTime)value;

        return date.ToString(_format);

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        return DateTime.ParseExact((string) value, _format, culture);


Then set up your XAML like this:

    <wpfDataGridMisc:DateConverter x:Key="dateConverter"/>

<DataGridTextColumn Binding="{Binding Date, Converter={StaticResource dateConverter}}"/

SQL Server Date and Time


Convert Column from Date to Datetime Sql Server


ALTER TABLE Tab ALTER COLUMN LastModified datetime2 not null

(I chose datetime2 over datetime, since the former is recommended for all new development work http://msdn.microsoft.com/en-us/library/ms187819.aspx, and since the column is currently date, I know you’re on SQL Server 2008 or later)

SQL Server Management Studio I get the following error.

Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created.

You can’t change the type of a column in place.  You need to create a new column, copy of the values over, and then drop the original column.

SQL Management Studio usually accomplishes this by creating a temporary table with the new column name, copying the values over, dropping the original table with the old column, and then renaming the new temporary table to the new name.  Often it does this without people even realizing it.

However, this can be a very invasive approach, especially if you already have a lot of rows in the table, so you may want to just write a SQL script add the new column to the table, copy the values over, drop the original column, and then use sp_rename to change the new temporary column name back to the original column name.  This is the same idea as what SQL Management Studio is doing, except they are dropping and recreating the whole table, and you are just dropping and recreating the column.

However, if you DO want to let SQL Manangement Studio do it this way, you can turn off that error message.  I believe it was originally added because people did not wantdrop and recreate the table by default.  To turn this message off, go to *Tools->Options-?Designers*, and uncheck the **"Prevent saving changes that require table re-creation"**, then you should be able to save your changes in the designer.

[Prevent saving changes that require table re-creation]


DECLARE @date1 date= ’11-21-38′;

DECLARE @date2 date= ’12-29-33 23:20:51′; — will take only the date 
SELECT @date1 AS ‘date1’, @date2 AS ‘date2’

DECLARE @smalldatetime1 smalldatetime = ‘1955-12-13 12:43:31’; — will round to next minute

DECLARE @smalldatetime2 smalldatetime = ‘1955-12-13 12:43:29’; — will not round to next minute 
SELECT @smalldatetime1 AS ‘@smalldatetime1’, @smalldatetime2 AS ‘@smalldatetime2’; 

DECLARE @datetime1 datetime = ’12-23-35′;

DECLARE @time1 time(4) = ’11:10:05.1234′;

DECLARE @datetime2 datetime = @time1  
DECLARE @datetime3 datetime = ‘1955-12-13 12:43:31’;

SELECT @datetime1 AS ‘datetime1’, @datetime2 AS ‘datetimevar2’  , @datetime3 AS ‘datetimevar3’ 

DECLARE @datetime2_1 datetime2(4) = ’12-13-25 12:32:10.1234′;

DECLARE @datetime2_2 datetime2 = ’12-13-25 11:32:10.1234567′;

DECLARE @datetime2_3 datetime2 = ’12-13-25′;  
SELECT @datetime2_1 AS ‘datetime2_1’, @datetime2_2 AS ‘datetime2_2’, @datetime2_3 AS ‘datetime2_3’;


Posted in .NET | Etiquetado: , , , , , , | Leave a Comment »

“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 »

Transacciones: Gestión de bloqueos en SQL Server

Posted by kiquenet en 18 septiembre 2010

Se ha comentado el funcionamiento de las transacciones en este gestor de base de datos (SGBD). Sin embargo, para acabar de comprender su funcionamiento debemos conocer cómo se manejan los bloqueos en SQL Server. El correcto funcionamiento y la eficiencia de nuestra aplicación que maneja usuarios concurrentes accediendo a nuestra base de datos dependen de la configuración de SQL Server y de la programación de la aplicación, así que, tanto para una cosa como para la otra, debemos comprender y manejar estos conceptos.

Bloqueos y rendimiento

Los bloqueos son los encargados de hacer que las transacciones tengan la propiedad de aislamiento. La realización de bloqueos en un entorno concurrente proporciona aislamiento en los siguientes casos:

  • Múltiples transacciones intentan modificar la misma fila.
  • Datos no confirmados por una transacción son leídos por otras transacciones. Esto se conoce como lecturas sucias.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a valores diferentes con el mismo conjunto de datos. Conocido como lecturas no repetibles.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a diferentes conjuntos de datos. Es lo que se conoce como “lecturas fantasma”.

El establecimiento de los bloqueos es automático en SQL Server, no teniéndose que preocupar, por tanto, el programador directamente por ellos, aunque debe tener en cuenta qué tipo de bloqueos establece cada operación atendiendo a las cláusulas incluidas, etc., para minimizar los interbloqueos.

Recursos de los bloqueos. Granularidad del bloqueo

El establecimiento de bloqueos es automático en SQL Server. El gestor de bloqueos es el encargado de decidir el tipo y la granularidad del bloqueo.

Los bloqueos se pueden producir tanto a nivel de página de datos como a nivel de página de índice. El gestor de bloqueo puede decidir bloquear los recursos: tabla, extensión, página, fila y rango de clave.

La granularidad del bloqueo incide de forma directa sobre la concurrencia y el rendimiento del sistema. El optimizador de consultas decide la granularidad del bloqueo automáticamente. No obstante, SQL Server proporciona extensiones de T-SQL que proporcionan un mecanismo para controlar la granuralidad de los bloqueos.

Tipos de bloqueo en SQL Server

El procedimiento almacenado “sp_lock” proporciona los bloqueos activos en el sistema gestor de bases de datos. También ofrecen información sobre bloqueos el administrador corporativo, el monitor de rendimiento, el administrador de memoria y el objeto de SQL Server bloqueos.

Dependiendo del tipo de transacción, el gestor de bloqueos establece distintos tipos de bloqueos.

Bloqueos compartidos

Se establecen para operaciones de sólo lectura, por ejemplo “select”.

Varias transacciones pueden mantener varios bloqueos compartidos simultáneamente sobre el mismo recurso ya que dichas transacciones no interfieren entre sí.

Si una transacción intenta modificar un dato de un recurso sobre el que se ha establecido un bloqueo compartido, se bloqueará hasta que todos los bloqueos compartidos se liberen.

Un bloqueo compartido se libera en cuanto se finaliza la operación de lectura de datos, es decir, no permanece a lo largo de la transacción. Existen cláusulas de las que se hablará más adelante para modificar este comportamiento.

Bloqueos de actualización

Los bloqueos de actualización se usan para instrucciones que modifican datos.

Cuando una transacción intenta actualizar una fila primero ha de leerla para asegurarse de que la fila en cuestión es realmente la que se quiere modificar. En este momento establece un bloqueo compartido. Una vez se ha asegurado que es la fila correcta procederá a modificarla, para lo que necesita establecer un bloqueo exclusivo que será liberado al terminar la transacción. Los bloqueos de actualización se emplean como bloqueo intermedio entre los dos anteriores para evitar interbloqueos.

Bloqueos exclusivos

Se concede un bloqueo de este tipo a una transacción cuando ésta está lista para modificar los datos. Un bloqueo exclusivo sobre un recurso asegura que ninguna otra transacción pueda interferir las acciones llevadas a cabo por la primera transacción sobre los recursos bloqueados.

SQL Server libera el bloqueo al finalizar la transacción.

Bloqueos de intención

El bloqueo de intención es un mecanismo que utilizan las transacciones para declarar su intención de obtener un bloqueo compartido, de actualización o exclusivo sobre un recurso.

No establece ningún bloqueo en si, excepto por el hecho de que otra transacción no podrá adquirir un bloqueo de actualización sobre un recurso sobre el que se haya establecido un bloqueo de intención.

Bloqueos de esquema

Se utilizan para mantener la integridad estructural de las tablas SQL Server. A diferencia de otros bloqueos, que proporcionan aislamiento para los datos, los bloqueos de esquema proporcionan aislamiento para el esquema de objetos de la base de datos.

Compatibilidad de bloqueos

En la siguiente lista se muestra la compatibilidad entre los distintos tipos de bloqueo. Para cada bloqueo que puede llegar a estar activo sobre una conexión al sistema gestor de bases de datos SQL Server se lista qué tipos de bloqueo pueden solicitarse concurrentemente (por ejemplo, si existe un bloqueo de modificación de esquema no puede solicitarse ningún otro tipo de bloqueo, tal y como muestra el último elemento de esta lista).

  • De intención compartido: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.
  • Compartido: de intención compartido, compartido, de actualización y de estabilidad de esquema.
  • De actualización: de intención compartido, compartido y de estabilidad de esquema.
  • De intención exclusivo: de intención compartido, de intención exclusivo y de estabilidad de esquema.
  • Compartido de actualización exclusivo: de intención compartido y de estabilidad de esquema.
  • Exclusivo: estabilidad de esquema.
  • Estabilidad de esquema: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo, exclusivo y de estabilidad de esquema.
  • Modificación de esquema: ningún bloqueo es compatible con el bloque de modificación de esquema.
Bloqueos de índices

De forma similar a los bloqueos de páginas de datos, SQL Server gestiona los bloqueos de las páginas de índices internamente.

Con el comando “sp_indexoption” se puede modificar el gestor permitiendo o no bloqueos a nivel de filas.

Se recomienda no realizar modificaciones sobre el comportamiento por defecto del gestor de bloqueos en lo referente a las páginas de índices ya que suele hacer buenas elecciones.


Todas las técnicas comentadas tratan en todo momento de mantener la integridad de la base de datos si no, solucionar el problema de mejorar el acceso concurrente nos crearía un problema más grave aún.

A esto que acabo de decir, tengo que añadir dos matices:
El primero de ellos es que siempre hay que tener presente que las cosas que hagas se pueden hacer bien o mal. Por ejemplo, cambiar los niveles de aislamiento de las transacciones puede provocar el colapso de la base de datos, que tus aplicaciones empiecen a mostrar datos sobre los que no se ha hecho commit (que puede ser un problema o no)… En fin, que hay que saber lo que se está haciendo.
El segundo es, en parte, continuación de lo anterior, y es que cada solución, como todo en este mundo tiene sus cosas buenas y sus cosas malas por lo que se adaptan mejor o peor a un tipo de problema determinado, dependiendo del coste de implementación, de la forma en que resuelven el problema de bloqueo, etc. Por poner un ejemplo, la solución más “sencillita” que sería utilizar un flag con un plan de contingencia, puede dejar registros bloqueados en el caso que comentas, por eso es necesario un plan de contingencia que consiste en una aplicación auxiliar que libere esos registros en caso de que se vaya la luz, etc… Sin embargo, implementarla es muy sencillo y no hay que controlar ningún otro efecto colateral… El bloqueo pesimista utilizando caducidad del bloqueo es, en general, el que mejor se ajusta a todo tipo de problemas pero requiere más implementación…

Como comentaba, todo depende del problema en cuestión.

Del Mister Alcalde

Posted in .NET, DataAccess | Etiquetado: , , | Leave a Comment »