Unanswered: Varchar(8000) issue while transferring sql server table to csv file
I am currently woking on transfering the table contents in sql server in an csv file i have created a stored procedure which would do same but the prob i am facing is the data in the table is not clean it contains tab,newline etc so i had to clean the data i had applied the folowing procedure
declare @columns varchar(8000), @sql varchar(8000), @sql1 varchar(8000),@data_file varchar(100)
--'@columns+''replace(replace(replace(''+column_name +'',Char(10),''''''''),Char(13),''''''''),Char(19) ,'''''''')'''
@columns=@columns+'replace(replace(replace('+colum n_name+',Char(10),''''),Char(13),''''),Char(19),'' '') as '+column_name+ ', '
ORDER BY ORDINAL_POSITION
the table_name contains around 300 columns with the column_name of min 20 characters
When i run the above query i get only few columns instead of all the columns so i tried to find the length which gives me the result as 4000.
I had declared @columns as Varchar(8000) i dont know why this issue is coming up?? is there any other way i can clean the data an the transfer it into the file
It would be of great help if any one can help me in this
Well because the length of the expression is an issue I would run an update statement per textual column instead before sorting your BCP command.
Generate the update statements "air-code" (i.e. written from my head without testing)
SELECT 'UPDATE ' table_schema + '.' + table_name + ' SET ' + column_name + ' = Replace(' + column_name + ', Char(10), '''''') WHERE CharIndex(' + column_name + ', Char(10)) > 0'
WHERE data_type IN (<your list of appropriate text data types>)
But my only concern her is since we are using an update statement will it impact the performance beacuse there would be milllions of records in the table and the structure of table which we work has many columns with varchar datatype...
I was under the impression that this was a one off thing so didn't really think of performance other than adding the WHERE clauses (incidentally: I got the parameters of CharIndex the wrong way round :P ).
Can you run this as a job out of hours?
If you are updating more than a thousand records or so on each update statement then consider batching them up using loops.
If you explain your scenario better then we may be able to help further.