Problem with linked servers

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561632
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
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].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> 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].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>

Problem with DateAdd and ReportParameter

Hi there,
I'm trying to use a a ReportParameter in CommandText. Usually this works
fine, but together with the DateAdd-Function I've a problem. When I use the
following query everything works fine
= "SELECT Datum1, Datum2 From Table WHERE DATEADD(year, 20, Datum1 >
Datum2"
When I change this to
= "SELECT Datum1, Datum2 From Table WHERE DATEADD(year, " +
Parameters!Jahr.Value + ", Datum1 > Datum2"
I am not able to do a preview of my report. Does anybody know what to do, to
make it work?Try using & instead of +
= "SELECT Datum1, Datum2 From Table WHERE DATEADD(year, " &
Parameters!Jahr.Value & ", Datum1) > Datum2"
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Betina Keiner" <BetinaKeiner@.Gradient.de> wrote in message
news:eMk1yj$bEHA.2812@.TK2MSFTNGP11.phx.gbl...
> Hi there,
> I'm trying to use a a ReportParameter in CommandText. Usually this works
> fine, but together with the DateAdd-Function I've a problem. When I use
the
> following query everything works fine
> = "SELECT Datum1, Datum2 From Table WHERE DATEADD(year, 20, Datum1
> Datum2"
> When I change this to
> = "SELECT Datum1, Datum2 From Table WHERE DATEADD(year, " +
> Parameters!Jahr.Value + ", Datum1 > Datum2"
> I am not able to do a preview of my report. Does anybody know what to do,
to
> make it work?
>