Solved

export results to text file sql server 2005

Posted on 2008-06-20
3
3,995 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

624 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