Wednesday, June 5, 2013

[SQL Server 2008 issues] Combining SQL versions on Cluster

[SQL Server 2008 issues] Combining SQL versions on Cluster

Combining SQL versions on Cluster

Posted: 04 Jun 2013 07:07 PM PDT

Hi AllIs it possible to combine SQL server 2008 R2 and SQL server 2012 on the same Cluster? We are starting of Sharepoint 2013 project and the developers wants a 2012 SQL and the cluster is currently 2008 R2 and we don't want to upgrade the existing instances to 2012.I believe it should be possible since all binaries are located in different directories. The only problem are the shared feature, which has to be upgraded, but I believe they are backward compatible.Hope someone here has experience in this or have some insights.Thanks/Bo

Import Data replaces NULL with 0

Posted: 04 Jun 2013 06:56 PM PDT

I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose "Select identity insert" option to copy the identity element property. By using this identity elements are imported properly. But after importing i compared the source database and destination database and found some changes!!. All NULL values in the table are replaced by 0. So how can i avoid it.Is there any option to copy it as it while using import data wizard? Please help

How to get a list of replicated articles ?

Posted: 04 Jun 2013 01:49 AM PDT

I need to programmatically to get a list of all replicated articles for a given database. I tried select * from sys.dm_repl_articles, but it returned only 2 out of 8 articles.In Replication properties/Articles GUI I see 8 checked tables as articles.What another sys.* or sp_* can I use?Thanks

Performace issue while updating records and trigger on table

Posted: 04 Jun 2013 03:57 PM PDT

