Skip to content

Instantly share code, notes, and snippets.

@SQLKiwi

SQLKiwi/1 ~ Test Rig Setup.sql Secret

Created November 22, 2018 13:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save SQLKiwi/d7ef9372ca10b0f579edfb9d52dec9cf to your computer and use it in GitHub Desktop.
Save SQLKiwi/d7ef9372ca10b0f579edfb9d52dec9cf to your computer and use it in GitHub Desktop.
Download ZIP
Sequence Tables scripts
Raw
1 ~ Test Rig Setup.sql
-- ================================================================================================================================================
-- TEST RIG SETUP - SQL SERVER 2005 OR 2008 REQUIRED
--
-- Creates all the objects required to run the tests
-- The loopback server implementation requires SQL Server 2008
-- ================================================================================================================================================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT OFF;
GO
USE master;
GO
-- Remove the loopback linked server if it exists
-- I use a GUID here as the name of the linked server to prevent a name collision
IF EXISTS (SELECT * FROM sys.servers WHERE name = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A')
EXECUTE sp_dropserver N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A', 'droplogins';
GO
-- Create the loopback linked server and configure it
DECLARE @servername SYSNAME;
SET @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName'));
EXECUTE sys.sp_addlinkedserver
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A',
@srvproduct = N'',
@provider = N'SQLNCLI',
@datasrc = @servername;
EXECUTE sys.sp_serveroption
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A',
@optname = 'RPC OUT',
@optvalue = 'ON';
-- Only for 2008
IF CONVERT(SYSNAME, SERVERPROPERTY(N'ProductVersion')) LIKE N'10.%'
BEGIN
-- Prevent a local transaction esclating to a distributed transaction (SQL Server 2008 only)
EXECUTE sys.sp_serveroption
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A',
@optname = 'remote proc transaction promotion',
@optvalue = 'OFF';
END;
GO
-- Enable common language runtime integration
IF EXISTS (SELECT * FROM sys.configurations WHERE name = N'clr enabled' AND value_in_use = 0)
BEGIN
-- Create a synonym as a way of remembering that we enabled CLR
-- This allows the clean-up script to reset it later
CREATE SYNONYM [FB111697-80B1-4B5C-A42C-6B074AC2DAF6]
FOR master.dbo.spt_values;
-- Enable CLR
EXECUTE sys.sp_configure
@configname = 'clr enabled',
@configvalue = 1;
RECONFIGURE;
END;
GO
-- Drop the CLR assembly, login, and asymmetric key if they exist in master
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SqlAllocation')
DROP ASSEMBLY [SqlAllocation];
IF EXISTS (SELECT * from sys.server_principals WHERE name = N'SqlAllocation_ExternalAccessLogin' AND type_desc = N'ASYMMETRIC_KEY_MAPPED_LOGIN')
DROP LOGIN [SqlAllocation_ExternalAccessLogin];
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = N'AK_SqlAllocation')
DROP ASYMMETRIC KEY [AK_SqlAllocation];
GO
-- Create the CLR assemby temporarily, with the SAFE permission set
CREATE ASSEMBLY [SqlAllocation]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030003EF894B0000000000000000E00002210B010800001000000020000000000000AE2D00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000E8850000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000005803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D0000002000000010000000100000000000000000000000000000200000602E7273726300000058030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902D000000000000480000000200050054220000040B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000F1A2F9AB2F844F82F47B11CC5B813EBA3558D48596A908127013543DFF5254D0F62D7F12F523F70F4017F68ED55029E79E74DCE327454F96410AC2E5E84EB1747B044748F4F9BF740EE8F21B0D2F749AED534FA08A4C902B4FAF4BF990C9CF2FC4BA58E10F31927103B0469AFE6178F511DCA0C23ABDB5B94D1809E7C5D571681B3004002F010000010000117201000070731000000A0C086F1100000A723100007008731200000A0D096F1300000A74170000010ADE0A092C06096F1400000ADCDE0A082C06086F1400000ADC731500000A13041104066F1600000A1104176F1700000A1104166F1800000A11046F1900000A731000000A130511056F1100000A731A00000A13061106727B0000706F1B00000A11066F1C00000A72980100701E166F1D00000A038C020000016F1E00000A11066F1C00000A729E0100701F0C1F146F1D00000A028C030000016F1E00000A1106176F1F00000A110611056F2000000A11066F1300000AA51F0000010BDE1726047E2100000A81020000011F63732200000A1307DE2FDE0C11062C0711066F1400000ADCDE0C11052C0711056F1400000ADC0407732200000A810200000116732200000A2A11072A000140000002001D000E2B000A0000000002000B002C37000A000000000000D7000FE600172000000102007C0083FF000C0000000002006E009F0D010C000000001E02282300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000C8020000237E0000340300004804000023537472696E6773000000007C070000A4010000235553002009000010000000234755494400000030090000D401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000020000000020000000200000004000000230000000F00000001000000010000000200000000000A0001000000000006003D0036000A00650050000A006E0050000600990087000600D100B2000600E50087000600FE00870006001901870006003401870006004D0187000600660187000600850187000600A20187000600D901B9010600F901B9010A00400225020A005602250206008E02B2000A00B1029B020A00D202BF020A00E4029B020A00EF02BF0206000703360006000E0336000A0022039B020A006E03BF020A00AD039B020A00D3039B020A00E00344000A00FB0344000600260436000600310436000000000001000000000001000100010010001C000000050001000100D02000000000960078000A0001004C22000000008618810016000500000000000000000001006802000002007502020003007F02210081001A00290081001F00310081001A00390081001A00410081001A00490081001A00510081001A00590081001A00610081001A00690081001A00710081002400790081001600810081001600890081001600910081001600990081001A00A100DF021600A90081003801B100F9023F01C1001A031600C90081001600C9003D031A00C9004C031F00C90063031F00D10088034301A90081001600B1009D031A00A900C4034701D900EA034C01E100EE035501B10007045A01A9001704600111002C04660111008100240009008100160020006B00CB0024007300E0002E002B0081012E00330099012E003B007B012E006300B5012E0023007B012E0043007B012E005B00AC012E0013007B012E004B007B012E005300990144007300F30064007300E0008400730025016A0104800000010000007F0EE9790100000029001702000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E0053716C416C6C6F636174696F6E2E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C496E7433320053716C537472696E6700416C6C6F63617465002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C416C6C6F636174696F6E004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053716C46616365744174747269627574650053657175656E63654E616D650052616E676553697A65004669727374416C6C6F6361746564004F75744174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200537472696E670049446973706F7361626C6500446973706F73650053716C436F6E6E656374696F6E537472696E674275696C646572007365745F44617461536F75726365007365745F496E74656772617465645365637572697479007365745F456E6C697374004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464007365745F53716C56616C756500436F6D6D616E6454797065007365745F436F6D6D616E6454797065007365745F436F6E6E656374696F6E00496E743332004E756C6C004E756C6C5265666572656E6365457863657074696F6E00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000049530045004C004500430054002000530045005200560045005200500052004F005000450052005400590028004E0027005300650072007600650072004E0061006D0065002700290001811B5500500044004100540045002000740065006D007000640062002E00640062006F002E00530065007100750065006E00630065005400610062006C0065002000570049005400480020002800520045004100440043004F004D004D00490054005400450044004C004F0043004B002900200053004500540020006E006500780074005F00760061006C007500650020003D0020006E006500780074005F00760061006C007500650020002B0020004000560020004F00550054005000550054002000640065006C0065007400650064002E006E006500780074005F00760061006C00750065002000570048004500520045002000730065007100750065006E00630065005F006E0061006D00650020003D002000400045003B0000054000560000054000450000ED293D04D789A7418562F35209C755B40008B77A5C561934E0890B00031109110D110910110903200001042001010E0420010102042001010880A00024000004800000940000000602000000240000525341310004000001000100F376ACC72BC853C56C6280B2BB6A1C1363EE66CA57901D21C382209D57C4747645C306AE771354FFE4940D58BD0B67727785E9B5A8E6A06FD8B77B3605906BAEDF1ADC3F2411BD6886D39EB5ED7C629193C524E6DCFFD10E62A578C56A70ABBDEB2E91C0DB42308932AED779D0D727A699206F1074EF07E867C1F77637B3C9951401000100540E044E616D6508416C6C6F63617465120100010054020A49734E756C6C61626C6500310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A6580000000120100010054020A49734E756C6C61626C6501062002010E124D0320001C0320000E042000126D08200312710E117508042001011C05200101117905200101124D030611091007080E08124D12551265124D1255110905010000000017010012436F7079726967687420C2A920203230313000001201000D53716C416C6C6F636174696F6E00000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E9797F0E00000100E9797F0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0041006C006C006F0063006100740069006F006E00000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700310031002E00330031003200300039000000000044001200010049006E007400650072006E0061006C004E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D00650000000000530071006C0041006C006C006F0063006100740069006F006E00000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700310031002E00330031003200300039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700310031002E003300310032003000390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
-- Create an asymmetric key, create a login from it, and give the login EXTERNAL_ACCESS permission
CREATE ASYMMETRIC KEY AK_SqlAllocation
FROM ASSEMBLY SqlAllocation;
CREATE LOGIN SqlAllocation_ExternalAccessLogin
FROM ASYMMETRIC KEY AK_SqlAllocation;
GRANT EXTERNAL ACCESS ASSEMBLY
TO SqlAllocation_ExternalAccessLogin;
DROP ASSEMBLY [SqlAllocation];
GO
-- Switch to the target database (we'll use tempdb for the demo)
USE [tempdb];
GO
-- Drop the allocation table and T-SQL procedure
IF OBJECT_ID(N'dbo.SequenceTable', N'U')
IS NOT NULL
DROP TABLE dbo.SequenceTable;
IF OBJECT_ID(N'dbo.Allocate_TSQL', N'P')
IS NOT NULL
DROP PROCEDURE dbo.Allocate_TSQL;
IF OBJECT_ID(N'dbo.Allocate_CLR', N'PC')
IS NOT NULL
DROP PROCEDURE dbo.Allocate_CLR;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'SqlAllocation')
DROP ASSEMBLY SqlAllocation;
GO
-- Create the assembly with EXTERNAL_ACCESS
-- The assembly key matches the asymmetric key created earlier. The login mapped to the key has EXTERNAL_ACCESS permission.
CREATE ASSEMBLY [SqlAllocation]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030003EF894B0000000000000000E00002210B010800001000000020000000000000AE2D00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000E8850000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000005803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D0000002000000010000000100000000000000000000000000000200000602E7273726300000058030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000902D000000000000480000000200050054220000040B00000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000F1A2F9AB2F844F82F47B11CC5B813EBA3558D48596A908127013543DFF5254D0F62D7F12F523F70F4017F68ED55029E79E74DCE327454F96410AC2E5E84EB1747B044748F4F9BF740EE8F21B0D2F749AED534FA08A4C902B4FAF4BF990C9CF2FC4BA58E10F31927103B0469AFE6178F511DCA0C23ABDB5B94D1809E7C5D571681B3004002F010000010000117201000070731000000A0C086F1100000A723100007008731200000A0D096F1300000A74170000010ADE0A092C06096F1400000ADCDE0A082C06086F1400000ADC731500000A13041104066F1600000A1104176F1700000A1104166F1800000A11046F1900000A731000000A130511056F1100000A731A00000A13061106727B0000706F1B00000A11066F1C00000A72980100701E166F1D00000A038C020000016F1E00000A11066F1C00000A729E0100701F0C1F146F1D00000A028C030000016F1E00000A1106176F1F00000A110611056F2000000A11066F1300000AA51F0000010BDE1726047E2100000A81020000011F63732200000A1307DE2FDE0C11062C0711066F1400000ADCDE0C11052C0711056F1400000ADC0407732200000A810200000116732200000A2A11072A000140000002001D000E2B000A0000000002000B002C37000A000000000000D7000FE600172000000102007C0083FF000C0000000002006E009F0D010C000000001E02282300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000C8020000237E0000340300004804000023537472696E6773000000007C070000A4010000235553002009000010000000234755494400000030090000D401000023426C6F620000000000000002000001471502000900000000FA0133001600000100000020000000020000000200000004000000230000000F00000001000000010000000200000000000A0001000000000006003D0036000A00650050000A006E0050000600990087000600D100B2000600E50087000600FE00870006001901870006003401870006004D0187000600660187000600850187000600A20187000600D901B9010600F901B9010A00400225020A005602250206008E02B2000A00B1029B020A00D202BF020A00E4029B020A00EF02BF0206000703360006000E0336000A0022039B020A006E03BF020A00AD039B020A00D3039B020A00E00344000A00FB0344000600260436000600310436000000000001000000000001000100010010001C000000050001000100D02000000000960078000A0001004C22000000008618810016000500000000000000000001006802000002007502020003007F02210081001A00290081001F00310081001A00390081001A00410081001A00490081001A00510081001A00590081001A00610081001A00690081001A00710081002400790081001600810081001600890081001600910081001600990081001A00A100DF021600A90081003801B100F9023F01C1001A031600C90081001600C9003D031A00C9004C031F00C90063031F00D10088034301A90081001600B1009D031A00A900C4034701D900EA034C01E100EE035501B10007045A01A9001704600111002C04660111008100240009008100160020006B00CB0024007300E0002E002B0081012E00330099012E003B007B012E006300B5012E0023007B012E0043007B012E005B00AC012E0013007B012E004B007B012E005300990144007300F30064007300E0008400730025016A0104800000010000007F0EE9790100000029001702000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E0053716C416C6C6F636174696F6E2E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C496E7433320053716C537472696E6700416C6C6F63617465002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C416C6C6F636174696F6E004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053716C46616365744174747269627574650053657175656E63654E616D650052616E676553697A65004669727374416C6C6F6361746564004F75744174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64004462436F6D6D616E6400457865637574655363616C617200537472696E670049446973706F7361626C6500446973706F73650053716C436F6E6E656374696F6E537472696E674275696C646572007365745F44617461536F75726365007365745F496E74656772617465645365637572697479007365745F456E6C697374004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464007365745F53716C56616C756500436F6D6D616E6454797065007365745F436F6D6D616E6454797065007365745F436F6E6E656374696F6E00496E743332004E756C6C004E756C6C5265666572656E6365457863657074696F6E00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000049530045004C004500430054002000530045005200560045005200500052004F005000450052005400590028004E0027005300650072007600650072004E0061006D0065002700290001811B5500500044004100540045002000740065006D007000640062002E00640062006F002E00530065007100750065006E00630065005400610062006C0065002000570049005400480020002800520045004100440043004F004D004D00490054005400450044004C004F0043004B002900200053004500540020006E006500780074005F00760061006C007500650020003D0020006E006500780074005F00760061006C007500650020002B0020004000560020004F00550054005000550054002000640065006C0065007400650064002E006E006500780074005F00760061006C00750065002000570048004500520045002000730065007100750065006E00630065005F006E0061006D00650020003D002000400045003B0000054000560000054000450000ED293D04D789A7418562F35209C755B40008B77A5C561934E0890B00031109110D110910110903200001042001010E0420010102042001010880A00024000004800000940000000602000000240000525341310004000001000100F376ACC72BC853C56C6280B2BB6A1C1363EE66CA57901D21C382209D57C4747645C306AE771354FFE4940D58BD0B67727785E9B5A8E6A06FD8B77B3605906BAEDF1ADC3F2411BD6886D39EB5ED7C629193C524E6DCFFD10E62A578C56A70ABBDEB2E91C0DB42308932AED779D0D727A699206F1074EF07E867C1F77637B3C9951401000100540E044E616D6508416C6C6F63617465120100010054020A49734E756C6C61626C6500310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A6580000000120100010054020A49734E756C6C61626C6501062002010E124D0320001C0320000E042000126D08200312710E117508042001011C05200101117905200101124D030611091007080E08124D12551265124D1255110905010000000017010012436F7079726967687420C2A920203230313000001201000D53716C416C6C6F636174696F6E00000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100E9797F0E00000100E9797F0E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00460020000010053007400720069006E006700460069006C00650049006E0066006F0000003C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0041006C006C006F0063006100740069006F006E00000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700310031002E00330031003200300039000000000044001200010049006E007400650072006E0061006C004E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0041006C006C006F0063006100740069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D00650000000000530071006C0041006C006C006F0063006100740069006F006E00000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700310031002E00330031003200300039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700310031002E003300310032003000390000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
-- Create the CLR procedure from the assembly
CREATE PROCEDURE
[dbo].[Allocate_CLR]
@SequenceName NVARCHAR(20),
@RangeSize INTEGER,
@FirstAllocated INTEGER OUTPUT
WITH EXECUTE AS CALLER
AS EXTERNAL NAME
[SqlAllocation].[StoredProcedures].[Allocate];
GO
-- The table used to hold sequences
CREATE TABLE dbo.SequenceTable
(
sequence_name NVARCHAR(20) PRIMARY KEY CLUSTERED,
next_value INTEGER NOT NULL
);
GO
-- Base procedure to allocate ranges (called by all implementations)
CREATE PROCEDURE dbo.Allocate_TSQL
@SequenceName NVARCHAR(20), -- The name of the sequence to allocate keys from
@RangeSize INTEGER = 1, -- The number of keys to allocate
@FirstAllocated INTEGER OUTPUT -- The first key allocated (output)
AS
BEGIN
SET XACT_ABORT ON; -- Most errors will abort the batch
SET NOCOUNT ON; -- Supress 'x row(s) affected' messages
SET ROWCOUNT 0; -- Reset rowcount
-- Validate the range size requested
IF (@RangeSize IS NULL OR @RangeSize < 1)
BEGIN
RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
RETURN 999;
END;
-- Initialize the output parameter
SET @FirstAllocated = NULL;
-- Update the row associated with @SequenceName, returning the
-- current value, and then incrementing it by @RangeSize
UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
SET @FirstAllocated = next_value,
next_value = next_value + @RangeSize
WHERE sequence_name = @SequenceName;
-- If @Allocated has a non-NULL value, we know we successfully updated a row
RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END;
END;
GO
-- Initialise the test sequence
INSERT dbo.SequenceTable
(sequence_name, next_value)
VALUES (N'Test Sequence', 1);
GO
Raw
2 ~ Run Locking Tests.sql
-- ================================================================================================================================================
-- TEST RUN SCRIPT
--
-- Runs tests to demonstrate locking differences between the implementations
-- The loopback server implementation requires SQL Server 2008
-- ================================================================================================================================================
USE tempdb;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT OFF;
GO
SELECT 'TEST ONE - ORIGINAL (BLOCKING)' AS Test;
GO
DECLARE @RC INTEGER, -- Return code from the procedure call
@Value INTEGER; -- The first ID allocated
-- Start a local transaction
BEGIN TRANSACTION;
-- Call the procedure to allocate a range of 100 keys
EXECUTE @RC =
dbo.Allocate_TSQL
@SequenceName = N'Test Sequence',
@RangeSize = 100,
@FirstAllocated = @Value OUTPUT;
-- Show the locks held
-- The UPDATE inside the procedure holds an exclusive row lock until the end
-- of the outermost transaction. This prevents other users from allocating
-- IDs concurrently
SELECT request_session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks;
-- Rollback the transaction
-- The ID allocation is also rolled back
ROLLBACK TRANSACTION;
-- The next value to allocate is still '1'
SELECT A.sequence_name,
A.next_value
FROM dbo.SequenceTable A;
GO
SELECT 'TEST TWO - CLR (NON-BLOCKING)' AS Test;
GO
DECLARE @RC INTEGER, -- Return code from the procedure call
@Value INTEGER; -- The first ID allocated
-- Start a local transaction
BEGIN TRANSACTION;
-- Call the procedure to allocate a range of 100 IDs
EXECUTE @RC =
dbo.Allocate_CLR
@SequenceName = N'Test Sequence',
@RangeSize = 100,
@FirstAllocated = @Value OUTPUT;
-- Show the locks held
-- There are no locks on the table - the ID allocation took place in a separate transaction, on a separate connection
SELECT request_session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks;
-- Rollback the transaction
-- The ID allocation is NOT rolled back
ROLLBACK TRANSACTION;
-- The next value to allocate is now '101'
SELECT A.sequence_name,
A.next_value
FROM dbo.SequenceTable A;
GO
SELECT 'TEST THREE - LOOPBACK SERVER (NON-BLOCKING, 2008 ONLY)' AS Test;
GO
DECLARE @RC INTEGER, -- Return code from the procedure call
@Value INTEGER; -- The first ID allocated
-- Start a local transaction
BEGIN TRANSACTION;
-- Call the procedure to allocate a range of 100 keys
-- (The linked server would usually be called some more
-- friendly, like 'loopback'. I use a GUID to avoid
-- a name collision with an exisiting linked server.
EXECUTE @RC =
[47B10603-3D2A-4DED-AD40-3C8598EB8B6A].tempdb.dbo.Allocate_TSQL
@SequenceName = N'Test Sequence',
@RangeSize = 100,
@FirstAllocated = @Value OUTPUT;
-- Show the locks held
-- There are none - the ID allocation took place in a separate transaction, on a separate connection
SELECT request_session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks;
-- Rollback the transaction
-- The ID allocation is NOT rolled back
ROLLBACK TRANSACTION;
-- The next value to allocate is now '201'
SELECT A.sequence_name,
A.next_value
FROM dbo.SequenceTable A;
GO
Raw
3 ~ Run Performance Tests.sql
-- ================================================================================================================================================
-- PERFORMANCE TESTS
--
-- Measures the time taken to run a configurable number of allocations using each method
-- The loopback server implementation requires SQL Server 2008
-- ================================================================================================================================================
USE tempdb;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET NOCOUNT OFF;
GO
DECLARE @StartTime DATETIME, -- Start time of each test run
@RunNumber INTEGER, -- Current test number
@Runs INTEGER, -- Total number of test runs
@KeyCount INTEGER, -- The number of keys to reserve on each call
@RC INTEGER, -- Return code from the procedure call
@Value INTEGER, -- The first ID allocated
@Time1 INTEGER, -- Total execution time for the basic method
@Time2 INTEGER, -- Total execution time for the CLR method
@Time3 INTEGER; -- Total execution time for the loopback method
-- *** START TEST CONFIGURATION OPTIONS ***
SELECT @Runs = 10000,
@KeyCount = 250;
-- *** END TEST CONFIGURATION OPTIONS ***
-- Initialize the run
SELECT @RunNumber = 1,
@StartTime = CURRENT_TIMESTAMP;
WHILE (@RunNumber <= @Runs)
BEGIN
-- Call the procedure to allocate a range of keys
EXECUTE @RC =
dbo.Allocate_TSQL
@SequenceName = N'Test Sequence',
@RangeSize = @KeyCount,
@FirstAllocated = @Value OUTPUT;
SET @RunNumber = @RunNumber + 1;
END;
SET @Time1 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP);
SELECT @RunNumber = 1,
@StartTime = CURRENT_TIMESTAMP;
WHILE (@RunNumber <= @Runs)
BEGIN
-- Call the procedure to allocate a range of keys
EXECUTE @RC =
dbo.Allocate_CLR
@SequenceName = N'Test Sequence',
@RangeSize = @KeyCount,
@FirstAllocated = @Value OUTPUT;
SET @RunNumber = @RunNumber + 1;
END;
SET @Time2 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP);
SELECT @RunNumber = 1,
@StartTime = CURRENT_TIMESTAMP;
WHILE (@RunNumber <= @Runs)
BEGIN
-- Call the procedure to allocate a range of keys
-- (GUID used for linked server name to avoid a name collision)
EXECUTE @RC =
[47B10603-3D2A-4DED-AD40-3C8598EB8B6A].tempdb.dbo.Allocate_TSQL
@SequenceName = N'Test Sequence',
@RangeSize = @KeyCount,
@FirstAllocated = @Value OUTPUT;
SET @RunNumber = @RunNumber + 1;
END;
SET @Time3 = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP);
-- Results
SELECT 'Performance Test Results (average, ms)';
SELECT run_count = @Runs,
allocation_size = @KeyCount,
blocking_run_ms = CONVERT(DEC(9,4), @Time1 * 1. / @Runs),
clr_method_ms = CONVERT(DEC(9,4), @Time2 * 1. / @Runs),
loopback_method_ms = CONVERT(DEC(9,4), @Time3 * 1. / @Runs);
GO
Raw
4 ~ Clean Up.sql
-- ================================================================================================================================================
-- POST-TEST CLEANUP
--
-- Drops all objects created by the script
-- Disables CLR integration if script #1 enabled it
-- ================================================================================================================================================
USE master;
GO
DROP LOGIN [SqlAllocation_ExternalAccessLogin];
DROP ASYMMETRIC KEY [AK_SqlAllocation];
GO
IF EXISTS
(
-- Check for the synonym we created if CLR
-- integration was enabled by this script
SELECT *
FROM master.sys.synonyms
WHERE name = N'FB111697-80B1-4B5C-A42C-6B074AC2DAF6'
AND type_desc = N'SYNONYM'
)
BEGIN
-- Disable CLR integration
EXECUTE sys.sp_configure
@configname = 'clr enabled',
@configvalue = 0;
RECONFIGURE;
DROP SYNONYM [FB111697-80B1-4B5C-A42C-6B074AC2DAF6];
END;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.Allocate_CLR;
DROP PROCEDURE dbo.Allocate_TSQL;
DROP ASSEMBLY SqlAllocation;
DROP TABLE dbo.SequenceTable;
EXECUTE sys.sp_dropserver
@server = N'47B10603-3D2A-4DED-AD40-3C8598EB8B6A',
@droplogins = 'droplogins';
GO
Raw
Allocate.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure(Name="Allocate")]
[return: SqlFacet(IsNullable = false)]
public static SqlInt32 Allocate
(
[SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 128)] SqlString SequenceName,
[SqlFacet(IsNullable = false)] SqlInt32 RangeSize,
[SqlFacet(IsNullable = true)]out SqlInt32 FirstAllocated
)
{
string serverName; // The name of the SQL Server instance this procedure was called from
int allocated; // The first range value allocated by this routine
// Use the context connection to discover the name of the calling SQL Server
// (this is needed for the non-context connection used later)
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT SERVERPROPERTY(N'ServerName')", conn))
{
serverName = (string)cmd.ExecuteScalar();
}
}
// Construct the connection string for the non-context connection
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource=serverName;
csb.IntegratedSecurity=true;
csb.Enlist = false;
// Use a separate transaction scope to allocate the range
using (SqlConnection conn = new SqlConnection(csb.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
// Parameterize the query to promote plan re-use
cmd.CommandText = "UPDATE tempdb.dbo.SequenceTable WITH (READCOMMITTEDLOCK) SET next_value = next_value + @V OUTPUT deleted.next_value WHERE sequence_name = @E;";
cmd.Parameters.Add("@V", SqlDbType.Int, 0).SqlValue = RangeSize;
cmd.Parameters.Add("@E", SqlDbType.NVarChar, 20).SqlValue = SequenceName;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
try
{
// The first ID allocated is returned by the OUTPUT clause of the UPDATE statement
allocated = (int)cmd.ExecuteScalar();
}
catch (NullReferenceException)
{
// The sequence name did not exist so return NULL in the output parameter
FirstAllocated = SqlInt32.Null;
// 'Entity not found' return code
return new SqlInt32(99);
}
}
}
// Set the output parameter
FirstAllocated = new SqlInt32(allocated);
// Success return code
return new SqlInt32(0);
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment