March 20, 2014 at 2:00 am
I am using SQL 2008 R2
I have declared
Declare @sqlquery Varchar(max)
Set @sqlquery = = (' A VERY BIG SELECT STATEMENT WITH 5000 COLUMNS in WHERE CLAUSE')as varchar(max))
Exec(@Query)
When I execute this , it gives me the message that varchar is greater than 8000 and does not allow me.
What is the solution to this. I cannot truncate the text used in the query.
Please help.
March 20, 2014 at 2:33 am
A varchar(max) can store up to 2 billion characters.
Can you post your complete code? I suspect the part you've obfuscated contains the problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2014 at 3:19 am
Can you post the complete code please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2014 at 3:36 am
ALTER PROCEDURE [dbo].[GetMissingValues]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET FMTONLY OFF
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @SQLQuery1 Varchar(max)
Declare @SQLQuery2 Varchar(max)
Declare @SQLQuery3 Varchar(max)
Declare @SQLQuery4 Varchar(max)
Declare @SQLQuery5 Varchar(max)
Declare @SQLQuery6 Varchar(max)
Declare @SQLQuery7 Varchar(max)
Declare @SQLQuery8 Varchar(max)
Set @SQLQuery1
=
CAST(' Select
COL1,
COL2,
COL3
FROM OPENQUERY(LINKED_SERVER_TO_ORA,
''COL1, COL2, COL3
from SCHEMA1.TABLE2 WHERE ID IN (' as varchar(max))
-- Now the IN CLAUSE I AM JOINING HERE with the VALUES REQUIRED
SET @SQLQuery2 = CAST (' ' as varchar(max))
SET @SQLQuery3 = CAST('
''''5417773'''',
''''5415437'''',
''''5415463'''',
''''5416222'''',
''''5416298'''',
''''5417276'''',
''''5417309'''',
''''5417318'''',
''''5417366'''',
''''5417543'''',
''''5417555'''',
''''5417571'''',
''''5417573'''',
''''5417578'''',
''''5417579'''',
''''5417656'''',
''''5417667'''',
''''5417669'''',
''''5417671'''',
''''5417689'''',
''''5421236'''',
''''5421255'''',
''''5421296'''',
''''5421316'''',
''''5421341'''',
''''5421355'''',
''''5421360'''',
''''5419702'''',
''''5419721'''',
''''5419743'''',
''''5419748'''',
''''5419762'''',
''''5419820'''',
''''5415940'''',
''''5415975'''',
''''5415980'''',
''''5416026'''',
''''5420725'''',
''''5420750'''',
''''5420757'''',
''''5420759'''',
''''5420767'''',
''''5420768'''',
' as varchar(max))
----------------------------------------------------------------------------------------------------
SET @SQLQuery4 = CAST('
''''5420776'''',
''''5420785'''',
''''5420805'''',
''''5421097'''',
''''5421185'''',
''''5421190'''',
''''5416557'''',
''''5416561'''',
''''5416568'''',
''''5416599'''',
''''5416627'''',
''''5415558'''',
''''5415567'''',
''''5415575'''',
''''5415581'''',
''''5415582'''',
''''5415586'''',
''''5415609'''',
''''5415623'''',
''''5415633'''',
''''5415638'''',
''''5416936'''',
''''5416949'''',
''''5416972'''',
''''5417026'''',
''''5417047'''',
''''5418412'''',
''''5418422'''',
''''5418423'''',
''''5418432'''',
''''5418438'''',
''''5418455'''',
''''5418475'''',
''''5418487'''',
''''5418497'''',
''''5418512'''',
''''5416733'''',
''''5416742'''',
''''5416766'''',
''''5416814'''',
''''5416819'''',
''''5416822'''',
''''5416849'''',
''''5415657'''',
''''5415666'''',
''''5415703'''',
''''5415711'''',
''''5415717'''',
''''5415748'''',
''''5415775'''',
''''5418878'''',
''''5418949'''',
''''5418955'''',
''''5419027'''',
''''5420449'''',
''''5420451'''',
''''5420501'''',
''''5420532'''',
''''5420533'''',
''''5420559'''',
''''5415310'''',
''''5415351'''',
''''5416380'''',
''''5416407'''',
''''5416460'''',
''''5416493'''',
''''5417971'''',
''''5418007'''',
''''5418012'''',
''''5415780'''',
''''5415801'''',
''''5415829'''',
''''5415855'''',
''''5419236'''',
''''5419264'''',
''''5419269'''',
''''5419299'''',
''''5419305'''',
''''5419309'''',
''''5419365'''',
''''5420567'''',
''''5420594'''',
''''5420652'''',
''''5420667'''',
''''5417701'''',
''''5417746'''',
''''5417810'''',
''''5420838'''',
''''5420883'''',
''''5420889'''',
''''5420899'''',
''''5420909'''',
''''5420929'''',
''''5418082'''',
''''5418109'''',
''''5418121'''',
''''5418139'''',
''''5418164'''',
''''5418178'''',
''''5419036'''',
''''5419046'''',
''''5419071'''',
''''5419077'''',
''''5419164'''',
''''5419166'''',
''''5420182'''',
''''5420252'''',
''''5420253'''',
''''5420264'''',
''''5420266'''',
''''5420277'''',
''''5419441'''',
''''5419470'''',
''''5419499'''',
''''5419500'''',
''''5419505'''',
''''5418609'''',
''''5418628'''',
''''5418647'''',
''''5418670'''',
''''5418693'''',
''''5419849'''',
''''5419879'''',
''''5419945'''',
''''5419962'''',
''''5419659'''',
''''5454772'''',
''''5418214'''',
''''5418219'''',
''''5418227'''',
''''5418258'''',
''''5418272'''',
''''5418279'''',
''''5418331'''',
''''5418336'''',
''''5416069'''',
''''5416091'''',
''''5416154'''',
''''5416173'''',
''''5417103'''',
''''5417211'''',
''''5417455'''',
''''5417496'''',
''''5417502'''',
''''5420355'''',
''''5420356'''',
''''5419978'''',
''''5419990'''',
''''5420004'''',
''''5420033'''',
''''5420059'''',
''''5420087'''',
''''5418706'''',
''''5418763'''',
''''5418810'''',
''''5418839'''',
''''5418841'''',
''''5420978'''',
''''5420990'''',
''''5420997'''',
''''5421017'''',
''''5421028'''',
''''4268257'''',
''''5416262'''',
''''5417612'''',
''''5417661'''',
''''5421326'''',
''''5420799'''',
''''5416633'''',
''''5415551'''',
''''5415553'''',
''''5417027'''',
''''5418903'''',
''''5420509'''',
''''5420546'''',
''''5417870'''',
''''5417907'''',
''''5417989'''',
''''5415887'''',
''''5419202'''',
''''5417704'''',
''''5417753'''',
''''5420870'''',
''''5420873'''',
''''5419054'''',
''''5419093'''',
''''5420132'''',
''''5420260'''',
''''5419419'''',
''''5419528'''',
''''5418651'''',
''''5419844'''',
''''5419928'''',
''''5419931'''',
''''5454781'''',
''''5418341'''',
''''5416097'''',
''''5417433'''',
''''5417499'''',
''''5420287'''',
''''5420330'''',
''''5420333'''',
''''5420996'''',
''''5418935'''',
''''5419664'''',
''''5416309'''',
''''5419521'''',
''''5417791'''',
''''5420911'''',
''''5415414'''',
''''5415462'''',
''''5416286'''',
''''5416334'''',
''''5417246'''',
''''5417334'''',
''''5416027'''',
''''5416046'''',
''''5420806'''',
''''5416547'''',
''''5415559'''',
''''5415595'''',
''''5415600'''',
''''5416994'''',
''''5418407'''',
''''5418466'''',
''''5418516'''',
''''5418521'''',
''''5418522'''',
''''5418529'''',
''''5416816'''',
''''5416834'''',
''''5416848'''',
''''5415708'''',
''''5415725'''',
''''5415761'''',
''''5418986'''',
''''5420551'''',
''''5415364'''',
''''5416426'''',
''''5416430'''',
''''5416437'''',
''''5416452'''',
''''5416525'''',
''''5417960'''',
''''5415850'''',
''''5415867'''',
''''5419228'''',
''''5419287'''',
''''5419292'''',
''''5417707'''',
''''5420949'''',
''''5419069'''',
''''5419133'''',
''''5419902'''',
''''5418244'''',
''''5418300'''',
''''5418358'''',
''''5416127'''',
''''5416135'''',
''''5416162'''',
''''5417146'''',
''''5417185''''
' as varchar(max))
SET @SQLQuery5 = CAST ('
''''5417773'''',''''5415437'''',''''5415463'''',''''5416222'''',''''5416298'''',''''5417276'''',''''5417309'''',''''5417318'''',''''5417366'''',''''5417543'''',''''5417555'''',''''5417571'''',''''5417573'''',''''5417578'''',''''5417579'''',''''5417656'''',''''5417667'''',''''5417669'''',''''5417671'''',''''5417689'''',''''5421236'''',''''5421255'''',''''5421296'''',''''5421316'''',''''5421341'''',''''5421355'''',''''5421360'''',''''5419702''''
' as varchar(max))
SET @SQLQuery6 = CAST(' ) '')' as varchar(max))
--SELECT @sqlquery = @SQLQuery1 + @SQLQuery2
EXEC(@SQLQuery1+@SQLQuery2+@SQLQuery3+@SQLQuery4+@SQLQuery5+@SQLQuery6+
@SQLQuery7+@SQLQuery8)
--PRINT @sqlquery
END
GO
-- NOW CALLING THE STORED PROCEDURE
EXEC [GetMissingValues]
March 20, 2014 at 12:23 pm
Given that what you're trying to do is conceptually very simple, you could do this really easily if you created a table in TempDB, indexed it and added your value list to it. Then querying this is super easy:
CREATE TABLE #tmpPatients(
PatientID INT PRIMARY KEY);
GO
INSERT INTO #tmpPatients(PatientID)
SELECT TOP 80 PatientID
FROM SCRIDB.dbo.Patient
ORDER BY NEWID();
SELECT xp.PatientID
FROM SCRIDB.dbo.Patient xp
LEFT JOIN #tmpPatients tmp ON xp.PatientID = tmp.PatientID
WHERE tmp.PatientID IS NULL;
Then you don't have to write any dynamic SQL at all, and can just drop the temporary table when you're done with it.
March 20, 2014 at 12:36 pm
Given that what you're trying to do is conceptually very simple, you could do this really easily if you created a table in TempDB, indexed it and added your value list to it. Then querying this is super easy:
CREATE TABLE #tmpPatients(
PatientID INT PRIMARY KEY);
GO
INSERT INTO #tmpPatients(PatientID)
SELECT TOP 80 PatientID
FROM SCRIDB.dbo.Patient
ORDER BY NEWID();
SELECT xp.PatientID
FROM SCRIDB.dbo.Patient xp
LEFT JOIN #tmpPatients tmp ON xp.PatientID = tmp.PatientID
WHERE tmp.PatientID IS NULL;
Then you don't have to write any dynamic SQL at all, and can just drop the temporary table when you're done with it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply