Disable single constraint and ALTER TABLE MyTable CHECK CONSTRAINT

-- Disable single constraint


ALTER TABLE MyTable NOCHECK CONSTRAINT  myconstraint




--Enable single constraint


ALTER TABLE MyTable CHECK CONSTRAINT myconstraint

List All The Column With Specific Data Types

Simple script to identify all the columns with same datatype in specific database:
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'text' --change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Database Mail not showing under Management Node in SQL Server

Database Mail not showing under Management Node in SQL Server


Database Mail is not available in SQL Server 2005 Express Edition--Check


Select @@version

Import Data from .unl files to SQL Server

Rename the File to .txt


or




BULK INSERT dbo.table FROM 'C.\something\_something.unl'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '#'
)

Save all DTS packages on server to files

This will save all dts packages on the server to storage files.
It uses a trusted connect to access the package - just change the LoadFromSQLServer call to use a sql server connection.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@Path varchar(128)
as
/*

*/

 set nocount on

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @FileName varchar(128)
declare @FilePath varchar(128)
declare @cmd varchar(2000)
 
 select  @ServerName = @@ServerName ,
  @FilePath = @Path
 
 if right(@Path,1) <> '\'
 begin
  select @Path = @Path + '\'
 end
 
 -- create output directory - will fail if already exists but ...
 select @cmd = 'mkdir ' + @FilePath
 exec master..xp_cmdshell @cmd
 
 
create table #packages (PackageName varchar(128))
 insert  #packages
  (PackageName)
 select  distinct name
 from msdb..sysdtspackages
 
 select @PackageName = ''
 while @PackageName < (select max(PackageName) from #packages)
 begin
  select @PackageName = min(PackageName) from #packages where PackageName > @PackageName

  select @FileName = @FilePath + @PackageName + '.dts'

  exec @rc = sp_OACreate 'DTS.Package', @objPackage output
  if @rc <> 0
  begin
   raiserror('failed to create package rc = %d', 16, -1, @rc)
   return
  end

  exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
   @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
  if @rc <> 0
  begin
   raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
   return
  end
  
  -- delete old file
  select @cmd = 'del ' + @FileName
  exec master..xp_cmdshell @cmd, no_output
  
  exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
  if @rc <> 0
  begin
   raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
   return
  end
  
  exec @rc = sp_OADestroy @objPackage
 end
go

Reference:http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html

How to backup SQL Server databases to a mapped drive

           

While taking backups for SQL Server databases onto a mapped drive you might get the following error:
"The system cannot find the path specified."
This is because a network share that you map using a local drive letter will not be visible to a SQL Server instance as it is running as a service. So, the SQL Server service runs in the context of the local console with the security context of the startup account of SQL Server. Also, mapped drives are specific to a session and not visible to a service started in the local console context.
So, if you want to backup a SQL Server database to a mapped drive using a local drive letter you have the following options:
1. Run the following command from a query window EXEC xp_cmdshell 'net use <drivename> <share name>'
-- where
<drive name>: Letter used to map the drive
<share name>: UNC path to the share
2. After that you should be able to backup using the mapped drive letter
3. Your Management Studio Object Explorer logical drive enumeration window should be able to list the above mapped drive


Reference:http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

To Connect and Disconnect a mapped drive using SQL

To Connect:

Exec master.dbo.xp_cmdshell 'net use x: \\123.456.78.90\Shared Password /User:Domain\UID /Persistent:yes'

To Disconnect:

Exec master.dbo.xp_cmdshell 'net use x: /delete'

xp_cmdshell Server Configuration Option

xp_cmdshell Server Configuration Option


Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Copy

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

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

You cannot back up databases to a network drive if your account have not sufficient permissions to access the network drive

SYMPTOMS

When you attempt to back up a database to a network drive, you may receive one o...

When you attempt to back up a database to a network drive, you may receive one of the following error messages, depending on the version of SQL Server you are running.

Back to the top

On SQL Server version 7.0, 2000, and 2005

Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'E:\pubs.dat'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

The following information will be in the error log:

998-12-29 11:58:21.62 kernel BackupDiskFile::CreateMedia: Backup device 'E:\pubs.dat' failed to create. Operating system error = 5(Access is denied.).

Back to the top

On SQL Server version 6.5

[SQL Server] Can't open dump device '<dump device path>'. Device error or device off line. Please consult the SQLServer errorlog for more details.

Messages similar to the following appear in the error log:

97/11/25 11:25:49.54 kernel dbsvolopen: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)
97/11/25 11:25:51.60 kernel dbswritecheck: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)

