Microsoft OLE-DB Provider for DB/2 ( for SQL Server v2014 )

Download

Microsoft bundles a database connectivity provider for DB/2.

It is bundled within the Feature Pack.

Depending on the version of SQL Server you have installed, you will be downloading a different package.

For us, we are on SQL Server 2014.  And, so will be downloading the Feature Pack for that version.

Download Link

Microsoft SQL Server Feature Pack is available here

Link

Download

Link

Image

Details

There are two files, ENU\DB2OLEDBV5_x64.msi and ENU\DB2OLEDBV5_x86.msi.

Depending on your OS bitness, SQL Server bitness, and use-cases Server ( Engine [ Linked Server], Development [Business Intelligence Development Studio [BIDS] ) you will likely need one or both files.

Installation

Install Microsoft OLE-DB Provider for DB/2

License Agreement

Image

licenseAgreement_20170922_0208PM

 

Registration Information

Image

RegistrationInformation_20170922_0208PM

 

Feature Selection

Outline

  1. Changed installation path from “C:\Program Files\Microsoft OLE DB Provider for DB2” to “E:\Program Files\Microsoft OLE DB Provider for DB2”
    • The basis for the change being we will like to reserve our system drive (C: ) for the OS

 

Image

featureSelection_20170922_0209PM

 

Revised

featureSelection_20170922_0210PM

 

Ready to install the Program

ReadyToInstallTheProgram_20170922_0210PM

 

Installing

 

Installing_20170922_0211PM

 

Validation

 

UDL File

Steps

  1. Launch Windows Explorer
  2. Create a new udl file
    • The file can have any name as long as it’s extension is udl
  3. Once the file is created, please right click on it, and choose the Open menu button
  4. Screens
    • Connection

 

Images

Connection

The first tab that comes to focus is the “Connection” tab.  We will be good if we are trying to a SQL Server DB, but as we will be connecting to DB/2, we will go back and choose the Provider tab.

Provider

Provider – Original

Provider – Revised

Provider – Explanation
  1. Changed provider from “Microsoft OLE DB Provider for SQL Server” to “Microsoft OLE DB Provider for DB2

 

Advanced

Advanced – Original

SQL Server

Linked Server

Add new Linked Server

Linked Server – General

Image

Textual
Item Meaning Value we used
Linked Server The name that the Linked Server will be referred to DB2
Server Type Other Data Sources Other Data Sources
Provider Provider name of the providers installed on the system Microsoft OLE DB Provider for DB2
Product Name Can be anything
Data Source Please leave blank
Provider String Please get Provider String from your DB/2 team Data Source=dbraq.labdomain,org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=3300
Location Disabled when provider is DB/2
Catalog  Initial default catalog MVSBQ

Linked Server – Server Options

Image

 

Validate Linked Server

sp_tables_ex

Query


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

TroubleShooting

Network Address – When “Fully Qualified Domain Name” not used
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go

/****** Object:  LinkedServer [DB2RAQ]    Script Date: 9/23/2017 8:49:54 AM ******/
EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=raq;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "A TCPIP socket error has occured (10022): An invalid argument was supplied.".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 35]
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2RAQ".

Incorrect user credentials
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    exec sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go


EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq.labdomain.org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.".

Summary

Microsoft provides a very capable OLE-DB Provider for DB/2.

Please keep in mind it is not an ODBC nor JDBC connectivity library.

What datatype is the null value?

Background

A few weeks ago, I ran into a Transact SQL problem and I was forced to ask myself why two database objects were not compatible.

In the rest of this post, I will share the “foundational”  scenario and try to start answering the question, what datatype is Null.

 

Scenario

Our baseline scenario is to join data from two data-sources.  In our case, data from two sources have being brought into our databases and are now available as two tables.

 

Lab

Let us lay out our database objects.

 

DDL – Tables

 

Let us create our two tables, datatype4null.person and datatype4null.organization.

 

Table – datatype4Null.person

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop table datatype4Null.person
*/
if object_id('datatype4Null.person') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[person]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [employer]  varchar(255) null

    , [dateofBirth]  datetime not null

    , [age] as datediff(year, [dateofBirth], getdate())

    , [gender] bit null

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullPersonUniqueID default NEWID()

)

go

set noexec off
go

 

Table – datatype4Null.organization

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go

/*
    drop table datatype4Null.organization
*/
if object_id('datatype4Null.organization') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[organization]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullOrganizationUniqueID default NEWID()


)
go

set noexec off
go

 

 

DML – Insert data

Let us insert data into our two tables.

Table – datatype4Null.person – DML

Insert data into the person table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[person]
go

insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Mustapha Thompson', 'NBC News', '4/7/1990', 1)
;


insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Stephanie Lo', 'Universal Studios', '3/10/1991', 0)
;

 

 

Table – datatype4Null.organization – DML

Insert data into the organization table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[organization]
go

insert into [datatype4Null].[organization]
([name])
values ('Staples')
;

 

DDL – Union View

As we will like to expose the data from our datasources as if they were from a single source, we will use a view to do so.

View – datatype4Null.v_entity_basedonTables

Here is the view that uses a union to merge the data from our two tables.

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnTables]
*/
if object_id('[datatype4Null].[v_entity_basedOnTables]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnTables] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnTables]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[person]  tblP
    
    union all

    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

DML – Fetch data from Union View

 

Let us fetch data from our union view:
SQL:

select *
from   [datatype4Null].[v_entity_basedOnTables]

 

Output:

datafromUnionOfTwoTables

 

 

So everything is good, we are able to fetch from our union.

 

DDL – View

 

But, what happens if we decide we will need to add a bit of logic and we will place some logic and thus use view instead of tables.

Let us create views rather than tables.

 

View datatype4Null.v_person

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_person]
*/
if object_id('[datatype4Null].[v_person]') is  null
begin
    exec('create view [datatype4Null].[v_person] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_person]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender


    from    [datatype4Null].[person]  tblP

go

 

View datatype4Null.v_organization

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_organization]
*/
if object_id('[datatype4Null].[v_organization]') is  null
begin
    exec('create view [datatype4Null].[v_organization] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

View datatype4Null.v_entity_basedonViews_DatatypeMismatched

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , tblO.employer
            , tblO.dateofBirth
            , tblO.age
            , tblO.gender

    from    [datatype4Null].[v_organization]  tblO

go

 

 

Query

Let us query our new Union View; the one that query’s the “Views Union“.

    select *
    from   [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]

 

Output:

But, we get an error message.

 

Image:

Msg 245, Level 16, State 1, Line 55
Conversion failed when converting the varchar value 'NBC News' to data type int.

 

Textual:

Msg 245, Level 16, State 1

 

 

Error Diagnosis

Our error is traced back to how SQL Server deduces and assigns datatypes to literal and computed columns.

Our view is defined as :

 

alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

The value null is defined as as int.

Now thinking forward, to get our datatypes we can issue ” sp_help <object-name>

 

Syntax:

exec sp_help '<object-name>'

 

Sample:

exec sp_help '[datatype4Null].[v_organization]'

 

Output :

datatypeforNullValue

 

Explanation:

From the screen shot above, we can see that SQL Server assigns our null value the int data type.

 

Solution

To fix, we will have to match our datatypes using cast or convert.

An example is pasted below:

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , cast(tblO.id as int)
            , cast(tblO.uniqueID as uniqueidentifier)
            , cast(tblO.name as varchar)
            , cast(tblO.employer as varchar)
            , cast(tblO.dateofBirth as datetime)
            , cast(tblO.age as int)
            , cast(tblO.gender as bit)

    from    [datatype4Null].[v_organization]  tblO

go

 

Query :

select *
from   [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]

 

Output:

datafromUnionOfTwoViewsUsingMatchedViews

 

Watching / Listening

 

Thanks to God for his Sabbath, my lazy Saturday.

Finally got around to watching Robert Redford’s “Horse Whisperer“.

The movie is almost done, but it is paused as I goggled for which songs goes “I was looking for a soft place to fall“.


A Soft Place To Fall” was written by Allison Moorer, Gwil Owen.

Daylight has found me here again
You can ask me anything, but where I’ve been
Things that used to matter seem so small
When you’re looking for a soft place to fall

Don’t misunderstand me, baby, please
I didn’t mean to bring back memories
You should know the reason why I called
I was looking for a soft place to fall

 

Summary

I think Allison Moorer &  Gwil Owen song’s lyrics makes for a good parting.

Incidentally, Demaryius Thomas story on ESPN ( http://espn.go.com/espn/feature/story/_/id/11830457/denver-broncos-demaryius-thomas-dreams-reuniting-estranged-family ) ended up along the same line.

Eli Saslow closed up his moving tribute by evoking Demaryius’s words :

“I’m making it my responsibility to give her a soft place to land,” he said.

Now that we have taking care of one pallid error message, let us return to things that matter.

 

 

 

Technical: Microsoft – SQL Server – Analysis Services – Adding new dimension to an existing Cube, but “No go”

Background

Trying my best to get comfortable with Microsoft SQL Server Analysis Services; specifically Cube Browsing.

Doubt

But, I am running into doubt about my understanding and progress; as when I try to add a new dimension to an existing cube, I am arriving at repeating rows.

Here is a screen shot:

RepeatingDataInCubeBrowser - v2

 

Google

Goggled, but honestly did not know what to Google for.  To properly search the web, one needs a bit of contextual jargon and as I said I am so new at this, I do not even know what to search on.

Leads

After much stumbling, I finally settled on a few leads:

  • In the Data Source View, though I have drawn a relationship between the fact table and our new introduced Dimension, the “Data Relationship” is still a missing the “ForeignKeyName”.
  • In the Cube design, Dimension Usage Tab, we do not have a defined relationship between our new Dimension and our Fact’s Measure Groups

 

Data Relation ForeignKeyName Missing

DataRelationShip-ForeignKeyName-Missing

Cube – Dimensions – Measure Groups – Missing Relationship

Cube-DimensionUsage-MeasureGroups

Problem Source

So how did we get into this problem.  Well our Dimension is based on a table that has a few String columns.

Let us substitute our actual table with one that has a similar structure:

Table Structure

In our fact table (Income), we have Sales Person 1, Sales Person Name, Address 1, Address 2, City, Country, State, and City, Postal Code, Address 1, and Address 2.

Attribute ColumnName Data Type
Sales Person ID SalesPersonID int
Sales Person Name SalesPersonName varchar(100)
Address1 Address1 varchar(100)
Address2 Address2 varchar(100)
City City varchar(300)
State State varchar(300)
Postal Code PostalCode varchar(100)
Country Country varchar(300)
Income Income currency

Imaging for a moment that our business-user will like to be able to query our system based on Postal Codes, Cities, States, and Countries.

Query Fact Attributes

There are a couple of pathways that we can use to query our fact table using the aforementioned geography attributes.

Our choices include:

Dimension Table Structure

Attribute ColumnName Data Type
Location ID LocationID  int identity(1,1)
Postal Code PostalCode  varchar(100)
City City  varchar(300)
State State  varchar(300)
Country Country  varchar(300)

DDL SQL – Dimension (dbo.DimLocation)

Here is the DDL for creating our dbo.dimLocation table.

  • Pasted below is our DDL for creating the dimLocation table
  • Our primary key is PK_DIMLocation and it is based on Location_ID; our Identity column
  • We have a unique index named idx_Unique_Country__State__City__PostalCode.   That index is on Country, State, City, and PostalCode
  • We have another index idx_originatingRecordCreationDate that tracks the dateOriginalRecordCreated column.  This index will afford us quick determination of our last batch import into our dbo.DimLocation


set noexec off
go

/*
	drop table [dbo].[DimLocation];
*/
if object_id('[dbo].[DimLocation]') is not null
begin

	set noexec on

end
go

create table [dbo].[DimLocation]
(
	[Location_ID] int not null identity(1,1)

    , 	[PostalCode] varchar(100) null

    , 	[City]	varchar(300) null

    , 	[State]	varchar(300) null

    , 	[Country] varchar(300) null

    ,   [dateOriginalRecordCreated] datetime not null

    ,   [dateInserted] datetime not null
		constraint LocationDateInserted default getutcdate()

    ,   [InsertedBy] sysname not null
 		constraint LocationInsertedBy default SYSTEM_USER

)
go

set noexec off
go

if not exists
	(
		SELECT 
			OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
			SCHEMA_NAME(schema_id) AS SchemaName,
			OBJECT_NAME(parent_object_id) AS TableName,
			type_desc AS ConstraintType
		FROM  sys.objects tblObject
		WHERE tblObject.type_desc LIKE 'PRIMARY_KEY_CONSTRAINT'
		AND   tblObject.parent_object_id = object_id('dbo.DimLocation')
	)
begin

	alter table dbo.DimLocation
		add constraint PK_DIMLocation primary key 
			(
				[Location_ID]
			)

end
go

if not exists
	(
		select *
		from   sys.indexes tblIndex
		where  tblIndex.object_id = object_id('dbo.dimLocation')
		and    tblIndex.name = 'idx_Unique_Country__State__City__PostalCode'
	)
begin

   CREATE UNIQUE NONCLUSTERED INDEX [idx_Unique_Country__State__City__PostalCode] 
	ON dbo.dimLocation
	(
		  [Country] ASC
		, [State] ASC
		, [City] ASC
		, [PostalCode] ASC
	)
	with
	(
	   data_compression = page
	)
	;

end
GO

if not exists
	(
		select *
		from   sys.indexes tblIndex
		where  tblIndex.object_id = object_id('dbo.dimLocation')
		and    tblIndex.name = 'idx_originatingRecordCreationDate'
	)
begin

	create index idx_originatingRecordCreationDate
	on dbo.dimLocation
	(
		[dateOriginalRecordCreated]
	)
	with
	(
	   data_compression = page
	)
	;

end

go

set noexec off
go

It is always best to be observant of any error messages and warnings.  And, we get one here!

Image Text

Warning! The maximum key length is 900 bytes. The index 
'idx_Unique_Country__State__City__PostalCode' has maximum length of 1000 bytes. For
 some combination of large values, the insert/update operation will fail.

Image Picture

TheMaximumLengthIs900Bytes

DML SQL – Dimension (dbo.DimLocation) – Insert

Pasted below is a set of DML SQL Statements that triggers an error!


set nocount on
go

truncate table [dbo].[DimLocation]
go

while (@@trancount > 0)
begin

	rollback tran

end
go

begin tran

	insert into [dbo].[DimLocation]
	([PostalCode], [City], [State], Country, [dateOriginalRecordCreated])
	values (28201, 'Charlotte', 'NC', 'USA', getutcdate())

	insert into [dbo].[DimLocation]
	([PostalCode], [City], [State], Country, [dateOriginalRecordCreated])
	values (94203, 'Sacramento', 'NC', 'USA', getutcdate())

	insert into [dbo].[DimLocation]
	([PostalCode], [City], [State], Country, [dateOriginalRecordCreated])
	values 
	(
		  replicate('0', 100) -- Postal Code
		, replicate('CITY', 75) -- City
		, replicate('NY', 150) -- State
		, replicate('US', 150) -- Country
		, getutcdate()
	)

rollback tran

go


Error Message Image

TheIndexEntryofLength


Error Message Textual

Server: Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length 1000 bytes for the index 
'idx_Unique_Country__State__City__PostalCode' exceeds the 
maximum length of 900 bytes.

Long Textual Columns

Issues that arise when Indexing & querying long textual columns are voluminous.

Due to the fact that textual searches are a bit slower than numeric searches, sometimes functions are targeted at Strings and smaller datasets are derived.

The numeric and binary data can then be initially compared to shrink the potential data pool.

Microsoft SQL Server has a few functions for the aforementioned purpose:

Hashbytes

Using Hash Columns as Index Column

To help avoid the problem with index keys larger than 900 bytes, we googled and found an excellent article by Microsoft’s Bart Duncan.

Living with SQL’s 900 Byte Index Key Length Limit (by Bart Duncan)
http://blogs.msdn.com/b/bartd/archive/2011/01/06/optionsforindexedlookupsoflongvalues.aspx

From Bart’s Duncan’s article we chose option E, which reads hash the columns and index the hash.

Add Hash Columns



--Adding CountryHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.dimLocation')
		and    tblColumn.name = 'CountryHash'
	)
begin
    alter table dbo.dimLocation
	add [CountryHash] as cast(HASHBYTES('MD5', [Country]) AS VARBINARY(16)) 
           persisted
end
go

--Adding StateHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.dimLocation')
		and    tblColumn.name = 'StateHash'
	)
begin
	alter table dbo.dimLocation
		add [StateHash] as cast(HASHBYTES('MD5', [State]) AS VARBINARY(16)) 
                  persisted
end
go

--Adding CityHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.dimLocation')
		and    tblColumn.name = 'CityHash'
	)
begin
	alter table dbo.dimLocation
		add [CityHash] as cast(HASHBYTES('MD5', [City]) AS VARBINARY(16)) 
                   persisted
end
go

--Adding PostalCodeHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.dimLocation')
		and    tblColumn.name = 'PostalCodeHash'
	)
begin
     alter table dbo.dimLocation
	add [PostalCodeHash] as cast(HASHBYTES('MD5', [PostalCode]) AS VARBINARY(16))           persisted
end
go

Create Candidate Key (Unique Index) on Hash Columns

  • So the plan is to create a unique index against our newly added Hash columns
  • It needs to be a unique index for it to be considered a candidate key
  • Why Candidate key? Because we will be creating a foreign key relationship from the fact table that references it


if not exists
	(
		select *
		from   sys.indexes tblIndex
		where  tblIndex.object_id = object_id('dbo.dimLocation')
		and    tblIndex.name = 
                          'idx_Unique_CountryHash_StateHash_CityHash_PostalCodeHash'
	)
begin

	CREATE UNIQUE NONCLUSTERED INDEX
                 [idx_Unique_CountryHash_StateHash_CityHash_PostalCodeHash] 
	ON dbo.dimLocation
	(
		  [CountryHash] ASC
		, [StateHash] ASC
		, [CityHash] ASC
		, [PostalCodeHash] ASC
	)
	with
	(
		   data_compression = page
	)
	  ;

