release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

Relatively easy SQL SELECT statement issues :/

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;

CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.

Relationships: Cascade delete

Hi,

I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)

I also have a Project invoice table, in which ProjectID is referred as Foreign key.

Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.

Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?

Thanks!

Hi !

That is done using "On cascade delete" option. The Null option does the effect you described first, setting the foreign key field to Null. Seems that you have an old vesion of the foreign key in place. Drop the Constraint and recreate it using the "on delete cascade" keywords and you will see that the row will be deleted.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Relationships problem in Sql Server 2000

I am using Enterprise Manager for Sql Server 2000. In designing a foreign key relationship for a table, let's call it table A, I have more then one column acting as a foreign key for the same primary column in another table, let's call it table B. While I can specify for one of those foreign key columns in table A, which has the same column in another table as its primary key, to Cascade Deletes and Updates, successfully, when I try to also specify Cascade Delete and Updates for a second of my foreign key columns, which has the same column in another table as its primary key, I get an error saying that the second relationship with the Cascade "may cause cycles or multiple cascade paths."
I do not understand why this should be happening. If the Cascade is allowed for one of my columns, why does allowing it for more than one of my columns which point to the same primary key column cause this problem. Or is this just a bug in Sql Server 2000 which has been fixed in later releases of the product.No, it's not a bug, it's by design. If you want to create more than one cascade relationship between two tables, then there is only a way is to specify only first relationship as cascade and maintain other(s) via trigger(s).|||I do not understand the reasoning behind this design. I do not see how it matters if deleting a row in one table causes more than one row in another table to be deleted because of more than one cascade relationship between tables. Also the same situation for updated cascades should work properly also if there is more than one relationship between two tables. I can understand if the relationship goes both ways it could cause a recurring cycle, but if the relationship just goes one way it never can cause cascading problems AFAICS.

relationships problem

VB.NET 05 (ADO.NET) / SQL SERVER 05.
i have a many-to-many relationship. therefore, according to the normalisation theory, i introduced a new table which acts as a link between them.

1) tblOrders
PK: orderID

2) tblOrdersItems
FK: orderID
FK: itemID

3) tblItems
PK: itemID

the user wishes to view the details of a particular order which should include all the items
contained withing that particular order.

question: what is the technique to be used in order to get this done?

i) dim varOrderID as integer
ii) select tblOrdersItems.itemID where orderID = varOrderID
iii) HOW TO NOW FETCH the details of the items contained in this order from tblItems which should be displayed on a datagrid?

I believe you could use query similar to

SELECT list of field here From TblItems I INNER JOIN tblOrdersItems OI ON I.itemID=OI.itemID

WHERE OI.orderID=MyIDHere

|||

You can use the following query,

Code Snippet

Select * from tblOrders A

Join tblOrdersItems B on A.orderId = b.orderId

Join tblItems C on C.itemId = B.ItemId

Where

A.orderId = @.YourOrderId

sql

relationships as a table

I have the tables tblEmployees and tblProjects.
tblProjects has one column called Percentagetime, to hold the percentage of
time by an employee for a project.

My Question : Why should we introduce a 3rd table called
tblProjectsAndEmployees?
tblProjectsAndEmployees will have
1)EmployeeNumber(PrimaryKey in tblEmployees)
2)ProjectNumber(Primary key in tblProjects)
3)PercentageTime

Having only the tblEmployees and tblProjects is not sufficient?

Kind Regards,
Vinodh Kumar PVinodh Kumar P (vinodh@.here.com) writes:
> I have the tables tblEmployees and tblProjects. tblProjects has one
> column called Percentagetime, to hold the percentage of time by an
> employee for a project.
> My Question : Why should we introduce a 3rd table called
> tblProjectsAndEmployees?
> tblProjectsAndEmployees will have
> 1)EmployeeNumber(PrimaryKey in tblEmployees)
> 2)ProjectNumber(Primary key in tblProjects)
> 3)PercentageTime
> Having only the tblEmployees and tblProjects is not sufficient?

As long as an employee only works on one single project it is. Or a project
never can be staffed by more than one employee.

But in real life an employee works on more than project, and project have
several members. Then you have a many-to-many relation, and the typical
way to sort that out is to introduce a linking table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Relationship to table in another DB possible?

Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- Dave
Hi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>

Relationship to table in another DB possible?

Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- DaveHi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>

relationship question

I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.Well, if you create a table with "ad_ID then a number like 46_1.jpg, 46_2.jpg" ... then your select statement is very simple. "Select * from ad_table where ad_ID = ??" and your insert statement is simple. However, if you create a table with ad_id, ad1, ad2, ad3, ad4, ad5, ad6, ad7, ad8 .... then you are limited to 8 pictures for that ad ... if you some day need ad9 you will have to alter the table. Either way works but one is more scalable.|||Originally posted by lisa1958
I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.

Well, using the Normal Forms (i thought the third one), the answer should be : store the possible pictures of an ad in a seperate table. Since you don't know how many pictures there'll be for an ad, you don't want to create columns for. Just think of the possibility that in the future more than 8 pictures must be stored for an ad, and u only have 8 columns for it in the ad table. In that case you should change the datamodel and all kinds of interfaces. Brrrrrrrrr
So, create a table for the ad, a table for the ad-pictures and a foreign key between them

Hope this helps

relationship inside the same table

i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)

relationship between xml in xml data type column

How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
Joyce
If the relationship is between elements in the same instance (i.e. same "cell"
in your table) then you can write an XML schema and make use of types xsd:ID
anjd xsd:IDREF. If the relationship is between XML fragments stored in different
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collection:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>
|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:[vbcol=seagreen]
> If the relationship is between elements in the same instance (i.e. same "cell"
> in your table) then you can write an XML schema and make use of types xsd:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in different
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema collection:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd be glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegro ups.com...
|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegr oups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>
|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:

relationship between xml in xml data type column

How would i express a relation between different kinds of XML segments
in the same table with an XML datatype column.
Further, can i do this using dataset designer? Do you know where I can
get more information on how to use the dataset designer, or how to
write xsd for sql server 2005?
thanks,
JoyceIf the relationship is between elements in the same instance (i.e. same "cel
l"
in your table) then you can write an XML schema and make use of types xsd:I
D
anjd xsd:IDREF. If the relationship is between XML fragments stored in diffe
rent
rows or columns then this won't work.
Here's a link to the Books On Line page about creating an XML schema collect
ion:
http://msdn2.microsoft.com/en-us/library/ms176009.aspx
If you can post more information on what you're trying to accomplish I'd be
glad
to help.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...
> How would i express a relation between different kinds of XML segments
> in the same table with an XML datatype column.
> Further, can i do this using dataset designer? Do you know where I can
> get more information on how to use the dataset designer, or how to
> write xsd for sql server 2005?
> thanks,
> Joyce
>|||hi Denis
This is a helpful start. If I would like to relate between elements in
different row, but in the same column, is there a way to do that?
I'm looking at using Visual Studio dataset designer, and writing out
some queries that can perhaps write the schema out for me.
thanks,
Joyce
Denis Ruckebusch [MSFT] wrote:
> If the relationship is between elements in the same instance (i.e. same "c
ell"
> in your table) then you can write an XML schema and make use of types xsd
:ID
> anjd xsd:IDREF. If the relationship is between XML fragments stored in dif
ferent
> rows or columns then this won't work.
> Here's a link to the Books On Line page about creating an XML schema colle
ction:
> http://msdn2.microsoft.com/en-us/library/ms176009.aspx
> If you can post more information on what you're trying to accomplish I'd b
e glad
> to help.
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "joyce chan" <joyceschan@.fastmail.fm> wrote in message
> news:1169140712.945912.315860@.q2g2000cwa.googlegroups.com...|||I'm not familiar with the dataset designer.
I'd look at using relational mechanisms such as id columns to express the
dependency.
Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169218663.655366.182930@.v45g2000cwv.googlegroups.com...
> hi Denis
> This is a helpful start. If I would like to relate between elements in
> different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>|||Hello joyce,
No thats not possible. Schema validation is only on an individual cell.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> hi Denis
> This is a helpful start. If I would like to relate between elements
> in different row, but in the same column, is there a way to do that?
> I'm looking at using Visual Studio dataset designer, and writing out
> some queries that can perhaps write the schema out for me.
> thanks,
> Joyce
> Denis Ruckebusch [MSFT] wrote:
>sql

RELATIONALIZE. PLEEEEAAASE!

I just found this table:
CREATE TABLE (
TipCode CHAR(3) NOT NULL,
TipGroup CHAR(1) , -- WTF?!?
FatigueDispo CHAR(1),
HWDesc NVARCHAR(100),
SWDesc NVARCHAR(100),
PRTDesc NVARCHAR(100),
STDesc NVARCHAR(100)
)
/*no primary key, no indexes */
Data looks like this:
TipCode TipGroup ...
010 1
020 2
030 ?
040 ?
Instead of making the TipGroup column not-null, and refusing to use empty
strings, they have represented the unknown values with this: '?'
[bonking head on desk]
Celko, please come here and beat my colleagues to death. You get overtime
pay if you beat the bosses too. I might even help.
"Here let's use a relational system to do nonsensical things like store '?'
which means 'unknown'" But NULL? NOOOOOO!!!! That would be too easy!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneSr. developer:
"NULL? We can't use null... not in a char(1) column... there's no room! NULL
is like four characters long... futher more it might get truncated to 'N' an
d
that's already reserved... and even if it doesn't - who knows what NULL
menas... better make it a '?'... it's a question mark... everybody knows wha
t
*that* means..."
Jr. developer:
"Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
brilliant, too, sir!"
Mike:
#$&% *!|||What do you mean you can't use NULL in char(1) column? Have you tried it?
=0}
Let's just say for grins, you try it. What happens?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BAD57E39-F45F-4547-8540-DA8943EC2B1A@.microsoft.com...
> Sr. developer:
> "NULL? We can't use null... not in a char(1) column... there's no room!
> NULL
> is like four characters long... futher more it might get truncated to 'N'
> and
> that's already reserved... and even if it doesn't - who knows what NULL
> menas... better make it a '?'... it's a question mark... everybody knows
> what
> *that* means..."
> Jr. developer:
> "Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
> brilliant, too, sir!"
> Mike:
> #$&% *!|||Have you?
I guess Mike's co-workers did and they didn't like the fact that QA displays
all four letters suggesting something nasty and unpredictable. :)
ML|||He's not talking about the string "NULL"
He's talking about the NULL value.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D3191D85-26D4-4187-81CB-B40E21E683B1@.microsoft.com...
> Have you?
> I guess Mike's co-workers did and they didn't like the fact that QA
> displays
> all four letters suggesting something nasty and unpredictable. :)
>
> ML|||Yes, I know it, you know it and Mike knows it. But do *they* know it? I mean
- just look at it. Sure looks like a string. And if it looks like a string,
then it must be a string. :)
ML
p.s. we are still joking here, right?|||yup. :)
so did you try it?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:834F8E45-5C09-4A69-B9E7-5CBFEF9A3165@.microsoft.com...
> Yes, I know it, you know it and Mike knows it. But do *they* know it? I
> mean
> - just look at it. Sure looks like a string. And if it looks like a
> string,
> then it must be a string. :)
>
> ML
> p.s. we are still joking here, right?|||My QA displays NULL in the result-set. And if I copy and paste it as text it
becomes a string. That's what makes it nasty and unpredictable. :)
It's quite obvious why they decided on '?'. That is if one of them went
through the same process of analysis. LOL
ML|||> Sr. developer:
> "NULL? We can't use null... not in a char(1) column... there's no room!
> NULL
> is like four characters long... futher more it might get truncated to 'N'
> and
> that's already reserved... and even if it doesn't - who knows what NULL
> menas... better make it a '?'... it's a question mark... everybody knows
> what
> *that* means..."
> Jr. developer:
> "Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
> brilliant, too, sir!"
ROFLMAO!
This is EXACTLY how they work here! OMG! I didn't even know that you
worked for TNS!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BAD57E39-F45F-4547-8540-DA8943EC2B1A@.microsoft.com...
> Mike:
> #$&% *!|||TNS is not an isolated case. :) If that's any consolation to you.
Overheard in an office:
"Relational model? Yes, we do have one. I'm married to the boss. Now go back
to work!"
ML

relational integrity and transactional replication ??

Hi Friends,
I have a db with more than 350 table, most of them have Relational Integrity
with PK and FK
we want to have this DB replication to another server.
What are the potential problems and solutions?
I had experienced problems and conflicts if db has Relational Integrity.
Thanks in advance,
Pat
Patrick,
what type of replication are you considering? In particular, are the
subscribers to be updating ones?
What problems have you experienced in the past?
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You may consider using the "NOT FOR REPLICATION" clause on your RI
definitions. The idea is that the integrity has already been enforced at the
subscriber or publisher so there is no need to enforce it again.
This will allow you to publish articles in multiple publications without
having to worry about the order that replication tries to inesert records.
"Patrick" wrote:

> Hi Friends,
> I have a db with more than 350 table, most of them have Relational Integrity
> with PK and FK
> we want to have this DB replication to another server.
> What are the potential problems and solutions?
> I had experienced problems and conflicts if db has Relational Integrity.
> Thanks in advance,
> Pat
>
>

Relational Design

I have a database with 3 tables in a many to many relationship.

EmployeeGamingLicense
EmployeeDocuments
EmployeeRenewals

I created the third table to hold the employeerenewal dates, I did this because I cant have repeating vaules in the primary table. I am just making sure that my course of action is the correct one. The purpose of the Employeerenewals is to give the users an indication that a certain renewal has been processed, because in the past there was several hundred that were not processed. The problem was that the users had no way of knowing this. What I was thinking was having the primary table calculate a renewal date based off the finalsuit and show the results in the Employeerenewals with a yes of no drop down. Now the yes or no drop down box will be give the users their red flag if the renewals have been processed or not.

Does that make sense??

Thanks guys :)3 tables in a many to many relationship all related to 'Employee'? Does the table EmployeeRenewal hold anything else other than someID, refID (license?) and a yes/no? Personally, I don't think there is such a thing as 'the correct course of action'; there's just a lot of courses that either work, don't work or somewhere in between (sometimes work). Anyway, how does the solution work so far?sql

Relational Database design problem

Hi,

I have a very important question about the database design. Suppose that I have a table named as tblPerson with the following Fields.

tblPerson:
--------
PersonID
PersonName

And I have a another table that has the Person Credit card Information:

tblPersonCreditCard:
---------

CreditCardID
PersonID
CreditCardTypeID

And Another table which contains the type of creditcards:

tblCreditCardsType:
----------

CreditCardTypeID
CardName

NOTE: Suppose CreditCardTypeID 1 = "MasterCard", 2 = "Visa" , 3 = "platinum"

Now if a person suppose "john" has three credit cards then it will be stored as.

tblPersonCreditCard:
---------

CreditCardID PersonID CreditCardTypeID
1 1 1
2 1 2
3 1 3

Each if the number belong to one Field.

Which means that "john" has three credit cards

Now as u see that in tblPersonCreditCard john's id, which is 1 is repeating 3 time. how can I design a database in which the id does not repeat. I have heard that this can be done by using the bitwise operator but HOW.

thanks in advance,

AzamI don't see a problem with each record in tblPersonCreditCard including a foreign key to the credit card's owner. Is there some problem you're having with doing so?|||No i am not having any problem. But I just wanted to know that if there a better way of doing the same thing.|||There is no reason to do what you are attempting to do. To create a many-to-one relationship, you need to have a foreign key which will potentially repeat in the child table.|||I have heard that you can use some sort of the bit wise operator to store all the relationship of the person in a single field. I have no idea how ??|||I think you mean "bitmask", actually. Here'san article, if you're really interested.

Relational Data Transfer in DTS

Hi Please help for this simple problem

DTS Transfer or any other method?

I have Customer_Order Table and customer_Order_Details Table.

For OrderID = 1, I have 3 rows of Order Details.I want to transfer
Customer_Order Table for OrderID 1 in DTS, the system should transfer
Order Table as well as Order details table Rows for ORderID =1.

How to customize in DT or is there any way to get this data to transfer
from source DB to Dest DB?

KAMAL KUMAR V

--
Posted via http://dbforums.comYou should be able to do this easily. Set two transfers and use a query for
your data source instead of a table or view.

Start designing a new package. Create a source connection and a destination
connection. Then click the source, control-click the destination and then
click on the 'transformation' icon from the task icons on the left.

Right-click on the transformation arrow and select "properties." The first
tabbed dialog is "Source" change the type to a "sql query" and enter the
text of a query that will select just the rows you want in the text box.

You can use the "preview" button to see the first few rows from your new
source query.

Then you specify the destination and transformations in the usual way.

"kamalkumar" <member39115@.dbforums.com> wrote in message
news:3357162.1063280440@.dbforums.com...
> Hi Please help for this simple problem
> DTS Transfer or any other method?
> I have Customer_Order Table and customer_Order_Details Table.
> For OrderID = 1, I have 3 rows of Order Details.I want to transfer
> Customer_Order Table for OrderID 1 in DTS, the system should transfer
> Order Table as well as Order details table Rows for ORderID =1.
> How to customize in DT or is there any way to get this data to transfer
> from source DB to Dest DB?
> KAMAL KUMAR V
> Posted via http://dbforums.com

Relation in transactional Replication

I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Relation in transactional Replication

I'm using Transactional Replication.
If i publish a referencing table in my receiving DB i don't maintainig the
references. In my test with option "Merge Pubblication" i realise no
referencing but i 'm not sure for my tuning.
When i publish the articles (tables), SQL message me a warning, but i don't
understand if it's only a warning for advise me to publish all tables
related or it's a error that compromise my Replication
Thanks in advance
Stper
> If i publish a referencing table in my receiving DB i don't maintainig the
> references. In my test with option "Merge Pubblication" i realise no
> referencing but i 'm not sure for my tuning.
> When i publish the articles (tables), SQL message me a warning, but i
don't
> understand if it's only a warning for advise me to publish all tables
> related or it's a error that compromise my Replication
This is just a warning.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com