MSSQL Data Types and Ranges

Exact numerics

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079
date (1 day accuracy. Introduced in SQL Server 2008)	Jan 1, 0001	Dec 31, 9999
datetimeoffset (100 nanoseconds accuracy. Introduced in SQL Server 2008)	Jan 1, 0001	Dec 31, 9999
datetime2 (100 nanoseconds accuracy. Introduced in SQL Server 2008)	Jan 1, 0001	Dec 31, 9999
time (100 nanoseconds accuracy. Introduced in SQL Server 2008)	00:00:00.0000000	23:59:59.9999999

Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (Introduced in SQL Server 2005).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Thanks ;- http://www.teratrax.com/sql-server-data-types-ranges/

Need to run SSIS Package under specific user account using SQL Server Agent JOB

The issue is we need to run the SSIS package where the source path has permission to a perticular user. But the JOB owner is failed to access the path while executing the package as its using SQL Server Agent Account to run SSIS package.

Solution :-

Step1:- Go to Security expand choose Credential from there right click choose New Credential.
Step 2 :-  Use identity as your wish either sql account\windows account. click Ok.
Step 3:- Go to SQL Server Agent  choose proxy create new proxy.
Step 4:- In principals grant the required roles. click ok.
Step 5:- Go to the Job choose the step edit, now drop down RUN AS.
Step 6:- Select click ok. Now it will succeed.

More Help :- http://stackoverflow.com/questions/812428/how-to-schedule-ssis-package-to-run-as-something-other-than-sql-agent-service-ac

or

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Script to get the details of SQL Server Installed Location , SQL Server Error Log location and Product Level for the Instance

Thanks to :- http://www.sqlserver-training.com

                declare @RegPathParams sysname
                declare @Arg sysname
                declare @Param sysname
                declare @MasterPath nvarchar(512)
                declare @LogPath nvarchar(512)
                declare @ErrorLogPath nvarchar(512)
                declare @n int

                select @n=0
                select @RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'
                select @Param='dummy'
                while(not @Param is null)
                begin
                    select @Param=null
                    select @Arg='SqlArg'+convert(nvarchar,@n)

                    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
                    if(@Param like '-d%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end
                    else if(@Param like '-l%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end
                    else if(@Param like '-e%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end

                    select @n=@n+1
                end

                declare @SmoRoot nvarchar(512)
                exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

SELECT
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName]

ALL Constraints of a given Database

SELECT KCU.table_name,

KCU.column_name field_name,

TC.constraint_type,

CASE TC.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

CASE TC.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

rc.match_option 'match_type',

rc.update_rule 'on_update',

rc.delete_rule 'on_delete',

ccu.table_name 'references_table',

ccu.column_name 'references_field',

KCU.ordinal_position 'field_position'

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

ON KCU.table_name =TC.table_name

AND KCU.table_schema =TC.table_schema

AND KCU.table_catalog =TC.table_catalog

AND KCU.constraint_catalog =TC.constraint_catalog

AND KCU.constraint_name =TC.constraint_name

LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

ON rc.constraint_schema =TC.constraint_schema

AND rc.constraint_catalog =TC.constraint_catalog

AND rc.constraint_name =TC.constraint_name

LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

ON rc.unique_constraint_schema = ccu.constraint_schema

AND rc.unique_constraint_catalog = ccu.constraint_catalog

AND rc.unique_constraint_name = ccu.constraint_name

WHERE KCU.constraint_catalog = DB_NAME()

ORDER BY KCU.constraint_name,

KCU.ordinal_position;


Source :- http://www.extradrm.com/blog/?p=376

Rebuild Resource Database

Resolution  1:

Try to detach and attach the DB files from some other SQL Server with same version and patch level.
Bring SQL Server ONline.


Resolution 2:

Rebuild the resource Database

---

The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.

To rebuild the resource system database:

1. Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
2. In the left navigation area, click Maintenance, and then click Repair.
3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. ClickOK or Install to continue.
4. On the Select Instance page, select the instance to repair, and then click Next.
5. The repair rules will run to validate the operation. To continue, click Next.
6. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
   
   
   
   More on http://msdn.microsoft.com/en-us/library/dd207003.aspx#RebuildProcedure
   
   

Contained Databases - SQL Serve 2012

What are the Benefits of using Contained Database ?

Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.

What type of Authentication Modes are supported by Contained Database?

Contained Databases supports

• SQL Server Authentication
• Windows Based Authentication

A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn't exists in master’s syslogins tables and will not even show up when you list all SQL Users.

Contained DB Users

http://blogs.extremeexperts.com/2012/10/03/contained-databases-inside-sql-server-2012/