Hi All,I am having a performance issue while updating records into sql server table,I have created below trigger to update related tables if the status of this table is updated/Changed.USE [EMEA_SERVICEMAX]GO/****** Object: Trigger [Servicemax].[ERP_ACCOUNT_DATE_UPDATE] Script Date: 06/05/2013 10:19:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER [Servicemax].[ERP_ACCOUNT_DATE_UPDATE]ON [EMEA_SERVICEMAX].[Servicemax].[ERP_ACCOUNT_STG]FOR UPDATEAS BEGINDECLARE @nOldValue varchar(20),@nNewValue varchar(20),@record_dt date,@CUST_NUM varchar(20),@SOURCE_SERVER varchar(15),@COMPANY_NO intSELECT @nOldValue=b.CUSTOMER_STATUS, @nNewValue=a.CUSTOMER_STATUS ,@record_dt=a.record_insert_dt,@CUST_NUM=a.customer_number,@SOURCE_SERVER = a.SOURCE_SERVER,@COMPANY_NO=a.COMPANY_NOFROM inserted a, deleted bIF @nNewValue = @nOldValuereturn else BEGINupdate EMEA_SERVICEMAX.Servicemax.SALES_ORDERS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.INSTALLATION_STGset record_insert_dt=@record_dtwhere LOCATION_CUSTOMER_NO=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.ERP_ACCOUNT_PROJECTS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.SERVICE_CONTRACTS_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOupdate EMEA_SERVICEMAX.Servicemax.SERVICE_ORDER_STGset record_insert_dt=@record_dtwhere customer_number=@CUST_NUMand SOURCE_SERVER=@SOURCE_SERVERand COMPANY_NO=@COMPANY_NOENDENDGOAny help on this may be appreciated.Thanks,Nilesh

Using the same partition function and scheme for multiple tables

Posted: 04 Jun 2013 02:58 PM PDT

I have a database where most of the tables have an integer field say "SetId" denoting a batch of data.If a new SetId is created all these tables will get data related to the new SetId.SetId governs data retention, purging, it also forms part of filtering in many user queries on these tables.I am thinking of partitioning all these tables on the SetID column, which I am sure would be beneficial for performance and data maintenance.My question is, do I use a common partition function and partition scheme to partition all the tables on SetId, or is it more advisable to use separate partition functions and schemes for each tables?

Query Help

Posted: 04 Jun 2013 07:29 AM PDT

Hello I need one help in developing Query[code="sql"]CREATE TABLE #TEMP(cStudentID varchar(09),iSchoolCode int,cGradeCode char(02),dtEntryDate datetime,dtWithdrawDate datetime,iSchoolYearCode int)INSERT INTO #TEMP VALUES ('001223436',301,'10','2004-09-02 00:00:00.000','2005-06-16 00:00:00.000',2003)INSERT INTO #TEMP VALUES ('001223436',301,'11','2005-09-01 00:00:00.000','2006-06-15 00:00:00.000',2004)INSERT INTO #TEMP VALUES ('001223436',301,'12','2006-08-31 00:00:00.000','2007-06-12 00:00:00.000',2005)INSERT INTO #TEMP VALUES ('001223436',301,'10','2007-08-30 00:00:00.000','2008-06-11 00:00:00.000',2006)INSERT INTO #TEMP VALUES ('001223436',301,'11','2008-08-28 00:00:00.000','2009-06-12 00:00:00.000',2007)INSERT INTO #TEMP VALUES ('001223436',301,'12','2009-09-03 00:00:00.000','2010-06-16 00:00:00.000',2008)INSERT INTO #TEMP VALUES ('001223436',301,'12','2010-09-02 00:00:00.000','2011-06-13 00:00:00.000',2009)INSERT INTO #TEMP VALUES ('001223436',301,'12','2011-06-14 00:00:00.000','2011-06-15 00:00:00.000',2010)INSERT INTO #TEMP VALUES ('001223436',301,'99','2011-09-01 00:00:00.000','2012-06-13 00:00:00.000',2011)[/code]If Student GradeCode is repeat in Next School Year, we need to set flag for that.so Expected result is[code="plain"]StudentID iSchoolYearCode IsRepeat001223436 2003 0001223436 2004 0001223436 2005 0001223436 2006 1001223436 2007 1001223436 2008 1001223436 2009 1001223436 2010 1001223436 2011 0[/code]Please help me to develop this.If Anyone do this w/o CTE will greatly Appreciate because i need to use in one of the Logic in my Script.Thanks

How to view individual records?

Posted: 04 Jun 2013 09:22 AM PDT

As a newbie, I have a question which I hope isn't too stupid:In SSMS, I know I can view information about a database viaObject Explorer | Database Engine instance | database (right-click) | Properties But isn't there any way to view individual records in the database? Otherwise, how do I even know what data fields are in the individual records? Without knowing even that much, how can I even practice making queries?

How to calculate space needed to modify a column in a table?

Posted: 30 May 2013 01:39 PM PDT

I have a situation that's driving me INSANE...The vendors of our financial processing system came to us asking that we increase a column in a table from char(15) to char(19) to allow for extra room in a needed field from the web application. Typically this type of change wouldn't such a big deal, however this table has about 90 million rows in it and has a data footprint of about 214GB.Now unless I'm missing something (which is very possible, as I'm no expert at anything), to issue an ALTER COLUMN like this via SSMS it involves the creation of a new "temp" table, transfer the data from the existing table to the new table, create all non-clustered indexes on the new table, and drop the old table, rename the new.Consider the command:[code="sql"]A-LTER TABLE MyProblemChild A-LTER COLUMN [FleetCardVehicleNo] char (19) NULLGO[/code]I did this on a testing server with attached raided storage and it took about 1.5hrs and the log file was pre-sized at 230GB - not too badWhen I attempted to run the same process on our production server (which has the data, index, and log files on a SAN, each residing on their own respective LUNS), the process ran for over 5 hours, grew the log file to over 490GB...and only stopped there because the LUN ran out of space... to which I had to kill/rollback the entire thing!How can I calculate how much log file space will be needed to successfully complete the operation?

Create FirstName and LastName to Replace Existing FirstName and LastName

Posted: 04 Jun 2013 10:50 AM PDT

Hello EveryoneI hope that you all are having a very nice day.I am wanting to change all the FirstName and LastName values in a table. This is more for Demo security really. Creating some fake names from existing names. These are stored in two separate columns. I cannot think of any way to easily do this. I need approx 25,000 rows. LOL There are names in the columns currently, I would just like to change them so that the entire record does not show an actual person. I want to use real names, not anything like FirstName1, FirstName2, etc....So Frank Smith, would be changed to Joe JacksonI have already changed all the dates and numeric values, those were simple.I greatly appreciate any and all assistance, suggestions and comments.ThanksAndrew SQLDBA

SSIS and Varbinary Column

Posted: 04 Jun 2013 11:37 AM PDT

Hi All,Hope someone can help here.I have an SSIS package, just reads a row and inserts it into a table on another server.In Design with a hard coded row id the apckage works as expected.But in the actual environment the package is executed through a stored procedure.When this is executed the package completes with no errors but the varbinary column is null all other column data is inserted.The source is a sql selectSELECT Org_ID, pDate, SheetNumber, Title, Contents, TestGroup, PhotoNumberFact, PhotoNumberFactBlob, isDeletedFROM dbo.fact_sheetWHERE (update_id = ?)The destination is a straight column mapping to the destination table.The stored proc uses sp_add_job, sp_add_jobserver, sp_add_jobstep, sp_update_job and sp_start_job.Does anyone have an idea as to why this is happening?Ken

How to add Just a delete word in the column of the destination table while the record is found to be deleted. while using the stored procdure with MERGE.

Posted: 04 Jun 2013 06:34 AM PDT

Hi Lowel, SSCChampion, SSCcrazy eights and other friends.I am still finding problem in regards of my early posts.My issue is: [b]When I use the following stored procedure , to merge data between source and destination , I also need to add fix value/string in the one column called STATUS in the destination.This value/string is "Deleted", I need to add this values when merge query finds that particular record should be deleted via `WHEN NOT MATCHED BY THE SOURCE`` sql command.I actully don`t want to delete that records but just want to add delete word in that record`s column which is status.I need your guidance.[/b].thanks. my procudre is on http://www.sqlservercentral.com/articles/EDW/77100/or CREATE PROCEDURE [TEST_SCHEMA].[generate_merge] @SrcDB SYSNAME, --Name of the Source database @SrcSchema SYSNAME, --Name of the Source schema @SrcTable SYSNAME, --Name of the Source table @TgtDB SYSNAME, --Name of the Target database @TgtSchema SYSNAME, --Name of the Target schema @TgtTable SYSNAME, --Name of the Target table @predicate SYSNAME = null, --Comma-delimited list (between single qutoes '') of items to match when automatic predicate is not possible. [Does not remove leading/trailing spaces since column names can have leading/trailing spaces] @match_qual SYSNAME = null, --Pass in additional qualifier logic for the 'WHEN MATCHED THEN' portion of the MERGE statment @not_match_qual SYSNAME = null, --Pass in additional qualifier logic for the 'WHEN NOT MATCHED THEN' portion of the MERGE statment @debug SMALLINT = null --Pass in 1 to kick out just the MERGE statement text without executing itASBEGIN DECLARE @merge_sql NVARCHAR(MAX); --overall dynamic sql statement for the merge DECLARE @columns_sql NVARCHAR(MAX); --the dynamic sql to generate the list of columns used in the update, insert, and insert-values portion of the merge dynamic sql DECLARE @pred_sql NVARCHAR(MAX); --the dynamic sql to generate the predicate/matching-statement of the merge dynamic sql (populates @pred) DECLARE @pk_sql NVARCHAR(MAX); --the dynamic sql to populate the @pk table variable that holds the primary keys of the target table DECLARE @updt NVARCHAR(MAX); --contains the comma-seperated columns used in the UPDATE portion of the merge dynamic sql (populated by @columns_sql) DECLARE @insert NVARCHAR(MAX); --contains the comma-seperated columns used in the INSERT portion of the merge dynamic sql (populated by @insert_sql) DECLARE @vals NVARCHAR(MAX); --contains the comma-seperated columns used in the VALUES portion of the merge dynamic sql (populated by @vals_sql) DECLARE @pred NVARCHAR(MAX); --contains the predicate/matching-statement of the merge dynamic sql (populated by @pred_sql) DECLARE @pred_param NVARCHAR(MAX) = @predicate; --populated by @predicate. used in the dynamic generation of the predicate statment of the merge DECLARE @pred_item NVARCHAR(MAX); --used as a placeholder of each individual item contained within the explicitley passed in predicate DECLARE @done_ind SMALLINT = 0; --used in the dynamic generation of the predicate statment of the merge DECLARE @dsql_param NVARCHAR(500); --contains the necessary parameters for the dynamic sql execution /************************************************************************************************ * Generate the dynamic sql (@columns_sql) statement that will * * populate the @columns temp table with the columns that will be used in the merge dynamic sql * * The @columns table will contain columns that exist in both the source and target * * tables that have the same data types. * ************************************************************************************************/ --Create the temporary table to collect all the columns shared --between both the Source and Target tables. DECLARE @columns TABLE ( table_catalog VARCHAR(100) NULL, table_schema VARCHAR(100) NULL, table_name VARCHAR(100) NULL, column_name VARCHAR(100) NULL, data_type VARCHAR(100) NULL, character_maximum_length INT NULL, numeric_precision INT NULL, src_column_path VARCHAR(100) NULL, tgt_column_path VARCHAR(100) NULL ) /************************************************************************************************ * Generate the dynamic sql (@columns_sql) statement that will * * populate the @columns temp table with the columns that will be used in the merge dynamic sql * * The @columns table will contain columns that exist in both the source and target * * tables that have the same data types. * ************************************************************************************************/ set @columns_sql = 'SELECT tgt.table_catalog, tgt.table_schema, tgt.table_name, tgt.column_name, tgt.data_type, tgt.character_maximum_length, tgt.numeric_precision, (src.table_catalog+''.''+src.table_schema+''.''+src.table_name+''.''+src.column_name) AS src_column_path, (tgt.table_catalog+''.''+tgt.table_schema+''.''+tgt.table_name+''.''+tgt.column_name) AS tgt_column_path FROM ' + @TgtDB + '.information_schema.columns tgt with(nolock) INNER JOIN ' + @SrcDB + '.information_schema.columns src with(nolock) ON tgt.column_name = src.column_name AND tgt.data_type = src.data_type AND (tgt.character_maximum_length IS NULL OR tgt.character_maximum_length >= src.character_maximum_length) AND (tgt.numeric_precision IS NULL OR tgt.numeric_precision >= src.numeric_precision) WHERE tgt.table_catalog = ''' + @TgtDB + ''' AND tgt.table_schema = ''' + @TgtSchema + ''' AND tgt.table_name = ''' + @TgtTable + ''' AND src.table_catalog = ''' + @SrcDB + ''' AND src.table_schema = ''' + @SrcSchema + ''' AND src.table_name = ''' + @SrcTable + ''' ORDER BY tgt.ordinal_position' --execute the @columns_sql dynamic sql and populate @columns table with the data INSERT INTO @columns exec sp_executesql @columns_sql /************************************************************************************** * Create the temporary table to collect all the primary key columns * * These primary key columns will be filtered out of the update portion of the merge * * We do not want to update any portion of clustered index for performance * **************************************************************************************/ DECLARE @pk TABLE ( column_name VARCHAR(100) NULL ); set @pk_sql = 'SELECT ' + 'ccu.column_name ' + 'FROM ' + @TgtDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_tgt with(nolock) ' + 'INNER JOIN ' + @TgtDB +'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu with(nolock) ' + 'ON tc_tgt.CONSTRAINT_NAME = ccu.Constraint_name ' + 'AND tc_tgt.table_schema = ccu.table_schema ' + 'AND tc_tgt.table_name = ccu.table_name ' + 'WHERE ' + 'tc_tgt.CONSTRAINT_TYPE = ''Primary Key'' ' + 'and tc_tgt.table_catalog = ''' + @TgtDB + ''' ' + 'and tc_tgt.table_name = ''' + @TgtTable + ''' ' + 'and tc_tgt.table_schema = ''' + @TgtSchema + ''' ' INSERT INTO @pk exec sp_executesql @pk_sql /**************************************************************************************** * This generates the matching statement (aka Predicate) statement of the Merge. * * If a predicate is explicitly passed in, use that to generate the matching statement. * * Else execute the @pred_sql statement to decide what to match on and generate the * * matching statement automatically. * ****************************************************************************************/ IF @pred_param is not null -- If a comma-separated list of predicate match items were passed in via @predicate BEGIN -- These next two SET statements do basic clean-up on the comma-separated list of predicate items (@pred_param) -- This basic clean-up only removes leading/trailing commas that are passed in. -- This DOES NOT remove leading/trailing spaces since column names can have leading/trailing spaces (as stupid as that sounds) -- If the user passed in a predicate that begins with a comma, strip it out SET @pred_param = case when SUBSTRING(ltrim(@pred_param),1,1) = ',' then SUBSTRING(@pred_param,(charindex(',',@pred_param)+1),LEN(@pred_param)) else @pred_param end -- If the user passed in a predicate that ends with a comma, strip it out SET @pred_param = case when SUBSTRING(rtrim(@pred_param),LEN(@pred_param),1) = ',' then SUBSTRING(@pred_param,1,LEN(@pred_param)-1) else @pred_param end -- End clean-up of (@pred_param) -- loop through the comma-seperated predicate that was passed in via the paramater and construct the predicate statement WHILE (@done_ind = 0) BEGIN set @pred_item = case when charindex(',',@pred_param) > 0 then SUBSTRING(@pred_param,1,(charindex(',',@pred_param)-1)) else @pred_param end set @pred_param = SUBSTRING(@pred_param,(charindex(',',@pred_param)+1),LEN(@pred_param)) set @pred = case when @pred IS NULL then (coalesce(@pred,'') + 'src.[' + @pred_item + '] = ' + 'tgt.[' + @pred_item + ']') else (coalesce(@pred,'') + ' and ' + 'src.[' + @pred_item + '] = ' + 'tgt.[' + @pred_item + ']') end set @done_ind = case when @pred_param = @pred_item then 1 else 0 end END END ELSE -- If an explicite list of predicate match items was NOT passed in then automatically construct the predicate -- match statement based on the primary keys of the Source and Target tables BEGIN set @pred_sql = ' SELECT @predsqlout = COALESCE(@predsqlout+'' and '','''')+' + '(''''+''src.''+column_name+'' = tgt.''+ccu.column_name)' + ' FROM ' + @TgtDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc_tgt with(nolock) ' + ' INNER JOIN ' + @TgtDB +'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu with(nolock) ' + ' ON tc_tgt.CONSTRAINT_NAME = ccu.Constraint_name' + ' AND tc_tgt.table_schema = ccu.table_schema' + ' AND tc_tgt.table_name = ccu.table_name' + ' WHERE' + ' tc_tgt.CONSTRAINT_TYPE = ''Primary Key''' + ' and tc_tgt.table_catalog = ''' + @TgtDB + '''' + ' and tc_tgt.table_name = ''' + @TgtTable + '''' + ' and tc_tgt.table_schema = ''' + @TgtSchema + '''' set @dsql_param = ' @predsqlout nvarchar(max) OUTPUT' EXEC sp_executesql @pred_sql, @dsql_param, @predsqlout = @pred OUTPUT; END /************************************************************************* * A Merge statement contains 3 seperate lists of column names * * 1) List of columns used for Update Statement * * 2) List of columns used for Insert Statement * * 3) List of columns used for Values portion of the Insert Statement * **************************************************************************/ --1) List of columns used for Update Statement --Populate @updt with the list of columns that will be used to construct the Update Statment portion of the Merge set @updt = CAST((SELECT ',tgt.[' + column_name + '] = src.[' + column_name + ']' FROM @columns c where c.column_name != 'meta_orignl_load_dts' --we do not want the original time the row was created to be overwritten and not exists (select 'x' from @pk p where p.column_name = c.column_name) --we do not want the primary key columns updated for performace FOR XML PATH('')) AS NVARCHAR(MAX) ) --2) List of columns used for Insert Statement --Populate @insert with the list of columns that will be used to construct the Insert Statment portion of the Merge set @insert = CAST((SELECT ',' + '[' + column_name + ']' FROM @columns FOR XML PATH('')) AS NVARCHAR(MAX) ) --3) List of columns used for Insert-Values Statement --Populate @vals with the list of columns that will be used to construct the Insert-Values Statment portion of the Merge set @vals = CAST((SELECT ',src.' + '[' + column_name + ']' FROM @columns FOR XML PATH('')) AS NVARCHAR(MAX) ) /************************************************************************************* * Generate the final Merge statement using the following... * * -The parameters (@TgtDB, @TgtSchema, @TgtTable, @SrcDB, @SrcSchema, @SrcTable) * * -The predicate matching statement (@pred) * * -The update column list (@updt) * * -The insert column list (@insert) * * -The insert-value column list (@vals) * * -Filter out Primary Key from the update (updating primary key essentially * * turns the update into an insert and you lose all efficiency benefits) * *************************************************************************************/ SET @merge_sql = (' MERGE into ' + @TgtDB + '.' + @TgtSchema + '.' + @TgtTable + ' tgt ' + ' using ' + @SrcDB + '.' + @SrcSchema + '.' + @SrcTable + ' src ' + ' on ' + @pred + ' when matched ' + coalesce(@match_qual,'') + ' then update ' + ' set ' + SUBSTRING(@updt, 2, LEN(@updt)) + ' when not matched ' + coalesce(@not_match_qual,'') + ' then insert (' + SUBSTRING(@insert, 2, LEN(@insert)) + ')' + ' values ( ' + SUBSTRING(@vals, 2, LEN(@vals)) + ')' + ' when not matched by source then delete;' --ADDED THIS LINE TO DELTE ROWS OFF OF TARGET TABLE THAT DO NOT EXIST IN SOURCE ); --Either execute the final Merge statement to merge the staging table into production --Or kick out the actual merge statement text if debug is turned on (@debug=1) IF @debug = 1 BEGIN -- If debug is turned on simply select the text of merge statement and return that select @merge_sql; END ELSE BEGIN -- If debug is not turned on then execute the merge statement EXEC sp_executesql @merge_sql; END END;

How To Prevent New Line/Carriage Return In Query Results Within SSMS

Posted: 04 Jun 2013 03:53 AM PDT

I realize there is porbably a term or common phrase to describe this ( something better then New LIne / Carriage Return) but I don't know what it is. The problem is that if the source data includes CHAR(10) and CHAR(13) in a VARCHAR field then when I include that field in my query where the destination is GRID then the contents of that field that come after the CHAR(10) & CHAR(13) are paced in column 1 of the next row in the grid. I know that CHAR(10) and CHAR(13) are both within the VARCHAR field because I;ve searched for and found them and so I can manually strip them from teh results via teh REPLACE() function but I swear that this did not use to be an issue for me with SSMS Query results. I'm also certain that the CHAR(10) and CHAR(13) have always been stored in various VARCHAR columsn of various tables of the database I work with and in the past have never caused this wrapping effect in query results. So why in the world are the CHAR(10) and CHAR(13) instances causing my query results to go to new rows and in the first column insetad of either not being a factor at all or wrapping to a new line within in the same row and column?

Best way to find certain records.

Posted: 17 May 2013 08:48 AM PDT

I have to find certain data from a table based on date range & and activity.So, if a records is 60 days old for a certain activity and do not fall within 90 & 120 days for other activity and do not have anything less than 60 days as create date, we should delete it.The script is to crete table & data is as below.So from the given example we have to delete only client id 5.[code="sql"]Create table TestPurgeClient(ClientId int,ActivityId int,CreateDate Datetime)--select * from TestPurgeClient--Allinsert into TestPurgeClient values(1,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(1,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(1,34,'2013-01-17 00:00:00.000')-- 60 day + Currentinsert into TestPurgeClient values(2,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(2,72,GETDATE())--60 + 90 dayinsert into TestPurgeClient values(3,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(3,33, '2013-02-16 00:00:00.000')--All + currentinsert into TestPurgeClient values(4,72, '2013-03-18 00:00:00.000')insert into TestPurgeClient values(4,33, '2013-02-16 00:00:00.000')insert into TestPurgeClient values(4,34,'2013-01-17 00:00:00.000')insert into TestPurgeClient values(4,34,GETDATE())--60 Day Onlyinsert into TestPurgeClient values(5,72, '2013-03-18 00:00:00.000')[/code]

How To Prevent New Line/Carriage Return In Query Results Within SSMS

Posted: 04 Jun 2013 04:32 AM PDT

I realize there is probably a term or common phrase to describe this ( something better then New LIne / Carriage Return) but I don't know what it is. The problem is that if the source data includes CHAR(10) and CHAR(13) in a VARCHAR field then when I include that field in my query where the destination is GRID then the contents of that field that come after the CHAR(10) & CHAR(13) are paced in column 1 of the next row in the grid. I know that CHAR(10) and CHAR(13) are both within the VARCHAR field because I;ve searched for and found them and so I can manually strip them from teh results via teh REPLACE() function but I swear that this did not use to be an issue for me with SSMS Query results. I'm also certain that the CHAR(10) and CHAR(13) have always been stored in various VARCHAR columsn of various tables of the database I work with and in the past have never caused this wrapping effect in query results. So why in the world are the CHAR(10) and CHAR(13) instances causing my query results to go to new rows and in the first column insetad of either not being a factor at all or wrapping to a new line within in the same row and column?

Clustered index creating performance issues

Posted: 04 Jun 2013 07:55 AM PDT

Hi everyone. I'm going through my index usage and finding some cases where the clustered indexes that I created might be doing more harm than good. The four listed here are the worst performers. I think that that the data is bulk loaded and as a result, I may be impacting performance by sorting the data with clustered indexes. These numbers represent a typical workload start to finish.Table 1 clustered index: 34 total reads, 472,498 total writesTable 2 clustered index: 1578 total reads, 862,368 total writesTable 3 clustered index: 0 total reads, 1,759,849 total writesTable 4 clustered index: 3,551,723 total reads, 5,467,635 total writesIs there a chance that converting these back to heaps and selectively creating nonclustered indexes might be a performance boost?Thanks,Howard

Database growth

Posted: 04 Jun 2013 06:30 AM PDT

I have same database across 2 different servers. One of them has it (mdf file) as 200GB and another one has it has 160GB. The one with 200GB is simple recovery model and another one with 160GB is Full with log backup taken every 2 hours. I am wondering why there is so much difference in size when the data is essentially the same. Now, I remember one time one of my team member did index rebuild on the one with 200GB db server. I am not sure if that has to do anything with its growth. What can I do to bring the size down on 200GB db?

what are a given user's connection properties?

Posted: 04 Jun 2013 06:47 AM PDT

I'm having trouble with my keyword searching today, hopefully someone can help. I'm trying to find out what a given SPID's connection properties are? Something like what DBCC UserOptions return but for a given SPID. Specifically, what is the isolation level of a given SPID. Thanks in advance!!!!

Debugging SQL server Stored Procedures

Posted: 19 Oct 2010 07:09 PM PDT

HI Everyone,Any one out there,Please advise me the alternate permissions other than the SYSADMIN to grant the user to debug sql stored procedures

How to delete the old post in this forum?

Posted: 04 Jun 2013 05:54 AM PDT

How can I delete my previous posts for which I got the answer from major member of this forum!

Allowing users to directly query data mart

Posted: 04 Jun 2013 03:55 AM PDT

I am looking for resources to help evaluate options for allowing users to query our data mart directly. (T-SQL). These are not experienced users and we can't risk having them block existing processes or using too many server resources. This is a large DM. If someone can point me to some resources or articles addressing this I'd appreciate it. I'm aware of the options but not the pros/cons of each regarding this scenario. Additional hardware is not an option but new instances/databases are.Specs:SQL Server 2008 R2 active/passive 2 node cluster40TB database2TB largest fact tableThanks.-Brian

Creatin of role & assign users

Posted: 04 Jun 2013 02:31 AM PDT

One Employee have been asked to Create a User defined Role named (ReadOnlyXYZ)then create individual 90 login account :w00t:with read only access to a Specific Database..:w00t:can anyone help in automating this??is their any easy process for it...

What are the options for splitting/reducing large mdf files?

Posted: 04 Jun 2013 12:37 AM PDT

I have a database with 2 mdf files. One file is 750gb and the other file is 112gb. There are performance issues with backing up this database and the database will continue to grow larger. What are the options in looking into managing large data files? Is there some mechanism for splitting it up?Any input is appreciated!

Go from SQL 2008 R2 standard edition to Developer?

Posted: 04 Apr 2011 08:36 AM PDT

Is it possible to 'downgrade' SQL 2008 R2 from standard edition to Developer edition? I'm working on developing a long-term SQL Server infrastructure for my company. We're evaluating using the Enterprise edition, but my bet at this point is that we'll go with Standard at least short-term. If, at some point in the future we decide to go to Enterprise, we'd want to (A) upgrade our production Standard installs to Enterprise, and (B) change our non-production installs to Developer to reduce licensing costs. (I'd be OK with the production environments having slightly less capabilities, since Developer is now equivalent to Data Center)From what I've seen ([url]http://msdn.microsoft.com/en-us/library/ms143393.aspx[/url]), it doesn't look like what I'd like to do is possible. Hoping someone knows otherwise. I'd like to avoid having to install SQL Server from scratch if we changed editions. Thanks....Brian Kukowski

Taks SSIS-package is not executed at all

Posted: 04 Jun 2013 02:48 AM PDT

Hi,there is a for-each-loop-container within a simple script task to set a variable and two following data-flow-tasks.Between Script- and data-flow-tasks there are Precedence Constraints, wich check the value of variable.That all works properly, but there is another task behind the data-flow-tasks wich is not executed at all. Thanks in advance for any hint.Best regardsMatze

Help with next date in SQL query

Posted: 17 May 2013 10:04 PM PDT

What I need is to be able to find out when a customer next called in from the date I have select with the next date & time on any Purchase, does don't need to be same. It's like using MIN & MAX but that does not work on this query as it select the same date & time for both.SELECT ID, Name, CallDateTime, Num, RCFROM HistoryWHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')As you can see in the query above that all the data is in one overall table called History, this records all the purchases.So I want to know that if a customer after the 1/05/2013 called in with the outcome of called what was he next purchases date, some customer might not have come so that can stay blank. So the query is like this nowID Name CallDateTime Num RC3936803 Name1 01/05/2013 11:16:27 84 Called5211387 Name2 01/05/2013 12:14:21 604 Called5185689 Name3 01/05/2013 12:15:28 298 Called4811923 Name4 01/05/2013 12:29:36 170 Calledbut i also want it to show the below,ID Name CallDateTime Num RC Next CallDateTime Total Number Of Days3936803 Name1 01/05/2013 11:16 84 Called 04/05/2013 11:16 35211387 Name2 01/05/2013 12:14 604 Called 04/05/2013 12:14 35185689 Name3 01/05/2013 12:15 298 Called 04/05/2013 12:15 34811923 Name4 01/05/2013 12:29 170 Called 04/05/2013 12:29 3This is the query I have at the moment BUT not show how to add two more columns to it they are next called in date & time after the first called in. I want it to show the next time & date does not matter what the RC code is next visit.So I want mine output to show the below, adding the next called time & date and how many days it take from the first date to the next date,

Who Dropped Database

Posted: 03 Jun 2013 11:57 PM PDT

Hi Team,How can i know who dropped database in sql server...Plz bit urgent.

List of queries using MAXDOP

Posted: 29 May 2013 09:47 AM PDT

Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config:4 CPUS - each 8 Core - 16 logical processorsCan you please help me determine if it is possible to find which queries are utilizing MAXDOP for query processing?ThanksJagan K

Application not Working

Posted: 04 Jun 2013 12:57 AM PDT

Here we have an application which uses a Stored procedure.The user have created a SP but the application fails to pick the SP..ALTER PROCEDURE [INDIADNS\Name.sirname].[EMPLOYEE_SP]Note - INDIADNS - Its the Domain name...Now problem here is... this issue never came.. coz when the same user use to create a SP the application picks the SP well & do execute it well..Today when debugging i came to know that due to the Domain name & username before SP the problem occurs... Please suggest

differences

Posted: 04 Jun 2013 12:52 AM PDT

what are the differences b\w 32 bit vs 64 bit in sql ?

Database Virtualisation Tool

Posted: 03 Jun 2013 11:52 PM PDT

Hi,I would like to know if anyone has come across or used Delphix virtualised tool?Thanks

SQL Server 2000 to SQL Server 2008 Migration and Performance

Posted: 03 Jun 2013 11:45 PM PDT

Hi, am new to sql server dba and recently my client has moved his db's from SQL 2000 to SQL 2008 through Detach and Attach. We have run the update statistics, but we have not found any improvement in the report generation. The reports are getting generated from dynamic queries, and now, am looking how to improve the performance from SQL Server side, not on the code. The code part will come later. Need suggestions on what settings do I need to do on SQL Server 2008.

SSIS Package execution progress

Posted: 04 Jun 2013 12:10 AM PDT

HI,We recently upgraded our servers to 2012 and when running SSIS packages from cmd it's giving execution progress of each and every data flow task which is pretty long..before we never got these execution progress outputs. So is there a way to avoid these execution progress list for each and every dataflow task?Thank you!

SSRS - reports rendering incorrectly

Posted: 02 Jun 2013 05:16 PM PDT

Hi allI am currently having this problem on SSRS rending incorrectly.The problem is basicall that reports do not display correctly in Chrome, Firefox, Safari browsers etc – the IFRAME's height defaults to a few hundred pixels, so you only see the top 2 inches of the report. There are artlices which suggest changes to the ReportViewer.aspx file found under SSRS directory. I am not confortable making changes to SQL system files, but if it has to get done, then so be it. Other than making a change to this file, is there any other options i can look at?thanks all for inputs!!!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog