DTS Fail when IDENTITY(1,1)

Hi,

i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?

Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.

e.g

Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)

Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)

i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.

i m using DTSDataPump in package to transform data.

waiting for ur reply|||Does the table have existing data?
NO

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable

Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.

-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP

yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO

INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO

SELECT * FROM Table1
GO

INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO

SELECT * FROM Table2
GO

SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]

u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.

Why are you bothering with a DTS package at all?|||That's what I don't get.

Why are you bothering with a DTS package at all?

i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.

whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.

:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this

CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:

Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text

Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"

'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect

'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep

'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With

'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp

'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True

'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask

objPackage.Execute

End Sub

dts fail

Hi y'all,

I'm facing a database data transmission problem during synchronysing. When dts fails i need a better solution instead inconsistent data.

I'm looking for data comparer for sql server where i have total control of my actions.

Any suggestions.

THanks in advance!

DTS should not be synchronizing your database because that is job of replication but the rest of your post says what you need is SQL Compare, there are third party tools and Microsoft have one so test drive it. If you think you need replication all I have to tell you is get a very good book or you can run into serious problems. Hope this helps.

http://msdn2.microsoft.com/en-us/teamsystem/aa718667.aspx

|||

so i need a database compare tool that generates a script...Thanks!

sql

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:

>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'***************************************
*******************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'***************************************
*********************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:[vbcol=seagreen]
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
[vbcol=seagreen]
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
> '***************************************
*******************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
> '***************************************
*********************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >I am trying to DTS some data from delimited source and some of the
> >dates have the value 99999999. This naturally makes the DTS job fail.
> >Is there a method to make it ignore these values and append only those
> >with acceptable date formats?: 20060810
> >
> >Thanks for any suggestions.
> >
> >RBollinger|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
>'**********************************************************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
>'************************************************************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:
>> There are probably quite a few different ways...it all
>> depends as to what best meets the overall needs for the
>> package.
>> One option would be to use a query against the text source
>> to select the rows you want to import using whatever
>> criteria against the date column.
>> Another would be to use an ActiveX Transformation script
>> with the Transform Data task, check the value of the column
>> with the date value and if it's not a date or in whatever
>> format, use DTSTransformStat_SkipInsert
>> to skip the row.
>> And another option would be to import the data into a
>> staging table, use a transform data task and query the
>> staging table for rows with the appropriate values for the
>> date column.
>> -Sue
>> On 10 Aug 2006 14:09:57 -0700, "robboll"
>> <robboll@.hotmail.com> wrote:
>> >I am trying to DTS some data from delimited source and some of the
>> >dates have the value 99999999. This naturally makes the DTS job fail.
>> >Is there a method to make it ignore these values and append only those
>> >with acceptable date formats?: 20060810
>> >
>> >Thanks for any suggestions.
>> >
>> >RBollinger|||Thanks!
Sue Hoegemeier wrote:
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >Making a staging table isn't an option for me so I'll use the
> >Transformation script:
> >
> >The source is a text file and I am having difficulty with the date.
> >The script is as follows (it may wrap):
> >
> >'**********************************************************************
> >' Visual Basic Transformation Script
> >' Copy each source column to the
> >' destination column
> >'************************************************************************
> >
> >Function Main()
> > DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> >
> > if DTSSource("Col002") = "99999999" then
> > Main = DTSTransforStat_SkipRow
> > else
> > DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
> >DTSSource("Col002"))
> > end if
> >
> > DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> > DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> > DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> > DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
> >Main = DTSTransformStat_OK
> >End Function
> >
> >This is bombing with a type mismatch 'CONVERT' error. Any suggestions
> >appreicated.
> >
> >RBollinger
> >
> >
> >
> >
> >
> >
> >
> >Sue Hoegemeier wrote:
> >> There are probably quite a few different ways...it all
> >> depends as to what best meets the overall needs for the
> >> package.
> >> One option would be to use a query against the text source
> >> to select the rows you want to import using whatever
> >> criteria against the date column.
> >> Another would be to use an ActiveX Transformation script
> >> with the Transform Data task, check the value of the column
> >> with the date value and if it's not a date or in whatever
> >> format, use DTSTransformStat_SkipInsert
> >> to skip the row.
> >> And another option would be to import the data into a
> >> staging table, use a transform data task and query the
> >> staging table for rows with the appropriate values for the
> >> date column.
> >>
> >> -Sue
> >>
> >> On 10 Aug 2006 14:09:57 -0700, "robboll"
> >> <robboll@.hotmail.com> wrote:
> >>
> >> >I am trying to DTS some data from delimited source and some of the
> >> >dates have the value 99999999. This naturally makes the DTS job fail.
> >> >Is there a method to make it ignore these values and append only those
> >> >with acceptable date formats?: 20060810
> >> >
> >> >Thanks for any suggestions.
> >> >
> >> >RBollinger

