Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
QuietRiot
Yak Posting Veteran
67 Posts |
 Posted - 2008-12-11 : 19:47:05
|
Unfortunately I am at home and unable to test this at the moment. Right now I run this in Master and it comes up with like 50 different results with headers in grid or on file. I would like it as one table.original:Exec sp_MSforeachdb @command1='if ''?'' like ''XMP%'' Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValueFrom ?..FundGroupInfo a, ?..FundCustomFieldDefinitions d join ?..FundCustomFieldValues b on d.fieldId = b.FieldId join ?..FundInfo c on b.FundId = c.FundIdWhere d.FieldName = ''Fund Detail''Order by b.FundId ASC' how can I get that as 1 table? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-11 : 20:21:28
|
| Use Dynamic SQL . |
 |
|
|
QuietRiot
Yak Posting Veteran
67 Posts |
Posted - 2008-12-11 : 20:39:34
|
quote:
Originally posted by sodeep
Use Dynamic SQL .
I'm afraid I wouldn't know where to begin. I was hopeing it would be an easy fix like sticking union all at the endExec sp_MSforeachdb @command1='if ''?'' like ''XMP%'' Select a.FundGroupNumber,b.FundId, C.FundName, b.FieldValueFrom ?..FundGroupInfo a, ?..FundCustomFieldDefinitions d join ?..FundCustomFieldValues b on d.fieldId = b.FieldId join ?..FundInfo c on b.FundId = c.FundIdWhere d.FieldName = ''Fund Detail''Order by b.FundId ASCUNION ALL' but I guess that wont work? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-11 : 21:54:07
|
| See this:http://www.sqlservercentral.com/Forums/Topic546370-149-1.aspx |
|
|
|
QuietRiot
Yak Posting Veteran
67 Posts |
Posted - 2008-12-12 : 09:40:53
|
quote:
Originally posted by sodeep
See this:http://www.sqlservercentral.com/Forums/Topic546370-149-1.aspx
Thanks.. I'm using sqlserver 2000 and not sure if this would work for me? I attempted it but, I think its an issue with VARCHAR? There is no MAX. Also we have atleast 60 databases. Anyone have any ideas? right now i just get the SQL string in the messages tab and it cuts off at like the 9th databaseDECLARE @SQL VARCHAR(8000) SELECT @SQL = COALESCE(@SQL+' UNION ALL'+CHAR(10),'') + REPLACE(' SELECT ''**DBName**'' AS DB_Name, fundtransactionsource, createdbyuserid, fundtransactiontype,verifieddatetime,transactionid, fundtransactionlineno, postingdate, tradedate, creationdatetime, fundid, accountnumber, fundtransactionstatus, grossamount, netamount, shareamount FROM **DBName**..Fundtransactionlines WHERE postingdate = ''12/08/08'' AND createdbyuserid NOT in (2,37,38,335) AND verifieddatetime is null'+CHAR(10), '**DBName**',Name) FROM master..Sysdatabases WHERE name like 'AMD%' PRINT (@SQL) EXEC (@SQL) |
|
|
|
|
|
|
|
|
Can I add a UNION ALL to this?