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.


(Bernaert Dominique) #8

Hi,
I’m facing the same problem.
Was a solution found for this?

I create a simple statement:
Select * from files where fileid=:fileid
And I get an sqlparameter with parametername @fileid is not part of this SqlParameterCollection.

This is on a Windows 10 machine with SQL Server 2017

Thx.


(antonk) #9

What is the exact database driver and Data Abstract version you’re using?


(Bernaert Dominique) #10

I’m using Relativity server version 9.4.107.1363Relativity


(antonk) #11

Please describe step-by-step how to reproduce the issue.


(Bernaert Dominique) #12

I open relativity server admin and open the schema modeler.
Right click on table, select Add
Click on statements
Click add statement
Select target table, in this case ‘Files’
Select statement type: SQL
I get the message there sql string is empty, I click yes
I click validate, perfect
I click preview data, perfect
I modify the sql and add a parameter, here below is the statement:

SELECT
[FileId], [Sys_CompanyId], [FileType],
[FileDescription], [FileRelation], [FileApplicator],
[FileRemarks], [FileStatus], [FileUser],
[FileRemoved], [FileDateDemand], [FileDateTarification],
[FileDateDeed], [FileTotalAmount], [FileMortgageAmount],
[FileMortgageAmountMandate], [FileAmountCommission], [FilePortalVisible],
[FilePortalCanComment], [FilePortalCanAddDocuments], [FileDateApproval],
[FileDateSigning], [FilePeriod], [FileMonthlyCharge],
[FileStartDatePayments], [FileNumberOfTranches], [FileInterestRate]
FROM
[Files]
WHERE [FileId]=:FileId

I click validate, I get a message there is parameter, Modeler adds a parameter to the parameters section of the table.

I click validate, perfect
I click preview data, I get a screen where I can enter the value for the parameter.
I enter a valid value.
I click execute
I get the attached error.
Error


(antonk) #13

Hello

It seems this is something new and specific for Relativity Server data preview only (i.e. this doesn’t reproduce for local Schema). This is clearly a bug (not related to the original one described in this thread). We’ll try to get this one fixed for the next Beta build.

Sorry for the inconvenience


(RemObjects) #14

Thanks, logged as bugs://81018


(RemObjects) #15

bugs://81018 got closed with status fixed.


(antonk) #16

Hello

The fix will be available in the next Beta build


(Bernaert Dominique) #17

Thx,

do you have any idea when it will be released?

Thx,

Adm-Concept
Bernaert Dominique​
Technical Lead


(antonk) #18

It should be released at Friday. In the worst case I’ll just prepare a separate Relativity Server build for you.


(Bernaert Dominique) #19

Hi Antonk,

no it’s fine, if I have in the first half of next week there’s no issue.

Thx.

Adm-Concept
Bernaert Dominique​
Technical Lead