July 14, 2012 at 3:11 pm
I have a databse that has phone number in it so I can send text/email messages to it but I have to put like @txt.att or @messaging.sprintpcs.com at the end of the number. Is there a way in my code to add it so it will send with out having to have the number like 8888888888@txt.att.net in the database.
USE [SMS]
GO
/****** Object: StoredProcedure [dbo].[usp_Birthday] Script Date: 07/14/2012 16:00:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_Birthday]
as
DECLARE @TodaysDate date = getdate(),
@PhoneNumber nvarchar(max),
@FirstName varchar(25) ,
@body nvarchar(MAX)-- = ' Happy Birthday '
DECLARE @CurrentMonth int = MONTH(GETDATE()), @CurrentDay int = DAY(GETDATE())
DECLARE @Birthdate TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(MAX), FirstName varchar(25))
--set Identity_insert [@Birthday] on
INSERT @Birthdate
SELECT PhoneNumber, FirstName from dbo.Name where MONTH(BirthDate) = @CurrentMonth and DAY(BirthDate) = @CurrentDay
DECLARE @NumberOfBirthdays smallint = (SELECT COUNT(*) from @Birthdate)
DECLARE @MinID int
WHILE @NumberOfBirthdays > 0
BEGIN
SET @MinID = (SELECT MIN(ID) From @Birthdate)
SET @PhoneNumber = (SELECT PhoneNumber from @Birthdate where ID = @MinID)
set @FirstName = (Select FirstName from @Birthdate where ID = @MinID)
SET @body = 'Happy BirthDay ' + @FirstName
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Happy Birthday',
@recipients = @PhoneNumber,
--@blind_copy_recipients = ***@*****.com; ***@*****.com',
@body = @body,
@profile_name ='gmail';
DELETE FROM @Birthdate where ID = @MINID
SET @NumberOfBirthdays = @NumberofBirthdays -1
END
--END
RETURN;
July 14, 2012 at 3:17 pm
How do you know if you should use @txt.att or @messaging.sprintpcs.com or something else?
July 14, 2012 at 3:23 pm
Right now I have been asking people who the phone carrier is and can just add it to the end of there number. But I was trying to find a way to add it in the coder and it would send out.
July 14, 2012 at 5:40 pm
edward_hall76 (7/14/2012)
Right now I have been asking people who the phone carrier is and can just add it to the end of there number. But I was trying to find a way to add it in the coder and it would send out.
Edward - You should record such valuable piece of information - the carrier- in your database then depending on the carrier your code can just concatenate the proper sufix.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 14, 2012 at 6:16 pm
Can you give me an example and where to Add it in the database at
July 16, 2012 at 7:00 am
IMHO the best way is to create a carriers table with the information needed and add a field column (Insert Celko's quote saying "columns are not fields":-D) for a Foreign Key to relate your phones' table to your carriers table.
Another way to do it is just adding the carrier field to your phones' table as a varchar(n).
July 16, 2012 at 7:08 am
but wouldnt you still have to ask everyone who there carrier is so you could reference it to that number. or is there a way just to run it the check against the number and it the carrier match up it send the text/email and if not it wouldnt send.
July 16, 2012 at 7:18 am
Yes you would have to ask who the carrier is still but you save a hell of a lot of storage space in the DB.
Going off only ever using carrier @messaging.sprintpcs.com
If your just appending the carrier to the end of the number on each record, your adding an extra 24 bytes varchar/ 48 bytes nvarchar to the string, now depending how full your pages are or how many rows you have will result in page splits and increased disk space usage.
Now if you had a seperate table, you only store the carrier once, and then each number gets a flag, maybe a tiny int column which is 1 byte worth of storage, saving you 23/47 extra bytes which dont need to be stored.
Then all you need to do is link the two tables when you query the phone number to just append the carrier and send the email.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply