Post reply

Setting Connection Options via T-SQL

  • November 30, 2003 at 7:21 pm

    #106646

    Hi,

    I need to create a report that is tab delimited.

    I can achieve this by setting my connection options in Query Analyser.

    How could I do this as part of a stored Procedure. Such as 'set ........'

    Franknf


    Franknf

  • December 1, 2003 at 3:58 am

    #484091

    You cannot, that is an internal formating built by QA not by the data stream. Your client has to build it itself.

  • December 1, 2003 at 3:16 pm

    #484174

    Thanks Antares686,

    I'll just have to add another step at the client side then.

    Franknf


    Franknf

  • December 1, 2003 at 8:53 pm

    #484205

    You could try outputting your results as one long formatted column using the ASCII code for the tab character (I think it's 11).

    EG:

    

    SELECT Column1 + Char(11) + Column2 + etc...

    FROM Table

    You'll need to convert any numeric or datetime values to character formats.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 1, 2003 at 8:59 pm

    #484206

    Thanks phillcart,

    I didn't think of that.

    I think that's what I'll do.

    Franknf


    Franknf

  • December 1, 2003 at 9:57 pm

    #484209

    horizontal tab = CHAR(9)


    * Noel

  • December 1, 2003 at 9:59 pm

    #484210

    Thanks noeld, whats 11 ???

    Don't have my ASCII table handy.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 1, 2003 at 10:14 pm

    #484211

    VERTICAL tab = CHAR(11)


    * Noel

  • December 1, 2003 at 10:18 pm

    #484212

    Well I'm impressed wasn't to bad a guess, at least it was something to do with tabbing

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 1, 2003 at 10:24 pm

    #484213

    I have to admit that i like the tabbing idea but is going to be a little complicated is there are NULLs on the way and the number of columns is big.

    Any way I think is good to be creative!


    * Noel

  • December 2, 2003 at 4:12 pm

    #484385

    Dealing with the nulls is actually fairly simple. Try something like the following...

    SELECT ISNULL(field1 + char(9),'') + ISNULL(field2 + char(9),'') + ISNULL(Lastfield,'') as DelimittedString

    Since a null added to anything will end up null it will not be added to your string 🙂

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • December 2, 2003 at 4:42 pm

    #484386

    If someone wanted to get really creative they could put togther a dynamic SQL statement based on syscolumns ...

    Pass in a table name, retrieve the object id and cursor through the column list adding in CONVERTS as appropriate.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

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

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