Getting QUOTENAME script to work with datatypes

Question

Post reply

Getting QUOTENAME script to work with datatypes

rons-605185

SSCommitted

Points: 1618
More actions
December 7, 2009 at 3:13 pm

#385904

When using the QUOTENAME script to pull/extract data from a table (example script below) I had the following issues:
5 fields that had NULL values for all records (OpportunityID,TaskID, CaseID, CampaignID, ProjectID)
3 fields with '0' as value for all records (ContactMade, BPartnerAvailable, CustomerAvailable)
2 fields as ntext datatype (NoteTextRTF, NoteTextTrunc)
When running the QUOTENAME script below, if it hit one of these fields then the result was a single column of 'NULL' and nothing else. When I commented out the above problem fields from the script, then was able to output results (i.e table data extract without the problem fields).
QUESTION: How to code or fix the script to allow extraction of the above fields (Null, 0, ntext values) so that the QUOTENAME script will extract all of the data correctly?
Thanks.
QUOTENAME SCRIPT:
select 'Select '
+ QUOTENAME(NoteID,'''') + ','
+ QUOTENAME(CompanyID, '''') + ','
+ QUOTENAME(EmployeeID, '''') + ','
+ QUOTENAME(ContactID, '''') + ','
+ QUOTENAME(OpportunityID,'''') + ','
+ QUOTENAME(TaskID, '''') + ','
+ QUOTENAME(CaseID, '''') + ','
+ QUOTENAME(CampaignID, '''') + ','
QUOTENAME(ProjectID, '''') + ','
+ QUOTENAME(EntryDate, '''') + ','
+ QUOTENAME(NoteType, '''') + ','
+ QUOTENAME(NoteText, '''') + ','
+ QUOTENAME(NoteTextRTF, '''') + ','
+ QUOTENAME(NoteTextTrunc, '''') + ','
+ QUOTENAME(ContactMade, '''') + ','
+ QUOTENAME(BPartnerAvailable, '''') + ','
+ QUOTENAME(CustomerAvailable, '''') + ','
+ QUOTENAME(CreationDate, '''') + ','
+ QUOTENAME(ChangeDate, '''') + ','
+ QUOTENAME(ChangeDateOffline, '''') + ','
+ QUOTENAME(ChangedByID, '''') + ','
+ QUOTENAME(ChangedByIDOffline, '''') + ','
+ ' UNION ALL'
from dbo.NOTE_tblNote

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

Wrap each QUOTENAME function instance with an ISNULL function:

select 'Select '

+ ISNULL(QUOTENAME(NoteID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CompanyID, ''''),'[NULL VALUE]') + ','

...

from dbo.NOTE_tblNote

_________________________________
seth delconte
http://sqlkeys.com

rons-605185 SSCommitted Points: 1618 More actions · Answer 2

Thanks for the help. Was able to get 99% of the data extract to work. There is a problem with only 1 field in this table. The field is OleObjects and the data type is Image. If I run the script with the line

+ ISNULL(QUOTENAME(OleObjects,''''),'[NULL VALUE]')+ ','

then I get the following error message:

Msg 206, Level 16, State 2, Line 1

Operand type clash: image is incompatible with nvarchar

Do I substitute '[image]' in place of '[NULL VALUE]' for this line?

Using the script below:

select 'Select '

+ ISNULL(QUOTENAME(NoteID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CompanyID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(EmployeeID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ContactID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(OpportunityID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(TaskID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CaseID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CampaignID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ProjectID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(EntryDate,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(NoteType,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(NoteText,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(NoteTextRTF,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(NoteTextTrunc,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ContactMade,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(OleObjects,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(BPartnerAvailable,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CustomerAvailable,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(CreationDate,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ChangeDate,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ChangeDateOffline,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ChangedByID,''''),'[NULL VALUE]')+ ','

+ ISNULL(QUOTENAME(ChangedByIDOffline,''''),'[NULL VALUE]')+ ','

+ ' UNION ALL'

from dbo.NOTE_tblNote

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

Also see how single quotes work in SQL Server

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

Do I substitute '[image]' in place of '[NULL VALUE]' for this line?

No, the '[NULL VALUE]' string that I substituted is just a placeholder. You could put anything in there that the datatype will accept.

But why do you want to use the QUOTENAME function on an image type data field? QUOTENAME puts delimiters of your choice around a string - so it should only be used on string data such as varchar, text, etc. You can't put single quotes around an image :-D.

_________________________________
seth delconte
http://sqlkeys.com