SQL Server: delete all rows of all tables to empty a database

In SQL Server it's possible to empty a database deleting all rows of all tables with the following simple script I've found in the blog post T-SQL Trick for Deleting All Data in Your Database:

-- disable referential integrity

EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? '

-- enable referential integrity again

But if the database contains identity columns and you want to reset them, you can visit the forum topic Truncate All Tables, answer of 06/13/2007 at 11:38:52, or download directly the code from the file EmptyAllTables.sql.txt.

SQL Server: how create a linked server to Oracle

The steps to create a new linked server to access Oracle data from SQL Server are:

  1. install the Oracle client drivers on the SQL Server machine;
  2. install the Oracle client ODAC (Oracle Data Access Components);
  3. in the linked server providers, configure the OraOLEDB.Oracle provider to allow Inprocess.

Now you can choose:

Finally: you have to configure the security, i.e. the login used to access the Oracle server.

About the two final steps: you can use the SQL Server Management Studio (as described in the first link) or via stored procedures (as in the second link).

SQL Server: How enable database mail

To enable database mail, from Sql Server Management Studio execute the following query:

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'Database Mail XPs', 1

Then from the Management node, right-click Database Mail, and select Configure Database Mail.

Here you can add profiles, and for each profile multiple email accounts.

Remember: Database Mail requires the Sql Server Agent service to run, else messages will accumulate in a query in the Msdb database.

How search a string in all the tables of a database

If you receive a database in witch you need to search for some info, and you know the info is there, but you don't know where, probabily adding a search stored procedure could help you.

I've found a good stored procedure in the article How to search all columns of all tables in a database for a keyword?: it's written for SQL Server 7 and 2000, but I've used in SQL Server 2008 without any problem.

In the file SearchAllTables.sql.txt you can also find a backup copy of this important script!

How enable the filestream feature in SQL Server 2008

SQL Server 2008 introduces the concept of filestream, but by default it is disabled.

To enable it you have to do the following things:

  1. enable the feature from SQL Server Configuration manager (SQL Server Services, SQL Server, Properties, FileStream tab);
  2. then activate on the database server, via SSMS (Database server, properties) or via T-SQL:
         EXEC sp_configure filestream_access_level, 2

More details in the article How to: Enable FILESTREAM.

Import Data from Excel with T-SQL

Here are the two examples of importing data from Excel into SQL Server using T-SQL: both tecniques use the OPENROWSET function:

--Excel 97-2003
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyExcel.xls', 'SELECT * FROM [Sheet1$]')

--Excel 2007-2010
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=C:\MyExcel.xlsx', 'SELECT * FROM [Sheet1$]')

If you are unable to use the OPENROWSET function, you need to do the following:

sp_configure 'show advanced options', 1

sp_configure 'Ad Hoc Distributed Queries', 1

SQL Server On Non Standard Ports

If you have an installation of SQL Server on non standard ports, from your code you can connect to it from your code using a standard connection string, but with the addition of creating a ODBC system connection pointing the same SQL Server, and assigning there the custom TCP port.

When you need to connect to SQL Server Management Studio, in the login form you need to use SERVER,PORT.

Enabling dtsx Logging

In SQL Server 2005, to enable dtsx Logging:

  1. open your dtsx in SSMS, then right click and select "Logging" and a dialog box will open
  2. check the root of the tree on the left pane of the dialog box
  3. add a new SSIS log provider, enable it and create a new connection
  4. in the Details tab, select all events, except "Diagnostics"
  5. press the Advanced button, and clear the Computer, Operator, SourceID and ExecutionID columns

To log the value of the dtsx variables:

  1. insert a Script Task where you iterate the dts variabiles via Dts.Variables.GetEnumerator() and log them with the Dts.Log method
  2. in the previous dialog, configure only this task to log the ScriptTaskLogEntry events