Related Posts

• Setting Firewall rules in new style Azure portal for SQL server

  Since this one caught me out and a few other people it seemed like a good idea to blog about this. Sometime ago the Azure portal got a make over, which l have say make the portal look very pretty.  Whilst setting up a proof of concept for PowerBI l thought it was also a good time to look at setting up an Azure SQL database.  The last time l did this was in the previous portal.  So l duly set up the database, one of the last steps was to allow the IP address from my work organisation access to the database.  So l goggled and found this page - https://azure.microsoft.com/en-gb/documentation/articles/sql-database-configure-firewall-settings/ The Wrong Way Open the new style Azure Portal  Click on browse (1) From the list of blades click once on SQL databases (Figure 1) Figure 1 - Opening the SQL databases blade Click on the 'SQL databases' blade this will open a list of the all the SQL databases that are in your account (Figure 2) Figure 2 - List of SQL databases showing area where to click to show settings Clicking on the ellipsis will ...

• Import sessionize.com JSON data into SQL Server - Part 1

  

  ....TLDR in the next few blog posts I will be showing how to  Export Json data from a Sessionise.com API url Insert the Json data into SQL Server 2016 database Generate a dataset from the imported Json data Display data on SSRS report I love Azure Data Studio with the addition of the notebook feature it's just soooo much more wonderful.  For me this feature is something that makes the product even more amazing.  My first introduction to notebooks was Jupiter notebooks, when I did some courses on Python. The coursework required you to use them to submit coursework using Jupiter notebooks. So when the Azure Data Studio team announced that they would support notebooks I was very excited. Even better the notebooks can support SQL, Python, and several other languages. So this was a great opportunity for me to look at using Python and SQL.  During one of ...

• Import sessionize.com JSON data into SQL Server - Part 2

  

  In the first blog post the Json string from sessonize.com API has been placed into the table dbo.JsonData.  The first check run is to see if the data is valid JSON, using the ISJSON() function.  By running the query shown in Figure 1 the result shows that the data contained in the table dbo. JsonData is valid JSON.   Figure 1 – The function ISJSON() returns 1 showing the data in the field [Data] is valid JSON Now the JSON data is in the SQL database lets see about doing something useful with it.  In SQL Server 2016 there was a number of new functions added that allow the querying and manipulation of JSON data.  Having done some research, I found this blog post - https://visakhm.blogspot.com/2016/07/whats-new-in-sql-2016-native-json_13.html.  Using code in this blog post I was able to extract the data from the JSON string supplied by the API from the sessionise.com website.   Before querying the data I need to explain one concept which is crucial for extracting data from structured JSON.  In the example in Figure 1 below the path of the ‘title’ key value pair is as follows  Sessions.0.title this would ...

• Win 10 Pro and SQL Server 2016 SSRS report mgr

  Ok long story, short. I downloaded and installed SQL server 2016 CTP3 on Windows 10 Pro X64 Virtual machine which was set up for testing purposes. Yes l know that was a silly idea, what the heck living dangerously is fun sometimes! The OS was a standard install and it's standalone as in not connected to a domain or clever stuff. When propmted to enter a user account, l used a personal Microsoft account l have.  Next l installed SQL Server 2016 CTP nothing fancy and choose the native SSRS install (If you just interested in list of steps l followed they are at the bottom of this posting) Next logged in as the user account that was used during set up, this is Microsoft account. Now l wanted to play with SSRS, so open the default browser (Microsoft Edge) and entered the url “localhost/reportserver” and waited. Next got a dialog box asking me to enter my user credentials  So l entered my Microsoft account details, and eventually ended up with the error message below. Yes l know that I should have remembered, ...

• Exploring Temporary Objects in SQL Server

  There are many mysteries in life fortunately there are few about table variables.  That said when I look at other people's code, or when I hear their opinions about Table Variables, there is some mis-understandings about Table variables.  Let's be clear I love table variables, like many things in life if used correctly they are amazing.  It's easy to misuse something especially if we make assumptions and yes, I am a pointing a finger at myself.   When I talk to people about table variables one of the items of advice that I give them, is that table variables are NOT created in memory.  Just like temporary tables are created in TempDB.  There might be some people reading this which disagree with me.  So, let's run some code.  /*Create a table variable, with a column name which is kind of unique*/  DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT);  /*Place some values into the table variable, just so it has something    In the table variable*/  INSERT INTO @TestTableVariable  VALUES(1);  INSERT INTO @TestTableVariable  VALUES(2);  DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT);  IF EXISTS  (     SELECT *      FROM tempdb.sys.tables AS T           INNER JOIN ...

• M (formally know as Power Query Formula Language), PowerBI and parameterising data sources - part one

  One of the attractions of being a programmer was that l could find ways to make routine tasks easier and quicker.  As our company and clients have discovered the joy of Power BI also the reports l have created.  There are more clients are asking for reports.  Some of these are the same reports to ensure everyone has the same view of their data. Scenario There are two databases l populate with data, one of these is on my machine, the other is an Azure database.  As you would expect l use the local copy to test out various changes to the database.  Then using the Redgate tool SQL Compare l can script any changes and apply them to the Azure database.  It is possible to change the data source for each dataset easily using the following method. From the main window, on the 'Home' tab click once on the 'Edit Queries' button see Figure 1 Figure 1 - showing the location of the 'Edit Queries' button This will open the 'Edit Queries' window now to see where the data source for a selected dataset. ...