end
GO

Create Table – dbo.FactSales



set noexec off
go

/*
  drop table [dbo].[factSales];
*/
if object_id('[dbo].[factSales]') is not null
begin

   set noexec on

end
go

create table [dbo].[factSales]
(
	  [Sales_ID] bigint not null identity(1,1)

	, [PostalCode]	varchar(100) null

	, [City]  varchar(300) null

	, [State] varchar(300) null

	, [Country] varchar(300) null

	, [dateInserted] datetime not null
			constraint factSalesDateInserted default getutcdate()

	,   [InsertedBy] sysname not null
			constraint factSalesInsertedBy default SYSTEM_USER

)
go

set noexec off
go

if not exists
	(
		SELECT 
			OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
			SCHEMA_NAME(schema_id) AS SchemaName,
			OBJECT_NAME(parent_object_id) AS TableName,
			type_desc AS ConstraintType
		FROM  sys.objects tblObject
		WHERE tblObject.type_desc LIKE 'PRIMARY_KEY_CONSTRAINT'
		AND   tblObject.parent_object_id = object_id('dbo.factSales')
	)
begin

	alter table dbo.factSales
		add constraint PK_Sales primary key 
			(
				[Sales_ID]
			)

end
go

set noexec off
go

--Adding CountryHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.factSales')
		and    tblColumn.name = 'CountryHash'
	)
begin
     alter table dbo.factSales
	add [CountryHash] as cast(HASHBYTES('MD5', [Country]) AS VARBINARY(16)) 
            persisted
end
go

--Adding StateHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.factSales')
		and    tblColumn.name = 'StateHash'
	)
begin
	alter table dbo.factSales
	  add [StateHash] as cast(HASHBYTES('MD5', [State]) AS VARBINARY(16)) 
            persisted
end
go

--Adding CityHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.factSales')
		and    tblColumn.name = 'CityHash'
	)
begin
	alter table dbo.factSales
	  add [CityHash] as cast(HASHBYTES('MD5', [City]) AS VARBINARY(16)) persisted
end
go

--Adding PostalCodeHash
if not exists
	(
		select *
		from   sys.columns tblColumn
		where  tblColumn.object_id = object_id('dbo.factSales')
		and    tblColumn.name = 'PostalCodeHash'
	)
begin
     alter table dbo.factSales
	add [PostalCodeHash] as cast(HASHBYTES('MD5', [PostalCode]) AS VARBINARY(16))           persisted
end
go

Create Foreign key on dbo.FactSales



if not exists
	(
		SELECT 
			[NAME],
			OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
			SCHEMA_NAME(schema_id) AS SchemaName,
			OBJECT_NAME(parent_object_id) AS TableName,
			type_desc AS ConstraintType
		FROM sys.objects
		WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'
		AND   name = 'FK_CountryHash_StateHash_CityHash_PostalCodeHash'
	)
begin

	ALTER TABLE [dbo].[factSales]  
  	   WITH CHECK 
		ADD  CONSTRAINT [FK_CountryHash_StateHash_CityHash_PostalCodeHash]
		    FOREIGN KEY(
				  [CountryHash]
				, [StateHash]
			        , [CityHash]
				, [PostalCodeHash]
				)
			REFERENCES [dbo].[DimLocation]
				 (
				  [CountryHash]
				, [StateHash]
				, [CityHash]
				, [PostalCodeHash]
				 )
end

GO

SQL Server Analysis Services – Setting up Relationship

Refresh Data Source Views

  • Launched BIDS
  • Accessed Data Source Views
  • In the Editor panel, right click in the panel and choose the “Refresh..” drop-down menu

Review Relationship

Select the arrow that joins factSales and DimLocation.  Once selected, right click on your selection

EditRelationship-FactSales-DimLocation (Arrow)

Edit Relationship

Edit the relationship and you will see that the system picked up our Foreign Key relationship.

EditRelationship-FactSales-DimLocation

Rebuild Project

Let us rebuild our project.

And, we immediately run into problems.

When our foreign key contains binary data types, the system says No!

Textual Error:

Dimension Attribute [Dim Doc].[Doc Hash] : The ‘Binary’ data type is not allowed for the ‘KeyColumns’ property #0

Textual Image:

BinaryDataTypeIsNotAllowedForTheKeyColumn

Solution

We have only a few viable options:

  • Review our original Fact table and see if there is any combination of attributes or function of attributes that can be guaranteed less than 900 and still remain unique
  • Add a new column to the Fact table that we will map to the Dimension Table Primary key

Because our Fact table is so big, we have to be careful and consider the cost of implementing changes.   Areas to consider includes:

  • Do we have enough existing data to reliable generate foreign key data
  • Can the change be done in management and trackable chunks

Other Possible Problems and Error Messages

If you choose to join the Dimension and Fact Table based on non-key columns

  • When we chose our candidate key, we received a warning stating “If you select a non-key granularity attribute, the server will not be able to aggregate data properly unless you make sure that all other attributes are directly or indirectly related to it by specifying them as related attributes“.

For all Dimension attributes, check the KeyColumns attribute

Keep in mind that it is not enough to make sure that the Dimension Key’s do not contain binary columns.

You also want to make to review the keyColumns property for all attributes and make sure that they have not listed the binary column as their KeyColumns.

 

KeyColumnsForBinaryData

Summary

We were lucky as we summarily chose an existing column that appears to be unique.  You might not be.

But, sometimes all there is to telling a story is to say enough for better minds to leapfrog you!

One does not have to win or pre-suppose winning every time one writes.

 

Listening

Listening to …

Avicii ft. Aloe Blacc – Wake Me Up
http://www.youtube.com/watch?v=qXO8hJJjXRk

References

Q/A

Modeling Analysis Services Solutions

Dimension Types

Dimension Relationships

Indexing Wide Chars

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Introduction

So basically we have a SQL Server Database Application and our topology involves a few DB SQL Server Instances.  During development, we are trying to constrain each Development\Developer environment to a single (lone) server.

Processing

Processing – Linked Server – Loopback (Create)



use master
go

/*
	select * from sys.servers
*/

declare @serverNameLocal sysname
declare @lsRemote        sysname
declare @tab		 sysname

set @lsRemote = 'LSREMOTE'
set @serverNameLocal = cast(serverproperty('servername') as sysname)
set @tab = char(9)

if exists
	(
	   select *
	   from   sys.servers tblServer
	   where  tblServer.name = @lsRemote
	)
begin

	print @tab + 'Dropping server ' + @lsRemote + ' ....'

	EXEC master.dbo.sp_dropserver @server=@lsRemote, @droplogins='droplogins'

end

