Strange problem with DTS package.

[nick122211]

Ok maybe I am doing this completely wrong but I am getting weird results. Basically I have a package that reads 4 fields from one DB (server names) and inserts the name into an exec statement for each row found. The problem is it appears it isn't returning all the rows. It is dropping one or two rows inconsistantly. So basically I am missing one server every time this process runs when the package is scheduled to run. To make matters worse when I manually execute the package I have no problems and all the rows are returned? Anyone seen anything like this before? Below is the code. I also have another process that if I schedule doesn't execute properly however if I execute it manually it runs fine.

Thanks
Nick

Declare @Temp Table(RowId Integer Identity(1,1), ServerName VarChar(100))

Insert Into @Temp(ServerName)
Select Server
From SMTPAlert.dbo.servers

Declare @Max Int
Declare @i Int
Declare @ServerName VarChar(100)


Select @Max = Max(RowId),
@i = 1
From @Temp

While @i <= @Max
Begin
Select @ServerName = ServerName
From @Temp
Where RowId = @i

exec SMTPAlert..sp_send_cdosysmail @ServerName,'MailAlert@XXXXX.com','mgmt-svc@XXXXXX.com',@ServerName, @servername

INSERT INTO SMTPAlert.DBO.sendrecieve
(Subject,sent)
VALUES (@servername, current_timeStamp)

Set @i = @i + 1
End

 

[Catadmin]

I don't have alerts set up, but when I did this code against sys.objects looking for user tables, I got a Select statement to pull up everything fine.

Are you sure you don't have a servername duplication issue?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[nick122211]

When I run the code in Query analyser it runs fine. It appears to be an issue only when it is scheduled to run. Manually it works perfectly. Thinking there has to be something wrong with the way DTS is running or something like that?

 

[SQLDenis]

Obvious question: are you connected to the correct DB or Server when in the DTS package? Make sure you don't have local selected

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[nick122211]

I believe so. I get 3 of the 4 records in the DB. It just is dropping one of them 3 out of 4 times the DTS Package is run. Very wierd.

 

[nick122211]

Maybe a better way of asking is am I doing this process right. I created a DTS package (Local) and added a SQL connection and then added a Execute Sql Task that I put this code in. I then scheduled the Package to run every 5 minutes. Maybe there is a better way to do this?

 

[SQLDenis]

Not that it should matter but maybe you have a memory issue
what happens when you change your code to use #temp tables?

create Table #temp(RowId Integer Identity(1,1), ServerName VarChar(100))

Insert Into #Temp(ServerName) 
Select Server
From   SMTPAlert.dbo.servers

Declare @Max Int
Declare @i Int
Declare @ServerName VarChar(100)


Select  @Max = Max(RowId),
        @i = 1
From    #Temp

While @i <= @Max
  Begin
    Select @ServerName = ServerName
    From   #Temp
    Where  RowId = @i

exec SMTPAlert..sp_send_cdosysmail @ServerName,'MailAlert@XXXXX.com','mgmt-svc@XXXXXX.com',@ServerName, @servername

INSERT INTO SMTPAlert.DBO.sendrecieve
                      (Subject,sent)
VALUES  (@servername, current_timeStamp)
 
    Set @i = @i + 1
  End

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

>>Maybe there is a better way to do this?

create a job copy and paste the code in a step and schedule it. you don't need a DTS packages for this stuff only

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/