Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

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.

 

 

 

Microsoft – SQL Server – Backup Failing with error Operating System error 5 (Access is denied)

Background

Checking a SQL Server backup and seeing that backups are failing.

 

Error

Error Message:



Error: 18204, Severity: 16, State: 1.

BackupDiskFile::CreateMedia: Backup device '\\backupServer\DBbackupProduction\model_backup_2014_04_17_094400_0249947.trn' failed to create. Operating system error 5(Access is denied.).

Error: 3041, Severity: 16, State: 1.



Error Image:
backupFailureErrorMessages

 

 

Security Audit – Backup Server – NTFS Share and Folder Permissions

The first thing I did was connect to the backup server using Computer Management.  Then accessed shared resources permissions set.

Computer Management – System Tools – Shared Folders – (choose shared resource) – Tab – Shared Permissions

ComputerManagement-SharedFolders-SharePermissions

 

Our service account has full permission to the Shared resource.

 

Computer Management – System Tools – Shared Folders – (choose shared resource) – Security Tab

ComputerManagement-SharedFolders-Security

 

Our service account has full permission to the NTFS folder.

 

Security Audit – SQL Server Agent – Log On As

I checked the SQL Server Agent over and over again and tried seeing why it will be failing.

runas

Also, investigated using runas… This will allow me to start a new session that fully impersonates our SQL Server Agent account.


C:\>runas /user:LABDOMAIN\SqlAccount  "dir \\dbBackupServer\sqlbackup"
Enter the password for LABDOMAIN\SqlAccount:
Attempting to start dir dbBackupServer\sqlbackup as user 
"LABDOMAIN\SqlAccount" ...
RUNAS ERROR: Unable to run - dir \\dbBackupServer\sqlbackup
1385: Logon failure: the user has not been granted the requested logon 
type at this computer.

 

Obviously, the “run as” failed as the account does not have Local Security Privileges “Logon locally”

The user has not been granted the requested

 

The “Logon Type” is important.  It is #2; which means “Interactive”.

But, even granting that account principal access on DB computer did not help.

 

Security Audit – SQL Server Service \ Log On As

I went back and checked the SQL Server DB itself and noticed that “SQL Server” is running as “LocalSystem”.

SQLServerConfigurationManager

I knew immediately that LocalSystem will usually not have network privileges.

And, so went ahead and changed SQL Server “Log on As” account.

Please do so using “Sql Server Configuration Manager” as doing so will allow the SQL Server Software to properly change to the new Account and make all necessary NTFS Security changes.

 

LogOnAsAccountChange

Please choose to restart your DB Engine.

Continue Reading

Logon Types Code

Please read through Randall F. Smith “Logon Type Codes Revealed” article for the best coverage of Logon Types ( http://www.windowsecurity.com/articles-tutorials/misc_network_security/Logon-Types.html )

 

Null Session Shares

If temporarily you will like to be continue to use Local System account on the DB Server and be able to access network shares on remote servers, please consider reading “How to enable null session shares on a Windows 2000-based computer”
http://support.microsoft.com/kb/289655 ).

Basically, it covers how to create a to a multi-string entry called HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares on the remote backup machine and adding each share you will like accessible by remote local system accounts.

 

Conclusion

I really had a rough time with this as I was looking to SQL Server Agent as being the instigator of backup activities.  But, really it ended up being the SQL Server Engine itself.

Please keep an eye on the “SQL Server VSS Writer” service, as well.

 

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

Microsoft – SQL Server – Error – Error: 17066, Severity: 16, State: 1 // SQL Server Assertion: File: cxrowset.cpp, line=1538 Failed Assertion = ‘pXacc–>FHasVirtualColumn()’. This error may be timing-related

We started getting this “error – Error: 17066, Severity: 16, State: 1:”

Due to its severity level 16 and SQL Server’s suggested remediation steps:

  • Run DBCC CheckDB (to check database for structural integrity)
  • Restart the server (to ensure in-memory data structures are not corrupted)

…. I am thinking I am in for long nights…and not the nice ones; actually the grumpy – I am a DBA ones…

SQL Server Error logs reads —


2013-01-03 01:22:28.12 spid59 Error: 17066, Severity: 16, State: 1.
2013-01-03 01:22:28.12 spid59 SQL Server Assertion: File: &lt;"cxrowset.cpp"&gt;, line=1538 Failed Assertion = 'pXacc-&gt;FHasVirtualColumn()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2013-01-03 01:22:28.12 spid59 Error: 3624, Severity: 20, State: 1.
2013-01-03 01:22:28.12 spid59 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
2013-01-03 01:22:28.13 spid65 Using 'dbghelp.dll' version '4.0.5'
2013-01-03 01:22:28.13 spid65 **Dump thread - spid = 0, EC = 0x00000009E92D64B0
2013-01-03 01:22:28.13 spid65 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0440.txt
2013-01-03 01:22:28.13 spid65 * *******************************************************************************
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * BEGIN STACK DUMP:
2013-01-03 01:22:28.13 spid65 * 01/03/13 01:22:28 spid 65
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Location: "cxrowset.cpp":1538
2013-01-03 01:22:28.13 spid65 * Expression: pXacc-&gt;FHasVirtualColumn()
2013-01-03 01:22:28.13 spid65 * SPID: 65
2013-01-03 01:22:28.13 spid65 * Process ID: 2036
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Input Buffer 255 bytes -
2013-01-03 01:22:28.13 spid65 * 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
2013-01-03 01:22:28.13 spid65 * ÿÿ &amp; 01 00 00 00 ff ff 0d 00 00 00 00 01 26 04 04 00 00 00
2013-01-03 01:22:28.13 spid65 * ç@ Ð 4Ž @ P 0 00 00 00 e7 40 1f 09 04 d0 00 34 8e 05 40 00 50 00 30
2013-01-03 01:22:28.13 spid65 * n v a r c h a r 00 20 00 6e 00 76 00 61 00 72 00 63 00 68 00 61 00 72
2013-01-03 01:22:28.13 spid65 * ( 4 0 0 0 ) , @ P 00 28 00 34 00 30 00 30 00 30 00 29 00 2c 00 40 00 50

Yes, young one, this is not an easy one.

And, you are possibly thinking “If you wanted cryptic error messages, you would have stayed with C++ and be an Old School hacker”

But, anyways as you have friends on the .Net, you have come to the right place.

Couple of things you want to keep in mind:

  1. What is your spid – In our case 65
  2. As spid is 65, quite a bit above 50 we are in user’s land; the session that is failing is not a system process, but a user process
  3. Also keep in mind the process ID, for those running multiple SQL Server Instances on the same server
  4. Then try your best to read through the InputBuffer — It contains every thing you need to tell you which SQL Statement is failing

Our problem was traced back to the day I took that proverbial short-cut:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionIDOrig as 0 persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionIDOrig]
		)

		ON [partitionScheme]([partitionIDOrig ])

)
go

