Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Smartlist doesn't appear and the lookup doesn't display contents

Posted on 2011-09-21
24
Medium Priority
?
566 Views
Last Modified: 2013-12-11
Something happened to a pc where SQL 2000 & GP is installed locally.

When trying to see the smartlist, the windows is empty.  Also when clicking the lookup no contents appear.

However, if we click on the navigator button at button left of of a inquiry window, we get a SQL Error (see pic).  If we click on lookup a master record, it works; it gives the error when inquirying a inquiry window that would display transaction.

After reading the error, it looks that it may be the SQL instance

Please advice
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
24 Comments
 

Author Comment

by:rayluvs
ID: 36577848
0
 
LVL 10

Accepted Solution

by:
Abdulmalek_Hamsho earned 2000 total points
ID: 36579265
There is a conflict between your OS (Windows System) collation (in Regional Settings) and your DB collation.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36579267
Sorry, conflict between your Windows Locale and DB Collation.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:rayluvs
ID: 36581233
Thank you.  We began to check how to resolve this and used the following scripts to determine:

sp_helpsort
SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')
SELECT SERVERPROPERTY('Edition')

How can we determine Windows Locale and DB Collation?
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 2000 total points
ID: 36583954
As I mentioned before: Control Panel -> Regional Settings or Region and Language ->  Advanced or Administrative -> Change Locale.

One more important thing to check. When you create the ODBC connection in the client side, make sure to uncheck the following options:

1- Perform Translation for character data.
2- Use regional settings when outputting currency ...etc.

Please try this before checking the collation issue.
0
 

Author Comment

by:rayluvs
ID: 36586427
Ok will do this morning
0
 

Author Comment

by:rayluvs
ID: 36718592
Verified Change Locale and is correct (the same as the PC that GP works fine).  Also the ODBC, all is unchecked.

How can we verify the DB collation against the PC that is working fine?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36720510
The collation is related to the DB not the PC.

Right click the SQL server in SQL Management Studio and select properties, you will find it here.
0
 

Author Comment

by:rayluvs
ID: 36781273
They are different.  

Do we have to reinstall SQL?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36813505
No no, just give me what're your OS locale and your DB collation.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36813507
And what's your Windows version?
0
 

Author Comment

by:rayluvs
ID: 36815288
Windows version is Windows 7

For DB collation we used "sp_helpsort".  The result:

      Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
      for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data.



0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36816015
Well, it seems your windows locale is spanish. You need to make spanish enabled rather than being your default language in your windows. This can be done in Region & Language Settings.
0
 

Author Comment

by:rayluvs
ID: 36896846
Yes its Spanish and also we changed it to other Spanish regions, like Honduras, etc. and still same problem.

Is there a way to know the instance collation and the DB collation?> Maybe there is the conflict?

Please advice
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36896924
To change the DB Collation you would need days, so please concentrate on your Windows locale, please make it English with Spanish enabled.
0
 

Author Comment

by:rayluvs
ID: 36896941
English with spanish? How?
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 2000 total points
ID: 36897175
Control Panel -> Region and Language -> Administrative -> Change System Locale -> Select English here
Control Panel -> Region and Language -> Keyboards and Languages -> Install/Uninstall Languages -> Install Spanish
0
 

Author Comment

by:rayluvs
ID: 36897285
Hi,

We just reinstall SQL & GP on the workstation and restore the backup DB and it worked.  Didn't get to test your last recommendation.
0
 

Author Comment

by:rayluvs
ID: 36898042
The problem is fixed.  What we noticed was that when initially SQL was installed it was another collation (but we are not sure since when we reinstall now we made sure that collation be the same as the server); all is working fine.

Any observations?
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36898111
Great. I expected that you have had a working SQL DB with the required collation.
0
 

Author Comment

by:rayluvs
ID: 36898147
Update of our situation & how it was resolved:

I was just informed that SQL wasn't re-install, the DB Collation was change.  I gave instruction not to do it since you said it takes days to do this, but one of the guys found the solution.  So anyways, here is what we did incase any user here ever need to do this:

There is a tool that comes with SQL, it's located at C:\Program Files\Microsoft SQL Server\80\Tools\binn.  The tool is called "rebuildm.exe".  We stopped the service and ran the tool; there is an option where you choose and DONE!
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 36898445
rebuildm.exe does not rebuild the Collation dear, it rebuildes the "master" DB. I think you changed the collations of the master DB only, which is not the sufficient solution to your case.
0
 

Author Comment

by:rayluvs
ID: 36898587
That's it, rebuilding the master DB, in the collation configuration window.  Obviously we had to backup our DB, detach, then re-attach, re-work the security info, etc.  I personally didn't do this, but it's working fine now.

Thanx
0
 

Author Closing Comment

by:rayluvs
ID: 36898595
Thanx.  Though the answered awarded didn't fix our problem, it was a possible cause if it was.

In our case, we fixed the "Smartlist doesn't appear and the lookup..." problem with SQL tool rebuild.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Hi, I've just fixed a problem with Sage 50 Accounts 2010 crashing when creating a new Company.  I've spent nearly 4 hours on it including three calls to the Sage helpdesk (they didn't work it out in the end - I did!) ... so I thought I'd share th…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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