RESTORE DATABASE using command

I found this error message when i restore the database using the GUI and it appear this error message "Error 3154: The backup set holds a backup of a database other than the existing database"
It because of trying to restore database on an existing active database.

Solution:
RESTORE DATABASE DatabaseName
FROM DISK = 'C:\myDatabase.bak'
WITH REPLACE

Use WITH REPLACE when using RESTORE command when u saw above error message "Error 3154: The backup set holds a backup of a database other than the existing database"

Tested in MSSQL 2005

SQL @@ROWCOUNT

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated.

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Example
USE DB2008;
GO
UPDATE User
SET JobTitle = 'Manager'
WHERE UserID = 'u10021'
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

SQL Injection

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

Example
UserID = Request.form ("userid");
var sql = "select * from UserTable where ID= '" + UserID + "'";

The user is prompted to enter the User ID. If she or he enters "jack", the query assembled by the script looks similar to the following:

select * from UserTable where ID = 'jack'

However, assume that the user enters the following:

jack; drop table UserTable--

In this case, the following query is assembled by the script:

select * from UserTable where ID = 'jack''; drop table UserTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in UserTable where ID is jack. Then, SQL Server will drop UserTable.

Input character	Meaning in Transact-SQL
;	Query delimiter.
'	Character data string delimiter.
--	Comment delimiter.
/* ... */	Comment delimiters. Text between /* and */ is not evaluated by the server.
xp_	Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Select Statement for Different Database

Database SQL Syntax
- Different databases using different sql statement

DB2
select * from table fetch first 10 rows only

Informix
select first 10 * from table

Microsoft SQL Server and Access
select top 10 * from table

MySQL and PostgreSQL
select * from table limit 10

Oracle 8i
select * from (select * from table) where rownum <= 10

Linked Servers

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

1. Remote server access.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.

When setting up a linked server, register the connection information and data source information with SQL Server. After registration is success, that data source can always be referred to with a single logical name.

You can manage a linked server definition with stored procedures or through SQL Server Enterprise Manager:

With stored procedures:

- Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.

- Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.

With SQL Server Enterprise Manager:

- Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under the Security folder). Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.

- Edit a linked server definition by right-clicking the linked server and clicking Properties.

- Delete a linked server definition by right-clicking the linked server and clicking Delete.

How Long Stored Procedure stay in Sql Server 2000 cache?

SQL Server 2000

once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To Monitor the cahce:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp

awe enabled Option - MsSql 2005

In Windows Server 2003, you able change the Address Windowing Extensions (AWE) API to provide access to physical memory in excess of the limits set on configured virtual memory. The specific amount of memory you can use depends on hardware configuration and operating system support.

• Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (GB).
• Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
• Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

Extra Note

• You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server.
• you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

COMPUTE (Transact-SQL) - SQL Server 2005

Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set.

USE Database;
GO
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE ID = 1
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);

MERGE statement - SQL Server 2008 New Feature

The idea behind the MERGE statement is that the developer can construct TSQL data-manipulation language (DML) statements in which INSERT, UPDATE, or DELETE can occur in the same statement, based on different search conditions. I think this idea is very cool. The ability to complete multiple statements within one statement could potentially lead to less coding and increased performance.
In addition to this statement, another great feature has been added to the INSERT statement. In SQL Server 2008, the developer can issue multiple rows to be inserted without using a SELECT statement as the INSERT statement source. Instead, the VALUE clause of the INSERT statement can be used to specify sets of values separated by parentheses and commas.

sp_helpfile

sp_helpfile returns the physical names and attributes of the current database.

Attributes that returns are name, fileid, filename, filegroup, size, maxsize, growth and usage.

- it for ms Sql Server

Database Engine Stored Procedures

sp_add_data_file_recover_suspect_db	sp_droptype
sp_add_log_file_recover_suspect_db	sp_executesql
sp_addextendedproc	sp_getapplock
sp_addextendedproperty	sp_getbindtoken
sp_addmessage	sp_help
sp_addtype	sp_helpconstraint
sp_addumpdevice	sp_helpdb
sp_altermessage	sp_helpdevice
sp_attach_db	sp_helpextendedproc
sp_attach_single_file_db	sp_helpfile
sp_autostats	sp_helpfilegroup
sp_bindefault	sp_helpindex
sp_bindrule	sp_helplanguage
sp_bindsession	sp_helpserver
sp_certify_removable	sp_helpsort
sp_configure	sp_helpstats
sp_control_plan_guide	sp_helptext
sp_create_plan_guide	sp_helptrigger
sp_create_plan_guide_from_handle	sp_indexoption
sp_create_removable	sp_invalidate_textptr
sp_createstats	sp_lock
sp_cycle_errorlog	sp_monitor
sp_datatype_info	sp_procoption
sp_dbcmptlevel	sp_recompile
sp_dbmmonitoraddmonitoring	sp_refreshview
sp_dbmmonitorchangealert	sp_releaseapplock
sp_dbmmonitorchangemonitoring	sp_rename
sp_dbmmonitordropalert	sp_renamedb
sp_dbmmonitordropmonitoring	sp_resetstatus
sp_dbmmonitorhelpalert	sp_serveroption
sp_dbmmonitorhelpmonitoring	sp_setnetname
sp_dbmmonitorresults	sp_settriggerorder
sp_dboption	sp_spaceused
sp_dbremove	sp_tableoption
sp_delete_backuphistory	sp_unbindefault
sp_depends	sp_unbindrule
sp_detach_db	sp_updateextendedproperty
sp_dropdevice	sp_updatestats
sp_dropextendedproc	sp_validname
sp_dropextendedproperty	sp_who
sp_dropmessage

Clear Transaction Log in SQL Server 2005 database

1. Backup DB
2. Detach DB
3. Rename Log file
4. Attach DB
5. New log file will be recreated
6. Delete Renamed Log file.

Above just is 1 of the way to clear the transaction log file

SQL Combine 2 Different Table

This SQL is use to combine 2 different table together for other purpose.
SELECT ISNULL(a.Col1,b.Cola) , ISNULL(a.Col2,b.Colb)
FROM Table1 a
FULL JOIN Table2 b
ON a.Col1 = b.Cola

Here is 2 Different Table,
Table Name : Salary

Name	Salary
emil	1000
rayden	2000

Table Name: Flight

FligtTicket	Price
Kuala Lumpur	265
Bangkok	878

Below Sql is use to combine above 2 Tables Become below 1 Table
SELECT ISNULL(a.Name,b.FlightTicket) AS Col1, ISNULL(a.Salary,b.Price) AS Col2 FROM Salary a
FULL JOIN Flight b ON a.Name = b.FlightTicket

Col1	Col2
emil	1000
rayden	2000
Kuala Lumpur	265
Bangkok	878

DBCC FREEPROCCACHE

DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache can causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC SQLPERF(logspace)

DBCC SQLPERF(logspace) is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed (as a percentage of total log file size). A drawback is the fact that the results are an aggregate for the database.

logspace can be 1 of the value below:
UMSStats - SQL thread management
WaitStats - resources, wait types
IOStats - outstanding reads & writes (note: always zeros)
RAStats - read ahead activity (note: always returns zeros)
Threads - I/O / CPU / memory usage per thread
SpinLockStats - statistics on spinlocks
UMSSpinStats - statistics on UMS
NetStats - ODS statistics
LRUStats - LRU-MRU chain statistics (Note: free page scan always zero)

DBCC SHOWFILESTATS WITH NO_INFOMSGS

This DBCC command provides us the data files for the current database that you select. DBCC SHOWFILESTATS or DBCC SHOWFILESTATS WITH NO_INFOMSGS is the command to display the current database File Name, Database Name, Total Extend, Used Extent, Field ID and File Group.

Get the ID of a Newly Inserted Row Using SCOPE_IDENTITY()

SCOPE_IDENTITY() command will give you the last identity value that was generated in the current scope.

@@Identity will return the last identity value that was generated in the current session but in any scope.

If you database got trigger to insert a new row, @@Identity will give you the id that generated by trigger instead of ID of the last row you inserted.

In order to get the ID of the row that you inserted, which is just what SCOPE_IDENTITY() will give you.

Below is the SQL to get the last inserted ID in the current scope that need to execute together with the commands.

string sqlString = "INSERT INTO Table(Col1, Col2) VALUES (@Col1, @Col2); SELECT SCOPE_IDENTITY () As NewID";
SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
objCmd.Connection.Open();
objCmd.Parameters.Add(new SqlParameter("@Col1", Col1Value));
objCmd.Parameters.Add(new SqlParameter("@Col2", Col2Value);

SqlDataReader dataReader = objCmd.ExecuteReader();

if (dataReader.HasRows)
{
dataReader.Read();
newRowID = Convert.ToInt32(dataReader["NewID"]);
}

dataReader.Close();

SQL Update Multiple Columns

Below Sql use to update multiple column for a table in 1 shot

*****************

UPDATE Table1
SET (Col1, Col2, Col3, Col4) =
(SELECT Col1a, Col2a, Col3a, Col4a FROM Table2 WHERE Col5a=Table1.Col5)
WHERE Col5 IN (SELECT Col5a FROM Table2 )

*****************

Identity column not getting sequential values

Identity values are not sequential. Even if you do not delete rows, you can end up with missing identity values. If a transaction inserts a new record to the table and ROLLS BACK, you can end up with a missing identity value.

Connect to Sql Server 2005 Express for Window SharePoint Service

If you wanna to connect to the sql server 2005 express after u install window share point to your window server 2003 and when u try to connect to the ServerName\Microsoft##SSEE, it return you error.
Actualy you can't connect to WSS database remotely because network connectivity is disabled by default. ( or without SQL Server Agent Services not installed)

You can use below as ur server name like the image below, it will let you to connect to your share point database

\\.\pipe\mssql$microsoft##ssee\sql\query