Working with DataGridView in VB.NET

 If you're completely unfamiliar with VB.NET and the DataGridView and you find yourself tasked with adding a data grid to a VB.NET application, using SQL Server, here is what to do:

• Open the solution
• Create a new form, if you need to, by right-clicking Project and clicking New Form
• Draw a DataGridView on the form using the tool in the Tools menu.
• Click the little arrow in the top right and select your data source, or create a new one.
• To create a new data source simply connect to your database and select the tables you want.
• Then back on the form, click the Data Grid View and change the DataSource property to the binding that got automatically created when you added the data grid view.
• Then in DataMember, select the table you want the grid to show.

SSRS Parameters bug checks

 Parameters not passing through:

Are they the same collation?

Are they both trimmed?

Check the parameter is being passed through, not just the name of the parameter

SSIS and Excel Bugs

Some helpful links for importing data from Excel to SQL Server using SSIS:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

https://www.wiseowl.co.uk/blog/s2531/loop-over-excel-worksheets.htm

https://www.wiseowl.co.uk/blog/s2492/excel-worksheets-loop.htm

Choose to install the 32 bit version of the driver above, and then you will see, in the Connection manager, the following option for connecting to Excel:

A Guide to DAX

What is DAX?

DAX is a great language developed by Microsoft for querying data in tables. It is used in Power BI, Power Query in Excel, and SSAS Tabular Models.

What can I do with DAX?

With DAX you can query data in three ways:

• By creating measures that aggregate or sum up columns
• By creating calculated columns
• By creating calculated tables

Note that the calculated tables can be embedded into measures, effectively giving you the ability to do what would be called a subquery in SQL. That is, aggregating data into a dataset, against which you then base another query.

Comparison to Excel Formula Language and SQL

DAX is far more powerful than Excel formula language. Its ability to produce tabular datasets, which can then be queried themselves (i.e. subqueries) makes it more like SQL. But it resembles Excel formula language far more. 

Basic Element of DAX

• DAX has variables, just like SQL. Declare them by writing VAR followed by the variable name.
• DAX has aggregation functions. These look like SUM and AVERAGE.
• DAX has relationships. These are accomplished in Power BI without the need for any JOIN statements at all. You simply connect the fields from multiple tables together, in a GUI.
• DAX has filters (the equivalent to WHERE clauses in SQL). These are accomplished by using the CALCULATE function. 
• DAX can use subqueries (queries within queries). These can be used by either creating new calculated tables, and then basing a query off of that, or by using the SUMMERIZE within a DAX measure to do it all in one go.

Difficulties Learning DAX

• Learning what iterator functions do can be tricky. Effectively they do the same as creating a calculated column and then aggregating the created column.
• Learning why you can't input variables as parameters in certain functions. Note that variables are not calculated independently or before the RETURN statement. So the variable is calculated for every row of data you display. It does not return a single value for every row. It is calculated for every row you display in your visualisation, so may return different values for different rows.
• Learning why, sometimes, you need to perform an aggregation within a measure, rather than refer to another measure that performs the very same aggregation. Again, I have no idea why this is.

SSIS Bugs - It runs on local but not in live

SSIS package running on local but not when deployed?

Here is a check list of things to look at:

• Check whether the Agent is set to run as 32 or 64 bit. Change and test again.
• Check whether the 64 bit mode is enabled or not on the package itself
• Check that the latest version of the OLEDB driver is installed on the machine you deployed to:
• Especially if the error message says "The requested OLE DB provider MSOLEDBDQL.1 is not registered." or "Class not registered."
• download it from here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=56730
• If the owner of the Agent Job has sysadmin or CONTROL server permissions, then the job will run using the SQL Server Agent service account (unless its set to use a proxy). Otherwise it will run as the Job owner. Make sure the relevant account has the right permissions on the server you are trying to connect to. So....
• Ensure the SQL Server Agent account has permission to run on all servers the job touches, if the Job is running as the SQL Server Agent
• Ensure the relevant account (see above) has correct file system permissions if the package is looking at the file system.
• Check that the package Target Server Version is the same version as your server.
• If you can't get it to run with SQL Server Agent, try using a Proxy. 
• First create a system login (use your personal one to test, then have service desk create a security login with a password that never expires)
• Give the system login sysadmin to test
• Then create a credential under Security > Credentials
• Then create a Proxy, adding that credential to it, and put the credential into the principles
• Then in the Job Step, set the Run As property to the credential.

Helpful link:

https://www.sqlservercentral.com/forums/topic/ssis-class-not-registered-error-deployment-or-permissions-issue

Power BI Wish List 2019

Power BI Wish List 2019

• Ability for Power BI to accept parameters from users and interface with SQL Server stored procedures
• Ability for Power BI to e-mail reports like SSRS can

If Power BI had these features, it would end almost all need for SSRS reports.

Types of Join in SQL Server

There are three types of join in SQL Server, and I'm not talking about INNER, LEFT/RIGHT, OUTER or CROSS. I'm talking about how SQL Server works under the hood to actually perform the query.

The query can either use:

• Nested loop join (LOOP)
• Merge join (MERGE)
• Hash join (HASH)

The Query Optimizer in SQL Server will automatically figure out which join to use when you execute the query, but you can tell SQL which to using a 'join hint'. If you find your query is running slow, you can try out different options, like this:

FROM 

Table A a

INNER LOOP JOIN

Table B b

ON a.Col = b.Col

As a rule of thumb, Nested Loops should be used when either both tables in the join expression have a small number of rows (up to 10K), or when one of the tables has many rows, an the other table has few (up to 10K). Merge joins should be used when both tables have a medium amount of rows, and Hash joins should be used when both tables have many rows.