print  @tab + 'Adding Self Server as Linked Server ... ' + @lsRemote

EXEC master.dbo.sp_addlinkedserver 
		  @server = @lsRemote
		, @srvproduct=@serverNameLocal
		, @provider=N'SQLOLEDB'
		, @datasrc=@serverNameLocal

EXEC master.dbo.sp_serveroption @server=@lsRemote
           , @optname=N'collation compatible', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'lazy schema validation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'remote proc transaction promotion'
       , @optvalue=N'false'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @lsRemote
       , @locallogin = NULL 
       , @useself = N'True'

print  @tab + 'Added Self Server as Linked Server ' + @lsRemote

Processing – Create Database (DBLab)


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Processing – Table – dbo.whatisonyourmind (Create)

set noexec off
go

use [DBLab]
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null 
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null 
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Processing – Table – dbo.whatisonyourmindArchive (Create)


set noexec off
go
use [DBLab]
go

if object_id('dbo.whatisonyourmindArchive') is not null
begin
	set noexec on
end
go

/*
	drop table dbo.whatisonyourmindArchive
*/
create table dbo.whatisonyourmindArchive
(

	  [id] bigint not null identity(1,1)

	, [post] nvarchar(600)

	, [addedBy] sysname not null 

	, [addedOn] datetime not null 

	, [modificationType] char (1) not null

	, [archivedBy] sysname not null 
		constraint defaultWhatIsOnYourMindArchivedBy default SYSTEM_USER

	, [dateArchived]  datetime not null 
		constraint defaultWhatIsOnYourMindArchivedOn default getutcdate()

)

go

set noexec off
go

Trigger

Here is a trigger the captures changes occurring on dbo.whatisonyourmind and propagates them unto dbo.whatisonyourmindArchive.


set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_Whatisonyourmind
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        --post changes unto dbo.whatisonyourmindArchive
	insert into dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

        /* process insert/updates as 1 */
	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

        /* remove records that are being deleted */
	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

Data Changes

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('Dev1819')

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV1819.2')

update [dbo].[whatisonyourmind]
set    [post] = 'DEV1819.3'
where  [addedBy] = SYSTEM_USER

delete
from   [dbo].[whatisonyourmind]
where  [post] = 'DEV1819.3'

select *
from   [dbo].[whatisonyourmind]

select *
from   [dbo].[whatisonyourmindArchive]

everything is good!

Trigger – Link Server

A few months later we get a request that we should decouple our OLTP (dbo.whatisonyourmind) and OLAP System (dbo.whatisonyourmindArchive).

And, we created the Linked Server mentioned earlier and change the trigger code a bit.

Here is our new trigger:



set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_WhatisonyourmindLS
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        /* insert into dbo.whatisonyourmind */
	insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

The basic changes between this new trigger and our earlier one is:

  • Changed “insert into dbo.whatisonyourmind” to “insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive”

Data Changes (Failed)

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV6834')

Upon, a quick test, nothing is good, as we get the error pasted below:


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Code Repository

To facilitate quick and broad participation and review,  I have shared this code in github. And, it is available in https://github.com/DanielAdeniji/MSSQLServerLinkedServerLBTrigger

 

Lab Environment

This problem has been tested out in the following environments:

  • Microsoft SQL Server 2012 – SP1 {11.0.2100}
  • Microsoft SQL Server 2008/R2 – SP2 {10.50.4000}
  • Microsoft SQL Server 2000 – SP4 {8.0.2039}

 

Summary

This seems to be a problem and I have been able to reproduce on a couple of MS SQL Server Instances; both of them running MS SQL Server 2012.

