Some SQL Snippets

Posted by David Jennaway

---

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, and shows 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

Posted by David Jennaway

Managing size of AsyncOperationBase table in CRM 4.0

Mahesh Vijayaraghavan Published Tuesday, July 29, 2008 10:02 AM

---

The asyncoperation entity is used in CRM 4.0 to manage various system jobs by the CRM Asynchronous Processing Host (MSCRMAsyncService) windows service. Over time this table accumulates large number of records leading to large databases. This is especially affects organizations that rely on asynchronous plug-ins and workflows. You can use the new Bulk Delete feature ( http://msdn.microsoft.com/en-us/library/cc155955.aspx) to manage the growth of records in the asyncoperation entity table. The bulk delete operation takes as input a QueryExpression and deletes the records returned by the query. There are some things to keep in mind when trying to delete asyncoperation records using bulk delete.

• You need prvDelete privilege for asyncoperation entity. In a default installation, System Administrator role has this privilege.
• You need prvBulkDelete privilege to call the BulkDelete API. In a default installation, System Administrator role has this privilege.
• Only asyncoperation records in Completed state can be deleted.
• If workflow type asyncoperations are deleted, you will lose history for some records.

In the example below, I have added a condition to select records that are completed for more than one month ago and are not workflow instances.

private static void DeleteCompletedAsyncOperationRecords(CrmService crmService)

{

QueryExpression expression = new QueryExpression(EntityName.asyncoperation.ToString());

expression.ColumnSet = new ColumnSet(new string[] { "asyncoperationid" });

expression.Criteria.AddCondition("statecode", ConditionOperator.Equal, (int)AsyncOperationState.Completed);

expression.Criteria.AddCondition("completedon", ConditionOperator.OlderThanXMonths, 1);

expression.Criteria.AddCondition("operationtype", ConditionOperator.NotEqual, (int)AsyncOperationType.Workflow);

Guid[] emptyRecipients = new Guid[0];

BulkDeleteRequest request = new BulkDeleteRequest();

request.JobName = "Bulk delete completed asyncoperations to free up space";

request.QuerySet = new QueryBase[] { expression };

request.ToRecipients = emptyRecipients;

request.CCRecipients = emptyRecipients;

request.SendEmailNotification = false;

request.RecurrencePattern = string.Empty;

request.StartDateTime = CrmDateTime.Now;

BulkDeleteResponse response = (BulkDeleteResponse)crmService.Execute(request);

Console.WriteLine("Bulk delete job id: {0}", response.JobId);

}

The bulk delete request is processed by MSCRMAsyncService in the background. Depending on the number of records returned by the query, the operation may take from minutes to hours to complete. You can monitor the status of the operation by selecting Settings àData Management à Bulk Record Deletion.

The number of records deleted (and failed to delete) are tracked by the feature and displayed in the grid.

The bulk delete operation can be scheduled as a recurring operation by setting the value of RecurrencePattern. If you plan to do so, I suggest that you run a non-recurring operation and wait for it to run to completion before creating the recurring operation. The AsyncOperationBase table contains thousands of records that would be deleted by the first time by the Bulk Delete operation. It may take several hours to delete the records because each record is deleted using a call to CRM SDK’s Delete method. Once the initial cleanup is done, you can create a recurring operation which should delete only a few records each time it runs. If you are considering adding a recurring system job to do the cleanup, I suggest a weekly frequency and running at off-peak usage times

request.RecurrencePattern = "FREQ=DAILY;INTERVAL=7;";

request.StartDateTime = CrmTypes.CreateCrmDateTimeFromUser(DateTime.Today.AddDays(1)); // start at midnight tomorrow

You may not see an immediate change in the database size. The records are physically deleted from the AsyncOperationBase table by DeletionService, which runs once a day. After the records are physically deleted by DeletionService, you may need to run “DBCC SHRINKDATABASE” against the organization database to see the actual space usage.

Cheers,

Creating an activity report which includes the related people

Ronald Lemmen - CRM, C# and Cme

---

In an activity CRM grid, it is not possible to add attributes from the activity type (letter, phonecall etc) itself. The fields to and from on the entities phonecall, letter, fax are therefore not eligable for addition on the CRM grid. It would be very useful to see those though. The same is valid for the to, cc and bcc in email and required and optional attendees in appointments. In this post I won't be giving a solution to show the attributes in the grid, instead I will give a workaround by using reports.

The only attributes which you can select in the grid are the attributes which are belonging to the entity activitypointer. These include the activityid, startdate, statecode, but also the regardingobjectid. So the question is, how to get the to, from, cc etc. For this you can use the function which I have posted in my previous post. This function accepts an ActivityID and an ActivityPartyType. So what is this type? Look at this page: ActivityPartyType. You will find a list of values mapped to what kind of field you want to add to your report.

By using that function you can create your query for the report. An example would be:

SELECT
activityid, activitytypecode, scheduledstart, subject, owneridname, statecodename,
regardingobjectidname,
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 1)) [to],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 2)) [from],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 5)) [required]
FROM
filteredactivitypointer

This query does select some default attributes and it adds the regarding, to, from and required fields. Add this query to the generation of a report and you'll be set to go.

Note: make sure that the function gets added to your database and assign the correct rights. See the post around the function for details.

Happy reporting!

MS SQL Server: Find a Value In Any Field In Any Table

I'm currently working on a Data Conversion and I've been trying to discover where certain fields are used within the database. I have a schema, but it doesn't contain all of the foreign keys as you would hope. So, I can find the values of the field but I need to know which other tables that this might be used in. Example if you have a Control File that is the home for multiple picklist lookups, it should be in there.

Well, I search the Internet for a solution to this and Matt had an answer. Warning, this script will take a long time to run... Duh! it is searching every text field in the entire database for the value.

I did modify the code Matt wrote to tell me where it was at at it was going along.... On my VPC with the amount of data I'm looking at, it could be hours and I would like to know where it is at and when it finds something.

DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @Oldtable VARCHAR(64)
DECLARE @Count int
DECLARE @Count1 int
 
SET NOCOUNT ON
 
set @Count  =0
set @Count1 =0
set @Oldtable =''
SET @value = 'MyValueToSearchFor'
 
CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64)
)
 
DECLARE TABLES CURSOR  
FOR  
  
    SELECT o.name, c.name  
    FROM syscolumns c   
    INNER JOIN sysobjects o ON c.id = o.id   
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)   
    ORDER BY o.name, c.name  
  
OPEN TABLES  
 
FETCH NEXT FROM TABLES   
INTO @table, @column  
  
WHILE @@FETCH_STATUS = 0   
BEGIN  
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '  
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '  
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''  
    SET @sql = @sql + @column + ''')'  
    EXEC(@sql)   
    IF (@Oldtable <> @table) 
 BEGIN
  Print 'Searching table ' +@table
  set @Oldtable = @table
 END
 SELECT @Count1=COUNT(*)  from #t
 if (@Count <> @Count1)
 BEGIN
  print '    **FOUND*** in ' +  @column
  SET @Count = @Count1
 END
 
    FETCH NEXT FROM TABLES   
    INTO @table, @column  
END  
  
CLOSE TABLES   
DEALLOCATE TABLES   
  
SELECT *   
FROM #t  
 
DROP TABLE #t

Matt Wrote

---

As part of a project at work, I found myself needing to update every field in an entire database that contained a certain value. If I had needed to do this in a database I had created or a database that didn’t have hundreds of tables, I might have done it manually like I have done it in the past. But that wasn’t the case, so I needed to find a better solution.

I talked to a coworker about the problem and he reminded me about the system tables that every MS SQL Server database contains. In these tables, SQL Server stores information about all the tables, fields, stored procedures, etc. in your database. They can be queried just like any other tables, and provide a handy way to write some useful dynamic queries.

 

Let’s begin. We’re going to be looking at two of the system tables: sysobjects, which is where we will find the tables in our database, and syscolumns, which is where we will find the columns in those tables. We will also be required to use a cursor in this query.

The first step is to declare some variables that will be used. These variables will store the value we are searching for, the dynamic SQL query, and the table and column names which we will use to build the query. There will also be a temporary table created to store the results of the query.

DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)

SET @value = 'whatever'

CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64)
)

Next we will declare the cursor and load with with the data we will be using. The select statement used will be pulling the table and column names by joining together the sysobjects and syscolumns tables.

There are a few things to note. We are only looking at records in the sysobjects table with the type field set to ‘U’ which represents user tables. Also, in this example we are searching for a string, so we’re only looking at records in syscolumns that are CHAR, NCHAR, VARCHAR, and NVARCHAR. You can find the numeric values for the different data types in systypes.

DECLARE TABLES CURSOR
FOR

    SELECT o.name, c.name
    FROM syscolumns c
    INNER JOIN sysobjects o ON c.id = o.id
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
    ORDER BY o.name, c.name

OPEN TABLES

Now we’ll use the data stored in the cursor to build and run the dynamic queries.

FETCH NEXT FROM TABLES
INTO @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
    SET @sql = @sql + @column + ''')'

    EXEC(@sql)

    FETCH NEXT FROM TABLES
    INTO @table, @column
END

CLOSE TABLES
DEALLOCATE TABLES

SELECT *
FROM #t

In this example, I just select the results of the dynamic query, but you could do many other more useful things.

Oh, and don’t forget to delete that temporary table when you’re done.

DROP TABLE #t