Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 07:28:07
|
I am dynamically creating a BAT file to perform large imports and exports of data. The table names are not known until run time, hence the need to use a proc to create the BAT.However, I have run into a bit of an issue, where I'm running out of space in the variable. I was using nvarchar(MAX), so switched to varchar(MAX). There are many lines that need to be written in the Text file, and I am also formatting the file so it is readable.What are my other options? I thought perhaps I could do a direct SELECT '' and execute that, however, i iterate the appending of rows which I cannot do using this method.I thought perhaps using numerous variables, but then I can only break them up to a certain extent and it still doesn't guarantee that I will not run out of space.Any alternatives????ThanksHearty head pats |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-03-20 : 07:49:25
|
I don't know much about LOB's but my understanding is that varchar(max) allows you to go store up to 2 GB (?) of data. This example stores a million characters into a variable.declare @a varchar(max);set @a = replicate(cast('a' as varchar(max)),1000000);select len(@a) You need to explicitly cast the things you put into the varchar(max) variable as I have done here, or you will be limited to maximum 8,000 characters. |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 08:03:00
|
Ah, I think you hit the nail on the head there. I am not casting everything I put into the variable (including other varchar values which are NOT MAX). Thank you. I shall have a go at doing that!Hearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 08:12:13
|
Hi SunitaCan I please ask you to look at this code, and tell me if I have to cast the strings as well? I hvae changed ALL the variables to do of VARCHAR(MAX) (and it still stops at 8000 chars), so do I need to explicitly cast EVERYTHING?:SET @SecuritySQLCMD = CASE COALESCE(@UserName, 0) WHEN 0 THEN '-E' ELSE '-U' + @UserName + ' -P' + @Password ENDSET @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 ScriptsSET @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 ScriptsSET @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 '+@FilePathSET @BCP4 = '.dat '+@SecurityBCP+' '+@BCPMode+' -b'+@BatchSize+' -S'+@ServerName+''''-- Create BCP Import IN ScriptsSET @BCP5 = '''bcp '+@DatabaseName+'.'SET @BCP6 = ' in '+@FilePathSET @BCP7 = '.dat '+@SecurityBCP+' '+@BCPMode+' -E'''-- DROPS & CREATES the dbo.NewStoreMapping tableSET @BATScript = 'SELECT ''@ECHO OFF''' +@NewLine+'+'+@SQLCMD1 +@NewLine+'+'+@SQLCMD2 +@NewLine+'+'+@SQLCMD3 +@NewLine+'+'+@SQLCMD4+@NewLine+@NewLineWHILE @Count <= @MaxNumberOfIterationsBEGIN -- 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 + 1END--SET @BATScript = @BATScriptSET @BATScript = @BATScript+@NewLine+@NewLine+'+'+'''PAUSE'''--SELECT @BATScriptEXEC (@BATScript) Thanks for your timeHearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 08:43:43
|
Ok, I'm getting frustrated here. I have cast pretty much everything that I can cast, yet it is still limited to 8000bytes ARGH!!Apologies in advance, as I hate pasting huge amounts of code, but I think another pair of eyes is required. Can ANYONE see where I have nto cast a string to VARCHAR(MAX):ALTER PROCEDURE dbo.CreateBATScriptForStoreExtrapolation( @ServerName VARCHAR(MAX), @DatabaseName VARCHAR(MAX), @FilePath VARCHAR(MAX), @UserName VARCHAR(MAX), @Password VARCHAR(MAX))ASSET 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 -SWS23BCP 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 sizeSET @FilePath = CAST('D:\' AS VARCHAR(MAX))--DROP TABLE #TableNamesCREATE TABLE #TableNames ( ID INT IDENTITY , TableName VARCHAR(100) , TableID INT , HasIdentity BIT , ColumnList VARCHAR(MAX) )--DROP TABLE #ColumnListCREATE 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 TemplateObjectIDFROM admin.GetTableTemplates (NULL, 'template')-- Obtain the number of iterations required to get all template table column listsSELECT @MaxNumberOfIterations = MAX(ID), @Count = 1 FROM #ColumnListWHILE @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 ENDINSERT INTO #TableNames (TableName, HasIdentity, ColumnList)SELECT tbls.TableSchema+'.'+tbls.TableName , tbls.HasIdentity , CAST(schm.ColumnList AS VARCHAR(MAX))FROM admin.GetTableNames(NULL,@SchemaName,@TemplateSchemaName)tblsINNER JOIN admin.GetTableNestedLevels (NULL, 'template') lvls ON lvls.ChildObjectID = tbls.TemplateObjectIDINNER JOIN #ColumnList schm ON schm.TemplateObjectID = tbls.TemplateObjectIDORDER BY tbls.PartitionDate, lvls.NestedLevel, tbls.TableObjectIDSELECT @MaxNumberOfIterations = MAX(ID), @Count = 1 FROM #TableNamesSELECT @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 ScriptsSET @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 ScriptsSET @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 ScriptsSET @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 tableSET @BATScript = CAST('SELECT ''@ECHO OFF''' +@NewLine+'+'+@SQLCMD1 +@NewLine+'+'+@SQLCMD2 +@NewLine+'+'+@SQLCMD3 +@NewLine+'+'+@SQLCMD4+@NewLine+@NewLine AS VARCHAR(MAX))WHILE @Count <= @MaxNumberOfIterationsBEGIN -- 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 + 1END--SET @BATScript = @BATScriptSET @BATScript = CAST(@BATScript+@NewLine+@NewLine+'+'+'''PAUSE'''AS VARCHAR(MAX))--SELECT @BATScriptEXEC (@BATScript) ThankyouHearty head pats |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-20 : 08:57:30
|
Hi Bex,Are yuo sure that it is actually only letting you have 8000 characters?if you were to print (or SELECT) a varchar(MAX) it won't show you you anything after a certain amount. (8000) But the information is actually still there.What happens if you put this line into your debug test:SELECT LEN(@BATScript)If you get a value greater than 8000 then you know that it's just an artifact of the SELECT or PRINT statement you are using for debugging.Regards,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 09:13:23
|
Hi CharlieThanks for that! You are right, when I did as suggested, the result was 197828. What I am trying to do is use the procedure (posted) to create the dynamic SQL statement. This is then executed by the EXEC(@BATScript) statement. I run another BAT file which runs the following statement at the command line:SQLCMD -E -SWS23 -dsainsburys -Q "EXEC dbo.CreateBATScriptForStoreExtrapolation 'WS23','sainsburys','D:\',NULL,NULL" -oD:\bcpscript.bat /h-1 This is supposed to output the results of the executed @BATScript. But these results are truncated???!!?So the SQL statement I have passed contains all the characters (makes sense as the procedure would fall over), but the outputted results is whats truncated?Any suggestions?Hearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 09:26:16
|
I think I know my problem. The SELECT statement needs to be cast to varchar(max) (so I have to added an embedded CAST in the proc).However, a NEW problem. When i do this , then only the first 2 sentences are created, and then its truncated. I don't know why this is happening!!!!Hearty head pats |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-20 : 09:39:10
|
Maybe a string splitting function and insert each line into a temp table? Something like -- you should be able to run this on a test 2005 database: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]GOCREATE 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 RETURNENDGO-- Example of useDECLARE @foo VARCHAR(MAX)SET @foo = 'THIS IS A BLOCK OF TEXTWITH LINE BREAKS IN ITREGULAR WINDOWS CHAR(13)+CHAR(10)'SELECT @foo = REPLICATE(@foo, 1000)SELECT LEN(@foo)SELECT @fooSELECT * FROM fn_Split(@foo, CHAR(13)+CHAR(10)) ORDER BY [position] ASC Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 09:39:55
|
is there any carraige return or line feed characters? Also are you sure you've set the number of characters to large number in query editor properties? |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 09:54:44
|
Hi Visakh1I have managed to recreate the issue:If you create the proc below:ALTER PROC TestASEXEC ('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 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-20 : 10:01:28
|
You can use the string splitting function to store the string in a table and then output it as data to wherever you need it to go.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 10:08:48
|
Some more wierdness:When I execute the statements below in management studio:EXEC ('SELECT REPLICATE(''A'',9000)')--EXEC ('SELECT REPLICATE(CAST(''A''AS VARCHAR(MAX)),9000)')--EXEC ('SELECT CAST(REPLICATE(''A'',9000)AS VARCHAR(MAX))')The second one (the one that works) outputs 9000? So why does this affect what is written to the results/bat file?Hi Charlie, This string splitting function? Could you explain further please? If I cannot resolve the above, then perhaps I shall have to take this route (although I am sure there is a resolution to my problem, I just don't knwo what is it )ThanksHearty head pats |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-20 : 10:11:32
|
See my post like 4 up -- fn_splitCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 10:31:51
|
Oh yes, sorry, I missed that post! Doh! Thanks for that!Hearty head pats |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 11:09:52
|
quote: Originally posted by Bex Hi Visakh1I have managed to recreate the issue:If you create the proc below:ALTER PROC TestASEXEC ('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
is it this?http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283368 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-20 : 11:30:58
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 12:05:14
|
Hi guysThankyou both for dedicating so much of your time on this. I finally found out the issue. I realised that the issue is with the SQLCMD command, as the SQL command was generating the correct output when in management studio.Anyway, I managed to find this sentence, and it is now no longer truncating to 256bytes once I inserted /y0 in the command line query:I used the -y0 param (width_display) w/ sqlcmd to generate the file and overcome the default 256 character limit on the file size:I still haven't got it working as of yet, as I need to apply all the CAST statements in the SELECT (so all strings are explicitly cast to varchar(max)), but I am one step nearer!Thanks again!Hearty head pats |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 12:05:59
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
i initially thought of that and gave suggestion (see 03/20/2009 : 09:39:55). but i didnt get any response, so i thought it might be problem due to some concatenation. |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-20 : 12:22:30
|
Hi visakh1So you did. I have to admit, this is the first time I have ever used BAT or SQLCMD programming, so I didn't actually understand what you meant. But now in context, I understand. Thanks for your time. Perhaps next time I shall ask you to expand if I don't get it. Could've saved me a few hours of wasted time!Have a good weekend!Hearty head pats |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-20 : 12:30:51
|
no probs...you too have a great weekend |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|