Insert Syntax Error

In SQL 7.0 SP3 , I am receiving this message...

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.

When I try to execute this code from SQL Query Analyzer...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@.dppnumbercursor)

Bsically, I want to insert the number held in @.dppnumbercursor in the APQPSub_id field.

Any help would be appreciated.How about END at the end?|||...and DEALLOCATE/CLOSE would be very appropriate ;)|||OK, I'm an idiot...can you tell I'm a newbie? Thanks for not tearing me up too bad :-)

Thanks to both for your help....|||You're welcome...from both of us ;)|||Nobody is going to mention the double quotes?

Or the fact that s/he doesn't need a cursor at all?

INSERT INTO APQPSubformTable(apqpsub_id)
SELECT PPAP_ID
FROM ppap
WHERE ppap_cancel <> '1'
AND ppap_close <> '1'
AND projectonhold <> '1'|||I was thinking about it, but then decided not to...when he/she thanked "both" of us ;)|||It's been one of those days:-)

Just for my own edification, what about the double quotes?

The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")

FETCH NEXT from DPPNumberCursor

INTO @.DPPNumberCursor

End

Close DPPNumberCursor|||Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).|||Just for my own edification, what about the double quotes?

It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.

I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.|||Don't Play with Settings...

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
SELECT @.dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
UNION ALL
SELECT @.dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'

Insert query problem

I am getting this message
"Msg 128, Level 15, State 1, Line 2
The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
context. Only constants, expressions, or variables allowed here. Column name
s
are not permitted."
when i execute below query in store procedure which dynamic sql query and i
guess because @.strA and strB are string data might cuase problem as in vb.ne
t
i put single quote around it like values(' " & strA & " ',' "& strB " ')"
vb.net code... but i dont know that thing in sql server 2005 as it use singl
e
quote any body has any idea thanks
declare strA nvarchar(20), strB nvarchar(60)
@.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(' + @.strA + ','
+ @.strB + ')'how do you want the query string to look like?
is
'A0000000000 B00C3124901 C00200603071'
the value you are inserting
in that case
do this
'''A0000000000 B00C3124901 C00200603071'''
its 3 single quotes.
it would be better if you can try this out in query analyzer and find the
problem.
and why are you using dynamic SQL.
Can you paste the actual insert script that you are trying to build?
"amjad" wrote:

> I am getting this message
> "Msg 128, Level 15, State 1, Line 2
> The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
> context. Only constants, expressions, or variables allowed here. Column na
mes
> are not permitted."
> when i execute below query in store procedure which dynamic sql query and
i
> guess because @.strA and strB are string data might cuase problem as in vb.
net
> i put single quote around it like values(' " & strA & " ',' "& strB " ')"
> vb.net code... but i dont know that thing in sql server 2005 as it use sin
gle
> quote any body has any idea thanks
> declare strA nvarchar(20), strB nvarchar(60)
> @.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(' + @.strA + ','
> + @.strB + ')'|||amjad (amjad@.discussions.microsoft.com) writes:
> I am getting this message
> "Msg 128, Level 15, State 1, Line 2
> The name 'A0000000000 B00C3124901 C00200603071' is not permitted in this
> context. Only constants, expressions, or variables allowed here. Column
> names are not permitted."
> when i execute below query in store procedure which dynamic sql query
> and i guess because @.strA and strB are string data might cuase problem
> as in vb.net i put single quote around it like values(' " & strA & " ','
> "& strB " ')" vb.net code... but i dont know that thing in sql server
> 2005 as it use single quote any body has any idea thanks
> declare strA nvarchar(20), strB nvarchar(60)
> @.sql='INSERT INTO [Dev Work].[dbo].[tbltemp](A,B)
> VALUES(' + @.strA + ',' + @.strB + ')'
There is no quoting delimiters in the generated SQL command.
But you should not generate SQL in this way. First of all, it's a little
difficult to see why you would need to use dynamic SQL at all in a stored
procedure for a simple INSERT command. What's wrong with:
INSERT INTO [Dev Work].[dbo].[tbltemp](A,B) VALUES(@.strA, @.strB)
If there is more to it that you don't show, so dynamic SQL indeed is
necessary, you should use sp_executesql instead. This saves you from
the syntactic hassle you are running into now.
I have a longer article where I discuss dynamic SQL in general, and also
extensively cover sp_executesql. You find it on
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks three quote solved the problem as i am doing some string manipulation
and validation on that data and put invalide data into temp table where i am
doing dynamic sql to insert one by one record. one thing more is their any
mod function in sql server like excel or vb thanks
"Omnibuzz" wrote:
> how do you want the query string to look like?
> is
> 'A0000000000 B00C3124901 C00200603071'
> the value you are inserting
> in that case
> do this
> '''A0000000000 B00C3124901 C00200603071'''
> its 3 single quotes.
> it would be better if you can try this out in query analyzer and find the
> problem.
> and why are you using dynamic SQL.
> Can you paste the actual insert script that you are trying to build?
> --
>
>
> "amjad" wrote:
>|||No sweat :)
--
"amjad" wrote:
> thanks three quote solved the problem as i am doing some string manipulati
on
> and validation on that data and put invalide data into temp table where i
am
> doing dynamic sql to insert one by one record. one thing more is their any
> mod function in sql server like excel or vb thanks
> "Omnibuzz" wrote:
>