Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Unanswered: All of a sudden i Can't Create Stored Procedures?

    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 help
    Last edited by Sammy_S; 02-17-03 at 19:58.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    The 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.

  3. #3
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    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

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •