Give Me One Good Reason Why You Would Store Documents In The Database?

Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.

So here is the question:
Would you store images/documents/spreadsheets in the Database and why?

SQL Server 2005 High Availability Podcast On DotNet Rocks

.NET Rocks has made their latest podcast available, this one deals with SQL Server 2005 high availability
Allan Hirt discusses the details of providing high availability with SQL Server 2005, and the things developers need to know in order to make their applications compatible. He discusses clustering, transaction log shipping, mirroring, and more

The podcast is available in MP3, WMA,WMA L lo-Fi and IPOD (AAC) formats and the duration is one hour and 15 minutes (1:14:55)

Get the podcast here: http://dotnetrocks.com/default.aspx?showID=215

How To Restart A Remote Computer

Sometimes you have to login to your work PC from home over the VPN and after a while for some reason or another you want to restart your PC. How can you do that? You can't use the start menu because only the log off button is displayed
Well one way is to open a command window and executing shutdown -r
That will restart your computer

Here is the basic usage of the shutdown command

Usage: shutdown [-i -l -s -r -a] [-f] [-m \\computername] [-t xx] [-c "comment"] [-d up:xx:yy]

No args Display this message (same as -?)
-i Display GUI interface, must be the first option
-l Log off (cannot be used with -m option)
-s Shutdown the computer
-r Shutdown and restart the computer
-a Abort a system shutdown
-m \\computername Remote computer to shutdown/restart/abort
-t xx Set timeout for shutdown to xx seconds
-c "comment" Shutdown comment (maximum of 127 characters)
-f Forces running applications to close without warning
-d [u][p]:xx:yy The reason code for the shutdown
u is the user code
p is a planned shutdown code
xx is the major reason code (positive integer less than 256)
yy is the minor reason code (positive integer less than 65536)

Need Help With Picking Out An ASP.NET 2.0 Book

I decided to get back into some web development again, the last time I did this was in October 2001. I have developed in ASP, JSP and ColdFusion. Since I know some C# and have been using that on and off for the last 2 years with windows forms I decided to use ASP.NET 2.0. Another factor is that it is also a corporate standard at work.
So for someone like me which book would you recommend? Or maybe you have read an ASP.NET book that is just awesome either way let me know.

Don't worry I will never spend less than 51% of my time with SQL ;-)

ASP.NET AJAX PDF Cheat Sheets Available For Download

The ASP.NET AJAX PDF Cheat Sheets have been updated for the ASP.NET AJAX 1.0 final release. There are 6 sheets that you can download in PDF or XPS format. Get the sheets here: http://aspnetresources.com/blog/ms_ajax_cheat_sheets_batch2.aspx

Are You ready For Daylight Saving Time Changes In 2007

In August of 2005 the United States Congress passed the Energy Policy Act, which changes the dates of both the start and end of daylight saving time (DST). When this law goes into effect in 2007, DST will start three weeks earlier (2:00 A.M. on the second Sunday in March) and will end one week later (2:00 A.M. on the first Sunday in November) than what had traditionally occurred.

Read this article (preparing for daylight saving time changes in 2007) to see how you are affected

I hope that you are not running NT4

Here are some direct update links
Windows XP
Windows 2003 Server
Full List

Microsoft products affected by the DST legislation
Windows Client
Windows Server
Windows Mobile
Microsoft Windows SharePoint Services
Microsoft Exchange Server
Microsoft Office Outlook
Microsoft Dynamics CRM
Microsoft Biztalk Server
Microsoft Entourage

ASP.NET AJAX Version 1.0 Released

ASP.NET AJAX formerly known as Atlas has been released get it here http://ajax.asp.net/

Microsoft SQL Server Database Publishing Wizard 1.0,SharePoint Server 2007 SDK And Windows SharePoint Services v3 SDK Available For Download

Microsoft SQL Server Database Publishing Wizard 1.0
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.



SharePoint Server 2007 SDK
The Microsoft Office SharePoint Server 2007 Software Development Kit (SDK) contains conceptual overviews, programming tasks, code samples, references, and an Enterprise Content Management (ECM) starter kit to guide you in developing solutions based on Microsoft Office SharePoint Server 2007.

Windows SharePoint Services v3 SDK
The Windows SharePoint Services 3.0 software development kit (SDK) contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Windows SharePoint Services 3.0.

Check If Auto Update Statistics Is Enabled With DATABASEPROPERTY

How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function
Run the following line of code

SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics

If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled

Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code


DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'

SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version

So what do all these values mean? Here is a list of all the properties

IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input

Version
Internal version number of the Microsoft® SQL Server™ code

SQL Server Doesn't Like Cheaters

I emailed a joke(see below) to some friends and one of them replied: "So what you're saying is that I should take a mistress?"

So that got me thinking, what would SQL say about this? Is mistress equal to mistrust. Well according to SQL server it is, run this in Query Analyzer

SELECT SOUNDEX('mistress'),SOUNDEX('mistrust'),DIFFERENCE('mistress','mistrust')

And here is the joke:

Two Ladies talking in heaven

1st woman: Hi! My name is Wanda.

2nd woman: Hi! I'm Sylvia. How'd you die?

1st woman: I Froze to Death.

2nd woman: How Horrible!

1st woman: It wasn't so bad. After I quit shaking from the cold, I
began to get warm & sleepy, and finally died a peaceful death. What
about you?

2nd woman: I died of a massive heart attack. I suspected that my
husband was cheating, so I came home early to catch him in the act.
But instead, I found him all by himself in the den watching TV.

1st woman: So, what happened?

2nd woman: I was so sure there was another woman there somewhere that I started running all over the house looking. I ran up into the attic and searched, and down into the basement. Then I went through every closet and checked under all the beds. I kept this up until I had looked everywhere,and finally I became so exhausted that I just keeled over with a heart attack and died.

1st woman: Too bad you didn't look in the freezer---we'd both
still be alive.


So what is the point of this all? Well it gives you a reason to run those barely used functions like SOUNDEX and DIFFERENCE ;>

A Witchbrew Of SQL Server News And Interesting Links

Since I have nothing interesting or fascinating to say I decided to put some links up to some posts of people who do have something interesting to say.
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation

Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1

Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)

Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend

And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?

sp_refreshview

Did you know that when you create a view and then later change the table the view is not updated?
Don't believe me? Run the following block of code

CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO

INSERT TestTable VALUES(1,'ABC')
GO

SELECT * FROM TestTable
GO

CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO


ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO

INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO

SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO

See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works

sp_refreshview TestView
GO

--All good now
SELECT * FROM TestView
GO


--Clean up this mess--
DROP VIEW TestView
GO


DROP TABLE TestTable
GO


And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)

Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) Released

Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) has been released.
This CTP is available as a Virtual PC image or as a self-extracting install. If you wish to use the Virtual PC image you will need Virtual PC or Virtual Server to run this image. If you wish to use the self extracting install, we advise that you do not install this on a production machine. Depending on your hardware the download files make take between 30-60 minutes to decompress.



The highlights of this CTP include:

• Extended, more powerful data APIs with the ADO.NET Entity Framework and LINQ to ADO.NET
  
  • With the ADO.NET Entity Framework developers will be able to model the view of the data that is appropriate for each one of the applications they are building, independently of the structure of the data in the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. Once the model is in place, the powerful ADO.NET Entity Framework API is used to access and manipulate the data as .NET classes or as rows and columns, whatever is appropriate for each application.
    
  
  
  • ADO.NET is fully integrated with LINQ and offers many options for using LINQ in various scenarios: LINQ to SQL provides direct access to database tables from the programming environment, LINQ to Entities enables developers to use LINQ over EDM models, and LINQ to DataSet allows the full expressivity of LINQ to be used over DataSets.
    
  
  

• C# 3.0 Language Support: This CTP implements all of the C#3.0 language features from the May LINQ CTP including:
  
  • Query Expressions
  
  
  • Object and Collection Initializers
  
  
  • Extension Methods
  
  
  • Local Variable Type Inference and Anonymous Types
  
  
  • Lambdas bound to Delegates and Expression trees
  
  

• VB 9.0 Language Support: This CTP implements all of the VB 9.0 language features from the May LINQ CTP including:
  
  • Query Expressions
  
  
  • Object Initializers
  
  
  • Extension Methods
  
  
  • Local Variable Type Inference
  
  
  • Anonymous Types
  
  

• LINQ to Objects API
  
  • The LINQ to Objects API supports queries over any .NET collection, such as arrays and Generic Lists. This API is defined in the System.Linq namespaces inside System.Core.dll. Click here for more details about LINQ.
    
  
  

• ClickOnce improvements
  
  • This CTP delivers ClickOnce improvements for the deployment of Windows Presentation Foundation applications, alternative browser support and ISV rebranding.
    
  
  

• Managed classes for Elliptic Curve Diffie Hellman and Elliptic Curve Digital Signature Algorithm cryptographic functionality
  
  • With the addition of these classes, cryptographic developers now have managed classes for Elliptic Curve Diffie Hellman secret agreement and Elliptic Curve Digital Signature Algorithm signing. These classes are built on the new CNG cryptographic libraries in Windows Vista, but still follow the familiar patterns of the cryptographic classes in .NET Framework 2.0.
    
  
  

• Runtime and design-time support for Office 2007 (including Outlook 2007)
  
  • Customers can build managed code add-ins with a consistent development experience, regardless of which version of Office they target, which Office application(s) they target, and which programming language they choose. Managed code add-ins enable developers to use strongly-typed class members, with the help of modern development tools, including intellisense and auto-complete. Additionally add-ins can potentially run in multiple versions of Office, enabled by abstracting version-specific code and supported by a version-resilient infrastructure.
    
  
  

• Support for advanced lifetime management of add-ins and their AppDomains
  
  • We’ve added the helper classes that manage the lifetime of add-ins, the objects passed between the host and add-ins, and even of the AppDomains the add-ins live in. By using the ContractBase and LifetimeToken handle, pipeline developer can let the hosts and add-ins act as if everything, including the AppDomain the add-in was activated in, was controlled by the garbage collector even though .Net Remoting would normally make that impossible.
    
  
  

• Client service support for Login/Logout, Role management and Profiles
  
  • ASP.NET 2.0 shipped with new application services for authentication, authorization and personalization. Most of these services are not tied to ASP.NET and can work in non-web applications. This CTP enables the use of these services in smart client applications for Logon/Logoff, Role management and profiles.
    
  
  

• A trace listener that logs event to ETW, event tracing for Windows in Vista
  
  • Event tracing for windows is greatly improved in Vista and the most performant loggings facility available in Windows. The System.Diagnostics.EventProviderTraceListener allows managed tracing to provide events to the Vista’s ETW infrastructure. This is a highly performant, thread-safe listener.
    
  
  

• Jscript Intellisense support
  
  • Jscript code formatting and Intellisense support provide developers with a richer editing experience. These improvements enable the IDE to provide statement completion, color syntax highlighting and in-place documentation to Jscript and associated script models such as ASP.NET AJAX.
    
  
  

• A new numeric type that provides support for very large numbers (Beyond the range of In64)
  
  • All existing numeric types in the Framework have a limited range. This is the first type that supports arbitrary range and will extend to accommodate any large number as needed. This type lives in the new System.Numeric namespace where all new numeric and arithmetic features are going to reside. It supports all the basic arithmetic operations including things like Pow, DivRem and GreatestCommonDivisor. It implements the following interfaces: IFormattable, IComparable, IComparable<BigInteger> and IEquatable<BigInteger>. It is serliazable and immutable. It has implicit casts from all basic integral types and explicit casts to/from all numeric type. To learn more about this type – please visit the BCL team blog.
    
  
  

• LINQ over XML (XLinq)
  
  • Enable further LINQ over XML feature support (in addition to the functionality available in the Oct 2006 CTP) such as the ability to apply XLST to transform into and out of XLinq trees, support for System.XML reader/writer interfaces for improved XML sharing with DOM applications and System.XML schema validation for XLinq nodes.
    
  
  

• SQL Server Compact Edition (SSCE)
  
  • SQL Server Compact Edition (SSCE) provides a local relational data store for occasionally connected client applications from desktops to devices. SSCE is light weight, embeddable and is easy to deploy with your client applications without requiring complex administration work from users. Timestamp (row version id) data type, improved table designer, Query processor enhancements and support for local transaction scope are some of the new features you find in this version of SSCE.
    
  
  

Thi CTP is available both as a regular install, and on a virtual machine.

Have fun

How To Check If Any, ALL Or No Parameters Have A NULL Value

Let's say you have a procedure that accepts 6 parameters. depending on if these parameters are all null, all not null, some null and some not null you want to do different things.
How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.


