December 16, 2017 at 12:09 am
Hi guys. I'm using existing stored procedures from DB to use in Table Adapters that outputs datasets. We use these datasets as the datasource in reports. It's a routine job as you know. But I have this problem in a special case of SP. The SP that used in this case generates output using sp_executesql SP running a query string stored in a variable, as long as I didn't find any other way except creating a dynamic sql query stored in a string variable. The problem here is that unlike other normal cases, although the output and schema are OK , meaning I successfully added its table adapter, but its Fill method, despite no error, can't successfully fill the corresponding strongly-typed datatable.
MORE DETAILS:
ASP.NET Webforms Project
Visual Studio 2012 Enterprise
SQL Server 2014.
I appreciate any help or hint if possible.
December 18, 2017 at 12:43 pm
I know what I'd try:
I'd create a temp table, then from within the dynamic sql I would run that same query EXCEPT include an insert from that query into the temp table, then once your dynamic sql exits, select from that temp table and see if your data adaptor can use that. There is an array of commands that get produced when you design a dataadapter and maybe some of those commands like a better defined target that could be fulfilled by the temp table definition in your stored procedure. I don't know if that's really the problem but since nobody's answering, I thought I'd throw a guess out there, heck half the fun of posting here is the hope that a smarter person will come along and show me where I goofed in my post !
like so:
DECLARE @SQL NVARCHAR(1000);
DECLARE @PARMDEF NVARCHAR(100);
DECLARE @ID INT;
SET @ID_IN = 25; -- HOPEFULLY IT EXISTS!
CREATE TABLE #TEST
( ID INT,
NAME1 VARCHAR(100)
);
SET @SQL = N'INSERT INTO #TEST SELECT ID, NAME1 FROM SOME_SOURCE_TABLE WHERE ID = @ID';
SET @PARMDEF = N'@ID INT';
EXECUTE sp_executesql @SQL, @PARMDEF, @ID = @ID_IN;
SELECT ID, NAME1 FROM #TEST
[/CODE]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply