[SQL Server] Efficiently Reuse Gaps in an Identity Column

Efficiently Reuse Gaps in an Identity Column

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[T-SQL] How To Get a row(date) for each month

---

• How To Get a row(date) for each month
• .sp_send_dbmail sending an XML File dashes in front of -<Label> ??
• Optimize SP
• String to date conversion
• Transaction Isolation Level with CTE
• Using between in where clause from subquery
• Conversion failed when converting from a character string to uniqueidentifier
• Converting nvarchar value to int
• Select full month number (with 0)

How To Get a row(date) for each month Posted: 12 Jun 2013 10:38 PM PDT Hi All, I need to get a minimum month and year from a table and then a row for every month/year up to today.How to I achieve that, here's the query I'm using below:Select MIN(DATENAME(MONTH, dtePostedToWebsiteDate)) as MinMonth ,MIN(Year(dtePostedToWebsiteDate)) as MinYear ,'' as [RowForEachMonth] --??From dtlVacancyPostAuditWhere bitPostToWebSite = 1Please help.ThanksTeee

.sp_send_dbmail sending an XML File dashes in front of -&lt;Label&gt; ?? Posted: 13 Jun 2013 12:43 AM PDT Hi using .sp_send_dbmail to email an xml fileNew at XML files, but my sample to recreate looks like this: <CLUB> <REFERRAL> <field1>6.6.11</field1> <field3>Ongoing</field3> File looks ok (so far) except I have dashes in front- <CLUB> - <REFERRAL> <field1>6.6.11</field1> <field3>Ongoing</field3> ThanksJoe

Optimize SP Posted: 12 Jun 2013 11:19 PM PDT Hi!Next, we need to unify SP 2 has a size pretty damn considerably. =) Let the following scenario: Sp [A]Sp [B] The Sp [A] has a SELECT with 10 tables with inner join and left. Both [A] as [B] are very complex and require a legal use of the server. Hence the question, I need a unified report of [A] and [B] .. How best to run? Thank you.

String to date conversion Posted: 12 Jun 2013 09:51 PM PDT Hi all experts,Is it possible in SQL Server to convert a string type to date like[code="sql"]DECLARE @OrgDate Nvarchar(50)='2013-06'select CAST(@OrgDate as DATE)[/code]The above is wrong. But i would like to know whether there is any way of doing this. Like in Oracle we have TO_DATE() function.

Transaction Isolation Level with CTE Posted: 12 Jun 2013 07:33 AM PDT How do you handle the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" with common table expressions? If I add that before the CTE, I get yelled at by SQL.[code="plain"]Msg 319, Level 15, State 1, Line 12Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.[/code]So I'm wondering, do I have to put it with a semi-colon – which runs ok – before the CTE or after the CTE and before the select statement? Does it matter?Thoughts or experiences?

Using between in where clause from subquery Posted: 12 Jun 2013 09:43 AM PDT I am trying to find out how to do the same thing I do in a join in a where clause.I have the following that works fine:[code]DECLARE @Client TABLE( ClientId int, ClientName varchar(100), ValidDate smalldatetime)DECLARE @ValidDates TABLE( ValidDateId int, StartDate smalldatetime, EndDate smalldatetime)INSERT @Client VALUES (1, 'Tom Jones', '02/15/2013')INSERT @Client VALUES (1, 'Larry Smith', '4/05/2013')INSERT @Client VALUES (1, 'Jerry Lowell', '07/25/2013')INSERT @ValidDates VALUES (1,'01/15/2013','03/30/2013')INSERT @ValidDates VALUES (2,'5/15/2013','08/30/2013')SELECT * FROM @Client clJOIN @ValidDates vdON cl.ValidDate BETWEEN vd.StartDate AND vd.EndDate[/code]But I need to do this in a where clause, possible using a subquery but how do I use the "BETWEEN" there?Thanks,Tom

