jan 21 anders jeppesen blog Microsoft CRM Microsoft CRM 2011 Scribe T-SQL

Issues when importing attachments with Scribe to CRM?

I became aware that when doing a normal Query against a SQL database with my binary attachment data like SELECT * FROM ATTACHMENTS It only took the first 128K of data from the column with the binary….that’s an issue when there is attachments in the size of 5+ MB :-). The solution was to break…

Read more

mar 16 anders jeppesen blog Microsoft CRM Microsoft CRM 2011 Scribe scripts T-SQL

Monitor Microsoft CRM Async log with Scribe

Use the script below to monitor the entries you have in the async log – you can make some rules based on how many days it have been waiting etc. SELECT TOP (10000) MAIN_FilteredAsyncOperation.name, MAIN_FilteredAsyncOperation.operationtypename, MAIN_FilteredAsyncOperation.statuscodename, (SELECT DATEDIFF(DAY, startedonutc, GETDATE()) FROM FilteredAsyncOperation WITH(NOLOCK)WHERE asyncoperationid = MAIN_FilteredAsyncOperation.asyncoperationid) AS DayDurationInQueue ,CONVERT(char(10),startedonutc,126) AS DateEnteredQueue FROM FilteredAsyncOperation AS MAIN_FilteredAsyncOperation…

Read more

mar 13 anders jeppesen blog Microsoft CRM Microsoft CRM 2011 Scribe scripts T-SQL

HOW TO Lookup Scribe CRM adapter error messages in MSMQ based integration

OOTB it’s not possible to see the complete error message from the CRM API if you get an error in your integrations using the Scribe Console API. But the call stack is in the system, hidden inside the SCRIBEINTERNAL database. WHO TO: Open the SCRIBEINTERNAL database from SQL Studio Manager Navigate to views and Query…

Read more

aug 13 anders jeppesen blog Microsoft CRM Microsoft CRM 2011 Scribe scripts T-SQL

Check for missing index on SQL database

To get an indicator on the missing index’s on your SQL you can use the script below. It’s taken from this the MS BlogURL: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, ‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘_’ + LEFT (PARSENAME(mid.statement,…

Read more

jan 27 blog T-SQL

T-SQL Script to Check / repaire a SQL database

Hi, I needed  to repaire a CRM database today and found this script handy: Change the database mode to single user: EXEC sp_dboption ‘DynamicsHML_MSCRM’, ‘single user’, ‘true’ And when done set it back with this: EXEC sp_dboption ‘DynamicsHML_MSCRM’, ‘single user’, ‘false’   Make a check of the database, this will output errors if any: DBCC…

Read more

nov 8 blog scripts SQL Reports T-SQL

Validate email address with T-SQL and regular expressions

I needed an easy way of validation email addresses in a SQL database, this is the outcome from it: –Select all records in the entity select id, name, emailaddress from table1 –The except compare the two dataset and output the difference EXCEPT –Select all records in the entity again now with the expression on select…

Read more

jan 13 anders jeppesen base64 Microsoft CRM 2011 SAP Scribe T-SQL unicode

Scribe and Microsoft CRM

Hello All, It’s a while since I last had written something – but I’d like to share some experience about Scribe which I have worked a lot with the last few months on various projects. Project 1:I have completed a standard data migration for a Polish afdelig of a larger drug company, however I ran…

Read more

okt 22 Microsoft CRM SQL Reports T-SQL Table of contents TOC

Create Table of contents (TOC) in SQL Reports

Hi all, This night I finished a advanced report with with table of contents with page numbers of cause it’s all dynamics and data is feeded from a Microsoft CRM database but that could be any data source if needed. The scenario is that my customer needed to print product brochere diretly from inside of…

Read more

sep 11 Microsoft CRM SQL Reports T-SQL

Check if file excist in SQL report

Hi, I needed a script to check if a picture file excist for use in my data set. This is the normal SQL script: But that will not work from inside the report manager, but if you build it into a function on the sql server it will work. Normally when I build my own…

Read more

jul 26 Microsoft Microsoft CRM scripts T-SQL tables update

T-SQL Make SLECT statement only on fields with data in

If you need to make a data dump of a table but don’t want to have all the empty fields, they just run this script and it will create the needed select statement that you can copy paste into a new query. DECLARE @TAB_NAME NVARCHAR(MAX) SET @TAB_NAME = ‘SystemUser’ DECLARE @RET_QUERY NVARCHAR(MAX) SET @RET_QUERY =…

Read more