none
Importing data with special character out of sql server

    Frage

  • I have a sql script that I run in sql server. After I get the results, I need to import them out to excel file. Since I am on a production server, I only have a read only rights. I can not create any object on the server. This leaves me with either copy data or save results as csv. both do not work well with me due to some fields with special characters that causes these fields to wrap around when I copy them to excel file, and I get several blank rows. These fields are email, address, phone (some phone #s are entered with + sign in the start of the field). I am not sure what to do to get this data out. I tried coping the data, one column at a time and do a lot of data manipulation to get the data out. I tried bcp to create fmt file, however, the links I found, only have the sample to format a table/view in the database into the .fmt file, is there any better way to export the data set out to excel?

    thanks

    Mittwoch, 30. Oktober 2013 03:15

Antworten

  • Most welcome! The SSIS package can be executed on another server/machine as long as you can establish a remote connection from that server/machine to the respective SQL Server Instance. Is it possible for you to achieve this?


    Per aspera ad astra!
    journeyintobi.com

    • Als Antwort markiert al.sallam Freitag, 1. November 2013 02:50
    Mittwoch, 30. Oktober 2013 15:15

Alle Antworten

  • Hello there!

    One simple solution would be to create a SSIS package with a data flow task which would do the following:

    - connect to the SOURCE SQL Server using an OLE DB Source. In here specify the SQL Script which you want to run and then configure the desired output columns;

    - do any data transformations in between, if you want to;

    - create an Excel Destination and write the information directly in the Excel file by mapping the columns specified in the previous step to the columns in the Excel destination.

    Here is a simple example on how to achieve this.

    Please let me know if this solves your problem!

    Best regards,


    Razvan


    Per aspera ad astra!
    journeyintobi.com

    Mittwoch, 30. Oktober 2013 09:50
  • Thank you Razvan for your response. I think that this solution would work fine. The only problem that I am having as I stated in my question is that, I can not create anything on the production server. It is a long process to get anything approved. short of creating SSIS, is that another way?

    thanks

    Mittwoch, 30. Oktober 2013 15:05
  • Most welcome! The SSIS package can be executed on another server/machine as long as you can establish a remote connection from that server/machine to the respective SQL Server Instance. Is it possible for you to achieve this?


    Per aspera ad astra!
    journeyintobi.com

    • Als Antwort markiert al.sallam Freitag, 1. November 2013 02:50
    Mittwoch, 30. Oktober 2013 15:15