SQL Space

It is very common to forget the mode of analysis services installed on your sandbox machine particularly since tabular mode is getting used in more recent projects, here is a snippet of ps code which can be run to find it out :

## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

#Write-Output $server

Write-Output ‘———————————–‘
Write-Output (“`nServer: {0}`nEdition: {1}`nBuild: {2}`nServerMode: {3}`n” -f
$server.Name, $server.Edition, $server.Version, $server.ServerMode)
Write-Output ‘———————————–‘

Or if you have SSMS installed, you can connect to Analysis services drop-down and then connect, then right click on server properties:

• Logic app is an app container which defines a work flow using triggers and actions 
  • Trigger – instantiates a work flow 
  • Action – activity or action to retrieve or update data 
• Behind the scenes, it is run on infrastructure of azure region (VM in a DC) 
• Can be developed in Azure portal and VS 
• Extensive connectors makes it rich with many options or can build a customer connector 
• Security is handled using SAS tokens 
• Exception handling is done using re-try logic 
• Scalability depends on the type of connectors used 
• Automatic deployment is possible using ARM Templates 
• Monitoring can be done by traditional tools like Log Analytics, Application insights and OMS 

[cc lang=”SQL”]

CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = ‘p@ssword123’
GO
CREATE SYMMETRIC KEY skey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY akey
GO

CREATE TABLE PhoneTable (
cid INT IDENTITY(1,1) NOT NULL,
PhoneNumber CHAR(12),
Encrypted VARBINARY(MAX),
UnEncrypted CHAR(12)
)

INSERT INTO PhoneTable (PhoneNumber)
VALUES (‘238-555-0197’), (‘664-555-0112’)

OPEN SYMMETRIC KEY skey DECRYPTION BY ASYMMETRIC KEY akey WITH PASSWORD = ‘p@ssword123’

UPDATE PhoneTable SET Encrypted = ENCRYPTBYKEY(KEY_GUID(‘skey’), PhoneNumber)

UPDATE PhoneTable SET UnEncrypted = CAST(DECRYPTBYKEY(Encrypted) AS VARCHAR)

SELECT * FROM PhoneTable

— cleanup
DROP TABLE PhoneTable
CLOSE SYMMETRIC KEY skey
DROP SYMMETRIC KEY skey
DROP ASYMMETRIC KEY akey

[/cc]

[cc lang=”SQL”]

— master database
use master
go

create table PhoneTable (
cid int identity(1,1) not null,
PhoneNumber char(12)
)

insert into PhoneTable
values (‘238-555-0197’), (‘664-555-0112’)

— unmasked for user with better than SELECT permissions on the table
select * from PhoneTable

— now alter table to mask the PhoneNumber column
ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = ‘partial(4, “xxx-xxxx”, 0)’) NULL;
–ALTER TABLE PhoneTable ALTER COLUMN PhoneNumber char(12) MASKED WITH (FUNCTION = ‘default()’) NULL;

— create a user without login who has only SELECT on the table
CREATE USER user1 WITHOUT LOGIN;
GRANT SELECT ON OBJECT::dbo.PhoneTable TO user1

— masked for user who has only READ access on the table
EXECUTE AS USER = ‘user1’;
SELECT *
FROM PhoneTable;
REVERT;

— verify the DDM applied in the entire database
SELECT OBJECT_NAME(object_id) TableName,
name ColumnName,
masking_function MaskFunction
FROM sys.masked_columns
ORDER BY TableName, ColumnName;

— cleanup
drop table PhoneTable
drop user user1

[/cc]

It is common to see these errors when migrating SSRS reports from earlier versions of SQL Server to later versions or viceversa.
However please do remember that most of SSIS, SSAS, SSRS projects are not completely backward compatible. So in a nutshell a report developed in SQL/SSRS 2016 need not necessarily render in SQL/SSRS 2008 R2. (for example the report parameter layout changes are NOT even existant in earlier versions of SQL server).

Common Errors seen when trying to render incompatible version of RDL on a different version of SQL Server engine.

[rsInvalidReportDefinition] The definition of this report is not valid or supported by this version of Reporting Services.

The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas.

Details: There are multiple root elements. Line 24, position 107822. C:\Users\xxx.rdl

Details: Data at the root level is invalid. Line 66, position 59916. C:\Users\xxx.rdl

Reason :

The RDL file (i.e XML source code) is not well formed XML when connected to different version of SQL Server engine then what it is compatible with (depending on the version of SSDT/BIDS) and thus breaking the contract, so the SQL engine cannot parse XML.

Solution :

1) Say if you are connecting to SQL 2016 datasource as relational engine using Visual Studio 2015 then change the targetserver version property to SQL 2016 as below.
(similarly if you are connecting to SQL 2008 R2 using BIDS or Visual Studio 2008 then change the targetserver version to SQL 2008 R2)

SSIS conditional split complains of error if the incoming columns in a condition are evaluated as NULL.

The Error is :

“Error: The expression on “output “Unknown” (12743)” evaluated to NULL, but the “component “Conditional Split” (12740)” requires a Boolean results.”

Best way to resolve this is by proper NULL handling.
If the condition was cola > colb

then using !ISNULL(cola) && !ISNULL(colb) && cola > colb will handle the cases where either of the columns are NULL