Connection Problem

[Jfury]

Hello everyone,

I'm currently getting the following error:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in c:\Inetpub\wwwroot\~phpdesigner_outputlocal_tmp~2278.php on line 4
Couldn't connect to datasource.

I'm currently running PHP5 V5.1.4, MS SQL Server 2005, and IIS V5.1

The code I'm using to connect is the following:

<?
// Connect to the ODBC datasource 'accessBase1'
//$myDSN = "SERVER=sqlserver;DATABASE=testDatabase;Trusted_Connection=yes";
$connect = odbc_connect("SERVER=sqlserver;DATABASE=testDatabase;Trusted_Connection=yes","user","") or die("Couldn't connect to datasource.");
//$connect = odbc_connect("msaccess","","") or die("Couldn't connect to datasource.");
// form query statement
$query = "SELECT column1 FROM testTable";
// prepare query statement
$result = odbc_prepare($connect,$query);
// execute query statement and display results
odbc_execute($result);
odbc_result_all($result,"border=1");
// We're done with the query results, so free memory
odbc_free_result($result);
// close connection
odbc_close($connect);
?>

I'm able to connect to ms access using odbc. However, I'm unable to connect to the ms sql server which is on a webserver not my local machine.

In addition, I've tested my odbc connection by pressing "Test Connection" while configuring my DNS, and this is the result:

Microsoft SQL Server ODBC Driver Version 03.85.1117

Data Source Name: sqlserver
Data Source Description: SQL Server DSN
Server: adsvapp02
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

Microsoft SQL Server ODBC Driver Version 03.85.1117

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

Any help will be greatly appreciated! 🙂

[jheitz]

Try this as the first argument for odbc_connect:

"DRIVER={SQL Server};SERVER=<ComputerName><ServerName>;DATABASE=<dbName>"

replacing, of course, <ComputerName>, <ServerName> and <dbName> as appropiate. I think the issue is on the SERVER, as you NEED to specify the complete name of your server.

Hope that helps

[Jfury]

Gave it a try, I still recieved the same error. I was wondering if there are any other methods used to connect to the server besides ODBC. I only have experience with php/mysql.

[jheitz]

Yes there are, although I've only used ODBC myself. Could you paste the code you tried after the changes so I can take a look at it? I am currently using PHP 5 with SQL Server Express 2005 myself and all works ok.

[Jfury]

$myDSN = "DRIVER={advsapp02};SERVER=advsapp02;DATABASE=testDatabase";
$connect = odbc_connect("$myDSN","username","") or die("Couldn't connect to datasource.");
//--form query statement
$query = "SELECT column1 FROM testTable";
//--prepare query statement
$result = odbc_prepare($connect,$query);
//--execute query statement and display results
odbc_execute($result);
odbc_result_all($result,"border=1");
//--We're done with the query results, so free memory
odbc_free_result($result);
//--close connection
odbc_close($connect);

Not quite sure if the driver is right.

[jheitz]

Read my previous post. I explained you should do something like this:

"DRIVER={SQL Server};SERVER=<ComputerName><ServerName>;DATABASE=<dbName>"

But as DRIVER you don't have {SQL Server} but {advsapp02}. This is incorrect!

Then, on SERVER you are not stating your COMPUTER'S NAME + "\" + YOUR SERVER NAME, only your server name.... Unless you change these it won't work.

[Jfury]

jheitz wrote:

But as DRIVER you don't have {SQL Server} but {advsapp02}. This is incorrect!

Then, on SERVER you are not stating your COMPUTER'S NAME + "\" + YOUR SERVER NAME, only your server name.... Unless you change these it won't work.

Alright, I've attempted with the following:

<?
$connect = odbc_connect("DRIVER={SQL Server};SERVER=advsapp02;DATABASE=testDatabase","username","") or die("Couldn't connect to datasource.");
//--form query statement
$query = "SELECT column1 FROM testTable";
//--prepare query statement
$result = odbc_prepare($connect,$query);
//--execute query statement and display results
odbc_execute($result);
odbc_result_all($result,"border=1");
//--We're done with the query results, so free memory
odbc_free_result($result);
//--close connection
odbc_close($connect);
?>

I've also asked my server admin the comp/server name and its advsapp02. I'm now getting the following error:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied., SQL state 08001 in SQLConnect in c:\Inetpub\wwwroot\~phpdesigner_output_tmp.php on line 2
Couldn't connect to datasource.

[jheitz]

You mention that:

I've also asked my server admin the comp/server name and its advsapp02.

I don't believe that is even possible. The computer must have a name. When you Open SQL Server's management studio express, as the servername you should have the full name. In my case for example, I have:

D971XB1J\SQLEXPRESS

So maybe in your case it should be ADVSAPP02\SQLEXPRESS, especially as ADVSAPP02 sounds like a more or less standardized server name.... Give it a go...

[Jfury]

I'll give it a try; however, I recently rebooted my computer and now my browser isn't show any errors or values.

I love and hate technology at times - this has never happened to me in the past! :bemused:

[Jfury]

I was wondering if anyone has a link available to a PHP5+IIS+ODBC installation/tutorial?

[thread_PHP]

hi
please find the kevs php/mysql guide chapter containing iis installation below

If you have Windows 2000, XP Professional3, or Server 2003, then install IIS
(if it’s not already on your system): open Control Panel > Add/Remove Programs

Add/Remove Windows Components, and select Internet Information Services (IIS)
from the list of components. If you’re not lucky enough to have IIS at your disposal,
4 you can use a free, third-party Web server like Apache instead. I’ll give
instructions for both options in detail.
First, whether or not you have IIS, complete these steps:
1. Unzip the file you downloaded from the PHP Website into a directory of
your choice. I recommend C:\PHP and will refer to this directory from this
point onward, but feel free to choose another directory if you like.
2. Find the file called php5ts.dll in the PHP folder and copy it to the system32
subfolder of your Windows folder (e.g. C:\WINDOWS\system32).
PHP 4.3
The file is called php4ts.dll for PHP 4.3.
3. Find the file called php.ini-dist in the PHP folder and copy it to your
Windows folder. Once it’s there, rename it php.ini.
4. Open the php.ini file in your favorite text editor (use WordPad if Notepad
doesn’t display the file properly). It’s a large file with a lot of confusing options,
but look for a line that begins with extension_dir, and set it so that
it points to the ext subfolder of your PHP folder:
extension_dir = "C:\PHP\ext"
A little further down, you’ll see a bunch of lines beginning with ;extension=.
These are optional extensions, disabled by default. We want to enable the
MySQL extension so that PHP can communicate with MySQL. To do this,
remove the semicolon from the start of the php_mysql.dll line:
extension=php_mysql.dll
3Windows XP Home Edition does not come with IIS.
4A feature-limited edition of IIS called “Personal Web Server” (PWS) was distributed on the Windows
98 Second Edition CD, and was available for earlier editions of Windows as well. While, technically,
PHP can run on PWS, this Web server is somewhat unstable and has a great many known security
holes. For these reasons, I highly recommend using Apache if an up-to-date version of IIS is not
available for your Windows operating system.
Order this 350 page hard-copy PHP/MySQL book now! 7
Installing PHP
Even further down, look for a line that starts with session.save_path and
set it to your Windows TEMP folder:
session.save_path = "C:\WINDOWS\Temp"
Save the changes you made and close your text editor.
Now, if you have IIS, follow these instructions:
1. In the Windows Control Panel, open Administrative Tools > Internet Information
Services.
2. In the tree view, expand the entry labelled local computer, then under Web
Sites look for Default Web Site (unless you have virtual hosts set up, in which
case, choose the site to which you want to add PHP support). Right-click on
the site and choose Properties.
3. Click the ISAPI Filters tab, and click Add…. In the Filter Name field, type PHP,
and in the Executable field, browse for the file called php5isapi.dll in the
PHP folder. Click OK.
PHP 4.3
For PHP 4.3, the file is called php4isapi.dll, and is located in the
sapi subfolder of your PHP folder.
Can’t click OK?
In older versions of Windows, the OK button may remain disabled even
after you have used the Browse… button to fill in the Executable field.
Simply make a small change to the value of the field using the keyboard
and then reverse it to enable the button.
4. Click the Home Directory tab, and click the Configuration… button. On the
Mappings tab, click Add. Again choose your php5isapi.dll file as the executable
(note that the file type filter in the dialog is set to show .exe files only
by default) and type .php in the extension box (including the .). Leave
everything else unchanged and click OK. If you want your Web server to
treat other file extensions as PHP files (.php3, .php4, and .phtml are common
choices), repeat this step for each extension. Click OK to close the Application
Configuration window.
8 Order this 350 page hard-copy PHP/MySQL book now!
Chapter 1: Installation
5. Click the Documents tab, and click the Add… button. Type index.php as
the Default Document Name and click OK. This will ensure that a file called
index.php will be displayed as the default document in a given folder on
your site. You may also want to add entries for index.php3 and index.phtml.
6. Click OK to close the Web Site Properties window. Close the Internet Information
Services window.
7. Again, in the Control Panel under Administrative Tools, open Services. Look
for the World Wide Web Publishing service near the bottom of the list. Rightclick
on it and choose Restart to restart IIS with the new configuration options.
Close the Services window.
8. You’re done! PHP is installed!
If you don’t have IIS, you’ll first need to install some other Web server. For our
purposes, I’ll assume you have downloaded and installed Apache server from
http://httpd.apache.org/; however, PHP can also be installed on Sambar Server[5],
OmniHTTPD[6], and others. I recommend Apache 1.3 for now, but if you want
to use Apache 2.0, be sure to read the following sidebar.
[5] http://www.sambar.com/
[6] http://www.omnicron.ca/httpd/

[thread_PHP]

hi Jfury
please try this programme

<html>
<head><title>PHP SQL TEST</title></head>
<body>
<h2>MS SQL / DSN Test</h2>
<table border="1">
<?php
$ConnString="DRIVER={SQL Server};SERVER=system12;DATABASE=Top100";
$DB_User="sa";
$DB_Passwd="sachi";
$QueryString="SELECT ContributionDate, Contribution FROM tblContributionsfhf";
$Connect = odbc_connect( $ConnString, $DB_User, $DB_Passwd );

$Result = odbc_exec( $Connect, $QueryString );
echo "\t" . "<tr>\n";
echo "\t\t" . "<td>ContributionDate</td><td>Contribution</td>" . "\n";

while( odbc_fetch_row( $Result ) )
{

    $ContributionDate  = odbc_result($Result,"ContributionDate"); 
    $Contribution  = odbc_result($Result,"Contribution"); 

echo "\t" . "<tr>\n";
echo "\t\t" . "<td>" . $ContributionDate . "</td><td>" . $Contribution . "</td>\n";
echo "\t" . "</tr>\n";
}

odbc_free_result( $Result );
odbc_close( $Connect );
?>
</table>

[Jfury]

Hey guys,

Thanks for the help, I haven't replyed since I didn't want to slow down production so I've been using Access and manage to complete the site ahead of schedule (which means more time on figuring out this problem).

thread_PHP,

I've attempted your script but no avail.

I keep getting the error:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'jbalancio'., SQL state 28000 in SQLConnect in c:\Inetpub\wwwroot\Test-Files\test.php on line 11 😕

Don't worry though, I'll have this fixed one way or another lol 🙂.

[Jfury]

Alas, I finally fixed the damn problem - I basically stopped using Windows NT Authentication and used SQL Server Authentication instead, this pretty much solved all my problems.

Windows = :glare: