Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INSERT fails in stored proc, works in Query Analyzer 2

Status
Not open for further replies.
cwadams1955

cwadams1955

Programmer
Jan 21, 2008
52
CA
Got something weird here. I have an INSERT query inside a stored proc, used to copy records to a new record. It's been working previously; yesterday the table layouts were changed to add a single identity column as the primary key to the table, now the INSERT fails. It doesn't give an error (it hits the IF @@ERROR statement, then continues on,) and if I set a breakpoint, RETURN the SQL statement as created, and copy it into Query Analyzer, it runs perfectly - the new record is inserted exactly as it should be. Here's the relevant code from the stored procedure:

Code: 
CREATE PROCEDURE dbo.sp_CopyBillRecords

@WEDateFrom             datetime,
@WEDateTo               datetime,
@ProjectFrom            varchar(10),
@ProjectTo              varchar(10),
@TaskFrom               varchar(50), 
@TaskTo		        varchar(50), 
@ServiceAreaIn          varchar(4) , 
@ErrCode                integer = 0 OUTPUT,
@BillRecordNo           bigint OUTPUT

AS

DECLARE	
/* -------------------------------------------
Temp variables for header record
---------------------------------------------- */
@BillRecNo              bigint,
@ProjectNo              varchar (10), 
@TaskNo		        varchar (50), 
@WEDate		        datetime, 
@ServiceArea            varchar (4), 
@Client	                varchar (255), 
@ReportDate             datetime, 
@Status		        smallint, 
@ApprovedBy             varchar (20), 
@ApprovalDate           datetime, 
@Complete               bit, 
@Comment                varchar (50), 
/* -------------------------------------------
Temp variables for Working fields
---------------------------------------------- */
@SqlState               nvarchar (4000),
@NewSequenceNo          bigint,
@NewBillRecordNo        bigint

SET NOCOUNT ON

/* -------------------------------------------
Begin SQL transaction
---------------------------------------------- */
BEGIN TRANSACTION

	/* -------------------------------------------
	Copy Header record first
	---------------------------------------------- */
	SELECT @Client = Client 
	FROM	[dbo].[tblBillRecordHeader] 
	WHERE ([ProjectNo] = @ProjectFrom 
	 AND	[TaskNo] = @TaskFrom 
	 AND	[WEDate] = @WEDateFrom 
	 AND	[ServiceArea] = @ServiceAreaIn
	AND	[BillRecordNo]=@BillRecordNo)

	--exec sp_executesql @SqlState, N'@Client varchar (255) OUTPUT', @Client = @Client OUTPUT

	/* -------------------------------------------
	Set up for new Header record.
	Not all fields will be written out,
	several should use the default values only.
	---------------------------------------------- */
	SET 	@ProjectNo = @ProjectTo
	SET	@TaskNo = @TaskTo
	SET	@WEDate = @WEDateTo
	SET 	@ServiceArea = @ServiceAreaIn

	SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	(' + dbo.quotestring(@ProjectNo) + ', '
					+ dbo.quotestring(@TaskNo) + ', '
					+ dbo.quotestring(@WEDate) + ', '
					+ dbo.quotestring(@ServiceArea) + ', '
					+ dbo.quotestring(@Client) + ')'

--	return @SqlState

	exec (@SqlState)
	IF @@ERROR<>0
		Begin
			-- Write to header failed
			SET @ErrCode=91
			GOTO TRAN_FAILED
		End

... and here's the copied SQL statement as it's created with the test data:

Code: 
INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	('175508001', '500.100', 'Aug  8 2008 12:00AM', 'COE', '63430')

When I run the stored proc the error number doesn't change from 0, but when I set a breakpoint and RETURN the SQL statement, this is what I get:
Code: 
Server: Msg 245, Level 16, State 1, Procedure sp_CopyBillRecords, Line 126
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	('175508001', '500.100', 'Aug  8 2008 12:00AM', 'COE', '63430')' to a column of data type int.

Any ideas as to what gives? Thanks.
 
Sort by date Sort by votes
I can't see anything apparent which would cause this issue, but my question is -- why use dynamic sql in this case? You don't need it at all for that insert statement.
 
Upvote 0 Downvote
Really, it's just a hold-over from the first SQL project I ever worked on. There were sometimes a couple of dozen SQL statements in each proc that were built dynamically and then EXECuted. I just got in the habit of doing it this way.
 
Upvote 0 Downvote
It turns out that it only throws the syntax error when I do a RETURN on the SQL statement. If I do a SELECT, it simply displays the SQL statement without giving the syntax error. The INSERT still doesn't work, however.
 
Upvote 0 Downvote
First, get out of the habit of using dynamic sql; it is very poor programming practice which makes your application vunerable to sql injection attacks and has poorer performance than nondynamic sql. It should only be used in very limited cases where nothing else will work and then very carefully.

Of course it throws an error when you use return, return has an int datatype you can't put text into it. It is generally only used to indicate success or failure or return an error code.

Ok you say the table was altered yesterday to add an identity. Are you sure it is set up properly? The insert would not work it the table had the column added and made it not allow nulls but the identity property itself wasn't set.

Some reading you need to do on dynamic SQl



"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Well, I'm *reasonably* sure it's set up correctly, seeing as I can run the SQL statement manually (copy the dynamically created SQL statement and paste it into Query Analyzer) and it works.

The identity field definition is:
3 BillRecordNo bigint 8 0

Identity Yes
IdentitySeed 1
IdentityIncrement 1

It's the primary key for the table, and used to link to two separate detail tables.
 
Upvote 0 Downvote
Instead of using the @SqlState variable at all, do this instead:

Code: 
INSERT INTO [dbo].[tblBillRecordHeader]
                    (ProjectNo,
                    TaskNo,
                    WEDate,
                    ServiceArea,
                    Client)
            VALUES    (@ProjectNo, @TaskNo, @WEDate, @ServiceArea, @Client)

You don't have to worry about escaping the single quotes when the value is encapsulated in a variable.
 
Upvote 0 Downvote
Yeah, I just did that, got the same results - no error, but it didn't INSERT the record, either.
 
Upvote 0 Downvote
Triggers?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
do you have an existing record and unique indexes prevent it from inserting?

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
No, just the relationships between the header table and the two detail tables so I can use cascade deletes. The package is pretty basic, actually - originally, the tables just had a primary key built from four of the data fields, but somebody thought of another feature they needed, which is why they're being changed to use this identity field.
 
Upvote 0 Downvote
I don't see where you COMMIT that transaction.
You open it but never COMMIT it (or I'm missing something).

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
SQLSister - there are only two records in the table currently, with the identity fields having values 1 & 2. When I was doing the testing with the original structure, I had about ten records in the tables, but those were cleared when the structure was changed and re-testing started. No fields are defined as unique identifiers, and no field other than the identity field is indexed.
 
Upvote 0 Downvote
bborissov,

The commit isn't in the section of code I pasted - there's some other work going on that I'm actually skipping right now for testing purposes (copying records in the detail tables.) Right after the Error check, I have a GOTO TRAN_COMPLETE and put the following tag at the bottom:

Code: 
TRAN_COMPLETE:

/* -------------------------------------------
End of process.
Commit the transaction.
---------------------------------------------- */
COMMIT TRANSACTION
...so I could skip the other INSERTS until I get this one working.
 
Upvote 0 Downvote
put a Print statment after the tran_complete and tran_failed statements to indicate which one fired. That way you will be sure the transaction was comitted if you see the correct print statement come up.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Well, actually, I'm stepping through the stored proc in debug mode - oh, jeeze, let me check something....

....you guys want to kill me now, or wait 'til later? After doing the code modifications, I forgot to make sure Auto-Rollback (which I'd had turned off before) was still turned off. It wasn't. Turn that off, and the record is now written out just like it should be. I still don't know why the other code wasn't working, but I'll think about that later. Right now, this raises one more question: I haven't used SCOPE_IDENTITY before; is the new identity value not available until the COMMIT? I thought I'd seen SCOPE_IDENTITY used inside a transaction to retrieve the new identity that was created.
 
Upvote 0 Downvote
Ignore that last question, I just answered it: the SCOPE_IDENTITY *is* putting the value in my work field, it just didn't print it from the SELECT statement until the procedure ended. So as long as I can retrieve that value, looks like I'm good to go. Thanks to all.
 
Upvote 0 Downvote
What you get as a message if you run this in QA:
Code: 
....
    SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordHeader]
                    (ProjectNo,
                    TaskNo,
                    WEDate,
                    ServiceArea,
                    Client)
            VALUES    (' + dbo.quotestring(@ProjectNo) + ', '
                    + dbo.quotestring(@TaskNo) + ', '
                    + dbo.quotestring(@WEDate) + ', '
                    + dbo.quotestring(@ServiceArea) + ', '
                    + dbo.quotestring(@Client) + ')'

PRINT @SqlState

---    exec (@SqlState)
....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Sorry for the previous question. I didn't read your answers before I post it. So just ignore it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
No problem. Just as FYI, though, it looked like this:

Code: 
INSERT INTO [dbo].[tblBillRecordHeader] (ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
VALUES '175508001', '500.100', '2008-08-08 00:00:00.000', 'COE', '63430')

I've changed the coding now to use nondynamic SQL and I'm in the process of doing the same to the other INSERTs for the detail tables.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
281
Andrzejek
Andrzejek
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
234
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
252
SaltyTheFrog
SaltyTheFrog
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
145
Chris Miller
Chris Miller
Auguy
  • Locked
  • Question
Using UDF In Select, Bad Idea? 2
Replies
3
Views
151
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top