Link to home
Start Free TrialLog in
Avatar of Dawn Cassara
Dawn Cassara

asked on

PHP-MSSQL 2016 connection requirements for Windows 2012

I have a PHP 7 program that needs to insert/update MSSQL 2016 on a Windows 2012 R2 server.
I'm using the same credentials as ASP programs, but it will not connect.
I've made sure its not a firewall problem - it's all on the same server, but I am testing on a browser and get the same error message when running it on the server (in Chrome).
I downloaded and installed the PHP 7 required dlls and added the extensions in php.ini.  I have both ts and nts enabled as I have no idea. I'm pretty sure it should be x64.
I downloaded and ran the Microsoft oledb driver (4.0 - msodbcsql.msi) which I found was required in a ton of posts.
There must be some other requirements I'm missing.
The error:
Notice: Trying to get property of non-object in C:\inetpub\wwwroot\MCP\CSVTest.php on line 91
error db connection
Avatar of JesterToo
JesterToo
Flag of United States of America image

My guess is the connection is failing at line 16 in your connect method of the script you posted.  The line 91 error referenced in your question is in a different file and is probably trying to consume a connection object that is null.

Try wrapping the connect() method with some error trapping  code to reveal why it is failing.
Avatar of Dave Baldwin
On IIS, you need the 'nts' versions of the 'sqlsrv' drivers.  I believe that you need the 32-bit version of PHP 7 to connect with those drivers.

You also need the Microsoft ODBC Driver 13.1 for SQL Server from this page: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
Avatar of Dawn Cassara
Dawn Cassara

ASKER

I read a post that said that it was easier to get ADODB to work - and that's what I'm using in asp and it works, but I can't find code examples of a connection in PHP with ADODB.
Any help on that?
No.  I've been using 'sqlsrv' with PHP ever since PHP 5.3.  The examples in the docs that come with the driver work just fine.
First, how do I tell which PHP 7 this server is set up to use?  32 or 64 bit.  

Now - there's a new twist.  I am writing the PHP to run as a service to update SQL files from CSV nightly, although I'm just testing it in a browser at this point.
So last night I wrote a program to do the same thing in an asp program - vbscript (to put in a .bat file and run with cscript, you must rename it to .vbs and take the <% %> off).  So, when run from the browser, it connects to the database just fine, but when run through task scheduler as a .vbs, it will not.  It completes normally but doesn't update the database as it does in the browser, but I don't think there is any way to put error trapping in vbscript.

However, how do I add error trapping to PHP?  I know vbscript better, so please give an example where it would help on the connection attempt.
And I need to write to a text file instead of the screen because I have to run this in task scheduler, if you can point in the right direction for that please.

So I think these two problems are related - maybe a security issue?  I checked out my settings and permissions in SQL and they look OK, and they work in .asp - I even enabled guest and tried that for the .php and .vbs scripts.  I searched for anything related, but could not find what user that task scheduler would use other than the one I set it to use - administrator for now.

