When creating even the simplest of Stored Procedures (that i know work!), i get prompted with an error Titled : <Microsoft SQL-DMO (ODBC SQLSTATE: 42000)> with an error message: <Error 170: Line 1: Incorrect syntax near "My Stored Proc Name". Must declare the variable '@.str_PartNo'>
I know for a fact that the stored procedure does not have a syntax error because i can run a stored proc that is already saved, but if i copy and paste it into a new store proc, i get this error! Also, when i click the check syntax there are no errors.
I was told to verify if the service <SQL Server Agent> is running. It is.
I could really use some helpThe agent won't affect this
Suspect either you aren't copying the correct data or there is an invalid character somewhere.
First thing to do is look for the definition of @.str_PartNo and see why it is not defined.
The message is saying it has a problem at line 1 which is a bit odd - do you have an exec of dynamic sql after a go somewhere?
try
create proc myproc
as
select 1
go
The copy and paste may be introducing errors due to invalid characters.|||it works, it seems the problem was that i was encompassing my Stored Proc name with double quotes, i've saved others with the double quotes...strange.
Thanks for the help|||It's because of the settings in the environment you are creating the sp from.
look at
set QUOTED_IDENTIFIER off
go
create procedure "mysp"
as
select 1
go
set QUOTED_IDENTIFIER on
go
create procedure "mysp"
as
select 1
go
drop procedure mysp
No comments:
Post a Comment