Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: auto export select statement

    hi all...
    i'm using ms sql server 2008 r2 advanced express.
    i have a select statement that generates my google shopping feed.
    is there a way to get this statement run automatically and export the results to a tab delimited csv file?
    i know it's a big ask!

    i'm currently using an asp script to create an xml file for the feed, but it's putting too much strain on the server.

    hope someone can advise me.

    thanks in advance
    Andy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    BCP can be used to export data.
    xp_cmdshell can be used to call BCP from the database server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Thanks blindman
    I have created a BCP string that exports the correct data, but there's no formatting or headers. I need the .txt file to be tab delimited and contain my headers.
    Thanks
    Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can specify tab as the field delimiter in your BCP command.
    To include headers, you need to cheat a bit as Brett Kaiser explained here:
    http://www.dbforums.com/microsoft-sq...t-headers.html
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    hi blindman
    before i get to adding the headers, i'm trying to get the bcp to export the data and formatting it using a format file.
    (I'm amazed at how difficult it is to export data out of ms sql!)

    I'm getting this error for my code below:

    'The table name specified exceeds the maximum allowed length'

    ---code---


    bcp "SELECT mydatabase.dbo.Products.ProductID AS 'g:id', mydatabase.dbo.Products.ProductID AS 'link', mydatabase.dbo.Products.Product AS title, mydatabase.dbo.Products.Price * 1.2 AS 'grice', mydatabase.dbo.Products.Image AS 'g:image_link', mydatabase.dbo.Categories.Category AS 'groduct_type', mydatabase.dbo.Manufacturers.Manufacturer AS 'g:brand', mydatabase.dbo.Google_Prod_Cat.Google_Prod_Categor y AS 'g:google_product_category' FROM mydatabase.dbo.Manufacturers INNER JOIN mydatabase.dbo.Categories INNER JOIN mydatabase.dbo.Products ON mydatabase.dbo.Categories.CategoryID = mydatabase.dbo.Products.CategoryID ON mydatabase.dbo.Manufacturers.ManufacturerID = mydatabase.dbo.Products.ManufacturerID LEFT OUTER JOIN mydatabase.dbo.Google_Prod_Cat ON mydatabase.dbo.Products.Google_Prod_CatID = mydatabase.dbo.Google_Prod_Cat.Google_Prod_CatID ORDER BY 'g:id' DESC" out c:\data\format\mydatabase.txt -T -f c:\data\format\mydatabase_format.fmt -S MyServer-PC01\SQLEXPRESS

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do yourself a favor and put that query in a view.
    Will make your debugging of the BCP statement SO much easier....

    "Category AS 'groduct_type'"?????

    Repeating the four-part naming convention in your SELECT clause also adds needless clutter, and could possibly be what is confusing the BCP.

    Once you reference the tables using a four-part naming convention, you can then reference the individual column with just [tablename].[columnname]

    Clean this stuff up, and then see if you still have problems you can't debug.

    Yes...it is difficult exporting from SQL Server. It's really designed to respond to data requests, ("server"), rather than pushing data out on its own.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    ok i'm nearly there!

    i'm using bcp to export my data to a .txt file.
    i have a .txt file containing my headers
    i have a .bat file to append the two and save as my feed.txt

    the issue i have now is that one of my database fields is a product description (description) and contains all manner of data including 'tabs'.
    where my export see's a 'tab' it breaks the formatting.
    is there a way to 'capture' all of the description text in one 'tab'?

    hope that makes sense.

    Thanks again

    Andy

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Instead of doing the BCP against the table, do the BCP against a view or a SELECT statement. Use the SELECT statement to Replace() the TAB characters with an appropriate replacesment (I often use '\t' because many packages import that correctly as a TAB character).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd recommend he use quoted delimiters instead of replacing the tab characters.
    I'm pretty sure quoted delimiters are supported by BCP.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2013
    Posts
    10
    or you can use

    sqlcmd -E -S"Server" -Q"Exec procedurename or SET NOCOUNT ON;select statement" -d "database" -s"," -I -W -h-1 > Filename or -o Filename
    for a comma delimited file with no header info

  11. #11
    Join Date
    Feb 2008
    Posts
    120
    hi all..... and thank you for all of your help so far.
    i've got my bcp working against my tables and i'm getting the results i want.
    one of the fields is a product description field and certain characters are tripping up the tabbed output.
    at the moment i'm using REPLACE(dbo.Products.Description, '', ' ') to remove the but i can't remove the closing
    any ideas please?

    thank you
    Andy

Posting Permissions

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