Dynamics Community Forum Thread Details

Store procedure error getting while deploying the Retail Package

Hi Everyone,

Hope all are doing well....

I created a stored procedure where it accepts a table type as a parameter. For this i have also created the user defined table type in the development server. Same scripts also i added in the Database=>Custom folder to deploy in the UAT environment. At the time of deploying it is throwing error and package is not deployed. I found the below error in the Log file.

An error occurred when running the customization script G:\DeployablePackages\f6398d7d-fcdd-4d08-9e7e-a76c1b22dee5\RetailServer\Data\Upgrade\Custom\EXT_SP_INSERTLTITEMSTOCK.sql against db_d365opsprod_20191119_01522168_9949. Exception calling "ExecuteSqlText" with "1" argument(s): "Column, parameter, or variable #1: Cannot find data type ext.LTITEMSTOCKTABLETYPE.
Must declare the table variable "@TVP_LTITEMSTOCKTABLETYPE".
Parameter or variable '@TVP_LTITEMSTOCKTABLETYPE' has an invalid data type. Near statement:
CREATE PROCEDURE [ext].[SP_INSERTLTITEMSTOCK]
@TVP_LTITEMSTOCKTABLETYPE [ext].[LTITEMSTOCKTABLETYPE] READONLY
AS
BEGIN
INSERT INTO EXT.LTITEMSTOCK
SELECT * FROM @TVP_LTITEMSTOCKTABLETYPE
END
... "
11/19/2019 3:01:07 AM: ############### Error occurred: ###############
11/19/2019 3:01:07 AM: db_d365opsprod_20191119_01522168_9949 - An error occurred when deploying customizations for database. Deployment will continue for other databases, but this deployment will be marked as failed.
11/19/2019 3:01:07 AM: ############### Error occurred: ###############
11/19/2019 3:01:07 AM:
PSMessageDetails :
Exception : System.Management.Automation.MethodInvocationException: Exception calling "ExecuteSqlText" with "1" argument(s): "Column, parameter, or variable #1: Cannot find data type ext.LTITEMSTOCKTABLETYPE.
Must declare the table variable "@TVP_LTITEMSTOCKTABLETYPE".
Parameter or variable '@TVP_LTITEMSTOCKTABLETYPE' has an invalid data type. Near statement:
CREATE PROCEDURE [ext].[SP_INSERTLTITEMSTOCK]
@TVP_LTITEMSTOCKTABLETYPE [ext].[LTITEMSTOCKTABLETYPE] READONLY
AS
BEGIN
INSERT INTO EXT.LTITEMSTOCK
SELECT * FROM @TVP_LTITEMSTOCKTABLETYPE
END... " ---> System.Exception: Column, parameter, or variable #1: Cannot find data type ext.LTITEMSTOCKTABLETYPE.
Must declare the table variable "@TVP_LTITEMSTOCKTABLETYPE".
Parameter or variable '@TVP_LTITEMSTOCKTABLETYPE' has an invalid data type. Near statement:
CREATE PROCEDURE [ext].[SP_INSERTLTITEMSTOCK]
@TVP_LTITEMSTOCKTABLETYPE [ext].[LTITEMSTOCKTABLETYPE] READONLY
AS
BEGIN
INSERT INTO EXT.LTITEMSTOCK
SELECT * FROM @TVP_LTITEMSTOCKTABLETYPE
END... ---> System.Data.SqlClient.SqlException: Column, parameter, or variable #1: Cannot find data type ext.LTITEMSTOCKTABLETYPE.
Must declare the table variable "@TVP_LTITEMSTOCKTABLETYPE".
Parameter or variable '@TVP_LTITEMSTOCKTABLETYPE' has an invalid data type.