|
I am building a reporting database that is pulling data from several source and different databases.
The plan so far is to turn on change data capture on the source databases and process the log generated using SSIS.
I can find a few papers and sample online on how to process the ubiquitous INSERT/UPDATE/DELETE log and it is helpful but I fail to find any good sample or tool with multiple tables.
I can't see how I can process the items in the right order and keep integrity.
Let's say we have a classic database with Product, Order and OrderLine.
And this scenario:
8:00 - Create a product P1
8:01 - Create an Order O1
8:02 - Create an Order Line L1
8:03 - Delete the Order Line L1
8:04 - Delete the Order O1
8:05 - Create a new Order O1
8:06 - Create a new Order Line L1
The cdc tables will contain
For Product
8:00 INSERT P1
For Order
8:01 INSERT O1
8:04 DELETE O1
8:05 INSERT O1
For Order Line
8:02 INSERT L1 Order:O1 Product:P1
8:03 DELETE L1
8:06 CREATE L1 Order:O1 Product:P1
To keep the referential integrity of the reporting database, I would need to:
- insert order lines after orders
- delete order lines before orders
Because of this I can't really treat a CDC at a time.
As I see it the only way would be to process all CDC simulataneously in datetime order.
Is there ways to do that?
The source databases are Oracle and MS SQL and possibly text files.
The reporting database is MS SQL
|
|
|
|
|
Wow in 20+ year of writing reporting systems I have never had to consider this for of replication, imagine reading the logs!
Why do normal replication tools (something else I know little about) not meet your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hello veri nice
http://www.hobby-zoo.ro
|
|
|
|
|
One idea could be do to disable all relations/checks/constraints on the report tables, execute CDC statements and then re enable relations/checks/constraints.
Your execution should handle errors nicely though for this to work.
Shreekar
|
|
|
|
|
i have to schedule backup of particular schema objects, using sql server agent jobs.
in database there is five schema like
schema [a]
[a].tbl_a -- Table
[a].usp_a --Procedures
[a].fn_a -- udf
schema[b]
[b].tbl_b --Table
[b].usp_b --Procedures
[b].fn_b --udf
where [a] and [b] are the schemas.
now i have to schedule full backup of only "[a]" schema objects.
And there is only one file group(Primary) for database.
using sql server agent job,is it possible or not?
|
|
|
|
|
Have you tried googling it?
|
|
|
|
|
Here is suggestion which I have not tried with database backups but I have used from scheduling jobs with the windows scheduler.
Create a batch program(text file with DOS commands, and with extension .bat). In the batch program connect to -iSQL and pass to it a .sql file that contains the backup instructions.--> That means you also have to create a .sql file. Now create a schedule in windows scheduler that executes the bat program
Windows Scheduler calls mybackup.bat; mybackup.bat calls -isql which in turn calls mybackup.sql.
Test the whole thing on test on test database.
|
|
|
|
|
Hello,
I just started learning TSQL Stored procedure (SQL Server 2005) and looks like it does not have strong set of functions that I could do in programming languages. Anyway, I wanted to return a string type data which holds comma separated value read from a temporary table. After spending hours, I came to the following code that WORKS. But I am wondering there should be more efficient way to do that. My following code runs at least 2 query to the temp table in each iteration to build the string where one of the query uses Nested SQL statement which can be more expensive in performance. So, Would anyone please tell me is there any better way to do that ? I thought there would be SKIP operation but SKIP operator is not being accepted by my SQL Server. IF there was any SKIP operator, at least I could save a nested query. Anyway, your help would be highly appreciated.
Regards.
DECLARE @Iterator INT
SET @Iterator = 0
WHILE (@Iterator < @totalIds)
BEGIN
IF(@Iterator = 0)
BEGIN
SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds
END
ELSE
BEGIN
SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50)))
FROM #deletedIds
WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds);
END
SET @Iterator = @Iterator + 1
END
|
|
|
|
|
That looks a real bottleneck: for N entries in the result list, you perform 2N-1 SELECT statements, and N-1 string concatenations, which must be as bad in SQL as they are in any OO language (remember the purpose of StringBuilder versus string in C#).
Are you sure you really need a comma separated list at the SQL level? If you were to perform a simple SELECT statement, you would get a result table that contains the data you want, one result element per row. At practically no cost.
Too often IMO people tend to find an SQL-only solution to a problem. This may well be one more example of that.
|
|
|
|
|
Hi Luc,
Thanks for your reply. Yes, thats what I realize, I would never do the same thing in my programming. (I am a C# programmer) But the problem is, I dont know the correct TSQL Syntax / operators that can let me get rid of this loop.
My intention to get data from stored procedure in the following structure
-------------------------
string product_name,
int affectedRows,
bool hasError,
int[] someIds
-----------------------
Now, everything was fine until I reached the need to get int[] someIds. I was lost, how I could do that. If I just needed to return SomeIds (: int[]) then, yes, I could simply return the result set, but as I needed to get the other string, bool, int etc type data so I created a temporary table in my procedure to store the int values (someIDs : int[]) then, I used the specified procedure to read back the int ids from the temporary table and serialize as comma separated string to return.
Would you please show me any technique to get such data structure from stored procedure ?
|
|
|
|
|
I wouldn't know how to do that, and that is due to the fact that I would never want that to begin with. Get a table result, and have the client code iterate over it. That is simple and inexpensive.
|
|
|
|
|
|
Hi Piebald,
Thank you sooooooooooooo much for the kind help. Yes, the COALESCE function is what solved my entire problem. In fact, the link you provided discussed the exactly same problem what I had to solve, so that page just did my task completely. I highly appreciate your help.
|
|
|
|
|
The difference between a conventional programming language like C# and a data oriented language like T-SQL is that T-SQL is "set" oriented. In other words, it has been designed to work with "sets" of data rather than a single piece of information. Your code seems to work with one piece of information at a time which defeats the purpose of using T-SQL.
Also I noted that you have temporary tables. Using Table Variables[^] may improve performance. Note that Table Variables have certain restrictions.
|
|
|
|
|
Hi Shameel,
Thank you very much for your suggestions. I got the problem solved by COALESCE function that was suggested by the previous reply. But The Table Variable over temporary variable is a nice thing that learned from your link.
Regards.
|
|
|
|
|
Oops, sorry, this is my first post and I couldn't see that the question was already answered. Please ignore.
I am not sure if I understand the question but here are the results of my testing...
-- Original query with bits added in so I get a result set
DECLARE @totalIds INT; SET @totalIds = 2
DECLARE @DeletedIDArr VARCHAR(50)
DROP table #deletedIds
SELECT * INTO #deletedIds FROM
(SELECT 1 AS Id UNION SELECT 2) a
DECLARE @Iterator INT
SET @Iterator = 0
WHILE (@Iterator < @totalIds)
BEGIN
IF(@Iterator = 0)
BEGIN
SELECT TOP 1 @DeletedIDArr = CAST(Id AS varchar(50)) FROM #deletedIds
END
ELSE
BEGIN
SELECT TOP 1 @DeletedIDArr = (@DeletedIDArr + ',' + CAST(Id AS varchar(50)))
FROM #deletedIds
WHERE Id NOT IN(SELECT TOP (@Iterator) Id FROM #deletedIds);
END
SET @Iterator = @Iterator + 1
END
SELECT @DeletedIDArr
-- My version
SET @DeletedIDArr=''
SELECT @DeletedIDArr = @DeletedIDArr+CAST(Id AS varchar(50))+',' FROM #deletedIds
SET @DeletedIDArr = LEFT(@DeletedIDArr,LEN(@DeletedIDArr)-1)
SELECT @DeletedIDArr
|
|
|
|
|
I have created a SP and using Oracle 11g.
I have declared a variable in a procedure as:
v_juriscode billingcycle.juriscode%TYPE; (an existing type)
.
.
.
I have a condition as
IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;
UPDATE DEBADGERPROGLOG SET JURISCODE=v_juriscode
WHERE UIDBADGERPROGLOG = V_UID;
When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition).
I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens.
Thanks in advance.
Lionel Noronha
|
|
|
|
|
IF n_count > 1
THEN
v_juriscode := 'LODESTAR';
ELSE
IF n_count = 1
THEN
SELECT pm.dejuriscode
INTO v_juriscode
FROM pwrline.lsmdphysicalmeter pm
WHERE pm.decommdevid = V_COMMDEVID;
END IF;
END IF;
IF v_juriscode IS NOT NULL THEN
UPDATE DEBADGERPROGLOG SET JURISCODE=v_juriscode
WHERE UIDBADGERPROGLOG = V_UID;
END IF;
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
lionelcyril wrote: When the condition c_count = 0 is encountered I have not set the value for juriscode (ie i hv not mentioned that condition).
I have noticed that when no value is set to the v_juriscode variable the table row is not updated, I wanted to know why this happens.
Precluding the previous answer that statement is contradictory.
If the update runs, the where condition is satisfied and no error occurs then an update did happen. There is no alternative.
If an update occurs then a value will be updated. There is no such thing a "no value" unless you mean null (presumably you don't mean spaces.) If you didn't set a value then null is used.
If you don't want an update if there is no value then use the code from the other response.
If you want an update with a different value then set v_juriscode to a default value before you execute the rest of the code.
|
|
|
|
|
Hi guys,
I am looking for the best ways to create an api which will monitor the database table row version.
if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information:
(1) LogIn detail of the data server.
(2) Access database name with datatime.
(3) Affected table name, row version and row number as well.
I will be glad for your any suggestion / ides on that.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I currently use
HOST_NAME() and
APP_NAME() in a trigger for insert, update and delete to monitor for anything that wasn't supposed to happen. You can then include any of the detail from INSERTED and DELETED tables as well as connection info.
HTH
|
|
|
|
|
Hi,
Thanks for the replay, well if we consider the scenario given below:
if any user:
1. logIn to the database -> open the table in design mode--> block the trigger.
2. Update a single column value.
3. Unblock the trigger - > Save the table.
I am thinking on how could I detect the changes from my C# application.
Thanks once again for your valuable input.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I think your app would need to hold a copy of the table and constantly compare, and I doubt that's worth the trouble, so don't bother trying.
You can't protect against a malicious authorized user. There are also times when you legitimately want to correct some data and make it appear to have always been correct.
In my opinion, an app should never hold onto data for longer than it needs it; when it needs the data again it should query it again.
|
|
|
|
|
Doing a trace isn't the same as an intruder-protection, and those are both different from versioning a row. I'd suggest you create a fingerprint for the data.
What are you trying to achieve/prevent exactly?
Bastard Programmer from Hell 
|
|
|
|
|
Hi all! I would like to know the SQL that can grant select on a Database Role to another role.
I want members of one role to view and see members in another role so thet the first role can select members from the second role. I don't know how to do this. Please help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
