Post reply

probelm with a sql query

  • December 19, 2008 at 4:49 am

    #125288

    hi,

    i want to concatenate one string with the substring of the value in my variable and the final string needs to be inserted into a table.wat change i need to do in the below query.

    insert into log values("C:\Documents and Settings\Desktop\New Folder (4)"+"substring(?,30,len(?))")

    thanks in advance

  • December 19, 2008 at 6:54 am

    #915219

    I think you just need to do this:

    insert into log values("C:\Documents and Settings\Desktop\New Folder (4)"+"substring(?,30,len(?)-30)")

    The third parameter to Substring is length and using LEN(?) you are getting the entire length of the string instead of the length you want returned.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 21, 2008 at 10:32 pm

    #915953

    hi jack,

    it is showing the following error.

    [Execute SQL Task] Error: Executing the query "INSERT INTO [log] (filename) VALUES ('C:\Documents and Settings\Desktop\New Folder (4)' + SUBSTRING(?, 30, (LEN(?)-30)))" failed with the following error: "Insert value list does not match column list". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    wat i have to do now

    thanks in advance

  • December 22, 2008 at 12:23 am

    #915975

    That means that the number of columns in the table don't match the number of values in the insert. What's the definition of the Log table?

    In general, it it recommended that the column list be specified for an insert so:

    INSERT INTO tbl (col1, col2, col3) values (val1, val2, val3)

    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

  • December 22, 2008 at 1:11 am

    #915986

    hi,

    the log table has only one column. actually im using this execute task in event handler.

    when i modify the insert statement such that only one ? is present then the task is executing correctly.

    but when i give as substring(which has two ?) it is not executing..

    wat is the mistake here?

    thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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