SELECT*

Timeout error occures when Comparing Large Databases with Databse Project while working with Team Foundation System For Database Professionals .If you receive a timeout error when you compare the schemas of large databases, you might need to increase the timeout value. This option is not exposed through the user interface. You must modify the QueryTimeoutSeconds value that is stored in the registry under HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\DBPro\Databaseand specified in seconds. By default, the timeout is 60 seconds. Source:http://msdn.microsoft.com/en-us/library/aa833294(VS.80).aspx

--Restore filelistonly gives infromation of .mdf andl .ldf logical filesnames. RESTORE FILELISTONLY FROM DISK = 'D:\DatabaseName.BAK' GO RESTORE DATABASE DatabaseName FROM DISK = 'D:\DatabaseName.BAK' WITH MOVE 'Database_Data' TO 'C:\DatabaseName_Data.mdf', MOVE 'DatabaseName_Log' TO 'E:\DatabaseName_Log.ldf' Some times we may get this while running the above script Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:0) in database "DatabaseName" as read from the backup set. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. The reason for this is database is not completely backed up.For that we have to check the status of .BAK File .So First run these commands. RESTORE HEADERONLY FROM DISK = 'GivePathHere\Name.BAK' RESTORE FILELISTONLY FROM DISK = 'GivePathHere\Name.BAK' The below query will tell us who is restoring database, when the database restoration p

Some times we can create table without mentioning column data type straightly. For eg: o CREATE TABLE [dbo].[Customer] ( [CustomerID] [int] NOT NULL IDENTITY(1, 1), [CustomerName] [varchar] (50) NOT NULL, [CreditLine] [smallmoney] NULL, [OutStandingBalance] [smallmoney] NULL, [AvailableCredit] AS ([CreditLine]-[OutStandingBalance]), [CreationDate] [datetime] NOT NULL ) ON [PRIMARY] Here if we observe AvailableCredit datatype is not mentioned. But in the alias the result it takes as smallmoney.

As a DBA sometimes we should able to trace out the activities of users whoever are connecting to Database Servers. If any developer could make changes to objects like He can create Objects like (Stored Procedures,Views,Triggers) as well as he can drop procedures, views and triggers. As a DBA We should suppose to track all these DML,DDL Events. And we can find out those activities using these. First Step: Check whether your server properties. Select * from sys.configurations where configuration_id = 1568 Second Step: If we run this below we can get the particular trace or all existing traces. SELECT * FROM ::FN_TRACE_GETINFO(0) GO select * from ::fn_trace_getinfo(null) go select * from ::fn_trace_getinfo(DEFAULT) In the result we can find the path of the trace file. Third Step: We can run this query and find the trace events. This sys.trace_events catalogue view contains a list of all sql server events. These trace events do not change for a given permission of the Mic

This error will come if we are trying write any queries(Distributed Queries) using Linked Servers. Linked server will create such a way using Security Context (RightClick on Linked Servers -> Properties->Be made using this Security Context).If this is the scenario If we run query we get the above error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. But you can access the same server Manually with Management Studio. But if you write query using Linked Server You may get this Error. So Ensure that after creating Linkeserver check this Linked server by using Query. Eg:SELECT * FROM LinkedServerName.master.dbo.sysobjects To avoid this error change security context one way is" Be made using this Security Context " TO " Be made without using a Security Context " And Sometimes you get the permission from that(remote system)sysadmin.

Some of the Security Issues at Sqlserver 2005: I have connected to another sqlserver2005 (Eg: sysravi\sqlexpress) with my Windows credentials (sysramesh\sqlexpress)(username\instance name) through Windows Authentication. Not with the SQLServer Authentication. In that server I have tried to create linked server and also tried to Create the Database. And no issue with the Linked server script But I am getting the error saying that : Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36 Solution: I made changes in that server (sysravi\sqlexpress) Security ->Login->BUILTIN\Administrators So in the BUILTIN\Administrators Properties ->Server Roles-> Click on SysAdmin Check Box. So you will get the privileges of SysAdmin. Now you can able to create the Linked Server as well as Databases.

Contains the Categories used by SQL Server Management studio to organize jobs,alerts,and operators this table is stored in the msdb Database USE msdb go SELECT Category_ID,Category_Class,CategoryClassDescription=CASE WHEN Category_Class=1 then 'job' WHEN Category_Class=2 then'Alert' WHEN Category_Class=3 THEN 'Operator'END, Category_Type,CategoryTypeDescription=CASE WHEN Category_Type=1 THEN 'Local' WHEN Category_Type=2 THEN 'MultiServer' WHEN Category_Type=3 THEN 'None'END,[Name] FROM SYSCATEGORIES ORDER BY Category_id