And, thus I opened a Connect Item – “Transaction context in use by another session (when LoopBack LinkedServer is used in trigger” {https://connect.microsoft.com/SQLServer/feedback/details/814524/transaction-context-in-use-by-another-session-when-loopback-linkedserver-is-used-in-trigger} to track.

Addendum

2014-04-09 – Connect Feedback – Alexey Stepanov

Alexey commented on the Connect Item and pointed out two Technet Articles that discusses the ‘handicap’:

References

References – Linked Server – Loopback

Microsoft – SQL Server – Transact SQL – Linked Server – Error – “Transaction context in use by another session (Msg 3910, Level 16, State 2, Line 1)”

Introduction

Playing around with Transact SQL today and found a limitation that was not obvious till today.

It concerns Linked Servers and explicit transactions.

Background

Background – Linked Server

So if you create a linked Server Connection; i.e.


USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'dbremote'
          , @srvproduct=N'DEVDB\MSSQL2012'
          , @provider=N'SQLOLEDB'
          , @datasrc=N'DEVDB\MSSQL2012'
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'collation compatible'
             , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'data access'
            , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc out'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'connect timeout'
           , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'collation name'
            , @optvalue=null
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'lazy schema validation'
             , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'query timeout'
              , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'use remote collation'
              , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'remote proc transaction promotion'
              , @optvalue=N'true'
GO

Background – Linked Server – Queries

And, you issue queries against it:

Query – Select

   select *
   from   dbRemote.hr.dbo.employee

Query – Delete

   delete
   from   dbRemote.hr.dbo.employee
   where  employeeID = 10101

everything is good!

Background – Linked Server – Queries – Fail

… until you decide to follow best practices and loop your SQL into an explicit transaction. Additionally, you reference a local resource via 4 part names:

Query – Delete

    begin tran tranCancellation

      --reference a local resource (dbRemote) via 4 part name
      delete
      from   dbRemote.hr.dbo.employee
      where  employeeID = 10101

   commit tran tranCancellation

Result


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Please keep in mind that you ‘re OK as long as SQL code that references local resources do not refer to them via 4 part names.

Query – Delete


    begin tran tranCancellation

      --notice that we are using 3-part and not 4-part names
      delete
      from   hr.dbo.employee
      where  employeeID = 10101

      --OK to use 4 part names for remote resource
      insert into dbConsolidated.hr.dbo.employee
      (employeeID, employeeName)
      values (10191, 'Sallie');

   commit tran tranCancellation

Reason

The reason for the failure is that “Distributed Transactions”, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.

Crediting

Crediting Microsoft’s Piort Rodak for his well written blog posting:

Piotr Rodak – DATA ACCESS setting on local server

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx

The limitation of a loopback linked server is that it cannot take part in an explicit or implicit transaction because this would require loopback distributed transaction, something that MSDTC apparently does not support.

In such scenario, you will get error 3910.

Implications

I reached the same conclusion has Piort Rodak, but habitually I lean towards pushing ahead .. that is, until Goggle lands me where I can actually follow a nicely published and creditable source.

I normally would not face this problem, but did so this time while provisioning a LAB environment and I was trying to use the same machine for multiple SQL Server Instances.

And, also I was trying to use the same code base as the original SQL.  That is, use 4 part names.

 

References

References – Linked Server – Use-case scenario

References – Distributed Transactions on Local Server

References – Transact SQL – Conventions

SQL Server – v2000 – BCP – Error – FUNCTION SEQUENCE ERROR

Introduction

Trying to get data out of SQL Server and hoping to use the quickest path.  One traditional tool is bcp.

Code – Transact SQL

Code – Transact SQL – dbProduct

Create database dbProduct and create a lone table dbo.product.

set noexec off
go

use [master]
go

if db_id('dbproduct') is null
begin

	print 'Creating DB dbproduct ...' 	

	exec('create database [dbproduct]')
	exec('alter database [dbproduct] set recovery simple');

	print 'Created DB dbproduct' 	

end
go

use [dbproduct]
go

/*
	drop table [dbo].[product]
*/
if object_id('dbo.product') is not null
begin

	set noexec on

end
go

/*
	drop table dbo.product;
*/

create table dbo.product
(

 	  [id] bigint not null identity(1,1)
	, [productName] varchar(600) not null
	, [size] varchar(80) null
	, [price] money not null

	, constraint PK_product primary key
		([id])

	, constraint Unique_ProductName unique
		([productName])

	, [addedBy] sysname not null
		constraint defaultproductAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultproductAddedOn default getdate()

)

set noexec off
go

Code – Transact SQL – dbSales

Create database dbSales and create a lone table dbo.order and dbo.orderDetail.


set noexec off
go

use [master]
go

if db_id('dbSales') is null
begin

	print 'Creating DB dbSales ...' 	

	exec('create database [dbSales]')
	exec('alter database [dbSales] set recovery simple');

	print 'Created DB dbSales' 	

end
go

use [dbSales]
go

if object_id('dbo.[order]') is not null
begin

	set noexec on

end
go

/*

	drop table dbo.[orderDetail];
	drop table dbo.[order];

*/

/*

	exec sp_help 'dbo.Order'

	exec sp_help 'dbo.OrderDetail'
*/

create table dbo.[order]
(
 	  [id] bigint not null identity(1,1) 

	, constraint PK_Order primary key
		([id])

	, [addedBy] sysname not null
		constraint defaultOrderAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultOrderAddedOn default getdate()

)
go

set noexec off
go

if object_id('dbo.[orderDetail]') is not null
begin

	set noexec on

end
go

create table dbo.[orderDetail]
(
 	   [OrderID] bigint
 	,  [sequenceID] int not null

	,  [productID] int not null

	,  [NumberofItems] int not null

		  constraint defaultOrderDetailNumbreofItems default 1

	,  [addedBy] sysname not null

		  constraint defaultOrderDetailAddedBy default SYSTEM_USER

	, [addedOn] datetime not null

		  constraint defaultOrderDetailAddedOn default getdate()

	, constraint PK_OrderDetail primary key
		(
			  [OrderID]
			, [SequenceID]
               )

	, constraint FK_OrderDetail foreign key
		(
			  [OrderID]
                )
	        references dbo.[Order]
	        (
			  [id]
                )

)
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.ufn_computedCost

Create Scaler function dbo.ufn_computedCost.


use [dbSales]
go

if object_id('dbo.ufn_computedCost') is not null
begin

	set noexec on

end
go

/*

	drop function dbo.ufn_computedCost

*/

create function dbo.ufn_computedCost
(
	  @productID   int
	, @NumberofItems  int
)
returns money
as
begin

	declare @cost money

	select @cost = 
			@NumberofItems * tblProduct.[price]
	from   [dbProduct].dbo.product tblProduct
	where  tblProduct.[id] = @productID

	return (@cost)

end
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.OrderDetail.cost

On the dbo.OrderDetail table, create a new column (cost) and bind it to the user function dbo.ufn_computedCost.


use [dbSales]
go

/*
	alter table dbo.orderDetail
	  drop column [cost]
*/

if not exists
	(

	    select name
	    from   syscolumns tblColumn
	    where  tblColumn.id = object_id('dbo.orderDetail')
	    and    tblColumn.name = 'cost'

	)
begin

	print 'Adding new column dbo.orderDetail - cost ... '

		alter table [dbo].[orderDetail]
		   add [cost]
			as dbo.ufn_computedCost(
                                                    [productID]
                                                  , [NumberofItems]
                                               )

	print 'Added new column dbo.orderDetail - cost'

end
go

Populate Tables

Let us populate the tables

Populate tables – [dbProduct].dbo.Product


set nocount on;
go

delete from [dbproduct].dbo.product;
go

set identity_insert [dbproduct].dbo.product on
go

	insert into [dbproduct].dbo.product
	([id], [productName], [size], [price])
	values(1, 'Frosted Flakes Cereal', '15 oz',2.98)

	insert into [dbproduct].dbo.product
	([id],[productName], [size], [price])
	values(2, 'Kellogg''s Corn Flakes Cereal', '24 oz', 4.67)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(3, 'Milk - Vitamin D','1 Gallon', 3.65)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(31, 'Chobani Non-Fat Greek Yogurt','15/6 oz', 15.99)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(41, 'Donsuemor Madeleines French Cakes','28 oz', 8.13)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(51, 'Turon','15 pieces', 11.85)

        insert into [dbProduct].[dbo].product
	([id],[productName],[size], [price])
	values(52, 'empanada','7 pieces', 7.00)

go

set identity_insert [dbproduct].dbo.product off
go

Populate tables – [dbSales].dbo.Order and [dbSales].dbo.OrderDetail


set nocount on;
go

delete from [dbSales].dbo.[orderDetail];
delete from [dbSales].dbo.[order];
go

set identity_insert [dbSales].dbo.[order] on

	insert into [dbSales].dbo.[order]
	([id])
	values (1)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 1, 1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 2, 3
	)

        --------------------------------------------------------
	insert into [dbSales].dbo.[order]
	([id])
	values (2)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		2, 1, 1
	)

       -----------------------------------------------------------

	insert into [dbSales].dbo.[order]
	([id])
	values (3)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID], [NumberofItems]
	)
	values
	(
		3, 1, 51,1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID],[NumberofItems]
	)
	values
	(
		3, 2, 52,2
	)

