Output where none expected - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Output where none expected

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-13 : 12:38:05

I am getting an output of

No collumn name
2005-01-13 11:31:36.873 ---- Current date time

when I should get none.

Declare @VacID int
select @VacID = 101
Declare @EmployeeId float , @VacDate Datetime, @BDay bit , @DayType Varchar(50),@lastdate Datetime,@sentdate Datetime,@jobcode int
Declare @DefJobcode int,@DefaultRate smallmoney

SELECT @EmployeeId = EmployeeId, @VacDate = VacDate, @BDay = BDay, @DayType = DayType,
@jobcode = case when BDay = 0
then 100
else 101
end
FROM dbo.VacSchCC
WHERE (VacID = @VacID)

select @DefJobcode = DefJCode,@DefaultRate = DefaultRate
From TimeclockPlus.dbo.employeelist
where @EmployeeId = EmployeeId

SELECT @lastdate = MAX(TimeOut)
FROM TimeClockPlus.dbo.EmployeeSchedules

Select @sentdate = Cast(Convert(VArchar(30),Max(End_Date),101) + ' 23:59:01' as datetime)
From Masterpcs.dbo.Payroll_Temp

-------------------------------------------------full day
If @VacDate Between @sentdate and @lastdate and(@DayType = 'Full Day' Or @Jobcode = 101)
Begin
Delete from dbo.VacSchCC
where @VacID = VacID
Delete from TimeClockPlus.dbo.Employeehours
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Update TimeClockPlus.dbo.Employeeschedules
Set jobcode = @DefJobcode,Rate = @DefaultRate
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
end

Anyone see anything?

Jim
Users <> Logic

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-13 : 12:43:47

Are you sure that's what you're running and haven't left in a debug select.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-13 : 12:47:12

What you see is what I have nigel.

I do not even use current date/time in it.

Thats whats driveing me nuts.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-13 : 12:53:48

Found It it was in a delete trigger for one of the tables.

Talk about a back track.

I did not know that an trigger could output to a sp that causes it to fire with no referance to it.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-13 : 13:04:41

It can't Jim...it's got to be something else...

Brett

8-)

EDIT: Post the Trigger and the whole sproc. When you run the sproc...do you get the result to the query analyzer output window?

Or is it in the ADP?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-13 : 13:47:15

It did brett, it put it in the ouput of QA or it would feed it back to my front end if I called the sp.

The line was in the middle of a trigger that copies the deleted record over to a backup table.

It was simply
select Getdate()
I must have left it in when i was messing about with it.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-13 : 14:00:14

Here was the trigger
ALTER TRIGGER VacSchCC_Delete
ON dbo.VacSchCC
FOR DELETE
AS

Declare @VacDate DAtetime,@VacID Int,@EmployeeId float,@Adddate Datetime,@Whoadd Varchar(30),@BDay bit,@DayType varchar(50)
Declare @Ddate DAtetime,@lastdate Datetime,@sentdate DAtetime,@remvac bit
Select @VacDate = VacDate ,@VacID = VacID,@EmployeeId = EmployeeId,@Adddate = Adddate,
@Whoadd = Whoadd, @BDay = BDay ,@DayType = DayType
From Deleted
Select Getdate()
Insert Into dbo.VacSchccDeleted
(VacID, EmployeeId, VacDate, Adddate, Whoadd, BDay, DayType)
SELECT @VacID, @EmployeeId, @VacDate, @Adddate, @Whoadd, @BDay, @DayType

And the whole SP.
ALTER PROCEDURE dbo.Delete_vac_day
(@VacID int)
AS
Set nocount on

Declare @EmployeeId float , @VacDate Datetime, @BDay bit , @DayType Varchar(50),@lastdate Datetime,@sentdate Datetime,@jobcode int
Declare @DefJobcode int,@DefaultRate smallmoney

SELECT @EmployeeId = EmployeeId, @VacDate = VacDate, @BDay = BDay, @DayType = DayType,
@jobcode = case when BDay = 0
then 100
else 101
end
FROM dbo.VacSchCC
WHERE (VacID = @VacID)

select @DefJobcode = DefJCode,@DefaultRate = DefaultRate
From TimeclockPlus.dbo.employeelist
where @EmployeeId = EmployeeId

SELECT @lastdate = MAX(TimeOut)
FROM TimeClockPlus.dbo.EmployeeSchedules

Select @sentdate = Cast(Convert(VArchar(30),Max(End_Date),101) + ' 23:59:01' as datetime)
From Masterpcs.dbo.Payroll_Temp

-------------------------------------------------full day
If @VacDate Between @sentdate and @lastdate and(@DayType = 'Full Day' Or @Jobcode = 101)
Begin
Delete from dbo.VacSchCC
where @VacID = VacID
Delete from TimeClockPlus.dbo.Employeehours
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Update TimeClockPlus.dbo.Employeeschedules
Set jobcode = @DefJobcode,Rate = @DefaultRate
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
end
-------------------------------------------------Half day Work First
If @VacDate Between @sentdate and @lastdate and @DayType = 'Half day Work First' and @Jobcode = 100
Begin
Delete from dbo.VacSchCC
where @VacID = VacID
Delete from TimeClockPlus.dbo.Employeehours
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Delete from TimeClockPlus.dbo.EmployeeSchedules
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Update TimeClockPlus.dbo.Employeeschedules
Set Timeout = Dateadd(hour,8,Timein)
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101)
end
-------------------------------------------------Half Day Work after
If @VacDate Between @sentdate and @lastdate and @DayType = 'Half Day Work after' and @Jobcode = 100
Begin
Delete from dbo.VacSchCC
where @VacID = VacID
Delete from TimeClockPlus.dbo.Employeehours
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Delete from TimeClockPlus.dbo.EmployeeSchedules
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101) and Jobcode = @jobcode
Update TimeClockPlus.dbo.Employeeschedules
Set Timein = Dateadd(hour,-8,Timeout)
where EmployeeId = @EmployeeId And Convert (varchar(30),Timeout,101) = Convert (varchar(30),@VacDate,101)
end
If @VacDate > @lastdate
Begin
Delete from dbo.VacSchCC
where @VacID = VacID
End

Select
VacMsg = case When @sentdate > @VacDate
Then 'You can not delete this vacation day it has already been sent'
Else ' Vacation Day Deleted'
end

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-13 : 14:12:56

The passing values from the trigger to the sproc threw me...

I just never thought to do this...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR DELETE
AS
SELECT GetDate()
GO

INSERT INTO myTable99(Col2) SELECT 'x'
GO

DELETE FROM myTable99 WHERE Col1 = 1
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources