This type of 'Insert Into' has been working fine for me.
But, I have run into a situation where @sql is larger than 8000 characters.
I thought that if I declare @sql as varchar(nomax) I should get over 2 gb of space.
It seems to use 8000 not the larger 2 gb for MAX.
DECLARE @sql VARCHAR(max)
SET @sql = N'INSERT INTO [#IssuesDetails]
SELECT * from openquery
([GETPAID], '''
+ Replace(@sql, '''', '''''') + ''')'
EXEC (@sql)
I attached the error.
I can do a work around but wondering why this does not work.
It seems to ignore the NOMAX.
When I run the SSRS report with less parameter choices everything works fine.
This 'Insert Into' is reading records from Oracle tables and inserting them into a SQL table called #IssuesDetails NomaxError.PNG
Microsoft SQL Server 2008
Last Comment
thayduck
8/22/2022 - Mon
thayduck
ASKER
Here is complete statement:
--build tbl_rmcore_gprecl table from Oracle table (ar invoice table)
SET @sql = 'SELECT SUBSTR(GPCOMP1.GPRECL.CUSTNO,1,15) as CUSTNO, SUBSTR(GPCOMP1.GPRECL.CUSTNO,2,6) as CUSTNO6, GPCOMP1.GPRECL.AMOUNT as AMOUNT, GPCOMP1.GPRECL.TRANTYPE as TRANTYPE,
GPCOMP1.GPRECL.DUEDATE as DUEDATE, GPCOMP1.GPRECL.INVDATE as INVDATE,(CASE WHEN GPCOMP1.GPRECL.FLEXDATE1 IS NOT NULL AND GPCOMP1.GPRECL.FLEXDATE1 <> ''0001/01/01'' THEN GPCOMP1.GPRECL.FLEXDATE1
ELSE SYSDATE END) as FLEXDATE1, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD2,1,5) as FLEXFIELD2, GPCOMP1.GPCUST.LIMIT as LIMIT,
GPCOMP1.ARMAST.INVDTE AS ARINVDATE, GPCOMP1.ARMAST.PNET as TERMS, SUBSTR(GPCOMP1.GPCOLL.TEAM,1,10) as TEAM,
GPCOMP1.GPCOLL.COLLNAME AS PORTFOLIONAME, GPCOMP1.GPCUST.COLLTYPE AS CUSTTYPE, GPCOMP1.GPCUST.COMPANY as COMPANY,
GPCOMP1.GPPROB.PROBLEM_ID AS ProblemID,GPCOMP1.GPPROB.STATUS as Status1, SUBSTR(GPCOMP1.GPPROB.UCONTENT01,1,255) AS DisputeDesc,
GPCOMP1.GPREACAT.DESCRIPTION AS ReasonCategory, GPCOMP1.GPPCAT.PDESC AS Reason,
GPCOMP1.GPOWNTYP.OWNDESC AS OwnerCategory, GPCOMP1.GPSLPN.SALEPERSON AS Owner1, GPCOMP1.GPPROB.SALESAREA AS SalesArea,
TO_CHAR(GPCOMP1.GPPROB.DE_DATE,''YYYY/MM/DD'') AS EscalationDate,
GPCOMP1.GPRESLVR.RESNAME AS Resolver,
TRUNC(SYSDATE)-TRUNC(GPCOMP1.GPPROB.DE_DATE) as EscalationDaysDiff,
TRUNC(SYSDATE)-TRUNC(GPCOMP1.GPPROB_RECL.MODIFIED_ON) as StatusDays,
GPCOMP1.GPSTATUS.DESCRIPTION AS StatusName, SUBSTR(GPCOMP1.GPPROB.UCONTENT04,1,30) AS ProblemExtended,
GPCOMP1.GPCOLL.COLLCODE as PortfolioCode, SUBSTR(GPCOMP1.GPPROB.UCONTENT02,1,255) AS NextStep, SUBSTR(GPCOMP1.GPPROB.UCONTENT03,1,255) AS Required1
WHERE GPCOMP1.GPRECL.CUSTNO = GPCOMP1.GPCUST.CUSTNO AND GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_ID AND
GPCOMP1.GPPROB_RECL.PROBLEM_ID = GPCOMP1.GPPROB.PROBLEM_ID AND GPCOMP1.GPPROB.PCODE = GPCOMP1.GPPCAT.PCODE AND
GPCOMP1.GPPCAT.REASCAT = GPCOMP1.GPREACAT.CATEGORY AND GPCOMP1.GPPROB.SALESID = GPCOMP1.GPSLPN.SALESID AND
GPCOMP1.GPSLPN.OWNTYPE = GPCOMP1.GPOWNTYP.OWNTYPE AND GPCOMP1.GPCUST.COLLECTOR = GPCOMP1.GPCOLL.COLLCODE AND
GPCOMP1.GPPROB.RESOLVER = GPCOMP1.GPRESLVR.RESCODE AND GPCOMP1.GPPROB_RECL.MODIFIED_BY = GPUSER_1.USER_ID AND
GPCOMP1.GPPROB.USER_ID = GPCOMP1.GPUSER.USER_ID AND (NOT (GPCOMP1.GPPROB.PROBLEM_ID IS NULL)) AND GPCOMP1.GPPROB.STATUS = GPCOMP1.GPSTATUS.CODE AND
GPCOMP1.GPRECL.CUSTNO = GPCOMP1.ARMAST.CUSTNO AND GPCOMP1.GPRECL.INVNO = GPCOMP1.ARMAST.INVNO AND GPCOMP1.GPCUST.INACTIVE = ''N''
AND GPCOMP1.GPCOLL.TEAM IN(''' + CONVERT(VARCHAR(MAX), @Teamo, 120) + ''') AND
GPCOMP1.GPCOLL.COLLNAME IN(''' + CONVERT(VARCHAR(MAX), @PortfolioMgro, 120) + ''') AND
GPCOMP1.GPCUST.COLLTYPE IN(''' + CONVERT(VARCHAR(MAX), @CustomerTypeo, 120) + ''') AND
GPCOMP1.GPPROB.STATUS IN(''' + CONVERT(VARCHAR(MAX), @DisputeStatuso, 120) + ''') AND
GPCOMP1.GPOWNTYP.OWNTYPE IN(''' + CONVERT(VARCHAR(MAX), @OwnerCategoryo, 120) + ''') AND
GPCOMP1.GPREACAT.CATEGORY IN(''' + CONVERT(VARCHAR(MAX), @DisputeReasonCategoryo, 120) + ''') AND
GPCOMP1.GPPROB.SALESID IN(''' + CONVERT(VARCHAR(MAX), @Ownero, 120) + ''') AND
GPCOMP1.GPPCAT.PCODE IN(''' + CONVERT(VARCHAR(MAX), @DisputeReasonCodeo, 120) + ''') AND
GPCOMP1.GPPROB.SALESAREA IN(''' + CONVERT(VARCHAR(MAX), @SalesAreao, 120) + ''') '
SET @sql = N'INSERT INTO [#IssuesDetails]
SELECT * from openquery
([GETPAID], '''
+ Replace(@sql, '''', '''''') + ''')'
EXEC (@sql)
Olaf Doschke
Big misunderstanding.
The max size of the varchar(max) type is 2GB, the max length of a query is not coming from the type limit, but from the query length limitation.
I never heard of varchar(nomax) and doubt it exists.
The T-SQL side limitation is quite theoretical, 65536*network packet size. This already is lower than 2GB, as defatul packet size is 4KB, meaning it's 256MB. The driver you use might limit this further and other capacity limits are about complexity. You may find your limit here: https://msdn.microsoft.com/en-us/library/ms143432%28v=sql.100%29.aspx
--build tbl_rmcore_gprecl table from Oracle table (ar invoice table)
SET @sql = 'SELECT SUBSTR(GPCOMP1.GPRECL.CUST
GPCOMP1.GPRECL.DUEDATE as DUEDATE, GPCOMP1.GPRECL.INVDATE as INVDATE,(CASE WHEN GPCOMP1.GPRECL.FLEXDATE1 IS NOT NULL AND GPCOMP1.GPRECL.FLEXDATE1 <> ''0001/01/01'' THEN GPCOMP1.GPRECL.FLEXDATE1
ELSE SYSDATE END) as FLEXDATE1, SUBSTR(GPCOMP1.GPRECL.FLEX
GPCOMP1.ARMAST.INVDTE AS ARINVDATE, GPCOMP1.ARMAST.PNET as TERMS, SUBSTR(GPCOMP1.GPCOLL.TEAM
GPCOMP1.GPCOLL.COLLNAME AS PORTFOLIONAME, GPCOMP1.GPCUST.COLLTYPE AS CUSTTYPE, GPCOMP1.GPCUST.COMPANY as COMPANY,
GPCOMP1.GPPROB.PROBLEM_ID AS ProblemID,GPCOMP1.GPPROB.S
GPCOMP1.GPREACAT.DESCRIPTI
GPCOMP1.GPOWNTYP.OWNDESC AS OwnerCategory, GPCOMP1.GPSLPN.SALEPERSON AS Owner1, GPCOMP1.GPPROB.SALESAREA AS SalesArea,
TO_CHAR(GPCOMP1.GPPROB.DE_
GPCOMP1.GPRESLVR.RESNAME AS Resolver,
TRUNC(SYSDATE)-TRUNC(GPCOM
TRUNC(SYSDATE)-TRUNC(GPCOM
GPCOMP1.GPSTATUS.DESCRIPTI
GPCOMP1.GPCOLL.COLLCODE as PortfolioCode, SUBSTR(GPCOMP1.GPPROB.UCON
FROM GPCOMP1.GPCUST, GPCOMP1.GPRECL, GPCOMP1.GPPROB_RECL, GPCOMP1.GPPROB, GPCOMP1.GPPCAT, GPCOMP1.GPREACAT,
GPCOMP1.GPSLPN, GPCOMP1.GPOWNTYP, GPCOMP1.GPCOLL, GPCOMP1.GPRESLVR, GPCOMP1.GPUSER GPUSER_1, GPCOMP1.GPUSER,
GPCOMP1.GPSTATUS, GPCOMP1.ARMAST
WHERE GPCOMP1.GPRECL.CUSTNO = GPCOMP1.GPCUST.CUSTNO AND GPCOMP1.GPRECL.TRAN_ID = GPCOMP1.GPPROB_RECL.TRAN_I
GPCOMP1.GPPROB_RECL.PROBLE
GPCOMP1.GPPCAT.REASCAT = GPCOMP1.GPREACAT.CATEGORY AND GPCOMP1.GPPROB.SALESID = GPCOMP1.GPSLPN.SALESID AND
GPCOMP1.GPSLPN.OWNTYPE = GPCOMP1.GPOWNTYP.OWNTYPE AND GPCOMP1.GPCUST.COLLECTOR = GPCOMP1.GPCOLL.COLLCODE AND
GPCOMP1.GPPROB.RESOLVER = GPCOMP1.GPRESLVR.RESCODE AND GPCOMP1.GPPROB_RECL.MODIFI
GPCOMP1.GPPROB.USER_ID = GPCOMP1.GPUSER.USER_ID AND (NOT (GPCOMP1.GPPROB.PROBLEM_ID
GPCOMP1.GPRECL.CUSTNO = GPCOMP1.ARMAST.CUSTNO AND GPCOMP1.GPRECL.INVNO = GPCOMP1.ARMAST.INVNO AND GPCOMP1.GPCUST.INACTIVE = ''N''
AND GPCOMP1.GPCOLL.TEAM IN(''' + CONVERT(VARCHAR(MAX), @Teamo, 120) + ''') AND
GPCOMP1.GPCOLL.COLLNAME IN(''' + CONVERT(VARCHAR(MAX), @PortfolioMgro, 120) + ''') AND
GPCOMP1.GPCUST.COLLTYPE IN(''' + CONVERT(VARCHAR(MAX), @CustomerTypeo, 120) + ''') AND
GPCOMP1.GPPROB.STATUS IN(''' + CONVERT(VARCHAR(MAX), @DisputeStatuso, 120) + ''') AND
GPCOMP1.GPOWNTYP.OWNTYPE IN(''' + CONVERT(VARCHAR(MAX), @OwnerCategoryo, 120) + ''') AND
GPCOMP1.GPREACAT.CATEGORY IN(''' + CONVERT(VARCHAR(MAX), @DisputeReasonCategoryo, 120) + ''') AND
GPCOMP1.GPPROB.SALESID IN(''' + CONVERT(VARCHAR(MAX), @Ownero, 120) + ''') AND
GPCOMP1.GPPCAT.PCODE IN(''' + CONVERT(VARCHAR(MAX), @DisputeReasonCodeo, 120) + ''') AND
GPCOMP1.GPPROB.SALESAREA IN(''' + CONVERT(VARCHAR(MAX), @SalesAreao, 120) + ''') '
SET @sql = N'INSERT INTO [#IssuesDetails]
SELECT * from openquery
([GETPAID], '''
+ Replace(@sql, '''', '''''') + ''')'
EXEC (@sql)