Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

Foreign key and indexes

I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a écrit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henri
a Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR
|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

Foreign Key - ON DELETE RESTRICT

Hi

Are there any way to use foreign key in MS-SQL Express with ON DELETE RESTRICT like other databases?

Best Regards

Igor Sane

to my knowledge there are only the cacade update/delete functions|||

isane did this answer your question? if not please provide more info or mark answer.

thanks,

derek

sql

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

foreign key

hi friends,
I want to check the relationship between tables before migration.
so i wrote a procedure which will push the unrelated data from the source
db(@.i_oldDB) to the error database(@.i_errorDb).
alter procedure TransactionValidation
(
@.i_oldDb varchar(100),
@.i_errorDb varchar(100),
@.i_ParentTable varchar(100),
@.i_ChildTable varchar(100),
@.i_PrimaryKey varchar(100),
@.i_ForeignKey varchar(100)
)
as
begin
Declare @.SQL nvarchar(4000)
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table ' +
@.i_errorDb + '..'+ @.i_ChildTable
exec sp_executesql @.sql
select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + ' from
'
+ @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
@.i_ChildTable + '.' + @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' +
@.i_oldDb + '..'+ @.i_ChildTable + '.'
+ @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
end
now my problem is, if i have multiple relationship column in the table...
this will not work. how to do this?
its very urgent.
pls help me to solve this.
thanks
vanithaThere are a couple of alternatives to NOT IN for composite keys. You could
use NOT EXISTS or an OUTER JOIN. The generated SQL would be something like
the untested examples below.
SELECT *
INTO MyErrorTable
FROM MyChildTable
WHERE NOT EXISTS
(
SELECT *
FROM MyParentTable
WHERE MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
)
SELECT MyChildTable.*
INTO MyErrorTable
FROM MyChildTable
LEFT OUTER JOIN MyParentTable ON
MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
WHERE MyParentTable.Col1 IS NULL
Hope this helps.
Dan Guzman
SQL Server MVP
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:710A7736-41F8-478F-BFFF-3AA86A31FFE0@.microsoft.com...
> hi friends,
> I want to check the relationship between tables before migration.
> so i wrote a procedure which will push the unrelated data from the source
> db(@.i_oldDB) to the error database(@.i_errorDb).
> alter procedure TransactionValidation
> (
> @.i_oldDb varchar(100),
> @.i_errorDb varchar(100),
> @.i_ParentTable varchar(100),
> @.i_ChildTable varchar(100),
> @.i_PrimaryKey varchar(100),
> @.i_ForeignKey varchar(100)
> )
> as
> begin
> Declare @.SQL nvarchar(4000)
> select @.SQL = 'if exists (select * from ' + @.i_errorDb +
> '.INFORMATION_SCHEMA.TABLES where ' +
> 'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table '
> +
> @.i_errorDb + '..'+ @.i_ChildTable
> exec sp_executesql @.sql
> select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + '
> from
> '
> + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
> @.i_ChildTable + '.' + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
>
> exec sp_executesql @.sql
> select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where '
> +
> @.i_oldDb + '..'+ @.i_ChildTable + '.'
> + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
> exec sp_executesql @.sql
>
> end
> now my problem is, if i have multiple relationship column in the table...
> this will not work. how to do this?
> its very urgent.
> pls help me to solve this.
> thanks
> vanitha
>

Foreign key

Hello,
I need to add a column to a table. The new column is a foreign key which references another table's column.

how can you do this? Does it need to be done in two steps, like:

alter table table1
add new_col_name datatype

then

alter table table1
add table constraint.

If so, what is the syntax for adding a foreign key contraint which references another table.

thank youHello,

Yes, it has to be done in two steps, just as you said.

To add a foreign key constraint :

ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field1)
REFERENCES table2(field2);
Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12)
REFERENCES table2(field21, field22);

Regards,

RBARAER|||thanks! what does the fk_table1_table2 mean? Is it just a lable?|||It can be done in one step, at least it can on Oracle:
alter table table1
add (new_col_name references table2(keycol));

or (to give the constraint a specific name):

alter table table1
add (new_col_name constraint table1_table2_fk references table2(keycol));|||cool, I'll try that also, but the first code worked.

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
>> I want to make a foreign key relationship between two tables but the key
> is
>> multiple fields. I am getting an error message when I try.
>> '''''?
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:
> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.
>
> "sql" wrote:
> > I want to make a foreign key relationship between two tables but the key
is
> > multiple fields. I am getting an error message when I try.
> > '''''?

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql

Foreign Key

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key

for example: consider two table "Staff" and "Department"

Staff with following columns
PK_ID
FK_DepartmentID
Name
Address

Department with following columns
PK_DepartmentID
DeptName

Actually what i need is: Initially i would be having the table name as
"Staff"
from Staff table i need to identify that the column FK_DepartmentID is
a foreign key
and the primary key is in the Department table

i need to traverse from Staff table and identify that FK_DepartmentID
is a primary key in Department table

this has to be accomplished by sql query... probably this could be
fetched from
Data Dictionary but i couldnt find the relationship between the system
tables.

Thanks
ArunDhaJArunDhaJ wrote:

Quote:

Originally Posted by

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key
(..)

(SQL Server 2005)

IMHO the easiest way is to use sys.foreign_keys. You don't need any
other system view. Try this:

USE YOUR_DATABASE; -- remember about current database context

SELECT
OBJECT_NAME(parent_object_id) as table_with_FK,
OBJECT_NAME(referenced_object_id) as referenced_table
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) = 'Staff'

--
Best regards,
Marcin Guzowski
http://guzowski.info

foreign key

Hi

is it a good practice to create FK on each & every column in a table whose values are taken from master table.

should I create or not.

If I create will that be heavy i.e what about memory consumption ?

pls reply

Thanks

Shubhangi1. You can foreign keys.

or

2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field

ro

3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0

or

if exists(select 1 from tablename where ...)

Foreign Key

how can i create a foreign key which spans on 3 columns.

Quote:

Originally Posted by NaimishGohil

how can i create a foreign key which spans on 3 columns.

Can u explain your problem a bit more. and can u provide some structure that u want to create.

Mandy

Foreign Key

Hi,

Is sql server performance affected with 7 to 8 FK on a single master table.

Thanks
ShubhangiNot at all

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

foreign key

i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2

'Normally', related tables live within the same database.

The Foreign Key constraint declaration doesn't go outside the scope of the database, so in this case you can't declare a FK constraint.

AFAIK, the option you have to enforce cross-database FK relationships, is by using triggers.

/Kenneth

|||

You can’t create a constraint across the database. But there is a workaround available to fix your issue. Using Instead of trigger / for after trigger. But I recommend to use the Instead of Trigger rather than after trigger..

Code Snippet

Use DB1

Go

Create table A

(

ID int Primary Key,

Name varchar(100)

)

Go

Code Snippet

Use DB2

Go

Create table BB

(

Id int,

[Desc] varchar(100)

)

Go

CreateTrigger BB_Triger

on BBInstead of Insert

as

Begin

Insert Into BB

Select * from Inserted as ins Where Exists (Select 1 From DB1..A a Where a.id = ins.id)

End

/*

--use any one

Create Trigger BB_Triger

on BBAfter Insert

as

Begin

Delete from BB

Where NOT EXISTS (Select 1 From DB1..A a Where a.id = BB.id)

End

*/

GO

Code Snippet

Insert Into DB1..A values(1,'One')

Insert Into DB2..A values(2,'Two')

Code Snippet

Insert Into DB2..BB values(1,'Valid')

select * from BB

Insert Into DB2..BB values(4,'In Valid')

select * from BB

|||Thanks good idea

foreign key

Hello,

I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me? Thanks in advance.

Regards,

Roel AlblasHi Roel,

I'm not quite sure what you mean. Do you need to know how to write the SQL statement that uses a foreign key? Or create tables with a foreign key?

Tell us more about what you want to do and we'll try to help.

Don|||I'm making a poll following an example. In that example I have to make tables wich has an relation to each other with a foreing key. I use SQL Server 2000 and Web Matrix.

Roel|||So you're building a table. And you'll use EM to do it. Okay.

Here's a simple example. Since I don't know the particulars of the data you'll use, I'll use a simple contact management example, where each person can have multiple phone numbers.

The Person table would look something like this:

PersonID int (identity, primary key)
Name varchar(30)
...

The Phone table would look something like this:

PhoneID int (identity, primary key)
PhoneNumber varchar(15)
PersonID int
... (type of number, etc.)

In this case, I've named the linking field, PersonID, the same in each table but that is not necessary. You may want to use a naming standard that identifies both primary and foreign keys in your tables. Note in particular that Phone.PersonID isnot an identity field, because it can have duplicate data when a person has several phone numbers.

Using this structure you can now do joins on the two tables to return all of the numbers for a person, or a list of everyone and their phone numbers.

If you want the database to enforce referential integrity (make sure that there are no phone numbers without a person, cascade deletes, etc.) you can also create a relationship between the tables. The easiest way to do this in EM is to create a database diagram with the two tables and create it visually.

Is this enough information? If not, ask away.

Don|||Hi

Just wondering if you know of a similar feature in web matrix to create relationships?
I am using MSDE?

Thanks

Ramila|||No, I sure don't. It's been a while since I did a project with Web Matrix.

There are some other admin tools available, such as these, but I don't know their capabilities for creating relationships:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also use T-SQL through the osql command-line utility to create or modify your tables.

How are you creating the structure of your database? Through Matrix? Another way?

Don

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin
> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520

> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)
|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>
|||Greg,
Thank you very much, this really helps!
-Lin

>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>
>.
>
|||Aaron,
Thank you very much!
-Lin

>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
??????
Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> ??????
|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>
|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> ??????
|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
is[vbcol=seagreen]

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT
'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key i
s
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
>
is[vbcol=seagreen]sql

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520

> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin

>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1bf2f01c4522f$18cc4fc0$a601280a@.phx
.gbl...
>
>.
>|||Aaron,
Thank you very much!
-Lin

>--Original Message--
>http://www.aspfaq.com/2520
>
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>

Foreign in Snapshot Replication

When I initialized and started the snapshot agent, how can I move the table
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:

> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:

> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:

> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>

Foreign characters in email

Hi,

My applications sends email using the smtp service.

When the service sends the mail, the content has some foreign characters in it.

These generally occur when I give forward the mail to someone or when the content has a fullstop.

I have given the subscriber's locale as en-us.

Below given is the first instance which i am facing.

Note: This is an auto-generated mail..Do not reply to this mail..

Above you can see, there are two dots instead of one.

Another instance is when I forward the mail:

?噉?扮灳??噉?剂???慬杮攽?獵挠慬獳伽瑵潬歯敍獳条?慥敤?污杩?氢晥??剉∽呌≒￣??慴楢摮硥∽???但呎?捡?吢?楓敺∽?￣????匠???????￣?剂￣?吾??圠????匾??匠?????乏???????噉??噉Hi,

These are the characters i had mentioned above.

Are you setting the BodyEncoding element in the DeliveryChannel of the ICF? Something like:

<Argument>
<Name>BodyEncoding</Name>
<Value>utf-16</Value>
</Argument>

HTH...

Joe

|||yes it is utf-16

Regards,
navbhan|||Generally speaking, I've seen a number of programs that do not natively support UTF-16. Try UTF-8 and see what happens. If your application requires UTF-16, that may not be a long term solutions, but at least it may help to clarify the issues a bit.

HTH...

Joe

Foreign characters in email

Hi,

My applications sends email using the smtp service.

When the service sends the mail, the content has some foreign characters in it.

These generally occur when I give forward the mail to someone or when the content has a fullstop.

I have given the subscriber's locale as en-us.

Below given is the first instance which i am facing.

Note: This is an auto-generated mail..Do not reply to this mail..

Above you can see, there are two dots instead of one.

Another instance is when I forward the mail:

?噉?扮灳??噉?剂???慬杮攽?獵挠慬獳伽瑵潬歯敍獳条?慥敤?污杩?氢晥??剉∽呌≒￣??慴楢摮硥∽???但呎?捡?吢?楓敺∽?￣????匠???????￣?剂￣?吾??圠????匾??匠?????乏???????噉??噉Hi,

These are the characters i had mentioned above.

Are you setting the BodyEncoding element in the DeliveryChannel of the ICF? Something like:

<Argument>
<Name>BodyEncoding</Name>
<Value>utf-16</Value>
</Argument>

HTH...

Joe

|||yes it is utf-16

Regards,
navbhan|||Generally speaking, I've seen a number of programs that do not natively support UTF-16. Try UTF-8 and see what happens. If your application requires UTF-16, that may not be a long term solutions, but at least it may help to clarify the issues a bit.

HTH...

Joe

foreign characters are not being imported into the table correctly

hello everyone,

i have few fields that contain foreign characters with diacritic marks which are not getting imported correctly.

below is the import format:

- File type: ASCII
- Row delimiter: carriage return and line feed {CR/LF}
- Column delimiter: Tab
- Text qualifier: None

Please advice.

Here is the errors i'm getting:

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Country_str_local_long_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Country_str_local_long_name" (37)" failed because truncation occurred, and the truncation row disposition on "output column "Country_str_local_long_name" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "L:\Country.txt" on data row 6.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Country_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

Hi,

Have you set the properties for the locale and the default code page?

If you're using a Flat File source, these properties are available in the Flat File Connection Manager Editor dialog box. You open this dialog box by double clicking the Flat File Source control, and then clicking New in the Flat File Source Editor dialog box.

sql

Foreign and primary keys

I have an application in which i need to get the foreign key fields
from a table and then get all the foreign keys primary key field from
the linking table. Could some one tell me how i do this using
INFORMATION_SCHEMA. I have tried and can get the foreign keys but not
sure how to get the associated primary keys.See:
http://groups.google.com/group/micr...a0218d9e069531c

Razvan

foreign and primary key question

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.

Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?

thanks

Yes you'd have to manually INSERT the data into the other table. By setting up the PL-FK constraint you are just setting up a "relation" between the tables so any inserts/updates/deletes into the tables are checked for their data consistency.

Assuming your first INSERT is going through a stored proc, get the ID of the value just inserted via SCOPE_IDENTITY() and immediately do the INSERT into the second table. You could also do this via triggers but I dont recommend it. they are a big performance overhead and drag your system.

Foreign And Composite Keys

Hi.

Could somebody please explain to me how to create a foreign key from a table that
has two composite keys? I have a table, UserPrecedence, with two composite keys -
up_owner, owner_userID. I have a second table, Users, that has the primary key
"emailAddress" and a userID table to which all other tables point their FK columns.

http://i103.photobucket.com/albums/m156/pbd22/Keys.jpg

I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence
but the Modify Table view complains that "both sides of the relationship must have the
same number of columns" when I try to create the relationship.

I am guessing this is because its a composite primary key. Can somebody explain
to me how this is done correctly (and why)?

I appreciate your help.
Thanks.

If you want to reference the "UserPrecedence" table from the "Users" table according to the present key structure of the "UserPrecedence" table you must also include the "up_order" column in the "Users" table. However, there might be other possibilities.

First, is the "owner_userID" column of the "UserPrecedence" table a unique column? If so, you might consider changing your primary key to this column.

|||
hi, thanks.

yes, owner_userID is unique but I need to keep my PK in UserPrecedence as a composite key on (up_order, owner_userID). The foreign key points from this table (source) to Users_userID (destination). Does this mean that I always have to add the composite PK column (I have many tables like this) that is not in the Users table to create the FK relationship?

thanks for your help.

|||

If your owner_userID field is unique, then you can do the following (which may not be best practice but it is an option):

You can alter your table and add a UNIQUE constraint on the owner_userID field

You can use then use the owner_userID field as a foreign key reference from other tables because you have designated it as an alternate key

The syntax for declaring the alternate MUST include explicit references

|||
Thanks for your help.

I am getting the following error:

"There are no primary or candidate keys in the referenced table 'precedence' that match the referencing column list in the foreign key 'FK_users_precedence'.

(Not to confuse you but I have made some naming changes to my tables - I am trying to sync up with the ISO-11179 rules. UserPrecedence is now 'precedence'. emailAddress is now email).

Below are the two tables in question:

DBO.users:

CREATE TABLE [dbo].[users](
[registerdate] [datetime] NOT NULL,
[password] [varchar](50) NOT NULL,
[role] [char](50) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[alternateemail] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[userid] [int] IDENTITY(1,1) NOT NULL,
[gender] [char](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[editdate] [datetime] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--

DBO.precedence

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]|||OK.

A little further along...

It seems I didn't create the UNIQE constraint on userid (since userid is a composite primary key and is,
therefore, UNIQUE, is it redundant to explicitly create a UNIQUE constraint on this column?). After I
did this, I ran the following code:

Code Snippet

ALTER TABLE users
ADD CONSTRAINT fk_users_precedence
FOREIGN KEY (userid)
REFERENCES precedence(userid)

and got the following error:

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_users_precedence". The conflict occurred in database "MyDB", table "dbo.precedence", column 'userid'."

here is my updated precedence CREATE script:

Code Snippet

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_userid] UNIQUE NONCLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

|||
OK. For anybody that is curious about this error and wants to see some possible solutions (if you
have a similar problem) the below thread provided a solution for me:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478256&SiteID=1

Thanks.

Foregein Key data voilation in merge replication

HI,
The situation is like this, merge replication is setup on server(Publisher),
and 4 clients machines and these are subscribers.
I have two table with primary key and foreign key relationship. Both the
tables have primary key with Uniqueidentifier column. First uniqueidnetifer
column is mapped to second table as Foregein key.
here in some situation inserts into primary key and foregein key tables
happyining in the sequence like insert first in Primary Key table, second in
Foregein Key table.
In some situations the process is reversing, like first inserting foregein
key table then primary key table. With this type of activity i am loosing
most important data in teh foregein key tabel. Suggest me in this how can i
proceed.
Regards
Satish
After you make some data inserts on publisher, merge replication is applying
those changes on subscriber, but these modifications can be applied in
different sequence than you originally perfomed on publisher. The common way
to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
foreign key constraints. When this flag is set - foreign key constraint is
not checked for replicated data. I suggest you to check Books Online for
more information.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Satish" <Satish@.discussions.microsoft.com> wrote in message
news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> HI,
> The situation is like this, merge replication is setup on
server(Publisher),
> and 4 clients machines and these are subscribers.
> I have two table with primary key and foreign key relationship. Both the
> tables have primary key with Uniqueidentifier column. First
uniqueidnetifer
> column is mapped to second table as Foregein key.
> here in some situation inserts into primary key and foregein key tables
> happyining in the sequence like insert first in Primary Key table, second
in
> Foregein Key table.
> In some situations the process is reversing, like first inserting foregein
> key table then primary key table. With this type of activity i am loosing
> most important data in teh foregein key tabel. Suggest me in this how can
i
> proceed.
>
> Regards
> Satish
|||Thank you for quick reply. Most of the wesites are telling to create Foreign
Key with NOT FOR REPLICATION option.
I have got one more question that, why merge replication is not inserting
the data in the sequence manner, first Primary key data and then Foreign key
data. Any specific reason in this.
Regards
Satish
"Kestutis Adomavicius" wrote:

> After you make some data inserts on publisher, merge replication is applying
> those changes on subscriber, but these modifications can be applied in
> different sequence than you originally perfomed on publisher. The common way
> to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
> foreign key constraints. When this flag is set - foreign key constraint is
> not checked for replicated data. I suggest you to check Books Online for
> more information.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Satish" <Satish@.discussions.microsoft.com> wrote in message
> news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> server(Publisher),
> uniqueidnetifer
> in
> i
>
>
|||Satish,
This is standard behaviour in SQL Server 2000 and is improved in SQL Server
2005.
Have a look at these articles for more details:
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Forecasting TimeSeries from Cube

I've saw many tutorials about using TimeSeries. But all of them using a table. But I'm using a cube to represent data. So I'm trying to build forecast from cube, but it doesn't so good as in could be.
I've got the same problem as desribed in Microsoft's tutoral Adventure work. So I need to forecast a series of sales.
The problem is that I can't create second key value, as it shown in tutorial. So I can't split good's sales. I have created dimentions for goods and for time. So cube's browser shows me very handsome view, but the problem with mining model still remains...
Please, help me! How can I solve this problem?
Can I create a separate table from cube to build forecast by this table?
Or I can solve this problem not using tables?
I posted a solution here which may help.sql

