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