Nasty Mirroring Split Brain Situation
Mar 27, 2007
Yesterday I had a nasty mirroring problem.
The principal and the mirror server are both running SQL Server 2005 64-bit Enterprise Edition. Witness is running Workgroup Edition. We are running in high availability mode (SAFETY ON).
3 nights before something strange happened and 1 database failed over (out of the six being mirrored) for some unknown reason. The other 5 didn't. We failed that one back and all seemed ok over the weekend.
We came in Monday and found that the main live OLTP database was showing as the Principal on BOTH servers (in SMO/Management Studio and also in sys.database_mirroring). This is the "split brain" scenario that the presence of the witness is supposed to prevent. Both databases were accessible with a USE statement - clearly not right.
I pondered what to do, eventually I decided to remove mirroring from this database. That was ok until suddenly a few minutes later we realised the (original) Principal was in recovery! uisers of course were kicked out/unable to connect. I tried to force recovery with RESTORE DATABASE dbname WITH RECOVERY but it complained users were connected!
I had to KILL the users then recovery proceeded and the database became available again. I forced the mirror offline to prevent accidental usage.
This is obviously a nasty situation where mirroriing - which is supposed to prevent downtime - actually caused it instead.
I intend to log a call with CSS but I wanted to warn other users if they encounter something similar - it has shaken my confidence in mirroring quite severely.
View 4 Replies
ADVERTISEMENT
Feb 22, 2006
Hi All,
here is the trigger:
CREATE TRIGGER Members_Set_ApplicationSyncDate ON lyrisadmin.members_ AFTER UPDATEAS
BEGIN
IF NOT UPDATE(ApplicationSyncDate) BEGIN UPDATE lyrisadmin.members_ set ApplicationSyncDate = NULL where MemberID_ = (Select MemberID_ from inserted) AND List_ = (Select List_ from inserted)
END
END
Problem is: If someone runs a query like n"Update TBL Set List_ = 'goo' WHERE List_ = 'boo'
in this case MemberID_ is not present and and error is thrown in my Update statement. How can I verify that MemberID_ is or is not available in the trigger?
Many Many Thanks!
View 2 Replies
View Related
Mar 20, 2006
[Yes, I know I keep posting this, and I'm sorry for that. But I want someone to confirm that this is a bug and not by design. I feel it is very very important]
All,
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone.
Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure".
Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
Reply here or email direct with any queries!
View 1 Replies
View Related
Mar 2, 2006
All,
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone.
Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure".
Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
Reply here or email direct with any queries!
[Microsoft follow-up] This needs either fixing or explaining.
View 11 Replies
View Related
Jul 20, 2005
I am currently working on a PHP based website that needs to be able to drawfrom Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. Itook a lot of time and care creating a flexible and solid wrapper and amdeep into coding. The only problem is a noticed VARCHAR fields being drawnfrom SQL Server 2000 are being truncated to 255 characters.I searched around php.net and found the following :Note to Win32 Users: Due to a limitation in the underlying API used by PHP(MS DbLib C API), the length of VARCHAR fields is limited to 255. If youneed to store more data, use a TEXT field instead.(http://www.php.net/manual/en/functi...ield-length.php)The only problem with this advice is Text fields seem to be limited to 16characters in length, and I am having similar results in terms of truncationwith other character based fields that can store more than 255 characters.I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions andthe functions referenced here http://www.php.net/manual/en/ref.mssql.php.What are my options here? Has anybody worked around this or am I missingsomething obvious?James
View 4 Replies
View Related
Oct 5, 2004
I feel like I am having a total brain fart. I am trying to use a select * where statement to get all the records from my database that match a given setup and using a URL variable:
Code:
SELECT * FROM dbo.TerraLogMain WHERE Volume = '#URL.VolumeID#'
Now I know for a fact that there are multiple records for Volume = Maps6 (for example) but I am only getting one record returned.
It also looks like the URL variable is getting passed okay:
Code:
http://localhost/GISTerraLog/VolumeDetail.cfm?VolumeID=Maps6
I also tried using a direct statement just to see if that gave up any hints:
SELECT *
FROM dbo.TerraLogMain
WHERE Volume = 'Maps6'
but I still only get one record returned.
So any clues as to what I am missing?
Thanks for any help!
Cheers,
Melissa
View 8 Replies
View Related
Feb 18, 2007
Guys I'm sorry to be asking such a routine question...
I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.1 public int Add()
2
3 {
4
5 string SQL;
6
7 SQL = "INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +
8
9 "LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +
10
11 "FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +
12
13 "GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +
14
15 "AirExchange, InternetAccessID, InteriorDescription) " +
16
17 "VALUES ( @PropertyID, @CeilingHeight, " +
18
19 "@LoadingDocks, @PassengerElevators, @FreightElevators, @PassengerEscalators, " +
20
21 "@FireSprinklersID, @SecurityCameras, @SmokeDetection, @FireAlarms, " +
22
23 "@GasDetection, @SecureAccess, @HeatTypeID, @AirConditioningID, " +
24
25 "@AirExchange, @InternetAccessID, @InteriorDescription)";
26
27 PropertyDB myConnection = new PropertyDB();
28
29 SqlConnection conn = myConnection.GetOpenConnection();
30
31 SqlCommand cmd = new SqlCommand(SQL, conn);
32
33 cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = PropertyID;
34
35 cmd.Parameters.Add("@CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;
36
37 cmd.Parameters.Add("@LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;
38
39 cmd.Parameters.Add("@PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;
40
41 cmd.Parameters.Add("@FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;
42
43 cmd.Parameters.Add("@PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;
44
45 cmd.Parameters.Add("@FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;
46
47 cmd.Parameters.Add("@SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;
48
49 cmd.Parameters.Add("@SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;
50
51 cmd.Parameters.Add("@SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;
52
53 cmd.Parameters.Add("@FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;
54
55 cmd.Parameters.Add("@GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;
56
57 cmd.Parameters.Add("@SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;
58
59 cmd.Parameters.Add("@HeatTypeID", SqlDbType.Int).Value = HeatTypeID;
60
61 cmd.Parameters.Add("@AirConditioningID", SqlDbType.Int).Value = AirConditioningID;
62
63 cmd.Parameters.Add("@AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;
64
65 cmd.Parameters.Add("@InternetAccessID", SqlDbType.Int).Value = InternetAccessID;
66
67 cmd.Parameters.Add("@InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;
68
69 cmd.ExecuteNonQuery();
70
71 cmd.CommandText = "SELECT @@IDENTITY";
72
73 this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());
74
75 conn.Close();
76
77 return this.BuildingInteriorID;
78
79 }
80
Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?
Any comments are greatly welcomed.
View 2 Replies
View Related
Mar 16, 2007
Hello,I am working on a Blog and a Documents systems.What I need is:1. Each blog can have various language versions.2. Each document can have various language versions.I have been thinking about this and I end up with two approaches:1. Use a structure where all tables depend on a localized table:BLOGS|---- BlogsLocalized|---- BlogsPosts|---- BlogsRatings|---- BlogsComments2. Use a structure where each table has a localized versionBLOGS|---- BlogsLocalized|---- BlogsPosts|---- BlogsPostsLocalized|---- BlogsComments|---- BlogsCommentsLocalized3. Create a simpler, without localization, in SQL and in my web sites have different versions for each language.The same approach is under thinking for DocumentsTables.Could someone give me some advice?I have been looking in internet but until no I couldn't find anything really useful.Thanks,Miguel
View 1 Replies
View Related
Nov 14, 2003
I’ve just remembered a very small yet challenging SQL statement brain teaser a friend of mine asked me long ago.
Though as simple as it might seem, I still couldn’t figure out how to solve it until this day. I just thought that some of you guys might have an answer to it.
Here is the question:
You have a table named “table1� that has only 2 fields: “name� and “phone�.
This table is populated with the following sample data
NamePhone
---------------------
John111111
Tom222222
Jack333333
John444444
Smith555555
----------------------
5 records
To get all the distinct names in this table, you would write the sql statement:
Select distinct name from table1
And the result would be:
Name
-------
John
Tom
Jack
Smith
-------
4 records
The question is: can you write a sql statement that can return all the distinct names along with their phone numbers.
E.g.
NamePhone
---------------------
John111111
Tom222222
Jack333333
Smith555555
----------------------
4 Records
View 3 Replies
View Related
Sep 8, 2005
Any help is sincerely appreciated:I have data in a table that represents the following:Admin Visit Type Registration Date Discharge Date D 20050301 20050301D 20050301 20050301W 20050301 20050301E 20050301 20050301D 20050301 20050302W 20050301 20050303W 20050301 20050311D 20050301 20050301Patient Type is always and I for the records I want but there are also Patient Types = O which I don't care about..What I would like to do is accoumlate a counter on the number of Registrations per date as well as Discharges per date.There can be thousands of registrations per day as well as thousands of discharges per day. So lets say I want to pass a date parameter to accumulate the total registrations and discharges per day. I have beat my head against the desk for the last two days because I believe this is a simple query but I just cannot get the results I want - so any help is greatly appreciated. I have written the following sql but I do not get a sum of the total registrations and discharges and EXPR3 and Expr4 always equal each other which is not the case . For example on 20040301 I have 88 registrations and 17 discharges but I can't ever get the correct totals...I wrote the following in Query Analyzer - but it does not work and I have went around in circles and have tried so many things I am just frustrated.........Declare @Parm_Beg_Date as nvarchar(8)Set @Parm_Beg_Date = 20040313
SELECT
Patient_Visit_Result_Master.PVR_Admin_Visit_Type,Patient_Visit_Result_Master.PVR_Patient_Type, Patient_Visit_Result_Master.PVR_Registration_Date,Patient_Visit_Result_Master.PVR_Discharge_Date, Count(Distinct(Patient_Visit_Result_Master.PVR_Registration_Date)) as Expr3,Count(Distinct(Patient_Visit_Result_Master.PVR_Discharge_Date)) as Expr4
FROM Patient_Visit_Result_Master INNER JOINPatient_Visit_Result_Master Patient_Visit_Result_Master_1 ON Patient_Visit_Result_Master.PVR_Hospital_ID = Patient_Visit_Result_Master_1.PVR_Hospital_ID AND @Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Registration_Date as nvarchar(8))or@Parm_Beg_Date = Cast(Patient_Visit_Result_Master_1.PVR_Discharge_Date as nvarchar(8))
WHERE (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'E') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @Parm_Beg_Date) OR (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') AND (Cast(Patient_Visit_Result_Master.PVR_Registration_Date as nvarchar(8)) = @Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'W') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @Parm_Beg_Date)
Or (Patient_Visit_Result_Master.PVR_Admin_Visit_Type = 'D') AND (Patient_Visit_Result_Master.PVR_Patient_Type = 'I') and (Cast(Patient_Visit_Result_Master.PVR_Discharge_Date as nvarchar(8)) = @Parm_Beg_Date)
GROUP BY Patient_Visit_Result_Master.PVR_Admin_Visit_Type,Patient_Visit_Result_Master.PVR_Patient_Type, Patient_Visit_Result_Master.PVR_Registration_Date,Patient_Visit_Result_Master.PVR_Discharge_Date
Order By Patient_Visit_Result_Master.PVR_Admin_Visit_TypeThanks in advance
View 10 Replies
View Related
Sep 5, 2005
This one has been stumping me for several days. I can run a query thatreturns several different items from several different manufacturers,each with a ranking score. Each manufacturer can have any number ofitems:Item_Name Manufacturer rankItem 1 Manu_A 82Item 2 Manu_A 65Item 3 Manu_A 41Item 4 Manu_B 32Item 5 Manu_C 21Item 6 Manu_B 19However, I would like the records to be ordered so that the highestranking item is shown first, then the next highest item from adifferent manufacturer is shown second, then the next highest item froma third manufacturer is show, etc.:Item 1 Manu_A 82Item 4 Manu_B 32Item 5 Manu_C 21Item 2 Manu_A 65Item 6 Manu_B 19Item 3 Manu_A 41Does anyone have any thoughts on how to order the results in thisfashion?thanks,Matt Weiner
View 2 Replies
View Related
Mar 8, 2006
I have a requirement (motivated by a SOX thing) that is just giving mefits. I know it should be easy and I'm probably overthinking it, but Ijust can seem to find the best way to get where I need to go.I have some payment projection data derived from a huge procedure thatI'm dumping into a temp table that looks like looks this:Key Pd Start End AnnualAmt MonthAmt DailyAmt6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.6164386789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.1780826789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.0958906789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479(there are actually 6 levels of keys, but you get the idea)I need it to get into a reporting table looking like this:Key Rev Year ProjectedAmt6789 2005 29,021.926789 2006 49,500.006789 2007 49,500.006789 2008 49,500.006789 2009 49,500.006789 2010 20,478.086789 2010 31,924.116789 2011 54,450.006789 2012 54,450.006789 2013 54,450.006789 2014 54,450.006789 2015 22,525.886789 2015 35,117.406789 2016 59,895.006789 2017 59,895.006789 2018 59,895.006789 2019 59,895.006789 2020 24.779.10etc...I'm having a problem wrapping my head around how to get the rows in themiddle of each period.The other, probably minor and statistically insignificant, issue isproration on a leap year. If a proration occurs on a leap year and Ihave to calculate the proration based on a DATEDIFF and an Annual orMonthly Amount, I'm going to be a day over.Anybody have any tricks or ideas???Thanks so much for your help!Jody
View 3 Replies
View Related
May 10, 2007
I have a table with 2 columns as follows:
COORD LEVEL===== =====1 11 22 23 21 32 33 34 35 3
I need to produce a result that has 1 column that is the concatenation of each COORD separated by a '.' by increasing LEVEL
EX: The result should look like this
1.1.1
1.2.1
1.2.2
1.2.3
1.2.4
1.2.5
1.3.1
1.3.2
1.3.3
1.3.4
1.3.5
Please note that the answer must be flexible enough to handle any number of levels and coordinates (although levels will most likely be less than 5 and coordinates less than 100)
Can this be done in a simple SQL statement?
ANY help is very much appreciated.
View 7 Replies
View Related
Apr 17, 2008
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
Value1
Value2
LineNumber
1
Hey
1
1
Can
2
1
Someone
3
1
Help
4
1
With
5
1
This
6
2
Very
1
2
Simple
2
2
Issue
3
2
As
4
2
I
5
3
Have
1
3
A
2
3
Brain
3
3
Freeze
4
--Thanks--
View 4 Replies
View Related
Feb 26, 2002
For anyone who can help me resolve this minor data issue. I am trying to clean a phone number column so that all numbers are reflected in the same way. I have phone numbers in my column in this this format:
(123)123-1234
123-123-1234
(123) 123-1234
How can I clean this column so that the numbers are reflected in this way: 1231231234
I'm having brain freez right now. Help me please!!! Thanks in Advance
View 2 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 Replies
View Related
Feb 10, 2004
Hey all
I've got a DTS package with a script that does this:
Set xlApp = CreateObject("Excel.Application")
This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??
Thanks! I gotta get this solved within 5 hrs :(
View 9 Replies
View Related
May 14, 2004
Hi,
I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?
Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007
Any help will be appreciated.
View 7 Replies
View Related
Jun 1, 2008
Hi everyone -
I am relatively new to this and trying to make my database as efficient as possible.
Here is the situation. In my database, there are multiple "stocks" that each have a "pricing history". When something happens that changes the price of the stock, an entry containing information about the time the change occured, who changed it, etc. is added.
I have many stocks. The information of these stocks is independant in the sense that I never really need to grab/compare information about two stocks at once. I only look at one stock at a time.
So, do I set up a "pricing history" table for every single stock (pricinghist1,pricinghist2,etc.)? Or do I set up one "pricing history" table it and index the table by stock number?
Are these equivilant? Is one more efficient than the other?
Thanks!
N
View 3 Replies
View Related
Apr 29, 2008
Here is the basic situation.
(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.
The way I wanted to approach this situation is as follows.
Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.
The concept is simple, but I'm not sure how to implement this.
For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.
Lastly, If you have better approach, I would like to hear about it.
Thank you.
View 11 Replies
View Related
Apr 17, 2008
Hello All
I have a table called Tax Act
In that I have Tax Act ID. Which is supposed to be NOT NULL.
I have to create IDs by my self using some MAX and MAX+1 incrementing function.
Please guide me on how to
View 3 Replies
View Related
Jul 12, 2007
Hello everyone,
I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.
The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.
To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:
Person.Base
Id (uniqueidentifier)
UniqueIdTypeId (uniqueidentifier)
UniqueId (uniqueidentifier)
Password (char(88))
PasswordSalt (char(10))
PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]
PasswordRecoveryAnswer (char(88)) [NULLABLE]
CreationDate (datetime)
AuthenticationWindowStartDate (datetime)
AuthenticationWindowAttemptCount (tinyint)
IsActive (bit)
IsBanned (bit)
IsLocked (bit)
Status (bit)
Person.Emails
PersonId (uniqueidentifier)
EmailAddress (nvarchar(256))
CreationDate (datetime)
ValidationCode (char(10))
ValidationDate (datetime) [NULLABLE]
IsValid (bit)
IsPrimary (bit)
IsRollbackTarget (bit)
Status (bit)
So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.
In order to meet this business requierment I would create some addicional tables. Let's get started:
Person.CustomFields
Id (uniqueidentifier)
Name (nvarchar(50))
Description (nvarchar(3000)) [NULLABLE]
Status (bit)
Person.CustomField_Value
FieldId (uniqueidentifier)
Value (nvarchar(450))
This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.
How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.
Best regards and thanks in advance.
View 11 Replies
View Related
Jan 25, 2008
Hi, I have the following tables:
Categories {Category_ID, Column2, ...}
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
View 2 Replies
View Related
Apr 3, 2002
I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.
Regards,
View 1 Replies
View Related
Dec 6, 1999
I need help on reproducing a deadlock. The sample that I have so far is only creating blocking and it never deadlocks. It's because I'm not sure what order the system tables are being help when creating temp tables in a transaction, therefore, I can't tell which pages need to be requested from another transaction to cause the deadlock. Any help will be much appreciated.
Thanks,
Mike
View 2 Replies
View Related
Jun 21, 2004
We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.
The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.
So... here's my question:
After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!
Thanks!
whill96205 the Noob :confused:
View 3 Replies
View Related
Jun 26, 2004
Hi, all..
I by mistake Overwrites distribution DB.
Server1 and Server2 replicates each other.
Applications are using Server1, and Server2 is back up purpose.
When server1 fails application redirected SErver2, using same data since there is replication.
Server2 had distribution DB.
Now there is no distribution DB.. it's messed up...
I want to restore replication between Server1 and Server2.
I tried to make replication from Server1, I got errors..
How can I do this???
View 1 Replies
View Related
Nov 18, 2005
I have a Transaction replication running between 2 SQl 2000 Servers. The distributor is on seperate machine.
Server1 is an online Db which is sending replicated trn to Server2 which is a reporting Server. Because of space issues, I have to remove about 100 Million Rows from Server1 Table without affectiing Server2 Table (Server 2 Retains those rows)
Is there any Stored Proc available to disable the replication only during that time ?
Thanks
View 3 Replies
View Related
Sep 6, 2013
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
View 1 Replies
View Related
Feb 1, 2007
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table
Cananyone help me with this query please
View 2 Replies
View Related
Jan 3, 2006
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
View 2 Replies
View Related
Jul 20, 2005
Hello,I have a rather large table in MS SQL 2000 that I'm writing reports inCrystal from, but I'm unsure how to get the various data files (fromdifferent departments) into the table. Below is a small chunk of thetable:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefitsI'm getting data from multiple places, but as I import the data, Iwant it to populate the table with DeptCode and Type being the primarykeys.If someone sends me the following file:DeptCode;Type;AveMonthVolume;WorkedFTE1000;Budget;100;2001010;Target;233;433And I get the following file from another source:DeptCode;Type;TotHours;Benefits1000;Budget;433;4001010;Target;33;43I want a simple way to import all this into the table so it looks likethis:DeptCode;Type;AveMonthVolume;WorkedFTE;TotHours;Be nefits1000;Budget;100;200;433;4001010;Target;233;433;33;43The data will be coming in delimited text and in Excel format. Iassume I can import his into MS SQL Enterprise Manager directly, but Ican't find any simple way of running in update query to import mydata. Also, I'd hate to use MS Access as the middle-man if at allpossible... but if this is the only option, I'll do it.Does anyone have other suggestions? Above is only a small example...the table has about 25 columns with various data I'll be collectingfrom 3-4 departments, so it's a hodge podge of data to combine intoone report.Thanks for any suggestions or ideas in solving this. Thanks inadvance...Alex.
View 2 Replies
View Related
Jul 6, 2007
I am here in a peculiar situtation i am not very sure the problem which i am facing as of know is a Database or Network.
One of our organisation application is connected to SQLServer 2005 (clustered) database was working fine before few days back we gone though a windows 2003 server patch update activity and server was restarted and a failover occured.
The day patch activity is done we are facing data loss problem over the network.
Checked the SQLServer2005 ErrorLog but no error related to database.
Few user are facing problem as mentioned below
Error : Checknetwork Documentation
Search the net but no satisfactory answer.
Please help me with a resolution or work around.
One more Issue :
While pinging the server some time we get request Time out.This Request time out is for 1 ms.
Do this also effects the thick client application connectivity with the Database.
regards
Sufian
View 1 Replies
View Related
