1

I'm trying to do an append query from MS Access into SQL server. The SQL server column is varchar(max) which I thought meant it can accept more than 4000 characters

I get the following error when running this query from VBA in MS Access

Run Time Error 3155 ODBC - insert on a linked table failed [Microsoft] [SQL Server Native Client 10.0] {SQL Server] The size (7596) given to the parameter '@P6' exceeds the maximum allowed (4000). (#2717)

adding my queries

this query is based on an linked outlook folder, Deleted Items

SELECT Trim(Mid([contents],InStr([contents],"Short Description: ")+19,(InStr([contents],"Requestor: ")-1)-(InStr([contents],"Short Description: ")+19)-3)) AS ShortDesc, Trim(Mid([contents],InStr([contents],"Requestor: ")+10,(InStr([contents],"Requestor EMail: ")-1)-(InStr([contents],"Requestor: ")+10)-3)) AS Requester, Trim(Mid(Mid([contents],InStr([contents],"Office Location: ")+3),InStr(Mid([contents],InStr([contents],"Office Location: ")),"Description:")+13,(InStr(Mid([contents],InStr([contents],"Office Location: ")),"Assigned Task: ")-1)-(InStr(Mid([contents],InStr([contents],"Office Location: ")),"Description:")+13)-3)) AS Description, Trim(Mid([contents],InStr([contents],"Request Item: ")+14,12)) AS TicketNoText, Val(Mid([contents],InStr([contents],"Request Item: ")+19,7)) AS TicketNo, Val(Mid([contents],InStr([contents],"Assigned Task: ")+19,7)) AS TaskNo, Mid([contents],InStr([contents],"Delivery Date: ")+15,10) AS DeliveryDate, Trim(Mid([contents],InStr([contents],"Requestor EMail: ")+17,(InStr([contents],"Office Location: ")-1)-(InStr([contents],"Requestor EMail: ")+17)-3)) AS RequesterEMail
FROM [Deleted Items]
WHERE ((([Deleted Items].From)="xxxxxxx@service-now.com") AND (([Deleted Items].Subject)="you just assigned a ticket to yourself"));

then, the append query is based on this one and a few other ones

INSERT INTO PROJECTS ( TaskNo, RequesterID, Description, TicketNo, ProjectFolderLink, SNLink, OpenedOn, DateDue )
SELECT QSNNew.taskno, cmbRequesters.RequesterID, "SHORT DESCRIPTION: " & [shortdesc] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "DESCRIPTION: " & [Description] AS Expr4, QSNNew.TicketNo, "#\\link to a network folder" & lpad([ticketno],"0",7) & "\#" AS Expr1, "#https://xxxx.service-now.com/nav_to.do?uri=sc_task.do?sysparm_query=number=TASK" & lpad([taskno],"0",7) & "#" AS Expr2, Now() AS Expr3, Mid([DeliveryDate],6,2) & "/" & Right([DeliveryDate],2) & "/" & Left([DeliveryDate],4) AS Expr5
FROM (QSNNew LEFT JOIN PROJECTS ON QSNNew.TicketNo = PROJECTS.TicketNo) LEFT JOIN cmbRequesters ON QSNNew.[Requester] = cmbRequesters.RequesterName
WHERE (((PROJECTS.TicketNo) Is Null));

in case anyone is wondering what I'm doing, I'm loading tickets from Service Now into an Access database and there's no other way of doing it, other than parsing notification emails i get from Service Now when a ticket is assigned to me. So I'm parsing those emails and creating my own version with links to the ServiceNow page, network folders for the ticket, etc.

lalachka
  • 403
  • 5
  • 16
  • 36
  • 2
    The column might be varchar(max), but the parameter (@P6) being used to insert the data clearly isn't. Do you have access to the code doing the insert? If so please post. – Dale K Sep 10 '19 at 02:18
  • 1
    See also this question: https://stackoverflow.com/questions/7978879/moving-from-text-to-varcharmax-are-there-any-troubles-to-expect-with-ms-acces – Andre Sep 10 '19 at 06:25
  • @Dale Burrell it's a few queries, one feeding from another and the base of it is outlook inbox)))) total mess but theres no other option to get this done. I will post the code for the 2 of them tomorrow. Thank you!! – lalachka Sep 11 '19 at 03:13
  • @dale-burrell I updated the original with the queries – lalachka Sep 12 '19 at 02:35
  • I can't see parameter `@P6` anywhere in your code - thats where the error lies. – Dale K Sep 12 '19 at 02:46
  • yeah it assigns that name by itsels but i know it's the DESCRIPTION field that errors. someone suggested using ADODB, will do and will see what happens – lalachka Sep 12 '19 at 06:10

4 Answers4

2

It's a matter of driver (SQL Native Client and ODBC Driver 17 are limited to 4000 chars). If you use SQL Server Driver (10.09.18362.01) limit is 64000 chars.

As yu2 suggested ADODB query would avoid that (ODBC Passthrough should do it too).

The parameter @P6 is produced by ODBC see Optimizing Microsoft Office Access Applications Linked to SQL Server Understanding Dynasets

ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20
1

This is answer for your question

3155 Insert into Linked Table error

On this link Microsoft recommends to use ADODB instead of ODBC if you can't decrease field size in application.

ODBC protocol is generally for big data -- sql server -- which uses "Fire Hose" bandwith. Access uses (my term) garden hose bandwith (with all due respect for mini RDBMSs) because Access is basically a mini RDBMS (relational database management system) which is also file based. Unless everything (front/back ends) is set up perfectly and conditions are ideal -- you will encounter the problem you are having. Microsoft came up with ADODB as a workaround for this problem. When I have to interface between sql server and Access -- I use ADODB. This has proven to be much more reliable and consistent between the small and large RDBMSs. Here is some sample ADODB code for reading from and writing to a Sql Server from Access

'--add a reference in Tools/References to Microsoft ActiveX Data Objects 2.x Library '--(2.5 or higher) ...

Dale K
  • 25,246
  • 15
  • 42
  • 71
yu2
  • 126
  • 4
  • Hi @yu2, Link only answers are not suitable for SO and will get deleted. Answers may reference links, but need to stand alone in case the link becomes broken in future. Please correct this. – Dale K Sep 10 '19 at 02:43
0

The Access "Long Text" column can contain a text string up to a gigabyte in size. The message says you are trying to fit 7596 characters into a 4000 character field.

If so, your SQL server database should be exposing an ODBC LongVarChar column instead of VarChar.

LongVarChar is an ODBC type. The mapping is done by the ODBC driver. If you use an ODBC driver that maps VarChar(MAX) to a VarChar ODBC column, you can either get a different driver, or, possibly, use a SQL SERVER 'TEXT' column instead. TEXT is the old SQL Server column type, from when VarChar could only go to 4000. Old ODBC drivers recognize that TEXT columns map to LongVarChar.

david
  • 2,435
  • 1
  • 21
  • 33
  • I thought varchar(max) can handle more than 4000. I will post my queries, seems someone has an answer once I do – lalachka Sep 11 '19 at 03:16
-1

I think the error message is quite helpful.
You are trying to fit a string with length of 7596 while your maximum varchar length is 4000.
I guess you either truncate it or store it as a blob.

John
  • 974
  • 8
  • 16