Link to home
Start Free TrialLog in
Avatar of Isaac
IsaacFlag for United States of America

asked on

T-SQL error

I get the following error when i execute my stored procedure:
-----------------------------------------------------------------------------------------
Msg 2739, Level 16, State 1, Procedure sp_insUserInfo, Line 0
The text, ntext, and image data types are invalid for local variables.
-----------------------------------------------------------------------------------------

Stored Procedure:

USE [helpDesk]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_insUserInfo]
      -- Parameters for 'tbl_user'
      @personnel_id int,
      @fname varchar(50),
      @lname varchar(50),
      @username varchar(50),
      @pwd varchar(50),
      @role_ID int,
      @email_addr varchar(50)


      
AS
      DECLARE @dte_added datetime
      DECLARE @rec_id int

      DECLARE @ticket_request text    <-- What can I use in place of "text"
      DECLARE @ticket_dte datetime
      DECLARE @subject varchar(50)
      DECLARE @tick_status varchar(10)

      SET @ticket_request = NULL
      SET @ticket_dte = NULL
      SET @subject = NULL
      SET @tick_status = NULL

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      SET @dte_added = GETDATE()

      INSERT INTO tbl_user(personnel_id,fname,lname,username,pwd,role_id,email_addr,dte_added) VALUES(@personnel_id,@fname,@lname,@username,@pwd,@role_ID,@email_addr,@dte_added)
      SET @rec_id = SCOPE_IDENTITY()

      INSERT INTO tblTicket(personnel_id,ticket_request,ticket_dte,subject,tick_status) VALUES(@rec_id ,@ticket_request,@ticket_dte,@subject,@tick_status)
      
      return @rec_id
END
GO


Thanks.
ASKER CERTIFIED SOLUTION
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Isaac

ASKER

aneeshattingal,

That's the exact procedure.
It's just supposed to insert data into a table.
Sounds like something is wrong.  I like to read your insight.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Isaac

ASKER

Thanks you guys