avoid using BCP for snapshot

sql2k sp3
Howdy kids. So, now that management has made up they're
minds(again, pulling my hair out now) Im back to part of
where I was yesterday. At least since then though Ive
learned a bit more about Replication and what will be
required to make it work in my environment.
Well you've all heard this first part a few times now. The
Subscriber has more columns than the Publisher. Im now
back to needing to do a snapshot to populate the
Subscriber. I can write Subscriber Insert and Update procs
to handle individual data modifications AFTER the initial
snapshot is done. But what about that initial snapshot? It
gets BCP'd into the Subscriber table which wont work here
beacuase of the different schemas. I know how to use
format files for normal BCP but it wont work for this
scenario. So, how to I do that initial snapshot without
using BCP? Im guessing DTS but am hoping to be able to use
the procs I have to create anyways to accomodate for the
different schema unless DTS is a whole lot faster?
TIA, ChrisR
post the schemas for the table on the publisher and subscriber here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. So, now that management has made up they're
> minds(again, pulling my hair out now) Im back to part of
> where I was yesterday. At least since then though Ive
> learned a bit more about Replication and what will be
> required to make it work in my environment.
> Well you've all heard this first part a few times now. The
> Subscriber has more columns than the Publisher. Im now
> back to needing to do a snapshot to populate the
> Subscriber. I can write Subscriber Insert and Update procs
> to handle individual data modifications AFTER the initial
> snapshot is done. But what about that initial snapshot? It
> gets BCP'd into the Subscriber table which wont work here
> beacuase of the different schemas. I know how to use
> format files for normal BCP but it wont work for this
> scenario. So, how to I do that initial snapshot without
> using BCP? Im guessing DTS but am hoping to be able to use
> the procs I have to create anyways to accomodate for the
> different schema unless DTS is a whole lot faster?
> TIA, ChrisR
|||Chris,
the fastest way is BCP out the data, transfer it over to teh subscriber,
create the table there and then use BULK INSERT on the subscriber.
Alternatively, to make things easier, I would create the table on the
subscriber and use the view mentioned in your earlier post with a linked
server to insert the records. DTS would do the same thing if you used an
ExecuteSQL task. However if you are concerned about errors on the insert,
then I would use DTS - the transform data task with error logging.
HTH,
Paul Ibison
|||the fastest way is to dts it over. with bcp you have to bcp into the file
system and then bcp from the file system to the server.
for a single table with DTS is is blasted over as a rowset. For multiple
tables it is bcp'd into the file system and then bcp'd from the file system
to the server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uZ9DqxIhEHA.1568@.TK2MSFTNGP09.phx.gbl...
> Chris,
> the fastest way is BCP out the data, transfer it over to teh subscriber,
> create the table there and then use BULK INSERT on the subscriber.
> Alternatively, to make things easier, I would create the table on the
> subscriber and use the view mentioned in your earlier post with a linked
> server to insert the records. DTS would do the same thing if you used an
> ExecuteSQL task. However if you are concerned about errors on the insert,
> then I would use DTS - the transform data task with error logging.
> HTH,
> Paul Ibison
>
|||Also included Insert proc for Subscriber. Is there a way I
can just use that IF its as quick as DTS?
--Publisher
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Subscriber
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Insert proc on Subscriber
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure sp_msIns_TransDtl
@.TransDtlKey int ,
@.CustomerKey int ,
@.SerialNbr char (10) ,
@.TranCode char (4) ,
@.TransDate smalldatetime ,
@.TransAmt money ,
@.RefNbr char (23) ,
@.MerchName varchar (25) ,
@.City varchar (15) ,
@.State varchar (3) ,
@.RejectReason varchar (15) ,
@.PostDate datetime ,
@.CreateDate datetime ,
@.MerchSIC char (4)
as
declare
@.TransDateShort char (10) ,
@.TransDateMonth tinyint ,
@.TransDateYear smallint ,
@.PostDateShort char (10) ,
@.PostDateMonth tinyint ,
@.PostDateYear smallint
select @.TransDateShort = Convert(varchar(10), @.TransDate,
101)-- from TransDTL
select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
select @.TransDateYear = Year(@.TransDate)-- from TransDTL
select @.PostDateShort = Convert(varchar(10), @.PostDate,
101)-- from TransDTL
select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
select @.PostDateYear = Year(@.PostDate)-- from TransDTL
insert into TransDTL
(
TransDtlKey ,
CustomerKey ,
SerialNbr ,
TranCode ,
TransDate ,
TransDateShort ,
TransDateMonth ,
TransDateYear ,
TransAmt ,
RefNbr ,
MerchName ,
City ,
State ,
RejectReason ,
PostDate ,
PostDateShort ,
PostDateMonth ,
PostDateYear ,
CreateDate ,
MerchSIC
)
values
(
@.TransDtlKey ,
@.CustomerKey ,
@.SerialNbr ,
@.TranCode ,
@.TransDate ,
@.TransDateShort ,
@.TransDateMonth ,
@.TransDateYear ,
@.TransAmt ,
@.RefNbr ,
@.MerchName ,
@.City ,
@.State ,
@.RejectReason ,
@.PostDate ,
@.PostDateShort ,
@.PostDateMonth ,
@.PostDateYear ,
@.CreateDate ,
@.MerchSIC
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

>--Original Message--
>post the schemas for the table on the publisher and
subscriber here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
The[vbcol=seagreen]
procs[vbcol=seagreen]
initial[vbcol=seagreen]
It[vbcol=seagreen]
here[vbcol=seagreen]
use
>
>.
>
|||Thanks Paul. I dont think Bulk Insert would work because
of the schema differences. Even with format files. Look @.
the insert proc in my reply to Hilary and it will make
more sense.
Thanks again.

>--Original Message--
>Chris,
>the fastest way is BCP out the data, transfer it over to
teh subscriber,
>create the table there and then use BULK INSERT on the
subscriber.
>Alternatively, to make things easier, I would create the
table on the
>subscriber and use the view mentioned in your earlier
post with a linked
>server to insert the records. DTS would do the same thing
if you used an
>ExecuteSQL task. However if you are concerned about
errors on the insert,
>then I would use DTS - the transform data task with error
logging.
>HTH,
>Paul Ibison
>
>.
>
|||Fair point - I'd done things like this because we also saved audit copies of
tables. The bcp files were zipped up before transferring, but I assume that
DTS is still faster in this case.
Finally, Insert Into... Select... using a linked server directly is
presumably faster?
Regards,
Paul Ibison
|||I am not sure about this. I'd have to test it. You can set up a DTS package
so that it will do a non logged insert which should be faster than the
insert select which is logged.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3mklsJhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> Fair point - I'd done things like this because we also saved audit copies
of
> tables. The bcp files were zipped up before transferring, but I assume
that
> DTS is still faster in this case.
> Finally, Insert Into... Select... using a linked server directly is
> presumably faster?
> Regards,
> Paul Ibison
>
|||you may be running into a bug. it looks like you have to use character mode
to handle the money data type correctly.
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
drop view CustomSyncObject
go
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
--inserting data into publisher
go
select * from transdtl
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--startup snapshot agent
select * from transdtl
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
select * from transdtl where transdtlkey=1
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7a2b01c4848f$8c8961e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Also included Insert proc for Subscriber. Is there a way I
> can just use that IF its as quick as DTS?
> --Publisher
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TransDate] [smalldatetime] NOT NULL ,
> [TransAmt] [money] NOT NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> --Subscriber
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> --Insert proc on Subscriber
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
> as
> declare
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint
>
> select @.TransDateShort = Convert(varchar(10), @.TransDate,
> 101)-- from TransDTL
> select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
> select @.TransDateYear = Year(@.TransDate)-- from TransDTL
> select @.PostDateShort = Convert(varchar(10), @.PostDate,
> 101)-- from TransDTL
> select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
> select @.PostDateYear = Year(@.PostDate)-- from TransDTL
> insert into TransDTL
> (
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
> )
> values
> (
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
> )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
>
>
> subscriber here.
> message
> The
> procs
> initial
> It
> here
> use