The operation cannot be performed on a database with database snapshots or active DBCC replicas

 

When trying to restore the database with snapshots got the following error SQL message

SQL Server T-SQL Command

restore database PRD_Accounts from disk ='H:\PRD_Accounts_backup_daily.bak' with replace, stats

Error:

Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Resolution

Since we cannot restore a database if we have any snapshots.
Please drop all snapshots and try database restore again.

1. To check the database snapshot and it’s source database

select database_id, name,source_database_id,Source_DBName=DB_NAME(source_database_id) from master.sys.databases where source_database_id IS NOT NULL

2. Drop the Snapshot database

DROP DATABASE Accounts_Rpt

3. Create script of snapshot database, before dropping it. If at all we need to re-establish the snapshot after restore we can use it

USE master GO /****** Object:  Database [Benefits_Rpt_sol48u]    Script Date: 12/03/2012 00:01:02 ******/ CREATE DATABASE [Accounts_Rpt] ON ( NAME = N'PRD_Accounts', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss1' ), ( NAME = N'indexdat1', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss2' ) AS SNAPSHOT OF [PRD_Accounts] GO

Error on executing sp_cycle_agent_errorlog Msg 22022, Level 16, State 1, Line 0

sp_cycle_agent_errorlog

Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to SQLAgent.1; SQLAgent.1 becomes SQLAgent.2, SQLAgent.2 becomes SQLAgent.3, and so on. sp_cycle_agent_errorlog enables you to cycle the error log files without stopping and starting the server.

This stored procedure must be run from the msdb database.

Error on executing of sp_cycle_agent_errorlog

 

USE msdb

go

EXEC sp_cycle_agent_errorlog

GO

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Access is denied.

Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.

SQL Mail: The mail could not be sent to the recipients because of the mail server failure.

SQL Mail Sent fail with errors below;

Error from the SQL Mail log:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-07T22:09:50). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 205.191.22.44:25). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:13:58). Exception Message: Could not connect to mail server. (No such host is known). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:12:44). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-24T19:55:11). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 16.236.32.51:25). )

Resolution:

From the error message it is clear that it is a communication issue between SQL Server host and the Mail Exchange Server.

Problem was related to Exchange Server not allowing relaying from SQL Server.

Please engage Network Administrator to add SQL Server host IP Address to allow to send emails taking below steps, like

On Mail server > Exchange Server Manager > SMTP Connector Properties
> Access Properties > Access Tab > Relay button > Add  IP Address of SQL Server

Also cross check if the Anti Virus (AV) program running on the SQL Server host. AV blocks services from sending on TCP-25. Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Query to find the Linked Servers with given remote login

 

It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.

SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date FROM SYS.SERVERS S INNER JOIN SYS.LINKED_LOGINS L ON  s.server_id = L.server_id WHERE S.server_id > 0 AND S.product ='SQL Server' AND L.remote_name ='sa'

Returning the parameters for a specified stored procedure or function

 

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name     ,o.name AS object_name     ,o.type_desc     ,p.parameter_id     ,p.name AS parameter_name     ,TYPE_NAME(p.user_type_id) AS parameter_type     ,p.max_length     ,p.precision     ,p.scale     ,p.is_output FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id = OBJECT_ID('<schema_name.object_name>') ORDER BY schema_name, o.object_name, p.parameter_id; GO

 

Reference: http://msdn.microsoft.com/en-us/library/ms190324.aspx

List database objects modified in the last ‘N’ days

 

Before you run the following query, replace <database_name> and <n_days> with valid values.

 

USE <database_name>; GO SELECT name AS object_name   ,SCHEMA_NAME(schema_id) AS schema_name   ,type_desc   ,create_date   ,modify_date FROM sys.objects WHERE modify_date > GETDATE() - <n_days> ORDER BY modify_date; GO

 

Source: http://msdn.microsoft.com/en-us/library/ms190324.aspx

Script to check SQL Server Linked Server Status

 

It leverages on the system stored procedure sys.sp_testlinkedserver

SET NOCOUNT ON; DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300) IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN DECLARE srvname INSENSITIVE CURSOR FOR         (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY OPEN srvname     FETCH NEXT FROM srvname INTO @LinkName WHILE @@FETCH_STATUS = 0 BEGIN     BEGIN TRY         EXEC @retval = sys.sp_testlinkedserver @LinkName     END TRY         BEGIN CATCH         IF @LinkName IS NULL RETURN         SET @retval = SIGN(@@ERROR)         IF @retval <> 0         BEGIN           SET @msg = 'Unable to connect to the Linked server : ' + @LinkName           RAISERROR (@msg, 16, 2 )         END     END CATCH     FETCH NEXT FROM srvname INTO @LinkName     END        CLOSE srvname DEALLOCATE srvname

Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

 

SSIS Package – SQL Agent Job Error:

Executed as user: PRODUCTION\SQLSrvr. ...te Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:08:22 PM Error: 2012-07-25 22:08:23.31 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-07-25 22:08:23.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:08:22 PM Finished: 10:. The step succeeded.

Solution:

If you are using SSIS 2005 - you need to save the packages as Do Not save sensitive - which you are doing.

When you import the package into the SSIS system you need to make sure you alter the package protection level there to do not save sensitive too. In SQL Server 2008 and above this is the default.

Reference: http://support.microsoft.com/kb/918760

How to find on which node SQL Server instance was running before restart?

 

In situation, where SQL Server instance running on a node is restarted usually after Windows Operating System patching or for some other reason, there may be need to find to which node the SQL Server instance was running on before.

Here is the solution for this;

By default whenever SQL Server instance restarted on Clustered Environment, on SQL Server Error Log it will post the message below;

The NETBIOS name of the local node that is running the server is 'ClusterNode1'.

So, making use of the xp_ReadErrorLog we can query the ErrorLog and find the details.

-- To know the list available Error log files exec xp_enumerrorlogs 1 -- Search the available error log files exec xp_ReadErrorLog 0, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 1, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 2, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 3, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 4, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 5, 1 , 'NETBIOS name of the local node'

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

 

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

A linked server definition specifies the following objects:

· An OLE DB provider : is a DLL that manages and interacts with a specific data source

· An OLE DB data source : identifies the specific database that can be accessed through OLE DB

Basics of Linked Server Configuration:

Advantages of Linked Server:

· The ability to access data from outside of SQL Server.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000)) ) (CONNECT_DATA = (SERVICE_NAME = stg01.siva) )

Validate tnsnames.ora entry

On Command Prompt Tnsping STG21.SIVA

Notes:

Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA

Open up ODBC

Select System tab

Click Add

Select Microsoft ODBC for Oracle

Click Finish

Click OK, OK

Connect to SQL Server Management Studio

Right click select new Linked Server

Click OK

Test Connection

Right click select Test connection

Validation:

Just run the sample query

exec ('select user, sysdate from dual') at STG21_SQLSVR54

Output look like;

USER SYSDATE SQLSVR07 2012-09-25 01:37:02.000

MS Visual Studio 2010 Source Control Explorer shortcut

 

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe"/Command View.TfsSourceControlExplorer

Free ebooks: Great content from Microsoft Press that won’t cost you a penny

 

Please try downloading SQL Server 2012, 2008 R2 and more from the link below

http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx

Start multiple Putty sessions using DOS Batch file

 

There is a requirement for me open up multiple sessions of same HP Unix Host server. I just automate this using simple DOS Batch file below;

@echo off set uname=siva set pwd=#`F$(*&!-=~ start /max  C:\Windows\System32\putty.exe -load "D1- Session1" -l %uname% -pw %pwd%   start /max  C:\Windows\System32\putty.exe -load "D1- Session2" -l %uname% -pw %pwd% start /max  C:\Windows\System32\putty.exe -load "D1- Session3" -l %uname% -pw %pwd%   start /max  C:\Windows\System32\putty.exe -load "D1- Session4" -l %uname% -pw %pwd% start /max C:\Windows\System32\putty.exe –load  "D1–Session5" -l %uname% -pw %pwd% @echo on

Outlook Public folder: How to set up the AutoForward Rule on Public Folder

 

You must have Owner (Owner permission: With Owner permission you can create, read, modify, and delete all items and files and create subfolders. As folder owner, you can change the permission level that others have for the folder. This permission does not apply to delegates.) permission for a public folder to create or modify rules (rule: One or more automatic actions taken on e-mail messages and meeting requests that meet certain conditions, along with any exceptions to those conditions. Rules are also referred to as filters.) for it.

1. In the Folder List (Folder List: Displays the folders available in your mailbox. To view subfolders, click the plus sign (+) next to the folder. If the Folder List is not visible, on the Go menu, click Folder List.), in the Navigation Pane (Navigation Pane: The column on the left side of the Outlook window that includes panes such as Shortcuts or Mail and the shortcuts or folders within each pane. Click a folder to show the items in the folder.), right-click the public folder you want to create or modify rules for, and then click Properties on the shortcut menu.
2. Click the Administration tab.
3. Click Folder Assistant.
   Do either of the following:
4. To create a rule, click Add Rule.
5. To modify an existing rule, click a rule in the list, and then click Edit Rule.
   Enter the conditions that the item must meet for the action to occur. For more conditions, click Advanced.
   Under Perform these actions, select the options you want.
   

Quick way of getting Date and Time appended on filename in DOS Batch

 

echo off set filename=F:\Sivaprasad\Error%time:~0,2%%time:~3,2%%time:~6,2%_%date:~-10,2%%date:~-7,2%%date:~-4,4%.txt echo  %filename%

DOS Batch file To search string in given file

 

Requirement:

To Search the given files under current directory for given strings. If anything found to be written to a file.

Sample Screen:

 

Main Script: CHK_Error.bat

@echo off set fname= set filename= set yyyymmdd= set hhmm= IF %1. NEQ . (set fname="C:\Accounts\CalWIN\Build\SQLServer\DOS_Batch\%1*.lst") ELSE ( GOTO _Usage ) for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%i%%j for /F "tokens=1-2 delims=: " %%l in ('time /t') do set hhmm=%%l%%m set filename=Error_%yyyymmdd%%hhmm%.txt echo "Error Log file name is : " %filename% echo "files to be searched : " %fname% findstr /i /n /g:search.txt  %fname% > %filename% if %errorlevel% == 0 (GOTO :_Error_Found ) else  ( Echo  **  No Errors found    ** Goto :END ) :_Error_Found notepad %filename% Goto :END :_Usage echo " Usage is chkerrors.bat <filestring> :END @echo on

Search.txt

msg error severity warning fail fatal

Input Files: file1.lst

dgadagdagad abacdefghijklmnopqrstuvwxyzagadgda gagad st messgae g adgadg agadgadgadadadsflmsvbkobsagad dgadg ad ddad dgadgadg error 121 dgad gdagad gadagad gagad msg 128, 9341353513

Input Files: file2.lst

test messgae g sagad dgadg ad ddad dgadgadg error 121 dgad gdagad gadagad gagad msg 128, 9341353513

ZoomIt screen zoom and annotation tool for presentation

 

ZoomIt is screen zoom and annotation tool for technical presentations that include application demonstrations. ZoomIt runs unobtrusively in the tray and activates with customizable hotkeys to zoom in on an area of the screen, move around while zoomed, and draw on the zoomed image.

Enjoy using this tool on your presentation. It can be downloaded from http://technet.microsoft.com/en-us/sysinternals/bb897434

Database Development Lifecycle

 

SQL Server Database Development with

Visual Studio 2010

1. Establish Project Environment

2. Perform Isolated, iterative development

3. Build the Project

4. Deploy from Project environment

Establish the Project Environment

The following illustration shows how the team member who performs the role of the database administrator (DBA) establishes the project environment.

The DBA starts by creating a database project and importing the database schema from the production database. The DBA then can create a data generation plan to create test data for the isolated development environments. Finally, the DBA checks the database project into version control, which makes it available to the team

Perform Isolated, iterative Development

Each database professional synchronizes their development environment to version control. They can check out files as they are changed and develop and test those changes in isolation. Changes made to their copy of the database project are deployed only to their isolated development environment. After a team member generates realistic test data and runs unit tests against a private copy of the database, the team member checks the changes into version control. Other team members obtain tested changes from version control.

Generate Daily Builds of the Project

how the build cycle works for database projects

The daily build is generated by synchronizing to the most recent tested version of the database project in the version control system. That build can be deployed to a test database where unit tests are run against generated test data.

Deployment from Project Environment

how the DBA can deploy a version of the database project into production

When it is time to deploy a version of the database to production, the DBA synchronizes to a label from the version control system. The DBA retrieves matching files for the database project, related scripts, and tests. Next, the DBA builds the .dbschema file. From the .dbschema file, the DBA generates the deployment script, makes manual modifications as needed, and does a test deployment to the staging server. The DBA iterates this process until the script is ready to be deployed to the production server. If the DBA made changes to the schema or to the pre-deployment or post-deployment scripts, those changes will be checked back in to version control.

Source: http://msdn.microsoft.com/en-us/library/aa833404.aspx

SQL Server 2012 official launch by March 7, 2012

SQL Server 2012 to be released officially by March 7, 2012.

http://www.sqlserverlaunch.com