Crm 2013 – No more ExtensionBase tables

So, Dynamics Crm 2013 is here, and there’s lots to say about the new UI, and the new features. But, many others are talking about these, so I thought I’d start with what may seem to be an obscure technical change, but it’s one that I welcome, and which is a significant contribution to the stability and performance of Crm 2013.

With Crm 3.0, Microsoft changed the underlying table structure so that any customisable entity was split across 2 tables; a base table that contained all system attributes, and an extensionbase table for custom attributes. For example, there was an accountbase and an accountextensionbase table. Each table used the entity’s key as the primary key, and the extensionbase table also had a foreign key constraint from the primary key field to the primary key in the base table. Each entity has a SQL view that joined the data from these table to make it appear as one table to the platform. As I understand it, the main reason for this design was to allow for more custom attributes, as SQL Server had a row-size limit of 8060 bytes, and some of the system attributes were already using ~6000 bytes.

The same table design was retained in Crm 4.0 and Crm 2011. However, Crm 2011 introduced a significant change to the plugin execution pipeline, which allowed custom plugins to execute within the original SQL transaction. This was a very welcome change that provided greater extensibility. However it did mean that the duration of SQL transactions could be extended, which means that SQL locks may be held for longer, which means potentially more locking contention between transactions. In very occasional circumstances, a combination of certain plugin patterns, the design of the base and extensionbase tables, and heavy concurrent use, could give rise to deadlocks (see below for an example).

Given this, I’m very glad that the product team retained the facility to have plugins execute within the original transaction (then again, it would be hard to remove this facility from us). It wouldn’t be realistic to ask customers to reduce concurrent usage of CRM, so the only way to reduce the potential deadlock issue was to address the design of the base and extensionbase tables. From my investigations (sorry, but I actually quite like investigating SQL locking behaviour), a substantial improvement could have been made by retaining the table design, but modifying the SQL view, but a greater improvement comes from combining the tables into one. An added advantage of this change is that the performance of most data update operations are also improved.

Deadlock example

Here are two SQL statements generated by CRM:

select
'new_entity0'.new_entityId as 'new_entityid'
, 'new_entity0'.OwningBusinessUnit as 'owningbusinessunit'
, 'new_entity0'.OwnerId as 'ownerid'
, 'new_entity0'.OwnerIdType as 'owneridtype'
from new_entity as 'new_entity0'
where ('new_entity0'.new_entityId = @new_entityId0)  

And

update [new_entityExtensionBase]

set [new_attribute]=@attribute0
where ([new_entityId] = @new_entityId1)
 
These were deadlocked, with the SELECT statement being the deadlock victim. The locks that caused the deadlock were:

• The SELECT statement had a shared lock on the new_entityExtensionBase table, and was requesting a shared lock on new_entityBase table
• The UPDATE statement had an update lock on the new_entityBase table, and was requesting an update lock on new_entityExtensionBase table

The likely reason for this locking behaviour was that:

• Although the SELECT statement was requesting fields from the new_entityBase table, it had obtained a lock on the new_entityExtensionBase table to perform the join in the new_entity view
• The UPDATE statement that updates a custom attribute (new_attribute) on the new_entity entity would have been the second statement of 2 in the transaction. The first statement would modify system fields (e.g. modifiedon) in the new_entityBase table, and hence place an exclusive lock on a row in the new_entityBase table, and the second statement is the one above, which is attempting to update the new_entityExtensionBase table

Both operations needed to access both tables, and if you’re very unlucky, then the two operations, working on the same record, may overlap in time, and cause a deadlock.

The new design in Crm 2013 solves this in three ways:

1. With just the one entity table, the SELECT statement only needs one lock, and does not need to obtain one lock, then request another
2. Only one UPDATE statement is required in the transaction, so locks are only required on the one table and they can be requested together, as they would be part of just one statement
3. Both operations will complete more quickly, reducing the time for which the locks are held

Of these 3 improvements, either no. 1 or 2 would have been sufficient to prevent deadlocks in this example, but it is gratifying that both improvements have been made. The third improvement would not necessarily prevent deadlocks, but will reduce their probability by reducing overall lock contention, and will also provide a performance improvement.

SQL Setup error "Registry properties are not valid under this context"

When using new versions of software (in this case SQL Server 2012 service pack 1), there's always the chance of a new, random error. In this case it was "Registry properties are not valid under this context" when attempting to add a component (the Full-text service) to an existing installation.

It seems like the issue comes down to the sequence of installing updates, both to the existing installation, and to the setup program. The specific scenario was:

• The initial install of SQL Server had been done directly from the slipstreamed SQL Server 2012 service pack 1 setup. At this time, the server was not connected to the internet, so no additional updates were applied either to the installed components, or the setup program
• When attempting to add the Full-text service, the server was connected to the internet, and had the option set to install updates to other products via Microsoft Update. When I started the setup (which used exactly the same initial source), the setup downloaded an updated setup, and also found a 145 MB update rollup that would also be installed
• Part way through the setup steps, setup failed with the message "Registry properties are not valid under this context"

The problem seemed to be that the setup program was using a more recent update than the currently installed components. Even though the setup program had identified updates to apply to the current components, it had not yet applied them before crashing out with the error.

The solution was to go to Microsoft Update and install the SQL Update Rollup, then go back and run SQL Setup to add the extra component. Interestingly, SQL Setup still reported that it had found this 145 MB rollup to apply, even though it was already installed

Possible SQL Gotcha - use of 'Not In' with NULLs and the customer attribute

I was recently putting together a bit of SQL to illustrate the use of a NOT IN clause for a forum answer, and got some unexpected results. The query was a relatively simple example; find all accounts with no associated opportunities. So, I tried this:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT accountid FROM FilteredOpportunity)

Nice, simple query, but it returned no data (and it should have done). However, the following works fine:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT customerid FROM FilteredOpportunity)

The only difference is the use of customerid instead of accountid in the subquery. If I'd have expected the first query to work instead of the second query, as customerid is a generated field (it's generated within the Opportunity via by the SQL function COALESCE(accountid, contactid)).

This all seems weird, but it comes down to what happens with nulls. An opportunity will be associated with one of an account, or a contact. So, the subquery 'SELECT accountid FROM FilteredOpportunity' could return a null (if you have an opportunity against a contact), but 'SELECT customerid FROM FilteredOpportunity' will always return non-null values. Don't ask me why, but the presence of nulls in the subquery cause the NOT IN query to misbehave.

One way to confirm this is with another variation on the query above, which also works:

SELECT name FROM FilteredAccount
WHERE accountid NOT IN (SELECT accountid FROM FilteredOpportunity WHERE accountid is NOT NULL)

This query explicitly excludes nulls from the results on the subquery, and so it works fine.

The main lesson I took from this is to always test for nulls in the subquery when using NOT IN; another lesson is to pay close attention when using attributes that represent the composite Customer data type in CRM

For reference, the reason why I was doing this is because this is a classic example of a query that cannot be done through FetchXML, and hence cannot be written with an Advanced Find in CRM. If the primary entity is an account, contact or lead then you have a manual workaround in CRM, for example:

• Create a marketing list, and populate it with all accounts
• Use Advanced Find to remove from the list all accounts that have an opportunity
• This will then leave you with a marketing list that contains all accounts without an opportunity

The CRM 4.0 Reporting Services Connector - how it works

The Dynamic CRM Connector for Reporting Services is a very useful component that avoids the need to configure Active Directory delegation (aka double-hop authentication) when multiple servers are used for the CRM, Reporting Services and SQL Server roles in a CRM implementation. In general, it is easy to install and use, but I'm always interested in how these things work.

How the Connector is installed and invoked
The connector is installed as a Data Processing Extension with SSRS. These extensions are registered within the rsreportserver.config file on the Reporting Server, as per the following snippet:

<Data>
<Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions" /> <Extension Name="OLEDB" Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions" />
<Extension Name="MSCRM" Type="Microsoft.Crm.Reporting.DataConnector.SrsExtConnection,Microsoft.Crm.Reporting.DataConnector" /> </Data>

All CRM reports with SSRS are configured to use a specific SSRS Data Source. When the Connector is installed, the Data Source is changed to use the MSCRM Data Processing Extension, instead of the default SQL Server extension. See images below:

Report properties showing the MSCRM Data Source


MSCRM Data Source using the SQL Server extension before the connector is installed


MSCRM Data Source using the CRM extension after the connector is installed.

There are 3 differences between these configurations:

1. The Connection Type, which specifies the extension
2. The Connection String is absent with the CRM connector. This is because the connector reads some of the database information from registry values that were created during its installation, and some from data passed to it when the report is run (see below)
3. The Credentials. With the SQL Server connector, standard Windows Integrated security is used - i.e. the user's AD credentials are used to connect to SQL Server. With the CRM connector, separate 'credentials' are passed to SSRS (again, see below)

What happens when a report is run
If you try to run a CRM report with the CRM connector installed, the connector will require some 'credentials', as per point no.3 above. This image shows what happens if you try to run a report from Report Manager:


Running a CRM report from Report Manager when the CRM connector is installed

These 'credentials' are not what they seem; rather they are a cunning way for the CRM platform to pass information about the current user to the CRM connector. The CRM connector expects the current user's systemuserid (a Guid) to be passed into the Log In Name box, and the organizationid (another Guid) to be passed into the Password box. These are not your login name and password.

As the report uses a data source that uses the CRM connector, the RS Report Server code calls the CRM connector code (the SrsExtConnection class in the Microsoft.Crm.Reporting.DataConnector assembly, as per the rsreportserver.config data above). The code will then:

1. Check that it is permitted to impersonate a CRM user. This checks that the identity the code is running under (which the identity of the ReportServer application pool, or the Reporing Services service, depending on the version of Reporting Services) belongs to the AD group PrivReportingGroup
2. Connect to the MSCRM_Config database to determine the correct MSCRM organization database, based on the organizationid that was passed in the 'credentials'
3. Connect to the relevant MSCRM organization database. Note that this is done (as was the previous step) using integrated security under the AD identity as per step 1 above
4. Use the SQL statement SET Context_Info to pass the calling CRM user's systemuserid into the Context_Info
5. Execute the SQL statement(s) within the report definition. The definition of all filtered views use the fn_FindUserGuid function to read the systemuserid from the Context_Info

What can you do with this information
One use is for troubleshooting. Checking the rsreportserver.config is a quick way to see if the connector is installed, and checking the configuration of the MSCRM Data Source will tell you if the connector is in use. Changing the MSCRM Data Source is a quick way to turn the connector on or off for test purposes.

You can also run the reports directly, rather than from CRM. Again, when troubleshooting I find it useful to run a report directly from Report Manager web interface. To do this with the connector, you need to enter the systemuserid and organizationid when prompted (see image above). These values can be read from the filteredsystemuser and filterorganization views respectively in the MSCRM database.

A further option is to run the reports via other means, such as Url Access, as described here (that article was written for CRM 3, see here for an update for CRM 4). To do this with the connector installed, you will also have to pass the systemuserid and organizationid on the query string. This is done using the following syntax:

&dsu:CRM=<systemuserid>&dsp:CRM=<organizationid>

What takes up most space in an MSCRM database

A frequent question is 'What is taking up most space in my MSCRM database'. My first step is to check which tables take up most space, for which I use the SQL script at the end of this post. The most common large tables, and reasons for them are:

1. AsyncOperationBase: This stores all asynchronous operations, and can get very large if completed ones aren't cleared out. You have to explicitly set the registry values AsyncRemoveCompletedWorkflows and AsyncRemoveCompletedJobs. These 2 values are covered in separate KB articles (http://support.microsoft.com/kb/968755 and http://support.microsoft.com/kb/957871). To clear existing records, use the SQL script in http://support.microsoft.com/kb/968520/
2. ActivityMimeAttachment: This stores email attachments
3. AnnotationBase: This stores other files (those associated with Notes)
4. ActivityPointerBase and/or ActivityPartyBase; These store activities and the associated parties respectively. Although individual record sizes aren't typically large, these tables typically have the largest number of records

For no.s 2 to 4 I've not given a way of clearing out space, as this would involve deletion of business data.

SQL Script
I normally use the following SQL Script to find what objects take up most space in a SQL database. It doesn't take account all possible storage in all SQL databases, but works fine for an MSCRM database

select OBJECT_NAME(object_id), SUM(reserved_page_count) / 128 as ReservedMB, sum(used_page_count) / 128 as UsedMB, MAX(row_count)
from sys.dm_db_partition_stats
group by object_id
order by SUM(reserved_page_count) desc

A bit of SQL - finding CRM attributes with no data

Over time I've been asked to review various existing CRM implementations. One thing I often like to check is whether there are any attributes that are not in use. To this end I created the stored procedure at the end of this post.

The stored procedure lists the attributes in an entity along with how many records contain data in that attribute. The parameters in the procedure allow you to limit the attributes to custom attributes, and to specify a maximum count of values (above which the attribute data won't be output).

Notes:

1. The procedure as written would be created in the MSCRM organisation database. You could create it in a different database, and add the databasename.schemaname. prefixes if you want
2. The procedure uses the supported route of querying the filtered view for an entity. If you have SQL dbo permission, and don't mind deleted records being included in the counts, then you can run the query against the base view, which is considerably faster. To do this, remove the string 'Filtered' from the procedure definition

Use the following SQL to create the stored procedure:

create proc pExcCheckColumnValues @entityName nvarchar(64), @customOnly bit = 0, @maxThreshold int = null
as
declare @attrName nvarchar(50), @sql nvarchar(1024)
declare cur cursor fast_forward for
select a.name from attribute a
join entity e on a.entityid = e.entityid
where e.name = @entityName and a.IsLogical = 0
and ((@customOnly = 1 and a.IsCustomField = 1) or isnull(@customOnly, 0) = 0)
create table #tmp (EntityName nvarchar(64), AttributeName nvarchar(50), ValueCount int)
open cur
fetch next from cur into @attrName
while @@fetch_status = 0
begin
set @sql = 'insert #tmp select ''' + @entityName + ''', ''' + @attrName + ''', count(*) from Filtered' + @entityName + ' where ' + @attrName + ' is not null'
+ case when @maxThreshold is not null then ' having count(*) <= ' + cast(@maxThreshold as nvarchar) else '' end
exec (@sql)
fetch next from cur into @attrName
end
close cur
deallocate cur
select * from #tmp
go

To give an example of how to use the procedure, the following will return all custom attributes of the account, where no more than 10 account records have a value in this field:

exec pExcCheckColumnValues @entityName = 'account', @customOnly = 1, @maxThreshold = 10

Analysing SQL Queries in CRM environments

I've another post here on the Dynamics CRM team blog describing some techniques I use to make effective use of SQL Profiler and SQL Management Studio. This post also references a tool I published here on the MSDN Code Gallery

New Code Gallery resource - sp_executesql parser

I've added a new code resource to the MSDN code gallery. This is a simple tool to parse calls to sp_executesql. The reason for it is that the SQL tools cannot show the execution plan for calls to sp_executesql, which is a pain when doing performance analysis of these queries. So, I created a tool that replaces the manual editing process.

Soon, I'll post a more detailed description of SQL performance analysis for CRM implementations, which will reference this tool.

Reports on CRM Privileges - Update to view by User

I've added 2 reports to the resource on the MSDN Code Gallery. These show cumulative privileges by user across their roles

Reading RDL Definitions directly from a ReportServer database

Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

Some SQL Snippets

Over time (don't ask how long – suffice to say I first used Microsoft SQL Server on OS/2) you pick up a fair amount of useful SQL Server knowledge. This post is intended to be a random collection of snippets that I use and remember, and I expect to add to the post periodically as I encounter further uses for SQL knowledge. So, in no particular order...

Finding SQL objects that contain a particular string
The definition of SQL objects can be accessed via the sys.syscomments view in the SQL database, and can be queried. The following example returns the name of objects that contain 'Test' somewhere within the definition. The object_name function is a quick way to get the name of an object from its id – the other way is to join to the sys.objects view.

select distinct object_name(id) from sys.syscomments where text like '%Test%'

Note that this only works if the SQL object definition was not encrypted with the WITH ENCRYPTION option

Granting Permissions to a set of objects
I've yet to find a good user-interface in SQL for setting permissions on a set of objects quickly, so I tend to use SQL commands. The following procedure shows how to use a cursor to iterate through a set of objects and execute a dynamically-built GRANT statement on them

declare cur cursor fast_forward for

select name from sys.objects
where type = 'V' and name like 'vw_%' -- Get all views, beginning vw_

declare @obj sysname, @sql nvarchar(2000)
open cur
fetch next from cur into @obj
while @@fetch_status = 0
begin
set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'
-- grant select permission to public
exec (@sql)
fetch next from cur into @obj
end
close cur
deallocate cur

Outputting stored procedure information to a table
There are cases when you might want to use the results of a stored procedure in a table structure for future processing. There's not an EXECUTE INTO statement but you can use INSERT ... EXECUTE. You can also use this with dynamically constructed SQL, using EXECUTE (@sql). The following example uses both EXECUTE syntaxes, andshows how to iterate though the names of 'tables' from a linked server – this is used to query Excel spreadsheets where there is a dynamic range of identically structured worksheets

create
table #excelsheets -- Store names of spreadsheets in Excel
( TABLE_CAT sysname null
,TABLE_SCHEM sysname null
,TABLE_NAME sysname not null
,TABLE_TYPE sysname null
,REMARKS nvarchar(255) null )

insert #excelsheets execute sp_tables_ex 'EXCELDYNAMIC'
-- EXCELDYNAMIC is a linked server

create table #tmp
-- Temporary storage of data, so results can be output as one result set
( TABLE_NAME sysname
,[Month] int
,[Target] decimal(10,2) )

declare cur cursor fast_forward
for select TABLE_NAME from #excelsheets
declare @tbl sysname, @sql nvarchar(4000)
open cur
fetch next from cur into @tbl
while @@fetch_status = 0
begin
-- Build dynamic SQL statement. It would be nice to pass the statement as a parameter to OPENQUERY, but that's not permitted
set @sql = 'Select ''' + @tbl + ''' as TABLE_NAME, [Month], [Target] FROM EXCELDYNAMIC...[' + @tbl + ']'
insert #tmp exec (@sql)
fetch next from cur into @tbl
end
-- Cleanup and output results
close cur
deallocate cur
select * from #tmp
drop table #tmp
drop table #excelsheets

SQL Server: The instance name must be the same as computer name

This is something I’ve posted about on newsgroups, but one of my colleagues encountered it recently, and I think it deserves a blog entry.


The CRM Environment Diagnostics Wizard may throw the error ‘The instance name must be the same as computer name’. The most common cause of this is if the SQL Server has been renamed after SQL Server was installed. The reason is that, at installation time, SQL Server stores the computer name in a system table, sysservers. This information is not updated when the computer is renamed, and the error from the CRM Environment Diagnostics Wizard indicates that the entry in sysservers does not match the current computer name.


You can diagnose and resolve this by using some SQL system stored procedures. One of them lists the data in sysservers, the other 2 allow you to modify the data to reflect the current machine name.


To check if this is the issue, use SQL Management Studio (or Query Analyzer for SQL 2000) to execute the following query:
sp_helpserver
This will return output like the following:
Name,network_name,status,id,collation_name,connect_timeout,query_timeout
ORGNAME,ORIGNAME,rpc,rpc out,use remote collation,0,null,0,0


If the value in the name column does not match the current computer name, then you have to use the following SQL stored procedures to fix the problem. Note that sp_helpserver normally returns one record, but can return more records if you have configured linked servers. If this is the case, it is the row with id=0 that matters.


To change the information you have to first remove the incorrect record, then add the correct one, with the following queries:
sp_dropserver ‘ORIGNAME’ -- where ORIGNAME is the name returned by sp_helpserver
sp_addserver ‘CURRENTNAME’, ‘LOCAL’ – where CURRENTNAME is the current computer name



If you use named instances, refer to them in the form SERVERNAME\INSTANCENAME. It may then be necessary to restart SQL Server after these changes, but I'm not sure of this. It can't harm though if you can.



There is a KB article about this here. This descibes a similar solution, but be warned of a couple of minor issues with the solution - it fails to specify that quotes are required around the parameters to sp_dropserver and sp_addserver, and I have a feeling (though can't provide concrete evidence) that running sp_helpserver is more reliable than select @@servername.

Excel Data Type issues with OLEDB and SSIS

I recently met an annoying issue when reading Excel data in SSIS. The issue was when a column contained a mix of data-types – in this case string and numeric information. The problem comes from the fact that Excel does not have a concept of column data-types, and the issues in SSIS come from the way the OLE-DB provider for Excel attempts to resolve this.

I found the information I needed in an excellent blog post here, and I won’t repeat the content here. One additional point that is specific to SSIS is how to add the IMEX extended property to the connection string. In Visual Studio 2005 (I’ve not checked other versions) you can only do this by editing the ConnectionString property directly in the Properties window – the dialog box doesn’t offer this option. See the image below.

SQL Linked Server by IP Address

A recent SQL Server problem I had was to setup a Linked Server from one SQL 2005 server to another, with a couple of specific requirements: The destination server could only be accessed by IP address, but the customer wanted to be able to identify the server by name in SQL code.

This should have been simple, but as I'm blogging about it you may be able to guess that it wasn't quite so straightforward...

It started promisingly. This is how to add a SQL linked server via SQL Management Studio if you can access it by name:

If you want to specify the other parameters, it seemed to make sense to specify 'Other data source', and 'Microsoft OLE DB Provider for SQL Server': That was easy... till I tried to query the server, which failed with a timeout. Then, when I opended the properties of the Linked Server, the Data Source information had gone:

If you specify the OLE DB Provider for SQL Server, then the Data Source is lost.

To get round this, I had to add the linked server via the sp_addlinkedserver system stored procedure, and specify a provider of 'SQLNCLI' (the native SQL client), and not SQLOLEDB (the OleDb provider for SQL Server). The following example shows how to do it.

sp_addlinkedserver
@server = 'REMOTESQL' -- Name used in queries
, @provider = 'SQLNCLI' -- SQL Native Client
, @srvproduct = '' -- Cannot be null, and if 'SQL Server' then you cannot specify the datasrc
, @datasrc = 'SERVER=10.0.0.1' -- IP Address of the server
, @catalog = 'MYDB' -- database name on the server, if required

SQL Timeouts in CRM - Generic SQL Error

I often find myself answering forum posts about SQL errors (the most common error is 'Generic SQL Error'). By far the most likely cause of this error is a timeout when accessing SQL server. If this is a case your options are to increase the timeout, or to try and ensure the query does not take so long.

Increase the Timeout
The SQL timeout is controlled by a registry value on each CRM server:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\OLEDBTimeout

This value does not exist by default, and if it does not exist then the default value will be 30 seconds. To change it, add the registry value (of type DWORD), and put in the value you want (measured in seconds). Then you have to recycle the CrmAppPool application pool for the change to take effect (this is a step most Microsoft documentation omits to mention); do this we IISReset, or less drastically via iisapp.vbs

Reduce the time taken by the query
This may not be so simple, as you may have little control over the query. If the query is run as a result of your code (e.g. through a RetrieveMultiple request), then you may be able to make useful changes. For example, RetrieveMultiple requests on activities are not necessarily processed very efficiently by CRM (the problem is the way that is accesses the activity parties), and I've been able to make significant improvements by using a FetchXml query instead, which gives closer control over the joins used.

Otherwise, the other query optimisation option is to add indexes in SQL Server. This is a massive topic in its own right (I used to deliver 5 day training courses just on this topic), so I'm not going to go into detail here. The general steps are:

1. Identify the SQL query that takes the time - I use CRM tracing for this - http://support.microsoft.com/kb/907490
2. Use the SQL Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes

There are 2 important things to take into account:

1. Although adding an index may improve the performance of one query, it can adversely affect other SQL operations - most obviously data updates. There is no easy solution to this, though the SQL Profiler can help you if you capture and analyse a representative sample of SQL operations
2. Some out-dated CRM documentation suggested that it is unsupported to add indexes to the MSCRM database. However, adding indexes is supported, providing the index does not implement any constraints (i.e. it's not a UNIQUE index)

Stored procedures in SSIS; the need for nocount

I've been doing a lot of SSIS work lately, and met an odd problem when using stored procedures in an OLEDB source. The general idea was to have a stored procedure that did some data modification, then finished with a SELECT statement to output logging information.

When previewing the OLEDB source everything was fine, but running the package would give the following error in the pre-execute phase:
'A rowset based on the SQL command was not returned by the OLE DB provider'

It took a while to work out, but eventually the problem came down to the way that SQL Server returns information about the number of rows affected. The resolution is to put the following SQL statement at the start of the stored procedure defnition to prevent output of the number of rows:

SET NOCOUNT ON

After that it was all fine.

Syntax issues using SQL Execute As and Revert statements

SQL Server 2005 provides a means to switch user context via the EXECUTE AS statement. This can be very useful when querying CRM Filtered views, as they use the SQL user context to determine what data to return and if queried using the CRM service account they return no data. An example of how this can be used in CRM callouts can be found here. I've also used a similar approach when running SSIS packages programmatically from a web application, as ASP .Net impersonation doesn't work properly in SSIS packages.


However, when testing this further, and checking the behaviour of REVERT, I found this only works correctly if you separate your SQL statements with semi-colons. Take the following 2 examples:


Execute as user='crmdom\admin'
select * from filteredaccount
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql)
revert


Of these 2, the former appears to work, but the REVERT statement doesn't get processed, and the latter gives an 'incorrect syntax' error. But, if you add a semi-colon after the statement before REVERT, then they work fine:


Execute as user='crmdom\admin'
select * from filteredaccount;
revert


declare @sql nvarchar(2000)
Execute as user='crmdom\admin'
set @sql = 'select * from filteredaccount'
exec (@sql);
revert


Now, that is not what I expected in SQL syntax. I always thought semi-colons were an optional statement separator, but apparently not -though I expect this is a bug rather than by design.


Note, this behaviour happens on build 2047 of SQL 2005, whereas it works without semi-colons on build 1399, but that doesn't stop me using them as a matter of course with impersonation.