So let's start with all nulls
You can do a bunch of ANDs
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a NOT EXIST NOT NULL test
Finally you can use COALESCE which will return NULL if ALL the values are NULL


--ALL NULL
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


IF @Param1 IS NULL
AND @Param2 IS NULL
AND @Param3 IS NULL
AND @Param4 IS NULL
AND @Param5 IS NULL
AND @Param6 IS NULL
PRINT 'ALL NULL'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'ALL NULL'


IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'ALL NULL'



IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
PRINT 'ALL NULL'

Next up is to test that at least one value is not NULL
You can do a bunch of Ors
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

--Some Non Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'At least One Value is not NULL'



IF @Param1 IS NOT NULL
OR @Param2 IS NOT NULL
OR @Param3 IS NOT NULL
OR @Param4 IS NOT NULL
OR @Param5 IS NOT NULL
OR @Param6 IS NOT NULL
PRINT 'At least One Not NULL'


IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'At least One Not NULL'

IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
PRINT 'At least One Not NULL'


Next up is to test that there are no NULLs
You can do a bunch of ANDs
You can also union the parameters and do a NOT EXIST NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

--No NULLS
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 ='A',
@Param5 ='A',
@Param6 =1


IF @Param1 IS NOT NULL
AND @Param2 IS NOT NULL
AND @Param3 IS NOT NULL
AND @Param4 IS NOT NULL
AND @Param5 IS NOT NULL
AND @Param6 IS NOT NULL
PRINT 'No NULLs'

IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'No NULLs'


SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'No NULLs'

The final test is to test that at least one value is NULL
You can do a bunch of Ors
You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test

--Some Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 =null,
@Param5 ='A',
@Param6 =1

IF @Param1 IS NULL
OR @Param2 IS NULL
OR @Param3 IS NULL
OR @Param4 IS NULL
OR @Param5 IS NULL
OR @Param6 IS NULL
PRINT 'At least One NULL'

SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'At least One NULL'
ELSE
PRINT 'No NULLs'

IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'At least One NULL'

SQL Server Integration Services 2005 Connectivity Whitepaper Available At ssis.wiki.com

Bob Beauchemin author of A Developer's Guide to SQL Server 2005 (Microsoft .NET Development Series) has written a SSIS Connectivity Whitepaper(still a draft)

This whitepaper enumerates the connectivity options for databases with SQL Server Integration Services 2005 and mentions special considerations that are required when using some database sources with some of the more complex transformations.

It is a word document and contains 27 pages. You can download it here: http://ssis.wiki.com/Connectivity_White_Paper

Enjoy

Increase Your Productivity With Query Analyzer

Did you know that you can have CTRL + Number key combinations/shortcuts in Query Analyzer? You can set it up so that CTRL + 5 executes sp_who2 for example. I thought everyone already knew this but apparently not; I showed this to 3 coworkers yesterday and they didn't know about it. So how do you set this up? It is pretty easy, select Tools -->Customize from the toolbar. In the Customize tab you will see 3 shortcuts already:

ALT + F1 sp_help

CTRL + 1 sp_who

CTRL + 2 sp_lock



To add your own queries/procedures type or paste the query or stored procedure that you want to execute into one of the empty ones (see picture below)



If you have 4 Query Analyzers applications open it will be available in all 4 of them. I usually have 4 Query Analyzer applications open, one for staging, one for development and one each for one of the production boxes. so if i have to quickly check that a table is the same on all 4 machines I just hit CTRL + 6 in all 4 Query Analyzer applications and I am done.

I created a Word document that looks like this
3 - errorlog
4 - product table
5 - country table
6 - vendor codes
7 - sp_who2
etc etc etc

The font-size is 32, I printed this out and put it next to my monitor so that I can quickly see which key combination I have to hit. Once you set this up you will see how much time it will save you.

[wrong]I did not see this functionality in SQL Server 2005 Management Studio so maybe it is something we should request from Microsoft to be added in SP3?[/wrong]

It is there in SS 2005. Click Tools -> Customize -> "Commands" tab -> "Keyboard" button (Lower left corner of screen).On the options window, click the Keyboard option (under Environment). See pictures below:

SQL Server 2005 Express Edition for Beginners Learning Videos Available

Microsoft has a set of learning videos available on the SQL Server Express site, these videos are geared towards beginners.

The series includes almost 9 hours of video-based instruction that walks SQL Server beginners through the steps of learning about SQL Server databases to actually connecting a SQL Server database to a Web application. Select your starting point below based on your skill set.

Introduction

Learning Video 1: What is a database?

Designing Tables

Learning Video 2: Understanding Database Tables and Records

Learning Video 3: More about Column Data Types and Other Properties

Learning Video 4: Designing Relational Database Tables

Database Functions

Learning Video 5: Manipulating Database Data

Learning Video 6: More Structured Query Language

Learning Video 12: Creating and Using Stored Procedures

Learning Video 13: Enabling Full-Text Search in your Text Data

Creating and Using Reports

Learning Video 10: Getting Started with Reporting Services

Learning Video 11: Embedding, Packaging and Deploying SQL Server Express Reporting Services

Database Security

Learning Video 7: Understanding Security and Network Connectivity

Database Management

Learning Video 9: Using SQL Server Management Studio Express

Publishing to the Web

Learning Video 8: Connecting your Web Application to SQL Server 2005 Express Edition

So what are you waiting for? go to the link below to check it out.
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

Difference of two datetime columns caused overflow at runtime

You want to get the difference in seconds between 2 dates, you can do that by using datediff(s,date1,date2)
However if the difference in seconds is greater than a value that can be stored in an integer then you will get the following message

Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.

Run this to see that message
SELECT DATEDIFF(s,'19000101','20060101')

So what can you do to eliminate this?

You can convert to bigint and then multiply by 60

SELECT CONVERT(BIGINT,DATEDIFF(mi,'19000101','20060101'))* 60


To make sure that the match is correct you can compare these two


SELECT DATEDIFF(s,'19700101','20060101')

SELECT CONVERT(BIGINT,DATEDIFF(mi,'19700101','20060101'))* 60

They both return 1136073600

Blogstats For The Year 2006

So the year 2006 is finally over; here are some stats

Visits By Source
As you can see from the picture Google is king in terms of people reaching this blog, Tek-Tips is number two and MSN is number three. Actually the real number two is a direct link; this is either from people who type the URL in their address bar or maybe use a RSS reader or even outlook express to read the SQL server newsgroups. I assume if people use the Google groups that this counts towards the overall Google number



Visits By New And Returning
Of the people who visit this site 23.39 percent are returning visitors. I don’t know if this is good or bad and what other people’s numbers are; I have nothing to compare against.


Visits and Pageviews
So during 2006 this blog had 45375 page views, the average is 124 page views a day. I guess that this is not a bad thing; my average is higher when looking only at the last month. At least it looks like the average is going up not down ;-)
The big spike that you see is when one of my articles got posted to digg and then to dzone.



Geo Map Overlay
Most people who visit this site come from North America, Europe, India and Singapore
Nothing more to say here really; the picture speaks for itself



And that is it for the year 2006; from now on it is all 2007

Top Google Searches For December 2006 And For 2006

Below are the top searches for December 2006 and also for the whole year of 2006. These searches were made from the Google search box on this site. I removed out racists and derogatory terms but there are still some interesting searches out there

December 2006
Fleury
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
www.sampiyonhersekli.com
how to analyze logical reads in sql server?
mpentoo modules
To be is to be something in particular; to be nothing in particular or everything in general is to be nothing
Free download of projects in java
money
pages/per sec
Heidi Hanson figure competition
169.254.x.x
WHAT DOES IT MEAN TO MASK DIFFERENT TABLESIN SQL
performances COALESCE vs Case in SQL Server
redneck timeout
DSN and ODBC


Top Searches for the whole year of 2006
dtsrun from sp
query multiple databases
macrovision remover
ToBase64String sql procedure
/pub /books ansys ftp
HOW TO IMPROVE SQL SERVER Query PERFORMANCE
increase maximum row size in mssql
dbreindex microsoft sqlserver forum
Line 1: Incorrect syntax near 'TRY'.
sql server CONNECTIVITY ERRORS thro dsl after installing xp sp2
heather mills
deadlock
MS SQL zero fill
cfd freeware software
sex
cad cam software prodajem
body types
type of database backup
/tutorials/ .pdf ftp ansys
sql server management studio