Re: Updating a table in other MS Sql 7 DB

["Shaver, Richard H" <richard.h.shaver@LMCO.COM>]

Hey good people,
These half meg files lock up my laptop for several minutes each time I come
across them.  Can you use something other than .bmp files - maybe jpeg or
gif?  And can those who reply please delete the graphics before you send.
Those of us on travel stuck with 28k modem speeds would highly appreciate
it.
Thanks
Rick

-----Original Message-----
From: stephen keen [mailto:stephen_keen@SQR-SERVICES.COM]
Sent: Thursday, January 04, 2001 4:31 PM
To: SQR-USERS@list.iex.net
Subject: Re: Updating a table in other MS Sql 7 DB


Here is a sample of code where I am logged into a peoplesoft database and am
selecting from a non peoplesoft database.  I have ODBC connections for all
databases.  I do not link the tables.






!Begin-Procedure cust_deposits

let $old_market = '001'

Begin-Select -DB=Fred;DSN=Fred;UID=sa;PWD=sammy

GL_number

Account_number

name

street

street1

FROM datBHISTORY

WHERE acct_yr_mo = $A_Y_M and type = 'D'

End-Select





-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Stan Quick
Sent: Thursday, January 04, 2001 4:35 PM
To: SQR-USERS@list.iex.net
Subject: FW: Updating a table in other MS Sql 7 DB





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