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

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

For XML Explicit

friends,
Below is the table "PCreditCardType"
I wanted to change the data of that table to the below mentioned XML format.
.
The solution should be through For XML Explicit or any SQL Operation...
Can you plz help me out'
Select * from PCreditCardType
CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
1 AMX American Express NULL
2 BCD BankCard NULL
3 DCB Diners Club NULL
4 DSC Discover NULL
5 ERC Eurocard NULL
6 JCB JCB NULL
7 MCD MasterCard NULL
8 VSA Visa NULL
<domainData name=" PCreditCardType " >
<cols>
<col name="CCTypeCode"/>
<col name="CCTypeName">
<col name="CCExpNumberOfYears">
</cols>
<rows>
<row> <![CDATA[AMX,American Express,NULL]]> </row>
<row> <![CDATA[BCD,BankCard,NULL]]> </row>
<row> <![CDATA[DCB,Diners Club,NULL]]> </row>
<row> <![CDATA[DSC,Discover,NULL]]> </row>
<row> <![CDATA[ERC,Eurocard,NULL]]> </row>
<row> <![CDATA[JCB,JCB,NULL]]> </row>
<row> <![CDATA[MCD,MasterCard,NULL]]> </row>
<row> <![CDATA[VSA,Visa,NULL]]> </row>
</rows>
</domainData>
Regards
Rao KRMYou can use the following query to dump XML rows section:
SELECT 1 as Tag,
NULL as Parent,
ISNULL(CCTypeCode, 'NULL') + ',' + ISNULL(CCTypeName, 'NULL') + ',' +
ISNULL(CCExpNumberOfYears, 'NULL') as [row!1!!cdata]
FROM PCreditCardType
FOR XML EXPLICIT
Then merge it with columns XML data in the application.
With best regards
Martin Rakhmanov
"For XML Explicit help wanted" <For XML Explicit help
wanted@.discussions.microsoft.com> wrote in message
news:73FCEF35-0CAE-4E51-B6DE-9A815BAF9DBA@.microsoft.com...
> friends,
> Below is the table "PCreditCardType"
> I wanted to change the data of that table to the below mentioned XML
format..
> The solution should be through For XML Explicit or any SQL Operation...
> Can you plz help me out'
> Select * from PCreditCardType
> CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
> 1 AMX American Express NULL
> 2 BCD BankCard NULL
> 3 DCB Diners Club NULL
> 4 DSC Discover NULL
> 5 ERC Eurocard NULL
> 6 JCB JCB NULL
> 7 MCD MasterCard NULL
> 8 VSA Visa NULL
>
> <domainData name=" PCreditCardType " >
> <cols>
> <col name="CCTypeCode"/>
> <col name="CCTypeName">
> <col name="CCExpNumberOfYears">
> </cols>
> <rows>
> <row> <![CDATA[AMX,American Express,NULL]]> </row>
> <row> <![CDATA[BCD,BankCard,NULL]]> </row>
> <row> <![CDATA[DCB,Diners Club,NULL]]> </row>
> <row> <![CDATA[DSC,Discover,NULL]]> </row>
> <row> <![CDATA[ERC,Eurocard,NULL]]> </row>
> <row> <![CDATA[JCB,JCB,NULL]]> </row>
> <row> <![CDATA[MCD,MasterCard,NULL]]> </row>
> <row> <![CDATA[VSA,Visa,NULL]]> </row>
> </rows>
> </domainData>
>
> Regards
> Rao KRM

For XML Explicit

friends,
Below is the table "PCreditCardType"
I wanted to change the data of that table to the below mentioned XML format..
The solution should be through For XML Explicit or any SQL Operation...
Can you plz help me out?
Select * from PCreditCardType
CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
1 AMX American Express NULL
2 BCD BankCard NULL
3 DCB Diners Club NULL
4 DSC Discover NULL
5 ERC Eurocard NULL
6 JCB JCB NULL
7 MCD MasterCard NULL
8 VSA Visa NULL
<domainData name=" PCreditCardType " >
<cols>
<col name="CCTypeCode"/>
<col name="CCTypeName">
<col name="CCExpNumberOfYears">
</cols>
<rows>
<row> <![CDATA[AMX,American Express,NULL]]> </row>
<row> <![CDATA[BCD,BankCard,NULL]]> </row>
<row> <![CDATA[DCB,Diners Club,NULL]]> </row>
<row> <![CDATA[DSC,Discover,NULL]]> </row>
<row> <![CDATA[ERC,Eurocard,NULL]]> </row>
<row> <![CDATA[JCB,JCB,NULL]]> </row>
<row> <![CDATA[MCD,MasterCard,NULL]]> </row>
<row> <![CDATA[VSA,Visa,NULL]]> </row>
</rows>
</domainData>
Regards
Rao KRM
You can use the following query to dump XML rows section:
SELECT 1 as Tag,
NULL as Parent,
ISNULL(CCTypeCode, 'NULL') + ',' + ISNULL(CCTypeName, 'NULL') + ',' +
ISNULL(CCExpNumberOfYears, 'NULL') as [row!1!!cdata]
FROM PCreditCardType
FOR XML EXPLICIT
Then merge it with columns XML data in the application.
With best regards
Martin Rakhmanov
"For XML Explicit help wanted" <For XML Explicit help
wanted@.discussions.microsoft.com> wrote in message
news:73FCEF35-0CAE-4E51-B6DE-9A815BAF9DBA@.microsoft.com...
> friends,
> Below is the table "PCreditCardType"
> I wanted to change the data of that table to the below mentioned XML
format..
> The solution should be through For XML Explicit or any SQL Operation...
> Can you plz help me out?
> Select * from PCreditCardType
> CCTypeId CCTypeCode CCTypeName CCExpNumberOfYears
> 1 AMX American Express NULL
> 2 BCD BankCard NULL
> 3 DCB Diners Club NULL
> 4 DSC Discover NULL
> 5 ERC Eurocard NULL
> 6 JCB JCB NULL
> 7 MCD MasterCard NULL
> 8 VSA Visa NULL
>
> <domainData name=" PCreditCardType " >
> <cols>
> <col name="CCTypeCode"/>
> <col name="CCTypeName">
> <col name="CCExpNumberOfYears">
> </cols>
> <rows>
> <row> <![CDATA[AMX,American Express,NULL]]> </row>
> <row> <![CDATA[BCD,BankCard,NULL]]> </row>
> <row> <![CDATA[DCB,Diners Club,NULL]]> </row>
> <row> <![CDATA[DSC,Discover,NULL]]> </row>
> <row> <![CDATA[ERC,Eurocard,NULL]]> </row>
> <row> <![CDATA[JCB,JCB,NULL]]> </row>
> <row> <![CDATA[MCD,MasterCard,NULL]]> </row>
> <row> <![CDATA[VSA,Visa,NULL]]> </row>
> </rows>
> </domainData>
>
> Regards
> Rao KRM

For Loop help

Dear Friends,
I am getting problem in the following statement.
Please help.
ALTER PROCEDURE dbo.Defaulters
(@.Stdt smalldatetime)
AS
declare @.CNT int
select @.cnt = 1
FOR @.cnt <= 7
Select dbo.Employee_Master.Employee_Name
@.stdt =@.stdt +1
@.cnt = @.cnt + 1
Next
Best regards
SharadIs this a CLR stored procedure written in VB.NET?
"Shailesh" wrote:

> Dear Friends,
> I am getting problem in the following statement.
> Please help.
> ALTER PROCEDURE dbo.Defaulters
> (@.Stdt smalldatetime)
> AS
> declare @.CNT int
> select @.cnt = 1
> FOR @.cnt <= 7
>
> Select dbo.Employee_Master.Employee_Name
> @.stdt =@.stdt +1
> @.cnt = @.cnt + 1
> Next
>
> Best regards
> Sharad
>
>|||Shailesh (shailesh_gothal@.hotmail.com) writes:
> I am getting problem in the following statement.
> Please help.
> ALTER PROCEDURE dbo.Defaulters
> (@.Stdt smalldatetime) AS
> declare @.CNT int
> select @.cnt = 1
> FOR @.cnt <= 7
>
> Select dbo.Employee_Master.Employee_Name
> @.stdt =@.stdt +1
> @.cnt = @.cnt + 1
> Next
I'm sorry, but I'm afraid that a newsgroup is not the right venue for you
to help. A newsgroup is good when you have a specific question. But it's
not a very efficient place to learn the subject from the bottom.
If all you want help with is to write a control loop, I refer you to
the topic "Control-of-Flow Language" in the Transact-SQL Reference in
Books Online. The reason I give you "read-the-manual" answer, is that I
think that are better served by learing to use the manual. While it may
be with some resistence in the beginning, it pays off in the long run.
Now, even with the proper syntax the procedure would not make much sense.
You would select all rows in the table Employee_Name seven times. (Provided
that you have a database called dbo, that is!) This does not look like a
useful operation.
I would suggest that you should find some SQL Server training locally, our
get a book like Richard Waymires "SQL Server 2000 in 21 days". Playing
around and inventing your own syntax will only be frustrating and
ineffecient.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

For Each Loop Script

hi friends,

i have choosen the for each loop in that i was selected the all jpgs(d:\images ) directory

when we get the *.jpgs

in the for each loop i am getting the all jpgs (one by one in a local variable fname)

i have choosen the script task in the for each loop

now when we displaying the jpgs through the message box in script

msgbox(fname)

i am getting the full name with path like

"d:\iimages\aaa.jpg"

I WANT TO BE

===============================

FNAME FILE PATH

===============================

aa.JPG "d:\iimages\aaa.jpg"

===============================

?

i need the file name only

regards,

koti

A .Net question really, see the System.IO.Path.GetFilename method which can be used in your VB.Net code.

FOR EACH LOOP

HI FRIENDS

WHAT IS THE USE OF FOREACH LOOP AND GIVE ME THE ONE EXAMPLE

REGARDS

KOTI

It loop over whatever the enumerator is designed to target. Have you bothered to look at or attempt to read the documentation? Is this your homework?

Foreach Loop Container
(http://msdn2.microsoft.com/en-us/library/dd6cc2ba-631f-4adf-89dc-29ef449c6933.aspx)