July 14, 2008 at 5:08 am
Hi all
Am using Distribution transaction coordinator in my stored procedure. Actual use of this is I have a data in primary server and i used to move the data to the backup server every night using the batch file. The stored procedure will get called in the application(running under batch file). The problem here is i get the error 'dtc has failed to make a transaction'
I linked both the servers using
exec sp_addlinkedserver 'fts-db'
exec sp_addlinkedsrvlogin 'fts-db',false,null,'sa','fts234'
i enabledthe dtc also.
I submitted the sp here
ALTER procedure [dbo].[backupdata24_Split]
@tblname as varchar(50)
as
BEGIN DISTRIBUTED TRANSACTION
set NOCOUNT ON
SET XACT_ABORT ON
declare @inserttablename as varchar(50)
declare @dateval datetime
declare @qry as nvarchar(max)
declare @curdateval nvarchar(20)
set @curdateval=convert(nvarchar,getdate(),101) + ' 23:59:59'
print @curdateval
--getting the currentdate - 24 hours
set @dateval=dateadd(hour,-48,@curdateval)
--set @dateval=dateadd(hour,-48,getdate())
--getting the historytablename with month
set @inserttablename = @tblname + '_'+ DATENAME(month, @dateval)
begin try
IF OBJECT_ID (N'bulkdata.dbo.'+ @inserttablename, N'U') IS NULL
begin
select @qry='CREATE TABLE bulkdata.dbo.'+@inserttablename +' ([SNo] [bigint] IDENTITY(1,1) NOT NULL,
[REGISTRATIONNO] [varchar](50) NULL,
[UNIT_NO] [varchar](50) NULL,
[LATITUDE] [float] NULL,
[LONGITUDE] [float] NULL,
[ALTITUDE] [numeric](15, 9) NULL,
[SPEED] [numeric](5, 0) NULL,
[GPS_DATETIME] [datetime] NULL,
[ODOMETER] [numeric](12, 2) NULL,
[IGNITION] [char](1) NULL,
[DOOR_SENSOR] [char](1) NULL,
[LOCATION] [varchar](200) NULL,
[GEO_BOUNDARY] [varchar](200) NULL,
[BasestationName] [varchar](50) NULL,
[analog1] [varchar](20) NULL,
[analog2] [varchar](20) NULL,
[logic_state] [varchar](20) NULL,
[gps_valid] [char](1) NULL,
[gps_connected] [char](4) NULL,
[SlNo] [bigint] NULL,
[EngineTime] [nvarchar](50) NULL,
[AContime] [nvarchar](50) NULL,
[SpeedLimitTime] [nvarchar](50) NULL,
[IdleTime] [nvarchar](50) NULL) ON [PRIMARY]'
exec sp_executesql @qry
select @qry='insert into bulkdata.dbo.' + @inserttablename +'([REGISTRATIONNO],[UNIT_NO],[LATITUDE],[LONGITUDE],[ALTITUDE],[SPEED],[GPS_DATETIME],[ODOMETER],[IGNITION],[DOOR_SENSOR],[SlNo],[LOCATION],[BaseStationName],[analog1],[analog2],[logic_state],[gps_valid],[gps_connected],[Geo_Boundary]) select A.[REGISTRATIONNO],A.[UNIT_NO],A.[LATITUDE],A.[LONGITUDE],A.[ALTITUDE],A.[SPEED],A.[GPS_DATETIME],A.[ODOMETER],A.[IGNITION],A.[DOOR_SENSOR],A.[SlNo],A.[LOCATION],A.[BaseStationName],A.[analog1],A.[analog2],A.[logic_state],A.[gps_valid],A.[gps_connected],A.[Geo_Boundary] from [fts-db].[vts1].[dbo].[gpsdata_history] as A , [mapping].[dbo].[virtualmapping_web] as B where A.[REGISTRATIONNO] = B.[REGISTRATIONNO] and ''' + cast(@dateval as varchar) + '''>A.gps_datetime and B.tablename1=''' + @tblname +''' and A.[REGISTRATIONNO] in (select registrationno from [fts-db].[vts1].[dbo].[gpsdata_history] group by registrationno having count(registrationno)>1)'
exec sp_executesql @qry
end
else
begin
select @qry='insert into bulkdata.dbo.' + @inserttablename +'([REGISTRATIONNO],[UNIT_NO],[LATITUDE],[LONGITUDE],[ALTITUDE],[SPEED],[GPS_DATETIME],[ODOMETER],[IGNITION],[DOOR_SENSOR],[SlNo],[LOCATION],[BaseStationName],[analog1],[analog2],[logic_state],[gps_valid],[gps_connected],[Geo_Boundary]) select A.[REGISTRATIONNO],A.[UNIT_NO],A.[LATITUDE],A.[LONGITUDE],A.[ALTITUDE],A.[SPEED],A.[GPS_DATETIME],A.[ODOMETER],A.[IGNITION],A.[DOOR_SENSOR],A.[SlNo],A.[LOCATION],A.[BaseStationName],A.[analog1],A.[analog2],A.[logic_state],A.[gps_valid],A.[gps_connected],A.[Geo_Boundary] from [fts-db].[vts1].[dbo].[gpsdata_history] as A , [mapping].[dbo].[virtualmapping_web] as B where A.[REGISTRATIONNO] = B.[REGISTRATIONNO] and ''' + cast(@dateval as varchar) + '''>A.gps_datetime and B.tablename1=''' + @tblname +'''and A.[REGISTRATIONNO] in (select registrationno from [fts-db].[vts1].[dbo].[gpsdata_history] group by registrationno having count(registrationno)>1)'
exec sp_executesql @qry
end
declare @dateval1 nvarchar(20)
set @dateval1=convert(nvarchar,@dateval,101) + ' ' +convert(nvarchar,@dateval,108)
--to make sure the insert happend
-- backup server
select @qry='insert into [fts-db].[vts1].[dbo].[reportdatetime]([date],modified_date,tablename) select isnull(max(gps_datetime),'''+@dateval1+'''),getdate(),''' + @inserttablename +''' from bulkdata.dbo.'+ @inserttablename
exec sp_executesql @qry
commit tran
end try
begin catch
declare @error as int
select @error=@@Error
if(@error<>0)
begin
RAISERROR ('Unable to Delete - Error' , 16, 1)
rollback tran
end
end catch
July 14, 2008 at 9:26 am
How long does the first transaction run before it errors out?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 9:50 am
Hi
thanks for ur reply. Its has taken more than 3 minutes for the first transaction.
July 14, 2008 at 11:44 am
That's probably timing out then. Can you break up the transaction into smaller pieces? Do it a few rows at a time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 10:38 pm
Yes i will do that. Could you suggest what could be the best method to move bulk records automatically from one server to another server
July 15, 2008 at 6:52 am
I generally use SSIS to bulk export them to a .txt file, then move that, then use SSIS to bulk import it. Then use a checksum to verify that they all got moved. (Sum() on the ID column, for example.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply