2dawgs Posted March 12, 2012 Share Posted March 12, 2012 Hello forum - I wonder if someone can give me a clue-by-four on this problem I've been stumped on. After searching here and elsewhere on the web for hours, trying different drivers and different connection methods, going around in circles with conflicting documentation, I'm still no closer to a solution. I'm trying to get php to connect to a SQL Server 2005 using odbc, and getting a return status of IM014, which maps to "The specified DSN contains an architecture mismatch between the Driver and Application." I've got SQL Server 2005 (64-bit) running on Windows Server "Enterprise" (64-bit), and the web server (IIS7) on Windows Server 2008 (also 64-bit). The driver I'm using is the sqlncli_x64.msi driver obtained from here: http://www.microsoft.com/download/en/details.aspx?id=15748. This comes with 32-bit and 64-bit drivers, and I'm getting this same error using either of them. Here's a code snippet I'm using to build the connection string: $DSN="Survey_Data_64"; // name of dsn I created on the IIS7 box $Username="XXXXXXXXX"; $Password="XXXXXXXXX"; $connect = odbc_connect($DSN, $Username, $Password); echo odbc_error() . "<br />"; I presume this is something going on between IIS7 and the driver, since the ODBC data source manager says the DSNs I created for both drivers successfully connect to the database server. I also tried the DSN-less version of the call, and got a different error message - it doesn't work either way. Anyone see what I might be overlooking? Tenkyoo! Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/ Share on other sites More sharing options...
cpd Posted March 12, 2012 Share Posted March 12, 2012 The error would suggest the drivers and database type are incompatible. Try using Microsoft's SQLSRV driver. That's what I use and it works a charm. I'm fairly sure it'll work in SQL Server 2005. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1326548 Share on other sites More sharing options...
2dawgs Posted March 13, 2012 Author Share Posted March 13, 2012 The error would suggest the drivers and database type are incompatible. If I'm not using the right driver, then which is the right one, and where can I obtain it? Try using Microsoft's SQLSRV driver. That's what I use and it works a charm. I'm fairly sure it'll work in SQL Server 2005. According to php.net, it's only supported with SQL Server 2008 (http://us3.php.net/manual/en/sqlsrv.requirements.php). Nevertheless, I tried it anyway (in fact, that was the first method I tried), but was not able to get that to work, either. That probably could be the subject of a thread of its own. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1326799 Share on other sites More sharing options...
cpd Posted March 13, 2012 Share Posted March 13, 2012 In that case you will want the slightly outdated MSSQL drivers which should be lurking around on the web somewhere. They should connect you to SQL Server 2005. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1326936 Share on other sites More sharing options...
2dawgs Posted March 13, 2012 Author Share Posted March 13, 2012 Thanks for taking the time to help me out, CPD. While I was waiting to hear back, I did some more digging... At this point, I was able to figure out the part about why the sqlsrv extension didn't work. php.net says you need the SQL Server 2008 Native Client to work with the sqlsrv extension. This is no longer correct. On March 6 (which happens to be the day I first attempted this), a newer version of the sqlsrv extension was released. The newest version (3.0) requires the SQL Server 2012 Native Client, which btw is backwardly-compatible with SQL Server 2005. Once I finally stumbled onto that crucial piece of information (which I ignored previously since I thought I already knew which version of the native client it needed), I installed the sql 2012 native client and was able to get the script that was connecting with sqlsrv working. Red Solo cup time! Ehhh... maybe not just yet. In order to support our legacy code, I still need a working ODBC connection, and this didn't fix the problem with my ODBC-based script. (ODBC also relies on the native client.) Since I can now confirm I have a working Native Client, and the DSN I created which uses it says it can connect successfully, I'm now back to scrutinizing my own code. It is now returning an error code of "28000" (Login failure), which seems to suggest the script is now connecting to the database, but not really sure since there is no sign of the the login failure present in the SQL Server logs. Since this is the same user name and password I'm using in my working script (that uses sqlsrv) - yes, I checked... about 20 times - I'm ruling that out as the problem. (For now... ) I'll just keep posting as I find out more, but feel free to jump in if you have any thoughts about this. Thanks again for your help! Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1326945 Share on other sites More sharing options...
2dawgs Posted March 13, 2012 Author Share Posted March 13, 2012 Solved the ODBC issue. I was referring to the database server by its IP address in the connection string. Apparently, it must be the host name. Thanks again for the help - hope this saves someone else some frustration. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1326980 Share on other sites More sharing options...
cpd Posted March 14, 2012 Share Posted March 14, 2012 You should consider a PDO connection as well. I know you say you need an ODBC (reasons for this I'm unsure of) but perhaps try a PDO connection instead. I know Pascal Data Objects work with SQL Server 2005 so one would assume the PHP Data Object, which Pascal Data Objects is vaguely based on, would work. I do understand you've got it working as well but exploration could lead to a better solution. Depends if your willing to, and can, put the time in; sometimes time isn't on a programmers side. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1327262 Share on other sites More sharing options...
2dawgs Posted March 14, 2012 Author Share Posted March 14, 2012 I agree, and I did come across some information about PDO while working on this, and there is a lot I like about it. I need to maintain ODBC connectivity on a site (which I am in the process of rebuilding), at least for the time being, to keep all the old stuff running through server migrations and other changes in the hosting environment. However, any new development we do will definitely be done using something else, since it appears ODBC is nearing its EOL. Thanks for the info! Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1327284 Share on other sites More sharing options...
cpd Posted March 14, 2012 Share Posted March 14, 2012 I agree, and I did come across some information about PDO while working on this, and there is a lot I like about it. I need to maintain ODBC connectivity on a site (which I am in the process of rebuilding), at least for the time being, to keep all the old stuff running through server migrations and other changes in the hosting environment. However, any new development we do will definitely be done using something else, since it appears ODBC is nearing its EOL. Thanks for the info! I thinks ODBC has a fair few more years in it, at least while MySQL is still around haha. Just so you know, using a PDO connection shouldn't effect your code too much. All that would need changing is a bit of syntax and a bit of additional code required for binding parameters. Anyway, I'll leave you be with your project, I've rambled enough. Quote Link to comment https://forums.phpfreaks.com/topic/258742-odbc-error-im014-connecting-to-sql-server-2005/#findComment-1327482 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.