Lengh of LOB data to be replicated exceeds configured maximum 65536

Whenever you try to replicate data from a database that saves the images(filestream) in the database, and you have included those images in your replication, then you might get this error

Length of LOB data (583669) to be replicated exceeds configured maximum 65536

 Solution:

There are two solutions for this problem 

• using TSQL
• sp_configure 'max text repl size', '2147483647'
  GO
  RECONFIGURE 
• using SQL Server Manager Studio
• In Object Explorer, Right click on your server name and select properties 
• Click the Advanced node
• Under Miscellaneous option, change Max Text Replication Size to the desired value.

Attached failed, Unable to open physical file, operating system error

Some time when you try to attach database files (.mdf, . ldf) to sql server , you might get the error
"Unable to open physical file, operating system error"

The reason behind this is that the account from which you are accessing the file , does not have rights for this specific folder or file.

Solution for this is that , you have to give the rights. In my case i have given the rights to the local administrator

After giving the rights , try to attach the file again.

I have attached the database successfully.

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."  i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO
emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL

After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
 
it will give you the error  Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
It will be executed successfully

An easy way to get the Definition of an Object

I was using the sp_helptext very frequently to get the definition of an object.

Here is another easy way to get the definition of an object

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE type='V'

You can use the, ALTER in the result set of the above query by using the REPLACE.

In the where condition you can specify the Procedure(P) or Functions(FN) as well.

Difference between NEWSEQUENTIALID() and NEWID()

In this article, we will be looking at the Difference between NEWSEQUENTIALID() and NEWID() and How NEWSEQUENTIALID() is generated.

NEWSEQUENTIALID() generates the unique identifier values sequentially.
Whereas NEWID() generates the unique identifier values randomly.

NEWSEQUENTIALID() function creates the GUIDs greater than the previously generated GUIDs, since the last restart of the system, because after restarting the system the next NEWSEQUENTIALID can be started from the lower range.
Both NEWID() and NEWSEQUENTIALID() are globally unique. Starting of NEWSEQUENTIALID after the restart of the computer does not affect its globally uniqueness.

NEWSEQUENTIALID() uses the default constraint, and it can't be used in SELECT or SET queries like NEWID().
The following query SELECT NEWSEQUENTIALID() will generate error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This will also generate the similar error

declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

while if you use
SELECT NEWID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

No error will be produced and guid will be returned.

NEWID() is not good for performance because it is generated randomly so that it increases the page splits in the indexes.
NEWSEQUENTIALID() can be used to generate GUIDs to reduce page contention at the leaf level of indexes.

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer.
GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.
NEWSEQUENTIALID() returns the value that includes the MAC of the NIC of the system. If the system has the network card, then it is guaranteed that the GUID generated using NEWSEQUENTIALID() will globally unique across the servers.
If the NEWSEQUENTIALID() used on a computer that doesn't have NIC, the values generated using NEWSEQUENTIALID() are not guaranteed to be unique across the servers, it will be unique only for that system.
Internally NEWSEQUENTIALID() usses a Windows API called UuidCreateSequential(), which generates the value based on the NIC's MAC address and an internal hardware timestamp.

Here you can see the Newsequentialid (Histrory/Benefits and Implementation)

Find Created date and Modified date of Database objects

This script will return the objects in sql server database with modified and created date.
However you can change the order by clause as well as the Where clause according to your requirements

SELECT sys.schemas.name + '.' + sys.objects.name,
create_date,
modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
OR type = 'U'
OR type = 'FN'
OR type = 'V'
ORDER BY modify_date DESC

The newsequentialid() built-in function can only be used in a DEFAULT expression

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.


This error occurs when we try to use the NEWSEQUENTIALID() function in the SQL statements or in SET statements. e.g.

SELECT NEWSEQUENTIALID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique
OR
INSERT INTO AA(id, name) VALUES(NEWSEQUENTIALID(),'abc')

The NEWSEQUENTIALID() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.