World Search

Sunday, June 28, 2015

Saturday, June 27, 2015

Upgrades from Earlier Versions to SQL Server 2014

We can find the supported upgrades to SQL Server 2014 from the below link:

https://msdn.microsoft.com/en-us/library/ms143393.aspx

SSRS Upgrade: 

  • Upgrade: You upgrade the Reporting Services components on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade. In-place upgrade is not supported from one mode of Reporting Services server to another. For example, you cannot upgrade a Native Mode report server to a SharePoint mode report server. You can migrate your report items from one mode to another. For more information, see the ‘Native to SharePoint Migration’ section later in this document.
  • Migrate: You install and configure a new SharePoint environment, copy your report items and resources to the new environment, and configure the new environment to use existing content. A lower level form of migration is to copy the Reporting Services databases, configuration files, and if you are using SharePoint mode, the SharePoint content databases.

https://msdn.microsoft.com/en-us/library/ms143747%28v=sql.120%29.aspx

Well defined steps of SSRS to new Server:

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

In Place Upgrade Of SQL Server:

http://sqlmag.com/sql-server-2014/migrating-sql-server-2014

Known Issues:

http://www.sanssql.com/2013/11/upgrade-error-valid-database.html

Wednesday, June 24, 2015

Grant execute permission to all SP's in the DB

/*
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
 in the DB to diffrent users.


*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1

declare @sp_count int,@sp_name varchar(50)

--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name

select @sp_count=count(*) from #temp_sp_list

while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list

EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;

delete from #temp_sp_list where name=@sp_name

select @sp_count=count(*) from #temp_sp_list

print @sp_count;
end

drop table #temp_sp_list


-- Thanks to Rakesh Rao :)

Tuesday, June 2, 2015

To stop multiple traces on server







Here in the steps to stop the traces:_



Step 1: Find out what are all running

select * FROM :: fn_trace_getinfo(default)

Step 2: Stop them by passing trace id and status
 
sp_trace_setstatus 3, 0

go

sp_trace_setstatus 3, 2



Syntax

sp_trace_setstatus [ @traceid = ] trace_id     , [ @status = ] status



0
Stops the specified trace.
1
Starts the specified trace.
2
Closes the specified trace and deletes its definition from the server.




Monday, June 1, 2015

Replication _ IMP

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 256 articles. Snapshot and transactional replication support up to 32,767 articles.

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 246 columns in a table. Snapshot and transactional replication support up to 1,000 columns in a table.

Merge publication supports up to 1,024 bytes for a column used in a row filter. Snapshot and transactional replication support up to 8,000 bytes for a column used in a row filter.

Merge publication supports more than 30 unique indexes in a table. The unique index limit is determined by the number of articles and columns.