Error on Preview Data on Stored Procedure: SqlParameter with ParameterName * is not in SqlParameterCollection


(Christian Gotzes) #1

Hello.

I’m new to Data Abstract and try to use a Stored Procedure with a Parameter on MS Sql Server to retrieve a Select Statement.


On the Sql Page for the Statement I get an SchemaParameterCollection could not be changed to IXMLSerializable.
On the Preview Page I got the Error:
Error while executing SQL: An exception occurred on the server: SqlParameter with ParameterName ‘@SEARCHTEXT’ is not included in SqlParameterCollection.

Perhaps someone has an “helping Hand” for me.
Thanks.


(antonk) #2

Hello

Please send to support@ or attach here the schema file on your screenshot and DDL for the stored procedure you used.

Thanks in advance


(Christian Gotzes) #3

Hello.

Here is the DDL for the stored procedure:
CREATE PROCEDURE [dbo].[GetShortItemInformation](@SEARCHTEXT NVARCHAR(100)) AS
BEGIN
SET NOCOUNT ON;
DECLARE @ITEMNO NVARCHAR(20);

SELECT @ITEMNO = ISNULL([No_], '') FROM [OSG$Item] WHERE [No_] = @SEARCHTEXT;
IF @ITEMNO = '' OR @ITEMNO IS NULL BEGIN SELECT @ITEMNO = ISNULL([No_], '') FROM [OSG$Item] WHERE [Vendor Item No_] = @SEARCHTEXT; END;
IF @ITEMNO = '' OR @ITEMNO IS NULL BEGIN SELECT @ITEMNO = ISNULL([No_], '') FROM [OSG$Item] WHERE [EAN] = @SEARCHTEXT; END;
IF @ITEMNO <> '' BEGIN
	SELECT Item.[No_] AS ItemNo, Item.[Description], Item.[Description 2] AS Description2, 
		Item.EAN, Item.[Vendor No_] AS VendorNo, Item.[Vendor Item No_] as VendorItemNo, 
	(SELECT Count(*) FROM [OSG$BOM Component] BOM WHERE BOM.[Parent Item No_] = Item.[No_]) AS BOMCount, 
	(SELECT Sum([Outstanding Qty_ (Base)]) FROM [OSG$Purchase Line] PurchLine WHERE PurchLine.[Type] = 2 AND PurchLine.[No_] = Item.[No_]) AS PurchaseQuantity,
	(SELECT ISNULL(SUM(SUM$Quantity), 0) FROM [OSG$Item Ledger Entry$VSIFT$3] WHERE ([Item No_] = Item.[No_])) AS Inventory
	FROM [OSG$Item] Item WHERE [No_] = @ITEMNO;
END;

END

Also attached the schema file as zip.

Thanks.MobileApp.zip (956 Bytes)


(antonk) #4

Unfortunately I was not able to reproduce the issue. Preview works as expected. Could you say which exactly Schema Modeler version do you use? You can see the version info in Help -> About

Btw you could define statements that use Stored Procedures in more efficient way. Change the statement type to Stored Procedure and select the procedure name in the dropdown list.

2018-02-06_12-27-07_chrome


(Christian Gotzes) #5

Hi.

Schema Modeler was: Version 9.3.105.1337 Build date 2017-10-13
Now I update to 9.3.105.1351. Same problem.
Also I have update the SQL Client to a current version. No change.
But it seems that this problem is only on this computer with this environment. On a second computer with 9.3.105.1345 there is no error message.
So I will use this the second Computer for this Project.

Thanks for your help.


(antonk) #6

Do both computers have the same Locale settings (like number format, language used etc?) I still would like to find out what is the issue here


(Christian Gotzes) #7

That will be great if we find the reason.

Both are Windows 10 Pro Version 1709. Both have the same Locale settings. Same Language. Sql Server is Version 11.0.6251.0.
On both Sql Client for 2008 R2 and Sql Client 2012 are installed. But on 2012 with different Releases.
Working PC: Sql Client 2008 is 10.51.2500.0, Sql Client 2012 is 11.3.6518.0.
PC with Error: Sql Client 2008 is 10.51.2500.0, Sql Client 2012 is 11.4.7001.0.
Main Difference is that the computer which raises the error is not Part of the Domain where the Sql Server is working in. And the Computer that has the error is an real Computer while working is an Virtual Machine.

In addition to that information above I copied the Database to the computer where the error raises and change the schema to that computer. Same Error.
If you have any other idea or if you need more information I will try to do my best to follow you.