numeros - ¿Cómo implementarías secuencias en Microsoft SQL Server?
secuencias sql ejemplos (16)
¿Alguien tiene una buena manera de implementar algo así como una secuencia en el servidor SQL?
A veces simplemente no desea utilizar un GUID, además del hecho de que son feos como diablos. ¿Tal vez la secuencia que quieres no es numérica? Además, insertar una fila y luego preguntarle a la DB qué número es, parece tan hambriento.
Como afirma sqljunkiesshare , las secuencias se agregaron a SQL Server 2012. Aquí se explica cómo hacerlo en la GUI. Este es el error de:
CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
- En el Explorador de objetos , expanda la carpeta Programabilidad
- Debajo de la carpeta Programabilidad , haga clic derecho en la carpeta Secuencias como se muestra a continuación:
- Subrayados están los valores que actualizaría para obtener el equivalente de la declaración SQL anterior, sin embargo, consideraría cambiarlos según sus necesidades (ver notas a continuación).
Notas:
El valor de inicio predeterminado, el valor mínimo y el valor máximo se determinaron por el rango del tipo de datos que era un int en este caso. Consulte aquí para obtener más rangos de tipos de datos si desea usar algo que no sea int .
Es muy probable que quieras que tu secuencia comience en 1 y que también quieras que tu valor mínimo sea 1.
Como dijo correctamente , a partir de SQL Server 2012 hay una característica SEQUENCE incorporada.
La pregunta original no aclara, pero supongo que los requisitos para la secuencia son:
- Tiene que proporcionar un conjunto de números crecientes únicos
- Si varios usuarios solicitan el siguiente valor de la secuencia simultáneamente, todos deberían obtener valores diferentes. En otras palabras, la exclusividad de los valores generados está garantizada sin importar qué.
- Debido a la posibilidad de que algunas transacciones se puedan retrotraer, es posible que el resultado final de los números generados tenga vacíos.
Me gustaría comentar la declaración en la pregunta original:
"Además, insertando una fila y luego preguntando al DB qué número parece tan hackish".
Bueno, no hay mucho que podamos hacer al respecto aquí. El DB es un proveedor de los números secuenciales y DB maneja todos estos problemas de concurrencia que usted no puede manejar usted mismo. No veo alternativa a preguntar al DB por el siguiente valor de la secuencia. Tiene que haber una operación atómica "dame el siguiente valor de la secuencia" y solo DB puede proporcionar tal operación atómica . Ningún código de cliente puede garantizar que él sea el único que trabaje con la secuencia.
Para responder a la pregunta en el título "¿Cómo implementarías secuencias?" - Estamos usando 2008, que no tiene la característica SEQUENCE , así que después de leer algo sobre este tema terminé con lo siguiente.
Para cada secuencia que necesito, creo una tabla de ayuda por separado con solo una columna de IDENTITY (de la misma manera que en 2012 crearía un objeto de secuencia separado).
CREATE TABLE [dbo].[SequenceContractNumber]
(
[ContractNumber] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC)
)
Puede especificar el valor inicial y el incremento para él. Luego creo un procedimiento almacenado que devolvería el siguiente valor de la secuencia. El procedimiento inicia una transacción, inserta una fila en la tabla auxiliar, recuerda el valor de identidad generado y revierte la transacción. Por lo tanto, la tabla de ayuda siempre permanece vacía.
CREATE PROCEDURE [dbo].[GetNewContractNumber]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @Result int = 0;
IF @@TRANCOUNT > 0
BEGIN
-- Procedure is called when there is an active transaction.
-- Create a named savepoint
-- to be able to roll back only the work done in the procedure.
SAVE TRANSACTION ProcedureGetNewContractNumber;
END ELSE BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION ProcedureGetNewContractNumber;
END;
INSERT INTO dbo.SequenceContractNumber DEFAULT VALUES;
SET @Result = SCOPE_IDENTITY();
-- Rollback to a named savepoint or named transaction
ROLLBACK TRANSACTION ProcedureGetNewContractNumber;
RETURN @Result;
END
Algunas notas sobre el procedimiento.
En primer lugar, no era obvio cómo insertar una fila en una tabla que tiene una sola columna de identidad. La respuesta es DEFAULT VALUES .
Luego, quería que el procedimiento funcionara correctamente si se llamaba dentro de otra transacción. El ROLLBACK simple ROLLBACK todo si hay transacciones anidadas. En mi caso, necesito retroceder solo INSERT en la tabla auxiliar, así que utilicé SAVE TRANSACTION .
ROLLBACK TRANSACTION sin un savepoint_name o transaction_name retrocede al inicio de la transacción. Al anidar las transacciones, esta misma instrucción retrotrae todas las transacciones internas a la instrucción BEGIN TRANSACTION más externa.
Así es como uso el procedimiento (dentro de otro procedimiento grande que, por ejemplo, crea un nuevo contrato):
DECLARE @VarContractNumber int;
EXEC @VarContractNumber = dbo.GetNewContractNumber;
Todo funciona bien si necesita generar valores de secuencia de uno en uno. En el caso de los contratos, cada contrato se crea individualmente, por lo que este enfoque funciona a la perfección. Puedo estar seguro de que todos los contratos siempre tienen números de contrato únicos.
NB: solo para evitar posibles preguntas. Estos números de contrato son adicionales a la clave de identidad sustituta que tiene mi tabla de contratos. La clave sustituta es la clave interna que se utiliza para la integridad referencial. El número de contrato generado es un número fácil de usar que está impreso en el contrato. Además, la misma tabla de Contratos contiene contratos finales y Propuestas, que pueden convertirse en contratos o pueden permanecer como propuestas para siempre. Tanto las Propuestas como los Contratos tienen datos muy similares, es por eso que se mantienen en la misma tabla. La propuesta puede convertirse en un contrato simplemente cambiando la bandera en una fila. Las propuestas están numeradas usando una secuencia separada de números, para la cual tengo una segunda tabla SequenceProposalNumber y un segundo procedimiento GetNewProposalNumber .
Recientemente, sin embargo, me encontré con un problema. Necesitaba generar valores de secuencia en un lote, en lugar de uno por uno.
Necesito un procedimiento que procese todos los pagos que se recibieron durante un trimestre determinado de una vez. El resultado de tal procesamiento podría ser ~ 20,000 transacciones que quiero registrar en la tabla de Transactions . Tengo un diseño similar aquí. Transactions tabla de Transactions tiene una columna de IDENTITY interna que el usuario final nunca ve y tiene un Número de transacción amigable para el hombre que se imprimiría en la declaración. Entonces, necesito una forma de generar un número dado de valores únicos en un lote.
Esencialmente, utilicé el mismo enfoque, pero hay algunas peculiaridades.
Primero, no hay una forma directa de insertar múltiples filas en una tabla con solo una columna de IDENTITY . Aunque hay una solución alternativa mediante (ab) el uso de MERGE , al final no la utilicé. Decidí que era más fácil agregar una columna de Filler ficticia. Mi tabla de Secuencia siempre va a estar vacía, por lo que la columna adicional realmente no importa.
La tabla de ayuda se ve así:
CREATE TABLE [dbo].[SequenceS2TransactionNumber]
(
[S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
[Filler] [int] NULL,
CONSTRAINT [PK_SequenceS2TransactionNumber]
PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC)
)
El procedimiento se ve así:
-- Description: Returns a list of new unique S2 Transaction numbers of the given size
-- The caller should create a temp table #NewS2TransactionNumbers,
-- which would hold the result
CREATE PROCEDURE [dbo].[GetNewS2TransactionNumbers]
@ParamCount int -- not NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT > 0
BEGIN
-- Procedure is called when there is an active transaction.
-- Create a named savepoint
-- to be able to roll back only the work done in the procedure.
SAVE TRANSACTION ProcedureGetNewS2TransactionNos;
END ELSE BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION ProcedureGetNewS2TransactionNos;
END;
DECLARE @VarNumberCount int;
SET @VarNumberCount =
(
SELECT TOP(1) dbo.Numbers.Number
FROM dbo.Numbers
ORDER BY dbo.Numbers.Number DESC
);
-- table variable is not affected by the ROLLBACK, so use it for temporary storage
DECLARE @TableTransactionNumbers table
(
ID int NOT NULL
);
IF @VarNumberCount >= @ParamCount
BEGIN
-- the Numbers table is large enough to provide the given number of rows
INSERT INTO dbo.SequenceS2TransactionNumber
(Filler)
OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
-- save generated unique numbers into a table variable first
SELECT TOP(@ParamCount) dbo.Numbers.Number
FROM dbo.Numbers
OPTION (MAXDOP 1);
END ELSE BEGIN
-- the Numbers table is not large enough to provide the given number of rows
-- expand the Numbers table by cross joining it with itself
INSERT INTO dbo.SequenceS2TransactionNumber
(Filler)
OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
-- save generated unique numbers into a table variable first
SELECT TOP(@ParamCount) n1.Number
FROM dbo.Numbers AS n1 CROSS JOIN dbo.Numbers AS n2
OPTION (MAXDOP 1);
END;
/*
-- this method can be used if the SequenceS2TransactionNumber
-- had only one identity column
MERGE INTO dbo.SequenceS2TransactionNumber
USING
(
SELECT *
FROM dbo.Numbers
WHERE dbo.Numbers.Number <= @ParamCount
) AS T
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES
OUTPUT inserted.S2TransactionNumber
-- return generated unique numbers directly to the caller
;
*/
-- Rollback to a named savepoint or named transaction
ROLLBACK TRANSACTION ProcedureGetNewS2TransactionNos;
IF object_id(''tempdb..#NewS2TransactionNumbers'') IS NOT NULL
BEGIN
INSERT INTO #NewS2TransactionNumbers (ID)
SELECT TT.ID FROM @TableTransactionNumbers AS TT;
END
END
Y así es como se usa (dentro de un gran procedimiento almacenado que calcula las transacciones):
-- Generate a batch of new unique transaction numbers
-- and store them in #NewS2TransactionNumbers
DECLARE @VarTransactionCount int;
SET @VarTransactionCount = ...
CREATE TABLE #NewS2TransactionNumbers(ID int NOT NULL);
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;
-- use the generated numbers...
SELECT ID FROM #NewS2TransactionNumbers AS TT;
Aquí hay algunas cosas que requieren explicación.
Necesito insertar un número dado de filas en la tabla SequenceS2TransactionNumber . Yo uso una tabla de Numbers auxiliares para esto. Esta tabla simplemente contiene números enteros de 1 a 100,000. También se usa en otros lugares del sistema. Compruebo si hay suficientes filas en la tabla de Numbers y la expandí a 100,000 * 100,000 uniéndose entre sí si es necesario.
Tengo que guardar el resultado de la inserción masiva en algún lugar y pasarlo a la persona que llama de alguna manera. Una forma de pasar una tabla fuera del procedimiento almacenado es usar una tabla temporal. No puedo usar el parámetro con valores de tabla aquí, porque es de solo lectura desafortunadamente. Además, no puedo insertar directamente los valores de secuencia generados en la tabla temporal #NewS2TransactionNumbers . No puedo usar #NewS2TransactionNumbers en la cláusula OUTPUT , porque ROLLBACK lo limpiará. Afortunadamente, las variables de tabla no se ven afectadas por el ROLLBACK .
Entonces, utilizo la variable de tabla @TableTransactionNumbers como destino de la cláusula OUTPUT . Luego ROLLBACK la transacción para limpiar la tabla de Secuencia. Luego copie los valores de secuencia generados de la variable de tabla @TableTransactionNumbers a la tabla temporal #NewS2TransactionNumbers , ya que solo la tabla temporal #NewS2TransactionNumbers puede ser visible para la persona que llama del procedimiento almacenado. La variable de tabla @TableTransactionNumbers no es visible para la persona que llama del procedimiento almacenado.
Además, es posible usar la cláusula OUTPUT para enviar la secuencia generada directamente a la persona que llama (como puede ver en la variante comentada que usa MERGE ). Funciona bien por sí mismo, pero necesitaba los valores generados en alguna tabla para su posterior procesamiento en el procedimiento almacenado de llamadas. Cuando intenté algo como esto:
INSERT INTO @TableTransactions (ID)
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;
Me estaba dando un error
No se puede usar la instrucción ROLLBACK dentro de una instrucción INSERT-EXEC.
Pero, necesito ROLLBACK dentro del EXEC , es por eso que terminé teniendo muchas tablas temporales.
Después de todo esto, qué bueno sería cambiar a la última versión del servidor SQL que tiene un objeto SEQUENCE adecuado.
Considere el siguiente fragmento.
CREATE TABLE [SEQUENCE](
[NAME] [varchar](100) NOT NULL,
[NEXT_AVAILABLE_ID] [int] NOT NULL,
CONSTRAINT [PK_SEQUENCES] PRIMARY KEY CLUSTERED
(
[NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE CLAIM_IDS (@sequenceName varchar(100), @howMany int)
AS
BEGIN
DECLARE @result int
update SEQUENCE
set
@result = NEXT_AVAILABLE_ID,
NEXT_AVAILABLE_ID = NEXT_AVAILABLE_ID + @howMany
where Name = @sequenceName
Select @result as AVAILABLE_ID
END
GO
Crea una tabla de etapas con un identificador.
Antes de cargar la tabla de etapas, trunque y reste el identificador para comenzar en 1.
Cargue su mesa Cada fila ahora tiene un valor único de 1 a N.
Crea una tabla que contenga números de secuencia. Esto podría ser varias filas, una para cada secuencia.
Busca el número de secuencia de la tabla de secuencias que creaste. Actualice el número de secuencia agregando el número de filas en la tabla de etapas al número de secuencia.
Actualice el identificador de la tabla de etapas agregando el número de secuencia al que lo miró. Este es un proceso sencillo de un paso. o Cargue su tabla de objetivos, agregue el número de secuencia al identificador mientras carga en ETL. Esto puede aprovechar el cargador masivo y permitir otras transformaciones.
El otro problema con las columnas de identidad es que si tiene más de una tabla donde los números de secuencia deben ser únicos, una columna de identidad no funciona. Y como menciona Corey Trager, un tipo de implementación de secuencia propia puede presentar algunos problemas de bloqueo.
Las soluciones más sencillas equivalentes parecen ser la creación de una tabla de SQL Server con una sola columna para la identidad, que toma el lugar de un tipo separado de objeto de "secuencia". Por ejemplo, si en Oracle tendría dos tablas de una secuencia como Dogs <- sequence object -> Cats, en SQL Server crearía tres objetos de base de datos, todas las tablas como Dogs <- Pets with identity column -> Gatos. Debería insertar una fila en la tabla Pets para obtener el número de secuencia donde normalmente usaría NEXTVAL y luego insertarlo en la tabla Dogs o Cats como lo haría normalmente una vez que obtenga el tipo de mascota real del usuario. Cualquier columna común adicional podría trasladarse de las tablas Perros / Gatos a la tabla Supertipo de Mascotas, con algunas consecuencias que 1) habría una fila para cada número de secuencia, 2) cualquier columna que no pueda ser poblada al obtener el número de secuencia lo haría necesita tener valores predeterminados y 3) requeriría una unión para obtener todas las columnas.
En SQL Server 2012, puede simplemente usar
CREATE SEQUENCE
En 2005 y 2008, puede obtener una lista arbitraria de números secuenciales utilizando una expresión de tabla común.
Aquí hay un ejemplo (tenga en cuenta que la opción MAXRECURSION es importante):
DECLARE @MinValue INT = 1;
DECLARE @MaxValue INT = 1000;
WITH IndexMaker (IndexNumber) AS
(
SELECT
@MinValue AS IndexNumber
UNION ALL SELECT
IndexNumber + 1
FROM
IndexMaker
WHERE IndexNumber < @MaxValue
)
SELECT
IndexNumber
FROM
IndexMaker
ORDER BY
IndexNumber
OPTION
(MAXRECURSION 0)
Estoy totalmente de acuerdo y lo hice el año pasado en un proyecto.
Acabo de crear una tabla con el nombre de la secuencia, el valor actual y la cantidad de incremento.
Luego creé un 2 procs para agregarlos y eliminarlos. Y 2 funciones para obtener siguiente, y ponerse al día.
La forma en que solía resolver este problema era una tabla ''Secuencias'' que almacena todas mis secuencias y un ''próximo'' procedimiento almacenado.
Tabla Sql:
CREATE TABLE Sequences (
name VARCHAR(30) NOT NULL,
value BIGINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_Sequences PRIMARY KEY (name)
);
PK_Secuencias se usa solo para garantizar que nunca haya secuencias con el mismo nombre.
Procedimiento almacenado Sql:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''nextVal'') AND type in (N''P'', N''PC'')) DROP PROCEDURE nextVal;
GO
CREATE PROCEDURE nextval
@name VARCHAR(30)
AS
BEGIN
DECLARE @value BIGINT
BEGIN TRANSACTION
UPDATE Sequences
SET @value=value=value + 1
WHERE name = @name;
-- SELECT @value=value FROM Sequences WHERE name=@name
COMMIT TRANSACTION
SELECT @value AS nextval
END;
Inserta algunas secuencias:
INSERT INTO Sequences(name, value) VALUES (''SEQ_Workshop'', 0);
INSERT INTO Sequences(name, value) VALUES (''SEQ_Participant'', 0);
INSERT INTO Sequences(name, value) VALUES (''SEQ_Invoice'', 0);
Finalmente, obtenga el siguiente valor de una secuencia,
execute nextval ''SEQ_Participant'';
Cierto código c # para obtener el siguiente valor de la tabla de Secuencias,
public long getNextVal()
{
long nextval = -1;
SqlConnection connection = new SqlConnection("your connection string");
try
{
//Connect and execute the select sql command.
connection.Open();
SqlCommand command = new SqlCommand("nextval", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant";
nextval = Int64.Parse(command.ExecuteScalar().ToString());
command.Dispose();
}
catch (Exception) { }
finally
{
connection.Dispose();
}
return nextval;
}
Las secuencias implementadas por Oracle requieren una llamada a la base de datos antes del inserto. las identidades implementadas por SQL Server requieren una llamada a la base de datos después de la inserción.
Uno no es más hambriento que el otro. El efecto neto es el mismo: dependencia / dependencia en el almacén de datos para proporcionar valores únicos de claves artificiales y (en la mayoría de los casos) dos llamadas a la tienda.
Supongo que su modelo relacional se basa en claves artificiales, y en este contexto, ofreceré la siguiente observación:
Nunca debemos tratar de imbuir claves artificiales con significado; su único propósito debería ser vincular registros relacionados.
¿Cuál es su necesidad relacionada con ordenar datos? ¿se puede manejar en la vista (presentación) o es un verdadero atributo de sus datos que debe persistir?
Podrías usar tablas simples y usarlas como secuencias. Eso significa que tus insertos siempre serán:
BEGIN TRANSACTION
SELECT number from plain old table..
UPDATE plain old table, set the number to be the next number
INSERT your row
COMMIT
Pero no hagas esto El bloqueo sería malo ...
Empecé en SQL Server y para mí, el esquema de "secuencia" de Oracle parecía un truco. Supongo que vienes de la otra dirección y hacia ti, y scope_identity () parece un truco.
Superalo. Cuando fueres haz lo que vieres.
Por SQL puedes usar esta estrategia;
CREATE SEQUENCE [dbo].[SequenceFile]
AS int
START WITH 1
INCREMENT BY 1 ;
y lea el siguiente valor único con este SQL
SELECT NEXT VALUE FOR [dbo].[SequenceFile]
Si desea insertar datos con una clave secuencial, pero no desea tener que consultar nuevamente la base de datos para obtener la clave recién insertada, creo que sus únicas dos opciones son:
- Realice la inserción a través de un procedimiento almacenado que devuelve el valor clave recién insertado
- Implemente la secuencia del lado del cliente (para que sepa la nueva clave antes de insertarla)
Si estoy haciendo generación de claves en el lado del cliente, me encantan los GUID. Creo que son hermosos como diablos.
row["ID"] = Guid.NewGuid();
Esa línea debería colocarse en el capó de un deportivo en alguna parte.
Si está utilizando SQL Server 2005, tiene la opción de utilizar Row_Number
Una columna de Identidad es más o menos análoga a una secuencia.
¡TRANSACCIÓN SEGURA! Para las versiones de SQL Server antes de 2012 ... (gracias a Matt G.) Una cosa que falta en esta discusión es la seguridad de las transacciones. Si obtiene un número de una secuencia, ese número debe ser único, y ninguna otra aplicación o código debería poder obtener ese número. En mi caso, a menudo extraemos números únicos de las secuencias, pero la transacción real puede abarcar una cantidad considerable de tiempo, por lo que no queremos que nadie más obtenga el mismo número antes de comprometer la transacción. Necesitábamos imitar el comportamiento de las secuencias de oráculo , donde se reservó un número cuando se extrajo. Mi solución es usar xp_cmdshell para obtener una sesión / transacción separada en la base de datos, de modo que podamos actualizar inmediatamente la secuencia, para toda la base de datos, incluso antes de que se complete la transacción.
--it is used like this:
-- use the sequence in either insert or select:
Insert into MyTable Values (NextVal(''MySequence''), ''Foo'');
SELECT NextVal(''MySequence'');
--you can make as many sequences as you want, by name:
SELECT NextVal(''Mikes Other Sequence'');
--or a blank sequence identifier
SELECT NextVal('''');
La solución requiere una sola tabla para mantener los valores de secuencia usados, y un procedimiento que crea una segunda transacción autónoma para asegurar que las sesiones concurrentes no se enreden. Puede tener tantas secuencias únicas como quiera, se hace referencia por nombre. El siguiente código de ejemplo se modifica para omitir el usuario solicitante y el sello de fecha en la tabla de historial de secuencias (para la auditoría), pero pensé que el ejemplo menos complejo era mejor ;-).
CREATE TABLE SequenceHolder(SeqName varchar(40), LastVal int);
GO
CREATE function NextVAL(@SEQname varchar(40))
returns int
as
begin
declare @lastval int
declare @barcode int;
set @lastval = (SELECT max(LastVal)
FROM SequenceHolder
WHERE SeqName = @SEQname);
if @lastval is null set @lastval = 0
set @barcode = @lastval + 1;
--=========== USE xp_cmdshell TO INSERT AND COMMINT NOW, IN A SEPERATE TRANSACTION =============================
DECLARE @sql varchar(4000)
DECLARE @cmd varchar(4000)
DECLARE @recorded int;
SET @sql = ''INSERT INTO SequenceHolder(SeqName, LastVal) VALUES ('''''' + @SEQname + '''''', '' + CAST(@barcode AS nvarchar(50)) + '') ''
SET @cmd = ''SQLCMD -S '' + @@servername +
'' -d '' + db_name() + '' -Q "'' + @sql + ''"''
EXEC master..xp_cmdshell @cmd, ''no_output''
--===============================================================================================================
-- once submitted, make sure our value actually stuck in the table
set @recorded = (SELECT COUNT(*)
FROM SequenceHolder
WHERE SeqName = @SEQname
AND LastVal = @barcode);
--TRIGGER AN ERROR
IF (@recorded != 1)
return cast(''Barcode was not recorded in SequenceHolder, xp_cmdshell FAILED!! ['' + @cmd +'']'' as int);
return (@barcode)
end
GO
COMMIT;
Ahora, para que el procedimiento funcione, necesitará habilitar xp_cmdshell, hay muchas descripciones buenas de cómo hacerlo, aquí están mis notas personales que hice cuando intentaba que todo funcionara. La idea básica es que necesita que xp_cmdshell esté activado en SQLServer Surface. Es una configuración y necesita establecer una cuenta de usuario como la cuenta en la que se ejecutará el comando xp_cmdshell, que accederá a la base de datos para insertar el número de secuencia y confirmarlo.
--- LOOSEN SECURITY SO THAT xp_cmdshell will run
---- To allow advanced options to be changed.
EXEC sp_configure ''show advanced options'', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure ''xp_cmdshell'', 1
GO
---- To update the currently configured value for this feature.
RECONFIGURE
GO
—-Run SQLServer Management Studio as Administrator,
—- Login as domain user, not sqlserver user.
--MAKE A DATABASE USER THAT HAS LOCAL or domain LOGIN! (not SQL server login)
--insure the account HAS PERMISSION TO ACCESS THE DATABASE IN QUESTION. (UserMapping tab in User Properties in SQLServer)
—grant the following
GRANT EXECUTE on xp_cmdshell TO [domain/user]
—- run the following:
EXEC sp_xp_cmdshell_proxy_account ''domain/user'', ''pwd''
--alternative to the exec cmd above:
create credential ##xp_cmdshell_proxy_account## with identity = ''domain/user'', secret = ''pwd''
-—IF YOU NEED TO REMOVE THE CREDENTIAL USE THIS
EXEC sp_xp_cmdshell_proxy_account NULL;
-—ways to figure out which user is actually running the xp_cmdshell command.
exec xp_cmdshell ''whoami.exe''
EXEC xp_cmdshell ''osql -E -Q"select suser_sname()"''
EXEC xp_cmdshell ''osql -E -Q"select * from sys.login_token"''
Sql Server 2012 ha introducido objetos SEQUENCE , que le permiten generar valores numéricos secuenciales no asociados con ninguna tabla.
Crearlos es fácil:
CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
Un ejemplo de usarlos antes de la inserción:
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
VALUES (@NextID, ''Rim'', 2) ;
Ver mi blog para una mirada en profundidad sobre cómo usar secuencias:
http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/