declare @a varchar(max);

set @a = replicate(cast('a' as varchar(max)),1000000);
select len(@a)

SET @SecuritySQLCMD = CASE COALESCE(@UserName, 0) WHEN 0 THEN '-E' ELSE '-U' + @UserName + ' -P' + @Password END
SET @SecurityBCP	= CASE COALESCE(@UserName, 0) WHEN 0 THEN '-T' ELSE '-U' + @UserName + ' -P' + @Password END

-- **************************************************************************************************
-- CREATE BAT Script
-- **************************************************************************************************
SET @NewLine = '+CHAR(13) + CHAR(10)'

--Create SQLCMD Scripts
SET @SQLCMD1 = '''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "IF OBJECT_ID(''''dbo.NewStoreMapping'''',''''u'''')IS NOT NULL BEGIN DROP TABLE dbo.NewStoreMapping END;"'''
SET @SQLCMD2 = '''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "CREATE TABLE dbo.NewStoreMapping (ID INT IDENTITY, StoreNo INT, NewStoreNo INT);"'''
SET @SQLCMD3 = '''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "INSERT INTO dbo.NewStoreMapping(StoreNo,NewStoreNo)SELECT DISTINCT StoreNo, '+@MaxStoreNo+' FROM dbo.CRDM_Header;"'''
SET @SQLCMD4 = '''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "UPDATE dbo.NewStoreMapping SET NewStoreNo = NewStoreNo+ID;"'''

-- Create BCP Export OUT Scripts
SET @BCP1 = '''bcp "SELECT t1.MAXTransactionID+t2.'
SET @BCP2 = ' FROM  '+@DatabaseName+'.'
SET @BCP3 = ' t2 INNER JOIN '+@DatabaseName+'.dbo.NewStoreMapping store ON store.StoreNo = t2.StoreNo, (SELECT MAX(TransactionID) AS MaxTransactionID FROM '+@DatabaseName+'.dbo.CRDM_Header)AS t1" queryout '+@FilePath
SET @BCP4 = '.dat '+@SecurityBCP+' '+@BCPMode+' -b'+@BatchSize+' -S'+@ServerName+''''

-- Create BCP Import IN Scripts
SET @BCP5 = '''bcp '+@DatabaseName+'.'
SET @BCP6 = ' in '+@FilePath
SET @BCP7 = '.dat '+@SecurityBCP+' '+@BCPMode+' -E'''


-- DROPS & CREATES the dbo.NewStoreMapping table
SET @BATScript = 'SELECT ''@ECHO OFF'''
				+@NewLine+'+'+@SQLCMD1
				+@NewLine+'+'+@SQLCMD2
				+@NewLine+'+'+@SQLCMD3
				+@NewLine+'+'+@SQLCMD4+@NewLine+@NewLine

WHILE @Count <= @MaxNumberOfIterations
BEGIN
	-- Creates BCP Export OUT scripts for all tables
	SELECT @BATScript = @BATScript+'+'+@BCP1+ColumnList+@BCP2+TableName+@BCP3+TableName+@BCP4+@NewLine+''
	FROM #TableNames
	WHERE ID = @Count

	-- Create BCP Import In scripts for all tables
	SELECT @BATScript = @BATScript+'+'+@BCP5+TableName+ @BCP6+TableName+@BCP7+''+@NewLine+'+'+@NewLine
	FROM #TableNames
	WHERE ID = @Count

	SET @Count = @Count + 1
END

--SET @BATScript = @BATScript
SET @BATScript = @BATScript+@NewLine+@NewLine+'+'+'''PAUSE'''

--SELECT @BATScript
EXEC (@BATScript)

ALTER PROCEDURE dbo.CreateBATScriptForStoreExtrapolation
(
  @ServerName	VARCHAR(MAX)
, @DatabaseName	VARCHAR(MAX)
, @FilePath		VARCHAR(MAX)
, @UserName		VARCHAR(MAX)
, @Password		VARCHAR(MAX)
)
AS
SET NOCOUNT ON

/*
DESIRED OUTPUT:
BCP OUT - bcp "SELECT h1.MaxTransactionID+a.TransactionID, RecordSeqNo, InsertedDateTime, EndTransDateTime, store.NewStoreNo AS StoreNo, POSNo, TicketNo, TradingDay, CashierNo, AccountNumber, PaymentAmount FROM sainsburys.dbo.CRDM_AccountPayment a INNER JOIN sainsburys.dbo.NewStoreMapping store ON store.StoreNo = a.StoreNo, (SELECT MAX(TransactionID) AS MaxTransactionID FROM sainsburys.dbo.CRDM_Header)AS h1" queryout D:\AccountPayment_YYYYMMDD.dat -T -c -b10000 -SWS23
BCP IN  - bcp sainsburys.dbo.CRDM_AccountPayment in D:\AccountPayment.dat -T -c -E
*/

DECLARE   @SchemaName				VARCHAR(50)
		, @TemplateSchemaName		VARCHAR(50)
		, @Count					INT
		, @MaxNumberOfIterations	INT
		, @BATScript				VARCHAR(MAX)
		, @ColumnCounter			INT
		, @TemplateObjectID			INT
		, @MaxNumberOfColumns		INT
		, @SQLColumnList			VARCHAR(MAX)
		, @SecuritySQLCMD			VARCHAR(MAX)
		, @SecurityBCP				VARCHAR(MAX)
		, @MaxStoreNo				VARCHAR(MAX)
		, @SQLCMD1					VARCHAR(MAX)
		, @SQLCMD2					VARCHAR(MAX)
		, @SQLCMD3					VARCHAR(MAX)
		, @SQLCMD4					VARCHAR(MAX)
		, @BCP1						VARCHAR(MAX)
		, @BCP2						VARCHAR(MAX)
		, @BCP3						VARCHAR(MAX)
		, @BCP4						VARCHAR(MAX)
		, @BCP5						VARCHAR(MAX)
		, @BCP6						VARCHAR(MAX)
		, @BCP7						VARCHAR(MAX)
		, @BCPMode					VARCHAR(MAX)
		, @BatchSize				VARCHAR(MAX)
		, @NewLine					VARCHAR(MAX)



SET @SchemaName = 'crdm'
SET @TemplateSchemaName = 'template'
SET @BCPMode = CAST('-c' AS VARCHAR(MAX))
SET @BatchSize = 10000 -- Batch commit size
SET @FilePath = CAST('D:\' AS VARCHAR(MAX))

--DROP TABLE #TableNames
CREATE TABLE #TableNames (	  ID				INT IDENTITY
							, TableName			VARCHAR(100)
							, TableID			INT
							, HasIdentity		BIT
							, ColumnList		VARCHAR(MAX)
							)

--DROP TABLE #ColumnList
CREATE TABLE #ColumnList (	  ID				INT IDENTITY
							, TemplateObjectID	INT
							, ColumnList		VARCHAR(MAX)
							)

-- Create the column list for each table template and save in the columnlist table
-- This prevents the same column list being created for every table in each separate
-- partition (so improves performance)
INSERT #ColumnList (TemplateObjectID)
SELECT TemplateObjectID
FROM admin.GetTableTemplates (NULL, 'template')

-- Obtain the number of iterations required to get all template table column lists
SELECT @MaxNumberOfIterations = MAX(ID), @Count = 1 FROM #ColumnList

WHILE @Count <= @MaxNumberOfIterations
	BEGIN
			SELECT @TemplateObjectID = TemplateObjectID
			FROM #ColumnList WHERE ID = @Count

			SELECT @MaxNumberOfColumns = MAX(ColumnID), @ColumnCounter = 1 
			FROM admin.GetTableSchema (@TemplateObjectID)

			-- Create the column list for the SELECT statement
			WHILE @MaxNumberOfColumns >= @ColumnCounter
				BEGIN

					SELECT	@SQLColumnList = CASE ColumnID WHEN 1 
												THEN ColumnName -- If this is the 1st column, include the SELECT
												ELSE -- Append all other columns
													 CASE ColumnName WHEN 'StoreNo' 
														THEN @SQLColumnList+', '+ 'store.NewStoreNo' -- Replace storeNo with new mapping
														ELSE @SQLColumnList+', '+ ColumnName
													 END
											 END
					FROM	admin.GetTableSchema (@TemplateObjectID) 
					WHERE	ColumnID = @ColumnCounter

					SET @ColumnCounter = @ColumnCounter+1
				END	

			-- Save the columnList in the table
			UPDATE #ColumnList
			SET ColumnList = @SQLColumnList
			WHERE ID = @Count

			SET @Count = @Count + 1
		END

