|
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
|
|
|
|
|
Hope this will help, when the user buy 3 item with the product ID 2, execute the stored procedure passing the quantity and the ID
update TableName set Qty=Qty-@Qty where ID=@ID
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
thanks, I worked it out in the end, and it only took me the whole day!!!
UPDATE Products
SET ProdQuantity = 0
WHERE ProductID =(SELECT ProductID FROM ShoppingCart WHERE ProductID = Products.ProductID and ShoppingCart.CartID=@CartID)
Their you go
Thanks again
|
|
|
|
|
Hi all~
How do I write a Sql Statement that joining 2 tables which in 2 different database files respectively?
Thanks
|
|
|
|
|
In SQL Server to access table in different database use database_name.dbo.table_name
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Oh
Iam sorry.
I miss to mention.
I would like to write a sql statement that can join 2 tables and 1 is assoicated by one connection and 1 is assoicated by another connection.
Is it suitable to use "database_name.dbo.table_name" with SQL Ce Server 2000?
Also what is the database name and dbo?
Can I get some examples?
Thanks
|
|
|
|
|
I don`t know if it's suitable with SQL Ce Server.
If your database name is db1 and your table name is table1, to access the field field1 in the table use:
db1.dbo.table1.field1
I hope this helps.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I'm in the midst of developing a module which adds/remove contact list from Microsoft Outlook. I have two problems currently:
1. When my module perform the insert, I get this security popup: "A program is trying to access email addressed stored in Outlook. Do you want to Allow this?"
Now, there's a software that automatically click "YES" when this popup appears, but is there a better solution that this? Thanks a bunch!
Download the software I mentioned ("Context Magic")[^]
2. I wish to insert contact item into (a) a database, and (b) outlook address book. And I wish to wrap this INSERT inside a transaction. Now, usually, we just pass a IDbTransaction into our Create(..) method:
Create(ct As ContactItem, tran As IDbTransaction)
Now if I want the transaction to cover my insert into Microsoft Outlook address book also?
Thanks in advance!
References:
a. Article From Code Project[^]
b. Article from MSDN[^]
c. Download Binary if you don't have Microsoft Outlook 2003 (and therefore the object library required) [^]
Norman Fung
|
|
|
|
|
I try to use ODBC to update an item in MySQL table "my_status", but it doesn't work. Seems the data can be loaded to the dataset, but how to write it back to the data sourse? Is there anybody can give me some ideas or samples on how to do this?
Here is my code:
/////connect to the database with DataSet
String* strSql = "SELECT * FROM my_status";
OdbcDataAdapter* da = new OdbcDataAdapter(strSql,MYSQLCONN);
OdbcCommandBuilder* custCB = new OdbcCommandBuilder(da);
OdbcCommand* cmd;
cmd = new OdbcCommand("Select * from my_status", MyConn);
da->SelectCommand = cmd;
DataSet* wrbtDS = new DataSet();
da->Fill(wrbtDS, "my_status");
DataRow* cRow = wrbtDS->Tables->Item["my_status"]->Rows->Item[0];
//// load the image into the dataset
String * FILE_NAME = "Solid.jpg"; // for inserting a image file
FileStream* fs = new FileStream(FILE_NAME, FileMode::Open, FileAccess::Read);
Int16 imglen = (int)fs->Length;
Byte buffer[] = new Byte[imglen];
fs->Read(buffer,0,buffer->Length);
cRow->Item["image"] = buffer;
/* until here the item["image"] is changed and shows in DataSet, but how to update the data source with this updated DataSet??????? The following code doesn't work and how to figure it out?*/
wrbtDS->Tables->Item["my_status"]->Rows->Item[0]->AcceptChanges();
da->Update(wrbtDS, "my_status");
Thanks a lot!
Kevin
|
|
|
|
|
Hi there,
Just a small question: Does anyone know how to display the menu bar on the top of a table screen in SQL Server (2000) Enterprise Manager? There must be a setting somewhere that I could not find out. I really hate to use the menu in the main form everytime. Thanks a lot!
Frank
|
|
|
|
|
Hello All!
I'm working on a Store Procedure. This works fine...
SELECT MAX(TableID) AS returnvalue
FROM TblID
WHERE (Inuse = 0)
UPDATE T1
SET Inuse = 1
FROM TblID T1 INNER JOIN
(SELECT MAX(TableID) AS MaxT
FROM TblID
WHERE (Inuse = 0) AND (TableID <= 5)) T2 ON T1.TableID = T2.MaxT
But now I need to make it so if the VALUE of "InUse" is not 0, it will give an error message or something. I will use this SP in my VB.net program. Unless it is easier to catch the error on the client side within my program. But I thought this would be the better way to go. And if I do have a message built in the SQL code, how can I display that in my code for my program.
This is what I have now for the SP code....
IF (SELECT MAX(TableID) AS returnvalue
FROM TblID
WHERE Inuse = 0) BEGIN
UPDATE T1
SET Inuse = 1
FROM TblID T1 INNER JOIN
(SELECT MAX(TableID) AS MaxT
FROM TblID
WHERE (Inuse = 0) AND (TableID <= 5)) T2 ON T1.TableID = T2.MaxT END ELSE BEGIN PRINT ' Wrong'
And if your curious, this is my code to call the SP in vb.net....
conLog = New SqlConnection("Server=localhost;uid=**;pwd=**;database=footbet")
cmdLog = New SqlCommand("loginStore", conLog)
cmdLog.CommandType = CommandType.StoredProcedure
cmdLog.Parameters.IndexOf("@InUse")
conLog.Open()
cmdLog.ExecuteScalar()
myMainfrm.Show()
conLog.Close()
Thank you for your help!
Rudy
|
|
|
|
|
How could I get random a number of rows in SQL Server database
Example i've got 70 rows data in table "Question" which has two fields including "QuestionID"(int) and "QuestionContent"(nvarchar(1000)), now i want to take random 10 rows from it
Thank you so much.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
