Table-Valued Parameter Recieving Invalid Data Type Error

Question

I have an array of data from a console app I've developed that I need to transfer into a SQL database. As SQL injections are volatile, I decided to try passing it through a stored procedure via table-valued parameter. I created the table type with no issues and it is listed under 'User-Defined Table Types' folder. The issue I am having is when I try to create my stored procedure, I receive this error:

Msg 2715, Level 16, State 3, Procedure sp_IsertBackupData, Line 2 [Batch Start Line 0] Column, parameter, or variable #1: Cannot find data type VeeamTableType. Parameter or variable '@VeeamTableType' has an invalid data type.

Does anyone have any insight on what could be the issue here?

I have already tried refreshing the local cache.

CREATE PROCEDURE sp_IsertBackupData

@VeeamTableType VeeamTableType READONLY 

AS 

BEGIN

    DECLARE @q varchar(1000)
    SET @q= 'SELECT * FROM' + @VeeamTableType


    INSERT INTO ASManagement.dbo.VeeamBackupResults 
    (
    Id, 
    Server,
    BackupLogFileName,
    BackupLogFileSize,
    CreatedOn 
    )

    EXEC (@q)

END


SELECT * FROM ASManagement.VeeamBackupResults

CREATE TYPE VeeamBackupResults.VeeamTableType as TABLE

(

    Id int primary key,

    Server varchar(500) null,

    BackupLogFileName varchar (500) null, 

    BackupLogFileSize float null, 

    CreatedOn datetime null

)

score 1 · Accepted Answer · answered Aug 16 '19 at 16:51

The root cause of appearing the error is the stored Procedure cannot find VeeamTableType because you create it under VeeamBackupResults schema and you are trying to create the Procedure under default schema (dbo at most of you dose not change the default to be another one )

So for fixing the error, you should follow one of next solutions:-

1) Create procedure under the VeeamBackupResults schema

Try CREATE PROCEDURE VeeamBackupResults.sp_IsertBackupData

instead of CREATE PROCEDURE sp_IsertBackupData

Or 2) pass VeeamTableType with its correct schema

Try: @VeeamTableType VeeamBackupResults.VeeamTableType READONLY

instead of: @VeeamTableType VeeamTableType READONLY

Or 3) Create the user definde table variable into default schema

Try: CREATE TYPE VeeamTableType as TABLE

instead of: CREATE TYPE VeeamBackupResults.VeeamTableType as TABLE

Plz flag this answer as accepted one. https://stackoverflow.com/help/accepted-answer — ahmed abdelqader, Aug 16 '19 at 19:36

Serg · Answer 2 · 2019-08-16T16:52:11.163

0

You need no dynamic sql

CREATE PROCEDURE sp_IsertBackupData   
      @VeeamTableType VeeamTableType READONLY 
AS    
BEGIN
    INSERT INTO ASManagement.dbo.VeeamBackupResults 
    (
      Id, 
      Server,
      BackupLogFileName,
      BackupLogFileSize,
      CreatedOn 
    )
    SELECT * 
    FROM @VeeamTableType;
END

Fiddle to create proc

edited Aug 16 '19 at 16:52

answered Aug 16 '19 at 16:48

Serg

22,285
5
21
48

That doesn't really help the issue. – Dan Aug 16 '19 at 16:51
See fiddle. Compiles OK. – Serg Aug 16 '19 at 16:56

Table-Valued Parameter Recieving Invalid Data Type Error

2 Answers2