Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
9
Erster Beitrag:
vor 5 Monaten, 1 Woche
Letzter Beitrag:
vor 4 Monaten, 3 Wochen
Beteiligte Autoren:
Joris, Danny Lauwers, Arie, Sivaprakash, Peter Holemans, steve erts

[SOLVED] How to connect with MS SQL database over the network?

Startbeitrag von Joris am 16.05.2017 13:49

Hi,

I need to make a connection with an SQL database to load some data from another software into our Windev application (that is using Hyperfile).
I managed to do this by using the SQLConnect() command, SQLFirst(), SQLNext() etc...
This is working fine on the server (where the SQL database is located) but not on workstations.

I'm using this command :


MyConnection is int = SQLConnect("SBS2011\PFD","MyLogin","MyPassword","PFD","OLEDB",hOledbSQLServer)


SQL Server 2008 R2
SBS2011 is the name of the server
PFD is the name of the database

Many thanks,

Joris

Antworten:

Re: WD21 : How to connect with MS SQL database over the network?

Hi Joris,

I'ld use oleDB for SQL Server in this case instead of ODBC...
You'll make your life a lot easier.

Cheers,

Peter Holemans

von Peter Holemans - am 16.05.2017 14:08

Re: WD21 : How to connect with MS SQL database over the network?

I could connect to any MS Sql Server in my network with the following connection parameters.

DataBaseConnection is Connection

DataBaseConnection..Provider = hOledbSQLServer
DataBaseConnection..User = INIRead("Database","LogId", "", "INI FileName")
DataBaseConnection..Password = INIRead("Database", "Logpassword","", "INI FileName")
DataBaseConnection..Server = INIRead("Database", "ServerName","", "INI FileName")
DataBaseConnection..Database = INIRead("Database", "Database", "", "INI FileName")
//DataBaseConnection..Access = hORead

IF HOpenConnection(DataBaseConnection) = False THEN
Info(HErrorInfo)
EndProgram(False)
END

IF HChangeConnection("*", DataBaseConnection) = False THEN
Info(HErrorInfo)
EndProgram(False)
END

Hope by having relevant data in the ini file, you could also connect.

Happiness Always
BKR Sivaprakash

von Sivaprakash - am 18.05.2017 13:30

Re: WD21 : How to connect with MS SQL database over the network?

Thank you both for your answers. I am able to connect with the server and get some data with the code below.


MyConnection is Connection
MyQuery is Data Source

MyConnection..Provider = hOledbSQLServer
MyConnection..User = "MyLogin"
MyConnection..Password = "MyPassword"
MyConnection..Server = "SBS2011\PFD"
MyConnection..Database = "PFD"
MyConnection..Access = hORead

IF NOT HOpenConnection(MyConnection) THEN
Error("The connection to the data source failed." + CR + HErrorInfo(hErrFullDetails))
RETURN
END

IF NOT HExecuteSQLQuery(MyQuery,MyConnection,hQueryWithoutCorrection,"select DESCRIPTION from TR031") THEN
Error(HErrorInfo)
RETURN
END

FOR ALL MyQuery
Trace(MyQuery.DESCRIPTION)
END

HCloseConnection(MyConnection)



von Joris - am 22.05.2017 09:37

Re: WD21 : How to connect with MS SQL database over the network?

I created a little application using hyperfile to store my SQL server DB connections info in (and to encrypt them) and also used this to create a menu system to allow me to pick which DB to mount to.

steve

von steve erts - am 22.05.2017 17:58

Re: WD21 : How to connect with MS SQL database over the network?

Hi,

I'm still having problems to connect with the SQL database over the network. When testing on the server there is no problem, but when trying to connect from a workstation i get the error below.

The message says that the connection failed because the server does not exist or the access was refused. Login and password are correct. Pinging to the server is OK. So i think it must be some missing drivers or some setting in the SQL config? I have no idea...

Thank you for helping.
Joris.



The connection to the data source failed.

What happened?

OLE DB access error.

Error Number = 170124



Failure opening connection:

Data Source:

OLEDB Provider:



Error code: 73001

Level: non-fatal error

WD55 error code: 3001



System error message:

Description = [DBNETLIB][ConnectionOpen (Connect()).]De SQL-server bestaat niet of de toegang tot de server is geweigerd.

Source = Microsoft OLE DB Provider for SQL Server

SQL State = 08001

Error Number = -2147467259 (0x80004005)

Native Error Number = 17 (0x11)



Dump of the error of 'wd210hf.dll' module (21.0.124.5).

Identifier of detailed information (.err): 72801

Debugging information:

IEWDOLDB=105.3

Module=

Version=

Provider: SQLOLEDB

User: sa

Data source: SBS2011\PFD

Database: PFD

Unicode supported: 1

Page code of WL: 1252

Page code of the connection: UTF-16



MDAC Version = .



[Data format]

Query Parameter Checked = =

Query Parameter Needing conversion = =

DecimalSeparator =

DateFormat =



[Cursor settings Match]

LockType = , Location = , Type = , Capacities =

LockType = , Location = , Type = , Capacities =

LockType = , Location = , Type = , Capacities =



Additional Information:

EIT_NATIVECODE :

EIT_ADOCODE :

EIT_BASECODE :

EIT_ODBCDESCRIPTION :

EIT_ODBCCODE :

von Joris - am 31.05.2017 10:26

Re: WD21 : How to connect with MS SQL database over the network?

Joris,

I remember having this issue last year or so. If I'm correct the solutions was to enable "named pipes" as a networkprotocol for this sql-instance. It's done on the server (searhc Google) and it disabled by default. While the tcp/ip protocol is enabled.
I can't tell you what and why these protocols are there, but it helped me.

von Arie - am 31.05.2017 10:38

Re: WD21 : How to connect with MS SQL database over the network?

Hi,

On the server:
Set firewall rules and check if the TCP connection are enabled on the MS SQL and that the external connections are also enabled.

If this is en express SQL, you can seet the connection port to 1433 instead of a dynamic port. This can be set via SQL server configuration manager

On the workstation:
Check Firewall rules

Danny

von Danny Lauwers - am 31.05.2017 12:33

Re: WD21 : How to connect with MS SQL database over the network?

The problem is solved.
I had to specify the port because the SQL database did not use the default port number.

I only had to change this line of code to :

MyConnection..Server = "SBS2011,3900"

Thank you all for your input.

Kind regards,
Joris.

von Joris - am 31.05.2017 13:21
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.