Problem with Triggers - 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

Transact-SQL (2000)

Problem with Triggers

Author

Topic

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 04:28:29

Can I send sms using triggers.
Howwzat ?

If a message row is inserted into the table, the trigger is fired and the SMS is sent to the user, whose cell number is in the Cell number field and the message in the messages field.
Please help.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 04:33:56

Well.. i dont know about that whether you can send the sms via sql server, but what we are doing is..

we have created one service which runs on the client machine..which check for the data in the pariticular table for every 5 mins if the data found .. then it picks the mobile number and text.. and sends to the service provider and then service provider sends the sms..

how are u managaning sms???

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 04:45:42

See if this helps you
http://www.databasejournal.com/features/mssql/article.php/1467621

But Im not sure whether it is efficient doing this in SQL Server than other languages like ASP.NET

Madhivanan

Failing to plan is Planning to fail

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 05:33:43

sir, that's simple. I've given an interface from where the user can select or enter any mobile number, and then write a message, now this information comes to the Msglist table. Here......, a trigger can be fired and a procedure can be called which would do the job. I've some idea how to ADO's with T-SQL, but don't know which. Again when the SMS delivered confirmation comes to server, another procdure will run to transfer the delivered row to MsgDeli table.

Now Hozzat ?

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 05:44:53

Sir, thanks for that Url. what about the idea, "An IDEA Can change you life ".Please let me know how to create an object of a DLL and how to debug it ?.

Gulp it:
1.There is an interface which gives feature to type a number and a message.
2. There is a table say Msglist where this info gets stored.
3. At the instant of insert, a trigger fires which in turn calls a procedure giving details of the message.
4. The procedure sends SMS.
5. After Delivery notification, the same procedure inserts the message row to a table say Msgdeli.
6. There is also procedure which keeps check if any message is not deliverd.
7. Now we can call same procedure to send sms periodically.

That's the plan.

Please Help.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 05:51:09

sorry .. didnt get you..????

What front end you are using..??? i.e. vb, vb.net C#.. VC++ etc..

since in every frontend the objects are created differently..

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 05:56:34

VB.net sir.
it's simple logic sir.
1. The interface sends the data to a table
2. Trigger fires.
3. It calls a procedure with the data.
4. Procedure

executes the task.

the problem is what should be coded to get the SENDSMS procedure send message

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 06:04:07

aha oks..
so you want the code to execute the stored procedure via vb.net???

check out this site you will get the details over here..

http://samples.gotdotnet.com/quickstart/util/srcview.aspx?path=%2fquickstart%2fhowto%2fsamples%2fadoplus%2foutparamswithacommand%2foutparamswithacommand.src
or from the trigger you want to call that stored procedure..

then you can sp_executesql
or Exec function for the same..

is this what you want..???

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 06:22:01

sir,you've got it, but partially.

My problem is what beans should I stuff into the stored procedure to send an SMS.

Well I tried by hand on, and this is what i cooked:

CREATE PROCEDURE sendsms
@cell varchar(50),
@msg varchar(140),
@path char(2)
AS
BEGIN
SET NOCount ON
DECLARE @retval int
DECLARE @comhandle int
DECLARE @mailsvr varchar(15)
DECLARE @mport int
DECLARE @smsto varchar(20)
DECLARE @isdeli bit
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)

IF (@path='IS')
/// process for ISP to send SMS
BEGIN
EXEC @retval=master..sp_OAcreate 'smtp.clsMail',@comhandle
set @mailsvr='205.159.140.2'
set @mport=25
IF (@retval = 0)
BEGIN
EXEC @retval=master..sp_OASetProperty @comhandle,'Mailserver',@mailsvr
EXEC @retval=master..sp_OASetProperty @comhandle,'Port',@mport
EXEC @retval=master..sp_OASetProperty @comhandle,'Sendto',@cell
EXEC @retval=master..sp_OASetProperty @comhandle,'Message',@msg
EXEC @retval=master..sp_OAMethod @comhandle,'SendSMS',@isdeli
END
END

IF(@path='GS')
//Process for GSM modems

IF (@path='CD')
// process for CDMA modems

IF (@isdeli=0)
BEGIN
insert into Msgdeli(dNamegg,dNumbers,dMsg)
select Namegg,Numbers,Msg FROM MsgList
where Numbers=@cell

delete from Msglist where Numbers=@cell
END

SET NOCOUNT OFF
END
GO

so how does it tastes.

An Idea Can change your life

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 06:39:01

oks.. i got its little bit..
means from this procudure you want to send the email to the specific mail server .. and they will send the sms to the specified mobile number ..
correct??

if that is wat.. then you find the stored procedure.. which send the email.. over here..

http://www.sqlteam.com/item.asp?ItemID=5003

Hope this works for u .. ???

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 06:58:17

Sir, That's cool.
But. THIS A BIG BUT.
If a natural calamity strucks and my connection with ISP fails, now I should have an alternate path to route my SMS.
The probable alternates:
1. Gsm mobile phone
2. CDMA mobile phone.

This code is from previous message posted:
IF(@path='GS')
//Process for GSM modems

IF (@path='CD')
// process for CDMA modems

Now what should be there in between lines.

An Idea Can change ur life.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:08:54

well.. then in this case you need to specify the different ip address....for the same..

so the code as to be some where above and here..