DTS ActiveX Script error

The 1st 4 lines of code were copied verbatim from a VBS file that works
perfectly.
Why does it fail when running from a DTS ActiveX script?
Function Main()
set objShell = WScript.CreateObject("Wscript.Shell")
wait = true
strEXEC = "C:\FTPTest\FTPSNAP.BAT"
objShell.run strEXEC, 1, wait
Main = DTSTaskExecResult_Success
End Function
Debugging is not an option until I know how to make JIT debugging work...
(see my other posts on JIT Debugging)
Regards,
John> set objShell = WScript.CreateObject("Wscript.Shell")
The WScipt object is available from a Windows Scripting Host environment but
not from a DTS ActiveX script task.
It looks to me like you should use a DTS ExecuteProcess task instead.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
> The 1st 4 lines of code were copied verbatim from a VBS file that works
> perfectly.
> Why does it fail when running from a DTS ActiveX script?
> Function Main()
> set objShell = WScript.CreateObject("Wscript.Shell")
> wait = true
> strEXEC = "C:\FTPTest\FTPSNAP.BAT"
> objShell.run strEXEC, 1, wait
> Main = DTSTaskExecResult_Success
> End Function
> Debugging is not an option until I know how to make JIT debugging work...
> (see my other posts on JIT Debugging)
> --
> Regards,
> John|||http://www.codeproject.com/useritems/DTS__VBNET_.asp|||Yep, thats what what needed.
Thanks!
--
Regards,
John
"Dan Guzman" wrote:

> The WScipt object is available from a Windows Scripting Host environment b
ut
> not from a DTS ActiveX script task.
> It looks to me like you should use a DTS ExecuteProcess task instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
> news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
>
>|||Thanks for the reply!
I am not using VBNET, yet.
All my VB experience comes from MS Office with VBA.
Do you have a link that covers the same or similar topics from the
standpoint of using VB6 projects or an excel macro with VBA code?
From looking at the code samples on your link... I don't recognize the "Try"
and "End Try" statements nor the "Catch exc" I assume those are some new NE
T
commands?
Regards,
John
"vipinjosea" wrote:

> http://www.codeproject.com/useritems/DTS__VBNET_.asp
>

DTC (C!) fails on same server

Is it a known issue that DTC will fail using SQL OLE defined links from and
to the same server? If I change servers within the link properties all is
well. If I link to myself, or another instance on myself, this is what I
get:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
It makes sense that it fails, in a way, because DTC is being both a target
and a sender.
Thanks,
JohnMSDTC does not work in loopback mode. From BOL, topic "loopback linked
servers":
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"John Beatty" <jbeatty@.wmsgaming.com> wrote in message
news:%23faMtUzKFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Is it a known issue that DTC will fail using SQL OLE defined links from
> and
> to the same server? If I change servers within the link properties all is
> well. If I link to myself, or another instance on myself, this is what I
> get:
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> It makes sense that it fails, in a way, because DTC is being both a target
> and a sender.
> Thanks,
> John
>
>