Something you may want to check.
Does your ODBC connection include something like: (Picture
below includes a check next to "Create temporary stored ...")
If so, your userid will need to
have the privilege in your target database to create temporary stored
procedures.
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
On Behalf Of David M. Thelen
Sent: Tuesday, January 02, 2001
10:41 PM
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