If you take a quick look at our definition for partitionIDOrig, you will see it is hard-coded to be 0.

Everything worked well when we were inserting into the table.

But, once we changed to Merge, things broke.

To fix:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionID as isNull(cast(rand(100) as int), 0) persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionID]
		)

		ON [partitionScheme]([partitionID ])

)
go

Our new partitionID is an actual computed \ variant column:


    partitionID as isNull(cast(rand(100) as int), 0) persisted

 

I think when Microsoft invokes FHasVirtualColumn(), they are asking whether we have Virtual columns — And, things “stumble” a bit when the calculated column is actually a hard-coded value.


References:

MSSQL Server – Database Recovery [Manual] (after a crash) – MS SQL Server v2005 and above

 

Tried out Paul Randal’s database recovery steps (http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx)  and it works a charm —

Note that the database server will try to and most like recover itself.  But, in our case, the time for a successful recovery was too much and so we chose to ‘circumvent’ possible recovery by renaming the database log files.

Here are the steps to re-create database crash & manual recovery:

  • Create database Demo
  • Create Table
  • Generate Transactions
  • Get MS SQL Server’s Process ID
  • Kill MS SQL Server Process
  • Rename Database Log Files
  • Restart MS SQL Server Service
  • Execute Manual Restore
    • Set database to emergency mode
    • Set database to single user
    • Execute DBCC CheckDB with repair_allow_data_loss
    • Set database to multi_user

 

Create Database Demo

 


if db_id('Demo') is not null
begin
    DROP DATABASE Demo;
end
go

CREATE DATABASE Demo
ON
PRIMARY
(
  NAME = Demo_PrimaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_PrimaryDataFile.mdf'
    , SIZE = 3 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile2,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile2.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
)
LOG ON
(
  NAME = Demo_LogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
,
(
  NAME = Demo_SecondaryLogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
COLLATE Latin1_General_CI_AS;

 

Create Table – Session

 

use [Demo]
go

if object_id('session') is not null
begin
drop table session
end
go

create table session
(
  [id] int not null identity(1,1) primary key
, [dateAdded] datetime not null default getdate()
, [serverName] sysname not null default @@servername
)
go

 

Generate Transactions


use [demo]
go

set nocount on
go

declare @id int

set @id = 1

while (@id != 0)
begin

      insert into session default values

      set @id = @id + 1

      print @id

end
go

 

Get MS SQL Server’s System Process ID (PID)

use [master]
go

select serverproperty('processid') as processID


 

Using Task Manager kill that process (PID)

  • Launch Task Manager
  • Identify the matching Process ID
  • Kill that Process

 

 

 

Using Windows Explorer – Rename \ Move the Log files

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf to xDemo_LogFile.ldf

 

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf to xDemo_SecondaryLogFile.ldf

 

 

 

Restart MS SQL Server – And, the erroglog  will indicate that the Demo database can not be brought online


2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'. Diagnose and correct the operating system error, and retry the operation.

2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf" may be incorrect.
2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'. 

Diagnose and correct the operating system error, and retry the operation.
2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf" may be incorrect.

2010-09-27 23:20:26.75 spid24s     The log cannot be rebuilt because the database was not cleanly shut down.

 

To fix do the following:

  • Set database to emergency mode
  • Set database to single user
  • Execute DBCC CheckDB with repair_allow_data_loss
  • Set database to multi_user

 

 


ALTER DATABASE Demo SET EMERGENCY;
GO 

ALTER DATABASE Demo SET SINGLE_USER;
GO

DBCC CHECKDB (Demo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

ALTER DATABASE Demo SET MULTI_USER;
GO

 

 

References

  1. Creating, detaching, re-attaching, and fixing a suspect database
    http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx