|
The System.Data.OracleClient in .NET is now obsolete (deprecated).
So I installed the ODP.NET from Oracle (I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll).
When running VS, I see both under the Add References (not really sure of what the difference between version).
I am using .NET Framework 4.0 so I included 4.112.3.0 version in the project.
Problem is that the Microsoft and .NET framework versions yield different results.
For instance the Oracle version won't permit || in the data set (aka SELECT A || B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc to make valid table adapter SQL statement in the ODP.NET version.
Additionally, it appears not to function identically so worried about potential errors being introduced in the datasets.
Is there any clear article identifying the differences between the two libraries (most articles say just to replace but that is the issue, they are not identical).
|
|
|
|
|
Dale Haessel wrote: I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll
The first number in the Oracle ODP version indicates which framework version it's intended for.
Dale Haessel wrote: For instance the Oracle version won't permit || in the data set (aka SELECT A ||
B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc
to make valid table adapter SQL statement in the ODP.NET version.
I never had that problem, what error message do you get?
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Using VS dataset / table adapter design wizard, it gives:
The wizard detected the following problems when configuring the TableAdapter: "RCM_MRO_BOM": Details:
Generated SELECT Statement
Error in SELECT clause: expression near '|'.
Missing FROM CLause.
Error in SELECT clause: expression near ','.
Unable to parse query text.
...
Wizard Configuration Text =
SELECT BOM_REF_KEY, DESCRIPTION, BOM_REVIEWED, COMMENTS || ' ' as X, 1 as MatchType FROM SAPRCM.RCM_MRO_BOM a
|
|
|
|
|
Aha, the design wizard. That sucks big donkey balls if you excuse my expression, and not just when using with Oracle in my opinion.
Use Oracle SQL Developer[^] for creating your queries instead, or SQLTools[^] if you want something lightweight and high performance.
I personally also prefer DataReaders instead of TableAdapters, but the comparison and reasoning is to much for a forum post on my behalf, read Piebalds post[^] for some reasoning, otherwise I'd recommend Google.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Dear all,
I am new to the SSIS. I have many tables from old database (SQL 2000) and have to transfer to a new database (SQL 2012).
I have created one SSIS project and have successfully shipped the data of one table already. I have also added a 'Execute SQL Task' to clean out table and reset identity column before the 'Data Flow Task'.
Now what is the best way to import the rest? Import all tables in just one package? Or create one package for each table and create one package to 'call' all other package one by one?
I think it is possible to create one package and use parameter to go over a pre-defined set of tables.
|
|
|
|
|
Why SSIS, do you expect to have to repeat this process?
If it is a one off exercise then I would set up a linked server and drag the data across using scripts, saving the scripts of course.
Actually I would backup and restore onto the 2012 server if it will work, I'm not sure the restore would work over such a wide version range!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well, I have already imported several tables. And the last one is a painful one. As for the last one, I still have to split that (old) one into five new tables.
I think I am going to try to use the linked server approach. Thanks.
|
|
|
|
|
i m a fresher graduate. tell me which feild should i choose DataBase admin or software developer? please suggest me...
|
|
|
|
|
|
| No
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Whichever one you are interested in. Seriously, you cannot expect people who know nothing about you to be able to advise you on a choice of career.
Use the best guess
|
|
|
|
|
ok..thanks..i am intrested toward database so how to get started with a dba profile..
|
|
|
|
|
|
None.
Find out what you are able to do as a job, then go back to college/university and learn that.
|
|
|
|
|
DBAs tend to get paid more. DBAs are more likely to be on call all the time. DBAs tend to be very specialized and the skills that make them valuable don't translate well to other databases nor to other types of work. Smaller companies find it harder to justify paying a DBA and also find it harder to provide enough tasks for them to do.
saurabhspk wrote: i m a fresher graduate
Depending on what that means exactly you are probably unlikely to find a DBA job. At best only the largest companies would be willing to hire someone without experience to be a DBA.
|
|
|
|
|
DBAs are like magicians.
^_^
|
|
|
|
|
Database Admin and software developer both are good. But if you have a mind to start a business(IT) means choose software developer. And choosing correct and growth platform.
|
|
|
|
|
Hi,
I have a Master table A(AId, Value) and Detail table B (BId, Value, AId). AId and BId are Identity increment columns. The value field in B comes from some other table C, value field in A table is always the same value: 1.
When I am inserting Detail table B, I am doing it with a select statement on the other table C. The problem is filling the foreign key AId value in Table B. First thing to fill it is, if there is some AId already there in the B table then we have to assign that AId to it.
If there is no AId in the B table for the correspoding value, we have to insert a record in the A table and that AId should be assigned to the AId field of that row. For this given scenarion I am struggling to write a stored procedure.
I tried to write another function to be called inside the stored proc, but funtions in SQL Server dont support Insert inside the UDF.
Now trying to use Stored Procs return values in the Insert statement, I dont know so far no success. I am searching too. If anybody can please help me that would be really great.
Thanks in Advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
Do this in 2 steps, insert the master record, get the ID and then insert the details records.
I use 2 separate stored procs called from the DAL. The first one inserts the master record and returns the record inserted based on using scope_identity() to get the ID (you can return the ID only if you don't need the entire record.
The second proc inserts the detail records using the ID from the record/integer returned by the first proc.
You can write it into 1 proc but I find that irritating if you need to store more than 1 detail record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is it better to use cursors or is it better to use multiple stored procs to do the same. Because the same thing I can achieve by using Cursor in stored proc. But its really really slow. Like may be its processing 23000 records per 40 minutes.
I need to process 1 million records.
And as you said If I call the database for each insert, isnt it going to be more back and forth traffic from the application database server vice versa.
I tried my best to avoid cursors, only one way is to use function but I have to use my connection with some special permissions to execute Insert command within UDF. It seems its not easy in the organization.
I am trying to put the insert logic in the stored proc and calling that stored proc within fuction but it is asking for the below permission. I am not sure if I can really do that in this way but when I am trying it failed to execute with the below error. Then I went for the Cursor approach but its running for too long. Please if you can please advice me something, I have to finish it as soon as possible.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
If you are inserting 1m records through a UI then you have bigger problems, I'd seriously look at your business logic.
Where are you getting your 1m records from?
Why are you inserting then one at a time?
Nothing is going insert 1m single records with any acceptable speed. You need to look at how to get the bulk data into your database and then process the data in bulk!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am getting the records from two different databases and I have to put them into another database. I have to run a nightly job to perform this operation.
I am not saying I am inserting one record at a time, I am doing it using cursor right now. But its taking too long time. Is there any other approach to do it.
The insert process has to have the logic that I explained in the thread.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
indian143 wrote: I am not saying I am inserting one record at a time, I am doing it using cursor
right now
Yes you are, a cursor implies you insert the master then insert the detail and loop to the next master!
How are you transporting from the 2 databases to your DB?
Idea
Set up a view that joins the master/detail in the source systems and load all the records into a staging table in the destination DB, then process the inserts in bulk using 2 queries (if the DBs are linked then use the view direct).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is it ok if I do it using xp_CmdShell or OpenRowSet functions, because I need to have insert logic in my function. If there is any other approach without using these three CmdShell, OpenRowSet, Cursor, please give me that advice.
staging is not an option for me right now.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
indian143 wrote: staging is not an option for me right now.
IMHO you are screwed, your thinking is too small, no method that processes a single record at a time is going to perform when attempting to process 1m records! It seems like you are being artificially constrained by policy/political issues not technical ones.
If you can't use a staging process on the target DB then create a staging DB on the same server that does the processing and shoves the data into the target DB.
I have no experience with either openrowset or xp_cmdshell in a data loading context, never used them for that - ever!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
