April 28, 2016 at 7:45 am
Tried rebuilding the clustered index? I wonder if you're hitting a limit due to previous table/column changes.
April 28, 2016 at 8:12 am
Wow.. That is a reach?? But what the heck why not, I obviously don't have the answer.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 28, 2016 at 8:19 am
I'll echo what Lynn said a few posts ago.
You said you were able to reproduce this on a new table.
Posting the DDL and script you used to do that would go a long way.
Cheers!
April 28, 2016 at 9:58 am
This is just crazy.
Below is the proc and the table DDL (Which I posted earlier).
When I run the proc my PRINT statement throws this on screen. I am printing the variable that I use to update the table.
So this is the print to screen (WHICH IS EXACTLY WHAT I WANT, IT IS PERFECT)
INSERT INTO [addb20].[Target_MergeAMD2_WithData].[dbo].[ImportExportBatchData]
([DataColumn1], [DataColumn10], [DataColumn100], [DataColumn11], [DataColumn12], [DataColumn13], [DataColumn14], [DataColumn15], [DataColumn16], [DataColumn17], [DataColumn18], [DataColumn19], [DataColumn2], [DataColumn20], [DataColumn21], [DataColumn22], [DataColumn23], [DataColumn24], [DataColumn25], [DataColumn26], [DataColumn27], [DataColumn28], [DataColumn29], [DataColumn3], [DataColumn30], [DataColumn31], [DataColumn32], [DataColumn33], [DataColumn34], [DataColumn35], [DataColumn36], [DataColumn37], [DataColumn38], [DataColumn39], [DataColumn4], [DataColumn40], [DataColumn41], [DataColumn42], [DataColumn43], [DataColumn44], [DataColumn45], [DataColumn46], [DataColumn47], [DataColumn48], [DataColumn49], [DataColumn5], [DataColumn50], [DataColumn51], [DataColumn52], [DataColumn53], [DataColumn54], [DataColumn55], [DataColumn56], [DataColumn57], [DataColumn58], [DataColumn59], [DataColumn6], [DataColumn60], [DataColumn61], [DataColumn62], [DataColumn63], [DataColumn64], [DataColumn65], [DataColumn66], [DataColumn67], [DataColumn68], [DataColumn69], [DataColumn7], [DataColumn70], [DataColumn71], [DataColumn72], [DataColumn73], [DataColumn74], [DataColumn75], [DataColumn76], [DataColumn77], [DataColumn78], [DataColumn79], [DataColumn8], [DataColumn80], [DataColumn81], [DataColumn82], [DataColumn83], [DataColumn84], [DataColumn85], [DataColumn86], [DataColumn87], [DataColumn88], [DataColumn89], [DataColumn9], [DataColumn90], [DataColumn91], [DataColumn92], [DataColumn93], [DataColumn94], [DataColumn95], [DataColumn96], [DataColumn97], [DataColumn98], [DataColumn99], [ImportExportBatchGUID] ,ImportIdentity, GUIDIdentity)
SELECT st.[DataColumn1], st.[DataColumn10], st.[DataColumn100], st.[DataColumn11], st.[DataColumn12], st.[DataColumn13], st.[DataColumn14], st.[DataColumn15], st.[DataColumn16], st.[DataColumn17], st.[DataColumn18], st.[DataColumn19], st.[DataColumn2], st.[DataColumn20], st.[DataColumn21], st.[DataColumn22], st.[DataColumn23], st.[DataColumn24], st.[DataColumn25], st.[DataColumn26], st.[DataColumn27], st.[DataColumn28], st.[DataColumn29], st.[DataColumn3], st.[DataColumn30], st.[DataColumn31], st.[DataColumn32], st.[DataColumn33], st.[DataColumn34], st.[DataColumn35], st.[DataColumn36], st.[DataColumn37], st.[DataColumn38], st.[DataColumn39], st.[DataColumn4], st.[DataColumn40], st.[DataColumn41], st.[DataColumn42], st.[DataColumn43], st.[DataColumn44], st.[DataColumn45], st.[DataColumn46], st.[DataColumn47], st.[DataColumn48], st.[DataColumn49], st.[DataColumn5], st.[DataColumn50], st.[DataColumn51], st.[DataColumn52], st.[DataColumn53], st.[DataColumn54], st.[DataColumn55], st.[DataColumn56], st.[DataColumn57], st.[DataColumn58], st.[DataColumn59], st.[DataColumn6], st.[DataColumn60], st.[DataColumn61], st.[DataColumn62], st.[DataColumn63], st.[DataColumn64], st.[DataColumn65], st.[DataColumn66], st.[DataColumn67], st.[DataColumn68], st.[DataColumn69], st.[DataColumn7], st.[DataColumn70], st.[DataColumn71], st.[DataColumn72], st.[DataColumn73], st.[DataColumn74], st.[DataColumn75], st.[DataColumn76], st.[DataColumn77], st.[DataColumn78], st.[DataColumn79], st.[DataColumn8], st.[DataColumn80], st.[DataColumn81], st.[DataColumn82], st.[DataColumn83], st.[DataColumn84], st.[DataColumn85], st.[DataColumn86], st.[DataColumn87], st.[DataColumn88], st.[DataColumn89], st.[DataColumn9], st.[DataColumn90], st.[DataColumn91], st.[DataColumn92], st.[DataColumn93], st.[DataColumn94], st.[DataColumn95], st.[DataColumn96], st.[DataColumn97], st.[DataColumn98], st.[DataColumn99], FK1.DestinationGUIDVal, st.ImportExportBatchDataID, '0000'
FROM [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatchData] st
JOIN [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatch] as FK1Table on FK1Table.ImportExportBatchGUID = st.ImportExportBatchGUID
JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceGUIDVal = st.ImportExportBatchGUID
AND FK1.SourceTable = 'ImportExportBatch'
AND FK1.BatchID = 4113
NOW.. I take that variable and update the table with it. THEN I query the table and copy / paste from SSMS to my query window that field. AND Here is the contents. IF I do a LEN on the field it is exactly 4,000 characters.
And the contents: (And note at the end of this string you see "ImportExportNatchG" supposed to be "ImportExportNatchGUID", And then followed by everything else you see in the first string above.
INSERT INTO [addb20].[Target_MergeAMD2_WithData].[dbo].[ImportExportBatchData] ([DataColumn1], [DataColumn10], [DataColumn100], [DataColumn11], [DataColumn12], [DataColumn13], [DataColumn14], [DataColumn15], [DataColumn16], [DataColumn17], [DataColumn18], [DataColumn19], [DataColumn2], [DataColumn20], [DataColumn21], [DataColumn22], [DataColumn23], [DataColumn24], [DataColumn25], [DataColumn26], [DataColumn27], [DataColumn28], [DataColumn29], [DataColumn3], [DataColumn30], [DataColumn31], [DataColumn32], [DataColumn33], [DataColumn34], [DataColumn35], [DataColumn36], [DataColumn37], [DataColumn38], [DataColumn39], [DataColumn4], [DataColumn40], [DataColumn41], [DataColumn42], [DataColumn43], [DataColumn44], [DataColumn45], [DataColumn46], [DataColumn47], [DataColumn48], [DataColumn49], [DataColumn5], [DataColumn50], [DataColumn51], [DataColumn52], [DataColumn53], [DataColumn54], [DataColumn55], [DataColumn56], [DataColumn57], [DataColumn58], [DataColumn59], [DataColumn6], [DataColumn60], [DataColumn61], [DataColumn62], [DataColumn63], [DataColumn64], [DataColumn65], [DataColumn66], [DataColumn67], [DataColumn68], [DataColumn69], [DataColumn7], [DataColumn70], [DataColumn71], [DataColumn72], [DataColumn73], [DataColumn74], [DataColumn75], [DataColumn76], [DataColumn77], [DataColumn78], [DataColumn79], [DataColumn8], [DataColumn80], [DataColumn81], [DataColumn82], [DataColumn83], [DataColumn84], [DataColumn85], [DataColumn86], [DataColumn87], [DataColumn88], [DataColumn89], [DataColumn9], [DataColumn90], [DataColumn91], [DataColumn92], [DataColumn93], [DataColumn94], [DataColumn95], [DataColumn96], [DataColumn97], [DataColumn98], [DataColumn99], [ImportExportBatchGUID] ,ImportIdentity, GUIDIdentity)
SELECT st.[DataColumn1], st.[DataColumn10], st.[DataColumn100], st.[DataColumn11], st.[DataColumn12], st.[DataColumn13], st.[DataColumn14], st.[DataColumn15], st.[DataColumn16], st.[DataColumn17], st.[DataColumn18], st.[DataColumn19], st.[DataColumn2], st.[DataColumn20], st.[DataColumn21], st.[DataColumn22], st.[DataColumn23], st.[DataColumn24], st.[DataColumn25], st.[DataColumn26], st.[DataColumn27], st.[DataColumn28], st.[DataColumn29], st.[DataColumn3], st.[DataColumn30], st.[DataColumn31], st.[DataColumn32], st.[DataColumn33], st.[DataColumn34], st.[DataColumn35], st.[DataColumn36], st.[DataColumn37], st.[DataColumn38], st.[DataColumn39], st.[DataColumn4], st.[DataColumn40], st.[DataColumn41], st.[DataColumn42], st.[DataColumn43], st.[DataColumn44], st.[DataColumn45], st.[DataColumn46], st.[DataColumn47], st.[DataColumn48], st.[DataColumn49], st.[DataColumn5], st.[DataColumn50], st.[DataColumn51], st.[DataColumn52], st.[DataColumn53], st.[DataColumn54], st.[DataColumn55], st.[DataColumn56], st.[DataColumn57], st.[DataColumn58], st.[DataColumn59], st.[DataColumn6], st.[DataColumn60], st.[DataColumn61], st.[DataColumn62], st.[DataColumn63], st.[DataColumn64], st.[DataColumn65], st.[DataColumn66], st.[DataColumn67], st.[DataColumn68], st.[DataColumn69], st.[DataColumn7], st.[DataColumn70], st.[DataColumn71], st.[DataColumn72], st.[DataColumn73], st.[DataColumn74], st.[DataColumn75], st.[DataColumn76], st.[DataColumn77], st.[DataColumn78], st.[DataColumn79], st.[DataColumn8], st.[DataColumn80], st.[DataColumn81], st.[DataColumn82], st.[DataColumn83], st.[DataColumn84], st.[DataColumn85], st.[DataColumn86], st.[DataColumn87], st.[DataColumn88], st.[DataColumn89], st.[DataColumn9], st.[DataColumn90], st.[DataColumn91], st.[DataColumn92], st.[DataColumn93], st.[DataColumn94], st.[DataColumn95], st.[DataColumn96], st.[DataColumn97], st.[DataColumn98], st.[DataColumn99], FK1.DestinationGUIDVal, st.ImportExportBatchDataID, '0000'
FROM [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatchData] st
JOIN [addb20].[Source_MergeAMD1_WithData].[dbo].[ImportExportBatch] as FK1Table on FK1Table.ImportExportBatchGUID = st.ImportExportBatchG
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 28, 2016 at 9:59 am
PROC:
USE [ETL]
GO
/****** Object: StoredProcedure [dbo].[ETLProcessTableFieldsDynamic_JOIN2] Script Date: 4/28/2016 11:13:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ETLProcessTableFieldsDynamic_JOIN] (@BatchID int)
AS
--EXEC ETLProcessTableFieldsDynamic_JOIN 4045
DECLARE @TablesToProcess AS int
,@FKCount AS int
,@FKID AS int
,@FKNumber AS int
,@PKTableName AS varchar(200)
,@FKColumnName AS varchar(200)
,@PKColumnName AS varchar(200)
,@CorrelationField AS varchar(50)
,@Alias AS varchar(20)
,@SourceServerName AS varchar(100)
,@SourceDBName AS varchar(100)
,@SourceSchemaName AS varchar(50)
,@SourceFieldName AS varchar(200)
,@SourceTableName AS varchar(200)
,@DestinationServerName AS varchar(100)
,@DestinationDBName AS varchar(100)
,@DestinationSchemaName AS varchar(50)
,@DestinationFieldName AS varchar(200)
,@DestinationTableName AS varchar(200)
,@RelationshipID AS int
,@PreparedStatementID AS int
,@PreparedStatement AS varchar(max)
,@ETLMappingID AS int
,@SQL1 AS varchar(8000)
,@SQL2 AS varchar(8000);
-- SELECT * FROM FKFieldList
-- SELECT * FROM ETLPreparedStatement WHERE HasFK = 1
--Test table while building JOIN for dependent table is: [AccountingPeriodStaffingSupplier]. Has 2 FK's
DECLARE @StatementsToProcess TABLE
(PreparedStatementID int,ETLMappingID int,DestinationTable varchar(200),PreparedStatement varchar(8000),Processed bit);
DECLARE @FKFieldList TABLE
(FKID int,RelationshipID int,PKTableName varchar(200),FKColumnName varchar(100),PKColumnName varchar(200),CorrelationField varchar(50),Processed bit);
BEGIN -- Make sure environment is set
INSERT INTO @StatementsToProcess
(PreparedStatementID,ETLMappingID,DestinationTable,PreparedStatement,Processed)
SELECT PreparedStatementID,ETLMappingID,DestinationTable,PreparedStatement,0
FROM ETLPreparedStatement
WHERE HasFK = 1
--AND DependencyLevel = 1-- Comment this line in production
--AND DestinationTable = 'AccountingPeriodStaffingSupplier'-- REMOVE in production this is our test table in dev
AND JoinProcessed = 0
AND BatchID = @BatchID
ORDER BY DependencyLevel;
END
-- Get a list of Tables that have a FK that needs to be processed
BEGIN
SELECT @TablesToProcess = Count(*)
FROM @StatementsToProcess
WHERE Processed = 0;
END
PRINT ' Tables to process: '
+ Cast(@TablesToProcess AS varchar);
WHILE @TablesToProcess > 0
BEGIN
BEGIN
SELECT TOP 1 @PreparedStatementID = PreparedStatementID,@ETLMappingID = ETLMappingID,@DestinationTableName = DestinationTable,
@PreparedStatement = PreparedStatement
FROM @StatementsToProcess
WHERE Processed = 0;
END
BEGIN
INSERT INTO @FKFieldList
(FKID,RelationshipID,PKTableName,FKColumnName,PKColumnName,CorrelationField,Processed)
SELECT fkl.FKID,fkl.RelationshipID,fkl.PKTableName,fkl.FKColumnName,r.PKColumn_Name,fkl.CorrelationField,0
FROM FKFieldList fkl
JOIN Relationships AS r
ON r.RelationshipID = fkl.RelationshipID
WHERE fkl.FKTableName = @DestinationTableName;
END
BEGIN
SELECT @SourceServerName = SourceServer,@SourceDBName = SourceDB,@SourceSchemaName = SourceSchema,@SourceTableName = SourceTable
FROM ETLMapping
WHERE ETLMappingID = @ETLMappingID;
END
--EXEC ETLProcessTableFieldsDynamic_JOIN 4036
SELECT @FKCount = Count(*)
FROM @FKFieldList
WHERE Processed = 0;
SET @FKNumber = 0;
WHILE @FKCount > 0
BEGIN
BEGIN
SELECT TOP 1 @FKID = FKID,@PKTableName = PKTableName,@FKColumnName = FKColumnName,@CorrelationField = CorrelationField
FROM @FKFieldList
WHERE Processed = 0;
END
PRINT '@FKNumber: '
+ Cast(@FKNumber AS varchar);
SET @FKNumber = ( @FKNumber + 1 );
SET @Alias = 'FK' + Cast(@FKNumber AS varchar);
-- BUILD JOIN * Get the FK Table
PRINT '@FKNumber: '
+ Cast(@FKNumber AS varchar);
BEGIN
SET @SQL1 = ' JOIN [' + @SourceServerName + '].['
+ @SourceDBName + '].[' + @SourceSchemaName + '].['
+ @PKTableName + '] as ' + @Alias + 'Table on '
+ @Alias + 'Table.' + @FKColumnName + ' = st.'
+ @FKColumnName;
END
-- Build Map to Corelation
SET @SQL2 = ' JOIN [ETL].[dbo].[ETLCorrelation] '
+ @Alias + ' on ' + @Alias + '.SourceGUIDVal = st.'
+ @FKColumnName + ' AND ' + @Alias
+ '.SourceTable = ''' + @PKTableName + ''''
+ ' AND ' + @Alias + '.BatchID = '
+ Cast(@BatchID AS varchar(10));
SET @PreparedStatement = LEFT(LTRIM(RTRIM(LTRIM(RTRIM(@PreparedStatement)) + ' '
+ LTRIM(RTRIM(@SQL1)) + ' '
+ LTRIM(RTRIM(@SQL2)))), 8000) ;
SET @FKCount = ( @FKCount - 1 );
BEGIN
UPDATE @FKFieldList
SET Processed = 1
WHERE FKID = @FKID;
END
BEGIN
UPDATE ETLPreparedStatement
SET PreparedStatement = @PreparedStatement,
JoinProcessed = 1
WHERE PreparedStatementID = @PreparedStatementID;
END
CONTINUE;
END;
PRINT '@ETLMappingID: '
+ Cast(@ETLMappingID AS varchar);
PRINT '@PreparedStatement: '
+ @PreparedStatement;
DELETE FROM @FKFieldList;
SET @TablesToProcess = ( @TablesToProcess - 1 );
UPDATE @StatementsToProcess
SET Processed = 1
WHERE PreparedStatementID = @PreparedStatementID;
CONTINUE;
END;
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 28, 2016 at 10:01 am
Table DDL:
USE [ETL]
GO
/****** Object: Table [dbo].[ETLPreparedStatement] Script Date: 4/28/2016 12:00:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ETLPreparedStatement](
[PreparedStatementID] [int] IDENTITY(1,1) NOT NULL,
[ETLMappingID] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_ETLMappingID] DEFAULT ((0)),
[BatchID] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_BatchID] DEFAULT ((0)),
[DestinationDB] [varchar](200) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DestinationDB] DEFAULT ('Not Defined or NULL'),
[DestinationTable] [varchar](200) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DestinationTable] DEFAULT ('Not Defined or NULL'),
[DependencyLevel] [int] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_DependencyLevel] DEFAULT ((999)),
[PreparedStatement] [varchar](max) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_PreparedStatement] DEFAULT ('Not Defined or NULL'),
[InsertSelectStatement] [varchar](8000) NULL,
[JoinStatement] [varchar](8000) NULL,
[WhereStatement] [varchar](8000) NULL,
[StatementType] [varchar](50) NOT NULL CONSTRAINT [DF_ETLPreparedStatement_StatementType] DEFAULT ('Not Defined or NULL'),
[HasFK] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_HasFKInStatement] DEFAULT ((0)),
[NoDependentProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_NoDependentProcessed] DEFAULT ((0)),
[JoinProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_JoinProcessed] DEFAULT ((0)),
[FROMWHEREProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_FROMWHEREProcessed] DEFAULT ((0)),
[ETLMergeProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_ETLMergeProcessed] DEFAULT ((0)),
[CorrelationProcessed] [bit] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_CorrelationProcessed] DEFAULT ((0)),
[RecordDate] [datetime] NOT NULL CONSTRAINT [DF_ETLPreparedStatement_RecordDate] DEFAULT (getdate()),
CONSTRAINT [PK_ETLPreparedStatement] PRIMARY KEY CLUSTERED
(
[PreparedStatementID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 28, 2016 at 10:06 am
Jeffery, You said you recreated the problem with another table and some test code. That is what I want to see, not the code and table from the original problem. I still have not been able to recreate the problem.
April 28, 2016 at 10:33 am
It sounds like:
at least one nvarchar is working its way into some concatenation or other string operation, forcing the string to become nvarchar, and thus a max of 4000 bytes.
or maybe your SSMS options limit the output length of a SELECT'ed or PRINT'ed string to 4,000 bytes in certain cases.
If you've updated a table with the string, query the table and just select the string length, not the value:
SELECT LEN(string)
FROM updated_table_name
WHERE key_col = key_value
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 28, 2016 at 10:44 am
Oh I got rid of that, sorry. All I did was make another table with just two fields ETLMappingID and PreparedStatement varchar(max)
Then in the PROC added another block UPDATE ETELPreparedStatement2 set ETLMappingID = @ETLMappingID, PreparedStatement = @PreparedStatement
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 28, 2016 at 4:10 pm
The final variable will truncate to the shorter variables being used to build the string. you have to make all 3 a varchar(8000) for this to work.
April 28, 2016 at 4:12 pm
make sure that the variables you are concatenating with are all varchar(8000). Otherwise, it will truncate to the shorter one (the varchar(4000)) even though it is larger in size.
April 28, 2016 at 4:19 pm
Unless I'm misunderstanding your claim (quite possible), that's not true.
DECLARE @string1 varchar(3);
DECLARE @string2 varchar(2);
SET @string1='aaa';
SET @string2='bb';
SELECT LEN(@string1+@string2),@string1+@string2;
Cheers!
April 28, 2016 at 4:25 pm
Its because he is executing dynamic sql. I've only ever had this problem when executing dynamic sql with large statements. for some reason, it truncates to the shorter variable size.
April 28, 2016 at 4:31 pm
There's no execution of dynamic SQL yet.
It's the text of a TSQL statement, yes, but it's just being assigned to a variable, and then the value of a varchar(max) column for a particular row is updated with the contents of the variable.
According to what's been said, after the column is updated, PRINTing the value of the variable returns the entire string (so no truncation in the variable). Selecting the value of the varchar(max) column that just got updated using that variable, though, returns a string truncated at 4000 characters.
That's the story, but no one has been able to reproduce the problem, and no standalone scripts to reproduce the issue have been provided, so there's not much more to do at this point 🙂
Cheers!
April 28, 2016 at 8:00 pm
Not exactly.
The execution comes later in another proc.
HOWEVER the printing of the variable is just BEFORE I set the variable value to the DB. What is printed to screen is absolutely correct. What is saved to the DB is truncated. I know this not from just a print but doing a LEN(PreparedStatement) wihtin a select query.
I have a work around that I am implementing as we speak. This so far seems to work but I don't particularly like it. It does add some visibility to each step of my process; and has no significant impact on performance. That said I still do not like it and I will continue to dig.
The work around, in case anyone else runs into this is as follows.
My statement is built through the process of running several procs. One builds the insert / select. The Select get s set with aliases for the FK's in the table.
Another proc then builds the JOIN statement and yet a third builds the WHERE clause.
I am sorry but I can not go into the business rules behind this or why all of this is dynamic.
Anyway the work around is each of these steps. Rather than taking the value of the column and ADDING to it is not saved in its own column.
The proc that does the execution; rather than loading the contents of the one column will not concatenate each of said columns. The JOIN and WHERE columns I use a coalesce with my alternate value as a space, for those tables that do not have one or both of those conditions in their respective statements.
I just made my changes and outside my process read each of these into a variable and it worked fine.
Again not what I WANT but it works and allows me to move forward so it will do for the moment.
Thank you all for your efforts and contribution. When I I figure out the root cause of this I will share with the community.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply