Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float Data Type for Money

Hi,
I'm now supporting a production database that uses the float data type to
store monetary values in one of the tables. No this is not my design but I
am required to support it and to generate reports for the data. I
understand that the float/real data types round incorrectly. How can I
round the data correctly? Here is an sample float value:
1139.3099999999999
I need 1139.31.
Thanks
JerryI also need to have any trailing zeros removed as well.
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm now supporting a production database that uses the float data type to
> store monetary values in one of the tables. No this is not my design but
> I am required to support it and to generate reports for the data. I
> understand that the float/real data types round incorrectly. How can I
> round the data correctly? Here is an sample float value:
> 1139.3099999999999
> I need 1139.31.
> Thanks
> Jerry
>|||This seemed to work:
convert(decimal(10,2),round(convert(mone
y,column),2)) --column is the
float column
Is this ok or...?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:emIDnmjvFHA.3400@.TK2MSFTNGP14.phx.gbl...
>I also need to have any trailing zeros removed as well.
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
>|||You cannot support it. It will not work, thanks to floating point
rounding errors. It is also illegal in the EU and in violation of GAAP
in the United States. Under SOX, there is a good chance that your boss
is going to jail for this kind of accounting. I would update my resume
and send the boss a letter so that you do not get caught up in the mess
that is coming.
Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
not convert to MONEY -- it is proprietary and has funny math.|||--CELKO-- wrote:
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
Hi Joe,
Please, please, please, can you post a reference to the illegality of
this in the EU? I can find *nothing* online (other than Euro
*conversion* rules), and We're about to have a new system introduced
here that uses floating point all over the place for currency, so if
you could provide a reference, I might be able to force a change to the
system.
Damien|||Thanks Joe.
Scary thing is it's an accounting-based software package. Yeah I noticed
this and about 5 other "Why did you...?" yesterday afternoon when I was
meeting with the vendor. I'll follow up with the vendor, management and
accounting today. Do you have any links that support the violation that I
can forward on?
For the reports I'll be generating in RS for account aging, what would the
kludge code look like to round to 2 decimal places and trucate trailing
zeros? Here is the code I came up with:
convert(decimal(10,2),round(convert(mone
y,column),2))
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127274857.269620.320950@.g43g2000cwa.googlegroups.com...
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
>|||On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
(snip)
>For the reports I'll be generating in RS for account aging, what would the
>kludge code look like to round to 2 decimal places and trucate trailing
>zeros? Here is the code I came up with:
> convert(decimal(10,2),round(convert(mone
y,column),2))
Hi Jerry,
No need to make it that complicated.
SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
will do.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Overcomplicating things again...damn! ;-)
Thanks Hugo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p8i3j1p1g9bcicg3doqjdpca4qocv534dp@.
4ax.com...
> On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
> (snip)
> Hi Jerry,
> No need to make it that complicated.
> SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
> will do.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is a quick "cut & paste":
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||Thanks Joe!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127417068.157889.200980@.g14g2000cwa.googlegroups.com...
> Here is a quick "cut & paste":
> The MONEY datatype has rounding errors. Using more than one operation
> (multiplication or division) on money columns will produce severe
> rounding errors. A simple way to visualize money arithmetic is to place
> a ROUND() function calls after every operation. For example,
> Amount = (Portion / total_amt) * gross_amt
> can be rewritten using money arithmetic as:
> Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
> Rounding to four decimal places might not seem an issue, until the
> numbers you are using are greater than 10,000.
> BEGIN
> DECLARE @.gross_amt MONEY,
> @.total_amt MONEY,
> @.my_part MONEY,
> @.money_result MONEY,
> @.float_result FLOAT,
> @.all_floats FLOAT;
> SET @.gross_amt = 55294.72;
> SET @.total_amt = 7328.75;
> SET @.my_part = 1793.33;
> SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.Retult3 = (CAST(@.my_part AS FLOAT)
> / CAST( @.total_amt AS FLOAT))
> * CAST(FLOAT, @.gross_amtAS FLOAT);
> SELECT @.money_result, @.float_result, @.all_floats;
> END;
> @.money_result = 13525.09 -- incorrect
> @.float_result = 13525.0885 -- incorrect
> @.all_floats = 13530.5038673171 -- correct, with a -5.42 error
>

Flipping answers in a table.

Hello all,

I have a simple task that I need to switch all the answers in my tables from yes(11) to no(10) and no to yes. It is simple enough to just make three update statements that will do this but is there a more elegant way to do this swap from one update statment.

Here are the three update statments:
update ans_test
set ans_chc_id = 99
where ans_chc_id = 11

update ans_test
set ans_chc_id = 11
where ans_chc_id = 10

update ans_test
set ans_chc_id = 10
where ans_chc_id = 99

Use CASE statements to allow a single update statement to do the work. Something like:

Code Snippet

update ans_test
set ans_chc_id
= case when ans_chc_id = 11 then 10
else 11
end
--where ans_chc_id between 10 and 11

The WHERE statement is necessary ONLY if the column contains values other than 10 and 11 and you do not want the row update in that particular case.

|||Excellent, thank you very much for your help.
|||

This 'seems' somewhat 'odd'. Normally a Yes/No question 'should' have three responses: Yes/No/Unknown. And equally often the data is stored as 0/1/NULL. I don't get values like 10, 11, and 99. It seems like you are really making things unnecessarily difficult for youself.

However, expanding on Kent's suggestion, you can handle the flipping 'flipping' with a CASE structure similar to this:

Code Snippet

UPDATE Ans_Test
SET Ans_Chc_ID = CASE
WHEN 10 THEN 99
WHEN 11 THEN 10
WHEN 99 THEN 11
END
WHERE {criteria}

flat file to raletionship database

I have an app which needs to download from mainframe flat file to my
relationship database (Parent - Child) tables.
I would like to know are there any better solution.
My flat file data structure like following:
Filed Name
Account Number : Char(10)
Account Name: Char(35)
Address1 Char(30)
Address2 Char(30)
City Char(25)
....etc
New table:
Parent table:
Account Number: Char(10)
Account Name Char(35)
Child table:
Account Number char(10)
Update ID Integer
Address1 char(30)
Address2 char(30)
City char(25)
The app downloads data every night and convert to my parent child table.
I can use append query to copy all the data to my table.
The problem is in child table. I need add a Update ID and validate
duplicates.
I have to append the data and make sure the record does not duplicates.
Are there any better solutions for this application?
Any information is great appreciated.
Thanks in advance,
Souris,Since you're the only who knows what the business rule for data
transformation, we would not be able to suggest much. The only advice I can
give is to upload the data into a work table and then call a stored
procedure that has your business rule implemented to massage the data.
-oj
"souris" <soukkris@.viddotron.com> wrote in message
news:u79vhWaCFHA.4008@.tk2msftngp13.phx.gbl...
>I have an app which needs to download from mainframe flat file to my
>relationship database (Parent - Child) tables.
> I would like to know are there any better solution.
> My flat file data structure like following:
> Filed Name
> Account Number : Char(10)
> Account Name: Char(35)
> Address1 Char(30)
> Address2 Char(30)
> City Char(25)
> ....etc
> New table:
> Parent table:
> Account Number: Char(10)
> Account Name Char(35)
> Child table:
> Account Number char(10)
> Update ID Integer
> Address1 char(30)
> Address2 char(30)
> City char(25)
>
> The app downloads data every night and convert to my parent child table.
> I can use append query to copy all the data to my table.
> The problem is in child table. I need add a Update ID and validate
> duplicates.
> I have to append the data and make sure the record does not duplicates.
> Are there any better solutions for this application?
> Any information is great appreciated.
> Thanks in advance,
> Souris,
>

Flat File into multiple SQL 2005 Tables.

Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes,

we have 16 .txt files, some with over 350 columns.

That info from each individual file needs importing to multiple sql tables.

need to look at sql table1 does record exist? if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns...

then look at sql table2 and repeat for next n columns....

So I was wondering is it going to be better to write this as a dtsx package? if so can you point me to an example

or should I just write the code as part of a code behind page that scrapes the info and does a standard update/insert procedure?

Any help would be welcome.

thanks

Sounds easy with a Flat File source, a lookup transformation (to see if you need to update or insert), OLE DB Destination and an OLE DB Command transformation...|||

Cheer Phil,

Do you know of any page that has an example to follow?

|||

Jamie has an example of hw to use the lookup transform to chek if the row exist:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Flat file CSV problem

Hi all,

I hope someone can help with a problem i'm having.
I want to process a large number of CSV files into various tables in an SQL database.
The CSV file contains entries on a row by row basis relating to specific events (indicated by an eventID in column 0).
Eventually i think i want to be using a conditional split to process each row seperately depending on the eventID but before i get this far i am having a problem with the source data.

Each event can have varying amounts of columns filled in in the CSV file. And each CSV file can have multiple event types in it.

The flat file manager seems to merge a number of different rows into one within the preview pane. It seems to ignore the end of row delimiter of CrLf.

Can anyone please help me to sort this so that each row is on its own and will allow me to pass the structured data set to a conditional split task?

Many thanks in advance,

GrantOk,

I seem to have managed to set all the data for each row into one component and have a script to extract the EventID to one output column and the remaining parameters to a second column. I need to perform a check on the EventID by passing it and another variable into a stored procedure. How do i go about doing this in the data flow section? is it possible or do i have to look at using the control flow section for this?

Thanks,

Grant|||

Grant,

What kind of check does that procedure performed? Keep in mind that any operation you define in the dataflow will be performed in row by row basis; so a call to a procedure in a data flow will be executed as many times as rows you have in the file. Since you already succeed on separating the eventID from the rest I would try to use a conditional split transformation based on the EventID value and then to performed specific transformations/checks to every data pipeline.

Rafael Salas

|||Hi Rafael,

Thanks for the response. The stored procedure i was talking about returns a value based on the event ID. Having thought about what you said i can set this value manually after the conditional split has been carried out and i know what the event ID is. Does that sound more plausible?

I am trying to rewrite a windows application that processes these files, initially this used a stored procedure to process each row of data. The main stored procedure calls other sub procedures within it. The problem was that the stored procedure itself was getting very unwieldly with a large number of if and nested if statement which meant that the addition of new events was complex and time consuming. Using the SSIS package a believe i can make this a much easier process to manage.

Is it still possible to call a stored procedure in the dataflow task once the conditional split has been performed? i realise that there may be other stored procedures required once i have my data row?

I have just attempted to run some SQL code on a per row basis and have discovered that i cannot seem to user variables or parameters in the OLE DB Command task. How would i go about either returning a value dependant on if the current eventID exists in another table in the database or indeed; how to insert data into a specific table if it doesn't exist.
After i do that then i can get on to processing the event data to the table where this is stored. I hope that makes sense.

Many thanks,

Grant|||

Wow a lot of questions!

First to all let me clarify that there is nothing wrong with having OLE DB Command tasks in your Dataflow; it is just that as personal pratice I try to use bulk operations against the DB when possible.

If what you are trying to use thr stored procedures for is to check if a row exists in a table; you can use the lookup transformation in your dataflow; then use the error output as your insert pipeline and the output as the 'already exists' kind of pipeline (or just not use it if you want to ignore them). The lookup can be also returned other columns from your lookup table if that is what you need

Rafael Salas

|||I tend to ask a lot of them yes :)

I looked into the lookup transformation which i can see how i would use the error output etc. I was then using an OLEDB destinbation to insert rows to the table. The problem with the OLEDB destination is that i cannot loop back to the lookup. With an oledb command would i script the insert command and then be able to loop back to the lookup transformation.
E.g.

Should this be ok to enter as a SQL Command:

if (select count(*) from SerialPartRev Where SerialPartRev.SerialNo = ? and SerialPartRev.PartNo = ?) = 0
begin
Insert into SerialPartRev (SerialNo, PartNo, RevisionNo)
Values(?,?,?)
end

The reason i want to do this is so that the SerialPartRev table is update automatically. I require to do the same thing with a Username table. The big problem is how to check again for the entry before processing further. As i have found looping back to the lookup isn't possible due to it only accepting one input path.

I'm quite happy to accept that i am doing this wrong and that maybe someone could suggest another process for implementing this.

Cheers,

Grant|||Rafael,

I have been reading you're previous post again and whilst i understand what you are saying about using the output and error output in the pipeline depending on if the row exists but one question still remains. Firstly i have to say that there will be a couple of instances where i have to check if data from the columns in the flat file exists in SQL tables. Regardless of if i have to insert the row manually or if it already exists after both of these operations the output still has to go to the one conditional split task. Effectively it splits the path in two and then rejoins after carrying out the necessary functionality. Is this possible without the use of scripting? I believe i can achieve this via a script although it will mean exposing a password in plain taxt so that the database connection string will work properly.

Thank you,

Grant|||Actually, forget it. I have managed to call the stored procedures form the OLE DB commands. I have no idea what i was doing wrong previously but it seems to be working now.

Thanks for you're help on this matter it was most appreciated. I'm sure i'll have more questions in time.

Cheers,

Grant

Flat File Connection with different row types

I have a file format that uses many rows to describe relationships between one-to-many entries. Basically in many tables I one file with the only relations between tables been the order of the rows.

From what I’ve found so far, the Flat file Connection is not the correct Connection type to use for this. Is there something better suited, or documentation of creating Connection types.

Cheers Simeon.

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamiesql

FK. Can I do this?

Hello,

I have 3 tables:
[A] > Aid (PK)
[B] > BId (PK)
[C] > CId (PK), TargetId (FK)

TargetId should be related to both Aid and Bid.
Records created in C can be related to records in A or in B and TargetId can be either a Aid or Bid.

Can and/or should I do this?

Thanks,
Miguel

Hey,

You can relate TargetID to both A and B, but that means that that value has to reside in both tables, or you will get a constraint exception. That means that TargetID cannot be "either a Aid or Bid", but has to be both, if you set it up as a FK.

If that is the case, then sure, it's better to add constraints that are valid than to not have them for integrity sake.

|||

Although you cannot have an FK that say either A or B, you could build a trigger that checks the existance of id in either A or B prior to inserting the value in the referring table.

Or you could perhaps build a CHECK constraint. Dunno if it is possible to do a SELECT (from A and B) in a CHECK constraint though.

|||

Hi,

This seems really strange. I will try to explain it by using the real project I am working on:

I have 3 tables: Posts, Events and Files.
Each post, event and file can be a associated to one or many tags.

My idea was to create only one Tags table.
Note that each tag can have various associations.
It can be associate to various posts, events and files simultaneous.

My idea was to create a Tags table as follows:
[Tags] > TagId (PK), PostId (FK), EventId (FK), FileId (FK).

- Will I have problems with my Transact SQL queries?
- Will I have problems with .NET 3.5 LINQ?

The other 2 options I see are:

1. Having only one FK in table Tags, i.e. TargetId, which could be
associated with PostId, EventId or FileId ...
This does seem right to me. I feel I will have problems later on.

2. Have 3 Tags tables: for posts, for Events and for Files.
I would like to avoid having 3 tables but ...

I need to extend my decision to categories, ratings, etc.
So having 3 Tags tables, 3 Categories tables, 3 Ratings tables does not seem a good idea.

Could, someone, please advice me on this?

Thanks,
Miguel

|||

Hey,

I would have one tags table, but not have any foreign key. Because the tag could be in any one of those three tables, but not all of them, I wouldn't do that personally. That requires some extra care when you are dealing with the data, in ensuring that if you remove anything, you ensure that the tag isn't being used anywhere else.

|||

I am going for this:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

I think it is the best option. I hope. :-)

Thanks,

Miguel

|||

Yeah, actually that would be better That does make some more sense than not having a FK.

FK Naming

Hello,

I have 2 tables: Articles and Users.
These 2 tables are related by AuthorId (FK) in Articles and UserId (PK) in Users.

My question is: should the use the same name for the 2 keys, i.e., UserId?

Or it is normal to use AuthorId in Articles table and UserId in Users table. This makes more sense.

Just a naming question.

Thanks,
Miguel

It's normal to use AuthorID. Actually this would make it more clear (for anyone else looking at your database) that users from the Users table are the authors of these articles.

So, if you ever created for instance a ReaderComment table, you can have a ReaderID FK that is also a reference to the Users table.

Good Luck :)

|||

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

|||

Answer is May be or may not be

As per the general norms in paent and child tables if columns names are same there will be no confusion.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Ania:

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

It's all about what flavor you prefer. Personally, I try to avoid involving prefixing/suffixing columns for PKs and FKs. I mean, if you were ever curious if it is an FK or not, have a look in the table definition. Making complex column names like this will only make your SQL code harder to read and debug.

My suggestion: Keep names simple and intuitive. Don't use names for expressing relations. The DDL is perfectly capable of doing this already.

(Besides, you have no guarantee that a column named FK is actually an FK...)

|||

I typically name the foreign key like this:

<child_table_name>_<parent_table_name>_fk.

If there are more than one, I name it like this:

<child_table_name>_<parent_table_name>_author_fk.

<child_table_name>_<parent_table_name>_reader_fk.

As for the foreign key column names themselves, I typically use this method:

UserIdAuthor

UserIdReader

It's a bit more typing than AuthorId and ReaderId, but, at a glance, you know both it's fk lineage and its business purpose.

PS. I don't put the fk column name in the fk name because there may be more than one column in the fk!

FK is showing PK ID instead of 'Name'

I have 2 tables, OT_ClaimantDetails and OT_OTDetails. PK in OT_OTDetails is OTDetailsID which is also FK in OT_ClaimantDetails. I am trying to run a select query to display certain data but instead of displaying the Name of the OT it displays the OTDetails ID.

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc
FROM OT_ClaimantDetails, OT_OTDetails
WHERE OT_ClaimantDetails.ClaimantDetailsOTDetailsID=OT_OTDetails.OTDetailsID

How should I change my query?

Is Name a column in one of the tables? If so, which one? If not, then where does it reside?

|||No, 'Name' is the users name I'm refering to. So basically instead of showing me 'ID 5' it should show me users number 5, for example, jack. In the OT_OTdetails table I have OTDetailsID and OTName. THe OTName is the 'Name' I would like to see instead of the OTDetailsID.|||

Unless there's something I'm missing you should be able to just add it to the select list.

Code Snippet

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc, OT_OTDetails.OTName

|||tried it, doesn't work. What I have done, I have changed the FK (used to be OT_ClaimantDetails.OTDetailsID) to OT_ClaimantDetails.ClaimantDetailsOTDetailsID, because I received a "Ambiguous column name" error in my browser. This solved the problem to display my results again, but now I see OTDetailsID instead of the OTName.|||

Can you post your DDL?

I don't see where you're referencing any OT_OTDetails columns in your select.

This doesn't make sense.

|||

I'm new at this, what do you mean by "post DDL"?

Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?|||

DDL = Data Definition Language....your table definitions.

>>Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?

No.

But you are joining the two tables together on the OTDetailsID (in your where clause).

So columns from the OT_OTDetail should be available.

When you put OTName in the select list, do you qualify it with OT_OTDetails. (not OT_ClaimantDetails)?

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||Standby guys, I'm going to try and get the DDL posted, maybe this will help you to help me.|||

I'm using SQL 2005.

Column OT_ClaimantDetails

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_ClaimantDetails] Script Date: 05/31/2007 17:07:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OT_ClaimantDetails](

[ClaimantDetailsID] [int] IDENTITY(1,1) NOT NULL,

[ReferanceDIMS] [varchar](50) NULL,

[ReferanceClient] [varchar](50) NULL,

[DateReceived] [datetime] NULL,

[DateEntered] [datetime] NULL,

[DateContactClaimant] [datetime] NULL,

[DateContactClient] [datetime] NULL,

[DateAppointm] [datetime] NULL,

[DateComplExpect] [datetime] NULL,

[DateCompleteActual] [datetime] NULL,

[DateCompleteCalc] [nchar](10) NULL,

[ReportOT] [varchar](250) NULL,

[ReportClient] [varchar](250) NULL,

[ClaimantFactID] [int] NULL,

[CompanyDetailsID] [int] NULL,

[ClaimantDetailsOTDetailsID] [int] NULL,

[BenefitsID] [int] NULL,

[DiagnosisID] [int] NULL,

[EvaluationID] [int] NULL,

[OccupationalID] [int] NULL,

[RecommendationID] [int] NULL,

CONSTRAINT [PK_OT_ClaimantDetails] PRIMARY KEY CLUSTERED

(

[ClaimantDetailsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits] FOREIGN KEY([BenefitsID])

REFERENCES [dbo].[OT_Benefits] ([BenefitsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact] FOREIGN KEY([ClaimantFactID])

REFERENCES [dbo].[OT_ClaimantFact] ([ClaimantFactID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails] FOREIGN KEY([CompanyDetailsID])

REFERENCES [dbo].[OT_CompanyDetails] ([CompanyDetailsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis] FOREIGN KEY([DiagnosisID])

REFERENCES [dbo].[OT_Diagnosis] ([DiagnosisID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation] FOREIGN KEY([EvaluationID])

REFERENCES [dbo].[OT_Evaluation] ([EvaluationID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational] FOREIGN KEY([OccupationalID])

REFERENCES [dbo].[OT_Occupational] ([OccupationalID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails] FOREIGN KEY([ClaimantDetailsOTDetailsID])

REFERENCES [dbo].[OT_OTDetails] ([OTDetailsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation] FOREIGN KEY([RecommendationID])

REFERENCES [dbo].[OT_Recommendation] ([RecommendationID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation]

--

COLUMN OT_OTDETAILS

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_OTDetails] Script Date: 05/31/2007 17:09:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OT_OTDetails](

[OTDetailsID] [int] IDENTITY(1,1) NOT NULL,

[OTName] [varchar](50) NULL,

[OTLastName] [varchar](50) NULL,

[Tel] [nchar](10) NULL,

[Fax] [nchar](10) NULL,

[Cell] [nchar](10) NULL,

[Email] [varchar](50) NULL,

[City] [varchar](50) NULL,

CONSTRAINT [PK_OT_OTFact] PRIMARY KEY CLUSTERED

(

[OTDetailsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Let me know if this helps..

THX

|||

Thanks Jackow, that helped better understand your situation.

From you original description, I think this is closer to your objective:

Code Snippet

SELECT
d.OTName
c.ClaimantFactID,
c.ClaimantDetailsOTDetailsID,
c.ReferanceDIMS,
c.DateReceived,
c.DateEntered,
c.DateCompleteCalc
FROM OT_ClaimantDetails c
JOIN OT_OTDetails d
ON c.ClaimantDetailsOTDetailsID = d.OTDetailsID

|||

Thanks jackow. Very helpful.

Exactly Arnie...looks like the original concept should be working.

|||

HI Guys,

Sorry for my delayed response. Thanks for the help. We will chat soon!!

FK is showing PK ID instead of 'Name'

I have 2 tables, OT_ClaimantDetails and OT_OTDetails. PK in OT_OTDetails is OTDetailsID which is also FK in OT_ClaimantDetails. I am trying to run a select query to display certain data but instead of displaying the Name of the OT it displays the OTDetails ID.

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc
FROM OT_ClaimantDetails, OT_OTDetails
WHERE OT_ClaimantDetails.ClaimantDetailsOTDetailsID=OT_OTDetails.OTDetailsID

How should I change my query?

Is Name a column in one of the tables? If so, which one? If not, then where does it reside?

|||No, 'Name' is the users name I'm refering to. So basically instead of showing me 'ID 5' it should show me users number 5, for example, jack. In the OT_OTdetails table I have OTDetailsID and OTName. THe OTName is the 'Name' I would like to see instead of the OTDetailsID.|||

Unless there's something I'm missing you should be able to just add it to the select list.

Code Snippet

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc, OT_OTDetails.OTName

|||tried it, doesn't work. What I have done, I have changed the FK (used to be OT_ClaimantDetails.OTDetailsID) to OT_ClaimantDetails.ClaimantDetailsOTDetailsID, because I received a "Ambiguous column name" error in my browser. This solved the problem to display my results again, but now I see OTDetailsID instead of the OTName.|||

Can you post your DDL?

I don't see where you're referencing any OT_OTDetails columns in your select.

This doesn't make sense.

|||

I'm new at this, what do you mean by "post DDL"?

Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?|||

DDL = Data Definition Language....your table definitions.

>>Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?

No.

But you are joining the two tables together on the OTDetailsID (in your where clause).

So columns from the OT_OTDetail should be available.

When you put OTName in the select list, do you qualify it with OT_OTDetails. (not OT_ClaimantDetails)?

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||Standby guys, I'm going to try and get the DDL posted, maybe this will help you to help me.|||

I'm using SQL 2005.

Column OT_ClaimantDetails

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_ClaimantDetails] Script Date: 05/31/2007 17:07:41 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_ClaimantDetails](

[ClaimantDetailsID] [int] IDENTITY(1,1)NOTNULL,

[ReferanceDIMS] [varchar](50)NULL,

[ReferanceClient] [varchar](50)NULL,

[DateReceived] [datetime] NULL,

[DateEntered] [datetime] NULL,

[DateContactClaimant] [datetime] NULL,

[DateContactClient] [datetime] NULL,

[DateAppointm] [datetime] NULL,

[DateComplExpect] [datetime] NULL,

[DateCompleteActual] [datetime] NULL,

[DateCompleteCalc] [nchar](10)NULL,

[ReportOT] [varchar](250)NULL,

[ReportClient] [varchar](250)NULL,

[ClaimantFactID] [int] NULL,

[CompanyDetailsID] [int] NULL,

[ClaimantDetailsOTDetailsID] [int] NULL,

[BenefitsID] [int] NULL,

[DiagnosisID] [int] NULL,

[EvaluationID] [int] NULL,

[OccupationalID] [int] NULL,

[RecommendationID] [int] NULL,

CONSTRAINT [PK_OT_ClaimantDetails] PRIMARYKEYCLUSTERED

(

[ClaimantDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits] FOREIGNKEY([BenefitsID])

REFERENCES [dbo].[OT_Benefits] ([BenefitsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact] FOREIGNKEY([ClaimantFactID])

REFERENCES [dbo].[OT_ClaimantFact] ([ClaimantFactID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails] FOREIGNKEY([CompanyDetailsID])

REFERENCES [dbo].[OT_CompanyDetails] ([CompanyDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis] FOREIGNKEY([DiagnosisID])

REFERENCES [dbo].[OT_Diagnosis] ([DiagnosisID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation] FOREIGNKEY([EvaluationID])

REFERENCES [dbo].[OT_Evaluation] ([EvaluationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational] FOREIGNKEY([OccupationalID])

REFERENCES [dbo].[OT_Occupational] ([OccupationalID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails] FOREIGNKEY([ClaimantDetailsOTDetailsID])

REFERENCES [dbo].[OT_OTDetails] ([OTDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation] FOREIGNKEY([RecommendationID])

REFERENCES [dbo].[OT_Recommendation] ([RecommendationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation]

--

COLUMN OT_OTDETAILS

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_OTDetails] Script Date: 05/31/2007 17:09:58 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_OTDetails](

[OTDetailsID] [int] IDENTITY(1,1)NOTNULL,

[OTName] [varchar](50)NULL,

[OTLastName] [varchar](50)NULL,

[Tel] [nchar](10)NULL,

[Fax] [nchar](10)NULL,

[Cell] [nchar](10)NULL,

[Email] [varchar](50)NULL,

[City] [varchar](50)NULL,

CONSTRAINT [PK_OT_OTFact] PRIMARYKEYCLUSTERED

(

[OTDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Let me know if this helps..

THX

|||

Thanks Jackow, that helped better understand your situation.

From you original description, I think this is closer to your objective:

Code Snippet

SELECT
d.OTName
c.ClaimantFactID,
c.ClaimantDetailsOTDetailsID,
c.ReferanceDIMS,
c.DateReceived,
c.DateEntered,
c.DateCompleteCalc
FROM OT_ClaimantDetails c
JOIN OT_OTDetails d
ON c.ClaimantDetailsOTDetailsID = d.OTDetailsID

|||

Thanks jackow. Very helpful.

Exactly Arnie...looks like the original concept should be working.

|||

HI Guys,

Sorry for my delayed response. Thanks for the help. We will chat soon!!

FK Index Advice

I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.

In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...

I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.

Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.

Thanks in advance!

Ryan HuntRun the CPU intensive statements through the Index Tuning Wizard (ITW) in order to get any index recommendations that may be necessary.

Review information from this http://www.sql-server-performance.com/mr_indexing.asp link on choosing indexes.

FK from other database

how to solve that problem?
i have 2 databases, in few tables from one database i need foreign keys from
other databases.Cant do it. You could try using triggers and stored procedures to check for
consistency but you can't use FK across databases.
MC
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OjfFmHtAFHA.1300@.TK2MSFTNGP14.phx.gbl...
> how to solve that problem?
> i have 2 databases, in few tables from one database i need foreign keys
> from other databases.
>

FK for tables out of the publication.

Hi ,
I would like to know what is the base approach for FK for tables out of the
publication.
I have several situations that I would like to add FK from tables out of
publication ( tables that do not replicate at all – historical tables or
replicate by different publication) to a table in live publication. This
actually can be done, but when I have to initialize the publication and apply
new snapshot the agent will fail because it does not able to drop the table
/delete rows due to the FK. I thought that define the FK as “not for
replication” will solve it but it doesn’t.
I have the problem on SQL 2000 enterprise edition SP3 and SP4.
Eyal
Eyal,
you can use pre-snapshot and post-snapshot scripts to handle FKs outside of
the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||What you need to do is change the article properties to keep the existing
table intact. Right click on your publication, select properties, click on
the articles tab, and then select the snapshot tab, and select keep existing
table unchanged. Note that this might duplicate data, so you may need to use
the delete data option and have cascading deletes on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:D207B0D7-8ADA-4BB1-98AC-962DE0778AD2@.microsoft.com...
> Hi ,
> I would like to know what is the base approach for FK for tables out of
> the
> publication.
> I have several situations that I would like to add FK from tables out
> of
> publication ( tables that do not replicate at all - historical tables or
> replicate by different publication) to a table in live publication.
> This
> actually can be done, but when I have to initialize the publication and
> apply
> new snapshot the agent will fail because it does not able to drop the
> table
> /delete rows due to the FK. I thought that define the FK as "not for
> replication" will solve it but it doesn't.
> I have the problem on SQL 2000 enterprise edition SP3 and SP4.
> Eyal
>

FK Constraint Problem

Hi All,
I use several SP's to create tables and I have run into a problem I do
not understand. All the SP's create a FK constraint when the table is
built and they all work, except for 1.
CREATE TABLE dbo.ZipCodes (
CountryID int NOT NULL,
StateID int NULL,
CountyID int NULL,
ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
ZipCodeStatus smallint NOT NULL,
ZipCode nvarchar (25) NOT NULL,
CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
)
The last FK constraint (*) will cause an error and the table is not
built. Once I add some data to the table then I can manually setup the
same constraint.
How do I get this to work when the SP is executed, before any data is added?
Thanks for any help,
Charles
On Tue, 14 Mar 2006 15:05:21 -0600, Charles E Finkenbiner wrote:

>Hi All,
>I use several SP's to create tables and I have run into a problem I do
>not understand. All the SP's create a FK constraint when the table is
>built and they all work, except for 1.
>CREATE TABLE dbo.ZipCodes (
> CountryID int NOT NULL,
> StateID int NULL,
> CountyID int NULL,
> ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
> ZipCodeStatus smallint NOT NULL,
> ZipCode nvarchar (25) NOT NULL,
> CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
>Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
>* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
>Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
>)
>The last FK constraint (*) will cause an error and the table is not
>built. Once I add some data to the table then I can manually setup the
>same constraint.
>How do I get this to work when the SP is executed, before any data is added?
Hi Charles,
What is the error message you get?
Also, are you sure that this is the constraint you need to define? It's
highly unusual to have an identity column as a foreign key column.
Are you sure that the constraint should not go on the ZipCode column?
(And a foreign key constraint on a CityID column would make even more
sense, but there is not even a column CityID in the table).
Or did you intend to add a foreign key to the Cities table that
references the ZipCodes table?
Hugo Kornelis, SQL Server MVP
|||On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Charles,
> What is the error message you get?
> Also, are you sure that this is the constraint you need to define? It's
> highly unusual to have an identity column as a foreign key column.
> Are you sure that the constraint should not go on the ZipCode column?
> (And a foreign key constraint on a CityID column would make even more
> sense, but there is not even a column CityID in the table).
> Or did you intend to add a foreign key to the Cities table that
> references the ZipCodes table?
>
Hi Hugo,
The exact error message is: "There are no primary or candidate keys in
the referenced table 'Cities' that match the referencing column list in
the foreign key 'FK_ZipCodes_Cities'."
The ZipCodes table contains each zip code, once. There can be many
cities that use that zip code (this is to maintain the history of the
changes a zip code goes through, it may be Unacceptable to the PO now to
use a certain city name but my data goes back to the DOS days and I flag
that city name as unacceptable).
The Cities table does contain a column called ZipCodeID and it is
defined as int, so a 1 to many relationship exists between ZipCodes and
Cities.
Also, I am no expert with SQL but I do have many years experience with
relational databases since the DOS days. It is possible that I have
messed up my logic.
Thanks for any help,
Charles
|||On 3/14/2006 5:36 PM, Charles E Finkenbiner wrote:
> On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Hugo,
> The exact error message is: "There are no primary or candidate keys in
> the referenced table 'Cities' that match the referencing column list in
> the foreign key 'FK_ZipCodes_Cities'."
> The ZipCodes table contains each zip code, once. There can be many
> cities that use that zip code (this is to maintain the history of the
> changes a zip code goes through, it may be Unacceptable to the PO now to
> use a certain city name but my data goes back to the DOS days and I flag
> that city name as unacceptable).
> The Cities table does contain a column called ZipCodeID and it is
> defined as int, so a 1 to many relationship exists between ZipCodes and
> Cities.
> Also, I am no expert with SQL but I do have many years experience with
> relational databases since the DOS days. It is possible that I have
> messed up my logic.
>
> Thanks for any help,
> Charles
Hi,
I just noticed something else. I do not need to add any data at all.
After the ZipCodes table is built I can manually define the constraint
with no error message.
Primary key table: ZipCodes
Primary key column: ZipCodeID
Foreign key table: Cities
Foreign key column: ZipCodeID
So, I can do this manually but not with code. Any ideas?
Thanks for any help,
Charles
|||On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
(snip)
>I just noticed something else. I do not need to add any data at all.
>After the ZipCodes table is built I can manually define the constraint
>with no error message.
> Primary key table: ZipCodes
>Primary key column: ZipCodeID
> Foreign key table: Cities
>Foreign key column: ZipCodeID
>So, I can do this manually but not with code. Any ideas?
Hi Charles,
A foreign key constraint is always defined on the referencing column,
which "lives" in the table on the "many" side of the one-to-many
relationship. You tried to define it in the ZipCodes table, whereas it
should have been defined on the Cities table.
ALTER TABLE Cities
ADD CONSTRAINT FK_ZipCodes_Cities
FOREIGN KEY (ZipCodeID)
REFERENCES ZipCodes (ZipCodeID)
ON DELETE CASCADE ON UPDATE CASCADE
Hugo Kornelis, SQL Server MVP
|||On 3/15/2006 2:28 PM, Hugo Kornelis wrote:
> On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
> (snip)
>
> Hi Charles,
> A foreign key constraint is always defined on the referencing column,
> which "lives" in the table on the "many" side of the one-to-many
> relationship. You tried to define it in the ZipCodes table, whereas it
> should have been defined on the Cities table.
> ALTER TABLE Cities
> ADD CONSTRAINT FK_ZipCodes_Cities
> FOREIGN KEY (ZipCodeID)
> REFERENCES ZipCodes (ZipCodeID)
> ON DELETE CASCADE ON UPDATE CASCADE
>
Hi Hugo,
I see the error of my ways. :D) My brain must have been on vacation
because I never caught that mistake. I guess using Access as my SQL
database designer has gotten me confused. I am in the ZipCodes table
when I define the constraint and it works in Access XP (2002). At the
same time I have other SP's that define constraints in the 'many' side
table and I never connected the two. Sorry for the newbie mistake and
thanks for taking the time to let me know.
Thanks for your help,
Charles

FK Checks

Suppose you have two (or more) tables with foreign key constraints. My
question is thus:

Is it better to check if the fk exists before you try to perform the
insert or let SQL do it for you?

On one hand, if you check yourself and the key does not exist you can
gracefully handle it (maybe exit out of method with error). If you let
SQL do it, the server will throw an error which cannot be suppressed.

On the performance side, you doing the check will incur a slight (VERY
slight) hit since SQL will ALSO check anyways.Jason (JayCallas@.hotmail.com) writes:
> Suppose you have two (or more) tables with foreign key constraints. My
> question is thus:
> Is it better to check if the fk exists before you try to perform the
> insert or let SQL do it for you?

Depends on business requirements. Basically, Fkeys is the database's
mean of protection against bad data. If the user interface does not
perform any checks itself, and relies on the database, you may avoid
integrity violations, but the users may not get adequate error message.

The way I see database constraints constitutes an inner defense line.
The user interface should help the user, and have its own defense line.
Error messages from the database exposed to the user, should be considered
a bug.

But validation in a GUI, can be quite different from the validation in
SQL Server. Normally you don't let the user to type in the FK as free-
text, but you let him choose from a drop-down box or from a search screen.
Once you know, the user has selected data this way, you can assume that
you are safe. (Although, someone may delete the row before the users
saves.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns944C1D0487E5Yazorman@.127.0.0.1>...
> Depends on business requirements. Basically, Fkeys is the database's
> mean of protection against bad data. If the user interface does not
> perform any checks itself, and relies on the database, you may avoid
> integrity violations, but the users may not get adequate error message.
> The way I see database constraints constitutes an inner defense line.
> The user interface should help the user, and have its own defense line.
> Error messages from the database exposed to the user, should be considered
> a bug.
> But validation in a GUI, can be quite different from the validation in
> SQL Server. Normally you don't let the user to type in the FK as free-
> text, but you let him choose from a drop-down box or from a search screen.
> Once you know, the user has selected data this way, you can assume that
> you are safe. (Although, someone may delete the row before the users
> saves.)

I should have been more explicit in my question. I was referring to
performing checks within stored procedures.

Assuming Table2 has a FK relationship with Table1.

IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @.field1)
INSERT INTO Table2 (@.field1, @.field2, field3)

as opposed to

INSERT INTO Table2 (@.field1, @.field2, field3)
IF @.@.Error RAISERROR('Unable to insert into Table2 because of missing
constraint', 15, 1)|||Jason (JayCallas@.hotmail.com) writes:
> I should have been more explicit in my question. I was referring to
> performing checks within stored procedures.
> Assuming Table2 has a FK relationship with Table1.
> IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @.field1)
> INSERT INTO Table2 (@.field1, @.field2, field3)
> as opposed to
> INSERT INTO Table2 (@.field1, @.field2, field3)
> IF @.@.Error RAISERROR('Unable to insert into Table2 because of missing
> constraint', 15, 1)

Again it depends. Say that your procedure is to be called from a GUI. In
this case you can presume that the GUI gets the information from the
databaes, and a foreign-key violation could only occur if the GUI is
incorrect. On the other hand, say that you are accepting data from an
external source over which you have no control. In this case, it is better
to check explicitly, so that you can log incorrect data appropriately.
Recall that when a constraint blows up, you cannot in T-SQL determine
which constraint that fired.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

fixing SQL CE Merge replication

Does anyone know of a way to regenerate the system tables that merge
replication uses? A lot of my users are getting blank system tables which
causes native exceptions when I go to sync or reinitialize. The number of
rows that I would stand to lose if I couldn't regenerate these tables is
really unacceptable. On a side note, how are these tables clearing
themselves? Our program doesn't touch any system tables and the db is
password protected to keep users out of it.
Currently, we are creating a new subscription and then inserting the rows
that are in the old database into the new db. The problem with this is that
it takes over an hour per database.
Something is very wrong here. Your merge replication system tables should
have data in them. Especially msmerge_contents, and msmerge_replinfo.
You should call PSS on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Scott Simons" <Scott.Simons.At.MealMagic.Com.Remove.This> wrote in message
news:8D20ADAA-6607-4AEB-A2FE-AF7E2A33DD93@.microsoft.com...
> Does anyone know of a way to regenerate the system tables that merge
> replication uses? A lot of my users are getting blank system tables which
> causes native exceptions when I go to sync or reinitialize. The number of
> rows that I would stand to lose if I couldn't regenerate these tables is
> really unacceptable. On a side note, how are these tables clearing
> themselves? Our program doesn't touch any system tables and the db is
> password protected to keep users out of it.
> Currently, we are creating a new subscription and then inserting the rows
> that are in the old database into the new db. The problem with this is
that
> it takes over an hour per database.

Fixing a screwup

After synching a handful of subscribers, I reviewed the data that came back
and realized I forgot to set auto range identity on two tables that required
it. So I dumped the pub, dumped the subscriptions, manually assigned the
identity values to consecutive identities, reset the identity columns to
"not for replication", re-created the publication, then let the subscribers
resynch.
Before anyone goes berserk on manually assigning an identity value, note
that at this time, the identity values on those two tables (at the "end of
the food chain" so to speak) are not being used or keyed to other tables,
but may in the future. Whether a record is 1 or 15 or whatever is
irrelevant, but I want to simply ensure uniqueness for future use.
So, my question is, have I overlooked anything or are there other hidden
potential issues?
Earl,
I'm not too sure what you did. It seems as though you set up a nosync
initialization? Or was it a normal initialization but without automatic
range management?
In one place I worked they would often forget to set the automatic range
management on articles. Rather than reinitializing, which was simply not
possible, what they'd do is reseed the identity value at the subscriber.
There were only 3 subscribers, so there was never a posibility of range
overlaps. The main difference to automatic range management was the absence
of check constraints and no possibility of getting a new range from
MSrepl_identity_range but as the difference between seeds was so large it
would be 200 years or so before a clash occurs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, it was a normal initialization but without automatic range management.
It's an easy thing to forget, especially if you've had to go through a lot
of contortions massaging the data into shape. The subscribers are PocketPCs,
so it would've been a real PIA to reseed those values. The identity values
back in the pub (once synched) were like this:
1
2
3
...
1
2
3
...
1
2
3
Seemed easier to drop the pub and the subscriptions, then do a manual reseed
of the pub tables (had I not caught it immediately and thus been dealing
with a large number of records, I'm not sure how I would've handled the
situation):
1
2
3
4
5
6
7
8
9
...
Which brings to mind a followup question: If my subscribers had the same
name as their pubs then I could've just created a new publication AND
subscription and just moved on? Thanks for your thoughts!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e04BD$vdFHA.892@.tk2msftngp13.phx.gbl...
> Earl,
> I'm not too sure what you did. It seems as though you set up a nosync
> initialization? Or was it a normal initialization but without automatic
> range management?
> In one place I worked they would often forget to set the automatic range
> management on articles. Rather than reinitializing, which was simply not
> possible, what they'd do is reseed the identity value at the subscriber.
> There were only 3 subscribers, so there was never a posibility of range
> overlaps. The main difference to automatic range management was the
> absence of check constraints and no possibility of getting a new range
> from MSrepl_identity_range but as the difference between seeds was so
> large it would be 200 years or so before a clash occurs.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
"Which brings to mind a followup question: If my subscribers had the same
name as their pubs then I could've just created a new publication AND
subscription and just moved on? Thanks for your thoughts!"
- sorry - I'm probably being a bit thick, but I don't follow this. How can
the subscriber have the same computer name as the publisher, and in what way
would this simplify things?
Rgds,
Paul Ibison (www.replicationanswers.com)
|||Hehe ... merge publication on a PocketPC, subscribers assign their own
subscription name, which I usually just keep as the same name across the
board. I have an input form in my app where the users can enter all of their
replication settings. In this case, once I had reseeded the tables, I
could've just created a new pub and set up auto range management at the same
time. Instead of the users synching to the old pub, they would enter the new
pub and subscription names, tap the synch button (which would create a new
db onboard and download the new pub) and keep on trucking. Perhaps it is me
that is "thick" on this one because that may or may not have worked (maybe
I'll try that next time).
Sorry for not including more about how my app works -- I'm primarily a
developer and not a DBA (although since I spend so much time with SLQ
Server, I decided long ago to go ahead and try to get the MCDBA cert).
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehq9zp0dFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Earl,
> "Which brings to mind a followup question: If my subscribers had the same
> name as their pubs then I could've just created a new publication AND
> subscription and just moved on? Thanks for your thoughts!"
> - sorry - I'm probably being a bit thick, but I don't follow this. How can
> the subscriber have the same computer name as the publisher, and in what
> way would this simplify things?
> Rgds,
> Paul Ibison (www.replicationanswers.com)
>