Formatting and storing qeueries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Formatting and storing qeueries?

Author

Topic

leighrogers
Starting Member

11 Posts

Posted - 2006-11-02 : 10:18:21

Hi guys!
I have a series of SQL that looks like :

set line 150
set pagesize 20

ttitle - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - In Stock Report - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

column title format a30
column genre format a30
column certificate format a5
column director format a30
column actors format a30
column release format a30

Select dvd.DVDID, release, title from rental, dvd where rental.dvdid = dvd.dvdid and datereturn is not null;

ttitle - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Out of Stock Report - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

column title format a30
column genre format a30
column certificate format a5
column director format a30
column actors format a30
column release format a30

Select dvd.DVDID, release, title from rental, dvd where rental.dvdid = dvd.dvdid and datereturn is null;

ttitle - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Coming Soon Report - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

column title format a20
column release format a20
column certificate format a3
column director format a20
column actors format a25
column release format a9
column genre format a10

select * from dvd where release > '02-nov-06';

Is there anyway that I can save this code as a file a bit like an old batch file in dos. So all the user has to do at the sql prompt it type: "Runreport". ?

Also, I don't like the way the formatting text runs in the window - it makes the reports look messy! Is there any way of hiding the formatting text?

Thanks in advance :-)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-02 : 10:33:51

If this was t-sql you would use osql to run a script file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leighrogers
Starting Member

11 Posts

Posted - 2006-11-02 : 10:37:22

I wish I knew what that meant...

:-/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-02 : 11:51:38

Users do NOT get a "SQL prompt". You give users reports.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

leighrogers
Starting Member

11 Posts

Posted - 2006-11-02 : 12:49:10

:-)
I understand what you mean, and normally i would create a web page and do the reports that way - or use the Oracle report writer.

Unfortunately the course I am on restricts what methods i can use to achieve my goals. IE, my task says erkhmmn, quote

"Create a sql report (not using oracle report writer) demonstrating appropriate functionality for the assigned role. "

My lecturer either cannot, or will not expand on this. I have been told I cannot yet use a front end - so web page is out :-(

This is why my posts seem vague and why I am trying to avoid the most sensible way forward - not because I don't know what I am talking about?!

All I wanted to know is how to store procedures, lines of code - anything in a text file so that I (or someone else) can just type in one word to run the lot!

Thanks for your help :-)

Leigh

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 13:15:42

OSQL is a command line tool provided with MS SQL server that can run a SQL script - and store the output in a file. It won't format the output though, so you're pretty much restricted to just using a column headings and rows output style.

OSQL, it various connection parameters (ServerName/DatabaseName/UserID/Password/etc), Input script and Output results file can be wrapped up in a batch file.

The other thing you could look up in the help file (Books Online) is BCP. It allows format files, but I'm still a bit sceptical that you will be able to use that to make much in the way of formatted report!

Mind you, if you managed to get any of these command line tools to generate a half decent looking report it would be worthy of good marks!

Mind you(2), if you turned up here for interview and told me that's what you had learned I would throw my hands up in horror and cursor your teacher for not getting you to cut-your-teeth on something more mainstream useful ...

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-02 : 13:32:36

>> oracle report writer
Oracle? (hence my comment about t-sql).
Have a look at sql-plus - that might do it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources