MS SQL server programming

hello i want ours help please i have upload my table schemiea of sms_in where this table i receive from my mobile  operator all sms msg sent from mobile user to a specific short code of sms services and i have uploded how data is present
the field status mean processing / null / done
done when i fnish it / null will be first default value / processing when i do something in this row..

readflag 0 is default and we put 1 when i read it

as u know we receive daily every couple of second or minute sms to my shortcode and it will be stored in this table and what i need to process this message ?

so the solution be by running a script ASP or PHP to fetch row. each minute ..using windows task scheduler ??

and which query should be applied ??
i read some topics in internet to use SQL transc  lock table ?? serizable but i don't know this stuff :D bcz i don\t know the difference and what do the locking ?

and the lock will be for table or for row ..

so my task is fetch rows in this table every min and run the script again to proccess the new in coming msg so please

can anyone helpppp me !!!!!!!!!!!!!!
kindly note find the two attachment document:)
Who is Participating?
mr_nadgerConnect With a Mentor Commented:
look in SQL Management Studio, under "SQL Server Agent" there is a Jobs section, here you can create a job and schedule how often you want it to run.

To create a trigger on a table, expand the table name in the SQM Management Studio and you should see a triggers option. Right click on this and select New Trigger.
You'll get something like this and will want to user the INSERT option.

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

    -- Insert statements for trigger here


 Put the code in for what you need to do between Begin and End, as you should be sorted.
Connecting to a seperate database on the same server shouldn't be a problem, you just need to fully qualify the table name (i.e. myotherdatabase.dbo.myothertable).
A select query would let you find out if a user is subscribed, so you could use that in an If clause to prevent people being resubscribed.

It sounds like you've set up the database and table structures before you've worked out how you're going to have them interact - you might want to have another look at where all the data's held and see if you can simplify it.
Julian HansenCommented:
what do you want to do with the data you read from the database?

Reading it is easy - but you obviously want to do something with it.
AFIF JABADOAuthor Commented:
i want to select the in coming msg from mobiles users when he send msg to my shortcode
than read each row. after read the fetched row i want to proccess it if lie the message is  1
subscribe the msisdn mobile number to service Cooking...

so the script should be always execute to check new sms and proccess it ..
and if i run the same script always i am afraid to read the sms 2 times so i subscribe the user twice ...

so if i process the sms sent from mobile user i change status to done and readflag to 1
to not read it again ...

sms-process.php for example fetch rows always select .. insert in another table after checking .. than update the selected row ..

it\s will be like queuing ! we have always a msg in queue and u want to process it for delivering
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Julian HansenCommented:
Is the subscription part of the same database?

If so why don't you just create a trigger - as the new row comes in from the SMS the trigger fires and subscribes the user?
why are you using windows scheduler to process SQL tasks, why not set up a job in SQL to run every few minutes and call a stored proc to handle all this?

Your stored proc could select all rows where readflag=0, perform the necessary tasks on this subset, then set the readflag=1.
//update currently unread items to a tempoary "being processed" status
update cgi_sms
set readflag=2 where readflag=0

//set these "being processed" items to "read"
insert into myother table
select * from cgi_sms
where readflag=2
update cgi_sms
set readflag=1 where readflag=2

(using this "being processed" state will prevent you accidentally updating messages which came in mid-processing being set to read accidentally)
AFIF JABADOAuthor Commented:
yes we have a subscription part ... mmm i don\t know about trigger function :D
and i have see something about isolation level.. and level 0 1 ....
and i should care someone mobile users send sms message with empty field.. so i should send to him automatic sms  wrong. or something like this

and i have another database withe all service name who i should check it using trigger
because the table of sms in in another table of services !!

and when i get the sms from mobile user i want to check  if he is already subscribed to this service before
. than if he is not subcibed he will be subcribed !! and charge money
AFIF JABADOAuthor Commented:
first i don\t know  how SQL to run every few minutes ....
and than i have 2 different tables one for sms_in and the second a isolated database where i put all info of subscribed mobile users so who i can access to  two different  database ? using procedure.. and please i f we can read my last comment !!

and i don\t understand i have only two flag 0 unread and 1 is read what is flagread 2 ?
just a thought on using a trigger instead of a scheduled bulk task - how many messages are you expecting and how frequently?
Julian HansenCommented:
You don't need an SQL task - If I understand this correctly, this is a very simple process of setting up a trigger to populate the subscribtion table whenever a new record comes into the SMS table.

Not sure why locking is required - unless more than one process is accessing either of the tables to write?
AFIF JABADOAuthor Commented:
i am work in new company and the company have an old systm.. and i work to modify it ..
finaly? i need to use ?? locking??

on the trigger ? i should select some msg? or all .. and set status begin processing run it each minute using sql agent job scheduler. .???

what is the best solution ?? the trigger select all msg or a part ! than this trigger will run every  X second or minute??

and what about locking ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.