Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2014
    Posts
    4

    Unanswered: help with export to excel

    I put this together to export the user name /password to a csv file to test my SP to output the user name/password. but I am getting errors below.
    any help would be great! the below code is at the end of my SP.
    Thanks.

    DECLARE @user_name varchar(50)
    DECLARE @psswrd varchar(10)

    SELECT @user_name ,@psswrd
    FROM ngweb_bulk_enrollments
    EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C:\test.csv -Sserver1 -T -t, -r\r\n -c'

    This works but I don't get the headers in the file. How can I include the headers?
    Thanks.!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be surprised if this does what you want/expect. By using the @ signs you specify that you want to use variables, then repeat those uninitialized variables for every row in the ngweb_bulk_enrollments table. This ought to result in gibberish, repeated many times.

    Using xp_cmdshell is generally a bad idea. It was the only way to do many things until SQL 2000 was released, but SQL Server has come a long way since then! If at all possible, you should turn access to xp_cmdshell back off, it is almost always a serious security risk to your SQL Server.

    What I suspect that you want is to export the two columns from the table into a spreadsheet. This is easy to do using either SSIS or if necessary DTS.

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

  3. #3
    Join Date
    Jun 2014
    Posts
    4
    I got it didn't know that. when I ran the querry it ran but now I cannot execute the SP I get erros:
    Erro source .Netsqlclinet data provider . Error message time out period elaspsed prior to completion of the operation or server is not responding?

    error attached.
    How can I fix this?
    Thanks.

    Quote Originally Posted by Pat Phelan View Post
    I'd be surprised if this does what you want/expect. By using the @ signs you specify that you want to use variables, then repeat those uninitialized variables for every row in the ngweb_bulk_enrollments table. This ought to result in gibberish, repeated many times.

    Using xp_cmdshell is generally a bad idea. It was the only way to do many things until SQL 2000 was released, but SQL Server has come a long way since then! If at all possible, you should turn access to xp_cmdshell back off, it is almost always a serious security risk to your SQL Server.

    What I suspect that you want is to export the two columns from the table into a spreadsheet. This is easy to do using either SSIS or if necessary DTS.

    -PatP
    Attached Thumbnails Attached Thumbnails sql error.jpg  

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    the best way to fix that problem is to use SSIS or DTS to export the data. They are both less vulnerable to problems than xp_cmdshell is.

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

  5. #5
    Join Date
    Aug 2008
    Posts
    147
    Another method is using powershell - exporting the data from SQL Server to Excel .
    This method gives you lots of control of Excel - via the apis exposed.
    SQL Server Export to Excel with Powershell - SQL Server DBA
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    BCP doesn't do column headers.
    I have fudged it in the past by adding the columns as a row in the dataset, something like:

    Code:
    SELECT list
         , of
         , coumns
    FROM   (
            SELECT 'list'    As list
                 , 'of'      As of
                 , 'columns' As columns
                 , -937 As sortation
            UNION ALL
            SELECT list
                 , of
                 , columns
                 , 937 As sortation
            FROM   schema.object
           ) As x
    ORDER
        BY sortation ASC
    I don't use xp_cmdshell either. SQL Jobs have command line ... commands ... available as a type. Alternatively simple batch files and windows services or similar can work (although I like to have all my SQL jobs in the same place, up to you).
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2014
    Posts
    4
    Thanks George. Can you give me a sample on how to export this user/pass through an store proc? I am a rookie/new with SQL and trying to learn as much. I did try xp_cmdshell in my SP but it hosed my process so I had to kill it. A lesson learned. All I want to do is to get the username/pass and output it to an excel, note or word.


    Quote Originally Posted by gvee View Post
    BCP doesn't do column headers.
    I have fudged it in the past by adding the columns as a row in the dataset, something like:

    Code:
    SELECT list
         , of
         , coumns
    FROM   (
            SELECT 'list'    As list
                 , 'of'      As of
                 , 'columns' As columns
                 , -937 As sortation
            UNION ALL
            SELECT list
                 , of
                 , columns
                 , 937 As sortation
            FROM   schema.object
           ) As x
    ORDER
        BY sortation ASC
    I don't use xp_cmdshell either. SQL Jobs have command line ... commands ... available as a type. Alternatively simple batch files and windows services or similar can work (although I like to have all my SQL jobs in the same place, up to you).

  8. #8
    Join Date
    Jun 2014
    Posts
    4
    Thanks for the info, Do I have to incorporate all that code into my store proc? haven't done this before
    .

    Quote Originally Posted by JackVamvas View Post
    Another method is using powershell - exporting the data from SQL Server to Excel .
    This method gives you lots of control of Excel - via the apis exposed.
    SQL Server Export to Excel with Powershell - SQL Server DBA

Posting Permissions

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