Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3999
  • Last Modified:

export results to text file sql server 2005

could someone walk me through the process of getting this script out to a text file in sql server 2005?
i thought i could just do SQLCMD mode ... but that doens't work.  its just the adventure works db ....

here's the syntax i have... feel ffree to edit it as you wish.  again... i would like instructions on what to do... where to go... what to click... etc.

bcp "SELECT top 10 * from dbo.databaselog" queryout c:\test.txt
0
alenknight
Asked:
alenknight
  • 2
1 Solution
 
sbagireddiCommented:
This is a more generic script but reusable:


CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select top 10* from information_Schema.tables where table_name='databaselog')
    Begin
        Declare @str varchar(1000)
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''
        Exec(@str)
    end
else
    Select 'The table '+@table+' does not exist in the database'

EXEC BCP_Text_File 'DatabaseLog','C:\DatabaseLog.txt'
0
 
alenknightAuthor Commented:
isn't bcp supposed to work by itself?  why create a stored procedure?  i would like to not have to edit a stored procedure each time i wanna change table
0
 
sbagireddiCommented:
Oops..typo


Corrected script:


CREATE Procedure BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name='databaselog')
    Begin
        Declare @str varchar(1000)
        set @str='Exec Master..xp_Cmdshell ''bcp "Select top 10* from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''
        Exec(@str)
    end
else
    Select 'The table '+@table+' does not exist in the database'

EXEC BCP_Text_File 'DatabaseLog','C:\DatabaseLog.txt'
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now