Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Linked Server Insert

Posted on 2007-03-28
2
Medium Priority
?
258 Views
Last Modified: 2008-02-01
The problem here may have nothing to do with the fact that I am using a linked server.
Basically I am trying to fill a customer table based on a view from another database. It works fine apart from 2 things.

delete from  webdata.dbo.Customers
INSERT INTO webdata.dbo.Customers (CustomerId, [name] )
select distinct cCustomerId, cName From [Enterprise].[JDE_Production].[dbo].[e_VIEWCustomers]

I get this error "String or binary data would be truncated. The statement has been terminated."
I know what this means as when i use Left(cName ,15) the statment run fine. I am going to be filling address and other info and woould rather not use left for each. Can I let the statement run and truncate the data if it is too large.

The other problem is this when I remove the DISTINCT from the select it fails and tells me I cannot insert duplicates however  the VIEW e_VIEWCustomers already contains DISTINCT. Maybe I am using distinct wrong.

Here is the e_VIEWCustomers select if you want to have a look  THANKS.

SELECT DISTINCT
                      TOP 100 PERCENT PRODDTA.F0101.ABAN8 AS cCode, RIGHT(STR(PRODDTA.F0101.ABAN8), 6) AS cCustomerId,
                      PRODDTA.F0111.WWMLNM AS cName, PRODDTA.F0116.ALADD1 AS cAddress1, PRODDTA.F0116.ALADD2 AS cAddress2,
                      PRODDTA.F0116.ALADD3 AS cAddress3, PRODDTA.F0116.ALADD4 AS cAddress4, PRODDTA.F0101.ABAC03 AS cNon_Supp,
                      PRODDTA.F03012.AIDAOJ AS cDate, PRODDTA.F0101.ABSBLI AS cDeleted, PRODDTA.F03012.AIACL AS cLimit, PRODDTA.F03012.AITSTA AS cComment,
                       PRODDTA.F03012.AIEXR1 AS cVatExempt, PRODDTA.F0101.ABTAX AS cVatNo, PRODDTA.F0030.AYCBNK AS cBank_No,
                      PRODDTA.F03012.AIRYIN AS cDirect_DB, PRODDTA.F0111.WWGNNM AS cFirstname, PRODDTA.F0111.WWSRNM AS cSurname,
                      PRODDTA.F0111.WWSLNM AS cSalutation, PRODDTA.F03012.AISTMT AS cForcePrint, PRODDTA.F0115.WPPH1 AS cTelephone,
                      PRODDTA.F03012.AICLMG AS cRep, PRODDTA.F03012.AIHDAR AS cStopped, PRODDTA.F0101.ABUPMJ AS F0101UPMJ,
                      PRODDTA.F0111.WWUPMJ AS F0111UPMJ, PRODDTA.F0115.WPUPMJ AS F0115UPMJ, PRODDTA.F0116.ALUPMJ AS F0116UPMJ,
                      PRODDTA.F03012.AIUPMJ AS F03012UPMJ, PRODDTA.F0101.ABMCU AS CBusinessUnit, RTRIM(RIGHT(PRODDTA.F0101.ABMCU, 2)) AS CBranchNo,
                      RTRIM(RIGHT(REPLACE(PRODDTA.F0101.ABMCU, '  1', 'X'), 2)) AS Expr1
FROM         PRODDTA.F0101 LEFT OUTER JOIN
                      PRODDTA.F0115 ON PRODDTA.F0101.ABAN8 = PRODDTA.F0115.WPAN8 LEFT OUTER JOIN
                      PRODDTA.F0030 ON PRODDTA.F0101.ABAN8 = PRODDTA.F0030.AYAN8 LEFT OUTER JOIN
                      PRODDTA.F0111 ON PRODDTA.F0101.ABAN8 = PRODDTA.F0111.WWAN8 LEFT OUTER JOIN
                      PRODDTA.F03012 ON PRODDTA.F0101.ABAN8 = PRODDTA.F03012.AIAN8 LEFT OUTER JOIN
                      PRODDTA.F0116 ON PRODDTA.F0101.ABAN8 = PRODDTA.F0116.ALAN8
WHERE     (PRODDTA.F03012.AICO = '00001') AND (PRODDTA.F0101.ABAN8 > 10000000)
ORDER BY PRODDTA.F0101.ABAN8






0
Comment
Question by:Eamon
[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
2 Comments
 
LVL 1

Author Comment

by:Eamon
ID: 18809200
Ok I am using the DISTINCT wrong as when I add more fields it fails so there must be a duplicate account no with a different address or something.

So forget about the DISTINCT
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 18813433
Hi,

For your error message
I get this error "String or binary data would be truncated. The statement has been terminated."
I know what this means as when i use Left(cName ,15) the statment run fine. I am going to be filling address and other info and woould rather not use left for each. Can I let the statement run and truncate the data if it is too large.

It has already failed.

No, it will not truncate the data. Use left( cName, 15 ) or whatever is appropriate.

Regards
  David

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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