Dump Transaction Log Difficult??
Jan 4, 2000
Hello:
I am working with an application in mssql 65.5, with sp4.
I have the database option 'truncatelog on checkpoint' set, there are no transaction log dumps taken at this time.
We plan now that y2k is over to upgrade to sp 5a. I am not not sure the new configuration
parameter 'logLRU buffers'(I don't have the sp5a readme text with me now) in our situation.
Here is our situtation with the transaction logs: The log, mostly with month-end high actvity,
fills up when long-running, high activity transactions are run.
The log gets to 100% sometimes rather quickly and sometimes near that or above 80%easily.
We check with dbcc open tran and there are no current transaction running. We expand the log just a little.
But it still shows 100@ used when we run dbcc sqlperf (logspace).
Here's the rub: when we try to dump the log, with wither or both back-to-back: dump tran abcdb
with truncate_only and then dump tran with no_log still nothing happens.
Sometimes we stop and start the mssql server and that helps but not always and sometimes when
we are really desperate we stop/start the NT server box(at off hours). Sometimes, the transaction log
reduces to nearly 0% used when we run dbcc sqlperf (logspace) and sometimes not. So we run dump tran
a couple of more times. We wait for several minutes usually to if the dump tan started and if it shows up
under EM under curent activity
This is becoming very frustrating and dangerous as well.
1) has any one had similar experiences with mssql 6.5 sp4?
2) Can anyone advise what we can do to more easily to dump the transaction log? Are we doing
any thing really wrong?
3) Would upgrading to sp 5a really help or should we just start planning upgrading to mssql 7.0?
Any suggestiosn you can furnish will be helpful. Thanks.
David Spaisman
View 1 Replies
ADVERTISEMENT
Feb 22, 1999
Hi,
I need to dump transaction log every night. Before I do that I'd like to check those open transactions and kill the ones not part of sa or SQL server dba's activity. How can I achieve this?
Thanks in advance.
May
View 1 Replies
View Related
Jan 14, 1999
I have a transaction log that is 80% full and will not dump. I have used the "dump transaction dbname with no_log" command. It return the usual "This command did not return data, and it did not return any rows". However, this didn't do anything. The transaction log is still full. I don't want to restart the server if I don't have to. Any ideas?
View 5 Replies
View Related
Aug 5, 1998
is there any way to monitor the space utilisation of log space and if it crosses 60% or above, dump the transaction log, effectively....
i tried some of the things, given below.
1. script to monitor the space used of log using dbcc sqlperf(logspace) and check if crosses 60 % and dump the transaction into disk file and execute this script through a schedule task running every minute.
2. same result thro using sql server performance monitor.
View 5 Replies
View Related
Jan 12, 1999
Hi, I am trying to set up an auto dump for Transaction log using performance monitor ( Log space used(%) )... Although I followed the steps, it did not work... can anyone tell me what am I doing wrong ?
1. create an error # using Server/message/New etc...
2. Defined sql exec alert : right click sql exective/manage alerts/operators..
3. Defined performance monitor using add to alert and set Alert if radio botton to over %80.and Run_Program on Alert: c:mssqlinnE 5555 /T /S servername
then I inset 2000 rows is a table I still get :
Msg 1105, Level 17, State 1
Can't allocate space for object 'publishers' in database 'test' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
I thought what I did is the right thing to do, but it seems that I am doing something wrong, Please help.
Thanks
Ali
View 1 Replies
View Related
Jan 23, 2008
I am doing a differential backup every night with t-log backups every 15 mins. The differential backup occurs at 3:00a and is about 300 MB. The very next t-log backup at 3:15a is also about 300 MB instead of the usual 1 - 2 MB. Why is this and how can I avoid it? Is this just how SQL Server works? Do I have to do a full backup every night instead? Thanks in advance for your help.
- Michael
View 9 Replies
View Related
Jul 1, 1999
I have a 28Gb temp db transaction log which I want to dump halfway through a process, I have tried;
dump tran tempdb with no_log
which seems to have little effect.
Thanks for your help.
Andy
View 1 Replies
View Related
Feb 8, 1999
hi, Does anyone have done this before. if so, please enlight me with your experience
Ali
View 1 Replies
View Related
Mar 3, 1999
I issued the following statement on my database as my log file showed 100% full.
However after running this command and also running it again after expanding the size
to almost double the value of both my database log and data devices it still shows the log as being 100% full.
DUMP TRANSACTION <database> WITH TRUNCATE_ONLY (and also used the NO_LOG option).
So can anyone tell me whats going wrong here? I should note that I am still able to enter data into the database without
any errors.
View 1 Replies
View Related
Dec 15, 2006
I have the following table:tblFriendsOwnerCode FriendCode7 107 1410 710 1210 1312 1013 1013 1814 718 13
I need a SP which return the following (im unsure about the best return datatype and the sql statement):
I want return all friendcodes of user nr 7 (10 and 14)and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7
(if possible WITHOUT the use of a temptable!)
View 4 Replies
View Related
Mar 11, 2008
I have roles set up for different companies. The role names are structured companyname_department, ex.,
CallawayContracting_SalesDepartment
CallawayContracting_Administration
FredsAutobody_PaintSales
How would I search the roles and return only departments that belong to a certian company and also users that belong to a certian company. I appear to have gotten myself into quite a bind. Any help would be much appreciated! I will be certian to click best answer.
View 4 Replies
View Related
Apr 6, 2008
I'm pretty new to this so I'll explain as best I can.
I am building a small DB which will track attendance for employees based on a point system. I believe I'm almost there (with this piece) but am stuck.
The basic concept:
1. Collect all records from the attendance table for the previous 14 day period
2. sum the points column in the attendance table and group by UID, storing in a new table called TOTAL_POINTS ONLY for those UID's which have a value > 0
3. Perform a basic insert into statement on the attendance table for each UID matching those found in the previous TOTAL_POINTS table
Number 3 is where I'm failing and could really use some help.
My code thus far...
-------------------------------
/*Declare local variables for current date and start date.*/
--
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, @DateNow)
--
/*Create table to hold totals for future calculations*/
CREATE TABLE POINT_TOTALS
(UID int, TOTAL float)
/*select ALL records from the table within the above defined date range
and group them by UID tallying the score column*/
--
INSERT INTO POINT_TOTALS
SELECT UID, SUM (POINTS) AS TOTAL_POINTS
FROM attendance
WHERE date >= @StartDate
GROUP BY UID
--
/*If the TOTAL_POINTS > 0 for the 14 day period, insert a record in to the
attendance table which deducts .5 points for the UID in question*/
*** This is where I'm failing ***
--This was just to make sure I was returning the correct results to the POINTS_TOTAL table.
SELECT UID FROM POINT_TOTALS
WHERE TOTAL > 0
/*All I want to do now is for each of the UID's in the POINT_TOTALS table,
I want to perform a basic insert on the ATTENDANCE table where the UID's in both
match. I would think this to be fairly simple but I can't seem to figure it out.
*/
DROP TABLE POINT_TOTALS
View 2 Replies
View Related
Apr 25, 2008
--This is works
SELECT *
from vwClientsByAge
WHERE age like '18'
--This gives me an error
SELECT clientId, firstName, age
from vwClientsByAge
WHERE age < CONVERT(int, '18')
Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Whats wrong with the conversion?
However, this works:
SELECT age
from vwClientsByAge
WHERE age < CONVERT(int, '18')
What is wrong?
View 3 Replies
View Related
May 29, 2008
Hey i have a query i need help with.
I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child.
Example of the data is below. I think i need to do this in a sub query
ID Ind Parent Child Cost
P110041012705921.8000
W11004101270595.4500
A110041012705921.8000
B110041012705916.3500
R110041012705916.3500
B0100420043.3000
P0100420043.3000
W0100420021.6500
View 2 Replies
View Related
Aug 2, 2005
Hello !This is my table:Ordernr Date ArticleO1 1.1.05 22O2 2.2.05 33O3 5.5.05 22O4 2.2.05 33O7 8.8.05 55I need one result-row for each article with the newest Order(max(date)):article lastDate lastOrdernumber22 5.5.05 O333 2.2.05 O455 8.8.05 O7How can I get this ?I tried this:SELECT distinct article, max(date), max(ordernr)FROM tableGROUP BY articlearticle and max(date) is ok, but I am not sure that max(ordernr) andmax(date) comes from the same row.I think, I will need complex subqueries.Many thanksaaapaul
View 8 Replies
View Related
Jan 25, 2006
Hello !I habe 2 TablesTable1: OrdersFields: Ordernr, OpiecesTable2: CalloffsOrdernr, CpiecesIn Table1 ordernr is primary key.In Table2 the same ordernr can exist oftenMy problemIf the sum(Cpieces) < Opieces:I have to create a new virtual calloffwith Cpieces = opieces - sum(cpieces)Its too high for me.Please helpBest regardsaaapaul
View 8 Replies
View Related
Jul 20, 2005
HiI am trying to produce an update trigger. I understand the concept ofdelete and insert triggers without a problem. Unfortuantely, theupdate triggers do not have particularly simple documentation in BoL.So, can someone please explain to me, quite simply how I would producea trigger on the following:I have table 1 which we'll call simon. In here are various columns androws. I also have table 2, called simon_a, my audit table.Whenever anything is updated or deleted in simon, I want it sent tothe simon_a table. Delete, as above, is fine since it's conceptual buthelp me out on the update one. I cannot seem to figure out how to getthe information from the table before it's updated.As ever, champagne and beer for the successful answer.With thanksSimon
View 5 Replies
View Related
Jul 20, 2005
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert
View 22 Replies
View Related
Jan 6, 2008
I Installed CE 3.1 months ago to play around with it to see how easy or difficult it would be to move from SQL Express to CE. Got it all to work with SQL Managment and VS 2005. Now, months later I am back to really upgrading my product to use CE. Oops, now we are on version 3.5. Decided to stay with 3.1 until I realized transact-sql command "TOP" is not supported in 3.1. So, onto the hours of research to do a simple upgrade. Here's where I stand:
1. Uninstalled CE 3.1. Installed CE 3.5. Oh, if only it were that easy. Visual Studio 2005 is still using the dll from 3.1 and still can only see reference to 3.1 in the GAC.
2. SQL Management Studio now cannot open the .sdf database.
3. There is no mention of using CE 3.5 with VS 2005, only VS 2008. Is VS 2008 a requirement to be able to work with .sdf file in VS? I remember I had to install "Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005" when I went through the first installation of CE. Is this now obsolete if I want to use 3.5? I don't even remember what its purpose was other than I needed it.
It seems there is and upgrade.exe file that need to be run command line to updgrade my .sdf file. Are you kidding?
Has anyone upgraded while still using VS 2005? Any advice is greatly appreciated.
View 6 Replies
View Related
Nov 22, 2006
Why have you made connecting to a sql server express database so difficult?
I have it working locally, but going to production has been nothing but a nightmare.
I can't even connect locally on the production box.
I am on a dedicated server and my login is an Admin on the box. I have just installed SQL Express + the fancy management interface.
I have made a copy of my database that I was connecting to dynamically (which was failing remotely) and I have attached it to the server. I have made myself a user of the database and granted my self evey permission available.
I have turned on impersonation in my web.config. The database knows it's me trying to connect and STILL denies me when I'm actually ON the production server. It is not even a remote connection problem.
How can I sit there an look at myself as a user of the database in the admin interface, yet I cannot connect via a web app. With SQL server 2000 and MSDE it was soooo simple....
Here is a snippet from my web.config
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Integrated Security=True;Initial Catalog=TEST" providerName="System.Data.SqlClient"/>
...
</connectionStrings>
Here is the error:
Cannot open database requested in login 'TEST'. Login fails.
Login failed for user 'DEDICATEDquick'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'TEST'. Login fails.
Login failed for user 'DEDICATEDquick'.
I have a screen shot of the admin interface to prove I am a user.
This should be a 5 minute task that has eaten up days.
So frustrated...
Bernie Quick
View 6 Replies
View Related
Nov 13, 2006
Hi.
I am developing for a system that receives an input from an external modem.
The Transaction is split into 2 sections,
Section 1 = grants the transaction ID,
Section 2 = deliver the transaction Data.
I have 2 corresponding tables,
One called tblremoteunitrequestID (Where the transaction ID is granted)
The other called tblremoteunitrequests (Where the transaction is completed, about 1 second later)
I am writing a diagnostic report that determines if the first part of the transaction completes but the second part fails.
I am having difficulties designing the SQL for this.
Here is some sample data for tblremoteunitrequestID: (The first stage of the transaction)
RecordDate | Serial
13/11/2006 14:00:36 0000-0000-0000-0006
13/11/2006 14:00:30 0000-0000-0000-0004
13/11/2006 13:59:04 0000-0000-0000-0092 (This didtn transaction didnt complete)
13/11/2006 12:15:22 0000-0000-0000-0092 (nor did this one)
13/11/2006 10:31:54 0000-0000-0000-0092
13/11/2006 10:00:29 0000-0000-0000-0006
Here is some sample data for tblremoteunitrequests: (The second stage of transaction, 1st stage has to be completed beforehand)
DateReceived | Serial
13/11/2006 14:00:37 0000-0000-0000-0006
13/11/2006 14:00:31 0000-0000-0000-0004
13/11/2006 10:31:56 0000-0000-0000-0092
13/11/2006 10:00:31 0000-0000-0000-0006
13/11/2006 10:00:25 0000-0000-0000-0004
13/11/2006 07:19:13 0000-0000-0000-0020
From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.
Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.
I Only want to display the transactions that didnt complete, sounds easy huh?
This is what I hope to get in my Results table:
DateReceived | Serial
13/11/2006 13:59:04 0000-0000-0000-0092
13/11/2006 12:15:22 0000-0000-0000-0092
I was experimenting with T-SQL today, this is what I have done so far:
SELECT DISTINCT
TBLRemoteFeildUnitRequestID.Serial, TBLRemoteFeildUnitRequestID.RecordDate,
CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)
THEN ' Ok'
ELSE ' Not ok'
END AS PROBLEM
FROM TBLRemoteFeildUnitRequestID LEFT OUTER JOIN
TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial
WHERE TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()
ORDER BY RecordDate DESC
This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".
Heres a sample of the result I got:
Serial | RecordDate (1st part of transaction) | Status
0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok
0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated)
0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok
0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated)
0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok
0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok
I have just about had enough, I have wasted an entire day on this
Someone please Help
Dan
View 4 Replies
View Related
Jul 6, 2005
hello,
I could need some help with a little query.
table "acme"
name1 varchar(128)
name2 varchar(128)
idate datetime
content
A,H,1/1/2005
A,H,2/1/2005
A,I,2/1/2005
A,J,3/1/2005
B,K,4/1/2005
B,L,5/1/2005
I want the following result (for 'A'):
1/1/2005,1
2/1/2005,3
3/1/2005,4
I want to filter for Column "Name1" and cumulative count the entries grouped by date.
what's the simplest solution?
best regards, thilo.
View 4 Replies
View Related
Sep 20, 2007
I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:
- Anything that a company does is defined by a workflow.
- A workflow consists of tasks.
- Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.
You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.
Here's an oversimplified visual:
Each arrow points to its dependency. Each task can have multiple dependencies.
The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.
Code:
[Tasks]
ID Status Name
-- ------ ----
1 Done Start Processing Loan Application
2 Done Photocopy applicant's driver's license
3 NotDone Photocopy proof of house ownership
4 NotDone Get a copy of applicant's W-2 forms
5 NotDone Perform credit check on applicant
6 NotDone Sign loan contract
[Dependencies]
TaskID DependencyID
------ ------------
1 0
2 1
3 1
4 2
5 2
5 3
6 4
6 5
Tasks has a many-to-many relationship with itself.
Here's the hard part:
- A task can't be started until all of its dependencies have been completed.
- after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.
When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.
The requirements of the query are very simple, but the implementation is difficult.
I'll post a prelimenary solution in the next post:
View 1 Replies
View Related
Nov 23, 2005
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.
View 5 Replies
View Related
Jul 7, 2007
Hi all,
i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.
sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.
i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.
Alon.
View 4 Replies
View Related
Jan 8, 2007
Well, I finally got RS2005 installed on Vista, only problem is, now it won't run. I've installed SP2 (Ctp) and still no joy. It just errors when attempting to access the web service.
Here's the error:
w3wp!library!5!01/08/2007-20:36:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.Authentication.WindowsAuthentication.GetUserInfo(IIdentity& userIdentity, IntPtr& userId)
at Microsoft.ReportingServices.WebServer.WebServiceHelper.ConstructRSServiceObjectFromSecurityExtension()
at Microsoft.ReportingServices.WebServer.Global.ConstructRSServiceFromRequest()
at Microsoft.ReportingServices.WebServer.Global.get_Service()
at Microsoft.ReportingServices.WebServer.Global.ShouldRejectAntiDos()
at Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e)
Any clues anyone? I'm all out.
Cheers,
Paul
View 3 Replies
View Related
May 18, 2006
I created a package from an Import Data Wizard, in 2000 you had the option to schedule that job, right then and there, but apparently that was too convenient, anyway... After creating my stored package within the database, I try to set up a job to run that package, it is pretty straightforward and I think it is going well until I try to execute the job and I get the following error.
Message
Executed as user: servernamesystem. The package execution failed. The step failed.
So after some investigation, I believe it is a permissions issue, and at that time (default install) all services were running under "Local System". I had created the package with my domain account which is in the administrators group of the server. So, I added another service domain account in the administrators group and changed the services to run under that account. Again. No Dice, now I get this error...
Message
Executed as user: domainservice_account. The package execution failed. The step failed.
Any help would be appreciated at this point in time.
Thanx
View 6 Replies
View Related
Aug 18, 2007
GO
CREATE TABLE [dbo].[Product]
(
[ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
[Name] [varchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
)
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?
View 2 Replies
View Related
Jul 2, 1999
How do you shrink the tempdb in SQL Server 7 after you allocated a large space to it but later realized you allocated too much.... I need to reclaim the space allocated to it but when I try to change the size or do a shrinkdb it either tells me I cannot shrink it smaller than allocated size or it shrinks tempdb to current (which is too large)... I looked online but could not find anything...
Any suggestions or assistance would be much appreciated... up to my eyes in tempdb..... Bill.
--------------------------------------------------------------------------------
View 1 Replies
View Related
Nov 14, 2007
Hi! I am rather new to SQL, and could use some help. I have a table with lots of records, with fields like this:
TableA.Batch
TableA.Wafer
TableA.DieID
TableA.Param
TableA.Value
For each batch, there are ~25 wafers. For each wafer, there are thousands of dies. For each die there are several parameters, and for each die and parameter there is a value.
I want to calculate the median and robust sigma (inter-quartile range/1.35) per wafer, for a parameter. The following code works perfectly for me:
DECLARE @Q1 FLOAT, @MEDIAN FLOAT, @Q3 FLOAT, @SIGMA FLOAT, @x INT, @y INT, @z INT
SET @Q1 = SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value) Quartile1
ORDER BY value DESC
SET @MEDIAN = SELECT TOP 1 value FROM (
SELECT TOP 50 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value) Median
ORDER BY value
SET @Q3 = SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value DESC) Quartile3
ORDER BY value
SET @SIGMA = (@Q3-@Q1)/1.35
SELECT @MEDIAN, @SIGMA
Ok, if you are still there: I need to do this for hundreds of wafers, and hopefully without having to manually set the batch and wafer numbers. I'm using MS SQL server by the way. As far as I've understood, there is no for-loop in SQL, and I can't see how I can do this by using GROUP BY.
Any help is very appreciated.
View 5 Replies
View Related
Aug 5, 2005
Hallo !I have 2 tables with similar construction.DataOLD and DataNEW.In my grid I need a JOINED table.On the one hand I need all the data from the old table and the datafrom then new table with the same id (left outer join works)But additionally I need all rows from the new table where no equivalentrows are in the old table.example:TableOLDdiameter amountOLD20 10050 20030 300TableNEWdiameter amountNEW20 50060 60070 120Wished Result:diameter amountOLD amoutNEW20 100 50050 200 NULL30 300 NULL60 NULL 60070 NULL 120Who can help me?Many Thanks.aaapaul
View 6 Replies
View Related
Jul 20, 2005
Hello all.I have a table defined in sql server as follows:ROW_ID (identity)DEPTH_FROM Number (8,3)DEPTH_TO Number (8,3)COLOUR Char(10)With typical data like:ROW_ID DEPTH_FROM DEPTH_TO COLOUR---------------------------------------------------------------1 0 5BLUE2 5 8BLUE3 8 10RED4 10 12GREEN5 12 16GREENI want to create a view that will 'compress/roll up' the data soit appears like:DEPTH_FROM DEPTH_TO COLOUR---------------------------------------------------------0 8 BLUE8 10 RED10 16 GREENI have been working on this for several days, with no luck,any help would be appreciated. BTW: there are no overlapsallowed in the depth_from, depth_to values.Thanks in advance.
View 6 Replies
View Related
