Professional Web Applications Themes

Problem creating function in MS SQL 2000?? - Microsoft SQL / MS SQL Server

Hi, I am trying to create the following function in MS SQL Server 2000 through WinSQL (ODBC utility program): CREATE FUNCTION GetString (input_value varchar(255)) returns varchar(255) as begin return SUBSTRING(input_value, (CHARINDEX(';', input_value)+1), LEN(input_value)) end It returns the following error messages: Error: Unclosed quotation mark before the character string ''. (State:37000, Native Code: 69) Error: Line 3: Incorrect syntax near ''. (State:37000, Native Code: AA) Error: Unclosed quotation mark before the character string ', input_value)+1), LEN(input_value)) end'. (State:37000, Native Code: 69) The ';' used in the statement is creating the problem. How do I escape the ; ? The problem does ...

  1. #1

    Default Problem creating function in MS SQL 2000??

    Hi,

    I am trying to create the following function in MS SQL Server 2000 through
    WinSQL (ODBC utility program):

    CREATE FUNCTION GetString (input_value varchar(255)) returns varchar(255)
    as
    begin
    return SUBSTRING(input_value, (CHARINDEX(';', input_value)+1),
    LEN(input_value))
    end

    It returns the following error messages:

    Error: Unclosed quotation mark before the character string ''. (State:37000,
    Native Code: 69)
    Error: Line 3: Incorrect syntax near ''. (State:37000, Native Code: AA)
    Error: Unclosed quotation mark before the character string ',
    input_value)+1), LEN(input_value))
    end'. (State:37000, Native Code: 69)

    The ';' used in the statement is creating the problem. How do I escape the
    ; ?

    The problem does not occur if I run the same statement in Query yzer
    that comes along with SQL Server.

    This is an urgent problem and any help would be appreciated.

    Thank you,

    Sameer


    Sameer Guest

  2. #2

    Default Re: Problem creating function in MS SQL 2000??

    Hi

    I think you are trying to put this into a string? Therefore you need to
    escape the quotation marks with a second quotation mark e.g.

    DECLARE SQL VARCHAR(8000)

    SET SQL = 'CREATE FUNCTION GetString (input_value varchar(255)) returns
    varchar(255)
    AS
    BEGIN
    RETURN SUBSTRING(input_value, (CHARINDEX('';'', input_value)+1),
    LEN(input_value))
    END'

    EXEC ( SQL)

    John


    "Sameer Karmarkar" <com> wrote in message
    news:%phx.gbl... 
    (State:37000, 
    the 


    John Guest

  3. #3

    Default Re: Problem creating function in MS SQL 2000??

    Sameer Karmarkar (com) writes: 

    That appears to be a question for a forum devoted to WinSQL.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: Problem creating function in MS SQL 2000??

    The problem is not restricted to WinSQL only.

    This problem was also experienced when I tried to create the function
    through Java code (JDBC).

    Thanks,

    Sameer

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... [/ref]
    through [/ref]
    varchar(255) 
    >
    > That appears to be a question for a forum devoted to WinSQL.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Sameer Guest

Similar Threads

  1. creating DSN in CF MX 7 to SQL Server 2000 SP4 db
    By Richard Dryfist in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 3rd, 03:28 PM
  2. Problem creating trigger-function with arguments (8.0rc4)
    By Michael Fuhr in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 7th, 11:08 PM
  3. Access 2000 and creating stored procedures
    By Mike in forum ASP Database
    Replies: 11
    Last Post: August 20th, 11:10 AM
  4. Creating trust between SBS 2003 & 2000 server
    By divakar in forum Windows Server
    Replies: 2
    Last Post: July 5th, 12:09 PM
  5. [PHP] problem: creating global alias inside function
    By Mike Ford in forum PHP Development
    Replies: 3
    Last Post: July 29th, 03:34 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139