Embarassingly basic question 2

[paula]

Here's a nice easy one for you folks...

Somehow I've managed to work with Crystal Reports for some time now, without having to involve myself with or understand SQL or SQL servers (or even much about relational databases. Until now.

I'm now trying to develop reports based on complex SQL server databases, so have found myself a tutorial book on relational databases and basic SQL. The book has exercises in (basically creating a small database and manipulating the data), and I'm shortly getting access to a SQL 7.0 server to create this database on. Presumably, I'll be given a password/username etc. to connect to the server, but where do you write the queries etc.?! Do you use Microsoft Query, or is there something else?

Thanks.

 

[Qaroven]

If you're going to be using MS SQL Server 7, simplest way
to develop SQL queries is with the Query Analyser app.

Ben
+61 403 395 052

 

[link9]

Ben, does the query analyzer have the visual tools that the enterprise manager has?

like where you can just add tables to a diagram, set up the relationships as you want them, and type your criteria for the query in the little dummy boxes down on the bottom, and them just view the SQL code?

If it does, then please tell me where to find it, because I've only been able to find that in the actual table view in enterprise manager.

thx -
Paul Prewett

 

[paula]

Thanks Ben...

When you refer to the 'query analyser', presumably this is software which is supplied with the SQL Server software?

Basically, I'm working on a networked client PC, and will be able to connect to the SQL server with a password/username...do you think the query analyser software will actually be sitting on the server PC, or could be available as a network application?! (clearly I know little about systems architecture too!)

Cheers.

 

[Qaroven]

link9: No as far as i know Query Analyser is just for if you actually want to do everything manually (if are just testing SQL). From memory the only 'visual' thing you can do in QA is change the output from striaght text to a 'visual' cut-n-paste style grid.
I tend to use QA to set up publish/subscribe, etc mainly because Enterprise Manager crashes with alarming regularity if I use it to do the same stuff.

paula: Query Analyser (QA) typically installs with SQL Server so should be on server machine. If you have file access to that machine through your network login you can simply run the server version of QA (<drive>:/mssql7/binn/isqlw.exe from memory) from your client machine (something you can't do with Enterprise Manager - you have to install that on the client machine).

Otherwise you can install QA (or possibly simply copy the file) on your client machine. Ben
+61 403 395 052

 

[paula]

Many thanks, Ben...and you didn't even make me feel stupid! :-D

 

[foxdev]

Paula, I'm kinda the reverse - I struggle with Crystal Reports but am quite comfortable with SQLS. I say this so that you'll know my bias when evaluating my next statement.

From what little I've used CR to produce somewhat complex reports (pulling from 5+ tables), I've found it simplest, more robust, and easier to debug when I create stored procedures in SQLS, then call those SPs from CR, rather than creating large, complex queries in CR. Don't be alarmed, though; to a large extent, stored procedures are merely combinations of individual statements you type in Query Analyzer (a bit of an oversimplification, but one that will work in this case).

One thing that QA will do for you that hasn't been mentioned: it'll allow you to do a basic check of your syntax before executing. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[paula]

Thanks Robert - I've certainly run into problems as my reports get more complex (they're mostly based on large databases e.g. knowhow items database for an international law firm, so table joins are always an issue...hence my interest in SQL so I can get to the bottom of things!

 

[ChrisSharratt]

Paula,
An added hint regarding CR & SQL 7.
Not sure which version of Crystal you use... but in Version 5 + Upwards there is an option to &quot;Show SQL Query&quot; (Under CR Version 7 it's under the Database menu).
This option will display the SQL &quot;SELECT&quot; statement that Crystal executes on the SQL Server... Copying & Pasting this text into Query Analyzer will often give you a head-start.
(NB: You may have to turn &quot;Quoted Identifiers&quot; on within SQL QA. See SQL Books on-line for info on how to do this... basically &quot;SET Quoted_identifiers ON/OFF&quot;.
Regards Chris... DBA

 

[paula]

Thanks, Chris - I do use version 7 of Crystal, so that will be useful as soon as my SQL knowledge has progressed a little!

I'm using a 'SAMS Teach Yourself SQL' book for now which is relatively simple. Could anyone suggest a good reference guide for later on?

Cheers,

Paula