[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL server programming

Posted on 2012-09-19
11
Medium Priority
?
625 Views
Last Modified: 2012-09-20
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:)
sms-in-data-table.png
sms-in-table.png
0
Comment
Question by:afifosh
  • 5
  • 3
  • 3
11 Comments
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38416777
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.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38416790
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
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38416855
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?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Expert Comment

by:mr_nadger
ID: 38416863
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.
e.g.
//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)
0
 
LVL 1

Author Comment

by:afifosh
ID: 38416869
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
0
 
LVL 1

Author Comment

by:afifosh
ID: 38416892
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 ?
0
 
LVL 7

Accepted Solution

by:
mr_nadger earned 1500 total points
ID: 38416935
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>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

 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.
0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 38416940
just a thought on using a trigger instead of a scheduled bulk task - how many messages are you expecting and how frequently?
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 38416948
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?
0
 
LVL 1

Author Comment

by:afifosh
ID: 38416970
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 ?
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question