|
|
Thanks so much.I Know this solution, I have linked the server but the problem is Oracle DB is a big DB, I only want to syn some data only depend on my need.So that the DB is smaller and the website will run quicker.
|
|
|
|
|
So write some queries to extract only the data you require.
You can set up Tasks to make this happen on a regular basis - see Schedule a Job[^]
Note that a "smaller DB" does not necessarily mean that it will be quicker, much depends on the database design, indexes used, and efficiently written queries for example.
|
|
|
|
|
OK I Know the schedule job.I Can get everything i want to get.But this is only get data not syn data.At the 8 am everyday i get data I need yesterday.But if there is some error that need to run some procedure to update the source oracle DB and they don't tell me.I don't have exactly the data I need.That why i ask for syn tool
|
|
|
|
|
I'm sorry you've lost me. What is "syn data"
If you mean "sync" then you can have oracle "publish" the data changes - see Create a Publication from an Oracle Database[^]
There are also plenty of synchronisation tools out there, some of which are very expensive.
Personally, I would just get the data from the original source - as I said earlier you are not necessarily going to get your website to "run quicker" by doing this. It's more likely that it's the way you are getting the data that requires tuning
|
|
|
|
|
Thank so much.I Mean Sync.I Will try it then report you later.Thanks.
|
|
|
|
|
Hope it works 
|
|
|
|
|
Hi All,
I am using SQL Server Change Data Capture to track my changes in my Web Application. But the problem I am having is, if I have one page which is updating multiple Tables, parent and child tables in one transaction, how can I track all those tables transactions at one time, because sometimes page can update some tables and may not update other tables. As CDC is working on individual tables it is very difficult for me whether the transaction has updated particular tables. Can anybody please help me how to track the changes on the tables in that approach.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi All,
I am using a stored proc which is using a depending upon that it is pulling some values and make some changes in the database, what I need to do is to throw an error when date conversion from that string or text fails and should not perform further action.
Can anybody please help me with this, any code snippet, a link or even a suggestion helps me, thanks in advance. Below is my code
ALTER procedure [dbo].[TrackUpdatedColumnNamesProc]
(@DateToTrack nvarchar(max))
as
begin
--declare @DateToTrack nvarchar(max)='2016-03-18'
delete from TrackUpdatedColumnNames;
--check here (as soon as conversion of @DateToTrack fails I shouldn't be doing any logic
--doing the logic here
end
GO
declare @DateToTrack nvarchar(max)='2016-03-18'
exec [dbo].[TrackUpdatedColumnNamesProc] @DateToTrack
GO
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
So use try/catch in the stored proc.
However you are not processing sequentially, that requires a cursor or while or a CTE, so you should test the date conversion before performing the delete. Pretty sure there is and IsDate function in TSQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah sure thank you very much buddy.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
The simplest option is to change your parameter's type to date. Then, if the caller tries to pass in a value that isn't a date, an error will be thrown, and the procedure will not be executed.
You should always use an appropriate data type for the data you're dealing with. Storing dates in strings is a sign of a bad design.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi All,
I am using CDC to capture the modified data, all I need to know is which columns are modified on a particular day so that I can extract those modified columns.
I am able to do it by using the CaptureInstance (like I have to use the name like this: cdc.fn_cdc_get_all_changes_CDC_Provider) name hard coded function, Is there any way I can get it by using the run time without using the hard coded name.
SELECT --sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time', CAST(sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS DATE),
@captureinstance, @ColName, sys.fn_cdc_is_bit_set(@TrackIfColumnChanges, __$update_mask)
--,fn_cdc_get_all_changes_CDC_Provider.__$operation
FROM cdc.fn_cdc_get_all_changes_CDC_Provider(@from_lsn, @to_lsn, 'all')
WHERE sys.fn_cdc_is_bit_set(@TrackIfColumnChanges, __$update_mask)=1
AND CAST(sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS DATE) = CAST(GETDATE() AS DATE)
Can anybody please give me any idea any code snippet, a link or even suggestion would help me a lot.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 17-Mar-16 20:24pm.
|
|
|
|
|
What is CDC?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh sorry man, its Change Data Capture  , somehow I am able to reach it with SQL coding but if I can capture the column names only with the some settings it will be easier.
Thanks in advance buddies
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 17-Mar-16 20:32pm.
|
|
|
|
|
Sorry for a stupid question.
I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser.
User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen.
In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this:
ID_USer.
Date_Change.
Reason_To_Change.
..........
Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field.
I am wondering if this design is ok? Because the more table, the more log table in my Design.
Please help me to design better.
THanks.
|
|
|
|
|
Why would you duplicate all the data from one table in the log table? Use key values, or just save the userid (or whatever you use as identifier) and the transaction details. Then when you want to print a report you can lookup the user's details by the identifier.
|
|
|
|
|
We use triggers spit to populate the following table. IMHO this is the only valid use of triggers spit. This also services all tables that are being audited.
CREATE TABLE [dbo].[AuditLog](
[AuditID] [INT] IDENTITY(1,1) NOT NULL,
[Action] <a href="1">CHAR</a> NULL,
[TableName] <a href="128">VARCHAR</a> NULL,
[PrimaryKeyField] <a href="1000">VARCHAR</a> NULL,
[PrimaryKeyValue] <a href="1000">VARCHAR</a> NULL,
[FieldName] <a href="500">VARCHAR</a> NULL,
[OldValue] <a href="8000">VARCHAR</a> NULL,
[NewValue] <a href="8000">VARCHAR</a> NULL,
[ModifiedDate] [DATETIME] NULL DEFAULT (GETDATE()),
[UserName] <a href="200">VARCHAR</a> NULL
) ON [PRIMARY]
Actions being Add,Edit,Delete
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
Just after opinions here: which of these would be the best practice for an SQLite database:
DELETE FROM Messages WHERE Account = @acct AND NOT EXISTS (SELECT * FROM temp.IdList WHERE MailId=Id)
or
DELETE FROM Messages WHERE Account = @acct AND Id NOT IN (SELECT MailId FROM temp.IdList)
Indexes exist for both Messages(Account, Id) and temp.IdList(MailId)
Each table may hold in excess of 20000 rows
From what I understand, the correlated exists will need to be evaluated for each row of the Messages table, but will stop evaluating as soon as it finds a match (and being indexed, should be pretty quick). However, with the IN expression, the full result set is evaluated only once, but the comparison will be against that full set.
Cheers,
Mick
|
|
|
|
|
Just going by your analysis, I would say the NOT EXISTS would be better. As you say if the table is indexed it should be pretty quick.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I cannot speak for SQLite however I can for SQL Server(which perhaps does not help very much here).
It is best to never use a NOT IN statement, where the results returned could contain many rows, as it will need to evaluate for every single row in the inner select.
Whereas with a NOT EXISTS or a LEFT JOIN with a IS NOT NULL condition, which is another option, it will evaluate as false as soon as it hits the first row where there is a match.
So basically I agree with your evaluation - for what it's worth I always use NOT EXISTS nowadays.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
|
NOT IN and NOT EXISTS are not directly comparable since they treat nulls differently. EXISTS returns TRUE or FALSE only, IN is trivalent and returns TRUE, <code>FALSE or NULL.
Use the one that gives you the result you expect. (Most probably NOT EXISTS)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
