How to simplify this slow Stored Procedure

Dear Reader(s),

Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:

-----------------------
use MJ_ReportBase
go
if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
begin
drop procedure sp_Periode
end
go
create procedure sp_Periode
@.start int
, @.stop int
as
declare @.x int

set @.x = 0
set @.x=@.start

delete from tbl_periode

while (@.x>=@.stop)
begin

-- --
-- --
-- Create table tbl_inout
if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
begin
drop table tbl_inout
end

select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
(select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105) group by badgeid)
and badgeid>0
order by personname

-- --
-- --
-- Create table tbl_result

if exists(select 1 from sysobjects where type='U' and name='tbl_result')
begin
drop table tbl_result
end

-- --
-- --

select
convert(varchar,datetimestamp,105) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
into
tbl_result
from
WinXS..x18
where
convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105)
and
accname in ('Kelder -1','Tnk Entree')
and badgeid>0
group by
convert(varchar,WinXS..x18.datetimestamp,105)
, badgeid
, initials
, personname
order by
initials
, personname asc
, convert(varchar,datetimestamp,105) asc

-- --
-- --
-- Rapportage tabel

insert into
tbl_periode
select
tbl_result.datum as DATUM
, ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
, tbl_result.min as MIN
, tbl_result.max as MAX
, case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
, substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
from
tbl_inout,tbl_result
where
tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
= convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
order by
tbl_result.naam asc

-- --
-- --
--

set @.x=@.x-1
end
go

print 'Klaar!'
-----------------------

What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.

Many thanks in advance.
:)First...collapse the queries in to 1.

Second, what's the first select for?

Third

Loose the loop and do

WHERE datetimestamp > GetDate()-@.Start
AND datetimestamp < = GetDate()-@.Stop|||I don't even want to try to figure out your code. Don't use permanent tables for temporary storage (your tbl_inout table). That will get you into trouble in a multi-user environment.

Post the relevent DDL for your tables any somebody here can probably show you a more efficient set-based algorithm.|||Hi to all,

I have found the solution after a long time puzzeling. :)

Please check the sql below:
-----------------------
select
tbl_INOUT.*
, CASE B.inoutreg WHEN 1 THEN 'IN' WHEN 2 THEN 'UIT' ELSE 'Fout' END 'STATUS'
from
(
select
convert(char(10),datetimestamp,120) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
from
WinXS..x18
where
(convert(varchar(10),datetimestamp,120)>=convert(varchar(10),'2005-01-01',120)
and
convert(varchar(10),datetimestamp,120)<=convert(varchar(10),'2005-02-28',120))
and
accname in ('Kelder -1','Tnk Entree')
and
badgeid>0
group by
convert(char(10),datetimestamp,120)
, badgeid
, initials
, personname
) tbl_INOUT, WinXS..x18 B
where
tbl_INOUT.pas=B.badgeid
and
convert(datetime,tbl_INOUT.datum+' '+tbl_INOUT.max,120)=B.datetimestamp
and
badgeid=81
order by
tbl_INOUT.naam asc
, tbl_INOUT.datum DESC

Greetz,
DePrins
:D

How to set the following report Parameters?

Hi,

I have a report and the parameters Passed to the report are Cusip and PeriodId through a stored Procedure.

i want to populate the cusip Parameters using a dataset thats got by a sproc called usp_getCusips.

and the PerioId using a dataset thats been got by another sproc called usp_Getperiods...

So in my report i have added 3 datasets one for the main report, one to get the values of the PeriodId and other to get the value of the cusip.

So in my Report parameters for the Parameter Cusip and i have checked the Available value option and selected its corresponding Dataset and value .

and i have also done the same thing for the PeriodId.. But when i run my sproc i am getting this Error.

[rsInvalidReportParameterDependency] The report parameter ‘Cusip’ has a DefaultValue or a ValidValue that depends on the report parameter “Cusip”. Forward dependencies are not valid.

[rsInvalidReportParameterDependency] The report parameter ‘PeriodId’ has a DefaultValue or a ValidValue that depends on the report parameter “PeriodId”. Forward dependencies are not valid.

Any Help will be appreciated.

