Devart Forums

Thanks for the suggestion. It's definitely not a permissions issue. All the backend objects have the same owner and same rights. Front end permissions are handled outside of SQL security. For kicks I tried your code below and as I expected it works just fine. I'm convinced it's either something funky with TMSConnection not liking my connection string for getting at table types (while EVERYTHING else works fine) or something from SDAC isn't making it into my exe. Not sure how that could be though.

I've tried all sorts of things here to determine what's going on.

I added code to find the table names:
The above works fine on my development machine.

I'm wondering if something from SDAC isn't getting compiled properly into my application. That's the only thing that makes sense to me right now. The above code works flawlessly on my dev machine but it crashes on the 2nd line on the client's system.

This issue is acting up again. Up until today I was doing development work with this on my local development system. This morning I deployed the updated version to my client. When my application starts up, the TMSConnection connects just fine. In a form where I use it in the same TMSStoredProc not working again. I'm getting a "table does not exist" error here again (in the 3rd line).

tblSelRawMatls.Connection := dmKT.cnnKT_SDAC;
tblSelRawMatls.TableTypeName := 'dbo.TT_RawMatlIDs';
tblSelRawMatls.Open;

I'm using the same connection string but with a few differences of course because the sql db name on my client's system is slightly different and the sql password is different. I know the TMSConnection is good because I use a TMSStoredProc in another part of my application to update some other data and that behaves as expected. I don't use any table types with that one though so it's something with the table type again. Why is it telling me that my table type does not exist? I've checked backend permissions, schema, and other properties for the table type. When I log into the backend using the same credentials that the front end is using, I can see the table type. I can run the stored procedure its used in just fine from the backend in SSMS on my client's system. It's just that either TMSConnection or TMSStoredProc is having trouble finding it. Why might this be happening?

Thank you in advance!

I figured it out. The way you set up your connection object gave me an idea. I have a TMSConnection object (for new things) on my main data form along with my original TADOConnection object. I was using the same connection string for both of them. The TMSConnection object connected just fine and most things worked fine but something in my connection string made TMSConnection unhappy so I simplified it and it solved the entire problem.

I figured it out. The way you set up your connection object gave me an idea. I have a TMSConnection object (for new things) on my main data form along with my original TADOConnection object. I was using the same connection string for both of them. The TMSConnection object connected just fine and most things worked fine but something in my connection string made TMSConnection unhappy so I simplified it and it solved the entire problem.

Also, I guess you didn't read my OP clearly. I did post the exact message for you.

"Table-Valued Parameter type is not supported by SQL Server."

I still get that if I use a TMSTableData object on the form and try to set the table type name there. Both errors make no sense at all.

Just in case this helps you answer this (before I take the time to create an entire sample project), here's my front end code. When @iudErrors in the backend is NVARCHAR(MAX) (OUTPUT variable) I get what I explained in my OP. I tried "AsWideString" as well but same result.

sp.Execute;
iudErrors := sp.Params.ParamByName('@iudErrors').AsString;
if iudErrors <> '' then
begin
Windows.MessageBox(Self.Handle, PChar(iudErrors), PChar(sAppTitle), MB_ICONEXCLAMATION + MB_OK);
end;

Does this help you at all? This seems like it should be something simple. I'm using Delphi 10.2 and SDAC 8.0.5

I had an error in the code I posted in my original message. I'd swapped something just to try it out.

This is my current code. The first 2 lines run without errors. The 3rd line tells me that the table doesn't exist but it does. I use user defined table types all the time in my work. I'm sure it's something simple that I'm missing in SDAC.
It doesn't matter if I specify 'dbo.TT_RawMatlIDs' or 'TT_RawMatlIDs' above. I get the same error.

This is my table definition:

CREATE TYPE [dbo].[TT_RawMatlIDs] AS TABLE(
[RawMatlID] [int] NULL
)
GO

I have an MS SQL output variable that's NVARCHAR(MAX) and it works fine when I test it in a backend call and I know it would work find in any vb.net code I'd write because I've done this many times there but when I try to get its value using TMSStoredProc I get nothing. It comes back as a zero length string. If I change it to NVARCHAR(1000) on the backend I get a correct result but I don't want that. I want NVARCHAR(MAX). On the front end I do exactly as in the SDAC example code for getting an output variable from a stored procedure. What do I need to do to be able to handle "MAX" with TMSStoredProc?

Thanks,

Keith

I first created the application I'm making changes to some 15 years ago. I didn't know about SDAC at the time and used all native TADO components. And there was no option in "MS SQL 2000" to use table variables as stored procedure input parameters. In vb.net projects with MS SQL I've been using table parameters with stored procedures for many years now. I have a need to use that strategy with my old delphi project that I upgraded to tokyo 10.2 (and MS SQL 2017) a few years back. I also bought the SDAC components at that time but only now have a need for them.

I'm having trouble getting this working. I am trying to figure out how to get some data into a table in code so that I can use it here:

sp.Params.ParamByName('@RawMatlIDs').AsTable := <in memory table of some kind>

In the past I've used the DevExpress TDxMemData control for all sorts of in memory table needs. That won't work here.

I've tried using TMSTableData because I found an example of that in the SDAC documentation (https://docs.devart.com/sdac/table_valu ... meters.htm).

var
tblSelRawMatls: TMSTableData;

tblSelRawMatls.Connection := KTData.dmKT.cnnKT_SDAC; //<<<<<< ERROR HERE but no explanation for it in E.Message
tblSelRawMatls.Open;
tblSelRawMatls.TableTypeName := 'TT_RawMatlIDs';
tblSelRawMatls.Append;
tblSelRawMatls.Fields[0].AsString := '1';
tblSelRawMatls.Fields[1].AsString := '2';
tblSelRawMatls.Post;

cnnKT_SDAC is connected just fine but when I apply that connection to the TMSTableData it crashes.

I tried using an actual TMSTableData component on the form and I can connect to my database but when I try to set the table type I get an error: "table-valued parameter type is not supported by sql server"

That makes no sense to me. My entire reason for getting these components was so I could pass table params to a procedure (instead of having to parse long delimited strings) and in the example above in the SDAC documentation they clearly do it. Yes TT_RawMatlIDs exists in my database.

In vb.net it's a super simple matter to create an in-code table to pass to SQL but this seems way more complex in Delphi.

Any kind assistance would be gratefully appreciated!

Thanks,

Keith

Thank you!

In vb.net I'd pass a byte array to MS SQL using the SQL param type varbinary using the .net native sql server objects. How do I pass a TByeArray to SQL Server using TMSStoredProc? I've tried AsBlob, AsBytes, AsObject. Nothing else in the list seems logical to me.

Thanks,

Keith