Monthly Archives: January 2019

Error on WebAPI after deployment to Azure App Services

Encountered this below error today while deploying a WebApI to Azure App Services. Initially it wasn’t very clear what the error was about so we checked the bin folder for the below assembly but couldn’t find it. Since the API was working locally the answer had to be a configuration change from Dev to Prod (i.e a Webconfig issue) or there were some artifacts left over from the creation of the Application Service in Azure that had nothing to do without project. So we first redeployed the WebConfig file to see if this will take care of the issue but it didn’t and finally we simply deleted any extra files that were present in the directory while publishing from within VS.

Attempt by security transparent method ‘System.Web.WebPages.Administration.SiteAdmin.RegisterAdminModule()’ to access security critical method ‘System.Web.WebPages.ApplicationPart..ctor(System.Reflection.Assembly, System.String)’ failed.]

 

The screen you are interested in is

And you need to check the box that says “Remove Additional Files at Destination” and then redeploy the code again.

 

https://stackoverflow.com/questions/29755925/attempt-by-security-transparent-method-system-web-webpages-administration-sitea

XML Validation: Declaration not found for element Error Message

Often when working with XML data type we encounter issues with the schema validation for the incoming XML. SQL Server does allow us to have XML datatype without schema binding. However it is a best practice to enforce Schema Binding so that we can improve data validation as well as index performance when querying the XML data later.

The biggest problem SQL Developers encounter when working with XML datatype is that they are unfamiliar with the nature of XML and how to query it. Also the error messages coming from SQL Server aren’t always clear about what the root cause it. Take for example the below scenario

We start out by creating an XML schema for some data coming in

CREATE XML SCHEMA COLLECTION listingAttributeDemo1 AS 
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="attributes">
    <xs:complexType>
      <xs:simpleContent>
        <xs:extension base="xs:string">
          <xs:attribute type="xs:byte" name="ListingId"/>
          <xs:attribute type="xs:byte" name="Bathrooms"/>
          <xs:attribute type="xs:byte" name="ParkingSpace"/>
          <xs:attribute type="xs:byte" name="Bedrooms"/>
          <xs:attribute type="xs:dateTime" name="CreatedDate"/>
          <xs:attribute type="xs:dateTime" name="UpdatedDate"/>
          <xs:attribute type="xs:byte" name="CreatedBy"/>
          <xs:attribute type="xs:byte" name="UpdatedBy"/>
        </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
  </xs:element>
</xs:schema>'

 

Notice how the element name in line 3 is attributes is in small case.

Next we create a variable that binds the XML datatype to the above schema.

DECLARE @DATA xml (listingAttributeDemo1)
=N'<Attributes ListingId="2" Bathrooms="1" ParkingSpace="7" Bedrooms="2" CreatedDate="2018-03-01T23:00:28" UpdatedDate="2018-03-04T07:39:03" CreatedBy="12" UpdatedBy="12" />'
SELECT @DATA

 

Notice how when passing the XML data I changed the Name of the element to Attributes with a Capital A.

In this case I get the below error because the schema validation is case sensitive regardless of collation and other factors.

Msg 6913, Level 16, State 1, Line 29

XML Validation: Declaration not found for element ‘Attributes’. Location: /*:Attributes[1]

To correct is we can fix the schema or change the case of the element as shown below

DECLARE @DATA xml (listingAttributeDemo1)
=N'<attributes ListingId="2" Bathrooms="1" ParkingSpace="7" Bedrooms="2" CreatedDate="2018-03-01T23:00:28" UpdatedDate="2018-03-04T07:39:03" CreatedBy="12" UpdatedBy="12" />'
SELECT @DATA

 

The correct solution would be to fix the schema so that it can track the data faithfully.

Finding candidate keys/Primary keys from data that needs to be imported

This is a continuation from the previous post where we went and inserted data from multiple CSV files into SQL server tables using linked server connection. Often the next step is to identify how the tables are related to each other. While there is no guarantee that the relationships defined here are 100% accurate it will help identify the obvious ones and make the job easier.

First we get a count of rows in the table, we can use this to compare with counts from distinct values of the columns later. The counts are stored in a table so that we can perform other checks later if needed.

CREATE TABLE [dbo].[TblRowCounts](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[tabname] [VARCHAR](1000) NULL,
	[counts] [INT] NULL
) ON [PRIMARY]
GO
 
INSERT INTO TblRowCounts  ( tabname , counts)
EXEC sp_msforeachtable 'select "?",count(*) from ?';

Now that we have the table row counts, we get the distinct Column values counts for each table. If the column is a candidate we should see the numbers match. First we create a table to store the counts for each column

CREATE TABLE [dbo].[ColRowCounts](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[tabname] [VARCHAR](1000) NULL,
	[colname] [VARCHAR](100) NULL,
	[counts] [INT] NULL
) ON [PRIMARY]

Next we calculate the columns distinct values count by using a cursor.

DECLARE @tabname VARCHAR(100) 
DECLARE @colname VARCHAR(100)
 
DECLARE loopcol CURSOR FOR 
SELECT TABLE_NAME , COLUMN_NAME FROM INFORMATION_SCHEMA.cOLUMNS
WHERE DATA_TYPE not like '%text%'
 
OPEN loopcol
FETCH NEXT FROM loopcol INTO @tabname, @colName
 
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @SQL NVARCHAR(4000) = ' insert into ColRowCounts (tabname , colname , counts) select'++CHAR(39)+@tabname+CHAR(39)++' ,'+CHAR(39)+@colname+CHAR(39)+' , count(distinct (['+@colname+'])) from '+@tabname+''
EXEC (@SQL )
 
FETCH NEXT FROM loopcol INTO @tabname , @colname
 
END 
CLOSE loopcol 
DEALLOCATE loopcol

With the row counts inserted into the tables we can now compare the numbers to see columns which have distinct counts the same as the total number of rows in the table , in other words columns with unique values.

SELECT c.tabname , c.colname
FROM [dbo].[TblRowCounts] t
INNER join ColRowCounts c
ON t.tabname = '[dbo].['+c.tabname+']'
and t.counts= c.counts

A slightly modified version of this script allows me to script out the alter table command that will convert the above columns to NOT NULLABLE as should be the case based on the numbers we got from the previous scripts.

WITH cte AS (
SELECT c.tabname , c.colname
FROM [dbo].[TblRowCounts] t
INNER join ColRowCounts c
ON t.tabname = '[dbo].['+c.tabname+']'
and t.counts= c.counts)
 
SELECT 'ALTER TABLE ' +t.tabname + ' ALTER COLUMN ['+colname+'] ' + DATA_TYPE+ CASE WHEN CHARACTER_MAXIMUM_LENGTH  IS NOT NULL THEN 
+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100))+')'
ELSE ''
END + ' NOT NULL'
FROM cte  t
INNER join INFORMATION_SCHEMA.COLUMNS c
ON t.tabname= c.TABLE_NAME
and t.colname= c.COLUMN_NAME

With the columns being not nullables they are now ready to have primary keys built on top of them. To build out the script I use the below code block to generate scripts but do not execute them without reviewing the column data first.

WITH cte AS (
SELECT c.tabname , c.colname
FROM [dbo].[TblRowCounts] t
INNER join ColRowCounts c
ON t.tabname = '[dbo].['+c.tabname+']'
and t.counts= c.counts)
 
SELECT 'ALTER TABLE ' +t.tabname + ' ADD CONSTRAINT PK_'+REPLACE(colname,' ','')+' PRIMARY KEY CLUSTERED (['+colname+'])', COUNT(*)
FROM cte  t
GROUP BY t.tabname , colname
ORDER BY t.tabname

With the Primary keys created the next step obviously is to create the Foreign Keys,  in order to do this we rely on the column names being the same in both tables. If the Primary key column name can be found on any other table we assume the two columns are related and go ahead with creating the PK- FK Relationship.

DROP TABLE #fktables
go
SELECT row_number() OVER( ORDER BY c.table_name) AS rid ,  'Alter table ' +c.TABLE_NAME+' Add constraint FK_'+c.TABLE_NAME+REPLACE(c.COLUMN_NAME,' ','')+' FOREIGN KEY (['+c.COLUMN_NAME+']) REFERENCES '+ k.TABLE_NAME+' (['+ k.COLUMN_NAME+']) ' AS def
INTO #fktables
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
INNER join INFORMATION_SCHEMA.COLUMNS c
ON c.COLUMN_NAME = k.COLUMN_NAME
and c.TABLE_NAME != k.TABLE_NAME
ORDER BY c.TABLE_NAME
 
DECLARE @cnt INT =1
WHILE @cnt <= (SELECT MAX(rid) FROM #fktables)
BEGIN TRY 
DECLARE @SQL NVARCHAR(2000) = (SELECT def FROM #fktables WHERE rid =@cnt )
EXEC (@SQL)
SET @cnt = @cnt +1 
END TRY
BEGIN CATCH 
SET @cnt =@cnt+1 
END CATCH

 

With this we have taken raw data from CSV files imported them into SQL Server and then established meaningful relationships between previously unrelated data in disjoint tables. The rest of the work is pretty much standard for DB developers e.g creating SPs etc.

 

 

Adding CSV files as linked Server Connection

I often get a lot of migration projects where the data needs to port from CSV format into SQL Server. Here is a quick way I achieve it using Linked Server Connection in SQL Server. Why use Linked Server? Because it is very simple and easy to do. I could do the same using say and SSIS package but that would be too much effort some something as simple as a raw import. SSIS packages are useful when you want to do the same thing over and over.

The basic process here is the treat the folder which contains the files like it were a database and the individual files as table. Since we already have some technologies that have this format of storing data we can simply leverage their OLEBD driver to treat the data source in this way. Which is why we start off with installing Access Database OLEBD providers.

Download and install Access database OLEBD Providers from here.

Make sure to install the version that corresponds with your CPU architecture e.g. 32 vs 64 bit. Also you don’t need worry that it says Access Engine 2010 this will work on higher versions on SQL server too. Three cheers for backward compatibility.

Once Installed Run the below script to enabled SQL Server to access the provider which in turn can then access the files like as if they were tables.

 

SP_CONFIGURE 'show advanced options', 1;
GO
RECONFIGURE;
GO
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1;
GO

 

AS you can see above, we need to install Ad Hoc Distributed queries. This is mainly so that we can query the metadata of the tables (CSV Files) from with SSMS. Without the above step you will encounter and error about access denied or could not establish a connection, depending on which version of SQL you’re querying from.

RECONFIGURE;

EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′,N’AllowInProcess’, 1

The above line of code is important so that the execution context of the query doesn’t change from that of the user who ran the query i.e in runs in the same context as the process calling it. With this we are done setting up the bare minimum need to do the job.

Next we need to setup the linked server, mostly because I already have a number of file and can’t be bothered to write openrowset queries for each file.

Once the above script has been executed you can create a linked server connection to the folder which has the csv files and SQL will in turn list the CSV files within the linked server connection as if they were tables.

EXEC master.dbo.SP_ADDLINKEDSERVER @server =N'CSV',@srvproduct=N'CSVFILES',@provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'G:\Data', @provstr=N'text'

GO

After this you can query the files just like a normal table such as select * from CSV…tablename