Search found 12 matches
- Fri 10 Jun 2022 23:10
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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.
- Fri 10 Jun 2022 01:59
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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.
I added code to find the table names:
Code: Select all
x := TStringList.Create;
dmKT.cnnKT_SDAC.GetTableTypeNames(x); // CRASHES HERE
tableTypeNames := '';
for i := 0 to x.Count - 1 do
begin
tableTypeNames := tableTypeNames + '; ' + x[i];
end;
showmessage(tableTypeNames);
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.
- Tue 07 Jun 2022 15:08
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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!
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!
- Thu 14 Apr 2022 14:11
- Forum: SQL Server Data Access Components
- Topic: TMSStoredProc and NVARCHAR(MAX)
- Replies: 4
- Views: 9145
Re: TMSStoredProc and NVARCHAR(MAX)
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.
- Thu 14 Apr 2022 13:45
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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.
- Thu 14 Apr 2022 00:12
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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.
"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.
- Wed 13 Apr 2022 19:21
- Forum: SQL Server Data Access Components
- Topic: TMSStoredProc and NVARCHAR(MAX)
- Replies: 4
- Views: 9145
Re: TMSStoredProc and NVARCHAR(MAX)
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
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
- Wed 13 Apr 2022 19:13
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Re: Table value parameter for TMSStoredProc
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
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.
Code: Select all
tblSelRawMatls.Connection := dmKT.cnnKT_SDAC;
tblSelRawMatls.TableTypeName := 'dbo.TT_RawMatlIDs';
tblSelRawMatls.Open; // ERROR HERE: Table does not exist
tblSelRawMatls.Append;
tblSelRawMatls.Fields[0].AsInteger := 1;
tblSelRawMatls.Post;
This is my table definition:
CREATE TYPE [dbo].[TT_RawMatlIDs] AS TABLE(
[RawMatlID] [int] NULL
)
GO
- Tue 12 Apr 2022 19:38
- Forum: SQL Server Data Access Components
- Topic: TMSStoredProc and NVARCHAR(MAX)
- Replies: 4
- Views: 9145
TMSStoredProc and NVARCHAR(MAX)
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
Thanks,
Keith
- Sat 09 Apr 2022 16:31
- Forum: SQL Server Data Access Components
- Topic: Table value parameter for TMSStoredProc
- Replies: 9
- Views: 12279
Table value parameter for TMSStoredProc
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
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
- Sat 09 Apr 2022 16:08
- Forum: SQL Server Data Access Components
- Topic: passing TByteArray via TMSStoredProc
- Replies: 2
- Views: 8329
Re: passing TByteArray via TMSStoredProc
Thank you!
- Sat 09 Apr 2022 02:44
- Forum: SQL Server Data Access Components
- Topic: passing TByteArray via TMSStoredProc
- Replies: 2
- Views: 8329
passing TByteArray via TMSStoredProc
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
Thanks,
Keith