1. 2 databases A & B which are both mirrored in high safety mode (manual failover)
2. The mirror of A & B are on a separate and remote site in relation to the Principal
3. Some of the application functions insert/update to database A&B as one transaction unit
My query is give the above scenario should I expect any DB incosistency on the mirror Partners for A & B in relation to cross db transactions? I believe not as the update on the Primary should be commited as whole on both Principal databases & mirror databases prior to the client getting confirmation on commit.
Further my understanding is that even in an automated failover with synchronous mirroring there should be no issues with cross DB transactions.
I'm not personally a DBA or claim to be a database expert, instead I am a software architect who works closely with database architects and DBA's. I'm presently architecting a system where we will have a smart client used in both Europe and the United States from the same database instance.
The system is to be used for performance testing of devices and then the results will be available to management through the same smart client used by test engineers etc. The first release is operational in the United States today with Poland coming online shortly.
Today we are weighing our options between having cross-geo database mirroring or merely having the database server in the United States with application server level caching implemented in Poland and the United States.
The .net framework's Windows Communication Foundation is used with a netTcpBinding and the service layer is asynchronous with the reliableSession option enabled.
Does anyone have any feedback on whether you would go with cross-geo database mirroring or would you use local application servers with possibly some caching capabilities at that layer? Please also describe the benefits and any risks associated with each also...
I€™m about to move all our databases from an old lousy server to a brand new HP setup. I have 8 databases (total of approx 100Gb) and I get 2 BL25p servers and SAN access for them.
Now, I want high availability and I like the idea of mirroring the databases for achieving that. On the other hand I don€™t like the idea of the mirror server doing almost nothing while the principal gets the entire load €“ seems like a waste of good resources.
But is this really the situation or mirror server also be loaded, just for being the mirror?
Now, if the main load is on the principal would I then benefit from distributing the databases on both servers, so both servers would act like principal and mirror?
What I mean is that server 1 could be principal for DB 1-4 while server 2 would mirror these DB€™s and likewise serever 2 would be principal for DB 5-8 and server 1 would mirror them.
Does it make sense? Would I benefit anything from this?
Say for example DatabaseA has failed over to the mirror. Then you call a stored procedure in that database that queries a table on DatabaseB that is on the principle (has not failed). What happens? Is mirroring smart enough to know that? If not, are there workarounds? Perphaps manually failing over the other databases?
Hi there, I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following: <code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction? Any help is greatly appreciated
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Hi, I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.
I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.
It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.
I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this
thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
1982 Jammu and kashmir $30,000.00 1 Animal Husbandary
1882 Jharkhand $35,000.00 1 Animal Husbandary
1982 Karnataka $40,000.00 1 Animal Husbandary
1982 Kerala $20,000.00 1 Animal Husbandary
1982 Madhya pradesh $5,000.00 1 Animal Husbandary
I want to produce report as by using crosstab query as showing bellow
Year Asam Hyadrabad goa arunachal pardesh etc............ 1981 1000.00 2000.00 8000.2 00000 000000 ...... 1981 ' ' Is it possible by crosstab query ?or please suggest me another way as early as possible.
I wanna develope a report using cross tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008 test 45 50 25 10 11
Please can any one help me how to do this this is very urgent ?
I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.
Many thanks.
SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1, (CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2, (CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3, (CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4, (CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5, (CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6, (CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7, (CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8, (CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9, (CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10 FROM tblTest f WHERE (f.fID=219)
" I have two sql server2000 database named db1 and db2. i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1. now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access. Is there some othere way with out creating view?
Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date
blah blah blah , sorry I made it sound so confusing
I need to do this so it would be the correct format for a report. Can anyone suggest how to do this? I need to create a view with this format, so speed is probably an issue?
So I have 2 options : I have to do in in Cross tab or I have to create a query for this. Is it possible to built a query like this,I mean assigning data as column?
we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?
insert into @log(date, category, value1, value2) select '2008-01-01', 'ABC', 11,12 union all select '2008-01-02', 'ABC', 35,53 union all select '2008-01-03', 'ABC', 38,62 union all select '2008-01-05', 'ABC', 59,95 union all select '2008-01-02', 'XYZ', 42,21 union all select '2008-01-04', 'XYZ', 9,7 union all select '2008-01-05', 'XYZ', 89,45 union all select '2008-01-01', 'HHH', 70,52 union all select '2008-01-03', 'HHH', 3,83 union all select '2008-01-05', 'HHH', 26,77
where 1) date is always up to the day (no time variation) 2) date and category can be considered a composite unique key
Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:
Ideally, the results include - every day in the date range (even if there is no corresponding data for that date) - the columns values to be dependent on the categories found within the date range
I came up with this
-- to fulfill requirement "every day in the date range" declare @dt table (d smalldatetime) insert into @dt select '2008-01-01' union all select '2008-01-02' union all select '2008-01-03' union all select '2008-01-04' union all select '2008-01-05'
-- to fulfill display all the categories (manually determined) select convert(varchar(10),d,101) as date, abc.value1 as abc_value1, abc.value2 as abc_value2, xyz.value1 as xyz_value1, xyz.value2 as xyz_value2, hhh.value1 as hhh_value1, hhh.value2 as hhh_value2 from @dt dt left join @log abc on dt.d = abc.date and abc.category = 'ABC' left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ' left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'
just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.
my table data consists like below: country_name Revenue PERIOD_TYPE_OUT Amount_out UK Solutions01 Apr 2007 To 31 Mar 200856.83000000 UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000
while iam using the cross tab in front end application data view is coming as country_name
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 solution solution uk 116.07 56.83
Actual o/p should be:
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth solution solution uk 116.07 56.83 -0.51
can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate is : (56.83-116.07)/116.07 ([current year]-[previous year])/[previous year]
I am using SQL server 2005 and want to write a cross-tab query. i have managed a basic one but can't seem to write a more complex one. if i have a table with columns called id, person_id, field_description, and content. e.g. image that the two values i could have in field description are age and favourite_colour, and then the corrosponding content for these fields would be 56yrs and blue or something.
now i want column names as person_id, age and favourite_colour. and the actual data should be content.
Have i made sense? I am sure this must be simple, but can't figure the syntax
Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill
I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is: TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow SELECT GD_PROFILE.ID FROM GD_PROFILE GROUP BY GD_PROFILE.ID PIVOT GD_PROFILE.ID_APLIC;
The RESULT is:
ID
20
22
77
122
141
179
211
327
334
370
388
405
524
584
645
687
826
865
914
CN1
1
1
1
1
1
CN2
1
1
1
1
CN3
1
1
1
1
CN4
1
1
1
1
1
CN5
1
1
1
1
CN8
1
1
1
1
1
1
1
1
1
1
1
CN14
1
1
1
1
1
1
1
1
CN15
1
1
1
1
1
1
1
1
1
1
CN22
1
1
1
1
1
1
1
1
CN29
1
1
1
1
1
1
1
CN36
1
1
1
I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?
The Cross is a simple cross query, and the goal is return this final result:
Profile 1 - ID CN1, ID CN4 Profile 2 - ID CN2, ID CN3, ID CN5 Profile 3 - ID CN8 Profile 4 - ID CN14 Profile 5 - ID CN15 Profile 6 - ID CN22 Profile 7 - ID CN29 Profile 8 - ID CN36
