connection two SQL server and two databases

[mtajkov]

I use SQLRDD and ODBC connection for MYSql. How I open One table from one sql server and at the same time Open second table from second server?

Regards,
Milos

FWHX 12.08

[Antonio Linares]

Milos,

Have you considered to use ADORDD ? It is free and you have full source code for it

[mtajkov]

The application has been in development for several years, and I'm not sure how the changes in the code were necessary?

Regards,
Miloš

[Rick Lipkin]

Miloš

This is not a problem .. just create two connections oCn1to one database and oCn2 to the other database and make sure when you open your recordset you pass the correct connection to manipulate your tables.

If it were me, I would be a bit more descriptive on my connection name like:

oCnServer1
oCnServer2

Rick Lipkin

[mtajkov]

this is first connection:

Code: Select all  Expand view  RUN

cDatabase=AllTrim(System->Database)
 
      If ! cDatabase IN hIniFile
         MsgInfo("Connection [" + cDatabase + "] not found in sqlrdd.ini")
         Quit
      EndIf

      hDsn := hIniFile[ cDatabase ]

      If !"CONNSTRING" IN hDsn
         MsgInfo( "ConnString not found in " + cDatabase)
         Quit
      EndIf

   aDsn    := OdbcDsnEntries()
 
   if len(aDsn) == 0
      MsgAlert("Mora te kreirati ODBC DSN konfiguraciju iz vašeg kontrol panela",;
               "ODBC DSN selekcija")
      return nil
   endif

 cConnString := hDsn[ "CONNSTRING" ]

if !Connect(cConnString)
  return
endif

oSql   := SR_GetConnection()
 

and this second connection:

Code: Select all  Expand view  RUN

USE System NEW

hIniFile := HB_ReadIni( "sperdd.ini", .F.,,.F. )     // Read ini file in a hash table

 sDatabase=AllTrim(System->Database)


      If ! sDatabase IN hIniFile
         MsgInfo("Connection [" + sDatabase + "] not found in sperdd.ini")
         Quit
      EndIf

      hDsn := hIniFile[ sDatabase ]

      If !"CONNSTRING" IN hDsn
         MsgInfo( "ConnString not found in " + sDatabase)
         Quit
      EndIf

   aDsn    := OdbcDsnEntries()
 
   if len(aDsn) == 0
      MsgAlert("Mora te kreirati ODBC DSN konfiguraciju iz vašeg kontrol panela",;
               "ODBC DSN selekcija")
      return nil
   endif

 cConnString := hDsn[ "CONNSTRING" ]

if !Connect(cConnString)
  return
endif

oSqlSpe   := SR_GetConnection()
 

if I try from first connection (server/databases..):
USE Inf_Firma SHARED VIA "SQLRDD" NEW
It is okay.

But if I try to open the same table with another server (base) is not functioning. The application is stopped without notice error.The same is true if you change the order.

Regards,
Miloš

[avista]

May be this help

I use 7 servers via VPN
I use one connection and SQL statements something like this

SELECT * FROM DataBase1@Server1:table
SELECT * FROM DataBase2@Server2:table
.
.
.

Best regards,

[mtajkov]

I try

Code: Select all  Expand view  RUN

                    nErr := oSql:exec("INSERT INTO &cKnjiga (GK_Godina, GK_Firma, GK_Nalog, GK_Datum, GK_Dokum, GK_Opis, GK_Konto, GK_Analit, GK_DugPoc, GK_PotPoc, GK_Duguje, GK_Potraz)"+;
                               " SELECT GK_Godina, '" + mFirma_id + "', GK_Nalog, GK_Datum, GK_Dokum, GK_Opis, GK_Konto, GK_Analit, GK_DugPoc, GK_PotPoc, GK_Duguje, GK_Potraz FROM Infobit@Server:Spe_Knjiga"+;
                               " WHERE &cFirma .Gk_Godina= '" + mObrGod + "'")
 

