We have a proc that runs nightly which updates stats. For each database it will run dbcc updateusage on each table, then then sp_updatestats on the database itself.
This process normally takes about an hour, but recently the time has gone up dramatically.
I am looking at ways of making the run faster. One suggestion was to split the work and have it run in parrallel, with multiple procs all doing the same job but on different databases. So for example, I could have a proc that would determine which databases would take the longest to process, and then call 3 different copies of our update proc, each getting a different set of databases to process.
If I went this route, how could I determine which databases would take the longest to complete this process? I'm investigating determining this from sysindexes' rowmodctr, or maybe just database size?
Any suggestions on how I could get an estimate of which dbs would be the most costly to update stats? This is for SQL Server 2000.
I have just started studying for an MCTS, to eventually get to MCITP for SQL Server 2k5. I am currently in an Application Support position, working with a large clinical Oracle Database, but mainly on a simple querying basis. The plan is obviously to get away from the Tech Support, towards the DBA and I have been researching the current job market and never once found a job offer that said "offering an entry level or Junior DBA position for those that are just starting"... There's always at least a few years of experience already as a DBA required!
I am wondering how one is supposed to get that experience if he doesn't get a beginners position to start off with?
Any stories from experience? Any hints where to look, how to apply and where maybe?
I want to create a procedure where each time I add a new record to a table I want to set the field “Display” ( BIT ) to “false” in a position that is two rows before the last one. How to do that ?
PRINT cast(111 as decimal(38,35)) / 23 --> 4.82608695652173913043478260869565217
then here
DECLARE @Zähler decimal(38,35) , @Nenner decimal(38,35) SET @Zähler = 111.0 SET @Nenner = 23.0 PRINT cast(@Zähler as decimal(38,35)) / @Nenner --> 4.826086
Of course, in the upper part 23 is implicitly an integer, in the lower example it is declared as decimal. But what if I need to devide by 23.5? Why is dividing by an decimal reducing the results decimal positions?
/*Code below raises following errors:Server: Msg 245, Level 16, State 1, Line 6Syntax error converting the varchar value 'a' to a column of data typeint.*/create table #x (i integer, c char(1))create table #y (c char(1), i integer)insert into #x VALUES (1, 'a')insert into #y SELECT * from #xdrop table #xdrop table #y--CODE COMPLETEIs there a way to use the column names as the basis for the insert asopposed to column position? The key here is that I DO NOT want toSPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')Regards,Ty
Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:
select count(defendanteventpros.vbkey) as countcase from defendanteventpros where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007') having count(defendanteventpros.vbkey)>4
The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).
What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.
I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!
Hi all As you know when you run a piece of TSQL scrypt in Query Analizer at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time from SQL-Server environment and use it in the Front-End Application to inform the user?
I'm in stage of manually populating a database that requires me to have about a dozen tables open simultaneously. It would be great to be able to save the tables, locations and sizing so that these can be recalled and opened automatically. Currently I do a screen capture, which is pretty lame.
Any suggestions would be welcome.
Config:
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
I have two related tables in my SQL database that I wish to join as follows:------------------------------------tblCustomersID (pk)Nameetc.tblCustomerManagersID (pk)CustomerID (fk)Manager (this *is* an fk but for the purposes of demonstration isnot)StartDate (indicates the date upon which the manager took / is takingcontrol of the company)------------------------------------Example entries are:tblCustomers1 Microsoft2 Symantec3 BorlandtblCustomerManagers1 1 Barry 01/01/032 1 Peter 01/07/033 2 Norman 01/02/034 3 Terry 01/01/035 3 Peter 01/07/05------------------------------------What I want to do is extract, in one query, a list of all customers andtheir *current* associated manager, so the result set today would be:Microsoft Peter 01/07/03Symantec Norman 01/02/03Borland Terry 01/01/03Currently I have:SELECT [Name], [Manager], [StartDate]FROM tblCustomersINNER JOIN tblCustomerManagers ON tblCustomerManagers.[CustomerID] =tblCustomers.[ID]WHERE [StartDate] <= GETDATE()ORDER BY [Name], [StartDate] DESCbut this obviously returns multiple entries for customers having managersprior to today eg:....Microsoft Peter 01/07/03Microsoft Barry 01/01/03....I know this is a simple question but I cannot think of a way of doing itwithout making the query extremely complicated.Any help is appreciated,Thanks,df
Hi i'm developing a website for my final year project with ASP.net and SQL Server 2005 databse. The problem i'm facing since last week is that, i'm unable to use "Select" query to fetch the data from my SQL Server database. i've used the following code to INSERT the database which is successfull.SqlDataSource sample= new SqlDataSource(); sample.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString();sample.InsertCommandType = SqlDataSourceCommandType.Text; sample.InsertCommand = "INSERT INTO Table_Name(Fields) VALUES (@Fields)"; sample.InsertParameters.Add("Fields", FieldsTxtBox.Text);
It successfully updates my database fields. I've tried many codes that i've found over the internet but none worked. can anyone please tell me how to use SELECT query in the fashion as the above one? I shall be very thankfull for the help. Regards, Jigzy
For example if I have a winform with a textbox and search button. If I type in ABCD1234. I won't know what the data is coming into the X Table. So there will be hundreds of description data in the description field in X table.
So in the X Table, there are ABCD1234,ABCD_1234,ABCD/1234,ABCD 1234
But I will only pick this 'ABCD1234' record up, and the rest won't be picked. They are variations of the same description. It needs to pick also ABCD_1234,ABCD/1234,ABCD 1234
If also type in 5678, in another column like code in X Table. It would have 005678,05678,56780. But I will only pick this '5678' record up, and the rest won't be picked. They are variations of the same code. It needs to pick also 005678,05678,56780
I have tried LIKE, FREETEXT and CONTAINS.
But another idea is a table of alternate descriptions for those that you know about and look those up and search for them all. But how can that be done?
create table testedit ( editid int ,Tguid varchar(20) ,ttime numeric(4,2) ,numApp int ,numOrg int ,custid varchar(1) )
INSERT INTO testedit values(1,'ABC',12.52,40,11,'Z') INSERT INTO testedit values(2,'ABC',12.52,500,33,'Z') INSERT INTO testedit values(3,'ABC',12.53,500,33,'Z')
Out of this records I would like to select only the 1st and the 3rd record. ie. My result should only have the below rows
I have two tables in my database. I want to insert date and no.of hours worked on that day for a particular project. So in a week if end user enters 7 dates and hrs for each day...... i have to insert those values into the table against one project only. Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?
Hi, What the best way to calculate the age given two datetimes. I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.
I am looking for something that would return the following output
birthday= 12/6/1973 EndDate = 11/30/2001 then the age should be 27
If birthday = 12/6/1973 enddate = 12/11/1973 then the age should be 28
I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?
Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.
When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
Hi, First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this. Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later. How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me. Skills Table
I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...
Select dbo.res.book_date, dbo.res.arrive_date, Cast (dbo.translog.systemdate as datetime) From dbo.res Where dbo.res.resid > '500'
The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.
Hello, I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time. Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!
Hi All, I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;
Here is my SQL, hope someone can modify it and thanks in advance.
Code:
SELECT DISTINCT instance, exec_time AS median FROM (SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time) sub_a ORDER BY 1 DESC) sub_1 UNION ALL SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time DESC) sub_b ORDER BY 1) sub_2)
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.
the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.
For Example
If the Finalsuit is 12/01/2000 then the renewal would be 12/31/2002 the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?
--Use the following formula to calculate a person's age in years in a stored procedure, --where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:
DECLARE @age int DECLARE @dob datetime DECLARE @yyyymmdd varchar(11)
--Notes: --Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now. --The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer. --The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement --the "year" part of the function to calculate the number of year boundaries crossed.
