Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0

ERROR MESSAGE:
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.


RESOLUTION:
We have to enable the Database Mail feature on the server. In the SQL Server Management Studio to use Transact SQL to enable Database Mail, execute the following statement.

use master
go
sp_configure ’show advanced options’,1
go
reconfigure with override
go
sp_configure ‘Database Mail XPs’,1
–go
–sp_configure ‘SQL Mail XPs’,0
go
reconfigure
go

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

How can be used to remove all database pages and clear cache from the memory in SQL Server?

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

How do I reduce the size of my MS SQL database?

Shrink the size of the database data and log files.

Syntax
DBCC SHRINKDATABASE ('database' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE ('database_id' option [,option] ) [WITH NO_INFOMSGS]

DBCC SHRINKDATABASE (0 option [,option]) [WITH NO_INFOMSGS]

Options:
target_percent
NOTRUNCATE
TRUNCATEONLY

Key:
0 - Shrink the current database

target_percent - Percentage of free space to remain in the database file

NOTRUNCATE - Free space at the end of the data file is not returned to the OS
(pages are still moved)
TRUNCATEONLY - Release free space at the end of the data file to the OS
(do not move pages)
NO_INFOMSGS - Suppress all information messages (severity 0-10)
Only one of the truncate options can be specified - they do not apply to log files.

Example

DBCC SHRINKDATABASE (MyDatabase);

How to find last executed queries in SQL server 2005?

SELECT deqs.last_execution_time AS [DateTime], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

The Tabular Data Stream (TDS) version 0x730a0003 of the client library used to open the connection is unsupported or unknown.

Some customers have reported connection failure because of wrong TDS version.
The client application gets the following error message: (or similar depends on the protocol used)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)


When you check the errorlog of your SQL Server, you see the following error message:

The Tabular Data Stream (TDS) version 0x730a0003 of the client library used to open the connection is unsupported or unknown. The connection has been closed. [CLIENT: x.x.x.x]

Error: 17802, Severity: 20, State: 1.

Depends on your client version, 0x730a0003 could be 0x73080003 or 0x73090003 as well.

The failure only happens when connecting to SQL Server 2008 CTP releases. Usually the client involved uses SQLClient which is part of .Net Framework. The reason here is that we don't supported connection between intermediate TDS version. SNAC usually ships with SQL Server and they are managed to be in the same TDS level. If you download SNAC CTP separately or make connection across machines with different level of SNAC, you may also see similar error. As a background, for 0x730a0003 TDS version, 73 represents TDS major version 7.3, 0x03 is the minor version for TDS7.3, 0xa is the intermediate TDS version number.

We have shipped the following intermediate TDS version for SQL Server 2008.

· 08 first 7.3 TDS version in Katmai (Shipped with Katmai CTP3 --Jun 2007--, Orcas Beta1)

· 09 TVP (Table Value Parameter) and NewDateTime (Shipped with Katmai CTP4 --build #1049, July 2007--, Orcas Beta2)

· 0A Large UDT (Shipped with Katmai CTP5 --build #1185, Nov 2007--, Orcas RTM, Vista SP1)

· 0B SparseColumn (Shipped with Katmai CTP6 --build #1321 Feb 2008-- )

All .Net Framework 3.5 released after Orcas RTM is on 0x0A TDS level. All SQL Server 2008 release after CTP6 (including RC0 and RTM) is on 0x0B TDS level. Connection can only be made between same TDS intermediate version, with one exception:

0B level of SQL Server can also accept 0A level of client. Thus, Orcas RTM can connect to SQL Server 2008 RTM without problem.

With the mechanism in place, it's fairly easy to tell which client can connect to which server. e.g. connection from Orcas RTM to Katmai CTP4 server would fail.

Orcas RTM contains .Net Framework 3.5. Some words worth to be quoted here: "As with .NET Framework 3.0, version 3.5 uses the CLR of version 2.0. In addition, it installs .NET Framework 2.0 SP1 and .Net Framework 3.0 SP1."


As a final note, you should not see the failure when there is no CTP server or beta version of client involved. Connection between RTM of client and server is always guaranteed.

SQL Query that search all column names based on criteria

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.


That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Serkan SONMEZ'
GO




CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

SQL Server vs MySQL vs Oracle vs PostgreSQL

As far as features, performance and power goes, Oracle is leader of the pack clearly. There are more features and tools available for Oracle than for any other DB. However, you will be paying, and paying dearly. You will be able to rest assured that your DB system is the best that money can buy.

Now in most cases, money *is* an object, and as such tradeoffs must be made. In my opinion, PostgreSQL is just below Oracle for powerand features. In fact, there are some things now that PGSQL even do better than Oracle especially in terms of transaction concurrency, PGSQL's MVCC (Multi Version Concurrency Control) model is widely accepted as beign the best conscurrency handling system there is. And its catching up. PGSQL version 8 is due out any day now, there are huge performance increases and features being added in the new version such as native Windows support (it will now install and run on any Win32 system including NT, 2000, XP and 2003), replication in the Slony-I project which brings PGSQL's scaleability to Oracle's calibre.

MySQL is a non-fitter in my opinion. It would be a great product, but since PGSQL's performance has taken great steps in the last few years there is little room for this between PGSQL and SQLite for MySQL to fit in. (SQLite is an ultralightweight, ultrafast SQL database that is embeddable and can be used for small simple projects, and is blisteringly fast in such uses.) MySQL has a *huge* following, and I'm risking a lynching bad mouthing it here, but I personally feel that it is a has-been product with benefits that were once leaders of the pack but have since been overtaken by superior products, namely PostgreSQL and SQLite.

MS SQL is a good product to work with if you're a die hard Windows and Microsoft user and are familiar enough with the other software that goes with it, and are comfortable paying large amounts of money for licences. Personally, with far superior open source options, I feel that MS SQL is a silly choice unless you have your hands tied by other considerations such as licencing constraints or compatibility with other MS products.

Finally, my overall view is that I use SQLite for small projects such as web polls and guestbooks where only the standard SQL commands are sufficient and there is no need for transactions, row/column/table locking, stored procedures and the like. I use PostgreSQL for large projects requiring transactions, stored procedures and with replication I cannot see myself involved in anything too big for PG. In fact I can't think of anything outside genetics and other highly specialised fields where PG would not suffice.

Finally, PG is distributed under the BSD licence, which means there is no chance of licencing compliance issues in your application, and you are guaranteed that there will never be any costs associated with the use of the DB itself.

Table Counts on SQL Server

DECLARE @SQL nvarchar(2000),
@TableName sysname,
@TableCount integer

Declare @TableCounts table(TableName sysname, TableCount integer)

SET NOCOUNT ON

DECLARE TableCursor CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'U' ORDER BY 1

OPEN TableCursor

FETCH NEXT FROM TableCursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL = N'SELECT @TableCountOut = COUNT(*) FROM [' + @TableName + ']'
exec sp_executesql @SQL, N'@TableCountOut integer output', @TableCount output
Insert @TableCounts (TableName, TableCount) Values (@TableName, @TableCount)
FETCH NEXT FROM TableCursor
INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Select * From @TableCounts

Can I find past queries from the sysobjects table?

Questions:
Is there any way to look in logs, or to set up logs, that will track historical queries into the sysobjects table? The intruders seem to always be going after this table first to find our schema - is there any way to tell when a query was directed against that table?

Resolutions:
Profiler would work for future statements only...but it would work great for them. But... you're not going to be able to get at the past select statements

Do not run stored procedure if file does exist using sysobjects

if not exists (SELECT * FROM Warehouse..sysobjects WHERE name = 'sales' and convert(varchar, crdate, 102) = CONVERT(varchar, GETDATE(), 102)) goto ExitHere

DB Status in SQL Server : Indicates the number of records of all databases objects. (Stored procedure, User Table, Indexes)

DECLARE @db_name AS varchar(100)
DECLARE @sql AS nvarchar(1000)
DROP TABLE #DB_Stats
CREATE TABLE #DB_Stats (
dbname varchar(100),
Description varchar(100),
Total_count int)

DECLARE cur_DB_Names CURSOR FOR
SELECT name FROM master..sysdatabases

open cur_DB_Names

Fetch next from cur_DB_Names into @DB_name

WHILE @@fetch_status =0
begin

SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''User_table'', count(*) from [' + @DB_name + ']..sysobjects where xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''',''indexes'', count(i.name) from [' + @DB_name + ']..sysobjects n join [' + @DB_name + ']..sysindexes i on (n.id = i.id) where n.xtype = ''U'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''V'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''V'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''P'',count(*) from [' + @DB_name + ']..sysobjects where xtype = ''P'''
exec sp_executesql @sql
SET @sql= 'insert into #DB_Stats(dbname, description, total_count) select '''+@DB_name + ''' ,''DTS'',COUNT(distinct cast(id as varchar(128))) from [msdb]..sysdtspackages'
exec sp_executesql @sql
Fetch next from cur_DB_Names into @DB_name
end

CLOSE cur_DB_Names
deallocate cur_DB_Names

SELECT * FROM #db_stats

Disable all jobs on a SQL Server

Have you ever needed to quickly disable all jobs on a SQL Server? If you have a lot of jobs to disable, then this stored procedure will help you out. We move our systems to our disaster recovery site twice per year for disaster recovery testing. As part of this process, we need to disable all jobs on our SQL Servers. It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you want to save all of the seconds that you can.

CREATE PROC isp_Disable Jobs
AS
SET NOCOUNT ON
CREATE TABLE #Job_Names
(
Job_Name SYSNAME NOT NULL
)
INSERT INTO #Job_Names

SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name
DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM #Job_Names
SET @job_id = NULL
OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name
END
CLOSE disable_jobs
DEALLOCATE disable_jobs
DROP TABLE #Job_Names
RETURN

If you want to quickly enable all jobs, just change @enabled = 0 to @enabled = 1. Change the stored proc name as well so that it makes sense.

How can I list a stored procedures in SQL Server?

use [AustriaUnity]
GO
select [Object_id],[name],[type_desc] from sys.objects where type='P' and [name] LIKE '%%'

How to Convert Unix Epoch to Datetime in MS SQL

This article will demonstrate an easy way to convert an Integer Field that stores an UNIX Epoch number into a Human readable DATETIME value. This is very important when dealing with data imported from UNIX systems. This article will use a temp table to effectly demonstrate the use of the query funtion.


1. Example using MS SQL Server Management Studio Open a SQL Query window Using MS SQL Server Management Studio or your preferred SQL Query Tool to a database which you have access.

2. Execute the following to setup the table and data required for the deminstration;

* Create a TEMP Table in MS SQL by running the following;
* CREATE TABLE #tmp_epoch_test (unix_epoch_time INT);
* Insert a known value to test retreive later;
* INSERT INTO #tmp_epoch_test VALUES (1232648493);

3. Execute the following to return the DATETIME, human readable format;

* SELECT Dateadd(ss, unix_epoch_time,'19700101')
* FROM #tmp_epoch_test;

Returned Results should be: "2009-01-22 18:21:33.000"

4. Execute the following to remove the TEMP TABLE;

DROP TABLE #tmp_epoch_test

How to enable and disable advance options and Ole Automation Procedures in SQL Server 2005

The system stored procedures in SQL Server are very useful. You can find a system stored procedure to do almost any task. However, in order to use these stored procedures, you need to enable Ole Automation Procedures.

In SQL Server 2005, the option is disabled by default.

You might recieve an error similar to the following

SQL Server blocked access to procedure 'sys.sp_OACreate of component 'Ole Automation Procedures

if you are trying to use the Ole Automation Procedures and the option is turned off.

You will need to enable advance options before enabling Ole Automation Procedures.





1. Use the following code to show the current status of advance option and Ole Automation procedure status.

-- Show Advance Options, 0 = disabled, 1 = enabled
EXEC sp_configure 'show advanced options';
GO

-- Show Ole Automation Procedures, 0 = disabled, 1 = enabled
EXEC sp_configure 'Ole Automation Procedures';
GO

2. Use the following code to enable the advance option and Ole Automation Procedures.

-- Enable the Advance options if they are not already turned on
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

3. Use the following code to disable the advance option and Ole Automation Procedures.

-- Disable the Advance options
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

-- Disable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO

How to find the disk size of a table in Microsoft SQL Server

1.Connect to the SQL server instance. Depending on what I'm doing, I usually use SQL Management Studio, or my own custom script / application.

2.Execute "sp_spaceused". As you can imagine there are a few commands that will allow you to figure out your table size. I prefer "sp_spaceused".

usage: "sp_spaceused 'Name of your table'"
example: "sp_spaceused 'user_accounts'"

Yes, the table name is treated like a varchar / string for this command. Something else to keep in mind is that this particular procedure looks at the [sysindexes] system table. There are instances where this table can become a little out of sync; like right after an index is dropped. To compensate for this, there is an optional parameter that you can add to force the recalculation before execution. Just simply add a "true" to the end.

example: "sp_spaceused 'user_accounts', true"

For reference, the true simply forces the system to recalculate by executing "dbcc updateusage (0)" for all the tables, or "dbcc updateusage ('Name of your table')" for a specific table.

3. Analyze the results. The data returned includes the "name", "rows", "reserved", "data", "index_size", and "unused".

- "name", the name of the table.
- "rows", the number of records in the table.
- "reserved", the amount of space currently allocated for the table.
- "data", the current space used by the table's data.
- "index_size", the current size of the table's indexes.
- "unused", the amount of unused space reserved for the table.

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Check these ;

a) Typo in instance name or wrong instance name. The instance name is not the one you are targeting.
b) Target SQL Server is not running
c) Named Pipe is not enabled on the server. In this case, the SQL server is not listenning on the specific pipe name.

How i can update data in the tables present in Oracle using Sql stored procedure?

In ms sql server, you can create linked servers that point to Oracle databases. check out the sp_addlinkedserver stored procedure, and the OPENQUERY() function in the books online.
You can also insert, update and delete using the openquery function...

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Problem:

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Resolution:

This is because your master is in a different collation than your user database.
Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

How is it possible to make sql server query to ignore locked rows ?

Readpast is the sql server 2005 (new) notation, and has the following difference:

NOLOCK would give you the current value of the row, ignoring locks, possibly giving dirty (uncommitted) data

READPAST would only give you only those rows that are not locked and not dirty (only committed) data