exec statement in SP issue

[bshen]

Hi guys,

I am using Crystal report to export the report to an PDF file, but it's fail to get the right result. The issue I am wondering is I am using the Exec statement in my SP, my SP is like below:

DECLARE @XMLDataFormat as nvarchar(4000)

SELECT @XMLDataFormat = XMLDataFormat FROM tbDocs where DocID = @DocID

DECLARE @s1 as varchar(8000)

SET @s1 = '
DECLARE @intDoc INT
EXEC sp_xml_preparedocument @intDoc OUTPUT, '''+ REPLACE(@vstrXML, '''','''''') +'''
SELECT ''' + @Title + ''' as Title, * FROM OPENXML(@intDoc , ''/data/form'') WITH ('+lower(@XMLDataFormat)+')
EXEC sp_xml_removedocument @intDoc'

--print @s1
EXEC (@s1)

When I using SQL Profiler trace the ReportDocuemnt.Export function, it called 6 SPs:

exec [LandCanada_brokers]..sp_tables_rowset N'sprpt_GetXMLForm_RS;1', N'dbo', NULL
exec [LandCanada_brokers]..sp_procedures_rowset N'sprpt_GetXMLForm_RS', 1, N'dbo'
exec [LandCanada_brokers]..sp_procedure_params_rowset N'sprpt_GetXMLForm_RS', 1, N'dbo', NULL
exec "LandCanada_brokers"."dbo"."sprpt_GetXMLForm_RS";1 0, 0
exec [LandCanada_brokers]..sp_procedure_params_rowset N'sprpt_GetXMLForm_RS', 1, N'dbo', NULL
exec [LandCanada_brokers]..sp_indexes_rowset N'sprpt_GetXMLForm_RS;1', NULL, N'dbo'

Which I believe at the end, there is a call like:
exec "LandCanada_brokers"."dbo"."sprpt_GetXMLForm_RS";1 1, 124
is not fire, which means the SP can't take any param value in this case, so the result you can image.

Any idea? Please give a hand.

Thanks