March 5, 2009 at 8:15 am
Hi, I have a physical table from which I need to create a crosstab for a report. The way I am doing this is by using a SELECT CASE statement -
SELECT
SUM(CASE ,
SUM(CASE ,
etc.
As the number of fields can vary from client to client, The SELECT CASE statement is being built dynamically, depending on how many instances of there are in the table. This is then being assigned to the variable @sqlstring which is a varchar(8000).
When I try to exceute @sqlstring, what I am finding is that it is working for some clients but not others - I have printed @sqlstring and it is being truncated, but it is no where near its limit of 8000 characters. In fact having checked the length of this variable it is truncating at 4000 characters, and I can't see why. Any help would be appreciated. I can post the code if required.
Thanks
March 5, 2009 at 11:52 am
I think it might be caused by a convesion to NVARCHAR somewhere
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 6, 2009 at 3:49 am
Thanks JacekO for replying. However there are no nvarchar fields in the query.
However if I do SELECT @sqlstring = @sqlstring + @sqlstring, the length of @sqlstring increases to 8000.
It seems to me that dynamic SQL will not support a string more than 4000 characters, regardless of the data type. I'm going to have to figure out a way of splitting the statement across 2 variables, i.e. @sqlstring1 + @sqlstring2. Can anyone confirm this please?
March 6, 2009 at 8:33 am
How do you execute @sqlstring?
_____________
Code for TallyGenerator
March 6, 2009 at 8:54 am
exec (@sqlstring)
March 6, 2009 at 9:10 am
If you check BOL it says somewhere in there that variables used with the EXECUTE statement get reduced to 4000, what you can do it break your code up.
so for example you could use:
EXEC @SQL1 + @SQL2
I would post an example if you want but it will contain over 4000 chars he he he
just let me know
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 6, 2009 at 9:34 am
Thanks guys. That would explain why it won't execute, but why is @sqlstring not being populated up to 8000 to start with, as it is defined as a varchar (8000)?
March 6, 2009 at 9:43 am
I'm sure that the @string is being populated but it's the actualy EXECUTE statement that is doing the truncating.
If you do a len on your @string you'll see it is actually over (4000)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 6, 2009 at 9:43 am
wolfgang.birch (3/6/2009)
but why is @sqlstring not being populated up to 8000 to start with, as it is defined as a varchar (8000)?
See the first reply.
You have nvarchar somewhere.
_____________
Code for TallyGenerator
March 6, 2009 at 9:58 am
Post your select statement you use to build your string.
I think one or more of the fields you are getting your data from is defined as NVARCHAR or NCHAR and this is messing this up. You can cast them to VARCHAR in your SELECT and this should fix it.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 6, 2009 at 10:21 am
I've checked the data source, which is a physical table (tbl_11689_ShareTransactions) and there are no nvarchar fields there. Also the length of @sqlstring is 4000... I have attached the code below, and I have scripted the table tbl_11689_ShareTransactions which is at the end of the code.
Thanks.
--============
--Declarations
--============
declare @SchemeID int,
@table AS sysname, -- Table or view to Crosstab
@rownames AS varchar(128), -- Summary Key for rows
@rownamesalias AS sysname, -- Alias for grouping column
@columnnames AS varchar(128), -- Summary Key for Columns
@summarycell AS sysname, -- Data cells
@sqlstring AS varchar(8000),
@sqlstringoutput AS varchar(8000),
@keysqlstring AS varchar(1000),
@NEWLINE AS char(1), --carriage return
@key AS varchar(100)
set @schemeId = 1132
--hard code parameters for crosstab query below
set @table = 'tbl_11689_ShareTransactions'
set @rownames = 'Participant_ID'
set @rownamesalias = 'ParticipantId'
set @columnnames = 'iddesc'
set @summarycell = 'Shares'
SET @NEWLINE = CHAR(10)
--===============
--get source data
--===============
truncate table tbl_11689_ShareTransactions
insert into tbl_11689_ShareTransactions
SELECT BM.SchemeFk, BM.Participant_Id, B.BatchTypeId, BM.TransactionTypeId, SUM(BM.dblAmount)'Shares',
rtrim(bt.[Name]) + ':- ' + rtrim(ct.[Name]) + ' - ' + ST.[Description] as 'IDDesc'
FROM ctltblBenefitMovements BM INNER JOIN
ctltblBatch B ON BM.BatchId = B.BatchId INNER JOIN
ctltblManualBatchTypes bt on b.batchtypeid = bt.ManualBatchTypeID INNER JOIN
ctltblBenefitTransactionType ct on bm.transactiontypeid = ct.transactiontypeid inner join
ctltblsharetypes st on bm.sharetypeid = st.sharetypeid
WHERE (BM.SchemeFk = @SchemeID)
GROUP BY BM.SchemeFk, BM.Participant_Id, B.BatchTypeId, BM.TransactionTypeId,BM.dblAmount,
rtrim(bt.[Name]) + ':- ' + rtrim(ct.[Name]) + ' - ' + ST.[Description]
--============
--build the string
--cross tabulate the output from tbl_11689_ShareTransactions by building a dynamic case statement, based upon the number of separate
--transaction types in the temp table above
--============
SET @sqlstring =
'SELECT' + @NEWLINE +
' ' + @rownames + CASE
WHEN @rownamesalias IS NOT NULL THEN ' AS ' + @rownamesalias
ELSE ''
END
CREATE TABLE #keytable(keyvalue varchar(100) NOT NULL PRIMARY KEY)
--get individual transaction types
SET @keysqlstring =
'INSERT INTO #keytable ' +
'SELECT DISTINCT CAST(' + @columnnames + ' AS varchar(100)) ' +
'FROM ' + @table
EXEC (@keysqlstring)
SELECT @key = MIN(keyvalue) FROM #keytable
/* Complete the master sql string by looping through the temporary key table */
WHILE @key IS NOT NULL BEGIN
SET @sqlstring = @sqlstring + ',' + @NEWLINE +
' SUM(CASE CAST(' + @columnnames +
' AS varchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @summarycell IS NULL THEN '1'
ELSE @summarycell
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + char(39) + @key + char(39)
SELECT @key = MIN(keyvalue) FROM #keytable
WHERE keyvalue > @key
END
SET @sqlstring = @sqlstring + @NEWLINE +
'INTO tbl_11689_Crosstab' + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @rownames + @NEWLINE +
'ORDER BY ' + @rownames
PRINT @sqlstring
select len(@sqlstring) --= 4000? How can this be when @sqlstring is defined as varchar(8000)?
--EXEC (@sqlstring) --execute the statement and copy results to a second physical table
drop table #keytable
--create table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_11689_ShareTransactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_11689_ShareTransactions]
GO
CREATE TABLE [dbo].[tbl_11689_ShareTransactions] (
[SchemeFk] [int] NULL ,
[Participant_Id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BatchTypeId] [smallint] NULL ,
[TransactionTypeId] [int] NULL ,
[Shares] [decimal](38, 9) NULL ,
[IDDesc] [varchar] (156) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [Secondary]
GO
March 6, 2009 at 10:25 am
I have checked the data source (tbl_11689_ShareTransactions) and there are no NVARCHAR fields... also, the length of @sqlstring is 4000. The code is below, I have also scripted the source table FYI. Thanks.
--============
--Declarations
--============
--exec rpt11689_Shares_Report_Admin '1132'
declare @SchemeID int,
@table AS sysname, -- Table or view to Crosstab
@rownames AS varchar(128), -- Summary Key for rows
@rownamesalias AS sysname, -- Alias for grouping column
@columnnames AS varchar(128), -- Summary Key for Columns
@summarycell AS sysname, -- Data cells
@sqlstring AS varchar(8000),
@sqlstringoutput AS varchar(8000),
@keysqlstring AS varchar(1000),
@NEWLINE AS char(1), --carriage return
@key AS varchar(100)
set @schemeId = 1132
--hard code parameters for crosstab query below
set @table = 'tbl_11689_ShareTransactions'
set @rownames = 'Participant_ID'
set @rownamesalias = 'ParticipantId'
set @columnnames = 'iddesc'
set @summarycell = 'Shares'
SET @NEWLINE = CHAR(10)
--===============
--get source data
--===============
truncate table tbl_11689_ShareTransactions
insert into tbl_11689_ShareTransactions
SELECT BM.SchemeFk, BM.Participant_Id, B.BatchTypeId, BM.TransactionTypeId, SUM(BM.dblAmount)'Shares',
rtrim(bt.[Name]) + ':- ' + rtrim(ct.[Name]) + ' - ' + ST.[Description] as 'IDDesc'
FROM ctltblBenefitMovements BM INNER JOIN
ctltblBatch B ON BM.BatchId = B.BatchId INNER JOIN
ctltblManualBatchTypes bt on b.batchtypeid = bt.ManualBatchTypeID INNER JOIN
ctltblBenefitTransactionType ct on bm.transactiontypeid = ct.transactiontypeid inner join
ctltblsharetypes st on bm.sharetypeid = st.sharetypeid
WHERE (BM.SchemeFk = @SchemeID)
GROUP BY BM.SchemeFk, BM.Participant_Id, B.BatchTypeId, BM.TransactionTypeId,BM.dblAmount,
rtrim(bt.[Name]) + ':- ' + rtrim(ct.[Name]) + ' - ' + ST.[Description]
--============
--build the string
--cross tabulate the output from tbl_11689_ShareTransactions by building a dynamic case statement, based upon the number of separate
--transaction types in the temp table above
--============
SET @sqlstring =
'SELECT' + @NEWLINE +
' ' + @rownames + CASE
WHEN @rownamesalias IS NOT NULL THEN ' AS ' + @rownamesalias
ELSE ''
END
CREATE TABLE #keytable(keyvalue varchar(100) NOT NULL PRIMARY KEY)
--get individual transaction types
SET @keysqlstring =
'INSERT INTO #keytable ' +
'SELECT DISTINCT CAST(' + @columnnames + ' AS varchar(100)) ' +
'FROM ' + @table
EXEC (@keysqlstring)
SELECT @key = MIN(keyvalue) FROM #keytable
/* Complete the master sql string by looping through the temporary key table */
WHILE @key IS NOT NULL BEGIN
SET @sqlstring = @sqlstring + ',' + @NEWLINE +
' SUM(CASE CAST(' + @columnnames +
' AS varchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @summarycell IS NULL THEN '1'
ELSE @summarycell
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + char(39) + @key + char(39)
SELECT @key = MIN(keyvalue) FROM #keytable
WHERE keyvalue > @key
END
SET @sqlstring = @sqlstring + @NEWLINE +
'INTO tbl_11689_Crosstab' + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @rownames + @NEWLINE +
'ORDER BY ' + @rownames
PRINT @sqlstring
select len(@sqlstring) --= 4000? How can this be when @sqlstring is defined as varchar(8000)?
--EXEC (@sqlstring) --execute the statement and copy results to a second physical table
drop table #keytable
--create table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_11689_ShareTransactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_11689_ShareTransactions]
GO
CREATE TABLE [dbo].[tbl_11689_ShareTransactions] (
[SchemeFk] [int] NULL ,
[Participant_Id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BatchTypeId] [smallint] NULL ,
[TransactionTypeId] [int] NULL ,
[Shares] [decimal](38, 9) NULL ,
[IDDesc] [varchar] (156) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [Secondary]
GO
March 6, 2009 at 10:31 am
ok I found a reason.
You are concatenating Nvarchar values to your string which will change the type of @String.
Here is a snipet example from your code
WHILE @key IS NOT NULL BEGIN
SET @sqlstring = @sqlstring + ',' + @NEWLINE +
' SUM(CASE CAST(' + @columnnames +
' AS varchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
You see the N'' in the where clause that is saying NVARCHAR basically...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 9, 2009 at 4:47 am
Thanks for looking, I appreciate it, however I have tried removing the 'N' but it's still happening - the code for building the string now looks like this.
SET @sqlstring =
'SELECT' + @NEWLINE +
' ' + @rownames + CASE
WHEN @rownamesalias IS NOT NULL THEN ' AS ' + @rownamesalias
ELSE ''
END
CREATE TABLE #keytable(keyvalue varchar(100) NOT NULL PRIMARY KEY)
SET @keysqlstring =
'INSERT INTO #keytable ' +
'SELECT DISTINCT CAST(' + @columnnames + ' AS varchar(100)) ' +
'FROM ' + @table
EXEC (@keysqlstring)
SELECT @key = MIN(keyvalue) FROM #keytable
/* Complete the master sql string by looping through the temporary key table */
WHILE @key IS NOT NULL BEGIN
SET @sqlstring = @sqlstring + ',' + @NEWLINE +
/* Some manipulation of the following CASE statement can make this technique work for filling the summary cells with MAX, MIN values, Average, etc */
' SUM(CASE CAST(' + @columnnames +
' AS varchar(100))' + @NEWLINE +
' WHEN ''' + @key +
''' THEN ' + CASE
WHEN @summarycell IS NULL THEN '1'
ELSE @summarycell
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS ' + char(39) + @key + char(39)
SELECT @key = MIN(keyvalue) FROM #keytable
WHERE keyvalue > @key
END
SET @sqlstring = @sqlstring + @NEWLINE +
'INTO tbl_11689_Crosstab' + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @rownames + @NEWLINE +
'ORDER BY ' + @rownames
March 9, 2009 at 5:53 pm
You use "sysname" datatype for some strings (@rownamesalias, for example).
Sysname is actually nvarchar(128)
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply