Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
19
Erster Beitrag:
vor 6 Jahren, 6 Monaten
Letzter Beitrag:
vor 6 Jahren, 6 Monaten
Beteiligte Autoren:
Paul Murray , Arie, Kingdr, Paulo Oliveira, Jeff Graham

Deployment Application Server Cannot Connect To SQL Server 2008

Startbeitrag von Paul Murray am 12.01.2012 17:21

WEBDEV 15

Greetings All,

We recently migrated to SQL Server 2008. Now, an application that works fine in the IDE and had worked fine on the deployment server no longer works.

I tried connecting via the WinDev native client (also installed on the application server) and got the following error message (after installing the SQLServer 2008 client (sqlncli.msi):

native SQLSERVER access error.
Error Number = 107
Access library to SQLServer not found (ntwdblib.dll): SQLServer client layer may not be installed correctly. Install SQLServer client layer version 6.5 or later before using Native WinDev Access for SQLServer.

Copying ntwdblib.dll to the windows\system32 directory did not help, but I did get a different error message as follows:

native SQLSERVER access error.
Error Number = 107
Error returned by SQLServer:
Unable to connect: SQLServer is unavailable or does not exist. Unable to connect: SQLServer does not exist or network access denied., #10004, Severity 9


So I tried the SQL Server OLEDB driver in my IDE (which works fine) and tried to deploy again. Now I get the following error:

OLE DB access error.
Error Number = 170124
Failure opening connection:
Data Source:
OLEDB Provider:

My IDE is running on Windows 7.
My Application Server is running on Windows Server 2003 SP3.

If someone can point me in the right direction on this, I would really appreciate it as this is a production application that we are now without.

Thanks!!

Paul

Antworten:

Hi Paul,

Try google for "sql server error 170124"

One of the links:
http://www.mysnip.de/forum-archiv/thema/27131/14802/Deployment+with+External+Database.html

The posts are in English.

HTH,

von Jeff Graham - am 12.01.2012 17:36
sql server or windows authentication?

If the native access is looking for the ntwdblib.dll probably you are using the old method, check the H.SQLServerMode variable content.
force it to 0 to use the SQLnCli

the old method doesn't work well with windows aut.

von Paulo Oliveira - am 12.01.2012 17:42
Thanks, Jeff.

Yes, I saw that post. Does not seem to apply to my situation. The other posts did not have resolutions.

Thanks, again.

Paul

von Paul Murray - am 12.01.2012 17:51
Thanks, Paulo.

I tried that (I put the H.SQLServerMode = 0 in the code just before executing the embedded query).

I got a different error as:

native SQLSERVER access error.
Error Number = 117
Error 80004005 returned by SQL Server
[DBNETLIB][ConnetionOpen (Connect()).] SQL Server does not exist or access denied.
SQL State: 08001
SQL Error Number: 17

Question: Is error 17 a windev error number? Hard to tell here.

Thanks for your help!!

Paul

von Paul Murray - am 12.01.2012 18:10
In reading the help on SQL Server Native Access, they reference a program wdsqsins.exe.

Not sure what it is for. But I CAN connect to my database using it, although there is no place to specify which database that you want to access.

The helps seems to state that you can configure connections for the database, but I cannot see how I would reference these connections.

Also, I did install the Microsoft SQL Server native client using sqlncli.exe. Does this conflict with the WebDev SQL Server Native Access? Should I uninstall it? Or do I need it?

This is all maddening.

Does PC-Soft provide 'Paid' support? I am down for a week and cannot screw around with this any longer. I get a response from PC-Soft free support every other day usually asking me questions that were already answered by way of my rather thorough description of the problem. I did get an 82 page PDF file telling me how to install the application server even though I stated in my technical request that this application had been working for a whole year on the exact same server.

Not to say that the people in tech support are not friendly. They are. But maybe they are focused on WinDev 16 issues or something.

Very frustrating...

Paul

von Paul Murray - am 12.01.2012 21:20
Hi Paul,

Here is what they told me:

Quote
Patrick Phillpot
PC SOFT offers a Direct Assistance service available on subscription services or on-site assistance. Feel free to contact the Sales Department on this matter on the number +33 467 032 032 or by email to info@windev.com


I sent an email and they sent a contract form to fax back.

von Jeff Graham - am 13.01.2012 14:24
You didn't answer my question about the authentication method used.
By the error it loks like some authentication problem or the connection used in your program is wrong.

the sql server as a named instance?
the config of the tcp/ip protocol is the sql server is using the default port (1443)?

why don't you post here the code of your connection. If you post the code don't forget to use a fake user/password/machinename we don't want to know that.

von Paulo Oliveira - am 13.01.2012 16:42
Paul,

I don;t have the native (WD) driver but it seems to me sql2008 is doing things different by default. Security settings? Or connections?

You probably did already but if I google the error
"DBNETLIB][ConnetionOpen (Connect()).] SQL Server does not exist or access denied"
then there are numerous posts.

Someone mention this

I went into the surface area configuration and set the remote connections to both TCP/IP and Named Pipes and restarted the server

or this

Persist Security Info=False


You can add such options (i.e. Trusted_Connection=YES) in the extended-info property when opening the connection (HOpenConnection) or by using a connection type variable.

von Arie - am 13.01.2012 17:52
Sorry Paulo, I lost focus.

I understand that our database does not allow trusted security. You have to have a direct login for the database.

My application uses a defined database connection (in the analysis to connect) and it does work fine in the IDE. So there is no code.

I also tried defining a connection in the project code specifying H.SQLServerMode = 0 before the OpenConnection command.

The following code works fine in the IDE but when deployed yields:

'native sqlserver access error. error number = 117'
Error 80004005 returned by sql server
DBNETLIB][ConnetionOpen (Connect()).] SQL Server does not exist or access denied
SqlState: 08001
SQL Error number:17


