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.
Perhaps someone has an “helping Hand” for me.
Thanks.
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
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)
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.
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.
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
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.
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.
What is the exact database driver and Data Abstract version you’re using?
I’m using Relativity server version 9.4.107.1363
Please describe step-by-step how to reproduce the issue.
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.
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
Thanks, logged as bugs://81018
bugs://81018 got closed with status fixed.
Hello
The fix will be available in the next Beta build
Thx,
do you have any idea when it will be released?
Thx,
Bernaert Dominique
Technical Lead
It should be released at Friday. In the worst case I’ll just prepare a separate Relativity Server build for you.
Hi Antonk,
no it’s fine, if I have in the first half of next week there’s no issue.
Thx.
Bernaert Dominique
Technical Lead