The following message may also appear in the error log:

Msg 18204: dbswritecheck: Backup device '<device path and file>' failed to open, operating system error = 5 (Access is denied.)

Back to the top

CAUSE

This error message usually occurs as a result of incorrect permissions to the ne...

This error message usually occurs as a result of incorrect permissions to the network drive.

Back to the top

WORKAROUND

To enable backups to network drives, perform the following steps: The SQL Server...

To enable backups to network drives, perform the following steps:

1. The SQL Server service must be started using a domain user account to access any resources on a remote computer. Verify that the MSSQLServer service is started under a domain account that has write access to both the Windows NT Server share and its underlying partition (if the partition is formatted with the Windows NT file system, or NTFS).
2. In Control Panel, double-click the Services icon.
3. Select the MSSQLServer service and then click Startup.
4. Examine the startup options for the service and verify that This Account is selected and that a valid domain account is supplied in the form Domain_Name\Domain_account (with the correct password).
   
   NOTE: If you changed the service to run under a domain account, you must stop and restart the MSSQLServer service.
5. Verify that the account specified has write access on the Windows NT share to which you are backing up, as well as the underlying partition (if the partition is formatted as NTFS). To do this, perform the following steps:
   
   
   1. Right-click the share name in Windows NT Explorer.
   2. Click Sharing on the shortcut menu.
   3. On the Security tab, click Permissions.

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

How to Use SQL Server Migration Assistant

SQL Server: Manage the Migration Migrating massive databases can never be called an easy task, but Microsoft has a tool available that can help ease the way and ensure that you’re ready. Assess Your Database SSMA automates migration of most database objects, including stored procedures, functions, packages and triggers. There are few special data types like object type or spatial type that aren’t supported by the current version of SSMA. In addition, you may also have complex PL/SQL statements that can’t be automatically converted. You can use SSMA to run a migration assessment on your Oracle database and determine if your database schema contains such statements. The assessment summary reports contain the following information: Schema tree view: a list of objects from the source Oracle database schema. Conversion rate: the percentage of statements that SSMA is able to convert automatically. For this example (seeFigure 1), the SSMA tool can convert 99.39 percent of all statements in the source Oracle schema. Object count: the number of database objects found in the Oracle schema and a count of objects “With errors” (more on this later). Conversion Message summary:a description of the issues encountered while migrating the source Oracle schema. There are three types of conversion messages you’ll encounter during a migration using the SSMA tool: error, warning and information: An error message is raised when SSMA is not able to convert a database object or a statement within a database object. A warning message is raised when SSMA is able to convert the Oracle statement, but the converted statement may not produce the same result for some cases. For example, SSMA converts Oracle SUBSTR() to SQL Server SUBSTRING(). In most cases, SUBSTRING() would return the same outputs. There are some situations, however, where the results are different. For example, the Oracle SUBSTR() function supports negative values for character position. SUBSTR(‘TechNet’,-3,3) returns ‘Net’ in Oracle, whereas SUBSTRING(‘TechNet’,-3,3) would return an empty string in SQL Server. An information message is for SSMA to provide additional information about how it converts certain objects. Figure 1 A look at the Conversion Rate when converting from an Oracle database. Each error message includes a link that displays the object containing the error. There’s also a side-by-side comparison with the original statement on the left and what the converted statement would look like in SQL Server (see Figure 2) on the right. The error message also includes an estimate of how many hours of manual conversion would typically be required to resolve the issue. Figure 2 You will occasionally get a Migration Error message. Most organizations often perform assessments against a number of Oracle database schemas. They’ll use the total conversion rate and total estimated manual conversion time to compare and prioritize Oracle database schema for migration. Converting Database Schema SSMA gives you many options for schema conversion. For example, you can modify data type mapping. SSMA provides default data type mapping between Oracle and SQL Server. However, you can customize the data type mapping for a specific table, for all tables, for a specific object (such as a stored procedure or function) or for different usage (such as data type in column, data type in variable or data type in input/output parameter of your procedure). Convert the database schema by clicking the “Convert Schema” button. Then you can navigate to the different database object and compare the original schema object and the converted object (SeeFigure 3) Figure 3 The Schema Conversion view. When an object contains a statement that SSMA is unable to convert automatically, the tool will add a migration error description, comment on the specific statement or replace it with a generic type. This isolation approach lets you continue with the database migration and resolve the issue later. You could also resolve the issue and modify the statement directly from SSMA. For example, inFigure 3, there’s a user-defined function called “TENURE.” This returns an INTERVAL data type that SQL Server doesn’t support. You can modify the return type to NUMBER (see Figure 4) and reconvert the function. This removes the error and converts the return value to float(53). Figure 4 You can modify statements in SSMA to resolve incompatibilities. You can also modify converted statements. For example, you can replace “float(53)” return type with “INT.” Note that any modification you make with SSMA is stored locally. The changes you make to source statements aren’t applied to the Oracle database schema you have in production. Similarly, any changes made to the target SQL Server statement aren’t immediately applied to the server. This lets you continue to refine and make necessary modifications to converted schema without impacting your target server. You can deploy converted schema to the target SQL Server by right-clicking the schema name on the SQL Server Metadata Explorer window. You can also generate a script to create the entire schema information, which you can then deploy on your target server (see Figure 5). Figure 5 Deploying converted schema to SQL Server. Migrating Data After creating the database schema in the target SQL Server, you can use SSMA to migrate Oracle data. SSMA isn’t the only option for migrating data, though. You could also use SQL Server Integration Services (SSIS). However, migrating data with SSMA lets you use the same type of mapping for schema conversion. It also handles some of the common data-migration issues when migrating from Oracle to SQL Server. For example, Oracle has a wider range of supported date types than SQL Server. By default, SSMA raises the data migration error when it finds such a case. You can have SSMA automatically convert out-of-range date values with NULL or the nearest date SQL Server can support. You can modify this setting through Tools | Project Setting | General | Data Migration (see Figure 6). Figure 6 There are options for handling data-migration errors. After completing the data migration, SSMA will display a report with the number of rows migrated, the success rate and the times taken to migrate each table (see Figure 7). Figure 7 SSMA will give you a full data-migration report. Testing Database Migration After you’ve successfully migrated your database, the next step is validation. When migrating from Oracle and Sybase, SSMA lets you compare the source database and the migrated database. You can define a series of test cases; then SSMA runs the test cases on both the source and the target database. It will compare the results, as well as any changes the test cases made on the underlying tables. To define a test case, select New Test Cases from the Tester menu. A Test Case Wizard will walk you through the process of creating a test case. You can also select specific database objects to test. For example, there’s a procedure called ADD_EMPLOYEE. This procedure inserts a new record in the employee table based on the value provided from the input parameter. You can define specific input parameters to use in the test through the Call Values tab (see Figure 8). You can define as many call values as you need. Figure 8 Specifying call values in the Test Case Wizard. Besides comparing test object execution, SSMA can also test changes to the underlying table. For example, when executing the ADD_EMPLOYEE stored procedure, SQL Server will insert an additional row into the EMPLOYEES table. SSMA compares the changed rows in the affected table between the source and the target. If needed, you can also specify the level of granularity for the comparison (seeFigure 9). Figure 9 Specifying underlying tables for comparison. The final step in defining the test case is additional settings. One important setting is whether to roll back any changes made to the table as a result of testing (see Figure 10). In my example, when executing the ADD_EMPLOYEE stored procedure, a new record will be added to both the source Oracle database and the target SQL Server database. If you select the data rollback option, SSMA will remove the inserted value after the test is complete. Figure 10 Defining test case settings. After defining the test case, you can run it as many times as needed. For each run, you’ll receive a test result report that compares results (See Figure 11). Figure 11 SSMA will give you a full test result report. SSMA gives you rich functionality to automate database migration. You can use the tool to assess the complexity of the database you plan to migrate, convert the database schema, resolve common database-migration issues, migrate data from the source database and validate the migrated database. The tool is designed for migrating to SQL Server, but it also supports direct migration to SQL Azure (from MySQL, Sybase and Access databases). When migrating to SQL Azure, SSMA takes into account requirements for the SQL Azure platform. For example, SQL Azure requires that its tables have a clustered index. If the source table doesn’t include a primary key or clustered index, the tool can automatically add a ROWID column and set the clustered index on the column during the conversion. You can download SSMA from the Microsoft SQL Server Web site. Not only is the tool free, but you can also get free e-mail support from Microsoft Customer Service and Support. For more information on SSMA, visit the SSMA team blog for a video demonstration and how-to articles, as well as guidance on resolving common migration issue Reference:http://technet.microsoft.com/en-us/magazine/hh334645.aspx