|
All,
Just looking for peoples advice based on experience with sql high availability.
Basically in the past we have used SQL Clustering without any issues for high availability, I have used this in many companies how ever we are currently moving environements which I will not have the option to use clustering.
So basic setup, two sql servers (active/passive roles) running SQL 2008 standard, database files are stored on a high speed SAN.
So need some options, I know I can use log shipping, however failure time is >1min. Also for patching how do I restart a server and ensure the other server which is being logged shipped to has the latest data ?
Mirroring is a option, however according to MS mirroring should not be used on more than 10 databases. We have lots more!
The last option which seems bad practice but could work is simply kill all users, detach db's and then reattach to other server. Any issues with this ?
Ignore any issue with client connectivity based on server names, this can be resolved in different ways.
Any real advice appreciated, I have read most of the SQL documentation so no need to just forward me to there 
Thanks
Shane
|
|
|
|
|
Hello friends,
I need a optimized query for below condition
CaseName ScriptName ID Status Version
---------------------------
TC1 TS1 1 PASS R1
TC1 TS1 2 FAIL R1
TC1 TS1 3 PASS R1
TC2 TS1 1 PASS R1
TC2 TS1 1 PASS R1
TC3 TS1 1 PASS R1
TC1 TS2 4 PASS R2
TC1 TS3 5 PASS R2
Want to get distinct casename, ScriptName,status,Max(ID),version
Result I need is
TC1 TS1 3 PASS R1
TC2 TS1 1 PASS R1
TC3 TS1 1 PASS R1
TC1 TS2 4 PASS R2
TC1 TS3 5 PASS R2
When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records.
SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)"
SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)"
Thanks in Advance
Jishith
|
|
|
|
|
SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics a
WHERE EXISTS(SELECT CaseName FROM Metrics
WHERE a.CaseName=CaseName AND a.ScriptName=ScriptName AND a.Version = Version
GROUP BY CaseName,ScriptName,Version
HAVING MAX(ID)=a.ID)
I hope it can reduce your time
|
|
|
|
|
Is it helpful?
Select distinct CaseName , ScriptName , max(ID),Status ,Version
from Metrics Where Status = 'PASS'
Group By CaseName , ScriptName ,Status ,Version
|
|
|
|
|
Hi vica dianto,
This Query also takng much time.. but much faster than what i posted. I want to insert these result in an access table also. Is the insertion creating the touble or the query itself?
Hi abcurl,
"Status = 'PASS'".. Status can be fail or pass, but if two similar Cases having different status I want the lastest status, ie Case having Laresgt ID. One more thing i want the result to be inserted in an access table also
Thanks alot for the replies
Jishith
|
|
|
|
|
I have a typed dataset. It uses a connection string from the web.config file. I have changed the database name in the connection string, but it is still taking the old database name. How do I get that changed?
-----Have A Nice Day-----
|
|
|
|
|
If that's the case, then it's not really using the connection string from your Web.config file Use the Dataset Designer to make sure you're really referencing your external connection string, and not using a copy saved in the dataset's code.
|
|
|
|
|
Can you please guide me as to what needs to be done and how do I change it.
-----Have A Nice Day-----
|
|
|
|
|
I want to pass an array of Id's to a stored proc to be processed, but i keep getting an error saying : Error converting data type varchar to numeric.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getTranfer] @TRANSFER_ID int = null, @COMPANY VARCHAR(5),@FORMTYPE VARCHAR(30), @TRANSFER_IDS VARCHAR(500) = NULL, @SEAL_DATE datetime = NULL AS
-- Declare variables
DECLARE @rc Int, @ThisID varchar (10), @Pos int
IF @SEAL_DATE IS NULL
BEGIN
SELECT
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER,
CONSIDERATION,
TRANSFER_ID,
[DATE],
CURR,
CMF_NAME_1,
CMF_NAME_2,
CMF_ADDR_1,
CMF_ADDR_2,
CMF_ADDR_3,
CMF_ADDR_4,
CMF_POSTAL_CODE,
Cons_Curr = CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
-- Cons_Curr,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS
CONS_CURR_WORDS = CASE
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END
FROM
VW_TRANSFERS
WHERE
TRANSFER_ID= @TRANSFER_ID
AND
FORMTYPE= @FORMTYPE
AND
COMPANY = @COMPANY
OR
TRANSFER_ID IN (@TRANSFER_IDS)
END
-------------------
ELSE
SELECT
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER,
CONSIDERATION,
TRANSFER_ID,
[DATE],
CURR,
CMF_NAME_1,
CMF_NAME_2,
CMF_ADDR_1,
CMF_ADDR_2,
CMF_ADDR_3,
CMF_ADDR_4,
CMF_POSTAL_CODE,
--Cons_Curr,
Cons_Curr = CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS
CONS_CURR_WORDS = CASE
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END
FROM
VW_TRANSFERS
WHERE
FORMTYPE= @FORMTYPE
AND
COMPANY = @COMPANY
AND
DATE = @SEAL_DATE
OR
TRANSFER_ID IN (@TRANSFER_IDS)
Ferron
|
|
|
|
|
Your problem is that SQL Server does not allow an in clause to be a variable:
TRANSFER_ID IN (@TRANSFER_IDS)
for example will not work. What you need to do is split the variable into a table variable and join to that. Google has loads of examples
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
folks,
I have a table with column upddate with datatype as datetime.
I updated the table
update table set upddate=getdate()
when i select the values all the upddates are same.
I expected difference in milli seconds to find the last updated record.
can somebody help me out.
|
|
|
|
|
Actually, your expectation is not wrong. But the effect can only be seen if there are millions of records. For lesser number of rows, it will reflect the same time.
-----Have A Nice Day-----
|
|
|
|
|
Simple really, the update happened so quickly they are all the same time. Also be warned, SQL Server is only accurate (from memory) to a third of a millisecond.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: SQL Server is only accurate (from memory) to a third of a millisecond.
That's a good information to have. Thanks.
|
|
|
|
|
Actually, DATETIME in SQL Server is only accurate to about 3 milliseconds. Which is not very accurate at all, in computing terms. There is a new datatype called DATETIME2 in the latest version of SQL Server which is accurate down to nanoseconds. Hundreds of highly trained database experts worked for weeks to come up with that name for the new datatype.
|
|
|
|
|
I knew 3 and milliseconds came into, just couldn't remember where. I knew about datetime2 in 2008, but didn't realise so much time had been spent deciding on a name 
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have a small problem. I need to convert some rows into columns. Look at the following information.
Original Table:
Ind Sub String
0 1 22
0 2 21
0 3 5.6
0 4 John Rogers
1 1 12
1 2 76
1 3 99.3
1 4 Sarah Peters
Converted to:
Ind val1 val2 val3 val4
0 22 21 5.6 John Rogers
1 12 76 99.3 Sarah Peters
This is similar to a pivot, except that I am not count, averaging, counting, or any other agregating methods. Is there a simple query that will convert this or is this going to be a cursor issue?
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
Use Max(stringvalue) for your aggregate function (you are after all aggregating 1 value), you can see an example here[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
is there any tools like sql profiler to capture pl/sql commands for oracle ?
thanks
|
|
|
|
|
|
Hey am creating a receipt,
i want when the user enter 100 when i query this amount i want the query retrieves 100 and the character hundred.
Another example if the amount was 500250
I want the sql to retrieve Five Hundered Thousand and two hundred fifty
PS i want it to accept deciamls could sql provide this functionality if not how could I???
|
|
|
|
|
Here are a couple of links doing something like this.
Link 1[^]
Link 2[^]
|
|
|
|
|
Hi
I have 2 tables Table1 and Table2 as below
Table1 Table2
col1 col2 col1 col21 col31 col22 col32
1 xx 1 A 0 AA null
2 yy 1 B 0 BB null
3 zz 1 C 0 CC null
1 0 X null XX
1 0 Y null YY
now on joining 2 tables, i want the output as below
Table1.Col1 Table1.col2 Table2.col21 Table2.col31 Table2.col22 Table2.col32
1 xx A X AA XX
1 XX B Y BB YY
1 XX C null CC null
Am not sure whether join is to be used or what? Is there any way i can get the output like this. Table2 will have many other rows takking the col1 of Table1 as primary key.
Regards
Naina
|
|
|
|
|
I can't understand your question,where are separated data of Table1 and Table2?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
The first table Table1 has a primary key and the foreign key is the column in the second table Table2
Now table 2 has the data like this
foreignkey col1 col2 col3 col4
1 naina 1 null null
1 karri 2 null null
1 xxxxx 3 null null
1 null null aaaa 1
1 null null bbbb 2
now i need the output to be like this
primarykey col1 col2 col3 col4
1 naina 1 aaaa 1
1 karri 2 bbbb 2
1 xxxxx 3 null null
it means that the first columns with null data should be replaced with the last columns with data for that particular primary key value
Regards
Naina
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
