Re: Updating a table in other MS Sql 7 DB

[Doug Nichols <drnichols@ATT.NET>]

        Hi,
        Yes, I am qualifying the table name as below. here is the select code, 
this
works fine:
begin-select
VendorNum &BR_Vendor_id,
InvoiceKey &BR_Invoice,
CommRunDate &BR_InvDate,
CommAmount &BR_Amount
        do some-stuff
from LAPTOP.pstest.dbo.CommissionExportPS
where CommRunDate = $rundt
and FinancePickUp = 'N'
end-select

then when i try to execute this code, it gives me this message:
(SQR 5528) ODBC SQL dbexec: SQLExecute error 7312 in cursor 1:
   [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: Only one transaction can be active on this session.]
(SQR 5528) ODBC SQL dbexec: SQLExecute error 7392 in cursor 1:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Could not start a
transaction for OLE DB provider 'SQLOLEDB'.

this is the update code:
begin-sql
UPDATE LAPTOP.pstest.dbo.CommissionExportPS
SET FinancePickUp = 'N'
FROM LAPTOP.pstest.dbo.CommissionExportPS A, PS_C_BROKER_TMP B
WHERE A.VendorNum = B.C_BROKER_VEND_ID
AND A.InvoiceKey = B.C_BROKER_INVOICE
AND A.CommRunDate = B.C_BROKER_INV_DATE
AND A.FinancePickUp = 'Y'
end-sql

I think it's because ODBC can't handle this, because it works fine if I run
the update code in the SQL Server query tool.

        thanks,
        Doug

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of David M. Thelen
Sent: Tuesday, January 02, 2001 10:41 PM
To: SQR-USERS@list.iex.net
Subject: Re: Updating a table in other MS Sql 7 DB


Doug,
Did you try qualifying the table name  i.e.
servername.databasename.dbo.tablename?

Doug Nichols wrote:

>         Hi,
>
>         I am running an sqr against one database and want to select data
from and
> update a table in another
>         database on a different sql server. I am runnin Sql server 7.0. I
have set
> up a link between both database
>         servers and can select data fine. It's the update that fails. If I
use
> isql/w, the update query works fine, it just
>         doesn't work when I run it in my sqr program.
>         Does anybody have any tips?
>
>         thanks a lot,
>         Doug