Database

USE [B_D_DashPanel]
GO
/****** Object:  StoredProcedure [dbo].[sp_BD_PermConflicts]    Script Date: 09/10/2012 15:55:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
AS


Declare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)

Create TABLE #Chas (
Chassis_No nvarchar(50)
)

set nocount on

insert into #Chas (Chassis_No) values (@Chassis_Search)


insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel, 
itembompn.partnumber1
from #Chas,  workplacementorder 
join itembompn 
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit 
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr 
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_No



SELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder join b_d_Dashpanel.dbo.items on b_d_Dashpanel.dbo.workplacementorder.itemcode = b_d_Dashpanel.dbo.items.itemcode
where salesorderno = Chassis_No and b_d_DashPanel.dbo.workplacementorder.itemcode not in (select itemc from @Data)



drop table #Chas

IF @@SERVERNAME <> 'Server1'
  BEGIN
      EXEC SP_ADDLINKEDSERVER
        'Server1'

      EXEC SP_ADDLINKEDSRVLOGIN
        'Server1',
        'False',
        NULL,
        'userX',
        'passwordX'
  END

go

DECLARE @mode VARCHAR(1);
DECLARE @orderNum INT;

SET @orderNum = 1338464;
SET @mode = 'R' -- set to 'R' for Report or 'U' for update
BEGIN TRANSACTION

DECLARE @sourceName VARCHAR(8);
DECLARE @okay CHAR(1);
DECLARE @user VARCHAR(20);

SET @sourceName = 'liveDB';
SET @user = SYSTEM_USER;

CREATE synonym sourcesummary FOR [Server1].[liveDB].[dbo].[order_summary];
SET @okay = 'Y'

IF @okay = 'Y'
   AND @@SERVERNAME = 'Server1'
  BEGIN
      SELECT 'Cannot execute on live server - retry on the sever hosting the db that is to be copied to' [error]

      SET @okay = 'N'
  END

IF @okay = 'Y'
   AND NOT EXISTS (SELECT order_num
                   FROM   sourcesummary
                   WHERE  order_num = @orderNum)
  BEGIN
      SELECT 'Unable to find order' [error],@sourceName [source db],@orderNum [order number];

      SET @okay = 'N';
  END

IF @okay = 'Y'
  BEGIN
      IF EXISTS (SELECT order_num
                 FROM   order_summary
                 WHERE  ordr_num = @orderNum)
        BEGIN
            PRINT 'deleting order_summary:';

            DELETE order_summary
            WHERE  order_num = @orderNum;
        END

      BEGIN

	  PRINT 'copying order_summary:';
          INSERT INTO order_summary
          SELECT [order_num],[order_date], etc, etc, etc...
          FROM   sourcesummary
          WHERE  sourcesummary.order_num = @orderNum;
      END

	  PRINT 'retreiving copied order_summary:';
      SELECT 'copied' [order_summary],*
      FROM   order_summary
      WHERE  order_num = @orderNum;
  END

DROP synonym sourcesummary;

IF @mode = 'U'
  BEGIN
      COMMIT TRANSACTION

      SELECT 'Changes have been applied to the database' [notice]
  END
ELSE
  BEGIN
      ROLLBACK TRANSACTION

      SELECT 'All changes have been rolled back' [notice]
  END 

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'Server1' already exists.
deleting order_summary:

(1 row(s) affected)
copying order_summary:

(1 row(s) affected)
retreiving copied order_summary:

(0 row(s) affected)

(1 row(s) affected)