go

set identity_insert [dbSales].dbo.[order] off
go

Bcp Data

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:

"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

Output:

bcp-good

Bcp Data – Failed

Let us imagine that a few weeks later or we are are in the process of moving to a new system and we try to bcp again, but upon issuing an identical BCP Command, we are now getting an error.

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:


"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

 

Error:

Error – Textual

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error

Error – Image

bcp-failed

Error Diagnosis

An error that reads “Function sequence error” seems intimidating. But, no problem.

We issued a select statement against the target table for our bcp:

select * from dbsales.dbo.orderDetail

And, got back an helpful error message:

Server: Msg 208, Level 16, State 1, Procedure ufn_computedCost, Line 24
Invalid object name 'dbProduct.dbo.product'.

Fix

Our fix was to move the dbProduct database to our new system.

In Microsoft SQL Server it is sometimes difficult to find cross database dependencies;  partly because Transact SQL does not allow us to specify those relationships and constraints.

Using the code pasted below, we tried relating the dbo.OrderDetail.productID column in the dbSales database to the id column in the dbProduct.dbo.product table.

     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[product]
       (
    	  [id]
       )

But, you will get an error stating “cross-database foreign key references are not supported.



Server: Msg 1763, Level 16, State 1, Line 2
Cross-database foreign key references are not supported. 
Foreign key 'dbProduct.dbo.product'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

Please keep in mind that if you try to reference an entirely missing database object, you will get a different error.

In the example below, we tried referencing a missing object (dbProduct.dbo.productMissing):


     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[productMissing]
       (
    	  [id]
       )

And, get an error stating “references invalid table ….”


Server: Msg 1767, Level 16, State 1, Line 1
Foreign key 'FK_OrderDetail_Product' references invalid table 'dbProduct.dbo.product1'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

More research might reveal different root causes:

  • Someone might have renamed the dbProduct.dbo.product table
  • In our case, the dbProduct database had not yet been moved to our new system

Conclusion

Yes,  error messages are sometimes unpleasant.  And, quick Google searches might find problems that a bit worse and laiden with unrelated riddles.

But, steady and progressive remediation steps wins the day!

References