I am runnnig Microsoft SQL Server 2000 and have a database that gets
downloaded everymorning from an ftp site. This download contains the
new records and any changes to existing records. However I am running
into a problem in that I am forced to recreate the views over everytime
we replace the new database with the old one. I've tried restoring the
database as well as replacing it and am not sure what I am missing. I
am new to SQL Server and any help would be greatly appreciated.
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
I just did the dumbest thing ever. I think I have lost my database. i was trying to attach a different db with the same name. but it wasnt able to attach so the i renamed my current db and attached the new db.i dont know what has happened but my old db has been deleted and i cant find it the mdf file for it.... that has been removed for the directory it was in too!! I was using MS SQL Server Managemnent Studio can I undo this somehow?? PLEASE HELP
Our past CIO deleted a SQL Server database because he didn't know what it was or if we needed it. Guess what we do! We were able to undelete the backup files but when we try to restore from it we get the error "No a valid backup" Any ideas?
I am running sql server 2000 on windows 2000. i have my backups set to delete after a week. they are not deleting, can anyone tell me why this is happening?? the backups filled the hard drive.
I am currently trying to recover a data base that i detached. I have search the computer with *.mdf with out results...
I detached the database from msde 2000 and then installed sql express now when i go to attach the db it is missing, is there away i can recover the db?
Hi, I accidentaly deleted a package while working on it, intention was to delete another package just above it in solution explorer but the one i was working on got deleted, is there any way to retrieve it...??
Any ideas how I can recover a job that has been deleted (?) from SQL7.0 ? We had one which was used on a regular basis which has nowceased to exists. I've spoken to the people responsible for the serverand was met by blank faces (as usual). They have no idea when it wasdeleted, by whom or when. To make matters worse, they don't think theycan get it off the backup set without a major bit of work to theserver. Suprisingly, none of the users noticed that things had stoppedworking.Unfortunately, I needed to do some work on this in preparation for mymonthly meeting with my boss on Monday. Whilst I'm not to blame, I'dlike to understand a little more about any options available to us forrecovery as he will undoubtedly ask when he finds out.I have an old back up on another server, so it's not the end of theworld re-creating it, but I was under the impression it was backed upproperly. Is this normal or have they missed something ?Are there any ways I can recover this ? What can I get done to thebackup to make sure this is backed up properly ?Thanks in advance.Ryan
Dear All,I got a wierd problem which I haven't been able to explain.I am working on MS SQL 2000. I don't know for what reason, therelationship between Parent/Child table is getting deleted. When Iopen up the ER diagram in MSSQL Enterprise Manager, I see therelationship line come up for just a split of a second, and disappearsafterwards. This is the second time I am seeing this thing.I realized it when I accidently deleted the rows in the Parent Table.They should not have been deleted as I had associated records in theChild Table (because of Key Constraints).Has anyone ever come across this situation? Do you have anysuggestions?Regards,TinTin
I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help. Code:protected void btnRemove_Click(object sender, EventArgs e) {
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do 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
I want to know what to do to recover the deleted records on a specific table. Unfortunately, the dba was unable to activate Trace feature and I am not familiar in using the transaction logs to retrieve data.
I created an alert (18453) to audit successful logins. After I was done with the alert I made I edited the alert 18453 to disable event log logging and then deleted the alert. My problem is that the alert is deleted but it continues to log to the SQL Server error log and the windows application log. I have tried restarting the SQL Server Agent and even had the server rebooted over the weekend. Has anyone else had this problem? Where is this configured and how can I disable it?
The transaction file of Database got deleted.So Sql server shows the Db as suspect.Unfortunately also do not have latest bk up copy of DB. I tried creating dummy log file with same name and placing it in same location but that did not help. I wud highly appreciate if someone cud suggest a soln quickly.
Does anybody know if SQL 2000 has a tool to roll back (udelete) records? Let's say Payroll dept deletes 500 records by mistake... I know of 2 products that do that (Computer Assoc. - Unicenter Log Analyzer and Red Matrix Techmologies' SQL Audit 1.7 - but I have never used them. Any comments? Thanks for your help.
We've got a merge replication. I have one Publisher and one Subscriber. I've made a pull subscription to a Publisher.
I've got into trouble few times occasionally 3-4 times a year. Today also I got problem with one table only one row among so many not carried out on at subscriber, even it is not find out at publisher.
This row was entered at subscriber in the morning, printed by users but in evening report it is not coming down in reports, so I had checkup and find out it is not available at any server. How?
I don’t understand, how inserted, printed data erase from subscriber but rarely. Is there any clue?
Which parameter of the subscription is responsible for it ?
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
If I have an Identity-declared column, the table has lots of rows and I start deleteting records - will any old, deleted identities be re-used (without an explicit re-seed) ?
(I'm talking about numerical Identities, not these GUID things)
