{"id":3725,"date":"2014-03-29T08:49:58","date_gmt":"2014-03-29T08:49:58","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/03\/29\/how-to-redistribute-aspnet_regsql-exe-collection-of-common-programming-errors\/"},"modified":"2014-03-29T08:49:58","modified_gmt":"2014-03-29T08:49:58","slug":"how-to-redistribute-aspnet_regsql-exe-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/03\/29\/how-to-redistribute-aspnet_regsql-exe-collection-of-common-programming-errors\/","title":{"rendered":"How to redistribute aspnet_regsql.exe?-Collection of common programming errors"},"content":{"rendered":"<ul>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/34b114c4b7786056d6479ecce91edd7a?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nDSO<\/p>\n<p>I am writing a setup program for my ASP.NET web site, and I need to incorporate the aspnet_regsql tool for registering\/unregistering the membership\/roles tables into my database.<\/p>\n<p>If I bundle this with my setup, what are all the dependent files I need to include to ensure it all works?<\/p>\n<p>Or is it better to not bundle this, but locate the .NET Framework folder at runtime and launch it from there?<\/p>\n<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/91973b1bef91ee4f314cd95a37a45f2b?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nAnthonyWJones<\/p>\n<p>You should not distribute it, you should use find it in the appropriate framework runtime and use it from there.<\/p>\n<p>It could be that SPs and hotfix patches may modify the file slightly to be appropriate to other tweaks to the framework to which it belongs. If you include a version in your distribution it may be out of sync with the revision of the framework on the client machine.<\/p>\n<p>Ok thats actually unlikely in this case but still its better not to redistribute something which ought be in the preinstalled framework.<\/p>\n<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/bf2af16812228b5a54daeb30c96efaf2?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nRobert MacLean<\/li>\n<li><img decoding=\"async\" src=\"http:\/\/www.gravatar.com\/avatar\/f8869c89889c2ceff0fc7d7a253d9f8b?s=32&amp;d=identicon&amp;r=PG\" \/><br \/>\nChris S<\/p>\n<p>Using our trusty friend Reflector, it references:<\/p>\n<ul>\n<li>mscorlib<\/li>\n<li>System<\/li>\n<li>System.Data<\/li>\n<li>System.Drawing<\/li>\n<li>System.Web<\/li>\n<li>System.Windows.Forms<\/li>\n<li>advapi32.dll<\/li>\n<li>kernel32.dll<\/li>\n<\/ul>\n<p>So nothing extraordinary, just the .NET framework. My .exe is found in<\/p>\n<pre><code>C:\\Windows\\winsxs\\x86_aspnet_regsql_b03f5f7f11d50a3a_6.0.6000.16720_none_50001be1bbe18d26\n<\/code><\/pre>\n<p>so launching from the framework directory might not work (or my machine has a strange install).<\/p>\n<p>Here&#8217;s the tables it references, from SqlCacheDependencyAdmin<\/p>\n<pre><code>internal const string SQL_CREATE_ENABLE_DATABASE_SP = \"\/* Create notification table *\/ \\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \\n      CREATE TABLE dbo.{0} (\\n      tableName             NVARCHAR(450) NOT NULL PRIMARY KEY,\\n      notificationCreated   DATETIME NOT NULL DEFAULT(GETDATE()),\\n      changeId              INT NOT NULL DEFAULT(0)\\n      )\\n\\n\/* Create polling SP *\/\\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \\n   EXEC('CREATE PROCEDURE dbo.{1} AS\\n         SELECT tableName, changeId FROM dbo.{0}\\n         RETURN 0')\\n\\n\/* Create SP for registering a table. *\/ \\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \\n   EXEC('CREATE PROCEDURE dbo.{2} \\n             @tableName NVARCHAR(450) \\n         AS\\n         BEGIN\\n\\n         DECLARE @triggerName AS NVARCHAR(3000) \\n         DECLARE @fullTriggerName AS NVARCHAR(3000)\\n         DECLARE @canonTableName NVARCHAR(3000) \\n         DECLARE @quotedTableName NVARCHAR(3000) \\n\\n         \/* Create the trigger name *\/ \\n         SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \\n         SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \\n         SET @triggerName = @triggerName + ''{3}'' \\n         SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \\n\\n         \/* Create the cannonicalized table name for trigger creation *\/ \\n         \/* Do not touch it if the name contains other delimiters *\/ \\n         IF (CHARINDEX(''.'', @tableName)  0 OR \\n             CHARINDEX(''['', @tableName)  0 OR \\n             CHARINDEX('']'', @tableName)  0) \\n             SET @canonTableName = @tableName \\n         ELSE \\n             SET @canonTableName = ''['' + @tableName + '']'' \\n\\n         \/* First make sure the table exists *\/ \\n         IF (SELECT OBJECT_ID(@tableName, ''U'')) IS NULL \\n         BEGIN \\n             RAISERROR (''00000001'', 16, 1) \\n             RETURN \\n         END \\n\\n         BEGIN TRAN\\n         \/* Insert the value into the notification table *\/ \\n         IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (NOLOCK) WHERE tableName = @tableName) \\n             IF NOT EXISTS (SELECT tableName FROM dbo.{0} WITH (TABLOCKX) WHERE tableName = @tableName) \\n                 INSERT  dbo.{0} \\n                 VALUES (@tableName, GETDATE(), 0)\\n\\n         \/* Create the trigger *\/ \\n         SET @quotedTableName = QUOTENAME(@tableName, '''''''') \\n         IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \\n             IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \\n                 EXEC(''CREATE TRIGGER '' + @fullTriggerName + '' ON '' + @canonTableName +''\\n                       FOR INSERT, UPDATE, DELETE AS BEGIN\\n                       SET NOCOUNT ON\\n                       EXEC dbo.{6} N'' + @quotedTableName + ''\\n                       END\\n                       '')\\n         COMMIT TRAN\\n         END\\n   ')\\n\\n\/* Create SP for updating the change Id of a table. *\/ \\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{6}' AND type = 'P') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{6}' AND type = 'P') \\n   EXEC('CREATE PROCEDURE dbo.{6} \\n             @tableName NVARCHAR(450) \\n         AS\\n\\n         BEGIN \\n             UPDATE dbo.{0} WITH (ROWLOCK) SET changeId = changeId + 1 \\n             WHERE tableName = @tableName\\n         END\\n   ')\\n\\n\/* Create SP for unregistering a table. *\/ \\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \\n   EXEC('CREATE PROCEDURE dbo.{4} \\n             @tableName NVARCHAR(450) \\n         AS\\n         BEGIN\\n\\n         BEGIN TRAN\\n         DECLARE @triggerName AS NVARCHAR(3000) \\n         DECLARE @fullTriggerName AS NVARCHAR(3000)\\n         SET @triggerName = REPLACE(@tableName, ''['', ''__o__'') \\n         SET @triggerName = REPLACE(@triggerName, '']'', ''__c__'') \\n         SET @triggerName = @triggerName + ''{3}'' \\n         SET @fullTriggerName = ''dbo.['' + @triggerName + '']'' \\n\\n         \/* Remove the table-row from the notification table *\/ \\n         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = ''{0}'' AND type = ''U'') \\n             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = ''{0}'' AND type = ''U'') \\n             DELETE FROM dbo.{0} WHERE tableName = @tableName \\n\\n         \/* Remove the trigger *\/ \\n         IF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = ''TR'') \\n             IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = ''TR'') \\n             EXEC(''DROP TRIGGER '' + @fullTriggerName) \\n\\n         COMMIT TRAN\\n         END\\n   ')\\n\\n\/* Create SP for querying all registered table *\/ \\nIF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \\n   IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \\n   EXEC('CREATE PROCEDURE dbo.{5} \\n         AS\\n         SELECT tableName FROM dbo.{0}   ')\\n\\n\/* Create roles and grant them access to SP  *\/ \\nIF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') \\n    EXEC sp_addrole N'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \\n\\nGRANT EXECUTE ON dbo.{1} to aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess\\n\\n\";\n    internal const string SQL_DISABLE_DATABASE = \"\/* Remove notification table *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{0}' AND type = 'U') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{0}' AND type = 'U') \\n    BEGIN\\n      \/* First, unregister all registered tables *\/ \\n      DECLARE tables_cursor CURSOR FOR \\n      SELECT tableName FROM dbo.{0} \\n      DECLARE @tableName AS NVARCHAR(450) \\n\\n      OPEN tables_cursor \\n\\n      \/* Perform the first fetch. *\/ \\n      FETCH NEXT FROM tables_cursor INTO @tableName \\n\\n      \/* Check @@FETCH_STATUS to see if there are any more rows to fetch. *\/ \\n      WHILE @@FETCH_STATUS = 0 \\n      BEGIN \\n          EXEC {3} @tableName \\n\\n          \/* This is executed as long as the previous fetch succeeds. *\/ \\n          FETCH NEXT FROM tables_cursor INTO @tableName \\n      END \\n      CLOSE tables_cursor \\n      DEALLOCATE tables_cursor \\n\\n      \/* Drop the table *\/\\n      DROP TABLE dbo.{0} \\n    END\\n\\n\/* Remove polling SP *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{1}' AND type = 'P') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{1}' AND type = 'P') \\n      DROP PROCEDURE dbo.{1} \\n\\n\/* Remove SP that registers a table *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{2}' AND type = 'P') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{2}' AND type = 'P') \\n      DROP PROCEDURE dbo.{2} \\n\\n\/* Remove SP that unregisters a table *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{3}' AND type = 'P') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{3}' AND type = 'P') \\n      DROP PROCEDURE dbo.{3} \\n\\n\/* Remove SP that querys the registered table *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{4}' AND type = 'P') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{4}' AND type = 'P') \\n      DROP PROCEDURE dbo.{4} \\n\\n\/* Remove SP that updates the change Id of a table. *\/ \\nIF EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = '{5}' AND type = 'P') \\n    IF EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = '{5}' AND type = 'P') \\n      DROP PROCEDURE dbo.{5} \\n\\n\/* Drop roles *\/ \\nIF EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess') BEGIN\\nCREATE TABLE #aspnet_RoleMembers \\n( \\n    Group_name      sysname, \\n    Group_id        smallint, \\n    Users_in_group  sysname, \\n    User_id         smallint \\n) \\nINSERT INTO #aspnet_RoleMembers \\nEXEC sp_helpuser 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess' \\n \\nDECLARE @user_id smallint \\nDECLARE @cmd nvarchar(500) \\nDECLARE c1 CURSOR FORWARD_ONLY FOR  \\n    SELECT User_id FROM #aspnet_RoleMembers \\n  \\nOPEN c1 \\n  \\nFETCH c1 INTO @user_id \\nWHILE (@@fetch_status = 0)  \\nBEGIN \\n    SET @cmd = 'EXEC sp_droprolemember ''aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'',''' + USER_NAME(@user_id) + '''' \\n    EXEC (@cmd) \\n    FETCH c1 INTO @user_id \\nEND \\n \\nclose c1 \\ndeallocate c1 \\n    EXEC sp_droprole 'aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess'\\nEND\\n\";\n    internal const string SQL_QUERY_REGISTERED_TABLES_SP = \"AspNet_SqlCacheQueryRegisteredTablesStoredProcedure\";\n    internal const string SQL_QUERY_REGISTERED_TABLES_SP_DBO = \"dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure\";\n    internal const string SQL_REGISTER_TABLE_SP = \"AspNet_SqlCacheRegisterTableStoredProcedure\";\n    internal const string SQL_REGISTER_TABLE_SP_DBO = \"dbo.AspNet_SqlCacheRegisterTableStoredProcedure\";\n    internal const string SQL_TRIGGER_NAME_POSTFIX = \"_AspNet_SqlCacheNotification_Trigger\";\n    internal const string SQL_UNREGISTER_TABLE_SP = \"AspNet_SqlCacheUnRegisterTableStoredProcedure\";\n    internal const string SQL_UNREGISTER_TABLE_SP_DBO = \"dbo.AspNet_SqlCacheUnRegisterTableStoredProcedure\";\n    internal const string SQL_UPDATE_CHANGE_ID_SP = \"AspNet_SqlCacheUpdateChangeIdStoredProcedure\";\n<\/code><\/pre>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>DSO I am writing a setup program for my ASP.NET web site, and I need to incorporate the aspnet_regsql tool for registering\/unregistering the membership\/roles tables into my database. If I bundle this with my setup, what are all the dependent files I need to include to ensure it all works? Or is it better to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3725","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/3725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=3725"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/3725\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=3725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=3725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=3725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}