Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Strange problem in SQL2000 db

    Hello All,
    SQL 2000 does not allow me to create a table variable or UDF. I am sure my syntax is correct but it gives me a syntax error near 'create' or near 'table'. This db was originally ported from SQL 6.5->7.0->2000. Can anyone explain?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Posts
    7

    What's the error?

    ?

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    and what is your sql statement...

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You would get a better resolution to your problem if you post some sample code ... SQL Server details (2000/7.0/6.5) ... etc
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jan 2004
    Posts
    7

    Re: What's the error?

    Environment - SQl 2000 (originally upgraded from SQl 6.5 through 7.0 to 2000)

    Code:

    create function ufn_Concat(@Passedinvalue int)
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    DECLARE @XXtable TABLE(Note VARCHAR(255))
    DECLARE @Outputstr VARCHAR(8000)

    SET @Outputstr = SPACE(0)

    INSERT @XXtable
    SELECT Column_from_table
    FROM TABLE1
    WHERE Tablekey = @Passedinvalue

    UPDATE @XXtable
    SET @Outputstr = @Outputstr + ' / ' + Column_from_table

    RETURN @Outputstr
    END
    GO

    Error message:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'function'.
    Server: Msg 137, Level 15, State 1, Line 13
    Must declare the variable '@Passedinvalue'.
    Server: Msg 178, Level 15, State 1, Line 19
    A RETURN statement with a return value cannot be used in this context.

    This seems to happen on the production db on the server. I could execute the same piece of code in a dev copy db.

    Let me know if you need more information.

    Thanks!

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What do you get when you run sp_dbcmptlevel on this database? It should be 8.0

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A concrete example might be better...this does what you want...yes I got the same bugs trying to compile your code...but this will do what you want...

    Code:
    Use Northwind
    GO
    
    CREATE FUNCTION  udf_Concat(@x int)
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    	DECLARE @Outputstr VARCHAR(8000)
    	SET @Outputstr = ''
    
    SELECT @Outputstr = @Outputstr + CustomerId + '/'
      FROM Orders
     WHERE EmployeeId = @x
    
    SELECT @Outputstr = LEFT(@Outputstr,LEN(@Outputstr)-1)
    
    RETURN @Outputstr
    END
    GO
    
    SELECT dbo.udf_Concat(1)
    GO
    
    DROP FUNCTION udf_Concat
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    7
    Originally posted by MCrowley
    What do you get when you run sp_dbcmptlevel on this database? It should be 8.0
    It is set to 65. Not sure why. If i raise the compatibility to 80, I am able to compile the code.

    It was handed that way down from the previous dba. Will that explain why I cannot also use "Top" while selecting?

    Are there any points to keep in mind if i raise the compatibility to 80?

    I would really appreciate any response.

    Thanks in advance!

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    sp_dbcmptlevel sets certain database behaviors to be compatible with the specified earlier version of Microsoft SQL Server.

    You might want to take a look at this before changing the compatibility level of the database
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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