The installation of SQL Server requires .NET Framework 3.5.1.
Installing this framework on Windows 2012 (both standard and R2) is not easy, there is something wrong with the setup.
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 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? '
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
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.
The steps to create a new linked server to access Oracle data from SQL Server are:
- install the Oracle client drivers on the SQL Server machine;
- install the Oracle client ODAC (Oracle Data Access Components);
- 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).
In SQL Server, to change the schema owner (tipically, to be able to delete the user owning the schema), the command to use is something like this:
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO NewUser;
To enable database mail, from Sql Server Management Studio execute the following query:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
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.
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!
SQL Server 2008 introduces the concept of filestream, but by default it is disabled.
To enable it you have to do the following things:
- enable the feature from SQL Server Configuration manager (SQL Server Services, SQL Server, Properties, FileStream tab);
- 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.
Here are the two examples of importing data from Excel into SQL Server using T-SQL: both tecniques use the OPENROWSET function:
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyExcel.xls', 'SELECT * FROM [Sheet1$]')
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
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.
In SQL Server 2005, to enable dtsx Logging:
- open your dtsx in SSMS, then right click and select "Logging" and a dialog box will open
- check the root of the tree on the left pane of the dialog box
- add a new SSIS log provider, enable it and create a new connection
- in the Details tab, select all events, except "Diagnostics"
- press the Advanced button, and clear the Computer, Operator, SourceID and ExecutionID columns
To log the value of the dtsx variables:
- insert a Script Task where you iterate the dts variabiles via Dts.Variables.GetEnumerator() and log them with the Dts.Log method
- in the previous dialog, configure only this task to log the ScriptTaskLogEntry events