but get a syntax error:

Detaljan opis greške
--------------------
Error SR_ODBC/0 SQLExecDirect Error
42000 - 102 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'Infobit@Server:'.
Command sent to database :
INSERT INTO TMP_MISA_KNJIGA (GK_Godina, GK_Firma, GK_Nalog, GK_Datum, GK_Dokum, GK_Opis, GK_Konto, GK_Analit, GK_DugPoc, GK_PotPoc, GK_Duguje, GK_Potraz) SELECT GK_Godina, 'SPE', GK_Nalog, GK_Datum, GK_Dokum, GK_Opis, GK_Konto, GK_Analit, GK_DugPoc, GK_PotPoc, GK_Duguje, GK_Potraz FROM Infobit@Server:Spe_Knjiga WHERE SPE_KNJIGA .Gk_Godina= '2015'
Steatment handle :
Connection handle :
RetCode : -1
- RollBack executed.

Regards

[Antonio Linares]

Miloš,

SQLRDD is a commercial product so surely asking its developers may help you

[Antonio Linares]

I have just sent the error to Mr. Rao as he is our best SQL ADO expert

Lets see what he says

[Antonio Linares]

His answer:

First thing I want to tell you is that in T-SQL ( Transact SQL of MS Server ), all variables are prefixed with "@".
@Server means it is a variable by name "@Server".

We should not leave any variable name as it is in an Sql statement All should be constants.

If "infobit" is the server name, then Infobit must be substituted in the place of variable "@Server"

Instead of
Infobit@Server:Spe_Knjiga
It should be Infobit.Spe_knjiga
( full syntax is <Server>.<schema>.<tablename>)

For linked servers to work
we should link the server first by executing
sp_addlinkedserver(...)

[mtajkov]

Avista,

Mogu da Vas kontaktiram putem mail-a?

Pozdrav,
Miloš

[mtajkov]

Antonio,

I'm sorry but I do not know how to execute sp_addlinkedserver(...). I looked https://msdn.microsoft.com/en-us/library/ms190479.aspx but I can not understand. Please for more concrete explanation.

[devtuxtla]

HI.

This code could help:


cESQUEMA1:="midataone"
nMainone:=DB_oneConnection(cESQUEMA1, cIP)
cESQUEMA2:="midatatwo"
nMaintwo:=DB_oneConnection(cESQUEMA2, cIP)

use "mitablaone" alias a1 shared new via "sqlrdd" connection nMainone
use "mitablatwo" alias a2 shared new via "sqlrdd" connection nMaintwo

select a1
browse()

select a1
browse

a1->(dbclosearea())
a2->(dbclosearea())

return .t.


*--------------------------------------------------------------*
FUNCTION DB_oneConnection(cData, cIP)
*--------------------------------------------------------------*
LOCAL cConn, nConn
DEFAULT cIP:="localhost"
nConn := SR_AddConnection(CONNECT_MYSQL,"MYSQL=" + cIP + ";UID=userone;PWD=passone;DTB=" +cData)
RETURN (nConn)

*--------------------------------------------------------------*
FUNCTION DB_twoConnection(cData, cIP)
*--------------------------------------------------------------*
LOCAL cConn, nConn
DEFAULT cIP:="192.168.1.20"
nConn := SR_AddConnection(CONNECT_MYSQL,"MYSQL=" + cIP + ";UID=usertwo;PWD=passtwo;DTB=" +cData)
RETURN (nConn)

[mtajkov]

Yes, this works for the opening of the table, but not to query

Best regards

[mtajkov]

Using SQL Manager I execute:

Code: Select all  Expand view  RUN

EXEC sp_addlinkedserver
   @server = N'Spedicija',
   @srvproduct = N'',
   @provider = N'SQLNCLI',
   @datasrc = N'192.168.150.2\SqlSmart';
GO

everything works perfectly

Best regards