Pointers and links to AdventureWorks

Pointers and links to AdventureWorks

This short blog provides you almost everything you are looking for related to AdventureWorks, sample database, sample SSAS, and so.

• Global search strings (https://www.bing.com/search?q=AdventureWorks%20Download )
  
• CodePlex search strings (http://www.codeplex.com/site/search?query=Adventureworks&ac=2 )
  
• Direct download links (https://msftdbprodsamples.codeplex.com/releases/view/55330 )
  

But I am not sure where are some of these going to move once Microsoft removes codeplex as known.

Thanks to Kevin Farlee from MS providing this links.

Advertisements

C# Open Excel with Read Only access

This short blog shows you how to open Excel with Read Only access because it is protected by password.

When you read this blog Write Excel file to text file with any delimiter (https://chanmingman.wordpress.com/2011/10/10/write-excel-file-to-text-file-with-any-delimiter/).

You will see a line

//Open Spreadsheet

book = app.Workbooks.Open(strFileName, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);

When this line execute on the Excel file with password protection then you will get the prompt like the one below.

To avoid the prompt, you need to change the line of code to

book = app.Workbooks.Open(strFileName, Opt, true, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);

Format XML document using Code Writer

This short blog shows you how to format the XML document using a Windows App called Code Writer.

You could see I opened the XML file and it appeared everything in one long line.

Select … at the left bottom.

Select Advance then choose Format Document.

The XML document is now in proper format.

Code Writer is actually pre-installed with Windows Creator edition. You can find it from Windows Store if you do not have it.

C# Look at values throughout recursive function calls

This blog shows you how to get to the Parallel Watch window and you may face problem with it.

In one of the blog of Microsoft “7 More Lesser-known Debugging Tactics for Visual Studio” is showing the Parallel Watch window.

I have gotten the factorial code from the web.

Below is the factorial recursive sample

static
void Main(string[] args)
{
Console.WriteLine(“Enter a number”);
int number = Convert.ToInt32(Console.ReadLine());
long fact = GetFactorial(number);
Console.WriteLine(“{0} factorial is {1}”, number, fact);
Console.ReadKey();
}
private  static  long GetFactorial(int number)
{
If (number == 0)
  {
return 1;
   }
return number * GetFactorial(number – 1);
}
The blog tells you to Open the Parallel Watch window (Debug/Windows/Parallel Watch) but it does not tell you, you can only do that during run time and not design time.

I could see the “number” variable in the Parallel Watch but when I tried to watch the “fact” variable VS 2017 crashed.

Parallel Watch is a nice feature but still has room to improve.

Reference:

https://blogs.msdn.microsoft.com/visualstudio/2017/09/18/7-more-lesser-known-debugging-tactics-for-visual-studio/?MC=DevOps

SQL Server delete the duplicate rows

This short blog basically a continue version of the blog SQL Server find the duplicate rows (https://chanmingman.wordpress.com/2017/09/23/sql-server-fine-the-duplicate-rows/).

Once you find the duplicated row then you want to delete them. Before that, do store your unique row of your duplication rows first. Delete the duplicated rows then insert the unique row back into the table. You can keep this temporary unique row in another table or your RAM depend on how big is your data.

You need to append the top and bottom part to your existing SQL statement so you can delete the duplicated rows.

The top part will be

DELETE
FROM ship2

FROM Shippers1 ship2

inner
join

(

The bottom part is

)

as ship3 on

ship2.CompanyName = ship3.CompanyName

AND ship2.Phone = ship3.Phone

The complete statement as below.

DELETE
FROM ship2

FROM Shippers1 ship2

inner
join

(

SELECT ship.CompanyName, ship.Phone

From [dbo].[Shippers1] ship

inner
join

(

SELECT CompanyName, Phone,

COUNT(*)

as NumberofDuplicates

FROM [dbo].[Shippers1]

Group

by CompanyName, Phone

HAVING

COUNT(*)

> 1

)

as ship1 on

ship.CompanyName = ship1.CompanyName

AND ship.Phone = ship1.Phone

)

as ship3 on

ship2.CompanyName = ship3.CompanyName

AND ship2.Phone = ship3.Phone

SSIS find and remove the duplicate rows

This short blog shows you how to list the data in duplicated rows using.

Let’s look at the Northwind (https://chanmingman.wordpress.com/2012/03/18/northwind-in-microsoft-sql-server-2012/) database in SQL Server. You can see the Shippers table has 2 duplicate rows, United Package.

Duplicate the Shippers table.

1. Create a SSIS package.

2. Drag a Data Flow Task from SSIS Toolbox to Control Flow area.

3. Drag OLE DB Source, Aggregate, and OLE DB Destination from SSIS Toolbox to Data Flow.

4. Link them together by pulling the blue arrow.

5. Configure the OLE DB Source. First create a connection manager.

6. Configure the OLE DB Source.

7. Configure the Aggregate.

8. Configure the OLE DB Destination.

9. Mapping the columns.

Run the package to test it now.

How to see quarantined files in windows defender

This short blog shows how to see quarantined files in windows defender.

Many of the links out there are already obsolete. This is based on Win 10 Build 15063.

1. Press the Start button.

2. Type Windows Defender then choose Windows Defender Security Center.

3. Choose Scan history.

4. Under the Quarantined threats, select See full history.