European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2012 Hosting Belgium - HostForLIFE.eu :: How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" ?

clock October 15, 2019 12:15 by author Peter

With this short article, I will tell you about How to Fix Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'" on my SQL Server 2012 Hosting.

In the Configure Distribution Wizard, the step "Configuring SQL Server Agent to start automatically" errors with the following text:

TITLE: Configure Distribution Wizard
------------------------------
An error occurred configuring SQL Server Agent.
------------------------------
ADDITIONAL INFORMATION:
RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22002)

This is a very minor error, and not difficult to work around at all. The wizard is making an attempt to alter the SQL Server Agent service "Start Mode" to Automatic. you'll be able to try this via the SQL Server Configuration Manager instead.

In the Sysinternals method Monitor, you'll see: Operation: RegCreateKey Result: ACCESS DENIED Path: "HKLM\System\CurrentControlSet\Services\SQLAgent$SQL2012"

 

If you encounter this error, choose "No" in the "SQL Server Agent Start" page in the configure Distribution Wizard (as shown below), so set your agent service to Automatic start Mode via the SQL Server Configuration Manager.

The third step of the wizard that failed before won't happen. Why the failure truly happens I didn't figure this out, and i am open to feedback, however this seems like a vestigial step to a wizard that otherwise has no negative impact. Running SSMS with "run as Administrator" doesn't seem to fix this error either. i would like to recognize why this error happens within the 1st place.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.



SQL Server 2019 Hosting - HostForLIFE.eu :: Enforcing Foreign Key Constraint In A Multi-Valued Column In SQL Server

clock October 4, 2019 09:04 by author Peter

I have seen that sometimes, a few developers create a multi-valued column to store more than one value in a comma-separated manner (like 1,3,4) and then, they read the individual values by splitting using comma.

However, due to such design, they can't add a foreign key constraint like below.

  • ALTER TABLE <Table-name> 
  • ADD CONSTRAINT <FK-Name> FOREIGN KEY (<col-name>) REFERENCES <Lookup Table-name>(<Lookup col-name>); 

PS - Personally, I am not a fan of such design and I would recommend having a mapping table in such cases; however, at times, mostly on the existing system, you don't have the choice to rewrite or change the design and hence finding a quick fix is the only option.
 
To illustrate the problem and solution, let's take an example of two tables - Employee and Country - as below.
    CREATE TABLE Country ( 
       Id INT NOT NULL PRIMARY KEY, 
       Name varchar(100) NOT NULL, 
       Code varchar(50) NULL 
    ); 
      
    CREATE TABLE Employee ( 
       Id INT NOT NULL PRIMARY KEY, 
       HomeCountryId INT NOT NULL, 
       VisitedCountryIds varchar(200) NULL, 
       Constraint FK_Employee_Country FOREIGN KEY (HomeCountryId) REFERENCES Country(Id) 
    ); 


Let's assume the country id as 1, 2 till 249 (As per the latest data available during the time of writing the post).
 
As you can see there is FK constraint on the HomeCountryId, hence only valid Country Id (from 1-249) can be entered; however, in the field VisitedCountryIds, there is no check and any id (like 250, 251, etc.) can also be added even if it doesn't exist in the country table. Well, this can lead to the data integrity issue.
 
So how we can make sure that users can only enter valid country ids (from 1-249) in the VisitedCountryIds column?
 
The fix is two-fold as following.
 
Create the function in the SQL Server as below.
    CREATE FUNCTION [dbo].[svf_CheckCountryIds](@CountryIds nvarchar(200)) 
    RETURNS bit AS 
    BEGIN 
    declare @valid bit 
    declare @rowsInserted INT 
    declare @addedCountryIds table([CountryId] nvarchar(200)) 
     
    insert into @addedCountryIds 
    select value from STRING_SPLIT(@CountryIds, ',') 
    set @rowsInserted = @@rowcount 
     
    if (@rowsInserted = (select count(a.CountryId) from @addedCountryIds a join [Country] b on a.CountryId = b.Id)) 
    begin 
    set @valid = 1 
    end 
    else 
    begin 
    set @valid = 0 
    end 
     
    RETURN @valid 
    END 


As you can see in the above function, we are passing the column data that is in the comma concatenated form and then they are split using STRING_SPLIT function and stored in the addedCountryIds table variable. Also, the inserted row count is stored in the rowsInserted variable.
 
Later, the values on addedCountryIds arejoined with Country table and if the count is matching, i.e., if all the passed country id is present in the Country table, true/1 is returned else false/0 is returned.
 
Create the FK with check constraint on the VisitedCountryIds as follows,
    ALTER TABLE Employee 
    ADD CONSTRAINT [FK_Employee_VisitedCountryIds] CHECK ([dbo].[svf_CheckCountryIds]([VisitedCountryIds]) = 1) 


As you can see constraint FK_Employee_VisitedCountryIds is created on VisitedCountryIds with condition that function svf_CheckCountryIds should return value as 1/true.
Now when you enter any country id other than 1 to 249, for example, if you enter VisitedCountryIds as '103,236,250', an error will be thrown as follows as id 250 is not the part of the country id list.
 
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "FK_Employee_VisitedCountryIds". The conflict occurred in database "TestDb", table "dbo.Employee", column 'VisitedCountryIds'.
The statement has been terminated.
 
However, if you enter VisitedCountryIds as '103,236,249', it will be successfully inserted because all the ids are part of the country list.
 
I hope you found this post useful in handling the foreign keys in multivalued columns. Looking forward to your comments.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in