1

When I execute the following proc, I get this warning:

Attempting to set a non-NULL-able column's value to NULL.

USE [DbTwo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[TEST_warning_proc]
as 

IF OBJECT_ID('MySchema..vitals', 'U') IS NOT NULL DROP TABLE MySchema..vitals
IF OBJECT_ID('MySchema..order_list', 'U') IS NOT NULL DROP TABLE MySchema..order_list

select * into MySchema..vitals
from DbOne..vitals
where FacilityID in
 (select FacilityID from DbTwo..MySchemaFacilities)

select * into MySchema..order_list
from DbOne..order_list
where FacilityID in
 (select FacilityID from DbTwo..MySchemaFacilities)

How can this be possible since I'm doing a SELECT * INTO? Shouldn't that create a new table that exactly mirrors the original table?

I tried setting:

SET ANSI_WARNINGS OFF

but that didn't help.

Eliezer
  • 429
  • 1
  • 9
  • 20
  • Has any of your tables computed columns? – Lukasz Szozda May 16 '18 at 16:46
  • Are you using select *, or did you shorten your query for brevity? If it's the latter, can you add any columns which you are calling any built in or scalar functions (cast, left, ufn(), etc...) or anything that isn't simply listing the column name? – S3S May 16 '18 at 19:38

2 Answers2

0

I'm suspecting your table isn't getting dropped--maybe there is a block going on for this helper function. You can verify this theory by placing a select top 1 * from vitals right after you attempt to drop it. This is avoidable by using sys.objects. Using sys.objects

create table dbo.vitals (c1 int not null)

insert into vitals
values
(1)

if exists(SELECT 1 FROM sys.objects WHERE name = N'vitals')
begin
    drop table vitals
end

select null as c1 into vitals

select * from vitals

ONLINE DEMO

S3S
  • 24,809
  • 5
  • 26
  • 45
  • I was _really_ hoping your suspicion was correct but unfortunately it isn't. The tables are truly getting dropped; when I try to query them after my drop, there's no such object. – Eliezer May 16 '18 at 17:11
  • Then there must constraint issue. Is there a table referencing these? – S3S May 16 '18 at 17:30
  • There is nothing referencing the new tables at all. I just created these new tables in my proc and no one else even knows about them. But I don't understand what kind of constraint issue there could be: they're dropping successfully. What kind of constraint would cause an error when SELECTing * INTO ? – Eliezer May 16 '18 at 17:42
  • You're right, cause you would have gotten an error when they were dropped if there was a FK on them... i can't reproduce this yet. What happens if you change the table to another table name. Same issue? – S3S May 16 '18 at 18:25
  • What do you mean by "change the table to another table name"? – Eliezer May 16 '18 at 18:26
  • use a different table name in the proc... like `IF OBJECT_ID('MySchema..vitals2', 'U') IS NOT NULL DROP TABLE MySchema..vitals2 .... select * into MySchema..vitals2 from DbOne..vitals where FacilityID in (select FacilityID from DbTwo..MySchemaFacilities)`. I'm trying to narrow it down to these objects or something broader. – S3S May 16 '18 at 18:27
  • Different name, same problem... :-( – Eliezer May 16 '18 at 18:44
0

I found the issue...

When I was "sanitizing" my proc for posting here, I used the names

from DbOne..vitals

and

from DbOne..order_list

These two objects are actually views not tables. I tried running the original SELECT from the definition of the view and got:

Null value is eliminated by an aggregate or other SET operation.

Oops... My apologies.

Eliezer
  • 429
  • 1
  • 9
  • 20