?
Solved

goldmine NULL in history

Posted on 2011-10-02
13
Medium Priority
?
930 Views
Last Modified: 2013-11-15
When converting Goldmine 6 to 9 (6 to 8.5 to 9.2) each history and cal notes where stamped with the word NULL. If there was data in the notes it was retained.

I would like to remove the word null from the notes using MSSQL or ?

Any ideas?  Thank you
0
Comment
Question by:goldmineexpert
  • 5
  • 4
  • 4
13 Comments
 
LVL 12

Accepted Solution

by:
GMGenius earned 750 total points
ID: 36909212
It shouldnt matter if NULL is in that field, its classed as empty
0
 

Author Comment

by:goldmineexpert
ID: 36915254
Customer sees the word NULL in all history and cal records which had empty notes.

Any ideas to remove the word NULL from the notes ?

Thanks, bob
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 36915375
As posted on your other question

update CONTHIST set NOTES = 0x00 where NOTES is null

Open in new window


You can use this for any other table that has a Image type field
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 11

Expert Comment

by:stevengraff
ID: 36917563
Would this work as well?


update conthist set notes = '' where Notes='NULL'

Open in new window

0
 
LVL 12

Expert Comment

by:GMGenius
ID: 36917793
No. Null is a state not a value
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 36917806
And the note field is an image so you cannot set a string value
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 36917812
Right... I was just trying to account for the fact that the users are seeing that word through the UI, as if maybe it was actually written into the db that way.
0
 

Author Comment

by:goldmineexpert
ID: 36940776
I believe the word null is written as a word.

This did not work by GMGenius when I ran this:
update CONTHIST set NOTES = 0x00 where NOTES is null

Got this error when I ran this:
Msg 402, Level 16, State 1, Line 1
The data types image and varchar are incompatible in the equal to operator.

Any Ideas?
0
 
LVL 11

Assisted Solution

by:stevengraff
stevengraff earned 750 total points
ID: 36942020
First, just to be sure you're identifying things properly, run this query:
 
select  cast(cast(NOTES as varbinary(max))  as varchar(max)) Notes from conthist
where rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))) like 'null%'

Open in new window


The assumption is that the word Null appears at the beginning of the notes. True?

Next, this query should expunge the word 'Null'
 
update CONTHIST
set NOTES =
REPLACE(rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))),'NULL','')
where rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))) like 'null%'

Open in new window


It's not case sensitive.
0
 

Author Comment

by:goldmineexpert
ID: 37032503
I kind of put this on the back burner but will be testing today.

I will keep you updated of the progress.

Have you experienced any of this during your upgrades? I see it often.

bob
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 37066595
Have you experienced any of this during your upgrades? I see it often

I've seen it, but only on one or two... I don't have a sense of what's causing it, but it is an odd feature.
0
 

Author Closing Comment

by:goldmineexpert
ID: 37269706
still no resolve
0
 
LVL 11

Expert Comment

by:stevengraff
ID: 37269713
@goldmineexpert... what do you mean "still no resolve?"

The solution I posted for you should work just fine! Did you not try it? If you leave the database with the word NULL in the notes field... well, yes, that's unresolved work you still need to do. But I'm not hearing that you tried my solution and it failed (which I promise you it would not!)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

GoldMine CRM has a fairly open database architecture , however in this case the email distribution list is stored in a quirky way and does not allow easy access for external use. This article will give details of this and provide a method to extr…
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question