Create view and force source tables to stay valid

In this example I have a table called dbo.customers with (FN,LN, address, Phone) columns

--creates the view
Create view vw_customers

as
--only two columns are selected (FN,LN)
Select FN, LN
from customers

with Schemabinding

So. If you attempt to drop the the customers table , SQL will return " can not drop table with reference."

Report server web page is blank in Windows 2008 Server

localhost/reports is blank? You can not configure any new reports right?
are you using windows 2008 server?

If you answer yes to these questions than proceed to the next part below.

Well, right click on IE and run as administrator
or remove UAC in windows 2008.

Moving? Tempdb is out of space

Moving Tempdb

In order to move the tempdb database, open SSMS and run the following query:

use master
go
Alter database tempdb modify file
(name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file
(name = templog, filename = 'E:\Sqldata\templog.ldf')
Go

This command assumes that you are
moving the database files to E:\Sqldata directory

No mapping between account names and security IDs was done

SQL Server Setup has encountered the following error:
"No mapping between account names and security IDs was done."
"Error code 0x84BB0001."

1. go to services not ms sql config manager
2. change the service(s) to run under local services
3. reboot

you probably demoted domain controller or changed accounts somehow

Can not start MS SQL Server 2005 because ERRORLOG full?

Things to do:

1. Check the MS Sql server 2005 configuration tool and make sure MS SQL server is started
   
2. restart the MS Sql Server service ( this will cycle ERRORLOG to errorlog.1)
   
3. launch SSMS and exec sp_cycle_ERRORLOG (use master)
   

P.S check the ERRORLOG it may be a GIG is size (Sql won't start).

Transaction logs growing out of control?

login to SSMS and click new query :
backup log adventureworks with truncate_only

next, right click database adventureworks>tasks> shrink> files>file type > logs> click ok
thats it --only ms SQL server 2005

MS SQL 2008 R2 useful features

Yes ,SQL server 2008 has a new version again. Its R2 folks. Im going to give you some basic and useful ways to use the new features

• Policy based management (PBM) --control database,table, trigger and object names
• Resource governer --take control of your memory and processor again
• Back up compression ---Dont need more SAN LUNS
• data file encrytion --- someone stole your mdf files..no worries
• filestream--- replace BLOB with this
• New report builder 3.0 --more charts and datasets added
• New database mirroring recovery options
• totally rebuild database replication engine
• New sharepoint integration..

How to change the default port in MS SQL server ---2005 2008

By default MS SQL server 2005 uses dynamic ports. You have to change the port to static if needed.

Go to SQL server configuration
>Protocols for MSSQL
>click TCPIP
>IP Address
>remove dynamic port
>TCP port
change to your specific port

Am I seeing double --Find duplicates in a sql table

Find duplicates in your table.
This is an example for a database called instructapp and table called teacherInfo and a column called SSN

USE instructapp
SELECT SSN, COUNT(*) TotalCount
FROM teacherInfo
GROUP BY SSN
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Upgrade service pack on a MS SQL 2000 cluster

Steps to upgrading a MS SQL 2000 cluster is backing up the system databases
--backup database master to disk = ""

1. download the service pack from Microsoft.
2. Run the service pack against the active node ( do not attempt to run on passive node)
   
3. find the password for your cluster admin service ( your going to need it)
   

Take advantage of over 4 GB's memory in MS Sql server 2000

So you are having performance issues in MS SQL 2000? SQL server is sluggish and you have 6 GB's or more of memory.What could be the problem? Your tables all have indexes and databases are not set to auto shrink.

This sounds like you need AWE enabled. AWE
Launch Enterprise Administrator and run. this example is for 6 GB's

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO

this command works for 32 bit only it does not work for 64 bit

Recovering from corrupt master (system) in MS SQL 2000!!!

SQL server 2000 wont start? This is not for SQL 2005. Check the logs and there is a corrupt master database right?
Well, get ready for the fun because it all starts here.

• You will need a utility called rebuildm.exe ( its usually in your ms SQL 2000 disk)
• This will install a new master, model, tempdb, msdb
• start the SQL service in single user mode
• Launch Enterprise Admin in single user mode to recover master database.
  
• All the databases, tables, views, users and objects should be back in place
  

Learn to kill blocks in MS SQL Server 2000

Hey DBA's let stop thinking that tables are corrupt. Let start to kill the blocks or locks associated with that table
How to:
Open Enterprise Administrator>
Local instance>
Management>
Current activity>
Locks/Process ID>
Find the process>
kill process>
Done.