Feb 8

Written by: Richard Waddell
2/8/2013 11:51 PM 

LightSwitch provides for creating Data Sources in several ways. In the case of an existing database you would typically either specify the database itself or a WCF RIA Service.

But sometimes neither is feasible. I was confronted with a database with no primary keys and incredibly wide tables – so wide that I got error messages about it. And you can’t really do much with a database that has no primary keys. Also modifying the schema was not an option.

This actually turned out to be quite liberating. The reason we wanted to access the database outside of the legacy application it was designed for was to

• Provide views of the data that the legacy application did not.
• Add additional columns

By extracting the data into another database we could

• Eliminate literally hundreds of columns (Yes, I do mean literally)
• Create primary keys
• Create relationships with referential integrity through foreign keys

So what does the legacy database look like? Sorry, that’s proprietary, or at least I’m not taking any chances. Fortunately, it just doesn’t matter, the principles are the same for any situation. So I made up a very simplistic database I call ShoppingCenterMgmt. The fictional "Shopping Center Management” application it supports is so old that going to the mall was unknown unless you were in Washington D.C. You can’t get much more legacy than that.

It consists of three tables

• shopping_centers
• stores
• customers

Each shopping center exists at a specific physical location and contains one or more stores, which is identified by unit within shopping_center_id. If a store currently has an occupant it’s indicated by an entry in the customer_id field.

As you can see there are no primary keys, let alone foreign keys, therefore no referential integrity. The only thing the DBMS will prevent is duplicate unique keys. There’s nothing to prevent deletion of shopping centers that have stores or customers who currently occupy stores. Or you could add a store with an invalid shopping_center_id or customer_id. The opportunities are endless.

So we would like to not only capture the data in the columns shown above for these tables, but we would also like to add primary and foreign keys so we can achieve referential integrity and everything that goes with it. The first thing we need are target tables to import into.

The schema for the shopping_centers table looks like this:

We’ll create a table named Malls to receive the data.

We retain shopping_center_id as a unique index and add a Summary field which I’ll explain later.

Before moving on to the other tables, lets look at how the import works. We could write a stored procedure that specifically handles copying data from the shopping_centers table to the Malls table, but in a real-world scenario we’ve got several tables and a lot of columns so that will get pretty tedious. Far better to create tables to hold the desired table and column names and the screens to enter them.

It’s pretty simple, really, if you just take it one step at a time. To start with, we know we have to specify the name of the legacy table name, so we begin with that.

To begin with we just provide a column for the table name and require that it be unique.

We create a table name LegacyShadowTables for the target table names.

Now we need a way to pair up each legacy table with the appropriate shadow table.

This creates a many-to-many relationship between LegacyTable and LegacyShadowTable. Although our mapping will always be one-to-one, it doesn’t have to be.

Now we add a computed property ‘Tables’ to LegacyShadowTablePair.

public partial class LegacyShadowTablePair
{
    partial void Tables_Compute(ref string result)
    {
        result = this.LegacyTable != null ? this.LegacyTable.ToString() : "?";
        result += " -> ";
        result += this.LegacyShadowTable != null ? this.LegacyShadowTable.ToString() : "?";
    }
}

NOTE: Since I’m still running under debug, all the data I’m entering over the next few screens will be going into the debug database. This means it will have to be re-entered into the ‘real’ database after publishing. There’s not very much, but you may want to wait until then to enter it.

Starting with the Legacy Tables List Detail screen, we enter the first name, shopping_centers.

On the Legacy Shadow Tables List Detail screen, enter the  first shadow table name, Malls.

And create our first shadow table pair:

You’ve probably noticed there’s no validation of the table names that are entered. That’s more trouble than it’s worth In this initial effort to get the import up and running. Any errors in table or column names will result in exceptions that will be logged when the stored procedure runs.

Here are some examples from when I was debugging my attempts to get the procedure working:

Ok, we have our first pair defined and three convenient screens for visualizing the relationships:

Next we need to specify the column mappings between the tables.

Each Leg

acyShadowTablePair has multiple pairs of column mappings, so there’s a one-to-many relationship between LegacyShadowTablePair and LegacyShadowColumnPair. To prevent duplicating the column pairs for a particular table pair, LegacyShadowTablePair, LegacyTableColumn, and ShadowTableColumn are specified as ‘Include in Unique Index’. The Columns computed property is analogous to the ‘Tables’ computed column in the LegacyShadowTablePair table.

We can add a LegacyShadowColumnPair screen to enter the column names, but it’s far more convenient to use the Legacy Shadow Table Pair List Detail Screen. To do so you’ll need to modify the screen by Adding the Legacy Shadow Column Pairs collection to the screen

Click the line shown above, then drag the resulting query onto the screen.

Now we can maintain the column names by table pairs.

At this point we’re going to need to publish so we have a SQL database to import the data into. Strictly speaking you could use a different database as the target and continue to

run LightSwitch under debug, but if you want to use the LightSwitch database for the data then you have to publish now.

We’re going to set Access Control to ‘Use Forms Authentication’. This will cause the publishing process to create a Site Administrator logon.

You publish from the Application Type selection.

We’re hosting on IIS Server and I have the Prerequisites installed, which I recommend:

Note: If you have problems try unchecking the ‘IIS Server has the LightSwitch Deployment Prerequisites installed’. I was tearing my hair out last night trying to figure out why my Application Administrator settings were not being applied. I still don’t know if having this checked was the cause, because now it works fine both ways.

I publish directly to the server whenever possible:

HTTPS off, unless you want it on:

The easiest approach is to create the new database during the publish process. The User ID must be a login that serves in public and sysadmin server roles. As a note of interest, after publishing, if you take a look with SSMS, the database owner will show as dbo. If you look at the mapping for the login you specified, you will see that for the database you created, dbo maps to that login.

Application Administrator fields. You only have to do this once:

Note: If your credentials don’t work when you try to log in take a look at web.config as shown below. The comment “<!—If no admin user exists…” is incorrect. It should say, “<!—If no admin user with this user name exists…”. Don’t bother looking in the database right after you publish – it’s only during the first log on after you publish that these settings are used to update the database.

<configuration>
  <appSettings>
   ...    
    <!-- If no admin user exists, create an admin user with this user name -->
    <add key="Microsoft.LightSwitch.Admin.UserName" value="MallsMgr2" />
    <!-- When creating an admin user with Forms auth, use this as the full name -->
    <add key="Microsoft.LightSwitch.Admin.FullName" value="MallsMgr2 Name" />
    <!-- When creating an admin user with Forms auth, use this as the pasword -->
    <add key="Microsoft.LightSwitch.Admin.Password" value="MallsMgr12345/" />
    ...
  </appSettings>
...
</configuration>

Create a test certificate:

Your summary screen should look something like this:

Go ahead and click on the Publish button.

NOTE: If you have problems publishing check out this Beth Massi article. It’s from 6/8/2012 but seems to have the most recent information that isn’t just a description of each Wizard step, such as this article, helpful to 0 out of 1. Sigh.

Let’s start simple by writing a simple stored procedure to just copy the rows from the shopping_centers table to the Malls table:

USE [LS_Malls3]
GO
/****** Object:  StoredProcedure [dbo].[uspCopy_ShoppingCentersToMallsTable]    Script Date: 02/03/2013 09:17:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspCopy_ShoppingCentersToMallsTable] 
AS
BEGIN
    SET NOCOUNT ON;
    
	BEGIN TRY
		DELETE Malls
		INSERT INTO Malls (shopping_center_id, name, addr1, addr2, city, state, zip_code)
		SELECT shopping_center_id, name, addr1, addr2, city, state, zip_code
		FROM [ShoppingCenterMgmt].[dbo].[shopping_centers]
    END TRY
	BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        EXECUTE [dbo].[uspLogProcError];
	END CATCH
END;

There are two procedures to handle error reporting. I can’t remember where I got them, it may have been from here:

USE [LS_Malls3]
GO
/****** Object:  StoredProcedure [dbo].[uspPrintError]    Script Date: 02/03/2013 09:23:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- uspPrintError prints error information about the error that caused 
-- execution to jump to the CATCH block of a TRY...CATCH construct. 
-- Should be executed from within the scope of a CATCH block otherwise 
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError] 
AS
BEGIN
    SET NOCOUNT ON;
    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;

USE [LS_Malls3]
GO
/****** Object:  StoredProcedure [dbo].[uspLogProcError]    Script Date: 02/03/2013 09:24:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogProcError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;
    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;
    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;
        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLog_FAST_Error in order to successfully log error information.';
            RETURN;
        END
        INSERT [dbo].[ProcErrorLog] 
            (
            [ErrorTime],
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            GETDATE(),
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );
        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

And an error log table:

We’ll create a test row by manually inserting one into the shopping_centers table:

Then manually execute the stored procedure:

And the results:

Which can be seen in the Malls List Detail screen:

Since we’re deleting the contents of the Malls table before we do the inserts, we can run the stored procedure over and over again. But let’s see what happens if we take out the Delete and try to run it more than once.

The return value from the procedure is now –4:

And the  Messages tab shows that we’re trying to insert a duplicate in the Malls table.

Here’s the row in ProcErrorLog:

We can add the ProcErrorLog table to LightSwitch:

And a screen to view it:

Now we’ll replace the insert with dynamic sql using hard-coded values:

USE [LS_Malls3]
GO
/****** Object:  StoredProcedure [dbo].[uspCopy_ShoppingCentersToMallsTable]    Script Date: 02/03/2013 10:43:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspDynamicCopy_ShoppingCenters1] 
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @CR NVARCHAR(1) = CHAR(13)
	DECLARE @DIVIDER NVARCHAR(25) = @CR + '-------------------------' 
	DECLARE @debug bit = 0
	DECLARE @sql nvarchar(MAX);
	DECLARE @targetTableName nvarchar(255) = N' Malls '
	DECLARE @theTargetColumns nvarchar(MAX);
	DECLARE @theSourceColumns nvarchar(MAX);
	DECLARE @FROM_CLAUSE nvarchar(MAX) = N' FROM [ShoppingCenterMgmt].[dbo].[shopping_centers] '
    
	BEGIN TRY
		DELETE Malls
		Set @theTargetColumns = N' shopping_center_id, name, addr1, addr2, city, state, zip_code ';
		Set @theSourceColumns = N' shopping_center_id, name, addr1, addr2, city, state, zip_code '; 
		
        SET @sql = N' INSECT ' + @targetTableName + @CR +
        N' ( ' + @theTargetColumns + N' ) ' + @CR +
        N' SELECT ' + @theSourceColumns + @CR +
        @FROM_CLAUSE
        
		----------- DEBUG --------------
		IF @debug = 1 PRINT 'Insert: ' + @DIVIDER + @CR + @sql + @DIVIDER + @CR
		---------------------------------
        EXEC sp_executesql @sql
		
    END TRY
	BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];
        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        EXECUTE [dbo].[uspLogProcError];
        RETURN -1;
	END CATCH
END;

When we execute the procedure, we get a Return Value of –1, indicating an error:

The Messages tab is not much help:

Fortunately, we can use PRINT to see more. If you look near the beginning of the stored procedure you’ll see

	DECLARE @debug bit = 0

Further down, after @sql has been formatted you see

		----------- DEBUG --------------
		IF @debug = 1 PRINT 'Insert: ' + @DIVIDER + @CR + @sql + @DIVIDER + @CR
		---------------------------------

So if we set @debug to 1 instead of zero and run the procedure again we can see a display of the T-SQL code executed by sp_executesql, resulting in the Incorrect syntax error.

As you can see, we mis-spelled INSERT, so we fix it and run it again.

The code looks right and we don’t get any errors when it’s executed.

Ok, we have some working dynamic sql and we can use PRINT to debug errors. Now let’s start making it table driven instead of just plugging in hard-coded values. First we’ll add another table pair to make it a little more interesting. We add another legacy table, stores.

Our Malls don’t have any mundane old stores, they have Shops

So we’re mapping the stores table from out tired old shopping_centers database to the Shops table in our new, scintillating LS_Malls3 database. (The database names can also be table-driven, but for now we just hard-code them).  Below we see that we’ve paired up stores with Shops.

Let’s take a look at the actual tables.

You can see below that the foreign keys in the table pair table point to the appropriate rows in the LegacyTables and LegacyShadowTables tables.

And the foreign key in the LegacyShadowColumnPairs table points to the approriate row in the LegacyShadowTablePairs table. As I mentioned before, each LegacyShadowTablePairs row can point to multiple LegacyShadowColumnPairs rows.

So for our first task, let’s get the table names and id in the first LegacyShadowTablePairs row. Later we’ll do this in a loop, but for now we’ll keep it simple by dealing with the first pair we encounter.

SELECT Top 1 @shadowTablePairId = pairs.Id, @sourceTableName = LegacyTables.Name,  @targetTableName = LegacyShadowTables.Name
FROM LegacyShadowTablePairs pairs
Inner Join LegacyTables on LegacyTables.Id = pairs.LegacyShadowTablePair_LegacyTable
Inner Join LegacyShadowTables on LegacyShadowTables.Id = pairs.LegacyShadowTablePair_LegacyShadowTable
------------- DEBUG --------------
IF @debug = 1 PRINT @DIVIDER + @CR + N'ShadowTablePairID: ' + Convert(nvarchar(5), @shadowTablePairId) + @CR +
N'SourceTable: ' + @sourceTableName + @CR +
N'TargetTable: ' + @targetTableName + @LINE_DIVIDER
-----------------------------------

Resulting in:

Now get the column names:

DECLARE @ColumnNames AS Table
(
	ID int Primary Key Identity(1,1),
	SourceColumnName nvarchar(255),
	TargetColumnName nvarchar(255)
)
-- Assume this code will be used in an inner loop and clear the table just before you insert into it.
-- Otherwise you'll regret it sooner or later.
DELETE @ColumnNames
-- Insert into the table variable from the column pair rows that point to the table pair row we just read
INSERT Into @ColumnNames (SourceColumnName, TargetColumnName)
Select LegacyTableColumn, ShadowTableColumn FROM LegacyShadowColumnPairs columnPairs
WHERE columnPairs.LegacyShadowColumnPair_LegacyShadowTablePair = @shadowTablePairId
-- Set the starting index to the lowest ID (primary key) of the table and the maximum index to the highest. Don't
-- try to use 1 as the starting value. If you use this table variable in a loop, the ID will just keep incrementing
-- even if you clear the table by deleting it.
SELECT @ColumnTableIndex = MIN(ID) FROM @ColumnNames
SELECT @MaxColumnTableIndex = MAX(ID) FROM @ColumnNames		
-- Progress through the rows
WHILE @ColumnTableIndex <= @MaxColumnTableIndex
BEGIN
	DECLARE @sourceColumn nvarchar(255);			
	DECLARE @targetColumn nvarchar(255);
	SELECT @sourceColumn = SourceColumnName,  @targetColumn = TargetColumnName
	FROM @ColumnNames 
	WHERE ID = @ColumnTableIndex
	SET @theSourceColumns = @theSourceColumns + quoteName(@sourceColumn)
	SET @theTargetColumns = @theTargetColumns + quoteName(@targetColumn)
	-- Append a comma to the list of column names if we're not on the last one
	IF (@ColumnTableIndex < @MaxColumnTableIndex)
	BEGIN
		SET @theSourceColumns = @theSourceColumns + N', ';
		SET @theTargetColumns = @theTargetColumns + N', ';
	END
	SET @ColumnTableIndex = @ColumnTableIndex + 1
END

Build the sql code and execute it:

-- Build the sql code to insert the rows		
SET @sql = N' INSERT ' + @targetTableName + @CR +
N' ( ' + @theTargetColumns + N' ) ' + @CR +
N' SELECT ' + @theSourceColumns + @CR +
@FROM_CLAUSE
------------- DEBUG --------------
IF @debug = 1 PRINT 'Insert: ' + @LINE_DIVIDER + @sql + @LINE_DIVIDER
-----------------------------------
EXEC sp_executesql @sql

Check the results:

Ok, that’s the end of part 1. Lots more to come,

• Specifying the unique index so we can update existing records instead of having to clear the shadow tables every time. After all, we are going to add columns and we don’t want to lose them.
• Creating foreign key relationships.
• Testing the shadowed columns to see if any have actually changed. If we update blindly we’ll end up with unnecessary concurrency messages in LightSwitch, and ultimately we’re going to fire off the procedure once a minute.
• Firing off the procedure from LightSwitch
• Logging detail information when we fire manually and summary information when we run once a minute
• Detecting rows in the legacy table or shadow table that don’t have corresponding rows on the other side.

For more information on Dynamic SQL I recommend this article by Erland Sommarskog

Here’s the complete stored procedure up to now:

USE [LS_Malls3]
GO
/****** Object:  StoredProcedure [dbo].[uspDynamicCopy_ShoppingCenters2]    Script Date: 02/08/2013 19:26:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspDynamicCopy_ShoppingCenters2] 
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @CR NVARCHAR(1) = CHAR(13)
	DECLARE @DIVIDER NVARCHAR(25) = '-------------------------' 
	DECLARE @LINE_DIVIDER NVARCHAR(50) = @CR + @DIVIDER + @CR
	DECLARE @debug bit = 1
	DECLARE @sql nvarchar(MAX);
	DECLARE @shadowTablePairId int;
	DECLARE @targetTableName nvarchar(255);
	DECLARE @sourceTableName nvarchar(255);
	DECLARE @theTargetColumns nvarchar(MAX) = N'';
	DECLARE @theSourceColumns nvarchar(MAX) = N'';
	DECLARE @ColumnTableIndex int;
	DECLARE @MaxColumnTableIndex int;
	DECLARE @FROM_CLAUSE nvarchar(MAX) = N' FROM [ShoppingCenterMgmt].[dbo].[shopping_centers] '
    
	BEGIN TRY
		DELETE Malls
		
		SELECT Top 1 @shadowTablePairId = pairs.Id, @sourceTableName = LegacyTables.Name,  @targetTableName = LegacyShadowTables.Name
		FROM LegacyShadowTablePairs pairs
		Inner Join LegacyTables on LegacyTables.Id = pairs.LegacyShadowTablePair_LegacyTable
		Inner Join LegacyShadowTables on LegacyShadowTables.Id = pairs.LegacyShadowTablePair_LegacyShadowTable
		------------- DEBUG --------------
		IF @debug = 1 PRINT @DIVIDER + @CR + N'ShadowTablePairID: ' + Convert(nvarchar(5), @shadowTablePairId) + @CR +
		N'SourceTable: ' + @sourceTableName + @CR +
		N'TargetTable: ' + @targetTableName + @LINE_DIVIDER
		-----------------------------------
		-- Save the column names in a table variable
		DECLARE @ColumnNames AS Table
		(
			ID int Primary Key Identity(1,1),
			SourceColumnName nvarchar(255),
			TargetColumnName nvarchar(255)
		)
		-- Assume this code will be used in an inner loop and clear the table just before you insert into it.
		-- Otherwise you'll regret it sooner or later.
		DELETE @ColumnNames
		-- Insert into the table variable from the column pair rows that point to the table pair row we just read
		INSERT Into @ColumnNames (SourceColumnName, TargetColumnName)
		Select LegacyTableColumn, ShadowTableColumn FROM LegacyShadowColumnPairs columnPairs
		WHERE columnPairs.LegacyShadowColumnPair_LegacyShadowTablePair = @shadowTablePairId
		-- Set the starting index to the lowest ID (primary key) of the table and the maximum index to the highest. Don't
		-- try to use 1 as the starting value. If you use this table variable in a loop, the ID will just keep incrementing
		-- even if you clear the table by deleting it.
		SELECT @ColumnTableIndex = MIN(ID) FROM @ColumnNames
		SELECT @MaxColumnTableIndex = MAX(ID) FROM @ColumnNames		
		-- Progress through the rows
		WHILE @ColumnTableIndex <= @MaxColumnTableIndex
		BEGIN
			DECLARE @sourceColumn nvarchar(255);			
			DECLARE @targetColumn nvarchar(255);
			SELECT @sourceColumn = SourceColumnName,  @targetColumn = TargetColumnName
			FROM @ColumnNames 
			WHERE ID = @ColumnTableIndex
			SET @theSourceColumns = @theSourceColumns + quoteName(@sourceColumn)
			SET @theTargetColumns = @theTargetColumns + quoteName(@targetColumn)
			-- Append a comma to the list of column names if we're not on the last one
			IF (@ColumnTableIndex < @MaxColumnTableIndex)
			BEGIN
				SET @theSourceColumns = @theSourceColumns + N', ';
				SET @theTargetColumns = @theTargetColumns + N', ';
			END
			SET @ColumnTableIndex = @ColumnTableIndex + 1
		END
		SELECT @theSourceColumns
		SELECT @theTargetColumns
		
		-- Build the sql code to insert the rows		
		SET @sql = N' INSERT ' + @targetTableName + @CR +
		N' ( ' + @theTargetColumns + N' ) ' + @CR +
		N' SELECT ' + @theSourceColumns + @CR +
		@FROM_CLAUSE
		------------- DEBUG --------------
		IF @debug = 1 PRINT 'Insert: ' + @LINE_DIVIDER + @sql + @LINE_DIVIDER
		-----------------------------------
		EXEC sp_executesql @sql
		
    END TRY
	BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];
        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
        EXECUTE [dbo].[uspLogProcError];
        RETURN -1;
	END CATCH
END;