CREATE PROCEDURE sendsms
@cell varchar(50),
@msg varchar(140),
@path char(2)
AS
BEGIN
SET NOCount ON
DECLARE @retval int
DECLARE @comhandle int
DECLARE @mailsvr varchar(15)
DECLARE @mport int
DECLARE @smsto varchar(20)
DECLARE @isdeli bit
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)

IF(@path='GS')
Set @Mailsvr = // Some other route..

IF (@path='CD')
Set @mailsvr = /// some other route..

IF (@path='IS')
set @mailsvr='205.159.140.2'

EXEC @retval=master..sp_OAcreate 'smtp.clsMail',@comhandle
set @mport=25
IF (@retval = 0)
BEGIN
EXEC @retval=master..sp_OASetProperty @comhandle,'Mailserver',@mailsvr
EXEC @retval=master..sp_OASetProperty @comhandle,'Port',@mport
EXEC @retval=master..sp_OASetProperty @comhandle,'Sendto',@cell
EXEC @retval=master..sp_OASetProperty @comhandle,'Message',@msg
EXEC @retval=master..sp_OAMethod @comhandle,'SendSMS',@isdeli
END

IF (@isdeli=0)
BEGIN
insert into Msgdeli(dNamegg,dNumbers,dMsg)
select Namegg,Numbers,Msg FROM MsgList
where Numbers=@cell

delete from Msglist where Numbers=@cell
END

SET NOCOUNT OFF
END
GO

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 07:16:18

but how would i configure my modem, GSM/CDMA, coz, now the routing is not thru net, its through a physical device. What kind of stored procedure or whatever in SQL server will come to my aid, while writing between the lines.

An Idea Can Change Your Life

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:24:44

well.. i dont know its the wise idea to access the physical devices using the SQL Server.. for this case you need to have to do in the front end.

there are lots of COM Components which are available for doing so.. which comunicates with the external devices..

the device which u are using would also came with some interface or .. some kind of workspace project where the code would be there to communicate with the device????

And how the physical device will directly send sms??? and what is that physical device i guess this is really new to me...???

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 07:33:18

sir, sir....
The physical device is nothing but a Modem, GSM / CDMA /Landline.
Okay agreed. I would code from a front end for the purpose, and would also insert repective codes, for each.

But initially, if my first preference is to e-mail ISP the number, now if that fails, then what process would invoke my frontend which supports the alternate way to send SMS.

An Idea Can Change Your Life

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:47:09

then in that case..

well for that you have to do frontend and not in backend..

in this proc you can 1t some out parameter which will state you whether the procedure has sent the sms or not ..

somthing like this ...

CREATE PROCEDURE sendsms
@cell varchar(50),
@msg varchar(140),
@path char(2),
@err int out
AS
BEGIN
SET NOCount ON
DECLARE @retval int
DECLARE @comhandle int
DECLARE @mailsvr varchar(15)
DECLARE @mport int
DECLARE @smsto varchar(20)
DECLARE @isdeli bit
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)

IF (@path='IS')
/// process for ISP to send SMS
BEGIN
EXEC @retval=master..sp_OAcreate 'smtp.clsMail',@comhandle
set @mailsvr='205.159.140.2'
set @mport=25
IF (@retval = 0)
BEGIN
EXEC @retval=master..sp_OASetProperty @comhandle,'Mailserver',@mailsvr
EXEC @retval=master..sp_OASetProperty @comhandle,'Port',@mport
EXEC @retval=master..sp_OASetProperty @comhandle,'Sendto',@cell
EXEC @retval=master..sp_OASetProperty @comhandle,'Message',@msg
EXEC @retval=master..sp_OAMethod @comhandle,'SendSMS',@isdeli
END
END

if @@Error <> 0 -- then there is some error while sending the sms.
Begin
Set @err =1
return -- Return from here..
End

IF (@isdeli=0)
BEGIN
insert into Msgdeli(dNamegg,dNumbers,dMsg)
select Namegg,Numbers,Msg FROM MsgList
where Numbers=@cell
delete from Msglist where Numbers=@cell
END

Set @err = 0 -- there is not error while sending an sms.. :-)
SET NOCOUNT OFF
END
GO

now in the front end you have to check for the @err if its 1 then its failed so now communicate with the hardware device..
eg go for GSM ..or CDMa.. etc..

hope this helps..

Sucess Comes to those who Believe in Beauty of their Dream..

ksandeep
Starting Member

10 Posts

Posted - 2005-11-23 : 08:02:11

Sir, ultimately this means that, the frontend would control the SMS routing and not the Server procedure. Coz I've to check @err from front end. correct ?

I wanted the process to be automated. In the present scenario, the sender have to keep check if his message was delivered or not. if undelivered then he must choose the other alternative.

Thank you for giving me the replies. I would work on your suggestions and would reply u soon.
Again thank you

An Idea Can Change Your Life

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 08:16:24

quote:
Originally posted by ksandeep

Sir, ultimately this means that, the frontend would control the SMS routing and not the Server procedure. Coz I've to check @err from front end. correct ?

I wanted the process to be automated. In the present scenario, the sender have to keep check if his message was delivered or not. if undelivered then he must choose the other alternative.

Thank you for giving me the replies. I would work on your suggestions and would reply u soon.
Again thank you

An Idea Can Change Your Life

Well its always good to handle all this kind of routines from the front end then backend ... since you get the more functionality and also you can trap and play with the error more effectively..

and ya cum on i m not any Sir oks?? I am just an normal developer in some company like you so its fine you can call me by name..

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources