|
Hi there,
I've got the following two tables:
<br />
CREATE TABLE [dbo].[Countries] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
ALTER TABLE [dbo].[Countries] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
and...
<br />
CREATE TABLE [dbo].[Cities] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL ,<br />
[CountryID] [int] NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
<br />
ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
Now I'd like to establish a one to many relationship between them. I guess I know how to do that in the enterprise manager. My questions are as follows:
If I have a relationship between those tables:
- how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
- shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
- shall I use the CASCADE options?
- and, If I use those options, how will they affect inserting, updating and deleting.
I know that my questions might be a bit vague. I've been trying to google for a primer on this but couldn't come up with something really helpful. I'd appreciated anyone shedding some light here. Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
matthias s. wrote: how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
I can't answer this one  .
matthias s. wrote: shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
Well, you need a country to exist or not? if you do need the country to exist then...
matthias s. wrote: shall I use the CASCADE options?
Most certainly, countries won't be disappearing anytime soon (I hope  ) but in terms of programming, how important is it that you delete 50 cities if you try to delete a country? I think it's probably more important that you keep those cities. Imagine 400 records related to those cities being deleted as well .
matthias s. wrote: and, If I use those options, how will they affect inserting, updating and deleting.
Well, using enforcement of relationship will ensure inserts and updates have valid values for countryID. Deleting a country can have a serious impact on the infromation, as I stated above, is it ok for you to delete 50 cities because you delete a country? is that a good idea? you need to ask that yourself.
I think it's good practice to define relationships (the most important being enforce relationship for inserts and updates) because this makes programming easier. Cascade Delete... be very careful with this one, think if it's appropiate or not to remove information with this. Cascade Update doesn't affect much, specially if you use identity fields or GUID keys, you won't be needing this.
I just hope I don't make it more confusing  .
daniero
-- modified at 19:19 Tuesday 8th November, 2005
|
|
|
|
|
hello,
i am very new to VC++.Net. i am getting lots of bugs while working with
MsAccess Database. Here is my code fragment. i used the Connection
object from toolbox. so the connection is ok. i am able to retrieve the
data. But i failed to modify (inserting, updating and deleting).
DataSet *ds;
OleDb::OleDbDataAdapter* adapter=new OleDb::OleDbDataAdapter("select
prt_text,cut from spool",oleDbConnection1);
oleDbConnection1->Open();
adapter->Fill(ds,"spool");
DataRow *dr=ds->Tables->Item[0]->Rows->get_Item(1);
ds->Tables->Item[0]->Rows->Remove(dr);
ds->AcceptChanges();
adapter->Update(ds,"spool");
oleDbConnection1->Close();
how do i delete a record, update a record and add a record. plz help
me.
Thank u in advance.
regards,
basheer.
|
|
|
|
|
Hello everyone, i have a very strange problem when i using the full-text searching engin provided by SQL Server to search the web site.
If the keyword is directly after the tag , then there will be no matched result return. But if i change the to , then the correct result will be returned by the server. I spent whole day to solve this problem, but still get nothing.
Why this happend?
Thanks a lot!!
|
|
|
|
|
Hallo
I have 3 Tables
<br />
1st table with idfrist - descriptionfirst<br />
2nd table with idsecond - descriptionfirst<br />
3rd table with id - idfirst - idsecond - value<br />
Now i'd like to built a transformed tabel:
colheader with all items from idfirst
rowheader with all item from idsecond
values as sum from 3rd tables value (cols are( tofirst - tosecond - value)
The transformation works fine for all values entered.
But if one col from the colheader isn't used in the 3rd table,
there is no output column for this field defined in the first table ?!?
here si what is already have done with 2 tables
<br />
TRANSFORM Sum(third.value) three<br />
SELECT second.tofirst<br />
FROM third RIGHT JOIN second ON third.idsecond = second.tosecond<br />
WHERE (((third.tofirst) Like "*"))<br />
GROUP BY third.tofirst<br />
PIVOT second.idsecond;<br />
thx for helping
Tim
|
|
|
|
|
Got to this link and register free for
training your self for VS2005
https://www.microsoftelearning.com/visualstudio2005/
or
http://www.financialexpress.com/latest_full_story.php?content_id=107645
Microsoft dont give anything free except training
Vikas Amin
Embin Technology
Bombay
vikas.amin@embin.com
|
|
|
|
|
Must you spam every bloody forum multiple times?
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 21:38 Saturday 5th November, 2005
|
|
|
|
|
|
i am trying to get the data about database from a database(msql/access) using schemas(oledb)but the result is i am unable to get all information like column datatypes big/long/var-Binary and if a column is of type long then weather it is a autotype or simple long etc
in simple i wana get all information of the database. Suggest me a way
Know it share it that is correct knowledge (...kittu)
-- modified at 3:26 Saturday 5th November, 2005
|
|
|
|
|
Hi, take a look at ADO.NET 2.0. In .NET 2.0 it is much easier to get schemas.
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
I am Using VS C# 2005 But unable to get all meta dadata info
/************************************************
try
{
ref_con.Open();
DataTable dt1 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
_dataset.Tables.Add(dt1);
DataTable dt2 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
_dataset.Tables.Add(dt2);
DataTable dt3 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
_dataset.Tables.Add(dt3);
DataTable dt4 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
_dataset.Tables.Add(dt4);
DataTable dt5 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, new object[] { });
_dataset.Tables.Add(dt5);
DataTable dt6 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { });
_dataset.Tables.Add(dt6);
DataTable dt7 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, new object[] { });
_dataset.Tables.Add(dt7);
DataTable dt8 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
_dataset.Tables.Add(dt8);
_dbtables.GetTableList(dt1, dt2,_dataset);
_dbcolumns.GetColumnList(dt1, dt5, dt6, _dataset);
results = true;
}
catch (Exception excp)
{
System.Windows.Forms.MessageBox.Show(excp.Message,"Failed To Get MetaData",
System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (!ref_con.State.Equals(ConnectionState.Closed))
ref_con.Close();
}
but still unable to get all info
try this using a table containing columns of AutoNumber,long
and see the datatable value-----Provider_Types.
there same number value for some(binary) data types
************************************************/
Know it share it that is correct knowledge (...kittu)
-- modified at 21:34 Saturday 5th November, 2005
|
|
|
|
|
I am using MSDE on my computer. I have application that uses SQL. The application and MSDE are on the same computer. My connecting string follows:
this.cnKennel.ConnectionString = "Data Source=(local)\\NetSDK;packet size=4096;integrated security=SSPI" + ";persist security info=False;initial catalog=kennel";
Because I will be installing this application on many computers I would like not to specify the computer name/server name. Can someone tell me how I can do this.
|
|
|
|
|
I guess I don't clearly understand the question.
If the MSDE will always be on the same computer as the application, then your current connection string will always work (provided MSDE installed with the instance name NetSDK).If MSDE is installed without an instancename, then just DataSource = (local) will always work on the same box.
If the MSDE will be on only one of many computers, then you have no choice but to specify the name of the server (or its IP address if using TCPIP network library) and the instance name (if installed as an instance).
If there will be multiple clients, you likely should read the server name part of the connection string from a configuration file for your app, so it can be changed without recompling.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Thank you much, that was it!!!
|
|
|
|
|
Hello
I want to get data from MSSql sample database: pubs
I registered it in ODBC as sqlMY
(i use dialogWindow, without database support)
I do:
CDatabase *dat;
CRecordset *rec;
dat=new CDatabase;
dat->Open( "sqlMY", FALSE, FALSE, "ODBC;", TRUE );
new CRecordset(dat);
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "select * from authors", CRecordset::none
);
And it shows error messege in rec->Open(...) , error is about SQLFetchScroll/SQLExtendedFetch
1. What i do wrong?
2. How can i access to my row data in CRecordSet (shouldi do CMyRecordSet() derived from CRecordSet and add there my data column as variables)?
thanks
Best Greetings
Michal
|
|
|
|
|
ok, i found, but....
<quote>virtual BOOL Open( UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none );
(...)
lpszSQL
A string pointer containing one of the following:
A NULL pointer.
The name of a table.
An SQL SELECT statement (optionally with an SQL WHERE or ORDER BY clause).
A CALL statement specifying the name of a predefined query (stored procedure). Be careful that you do not insert whitespace between the curly brace and the CALL keyword.
even if i do
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "authors",CRecordset::none );
it still have error
df
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
1. @@Servername is always going to give you the name of the server on which the SQL Server resides, regardless of which network client is used to execute the sp.
2. "G:/Export" always refers to a drive that is local to the SQL server (either a physical drive letter or a mapped drive). Prifixing it with the servername acomplishes nothing, since as the error messge states, that is an invalid way to specify a path.
3. You might be able to specify a UNC name: //SomeComputerName/somesharename/export,
but you would need to
a. share the destination drive (Say as Exports)
b. provide the sharing source comuter name either as a parametr value, or 'hard coded' into the SP. For example, if the destination computer is named "FileServer1",
and it shares the exports directory as Export_Share, then you would replace "G:\export" with "//FileServer1/Export_Share"
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
Hai,
I am having a table(Suppose Table1) with some data. After wards I have made some changes in the table so that the primary key changed and added some more columns(some references also). How I can write the script to modify the table with this change with out dropping the table. Because I need the data saved in the table. How I can write the upgradable script for that.
Please show me the right way. Thanks very much.
Thank You,
Rahul.
"Learn from Mistakes for suceess otherwise get buried with the Mistakes"
|
|
|
|
|
You could use ALTER TABLE[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
May be it's late now... but in Enterprise Manager, when you enter in 'design table', after you make the changes, there's a button in the tool bar that says Save Change Scripts, that will save a script that'll do exactly what you are asking for  . So you can run this script in another server for example. The script alters the table, it doesn't drop and remake it.
daniero
|
|
|
|
|
daniero wrote: The script alters the table, it doesn't drop and remake it.
Not necessarily. Depends on what you are doing. Sometimes this script will copy the data to a temp table, drop the existing table and recreate it, then rewrite the data back into it. Alot depends on what you are modifying, and whether or not default values can be used. Otherwise...great suggestion....letting sql server create the script is the best thing to do.
|
|
|
|
|
hellow to all ..
i have a tabels with data , i have field that have dates ...
how can i write an sql statment that pick the first date from all the dates that the tabel have ?
by first i mean the smaliest date ..
thxx
|
|
|
|
|
SELECT MIN(dateColumn) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
