Post reply

Extract to a file

  • September 14, 2009 at 9:41 am

    #141730

    Hello,

    Has anyone ever tried to run a query to get information from SQL 2005 and put the results to a file? I have address information in a table and I want to create a delimited file for label printing with the address information.

    So, I would like to run a script on my local machine that gets the address information and puts it to a CSV '|' [Pipe] delimited file. Then I can email the file with column headings to a person to do a mail merge for label creation.

    If not the what is the best way to do this?

    Thank you,

    Patrick

  • September 14, 2009 at 10:02 am

    #1053312

    If you can run your script from SMSS, try this:

    Tools\Options\Query Results\SQL Server\Results to Text ... then choose your options.

    At least that's what it is in 2008. I can't remember if that's it exactly in 2005, but it's similar. Then select output to text when you run your script in SMSS.

    Also you can use BCP to execute a query, and BCP options can control the output format.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 14, 2009 at 10:45 am

    #1053334

    Another way to do this would be to create a SSIS package that can extract the file and write to a csv file, you can even get the SSIS package to email the file once it has been created, the wizard will be able to get you started on this or check out BOL.

  • September 14, 2009 at 10:55 am

    #1053339

    Where could I find more information on 'BoL'?

    Also, Besides the ways discussed previously; Could I do this through report manager?

    Just curious, then the client could run a label when ever they want.

  • September 14, 2009 at 11:05 am

    #1053343

    Bol = books on line = SQL sever help . search for SSIS tutorials to get you started.

    If you want a way for the client to run this automaically then (assuming they are using Excel or Word to do the mail merge) you can create a database connection via ODBC to get the data from the database.

  • September 14, 2009 at 3:22 pm

    #1053471

    Hello,

    As anyone used SaveDelimitedColumns or SaveDelimitedColumnsNullQuoted?

    These are some stored procedures that can to exactly what I am looking for but I am having a hard time getting fields with mixed data into the CSV file.

    Thank you,

    PN.

  • September 14, 2009 at 5:50 pm

    #1053514

    You could also try using the command line with BCP. Here's one for the AdventureWorks DB.

    At the command line cd to C:\Program Files\Microsoft SQL Server\90\Tools\Binn

    Then type

    bcp "SELECT CONT.FirstName,C

    ONT.LastName,ADDR.AddressLine1,ADDR.AddressLine2,ADDR.City,SP.StateProvinceCode,

    ADDR.PostalCode FROM AdventureWorks.HumanResources.Employee EMP JOIN AdventureWo

    rks.HumanResources.EmployeeAddress EA ON EMP.EmployeeID = EA.EmployeeID JOIN Adv

    entureWorks.Person.Address ADDR ON EA.AddressID = ADDR.AddressID JOIN AdventureW

    orks.Person.StateProvince SP ON ADDR.StateProvinceID = SP.StateProvinceID JOIN A

    dventureWorks.Person.Contact CONT ON CONT.ContactID = EMP.ContactID ORDER BY CO

    NT.LastName" queryout C:\Users\YourDesktop\Desktop\fileexport.txt -t "|" -S Server -T -c

    You'll have to modify below for your server,database,query,authentication type etc.

    You'll also want to checkout the rest of the parameters to tailor it specifically for your needs.

  • September 15, 2009 at 9:47 am

    #1053898

    Hello,

    I got it to work with the Free ware: SaveDelimitedColumns. Works pretty nice. I can put it on the network drive and the client pick it up.

    I tried the BCP and recieved a bunch of errors. Probably missing something to turn on.

    I thank you all for the ideas and I have gotten good information pieces from you.

    Best regards.

  • September 15, 2009 at 11:25 am

    #1053987

    Patrickn,

    I'm doing the same thing now with my project. In RS, I link to my datasource and insert my query and I do all the formatting I need in there to make it look pretty for the users. Then I have SSIS packages that execute the report (or CSV) on regular intervals to a reports server. And you can also store it on a network drive.

    -M

  • September 15, 2009 at 11:30 am

    #1053989

    I be interested in seeing this a liitle closer. Where did you dig up the research? How do you handle parm data (parameters passed into the script)?

    The thing I worked on is a quick script to run on demand.

  • September 15, 2009 at 11:50 am

    #1054005

    I'm sure there's a BOL somewhere, I just haven't taken the time to look.

    In Visual Studio, you'll want to have the BI component installed to create the report format. And in SQL to use IS, you'll want to use 'jobs'.

    If you tell me a bit more what your looking for or what you want the end result to be I can point you in the right direction.

    -Michelle 🙂

  • September 15, 2009 at 11:56 am

    #1054010

    Here's the BOL for reporting services:

    http://msdn.microsoft.com/en-us/library/ms159106.aspx

    You can pass all your parameters directly in RS, or use their expressions to handle the critera outside of your code. I prefer to handle all my own parameters inside my code and just pass the dataset to RS and do the formatting there.

    -Michelle 🙂

  • September 16, 2009 at 9:37 am

    #1054549

    Thanks for the information. I will review it.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply