Can't create a function

  • This is nuts!

    I am trying to create a function in a database in SQL2000. I have created and used this function on MANY other databases both 2000 and 2005. The problem database(s) throws the following:

    Server: Msg 170, Level 15, State 1, Line 2

    Line 2: Incorrect syntax near 'function'.

    Server: Msg 137, Level 15, State 1, Line 6

    Must declare the variable '@nCount'.

    Server: Msg 137, Level 15, State 1, Line 9

    Must declare the variable '@cSExp'.

    Server: Msg 137, Level 15, State 1, Line 10

    Must declare the variable '@nCount'.

    Server: Msg 137, Level 15, State 1, Line 11

    Must declare the variable '@cSExp'.

    Server: Msg 178, Level 15, State 1, Line 17

    A RETURN statement with a return value cannot be used in this context.

    I can create the function in master on this server but none of the other databases. Now here is the REALLY crazy part, if I copy another function already compiled in the database, change the name and try to compile that I get similar errors. The account I am logged into is Administrator and has the top level sysadmin role assigned to it. This has got to be some privilege issue but neither the DBAs at the client site or anyone I work with has seen this. Any ideas??????

    THANKS!!!!

    jb

  • Think you could maybe post the code that you're running? Without that I can't begin to guess why you're getting a syntax error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The syntax is not the issue. As I said (or thought I said), the function WILL compile against the "master" database on the server as well as another database. It also compiles on about a dozen other servers.

    This code

    ***************************************************************************************************

    CREATE FUNCTION [dbo].[AT] (@cSExp NVARCHAR(4000), @cExp2S NVARCHAR(4000), @nCount SMALLINT = 1 )

    RETURNS SMALLINT

    AS

    BEGIN

    IF @nCount > 0

    BEGIN

    DECLARE

    @i SMALLINT,

    @StartPos SMALLINT

    SELECT @i = 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN)

    WHILE @StartPos <> 0 and @nCount > @i

    SELECT @i = @i + 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN, @StartPos+1 )

    END

    ELSE

    SET @StartPos = NULL

    RETURN @StartPos

    END

    *****************************************************************************************************

    gives this error:

    ****************************************************************

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'FUNCTION'.

    Server: Msg 137, Level 15, State 1, Line 5

    Must declare the variable '@nCount'.

    Server: Msg 137, Level 15, State 1, Line 10

    Must declare the variable '@cSExp'.

    Server: Msg 137, Level 15, State 1, Line 11

    Must declare the variable '@nCount'.

    Server: Msg 137, Level 15, State 1, Line 12

    Must declare the variable '@cSExp'.

    Server: Msg 178, Level 15, State 1, Line 18

    A RETURN statement with a return value cannot be used in this context.

    ****************************************************************

    When run against the problem databases, yet on the SAME server runs just fine against master and a couple of other databases on the same server. AND the permissions are EXACTLY the same because I'm trying it under the SA account.

    ??????????????????????????????

    jb

  • Check this out, if you remove the GO before the create function, I get the same list of errors you get :

    SET NOCOUNT ON

    GO

    CREATE FUNCTION [dbo].[AT] (@cSExp NVARCHAR(4000), @cExp2S NVARCHAR(4000), @nCount SMALLINT = 1 )

    RETURNS SMALLINT

    AS

    BEGIN

    IF @nCount > 0

    BEGIN

    DECLARE

    @i SMALLINT,

    @StartPos SMALLINT

    SELECT @i = 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN)

    WHILE @StartPos <> 0 and @nCount > @i

    SELECT @i = @i + 1, @StartPos = charindex(@cSExp COLLATE Latin1_General_BIN, @cExp2S COLLATE Latin1_General_BIN, @StartPos+1 )

    END

    ELSE

    SET @StartPos = NULL

    RETURN @StartPos

    END

    GO

    --DROP FUNCTION AT

    I also had similar problems between case sensitive and case insensitive servers / db (assuming solution 1 doesn't work).

  • Nope, same error message. I figure it's got to be a permission thing because it'll run on other DBs on the same SQL server. I keep coming back to that or some wacky corruption. But I've been banging my head on this for a while now. This client has contract DBs come in and they're unwilling to pay for them to come in and look at this. Since it's my code it's my problem or so they reason.

    Thanks for the efforts though!

    jb

  • Can't be permissions with SA... unless there's something I'm not aware of.

    And I checked it on my server (case sensitive) and the code runs just fine.

    I have no idea what else can be causing this.

    Have you tried login on the server with a login that is in the built-in administrators group so that you're sure to be considered like a sa on the server?

  • Yes, I've tried 3 different accounts with sysadmin roles. They all do the same thing. I've tried using both windows authentication and an entered login (SA) logging into SQL server. Same behavior.

    jb

  • i get that error if the database is set to compatibility mode "70"; but at 80 or above it works fine....

    maybe the server you are playing with is an upgrade from SQL 7, and the compatibility mode is left at 70?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks Lowell, I'll have to look at that!

    jb

    ps big Calvin fan here!

  • Just an extra thought here, but why would you want to use a reserved word for the name of a function? It's not the least bit descriptive of what the function does, and, while unlikely, still allows for the possibility of some arcane bug related to reserved word checking...

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • smunson,

    I submit to an old coder's tenet "Why write it if somebody else already did, just borrow it" I found this code out on a MS (I believe) forum. It is supposed to be 2 things, a throwback to some old FoxPro functions and an included function in a future release of SQL Server. The code does exactly what I need it to do. Previously I had a VERY long string which I needed to parse thru using CHRINDEX. This function removes a ton of code and makes it a lot more readable.

    😎

    jb

  • jb, was my guesstimate right? were all the databases on that server at compatibility 70, but master at 80 due to an upgrade?

    you are all set on that server?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • hey lowell,

    was running around with fire hoses all day so I didn't get a chance to get back on. Hopefully today although my emails are piling up already. I will most certainly get back to you though.

    jb

  • I'm sure you can find the time to run this statement and get your answer :

    SELECT name, crdate, cmptlevel FROM master.sys.sysdatabases ORDER BY cmptlevel

  • Lowell,

    You are the man! The databases that I can't create the functions on are 70. Where I can create them is 80. So, next question is what happens if I change the 70 to 80? I am not a DBA, just a lowly SQL coder.

    jb

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply