Error initializing COM - 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 2005 Forums

Transact-SQL (2005)

Error initializing COM

Author

Topic

marat
Yak Posting Veteran

85 Posts

Posted - 2008-01-24 : 18:36:21

Hi,
Disk Space Left SP I am using is working fine in SQL2K, but when I am running the same SP in SQL2K5(I replaced xp_sendmail with msdb.dbo.sp_send_dbmail) I get an error initializing COM.
If I am running sp_send_dbmail separately it works ok, but if it is running in a batch it fails.
In my SP I am also using
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
It looks like FileSystemObject and sp_send_dbmail are conflicting.
If I comment out any of them ERROR disappears.
Any ideas what could cause an error.
Thanks
marat

marat
Yak Posting Veteran

85 Posts

Posted - 2008-01-29 : 06:35:37

Still can't get answer.
Attaching not working script(In SQL2K5).
SET NOCOUNT ON
DECLARE
@Percentagefree int,
@error2 varchar(8000)
set @Percentagefree = 50
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'

CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 begin EXEC sp_OAGetErrorInfo @fso; print 'error'; end
print 'no error'

set @maxcount =(select max(id) from #drives)
set @count=1

while @count <=@maxcount
begin

select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count

if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
set @error2=@error2+@error+char(13)

end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
end
set @count=@count+1
end
--select * from #drives

DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
--
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBMailProfile',
@recipients = 'marat@primus.com',
@body = 'The stored procedure finished successfully.',
@query = 'SELECT 5',
-- @execute_query_database = 'msdb',
-- @attach_query_result_as_file = 1,
@append_query_error = 1,
@subject = 'Automated Success Message'
-- @body_format = 'HTML'
Any clues?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 06:48:36

See this blog entry
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

E 12°55'05.25"
N 56°04'39.16"

marat
Yak Posting Veteran

85 Posts

Posted - 2008-01-29 : 07:05:59

Thanks Peso. Tara's method is nice.
But I really want to know what is wrong with the script I am using?
Why sp_send_dbmail fails when using @query parameter?
marat

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 07:07:50

Well, at least now we know that it is the mailing part that produces the error...

E 12°55'05.25"
N 56°04'39.16"

marat
Yak Posting Veteran

85 Posts

Posted - 2008-01-29 : 21:02:05

Hi Peso,
I used Tara's DLL; it works fine with sp_send_dbmail.
Special credits to Tara.
Thank you
marat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources