|
For my SQL Server work, I alway use a guid uniqueidentifer for my primary keys, which are usually created as the default value for the column with newid()
I once used client side generated GUID's for primary keys on an Oracle DB system, as Oracle didn't support guid's as unique identifiers. As the system was for a large call centre and each unique incoming telephone call was given a unique guid in the database so that it could be linked back to a booking record in their reservation system.
My call tracking software generated a guid for each call and wrote a record to the tracking DB as the primary key, it would then attach the guid to the call and then the booking system would read the guid and store it in the oracle db. The records in the two different tables where then PK/FK linked.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
I used them between a central SQL Server and Remote Clients operating with MS Access. They solved the problem quite well.
Things to think about
=====================
(1) Key is 4 time larger therefore, slower joins on large tables. Less keys fit on each 8k page.
(2) They are a pain when dealing with some DB maintenance issues. It is difficult to hand fix a row if it is based on a GUID.
(3) Some aggregate commands don't work with Guids (i.e. MIN, MAX).
(4) You will loss insert order. I know you aren't supposed to rely on an Identities order but, I end up using it anyway when debugging some runtime issues. Just add a timestamp to solve this issue.
|
|
|
|
|
Marc,
Take a look at my reply to rwestgraham. I think it will be of interest for you.
Take it easy!
|
|
|
|
|
hi,
I already have the oracle installed in my pc my friend did it.I dont know the server name of this server.I am trying to access the tables in oracle but in order to know that I need to know the server name.Can anyone help me in finding the server name of the oracle already installed.
Thanking you in advance
Satish
|
|
|
|
|
I don't know anything about Oracle, so this could be completely wrong, but I would have thought the server name would be the same as the machine name. I'm guessing that the client software would attempt to find the Oracle database on a network and to do that it would need to know the name (or IP) of the machine that Oracle is running on and that machine name is also the server name. If Oracle supports multiple server processes on one machine each process may need a separate named instance.
Anyway, Just a thought you could try out. (Maybe "localhost", or 127.0.0.1 will work)
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I haven't used oracle since oracle 9, so this might be out of date. You need to find the tnsnames.ora file installed on your hard drive in the oracle install directory. This will have a list of all the server names that are registered and you have access to. Open this file in notepad to view all the registered server names.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
When i call a stored procedure to create a table named DETHI
The table DETHI create is owned by ASPNET account. But when i
executed a select statement(SELECT * FROM DETHI), SQL Server reported 'Invalid object named DETHI'
I can't solve this problem
Please help, thanks in advance
|
|
|
|
|
It may have created your table using the name aspnet.DETHI meaning the owner of the table is aspnet user.
Try specifying CREATE TABLE dbo.DETHI in your stored procedure or access the table using SELECT * FROM aspnet.DETHI.
Hope it helps,
Edbert
|
|
|
|
|
What kind of Errors the SELECT statement can raise??
In what cases?
How to avoid them?
How to handle them?
------------------------------------
To study, study and only to study
|
|
|
|
|
_J_ wrote:
What kind of Errors the SELECT statement can raise??
That's kind of like asking "What kind of accidents can my car be involved in?"
_J_ wrote:
In what cases?
When something goes wrong or not specified correctly.
_J_ wrote:
How to avoid them?
Get your permissions sorted out. Don't refer to things that don't exist. Get the syntax right.
_J_ wrote:
How to handle them?
DECLARE @error_number
SELECT .... -- Your select statement goes here
SET @error_number = @@ERROR -- @@ERROR is very short lived, grab it while you can.
IF @error_number = ... -- You can find the error numbers and the errors they relate to in the sysmessages table
If you can be more specific then perhaps I can give you a more specific response. There are somewhere in the region of 3900 error / warning messages in SQL Server.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Ok. If i perform INSERT INTO ..... the most common error is cannot insert duplicate.
However if i perform the SELECT and all parameters in my SELECT statement are ok, i.e. no syntax error and also all columns and tables that i specifyed are exist. So is the posibility of some kind of an error that can occur???
------------------------------------
To study, study and only to study
|
|
|
|
|
|
So as i understand:
SELECT ..., ...., .... ... FROM ....
.....
....
SELECT @myERROR = @@ERROR
IF @myERROR != 0
here i handle the error, maybe call to RAISEERROR
------------------------------------
To study, study and only to study
|
|
|
|
|
|
I know that errors with severiry less than 10 is an information error(message) so in the C# i can't catch it in the try/catch/finally block, caues of that i will need to raise my error with severity > 10, after this i will catch this error in my application
------------------------------------
To study, study and only to study
|
|
|
|
|
|
ok. Thank u.
------------------------------------
To study, study and only to study
|
|
|
|
|
Most of the SQL errors are non-recoverable unless you are writing code to design SQL statements. If you commonly get SQL errors then it is a sign that your application isn't using the SQL server properly.
For example if you are adding a username/password to a registration database and the username must be unique, you should send a statement that returns different results based on a transacted exist/insert statement rather then trap the exception.
Common occurances shouldn't typically appear in error handling or catch blocks.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
I have not big DB (12 tables)
Suppose application is requested for some data about specified clientID
by means of ADO.NET i got all the relevant data about this clientID from several tables to the client, client perform tasks on this data change it maybe add something new or delete somtething (there is posibility that data will not changed at all), after this client click to SAVE button and return data to DB, here start the problem, i can delete all relevant data about specified clientID in all table and after this insert data tables (insert data that recieved from the application) or i can check what changed and to remove or update or insert data.
ANY IDEAS, and maybe how to perform my problem.
I'm using:
Windows Xp, .NET Framework, Sql-Server 2000.
------------------------------------
To study, study and only to study
|
|
|
|
|
To delete all relevant data and insert them again will be taxing to your database server.
For best performance you can balance between tables to be deleted (best for one-to-many relationship such as multiple selection) and tables to be updated (best for one-to-one relationship e.g. a table with many columns).
If you are using datasets, specify all the insert, delete and update commands and let it do the work for you.
Edbert
|
|
|
|
|
However if i don'et use dataset, i'm developing only db i don't care what hapen in the application layer.
What i should do do delete relevant rows and insert after this or to do some manipulation with data to understand what chenged and aafter this to insert o to delete or to update some rows????
------------------------------------
To study, study and only to study
|
|
|
|
|
SHAPE {EXEC sp_S_Events} APPEND ({SELECT * FROM attendees WHERE event_id=?} RELATE event_id TO PARAMETER 0) AS rsAttendees
From a method (namely, GetEvents) of my COM+ business layer component (written in Visual C++ 6.0 SP5), I am issuing the statement given above but it fails with an error saying:
Error 0x80004005: The data provider or other service returned an E_FAIL status.
However, the same statement works perfectly fine when executed from within a program of Visual Basic. A small portion of code from my COM+ component is given below:
STDMETHODIMP CBizLayer::GetEvents( long EventID, VARIANT *pDataset) {
_ConnectionPtr spConn;
_RecordsetPtr spDataset;
try {
.
.
.
spConn->Provider = L"MSDataShape";
spConn->Open(szConnect, _bstr_t(), _bstr_t(), -1);
spDataset->PutRefActiveConnection(spConn);
spDataset->PutCursorLocation(adUseClient);
spRS->Open(szShapeCmd, vtMissing, adOpenForwardOnly, adLockReadOnly, adCmdText);
spRS->PutRefActiveConnection(NULL);
::VariantClear(pDataset);
V_VT(pDataset) = VT_DISPATCH;
V_DISPATCH(pDataset) = (IDispatch*)(spDataset.Detach());
m_spObjectContext->SetComplete();
_hr = S_OK;
}
catch(const _com_error &ex) {
[...]
}
catch(...) {
[...]
}
I've tried to debug my component and I found out that the call to CBizLayer::GetEvents() method completes without any error (i.e. with an HRESULT of S_OK), but in the client application, I still receive this error . Why? What could be the reason? I am totally confused!
Please help!
Gurmeet BTW, can Google help me search my lost pajamas?
My Articles: HTML Reader C++ Class Library, Numeric Edit Control
|
|
|
|
|
I have a select statement followed by a from statement that looks like this:
aName aName, anotherName anotherName
where
etc...
What does that mean?? Why did not they put just:
aName, anotherName
where
also, when you are doing your select, if I do this:
ATable.aField,
AnotherTable.aNotherField,
someField,
someOtherField,
etc.
Which table am I pulling from if I do not specify which table it is coming from?
Cheers,
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
The first statement you mention is using aliases for the table. Typically you don't see people use the exact same name as the table, since that is pointless. I use it to abbreviate the table name i.e.
select e.EmpID, e.EmpName, et.Description
from Employee e, EmployeeType et
where...
Basically, I am aliasing Employee to e and EmployeeType to et.
Which answers your second question. The alias.field just specifies which table to pull the field from. It is only necessary when you have the same field name in two tables in your query. If you do not specify a table name, it will not cause a problem. If a field name exists in two tables in the same query and you do not specify a table name, it will result in an error (can't remember what it is right now).
Example:
select EmpID, EmpName, Description, e.EmpTypeID
from Employee e, EmployeeType et
where e.EmpTypeID = et.EmpTypeID
Notice, I do not put the table name in front of EmpID, EmpName or Description because they only exist in one table. However, both tables have a field called EmpTypeID, so I precede it with the table alias (e and et)
Jeff Martin
My Blog
|
|
|
|
|
Thank you, that answered my question completely and exactly!
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|