?
Solved

The identifier that starts with 'INSERT INTO is too long Maximum length is 128.

Posted on 2005-03-10
13
Medium Priority
?
3,826 Views
Last Modified: 2009-11-14
Hello

I'm trying to run this insert command on sql sever but I keep getting this error message

"INSERT INTO tblLicense (NumberBought, ProductID, Cost, TypeID, ServerWorkstation, StartDate, RenwalDate, SupplierID, OrderNumber, Notes, TimeInputmaid, UserID) VALUES ('12', '19', '12', '3', 'S', '31/03/2005', '00:00:00', '3', 'ordeernumber', 'notes', '10/03/2005 11:28:07', '6') "


Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'INSERT INTO tblLicense (NumberBought, ProductID, Cost, TypeID, ServerWorkstation, StartDate, RenwalDate, SupplierID, OrderNumber' is too long. Maximum length is 128.


This insert command is generated by a vb.net program how do I get round this problem?

Thanks FRIBBLE
0
Comment
Question by:FRIBBLE1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +5
13 Comments
 
LVL 11

Expert Comment

by:saleek
ID: 13504931
Is the stored procedure...

Change (all of) your double quotes to single quotes. Double quoted items
are considered identifiers and as such your entire query is being treated as
a column name.

regards,

KS
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13504933
Try removing the double quotes at the start and end of the statement. The error message thinks that you are providing an identifier (i.e. a column name) - these are limited to 128 characters
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 13504955

You may need to SET QUOTED_IDENTIFIERS OFF for this connection.
See the BOL page entitled: 103(error) for more information.

Is this command on its own, or does something come before it?

Do you have any triggers on the tblLicense table?

Thanks,
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:FRIBBLE1
ID: 13505004
Ok i've done that thats got rid of the first error.

It's now coming up with

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

This seems to be intermittent. The dates in sql server are in us format and the dates coming from my program are also in us format
MM/DD/YYYY


Thank FRIBBLE
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13505025
The problem is with the Renewal date column where the insert shows '00:00:00'
0
 
LVL 9

Expert Comment

by:solution46
ID: 13505028
Check the minimum date you are expecting to return. SQL Server will not be able to store any date earlier than 1 Jan 1799 and you will get this error.

s46.
0
 
LVL 23

Accepted Solution

by:
adathelad earned 1200 total points
ID: 13505029
I personally always pass dates into SQL using the yyyy-mm-dd format. This rules out any date conversion issues
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 13505038

Check the language of the login you are using is set to "English" aswell.
Or use an explicit "set dateformat mdy" command for the connection.

Ideally, use an unambigous date format in your insert statement, like ISO: e.g. yyyy-mm-dd as this is the format datetime columns will be stored in the database.

I'm sure VB.net will have a function to convert a date to ISO or something similar.

Hope that helps,
0
 
LVL 9

Expert Comment

by:solution46
ID: 13505039
Ah yes... MehulS78 is bob on (sorry, I forgot you were inserting set values, not reading them from a table).

Still, the problem is the same; you'd be best changing the renewal date to NULL (...'31/03/2005', , '3'...), but make sure your field accepts NULL values.
s46.
0
 
LVL 1

Author Comment

by:FRIBBLE1
ID: 13505055
the renwal date seems to work fine i will try changing the date format to yyyy-mm-dd and see what happens

0
 
LVL 23

Expert Comment

by:adathelad
ID: 13505061
The renewal date column is valid - by supplying 00:00:00, SQL Server will default the date to 1900-01-01


I think it's the start date field :'31/03/2005' - this is in the dmy format but sounds like SQL is trying to evaluate it in the mdy format. Hence, try passing in dates in the yyyy-mm-dd format, this cant go wrong
0
 

Expert Comment

by:kalyson1
ID: 25823425
It is astounding that FRIBBLE1 does not give at least partial credit to saleek, who answered the first problem he posted (the main problem he posted, in fact) with the correct solution.
0
 

Expert Comment

by:NIks
ID: 27625101
For passing string to a parameter, make sure it has single quotes in for double qoutes, also palces where we normally put single quotes ex: ColumnName like '%value%' has doubled single quotes making it look like ColumnName like ''%value%''

This should solve any problem occuring due to quotes
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question