Query op changes!!!!!

  • Hi Friends,

    When i execute following statement through query analyser it works fine.

    its gives out put for all databases. but when i run this through Excel sheet

    it runs only in master and gives out put only for this.

    how can i import data from all databases in excel using this query.

    Query is =

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

    set nocount on

    set quoted_identifier off

    use master

    declare @syntax varchar(400)

    declare @dbname varchar(30)

    declare @dbname1 varchar(30)

    declare @dbname_header varchar(75)

    declare dbnames_cursor cursor for select name from master..sysdatabases

    open dbnames_cursor

    fetch next from dbnames_cursor into @dbname

    while (@@fetch_status <> -1)

    begin

    select @dbname_header = "Database " + rtrim(upper(@dbname))

    print " "

    print @dbname_header

    set @dbname1 = @dbname+'..'+'sysfiles'

    select @syntax = 'select convert(varchar(25),name) "Name",

    convert(varchar(10),size/128) "Size",

    convert(varchar(70),filename) "Path",

    convert(varchar(15),filegroup_name(groupid)) "Filegroup",

    convert(varchar(15),maxsize) "Max Size - MB",

    convert(varchar(15),growth/128) "Growth" from ' + @dbname1

    exec (@syntax)

    fetch next from dbnames_cursor into @dbname

    end

    deallocate dbnames_cursor

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

    i m doing things as follows...

    1 open MS excel sheet

    2 Go to Data > new database query > selecting edit query > adding my query over there

    3 Save sheet.

    By doing this next time when i open my sheet it asks me for server name and sa password

    when i fill it

    it gives me output.

    this is the way how it works.

    but i m getting output diff from wht query gives i query analyser

    Please find attachments for snapshot

  • I don't know why it's not working in EXCEL, but I know this task can be done without cursors. Well, in fact, it is just a simple SELECT...

    SELECT * FROM master.sys.database_files

    --Ramesh


  • hey the query you are using is wrong it is running only on master database

    I just copied and ran in query analyzer I am getting the errors and I just printed the @syntax

    PRINT 'TEST'+ @syntax

    exec (@syntax)

    it gives as below

    select convert(varchar(25),name) "Name",

    convert(varchar(10),size/128) "Size",

    convert(varchar(70),filename) "Path",

    convert(varchar(15),filegroup_name(groupid)) "Filegroup",

    convert(varchar(15),maxsize) "Max Size - MB",

    convert(varchar(15),growth/128) "Growth" from master

    Please modify the query

    Rajesh Kasturi

  • Thak you all !!!!!!!!!

    sorry rajesh but query is working fine in all databases.

    dont know wht happened with you..

    Also guys i got the solution..

    it is resolved now.

    All i needed to do is to insert all output into temp table.

    Excel gave out put as desired as soon as i put it in temp table and take output from temp table.

  • Ramesh (2/14/2009)


    I don't know why it's not working in EXCEL, but I know this task can be done without cursors. Well, in fact, it is just a simple SELECT...

    SELECT * FROM master.sys.database_files

    hi Ramesh,

    sys.database_files give out put only for that perticular database.not for all databases.

  • Nikhil (2/15/2009)


    Ramesh (2/14/2009)


    I don't know why it's not working in EXCEL, but I know this task can be done without cursors. Well, in fact, it is just a simple SELECT...

    SELECT * FROM master.sys.database_files

    hi Ramesh,

    sys.database_files give out put only for that perticular database.not for all databases.

    ...it was my bad, actually it was sys.master_files

    --Ramesh


  • Change the query by removing cursor,on every loop it is breaking and give the output in different result pane..So the first result set is coming out of database.

    Better take a temp table insert the result set of all the loops and the fetch the result in one result pane.

    This will work.For me its working.

    Debojyoti

    Tech. Analyst

  • ya thanks!!!!!!!

    it is working fine now with temp table

  • Nikhil (2/16/2009)


    ya thanks!!!!!!!

    it is working fine now with temp table

    Why to loop around, when you really don't need to?

    --Ramesh


Viewing 9 posts - 1 through 8 (of 8 total)

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