Re: Writing SQL Data to a File

Dale E. Reed Jr. ( (no email) )
Fri, 13 Mar 1998 00:53:13 -0800

Jeff Cummings wrote:
> I'm looking for a way to query my database and then take the results and write them
> out to a file.
> It needs to be something that I can build, and then schedule to run daily.
> Anyone done this??

You can look at bcp (table in/out) or isql. isql is a console app that
is pretty extensive. Remember you can make the output basically anything
you want by formating the results in the query. For example, lets say you
wanted to create a script to create users on a mail server. You could
create a stored proc like this:

Create Proc NewUsers as
SELECT command='adduser ' + sa.Login + ' ' + sa.Password + ' ' + d.MailDomain
From MasterAccounts ma, SubAccounts sa, NetSoftwares ns, Groups g, Domains
Where ma.CustomerID = sa.CustomerID AND sa.NetSoftware=ns.NetSoftware
AND ma.maExpireDate Is Not Null AND sa.Login <> ''
AND (sa.saNew=1 OR ma.maNew=1)
AND sa.Active<>0 AND ma.Active<>0
AND ma.GroupID=g.GroupID and g.DomainID=d.DomainID
AND maExpireDate >= GetDate()

And get results that you could execute in a batch file, like:

del temp.bat
isql -Q "newusers" > temp.bat
call temp.bat

Here is the information about isql:

C:\>isql -?
usage: isql [-U login id] [-e echo input]
[-p print statistics] [-n remove numbering]
[-c cmdend] [-h headers] [-w columnwidth] [-s colseparator]
[-m errorlevel] [-t query timeout] [-l login timeout]
[-L list servers] [-a packetsize]
[-H hostname] [-P password]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-S server] [-d use database name]
[-r msgs to stderr] [-E trusted connection]
[-i inputfile] [-o outputfile]
[-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary (this screen)]

-- Dale E. Reed Jr.  (       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |