Share
Report
396Hi All,
Trust all are doing well...
I have created a custom with ext extension and also created a user defined table type. To insert values to the table, I have created a stored procedure which accepts the user defined table valued parameter. When I am executing the stored procedure getting the below error.
Error:
Msg 206, Level 16, State 2, Procedure ext.SP_INSERT_LTTHIRDPARTY, Line 0 [Batch Start Line 0]
Operand type clash: table is incompatible with LTTHIRDPARTYTRANSACTIONTABLETYPE
Error:
Msg 206, Level 16, State 2, Procedure ext.SP_INSERT_LTTHIRDPARTY, Line 0 [Batch Start Line 0]
Operand type clash: table is incompatible with LTTHIRDPARTYTRANSACTIONTABLETYPE
Kindly suggest …
Table Structure:
CREATE TABLE [ext].[LTTHIRDPARTYTRANSACTIONTABLE](
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[REPLICATIONCOUNTERFROMORIGIN] [int] IDENTITY(1,1) NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[CREATEDDATETIME] [datetime] NOT NULL,
[ROWVERSION] [timestamp] NOT NULL,
CONSTRAINT [I_LTTHIRDPARTYTRANSACTIONTABLE_REPLICATIONCOUNTERFROMORIGIN] UNIQUE CLUSTERED
(
[REPLICATIONCOUNTERFROMORIGIN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
User Defined Table Type Structure:
CREATE TYPE [ext].[LTTHIRDPARTYTRANSACTIONTABLETYPE] AS TABLE(
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL
)
Stored Procedure Structure:
CREATE PROCEDURE ext.SP_INSERT_LTTHIRDPARTY
(
@TVP_TABLE ext.LTTHIRDPARTYTRANSACTIONTABLETYPE READONLY
)
AS
BEGIN
INSERT INTO ext.LTTHIRDPARTYTRANSACTIONTABLE(CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID) select CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID from @TVP_TABLE
END
CREATE TABLE [ext].[LTTHIRDPARTYTRANSACTIONTABLE](
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[REPLICATIONCOUNTERFROMORIGIN] [int] IDENTITY(1,1) NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[CREATEDDATETIME] [datetime] NOT NULL,
[ROWVERSION] [timestamp] NOT NULL,
CONSTRAINT [I_LTTHIRDPARTYTRANSACTIONTABLE_REPLICATIONCOUNTERFROMORIGIN] UNIQUE CLUSTERED
(
[REPLICATIONCOUNTERFROMORIGIN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
User Defined Table Type Structure:
CREATE TYPE [ext].[LTTHIRDPARTYTRANSACTIONTABLETYPE] AS TABLE(
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL
)
Stored Procedure Structure:
CREATE PROCEDURE ext.SP_INSERT_LTTHIRDPARTY
(
@TVP_TABLE ext.LTTHIRDPARTYTRANSACTIONTABLETYPE READONLY
)
AS
BEGIN
INSERT INTO ext.LTTHIRDPARTYTRANSACTIONTABLE(CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID) select CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID from @TVP_TABLE
END
Executing Procedure Like Below:
DECLARE @TVP_TABLE AS TABLE
(
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL
)
INSERT INTO @TVP_TABLE (CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID) VALUES (6812453,'401','401001','401-401001-1','15243654',1,'xyz','xyz','xyz','xyz',
'USRT')
(
[CHANNEL] [bigint] NOT NULL,
[STORE] [nvarchar](10) NOT NULL,
[TERMINAL] [nvarchar](10) NOT NULL,
[TRANSACTIONID] [nvarchar](44) NOT NULL,
[BARCODE] [nvarchar](44) NOT NULL,
[LINENUM] [int] NOT NULL,
[EPAYTRANSACTIONID] [nvarchar](44) NOT NULL,
[EPAYRECEIPT] [nvarchar](max) NOT NULL,
[SONYPSNVOUCHERCODE] [nvarchar](44) NOT NULL,
[SONYRECEIPT] [nvarchar](max) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL
)
INSERT INTO @TVP_TABLE (CHANNEL,STORE,TERMINAL,TRANSACTIONID,BARCODE,LINENUM,EPAYTRANSACTIONID,EPAYRECEIPT,
SONYPSNVOUCHERCODE,SONYRECEIPT,DATAAREAID) VALUES (6812453,'401','401001','401-401001-1','15243654',1,'xyz','xyz','xyz','xyz',
'USRT')
exec ext.SP_INSERT_LTTHIRDPARTY @TVP_TABLE
All responses (
Answers (