PROJECT CODE


MyConnection is Connection
// Describe the connection
MyConnection..User = "PBApps"
MyConnection..Password = "XXXXXXXX"
MyConnection..Server = "SQLVS"
MyConnection..Database = "LIMO"
MyConnection..Provider = hNativeAccessSQLServer
MyConnection..Access = hORead
//MyConnection..ExtendedInfo = "Extended information"
MyConnection..CursorOptions = hClientCursor

H.SQLServerMode = 0 //to enforce the use of sqlncli

// Open the connection
HOpenConnection(MyConnection)

Info (HErrorInfo(hErrMessage))

gsErr is string
gsErr = HErrorInfo(hErrFullDetails)



PAGE CODE

GLOBAL DECLARATIONS



start_datetime is DateTime
end_datetime is DateTime



INITIALIZATION OF PAGE




PAGE_Page1..Title = "Airport Coordinator"

start_datetime..Date = DateSys()
start_datetime..Time = Now()
end_datetime..Date = DateSys()
end_datetime..Time = Now()
start_datetime..Hour-=6
end_datetime..Hour+=6

nRownbr is int

sSTRING1 is string
sSTRING2 is string

req_date_time is DateTime
airport_airline is string
airport_airline = "XXX"
PUDay is string
sPUTime is string
sConfo is string
sStatus_flag is string
nPax is int
sFlightStatus is string
sVIPFlag is string
sLName is string
sFName is string

sFlightNumber is string
sAirportFrom is string
sDrNo is string
sAccountNumber is string
sPUPoint is string

gbBresult is boolean

//QRY_ArrivingAirportPassengers2 is data source

QRY_ArrivingAirportPassengers2.start_datetime = start_datetime
QRY_ArrivingAirportPassengers2.end_datetime = end_datetime



Info(MyConnection..Server)
Info(MyConnection..Database)
Info(MyConnection..User)

// Initializes the query

gbBresult = HExecuteQuery(QRY_ArrivingAirportPassengers2,MyConnection)


IF gbBresult True THEN
//STC_Static2..Value = HError(hErrCurrent)
//EDT_1..Value = HErrorInfo(hErrMessage)
Info ("gbResult = False")
Info (HErrorInfo(hErrMessage))
RETURN
ELSE
Info ("gbResult = True")
END


// Read the first record of the query
HReadFirst(QRY_ArrivingAirportPassengers2)
WHILE NOT HOut()
// Process the record of the query
//airport_airline = QRY_ArrivingAirportPassengers2.airport_airline
req_date_time = QRY_ArrivingAirportPassengers2.req_date_time
PUDay = req_date_time..Day
sPUTime = TimeToString(req_date_time..Time,"HH:MM")
sConfo = Right(QRY_ArrivingAirportPassengers2.confirmation_no, 4)
sStatus_flag = QRY_ArrivingAirportPassengers2.status_flag
nPax = QRY_ArrivingAirportPassengers2.no_pass
sFlightStatus = QRY_ArrivingAirportPassengers2.flight_status
sVIPFlag = QRY_ArrivingAirportPassengers2.vip_flag
sLName = QRY_ArrivingAirportPassengers2.lname
sFName = QRY_ArrivingAirportPassengers2.fname

sFlightNumber = QRY_ArrivingAirportPassengers2.airport_flight
sAirportFrom = QRY_ArrivingAirportPassengers2.airport_from
sDrNo = QRY_ArrivingAirportPassengers2.dr_no
sAccountNumber = QRY_ArrivingAirportPassengers2.account_no
sPUPoint = QRY_ArrivingAirportPassengers2.airport_pu_point

IF sFlightStatus = Null OR sFlightStatus = "" THEN
sFlightStatus = "? "
END

IF sVIPFlag = Null OR sVIPFlag = "" THEN
sVIPFlag = "?"
END

IF sAirportFrom = Null OR sAirportFrom = "" THEN
sAirportFrom = "???"
END

IF sDrNo = Null OR sDrNo = "" THEN
sDrNo = "DRVR"
END

IF Position(sPUPoint,"CURB",0) > 0 THEN
sPUPoint = "CURB"
ELSE IF Position(sPUPoint,"BAGG",0) > 0 THEN
sPUPoint = "BAGG"
ELSE IF Position(sPUPoint,"CUSTOM",0) > 0 THEN
sPUPoint = "CUSTOMS"
END

sSTRING1 = PUDay + " " + sPUTime + " " + sConfo + " " + sVIPFlag + " " + sStatus_flag + " " + nPax + " " + NoSpace(sLName) + " " + Left(sFName ,1) + "."
sSTRING2 = "- " + sFlightNumber + " " + sAirportFrom + " " + sFlightStatus + " " + sDrNo + " " + sAccountNumber + " " + sPUPoint

IF airport_airline QRY_ArrivingAirportPassengers2.airport_airline THEN
airport_airline = QRY_ArrivingAirportPassengers2.airport_airline
nRownbr = TableAddLine(TABLE_ArrivalData,airport_airline)
TABLE_ArrivalData[nRownbr]..BrushColor = White
nRownbr = TableAddLine(TABLE_ArrivalData,sSTRING1)
IF sVIPFlag = "V" THEN
TABLE_ArrivalData[nRownbr]..BrushColor = LightRed
TABLE_ArrivalData[nRownbr]..Color = White
END
nRownbr = TableAddLine(TABLE_ArrivalData,sSTRING2)
IF sVIPFlag = "V" THEN
TABLE_ArrivalData[nRownbr]..BrushColor = LightRed
TABLE_ArrivalData[nRownbr]..Color = White
END
ELSE
nRownbr = TableAddLine(TABLE_ArrivalData,sSTRING1)
IF sVIPFlag = "V" THEN
TABLE_ArrivalData[nRownbr]..BrushColor = LightRed
TABLE_ArrivalData[nRownbr]..Color = White
END
nRownbr = TableAddLine(TABLE_ArrivalData,sSTRING2)
IF sVIPFlag = "V" THEN
TABLE_ArrivalData[nRownbr]..BrushColor = LightRed
TABLE_ArrivalData[nRownbr]..Color = White
END
END

// Read the next record
HReadNext()
//STC_Static2..Value = QRY_ArrivingAirportPassengers2.lname
END

HCancelDeclaration(QRY_ArrivingAirportPassengers2)


von Paul Murray - am 13.01.2012 20:04
I;m not sure if this is of any help.
hNativeAccessSQLServer is a constant and replaced by this : WinDevSQLServer
There's also hOledbSQLServer which is replaced by SQLOLEDB
You can easily see this when debugging.

A while ago I did also some tests with these values for the connection..provider setting.
SQLNCLI
and
SQLNCLI10
Maybe you could try these?

btw: can you explain to me the benefits of purchasing and using the NATIVE driver?
I mean the PCSoft native driver, not the MSSQL native client. Or do they relate some way?

von Arie - am 14.01.2012 13:25
Thanks, Arie!!

The performance of the application is supposed to be much faster with the native driver. But I ordered it a year ago while trying to get my application running. I received it after I got the app running with oledb and have not tried to use it until now.

Arie, can you please show me what the code should look like for your suggestions?

When I key in:

MyConnection..Provider = WinDevSQLServer

WinDevSQLServer does not show in the list. Neither does SQLOLEDB.

