February 13, 2009 at 10:04 pm
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
February 14, 2009 at 3:43 am
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
February 14, 2009 at 10:41 am
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
February 15, 2009 at 4:49 am
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.
February 15, 2009 at 11:08 pm
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.
February 16, 2009 at 7:15 am
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
February 16, 2009 at 7:44 am
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
February 16, 2009 at 11:26 pm
ya thanks!!!!!!!
it is working fine now with temp table
February 16, 2009 at 11:47 pm
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