Can we have SQL Server 2000 default Instance and SQL Server 2005 default Instance on the same server?

The default instance could be an installation of

SQL Server 2000,
SQL Server 2005,
SQL Server 2008, or
SQL Server 2008 R2.

Only one installation of SQL Server, regardless of version, can be the default instance at one time

SQL Server 2008 Service Pack 2 released

 

SQL Server 2008 SP 2 released on 29 Sep 2010.

To download the SQL Server 2008 SP2 release please select from the links below:

• SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

• SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

• SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

Oracle Linked Server issue

Today I got the error below on the SQL Server job it runs a SSIS Package.

Error:
Msg 7302, Level 16, State 1, Server USIVA007, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"SYS01_ORACLE01"

Resolution:
  Strange but recreating of the Linked Server connection, resolved the issue.

Reference:
Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

SQL Server Performance Optimization Seminar in Chennai by Sep 24 – 15, 2010

 

This seminar series targeting the audience across the world. It is begin from Chennai, India.

What is unique about the seminar & why i am interested in it

• Presenter Pinal Dave, well known on SQL Server community for his presentation skill
• Solid Quality Mentors differ from usual training / mentoring by giving real time & practical sessions
• It’s worth the time spent since learning the tips may take years
• More of interactive session with presenter rather dull theoretical sessions
• Real time experience on the issues faced by customers
• Unique in design
• Immerse in deep on the concept of SQL Server Performance optimization
• Follow up sessions to keep engaged on what we learned

What can we get out of the two day session;

Day 1:  Sep 24th 2010	Day 2: Sep 25th 2010
Resolution to popular misconception on SQL Server features	Evils of Index and workarounds
The unknown Tricks and Tips of sub queries	The Intelligence of Statistics
The Relation between External and Internal Joins	Query Optimization for unknown values
Triggers and Views	Query Optimization techniques
Question hour	Question hour

 Registration Form

Contacts : For clarifications, please call +91 98202 24429

Further details: http://www.solidq.in/Training/DirectionsSeminar/SQLChennai.aspx

About the Solid Quality Mentor:

• Solid Quality Mentors is the only company in the world with the largest number of the top experts on Microsoft’s Data Platform.
• A Microsoft Gold Certified Partner

Website:  http://www.solidq.com/

About the seminar presenter – Pinal Dave:

Pinal Dave is a Database Architect and Corporate Trainer specializing in SQL Server Query Optimization and Performance Tuning having 7 years of rich, hands-on experience. He has been awarded as the Microsoft Most Valuable Professional (MVP) in SQL Server technology and is also a Microsoft Certified Trainer (MCT)

Website: http://blog.sqlauthority.com/

MOSS – Microsoft Office Sharepoint Server 2007 SQL Server 2005 / 2008 Database Maintenance

 

There is a whitepaper in detail SQL Server 2005 /2008 Maintenance for MOSS ( Sharepoint ).

Database Maintenance for SharePoint

SQL Server 2008 R2 Update for Developers Training Kit

 

The SQL Server 2008 R2 Update for Developers Training Kit  is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Download it from here, it sizes 216.5 MB.

It contains PPT Slides, Video, Demo and Hand on Lab

Come and Play on SQL Server 2008 R2

 

Register or login to experience the SQL Server 2008 R2 virtual lab.

The environment also host the SharePoint 2010 and MS Office 2010.

http://www.previewsqlserver.com/index.htm

Me in SQL Data Camp in Chennai

 

I attended the SQL Data Camp in Chennai and received a RedGate book as a token of one the most active participant of the event.

Attending SQL Data Camp @ Chennai on 17th July 2010

 

I will be attending the SQL Data Camp, in Chennai on 17th July 2010

How to find who dropped the database? Is that logged on SQL Server?

 

Are you one looking to find answer for questions like below.,

· How to find who dropped a database?

· How to find who created the database?

· How to find who altered the database?

· How to find who altered the database configurations?

· How to find who dropped the schema?

· How to find who altered the schema?

· How to find who altered the server configuration?

· How to find who modified the login?

· How to find who modified the table?

If it is then,  are you wondering is that possible on SQL Server?

Yes, SQL Server can beginning from version SQL Server 2005 onwards.

That is where SQL Server Default trace come into picture.

What is SQL Server Default Trace?

Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

To get the default trace options

SELECT * FROM ::fn_trace_getinfo(default)

To know what is audited on default trace

SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description

  FROM ::fn_trace_geteventinfo(1) Trc

    JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

    JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id

Example:

Below is the screenshot  “Schema Changes History” of Standard Reports at Server Level.

See that “it shows the event of Drop and Alter of the database

 

The same can be queried from SQL Server Default Trace like;

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

Usage
Default trace can be queried to find answers on

1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
3. To find Login creation , modifications and failures
4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
5. Database file growth or shrinkage
6. DBCC operations

 

Points to ponder

1. Default trace is a Server Side trace and it is enable by default.
2. Default trace properties can not be modified, other than enabling and disabling of this trace.
3. Default trace by default enabled and stored on the “Log” folder
   
   
4. Default trace file will be created newly on start of the SQL Server service.
5. A single trace file can store  up to 20 MB, when it is full new file will be created.
6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.

Using Spatial Data type to draw circle in SQL Server 2008

 

--- Draw Circle in SQL Server 2008
declare @g Geometry
set @g = 'Point(10 5)'
select @g.STBuffer(5)

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

 

Error

• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.

Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

SQL2000 SP4 Installation Error sp_vupgrade_replication(1)

 

SQL Server 2005 Patching fails CREATE DATABASE failed.

Error:

Error Number : 29537

Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.

RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .

2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

1. Reference: a recently applied update, kb955706, failed to install

Delete files using DOS command FORFILES

 

To delete te

--Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.

 

EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '

/*

FORFILES Parameters

Parameter Name Description

/p Path

/m Search Mask (default is *.*)

/s Subdirectories will be searched recursively if this parameter is included

/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"

/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.

Variables

Variable Name Description

@FILE File name

@FNAME File name without extension

@EXT File extension

@PATH Full path of the file

@RELPATH Relative path of the file

@ISDIR Evaluates as TRUE if the file type is a directory

@FSIZE File size in bytes

@FDATE Last modified date stamp on the file

@FTIME Last modified timestamp on the file

*/

Privileges needed to run SQL Server Profiler without sysadmin privileges

 

In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,

GRANT ALTER TRACE to [Login]

BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler

Query to find when the table last accessed on SQL Server 2005 and above

 

------ Query to find when the User Table Last accessed ---------

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
    MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

------ Query to find when the Database Last accessed ---------

SELECT name, last_access =(
                select X1= max(bb.xx)
                from ( select xx =
                        max(last_user_seek)
                        where max(last_user_seek)is not null
                        union all
                        select xx = max(last_user_scan)
                        where max(last_user_scan)is not null
                        union all
                        select xx = max(last_user_lookup)
                        where max(last_user_lookup) is not null
                        union all
                        select xx =max(last_user_update)
                        where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name

Deny specific user from Remote Login to SQL Server

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger

 

CREATE TRIGGER Deny_Remote_Login_Login1_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Login1' AND (SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'Login1' --- Checking the given login and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely and Session_ID = @@SPID ) BEGIN PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally' ROLLBACK; END END;

SQL Server 2005 Uninstall Error: The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error: 2147749896 (0x80041008).

 

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).

Resolution: Copy and run the DOS batch file below

@echo on cd /d c:\temp if not exist %windir%\system32\wbem goto TryInstall cd /d %windir%\system32\wbem net stop winmgmt winmgmt /kill if exist Rep_bak rd Rep_bak /s /q rename Repository Rep_bak for %%i in (*.dll) do RegSvr32 -s %%i for %%i in (*.exe) do call :FixSrv %%i for %%i in (*.mof,*.mfl) do Mofcomp %%i net start winmgmt goto End :FixSrv if /I (%1) == (wbemcntl.exe) goto SkipSrv if /I (%1) == (wbemtest.exe) goto SkipSrv if /I (%1) == (mofcomp.exe) goto SkipSrv %1 /RegServer :SkipSrv goto End :TryInstall if not exist wmicore.exe goto End wmicore /s net start winmgmt :End

Thanks to: http://social.msdn.microsoft.com/Forums/en/Vsexpressinstall/thread/632ca405-1c38-405b-9ed3-01785c9f99d1

How to remove the subscription from Publication in SQL Server Replication

 

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

How to apply Service pack on SQL Server 2005 Cluster

 

1. Backup of all the System + User Database

2.Windows Login account with "Local Administrator" privileges on all the nodes involved

3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
   I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

4. Cross check if the cluster failover on the nodes involved successfully.

5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

6. Disable if any DDL Triggers

7. Verify if following services online on the nodes involved.

• Task Scheduler
• Com+ System Application Service
• Cryptographic Services
• Remote Registry Service 

8. Pause Database Mirroring

Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.

Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.

Reference: 

How to install SQL Server 2005 SP3

SQL Server 2005 SP3 Release Notes

SQL Server 2008 failover cluster rolling patch and service pack process