Power BI find Percentage on 2 columns in 2 different table

This post is showing how to find an average if you have sum of 2 columns in 2 different tables.

For example, I have Sales 1 in Sheet 1.

I have Sales 3 in Sheet 2.

You will think to create a Measure to SUM the 2 columns as some of the posts show you. DON’T, it does not give you error but the figure will never be correct.

You need to create New column not measure but using the same SUM function.

CSales = SuM(Sheet1[Sale1]) + SUM(Sheet2[Sale3])

Then I create a measure like the one below.

Measure = (SUM(Sheet1[Sale1]) + SUM(Sheet2[Sale3])) / SUM(Sheet1[CSales])

This may not be a perfect way but it works for me after trying a lot of methods the Internet suggested.

Advertisements

Sql Server Remove all tables

This post shows you how to remove all the tables in a database. Sometime you just need to refresh all the table but you don’t want to keep deleting and creating the same database. The sql statements below work for me.

DECLARE @sql NVARCHAR(max)=”

SELECT @sql +=
‘ Drop table ‘
+
QUOTENAME(TABLE_SCHEMA)
+
‘.’+
QUOTENAME(TABLE_NAME)
+
‘; ‘

FROM
INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE =
‘BASE TABLE’

Exec
Sp_executesql
@sql

Break all the constraint such as primary keys and foreign keys if you cannot delete the table because of the constrains.

EXEC
sp_msforeachtable
“ALTER TABLE ? NOCHECK CONSTRAINT all”

Reference

https://stackoverflow.com/questions/27606518/how-to-drop-all-tables-from-a-database-with-one-sql-query/32563084

SQL Server on Linux Posts You Might Find Helpful

This post get have the useful links to SQL Server on Linux.

Wanted to share a few links based on various conversions with you all

• https://blogs.msdn.microsoft.com/bobsql/2018/12/18/sql-server-on-linux-forced-unit-access-fua-internals/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/12/18/sql-server-linux-fsync-and-buffered-i-o/
  
  
• https://blogs.msdn.microsoft.com/bobsql/2018/12/18/sql-server-linux-directory-fsync-activities/
  
  
• https://blogs.msdn.microsoft.com/bobsql/2018/12/10/sql-server-instant-file-initialization-setfilevaliddata-windows-vs-fallocate-linux/
  

Previous Posts involving Linux you may also find handy:

• https://blogs.msdn.microsoft.com/bobsql/2018/07/31/sql-server-on-linux-how-is-delete-on-close-handled/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/07/18/sql-server-on-linux-why-do-i-have-two-sql-server-processes/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/05/20/sql-server-on-linux-quick-performance-monitoring/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/04/24/take-the-sql-server-mac-challenge/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/02/22/sql-server-on-linux-cu4-newsequentialid-uuid/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/02/05/sql-server-on-linux-kerberos-troubleshooting-hints-and-tips-and-hard-code-debugging/
  
• https://blogs.msdn.microsoft.com/bobsql/2018/01/28/how-it-works-sql-server-dtc-msdtc-and-xa-transactions/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/12/11/sql-server-linux-distributed-transactions-requiring-the-microsoft-distributed-transaction-coordinator-service-are-not-supported-on-sql-server-running-on-linux-sql-server-to-sql-server-distributed-tr/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/03/27/sql-server-on-linux-core-minidumps-and-breakpad/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/01/31/sql-server-on-linux-scattergather-vectored-io/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/01/29/sql-server-on-linux-an-lldb-debugging-tale/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/01/24/sql-server-on-linux-debugging-elf-and-pe-images-dbgbridge/
  
• https://blogs.msdn.microsoft.com/bobsql/2017/01/05/sql-server-on-linux-elf-and-pe-images-just-work/
  

Thanks to Robert Dorr

SQL Agent System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file

This post shows you one of the possible ways to resolve the error when you are running SQL Agent job and you keep getting the error “System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file”.

The complete message is the one below.

Executed as user: xxx.COM.MY\admin. Unhandled Exception: System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file ‘C:\temp\QC\input\QC_BookingData.xlsx’. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at ConAppExcelDelimiter.ReadExcelFile.ReadExcel(String strFileName, Int32 RowCount, Int32 ColCount, String strDelimiter, Int32 intSheet) at ConAppExcelDelimiter.Program.Main(String[] args). Process Exit Code 255. The step failed.

What you need to do is to create a folder at

C:\Windows\SysWOW64\config\systemprofile\Desktop

Reference:

http://szexperience.blogspot.com/2014/04/systemruntimeinteropservicescomexceptio.html

SSIS Script Task Read Write Variable

This post I wanted to post for quite a while but keep forgetting. In order to use Variable in SSIS Script Task there is no Intellisense. You have to remember the exact syntax for it. Very first you need to choose the Read/Write variable from Script Task Editor.

To read the value

string strSource = Dts.Variables[“User::varOraSource”].Value.ToString();

To write the value.

Dts.Variables[“User::varOraSource”].Value = strSourceFinal;

SSIS Edit Script… not openning

This post shows you one of the remedies when your Edit Script in SSIS is not opening in Visual Studio 2017.

When you need to do is to uninstall the SSDT. Reinstall it with the option Install new SQL Server Data Tools for Visual Studio 2017 instance (SSDT), like below. Do not install in your existing Visual Studio instance again.

MS Access VBA accessing to SQL Server

This article expect you know how to create a Form with a button in Microsoft Access. Like it or not they are still many Microsoft Access users out there today, like a group that I am teaching today. Unfortunately C# has never come into play on Microsoft Office VBA. VBA is still pretty much of VB6 and not very VB.NET alike. People still looking on how to read data from Microsoft SQL Server. I have tested the following code in MS Access 2016 to access SQL Server 2014 as well as Microsoft Access 365 to access SQL Server 2016, it works.

Private Sub Command1_Click()

Dim con As New ADODB.Connection

Dim strSQL As String

Dim rstNwind As New ADODB.Recordset

con.ConnectionString = “Provider=’sqloledb’;Data Source=’TRAINER\SQLEXPRESS’;” & _

“Initial Catalog=’NorthWind’;Integrated Security=’SSPI’;”

con.Open

strSQL = “SELECT * FROM Customers”

With rstNwind

Set .ActiveConnection = con

.CursorType = adOpenKeyset

.CursorLocation = adUseClient

.LockType = adLockOptimistic

.Open strSQL

End With

Set Me.Recordset = rstNwind

Me.txtCompany.ControlSource = “CompanyName”

Me.txtContact.ControlSource = “ContactName”

End Sub

Also don’t forget to set the reference to ADO Library.

Reference:

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/ado-code-examples-in-visual-basic?view=sql-server-2017