Forecasting in analysis tab cannot view

Hi I installed the add-in for excel data mining but when i try to select a table, in the tab of analyze, dont have any properties. please help me.

carlos of southamerica

Did you select a cell within a table?

Do you see "Table Tools" above the ribbon?

Do you see "Analyze" and "Design" ribbons? (they are usually the last 2 ribbons)

If all answers are yes, what do you see in the Analyze ribbon?

Forecast Model

Hi

I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.

Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.

Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this

Thanks

AY

You should probably do some upfront data preparation before you build your time series model. Do you want to do a daily sales forecast per product? In that case, you should aggregate sales figures per item at that level.

If you have data for multiple products, then you have a series for each one and each series needs to have values for all time slices present in the data. For example, if you have daily data, you need to have a sales figure for each product for each day and all the series need to begin/end at the same point. The error you're seeing is due to this issue. Specifying MISSING_VALUE_SUBSTITUTION will allow missing data points for a time slice across multiple series to be filled in with the specified value.

|||Thanks for the reply. Yes it is a Daily Sales Forecast that I am trying to build. I will try your suggestion

ForEachLoop task not behaving as expected

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 > script2 > script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

Do you have the tasks hooked together by precedence constraints? (The green arrows?)|||

Yes,

I think the problem is something else with my bat file.

Never mind

:-)

Thanks

ForEachLoop Container and Variables

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

Hi Scott,

We're all still learning SSIS.

It sounds like you're most of the way there.

There are a couple ways to approach this solution. The simplest way, from what I understand from your post, is to use placeholders and parameters in your Update/Insert statements. If you already have the Code and Supplier variables defined, you could perform an insert using an Execute SQL Task with something similar to the following code:

Code Snippet

INSERT INTO Vendors

(Code, Supplier)

VALUES(?, ?)

You could then supply Parameters:

Code Snippet

VariableName Direction DataType ParameterName ParameterSize

User::Code Input Int 0 -1

User::Supplier Input VarChar 1 -1

This would substitute the question marks in the SQL Statement property with the values contained in your variables.

Hope this helps,

Andy

|||

Scott,

Any special reason for not using a dataflow with a lookup transform to detect if the rows exists(update) or not (insert). That is by far a pretty common practice in these scenarios.

|||

Hi Rafael

Still new to this (and database stuff as a whole) and am going on someone elses advice!

I have looked at your suggestion and have got as far as the following:

OLEDB Source with a SQL select statement to return the data required

Look Up transform to look up the 2 columns from the Select against the destination table

After that I am a bit lost. I guess i have to add a OLEDB destination but do I do it to a table or a SQL Command?

thanks again

Scott

|||

I think you are on the right track. I would add an OLE DB Destination against the destination table.

Keep in mind you have to tweak the lookup to 'redirect' errors. Lookup will treat the no matches as errors; hence will be send to the error output of the component (red arrow). Then you have to connect the error output of the Lup to the input of the destination.

Now the updates; every row going to the green output of the L.up is an existing/to-updated row. Here you have 2 options; use an OLE DB Commnad to update the row in the destination table; or send those rows to an estiging table (yes a seconf OLE DB Destination) and then back in control flow use an Execute SQl task to do a 1 time update. The advantage of the second method is performance. the Update runs 1 time updating all the required rows. The First one will perform an update for every row passing trhough; wich depending on the volume of data can be performance killer; the good thing is that you don't need a second table.

This thread has some examples

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

ForEachLoop Container - How to Force Next Iteration -

How can I force a Next Iteration in a ForEach Loop container?

I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).

If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.

Is it possible to force "Next Interation' on the parent container?

Thanks - Covi

Not quite sure what you mean. In what circumstances do you want to 'force teh next iteration'?

-Jamie

sql

ForEachLoop and Object-Variable

Hi there!

I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).

So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).

The variable-mapping looks like that:

Mypackvar1 - Index 0

Mypackvar2 - Index 1

Mypackvar3 - Index 2

Mypackvar4 - Index 3

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Am i on the right way? Is it great bullshit what i am doing?

Thanks for any suggestion,

Torsten

Sounds like you're on the right track. What I do is create an ExecuteSQL task with the result set set to "Full result set". In the Result Set page I click Add, put 0 for the result name and pick an Object variable to put the result in.

In the For Each loop I make the collection a "Foreach ADO.Net Schema rowset enumerator".
In variable mappings I pick a variable with the same type as the column and put in the appropriate offset ( 0 through fieldcount-1).

It sounds like you're doing that or something very close. I'd double check the variable you're assigning to is the same type as the resultset column.

|||

Torsten_Katthoefer wrote:

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Have you stepped through with the debugger to make sure you're getting back the values you expect? Are you calling a stored procedure, or just executing SQL? How are you populating the recordset?

|||

Hmm, it works - a little bit...

One problem has been the datatype - in the db, the column is bigint, and the conversion to DTI8 makes some trouble, so i decided to use a an object as datatype (package scope), and first in the for-each-loop i started a script task like that (CRQ_ID is the variable with type DTI8, and CRQ_OBJ is the result from my query to set the enumerations):

Dim Message As String

Dts.Variables("v_CRQ_ID").Value = CType(Dts.Variables("v_CRQ_OBJ").Value, Int64)

Message = CStr(Dts.Variables("v_CRQ_OBJ").Value) + "-" + CStr(Dts.Variables("v_CRQ_ID").Value) + "-" + CStr(Dts.Variables("v_LGE").Value) + "-" + CStr(Dts.Variables("v_DDS").Value) + "-" + CStr(Dts.Variables("v_FIS_PERIODE").Value)

MsgBox(Message)

I get the message boxes a view times, and everytime CRQ_OBJ is equal CRQ_ID. Seems it works fine.

BUT: The next task is a sql task witch updates a few rows with the CRQ_ID as input parameter. But it doesn't matter on the new values. Could it be, that i've to use another way to set the package variable?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?

ForeachFile and ForeachItem

Since I installed SP1, I do not see ForeachFile and ForEachItem anymore as possible collections in the Foreach package, What went wrong? Thanks for any advice.

You may have to make sure there are selected under the SSIS Control flow items. Right Click on Control Flow task tab - select choose item... - select SSIS Control flow tab - you should see the foreachfile and foreachitem controls - make sure they are selected.

Carl

|||

thanks for your answer but I did check that

In the list, I only see the ForEach loop item, and that I checked.

It's when I use that control flow item that I do not get the choice for ForEachFile and ForeachItem in the Collections page.

|||

Still could not solve this (annoying) problem (need ForEach File)

Can somebody please help?

|||It sounds like your installation has been corrupted. You have the For and ForEach Shapes in the toolbox, but when you drag the ForEach shape over you are missing 2 of the values in the drop-down for the type of enumerator. Is this correct? If so you may need to do a reinstall.|||

thanks, I've installed SQL Standard edition this weekend (with lots of problems, had to reinstall windows, visual studio did not install) and since then I have the mentioned items.

just discovered another (very) big problem though, not one of my SSIS packages works, every time I want to edit the data flow, Visual studio crashes,

any experience with that?

|||

That's weird. No errors come up? It just crashes?

I had an issue with very large packages sometimes crashing. It didn't really crash the IDE, but I couldn't save any packages. I would get an Out of Memory error. That mysteriously disappeared though.

Do you have SP1?

|||

I copy my thread on that, works now, I had to delete all data sources and data source views, execute, edit in debug mode and stop debugging, very weird indeed.

Adress was different by package

I have CTP1 installed, did you install the Techn preview of SP2? Worth doing (and taking the risk)?

I used an evaluation version till last week.

Was working fine so I decided to buy a Standard License.

Installed during the weekend, I had many problems till I decided to reinstall completely Windows. I have installed SP1

Everything worked fine then, ONLY not one of my SSIS packages still works; I get following error when editing the data flow task

The thread 'Win32 Thread' (0x7e8) has exited with code 0 (0x0).

Unhandled exception at 0x54fc5e89 in devenv.exe: 0xC0000005: Access violation reading location 0x00000000.

Please help, this is a disaster otherwise

Seems to work if I delete datasources and data source views and execute first?