Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Invalid length parameter passed to the LEFT or SUBSTRING function. error urgent help needed

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

  • Thursday, March 15, 2012 11:51 AM
     
     
    Sign In to Vote
    0
    --------------------------------------

    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

     

All Replies

  • Thursday, March 15, 2012 11:54 AM
     
     
    Sign In to Vote
    0
    post sample data and your query

    Thanks and regards, Rishabh , Microsoft Community Contributor

     
  • Thursday, March 15, 2012 11:59 AM
     
     
    Sign In to Vote
    0
    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 12:01 PM
    Answerer
     
     Answered
    Sign In to Vote
    0
    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:03 PM
     
     
    Sign In to Vote
    0
     
  • Thursday, March 15, 2012 12:07 PM
     
     Answered
    Sign In to Vote
    1

    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:20 PM
    Moderator
     
     
    Sign In to Vote
    1

    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


     
  • Friday, March 16, 2012 5:37 AM
     
     
    Sign In to Vote
    0

    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.