Category Archives: SQL Server

SharePlex Conflict Resolution

Leave a Reply

To resolve conflicts for SharePlex you need to setup the conflict resolution methods. In additions all tables involved must have a primary key. Also it is commonly a good idea for each table to have a last updated and last site field. These can be used by the replication to work out what is the latest or best update to take.

Triggers can be used including to update the last updated and site fields. However every trigger that could be touched by the replication requires a wrapper e.g. when user !=’SPLEX’

For deletes you may need to set the SP_OPO_DEPENDENCY_CHECK to 2 which will not report out of sync when delete cascades are involved.

Setting up the latest timestamp as a winner

1. activate config on both servers

2. set up these parameters on both servers

sp_ctrl> stop capture
sp_ctrl> stop post
sp_ctrl> set param SP_OCT_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_DEPENDENCY_CHECK 2 —- for support of delete cascade
sp_ctrl> set param SP_OPO_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_SUPPRESSED_OOS 0
sp_ctrl> start capture
sp_ctrl> start post

3. stop post on both servers

4. Create same entries in conflict_resolution.SID file under var dir/data on both servers, make sure the timestamp column is specified in UPPER CASE. You must choose the same method (either !MostRecentRecord or !LeastRecentRecord) on both servers.

Example:
for latest timestamp as winner:

owner.table_name IUD !MostRecentRecord(TIMESTAMPCOL)

OR for earliest timestamp as winner

onwer.table_name IUD !LeastRecentRecord(TIMESTAMPCOL)

OR you can specify a DML type such as insert with one of the time based pre-built routine such as earliest timestamp as winner for insert

onwer.table_name I !LeastRecentRecord(TIMESTAMPCOL)

5. start post on both servers

To test conflict resolution, stop export on both servers and insert or update data with same PK to invoke conflict and restart export on both servers to verify results. For time based conflict resolution, the timestamp column must be updated during update or having value during insert as it is used to determine the priority for resolving conflict.

See attached document for example on how to set up default conflict resolution based on Time Priority.

Setting up a site as winner

You can also setup a site as the winner in a situation where there is a conflict. This can also be setup with the timestamp as well so you can tell Shareplex to check timestamp and if the same use the site. This is my usual pattern.

1. activate config on both servers
2. set up these parameters on both servers

sp_ctrl> stop capture
sp_ctrl> stop post
sp_ctrl> set param SP_OCT_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_DEPENDENCY_CHECK 2
sp_ctrl> set param SP_OPO_REDUCED_KEY 0
sp_ctrl> set param SP_OPO_SUPPRESSED_OOS 0
sp_ctrl> start capture
sp_ctrl> start post
3. stop Post on both servers
4. create entries in conflict_resolution.SID file under var dir/data on both servers
owner.table_name IUD !HostPriority
5. set this parameter to the same host server that has the priority, meaning data sent from this host will always be the winner for conflict resolution, enter exactly the host_name from querying v$instance on the priority host and name is case sensitive.
sp_ctrl> set param SP_OPO_TRUSTED_SOURCE 6. start Post on both servers

Entity Framework Scaffold DBContext (ASP.NET Core)

Leave a Reply

Steps:

– Install EF:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

– Add to project.json

 "tools": {
   "Microsoft.EntityFrameworkCore.Tools.DotNet": "1.0.0-preview3-final",
   "Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final",
   "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
 },

– From “NuGet Package Manager” then “Package Manager Console”

Scaffold-DbContext "Server=;Database=;user id=;password=;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=False" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

– Update appsettings.json to include the connection string..

  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=devsql01;Database=nasr_scratch;user id=dev;password=Automation15;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=False;"
    } 
  },

– Add the DbContext to the Startup.cs using the connection string..

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.            
            services.AddMvc();
            services.AddDbContext(
                o => o.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
        }

Update the Context:

public class MyContext : DbContext
{
    public MyContext(DbContextOptions options)
      :base(options)
    { }
}

– Create your controller. For Instance; BulkTagCreationController. You can now reference the context using DI. 

    [Route("api/configuration/[controller]")]
    public class BulkTagCreationController : Controller         
    {
        public readonly nasr_scratchContext _context;

        public BulkTagCreationController(nasr_scratchContext context)
        {
            _context = context;
        }

SQL Server CATCH block

Leave a Reply

Just like in Oracle SQL server allows the use of a catch block which can be very useful for debugging stored procedures!

First set up a transaction:

IF @@TRANCOUNT = 0
BEGIN TRANSACTION B4UPDATE
ELSE
SAVE TRANSACTION B4UPDATE

Then catch any errors and rollback if needed saving data about the issue.  If you also keep a variable with a step number you can also output this to an error table giving you the position where the code crunched!

BEGIN CATCH
ROLLBACK TRANSACTION B4UPDATE;
INSERT INTO ERRORS_SQL (TIMESTAMP, PROC_NAME, COMMENTS) VALUES (GETDATE(),‘BADGE_ALLOCATE_USEID’,‘errno: ‘ + ltrim(str(error_number())) + ‘ errmsg: ‘ + error_message())
COMMIT
END CATCH

SQL Server to_char conversion

Leave a Reply

When converting from oracle to SQL server you may struggle with Oracle to_char() and to_date() convertions.  SQL server handles this differently.  You have to used fixed types or you can use some clever madness.

For YYYYMMDDHH24MISS :

(SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), ‘-‘, ”), ‘T’, ”), ‘:’, ”))

For DD/MM/YYYY HH24:MI:SS :

(select CONVERT(VARCHAR(10), GETDATE(), 103) + ‘ ‘ + convert(VARCHAR(8), GETDATE(), 14))