|
Member 8701813 wrote: my stored procedure is very big.
That's a design-problem. Cut it into smaller pieces that are easier to debug/verify.
Member 8701813 wrote: i am not able to debug...
It means that the debugger doesn't work; I'd suggest running the sproc and see where it fails, and with what error. Past that part in a post here, and we'll have a look 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
hi,
you are telling that your sp is big instead of at one time u debugging can u cut that sp and check which part is throwing error in your Sp because i had also tried like that only when i faced this situation. I think it will help you.
Regards,
S Azarudhin
|
|
|
|
|
see my trick posted in code project, it may help you, however you will have to modify the existing code to call the logger on all the places where you feel the exception handling is weak or may need some values to trace the reason.
Logging error, debug, info messages into log tables
Sudhir Kankal
|
|
|
|
|
try to print messages over the screen it really helps you!!!!
Mani Prabhakar
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
