I seem to remember reading many moons ago about a function where you
can retrieve a count of the last recordset you opened.
For example:
I've got a stored procedure that returns a recordset using the TOP 10
so I only get the top 10 records. I need to know the recordcount but I
dont want to reuse the SELECT statement because its quite complex.
Any ideas?
What does @@Count do?
Thanks in advance
Hello all.
I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:
select * from U_DATAFILE
where count(group by VIEW_ACTION)>1
Thanks all :)
I have a table with three fileds
1 - SmsTime (datatype = datetime)
2 - MsgCnt (datatype = int)
3 - Message (datatype = varchar)
Here MsgCnt means it will give the number of sms part (70 charector is equal to One SMS).
So in each row MsgCnt may be vary. Some time it may be 1 or 3 or 5 or 2 etc.
Now here I need to count how many MsgCnt is received on Today. Its not to count the rows but counting the MsgCnt fild value.
Is there any sql way to find it.
Plesase help me!
Hi All,Here's a challenge.If there is a 'one word' answer to this, i.e. the name of a built inSQL Server function I can use, that would be great! However...I have a standard 1:m relationship, e.g. tblHeader and tblDetail.I want to create a view of records from 'tblHeader' and within thatview have a column (called e.g. DetailTypes) that provides a singlecomma separated varchar of values from a varchar field (called e.g.DetailType) that appears in related records in the 'tblDetail' table.(hope you understood that :)e.g. the contents of my 'tblDetail' table could be....Detail ID | Header ID | DetailType-----------------------------------1 | 1 | A2 | 1 | A3 | 1 | B4 | 2 | A5 | 2 | C6 | 3 | BTherefore, the view I want to create should return:Header ID | DetailTypes-------------------------------1 | 2A, 1B2 | 1A, 1C3 | 1Bi.e. the first row can be read as "Header 1 has 2 'A' detail recordsand 1 'B' detail record."I have created a view e.g. 'vqryHeaders' which calls a user definedfunction that takes the HeaderID and opens a cursor on another view,e.g. 'vqryDetailGrouped'. The other view groups the records intblDetail so that I can get a count of each DetailType for each HeaderID. The cursor then loops through the returned records concatenatingthe count and detail type into a comma separated string (as shownabove).However, when I run this across 20k records it is soooo sloooooww. Ihave indexes on the relationship fields and I am using realisticallysized varchars - neither made any difference in speed. It isdefinately the function that I wrote that slows it down as the view islightning fast when I remove my function call.I can supply source code if necessary, but I think that this is akind-of generic problem so I don't see the point - yet.I really hope you can help.Regards,Jezz
View 11 Replies View RelatedI report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?
View 1 Replies View RelatedI was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
Is this possible using a view?
I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:
Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END
how could I build this query or what better way of doing the job there are...
Hi,I am struggling with a simple query, but I just don't see it.I have the following example table.Table MessagesID Subject Reply_to1 A 02 Ax 13 A 14 B 05 By 46 C 0The table holds new messages as well as replies to messages.Messages with Reply_to = 0 are top messages, the other messages arereplies to a top message. The subject of a reply message does notnecessarily have to be the same as the subject of the top message.What I would like to have returned is this: a list of messages whereReply_to = 0 and the number of replies to this message.ID Subject Num_replies_to1 A 24 B 16 C 0Any assistance would be greatly appreciated.
View 3 Replies View RelatedIs there an easy way to count all records in a database?
View 3 Replies View RelatedTable1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I am trying to count records in my stored procedure. Can someone please help me.
these are the two procedures I am using
Alter Procedure usp_rptQualityReport As
SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))
ORDER BY
tblFailureReasons.FailureReason
Alter Procedure usp_rptQualityReport2 As
exec usp_rtpQualityReport
SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,
(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel
FROM usp_rptQualityReport;
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3
This is what I would like to have:
parameter # Calls
------------ -------
desktop 15
network 19
software 3
Please Help!
is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?
View 4 Replies View RelatedOk, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing
dbo_dbWafer_Slicing
WaferID
SawDate
SawRunNumber
Pass
03-157.05
1/8/2008 9:54:00 AM
03-157
0
03-157.03
1/8/2008 9:53:00 AM
03-157
-1
03-157.04
1/8/2008 9:53:00 AM
03-157
0
03-157.02
1/8/2008 9:52:00 AM
03-157
-1
03-157.01
1/8/2008 9:50:00 AM
03-157
-1
03-165.06
1/4/2008 10:46:00 AM
03-165
0
03-165.07
1/4/2008 10:46:00 AM
03-165
0
03-165.04
1/4/2008 10:45:00 AM
03-165
0
03-165.05
1/4/2008 10:45:00 AM
03-165
0
03-165.02
1/4/2008 10:44:00 AM
03-165
0
03-165.03
1/4/2008 10:44:00 AM
03-165
0
03-165.01
1/4/2008 10:43:00 AM
03-165
0
So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
my system has 2 db's - sql server 2000 & db2 @ separate locations. i have a select query which needs 2 pick up consolidated data from both the tables. also the schema on the db2 has minor changes when compared with the schema on sql server 2000.
while searching on microsoft i came across the technique of creating a linked server. would this be possible 2 implement in my scenario. also would in this case, be advised that i create another view in the db2 server which has changed the db2 schema to the sql server schema format??
please hurry..
regards,
sameer
One or more files listed in the statement could not be found or could not be initialized. (Microsoft SQL Server, Error: 5009)
I accidentaly created a log file on my drive E:, but every time that I try to delete the log file it keeps on returning the same error.
Can someone please help me delete the log file.
hii have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.thanx in advancecheers
View 1 Replies View RelatedHello everybody .
I have 40 GB db running mostly transaction processing.
I set up
1. back full backup 2 times a day (takes 30 -40 min)
2. log backup every 15 min
3. custom log shipping
4. We don't won't use Cluster.
Once in while becouse of nethwork, or other problem log shipping fails,
so I have to restart log shipping all over starting from restore in stand by mode last full back of my db.IT takes 2-3 hrs just to do this restore !!!
1. So I am asking advice is any way I can bring down time for restore ?
2. Should diffrential backup be taken ?
3. We will not use Custer
Alex
hello all !for MS SQL 2000i am having a table with > 100 000 rowsI must clean itDELETE FROM myTable WHERE Name LIKE 'aser%' AND info IS NULLDELETE FROM myTable WHERE Name LIKE 'tuyi%' AND Info = 'ok'DELETE FROM myTable WHERE Name LIKE 'hop%' AND info LIKE 'retro%'.....about 20 DELETE commandswhat is the best way to do it ?thank you
View 14 Replies View RelatedHi everyone im in deep in need of help in a very easy query and few questions i want to ask,, i use msn boy22202@hotmail.com please i want to contact anyone who use sql server 2005 that can help me in it.... thank you
View 4 Replies View RelatedI need to insert data to a temp table in SQL ,
I have
CREATE TABLE TMP_X (
doc_name varchar(200)
)
--select * from TMP_X
INSERT into TMP_X
values
(
'...,
but its saying there isn't a match, and i know why its trying to insert all the data as one row, but i need them as seperate rows as i want only 1 column..
is there another INSERT type function ?
If I have a table with one column
and i want to insert a few 100's rows of names
I can't use the INSERT stmt as that does one row at a time ,
how can i achieve this ?
I have stoopidly enough deleted default Db. That causes Enterprise Manager to be unable to work with my DB's. The default DB I deleted had no other functions other then being default DB, I mean it was outdated, and I had other DB's that contained all my importent work. They are still running, and I can view DB driven site hosted at localhost, even though default DB no longer excist. I am even able to upload new content, or add new users, so this means all my other DB's are fine. I can even see SQL server icon in my bottom right corner of my desktop, and it shows server running.
Now I am in the need of adding tables and rework some of my excisting tables and stored procedures, but I am not able to do that with Enterprise Manager, due to the lack of default Database.
How do I correct this problem? I have gotten one tip of doing the following: EXEC sp_defaultdb 'User', 'DB' but I am not sure what to do with this.....tried to run it from command line, and put my username and the DB I would set to default but nothing happend.
So I need more details, step-by-step guiding will work, as I don't know a hole lot about Enterprise Manager and SQL.
Btw, this is my error in Enterpr.Managr:
A connection could not be established to MyComputerVSDOTNET2003
Reason: Cannot open default database. Login failed..
Please verify SQL server is running and check your SQL server registration prpoerties and try again
Pls tell me there is a way to fix this problem.
Hello everybody,
please advice: what is the fastest standard method of user interface access to SQL database? I am looking for fast display of one master record plus related dependent records, plus fast scrolling through master records with display of dependent records as fast as posible. Perhaps a standard problem with standard solution? At current state of matters, I am still much slower then with my old Access97 database.
thanks for any advice,
Otakar Kverka
Prague
I notice this morning that my tempdb grows very fast. I have 26GB in my
hardrive and all the space occupied by tempdb and finaly the qeury got failed due to 0 space in hardrive and there is no space to grow tempdb.
The select query supposed to bring about 40000 rows.
I ran this same query in different server that is not growing even 1 mb.
I checked the tempdb option the Trunc log on checkpoint is true.
Why this problem happening ?.
I have just dbo permission to access all the database.
Do you have any advice regarding this?.
Thanks,
Ravi
Hello everybody
We need to move table T1 from database A to T1 database B on same server
size of table T1 15 GB and 40000000 rows
database B just created and will act as warehouse
could it be done simply by
1.creating table T1 on db B and then
2.set db to simple recovery
3.
insert into B.dbo.T1
select * from A.dbo.T1
4. create all the indexes on table T1 in db B
free disk space is 35GB
Any idea how to optimze import
Thank you
Hello, everyone:
My database backup files are 3-5GB. Restoring always take over 20 minutes. Is there the fast way to restore the big database?
Thanks
ZYT
(Win2003, SQL Server 2000 SP4)
I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.
After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.
I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.
Any thoughts? How can I prevent this behaviour?
Thank a lot in advanced.
I have a weird situation I had not expected.....
I insert a record to a table and "later" I update it.
I have two fields to capture time information: Created and LastModified.
My update is very simple: update .... set ..,[LastModifiedDate] = GetDate() where id = @pId.
Now my problem is that I am seeing the created and lastmodified times as the same (in format 2007-09-05 12:38:42.383) !!??!
The record has definitely been updated (other fields are populated).
Can anybody enlighten me?
Hello, everyone:
There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.
ZYT
Hi,
I have made some stored procedures to check if a user is involved with a certain record. basically every stored procedure contains the following logic.
example spCheckClientRelated:
select @res = count(*) from client_role where client_id = @cid and employee_id = @eid
if (@res = 0)
begin
... next select
end
if (@res = 0)
begin
... next select
end
....
return @res
end
so far so good. But the final check in CheckClientRelated tests if a user is related to one of the sales projects for that client.
I allready have the spCheckSalesProjectRelated that returns 1 or 0 similar to the example above
so I want to find an efficient method that selects all the sales_project_id 's from the sales_project table where client_id = @cid (i use offcourse select @sid = sales_project_id from sales_project where client_id = @cid at the moment)
And then I have to execute the spCheckSalesProjectRelated method for each @sid and @eid. This if offcourse where my problem is located. I don't know how to do a fast check for every selected @sid, until spCheckSalesProjectRelated returns 1
As you probably can determine from my question, sql is not really my domain, and I'm certainly not an expert, but I don't mind reading or looking up some stuff, so even a clue or a direction to look in would be most appreciated
thx in advance.
I have a very puzzling situation with a database. It's an Access 2000 mdbwith a SQL 7 back end, with forms bound using ODBC linked tables. At ourremote location (accessed via a T1 line) the time it took to go to a recordwas very slow. The go to mechanism was a box that the user typed the indexvalue into a combo box, with very simple code attached:with me.RecordsetClone.FindFirst "[Index] = " & me.cboGoToIf Not .NoMatch ThenMe.Bookmark = .BookmarkEnd Ifend withNow, one would say that going to a record is slow because I'm using..FindFirst over a T1 line. And that's what I thought. However, as I wasworking with the form, commenting out various sections not related to the GoTo, I found that the Go To functionality changed, though I didn't modify thecode.Previously, going to a record near the end of the 50,000 record recordsettook about 1-2 seconds, but going to a record near the beginning, took about20 seconds. After the form changed, going to any record in the recordsettook about 1-2 seconds.So the question remains: why did it take so long to go to a record near thebeginning of the recordset, but not near the end (and the ones in the middletook an amount of time about halfway between the two), and what changed sothat now the form is working fine for all records?I've compared the changed form with the previous copy, and I don't see anydifferences. I've compared all code in the form module, and I've comparedall form properties. The forms are identical as far as I could tell. Butsomething happened as I was commenting/uncommenting code in the form thatgot rid of the problem with it taking a long time to go to some of therecords.My first thought was that something got recompiled, and now the form isfast. So I went back to the original version and changed some code andrecompiled, also did a compact and repair. But it was still slow. I alsotried doing an explicit decompile and then recompiled it. But it was stillslow.So this is very frustrating that the form is now working fine, but I can'tsee anything that's changed. If I don't see why the form is now fast, thenthere's no reason to believe that it might not at some point go back tobeing slow again. And then I'd just have to hope that something changes. Itwould be good to figure this out.Any ideas as to what might have changed here to cause the form's Go To to befast would be appreciated.Thanks,Neil
View 8 Replies View Related