When i want to create this stored procedure, it specifies that i need to set the ANSI_NULLS and ANSI_WARNINGS. I already have. But it runs fine in Query Analyzer.


SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

declare @day as char(2)
declare @month as char(2)
declare @year as char(4)
declare @Exportdate as char(10)

set @day =datepart(dd, getdate())

if len(@day) = '1'
set @day = '0' + @day

set @month =datepart(mm, getdate())

if len(@month) = '1'
set @month = '0' + @month

set @year =datepart(yy, getdate())

set @exportdate = @year + '-' + @day + '-' + @month
--print @exportdate

Declare @counter as char(3)

set @counter = (select count(*) from directory where
startdate = @exportdate
and directoryid not in(select directoryid from statenet.dbo.nec106)
)

If @counter > 0
begin
insert into statenet.dbo.tblCAABimport
(
firstname,
surname,
directoryid,
Extension,
department,
departmentcode,
startdate,
type
)
(
SELECT
z.firstname,
z.surname,
b.directoryid,
c.extension as Extension,
y.department,
y.departmentcode,
b.startdate,
'ADD'
FROM invdirlink b
INNER JOIN
inventory c
ON
c.inventoryid = b.inventoryid
JOIN
dirdeptlink x
on
x.directoryid = b.directoryid
JOIN tblCAABdept y
on
x.departmentid = y.departmentid
JOIN directory z
on
b.directoryid = z.directoryid
WHERE
z.firstname not like 'e.%'
and
z.surname not like 'e.%'
--and
--b.startdate = @exportdate
and
z.directoryid not in
(select directoryid from vots138svr01.statenet.dbo.nec106)
)
end
else
print 'No updated records exists from CAAB for ' + @exportdate