I have two tables that have a column called "subscriberid". I imported rows into the main table, and it's sister table.
There are 90,000 rows in the main table and 94,021 in the other table.
It appears that roughly 4,000 rows have disappeared in the main table, but not in the other table, and I don't understand why?
I'm new to SQL Server 2005 Express and I'd appreciate any help you can give me.
Thanks,
Bill
Hello together,
strange things going on:
1. The table statistic shows for example 67 rows in a table, select count(*) only returns 63 rows.
2. Table statistic shows 50 rows, select count(*) returns 55 rows.
In both cases if you do an insert the newly inserted row sometimes can be retrieved by a select statement sometimes not. Row statistics sometimes is updated correctly, sometimes not.
Integrity check for these databases says everything is fine. DBCC CHECKDB, DBREINDEX, UPDATESTATISTICS, ... does not help or says everything is fine.
Already opened a case at HP's Microsoft support and they involved Microsoft itself but all are a little bit clueless at the moment.
As this is our main DB cluster and several databases are affected we had to stop most of our applications since last Thursday and now we are getting a little bit in trouble so any hint is very welcome.
Thanks in advance
PS: Restore is not an option - as all tools say everything is fine all of our backups from the last months include the error. Don't ask why nobody recognized the lost data earlier, seems they were stored but not required for some time.
Hi All,
I can do this in Access, with VB, but I'm pretty new to SQL Server.
Say you have the following table, call it TblStudents:
Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony
And you wanted a view that would give you:
Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony
How would you do this in SQL Server?
Thanks.
Henry
Hi Im having trouble with this it seems simple enough but its not!
I have a source Table called Access_table example
Name Role1 Role2 Role3 Role4 Role5
a 1 0 0 0 0
a 0 0 1 0 0
b 1 0 0 0 0
c 0 1 0 0 0
d 0 0 0 0 1
e 0 0 1 0 0
e 0 1 0 0 0
f 1 0 0 0 0
g 0 0 1 0 0
I need to create a view that basically finds all the names with double Roles and merge the results into 1 row example.
Name Role1 Role2 Role3 Role4 Role5
a 1 0 1 0 0
e 0 1 1 0 0
I cannot change the information in the source table and the results need to be in a view as the roles will change. Every time I try and do this I duplicate the row again. Can anybody suggest a solution.
Thanks in advance.
Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...
Hi, my name's John and this is my first post here at SQL team. I've learnt a lot from the forums here, never needed to post though. I'm pretty good at sql, but not that good and I think for the first time in couple of years I now have a query that I'm completely unable to create.
Let me explain the setup.
I've got three tables e.g.
Products (productid, Name)
100 Computer
200 Printer
300 PDA
ProductActions (ID, Name)
6 changed casing
8 Changed motherboard
7 replaced cd-drive
ProductPerformedActions (productid, actionid)
100,7
100,9
200,8
This basically shows a simple setup showing that product (computer) has been done the action "repaired" and product (printer) has been done the action "changed motherboard". The query i'd like to have is where I can select all products that have not been performed on cerrtain actions. e.g. a list of products that have not been performed on all actions, such as
UnPerformedActionsList(product,actionid)
PDA,6
PDA,7
PDA,8
Printer,7
Printer,6
Computer,8
I know its foundation is something like this:
select productid from products where productid not in
(select productid from productperformedactions....
which elimiantes products that have been performed an action on, but it also removes it for ALL actions,
which i'm trying to avoid. All i want is kind of a cross join, listing all actions, then inverting it, to show
all products that have NOT had it performed on them.
I hope this makes sense.
Thanks
John
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for
I have a table is MSSQL 2000 that looks as follows:
Code:
id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1
Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:
Code:
custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1
Any help would be great! I imagine some sort of join command can be constructed using a single table?
I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.
I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.
When it fails, the count resets to zero, then begins incrementing again.
I need to know how to generate the last column (Count).
I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.
VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174
[Code] ....
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View 1 Replies View RelatedI have a table like below:
ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I have a similar problem to the one indicated by David Van Diest. I could not find any answers to his query.
Could anyone help!
Thanks
Mon, 8 Feb 1999 16:24:55 -0600
From: David Van Diest
[sql7] DTS and disappearing indexes
I have noticed that if I use the Microsoft ODBC driver for SQL Server when I
transfer data from one database to another my indexes disappear. If I pick
other odbc source and then fill in the data source my indexs do not go away.
Anyone else see this behavior? I don`t think it should be working this way.
Thanks,
Dave
I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
PLEASE HELP!
HELP!!!
I have three servers replicating. One is a publisher and two subscribers. If I put data in the publisher, some of it will disappear after replicating. When data is entered in one of the subscribers, the same thing happens.
Anyone have any ideas??? I am doing a merge replication and all servers are running SQL Server 2000.
Kameron
Ok, I'm new here and I REALLY need some help here.
In my organisation, we have a Server with MSSQL 7 running with some databases. Some of the databases are published for replication.
Now, since Monday, we've been losing databases that are in use! :confused:
The files are disappearing and being marked as suspect in SQL. The physical file has been deleted from the machine even when SQL is running and on some databases are replicating. In one instance, we were making permission changes on a user and it just said that the file is no longer there. And when we checked, the msdb system database had disappeared as well. So now the server is showing up as having no databases. We were able to recover some of them through backups and a handy recovery tool. But now we are helpless as to WHY this is happening. We suspect Virus but we have Norton CorporateEdition running and Liveupdate updating every day.
I've searched the web for any one that had the same problem, but nothing came up. Only some stuff saying that space was an issue, but not on a drive with 21GB remaining and the biggest database is 3GB...
Any help, insight or resolution will be greatly appreciated.
I am running an Access 2000 MDB against a SQL 7 back end, using ODBC linkedtables over a LAN and a WAN. The system has been operational for years withrelatively few problems.Recently, WAN users have been reporting several hundred records disappearingat a time. The records are all sequential, and they're in a table thatcontains about 60,000 records. This all started last week at about the sametime (not sure if before or after) that the WAN went down for a couple ofhours for an unknown reason. Since then, every once in a while, a WAN userwill report that several hundred records in a block are just "missing."Then, an hour or two later, they reappear.Any ideas as to what's going on or what can be done to rectify this?Thanks!Neil
View 8 Replies View RelatedI am not sure where to put this but correct me if i'm in the wrong forum.
I've been working on this database for some time now. It's an Access 2003 database with a SQL backend. Everything was going well until one of the users noticed her data was disappearing after she send an email. The database has a memo field that gets pasted in the body of an email when it is created. Sometimes, when the user sends an email and goes back into the record, the memo field is blank. I know the field has data in it at the time of the email because it will give you an error if the field is null and you won't be able to send it. And, it goes into a history table with everything the user does with each record. I've checked this history table and it shows that all records in question, an email was sent. I have no idea why the data would disappear in this field. It does not happen all the time. I've performed the same steps and I can't duplicate the problem. There's no code associated with the memo field that deletes it after sending the email. Can anyone think of anything that would cause this?
The problem i am facing here is disappearing data in sql server 2000. I
not sure what is the problem, but it is getting worse and worse. Anyone
can point me to a right direction whether this is a security
vulnerability which i need to download the patch, or is it my computer
is infected with virus., or is it a bug in sql server?
I remember having read somewhere about this problem also, but not sure
in which website already. Anyone, please help!!! Because we are going
to deploy to production server soon, and this is a problem that need to
be solve ASAP..
Thanks in advance...
I am supporting a system that needs to allow users to have access to Temp DB. I set these users up using the GUI, but whenever the server is restarted, these users are wiped out. Is there anyway to keep these users when the server is rebooted?
Thanks for any help.
A user created stored procedure is dropped from system tables where no known drop procedure command is executed.
From ISQL the procedure runs properly after creation but, at sometime during execution the procudure dropped from the system tables.
Any help would be appreciated.
Environment:
SQL Server 6.5 sp4, windows NT 4 sp3, db-library, visual C++
I am using a hosted MSSQL 2000 database that powers the backend of mywebsite. Website visitors interact with it via ASP pages I havedeveloped. I also have an internal FileMaker 7 database thatperiodically synchs with it via Filemaker's ODBC functionality.Several times now, I have come in one day to discover that my ASP pagesdon't work. When I look into it, one of my MSSQL tables will be missinga few fields. There is nothing in any of my code that sends an ALTERTABLE command or any other command that affects table structure. Thesedeletions of fields is totally random.The most recent example was with a table called FreeTeacherSubs. I justdiscovered today that three fields went missing:HowDidYouHearAboutYES TEXTFollowUpCallOrEmail TEXTHowMaterialsFit TEXTSuffice to say I am baffled! Has anyone ever experienced fieldsdisappearing from their tables?Kevin
View 2 Replies View RelatedHi,We have a SQL server db backend for our ERP system. I have written aCrystal Report to extract data from one particular table calleditemspecbomw (c.30000 records) which contains amongst other thingsBill of Materials costings. When I run the report I know that somerows are missing as when I look at values through the ERP systemitself, the values are different.What I have found is that when I run the equivalent ERP system report,the Crystal Report I have written shows extra rows. SQL Query Analyserbehaves exactly the same as the report I have written even when usinga "select * from". I have tested opening up a bigger tables (c. 700000records) which appears without a problem. If anyone knows why thismystery is happening, I woould be grateful for your help.Many thanks,Tony.
View 2 Replies View RelatedI'm using an Access front end to a SQL 2000 database.I have a form from which I enter data. Everything seems fine, I check thetables and my records are being inserted.When I close Access and reopen it, data from three of my tables is gone.SQL Profiler turns up nothing. If I try to access a problem table fromEnterprise manager while Access is open I get an ODBC error. If I try toaccess it after I close Access, the records are gone.Where do I start looking to figure out where the problem is? I'm at a lost.TIA for help.--Jake
View 4 Replies View RelatedHas anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?
p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.
I created a simple stored procedure shown below. It works wonderfully. The only problem is that it likes to disappear on me. I am accessing it through a VB.Net program. The system will work fine throughout the day and suddenly it will be gone. I can exit Sql Server Management Studio and come back in and it will still be there so that is not causing it. The database stays connected all the time.
Any suggestions?
Code Snippet
IF EXISTS(
SELECT *
FROM sys.procedures
WHERE name = N'VerifyPart'
)
DROP procedure VerifyPart;
--create a procedure for the part code tab verify part function
go
create procedure VerifyPart
(
@PartNumber varchar(50) = null,
@LastUpdated varchar(30) = null
)
as
--Update Part Number
update Parts
set LastUpdated = @LastUpdated
where PartNumber = @PartNumber
I'm not even sure where to post this, but has anyone had a problem with maintenance plans disappearing?
I mean the SSIS package and the job just disappearing....?
I have four servers. Two dev and two prod. I created a pretty simple maintenance plan to backup databases. There are four tasks in the plan. One for each of four databases. Backing each up to a separate file, all in the same folder.
The plan gets associated with a job that has it run daily, every six hours, with no end date.
The plan runs just fine. Then this morning, on one of the servers, the plan and its job are just gone.
This is the second time this has happened. And it's not the same server that it happened on the first time.
Why would a maintenance plan just disappear?
Anyone?
J
When I enter data into a SQL database and save it, the data that I have just added disapears, but when I close the applicaiton and re-open it, it's still there - is there any reason for this and any way to get it to stay?
Any ideas?
I have set up 80 connections to the SQL Server - mostly through code. People have been successfully using the database, but then when they come in to use their computer and the database the next day, their connection is completely gone! What is that all about? So far this has happened to 2 or 3 different users. Any idea why?
Thanks.
Hi,
We have a number of databases on our SQL Server 2005. One of them, AddressPoint, keeps disappearing completely every few days. I should qualify that - the database name still exists and is visible in Management Studio, but there is no + icon to expand to see the object types.
I can manually restore the database. But we are unable to identify any process that might be deleting the database.
So where do I even start in trying to track down the process that is deleting it? I have not been able to pinpoint a specific time when the deletion might have occurred, as I am not using the database regularly, and neither are our users, yet. The latest deletion definitely occurred within the last 24 hours, but that's as specific as I can be.
Thanks,
Keith.