Thursday, October 29, 2020
Beetle Blog
By: douglas - Sql Server - 2/28/2014 12:26:25 PM

 Exception Message:

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
In this case it is possible one of your stored procedures has SET ANSI_NULLS OFF.  Change it to "ON".

By: douglas - Sql Server - 11/26/2012 8:38:33 PM

DBCC shrinkdatabase( database_name ) can take a long time and it doesn't show progress if you run it via SSMS.  To view the percent complete use this query:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time FROM sys.dm_exec_requests where command = 'DbccFilesCompact'


By: douglas - Sql Server - 5/1/2010 7:33:52 PM

 If you try to run a very large script in SQL Management Studio (2005, 2008) it may not run.  Instead it may give a "System.OutOfMemoryException" error.

In order to run the script you can use the sqlcmd in the command line instead.

sqlcmd -S YOURSQLSERVER\INSTANCENAME -i "C:\Your Script.sql”

Example connecting to a remove database using sql server authentication:

sqlcmd -S -i -U -P -d

Note that username and password are sent using clear text.

By: douglas - Sql Server - 4/26/2005 2:07:56 AM
To change the owner of an SQL database object (for example a table or stored procedure) this sql procedure can be used:
sp_changeobjectowner '<current owner>.<object name>','<new owner>'
  • current owner = the current owner of the object
  • object name = the object's name (could be the table's name or stored procedure's name)
  • new owner = the database user that should be the new owner of the object

sp_changeobjectowner is a built in SQL server object.

By: douglas - Sql Server - 4/2/2003 12:00:00 AM

When installing MSDE 2000 you may run into an error message stating that a strong sa password is needed.

You can bypass this message by running the MSDE setup program from the command line. For example:

C:\sql2ksp3\msde\setup.exe SECURITYMODE=SQL BLANKSAPWD=1

This sets up MSDE with a blank sa password.

A blank sa password is not recommended for security reasons. You should change the password after the MSDE installation is complete.

Use this procedure if you want to restore a backup to a different database on the same server or to a database on a different server.

  1. Right-click on the database you want to restore the backup to.
  2. Select "All Tasks" > "Restore Database...".
  3. If you want to create a new database using the backup, type in the new database name in the "Restore as database" dropdown, otherwise select the database you want to overwrite and restore the backup to.
  4. Select to restore "From Device".
  5. Click "Select Devices", the "Add...".
  6. Choose "Filename" and then browse for your backup file.
  7. Click "OK".
  8. Click "OK" again to return to the "Restore database" window.
  9. Click on the "Options" tab.
  10. If you are overwriting an existing database, check "Force restore over existing database.".
  11. In the "Move to physical file name" column, rename the old database name with your new database name.
    1. You must rename the data and log files if the database you are restoring from still exists on the same server. In this case you are making a copy of your database.
    2. Data and Log files should be named: <your database name>_Data.MDF and <your database name>_Log.LDF respectively.
  12. Click "OK" and the restore operation should complete.


By: douglas - Sql Server - 9/1/2002 12:00:00 AM

This note applies to creating DTS packages in SQL Server.

When creating a DTS package and scheduling it, make sure that the user who owns the schedule has permission to execute the DTS package.

General rule:
  • Log into the machine running SQL server.
  • Create the DTS package and schedule it.
  • Check the properties of the schedule and make sure the owner of the schedule is the same as the owner of the DTS package (i.e. the account you logged in as).

If the scheduler doesn't have permissions to the package then when it tries to run it won't be able to execute the package. This can be resolved if the scheduler runs under the account that owns the DTC package.


Beetle Blog v0.2.2

Copyright © 1997 - 2020 All rights reserved.