SqlServer Begin's

We successfully migrated from sql 2005 to sql 2008 few weeks back (a non production environment) , there was little or no issues with the migration, I would consider this to be one of the items that went well for us as a DAB team for last quarter. Few days back on of the dev folks walk up to me told me a transaction won’t get completed and the application is generating the following error. Msg 7139, Level 16, State 1, Procedure csp_esccm_tTablename_Update, Line 49 Length of LOB data (72322) to be replicated exceeds configured maximum 65536. The statement has been terminated. After going through the message I noticed that the error was to do with replication and that the maximum row size is not supported with one of the current settings after the migarion. Solution There are server level setting that needs to be changed for replication to successfully transfer large data rows. 1. exec sp_configure 'max text repl size' Highlight and execute the code on line one. T...

Last afternoon I was in the middle of trying to respond to one of my counter parts describing why one the developers I worked with had written a procedure in particular manner. I knew I had placed two columns in the include list but sp_helpindex never should the columns in the view. The only way I know of verifying if a index has a include list is by going through object explore à …. à table à index.   For me this thought was way too painful and this simple script is a result of that I haven’t taken the trouble to concatenate the columns declare @TableName varchar(100) SET @TableName = 'Production.ProductReview' – include the the table name along with the schema name SELECT object_id(@TableName) TableName ,SI.Name , SI.type_desc , SI.is_primary_key ,SC.name ColumneName ,SIC.column_id ,SIC.is_included_column -- this column will signify if a filed is a part of the include list FROM sys.indexes SI JOIN sys.index_columns SIC ON SI.object_id = SIC....

Have you run into days where you really want to do something and even though you don’t have the necessary privileges you yet continue ? I wanted to access on sql server instance but didn’t have all the information I needed about the server, I knew the physical server and I had admin privileges on the server. For me to remotely access the machine I needed the sql IP and port details which is not that easy to find in a clustered configured server instance. Find the Sql IP address Go to Failover cluster manager and click on the server applications; after identifying the sql server instance , highlight it and move to the center view which can be expanded. This will have the sql server IP address Identifying the port details Go to Sql server --> configuration manager then read your way through the pick

Have you ever run into “.net” error saying out of memory? Today I was gathering performance counter (using profiler) for a procedure that was modified due to performance issues. I created a script for the procedure to be executed 1000 times , when executing the procedure from MMS there was a error generated with the following message “An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.” My first thought was the server ran out of memory due to the procedure been executed many times( not to mention the procedure modified was one of the bad procedures I had ever seen in a long time) . Then I executed only the first 300 rows from the script and it executed successfully. Solution Change the MMS setting from result to Grid to Result to Text and it worked fine. This is because MMS allows X number of data elements on the grid (This depends on the Result to Grid setting) and Result to text has no limit (as of what I gath...

Following is a example of creating a procedure in all the databases using a loop mechanism . I couldn’t use SP_MSFOREACHDB as the procedure I was creating had many single quotations that made the procedure very difficult to handle. The result of this needs to be copied and pasted on a new window pane and executed. The procedure needs to be assigned to @SqlStr variable DECLARE @SqlStr nvarchar(max) set nocount on set @SqlStr = ' CREATE PROCEDURE [dbo].[dbmaint_StatManagement_new] ( @RowMaxCount BIGINT = 1000000, @DaysOld INT = 7, @MinDensity FLOAT = 1, @Sample INT = NULL, @Delay NCHAR(8) = NULL ) AS BEGIN SET NOCOUNT ON END ' --====================================================================================================================================================== CREATE TABLE #TableName (iid int identity(1,1) , DBName varchar(100)) insert INTO #TableName SELECT NAME FROM sys.databases WHERE NAME NOT IN ('TEMPDB,MASTER,MSDB,MOD...

SQL Server Analysis Services Tutorial Lesson 1: Defining a Data Source View within an Analysis Services Project In this lesson, you define a data source view within an Analysis Services project by using BI Development Studio. Lesson 2: Defining and Deploying a Cube In this lesson, you define a cube and its dimensions by using the Cube Wizard, and then deploy the cube to the local instance of Analysis Services. Lesson 3: Modifying Measures, Attributes and Hierarchies In this lesson, you improve the user-friendliness of the cube and incrementally deploy the related changes, processing the cube and its dimensions as needed. Lesson 4: Defining Advanced Attribute and Dimension Properties In this lesson, you define a referenced dimension relationship, order attribute members by using composite keys, and define custom error handling. Lesson 5: Defining Relationships Between Dimensions and Measure Groups In this lesson, you define a fact relationship for a degenerate dimension and define a man...