Friday, March 28, 2014

Differences Between the SQL-based and Fetch-based Reports


Area
SQL-based Report
Data Provider
The <DataProvider> element value in the .rdl file is set to SQL. For example:
<DataProvider>SQL</DataProvider>
Report query
The query specified for retrieving data is in the <CommandText> sub-element under the<Query> element in the report definition (.rdl file) is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be:
<CommandText>SELECT name FROM FilteredAccount;</CommandText>

Area
Fetch-based report
Data Provider
The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example:
<DataProvider>MSCRMFETCH</DataProvider>
Report query
The query specified for retrieving data is in the <CommandText> sub-element under the <Query> 
element in the report definition (.rdl file) is a FetchXML query. For example, the query for retrieving
 all account names for a Fetch-based report will be:
<CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt;
    &lt;entity name="account"&gt;
        &lt;attribute name="name" /&gt;
    &lt;/entity&gt;
&lt;/fetch&gt;</CommandText>

Tuesday, March 18, 2014

Get all the tables from the SQL database

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, 
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME