Select rows where any column contains a NULL

Thanks to Aaron Bertrand
http://dba.stackexchange.com/questions/14864/test-if-any-fields-are-null

 DECLARE @table NVARCHAR(255) = N'dbo.LU_Stores';  
 DECLARE @statement NVARCHAR(MAX) = N'SELECT * FROM ' + @table + ' WHERE 1 = 0';  
 SELECT @statement += N' OR ' + QUOTENAME(name) + ' IS NULL'  
   FROM sys.columns   
   WHERE [object_id] = OBJECT_ID(@table)  
   and right(name,2) = 'ID'  
 ;  
 --print @statement  
 EXEC sp_executesql @statement;  

Write Excel data using SQL

SQL Configuration to run these damn things

curtecy of http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Now, we should connect to the related SQL Server as an administrator and open a new query window.



After the query window is ready for running sql statements run the "sp_configure" sql statement.



If sp_configure command only lists a limited number (~14) of sql configuation settings, where 'Ad Hoc Distributed Queries' does not exist in the returned result set, we should open/enable the 'show advanced options' configuration parameter.



You can see 'show advanced options' in the list with run_value equals to "0" in such a situation.

To set 'show advanced options' run_value equal to 1 or to enable it, run


sp_configure 'show advanced options', 1

reconfigure


The return message from the above sql statements for a successful run is as;


Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


After Advanced Options is enabled, you can again run sp_configure t-sql command and in the returned list of configuration settings, go to row where name is 'Ad Hoc Distributed Queries' and control its run_value.

If 'Ad Hoc Distributed Queries' is turned off for considering server security run_value should be "0"

But since we want to enable 'Ad Hoc Distributed Queries' component in order to run 'OpenRowset/OpenDatasource' sql statements, we should set the run_value to "1"

The below sql code is a sample how you can enable a SQL Server configuration parameter.


sp_configure 'Ad Hoc Distributed Queries', 1


The returned message is :


Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.


A change with sp_configure comment will require reconfigure command to run in order to the new setting takes effect. So just run the "reconfigure" command:


reconfigure


Now you can see the run_value is set to 1 if you run the "sp_configure" command and control for the "Ad Hoc Distributed Queries" row.

So how do we write data back to Excel using SQL code?

First... read here to ensure all setings are correct.

Code is:

INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0; Database=[Full excel file path and name.xls]',
'SELECT [Columns to insert to] FROM [Sheet1$]')
SELECT *
from SQL_Table