I would not expect replication to be available in an Express version as it is one of the advanced features of the database but I don't know as I use neither the Express nor do I need the replication features.
Never underestimate the power of human stupidity
I configure SQL Server Merge Replication, when merger database together, if one of the two server machines damaged or die, the software did not transfer it, i have to manually reset ip to the software to run, how to automatically switch to 1 in 2 server fails ?
How can I change the below code snippet from using cursor to using table expression. The below code is about getting specific data for general account and all accounts below such given account
DECLARE @Account_ID AS BIGINT,
@IsLeaf AS BIT,
@TotalValue AS DECIMAL(18,3),
SELECT @TotalValue = ISNULL(SUM(JournalDet_Debit),0) * CASE WHEN @Currnecy = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
INNER JOIN Account_InBranch ON (AccountBranch_AccountID = Account_ID)
LEFT JOIN Journal_Details ON (Account_ID = JournalDet_AccountID)
LEFT JOIN Journal_Head ON (Journal_ID = JournalDet_HeadID)
WHERE (Account_ParentID = @AccountID OR Account_ID = @AccountID )
AND Journal_BranchID = @Branch
AND Journal_Date >= @DateFrom AND Journal_Date <= @DateTo
GROUP BY AccountBranch_BegBalDebit , AccountBranch_BalanceDebit , AccountBranch_CurrencyConv
FETCH NEXT FROM GetAccount_ID INTO @Account_ID
WHILE @@fetch_Status = 0
SET @TotalValue = @TotalValue + dbo.GetChildrenAccount(@Account_ID,@DateFrom,@DateTo,@Branch)
FETCH NEXT FROM GetAccount_ID INTO @Account_ID
It's tricky to answer without your table definitions and some sample data, but this should get you close:
WITH cteAccountTree As
cteAccountTree As P
INNERJOIN AccountTree As C
ON C.Account_ParentID = P.Account_ID
Account_Isleaf = 0
SELECT@TotalValue = IsNull(Sum(JournalDet_Debit), 0) * CASEWHEN@Currency = 0THEN AccountBranch_CurrencyConv ELSE1ENDFROM
cteAccountTree As T
INNERJOIN Account_InBranch As B ON B.AccountBranch_AccountID = T.Account_ID
LEFTJOIN Journal_Details As JD ON JD.JournalDet_AccountID = T.Account_ID
LEFTJOIN Journal_Head As JH ON JH.Journal_ID = JD.JournalDet_HeadID
Journal_BranchID = @BranchAnd
This should return the list of all accounts in the tree which have the specified account ID as an ancestor, excluding any with the Account_Isleaf flag set.
NB: If your tree is particularly deep, you might run into the default recursion limit. There will probably be a way to work around it, but it won't be as nice as the recursive CTE solution.
You then join the tree of account IDs to your branch and journal tables to calculate the total in one hit.
The only part I'm not sure about: your code seems to be double-counting at each level:
<hr size="1" color="#63B4FF" noshade="">
<span style="color: rgba(0, 88, 170, 1)">
<cite>"These people looked deep within my soul and assigned me a number based on the order in which I joined."</cite> - Homer</span>
Thank very much for help. I'm trying to learn about recursive expression table however I cannot know how its really work The Where clause you mentioned at the end of your post to be able to get the parent account and its child(s) too and that what I forgot to mention in the post because I did not know it at the time when I wrote the post what I need to get is the parent account and its child(s) account(s) and if those child(s) account(s) is/are a parent(s) for another child(s) account(s) I got them too it seems stupid but this function is used within a procedure to get the balance of the account chart within the system.
Finally, I'm thanking you too much. could you help sending me a link to an explanation of the recursive CTE and how it is implemented
Appreciating your help. Thanks very much I will review the function again as I'm not the one who wrote it however I'm trying to optimize a Stored Procedure performance which selects from a lot of tables + calling this function. the last time I executed the procedure it nearly took over 14 minutes without a single result while it was still executing
hi - How can you tell if a SQL Server instance max'ed out bottleneck is Memory? It's on a 8GB box with two processors. Seems CPU ok. Memory max out the whole time while this big SELECT-INTO statement is running. Avg Disk Queue lenght ok - avg below 1.0 so doesn't appear disk/paging is causing problem.
However, fact from Task Manager memory keeps 7 GB out of 8GB over the whole time doesn't mean memory is the bottleneck? Or is it?
The same query runs on another SQL server with double the RAM finishes a few times quicker however.
I want to send Email to Many Users from One Email Address. I did configured the Database Mail. After Scheduled this Job, It is sending Emails to some users and failing to some users. Following are the Error Message am getting from the Log.
1)The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-12-08T16:01:56).
Exception Message: Cannot send mails to mail server. (The operation has timed out.).
2)The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-12-08T16:02:05).
Exception Message: Cannot send mails to mail server. (Failure sending mail.).
I am getting this Time Out Message even after Updating the Time Out to 60 Sec
If this code works then you are presumably using SQL CE or similar, which is on the local machine. That is fine as far as it goes, but if you wish to share the database with other clients then you will need SQL server.
Just got it to work, create database, tables, records
make the con strings for that.
Better than the oleDB DBF I was going to use, which took 20 sec to write 49 records.
Finished the switch to localDB this morning.
I have never heard of localDB, but it seems handy for vb.net windows apps.
I have a .sql file. the file size is 401MB. i found that a table say 'pauseddetailes' which stores the values and more than 1,00,000 records which has to be archived periodically but it is not. Due to this i couldn't import the file in MySQL , phpMyadmin. i need to truncate the table 'pauseddetails' and then import the rest of the data in the db. This particular table is independent and doesn't have any foreign key constraint.
I tried increasing the value of the upload size , execution time and memory size in php.ini file still didn't work out.
Is there any possible way. Looking for the solution.