Post reply

getting this simple query to excel... why so tough?

  • February 9, 2006 at 10:28 am

    #110972

    this is what i've got.

    select serverproperty ('servername') as 'Server'

    select serverproperty ('InstanceName') AS 'Instance Name' WHERE 'Instance Name' <> NULL

    select serverproperty ('edition') as 'Edition'

    select serverproperty ('productlevel') as 'Service Pack'

    select serverproperty ('productversion') as 'Build'

    select CASE serverproperty ('IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END AS 'Clustered'

    select name as [Databases] from sysdatabases where dbid >4

    select [name] as 'Logins' from syslogins WHERE [name] <> 'sa'

    all i'm trying to do is land it in an .xls, and have the columns lineup

    with the values. thats it. but when ever it's run from dts i get a

    single column with only the first value of servername. nothing else,

    and what a pain.

    thoughts?

    _________________________

  • February 9, 2006 at 11:53 am

    #619884

    I would try and use DTS but have three separate queries. The first would be

    select

    serverproperty ('servername') as 'Server' ,

    ISNULL(serverproperty ('InstanceName'),'') AS 'Instance Name' ,

    serverproperty ('edition') as 'Edition',

    serverproperty ('productlevel') as 'Service Pack',

    serverproperty ('productversion') as 'Build',

    CASE serverproperty ('IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END AS 'Clustered'

    Your DTS would need two database connections. One to your Excel file, the other to the MASTER database.

    You would need a task that does a CREATE TABLE for each record set against the Excel connection.

    On completion you would need a datapump task for each of the three queries with the destination being your three new "tables" which are really worksheets within your Excel workbook.

    LinkedIn Profile

  • February 9, 2006 at 11:55 am

    #619885

    Each different select statement is a different recordset.

    Try putting them all into 1

     

    select 1 as property,

           serverproperty ('servername') as 'Server',

           isnull(serverproperty ('InstanceName'),'') AS 'Instance Name',

           serverproperty ('edition') as 'Edition',

           serverproperty ('productlevel') as 'Service Pack',

           serverproperty ('productversion') as 'Build',

           CASE serverproperty ('IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END AS 'Clustered',

           '' As [Databases],

           '' As 'Logins'

    Union

    select 2, '', '', '', '', '', '',

           name, ''

    from master..sysdatabases where dbid >4

    Union

    select 3, '', '', '', '', '', '', '',

           [name]

    from master..syslogins WHERE [name] <> 'sa'

    order by property

     

  • February 9, 2006 at 12:05 pm

    #619891

    thanks for the feed back.

    ray,

    every time to try to put it into one statement i

    get errors cause fo the 'case', and also for the

    query against the master for logins etc.

    could you give me a quick example that may work?

    thanks in advance.

    _________________________

  • February 9, 2006 at 12:18 pm

    #619893

    Wrap your CASE statement in a CAST(CASE statement AS CHAR(3))

    I have had problems with BIT fields doing peculiar things

    LinkedIn Profile

  • February 9, 2006 at 12:26 pm

    #619895

    cool... thanks for the tip. any other words of wisdom that i should look out for with this??

    _________________________

Viewing 6 posts - 1 through 5 (of 5 total)

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