Wednesday, August 22, 2012

Index was outside the bounds of the array

An error occurred while executing batch. Error message is: Index was outside the bounds of the array.
Today i come up with the above error on development box. A table is tiny and only 89 thousands rows. I found the fowllowing two links from microsoft site.
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/d30194ae-9f6a-46ab-b054-6dc2ed9195c2
http://support.microsoft.com/kb/2459027
I am running SQL Server version Microsoft SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)

I try to reproduce the same from created test database on the same error but it did not show up the same erro.

Resolution: Create a replica table of issue table and export the data that is works fine.

Friday, August 17, 2012

How to create a Snapshot database

sp_helpdb MyTest
go

-- The following script is easiest way to create a Snapshot database. 

CREATE DATABASE MyTest_snapshot_db ON
( NAME = MyTest,
FILENAME = 'D:\MSSQL\DATA\MyTest_snapshot_db.ss' )
AS SNAPSHOT OF MyTest;
go

Note: Remember you can't create a snapshot database using SSMS but you can delete snapshot database using ssms.

Thursday, August 16, 2012

[SQLSTATE 42000] (Error 10060) OLE DB provider "SQLNCLI10" for linked server


[SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server

Error Message
Executed as user: MyServer\Myaccount. TCP Provider: 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. [SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server "" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "MYSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.
Open Component Services via

 Run then dcomcnfg


To resolve this problem, follow these steps:
  1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
  2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
  3. Right-click My Computer, and then click Properties.
  4. Click the MS DTC tab, and then click Security Configuration.
  5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
  6. Under Network DTC Access, click Network Transactions.

    Note If you installed Service Pack 1 for Windows Server 2003, you can click Allow Inbound and Allow Outbound.
  7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
  8. Click OK.
  9. In the message box, click Yes to continue.
  10. In the DTC Console Message dialog box, click OK.
  11. In the System Properties dialog box, click OK.
  12. Reboot the computer for these changes to take effect.

    Note In some cases, you must start the DTC service before you start the SQL Server service so that the linked server distributed queries work well.
  13. Add above DTC configuration on both server
  14. You need  add XACT_ABORT ON on your stored procedure

Tuesday, August 14, 2012

Server: Msg 3013 RESTORE DATABASE is terminating abnormally.

Server: Msg 3013
RESTORE DATABASE is terminating abnormally.

Server: Msg 3159
The tail of the log for the database "DBTest" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Solution : If you want to restore the database on to new server where you created the database was never backup before then you need to take backup of your database on new server ( mostly empty db) before restore the database from the backup file.

Wednesday, August 08, 2012

[264] An attempt was made to send an email when no email session has been established

You need to enable mail profile located in the properties of the SQL Agent,
Alert System and choose Mail System and Mail profile from the drop down listbox.
Finally you must restart the SQL Server Agent and run your scheduled job to get email notification