none
Invalid length parameter passed to the LEFT or SUBSTRING function. error urgent help needed

    Question

  • --------------------------------------

    data from which i am inserting is such like below

    name
    Arizona Cities Category Terms
    Category Terms Geo
    Colorado Cities Category Terms
    Idaho Cities Category Terms
    Iowa Cities Category Terms

    • Changed type Naomi NModerator Friday, March 16, 2012 1:07 AM Question rather than discussion
    Thursday, March 15, 2012 11:51 AM

Answers

  • Hi skc_Chat

    I believe you are inserting into a view of some sort or a triggers are placed on the table that your are inserting. 

    points to debug 

    1) check if you are inserting into a view, then see if there is a left or substring function in the view and check if the values are valid over there or else post the view definition here.

    SQL to check if it is a view/ not sp_help sem_db.dbo.Campaign is to do sp_help on the object.

    2) check if there are any after / before triggers placed on this table.

    SQL: sp_helptrigger '<table_name>'

    Surendra


    Nothing is Permanent... even Knowledge....
    My Blog

    Thursday, March 15, 2012 12:07 PM
  • It is possible that column you are trying to SUBSTRING or Left has few characters or has even NULL

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by skc_chat Friday, March 16, 2012 5:37 AM
    Thursday, March 15, 2012 12:01 PM

All replies

  • post sample data and your query

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Thursday, March 15, 2012 11:54 AM
  • set identity_insert Campaign ON
    insert into sem_db.dbo.Campaign  (id,accountId,name,label,defaultKeyword,templateId,dailyBudget,defaultCPC,type,contentMarketingOptIn,
    searchMarketingOptIn,advancedMatch,startDate,endDate,status,vendorIdString,lastUpdate,syncStatus,externalID,
    statusChangeSync,platformTarget)
    select top 6  id,accountId,name,label,defaultKeyword,templateId,dailyBudget,defaultCPC,type,contentMarketingOptIn,
    searchMarketingOptIn,advancedMatch,startDate,endDate,status,vendorIdString,lastUpdate,syncStatus,externalID,
    statusChangeSync,platformTarget
     from sem_db_dk.dbo.Campaign
    set identity_insert Campaign OFF
    Thursday, March 15, 2012 11:59 AM
  • It is possible that column you are trying to SUBSTRING or Left has few characters or has even NULL

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by skc_chat Friday, March 16, 2012 5:37 AM
    Thursday, March 15, 2012 12:01 PM
  • Thursday, March 15, 2012 12:03 PM
  • Hi skc_Chat

    I believe you are inserting into a view of some sort or a triggers are placed on the table that your are inserting. 

    points to debug 

    1) check if you are inserting into a view, then see if there is a left or substring function in the view and check if the values are valid over there or else post the view definition here.

    SQL to check if it is a view/ not sp_help sem_db.dbo.Campaign is to do sp_help on the object.

    2) check if there are any after / before triggers placed on this table.

    SQL: sp_helptrigger '<table_name>'

    Surendra


    Nothing is Permanent... even Knowledge....
    My Blog

    Thursday, March 15, 2012 12:07 PM
  • The best way to handle the error is NULLIF with COALESCE:

    http://www.sqlusa.com/bestpractices2005/nullif/

    The error is typical when parsing a string for marker with the CHARINDEX function.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, March 15, 2012 12:20 PM
  • no there was not any trigger, although i have able to insert for time being by some other logical miss i thing from my side.

    but somehow its resolved now.

    i have just replaced some value keeping constraint in mind with where condition and it got inserted.

    although that error is not caught by me i ll search more in free time.

    thanks all.

    Friday, March 16, 2012 5:37 AM