|
hi,
i've a little db design problem. we are designing an app which should support multiple data storage technologies(sql server, oracle and xml).
because of xml and sql server replication we decided to use a guid column as pk col. but there are some problems.
if use guid:
@ the old version of this app used auto int as pk, so theres no way
to import old databases.
@ oracle doesnt have a guid datatype so we would need to put it into
a different datatype like char or raw but i think it would result
in bad performance
if not use guid:
@ sql server replication is not that easy anymore
@ how do i get a unique id in a xml document??????
does anybody have some hints??
is there another way to get a unique key in a xml doc except guids??
thanks in advance
|
|
|
|
|
The only way I can think of is to create an empty copy of the old database and append the GUID fields to all of the tables. Import the old database then update all of the GUID fields according per their in pk to keep the referencial integrity. It would take some time to create the script to update all of the GUIDs properly but is doable. Then you could import the GUID'd db into the final target db. As far as GUID in oracle as char columns, the performance would most probably suffer but if the columns are indexed properly it shouldn't be that bad.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
This was also the solution i was thinking of but it seem to be much work. thanks for your reply.
|
|
|
|
|
I have an application that lets users search based on degree of separation, so for instance, a user can search for age, hobbies, etc. and limit the search to just users who are 1 degree separate, 2 degrees separate, or 3 degrees separate.
However as it stands now, searches are taking a long long time because according to the programmer, the degree of separation is calculated dynamically upon search, and with a system of about 50,000 users this is taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using a scheduler to perform some calculations beforehand so the searches can use it? Any feedback will be greatly appreciated.
|
|
|
|
|
There are almost always ways to increase search speed. I usually attempt to trade insert time for select time when queries become to long and other methods (indexes, temp tables) have failed. In your case; when you add a new user you can precalc and store criteria that is necessary for future select statements. This usually will have minor effect on the Insert since it only affects one row in the database whereas, the select statement affects 50,000 rows.
I guess I would need a better description of what 1,2,3 degrees separate means to take this any farther.
|
|
|
|
|
My understanding of "degrees of separation" is:
Jack knows John - 1 degree of separation
John knows Mary - 1 degree of separation
Jack is 2 degrees of separation from Mary.
Mary knows Alice
John is 2 degrees of separation from Alice
Jack is 3 degrees of separation from Alice.
and so on. Someone once calculated that each person is a maximum of 6 degrees of separation from anyone else (I don't know how true that is)
Cada uno es artifice de su ventura
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
I have a Trigger on MSSQL, which calls an Extended Stored Procedure using the exec master..xp_sample where xp_sample is the exported function in the dll. Now the Trigger is written to fire AFTER an INSERT event using either FOR or AFTER. In the DLL a new connection to the DB is made and a query is used to query through the DB to return an xml string. The query is as:
SELECT * FROM Employees FOR XML AUTO
the query is being executed using the function:
retCode = SQLExecDirect(hstmt1, (UCHAR*)"SELECT * FROM Employees FOR XML AUTO", SQL_NTS);
The problem is when I do an INSERT from the Query Analyzer then the trigger is invoked and calls the dll function xp_sample. But the function SQLExecDirect never returns. Looks like it goes into some kind of a deadlock. Can anyone help, please
|
|
|
|
|
I'm guessing that your trigger is defined on the Employees table. Since you're creating a new connection, I think the query in your extended stored procedure is deadlocking on the locks held by the INSERT.
Triggers run in the context of the statements that cause them to execute, and significantly, as part of the implicit transaction implied by those statements. An INSERT causes the newly inserted row to be locked with an Exclusive lock. The statement in the XP takes shared locks. When it gets to the newly inserted row, it will block.
The Extended Stored Procedures documentation discusses bound connections, where you can make a new connection part of the original transaction. This might solve your problem.
What are you actually trying to achieve? An XP is really the last resort; you should try to use T-SQL if you can.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
Thanks for the reply, I am looking into it. Basically what I am looking at is whenever I do an INSERT into a Table, I need to Query the Table after the INSERT and send the data via sockets to some client. Now for the sockets implementation I could best think of Extended Stored Procedures where I would Execute a query and then send the data using sockets. This code would go into the dll making the ESP. Could there be a better option, I am not very comfortable with SQL, and would want to have more insight into it. Thanks again, Regards
Samvibes
|
|
|
|
|
OK, I can't think of a way to do this in T-SQL! But do you have to do it in the database server? Notification Services[^] looks like it might be able to do what you need.
Developing extended procedures is pretty hard to get right, from what I hear.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
How can I insert the binary data into image type field in the SQL database with ADO in VC++ project?
Thanks
|
|
|
|
|
Hi all,
Is it possible to write a SQL to join two tables from two different
databases ? Is this a common operation for business applications ?
If not, then is it not a good practice to split the stuff into different databases ? My case is like that : I have a central database to record
the meta information of all objects from different applications, and also
have a seperate database for each single application to store the detail of the objects. But when I search for objects according to some criteria,
then I need to have join SQL over two different databases(the central database and the application detail database).
Thanks
|
|
|
|
|
Hi
is two db in single server?
if it is in single server, u can
other wise
on way
bye
timcyspet
|
|
|
|
|
hi
you can do join as:
Server1.dbname1.owner1.table1 tb1 inerjoin
Server2.dbname2.owner2.table2 tb2 on column....
|
|
|
|
|
Thanks
But in MS SQL 2000, I need to have a database connection for
each database first, then I create the prepared statement with
the database connection. Then how can the prepared SQL statement be
created across the two different database connections ?
|
|
|
|
|
Hello
Help you this article ?!!
-----------------------------------------------
SELECT * FROM SERVER01.FixAsset.dbo.MyAsset m
inner join SERVER01.My_SQL_Edutaction.dbo.SaledAsset s
ON s.AssetId=m.[No]
------------------------------------------------
|
|
|
|
|
Thanks
But in MS SQL 2000, I need to have a database connection for
each database first, then I create the prepared statement with
the database connection. Then how can the prepared SQL statement be
created across the two different database connections ?
|
|
|
|
|
How can I get rid of the value of 3000.0000 since its datatype is money ? I would like it to be 3000, not 3000.0000.
The code is:
Dim pMoney As SqlParameter = New SqlParameter("@Money", SqlDbType.Money, 8)
pMoney.Value = Trim(CInt(txtMoney.Text))
cmdUpdate.Parameters.Add(pMoney)
Did I overlook something ?
Thanks!
|
|
|
|
|
The problem is not in how you are storing it (Actually your expectations are the only problem). 3000 in smallmoney is accurate to four decimal places, so it == 3000.0000 when stored. How are you viewing the result? Unless you apply formatting to the display/print, the default would be to display it at full precision.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Rob,
Hee hee, my expectation is just simple and easy. 4 decimal places isn't what I want it in my project but I can't change the precision in the Design Table of the SQL Ext. Manager. I did try CInt(txtMoney.text) but it won't get rid of the 4 decimal places. (i.e. from 4500.0000 to 4500)
Thanks for replying.
|
|
|
|
|
In C# I cast it to a decimal and then to a double and then convert it to a string, and it only outputs the decimals that are not 0 (10.5 instead of 10.5000). Something like:
string s = ((double) (decimal) row["MoneyAmount"]).ToString();
In VB.NET you could try something similar and it might work.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
How do i run a system stored procedure in sql server 2000 like sp_addlogin?
r
Johan
|
|
|
|
|
From where?
SQL server treats stored procedures whose name begins with sp_ as special, and will look them up in the master database regardless of what database you are currently connected to (but runs them in the context of the connected db).
To run them from the copmmand line (in a dos window)
type OSQL -E
then at the OSQL prompt (>)
type execute sP_XXX,'p1','p2'
then exit to leave OSQL
to execute them in C# use the execute method of your connection, or create a an SQlCommand object, set its text to the sp name, append the appropriate SQLParameters, and call the SQLCommand's executenorecords method.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
thx, this was what i was searching for:
<SQL server treats stored procedures whose name begins with <sp_ as special, and will look them up in the master database <regardless of what database you are currently connected to
<(but runs them in the context of the connected db).
|
|
|
|
|
Hi,
This is really bugging me, I have a product table with a quantity column, When a user buys an item I want the quantity column to change to "SOLD" or 0.
The thing is, how do I do this if a customer buys more than one item at a time?
I need to be able to pass the number of product ID's to the database and then let a stored procedure UPDATE the selected ProductID's Quantity column to "SOLD" or 0.
Somebody help or put me in the right direction! I have tried all i can think of but my SQL knowledge is limited!
Thanks in advance
JetSet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
