I am unable to save details with some emailids. I am getting this error
The index entry of length 904 bytes for the index 'IX_companyinfo_exhibition' exceeds the maximum length of 900 bytes.
When I entered the following emailsids, it's not getting saved:
info@combilift.com
marketing@hildaautomation.in
info@pakona.com
info@himedialabs.com
info.lho@liebherr.com
During debugging,in catch block, I found that I got this error:
The index entry of length 904 bytes for the index 'IX_companyinfo_exhibition' exceeds the maximum length of 900 bytes.
Stored Procedure:
USE [expogroupdb]
GO
/****** Object: StoredProcedure [dbo].[InsertManuallyUsingXmlExhibitorListNew] Script Date: 2/20/2018 9:49:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertManuallyUsingXmlExhibitorListNew]
@XML_TRANSACTIONDETAILS nText,
@exhibitor nvarchar(100)
AS
DECLARE @docHandle int
SET NOCOUNT ON;
DECLARE @exbitionID nvarchar(100)
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML_TRANSACTIONDETAILS
-- CREATE TEMP TABLE
CREATE TABLE #TempTable
(
maxid nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
companyname nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
email nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
website nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
country bigint NULL,
contactperson nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
telphone nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
mobile nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
fax nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
region bigint NULL,
status bigint NULL,
username bigint NULL,
date datetime NULL,
category bigint NULL,
urlorcatalog nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
address nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
lastupdatedby bigint NULL,
designation nvarchar(100) Collate SQL_Latin1_General_CP1_CI_AS NULL,
products nvarchar(400) Collate SQL_Latin1_General_CP1_CI_AS NULL,
comment nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL
)
-- Create Table to save Duplicates
CREATE TABLE #DuplTempTable
(
maxid nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
ID bigint ,
Dataid nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
companyname nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
email nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
website nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
country bigint NULL,
contactperson nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
telphone nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
mobile nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
fax nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
region bigint NULL,
status bigint NULL,
username bigint NULL,
date datetime NULL,
category bigint NULL,
urlorcatalog nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
address nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL,
lastupdatedby bigint NULL,
designation nvarchar(100) Collate SQL_Latin1_General_CP1_CI_AS NULL,
products nvarchar(400) Collate SQL_Latin1_General_CP1_CI_AS NULL,
comment nvarchar(200) Collate SQL_Latin1_General_CP1_CI_AS NULL
)
-- INSERT INTO TEMP TABLE DETAILS OF EMAIL
INSERT INTO #TempTable
(
maxid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,address,lastupdatedby,designation,products,comment
)
SELECT
data.maxid,data.companyname,data.email,data.website,data.country,data.contactperson,data.telphone,data.mobile,data.fax,data.region,data.status,data.username,GETDATE(),data.category,data.urlorcatalog,data.address,data.username,data.designation,data.products,data.comment
FROM OPENXML(@docHandle,N'master/TRANSACTION',3)
WITH
(
companyname nvarchar(200),
website nvarchar(200),
country bigint ,
contactperson nvarchar(200),
telphone nvarchar(200),
mobile nvarchar(200),
fax nvarchar(200),
region bigint,
status bigint,
username bigint,
category bigint,
urlorcatalog nvarchar(200),
email nvarchar(200),
maxid nvarchar(200),
address nvarchar(200),
designation nvarchar(100),
products nvarchar(400) ,
comment nvarchar(200)
) as data
-- INSERT INTO TEMP TABLE DETAILS OF EMAIL1
INSERT INTO #TempTable
(
maxid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,address,lastupdatedby,designation,products,comment
)
SELECT
data.maxid,data.companyname,data.email1,data.website,data.country,data.contactperson,data.telphone,data.mobile,data.fax,data.region,data.status,data.username,GETDATE(),data.category,data.urlorcatalog,data.address,data.username,data.designation,data.products,data.comment
FROM OPENXML(@docHandle,N'master/TRANSACTION',3)
WITH
(
companyname nvarchar(200),
website nvarchar(200),
country bigint ,
contactperson nvarchar(200),
telphone nvarchar(200),
mobile nvarchar(200),
fax nvarchar(200),
region bigint,
status bigint,
username bigint,
category bigint,
urlorcatalog nvarchar(200),
email1 nvarchar(200),
maxid nvarchar(200),
dataentrytype nvarchar(200),
lastupdatedby nvarchar(200),
address nvarchar(200),
designation nvarchar(100),
products nvarchar(400),
comment nvarchar(200)
) as data
WHERE data.email1 !=''
-- INSERT INTO TEMP TABLE DETAILS OF EMAIL2
INSERT INTO #TempTable
(
maxid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,address,lastupdatedby,designation,products,comment
)
SELECT
data.maxid,data.companyname,data.email2,data.website,data.country,data.contactperson,data.telphone,data.mobile,data.fax,data.region,data.status,data.username,GETDATE(),data.category,data.urlorcatalog,data.address,data.username,data.designation,data.products,data.comment
FROM OPENXML(@docHandle,N'master/TRANSACTION',3)
WITH
(
companyname nvarchar(200),
website nvarchar(200),
country bigint ,
contactperson nvarchar(200),
telphone nvarchar(200),
mobile nvarchar(200),
fax nvarchar(200),
region bigint,
status bigint,
username bigint,
category bigint,
urlorcatalog nvarchar(200),
email2 nvarchar(200),
maxid nvarchar(200),
address nvarchar(200),
designation nvarchar(100),
products nvarchar(400),
comment nvarchar(200)
) as data
WHERE data.email2 !=''
--**********************UPDATE TEMP TABLE IF COUNTRY AND REGION IS NULL********************
UPDATE #TempTable
SET
country=1
WHERE country=0
UPDATE #TempTable
SET
region=2
WHERE region=0
-- CODE TO COUNT DUPLICATES EMAILS
SELECT
COUNT
( email)
FROM
#TempTable T
WHERE
T.email !=''
AND
EXISTS (SELECT emailinfo.email FROM dbo.emailinfo WHERE email=T.email)
--insert into #DuplTempTable
INSERT INTO #DuplTempTable
(
ID,Dataid,companyname,email,website,country,contactperson,telphone,mobile,fax,region,status,username,date,category,urlorcatalog,address,lastupdatedby,designation,products,comment
)
select ID,COMPANY.Dataid,TEMP1.companyname,email,TEMP1.website,TEMP1.country,TEMP1.contactperson,TEMP1.telphone,TEMP1.mobile,TEMP1.fax,TEMP1.region,TEMP1.status,TEMP1.username,TEMP1.date,TEMP1.category,TEMP1.urlorcatalog,TEMP1.address,TEMP1.lastupdatedby,TEMP1.designation,TEMP1.products,TEMP1.comment
from
(
SELECT Email.Email,Email.dataId, tmp.companyname,tmp.website,tmp.country,tmp.contactperson,tmp.telphone,tmp.mobile,tmp.fax,tmp.region,tmp.status,tmp.username,tmp.date,tmp.category,tmp.urlorcatalog,tmp.address,tmp.lastupdatedby,tmp.designation,tmp.products,tmp.comment,ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
FROM #TempTable tmp
Inner JOIN emailinfo Email ON tmp.email =Email.email
WHERE
tmp.email !=''
)AS TEMP1
Inner JOIN dbo.companyinfo COMPANY ON TEMP1.DataID =COMPANY.dataId -- check this move this insd . email.dataid
WHERE
TEMP1.RowNumber =1
-- TRANSACTION BEGINS HERE
BEGIN TRANSACTION
---------------------- EXHIBITOR LIST ------------------------------------
IF EXISTS ( SELECT *
FROM exibitions
WHERE exhibitiorlist=@exhibitor )
BEGIN
SELECT @exbitionID = EID FROM exibitions WHERE exhibitiorlist=@exhibitor
end
ELSE
BEGIN
INSERT INTO exibitions VALUES (@exhibitor)
SET @exbitionID = (SELECT SCOPE_IDENTITY())
END
------------- UPADATE COMPANY INFO IF EMAIL EXIST ------------------------------------
--Akshata changes start
--select '#DuplTempTable',* from #DuplTempTable -- Akshata only for checking.
update cmp
set cmp.lastupdatedby=dupl.lastupdatedby,cmp.lastupdateddate=dupl.date,cmp.category=case when cmp.status!=1 and cmp.status!=2 and cmp.status!=13 and cmp.status!=17 then dupl.category else cmp.category end,
cmp.status=case when cmp.status!=1 and cmp.status < 6 and dupl.status < cmp.status then dupl.status else cmp.status end,
cmp.comment=case when cmp.comment!='' And cmp.comment!=dupl.comment and (dupl.comment!='' OR dupl.comment!=null) then COALESCE(cmp.comment + '/', '') + dupl.comment when (dupl.comment='' OR dupl.comment=null) then cmp.comment else dupl.comment end,
cmp.exblist=COALESCE(cmp.exblist + ',', '') + @exbitionID
from companyinfo cmp
inner join #DuplTempTable dupl on cmp.ID=dupl.ID
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
update cmp
set cmp.companyname=case when cmp.companyname='' or cmp.companyname=null then dupl.companyname else cmp.companyname end ,
cmp.website= case when cmp.website='' OR cmp.website IS NULL then dupl.website else cmp.website end ,
cmp.contactperson= case when cmp.contactperson='' OR cmp.contactperson IS NULL then dupl.contactperson else cmp.contactperson end,cmp.country = case when cmp.country=1 OR cmp.country IS NULL then dupl.country else cmp.country end,
cmp.telphone=case when cmp.telphone='' OR cmp.telphone IS NULL then dupl.telphone else cmp.telphone end,cmp.mobile= case when cmp.mobile='' OR cmp.mobile IS NULL then dupl.mobile else cmp.mobile end ,
cmp.fax= case when cmp.fax='' OR cmp.fax IS NULL then dupl.fax else cmp.fax end, cmp.region= case when cmp.region=2 OR cmp.region IS NULL then dupl.region else cmp.region end,
cmp.address= case when cmp.address='' OR cmp.address IS NULL then dupl.address else cmp.address end,cmp.designation= case when cmp.designation='' OR cmp.designation IS NULL then dupl.designation else cmp.designation end,
cmp.products= case when cmp.products='' OR cmp.products IS NULL then dupl.products else cmp.products end
from companyinfo cmp
inner join #DuplTempTable dupl on cmp.ID=dupl.ID
--select cmp.* from #DuplTempTable dpl inner join companyinfo cmp
--on dpl.ID=cmp.id
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
--akshata changes end
-- ************INSERT INTO PRIMARY TABLE(companyinfo)*************
INSERT INTO dbo.companyinfo
(
dataId,companyname,website,country,contactperson,telphone,mobile,fax,region,status,userid,date,category,urlorcatalog,dataentrytype,address,exblist,designation,products,comment
)
SELECT
T1.maxid,T1.companyname,T1.website,T1.country,T1.contactperson,T1.telphone,T1.mobile,T1.fax,T1.region,T1.status,T1.username,T1.date,T1.category,T1.urlorcatalog,'MANUAL',T1.address,@exbitionID,T1.designation,T1.products,T1.comment
FROM
(
SELECT tmpdata.*, ROW_NUMBER() OVER (PARTITION BY maxid ORDER BY maxid ) AS 'RowNumber'
FROM
(
SELECT
*
FROM
(
SELECT tmp.*, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber1'
FROM #TempTable tmp
-- LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
)AS T
WHERE T.RowNumber1=1
AND T.email !=''
AND
NOT EXISTS (SELECT emailinfo.email FROM dbo.emailinfo WHERE email=T.email)
)tmpdata
)AS T1
WHERE
T1.email !='' AND
T1.RowNumber=1
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
-- ************INSERT INTO SECONDARY TABLE*************
INSERT INTO emailinfo
(
DataID,email
)
SELECT
T.maxid,T.email
FROM
(
SELECT tmp.maxid,tmp.email, ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
FROM #TempTable tmp
--LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email
)AS T
WHERE T.RowNumber=1
AND T.email !=''
AND
NOT EXISTS (SELECT emailinfo.email FROM dbo.emailinfo WHERE email=T.email)
IF @@ERROR<>0 BEGIN ROLLBACK TRANSACTION RETURN -101 END
COMMIT TRANSACTION
DROP TABLE #TempTable
DROP TABLE #DuplTempTable
EXEC sp_xml_removedocument @docHandle
Is there any changes needed in stored procedure for email? I didn't tried anything much for this issue as I didn't got the meaning of this error only. I searched the web and the only thing I got is to modify the column.
I am beginner in C# and dealing with the stored procedure for the first time.