Conversion failed when converting from a character string to uniqueidentifier Posted: 12 Jun 2013 08:24 AM PDT Hello,I am trying to do an update based on a join condition and I am getting the following error:Conversion failed when converting from a character string to uniqueidentifierupdate PJ set PJ.efg=1 from abc pj join hackthis HAH on Pj.JID=hah.TId where pj.lss=0JID is a uniqueIdentifier and TID is an nvarchar(50). And I know its erroring out on the join condition because of 2 different datatypes.Is there a way that I convert/cast and fix this?Thanks

Converting nvarchar value to int Posted: 12 Jun 2013 05:45 AM PDT I've a column name called "Codes" and the datatype is nvarchar. The data looks like X200, 516, XD1, YTG, 24ZY, 40Y, 01DXNow, I just need the numeric values from these data. If the value is all letters then I just want to display it as 0.From the above example, I just want to see it as 200, 516, 1, 0, 24,40, 01Any help is much appreciated,Thanks in advance.

Select full month number (with 0) Posted: 28 Feb 2013 05:38 PM PST Hi,I use[code="sql"]DATEPART(month, myDate)[/code]or[code="sql"]MONTH(myDate)[/code]the resut is: 2 (if myDate is 2013.02.03). I would like return: 02Do you have any solution for this?Thans!

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Using REPLACE in an UPDATE statement

Using REPLACE in an UPDATE statement

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server] Handling SQL Server Errors

Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server] Using Dynamic SQL in Stored Procedures

Using Dynamic SQL in Stored Procedures

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server 2008 issues] Modifying a system stored procedure

---

• Modifying a system stored procedure
• How to format the data in column in sql server
• BCP IN a csv file
• cpu Utilization is showing 100 on particular database
• Import flat files into SQL Server (apart from SSIS)
• Using SSIS To Import Data From A File To A Database
• Exec msdb.dbo.sp_send_dbmail (zipped and password protected) issue
• 2008 R2 install (Sharepoint integrated mode)
• Verify multiple bak files via tsql
• sql service startup account
• 3 instances of SQL on 1 vm VS 3 separate vm's
• Need a help in backup Encyption Certificate
• Queries are faster with Statistics Sampling rather than FULLSCAN?
• Converting a Non Standard Format Number to a Date
• sqlMonitor alert:sql server instance unreachable
• How to easily find which of our hundreds of servers has Analysis Services installed?
• Policy management
• Move dbs Identify Jobs
• SQL Query
• SQL Server Express - automatic Backup
• Bcckup & restore
• Partitioning an existing table in sql server 2008
• Select in multiple levels...
• Update stats
• How to update base on parameter vaues in other table
• database in suspect mode

Modifying a system stored procedure Posted: 12 Jun 2013 07:37 AM PDT I have inherited maintenance of a SQL Server (2008), and I want to modify some of the system stored procedures. These are user-defined system stored procedures (for example: sys.sp_customproc). I can only assume they were created as system procedures so they could be shared across multiple databases? But regardless, I need to modify them.Here is an example of one of them.[code="sql"]USE [msdb]GO/****** Object: StoredProcedure [sys].[sp_dbmmonitorhelpmonitoring] Script Date: 06/12/2013 13:16:52 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [sys].[sp_dbmmonitorhelpmonitoring] asbeginset nocount onif (is_srvrolemember(N'sysadmin') <> 1 ) begin raiserror(21089, 16, 1) return (1) enddeclare @freq_type int, -- 4 = daily @freq_interval int, -- Every 1 days @freq_subday_type int, -- 4 = based on Minutes @freq_subday_interval int, -- interval @job_id uniqueidentifier, @schedule_id int, @retention_period int, @jobname nvarchar( 256 )select @jobname = isnull( formatmessage( 32047 ), N'Database Mirroring Monitor Job' )select @job_id = job_id from msdb.dbo.sysjobs where name = @jobnameif (@job_id is null) -- if the job does not exist, error outbegin raiserror( 32049, 16, 1 ) return 1 endselect @schedule_id = schedule_id from msdb.dbo.sysjobschedules where job_id = @job_idselect @freq_type = freq_type, @freq_interval = freq_interval, @freq_subday_type = freq_subday_type, @freq_subday_interval = freq_subday_interval from msdb.dbo.sysschedules where schedule_id = @schedule_id-- If the frequency parameters are not what we expect then return an error-- Someone has changed the job schedule on usif (@freq_type <> 4) or (@freq_interval <> 1) or (@freq_subday_type <> 4)begin raiserror( 32037, 16, 1) return 1endselect @freq_subday_interval update_periodreturn 0end[/code]When I try to execute it, I get the error:Msg 208, Level 16, State 6, Procedure sp_dbmmonitorhelpmonitoring, Line 46 Invalid object name 'sys.sp_dbmmonitorhelpmonitoring'.My login is 'sa', I am mapped to the user 'dbo' in the [msdb] database. How do I modify this stored procedure?

How to format the data in column in sql server Posted: 12 Jun 2013 12:38 PM PDT Hi, Please help me, In my table have a one date column. It contains data in different formats .I want to set all these different formats into a single format. Like... Date 10.12.2012 ---in this '10' is Day and 12 is Month and then Year. 12-10-2012 10.12.2012 2012/10/12

BCP IN a csv file Posted: 12 Jun 2013 01:40 PM PDT Hi All,Couldn't find a plain BCP IN syntax for CSV files on the web or in here.Can someone tell me once and for all what is the syntax?Thank you!-MB

cpu Utilization is showing 100 on particular database Posted: 12 Jun 2013 11:43 AM PDT hi,WITH DB_CPU_StatsAS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time(Ms)]FROM sys.dm_exec_query_stats AS qsCROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle)WHERE attribute = N'dbid') AS epaGROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time(Ms)] DESC) AS [row_num],DatabaseName, [CPU_Time(Ms)], CAST([CPU_Time(Ms)] * 1.0 / SUM([CPU_Time(Ms)]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]FROM DB_CPU_StatsWHERE DatabaseID > 4 -- system databasesAND DatabaseID <> 32767 -- ResourceDBORDER BY row_num OPTION (RECOMPILE); On single database cpu utilization 100 % .How to decrease cpu utilization on that database.?

Import flat files into SQL Server (apart from SSIS) Posted: 12 Jun 2013 09:41 AM PDT Which tool do you prefer to use to import flat files into SQL Server tables and why? We're trying to migrate some DTS packages into Stored Procedures but some contain data pumps which do these kind of imports.We've had lots of problems creating SSIS packages due to the lack of knowledge, that's why we're looking for alternatives.Thanks for all the ideas we might get.

Using SSIS To Import Data From A File To A Database Posted: 12 Jun 2013 04:07 AM PDT I am new to SSIS and I have been tasked to do use SSIS to import data from a file to our database.The file is delimited and has 46 columns. The file can contain 1,000,000 records. The test file I have contains 700,000.Each record in the file contains an ID. I need to take that ID, go to the database and see if the record already exists in the database. If it exists I need to update the record in the database. If it doesn't exist add the new record to the database.So far I set up a Flat File Source and I can see all of my columns and data. I'm not sure where to go from here.

Exec msdb.dbo.sp_send_dbmail (zipped and password protected) issue Posted: 12 Jun 2013 07:42 AM PDT Is it possible to send a csv file password protected and zipped and sent with msdb.dbo.sp_send_dbmail?

2008 R2 install (Sharepoint integrated mode) Posted: 12 Jun 2013 04:52 AM PDT How can set the sharepoint integrated mode (on) for Reporting Service if Reporting Service is already install on the server, would the DVD has to be loaded again or is there a value with I can turn on?

Verify multiple bak files via tsql Posted: 12 Jun 2013 03:26 AM PDT I backed up a database via Red Gate and used the sqb2mtf to convert the backup to be recognized by SQL (.bak file). In doing so, this created 9 bak files. Now I am attempting to VERIFY the backups via TSQL and am not having any luck.The command I am attempting to run is:RESTORE VERIFYONLYFROM DISK = 'F:\Temp\FULL_DbBackup_00.bak'In doing so I get the following error:Msg 3132, Level 16, State 1, Line 1The media set has 9 media families but only 1 are provided. All members must be provided.Msg 3013, Level 16, State 1, Line 1VERIFY DATABASE is terminating abnormally.I have been Googling this with no luck and am needing to verify all 9 of these bak files (00-08). I found that there is a WITH FILE = n switch that I can not get to work and I found where someone posted some syntax on using the backup command to split a backup into multiple files.Can someone please advise on the easiest way to do this? Any and all help will be greatly appreciated!!Thanks in Advance...

sql service startup account Posted: 12 Jun 2013 05:18 AM PDT Hi Team,I have sql service account which does not have sa rights but have all required domain rights and there is seperate account for agent service and this account has sa rights.When backup job runs it does not delete the old backup files.When I grant sa rights to sql service account it does.So my question is does sql service account always need 'sa' rights?

3 instances of SQL on 1 vm VS 3 separate vm's Posted: 12 Jun 2013 05:36 AM PDT We have a situation on one of our tier 3 sql servers where we want to move it to a vm. It currently has SQL Server 2005, 2008 R2 (2005 is named). We want to move this to a vm and upgrade some of the databases to 2012. So the thought was to have 3 separate VMs instead of just 1 with 3 instances. Thoughts?

Need a help in backup Encyption Certificate Posted: 12 Jun 2013 05:27 AM PDT HI ..I am having trouble with keeping backup of encryption certificate.I created database encryption key with folowing query.[b]Use TestGO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SQLServerTDECertificate GO[/b]but now SQL server not allowing me to do the backup.[b]BACKUP CERTIFICATE SQLServerTDECertificate TO FILE = 'C:\temp\TDE\SQLServerTDECertificate.cert' WITH PRIVATE KEY ( FILE = 'C:\temp\TDE\SQLServerTDECertificateKey.key', ENCRYPTION BY PASSWORD = '!@90jujujuijkop*&^jni@!') GO[/b][b]error message:[/b]Msg 15151, Level 16, State 1, Line 1Cannot find the certificate 'SQLServerTDECertificate', because it does not exist or you do not have permission.Please help me how I can do the backup.

Queries are faster with Statistics Sampling rather than FULLSCAN? Posted: 12 Jun 2013 03:09 AM PDT I have a really odd issue that I don't understand. We have a 3rd party Excel add-in that talks to another 3rd party application (ERP system) that allows adhoc reporting in Excel for some of our "power users". Over the past couple of months since we've been using the add-in, I've had users report that it is taking a long time to pull all the data into the spreadsheets.Unfortunately, it appears that the way the Excel software works is by sending individual queries instead of building a set and returning that. So it will execute this one statement 2500 times. I can't change how the 3rd party software works. One of the queries that is executed looks like this:[code]SELECT SUM(A.AMOUNTMST) FROM LEDGERTRANS A WHERE ((DATAAREAID IN (@P1) ) AND (((((TRANSDATE>=@P2) AND (TRANSDATE<=@P3)) AND (((ACCOUNTNUM=@P4) OR (ACCOUNTNUM=@P5)) OR (ACCOUNTNUM=@P6))) AND (DIMENSION=@P7)) AND (DIMENSION2_ LIKE @P8 ESCAPE '\' )))[/code]All 2500+ queries look similar to that, but the only thing that changes may be the number of items in the WHERE clause (there sometimes might only be one AccountNum and sometimes there may be 10+ AccountNum's).On that database, we update all STATISTICS with FULLSCAN daily. The LedgerTrans table in our environment has 11 million records.When the users report issues, the only thing I do is type "UPDATE STATISTICS LedgerTrans", which does a sampling. If I look at the details, it only sampled 137K rows, which is only ~1% of the rows.I managed to capture a SQL Profiler trace of the issue to try to compare execution plans, but I'm still confused. Before I do my "fix", the query formatted above had a duration of 2944 in SQL Profiler (which I assume is 3 seconds). After the fix, the duration is 4 for the same query. Attached are the two execution plans that I captured in SQL Profiler using the Showplan XML event.I don't understand how the simple Index Seek/Key Lookup execution plan is slower than the more complicated one with all of the scalars and the same Index Seek/Key Lookup.I don't think the system is under heavy load or blocking when the users report the slowness because it is 100% of the time (for the past 10+ times we've had this issue) resolved immediately by running that UPDATE STATISTICS command. I've use Remote Desktop Services shadowing and watched the add-in slowly progress through it's computations at a rate of 1+ minutes per 1 percent of activity, and in the middle of that worksheet refresh, I ran the UPDATE statistics command and it immediately started doing 1% of the total calculations every second.

Converting a Non Standard Format Number to a Date Posted: 12 Jun 2013 01:19 AM PDT I have dates stored on an AS400 platform in the following format that I need to convert to a standard date in SQl so I can use datediff in my select statements. Current Format: CYYMMDD example: 1130531 = 05/31/2013 in this format, the C is either 0 (for 1900) or 1 (for 2000)Desired Format: 05/31/2013All the articles I have found in the forums deal with a properly formated set of digits. Any help would be greatly appreciated!

sqlMonitor alert:sql server instance unreachable Posted: 12 Jun 2013 03:10 AM PDT Hi EveryoneOn our monitoring tool sqlMonitor we got an alert called 'sql server instance unreachable' which lasted for about 2 minutes. I have checked the Cpu queue length for this period and it spikes at around a value of 17 and the counter 'Machine:Processot time' peaks 100% and stays there for about 1 minute. How can i find out what caused this as having the data is good but i need a way of analyzing the query that may have caused this ?

How to easily find which of our hundreds of servers has Analysis Services installed? Posted: 12 Jun 2013 02:12 AM PDT Hi All,Someone asked the question today "Just how many of our servers have AS installed?" and I didn't know.Out of 300 servers (mainly SQL 2008) I reckon maybe 10 have AS installed.I was hoping I could just fire some t-sql at our central management server and get a result back but I can't find anywhere within SQL that this info is stored?At the moment it's looking to me like it'll be easier for me to do it with a powershell script and just feed that an input file that's lists all our servers and look for either a partial Service or AMO name.Has anyone else had to do this and found a neat way to do it?Many thanks.

Policy management Posted: 12 Jun 2013 01:59 AM PDT Hi,I have not created the job syspolicy_purge_history, it has been created by default in sql server 2008.Can anyone please tell me what is syspolicy_purge_history? and Is there any effect , if I disable the job, sorry I am new in sql server 2008.Thanks in advance

Move dbs Identify Jobs Posted: 10 Jun 2013 08:27 PM PDT I'm embarking on a project to migrate some dbs in my environment. Moving the db logins etc is straight forward. But how do I make sure the relevant jobs are moved to.Can MSDB be queried or do I need to set up profilwer.

SQL Query Posted: 11 Jun 2013 08:10 PM PDT Hi All,I have two parameters start and endweek which shows the measure column weekly from Monday -until sunday.Say 13/05 --- value 10020/05 -- value 20027/05 -- value 300But I need the query to be added weekly with another column commencing from April 1st 2013 until EndweekWeek Total13/05/2013 13/05/2013 weeks value + Week commnecing april 1st 2013 until May 12ths value20/05/2013 20/05/2013 weeks value +Week commnecing april 1st 2013 until May 19ths value27/05/2013 27/05/2013 weeks value +Week commnecing april 1st 2013 until May 26ths valueany help pls? thankks

SQL Server Express - automatic Backup Posted: 20 Feb 2013 10:50 PM PST Hi,I have SQL Server 2008 R2 Express Edition on a server.I need to backup my database (small db) on a daily basis.Since Express Edition does not allow automatic backups what can I use to do this backup?thanks

Bcckup & restore Posted: 11 Jun 2013 11:58 PM PDT Hi all,Can we restore sql server 2008 databases with sql 2012 database backups. Or backward compatibility is not possible in any version of sql.

Partitioning an existing table in sql server 2008 Posted: 11 Jun 2013 08:54 PM PDT Hi,I have a table of 600 million data. This table will grow monthly. We do monthly data load to this table.Column which i will use is LoadMonth. this will have the value like '201301','201304','201305'I would like to do a partitioning in this table.Please help me on this, what are the approaches/steps to create the partition.Regards,Karthik.

Select in multiple levels... Posted: 11 Jun 2013 09:07 PM PDT Hi,I have a query that now actually does exactly what I want it to and performs well. But the site has still low traffic and small amounts of data.I have never seen examples of queries with more than 2 "select levels" before, here I have "3 select levels", combined with 6 joins.Do you think this is going to be a problem as the site grows to tables with about 10 to 100 thousands rows ?SELECT DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1FROM(SELECT MAX(DenseGroup) OVER(PARTITION BY PropertyId) AS DistinctCompanyCount, DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId, Status, Day, LinkFormat, Updated, VardeFloat1FROM(SELECTDENSE_RANK() OVER (ORDER BY Categories.CatName,CompProp.VardeFloat1,CP2.Updated DESC,Company.CompanyId) AS DenseGroup,Categories.CatNamn,Categories.CatId,Company.CompanyId,Company.CompanyName,CompProp.PropertyId,Days.Status, Days.Day,OrgInfo.LinkFormat, CP2.Updated,CompProp.VardeFloat1FROM CompanyINNER JOIN CompProp ON Company.CompanyId = CompProp.CompanyId AND CompProp.PropertyId = 2 AND CompProp.State = 1INNER JOIN CompCat ON CompCat.CompanyId = CompProp.CompanyIdINNER JOIN Categories ON CompCat.CatId = Categories.CatIdINNER JOIN OrgInfo ON OrgInfo.UserId = Company.OrgIdLEFT OUTER JOIN Days ON Day.CompanyId = Company.CompanyId AND Days.Day >= '2013-06-10 00:00:00' AND Days.Day < '2013-06-15 00:00:00' LEFT OUTER JOIN CompProp AS CP2 ON Company.CompanyId = CP2.CompanyId AND CP2.PropertyId = 1WHERE Company.State = 1)AS PagingQ1)AS PagingQ2WHERE RankGrupp BETWEEN 1 AND 20ORDER BY CatName,VardeFloat1,Updated DESC,CompanyId,Day

Update stats Posted: 11 Jun 2013 08:44 PM PDT Hi ,In Production server we have executed updatestats for a database manually(which contains 5 tables) on Wensday and it took 1hr to comleted for all the tables.For the same database we created a updatestas job to run on sunday and it took around 15 hrs to complete.Can any one tell me how to find the cause for taking long time to run the job.

How to update base on parameter vaues in other table Posted: 11 Jun 2013 08:55 PM PDT Hi I have two table details and parameter.I want to update Type_score,Amount_score,Age_score acording to the criteria given in the #para table.Please help me as I need this urgently.drop table #TempCreate Table #Temp(Number Int,Type Varchar(100),Amount Int,Age Int,Type_score Varchar(100),Amount_score Int,Age_score Int)drop table #paracreate table #para(Id varchar(100),Type Varchar(100),valueFrom Int,valueTo Int,score Int)Insert Into #Temp(Number,Type,Amount,Age)Values (1,'Vannila',1000,20)Insert Into #Temp(Number,Type,Amount,Age)Values (2,'Strawberry',2500,40)Insert into #paraValues ('Product','Vannila','','',5), ('Product','Strawberry','','',1), ('Age','',18,25,5), ('Age','',26,30,1), ('Amount','',1000,2000,5), ('Amount','',2001,3000,1)Select * from #TempSelect * from #paraThanks in Advance!

database in suspect mode Posted: 11 Jun 2013 08:51 PM PDT Hii tried to put the database in emergency and then put into single user mode and ran dbcc check db with repair_allow_data_loss command but i am getting below error.... can any one helpMsg 7987, Level 16, State 1, Line 1System table pre-checks: Object ID 3 has chain linkage mismatch. (1:5177)->next = (1:3277), but (1:3277)->prev = (1:1289). Check statement terminated due to unrepairable error.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] How to delete duplicates/triplicates etc. except for the latest record based on date?

---

• How to delete duplicates/triplicates etc. except for the latest record based on date?
• Questions on resources and removing instances
• SQL to split row by date (split into multiple rows)
• displaying Columns through select
• Policy Management
• Need Help with the Error 'Subquery returned more than 1 value'.

How to delete duplicates/triplicates etc. except for the latest record based on date? Posted: 12 Jun 2013 11:24 AM PDT Hi,I have a table (t_account_code) with the following three columns - AccountCode, StartDate and EndDate. The table does not have an ID column (I know - it was inherited) and at this point, modifying the table is not an option, so please bear with me. With the combination of three columns, we get a PK.This table has multiple entries for the same AccountCode, and I want to delete all duplicate/triplicate/multiple entries EXCEPT for the latest one, based on StartDate.First, I've created a temp table with all account codes (unique) and their count, if greater than 1.I've tried the following query, first to do a select on the accounts that occur twice:select * from t_account_code tac1where AccountCode in (select AccountCode from #Tmp_accountcodes where count_num = 2)and StartDate = (select MIN(StartDate) from t_account_code tac2 where tac2.AccountCode = tac1.AccountCode)The distinct values of count_num are 2, 3 and 4.This works as intended and gives me the results I want (for duplicate values), but when I change it to a delete statement, I get a syntax error in the first row for the table alias - tac1. Of course, if I delete it, then my query won't work as I'm using tac1 in the sub-query.Any solution to this problem will be appreciated, even if it's a different query than the one above. I want to end up with unique AccountCodes in the table, with the latest StartDate, and delete any multiple ones with the older StartDate.E.g.AccountCode ---- StartDate ---- EndDate1234------------ 12/31/2012 ---- 3/30/20131234------------ 3/31/2013 ------4/29/20131234------------ 4/30/2013 -----12/31/20149876------------12/31/2012------3/30/20139876------------3/31/2013 -------5/30/2014I want to delete the first two rows for AccountCode '1234' and delete the first row for AccountCode '9876.' If an AccountCode occurs only once, then I want to leave it alone.Thanks.

Questions on resources and removing instances Posted: 12 Jun 2013 04:21 AM PDT Hi there,I've been working with SQL Databases for over 5 years now. Prior to this job, I has no working knowledge of SQL in general, so everything I've learned, I did by trial and error. I work for an accounting software company and last summer, we've introduced a SaaS solution to allow our customers (who subscribed to this service) to use the software on a remote virtual machine using an RDP connexion. To concept is as follow, we have 2 virtual machine for the software (a mid version and a high version of the software) as well as dedicated virtual machines for SQL servers. We're up to our 3rd SQL server now since we've respected the 50 instances limit rule for stand-alone SQL server. Each instance belongs to a customer with his security group in it to prevent the other customers to see his databases.So this is our basic setup but we've ran into a few issues that I will try to address with the best of my experiences:1- Removing Dead InstancesSaaS is not for everyone and a small percentage of our customers have realised that their Internet connexion is not stable enough to run this, so we've put them back to a local usage with the software. That being said, we're now stuck with SQL instances that are no longer active and that we can't reuses since they were named using the customer licence codes, so I've checked my options:a) Renaming the instances: I found multiple sites stating that trying to rename instances is not a great idea, can anyone here confirm this? If this is false, how would you go about renaming one?b) Removing the instances: Although this seems to work on servers with only a few instances, our first 2 SQL servers which are now full are not cooperating with the uninstallation. The uninstall process can be left running for over 2 hours and nothing happens, doesn't seem to be uninstalling anything (no error messages). Any ideas why it's doing that and is there another (safe) way to remove an SQL instance in Windows 2008 without affecting the working ones?c) Leaving them off: Disabling them and turning them off will free the resources but I am left with a question, this 50 instance rule, does it include inactive instances? If I got 45 active instances and 5 inactive ones, can I create 5 more without breaking anything?2- Managing ResourcesIf there's one thing I've learned quickly about SQL is that it loves RAM, there never seems to be enough of it. I've read that it consumes everything that it can to put in a pool for future usage. Now that's all great, but when you have 50 instances running, they all seem to be competing for resources. Now I had the idea of perhaps limiting the amount of RAM that an instance can use based on the number of users in an instance (we have customers with more than 1 user) and the the total size of the database(s). Not too much info I could find online regarding this, so does anyone here care to share their opinion on that strategy? If it's a good idea, what's the magic formula I should use to calculate the amount of RAM I should limit the instances based on the number of users and total size of their database(s)?We currently have 16GB of RAM per SQL server, will this be enough for this strategy?If this is plainly a bad idea, what would you recommend that would allow enough resources for all of the instances?Thank you for your time

SQL to split row by date (split into multiple rows) Posted: 11 Jun 2013 08:42 PM PDT I am looking for help with splitting a row into multiple rows based on dates overlapping.As an example, I have a table with the following data:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 31/12/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 1003, John Doe, Vacation, 19/08/2013, 23/08/2013, 1004, John Doe, Barclays, 01/01/2014, 31/01/2014, 505, John Doe, Santander, 06/01/2014, 25/01/2014, 506, John Doe, Vacation, 13/01/2014, 17/01/2014, 100I am looking to split the banking rows where they overlap with a vacation. So for example, the final result should be:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 16/06/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 100[b]3, John Doe, HSBC, 22/06/2013, 18/08/2013, 100[/b]4, John Doe, Vacation, 19/08/2013, 23/08/2013, 100[b]5, John Doe, HSBC, 24/08/2013, 31/12/2013, 100[/b]6, John Doe, Barclays, 01/01/2014, 12/01/2014, 50[b]7, John Doe, Barclays, 18/01/2014, 31/01/2014, 50[/b]8, John Doe, Santander, 06/01/2014, 12/01/2014, 50[b]9, John Doe, Santander, 18/01/2014, 25/01/2014, 50[/b]10, John Doe, Vacation, 13/01/2014, 17/01/2014, 100New rows after split are in bold. The Row ID should be unique although it doesn't need to be sequential. Any help or guidance would be appreciated.

displaying Columns through select Posted: 12 Jun 2013 04:26 AM PDT I have three tables A ID, A.name, A.LastNameB ID, B.Name,B.someothernameC ID, C.why,C.whyalsoall connected by ID'si have a query as below to get the dataselect A.Name,B.name,C.Why where A.ID = B.ID and C.ID = A.IDhowever i need the select in a way that if there are null values we show them as A.name is null or b.name is null i.e if A.name is null select A.Name as "A'S is NULL"how do i implement this logic in a select statement thanks

Policy Management Posted: 12 Jun 2013 12:19 AM PDT Hi,I have not created the job syspolicy_purge_history, it has been created by default in sql server 2008.Can anyone please tell me what is syspolicy_purge_history? and Is there any effect , if I disable the job, sorry I am new in sql server 2008.Thanks in advance

Need Help with the Error 'Subquery returned more than 1 value'. Posted: 11 Apr 2012 09:57 AM PDT [quote][/quote]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610