Regards,

Karen

I'm not sure how to directly answer your question, but it sounds like you have created a circular reference.

See the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=184243&SiteID=1

|||

Greg,

Thanks for the help, i got it to work.

Regards

Karen

|||Glad I could help.

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (3
2
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is ther
e
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this
a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> >I have a calculation in a stored procedure that returns a percentage like
> >(32
> > * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> > float.
> > I need to set or convert the result to have 2 decimal places like 1.86,
> > rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> > variations (examples from the internet), nothing is working for me. Is
> > there
> > a way to achieve this in SQL? I would really appreciate the help.
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to SET multiple variables from one table record?

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.

Currently I'll do something like this:

DECLARE @.var1 int
SET @.var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @.somekey)
DECLARE @.var2 nvarchar(20)
SET @.var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @.somekey)

Of course, I'd rather just have to query "table1" just once to assign my variables.

What obvious bit of T-SQL am I missing?

Thank you in advance.

Select @.var1 = field1, @.var2 = field2, @.var3 = field3 from table1 where recordid = @.recid

|||

Thank you PDraigh!

I knew it was something obvious - I think I need a holiday

How to set is_receive_enabled and is_enqueue_enabled from 1 to 0.

Hi All,

I used service broker activation in my receive queue (SQL 2005 SP1). However, it seems the stored procedure does not work after the receive queue get message. I saw the following error in SQL Error log.

"The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'"

Then I checked the queue in sys.service_queues and noticed is_receive_enabled and is_enqueue_enabled is 0 but not 1 (is_activation_enabled is 1, which is normal). I believe this is the cause of my service broker activation issue.

According to this link:

http://www.eggheadcafe.com/aspnet_answers/SQLServerservicebroker/May2006/post26788966.asp

I tried to use ALTER QUEUE ... WITH STATUS = ON command to set the above value back to 1, it does not work the value persists 0. Any idea?

Thanks in advance.

Michael

ALTER QUEUE [YourQueue] WITH STATUS = ON will enable the queue and both is_receive_enabled and is_enqueue_enabled will be set to 1. However, if your stored proc rolls back a RECEIVE 5 times consecutively, the poisoned message detector will automatically disable the queue. You should see error messages in the ERRORLOG if your stored proc is throwing an exception or not committing the RECEIVE correctly.

Rushi

|||

Thanks, Rushi. You are correct the receive queue is disabled and I also found 5 errors before the queue is disabled in the SQL ERRORLOG as below:

--

2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.93 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.95 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.96 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:47.98 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'Conversion failed when converting datetime from character string.'
2006-12-28 20:39:48.01 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:50.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:39:55.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:00.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:05.85 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'
2006-12-28 20:40:08.09 spid54s The activated proc [dbo].[Mystoredpro] running on queue test2.dbo.ReceiveQ output the following: 'The service queue "ReceiveQ" is currently disabled.'

Can you let me know:

a. Is the 5 times a built-in value or the value I can set it? Is it the option MAX_QUEUE_READERS = 5 while I set the activation?

b. Since the ReceiveQ is disabled, how can I enable it back? I tried "ALTER QUEUE [YourQueue] WITH STATUS = ON" before, but the ReceiveQ still is disabled. Is it normal? Can I set it back?

Michael

|||

a. The maximum number of times RECEIVE can be consecutively rolled-back without setting of the poisoned message detection cannot be configured. It is hard-coded to '5'. It is also not related to the MAX_QUEUE_READERS which controls max number of concurrent activated tasks.

b. The ATLER QUEUE should re-enable your queue. Of course, if there are pending messages it will also start activation and since you have a bug in your stored proc, the stored proc will rollback 5 times and disable it back.

Rushi

|||

yeah.. that's should my probme. Thanks a lot.

how to send query from SQL Server using post method

Hello Everyone
i am new to SQL Server. i have a problem. i have to send a query string
to a server using post method from a stored procedure.
i got a stored procedure in some group for sending Http Post Request
from Stored Procedure. but i dont know how to pass parameters and Http
Header Information.
i have to send 4 parameters (Control name are)
1. Name (character string)
2. msisdn (Numeric string like Mobile No.)
3. toAddress (Numaric String like Mobile No.)
4. message (character string)
Plz can anyone help me.
the HTTP_post stored procedure which i found at some group is as
follows:
****************************************
**************************
CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
out)
As
Declare
@.obj int
,@.hr int
,@.status int
,@.msg varchar(255)
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
eh
end
if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
str(@.status) goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
eh end
exec @.hr = sp_OADestroy @.obj
return
eh:
exec @.hr = sp_OADestroy @.obj
Raiserror(@.msg, 16, 1)
return
GO
****************************************
**************************Hi,
I think you need to modify this bit to add the parameters in...
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
change to...
declare @.parms varchar(500)
set @.parms = 'Var1=' + @.yourparm + '&Var2=' + @.yourparm2
exec @.hr = sp_OAMethod @.obj, 'send', NULL, @.parms
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<vineet.jsl@.gmail.com> wrote in message
news:1130749924.513462.309240@.f14g2000cwb.googlegroups.com...
> Hello Everyone
> i am new to SQL Server. i have a problem. i have to send a query string
> to a server using post method from a stored procedure.
> i got a stored procedure in some group for sending Http Post Request
> from Stored Procedure. but i dont know how to pass parameters and Http
> Header Information.
> i have to send 4 parameters (Control name are)
> 1. Name (character string)
> 2. msisdn (Numeric string like Mobile No.)
> 3. toAddress (Numaric String like Mobile No.)
> 4. message (character string)
> Plz can anyone help me.
> the HTTP_post stored procedure which i found at some group is as
> follows:
> ****************************************
**************************
> CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
> out)
> As
>
> Declare
> @.obj int
> ,@.hr int
> ,@.status int
> ,@.msg varchar(255)
>
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
> if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
> failed', 16,1) return end
>
> exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
> 'Content-Type', 'application/x-www-form-urlencoded'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
> goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
> eh
> end
>
> if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
> str(@.status) goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
> eh end
>
> exec @.hr = sp_OADestroy @.obj
> return
>
> eh:
> exec @.hr = sp_OADestroy @.obj
> Raiserror(@.msg, 16, 1)
> return
> GO
> ****************************************
**************************
>|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL but this time i have to
send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL from stored procedure
but this time i have to send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?XP_sendmail lives in the master database, so you should use the command
master..XP_sendmail
HTH, Jens Suessmeyer.

How to send mail programmatically from SQL2000

I tried to use the stored procedure xp_sendmail to send mail
programmatically from MSSQL2000.
But it seems to be inexistent.
Is there any other means to do that?Did you qualify with the master database?
EXEC master..xp_sendmail ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23apBGJDQGHA.516@.TK2MSFTNGP15.ph
x.gbl...
>I tried to use the stored procedure xp_sendmail to send mail
> programmatically from MSSQL2000.
> But it seems to be inexistent.
> Is there any other means to do that?
>|||I wrote now the following code:
exec [master].[dbo].[xp_sendmail]
@.recipients = 'bishoy@.bishoy.com',
@.message = 'Test',
@.subject = 'Test SQL Mail'
and received the following error:
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
Although when I added @.user parameter, I received the following error:
Server: Msg 17981, Level 16, State 1, Line 0
xp_sendmail: Invalid parameter '@.user'|||There are plenty of issues with xp_sendmail. I have seen this in newsgroups
reported before. You'll
probably find a resolution by Google and searching the newsgroup archives.Al
so consider using
xp_smtp_sendmail instead (www.sqldev.net).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoy@.bishoy.com> wrote in message news:%23ErGM4FQGHA.3896@.TK2MSFTNGP15.p
hx.gbl...
>I wrote now the following code:
> exec [master].[dbo].[xp_sendmail]
> @.recipients = 'bishoy@.bishoy.com',
> @.message = 'Test',
> @.subject = 'Test SQL Mail'
>
> and received the following error:
> Server: Msg 17985, Level 16, State 1, Line 0
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> Although when I added @.user parameter, I received the following error:
> Server: Msg 17981, Level 16, State 1, Line 0
> xp_sendmail: Invalid parameter '@.user'
>