[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL error

Posted on 2007-10-04
5
Medium Priority
?
520 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:Isaac
5 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 400 total points
ID: 20015156
VARCHAR, NVARCHAR, NCHAR, CHAR
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 20015180
You are not allowed to declare local variables for these datatypes, text / ntext
Is that the exact procedure, I think , there should be some select statements ?
0
 
LVL 5

Author Comment

by:Isaac
ID: 20015500
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.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 20016486
...
AS
      ...

      DECLARE @ticket_request VARCHAR(1)
      ...
BEGIN
      ...
      INSERT INTO tblTicket(personnel_id,ticket_request,ticket_dte,subject,tick_status) VALUES(@rec_id, CAST(@ticket_request AS TEXT),@ticket_dte,@subject,@tick_status)
      ...
0
 
LVL 5

Author Comment

by:Isaac
ID: 20017625
Thanks you guys
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question