Practical ProgrammingExperience is knowledge.
|
Forwarding your emails to your cellular phone is incredibly easy as all major carriers associate an email address directly with your telephone number:
Here are the major ones:
AT&T: phonenumber@txt.att.net
T-Mobile: phonenumber@tmomail.net
Virgin Mobile: phonenumber@vmobl.com
Sprint: phonenumber@messaging.sprintpcs.com
Verizon: phonenumber@vtext.com
Nextel: phonenumber@messaging.nextel.com
If you have done any signficant amount of programming in SQL you will have learned, with time, that nearly everything you can do in SQL Manager/Studio is 100% scriptable. Taking advantage of this can be a huge help with automating tasks… but you don’t need to bury your head in SQL Syntax to do so. Take the time to check out the wizards in studio, the context menus, etc… and you will almost always see a button or option to “generate script”. Get used to taking advantage of this because it will save you a multitude of headaches!
Here is a quick tip for restoring a table from a backup in script:
You cannot restore a database while it has active connections… to get around this you will need to set the database up in single_user mode (which deactivates all other connections except for your own). You will also need appropriate privelages to execute the commands like drop, restore, sp_adduser, and so on… db_creator roles are a simple solution for setting access priveleges but make sure you adequately protect the account as it opens up your database to lots of malicious activity if it were to fall in the wrong hands.
Here is a example to get you started down the right track:
--disconnect any one else so you can do the restore
ALTER DATABASE {your database}
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
USE MASTER
--delete the old database (you could overwrite it instead
---if you wanted, merge, etc.)
DROP DATABASE {your database}
--restore the database from the backup
RESTORE DATABASE [{your database}]
FROM DISK = N'{insert full path to ur backup file here}' WITH FILE = 1,
MOVE N'{your backupset}' TO N'{insert your path and db name here}.mdf',
MOVE N'{your backupset}_log' TO N'{insert your path and db name here}.ldf',
NOUNLOAD,
RECOVERY,
REPLACE,
STATS = 10
--grant privileges as necessary
use {your database}
exec sp_adduser '{user account}', '{user name}', 'db_datareader'
--reset the connection mode back to multi_user (this is important)
ALTER DATABASE {your database}
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
MSDN Ref:
1. Restore (Transact-SQL)
2. sp_addUser (Transact-SQL)
3. Drop Table (Transact-SQL)
4. Alter Database (Transact-SQL)
Users of SQL Server 2005 already know DTS Packages have been set aside for the newer, more functional, SSIS (SQL Server Integration Services). But if you are anything like myself, you have dozens of old DTS Packages and no desire to upgrade them. Send to the rescue The Microsoft SQL Server 2005 Feature Pack; you will want to Download the Microsoft SQL Server 2000 DTS Designer Components add-on.
Note:
The complete package is available here.
The title of this post may suggest to the reader that I am writing about something everyone at least moderately familiar with Cold Fusion should know. But, for the unaware, Cold Fusion compiles cfscript first, then it compiles the rest of the cfml. With this in mind, know that if you have a custom tag which has a closing tag, you are in fact calling your tag twice. This is where an ordinarily functional tag will have problems with udf’s declared in cfscript. Cold Fusion is not evaluating whether the tag is running in thistag.executionmode start or end at the time the cfscript is compiled. When this happens, the compiler will gripe about not being able to declare udf’s more than once.
I usually address this issue one of two ways:
Final Note
If the custom tag does not have a close tag, you can still declare the function in a cfscript block within the tag. I, however, still prefer to use a separate file. I usually place my custom tags in their own folders to keep everything neet, but I’ll have more on how I organize my tags and functions in a later post. Until then, dream in digital.
Why?
cftimer provides a simple and easy way to determine how long your code is taking to execute. Just assign it a label and turn on debugging output. The results will be displayed under the category CFTimer Times in the debug output measured in milliseconds. Ex. “[62ms] MyCodeBlock”
How?
<cftimer label="MyCodeBlock"> ... {some code} ... </cftimer>
Note:
Make sure you have Timer Information turned on in the Debug Output Settings from within the Coldfusion Administrator. If you still aren’t seeing any debug output make sure that debugging is enabled and that your ip has been added to the Debugging IP Addresses list.
Why?
When a query is passed to Microsoft SQL Server, a number of things have to happen in order for you to get a result set back. First, before anything can be done your SQL Statement is compiled by the Query Optimizer which creates an Execution Plan based on statistical data and things like indexes. Essentially it is making its best attempt to create a procedure that is as highly optimized as possible and can return the results your’re looking for.
Once this Execution Plan is created it is then cached on the server so that the next time a query with the same parameters and structure is called it can simply run that existing plan instead of having to build a new one which can be, and is often, processor and i/o intensive; creating undo overhead.
Using cached execution plans is considered a best-practice because it allows your queries to execute much faster (since the plan doesnt have to be constantly rebuilt/compiled) and as a side effect because you are passing data to typed values (int, varchar, etc.) you avoid any possibilities of SQL Injection Attacks.
How?
Note:
This is applicable to all languages and application types: web, desktop, cli… wether it be in cfml, c#, or c++, SQL essentially functions the same way with the exception of the driver being utilized. If you aren’t sure your execution plans are being cached take the time to look up the correct way to do it. You will be glad you did.
From comment: 12/22/09
I discovered later on, this is actually a very BAD implementation. The reason is when using indexes, executing a function on each item requires an entire index scan to be done instead of an index seek. This completely screws up doing a range search for dates on a clustered index. Yikes.
The methods above work, but boy or they ever inefficient. Avoid them if at all possible. Consider using things like DateDiff(), etc.
And Remember, when your writing high performance queries always study the execution plan and reduce bottlenecks!
Why?
Sooner or later you will need to do date-only range searches (where you are not concerned with times). Many programmers will actually specify a start time of 12:00 AM and ending time of 11:59:59 PM to get around this. But if you write a lot of queries you will quickly find this to be an annoyance and without an appropriate solution it can be easy to forget about times all together; creating a hard to find bug that will crop up and have your users up in arms.
How?
Since datetimes are stored as binary values in SQL we can utilize this to our advantage by casting the datetime object to a decimal and passing that result to the floor() function to isolate the date and effectively chop off the time.
declare @startdate datetime
declare @enddate datetime
set @startdate = '01/01/2008'
set @enddate = '03/01/2008'
select
fname + ' ' + lname as emp_name,
convert(varchar(10), hire_date, 101) as hire_date,
hire_date as og_hire_date,
floor(cast(hire_date as decimal(9,4))) as og_hire_date_cast
from employees
where
floor(cast(hire_date as decimal(9,4))) between @startdate and @enddate
Note:
Conversely if we only wanted the time, we are concerned with what is on the right side of the decimal. Using the function makes this a cinch. For more information on how SQL stores datetimes internally, check out Date and Time (Transact-SQL) on MSDN
SQL Function, MSDN Ref.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
CAST ( expression AS data_type [ (length ) ])
FLOOR ( numeric_expression )
Why?
Formatting datetimes directly in your query is significantly faster than using dateFormat() and timeFormat() in ColdFusion (and most other languages) and simplifies your query output by having formatting already completed.
How?
Formatting datetimes is incredibly simple. All you need do is call Convert() and specify the format parameter you wish to use.
Select top 1
convert(varchar(10), birthdate, 101) as birthdate
from employees
Notice the style parameter of 101. This returns the date in US Standard format mm/dd/yyyy. A plethora of style parameters are available including options for times and standard formats of countries outside the US. Check MSDN for a complete listing and more information on the Convert method.
SQL Function, MSDN Ref.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
