|
Hi,
I have two dataBase with different username and passWord ,
dataBase1 : UserName =u1 , Password=pass1
dataBase2 : UserName =u2 , Password=pass2
I have a Procedure on First dataBase , on this procedure I use a table of Second dataBase , But When I run the Application ,it make error that "u1" don't have access to dataBase2.
How can I use tables of dataBase2 in procedures of dataBase1?
Thanks.
|
|
|
|
|
Well, if you are working on SQL Server then you can operate across databases, even across different servers (if you link the servers)
Anyways you can use the full name of the table when you use two different databases like this:
SELECT t1.myColumn AS table1Column, t2.myColumn AS table2 column
FROM database1.dbo.myTable as t1
INNER JOIN database2.dbo.myTable as t2 ON t1.id = t2.id
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Oops sorry, you were talking about stored procedures.
Well, if you have a stored procedure on database1 you can use a table on database2 like this
CREATE PROCEDURE dbo.myStoredProc
AS
SELECT * FROM database2.dbo.myTable
GO
When you use a table name you can qualify it fully like this
[server].[database].[schema].[table name]
You can obviously miss the bits that are not needed. For example, in normal circumstanced you would only give the table name because everything is on the same server, in the same database, in the same schema.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have a int ID Field set to auto-increment. When does it run out of room? What's the highest number it can go to?
E=mc2 -> BOOM
|
|
|
|
|
IIRC: (2*10^31)-1 = 2147483647
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks. I'm a developer and know some SQL but not the mechanics of SQL Server. I leave that to the experts. Can I change my Auto-Increment field to numeric and if so does that buy me anything?
E=mc2 -> BOOM
|
|
|
|
|
Suppose DataSet myDataSet has a DataTable myDataTable. Do the follwing:
myDataSet.Tables.Remove(myDataTable);
bool tf=myDataSet.HasChanges(); //tf will be false
DataSet tbl=myDataSet.GetChanges(); //tbl will be null
I understand the datatable is removed, thus all the DataRow are removed from the dataset. So dataset does not detect any change and does not get any changes. But what is the common way to track the fact that a whole table is removed from the dataset? Thanks.
webx10
|
|
|
|
|
i am using vs2002 and want to enter data in a sql server 2000 database through vb interface. i am able to enter data if the vb application and sql server both are on same node but i want to enter data to database on other node running sql server. computers are networked under same workgroup and there is no domain controller.
i used the following code for connection.
Dim constudent As SqlConnection
Dim cmdadd As SqlCommand
Dim conn As String
conn = "server=.; UID=sa;PWD=secret;database=student"
constudent = New SqlConnection(conn)
cmdadd = New SqlCommand("insert into student values(@name,@roll,@stream)", constudent)
cmdadd.Parameters.Add("@name", TextBox1.Text)
cmdadd.Parameters.Add("@roll", Val(TextBox2.Text))
cmdadd.Parameters.Add("@stream", TextBox3.Text)
Try
constudent.Open()
cmdadd.ExecuteNonQuery()
constudent.Close()
MessageBox.Show("Data Added", "added", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
Catch ex As Exception
MessageBox.Show("error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
End Try
may any one hlp plz to modify the connection string or tell me how to establish such connection
|
|
|
|
|
server=TheMachineName
or
server=TheMachineName\TheSQLInstanceName
Oh... And logging in as sa through your application is a huge security risk - Don't do it or you could get 0wn3d.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks Colin Angus Mackay for reply
but it is not working
I even tried giving port no 1433 wit the ip address of machine but no success.
As there is no domain controller and all the systems are under same workgroup, if your solution works under these circumstances or not????
Reply.........
|
|
|
|
|
I don't think it's a connection string problem.
If the database is on server 2003 or XP SP 2, you needs to change some configuration values for it to work. You need to add sql server to the windows firewalls exceptions, open the ports, and change some registry settings.
It gets more complicated is you use the MSDTC but Microsoft provides step by step procedures. Search under SP 2 for SQL Server.
|
|
|
|
|
Please , can any one confirm that a table type or collection type parameter can be passed to stored procedure in oracle using ADO.NET ?
I , browsed several forum and site but without success
Thanks
love2code
|
|
|
|
|
You need to use, ODP.Net , Data Provider for Oracle supplied by Oracle. Assuming you have that here is the example from Oracle
/********* Example Start *************/
using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ODPSample
{
class AssocArray
{
static void Main(string[] args)
{
Console.WriteLine("Demo: PL/SQL Associative Array");
// Connect
string connectStr = "User Id=scott;Password=tiger;Data Source=oracle";
// Setup the Tables for sample
Setup(connectStr);
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand cmd = new OracleCommand("begin MyPack.TestVarchar2(:1, :2, :3);end;",
connection);
OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add("param3", OracleDbType.Varchar2);
// Setup the direction
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.InputOutput;
param3.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = new string[3]{"Input1",
"Input2",
"Input3"};
param2.Value = new string[3]{"Inout1",
"Inout2",
"Inout3"};
param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
// Array
param1.Size = 3;
param2.Size = 3;
param3.Size = 3;
// Setup the ArrayBind Size for param1
param1.ArrayBindSize = new int[3]{13,14,13};
// Setup the ArrayBind Status for param1
param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBind Size for param2
param2.ArrayBindSize = new int[3]{20,20,20};
// Setup the ArrayBind Size for param3
param3.ArrayBindSize = new int[3]{20,20,20};
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Results:");
Display(cmd.Parameters);
}
public static void Setup(string connectStr)
{
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand("", connection);
try
{
connection.Open();
command.CommandText = "drop table T1";
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
command.CommandText = "CREATE TABLE T1(COL1 number, COL2 varchar2(20))";
command.ExecuteNonQuery();
StringBuilder hdr = new StringBuilder();
hdr.Append("CREATE or replace PACKAGE MYPACK AS ");
hdr.Append("TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;");
hdr.Append(" PROCEDURE TestVarchar2(");
hdr.Append(" Param1 IN AssocArrayVarchar2_t,");
hdr.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
hdr.Append(" Param3 OUT AssocArrayVarchar2_t);");
hdr.Append(" END MYPACK;");
command.CommandText = hdr.ToString();
command.ExecuteNonQuery();
StringBuilder body = new StringBuilder();
body.Append("CREATE or REPLACE package body MYPACK as ");
body.Append(" PROCEDURE TestVarchar2(");
body.Append(" Param1 IN AssocArrayVarchar2_t,");
body.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
body.Append(" Param3 OUT AssocArrayVarchar2_t)");
body.Append(" IS");
body.Append(" i integer;");
body.Append(" BEGIN");
body.Append(" -- copy a few elements from Param2 to Param1\n");
body.Append(" Param3(1) := Param2(1);");
body.Append(" Param3(2) := NULL;");
body.Append(" Param3(3) := Param2(3);");
body.Append(" -- copy all elements from Param1 to Param2\n");
body.Append(" Param2(1) := Param1(1);");
body.Append(" Param2(2) := Param1(2);");
body.Append(" Param2(3) := Param1(3);");
body.Append(" -- insert some values to db\n");
body.Append(" FOR i IN 1..3 LOOP");
body.Append(" insert into T1 values(i,Param2(i));");
body.Append(" END LOOP;");
body.Append(" END TestVarchar2;");
body.Append("END MYPACK;");
command.CommandText = body.ToString();
command.ExecuteNonQuery();
command.CommandText="Commit";
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(command.CommandText + ":" + e.Message);
}
}
public static void Display(OracleParameterCollection collection)
{
foreach(OracleParameter p in collection)
{
Console.Write(p.ParameterName + ": ");
for(int i=0;i<3;i++)
{
if (p.Value is OracleString[])
Console.Write((p.Value as OracleString[])[i]);
else
Console.Write((p.Value as string[])[i]);
Console.Write(" ");
}
Console.WriteLine();
}
}
}
}
/********* Example End *************/
Regards
|
|
|
|
|
I found a really cool function at http://www.devx.com/tips/Tip/20009.
You pass in in a string with what it's deliminated by and it returns a temp table variable of all of the data split into rows..
i.e
select * from fn_SplitByID('oranges|apples|pears|lemons','|')
1 Oranges
2 Apples
3 Pears
4 Lemons
All really nice and pretty. The trouble is I have a table products (around 2000 rows). Each has a product_code (varchar(255) primary key), stock level (int), with a description field that contains deliminated text 'blue|green|pink' (varchar(255)), etc.
I want to extract and split the data into a whole new table:
product_id product_code stock_level description
1 ABC NULL BLUE
2 ABC NULL GREEN
3 ABC NULL PINK
4 DEFG NULL SILVER
5 DEFG NULL GOLD
...
Yeah I am not worried about the stock_level, just need a null value into the new table, as the stock would have to be recounted.
I just keep getting stuck on how to do this. Maybe I've been looking at too many different ways and the answer is straight in front of me. If you have any suggestions I would be grateful.
Many thanks
Kev
|
|
|
|
|
hi all,
In our Application ,we have to use 175 SQL Functions.So there are many dependancies between each other.
That mean ,If we creating a function,we have to use several function to create it.
So I need to create a function relation ship between each other.(like relatinship between Tables)
Please kindly tell me,Is there a way to get dependancies and relation in functions?
thanks in advance.
|
|
|
|
|
You could use sp_depends[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi everyone,
I want to update a row in a table in SQL with values from another row in the same table,
i wrote this:
UPDATE Income_Codes IC
SET IC.DateLeave = IC1.DateLeave
FROM Income_Codes IC1
WHERE IC.Cm = 420 AND IC1.Cm = 911
but it didn't work.
I tried this:
update Income_Codes
set DateLeave = (select DateLeave from Income_Codes where Cm = 911)
where Cm = 420
it worked, but if i have to update 20 fields, i have to write 20 selects, any other solution?
Thanks
|
|
|
|
|
delete the first row, and insert second row just changing the primary key
love2code
|
|
|
|
|
If you want both rows, then use the following
UPDATE table
SET (col1, col2, col3,..., col20) = (SELECT col1, col2, col3,..., col20
FROM table
WHERE <your_where_clause1>)
WHERE <your_where_clause_2>;
|
|
|
|
|
I tried it, but it gives an error,
any other solutions.
Thanks anyway.
|
|
|
|
|
Hi,
Anybody knows how to know if an ADO connection has been disconnected even before you execute anything? I have an application which has a global connection object and when it was disconnected from the server for whatever reason, it raises an error when you execute something even if the connection has been restored. Is there a way which I could know if the connection is still valid before I even execute anything? Thanks
SDE
|
|
|
|
|
alex1205 wrote: know if an ADO connection has been disconnected even before you execute anything?
You can check the connection state.
State Property (ADO)[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
Thanks for the response. I've tried that and it doesn't work. I also tried looking at the error count but it doesn't report any. 
SDE
|
|
|
|
|
alex1205 wrote: Thanks for the response. I've tried that and it doesn't work.
Oh, sorry about that. I take it the Connection State is showing the connection is still active?
alex1205 wrote: it raises an error when you execute something even if the connection has been restored.
In the original post you mention the next time you execute a query it throws an error. What is the error message it throws when this happens?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi all,
I have a question on sql writing
for a table schema as below
Table :
(studentID, studentName, courseID, classID, Age)
What is the correct sql of " select studentID from table where classID = "101" and group by courseID and order by age " ? (ie. Group by courseID, and within the same courseID, the studentID are ordered by age)
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
