Hi I have a report which is used to give the information of he DTS being used (eg:What are the stored procedures being used, and the tables and views present in it) .The report is getting generated but the problem is if there are three to four tables used in a procedure while fetching the details of it only the first table name alone gets fetched; it doesnt display all the table names in the report.The query which i used for the report is as follows:
SELECT d.id, p.dtsid, d.dtsname, d.Complexity, d.Description, d.InUse, d.Server, d.Module, p.Procedure, t.Table, p.ProcedureId, t.tableid, dt.dtsTable, dt.dtsid
FROM (((Select d.id, d.DTSName, IIf(d.Complexity Is Null, '', (Select c.Complexity from complexity c where c.[id]=d.complexity)) AS Complexity, d.Description, d.InUse , IIf(d.Server Is Null,'',(Select ServerName from Server c where c.[id]=d.Server)) AS Server, IIf(d.SubModule Is Null,'',(Select SubModuleName from SubModule c where c.[id]=d.SubModule)) AS [Module] From Dts d) AS d LEFT JOIN (SELECT dtsid, u.
as [dtsTableId], dt.[TableName] AS [dtsTable] FROM DTSUses u, Tables dt Where u.
=dt.[id]) AS dt ON d.id = dt.dtsID) LEFT JOIN (SELECT dtsid, u.[Procedure] as ProcedureId, p.[ProcedureName] AS [Procedure] FROM Dts d, DTSUses u, ProceduresFunctions p Where d.[id] = u.dtsid and u.[procedure]=p.[id]) AS p ON d.id = p.dtsid) LEFT JOIN (SELECT dtsid, u.[Procedure] as ProcedureId, pu.
as [Tableid], t.[TableName] AS
FROM Dts d, DTSUses u, ProceduresFunctions p, ProcedureUses pu, Tables t Where d.[id] = u.dtsid and u.[procedure]=p.[id] and p.[id]=pu.[ProcedureId] and pu.
=t.[id]) AS t ON (p.ProcedureId = t.ProcedureId) AND (p.dtsid = t.dtsid);
Can anyone help me sorting it out wuld be of great help
