DTC issue or SQL Syntax issue

Hi All,
The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
server is SQL 2000 Enterprise SP4.
I am executing this sql in query analyzer:
create table #StoreInfoTraits (
[record type] varchar(3),
[site id] int,
[current effective date] smalldatetime,
[trait id] varchar(50),
[trait value] int
)
DECLARE @.cmd as varchar(200)
SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
insert into #StoreInfoTraits exec(@.cmd)
DROP TABLE #StoreInfoTraits
And getting this error:
OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
"The transaction manager has disabled its support for remote/network
transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "co-dbdev-01" was unable to begin a distributed transaction.
Iâ've checked the DTC services per the following link and both DTC services
are running under a domain account and configured properly.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
This executes and returns the recordset:
Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
Since the remote stored proc returns the records when executing the stored
proc, is it a DTC issue or is this syntax correct ?
insert into #StoreInfoTraits exec(@.cmd)Hi
I think Bill's post covered most things in
http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
work OK then dtc itself can be ruled out.
Your syntax should work (well my test does on mine) providing dtc is running
correctly!
If you don't want to start a distributed transaction you could try
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
storeinfo.dbo.pr_traitassignments' )
DECLARE @.cmd varchar(400)
SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
effective date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
storeinfo.dbo.pr_traitassignments'' ) '
EXEC ( @.cmd )
John
"brymer28303" wrote:
> Hi All,
> The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> server is SQL 2000 Enterprise SP4.
> I am executing this sql in query analyzer:
> create table #StoreInfoTraits (
> [record type] varchar(3),
> [site id] int,
> [current effective date] smalldatetime,
> [trait id] varchar(50),
> [trait value] int
> )
> DECLARE @.cmd as varchar(200)
> SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> insert into #StoreInfoTraits exec(@.cmd)
> DROP TABLE #StoreInfoTraits
> And getting this error:
> OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> "The transaction manager has disabled its support for remote/network
> transactions.".
> Msg 7391, Level 16, State 2, Line 1
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "co-dbdev-01" was unable to begin a distributed transaction.
> Iâ've checked the DTC services per the following link and both DTC services
> are running under a domain account and configured properly.
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> This executes and returns the recordset:
> Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> Since the remote stored proc returns the records when executing the stored
> proc, is it a DTC issue or is this syntax correct ?
> insert into #StoreInfoTraits exec(@.cmd)
>
>|||Thanks John. I tried this
INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
date], [trait id], [trait value] )
SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
storeinfo.dbo.pr_TraitAssignments' )
and got this:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
the object has no columns or the current user does not have permissions on
that object.
I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
and it returns:
TVA 50 08-30-2006 ACQUISITION 0
TVA 51 08-30-2006 ACQUISITION 0
TVA 52 08-30-2006 ACQUISITION 0
TVA 53 08-30-2006 ACQUISITION 0
TVA 54 08-30-2006 ACQUISITION 0
And, we apparently have a dtc issue I found when I ran the dtctester.exe --
unable to connect.
"John Bell" wrote:
> Hi
> I think Bill's post covered most things in
> http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> work OK then dtc itself can be ruled out.
> Your syntax should work (well my test does on mine) providing dtc is running
> correctly!
> If you don't want to start a distributed transaction you could try
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> storeinfo.dbo.pr_traitassignments' )
> DECLARE @.cmd varchar(400)
> SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> effective date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> storeinfo.dbo.pr_traitassignments'' ) '
> EXEC ( @.cmd )
> John
> "brymer28303" wrote:
> > Hi All,
> >
> > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > server is SQL 2000 Enterprise SP4.
> >
> > I am executing this sql in query analyzer:
> >
> > create table #StoreInfoTraits (
> > [record type] varchar(3),
> > [site id] int,
> > [current effective date] smalldatetime,
> > [trait id] varchar(50),
> > [trait value] int
> > )
> > DECLARE @.cmd as varchar(200)
> > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > insert into #StoreInfoTraits exec(@.cmd)
> > DROP TABLE #StoreInfoTraits
> >
> > And getting this error:
> >
> > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > "The transaction manager has disabled its support for remote/network
> > transactions.".
> > Msg 7391, Level 16, State 2, Line 1
> > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> >
> > Iâ've checked the DTC services per the following link and both DTC services
> > are running under a domain account and configured properly.
> > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> >
> > This executes and returns the recordset:
> > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> >
> > Since the remote stored proc returns the records when executing the stored
> > proc, is it a DTC issue or is this syntax correct ?
> > insert into #StoreInfoTraits exec(@.cmd)
> >
> >
> >|||Hi
The user for the remote server will be determined by how the linked server
was created.
The dtctester error looks like you are not even making it to the remote
server! If you go through Bill's post and check out the networking issues it
may show something. You could also try DTCPing.
John
"brymer28303" wrote:
> Thanks John. I tried this
> INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> date], [trait id], [trait value] )
> SELECT * FROM OPENQUERY ([CO-DBDEV-01], 'EXEC
> storeinfo.dbo.pr_TraitAssignments' )
> and got this:
> Msg 7357, Level 16, State 2, Line 1
> Cannot process the object "EXEC storeinfo.dbo.pr_TraitAssignments". The OLE
> DB provider "SQLNCLI" for linked server "CO-DBDEV-01" indicates that either
> the object has no columns or the current user does not have permissions on
> that object.
> I can execute this Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments.
> and it returns:
> TVA 50 08-30-2006 ACQUISITION 0
> TVA 51 08-30-2006 ACQUISITION 0
> TVA 52 08-30-2006 ACQUISITION 0
> TVA 53 08-30-2006 ACQUISITION 0
> TVA 54 08-30-2006 ACQUISITION 0
> And, we apparently have a dtc issue I found when I ran the dtctester.exe --
> unable to connect.
> "John Bell" wrote:
> > Hi
> >
> > I think Bill's post covered most things in
> > http://msdn2.microsoft.com/en-us/library/aa561924.aspx if dtcping/dtctester
> > work OK then dtc itself can be ruled out.
> >
> > Your syntax should work (well my test does on mine) providing dtc is running
> > correctly!
> >
> > If you don't want to start a distributed transaction you could try
> >
> > INSERT INTO #StoreInfoTraits ([record type], [site id], [current effective
> > date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, 'EXEC
> > storeinfo.dbo.pr_traitassignments' )
> >
> > DECLARE @.cmd varchar(400)
> > SET @.cmd = 'INSERT INTO #StoreInfoTraits ([record type], [site id], [current
> > effective date], [trait id], [trait value] )
> > SELECT * FROM OPENQUERY ( co-dbdev-01, ''EXEC
> > storeinfo.dbo.pr_traitassignments'' ) '
> >
> > EXEC ( @.cmd )
> >
> > John
> >
> > "brymer28303" wrote:
> >
> > > Hi All,
> > >
> > > The sending server is SQL 2005 Enterprise SP2 64 bit. The destination
> > > server is SQL 2000 Enterprise SP4.
> > >
> > > I am executing this sql in query analyzer:
> > >
> > > create table #StoreInfoTraits (
> > > [record type] varchar(3),
> > > [site id] int,
> > > [current effective date] smalldatetime,
> > > [trait id] varchar(50),
> > > [trait value] int
> > > )
> > > DECLARE @.cmd as varchar(200)
> > > SET @.cmd = '[co-dbdev-01].storeinfo.dbo.pr_traitassignments'
> > > insert into #StoreInfoTraits exec(@.cmd)
> > > DROP TABLE #StoreInfoTraits
> > >
> > > And getting this error:
> > >
> > > OLE DB provider "SQLNCLI" for linked server "co-dbdev-01" returned message
> > > "The transaction manager has disabled its support for remote/network
> > > transactions.".
> > > Msg 7391, Level 16, State 2, Line 1
> > > The operation could not be performed because OLE DB provider "SQLNCLI" for
> > > linked server "co-dbdev-01" was unable to begin a distributed transaction.
> > >
> > > Iâ've checked the DTC services per the following link and both DTC services
> > > are running under a domain account and configured properly.
> > > http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0?hl=en&lr=&ie=UTF-8&oe=UTF-8
> > >
> > > This executes and returns the recordset:
> > > Exec [co-dbdev-01].storeinfo.dbo.pr_traitassignments
> > >
> > > Since the remote stored proc returns the records when executing the stored
> > > proc, is it a DTC issue or is this syntax correct ?
> > > insert into #StoreInfoTraits exec(@.cmd)
> > >
> > >
> > >