VFP and SQL Server - Connection Failed

[tygger]

Hi. I pretty much have no knowledge of VFP. A bunch of the queries are linked to SQL server tables and recently, the SQL server switched IP addresses.

Now VFP can't connect to the SQL tables and I'm getting:

Microsoft SQL Server Login

Connection Failed
SQLState:'01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
Connection failed:
SQLState:'08001'
SQL Server Error:17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.


The password and user login to SQL are correct because I can still login using Enterprise Manager.

Is there a file where I need to go in to change to the new IP address?

Thanks

 

[Mike Lewis]

Hi Tygger,

Chances are the problem is at the DSN level. Can you find the SQL Server DSN Configuration Dialogue for this connection? Look in your ODBC applet in Control Panel / Administrative Tools. If you can find it, there should be box labelled "Which SQL Server do you want to connect to?" That's probably where you need to enter the new IP address.

If that doesn't help, can you post some more information about how your VFP app is connecting to SQL Server -- ideally, the actual code that's causing the error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[tygger]

Hi Mike.

The problem is, I don't know where to look. I see a bunch of VFP files in a folder, but I dont know what each one does? I'm not familar with the various extensions.


You said to look for the Control Panel / Administrative Tools. I need to be walked through... when I open up VFP, I get a blank screen and a blank Command box.

Where do I go from here? Do I need to look for a certain file to change the IP address?

Thanks for your help.

 

[Mike Lewis]

Tygger,

OK, let me try to walk you through it. In fact, you don't need to open VFP for this exercise.

1. Go to Control Panel, then Administrative Tools, then Data Sources (ODBC).

2. Look at the first three tabs in the ODBC Adminstrator (User DSN, System DSN, and File DSN). You are looking for the DSN that is used to connect to your SQL Server database. I have no way of knowing what this is called. If you don't recognise the name, we're stumped.

3. Assuming you can identify the DSN, click on it, then click the Configure button, to the right.

4. In the next dialogue, you should see a box labelled "Which server do you want to connect to?". That's where you need to enter the new server IP address.

5. Once you've done that, click Next a few times (don't change any of the settings), and then click Finish.

With a lot of luck, that will solve the problem. If you get stuck at step 2, or if these steps don't make any difference, .... well, let's cross that one when we get to it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[tygger]

Mike,

Thanks! I was thinking I had to change the address within VFP. I made the change on my comp's ODBC settings and all is working now.

Thank you for you help.

 

[JRB-Bldr]

MIke's suggestion is that you look in the workstation's Control Panel, Administrative Tools, Data Sources. Go to the DSN tab page and review the specific ODBC DSN settings for that specific SQL Server connection that your VFP is trying to use.

That is typically where the IP address is stored, not in the VFP code itself.

When you have confirmed the correct IP setting at the Windows DSN level, here is one way to test it.

1. In Windows, make a file called "SQL_Test.udl" with Notepad.
2. Right-click the file (in windows) and select "Properties" from the context menu.
3. Click the "Provider" tab and select the appropriate DSN name provider for your database.
4. Click "Next" and fill in the requested information in the Connection tab.
5. CLick "Test Connection" and the OK to save the settings into the .udl file.

If you have your settings correct you will get a good Connection and it has nothing to do with VFP.

However if it all works, then your VFP should again work.

Good Luck,
JRB-Bldr

 

[tygger]

JRB, thanks for the testing technique.

You guys are awesome!

 

[Mike Lewis]

Tygger,

Delighted to hear that it's working.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)