Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Unanswered: binary(8) columns skipped when querying SQL Server

    Hi Guys,

    I have a serious issue with transferring binary(8) data from SQL Server 2008 R2 to Excel 2010.
    A standard query is made in Excel (Data -> From Other Sources -> From SQL Server) and it works... almost perfect.
    Almost, because in the query result I have almost all columns in destination spreadsheet, except of those columns which are binary(8) format in my table in SQL Server.

    So, in SQL there are columns - let's say:
    Date
    Start time
    End time
    User ID (as binary(8) format)
    Memo (as string)
    ...

    After Excel's query is done, in Excel there are only columns:
    Date
    Start time
    End time
    Memo (as string)
    ...

    All columns with types = binary(8) are skipped.

    I tried:
    1. Export from SQL Server to CSV file and then from CSV file to Excel (all columns as string) and it works, but it has to be automated so CSV is not a good idea.
    2. Adding to ConnectionString additional parameters NO_BINARY_RESULT=1 - does not help.
    3. Adding to ConnectionString additional parameter IMEX=1 - does not help.

    Do you know how to deal with that?

    Best regards

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Have you tried to use SSIS and include a data conversion task?

  3. #3
    Join Date
    Sep 2013
    Posts
    6
    I'm not entitled to create/modify anything in database, I have read-only permission.

    If you describe me the steps how to use "SSIS" (whatever it is) and how to include data conversion task, I will try to kick out this columns using your way.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I don't use Excel for this type of reporting, so my help is limited to database initiated tasks. SSIS is a tool to create integrated services packages which is one of the perferred methods of data extraction. Might suggest you ask a database developer or DBA to help you with the ETL process.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can do a type conversion from binary to string in the SELECT script, so you don't change anything in the database:
    Code:
    SELECT CONVERT(char(8), 0x4E616d65, 0)
    In your case that would become:
    Code:
    SELECT Date_,
    	Start_time,
    	End_time,
    	CONVERT(char(8), User_ID, 0) as User_ID,
    	Memo
    FROM ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    You can do a type conversion from binary to string in the SELECT script, so you don't change anything in the database:
    This is correct, but it makes the entire result set effectively "read-only" and that could be a problem for some uses.

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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He's exporting to Excel, so I hope he's not using it as a data editor....
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Sep 2013
    Posts
    6
    Thank you all guys for replies.

    Quote Originally Posted by Wim View Post
    Code:
    	CONVERT(char(8), User_ID, 0) as User_ID,
    FROM ...
    It could be a nice workaround. But Excel query allows to connect to data source (table), it does not allow to write SQL statement

    It's temporary working with CSV (SQL Server -> CSV -> Excel "proxy" -> Excel destination (proxy spreadsheet refreshing CSV query and makes a basic aggregation).

    Do you know if I can write a scheduled CSV export that will deploy a fresh data every day at 4 AM ?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by derlee View Post
    It could be a nice workaround. But Excel query allows to connect to data source (table), it does not allow to write SQL statement
    No, you can edit the SQL Statement that Excel uses to retrieve data.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Sep 2013
    Posts
    6
    Quote Originally Posted by blindman View Post
    No, you can edit the SQL Statement that Excel uses to retrieve data.
    Can I? :O in standard query? How? This would solve another issue with querying necessary columns. I have never seen a possibility to edit the SQL Statement in Excel.

  11. #11
    Join Date
    Sep 2013
    Posts
    6
    Finally I made a view with CONVERT and it works fine.
    Close please.

Posting Permissions

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