Posts Tagged ‘sql server’

Backing up QuoteWerks SQL Server database

Monday, July 10th, 2017

The following provides step-by-step screenshots to demonstrate how to set up a scheduled SQL Server backup for a QuoteWerks database (or, indeed, any other type of SQL Server database) hosted on the Express database engine.

(more…)

Why choose QuoteWerks Corporate rather than Professional?

Friday, October 15th, 2010

Why QuoteWerks Corporate edition?

As well as some additional features (to do with synchronisation) and the additional contact manager options i.e. MSCRM and Connectwise, (click for full details here http://www.hilltopsit.co.uk/quotewerks/quotewerks_pricing.aspx), the real reason we recommend QuoteWerks Corporate over Professional is the ability to host the database to SQL Server.

(more…)

Microsoft CRM – exporting 10000 records limit

Tuesday, November 17th, 2009

The default maximum number of records which can be exported from Microsoft Dynamics CRM is 10,000. This is the limit applied by Microsoft to (at least in part) avoid issues with SQL Server timeouts when exporting a large number of records. In order to change this default, you can follow these steps.

(more…)

BLOG: QuoteWerks Consultancy Services case study released

Thursday, September 10th, 2009

Hilltops IT has just published a case study to promote our QuoteWerks consultancy services. Our client – Invicta IT – has engaged Hilltops IT on several occasions since they first adopted the QuoteWerks solution; firstly for QuoteWerks report authoring services and also to install and configure ConnectIT-Sage QuoteWerks to Sage 50 Account link.

(more…)

SSRS: an answer to rsReportParameterValueNotSet error

Wednesday, September 2nd, 2009

An answer to SQL Server Reporting Services (SSRS) rsReportParameterValueNotSet – “This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value” error.

(more…)

MSCRM: Installing – the instance name must be the same as the computer name

Saturday, June 13th, 2009

When installing Microsoft Dynamics CRM MSCRM 4.0 you get the error “the instance name must be the same as the computer name” from the installation wizard Microsoft SQL Server System Requirements check.

(more…)

DEVELOPMENT: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server

Friday, May 8th, 2009

Running a query from SQL Server 2008 over a linked server connection to a SQL Server 2000 server causes the following error:

OLE DB provider “SQLNCLI10″ for linked server “mylinkedserver” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10″ for linked server “mylinkedserver” returned message “The stored procedure required to complete this operation could notbe found on the server. Please contact your system administrator.”.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowsetDBSCHEMA_TABLES_INFO” for OLE DB provider interface, but returns a failure code when it is used.

Or

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowsetDBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server ““. The provider supports the interface, but returns a failure code when it is used.


Firstly, SQL Server 2000 SP4 (service pack 4) must be installed.

Then the system stored procedures must be manually upgraded.

Note that when manually upgrading the system stored procedures, we used SQL Server Authentication mode which requires the syntax:
osql -U [adminlogin]-P [adminpassword]-S [linkedservername]-i [location]\instcat.sql

We first entered the line as:
osql -U sa -P myPassword -S myServer -i C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql

But this just bought up the osql help /? list of parameters. To avoid this, make sure that the -i path is in speech marks!
osql -U sa -P myPassword -S myServer -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql

Note that the manual upgrade takes a couple of minutes to run and there are a whole bunch of message and numbers displayed in the command window as it executes. If the process is successful, then the last but one line will read “instcat.sql completed successfully”.

In our experience, the SQL Service did not need to be restarted, the fix worked immediately and we were able to query the SQL Server 2000 server from SQL Server 2008 over the link immediately:
SELECT COUNT(*)
FROM myLinkedServer.myDatabase.dbo.myTable

We hope that this tip helps others new to SQL Server 2008 development!

Please see our website for more information on our software development services and software development resources.

DEVELOPMENT: rsReportServerDatabaseUnavailable on Reporting Services 2008

Thursday, May 7th, 2009

Trying to connect to the SQL Server Reporting Services reports server for the first time using http://MyServerName/Reportserver, we got the following error:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help. SQL Server does not exist or access denied.

Using http://localhost/Reportserver opened a login dialog, but the administrator username and password didn’t work.

Discussing this with the company who supplied the server, it came to light that the server name had been changed after SQL Server Reporting Services had been installed.

We resolved this by using the rsconfig utility:

Opening a Command Prompt and executing a line like this:
rsconfig -c -sMyServerName -dReportServer -aWindows -uDomain\Account -pMyPassword

Followed by resetting IIS (Internet Information Services)
iisreset

We hope this helps!

DEVELOPMENT: SQL Server 2008 SP1 released

Wednesday, April 8th, 2009

SQL Server 2008 Service Pack 1 (SP1) is now available. You can use these packages to upgrade any SQL Server 2008 edition.

(more…)

MSCRM: Microsoft CRM Authentication Error 0x80040204

Thursday, April 2nd, 2009

It’s been a challenging afternoon today, following one of our clients introducing a new server to their network domain. Users trying to log into the Microsoft CRM system (hosted on another server) got the error: “Authentication Failed” with code 0x80040204.

The introduction of the new Microsoft Windows Server 2008 machine somehow affected the existing Microsoft Windows SBS 2003 domain controller, and changed (or recreated) the Active Directory groups which Microsoft CRM relies on: PrivUserGroup, ReportingGroup, SQLAccessGroup and UserGroup.

In the Application Event Log, errors were reported from the SCRMDeploymentManagerSnapin: “IsCRMSysAdmin : WhoAmI failed”.

Checking the Microsoft CRM database, we could see that all System Users and Roles were still there, so the problem seemed to point towards Windows security and / or Active Directory.

We’ve found in the past that the best way to tackle these types of issue is to roll your sleeves up and get straight into the Microsoft CRM error tracing feature. Details of how to set this up are here: http://support.microsoft.com/kb/907490

Setting TraceCategories to “*:Verbose” will give you absolutely everything that’s going on to understand the problem, but we strongly recommend only enabling this temporarily.

The Trace output file showed the SQL statements for the login; copy/pasting these into a query editor showed record being returned, so no problem there. the problem was found about 20 lines down in the Trace: “Invalid code for CRM error”, “ADsGetObject() failed”. Looking more and more like Active Directory.

Microsoft CRM relies on the PrivUserGroup, ReportingGroup, SQLAccessGroup and UserGroup Security Groups, so we checked the details of the GUIDs in Active Directory against those registered in the Microsoft CRM database.

Viewing the Microsoft CRM values can be done by running the following in a query editor:
SELECT UserGroupID , PrivilegeUserGroupID, ReportingGroupID, SQLAccessGroupID
FROM OrganisationBase

To view those in Active Directory, you’ll need to use adsiedit.msc (part of the Windows Support Tools on the Windows Server installation CD-ROM or download it from http://download.microsoft.com, search for “adsiedit.msc”) and view the objectGUID value for each of these groups in hex.

The values from Active Directory need to be manipulated slightly, but they should match those in the Microsoft CRM database: first reverse the first four groups of characters, reverse the next two groups of characters, reverse the third group of two and copy and paste the fourth group of two and the final group of six (the last two groups are not reversed). Note that you do not reverse each pair of characters individually, but treat each pair as a group and reverse the groups as shown below.

So, for example:
objectGUID=0x 1x 2x 3x 4x 5x 6x 7x 8x 9x Ax Bx Cx Dx Ex Fx

Becomes:
GUID in database={3x2x1x0x-5x4x-7x6x-8x9x-AxBxCxDxExFx}

The values we found didn’t match, so it was just a simple SQL UPDATE statement to set them right:
UPDATE OrganizationBase
SET UserGroupID=’GUID’, PrivilegeUserGroupID=’GUID’, ReportingGroupID=’GUID’, SQLAccessGroupID=’GUID’

Where ‘GUID’ is the appropriate manipulated Active Directory objectGUID.


As an example, the specific values we had in Active Directory for the four groups were:
A7 7D FB B8 8B 85 8C 47 81 F0 36 D6 F0 89 8A F7
CB F5 E6 71 58 B6 19 4A BA 56 CC 49 67 0C 58 FF
C5 CC DE B9 8A 72 DD 48 A6 4E 5E 4D D4 64 DD 0E
18 F7 97 EF 41 B9 C8 4E AA F8 3B 2D CE 02 EC A2


So manipulated as above they became:
B8FB7DA7-858B-478C-81F0-36D6F0898AF7
71E6F5CB-B658-4A19-BA56-CC49670C58FF
B9DECCC5-728A-48DD-A64E-5E4DD464DD0E
EF97F718-B941-4EC8-AAF8-3B2DCE02ECA2


Our SQL statement looked like this:
UPDATE OrganizationBase
SET UserGroupID = ‘B8FB7DA7-858B-478C-81F0-36D6F0898AF7’
, PrivilegeUserGroupID = ’71E6F5CB-B658-4A19-BA56-CC49670C58FF’
, ReportingGroupID = ‘B9DECCC5-728A-48DD-A64E-5E4DD464DD0E’
, SQLAccessGroupID = ‘EF97F718-B941-4EC8-AAF8-3B2DCE02ECA2’

Many thanks to Brian Reid for his blog post here – http://www.c7solutions.com/blog/2008/04/crm-30-disaster-recovery.aspx which was really helpful in getting us through this issue.