INSERT INTO #TableNames (TableName, HasIdentity, ColumnList)
SELECT		  tbls.TableSchema+'.'+tbls.TableName
			, tbls.HasIdentity
			, CAST(schm.ColumnList AS VARCHAR(MAX))
FROM		admin.GetTableNames(NULL,@SchemaName,@TemplateSchemaName)tbls
INNER JOIN	admin.GetTableNestedLevels (NULL, 'template') lvls 
		ON	lvls.ChildObjectID = tbls.TemplateObjectID
INNER JOIN  #ColumnList schm 
		ON  schm.TemplateObjectID = tbls.TemplateObjectID
ORDER BY	tbls.PartitionDate, lvls.NestedLevel,  tbls.TableObjectID

SELECT @MaxNumberOfIterations = MAX(ID), @Count = 1 FROM #TableNames
SELECT @MaxStoreNo = CAST(MAX(StoreNo)AS VARCHAR(MAX)) FROM dbo.CRDM_Header

-- Will the file be run from a TRUSTED (-T for BCP, -E for SQLCMD) connection or not (TRUSTED refers 
-- to a network account that has the required access to the servers/database with the required 
-- permissions - READ/WRITE)
SET @SecuritySQLCMD = CAST(CASE COALESCE(@UserName, 0) WHEN 0 THEN '-E' ELSE '-U' + @UserName + ' -P' + @Password END AS VARCHAR(MAX))
SET @SecurityBCP	= CAST(CASE COALESCE(@UserName, 0) WHEN 0 THEN '-T' ELSE '-U' + @UserName + ' -P' + @Password END AS VARCHAR(MAX))

-- **************************************************************************************************
-- CREATE BAT Script
-- **************************************************************************************************
SET @NewLine = CAST('+CHAR(13) + CHAR(10)' AS VARCHAR(MAX))

--Create SQLCMD Scripts
SET @SQLCMD1 = CAST('''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "IF OBJECT_ID(''''dbo.NewStoreMapping'''',''''u'''')IS NOT NULL BEGIN DROP TABLE dbo.NewStoreMapping END;"''' AS VARCHAR(MAX))
SET @SQLCMD2 = CAST('''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "CREATE TABLE dbo.NewStoreMapping (ID INT IDENTITY, StoreNo INT, NewStoreNo INT);"''' AS VARCHAR(MAX))
SET @SQLCMD3 = CAST('''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "INSERT INTO dbo.NewStoreMapping(StoreNo,NewStoreNo)SELECT DISTINCT StoreNo, '+@MaxStoreNo+' FROM dbo.CRDM_Header;"''' AS VARCHAR(MAX))
SET @SQLCMD4 = CAST('''sqlcmd -S '+@ServerName+' '+@SecuritySQLCMD+' -d '+@DatabaseName+' -Q "UPDATE dbo.NewStoreMapping SET NewStoreNo = NewStoreNo+ID;"''' AS VARCHAR(MAX))

-- Create BCP Export OUT Scripts
SET @BCP1 = CAST('''bcp "SELECT t1.MAXTransactionID+t2.' AS VARCHAR(MAX))
SET @BCP2 = CAST(' FROM  '+@DatabaseName+'.' AS VARCHAR(MAX))
SET @BCP3 = CAST(' t2 INNER JOIN '+@DatabaseName+'.dbo.NewStoreMapping store ON store.StoreNo = t2.StoreNo, (SELECT MAX(TransactionID) AS MaxTransactionID FROM '+@DatabaseName+'.dbo.CRDM_Header)AS t1" queryout '+@FilePath AS VARCHAR(MAX))
SET @BCP4 = CAST('.dat '+@SecurityBCP+' '+@BCPMode+' -b'+@BatchSize+' -S'+@ServerName+'''' AS VARCHAR(MAX))

-- Create BCP Import IN Scripts
SET @BCP5 = CAST('''bcp '+@DatabaseName+'.' AS VARCHAR(MAX))
SET @BCP6 = CAST(' in '+@FilePath AS VARCHAR(MAX))
SET @BCP7 = CAST('.dat '+@SecurityBCP+' '+@BCPMode+' -E''' AS VARCHAR(MAX))


-- DROPS & CREATES the dbo.NewStoreMapping table
SET @BATScript = CAST('SELECT ''@ECHO OFF''' 
				+@NewLine+'+'+@SQLCMD1 
				+@NewLine+'+'+@SQLCMD2 
				+@NewLine+'+'+@SQLCMD3
				+@NewLine+'+'+@SQLCMD4+@NewLine+@NewLine AS VARCHAR(MAX))

WHILE @Count <= @MaxNumberOfIterations
BEGIN
	-- Creates BCP Export OUT scripts for all tables
	SELECT @BATScript = CAST(@BATScript+'+'+@BCP1+ColumnList+@BCP2+TableName+@BCP3+TableName+@BCP4+@NewLine+''AS VARCHAR(MAX))
	FROM #TableNames
	WHERE ID = @Count

	-- Create BCP Import In scripts for all tables
	SELECT @BATScript = CAST(@BATScript+'+'+@BCP5+TableName+ @BCP6+TableName+@BCP7+''+@NewLine+'+'+@NewLine AS VARCHAR(MAX))
	FROM #TableNames
	WHERE ID = @Count

	SET @Count = @Count + 1
END

--SET @BATScript = @BATScript
SET @BATScript = CAST(@BATScript+@NewLine+@NewLine+'+'+'''PAUSE'''AS VARCHAR(MAX))

--SELECT @BATScript
EXEC (@BATScript)

IF EXISTS (SELECT * from dbo.sysobjects WHERE  id = object_id(N'[dbo].[fn_Split]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		[position] INT IDENTITY PRIMARY KEY
		, [Text] VARCHAR(MAX)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO

-- Example of use
DECLARE @foo VARCHAR(MAX)

SET @foo = 'THIS IS A BLOCK OF TEXT
WITH LINE BREAKS IN IT
REGULAR WINDOWS CHAR(13)+CHAR(10)
'

SELECT @foo = REPLICATE(@foo, 1000)

SELECT LEN(@foo)
SELECT @foo

SELECT * FROM fn_Split(@foo, CHAR(13)+CHAR(10)) ORDER BY [position] ASC

ALTER PROC Test
AS

EXEC ('SELECT REPLICATE(''A'',9000)')
--EXEC ('SELECT REPLICATE(CAST(''A''AS VARCHAR(MAX)),9000)')
--EXEC ('SELECT CAST(REPLICATE(''A'',9000)AS VARCHAR(MAX))')

quote:

---

Originally posted by Bex

Hi Visakh1

I have managed to recreate the issue:

If you create the proc below:

ALTER PROC Test
AS

EXEC ('SELECT REPLICATE(''A'',9000)')
--EXEC ('SELECT REPLICATE(CAST(''A''AS VARCHAR(MAX)),9000)')
--EXEC ('SELECT CAST(REPLICATE(''A'',9000)AS VARCHAR(MAX))')

And then execute using the command line (save it in a BAT file):

SQLCMD -E -SWS23 -dsainsburys -Q "EXEC dbo.TEST" -oD:\bcpscript.bat /h-1

If you uncomment the second line instead of the first, you will see that the results are very different.

So if I don't CAST, I am limited to 8000Bytes, if I do CAST, then the results are truncated (to 256....)?


Hearty head pats

---

quote:

---

Originally posted by Transact Charlie

Hi Visakh16,

I don't think it's that -- the data isn't actually being truncated anywhere -- it's just not being displayed with more than a certain number of characters by the SELECT / PRINT or whatever -- the data is actually still there in the variable but only the first x number of characters are displayed.

I've not heard a comprehensive answer why SQL server does this or where to change the behaviour except for the obvious places (max length of returned columns etc).

The best solution I've used for situations like this is to split the string into rows (in this case with a delimiter of the end of line characters). Then put that information into a table and either use it there or use bcp to get the text out to a file.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---