Linking to SQL Server 2000

[jpesolutions]

I have been using CF for a number of years but always with Access. Not having
SQL Server 2000 experience is holding me back. Can anyone give me or point me
toward some really good information on how to link to a SQL Server to pull data
from. Even an example of code would be great (or as much code as is possible
as I know some of it is set up in the CF Administrator).

Thanks,

Jason Presley

[The ScareCrow]

The actual code to interact with the sql server is basically the same as
access. There are a number of functions that are differrent and sql server has
stored procedures and dts ect...

The books online under the sql server programs group will give you heaps of
information.

To connect via CF

Just create a DSN as in access, the difference being
First you need to create an sql server account and give this account
permissions to the sql server database(s)
Then in the CF admin panel
Create a new DSN with the sql server driver
Enter the name or ip address of the sql server (localhost will work if it is
on the same box)
Enter the name of the database
Enter the username and password

That's it

You can also create the dsn through a windows account, but this is a bit more
involved.

Ken

[jpesolutions]

I thought that i was a pretty smart guy but this thing is kicking my butt!
Here is what I am putting into the CF Admin.

CF Datasource Name = jpTest
Database = jpTestDB
Server (I have tried) = 127.0.0.1
(local)
localhost
C:\Program Files\Microsoft SQL
Server\MSSQL\Data
C:\Program Files\Microsoft SQL
Server\MSSQL
Username = sa
Password = (my password set for the sa account in SQL Server)

I have CF running on Apache 2.0 located on the C:\Program Files\Apache
Group\Apache2
The SQL Server is MS SQL Server 2000 MSDE located at C:\Program
Files\Microsoft SQL Server\MSSQL
The tree structure in the SQL Server Enterprise Manager is:
Console Root> Microsoft SQL Servers> SQL Server Group> (LOCAL)
(Windows NT)> Databases> jpTestDB

If there are other paramaters or things that would be helpful please let me
know. Evidently someone needs to come down to my Kindergarten level and hold
my hand through this thing. If anyone would be able (and willing) to help I
would greatly appriciate it. Maybe giving me the exact things to type into CF
Admin would help. I have found sites that tell me to enter the server address
into the server field but I don't know what the server address is so that is no
help. If you can break it down into tiny very simple instructions I would
greatly appriciate it!

Thanks

Jason

[mxstu]

Jason,

What kind error are you getting? Can you post the error message?

[jpesolutions]

That I can do! :D

Connection verification failed for data source: jpTest
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing
socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC
Driver]Error establishing socket. Connection refused: connect

[mxstu]

Do you have a firewall runinng? I've received errors like that due to firewall issues.

[jpesolutions]

Yes. I am on my computer at work and they have a network wide firewall. I'll check into that. Thanks

[The ScareCrow]

Also if you using Windows XP you might need to check out this link

[url]http://www.winnetmag.com/SQLServer/Article/ArticleID/43777/SQLServer_43777.html[/url]

Ken