The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

Step1 :- Go to the properties account
Step2:- Choose User Mapping
Step 3:- Choose Respective DB
Step 4:- Remove db_denydatareader (uncheck)
Step 5:- Click Ok and Refresh


Another Issue :- http://blog.sqlauthority.com/2012/06/19/sql-server-solution-user-not-able-to-see-any-user-created-object-in-tables-security-and-permissions-issue/

A snapshot was not generated because no subscriptions needed initialization

Thanks To :- http://social.msdn.microsoft.com/Forums/lv/sqlreplication/thread/12c21ca8-7031-4a9e-b813-0122f59a7b9d

Adding new article without generating a complete snapshot :




1) Make sure that your publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE or 0.



Use yourDB

select immediate_sync , allow_anonymous from syspublications



If either of them is TRUE then modify that to FALSE by using the following

command



EXEC sp_changepublication @publication = 'yourpublication', @property =

N'allow_anonymous', @value='False'

Go

EXEC sp_changepublication @publication = 'yourpublication', @property =

N'immediate_sync', @value='false'

Go



2) Now add the article to the publication



Use yourDB

EXEC sp_addarticle @publication = 'yourpublication', @article ='test',

@source_object='dbo.test', @force_invalidate_snapshot=1



If you do not use the @force_invalidate_snapshot option then you will receive the

following error

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99

Cannot make the change because a snapshot is already generated. Set

@force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.



3) Verify if you are using CONCURRENT or NATIVE method for synchronization by running the following command.



Use yourdb

select sync_method from syspublications



If the value is 3 or 4 then it is CONCURRENT and if it is 0 then it is NATIVE.

For more information check

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



4) Then add the subscription for this new article using the following command



EXEC sp_addsubscription @publication = 'yourpublication', @article = 'test',

@subscriber =‘subs_servername', @destination_db = 'subs_DBNAME',

@reserved='Internal'



If you are using the NATIVE method for synchronization then the parameter

@reserved=’Internal’ is optional but there is no harm in using it anyways. But if it is CONCURRENT then you have to use that parameter. Else the next time you run the snapshot agent it is going to generate a snapshot for all the articles.



Lastly start the SNAPSHOT AGENT job from the job activity monitor. To find

the job name follow these steps.



· select * from msdb..sysjobs where name like '%yourpublication%'

· Right click on each of those jobs and find which one contains the step

‘Snapshot Agent startup message’. This is the job that you want to

start from the first step.



Verify that the snapshot was generated for only one article.

Adding Artical To T-Repl

Step 1  :- Add Artical


USE [Publication DB NAME]
GO

EXEC sp_addarticle
    @publication = '[Publication Name]',
    @article = N'[ARTICAL NAME]',
    @source_object = N'[ARTICALNAME]',
    @force_invalidate_snapshot=1
GO


Step 2:-


EXEC sp_changepublication
@publication = '[publication  name]',
@property = N'allow_anonymous',
@value = 'false'
GO


EXEC sp_changepublication
@publication = '[publication name]',
@property = N'immediate_sync',
@value = 'false'
GO

Step 3:- Run the snapshot agent either from replication monitor or from agent.

Step 4: Make Flags true used in Step 2

More Help on :- http://support.microsoft.com/kb/830210

http://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot





 

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/