Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Commerce forum / Store procedure error ...
Commerce forum
Answered

Store procedure error getting while deploying the Retail Package

Posted on by 392

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.

I have created a script also for "@TVP_LTITEMSTOCKTABLETYPE". Still it is throwing the same error.

Please help me to rectify the issue.....

  • Pankaj_93 Profile Picture
    Pankaj_93 392 on at
    RE: Store procedure error getting while deploying the Retail Package

    Hi Oksana,

    Thank you very much for your response...

    It helps me alot...

  • Verified answer
    Oksana Kovaliova Profile Picture
    Oksana Kovaliova 3,586 on at
    RE: Store procedure error getting while deploying the Retail Package

    Hi, did you add correct permissions to your table type?

    Below is tested & working sample

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MYTABLETYPE')

    BEGIN

    CREATE TYPE [ext].[MYTABLETYPE] AS TABLE(

    [PARENTRECID] [bigint] NOT NULL,

    [PROPERTYNAME] [nvarchar](512) NOT NULL,

    [PROPERTYVALUE] [nvarchar](512) NULL

    )

    END

    GO

    GRANT EXECUTE ON TYPE::[ext].[MYTABLETYPE] TO [UsersRole];

    GO

    GRANT EXECUTE ON TYPE::[ext].[MYTABLETYPE] TO [PublishersRole];

    GO

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans