November 2, 2007 at 3:04 pm
I need to cast multiple fields to varchar - however, I won't know the field size or type
in advance. Should I just go for broke and do CAST(foo AS VarChar(8000))?
November 2, 2007 at 8:17 pm
No... I'd think not. What kind of "fields" are you talking about? Parameters from some GUI or columns in a table? Why wouldn't you know that max length?
--Jeff Moden
November 2, 2007 at 11:19 pm
Sorry, when I said fields I meant columns.
It is a table where I need to convert every column value to varchar. I don't have anyway of knowing
the number of columns or the data types prior to runtime.
To more clear, my SP is being a passed a query string which I slice and dice to do a "SELECT INTO"
to get the results into a temp table. I am then selecting the column names and stepping through them
with a cursor to build a new query string that casts every column value as varchar and selects into
a second temp table. The name of this second temp table then gets passed to an external app by
way of xp_cmdshell.
Since I am already pulling the column names from tempdb.information_schema.columns, I suppose
I could also grab the data_type and character_maximum_length and use that for the size of the
varchar cast. (datetime, decimal, etc have null for character_maximum_length but I could just pick
a sane value where character_maximum_length is null.)
So, the desired result is that the entire table get converted to strings. There may be a better way to
do this but this way is somewhat less of a kludge than our old way which made heavy use of bcp.
November 2, 2007 at 11:28 pm
Have you tried using SELECT/INTO using QUOTENAME for each column?
--Jeff Moden
November 2, 2007 at 11:49 pm
The task sounds like text processing.
I don't see anything related to databases in it.
I believe T-SQL has nothing to do with it.
There are many other, more effective tools for text file processing.
_____________
Code for TallyGenerator
November 3, 2007 at 12:07 am
Interesting idea. Are there any types QUOTENAME can't handle?
I hadn't even given it a thought because the T-SQL ref says:
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
However, it seems to digest DateTime and Decimal happily.
The key part will probably look something like this:
[font="Courier New"]
SET @sql = 'SELECT '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + 'QUOTENAME([' + @col_name + ']) AS [' + @col_name + '],'
FETCH NEXT FROM cur INTO @col_name
END
CLOSE cur
DEALLOCATE cur
SET @sql = SUBSTRING(@sql, 1, datalength(@sql) - 1)
SET @sql = @sql + ' INTO ['+ @tmp_prefix + '_dat] FROM ['+ @tmp_prefix + '_tmp]'[/font]
I will have to substring the delimiters out of existence unless you know of
a better way. Even so, that is more convenient (and maintainable) than
trying to get reliable sizes on the fly.
November 3, 2007 at 12:22 am
Sergiy (11/2/2007)
The task sounds like text processing.I don't see anything related to databases in it.
I believe T-SQL has nothing to do with it.
There are many other, more effective tools for text file processing.
While I respect your opinion, I must disagree in this instance.
In reality, I have done a less than optimum job of of explaining the task. Given any
query string that you would normally pass to xp_sendmail, pass the results of that
query to an external Python script with a buggy dbi module that blows up on dates
prior to 1970, etc.
One of the goals is to email query results as html tables - which prevents the use of
xp_sendmail. However, the CDO method limits the body size to 8000 - which takes no
time to hit when you start adding table markup.
The python method has been a great success other than worrying about out of range dates.
November 3, 2007 at 12:34 am
null (11/3/2007)
One of the goals is to email query results as html tables - which prevents the use ofxp_sendmail. However, the CDO method limits the body size to 8000 - which takes no
time to hit when you start adding table markup.
What you mean to query?
As I understand source of the data is some external file with absolutely unknown contents.
If that's right, what Relational Database Management System has to do with it?
_____________
Code for TallyGenerator
November 3, 2007 at 12:55 am
Sergiy, my apologies for not being clear. The data is completely in SQL Server.
When I say I don't know what the data is in advance, it is because I don't know what
query will be producing the result set. Anyone in the IT department can feed their
query string to the SP. Typically, it would be invoked as such:
[font="Courier New"]EXEC usp_xyz_mail @To = 'foo@foo.com', @Bcc = 'baz@foo.com', @Subject = 'Something witty...', @Query = 'SELECT * FROM some_table WHERE some_column = some_value'[/font]
However, the dbi module of the mailer script is not so robust - it reduces stress all around
if the result set is handed of to the script as string values only.
Formerly, I was using xp_cmdshell to invoke a bcp export of the temp table in a mostly comma
delimited format. Then xp_cmdshell called the script and the script read in the exported file.
Using the Python dbi module to directly read the temp table (by way of odbc) has proven
to be quicker and more headache free (despite the datetime issue).
November 3, 2007 at 1:56 am
cast(fieldname as varchar) will do.
November 3, 2007 at 7:45 am
Ronald San Juan (11/3/2007)
cast(fieldname as varchar) will do.
It won't. Cast doesn't do anything special to set the size of varchar and varchar defaults
to a length of 30.
Empirical testing shows that this is indeed an issue:
[font="Courier New"]SELECT CAST('1234567890123456789012345678901234567890' AS varchar)
result> 123456789012345678901234567890[/font]
There are many columns in the database (such as purchase order comments) that
are greater in length than 30.
November 3, 2007 at 8:00 am
null (11/3/2007)
Ronald San Juan (11/3/2007)
cast(fieldname as varchar) will do.It won't. Cast doesn't do anything special to set the size of varchar and varchar defaults
to a length of 30.
Empirical testing shows that this is indeed an issue:
[font="Courier New"]SELECT CAST('1234567890123456789012345678901234567890' AS varchar)
result> 123456789012345678901234567890[/font]
There are many columns in the database (such as purchase order comments) that
are greater in length than 30.
Sorry, i didn't read thouroughly. I thought they were just numbers and dates.
November 3, 2007 at 11:52 am
Uh oh... Jeff Moden was my hero for a bit with his QUOTENAME suggestion.
How fickle are the fleeting fortunes of fame - consider this:
[font="Courier New"]DECLARE @S VarChar(4000)
SET @S = REPLICATE('x', 200)
SELECT LEN(@s), LEN(QUOTENAME(@s))
Result> 200, 130[/font]
It looks like I am back to taking character_maximum_length from tempdb.information_schema.columns
and just picking some sane amount when character_maximum_length is null.
Or, going back to the bcp export - but I would rather avoid that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply