|
What do you mean by 'access to' ? You can read them with a select statement.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
If you're asking for the list of primary keys in a table, use the sql below
SELECT
cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE EXISTS
(
SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME = 'table_name' AND
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
)ORDER BY cu.ordinal_position
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi there,
i have assigned identity property to a column of my table, now i want to remove identity permanently,
how can i do this?
Becoming Programmer...
|
|
|
|
|
select the column....then change identity=no(wizards)
Rajendran.AL
|
|
|
|
|
i want to change it by query
Becoming Programmer...
|
|
|
|
|
I think you want
ALTER TABLE table
ALTER COLUMN col DROP DEFAULT That's a guess, though.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
not working
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
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
|
|
|
|