Thank you!
Microsoft ODBC Driver 13 for SQL Server is installed under Programs and Features and Microsoft SQL Server 2016.
Got more error trapping on the PHP - SQLSTATE: 08001
code: 2
message: [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLSTATE: HYT00
code: 0
message: [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired
SQLSTATE: 08001
code: 2
message: [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
Actually, you can put error trapping/handling in vbscript relatively easily.... see the "on error..." statements.  And, to run a vbscript file from the task scheduler I believe you have to run it as an argument to the cscript.exe command.  I don't think task scheduler honors file associations.  I've not done this in a long while and am just going on memory here.
I tried that but apparently the access to SQL is not set up for that, however, I got a bat file working:
"C:\Program Files\Internet Explorer\iexplore.exe" "http:\\www.xxxxx.com\xxx.asp"

I'd rather run the bat file this way if you see the error:
"c:\Windows\System32\cscript.exe //Nologo  //B"   "c:\xxx\xxxx.asp"
I'm not sure what you mean by "apparently the access to SQL is not set up for that"   .... could you elaborate on that?

However, you can run that last command format if you add the switch "//E:VBS".  Otherwise, cscript only supports .vbs, .js, and .wsf file extensions.
The vbs (although the same as the asp without the <% %>) completes successfully in task scheduler, but doesn't produce any results (attached).
Bat: "c:\Windows\System32\cscript.exe //Nologo  //B //E:VBS"   "c:\XYZ1\test.vbs"
The vbs doesn't write to the log, so I can't tell what it's doing.   It doesn't update the database.
The asp runs fine in the browser - exact same code, and writes to the log when finished - no errors.
Can you see why that may be?
This is the new code.  Test.txt
I'm still looking for help on this last question - why the same code runs in the browser as an asp but will not in a bat file as a vbs.
The vbs code is attached above.  It's a very short script - not complicated. Opens two text files, one to read and output to an SQL table - the other text file is the log.
I'm afraid that if it runs in a browser, it will time out and not finish - I've had that happen before and it would be a disaster.
Dawn, I've not had time to delve into this yet... yesterday was Sunday and I spent most of the weekend with my family.  But, I'll try to look at it sometime today.  I also have some other EE questions I'm involved with.  Perhaps someone else will be able to help you before I can get back to this question.

I agree with you that on the surface it seems odd that one method would work and another would not.  I'll have to create an equivalent data environment to test with so it could take a little while to test.

If this is a "time critical" problem you need solved right away then you might want to consider posting a "gig project" to attract more/better (not me, however!) help.
It's not too time sensitive - I'll take what I can get!  Thank you so much!
By the way - it's already happening - it's only copying half the tables before it times out.
It doesn't like the "SERVER." references - I tried just removing them but that didn't work.
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm so sorry your father is in the hospital.  I hope he gets better soon.
I actually had the same code without the Option Explicit and when put into the bat file, does complete, but doesn't do any work.
So I copied your code, put the correct parameter values in, renamed it to an asp and put the <% %> and ran it in the browser and it get's an error - "Expected Statement - Option Explicit", which I've never been able to run an asp with that in it without an error.
It worked, but then I used the bat file to run it in Task Scheduler and same thing - no results.
I'd appreciate any thoughts - I need to get this working as the asp script times out before completion.
Might it be a permissions problem?
I did rename it to a .vbs and used this bat file code: "c:\Windows\System32\cscript.exe //Nologo //B //E:VBS"   "c:\MCP\test.vbs"
I got these errors when running it from the task scheduler:
1 -424 - Object required for SQL connection
2 -424 - Object required for 20170502

Might it be a settings problem?  Do you need to put a qualifier for the createobject statements?  
Why does it not like Options Explicit?
I've checked the permissions and the user that I set to run it in the task scheduler has all rights to everything here.
GOT IT - There was a backslash missing in one of the paths - hard to see!
THANK YOU SO MUCH!!!!
So the test2.vbs is now running cleanly, but the bat file still produces no errors but no results:
"c:\Windows\System32\cscript.exe //Nologo //B //E:VBS"   "c:\MCP\EXPORT\test2.vbs"
I right-clicked and ran as administrator in the folder and I set it up in task scheduler - nothing.  No entries into the Log file.
but the bat file still produces no errors but no results...

Besides the call to cscript, what else is in the .bat file?

As for the other questions:

Option Explicit is for VBscript and "classic" VB code (aka VB6) only.  It's purpose is to force all variable to be declared or the script fails to run.  Really useful for detecting attempted usage of mis-spelled variables.

No, there is no "qualifier" for "CreateObject" except when running in an asp page... then, you MUST use the "Server" COM object reference.

Also, "//E:VBS" option is only required to tell cscript what flavor of code is contained in the file you're running when its extension isn't .vbs... it assumes a .vbs file is VBscript.

Will work some more on it later today.
I can run it in the task scheduler as a .vbs file now, which solves the timeout problem, but wanted to know the benefits of running it with cscript, if any.
There is nothing else in the bat file yet.
I don't think there is any difference at all... cscript is actually running the script file either way.

When testing a script do not put use the //B option... it prevents script errors from being displayed.
Thanks so much for your help!  I couldn't have worked through this without it.