Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: DTS export in sql server 2000

    Hi,
    I want to export the table data of a database in sql server 2000 to a text file.Is there a way I can do it through some query or command line. If yes can anyone tell me the query. I would like to avoid doing it through UI.

    Regards,
    Seena

  2. #2
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Certainly!

    From BOL:

    bcp Utility
    The bcp utility copies data between an instance of Microsoft® SQL Server™ 2000 and a data file in a user-specified format.

    Syntax
    bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

    For example, the Northwind database has the table Jane's Orders, which is owned by user Jane Doe. To bulk copy this table from the Northwind database to the Orders.txt file using the login Jane Doe and the password go dba, execute one of these commands:

    bcp "Northwind.Jane Doe.Jane's Orders" out "Jane's Orders.txt" -c -q -U"Jane Doe" -P"go dba"

    bcp "Northwind.[Jane Doe].[Jane's Orders]" out "Jane's Orders.txt" -c -U"Jane Doe" -P"go dba"

  3. #3
    Join Date
    Apr 2008
    Posts
    8

    Delimiter seperation

    Hi,
    Thanks for the help. I am able to get the data in text file. Just a small doubt

    Can I seperate the column values by a delimiter like a comma?

  4. #4
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Try this.....

    Code:
    bcp pubs..jobs out c:\jobs.txt /c /t , /r \n /Sservername /Usa /Ppassword
    Replace the comma above with whatever terminator you want to see in its place.

  5. #5
    Join Date
    Apr 2008
    Posts
    8

    Column names

    Hi,
    Thanks for the help
    I was able to use another delimiter using -t , in the syntax of bcp.
    One more small doubt- I have written a command for bcp using query as follows

    bcp "select CustomerID from Northwind.dbo.Customers" queryout c:\tablepuge\r
    esultdata.csv -t , -T -c

    I am able to get the customerid in the table to a .csv file correctly. I wanted to know is there a way I can get the column name also in this file so that the ids come below the column name in this case customerid

  6. #6
    Join Date
    Apr 2008
    Posts
    8

    bcp command to get column names

    Hi,
    I am using the following bcp command in a batch file

    bcp "select CustomerID from Northwind.dbo.Customers" queryout c:\tablepuge\r
    esultdata.csv -t , -T -c

    I am getting all the column values in the .csv file. Is there a way i can get the column names also written to the .csv file?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's a pain in the pooper this is. There are a few ways. All are bodges to a greater or lesser degree. The easiest is to output a sort of specification file from the system tables if that is agreeable. If not, how high tech are you prepared to go?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Threads merged
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    You can hard code the names, but I'd advise against it. It's just not a very elegant solution.

    But if you need a quick bandaid for this issue....

    Code:
    IF EXISTS (SELECT * FROM sysobjects WHERE name='vJobs') DROP VIEW [dbo].[vJobs]
    GO
    CREATE VIEW [dbo].[vJobs]
    AS
    SELECT 'job_id' AS job_id, 'job_desc' AS job_desc, 'min_lvl' AS min_lvl, 'max_lvl' AS max_lvl
    UNION ALL
    SELECT CAST(job_id AS CHAR(6)), CAST(job_desc AS VARCHAR(60)), CAST(min_lvl AS VARCHAR(60)), CAST(max_lvl AS VARCHAR(60))
    FROM [dbo].[jobs]
    Then use bcp to give you the output....

    Code:
    bcp pubs..vJobs out c:\jobs.txt /c /t , /r \n /SServerName /Usa /PPassword
    I'm sure there's a way to do this using syscolumns coupled with sysobjects. I just wasn't able to put one together before my first cup of coffee.

    I've never used format files with bcp, so Pootle's suggestion might be the best one here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •