VB stored proc error, but not in query analyzer

[violetstarlight]

Why would a stored procedure error when called from VB using ADO but when the command text is entered into query analyzer, it would work correctly?

VB returns an error about an unclosed quotation mark, that I can’t find anywhere in the statement. So I copied the command text and ran in query analyzer and it worked fine with no errors…HELP?

 

[dvannoy]

how are you calling the procedure??

 

[MarkSweetland]

One possibility: your Analyzer may have the Set quoted_identitier option set to OFF in which case the analyzer will allow single or double quotation marks to enclose character strings.

If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks:

SELECT * FROM [My Table]
WHERE [Last Name] = "O'Brien"

rather than
SELECT * FROM [My Table]
WHERE [Last Name] = 'O''Brien'

If it's ON, double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings. That may be why it works in the analyzer and not through ADO.




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[violetstarlight]

The code calling the stored proc is:
sp = "InsertNewThread '" & _
ID & "', '" & _
DateTimeReceived & "', '" & _
From & "', " & _
SupplierID & ", '" & _
Subject & "', '" & _
Message & "', " & _
ThreadStateID & ", " & _
iBitMarkAsRecd & ", " & _
NodeID

m_rsSaveThread.Open sp, g_cnBSFImports, adOpenKeyset, adLockOptimistic

....and the stored proc is:
CREATE PROCEDURE InsertNewThread
@ID VARCHAR(16),
@DateTimeReceived DATETIME,
@From VARCHAR(2000),
@SupplierID NUMERIC,
@Subject TEXT,
@Message TEXT,
@ThreadStateID NUMERIC,
@MarkAsRecd NUMERIC,
@NodeID NUMERIC
AS

SET NOCOUNT ON

BEGIN

SET QUOTED_IDENTIFIER ON

INSERT INTO tblThread
(ThreadID,
DateTimeReceived,
FromAddress,
SupplierID,
ThreadSubject,
ThreadMessage,
ThreadStateID,
MarkAsReceived,
NodeID)
VALUES
(@ID,
@DateTimeReceived,
@From,
@SupplierID,
@Subject,
@Message,
@ThreadStateID,
@MarkAsRecd,
@NodeID)

END

 

[violetstarlight]

...oh, and one thing is that the field erroring is the Message variable. And there are no quotes in it? The field contains text marked up with rich text formatting, but no quotes anywhere....

 

[CajunCenturion]

One thing that I've found helpful is to write my statements in the following manner:

sp = "InsertNewThread & _
         "'" & ID & "', " & _
         "'" & DateTimeReceived & "', " & _
         "'" & From & "', " & _
         "" & SupplierID & ", " & _
         "'" & Subject & "', " & _
         "'" & Message & "', " & _
         "" & ThreadStateID & ", " & _
         "" & iBitMarkAsRecd & ", " & _
         "" &  NodeID

Helps me to insure that my field quotations are correct and match up.

However, with respect to this problem, I think MarkSweetland is on the right track. I would check the contents of the Message variable for an embedded quotation mark.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[aalmeida]

Also helps to check how the SP was created specific the
"Set quoted_identifier" option set to "ON" or "OFF" that will affect the way SQL server looks at identifiers like table names strings and numbers.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"