How to direct T-SQL executoin output to a file

Is there a SQL Server 2000 equivalent of the Oracle SPOOL command like SPOOL 'C:\TEMP\MyFile.txt' which can be placed at the top of a series of SQL commands to direct the execution messages and results to the nominated file ? I want to use this in a Stored Procedure executed from within Access and capture all system and error messages at the server level.

hi,

open sql query analyzer

click on "query"

click on "results to file"

run a query.

or better yet

run BCPor the bulk copy program

regards,

joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

I found Steve's tip very helpful, however I also found out that you have to refer to the database name that the table belongs to even if the database you are refering to is the currently selected database within query analyser. This is because the command shell cannot see what the default database within query analyser is.

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

How to direct T-SQL executoin output to a file

Is there a SQL Server 2000 equivalent of the Oracle SPOOL command like SPOOL 'C:\TEMP\MyFile.txt' which can be placed at the top of a series of SQL commands to direct the execution messages and results to the nominated file ? I want to use this in a Stored Procedure executed from within Access and capture all system and error messages at the server level.

hi,

open sql query analyzer

click on "query"

click on "results to file"

run a query.

or better yet

run BCPor the bulk copy program

regards,

joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

I found Steve's tip very helpful, however I also found out that you have to refer to the database name that the table belongs to even if the database you are refering to is the currently selected database within query analyser. This is because the command shell cannot see what the default database within query analyser is.

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
MikeNevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
MikeNevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>sql

How to direct temp files for DTS packages

I have several DTS packages that import FoxPro data into SQL 2000. These
jobs create a temp file about the same size of the data that I am importing.
I call the DTS packages as a SQL job. If the job runs successfully then the
temp files are removed when it finishes, however if the job fails it leaves
the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
directory and I start to run low on disk space until I manually delete the
files.
Is there a way to direct where these files go? I would like to change this
to the D:\ where I have a lot more space.
Thanks
Mike
Nevermind. I found it. It is a windows configuration setting.
"Mike" <Mike@.Comcast.net> wrote in message
news:u7yCs%235MEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I have several DTS packages that import FoxPro data into SQL 2000. These
> jobs create a temp file about the same size of the data that I am
importing.
> I call the DTS packages as a SQL job. If the job runs successfully then
the
> temp files are removed when it finishes, however if the job fails it
leaves
> the file in the C:\Documents and Settings\SQLUser\Local Settings\Temp
> directory and I start to run low on disk space until I manually delete the
> files.
> Is there a way to direct where these files go? I would like to change this
> to the D:\ where I have a lot more space.
> Thanks
> Mike
>

How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||for data files look at
dbcc showfilestats
This is undocumented dbcc command.
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
For log files
look at
dbcc sqlperf(logspace)
Regards
Amish shah
http://shahamishm.tripod.com
*** Sent via Developersdex http://www.codecomments.com ***|||Uri Dimant schrieb:

> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you[vbcol=seagreen]
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...|||Amish Shah schrieb:

> for data files look at
> dbcc showfilestats
> This is undocumented dbcc command.
> It shows total size and used size in extents.
> If you multiply it by 64 you will get size in KB.
> For log files
> look at
> dbcc sqlperf(logspace)
>
> Regards
> Amish shah
> http://shahamishm.tripod.com
> *** Sent via Developersdex http://www.codecomments.com ***
Thank you Amish,
that really does the job but how do i get this information into a
table?
like:
select into table ( dbcc showfilestat )
thats what i would like for capacity planning and charting.
Thank you very much, Ulli|||Hi Ulli
You have to create your own table. By looking at the output of dbcc
showfilestats you can determine the number of columns and an appropriate
datatype.
Once you have the table created, you can do the following:
INSERT INT MyFileStatsTable
EXEC ('DBCC showfilestats')
HTH
Kalen Delaney, SQL Server MVP
<ulli77@.web.de> wrote in message
news:1154272978.472934.27330@.b28g2000cwb.googlegroups.com...
> Amish Shah schrieb:
>
>
> Thank you Amish,
> that really does the job but how do i get this information into a
> table?
> like:
> select into table ( dbcc showfilestat )
> thats what i would like for capacity planning and charting.
> Thank you very much, Ulli
>

How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I'm trying to figure out the amount of free space in the datafiles but
> > since sys.dm_db_file_space_usage works only for temp, I dont know which
> > table to join on sys.database_files or sys.master_files.
> >
> > Can anyone help me please?
> >sql