|
The only way to do it is the one you have used, dynamic sql.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
But it does not work and I am stumped. Thanx for your help.
I am working through your Pivot article too see if it applies to what I am trying to do. Good article!
|
|
|
|
|
JTRizos wrote: select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = "Yes"where EmployeeID = '+@Eid
Print this variable and run it directly in SSMS, see if it updates, I think you problem is simply formatting the string!
try changing "Yes" to 'Yes'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanx for the suggestion.
Printing the variable @command displays the correct Update command:
update EmergencyContact.dbo.CSERolesRolledUp set AddRequesterInformation = "Yes" where EmployeeID = 38
But I get an "Invalid column name 'Yes'" error and no update is done.
Feeling a bit more reassured that this will work but need to figure out why the error. At least you did not say this would not work.
Thanx again and Merry Christmas!
|
|
|
|
|
change the quotes around yes to single quotes '
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I got it to work. Followed your advise from your previous reply and after a few tries, bingo, it worked. Here's the code in case it can help others. I am leaving the print statements I used for testing.
Declare @colname varchar(200), @Eid varchar(50),@message varchar(80),@command varchar(200)
Declare my_cursor CURSOR
For Select replace(replace(replace(SecurityRole,' ',''),'/',''),'-','') as SecRole,EmployeeID
from EmergencyContact.dbo.CSEEmployeeRoles
order by EmployeeID, SecRole
open my_cursor
fetch next from my_cursor into @colname,@Eid
while @@fetch_status = 0
begin
select @message = @colname+' '+@Eid
--print @message
select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = ''Yes''
where EmployeeID = '+@Eid
exec (@command)
print @UpdDate
--print @colname
--print @command
fetch next from my_cursor into @colname,@Eid
--print @colname
end
close my_cursor
deallocate my_cursor
Thanx again for your help. Having never used Cursor or Dynamic SQL before, I just needed to know it can be done and I was on the right track.
Happy Holidays! 
|
|
|
|
|
Looking for some expertise with explains. I've a database procedure that is refusing to behave properly since it has been migrated from Oracle 9i to Oracle 10g. It runs completely in under 3 minutes on my Oracle 9i database, but on 10g it will take either 32 minutes or 10 minutes to complete depending upon whether the statistics are estimated or the table is analyzed. I've been using the DBMS_PROFILER and have narrowed it down to a for loop involving a cursor. However when I explain the cursor, I not only have two different plans, 9i vs 10g, but the costs are completely different, except that 10g is the much reduced costs!
Oracle 9i EXPLAIN
Operation Options Object Optimizer
------------------------------ --------------- ------------------------- -------------
SELECT STATEMENT Cost = 1405 CHOOSE
FILTER
TABLE ACCESS FULL TRANSACTION_TABLE ANALYZED
FILTER
NESTED LOOPS OUTER
MERGE JOIN CARTESIAN
FILTER
NESTED LOOPS OUTER
INDEX UNIQUE SCAN XPKTRANSACTION ANALYZED
INDEX RANGE SCAN XPKTRANSACTIONLOT ANALYZED
BUFFER SORT
INDEX RANGE SCAN XPKTRANSACTION ANALYZED
INDEX RANGE SCAN XPKTRANSACTIONLOT ANALYZED
Oracle 10g EXPLAIN
Operation Options Object Optimize
------------------------------ --------------- ------------------------- ---------
SELECT STATEMENT Cost = 374 ALL_ROWS
FILTER
TABLE ACCESS BY INDEX ROWID TRANSACTION_TABLE ANALYZED
INDEX RANGE SCAN IXTRANSACTION_FIFO ANALYZED
FILTER
HASH JOIN RIGHT OUTER
INDEX RANGE SCAN XPKTRANSACTIONLOT ANALYZED
NESTED LOOPS
FILTER
NESTED LOOPS OUTER
INDEX UNIQUE SCAN XPKTRANSACTION ANALYZED
INDEX RANGE SCAN XPKTRANSACTIONLOT ANALYZED
INDEX RANGE SCAN XPKTRANSACTION ANALYZED
Anybody with some good explain foo that could suggest some things to try in order to speed this puppy up. Thanks. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Not easy to say as I don't know the size of the tables or how the query looks like
I would also like to see the cost, card and byte for every level in the plan.
But, more often than one might believe, a full table scan might be the faster choice.
So try the query on the 10g with a FULL hint.
Or as <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968">Tom Kyte</a>[<a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968" target="_blank" title="New Window">^</a>] has said: "full scans are NOT ALWAYS evil, indexes are NOT ALWAYS good"
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Jörgen Andersson wrote: Or as Tom Kyte[^] has said: "full scans are NOT ALWAYS evil, indexes are NOT ALWAYS good"
For many years I tried to nail down the best practice when indexing and tuning a database (generally sql server). Every time I had a DBA cornered I would quiz them on this subject and every time I would get back the namby pamby response "it depends", what sort of response is that, I want to lay down immutable rules that say this is how to set up the indexing, "it depends" just does not cut it.
Ah thats better, needed that, it's been slow day here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A DBA that doesn't say "it depends", hasn't been presented a specific enough case.
But I have found one "rule" that hasn't failed me yet when it's about aggregating data. It's all about minimizing the amount of data (in bytes, not necessarily rows) that the db has to handle.
Say for example that you want to join together several tables, some holding descriptive texts and some other holding data that should be aggregated.
If you make a query that makes the joins and the aggregation in one go, then the db will join first and aggregate later, creating millions of rows with text that will just be grouped when the aggregation happens. Creating all this in memory, or in worst case the HD, takes time.
Instead I'm breaking down all queries into smaller parts that I materialize using subquery factoring (CTE for you SQLServer people) and then I join the results from them together.
So I'm making one or more queries that aggregate the data, and one or more separate queries for the descriptions, and then I join the aggregated results together in the end.
This method is often magnitudes faster than a onepiece query.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
 That's one of my "rules" too.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I'm going to try different hints for the cursor to see what happens. Thanks.
Ultimately I expect to have to re-develop this whole procedure. It was originally written for Oracle 7 and then had several changes when migrated to 8. Since then it has not been touched, but I think I'll be heavily testing over the holidays.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi
Recently i faced an issue on sql database.
The database is in production which holds the data of all the employees in employee table
Recently we found that values in employee table(not all values) got changed. Bdays, Status are the columns which we identified and not sure if there is more data that got modified.
I need to find what exactly has happened, like any transactions that took place on the table? Or any other other way to find what has happened on database.
Thanks in advance
Naina
Naina
|
|
|
|
|
You could check list of recently ran query in SQL.
See HERE[^].
|
|
|
|
|
Hi
Thanks for the query, but the query gives only today's transactions. Is there any way to get old transactions?
Naina
Naina
|
|
|
|
|
Hi all,
I am using oracle transactions in c#.net in .net frameowrk 2008.The WCF web application giving error "Can't serialize access for the transaction" when multiple user searches for same record in a table in Oracle 11g can anybody tell how can it be resolved.I have increased the initrans value to 100 it is not giving error now, but what if the user gets above 1000 limit.will initrans works.
Thanks in advance.
|
|
|
|
|
|
I am trying to load a DB up with about 3100 tables of data using C#.
Somewhere around table 1770 I get the following message
"Microsoft SQL Native Client: Not enough storage is available to complete this operation."
Does anyone know how to fix this problem, any help would be greatly appreciated.
Thanks in advance,
Michael
|
|
|
|
|
Well you are doing something wrong, we load 1000s of table every day, peak is about 2k per hour. There is not enough info to be able to help here.
How are you loading the data in, BCP, BulkCopy, 1 record at a time?
Is you client having the problem or is it just relaying the error from the server.
Have you checked the transaction log size of the server, the event log of the server, the database properties of the server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So...I am running Visual Studio 2010 on a new Windows 7 machine. I have a server on the network running Windows 2003 server with Oracle 9i and 10g installed. I am trying to connect to the Oracle 10g database on that server from a new .Net application on the Windows 7 machine. This is the error I keep getting:
{"System.Data.OracleClient requires Oracle client software version 8.1.7 or greater."}
I Googled the error and I have tried the following suggestions:
1. Changed the administration folder permissions for Read and Execute for Authenticated Users (with reboot).
2. Made changes to the tnsNames.ora file
3. Made changes to the sqlnet.ora file as follows; NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
4. Changed the listening port to the following different port numbers (1521,1522,8081).
This is the connection code with the various config strings that I have tried (Tel1 is the server name):
string CONNSTR =
"Data Source=//Tel1:1522;User Id=Patrick;Password=Pass;Integrated Security=no";
public Oracle()
{
InitializeComponent();
}
private void TestMyOracleConnection()
{
OracleConnection Conn = new OracleConnection(CONNSTR);
try
{
Conn.Open();
MessageBox.Show("Connection Established", "Success");
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message, "Oracle Connection Failed!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Oracle Connection Failed!");
}
finally
{
Conn.Close();
MessageBox.Show("Connection Closed", "Success");
}
}
private void buttonTestConnection_Click(object sender, EventArgs e)
{
TestMyOracleConnection();
}
I am exasperated with this. I never have a connection issue with MS SQL or MySQL...in fact, I made connections from the same .Net program to both of these DB's both over the network and to a remotely hosted server and it works perfectly. Obviously, my knowledge of Oracle leaves much to be desired. I would appreciate any idea's or suggestions. Thank you...Pat
|
|
|
|
|
I beleive your dev machine needs the Oracle client installed (ours did) not just the tsnames file.
One of the benefits f going to WCF is that I only need to deal with the server connection to Oracle, not all the clients
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
RAH,
Thank you for your solution proposal. Please see message to Dilbert below...and thank you for your time and help...Pat.
|
|
|
|
|
Do I understand you correct that you have both oracle 9i and 10g on the same server.
While possible, it's a mess to configure, and in IMAO quite pointless.
Ok, that was a side track.
The error message you get is quite straight forward (for once).
Mycroft is probably quite correct, your Oracle client software isn't properly installed.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hello Dilbert
Thank you for your input. Re: WCF - comparing the 'cloud' to a real app is, to me, a joke...but if you prefer it, go for it!... . Now that that's settled...I am sure you are correct. My original request was somehow cut off before it was finished, so you did not see the end of my original statement. In any event, I installed the client on the developer machine and now I have 2 problems; First, it still does not work. The application stops responding and second; although I took the precaution of running 2 different catch error clauses (see the code), both for Oracle and for general use, the app actually crashes Windows 7 if I do not shut it down immediately following failure. I suppose that I could put the entire 10gEX version on the developer machine and try connecting directly to a local version first just to test the application. I might try that next if I cannot think of another solution. I am new to Oracle, and frankly, I don't like it from an administrative point of view. But it is not my choice to make. If the vendor already has the database in place, it is simply for me to make the application work with it. Thank you again for your time and your reply...I do appreciate it. If you think of anything else, let me know...and have a nice day...Pat.
Re: 9i and 10g: a friend of mine that is a DBA for the military and an adjunct professor/instructor in databases at our college set this machine up for me to practice with. It was never intended to be an actual solution but rather a training aid. It also has MS SQL 2005 and MySql on it as well.>>Pat
|
|
|
|
|
PDTUM wrote: DBA for the military and an adjunct professor/instructor
Ok so a military academic set up your server with all the most common databases available and you wonder why it won't talk to you. 
Seriously, I'm not surprised the poor thing won't cooperate. And yes Oracle is a PITA to manage, especially after coming from SQL Server, it does however chew through the data with gratifying speed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
