|
We do it somewhat different to Mark, our staging table are exact replicas of the source data, this allows us to use Bulk Copy when loading the data. It also insures there are no errors in the Load part of the ELT process.
We then use stored procedures to do the transforms to the production format (these may also be staging tables in some solutions). The major benefit is that you are doing any transforms on a limited data set and it is easy to debug the procedure. It then becomes a simple matter to dump in the staging data to the production tables.
I find a well crafted ELT process leave any ETL application in the dust. There are some ETL apps that this may not apply to but the cost is extraordinary.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: our staging table are exact replicas of the source data, this allows us to use
Bulk Copy when loading the data. It also insures there are no errors in the Load
part of the ELT process.
I do the same thing, works great plus if there are any questions about the source data you already have a loaded copy in a table to query against.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
The second greatest scam after Y2K that the IT industry has ever perpetrated is ETL! Extract - Load - Transform simplifies and speeds up the entire process.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: The second greatest scam
I have worked for some CIO's that have come up with way more atrocities than ETL or Y2K.
Mycroft Holmes wrote: Extract - Load - Transform simplifies and speeds up the entire process.
I think you can write bad code / processes in any language or tool. It's up to the person to find the best way to do it that works for them. Not sure about you but I have some amazingly fast SSIS packages that load a gig of data in seconds, but have seen some others that take hours doing a lot less data.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Hi
To learn entity frame work
Do I need to learn linq?
thank you
|
|
|
|
|
that could be quite handy
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hello All,
If I use a try-catch block in my stored proc, and in the catch block write error to table, do I need to use BEGIN TRANSACTION - COMMIT TRANSACTION to get the try-catch block to work?
I am using -
BEGIN TRY
write my code here
END TRY
BEGIN CATCH
write my code here
END CATCH
But should be - to write errors to a log table.
BEGIN TRY
BEGIN TRANSACTION
write code here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
write code here
END CATCH
|
|
|
|
|
no you do not have to(but it is a safer option)..Have a look at the following snippet..-
USE AdventureWorks2008R2;
GO
-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError
DECLARE @ErrorLogID INT;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Call procedure to print error information.
EXECUTE dbo.uspPrintError;
-- Roll back any active or uncommittable transactions before
-- inserting information in the ErrorLog.
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO
|
|
|
|
|
Can someone answer these following questions:-
What is FaultException (WCF related)?
When does it occur; as in, is it a problem related to service configurations or is it an error in program logic?
|
|
|
|
|
|
The thing is I went there before I came here. I want some meaty explanation(from practical experience) rather than the cut and dried documentation provided by Microsoft.
PS:Seeing those huge tables and lists at the start of the page itself was a huge turnoff. 
|
|
|
|
|
Hi All ... in my form app. i have textboxs related databindings yonet_ogrt and I have 2 datagridview yonet_prg
and yonet_nbt.. so I want to all together navigate but textboxs and datagridview related yonet_prg is working but
datagridview related yonet_nbt not working... this is my trouble...
My code like this...
public void vt_iliski()
{
try
{
string sorgu_ogretmen = "select * from ogretmen";
string sorgu_program = "select * from program";
string sorgu_nobet = "select * from nobet";
ds_gnd = new DataSet();
DataTable tablo = new DataTable();
adpt_ogrt = new SqlCeDataAdapter(sorgu_ogretmen, baglan());
adpt_prg = new SqlCeDataAdapter(sorgu_program, baglan());
adpt_nbt = new SqlCeDataAdapter(sorgu_nobet, baglan());
adpt_ogrt.Fill(ds_gnd, "ogretmen");
adpt_prg.Fill(ds_gnd, "program");
adpt_nbt.Fill(ds_gnd, "nobet");
/* ********** relation -1 ****************** */
DataColumn ogrt_kolon = ds_gnd.Tables["ogretmen"].Columns["ogrt_ID"];
DataColumn prg_kolon = ds_gnd.Tables["program"].Columns["ogrt_ID"];
iliski1 = new DataRelation("Ogretmen_To_Program", ogrt_kolon, prg_kolon);
ForeignKeyConstraint kisitla = new ForeignKeyConstraint(ogrt_kolon, prg_kolon);
kisitla.UpdateRule = Rule.Cascade;
kisitla.DeleteRule = Rule.Cascade;
kisitla.AcceptRejectRule = AcceptRejectRule.Cascade;
ds_gnd.Tables["program"].Constraints.Add(kisitla);
ds_gnd.EnforceConstraints = true;
ds_gnd.Relations.Add(iliski1);
yonet_ogrt = new BindingSource(ds_gnd, ds_gnd.Tables["ogretmen"].ToString());
yonet_prg = new BindingSource(yonet_ogrt, "Ogretmen_To_Program");
/* ********** relation-2 ****************** */
DataColumn prg_kolon_nobet = ds_gnd.Tables["program"].Columns["prg_ID"];
DataColumn nobet_kolon = ds_gnd.Tables["nobet"].Columns["prg_ID"];
iliski2 = new DataRelation("Program_To_nobet",prg_kolon_nobet , nobet_kolon );
ForeignKeyConstraint kisitla2 = new ForeignKeyConstraint(prg_kolon_nobet, nobet_kolon);
kisitla2.UpdateRule = Rule.Cascade;
kisitla2.DeleteRule = Rule.Cascade;
kisitla2.AcceptRejectRule = AcceptRejectRule.Cascade;
ds_gnd.Tables["nobet"].Constraints.Add(kisitla2);
ds_gnd.EnforceConstraints = true;
ds_gnd.Relations.Add(iliski2);
yonet_prg = new BindingSource(ds_gnd, ds_gnd.Tables["Program"] .ToString());
yonet_nbt = new BindingSource(yonet_prg , "Program_To_nobet");
}
catch (Exception hata)
{
MessageBox.Show("Okul Günlük Nöbet Defteri iliskilendirme Hatasi olustu : " + hata.Message + " " + hata.StackTrace );
}
finally
{
baglanti.Dispose();
adpt_ogrt.Dispose();
adpt_prg.Dispose();
adpt_nbt.Dispose();
}
|
|
|
|
|
|
The following subquery returns 1024 records with a single count column. I am trying to return multiple columns with 1024 records. When I make changes to return multiple columns I get 17,000 records instead of 1024 records. I have very litle experience with subqueries. Does anyone have any suggestions?
SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Machine Count',
A.hotfixID AS 'Last Contact'
FROM (SELECT HF.computerid, HF.hotfixID
FROM hotfixdata HFD
INNER JOIN hotfix HF
ON HF.hotfixid = HFD.hotfixid
AND HFD.ignore <> 1
LEFT OUTER JOIN Computers AS C
ON C.ComputerID=HF.ComputerID
WHERE INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
GROUP BY HF.hotfixID, HF.ComputerID) A;
|
|
|
|
|
|
I have a class and the data in it has to be stored in the database. I have done this by extracting every single property and sending each of them as input parameters to a stored procedure in the database. But the thing is that the class has too many properties. Is there any way to accomplish this in a single go(lesser amount of code)? OH and I am using C# and Sql Server!
|
|
|
|
|
Presumably it throws an exception and posting that exception as part of your post is helpful.
Presuming that the exception is telling you the statement is too big...
If your SQL statement is too long then SQL/C# will have a problem with it. The solution is to reduce the size. (Might note that that could indicate a database design issue as well.)
Using the database import tools (sqlldr?) might help with the problem but might not. If not or you don't want to use it then solutions.
1. Do two calls.
2. Pass in a blob and parse it to create dynamic sql.
If it was me I would presume that there is a design issue.
|
|
|
|
|
As has been mentioned, if the table has too many field then there is probably a design problem.
Communication with the database is donkey work, most developers use or write a code generator for this. I have not hand coded DAL for many years.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The thing is I am still new. Is it not good that we do more of the grunt work at the starting stages itself? And DAL coding is quite hectic. Sometimes I really feel why do we need it anyway. Thank you for eye opener atleast.
|
|
|
|
|
First, I am an absolute "database-beginner" 
Here is my szenario in my daily business-live:
I have a lot (~30) different datatables which I combine to produce usefull-information.
The main topics are:
a)
My datatables are stored in xml-files. The xml-files are actually generated by reading out information from other tools in my business
b)
For the "production of usefull information" (this is reporting) I use my own proprietary C#-Programs, where I perform "the logic". Means: Filtering, Joining, Combining,.....
c)
The final result is written into Excel-Sheets. The end-users use these excel-files for their needs
Here is my question:
Especially, step b, with my proprietary C#-Programs needs a lot of time for maintenance/updates/.... due to heavily changing environment. It is time-consuming to maintain it.
How do "professional database-engineers" handle such a task?. What is their approach? Which tools are they using?
modified 9-Nov-12 7:14am.
|
|
|
|
|
Search a topic like "Business Intelligence" and you will get an idea of what others are doing.
At a high level, you have a central database which collects data from different systems, then you have reporting tools which either you or your user community can write their own reports. Also, sometimes you can have these reports run on a scheduled basis and have their output delivered to whoever needs it.
The cost of implementing such a system can be quite high.
You might want to implement some pieces of this.
For example:
1) Build a tool which will load your data extracts (xml files) into a central database.
2) Create views or stored procedures which "normalize" your data
3) Use a reporting tool to generate the reports you need or show your users how they can connect to the central database via Excel to get their own data. (See topics like Microsoft Query)
Empower the user to access the data and you will spend less time on maintenance.
Good luck.
|
|
|
|
|
Frygreen wrote: My datatables are stored in xml-files. The xml-files are actually generated by reading out information from other tools in my business
This is just wrong - extracting data from other tools is valid, moving them to xml files is probably the worst platform to work from.
Get hold of sql server, use Express if cost is an issue (has a size limit of 4gb). Your current xml files can be loaded into tables in a sql sever database.
I would seriously think about getting someone to normalise and organise your data for you it is a non trivial job. Although if you have already done this with xml you may be able to handle it.
Use SQL Server Reporting Services SSRS to produce your canned reports (no analysis or drill down) and look into Integration Analysis Services for data analysis beyond the static reporting requirement.
I may sound SQL Server centric b/c I am, it is by far the best tool for departmental database processing. Don't piss about with Access or Excel go straight for the professional tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your first replys. I got some ideas.
But, of course, more replys are welcome
|
|
|
|
|
we are using sql server 2008 in desktop application but client machine my database show i want database not show and secure my database please tell me what can i do.
thanks
badal
|
|
|
|
|
You can't "hide" your database, you need to apply authentication and authorisation to your database. This will limit what the user can see and do.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