It is now 10 days since we did the conversion. I am actually having unfortunate dreams where by brain is trying to piece this together.

I had an sql guru check out the server last night and here is his response:

"I tried and updated the OLEDB Provider. I am able to telnet to the SQLVS instance. I am able to connect to the SQLVS via ODBC. I have a feeling the password hard coded on the application side may be an issue? This is just a guess as I do not see any other reason for the connection to fail. Is there a way to confirm it?"

So I tried changing the password to something that does not work. It fails immediately when I test in the IDE. But I get the same error (sql server does not exist) when I deploy it. So the application cannot find the server to even check credentials.

Thanks!!

Paul

von Paul Murray - am 14.01.2012 15:36
Hi Paul

It's not pcsoft problem, it has to deal with Sqlconnect string issue, the
reason why your IDE works fine because pcSoft does provide a right connectString
declaration for native/oledb connectString syntax, try something as below
to see if it works for you or not

"Provider=SQLOLEDB.1;Trusted_connection=Yes;Initial Catalog=sp;Data Source=CosmosDB;Network Library=dbmssocn;"


or

Provider=SQLOLEDB.1; 'Database Provider
'Trusted_connection=Yes; 'Use NT Security Login
User id='test';
Password='test';
Data Source=192.168.2.111;
Initial Catalog=sp;
Network Library=dbmssocn; 'Net-Library(TCP/IP)



or *** sqloledb.1 / sqloledb.2

Connect via an IP address

Provider=sqloledb.1;Data Source=192.168.1.168,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Well, I don't understand such at all, just don't care as it works for me.

HTH

King

von Kingdr - am 14.01.2012 16:48
Just use your MyConnection variable

MyConnection is Connection
MyConnection..User = "PBApps"
MyConnection..Provider = hNativeAccessSQLServer
etc

When you put a breakpoint in this piece of code and it stops there when running, hover the hNativeAccessSQLServer setting (or add is as a watch) and I think you will see what I mean

So you could also write
MyConnection..Provider = "SQLOLEDB" // actually the same as hNativeAccessSQLServer
or
MyConnection..Provider = "SQLNCLI"
or
MyConnection..Provider = "SQLNCLI10"

von Arie - am 14.01.2012 16:56
Hi Paul

pls also see below, the paste help fm pcsoft


If you own the two versions of the Native SQL Server Access,
you have the ability to select the version that will be used.
By default, the Native Access via DB-Library
is used if it is installed on the computer ;
otherwise, the Native Access via SQLnCli is used.
The H.SQLServerMode variable is used to force a mode with the following values:

0 to force the Native Access via SQLnCli.
1 to force the Native Access via SQLnCli if it is installed ; otherwise, the Native Access via DB-Library will be used.
2 to force the use of the Native Access via DB-Library.
This variable must be modified before using SQLConnect.

H.SQLServerMode = 1 / 2

Cheers

King

von Kingdr - am 14.01.2012 16:58

SOLVED: Deployment Application Server Cannot Connect To SQL Server 2008

Thanks, King!!

I was just getting on to post that I got it to work.

And I had tried the setting the OLEDB method as well.

It seems like the only thing that I had to change was to use the IP address instead of the server name.

So I got rid of this:

MyConnection..Server = ‘SQLVS’

And used this:

MyConnection..Server = ‘192.168.0.127’

Amazing that after a year of using the server name (SQL), it no longer worked when we changed the cluster name to SQLVS.

I will keep this on hand for my next project.

Thanks again and to everyone who dove in to help!!

Paul

von Paul Murray - am 14.01.2012 16:58
Just as a follow up, I have several PowerBuilder web applications that were all working just fine after the migration to sql server 2008. These applications use a system dsn that works fine connecting to the server name as 'SQLVS'. Go figure.

von Paul Murray - am 14.01.2012 17:13

Re: SOLVED: Deployment Application Server Cannot Connect To SQL Server 2008

Cheers Paul!

Didn't the mayans say 2012 is the years of changing?
And they didn't have computers.

Have a nice weekend.

von Arie - am 14.01.2012 17:43

Re: SOLVED: Deployment Application Server Cannot Connect To SQL Server 2008

Thanks, Arie.

I wasn't kidding when I said that I was having bad dreams where I was trying to figure out the SQL. I reported this as a bug to WinDev.

If the Mayans are right I will not have to worry about WinDev in 2013.

Best,

Paul

von Paul Murray - am 14.01.2012 18:03
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.