Author Archives: Gary Melhaff

About Gary Melhaff

Data integration by day, saxophone worship by night

Product Reviews

Review of Melissadata Matchup for SSIS

Melissadata Review

 

Review of Wherescape Red

Wherescape Red Review

 

Review of Microsoft Master Data Services (MDS)

MDS Review

 

Review of Microsoft SqlServer Integration Services (SSIS)

SSIS Review

 

Advertisements

Query Oracle using Visual Studio? That’s just crazy talk

Or maybe not!  Here’s how you can do it.

There are other older and alternative drivers but I found the setup easiest with the latest Oracle and Microsoft software. And the latest version of SSDT can be installed alongside older software as well as develop backwards compatible SSIS and SSRS. Oh and schema and data compare are now built-in! So why are you waiting to upgrade?

Install SSDTSetup.exe file  (this is the new 2016 SSDT software that includes visual studio 2015) found at https://msdn.microsoft.com/en-us/library/mt204009.aspx

Install SetupODTforVS2015.exe file found at Oracle’s download site.

Copy your tnsnames.ora and sqlnet.ora files to c:\program files (x86)\oracle developer tools for vs2015\network\admin\

When you start SSDT or just visual studio, to go Tools – Connect to Database – Change the data source to Oracle Database with Data provider of ODP.NET – Next. The Data source name should reflect the databases in your tnsnames.ora file if its setup correction. Just sign in as normal. It will then appear in your server “Server Explorer” tool under Data Connections.

Capture

You should end up at this screen with the ability to scroll through your tnsnames aliases and the rest should be obvious…

Capture2

 

 

Sql Server Self-Join Child to Parent Hierarchy

Problem:   Series of child-parent relationship rows where you need to find the topmost parent.

Solution: TBD!

Script to create the situation below…

USE TempDb
GO
— Create a Table
drop table party_merge
go
CREATE TABLE Party_merge(
FromPartyID INT PRIMARY KEY,
ToPartyID INT
)
GO
— Insert Sample Data
— 3 gets gets 4 children (1, 2, 8, 9) assigned to merge to it and then itself gets merged to id=10
— 2 is the new parent for 4, 5, and 7
— 11 gets 12 assigned. Only parent 12 has only one child (11)

— Note:
— 14 gets parent assigned as 14 (due to source system issue)

INSERT INTO Party_merge
SELECT 1, 3
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 14, 14
UNION ALL
SELECT 4, 2
UNION ALL
SELECT 5, 2
UNION ALL
SELECT 7, 2
UNION ALL
SELECT 8, 3
UNION ALL
SELECT 9, 3
UNION ALL
SELECT 3, 10
UNION ALL
SELECT 11, 12
GO
— Check the data

SELECT *
FROM Party_merge
ORDER BY FromPartyID, ToPartyID
GO

—————————————————–
SELECT p1.FromPartyID, p1.ToPartyId
FROM Party_merge p1
LEFT OUTER JOIN Party_merge p2
ON p1.ToPartyID = p2.FromPartyID
WHERE p1.FromPartyID <> p1.ToPartyID
———————————–

Sql Server – When ISNULL results in “Insufficient result space to convert” …

This is a common error when doing null handling when replacing incoming value with something larger since the datatype defaults based on the incoming value.

You need to not just CAST or CONVERT to a different larger datatype but once but you have do it twice – both on the incoming column and then on the result. This can also happen when doing aggregations as well as it may demand overriding the default decimal or integer size for the resulting value.

Below demonstrates a further complication where CAST doesn’t work but CONVERT does…

Following doesn’t work…
SELECT CAST(ISNULL(CAST([Person_Acxiom_Abilitec_Id] AS VARCHAR(36)), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id…

Following does…
CAST(ISNULL(CONVERT(VARCHAR(36),[Person_Acxiom_Abilitec_Id]), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id

The only difference is using CONVERT instead of CAST.

SSRS native mode and Mac – What works and what doesn’t

So does it work or not in native mode?

I suggest that’s a matter of opinion. Can it work? Yes. The same as in IE? No.

Does it require custom hacks to add JavaScript to SSRS server config file like so many sites suggest? I couldn’t get any of those to make any difference and wasted a lot of time trying.

So what’s the answer you are wondering if I’ll ever get to?….

First realize that only IE fully supports ADFS security. So that’s one hurdle regardless of Mac or pc. The other is rendering the report.

For security forget single signon to your database (integrated security). Use embedded credentials instead if you don’t want to require user to signon. This of course requires managing AD accts or groups at the Ssrs folder level.

Now that security is working, on to rendering…

For this use web service URL (default of //servernm/ReportServer).

This works on Mac in safari and firefox. Chrome on Mac… Not so much.

Hey I didn’t say it’s perfect solution but it works!

So…
Using integrated security doesn’t work – at least not reliably. Don’t waste your time trying to get it to work on a Mac. I’ve seen it work a number of times but then just stop working. I think it has something to do with the keychain but after many hours of attempts to get it to work, nothing works very long. There is only one way I have found to get it work reliably.

Using Safari, embedded credentials, and calling the report via URL that is server name/ReportServer rather than Reports works really well. That combination is reliable and offers the advantage of not providing bread crumbs which lessens the chance that your customers will rely upon your folder structure not changing. It also offers the ability to send parameter values in the URL if you desire such as starting the report with a value in the parameter or rendering as a pdf, etc.

Testing SSRS with browsers other than IE can be tricky due to two reasons: IE is the only one by default that handles the double hop of integrated security. The other is that IE is the only one that renders reliably using the standard menu system of folders in SSRS. Macs add the additional complexity in that they don’t like ADFS security. IE stopped being supported on a Mac since around version 4.5 or 5 about a decade ago. That’s unfortunate. We have also found that Firefox seems to operate equally well.

Yes there are lots of web sites that tout back end hacks to add java script to alter the default rendering but with 2012 SP1, I couldn’t make any of those work and the above combination is reliable.

An option for Chrome on the PC is that you can download/install the IE add in which effectively calls IE in the background so it then seems to work just as well as IE.

Why do people in IT never learn from the past? My rant…

Does your environment include the following?

No logging, no error notification, flat file table designs for reporting, no compression, no partitioning, poorly indexed, endless layers of views and functions, truncate/load tables used for reporting (so if something goes wrong, no data), silos of redundant and inconsistent data & databases, excessive latency due to poor architecture, chaining marts from other marts… Along with ever increasing support staff just to keep the lights turned for an application.

Does that describe your architecture or lack thereof?

And you wonder why data warehouses fail?  Why executives are tired of excessive costs? Why IT gets a bad name?

But there’s always the latest buzzword that will save us…big data, data lakes, virtualization, in-memory databases, columnar databases…but it turns out those are all just tools not solutions. They often end up in the wrong hands and increasing costs while avoiding the real issues of attention to detail, taking the time to produce quality work, and hiring competent staff.

Do you find yourself re-explaining 101 concepts such as re-use, encapsulation, basic data movement physics?  Or justifying logical modeling as if understanding your organization’s information or a structured approach to documenting data architecture is obsolete?

That’s my rant for the day from someone with 3 decades of IT experience and tired of hearing of the same old excuses and seeing the same old problems simply because people are not learning from the past and repeating mistakes.