|
Imagine the following SQL Server 2005 scenario, if you will:
You have a "contacts" database holding the details of all your customers, suppliers etc etc. You also have a "shipping" database which allows you to create and fulfil orders. Shipping relies on some (but not all) aspects of the contacts db, and the contact db is also used by other databases/applications.
Ideally you don't want to incorporate chunks of contacts into shipping as you want to have a single source of this information which is up-to-date and reliable. But given this, how do you maintain referential integrity across the two databases, ensuring that shipping cannot contain contact information not existing in contacts?
Do you:
a) Replicate chunks of contacts in shipping which is automatically updated
b) Expose contacts as a service and have your shipping application refer to that
c) Some other method?
|
|
|
|
|
The easiest would be using triggers that operate on both the contacts and shipping db tables and check for referential integrity. But given the fact that most organizations do not want to hear about triggers in their applications. Besides, since your contacts db is referred to by many other databases, using triggers on all of them would be a maintenance headache and slow.
Your next best option would be a web method that exposes contact information. Given that you can easily write up a clr sproc that queries a webservice, this would seem both easy and smooth. However, what would happen if you delete a contact from the contact db? Would you have to propogate the delete to all other dependent databases?(I'm guesing no.)
Replication is expensive and hogs up both bandwidth and space, so give it a no-no unless you're brutally arm-twisted to do so.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Good Day,
I'm fairly new to ADO.NET and MS SQL Server 2005...
I have 2 tables, first is the List Table, where a primary key called "Number" is set to IDENTITY.
The other is the Content Table that contains the "Number" from List Table and some other columns.
The environment is MULTI-USER.
This is what I want to accomplish
1. LOCK BOTH TABLE FROM WRITING
2. Add Record to List, this will increment the IDENTITY
3. Get the Identity from the List, Add record to content table using the Identity as the primary key
4. FREE BOTH TABLE FROM WRITING
I need to lock it because the environment is Multiuser.
In short, I just want to learn how to LOCK and UNLOCK a table.
Thanks! 
|
|
|
|
|
Alternatively, how about having one table with an IDENTITY column? When you insert the row in the database you can then find out what identity the database gave it by using SELECT SCOPE_IDENTITY()
|
|
|
|
|
YES SIR! This is what I really want, this will work.
If I have an INSERT COMMAND, How can I use the Select SCOPE_IDENTITY() with that? Can you give an example?
Thank you!
|
|
|
|
|
The table would look something like this:
CREATE TABLE MyTabale
(
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstValue INT,
SecondValue INT,
ThirdValue INT
);
To insert into it and retrieve the Id value back:
INSERT INTO MyTable VALUES(@FirstValue, @SecondValue, @ThirdValue);<br />
SELECT SCOPE_IDENTITY();
The above either goes in a stored procedure, or the command text of one SqlCommand object. I recommend the stored procedure route.
Use ExecuteScalar on the command object to get the Id back to your application.
|
|
|
|
|
Thanks for the reply.
Can I concatenate the INSERT COMMAND and the SELECT COMMAND into a single SQLCommand object?
So instead of:
SqlCommand insert = new sqlcommand(@"INSERT ...");<br />
insert.ExecuteNonQuery();<br />
<br />
SqlCommand scope = new sqlcommand(@"select ....");<br />
int identity = (int)scope.ExecuteScalar();
It will be like:
Sqlcommand insertAndscope = new sqlcommand(@"Insert ...; Select...");<br />
int identity = (int)insertAndscope.ExecuteScalar();
What I'm afraid is that, if the command is divided into two, there is a lag time between the two commands - and in a multi-user enviroment, there will be a problem. For instance:
USER A - INSERT
USER B - INSERT
USER A - SCOPE (1 - instead of 0)
USER B - SCOPE (2 - instead of 1)
When what I want is:
User A - INSERT and SCOPE (0)
User B - INSERT and SCOPE (1)
Thanks!
|
|
|
|
|
Ian Uy wrote: Can I concatenate the INSERT COMMAND and the SELECT COMMAND into a single SQLCommand object?
Yes, wasn't that what I said? I said: "The above either goes in a stored procedure, or the command text of one SqlCommand object."
Ian Uy wrote: What I'm afraid is that, if the command is divided into two, there is a lag time between the two commands - and in a multi-user enviroment, there will be a problem.
Since I didn't suggest splitting the command in two this is not a problem. Is it?
|
|
|
|
|
Sorry. I misunderstood it.
It's okay now. Thank you. Gave a 5.
|
|
|
|
|
Hey everybody
I'm trying to get all the stored procedures and their parameters using ADOX and C++.
I've been looking all over the internet for an example and I couldn't find any.
I have created a _CatalogPtr object.
When I'm trying to use GetItem() or Item[] (using an index or name) I'm getting an error that no such Procedure exists.
Please Help! 
Thanks!!!
|
|
|
|
|
I have two problem,
1. how many types of indexes in sql server.
also if we delete the table having one cluster indexes and 2 non cluster indexes.then what happen.can any one tell me about that.how can we delete the table and if table is deleted then what is happening about cluster and non clustered indexes.is it deleted automatically.
2. i need to left outer join with where clause with 2nd table.what is the output of the query. it will return all rows of 1st table or it will return only selected rows
|
|
|
|
|
Ajeet mittal wrote: 1. how many types of indexes in sql server.
also if we delete the table having one cluster indexes and 2 non cluster indexes.then what happen.can any one tell me about that.how can we delete the table and if table is deleted then what is happening about cluster and non clustered indexes.is it deleted automatically.
Yes
Ajeet mittal wrote: 2. i need to left outer join with where clause with 2nd table.what is the output of the query. it will return all rows of 1st table or it will return only selected rows
What do you think? Have you tried it? If so, what happens?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
iam new with FireBird Database..
i want to do desktop application using visual studio 2005,and .Net Framework 2.0.
please give information about How to create and implement FireBird Database in .Net 2.0.
Thanks in Advance.
murali krishna
|
|
|
|
|
ConnectionStrings.com[^] should be your first point of call for database connection information.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi All,
i am using sql server 2005.
in a table a record is entered as product1 , status1 ,product2 , status2 , product3 , status3 in a row now i have to display it as
product1 status1
product2 status2
product3 status3
how can i do it .
please help.
regards
imran khan
|
|
|
|
|
Use the UNION operator to concatenate the results from multiple select statements.
BTW, your table design indicates that further normalization of your database is required. Unless there is an exceptional requirement to set your table up in this way, you should only have 1 Product field and 1 Status field.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hello
can we access the parameter of the procedure in a trigger where both are on the same table
Rajeev Kr. Sharma
VRI Software Pvt.Ltd.
New Delhi India
HumOnline.com
Stay Connected
|
|
|
|
|
Simple answer, no you can't. You can compare the old and new values within the trigger though if that helps. See my reply to your other question.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi
i have
table1 (name[unique], address, phone)
table2 (name[unique], occupation )
in my situation i am updating table2 name field value through procedure.
so i want to create a trigger which change the relevant name field value of table1
using sql server 2000
please help me
Rajeev Kr. Sharma
VRI Software Pvt.Ltd.
New Delhi India
HumOnline.com
Stay Connected
|
|
|
|
|
You can get the old value of the name from the deleted table and the new value of the name from the inserted record within your trigger. The deleted and inserted tables are special tables within a trigger, but if you are always updating using a stored prcoedure why not just update both atbles within a transaction. This will enaure referentail integrity too.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i am getting this error when i an trying to read data from trhe access database
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission
private void ProcessData()
{
while (true)
{
string dt = System.DateTime.Now.Date.ToString();
string[] dt1 = dt.Split();
string todays_date = dt1[0];
string strHtml = "you are late logged in";
OdbcConnection con = new OdbcConnection("DSN=target1");
string query = "SELECT DISTINCT EmpID FROM Employee";
con.Open();
OdbcCommand cd = new OdbcCommand(query,con);
OdbcDataReader dar = cd.ExecuteReader();
while (dar.Read())
{
int employee_id = (int)dar["EMPID"];
//string query2 = "";
try
{
string email = (string)dar["email_id"];
MailSender mail = new MailSender();
mail.SendMail(strHtml, "+ email +", "LateLogin");
}
catch (Exception)
{
}
}
//finally
//{
// //you can change the number of milliseconds according to your wish
// Thread.Sleep(10000);
//}
}
}
|
|
|
|
|
Hello everyone,
I have a Categories Table which has a self join, FK ParentCategoryID. I've provided a search facility with wild card search. Now the problem comes when I you search such that a only child category matches the criteria. In that case when I try to bind it with the TreeView it doesnt show anything as my function tries to insert rows from the 1st level till the end.
I was thinking if I can make out a query somehow that can give the parents of the matched child category all the way up to the first level, it can solve my problem. But the question is how???
Can any one give me some suggestions, guidelines on that
Thanks
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
Hi - I'm wasting a lot of time trying to get connection right to Sybase and didn't manage to get the combination/configuration right.. very nice if someone can help here!
In my sql.ini file under here C:\...\Sybase\ocnt1252\ini:
... many entries
[UATMACHINE]
somesrv.data.com,4300
somesrv.data.com,4300
... many more entries ...
Now,
connection string = "Provider=Sybase ASE OLE DB Provider;Data Source=UATMACHINE;Server Name=somesrv.data.com,4300;Database=MyDB;User ID=johdoe;Password=xxx;"
http://www.carlprothman.net/Default.aspx?tabid=86[^]
And when I stepped through the code, it failed on oConn.Open with this erro:
[System.InvalidOperationException] = {"The 'Sybase ASE OLE DB Provider' provider is not registered on the local machine."}
Also tried this:
"Provider=Sybase.ASEOLEDBProvider;Server Name=somesrv.data.com,4300;Initial Catalog=MyDB;User ID=johdoe;Password=xxx;"
http://www.devlist.com/ConnectionStringsPage.aspx[^][^]
Same error.
These reference seems to be suggesting I should:
(a) Use ASE ADO.NET Data Provider (i.e. Provider=ASAProv.90) instead
(b) download ASE ADO.NET Data Provider
REF[^]
(c) Instead of generic "new DbConnection()" I'm using now (trying to abstract away DAL), I should be using OleDbConnection new OleDbConnection[^]
Actually this is quite inconvenient as my connection code looks like this:
<br />
DbProviderFactory oDbProviderFactory = CDataUtil.GetConnectionFactory("System.Data.OleDb");<br />
DbConnection oConn = oDbProviderFactory.CreateConnection();<br />
oConn.ConnectionString = m_Cmd.ConnectionString;
(i.e. I'm using dotnet 2.0)
One more problem,
What's Open Server 15.0?? From isql path (client installed on my workstation already working) C:\...\Sybase\ocnt1252\ini seems to suggest we're using Sysbase version 12.5? Open Server 15.0 compatible?[^]
I am not sure if I have time to read the ebook[^] just so I can open a connection!
And, here's a nice/short 32 pages intro to dotnet+Sybase by Ashish Mahajan (but fell short in providing link to download:
download.sybase.com/presentation/2003_presentations/ASE135.ppt
This seems to be telling me that ASE ADO.NET Data Provider is NOT FREE... I don't want to go through admin process and would simply like to stay with M$ Ole DB instead.
http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit[^]
devy
modified on Saturday, February 16, 2008 12:19 AM
|
|
|
|
|
Hi Devvvy,
i have a legacy code in VB.Net 1.1 and Sybase ASE 12.5, system is working fine as i took over from previous developer. Now client want to upgrade to ASE 15.7. we created a totally new environment and installed DB and ASE Client on new machines.
I was getting Connection Error while connecting with new DB.
I modified connection string in web.config
Previous:
<add key="connectionString"
value="Provider=Sybase.ASEOLEDBProvider.2;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
New:
<add key="connectionString"
value="Provider=ASEOLEDB;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
Login works fine however just after Login when system tries to run any other query in database I get following error message:
[Message Class: 14][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 924] [ASEOLEDB]Database 'RLDB' is already open and can only have one user at a time.
[Message Class: 11][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 4001][ASEOLEDB]Cannot open default database 'RLDB'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5704][ASEOLEDB]Changed client character set setting to 'iso_1'.
[Message Class: 10][Message State: 2][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5701][ASEOLEDB]Changed database context to 'master'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5703][ASEOLEDB]Changed language setting to 'us_english'.
I am also getting similar error if i try to open both SYBASE CENTRAL and SQL ADVANTAGE at the same time.
any help will be welcome.
|
|
|
|
|
Good afternoon all.
the developers on my team are having an ongoing discussion about null values being represented as another value where applicable. The reason for this discussion is we are going through a system re-write and we need to know the best way to do the following in SQL server 2005 and ASP.NET
Meaning, due to the nature of our business, we can have situations where numeric or date values can actually be represented as a character value. For example, if a numeric mortgage rate is not present on a mortgage application document, the field is considered to be "not applicable". However the data type for the mortgage rate field is numeric in the SQL database.
The argument here is how do we store the "not applicable" for a null numeric field? We have talked about having child tables to store the field name and its character field representation while keeping the null in the parent table with its true datatype.
We have also discussed having an XML field in the parent table to store the XML character representation for that field. So for example if the mortgage rate for a record is "not applicable" null, we would store null in the Mortgage_Rate field in the database, but store <mortgage_rate>Not Applicable in the XML field for that record.
Does anyone have any other types of recommendations for storing this kind of data?
Thanks for any tips in advanced.
- AGD
Software Developer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
