Crystal Reports XI and SQL Server

[TheSlackers]

I'm reasonably good with crystal in terms of formatting data, linking tables etc but have only ever done it using an Oracle DB. I now want to connect to a SQL Server DB to get some info out of there but don't really know where to start. What connection type can I use for a start? Obviously I've always used the Oracle ones before. Also, do I need somethign like TNSnames to let crystal know where the DB name points too?

 

[garyk]

you typically use a TCP/IP connection. Just specify the IP as the server name and then whatever username and password you have been given.

 

[TheSlackers]

you typically use a TCP/IP connection. Just specify the IP as the server name and then whatever username and password you have been given.

Thanks for that I'll try it later and let you know. I think there are a couple of DB's on the same box, not sure how that will work with the IP.

 

[DotNetWebs]

... I think there are a couple of DB's on the same box, not sure how that will work with the IP.

Just define the database you want to use in the "initial catalog" part of the connection string.

Regards

Dotty

 

[Stray__Cat]

The syntax may be alittle different but you should keep in mind 2 points

the rough equivalent of the tns name is the instance name. It may be the name of the machine sql is running or a slashed name. The dba should tell you.

Plus, in sql server you have one level more than Oracle, the database. It contains users contexts and tables.
Oracle: servername->user->table
SQL: servername->database->user->table
So you have to specify the database to connect to into the connection settings.

 

[TheSlackers]

Right, well thanks for the help guys but I ain't getting it.

Just to give you more info I'm using Crystal XI and the "Standard report wizard". Pretty much the first thing it asks you to do is connect to a DB. Usually I'd go, new connection --> Oracle and input my details. Obvioulsy I can't do this for the SQL DB so what I really need to know is what type of link am I after ADO, ODBC, OLE DBetc etc? I looked for TCPIP but it aint on the list.

Once I know that I can figure out what I need to login!

 

[DotNetWebs]

OK I have just tried that for you with the Crystal Reports Version that comes with Visual Studio.

What you need to do is select OLE DB (ADO) in the wizard and then select "Microsoft OLE DB Provider for SQL Server" (if you are not given this option you may have to install the provider)

You are then given a simple form to fill in with the SERVER, USER ID, PASSWORD and DATABASE.

I have just tried it with one of my remote DBs and it works fine.

Regards

Dotty

 

[TheSlackers]

Wicked, will try it asap