Solved

export results to text file sql server 2005

Posted on 2008-06-20
3
3,991 Views
Last Modified: 2012-06-21
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
Comment
Question by:alenknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:sbagireddi
ID: 21835097
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
 

Author Comment

by:alenknight
ID: 21835112
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
 
LVL 8

Accepted Solution

by:
sbagireddi earned 500 total points
ID: 21835117
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 53
How to use TOP 1 in a T-SQL sub-query? 14 54
Help with SQL - TOP 10 by date and by group 13 45
Getting max record but maybe not use Group BY 2 38
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question