SQL to generate delete foreign Key Constraints:
-- ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>
SELECT 'ALTER TABLE ' + sch.name + '.' + tab1.name + ' DROP CONSTRAINT [' + obj.name + ']' as DropSQL,
obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
Based on an answer by Gustavo Rubio here : http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
Sql to generate delete tables in the database:
SELECT 'Drop Table ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']'
FROM information_schema.tables
WHERE TABLE_TYPE='BASE TABLE'
Monday, October 24, 2016
Sunday, March 22, 2015
MSDN KB : Crm 2011 Privilege List
Security Role and Privilege Reference : https://msdn.microsoft.com/en-us/library/gg334308(v=crm.5).aspx
Thursday, February 12, 2015
AS400 and Columns Headers using SQL , References
Label On Command : http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmstlabelon.htm
Describe Table http://publib.boulder.ibm.com/html/as400/v4r4/ic2924/info/db2/rbafzmst96.htm
DB2 Universal Database for iSeries SQL Reference : https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmst103.htm
Appendix G. DB2 UDB for iSeries Catalog Views : https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmst103.htm
Describe Table http://publib.boulder.ibm.com/html/as400/v4r4/ic2924/info/db2/rbafzmst96.htm
DB2 Universal Database for iSeries SQL Reference : https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmst103.htm
Appendix G. DB2 UDB for iSeries Catalog Views : https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmst103.htm
AS400 Query Tool
DB2 UDB for AS/400 Query Management Programming
Table of Contents
About DB2 UDB for AS/400 Query Management ProgrammingChapter 1. Introduction
Chapter 2. Query Capability
Chapter 3. Instance Processing
Chapter 4. Commands
Chapter 5. Procedures
Chapter 6. Report Forms
Chapter 7. Callable Interface
Chapter 8. Exported and Imported Objects
Chapter 9. Distributed Relational Database Architecture (DRDA)
Chapter 10. Coded Character Set Identifiers (CCSIDs)
Chapter 11. DB2 for AS/400 Query Management Considerations
Chapter 12. Using Query/400 Definition Information
Chapter 13. Control Language Interface
Appendix A. DBCS Data
Appendix B. DB2/400 Query Management Interface Example
Appendix C. Use of Quotation Marks and Apostrophes When Setting Global Variables
Appendix D. Sort Sequence Examples
Chapter 14. Bibliography
Index
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
Tuesday, February 10, 2015
Problem When Upgrading CRM 4 to CRM 2011 - Could not load file or assembly 'Microsoft.Crm.Reporting.DataExtension.Common.Fetch
I got the error dialog saying:
---------------------------
Microsoft Dynamics CRM Setup
---------------------------
Action Microsoft.Crm.Tools.Admin.ProvisionOrganizationLanguagesAction failed.
Could not load file or assembly 'Microsoft.Crm.Reporting.DataExtension.Common.Fetch, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
---------------------------
Microsoft Dynamics CRM Setup
---------------------------
Action Microsoft.Crm.Tools.Admin.ProvisionOrganizationLanguagesAction failed.
Could not load file or assembly 'Microsoft.Crm.Reporting.DataExtension.Common.Fetch, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
C# Code Parsers, mainly for SQL Parsing
.NET SQL Parser and Formatter Tool and SSMS Plugin : https://github.com/benlaan/sqlformat
Looks complicated but stable. uses Laan? algorithm.
SQL Parser on CodeProject : http://www.codeproject.com/Articles/32524/SQL-Parser
Looks interesting, has good explanation of the method used.
Gold Parser : http://goldparser.org/about/why-use-gold.htm
uses LALR algorithm, looks great and fast, simple to define languages.
Developed for .NET in Visual Basic.
Introduction in CodeProject : http://www.codeproject.com/Articles/10492/Introduction-to-GOLD-Parser
BSN-GoldParser https://code.google.com/p/bsn-goldparser/ : a project that uses GOLD parser . It builds a Sample Calculator here
There is a TSQL Parser based on the BSN project here
the above is based on a project for T-SQL Parsing by Arsène von Wyss. C.f., found here
ANTLR : http://www.antlr.org/
Uses LL Algorith. Looks like it is the most used library. NHibernate used it for HSQL Parsing, I guess it was similar to HSQL in Java Hibernate.
This is developed by Terence Parr at the university of San Diego.
Grammatica : http://grammatica.percederberg.net/index.html
Uses LL Algorith, has Java and C# implemetation.
Looks complicated but stable. uses Laan? algorithm.
SQL Parser on CodeProject : http://www.codeproject.com/Articles/32524/SQL-Parser
Looks interesting, has good explanation of the method used.
Gold Parser : http://goldparser.org/about/why-use-gold.htm
uses LALR algorithm, looks great and fast, simple to define languages.
Developed for .NET in Visual Basic.
Introduction in CodeProject : http://www.codeproject.com/Articles/10492/Introduction-to-GOLD-Parser
BSN-GoldParser https://code.google.com/p/bsn-goldparser/ : a project that uses GOLD parser . It builds a Sample Calculator here
There is a TSQL Parser based on the BSN project here
the above is based on a project for T-SQL Parsing by Arsène von Wyss. C.f., found here
ANTLR : http://www.antlr.org/
Uses LL Algorith. Looks like it is the most used library. NHibernate used it for HSQL Parsing, I guess it was similar to HSQL in Java Hibernate.
This is developed by Terence Parr at the university of San Diego.
Grammatica : http://grammatica.percederberg.net/index.html
Uses LL Algorith, has Java and C# implemetation.
Wednesday, January 28, 2015
Convert HTML to DocX / OpenXML
http://html2openxml.codeplex.com/wikipage?title=Serves%20a%20generated%20docx%20from%20the%20server&referringTitle=Documentation
OpenXML SDK 2.5 from Microsoft : http://www.microsoft.com/en-us/download/details.aspx?id=30425
Html to OpenXML http://html2openxml.codeplex.com/
DOCX Discussion on the Topic : https://docx.codeplex.com/discussions/254637
Render HTML in Reportings Services 2008 : http://sqlblog.com/blogs/greg_low/archive/2008/12/19/rendering-html-in-reporting-services-text-boxes-in-sql-server-2008.aspx
Adding Alternative Text using OpenXML SDK : http://stackoverflow.com/questions/18089921/add-html-string-to-openxml-docx-document
OpenXML SDK 2.5 from Microsoft : http://www.microsoft.com/en-us/download/details.aspx?id=30425
Html to OpenXML http://html2openxml.codeplex.com/
DOCX Discussion on the Topic : https://docx.codeplex.com/discussions/254637
Render HTML in Reportings Services 2008 : http://sqlblog.com/blogs/greg_low/archive/2008/12/19/rendering-html-in-reporting-services-text-boxes-in-sql-server-2008.aspx
Adding Alternative Text using OpenXML SDK : http://stackoverflow.com/questions/18089921/add-html-string-to-openxml-docx-document
3M SIP2 Resources and Implemetations - Standard Interchange Protocol
Definition in PDF : http://bibliotecaeupo.es/utils/SIP2/sip2_docs/sip2_developers_guide.pdf
PHP Implemetation : https://code.google.com/p/php-sip2/source/browse/trunk/sip2.class.php
Simple 3M SIP2 Standard Interchange Protocol implementation in perl : https://github.com/dpavlin/Biblio-SIP2
SIP2 Client in C# : https://github.com/NiceAndNerdy/SIP2.NET
Definitions and Examples from OCLC http://www.oclc.org/support/help/olib/900/Content/System/Supported%20SIP2%20Messages.htm#63_(Patron_Information)
MS Asynchronous Server Socket Example :
https://msdn.microsoft.com/en-us/library/fx6588te(v=vs.110).aspx
and Explanation of the sampe : https://msdn.microsoft.com/en-us/library/5w7b7x5f(v=vs.110).aspx
A TcpServer like the One in Delphi on CodeProject , looks great: http://www.codeproject.com/Articles/488668/Csharp-TCP-Server
The Server App freezes on Invoke so replace it with BeginInvoke methd.
Another TCP Server Example : http://sonyarouje.com/2011/11/25/multi-client-asynchronous-tcp-server/
PHP Implemetation : https://code.google.com/p/php-sip2/source/browse/trunk/sip2.class.php
Simple 3M SIP2 Standard Interchange Protocol implementation in perl : https://github.com/dpavlin/Biblio-SIP2
SIP2 Client in C# : https://github.com/NiceAndNerdy/SIP2.NET
Definitions and Examples from OCLC http://www.oclc.org/support/help/olib/900/Content/System/Supported%20SIP2%20Messages.htm#63_(Patron_Information)
MS Asynchronous Server Socket Example :
https://msdn.microsoft.com/en-us/library/fx6588te(v=vs.110).aspx
and Explanation of the sampe : https://msdn.microsoft.com/en-us/library/5w7b7x5f(v=vs.110).aspx
A TcpServer like the One in Delphi on CodeProject , looks great: http://www.codeproject.com/Articles/488668/Csharp-TCP-Server
The Server App freezes on Invoke so replace it with BeginInvoke methd.
Another TCP Server Example : http://sonyarouje.com/2011/11/25/multi-client-asynchronous-tcp-server/
Tuesday, January 27, 2015
Looking for Route message paramters : found List of InputParameters and OutputParameters for CRM 2011
following the artice by CRM2011 Plugin InputParameter and OutputParameter Helper by Michel Palmer.
The lists does not show output parameters for Route Message.
This did not help much so I added code to my plugin to list the parameters for Route Message.
The lists does not show output parameters for Route Message.
This did not help much so I added code to my plugin to list the parameters for Route Message.
Monday, January 26, 2015
Convert DocX or Doc to PDF
Using FixIT : Link to an interesting blog entry by Anon(?)
http://wpf4developers.blogspot.de/2014/05/c-convert-docx-to-pdf-document.html
using Word : an Asnwer by Demir. Because of licensing, if you do it in real time, Microsoft demands licensing office for all users in the network !!!!
http://stackoverflow.com/questions/19560170/how-to-convert-docx-to-pdf-in-c-sharp
This should be done in a background process.
Another answer using Word Automation :
http://stackoverflow.com/questions/5431580/convert-html-to-docx-in-c-sharp
http://wpf4developers.blogspot.de/2014/05/c-convert-docx-to-pdf-document.html
using Word : an Asnwer by Demir. Because of licensing, if you do it in real time, Microsoft demands licensing office for all users in the network !!!!
http://stackoverflow.com/questions/19560170/how-to-convert-docx-to-pdf-in-c-sharp
This should be done in a background process.
Another answer using Word Automation :
http://stackoverflow.com/questions/5431580/convert-html-to-docx-in-c-sharp
Saturday, November 29, 2014
Some JQuery Plugins
JQUERY.PANELSNAPAPUERY.PANELSNAP : Panels that appear on scoll, inluding a side menu
- http://www.jqueryscript.net/demo/jQuery-Plugin-For-Smooth-Scroll-Snapping-panelSnap/demos/
Can be used for Kanban!!!!
JQuery Sidebar - Comes from any side of the screen, can be used for secondary menu or for special options to an experienced user, like shortcuts.
JQuery Plugin for FullPage site, can be used for Presentations. Respond to mouse scroll.
Does not respond to touch events,( yet)
Saturday, November 22, 2014
SharePoint 2013 and Office Web App
Steve Mann installing the OWA server and connecting to SP2013 : http://technet.microsoft.com/en-us/library/ff431687(v=office.15).aspx
Microsoft Tech on installation, configuration and Installation : http://technet.microsoft.com/en-us/library/ff431687(v=office.15).aspx
Angular JS Partial Dynamics Loading Links
Question in a StackOverlow with examples : http://stackoverflow.com/questions/23770096/how-to-easily-render-partial-html-from-a-string-in-angular-js
Fiddler with the example for the solution above, partial loading example with timers : http://jsfiddle.net/8Bf8m/29/
Controller and Controller loading Documentation:
https://docs.angularjs.org/guide/controller
Fiddler with the example for the solution above, partial loading example with timers : http://jsfiddle.net/8Bf8m/29/
Controller and Controller loading Documentation:
https://docs.angularjs.org/guide/controller
Creating a Visualization App Using the Google Charts API and AngularJS, at SitePoint:
Monday, September 29, 2014
Restoring CRM DB from SQL Server Enterprise edition to SQL Server Standard Edition resulted in Error : contains a partition function 'AuditPFN'
When moving a database from a development environment to a production Environment, We backed up the database and restored it on the production database server which is a Sql Server Standard Edition.
I Got an error when restoring CRM 2013 Database Backup saying the AuditPFN is used in the database and it needs to be removed when using a standard edition server.
The AuditPFN means that a table can be spanned over multiple partitions, something that the Enterprise edition can do and the standard edition can not.
I hope there will not be any more surprises like this. I must say I was surprised to see the speed of moving the backup file which was some 10's of GB over the network, so doing multiple attempts was not a scary scenario.
the Error Message :
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'AAAAA_MSCRM' failed. (Microsoft.SqlServer.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.
------------------------------
Database 'AAAAA_MSCRM' cannot be started in this edition of SQL Server because it contains a partition function 'AuditPFN'. Only Enterprise edition of SQL Server supports partitioning.
Database 'AAAAA_MSCRM' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905....
OK, I search the internet and found thess posts:
KB by microsoft with a quick fix : Article ID: 2567984 : "Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database
XRM blog wrote this blog using MS KB : MS CRM 2011 Unable to restore SQL Server enterprise database to SQL Server 2012
The MS KB is a global solution but ALTAI just checked the database and found that the AuditPFN is used in just one table so they checked the installation of CRM on a Standard Edition server. They built the script for that table and view, dropped them on the standard server and re created the table on the enterprise edition. After that, their processed continued.
I am working on a CRM 2013 version, so I will try the ALTAI route first. I will have a CRM database in standard edition to compare the databases.
What I did:
I backed up the database and restored to a new Database.
I started with the check written by ALTAI to find the tables with partitioning option. It was just AuditBase.
Then I reverse engineered all the indexes and constrains, the table and the view, and then removed the AuditPFN options.
Afterwards I re-inserted all the records from the original database after checking there was no change.
The scripts I built is this:
GO
/****** Object: Table [dbo].[AuditBase] Script Date: 09/30/2014 03:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuditBase](
[AttributeMask] [nvarchar](max) NULL,
[TransactionId] [uniqueidentifier] NOT NULL,
[Action] [int] NULL,
[ObjectId] [uniqueidentifier] NOT NULL,
[ObjectIdName] [nvarchar](1) NULL,
[UserId] [uniqueidentifier] NOT NULL,
[ChangeData] [nvarchar](max) NULL,
[CreatedOn] [datetime] NOT NULL,
[Operation] [int] NOT NULL,
[AuditId] [uniqueidentifier] NOT NULL,
[CallingUserId] [uniqueidentifier] NULL,
[ObjectTypeCode] [int] NULL
)
GO
ALTER TABLE [dbo].[AuditBase] ADD DEFAULT (newsequentialid()) FOR [AuditId]
GO
GO
/****** Object: Index [cndx_PrimaryKey_Audit] Script Date: 09/30/2014 03:49:12 ******/
CREATE UNIQUE CLUSTERED INDEX [cndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [fndx_ObjectTypeCode] Script Date: 09/30/2014 03:51:23 ******/
CREATE NONCLUSTERED INDEX [fndx_ObjectTypeCode] ON [dbo].[AuditBase]
(
[ObjectTypeCode] ASC
)
WHERE ([ObjectTypeCode] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_ObjectId] Script Date: 09/30/2014 03:51:40 ******/
CREATE NONCLUSTERED INDEX [ndx_ObjectId] ON [dbo].[AuditBase]
(
[ObjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_PrimaryKey_Audit] Script Date: 09/30/2014 03:51:54 ******/
CREATE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit] ON [dbo].[AuditBase]
(
[AuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object: Index [ndx_PrimaryKey_Audit_Primary] Script Date: 09/30/2014 03:52:19 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ndx_PrimaryKey_Audit_Primary] ON [dbo].[AuditBase]
(
[CreatedOn] DESC,
[AuditId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [ndx_UserId] Script Date: 09/30/2014 03:52:33 ******/
CREATE NONCLUSTERED INDEX [ndx_UserId] ON [dbo].[AuditBase]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
GO
--
-- base view for Audit
--
create view [dbo].[Audit]
(
-- logical attributes
[UserIdName],
[CallingUserIdName],
-- physical attributes
[AttributeMask],
[TransactionId],
[Action],
[ObjectId],
[UserId],
[ChangeData],
[CreatedOn],
[Operation],
[AuditId],
[CallingUserId],
[ObjectTypeCode],
[ObjectIdName]
) with view_metadata as
select
-- logical attributes
[lk_audit_userid].[FullName],
[lk_audit_callinguserid].[FullName],
-- physical attribute
[AuditBase].[AttributeMask],
[AuditBase].[TransactionId],
[AuditBase].[Action],
[AuditBase].[ObjectId],
[AuditBase].[UserId],
[AuditBase].[ChangeData],
[AuditBase].[CreatedOn],
[AuditBase].[Operation],
[AuditBase].[AuditId],
[AuditBase].[CallingUserId],
[AuditBase].[ObjectTypeCode],
[AuditBase].[ObjectIdName]
from [AuditBase]
left join [SystemUserBase] [lk_audit_callinguserid] with(nolock) on ([AuditBase].[CallingUserId] = [lk_audit_callinguserid].[SystemUserId])
left join [SystemUserBase] [lk_audit_userid] with(nolock) on ([AuditBase].[UserId] = [lk_audit_userid].[SystemUserId])
GO
GO
/****** Object: PartitionScheme [AuditPScheme] Script Date: 02/17/2011 12:59:16 ******/
DROP PARTITION SCHEME [AuditPScheme]
GO
DROP PARTITION FUNCTION AuditPFN;
GO
Subscribe to:
Posts (Atom)