T-SQL: set end date to first or fifteenth of the month

Here is a simple assignment: if the subscription date is from first to 15th of the month then the expiration date should be set to the 15th of the same month a number of years later; if the subscription date is from 16th to the end of the month then the expiration date should be set to the first of the following month a given number of years later.

I am sure there are many ways to do this but here is one:

DECLARE @sDate DATETIME -- subscription date
DECLARE @sYears tinyint -- number of years the subscription will last
SET @sDate = getdate()
SET @sYears = 1

IF datepart(dd, @sDate) <= 15
SELECT DATEADD(year, @sYears, @sDate) - datepart(dd, @sDate) + 15
ELSE
SELECT DATEADD(year, @sYears, DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0))

Script Executor - package t-sql scripts into executables

In addition of allowing you to deploy multiple scripts to multiple databases directly from the interface Script Executor also allows you to package all your scripts into an executable.

An executable package in the context of the Script Executor is a small, dynamically generated executable file that contains embedded in it scripts, list of databases against which the scripts will be executed, and a small piece of code to run these scripts. You can run it as you would run any other Windows program.

Executable packages are suited for deploying Sql script to the end-users/clients. A user can run it without having Script Executor, or SSMS installed.

SQL Server execute scripts - multiple scripts, multiple databases

You launch SQL Server Management Studio – open script #1 and execute it against database 1 on SQL Server 1, then execute it against database 1 on SQL Server 2… then open t-sql script #2 and execute it against database 1 on SQL Server 1 etc… what a painful process, you dread the day you have to go through this (sometimes that is everyday). What a waste of resources too – a $100K database administrator spending hours executing scripts on tenths of server!

No more! You can now use Script Executor to take care of this job for you. One time only, you will go through a simple, and may I suggest fun, process of organizing your scripts into virtual containers and your databases into groups; then, map those script containers to the database groups; set an order of precedence if you need to and voila you have a package that you can now execute with a single click; or, you can even schedule it to run before you even make it to the office.

Script Executor will execute each script in the order you have defined against each database based on the mappings you have defined and once completed it will generate a comprehensive report allowing you to see how the script execution went on each target and also easily “flip through” the result sets that those scripts may generate.

Deploying (executing) multiple scripts against multiple SQL Server databases has never been easier.

You can download a free trial version of Script Executor from: http://www.xsql.com/download/script_executor/

A free community edition of Script Executor with limited functionality is also available: http://www.xsql.com/download/script_executor/

Applies to:

• SQL Server execute t-sql scripts
• SQL Server execute multiple scripts
• SQL Server deploy scripts to multiple databases
• SQL Server package t-sql scripts
• SQL Server run multiple scripts

Kindle, Zune, iPod - a free gift with every purchase

This month only we are giving away a free cool gift with every license purchase - kindle wireless, Zune, iPod Touch etc. Check out the details here: http://www.xsql.com/promotions/

Is Bing beating Yahoo already?

Looking at our site stats today I was surprised to see that in the last 30 days we have received about 20% more “organic” visitors from Bing than from Yahoo! Is this just an aberration or is Bing already beating Yahoo? What you seeing on your site? Please answer the poll on the right panel here and leave your comments below.

The poll has been closed - the final result was 60% for Bing and 40% for Yahoo however, the number of responses we got was statistically insignificant so I would put no stock on these results.

$100 for your SQL puzzle

We are planning to bring back the “puzzle of the month” program and this time the puzzles will come from you. We are asking you to send us the hardest SQL puzzles you have come across or that you can come up with.

If your puzzle is chosen to be published on our site we will:

• Send you a $100
• Link from the puzzle page to your blog or your site

The harder your puzzle the higher the chances that it will be picked, however,

The puzzles should fall into one or more of the following categories: SQL Server, T-SQL, Database Management Systems

Submit your puzzle here: sales@xsql.com

To pay or not to pay this is the question

Product reviews are absolutely critical to the success or failure of a product and that is especially true in the software world. Trying a software product takes effort and precious time so we all naturally gravitate towards relying on product reviews that others have done and only take the time to try one or two products that may have been heavily and positively reviewed.

Knowing how important those reviews are we have sent tenths of product review requests in the last 6 years and in the process we have learned the unsettling truth about product reviews: they don’t come by easily. In the case of the big guys (technical magazines and large user group sites) you don’t have to pay for the product review per-say but your product will forever be in the queue unless you are shelling out a few thousand advertising dollars in those medias, only then does your product magically shoot up to the top of the product review queue; whereas in the case of the smaller guys (bloggers and user group contributors) you often have to pay for the review anywhere from a set price of as low as $200 for one product review to a per word price that can be as high as $0.6 per word (this is the highest product review offer we have received).

In the case of the big guys since you are not paying directly for the product review there is no moral dilemma – deep down you know that the review can’t possibly be fair and un-biased. If you are paying $30K / year for advertisements on that media the last thing they would want to do is alienate you with a bad product review, but, it’s easy to shun those thoughts, after all this is your product and you truly believe that it is a great product.

On the other hand, when it comes to paying for the review directly it feels like cheating and unfair to the reader who believes that he is reading an impartial review. For it to feel right one must disclose in big bold letters that “this review has been paid for by the product vendor…”, but such disclosure would likely deem the review worthless. Ideally you would want someone to review your product simply because they like the product and they want to share it with other people but in all fairness reviewing a software product and then writing up your observations and conclusions may take hours of work of which we humans have a very limited supply of. Herein lies our dilemma – to pay or not to pay?

Until now we have chosen not to pay, instead we have only offered the reviewers a free license for the product they decide to review which we think is fair and does not present a conflict of interest. We would certainly be more comfortable continuing with this approach but the results are not very promising hence our pondering.

Let us know what you think – would you pay?

And of course, we would be delighted if you would consider reviewing any of our fine products in exchange for a free license.