Post reply

Error message on SQL Server 2000

  • September 19, 2005 at 7:35 am

    #89048

    Can someone help, I've been getting an error meesage when I execute this code

    declare @sDescription as varchar(1000)

    declare @sSkill as varchar(1000)

    declare @sFallback as varchar(1000)

    declare @swmskill as varchar(1000)

    declare @sql as varchar(5000)

    set @sDescription = 'crap2'

    set @sSkill = 'crap4'

    set @sFallback = 'crap1'

    set @swmskill = 'crap5'

     

    set @sql = ' INSERT INTO [TRC]  ([Description2],Skill, Fallback,wmskill) VALUES (' + @sDescription + ',' + @sSkill + ',' + @sFallback + ',' + @swmskill + ')'

    exec (@sql)

     

    The error message is :-

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

    The name 'crap2' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

    can anyone help please

  • September 19, 2005 at 7:39 am

    #590910

    If you had seen the @sql value just before executing it, you will know whats wrong.

    You have to have another single quote in front of each variable. Something like this:

    set @sql = ' INSERT INTO [TRC]  ([Description2],Skill, Fallback,wmskill) VALUES (' + '''' + @sDescription + '''' +  ',' + '''' +  @sSkill + '''' +  ',' +  '''' + @sFallback + '''' +  ',' + @swmskill + '''' + ')'

    Thanks,

    Ganesh

  • September 19, 2005 at 7:46 am

    #590914

    Why are you using dynamic sql for this???

  • September 19, 2005 at 7:55 am

    #590920

    What I posted wasn't the complete code, it was from a stored procedure which allowed me to specify the table name from vb code, I was testing it in the sql query analyzer, but to simplify debugging I hard coded the table name

  • September 19, 2005 at 7:55 am

    #590919

    many thanks for the assistance, it works great now

  • September 19, 2005 at 8:08 am

    #590954

    I strongly suggest you read this :

    The Curse and Blessings of Dynamic SQL

  • September 19, 2005 at 8:12 am

    #590957

    thanks for the info, makes an interesting read

  • September 19, 2005 at 8:52 am

    #590973

    in one of the reply's it said "If you had seen the @sql value just before executing it, you will know whats wrong." is there a way of "covering" the viable to actually see what it contains?

  • September 19, 2005 at 8:57 am

    #590974

    print @VariableName

    then execute from QA

  • September 19, 2005 at 9:19 am

    #590981

    thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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