Archive for the ‘SQL Server’ Category

Retrieving Random Rows From a Database Table

Occasionally, I’ve needed to retrieve a random number of records from a database table for testing and found that the NEWID() function in SQL Server works well. By using the NEWID() function in the ORDER BY clause and setting a TOP xx value, a random number of records can be returned from a table. There are similar approaches for each of the major databases (Oracle, PostgreSQL, MySQL, etc). The example below is using T-SQL and works for both SQL Server and Sybase.

SELECT TOP 5 lastname, firstname, phone
FROM dbo.customers
ORDER BY NEWID()

In MS Access you can achieve the same results using the RND() function in the ORDER BY clause with a SEED (an AutoNumber or DateTime column seems to work just fine). The RND() without a parameter returns a value between 0 and 1, but doesn’t regenerate for each row in the query and you end up with the same value for each row. The example below uses an AutoNumber column as the seed.

SELECT TOP 5 lastname, firstname, phone
FROM dbo.customers
ORDER BY RND([key]);

Tags:


Enabling/Using Package Configurations (SSIS)

If you can accomplish all of your tasks with one SSIS package, I’m happy for you – but sometimes more than one is necessary. If you ever find yourself with multiple packages with similar properties (variables, connection managers, executables, etc.) it may be in your best interest to use a Package Configuration. This will save you some time down the road if you decide to create another package, or update properties of the existing ones. What a package configuration will allow you to do is make properties of the package external – saved as either an XML configuration file, environment variable, registry entry, or a few others. Once external, you can setup all or some of your packages to use the configuration – this way if changes need to be made, you can do so without having to update each and every package. In this post I will be creating a XML package configuration that contains my connection managers. Recently I did some SSIS cleanup for a client; they had 25+ packages, all containing the same connections. I recreated their database schema locally for testing purposes and enabled a package configuration in each package. By doing so, I was able to update the connection strings in my configuration file to use my local databases and make changes to the packages as needed. Upon completion I could simply change the connection strings back and deliver back to the client. At the same time, if the client ever moved their data to a different location, they could follow the same steps without having to edit any of their packages. The two connections I have are ‘Duck’ and ‘Goose’:
SS_1
To create a configuration, select Package Configurations from the SSIS dropdown on your menu bar:

SS_2
Check the box to enable package configurations, and click Add.

SS_3

For configuration type, select ‘XML Configuration File’. Make sure the radio button for ‘Specify configuration settings directly’ is selected and click Browse. Navigate to a common directory (I used C:\PackageConfigurations) and name your file ‘Connections.dtsConfig’, click Save.

SS_4

When you click Next you will be brought to a screen where you can select what exactly will be included in your configuration. As I stated before we are just going to add our connection managers. Check the box for each of your connections (or expand to select only specific properties), and click Next.

SS_5

Name your configuration, and click Finish. You’ve successfully created a configuration – your package is currently using it. So now let’s say you have another package that contains Duck and Goose connections, how do we set it up to use our new package configuration? Open the package and again click SSIS > Package Configurations. Make sure the ‘Enable Package Configurations’ checkbox is checked, and click Add. Browse to your configuration file, and click Next. When you select an existing configuration you will be prompted to either reuse or overwrite the file, click ‘Reuse Existing’. Click Finish, and then Close. Your package is now using the same configuration file – you can open the XML file in any editor and change the connection strings and it will be updated across all packages that use the configuration. Keep in mind this can be used for variables and executables as well, making the configurations even more flexible!

Tags: , , ,


Query MS CRM XML Entity with XQuery and LINQ

In our company we have been working on a large MSCRM 2011 project. Last week, I was asked to create a report listing all the visible fields for each entity in the solution. During my search, I found a few different ways to do it, but the most fun way was querying the XML.
The XML is created when the solution is exported. It’s normally several levels deep and even when the structure would depend on the solution the XML listed below is an example of how it would looks like.

<Entities>
    <Entity>
      <Name LocalizedName="Account" OriginalName="Account">Account</Name>
      <ObjectTypeCode>1</ObjectTypeCode>
      <EntityInfo>
        <entity Name="Account">
          <LocalizedNames>
            <LocalizedName description="Account" languagecode="1033" />
          </LocalizedNames>
          <LocalizedCollectionNames>
            <LocalizedCollectionName description="Accounts" languagecode="1033" />
          </LocalizedCollectionNames>
          <Descriptions>
            <Description description="Business." languagecode="1033" />
          </Descriptions>
   <attributes>
		<attribute PhysicalName="Category">
			…
	<attribute>
   </attributes>
</entity>
      </EntityInfo>
   <FormXml>
        <forms type="main">
          <systemform>
            <formid>{b053a39a}</formid>
            <form>
              <hiddencontrols>
                <data id="addressid" datafieldname="addressid" classid="{}" />
                <data id="address2” datafieldname="address2" classid="{}" />
              </hiddencontrols>
		  <tabs>
                <tab name="general" verticallayout="true" id=”" IsUserDefined="0">
                  <labels>
                    <label description="General" languagecode="1033" />
                  </labels>
                  <columns>
                    <column width="100%">
<sections>
  <section name="account information" showlabel="false" showbar="false" id="{}" IsUserDefined="0">
                          <labels>
                            <label description="Information" languagecode="1033" />
                          </labels>
                          <rows>
                            <row>
                              <cell id="{}">
                                <labels>
                                  <label description="Name" languagecode="1033" />
                                </labels>
                                <control id="name" classid="{}" datafieldname="name" />
                              </cell>
                              <cell id="{}">
                                <labels>
                                  <label description="Phone" languagecode="1033" />
                                </labels>
                                <control id="telephone1" classid="{}" datafieldname="telephone1" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{}" showlabel="false" >
                                <labels>
                                  <label description="Contact" languagecode="1033" />
                                </labels>
                                <control id="primarycontactid" classid="{}" datafieldname="primarycontactid">
                                  <parameters>
                                    <FilterRelationshipName>customer_accounts</FilterRelationshipName>
                                    <DependentAttributeName>parentcustomerid</DependentAttributeName>
                                    <DependentAttributeType>account</DependentAttributeType>
                                    <AllowFilterOff>true</AllowFilterOff>
                                  </parameters>
                                </control>
                              </cell>
                              <cell id="{}" showlabel="true" >
                                <labels>
                                  <label description="Other Phone" languagecode="1033" />
                                </labels>
                                <control id="telepho" classid="{}" datafieldname="telep" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{}">
                                <labels>
                                  <label description="Parent" languagecode="1033" />
                                </labels>
                                <events>
                                  <event name="setparams" application="true" active="true">
                                    <InternalHandlers>
                                      <Handler handlerUniqueId=" " enabled="true" />
                                    </InternalHandlers>
                                  </event>
                                </events>
                                <control id="parentaccountid" classid="{}" datafieldname="parentaccountid" />
                              </cell>
                              <cell id="{ }">
                                <labels>
                                  <label description="Fax" languagecode="1033" />
                                </labels>
                                <control id="fax" classid="{}" datafieldname="fax" />
                              </cell>
                            </row>
                            <row>
                              <cell id="{ }">
                                <labels>
                                  <label description="E-mail" languagecode="1033" />
                                </labels>
                                <control id="emailadd" classid="{}" datafieldname="emailadd" />
                              </cell>
                              <cell id="{ }">
                                <labels>
                                  <label description="Web Site" languagecode="1033" />
                                </labels>
                                <control id="websiteurl" classid="{}" datafieldname="websiteurl" />
                              </cell>
                            </row>
                          </rows>
                        </section>
             </sections>
                    </column>
                  </columns>
                </tab>
</tabs>
</form>
     </systemform>
        </forms>
      </FormXml>
    </Entity>
</Entities>


'

Take into consideration that we have several entities and those entities have multiple tabs, sections, controls and labels. The listing above is just an example of what MS CRM exports in XML format. It shouldn’t be consider as a real entity. Having said that, lets continue with the real fun.

My task was to find all the visible and enabled fields in the solution. I also needed to know, if the fields belong to the Account entity or any other entity and I just needed to retrieve the fields from the Main form not the Mobile form.

Before we go further, is important to say that I had to retrieve the Label description, but I had to check the cell element, “Showlabel” attribute which controls the visibility as you can see on the next example.

<cell id="{}" showlabel="true" >
  <labels>
    <label description="Other Phone" languagecode="1033" />
  </labels>
  <control id="telepho" classid="{}" datafieldname="telep" />
 </cell>


'

Like I said before, there are different ways to do this. Here are a couple examples.
The first I used was XQuery and FLWOR expressions.

Basically it searches through the XML verifies if the “showlabel” attribute is either not there or set to true and retrieves the names.


SELECT ent.vals.value('@OriginalName', 'varchar(max)') AS Entity, ent.vals.value('@datafieldname', 'varchar(max)') FieldName
FROM @xml.nodes('for $en in //Entity
return ($en/Name,(for $q in ($en/FormXml/forms/systemform/form/tabs/tab/columns/column/sections/section/rows/row/cell)
where (empty($q/@showlabel) or ($q/@showlabel=&amp;quot;true&amp;quot;))
return $q/control)) ') ent(vals)

'

It returns the Entity name in one column follow by the Fields. It looks like this:

Another way to query is using Linq to XML. The statement looks like this:


var xrmEntities =   from e in doc.Elements(&amp;quot;Entity&amp;quot;)
                        from c in e.Elements(&amp;quot;FormXml&amp;quot;).Elements(&amp;quot;forms&amp;quot;).Elements(&amp;quot;systemform&amp;quot;).Elements(&amp;quot;form&amp;quot;)
                                    .Elements(&amp;quot;tabs&amp;quot;).Elements(&amp;quot;tab&amp;quot;)
                                    .Elements(&amp;quot;columns&amp;quot;).Elements(&amp;quot;column&amp;quot;)
                                    .Elements(&amp;quot;sections&amp;quot;).Elements(&amp;quot;section&amp;quot;)
                                   .Elements(&amp;quot;rows&amp;quot;).Elements(&amp;quot;row&amp;quot;)
                                    .Elements(&amp;quot;cell&amp;quot;)
                        where (string)c.Attribute(&amp;quot;showlabel&amp;quot;) == &amp;quot;true&amp;quot; || c.Attribute(&amp;quot;showlabel&amp;quot;) == null
                        from l in c.Elements(&amp;quot;labels&amp;quot;)
                        let formName = e.Element(&amp;quot;Name&amp;quot;).Attribute(&amp;quot;LocalizedName&amp;quot;).Value
                        let fieldName = l.Element(&amp;quot;label&amp;quot;).Attribute(&amp;quot;description&amp;quot;).Value
                        orderby formName, fieldName
                        select new {Entity = formName, Field = fieldName };
    xrmEntities.Count().Dump();
    xrmEntities.Dump();

//”doc”  variable loads the XML file.

The result returned by Linq is probably easier to understand. It looks like this.

Either way returns the Fields Names so, It’s up to you to decide what to use Linq or XQuery. Both of them are good things to learn as a developer.

Tags: , , , , , , , ,


Denali New T-SQL features.

SQL Server Denali comes with a number of new features that will help to make our life easier or more complicated depending on your point of view. However, I think it will make our life a lot easier.

A few of the new features that I will talk about today are the Try/Catch block, execute a store procedure With Result Sets and the IIF function.

TRY/CATCH

It was originally introduced in SQL 2005. However it lacked flexibility and we were not able to re-throw the error like we do it in C# or VB.net. However, now we can using “THROW”. Here is an example of how it works:

DECLARE @REG VARCHAR(50);
DECLARE @MORE INT;

	BEGIN TRY
		SELECT @REG= RegionDescription FROM DBO.Region R
		INNER JOIN Territories T ON T.RegionID = R.RegionID	 

		SELECT @MORE= @REG

	END TRY

	BEGIN CATCH
		PRINT 'You need to cast the varchar before assign it to a int';
		THROW;

	END CATCH

This is just a simple example of how it works, however there are many different ways to format the message so you can display or return the right message for your code or UI. You can find additional information at Microsoft

WITH RESULT SETS

Another new feature that was called to my attention is the execution of stored procedures “WITH RESULT SETS”. With this new feature you won’t need the high cost efficient functions. Something else that is nice with this feature is that you can rename the columns returned by the stored procedure. Also if you were using a temp table in order to see the data you will not need it any more with the new “WITH RESULT SETS.” Here is an example:

-- Create the store procedure in the Northwind database
CREATE PROCEDURE [dbo].[spTerritories] (@Region int)
as
BEGIN
	SELECT TerritoryID, TerritoryDescription, RegionID FROM Territories
	WHERE RegionID = @Region
END
GO

EXEC spTerritories @Region = 1
WITH RESULT SETS
(
	(
		PlaceID varchar(100), 
		PlaceDescription text,
		Region float
	) 
) 	

Now, regarding cost, the new approach is a lot more efficient than the previous versions. In fact if you run something like this:

DECLARE @TestTable TABLE (TerritoryID varchar(MAX), TerritoryDescription text,
    RegionID float )

INSERT INTO @TestTable 
EXEC spTerritories @Region = 1

SELECT * FROM @TestTable

It will cost you around 5 times more than just executing the store procedure With Result Sets. Take a look at the image below.

The blue square is the execution of the store procedure with the result sets that was 14% of the batch. The red squares make a total of 14 + 57 + 14 = 85% of the whole batch, so there is no doubt that using WITH RESULT SETS is more efficient that the old way.

IIF Function

Another new feature in SQL Denali are the built in functions. I will not write about all of them but you can find great examples of them at Pinal Dave’s blog. You can find a reference to his blog at the end of this post.

The one that caught my attention was the new IIF function. It basically replaces the Case statement for simple scenarios. Obviously if you have a long nested IIF function the CASE may be your best option. However the new IIF improves the code a lot.

Here are a couple of examples using tables from the Northwind database.

SELECT IIF(RegionID &gt;= 2, 'Good Region', 'The best') as RegionScore, 
		TerritoryID, TerritoryDescription, RegionID 
FROM  dbo.Territories 

Another example would be something like this. In this case I’m nesting the IIF function.

SELECT IIF(RegionID &gt;=2,
		IIF(
			(SELECT e.FirstName
			FROM  Employees  e
			LEFT JOIN EmployeeTerritories et 
                            on e.EmployeeID = et.EmployeeID
			WHERE et.TerritoryID =ttr.TerritoryID) is null, 'No Data',
			(
			        SELECT  e.FirstName 
				FROM Employees  e
				LEFT JOIN EmployeeTerritories et 
                                    on e.EmployeeID = et.EmployeeID
				WHERE et.TerritoryID =ttr.TerritoryID
		       )
			),'No Employee Assigned') as RegionScore, 
		TerritoryID, TerritoryDescription, RegionID 
FROM  dbo.Territories  ttr

Some of the other built-in functions are specified on Pinal’s website. You can take a look at them Clicking Here. Microsoft is another reference for any information I wrote on this post.

Tags: , , , , , , , ,


New SQL “Denali” is hereNuevo SQL Denali esta aqui

Technology users are growing around the world, as well as data and device dependency , whether it be pc, laptop , tablet or smart phone. Even when the economy doesn’t look very promising, generally speaking there are some other sectors that are not being hit as hard as others, technology is one of them. In fact many websites such as CNMBC.com, USNEWS.com and others are ranking technology carriers as the most growing carriers in the market right now. Demand on this career is growing exponentially as well as tools to supply such demand.

One of the new tools just releases for testing is “Denali”. This is new version of Microsoft SQL Server. This new version is cloud-ready information platform and looks pretty processing. It has the ability to interact with OData and sync information between multiple platforms.

Microsoft is also promising that the OS patching and downtime can be reduced significantly by 50-60% less by running SQL server on Windows Server Core.

Regarding the performance, Microsoft is introducing In-Memory Column Store or better known as “Apollo” on this version. Basically Apollo brings together the Vertipaq technology that was developed in Analysis Services and a new quory excecution paradigm called batch processing to provide speed up for common data warehouse queries.

Some other features that new Denali brings up are:

  • “Crescent”, is a reporting services project. According to Microsoft, it is easy to use and provides with highly interactive web-based data exploration, visualization, and presentation experience to users of all levels.
  • BI Semantic Model which is a single model for user with multiple ways of building business intelligence solutions.
  • “Juneau”, is the project for SQL Server Developer Tools. Actually “Juneau” unifies the development for database development across Database, BI and web and supports SQL Server and SQL Azure.

In order to install Denali there are some requirements you have to take in consideration:

  • First Denali does not install or enable Windows PowerShell; however Windows PowerShell 2.0 is an installation prerequisite.
  • .Net 3.5 PS1 is a requirement and you have to enable .NET framework 3.5 SP1 before you install Denali if you are installing it on a Windows Server 2008 R2 .Net 4.0 is a requirement too.

Regarding hardware and OS Denali can be installed on a Windows Server 2008 and 2008 R2 SP1, Windows 7 SP1 and Vista SP2. It requires a minimum of 1 GB on memory and a 64 version requires a 1.4Ghz process or a 1.0 GHz process if you install the 32-bit version.

Installing Denali was not a big problem. However it took more than 30 minutes installing.

When I start using it I didn’t noticed a big difference between this version and 2008 R2 Version. Here is an email of the new Management Studio UI.

The new UI is powered by Visual Studio so you will fell that you are working on it instead of regular SQL Management Studio.
In future blogs I will discuss about new TSQL features and Reporting tools as well as BI tools that Denali provides.

If you are interested on download and Install Denali you can download it from: Microsoft
Also if you want to learn more about it this video may help you. It talks about new Denali features. The speaker is Dandy Weyn and I can tell that he does a great job presenting the product.
Take a look at it. Microsoft Technet Videos

Tags: , , , ,


Table-Valued Parameters

Being able to pass a table as a parameter to a stored procedure is a welcome addition to SQL Server 2008. In previous versions you had to use an XML document or a delimited string, both of which required additional processing. Below is a simple example that populates the Shippers table in the Northwind database. See table-valued parameters in the MSDN documentation for more information.

-- Create a new user-defined table type that matches
-- the Shippers table in the Northwind database
-- (excluding the PK/Identity field).
CREATE TYPE dbo.ShipperTable AS TABLE (
    CompanyName     NVARCHAR(40) NOT NULL
    ,Phone          NVARCHAR(24) NULL
)
GO

-- Create a new stored procedure with a table valued parameter
-- to insert the data (you must specify the READONLY clause).
CREATE PROCEDURE dbo.AddShipper (
    @Shipper    ShipperTable READONLY
)
AS
    INSERT INTO dbo.Shippers
        SELECT  CompanyName, Phone FROM @Shipper
GO

-- Create a variable based on the new table type
DECLARE @LocalShipper ShipperTable

-- Populate the table variable using the table value constructor
-- syntax. In the value expression each list must be enclosed in
-- parentheses and separated by a comma.
INSERT INTO @LocalShipper VALUES
 ('DHL', '(480) 555-1234')
,('FEX', '(480) 555-5678')
,('UPS', '(480) 555-9012')

-- Insert the new shippers using the table variable as a parameter
EXEC AddShipper @Shipper = @LocalShipper

SELECT * FROM dbo.Shippers

-- Clean up
DELETE FROM dbo.Shippers WHERE CHARINDEX(CompanyName, 'DHL,FEX,UPS') > 0
DROP PROCEDURE AddShipper
DROP TYPE ShipperTable

Tags: , , , ,


MS CRM SQL Server Traces – CRM Brings the Noise – and I Filter it!MS CRM – Traza de SQL Server – Viene haciendo ruido – y yo la Filtre

Every once in a while when doing customization or development for MS CRM I need to run a SQL trace. Anyone who has ever watched a trace of SQL Server for MS CRM (or any enterprise level application, for that matter) knows that a lot of SQL gets generated.

In my case, I usually don’t want to see all the “background noise” SQL that the app generates for it’s internal processing – I’m more interested in what SQL is running to select, insert or update records. SQL Server has the option to add filters to filter out items you don’t want to see in a SQL trace.

Assuming you already know how to create a filter in SQL Server, you can add each of the following statements as a filter to the column “TextData” and filter out a lot of things that you would prefer not to see in the results. I hope it helps, of course, but I warn you that if I am filtering out something that you actually do want to see in the results, don’t blame me:)

NOTE: This is for CRM 4.0 and has not been testing in MSCRM 2011. I would say that it probably will not work very well.

%exec sp_executesql N'With TopSuspendedAsyncOperations(AsyncOperationId)%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%exec GetDBVersion @DBVersion=@p1 output%
%exec sp_reset_connection%
%exec sp_executesql N'update AsyncOperationBase%
%-- network protocol: LPC%
%exec sp_executesql N'SELECT OrganizationId, EventId, EventData, CreatedOn FROM Notification%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId, subscription.AsyncOperationId, subscription.EntityName,%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%and DeletionStateCode = 0',N'@lockedState int,@hostId nvarchar(62),@pausingStatus int,@cancelingStatus%
%exec sp_executesql N'SELECT Id FROM DataEncryptionKey  WHERE (((IsConfigurationRow = @IsConfigurationRow0))%
%exec sp_executesql N'SELECT Id, ColumnName, BigIntColumn, IntColumn, SmallIntColumn, TinyIntColumn,%
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey  WHERE (((Id = @Id0)) ) AND (IsDeleted =
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey%
%exec sp_executesql N'select sdkmessagefilter0.PrimaryObjectTypeCode as ''primaryobjecttypecode'',%
%exec sp_executesql N'SELECT LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'select sdkmessage0.SdkMessageId as ''sdkmessageid'' from SdkMessage%
%exec sp_executesql N'update WorkflowLogBase set <a href="mailto:ModifiedBy=@ModifiedBy0">ModifiedBy=@ModifiedBy0</a>, <a href="mailto:ModifiedOn=@ModifiedOn0">ModifiedOn=@ModifiedOn0</a>, Message=NULL,%
%exec p_GetFullName @organizationid=%
%exec sp_executesql N'select workflow0.UIData as ''uidata'', workflow0.IsCrmUIWorkflow as ''iscrmuiworkflow'',%
%exec sp_executesql N'select systemuser0.AccessMode as ''accessmode'', systemuser0.SystemUserId%
%exec sp_executesql N'select workflowlog0.AsyncOperationId as ''asyncoperationid'',%
%exec sp_executesql N'select workflow0.WorkflowId as ''workflowid'', workflow0.PrimaryEntity as ''primaryentity'',%
%exec sp_executesql N'delete from WorkflowCompletedScopeBase%
%exec sp_executesql N'SELECT Id, KeyType, ActiveKeyId, Enabled%
%exec sp_executesql N'SELECT Id, ScaleGroupId, KeyType,%
%select sdkmessageprocessingstepimage0.CreatedOn as ''createdon''%
%exec sp_executesql N'SELECT Label AS label,%
%SELECT EntityRelationshipId AS entityrelationshipid,%
%SELECT CascadeAssign AS cascadeassign,%
%exec sp_executesql N'SELECT EntityId AS entityid,%
%exec sp_executesql N'SELECT LogicalName AS logicalname,%
%exec sp_executesql N'SELECT T1.LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'SELECT RelationshipId AS relationshipid,%
%exec sp_executesql N'IF EXISTS (SELECT 1 FROM dbo.MatchCode%
%SELECT MatchCode FROM dbo.MatchCode%
%exec sp_executesql N'update WorkflowWaitSubscriptionBase%
%exec sp_executesql N'select distinct AsyncOperationId from WorkflowWaitSubscriptionBase%
%exec sp_executesql N'SELECT Id, HelpServerUrl, InstallOn, IsRegistered,%
%exec sp_executesql N'insert into AsyncOperationBase%
%exec sp_executesql N'SELECT T1.AttributeId AS attributeid,%
%exec sp_executesql N'select sdkmessagerequestinput%
%exec sp_executesql N'SELECT Id, KeyType, ScaleGroupId, ActiveKeyId%
%exec sp_executesql N'select sdkmessageprocessingstep0%
%AttributeId AS attributeid FROM AttributeLogicalView%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%select VersionNumber from EntityView%
%select VersionNumber from AttributePicklistValueView%
%select VersionNumber from EntityRelationshipView%
%select VersionNumber from RelationshipView%
%select VersionNumber from LocalizedLabelView%
%select VersionNumber from AttributeView%
%select VersionNumber from AttributeLookupValueView%
%select VersionNumber from EntityRelationshipRoleView%
%select VersionNumber from EntityRelationshipRelationshipsView%
%select VersionNumber from ViewAttributeView%
%exec sp_executesql N'select transactioncurrency0.ImportSequenceNumber%
%exec sp_executesql N'SELECT EntityId AS entityid FROM EntityLogicalView%
%exec sp_executesql N'SELECT AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT Id FROM Organization  WHERE (((UniqueName%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%exec sp_executesql N'exec p_GrantInheritedAccess @referencingId, @referencingOTC,%
%exec sp_executesql N'select contact0.OwningBusinessUnit as ''owningbusinessunit''%
%case when WorkflowState is null then 0 else 1 end%
%exec sp_executesql N'select asyncoperation0.AsyncOperationId as ''asyncoperationid''%
%exec sp_executesql N'SELECT Id, LOWER(SqlServerName) FROM Organization%
%isvalidforadvancedfind FROM EntityLogicalView%
%exec sp_executesql N'SELECT T1.AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT ReferencedEntityId AS referencedentityid,%
%exec sp_executesql N'select workflowdependency0.CreatedBy as ''createdby'',%
%exec sp_executesql N'select workflow0.WorkflowId as%
%exec sp_executesql N'select asyncoperation0.OwningUser%
%exec sp_executesql N'select workflow0.WorkflowId as%
%create table #CascadeCollect(id INT IDENTITY(1,1)%
%exec sp_executesql N'if (NOT EXISTS(select 0 from PrincipalObjectAccess%
%exec sp_executesql N'select systemuser0.OrganizationId as%
%exec sp_executesql N'select privilege0.PrivilegeId as%
%exec sp_executesql N'SELECT Id, LicenseKey, ProductId, InstallOn FROM ConfigSettings%
%exec sp_executesql N'select asyncoperation0.OwningUser as %
%#CascadeCollect%
%select organization0.SystemUserId as%
%insert into WorkflowLogBase%
%select sdkmessageprocessingstepimage0.SdkMessageProcessingStepImageId%
%exec sp_executesql N'exec p_GetCrmUserId @OrganizationId, @AuthInfo'%
%SELECT Id, DefaultOrganizationId FROM Deployment%
%exec sp_executesql N'SELECT Id, UserId FROM SystemUserOrganizations%
%exec sp_executesql N'SELECT Id, AuthInfo FROM SystemUserAuthentication%
%SELECT M.ObjectId FROM dbo.MatchCode%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId%
%and StatusCode in (@pausingStatus, @cancelingStatus)%
%exec sp_executesql N'select timezonedefinition0.TimeZoneCode%
%exec GetMyRunningJobs%
%exec sp_executesql N'select sdkmessagefilter0%
%exec sp_executesql N'select sdkmessagerequestfield0.Name%
%KeyType, ScaleGroupId FROM CrmKeySetting%
%select report0.Name as ''name'', report0.ReportTypeCode as%
%from TimeZoneRule as timezonerule0%
%exec sp_executesql N'select queue0.QueueId%
%truncate table #SyncEntry%
%create table #SyncEntry%
%exec sp_executesql N'update Subscription set LastSyncStartedOn%
%exec sp_executesql N'select subscriptionclients%
%exec sp_executesql N'select displaystring0.DisplayStringId%
%drop table #SyncEntryIds;%
%select convert(bigint, min_active_rowversion()-1)%
%select ObjectTypeCode, SyncState,%
%insert into SubscriptionSyncInfo%

Enjoy

De vez en cuando al hacer un desarrollo o una personalización de  MS CRM, necesito ejecutar un traz0 de SQL Server. Por cualquiera que haya visto alguna vez un trazo por MS CRM (o cualquier otra aplicación grande) sabe que una gran cantidad de SQL se genera - más de lo que prodrías imaginar. En mi caso, por lo general no quiero ver todo el ”ruido de fondo” en el SQL que genera la aplicación por su proceso interno, como los metadatos, flujos de trabajo, el servicio de eliminación, etc.  Estoy más interesado en el SQL que se ejecuta para seleccionar, insertar o actualizar los registros de CRM.

Suponiendo que tú ya sabes cómo crear un filtro de SQL Server, puedes agregar cada una de las siguientes afirmaciones como un filtro a la columna “TextData” y filtrará una gran cantidad de las cosas que no quieres ver en los resultados. Espero que te ayude, por supuesto, pero te advierto que si estoy filtrando algo que tú quieres ver en los resultados, no me eches la culpa! :)

Notar: Esto es para CRM 4.0 y no se probó con el MSCRM 2011.  Yo diría que que no funciona muy bien.

%exec sp_executesql N'With TopSuspendedAsyncOperations(AsyncOperationId)%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%exec GetDBVersion @DBVersion=@p1 output%
%exec sp_reset_connection%
%exec sp_executesql N'update AsyncOperationBase%
%-- network protocol: LPC%
%exec sp_executesql N'SELECT OrganizationId, EventId, EventData, CreatedOn FROM Notification%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId, subscription.AsyncOperationId, subscription.EntityName,%
%if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)%
%and DeletionStateCode = 0',N'@lockedState int,@hostId nvarchar(62),@pausingStatus int,@cancelingStatus%
%exec sp_executesql N'SELECT Id FROM DataEncryptionKey  WHERE (((IsConfigurationRow = @IsConfigurationRow0))%
%exec sp_executesql N'SELECT Id, ColumnName, BigIntColumn, IntColumn, SmallIntColumn, TinyIntColumn,%
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey  WHERE (((Id = @Id0)) ) AND (IsDeleted =
%exec sp_executesql N'SELECT Id, CreatedOn, Enabled, KeyType, ScaleGroupId FROM CrmKey%
%exec sp_executesql N'select sdkmessagefilter0.PrimaryObjectTypeCode as ''primaryobjecttypecode'',%
%exec sp_executesql N'SELECT LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'select sdkmessage0.SdkMessageId as ''sdkmessageid'' from SdkMessage%
%exec sp_executesql N'update WorkflowLogBase set <a href="mailto:ModifiedBy=@ModifiedBy0">ModifiedBy=@ModifiedBy0</a>, <a href="mailto:ModifiedOn=@ModifiedOn0">ModifiedOn=@ModifiedOn0</a>, Message=NULL,%
%exec p_GetFullName @organizationid=%
%exec sp_executesql N'select workflow0.UIData as ''uidata'', workflow0.IsCrmUIWorkflow as ''iscrmuiworkflow'',%
%exec sp_executesql N'select systemuser0.AccessMode as ''accessmode'', systemuser0.SystemUserId%
%exec sp_executesql N'select workflowlog0.AsyncOperationId as ''asyncoperationid'',%
%exec sp_executesql N'select workflow0.WorkflowId as ''workflowid'', workflow0.PrimaryEntity as ''primaryentity'',%
%exec sp_executesql N'delete from WorkflowCompletedScopeBase%
%exec sp_executesql N'SELECT Id, KeyType, ActiveKeyId, Enabled%
%exec sp_executesql N'SELECT Id, ScaleGroupId, KeyType,%
%select sdkmessageprocessingstepimage0.CreatedOn as ''createdon''%
%exec sp_executesql N'SELECT Label AS label,%
%SELECT EntityRelationshipId AS entityrelationshipid,%
%SELECT CascadeAssign AS cascadeassign,%
%exec sp_executesql N'SELECT EntityId AS entityid,%
%exec sp_executesql N'SELECT LogicalName AS logicalname,%
%exec sp_executesql N'SELECT T1.LocalizedLabelId AS localizedlabelid,%
%exec sp_executesql N'SELECT RelationshipId AS relationshipid,%
%exec sp_executesql N'IF EXISTS (SELECT 1 FROM dbo.MatchCode%
%SELECT MatchCode FROM dbo.MatchCode%
%exec sp_executesql N'update WorkflowWaitSubscriptionBase%
%exec sp_executesql N'select distinct AsyncOperationId from WorkflowWaitSubscriptionBase%
%exec sp_executesql N'SELECT Id, HelpServerUrl, InstallOn, IsRegistered,%
%exec sp_executesql N'insert into AsyncOperationBase%
%exec sp_executesql N'SELECT T1.AttributeId AS attributeid,%
%exec sp_executesql N'select sdkmessagerequestinput%
%exec sp_executesql N'SELECT Id, KeyType, ScaleGroupId, ActiveKeyId%
%exec sp_executesql N'select sdkmessageprocessingstep0%
%AttributeId AS attributeid FROM AttributeLogicalView%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%select VersionNumber from EntityView%
%select VersionNumber from AttributePicklistValueView%
%select VersionNumber from EntityRelationshipView%
%select VersionNumber from RelationshipView%
%select VersionNumber from LocalizedLabelView%
%select VersionNumber from AttributeView%
%select VersionNumber from AttributeLookupValueView%
%select VersionNumber from EntityRelationshipRoleView%
%select VersionNumber from EntityRelationshipRelationshipsView%
%select VersionNumber from ViewAttributeView%
%exec sp_executesql N'select transactioncurrency0.ImportSequenceNumber%
%exec sp_executesql N'SELECT EntityId AS entityid FROM EntityLogicalView%
%exec sp_executesql N'SELECT AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT Id FROM Organization  WHERE (((UniqueName%
%FROM AttributePicklistValueLogicalView WHERE AttributeId%
%exec sp_executesql N'SELECT T1.Label AS label,%
%exec sp_executesql N'exec p_GrantInheritedAccess @referencingId, @referencingOTC,%
%exec sp_executesql N'select contact0.OwningBusinessUnit as ''owningbusinessunit''%
%case when WorkflowState is null then 0 else 1 end%
%exec sp_executesql N'select asyncoperation0.AsyncOperationId as ''asyncoperationid''%
%exec sp_executesql N'SELECT Id, LOWER(SqlServerName) FROM Organization%
%isvalidforadvancedfind FROM EntityLogicalView%
%exec sp_executesql N'SELECT T1.AttributeTypeId AS attributetypeid,%
%exec sp_executesql N'SELECT ReferencedEntityId AS referencedentityid,%
%exec sp_executesql N'select workflowdependency0.CreatedBy as ''createdby'',%
%exec sp_executesql N'select workflow0.WorkflowId as%
%exec sp_executesql N'select asyncoperation0.OwningUser%
%exec sp_executesql N'select workflow0.WorkflowId as%
%create table #CascadeCollect(id INT IDENTITY(1,1)%
%exec sp_executesql N'if (NOT EXISTS(select 0 from PrincipalObjectAccess%
%exec sp_executesql N'select systemuser0.OrganizationId as%
%exec sp_executesql N'select privilege0.PrivilegeId as%
%exec sp_executesql N'SELECT Id, LicenseKey, ProductId, InstallOn FROM ConfigSettings%
%exec sp_executesql N'select asyncoperation0.OwningUser as %
%#CascadeCollect%
%select organization0.SystemUserId as%
%insert into WorkflowLogBase%
%select sdkmessageprocessingstepimage0.SdkMessageProcessingStepImageId%
%exec sp_executesql N'exec p_GetCrmUserId @OrganizationId, @AuthInfo'%
%SELECT Id, DefaultOrganizationId FROM Deployment%
%exec sp_executesql N'SELECT Id, UserId FROM SystemUserOrganizations%
%exec sp_executesql N'SELECT Id, AuthInfo FROM SystemUserAuthentication%
%SELECT M.ObjectId FROM dbo.MatchCode%
%exec sp_executesql N'select subscription.WorkflowWaitSubscriptionId%
%and StatusCode in (@pausingStatus, @cancelingStatus)%
%exec sp_executesql N'select timezonedefinition0.TimeZoneCode%
%exec GetMyRunningJobs%
%exec sp_executesql N'select sdkmessagefilter0%
%exec sp_executesql N'select sdkmessagerequestfield0.Name%
%KeyType, ScaleGroupId FROM CrmKeySetting%
%select report0.Name as ''name'', report0.ReportTypeCode as%
%from TimeZoneRule as timezonerule0%
%exec sp_executesql N'select queue0.QueueId%
%truncate table #SyncEntry%
%create table #SyncEntry%
%exec sp_executesql N'update Subscription set LastSyncStartedOn%
%exec sp_executesql N'select subscriptionclients%
%exec sp_executesql N'select displaystring0.DisplayStringId%
%drop table #SyncEntryIds;%
%select convert(bigint, min_active_rowversion()-1)%
%select ObjectTypeCode, SyncState,%
%insert into SubscriptionSyncInfo%

¡Disfruta!

Tags: , , ,


Retrieve the Primary Key column for a SQL Server table

During a recent project I was dynamically scripting data from related database tables and needed to know the primary key column from a parent table to use with the child tables. I normally use the Information_Schema Views (ISV) for most metadata related queries, but there are always several ways to achieve the same results and I wanted to see if another approach might be easier. I tried the following options and ended up using the ISV solution, it was the simplest and the often the best standard approach.

  1. SystemTables
  2. Stored Procedure
  3. Information_Schema Views

Using the Northwind database the following queries will return the primary key column for the Customers table. The system table approach is shown in two queries with the second one using a CTE instead of a sub-select.

-----------------------------------------------------------
-- Using the system tables
-----------------------------------------------------------
SELECT  sc.name AS PrimaryKey
FROM    syscolumns sc
            JOIN sysobjects so ON so.id = sc.id
WHERE   so.name = 'Customers'
AND     sc.colid IN (   SELECT  sik.colid
                        FROM    sysindexkeys sik
                                    JOIN sysobjects so ON sik.[id] = so.[id]
                        WHERE   sik.indid = 1
                        AND     so.name = 'Customers')
-- CTE
-- Get the id of the primary key column
;WITH PKColumnIdCTE AS
(
    SELECT  sik.colid
    FROM    sysindexkeys sik
                JOIN sysobjects so ON sik.id = so.id
    WHERE   sik.indid = 1
    AND     so.name = 'Customers'
)
-- Get the name of the primary key column
SELECT  sc.name AS PrimaryKey
FROM    syscolumns sc
            JOIN sysobjects     so ON so.id = sc.id
            JOIN PKColumnIdCTE  pk ON pk.colid = sc.colid
WHERE   so.name = 'Customers'


-----------------------------------------------------------
-- Using the system stored procedure
-----------------------------------------------------------
-- This uses the system tables internally and returns the
-- primary key information for the table as a row.
EXEC sp_Pkeys 'Customers'

-- You can extract the key by capturing the result in a temp
-- table and querying to select the column.
DECLARE @_PKInfo TABLE
(
     DbName     VARCHAR(128)
    ,OwnerName  VARCHAR(128)
    ,TableName  VARCHAR(128)
    ,ColumnName VARCHAR(128)
    ,KeySeq     INT
    ,PkName     VARCHAR(128)
)
INSERT INTO @_PKInfo EXEC sp_Pkeys 'Customers'
SELECT ColumnName AS PrimaryKey FROM @_PKInfo


-----------------------------------------------------------
-- Using the information_schema views
-----------------------------------------------------------
SELECT  ccu.column_name AS PrimaryKey
FROM    information_schema.table_constraints tc
            JOIN information_schema.constraint_column_usage ccu
                ON tc.constraint_name = ccu.Constraint_name
WHERE   tc.constraint_type = 'Primary Key'
AND     tc.table_name = 'Customers'

Durante un proyecto reciente me encontraba creado secuencias de comandos dinámicamente en tablas que se encuentran relacionadas. En ese momento me di cuenta de que necesitaba la columna que tenía el “primary Key” de la tabla padre para así poder usarla con la tabla hija. Yo normalmente utilizo las vistas del esquema de información “Information_Schema Views” (ISV) para la mayoría de las consultas relacionadas con los datos, pero hay formas siempre diferentes para lograr los mismos resultados y quería ver si otro enfoque podría ser más sencillo. Traté las siguientes opciones y me di cuenta que la solución de ISV, era más simple y da el mejor resultado así como también cumple mejor con los estándares.

Tablas del sistema

  1. 1. Tablas del sistema
  2. 2. Procedimientos Almacenados (Stored Procedure)
  3. 3. Vistas de esquema de información (Information_Schema Views )

Usando la base de datos Northwind las siguientes consultas retornaran el nombre de la columna que tiene la llave primaria en la tabla “Customers”. El enfoque de la tabla de sistema se muestra en dos consultas con el segundo uso de una CTE en lugar de un sub-Select.

-----------------------------------------------------------
-- Usando Tablas del Sistema
-----------------------------------------------------------
SELECT  sc.name AS PrimaryKey
FROM    syscolumns sc
            JOIN sysobjects so ON so.id = sc.id
WHERE   so.name = 'Customers'
AND     sc.colid IN (   SELECT  sik.colid
                        FROM    sysindexkeys sik
                                    JOIN sysobjects so ON sik.[id] = so.[id]
                        WHERE   sik.indid = 1
                        AND     so.name = 'Customers')
-- CTE
-- Get the id of the primary key column
;WITH PKColumnIdCTE AS
(
    SELECT  sik.colid
    FROM    sysindexkeys sik
                JOIN sysobjects so ON sik.id = so.id
    WHERE   sik.indid = 1
    AND     so.name = 'Customers'
)
-- Get the name of the primary key column
SELECT  sc.name AS PrimaryKey
FROM    syscolumns sc
            JOIN sysobjects     so ON so.id = sc.id
            JOIN PKColumnIdCTE  pk ON pk.colid = sc.colid
WHERE   so.name = 'Customers'


-----------------------------------------------------------
-- Usando Procedimientos Almacenados.
-----------------------------------------------------------
-- This uses the system tables internally and returns the
-- primary key information for the table as a row.
EXEC sp_Pkeys 'Customers'

-- You can extract the key by capturing the result in a temp
-- table and querying to select the column.
DECLARE @_PKInfo TABLE
(
     DbName     VARCHAR(128)
    ,OwnerName  VARCHAR(128)
    ,TableName  VARCHAR(128)
    ,ColumnName VARCHAR(128)
    ,KeySeq     INT
    ,PkName     VARCHAR(128)
)
INSERT INTO @_PKInfo EXEC sp_Pkeys 'Customers'
SELECT ColumnName AS PrimaryKey FROM @_PKInfo


-----------------------------------------------------------
-- Usando Vistas de esquema de Información
-----------------------------------------------------------
SELECT  ccu.column_name AS PrimaryKey
FROM    information_schema.table_constraints tc
            JOIN information_schema.constraint_column_usage ccu
                ON tc.constraint_name = ccu.Constraint_name
WHERE   tc.constraint_type = 'Primary Key'
AND     tc.table_name = 'Customers'

Tags: , , ,


XQuery

I have found XQuery to be of good use when persisting serialized objects to Sql Server. Using XQuery we can pass an xml string to a stored procedure and derive the expected results with fairly simple code. We start with an XML string, which may contain multiple nodes and values.

DECLARE @testXmlString XML
SET @testXmlString = '
<Root>
    <employee>
       <name>Tim</name>
       <age>27</age>
       <company>ToplineStrategies</company>
       <title>Developer</title>
       <Education>B.S. Computer Science</Education>
       <ExperienceLevel>MidLevel</ExperienceLevel>
    </employee>
    <employee>
       <name>Chris</name>
       <age>37</age>
       <company>Deloitte</company>
       <title>Project Manager</title>
       <Education>B.A. Business</Education>
       <ExperienceLevel>Expert</ExperienceLevel>
    </employee>
</Root>'

In a stored procedure, we are able to extract and insert these values into a table by using the value() and node() functions. Before the type “xml” was introduced (SQL Server 2005) you could use OPENXML for such tasks. However, this method is no longer recommended these days where the “xml” data type and its methods “nodes” and “value” exist.

Parsing objects serialized to XML in the database will save development efforts much time in the Data Access Layer and increase code readability. Using XQuery, the amount of methods in the DAL will be drastically decreased by parameterizing the name of the stored procedure. An alternative to using XQuery in this example is to hard code each field as a parameter to be passed to a command object. Over time this will not only increase the amount of code in the DAL but increase the LOE of code maintainability.

&lt;/pre&gt;<br />
SELECT<br />
emp.value ('./name[1]', 'varchar(80)') AS [Name],<br />
emp.value ('./age[1]', 'int') AS Age,<br />
emp.value ('./company[1]', 'varchar(80)') AS Company,<br />
emp.value ('./title[1]', 'varchar(80)') AS Title,<br />
emp.value ('./Education[1]', 'varchar(80)') AS Education,<br />
emp.value ('./ExperienceLevel[1]', 'varchar(80)') AS ExperienceLevel<br />
FROM @testXmlString.nodes ('/Root/employee') AS Employee (emp)</p>
<p>

Results:

Name Age Company Title Education ExperienceLevel
Tim 27 ToplineStrategies Developer B.S. Computer Science MidLevel
Chris 37 Deloitte Project Manager B.A. Business Expert

More information on XQuery is available @ http://www.w3schools.com/xquery/xquery_syntax.asp

Tags: , , ,


Random Error Message: “Specified cast is not valid” During an Excel Import

I’ve been working on a data import where I was using an Excel worksheet as the source and a destination of SQL Server. I was getting an error message “Specified cast is not valid” while the import was running. I had read about setting the “IMEX=1″ value in the connection string to have it treat all the columns of Excel data as strings, but it turns out there’s a bit more to it than that. This article by Joe Morrison is the best discussion of the whole issue and a workaround.

In my case, I have some freedom to tell the customer what format the data for this recurring import should be in, so I am going to try Access and see if that works better (I expect it will). It will give the customer a lot of the Excel/spreadsheet data editing capabilities in more of a real database format. (And yes, I understand that Access is not an enterprise level database system, but it does have it’s place and perfect for my needs.)

Tags: , ,