Re: Updating a table in other MS Sql 7 DB

[Gary Kearns <GKearns@ELCOR.COM>]

Here at Elcor (SQR 4.3.2) we have written an interface between PS and Maximo
and in order to make password maintenance simplier we've come up with this
strategy.

In a .sqc included at the top of the program, code your passwords:
   LET $PSCONNECTSTRING = 'DSN=PSPDB;UID=VP1;PWD=XXXX'
   LET $MXCONNECTSTRING = 'DSN=MAXDB;UID=MAXIMO;PWD=XXXX'

Then in any sqr use the following to switch between dbs:
BEGIN-SELECT -DB$PSCONNECTSTRING - Select from PS
BEGIN-SELECT -DB$MXCONNECTSTRING - Select from Maximo

We did this for several reasons:
1)  Password maintenance
2)  We run this interface for several plants each with their own Maximo db
so in order to have a set of common code between plants we actually
determine which Maximo connect string to use based off a run control
parameter.

Hope this helps.
Gary

-----Original Message-----
From: Peter Clark [mailto:PGCLARK@VAC-ACC.GC.CA]
Sent: Friday, January 05, 2001 5:39 AM
To: SQR-USERS@list.iex.net
Subject: Re: Updating a table in other MS Sql 7 DB


Stephen,

What SQR version are you on? The syntax of your -DB statement varies from
what we use in 4.3.2 (-DB'DSN=Fred;UID=sa;PWD=sammy')

... does this mean we will have to sift thru a pile of code looking for
connect strings when (or rather "if") we upgrade??

Peter

>>> stephen keen <stephen_keen@SQR-SERVICES.COM> 2001/01/04 8:31:05 pm >>>
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