SQL Table/CTE for SQL Server, SSIS and Biml data types

Cathrine Wilhelmsen posted an easy lookup table for the data types within SQL Server, SSIS and Biml.

For loading the meta-data information based on source tables, I find it handy to provide all three data types in the result set of the query. So here’s an easy Common Table Expression / Table Value Constructor which might help you also.


WITH DataTypeMatrix (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
AS
(
SELECT *
FROM
(
VALUES
('bigint','DT_I8','Int64')
,('binary','DT_BYTES','Binary')
,('bit','DT_BOOL','Boolean')
,('char','DT_STR','AnsiStringFixedLength')
,('date','DT_DBDATE','Date')
,('datetime','DT_DBTIMESTAMP','DateTime')
,('datetime2','DT_DBTIMESTAMP2','DateTime2')
,('datetimeoffset','DT_DBTIMESTAMPOFFSET','DateTimeOffset')
,('decimal','DT_NUMERIC','Decimal')
,('float','DT_R8','Double')
,('geography','DT_IMAGE','Object')
,('geometry','DT_IMAGE','Object')
,('hierarchyid','DT_BYTES','Object')
,('image','DT_IMAGE','Binary')
,('int','DT_I4','Int32')
,('money','DT_CY','Currency')
,('nchar','DT_WSTR','StringFixedLength')
,('ntext','DT_NTEXT','String')
,('numeric','DT_NUMERIC','Decimal')
,('nvarchar','DT_WSTR','String')
,('real','DT_R4','Single')
,('rowversion','DT_BYTES','Binary')
,('smalldatetime','DT_DBTIMESTAMP','DateTime')
,('smallint','DT_I2','Int16')
,('smallmoney','DT_CY','Currency')
,('sql_variant','DT_WSTR','Object')
,('text','DT_TEXT','AnsiString')
,('time','DT_DBTIME2','Time')
,('timestamp','DT_BYTES','Binary')
,('tinyint','DT_UI1','Byte')
,('uniqueidentifier','DT_GUID','Guid')
,('varbinary','DT_BYTES','Binary')
,('varchar','DT_STR','AnsiString')
,('xml','DT_NTEXT','Xml')
) AS F (DataTypeSQL, DataTypeSSIS, DataTypeBiml)
)

SELECT *
FROM DataTypeMatrix




Need to re-format the T-SQL code? Use the Notepad++ plugin ‘Poor Man’s T-Sql Formatter

 


 


 

MDS: How to create or deploy a package from a model

A few lines I need to remember for MDS 🙂

CREATE:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model “Biml meta-data” -version “VERSION_1” -package “C:\MDS\BimlMetaData_20170614.pkg” -includedata

DEPLOY:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy deployclone -package “C:\MDS\BimlMetaData_20170614.pkg” -service MDS1

MDS Create package from model

HRESULT E_FAIL has been returned from a call to a COM component

HRESULT E_FAIL has been returned from a call to a COM component

Ever got this error message from Visual Studio? Well I’m using mine for BI projects and this helped for me:

Start —> Run —> regsvr32 %SystemRoot%\System32\msxml3.dll

Start —> Run —> regsvr32 %SystemRoot%\SysWOW64\msxml3.dll

If it doesn’t, maybe you’ll be able to find a solution here.








SSMS Database Diagram – backend version is not supported

‘…backend version is not supported…’ was a new one for me. But when you think about it, you can’t use an ‘older’ version of SSMS to create a database diagram of a database on an SQL Server Instance that is a newer version. One option would be to download the latest stand-alone version of SSMS (which will also automatically update itself btw!).

Download SQL Server Management Studio (SSMS)
https://msdn.microsoft.com/en-us/library/mt238290.aspx

SSMS Database Diagram - backend version is not supported








SQL Server 2014 Developer Edition is now a free download

As of the 31st of March, SQL Server 2014 Developer Edition is now a free download:
https://myprodscussu1.app.vssubscriptions.visualstudio.com/Downloads?pid=1682

SQL Server 2014 Dev Download VS

Quote from MSDN:

SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.

Visual Studio Dev Essentials is Microsoft’s most comprehensive free developer program ever, with everything you need to build and deploy your app on any platform, including state-of-the-art tools, the power of the cloud, training, and support.

Ohh….and this is also a nice one:

SQL Server 2016 Developer Edition, when released later this year, will also be free.

Read the full post on the SQL Server Blog: https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

 

Evaluation Editions:
Just want to download an evaluation edition?:
Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation

SQL Server 2014 Dev Download

 


 


 


 

SSRS: Render your reports in HTML4.0 instead of MHTML!

By default in SQL Server 2012 (Enterprise) when you create a new data-driven subscription (DDS), you’re able to select ‘HTML4.0‘ as a rendering format. But when you create a normal subscription, the option isn’t available.

SSRS 2012 HTML4 subscription

 

Here’s how you can enable the functionality of rendering in HTML4.0 format for a normal subscription. Please remove Visible=”false” for the HTML4.0 rendering extension in the RSreportserver.config file as follows:

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

To get the HTML4.0 to be included in the body of the email rather than as an attachment, look for this element in the RSreportserver.config file:

< EmbeddedRenderFormats>

And add this as an extra entry within that element:

<RenderingExtension>HTML4.0</RenderingExtension>

In my own file the whole thing now looks like this:

< EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
<RenderingExtension>HTML4.0</RenderingExtension>
</EmbeddedRenderFormats>

This works fine for me using SQL 2008 R2 and up.

Also for SQL Server 2016, the option for HTML5 is also available (same principle as above). But in SQL 2016 with HTML5 it was still send as an attachment instead of embedded. So somehow the embed-config doesn’t work properly yet?








Two handy queries which can help you find things in your database


--Search for a specific string in object-definitions:
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName