XSD from SP problem

[Harry Simpson]

Traditionally I've always just dragged and dropped my stored proc onto the
designer surface of the dataset XSD object and viola! the output fields
schema is created...but the stored proc i wrote below will not drop to
create the XSD.....what are the limitations of the dropping as it relates to
the SP complexity....

Here's the SP

CREATE PROCEDURE p_tmGetMultipleFacAllDeptReport

@FirstString varchar(6000),
@SecondString varchar(6000),
@StartDate varchar(50),
@EndDate varchar(50)

AS

declare @selectString varchar(6000),
@selectString2 varchar(6000),
@selectString3 varchar(6000),
@whereString varchar(6000),
@whereString2 varchar(6000),
@cmdString varchar(6000),
@inString varchar(6000)

Select @selectString = ' SELECT pd.facility, f.name as FName, f.budget as
FacBudget, f.Sales_tax, d.name as DeptName, pd.Qty, pd.Recd,

pd.Invoiced, pd.Tams_Date, pd.PO, pd.Dept, pd.Proj, pd.Tams_Desc,
pd.Taxable, pd.Price, '
Select @selectString2 = @selectString + @StartDate + ' as StartDate, ' +
@EndDate + ' as EndDate '
Select @selectString3 = 'From t_tmPurchaseDetail pd JOIN t_tmFacility f ON
pd.Facility = f.number JOIN t_tmDepartment d ON pd.dept =

d.number '
Select @whereString = ' WHERE pd.Facility in '
Select @inString=@FirstString
Select @whereString2 = ' and pd.TAMS_DATE BETWEEN ''' + @StartDate +
'''AND ''' + @EndDate + ''''

Select @cmdString= @selectString2 + @selectString3 + @whereString +
@inString + @whereString2 + 'Order by pd.facility'

Exec (@cmdString)
GO

It runs just fine and brings back data but the designer will not let it drop
to create the XSD.....

Any ideas or do i just need to create the report from a dataset instead??

Thanks
Harry

 

[Bruce Barker]

because you use dynamic sql, there is no meta data about the result sets for
VS to use. just hand create the xsd.

-- bruce (sqlwork.com)