Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

nText field pulling blank records when I use Is Not Null

Is there something else I can use to check this?  I have a linked SQL table in access.  The SQL field is a ntext field.  When I run a query and say Is Not Null it is still in some cases pulling a blank record.  Thanks all
0
sbornstein2
Asked:
sbornstein2
  • 9
  • 6
1 Solution
 
NightmanCTOCommented:
Is the record blank or null?
Check whether or not it is an empty string    column=''.
Or perhaps it has been padded RTRIM(column)=''.
0
 
sbornstein2Author Commented:
ya well I tried in access "Is Not Null and <> """.  But I get an error when I say the <> "" in access against that field.
0
 
sbornstein2Author Commented:
it is blank I beleive
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NightmanCTOCommented:
Blank is not null, but empty string. Do you have access to Query Analyser?

If so, run this:

SELECT * FROM yourTable WHERE RTRIM(YourColumn)=''
0
 
sbornstein2Author Commented:
I cant access Query Analyzer right now unfortunatly.  I can only link to the table.  It is showing in design a memo field converting the SQL field Text to say that.  But I return approx 10 or so records that are showing my text field as blank in the query when using the Is Not Null only.
0
 
NightmanCTOCommented:
Then those records are not null, but probably empty string.

Post your query here and I'll see if I can modify it for you.
0
 
imran_fastCommented:
hi,
Is it showng <logn text> in enterprise manager? Can you verify that?
0
 
sbornstein2Author Commented:
it is just showing blank in Enterprise Manager and I dont want to change the datatype to a varchar(4000) etc. because it is setup using replication right now. I dont want to mess with that.
0
 
NightmanCTOCommented:
So run this query in Query Analyser:
SELECT COUNT(*) FROM yourTable WHERE RTRIM(YourColumn)=''

What does that return?
0
 
sbornstein2Author Commented:
anytime I use the '' it never works on a Text field.  I am in SQL now and it is not ntext it is a text field.  Your query above returns:

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type text is invalid for argument 1 of rtrim function.

I open the table and it is showing blank or an empty string.
0
 
NightmanCTOCommented:
Do this then
SELECT COUNT(*) FROM yourTable WHERE RTRIM(CAST(YourColumn as nvarchar(4000)))=''
0
 
sbornstein2Author Commented:
4850 rows out of about 9K.  So looks like there is quite alot.
0
 
sbornstein2Author Commented:
should I try an update to Null on these?
0
 
sbornstein2Author Commented:
ya that worked I did an update to null where those came up and it now works.  Thanks for your help though.  I wonder what I can do for the future for this other than changing the datatype
0
 
NightmanCTOCommented:
Identify how it became '' in the first place. i.e. prevent unexpected data from coming in to your database from the UI by validating it.

Good to see it's working now.
0
 
sbornstein2Author Commented:
yup thanks Nightman.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now