I have a procedure that returns records based on dynamic SQL, I then use the following SQL to return the data into the dynamic view module, but no data is displayed. I enter the same query into DNN/Host/SQL and the data is returned correctly, please help;

SQL: (It also works using a combo in dynamic forms) 

DECLARE @RetTable TABLE (
    Username nvarchar(255),
    AccountID int,
    Pass nvarchar(255),
    Title nvarchar(255),
    FullName nvarchar(255),
    Email nvarchar(255),
    TelNo nvarchar(50),
    JobTitle nvarchar(255),
    Signatory int,
    UserGroup int,
    Inactive int,
    UserFilter int,
    HierarchID int,
    LanguageID int)
 
  DECLARE @SQL nvarchar(255)
 
  SET @SQL = 'SELECT Username,AccountID,UserPassword,Title,FullName,EmailFromAddress'
  SET @SQL = @SQL + ',UserTelephoneNo,JobTitle,SignatoryID,UserGroupID,Inactive'
  SET @SQL = @SQL + ',UserFilterID,HierarchyID,LanguageID'
  SET @SQL = @SQL + ' FROM [server1].[database2].[dbo].[Users]'
 
  BEGIN
    -- Execute the dynamic SQL
    INSERT INTO @RetTable(
    Username,
    AccountID,
    Pass,
    Title,
    FullName,
    Email,
    TelNo,
    JobTitle,
    Signatory,
    UserGroup,
    Inactive,
    UserFilter,
    HierarchID,
    LanguageID)
    EXEC sp_executesql @SQL
    --PRINT @RetTable
  END

SELECT Username AS QuestionOption, Username AS QuestionOptionValue FROM @RetTable

 

Sorry, version info = 1.0.5.25948 / 5.4.2

Error trace:

Loading Data With Errors:Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near the keyword 'FROM'. at DataSprings.DNN.Modules.DynamicViews.DynamicViews.GenerateReport(String Status, String strNewRecordsPerPage, String strDetailView) at DataSprings.DNN.Modules.DynamicViews.DynamicViews.GetData(String Status, String strNewPageSize) at DataSprings.DNN.Modules.DynamicViews.DynamicViews.Page_Load(Object sender, EventArgs e)
Inside Get Data:Load
New Page Size:

Search Error:
Search Error Message:Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near the keyword 'FROM'.
Search Error Stack Trace: at DataSprings.DNN.Modules.DynamicViews.DynamicViews.GenerateReport(String Status, String strNewRecordsPerPage, String strDetailView) at DataSprings.DNN.Modules.DynamicViews.DynamicViews.GetData(String Status, String strNewPageSize) at DataSprings.DNN.Modules.DynamicViews.DynamicViews.Page_Load(Object sender, EventArgs e)

HI, I believe dynamic views can only use a basic "Select" sql statement to return values as opposed to using the "declare" and "exec sp_executesql @SQL" keywords. Perhaps you can modify this to a "select" statement instead.

What is the content of your "sp_executesql" procedure? -- David

Hi David,
The SQL is dynamic thus the database name is passed using a session variable to achive the correct results. My method works in dynamic forms without any problem.

sp_executesql - This is a standard (built-in) MS SQL Server procedure.

Thanks

My work around is easy as Dynamic Views affectively supports dynamic SQL so I can replace the database name in the SQL by using a session variable..

SELECT Username,AccountID,UserPassword,Title,FullName,EmailFromAddress
UserTelephoneNo,JobTitle,SignatoryID,UserGroupID,Inactive
,UserFilterID,HierarchyID,LanguageID
FROM [server1].[$(DSSession1)].[dbo].[Users]