SQL Server 2008 Collation choices for Dynamics GP sort order 52

When installing Dynamics GP 2010 on a SQL Server the end collation will be based on the Regional Settings that your server is running under.

English (United Kingdom) - Latin1_General_CI_AS


English (United States) - SQL_Latin1_General_CP1_CI_AS
Both are supported to use as Sort Order 52 for Microsoft Dynamics GP 2010.  However you can run a SQL_Latin1_General_CP1_CI_AS company database on a server running Latin1_General_CI_AS


The following website link shows the language setting and the collation defined by default
http://technet.microsoft.com/en-us/library/ms143508(SQL.90).aspx

The Dynamics GP Blogster: Granting Microsoft Dynamics GP user minimal access...

The Dynamics GP Blogster: Granting Microsoft Dynamics GP user minimal access...: "After the long title of this post, you probably already have the idea of what the article will be about. However, back in April of 2009, I w..."

How to transfer logins and passwords between instances of SQL Server

Fantastic article from Microsoft to transfer logins between SQL Server installs.  Pefect for server moves of SQL 7, 2000, 2005 and 2008

http://support.microsoft.com/kb/246133

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance - script

The following document was from the great Pinal Dave on from the following website: http://www.sqlauthority.com/. This article was extremely useful when I was troubleshooting performance issues in Dynamics GP:


Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://www.sqlauthority.com/)

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance

The following document was found in www.sqlauthority.com and was extremely useful.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Parser Error Message: Cannot use 'partitionResolver' unless the mode is 'StateServer' or 'SQLServer'

Installed SSRS 2005 on a Sharepoint MOSS 2007 server and what a pain as I realise SSRS did not work due to confliction with MOSS. Error was shown when trying to access it:

"Parser Error Message: Cannot use 'partitionResolver' unless the mode is 'StateServer' or 'SQLServer'"

1. Create a new virtual directory off \inetpub\wwwroot\
2. Give it a specific port number I used SSRS / 87
3. Configure (in SSRS) a new "Report Server Virtual Directory"
4. Configure (in SSRS) a new "Report Manager Virtual Directory"
5. Go to Web Service Identity and click the New button next to Report Server
6. Create a new App Pool for RS to run within, I used SSRSAppPool
7. Set the authenticated user for the new app pool to the SQL Server Service account you used to set up MOSS such as OSS_USER
8. This should have all the SQL permissions you need to run SSRS (I hope...)
9. Set Report Server and Report Manager to the new app pool and click Apply
10. After making these changes run IISRESET go to the following URL's:
http://Myserver:87/ReportServer/
http://Myserver:87/Reports/

SQL versions

Identify version by running query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Look up on list below:

RTM 90.1399
SQL Server 2005 Service Pack 1 90.2047
SQL Server 2005 Service Pack 2 90.3042

SQL 2000 has similar query with the following versions:

RTM 80.194.0
SQL Server 2000 SP1 80.384.0
SQL Server 2000 SP2 80.534.0
SQL Server 2000 SP3 80.760.0
SQL Server 2000 SP3a 80.760.0
SQL Server 2000 SP4 8.00.2039

Reducing databases with an ever expanding transaction log

Reducing a database due to its transaction log expanding to a large size run the following steps:

All done in SQL Server Management Studio

1. Run the following t-sql command: DUMP TRANSACTION [db_name] WITH NO_LOG
2.Run a shrink on the database

SSRS and forcing rectangle to not overlap on to second page

Using SSRS 2005 we had an issue with a report that contained a table and a rectangle at the bottom of the report. The rectangle acted as a printed form at the bottom of the report, however if the table contained too many lines then part of the rectangle would print on the next page. The solution seems to be to put the a new list box underneath the rectangle and this then forced the rectangle to always print as a whole

what a pain. I believe this is easier in SSRS 2008

The value provided for the report parameter 'XXXX' is not valid for its type

I received an error in Visual studion 2005 when trying to pass a parameter to my datasource. My parameter was a datetime field which the user uses a calendar to select their require date. In the datasource was a column called TRXDATE which was a datetime column. Everytime i tried to rest the report within Visual Basic I received the following error ('XXXX' being my parameter)

The value provided for the report parameter 'XXXX' is not valid for its type

After a few wasted hours which i will never get back, I realised that the problem was down to Visual Basic 2005 and nothing to do with my report. When publishing the report to Report Server it worked fine.

My advice when receiving this issue is to try the following:

1. Test the datasource in SQL. Check that it works with a date being passed through with the format of YYYY-MM-DD. If no then the problem is the data source
2. Publish the report to reportserver and test. If problem then check parameter and datasource setup in Visual Studio 2005
3. Look forward to the upgrade to SQL Server 2008

'/' is an unexpected token. The expected token is '='. Line 9, position 33.

When running a large SQL Server Reporting Services 2005 report I was receiving the following error:

'/' is an unexpected token. The expected token is '='. Line 9, position 33.

Resolution was to change the Web Config file, located at the following location - C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. I adjusted the executionTimeout element from 90 to 900 in this file to correct the issue.