jana
asked on
Smartlist doesn't appear and the lookup doesn't display contents
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, conflict between your Windows Locale and DB Collation.
ASKER
Thank you. We began to check how to resolve this and used the following scripts to determine:
sp_helpsort
SELECT SERVERPROPERTY('ProductLev el')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVer sion')
SELECT SERVERPROPERTY('Edition')
How can we determine Windows Locale and DB Collation?
sp_helpsort
SELECT SERVERPROPERTY('ProductLev
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVer
SELECT SERVERPROPERTY('Edition')
How can we determine Windows Locale and DB Collation?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok will do this morning
ASKER
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?
How can we verify the DB collation against the PC that is working fine?
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.
Right click the SQL server in SQL Management Studio and select properties, you will find it here.
ASKER
They are different.
Do we have to reinstall SQL?
Do we have to reinstall SQL?
No no, just give me what're your OS locale and your DB collation.
And what's your Windows version?
ASKER
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.
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.
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.
ASKER
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
Is there a way to know the instance collation and the DB collation?> Maybe there is the conflict?
Please advice
To change the DB Collation you would need days, so please concentrate on your Windows locale, please make it English with Spanish enabled.
ASKER
English with spanish? How?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
We just reinstall SQL & GP on the workstation and restore the backup DB and it worked. Didn't get to test your last recommendation.
ASKER
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?
Any observations?
Great. I expected that you have had a working SQL DB with the required collation.
ASKER
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!
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!
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.
ASKER
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
Thanx
ASKER
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.
In our case, we fixed the "Smartlist doesn't appear and the lookup..." problem with SQL tool rebuild.
ASKER
MSDynamicsErrorSmartlist.jpg