how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks!
ibiza
Try the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan
|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvan

how to get this resultset...

Hi,

I have 3 tables as follow :

Kanji :
kanji_id
...

References :
ref_id
...

KanjiRefs
kref_idkanji
kref_idref
kref_value
...

So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...

I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?

2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL

I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3

however, this obviously does not include kanjis having no reference
#3...

any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):

SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)

SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji

Razvan|||wow! It all works :P

many thanks!

Razvan Socol wrote:

Quote:

Originally Posted by

Try the following (untested):
>
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
>
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
>
Razvan

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvansql

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks!
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvan

how to get the status of update sucessing?

Dear Experts,

I have a stored procudure which can be successful called from application and update against tables, the question is how do I get the status which can indicate how many records were updated or no any record was updated in the action?

Thanks in advance.

Hi,

@.@.ERROR returns the error number for the last T-SQL statement executed while
@.@.ROWCOUNT returns the number of rows affected by the last SQL statement.

How to get the last item added

Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?

TABLE Sys_Con
SystemID int
ContactID int

Table Contact
ContactID int Identity
ContactName
...

Table System
SystemID
...After your insert

SELECT @.@.IDENTITY AS 'Identity'

Here is the resource:@.@.IDENTITY

hope this helps,
sivilian|||Thanks for your reply,
how do I get the identity back from my insert sp of my contact? I dont seem to be able to make this work. I get the error: "Procedure 'Add_Cl_Contact' expects parameter '@.ContactId', which was not supplied." on the line "iContactID = cmdSelect.Parameters("ContactId").Value"

Here is what I have up to now:

ALTER procedure dbo.Add_Cl_Contact
(
@.ContactNamenvarchar(75),
@.Departmentnvarchar (50)=null,
@.Titlenvarchar(50)=null,
@.Phone1char(20)=null,
@.Phone2char(20)=null,
@.Phone3char(20)=null,
@.Ext1char(10)=null,
@.Ext2char(10)=null,
@.Ext3char(10)=null,
@.Faxnvarchar(50)=null,
@.Emailnvarchar(50)=null,
@.ContactIdintOUTPUT
)
AS
insert into Cl_Contacts (
ContactName,
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
Email
)
values (
@.ContactName,
@.Department,
@.Title,
@.Phone1,
@.Phone2,
@.Phone3,
@.Ext1,
@.Ext2,
@.Ext3,
@.Fax,
@.Email
)
Select @.ContactId = @.@.IDENTITY
 '******************************************************************
 '******************************************************************
 Public Function Add_Cl_Contact(ByVal strContactName As String, _
 ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _
 ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _
 ByVal strPhone3 As String, ByVal strTitle As String, ByRef iContactID As Integer, ByRef strError As String) As Boolean
 '******************************************************************
 Dim bSuccess As Boolean = True
 Dim connect As New SqlConnection(strConnection)
 Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect)
 Dim paramReturnValue As SqlParameter
 cmdSelect.CommandType = CommandType.StoredProcedure
 'PARAM
 cmdSelect.Parameters.Add("@.ContactName", strContactName)
 cmdSelect.Parameters.Add("@.Department", strDep)
 cmdSelect.Parameters.Add("@.Title", strTitle)
 cmdSelect.Parameters.Add("@.Phone1", strPhone1)
 cmdSelect.Parameters.Add("@.Phone2", strPhone2)
 cmdSelect.Parameters.Add("@.Phone3", strPhone3)
 cmdSelect.Parameters.Add("@.Ext1", strExt1)
 cmdSelect.Parameters.Add("@.Ext2", strExt2)
 cmdSelect.Parameters.Add("@.Ext3", strExt3)
 cmdSelect.Parameters.Add("@.Fax", strFax)
 cmdSelect.Parameters.Add("@.Email", strEmail)
 paramReturnValue = cmdSelect.Parameters.Add("ContactId", SqlDbType.Int)
 paramReturnValue.Direction = ParameterDirection.ReturnValue
 Try
 connect.Open()
 cmdSelect.ExecuteNonQuery()
 iContactID = cmdSelect.Parameters("ContactId").Value
 connect.Close()
 Catch ex As Exception
 bSuccess = False
 strError = ex.Message
 Finally
 If connect.State = ConnectionState.Open Then
 connect.Close()
 End If
 End Try
 Return bSuccess
 End Function

|||I'd recommend using SCOPE_IDENTITY. check docs for more info about SCOPE_IDENTIY and @.@.IDENTITy..

insert into Cl_Contacts (
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
Email
)
values (
@.Department,
@.Title,
@.Phone1,
@.Phone2,
@.Phone3,
@.Ext1,
@.Ext2,
@.Ext3,
@.Fax,
@.Email
)
SELECT @.contactid = SCOPE_IDENTITY()

hth

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

How to get the count of source records doing UPDATE with joined tables.

I have an sql like this in my stored procedure.
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
So, COL1 of T1 table is modified when T1 finds matching records in T2
table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
there are multiple matching records from T2. I want to make T1 table
be updated when there is exactly one matching record in T2.
Of course I can check the count of the matching records in T2 before
doing the above but T2 is actually from openquery interface(so dynamic
sql) to a remote server and I don't know exactly how I can get a
cursor with a dynamic sql.(I guess I should search this soon.)
If there is a way to find out the count of matching source records for
the above sql, it will help me a lot. Thanks..I think this should work
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
And (Select Count(*) From T2 Where T2.COL2=T1.COL2)=1
Dmitriy
"Yi, Dong-ryon" <feeva@.hanmail.net> wrote in message
news:bf8bb96c.0503131958.770f80ea@.posting.google.com...
>I have an sql like this in my stored procedure.
> UPDATE T1
> SET COL1 = T2.COL1
> FROM T1, T2
> WHERE T1.COL2=T2.COL2
> So, COL1 of T1 table is modified when T1 finds matching records in T2
> table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
> there are multiple matching records from T2. I want to make T1 table
> be updated when there is exactly one matching record in T2.
> Of course I can check the count of the matching records in T2 before
> doing the above but T2 is actually from openquery interface(so dynamic
> sql) to a remote server and I don't know exactly how I can get a
> cursor with a dynamic sql.(I guess I should search this soon.)
> If there is a way to find out the count of matching source records for
> the above sql, it will help me a lot. Thanks..|||On 13 Mar 2005 19:58:20 -0800, Yi, Dong-ryon wrote:

>I have an sql like this in my stored procedure.
>UPDATE T1
>SET COL1 = T2.COL1
>FROM T1, T2
>WHERE T1.COL2=T2.COL2
>So, COL1 of T1 table is modified when T1 finds matching records in T2
>table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
>there are multiple matching records from T2. I want to make T1 table
>be updated when there is exactly one matching record in T2.
>Of course I can check the count of the matching records in T2 before
>doing the above but T2 is actually from openquery interface(so dynamic
>sql) to a remote server and I don't know exactly how I can get a
>cursor with a dynamic sql.(I guess I should search this soon.)
>If there is a way to find out the count of matching source records for
>the above sql, it will help me a lot. Thanks..
Hi Yi,
As an alternative to the suggestion made by Dmitriy, here's a version
that refers to the T2 table in only one place:
UPDATE T1
SET Col1 = T2a.Col1
FROM T1
INNER JOIN (SELECT Col2, MIN(Col1) AS Col1
FROM T2
GROUP BY Col2
HAVING COUNT(*) = 1) AS T2a
ON T2a.Col2 = T1.Col2
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to get the count difference between two tables

Hi,
I need to calcualte the count difference between two tables. I will
apply the method to 6 count comparisons. The following is my sample
code, which did not work. Could anyone help me out? Thanks!
create table one
(x float)
insert into one
values(1)
insert into one
values(2)
create table two
(x float)
insert into two
values(1)
insert into two
values(2)
insert into two
values(3)
declare @.one int, @.two int, @.diff int
set nocount on
select count(*) from one
set @.one=@.@.rowcount
select count(*) from two
set @.two=@.@.rowcount
set @.diff=(@.two-@.one)
print @.diff
Thanks a lot!
MikeMichael -
Do this instead:
declare @.one int, @.two int, @.diff int
set nocount on
select @.one=count(*) from one
select @.two=count(*) from two
set @.diff=(@.two-@.one)
print @.diff
@.@.rowcount returns the number of rows returned by a query. So "Select
count(*) from any_table_here" will always have an @.@.rowcount = 1, because
there is only 1 value returned from a "Select count(*)" query.|||On Feb 8, 2:20 pm, apf <a...@.discussions.microsoft.com> wrote:
> Michael -
> Do this instead:
> declare @.one int, @.two int, @.diff int
> set nocount on
> select @.one=count(*) from one
> select @.two=count(*) from two
> set @.diff=(@.two-@.one)
> print @.diff
> @.@.rowcount returns the number of rows returned by a query. So "Select
> count(*) from any_table_here" will always have an @.@.rowcount = 1, because
> there is only 1 value returned from a "Select count(*)" query.
Hi,
Thanks for your help! It works perfect. Could you tell me when the
output goes to GRID or when the output goes to MESSAGE? Because I used
PRINT statement, the output goes to different places in different
runs.
Thanks,
Mike|||I'm not completely sure what you're asking.
When you use Query Analyzer, there is a drop down in the top navigation that
lets you choose whether the results are returned in a grid, in text, or
output to a file.sql

How to get the count difference between two tables

Hi,
I need to calcualte the count difference between two tables. I will
apply the method to 6 count comparisons. The following is my sample
code, which did not work. Could anyone help me out? Thanks!
create table one
(x float)
insert into one
values(1)
insert into one
values(2)
create table two
(x float)
insert into two
values(1)
insert into two
values(2)
insert into two
values(3)
declare @.one int, @.two int, @.diff int
set nocount on
select count(*) from one
set @.one=@.@.rowcount
select count(*) from two
set @.two=@.@.rowcount
set @.diff=(@.two-@.one)
print @.diff
Thanks a lot!
MikeMichael -
Do this instead:
declare @.one int, @.two int, @.diff int
set nocount on
select @.one=count(*) from one
select @.two=count(*) from two
set @.diff=(@.two-@.one)
print @.diff
@.@.rowcount returns the number of rows returned by a query. So "Select
count(*) from any_table_here" will always have an @.@.rowcount = 1, because
there is only 1 value returned from a "Select count(*)" query.|||On Feb 8, 2:20 pm, apf <a...@.discussions.microsoft.com> wrote:
> Michael -
> Do this instead:
> declare @.one int, @.two int, @.diff int
> set nocount on
> select @.one=count(*) from one
> select @.two=count(*) from two
> set @.diff=(@.two-@.one)
> print @.diff
> @.@.rowcount returns the number of rows returned by a query. So "Select
> count(*) from any_table_here" will always have an @.@.rowcount = 1, because
> there is only 1 value returned from a "Select count(*)" query.
Hi,
Thanks for your help! It works perfect. Could you tell me when the
output goes to GRID or when the output goes to MESSAGE? Because I used
PRINT statement, the output goes to different places in different
runs.
Thanks,
Mike|||I'm not completely sure what you're asking.
When you use Query Analyzer, there is a drop down in the top navigation that
lets you choose whether the results are returned in a grid, in text, or
output to a file.

How to get the count difference between two tables

Hi,
I need to calcualte the count difference between two tables. I will
apply the method to 6 count comparisons. The following is my sample
code, which did not work. Could anyone help me out? Thanks!
create table one
(x float)
insert into one
values(1)
insert into one
values(2)
create table two
(x float)
insert into two
values(1)
insert into two
values(2)
insert into two
values(3)
declare @.one int, @.two int, @.diff int
set nocount on
select count(*) from one
set @.one=@.@.rowcount
select count(*) from two
set @.two=@.@.rowcount
set @.diff=(@.two-@.one)
print @.diff
Thanks a lot!
Mike
Michael -
Do this instead:
declare @.one int, @.two int, @.diff int
set nocount on
select @.one=count(*) from one
select @.two=count(*) from two
set @.diff=(@.two-@.one)
print @.diff
@.@.rowcount returns the number of rows returned by a query. So "Select
count(*) from any_table_here" will always have an @.@.rowcount = 1, because
there is only 1 value returned from a "Select count(*)" query.
|||On Feb 8, 2:20 pm, apf <a...@.discussions.microsoft.com> wrote:
> Michael -
> Do this instead:
> declare @.one int, @.two int, @.diff int
> set nocount on
> select @.one=count(*) from one
> select @.two=count(*) from two
> set @.diff=(@.two-@.one)
> print @.diff
> @.@.rowcount returns the number of rows returned by a query. So "Select
> count(*) from any_table_here" will always have an @.@.rowcount = 1, because
> there is only 1 value returned from a "Select count(*)" query.
Hi,
Thanks for your help! It works perfect. Could you tell me when the
output goes to GRID or when the output goes to MESSAGE? Because I used
PRINT statement, the output goes to different places in different
runs.
Thanks,
Mike
|||I'm not completely sure what you're asking.
When you use Query Analyzer, there is a drop down in the top navigation that
lets you choose whether the results are returned in a grid, in text, or
output to a file.

how to get tables involved in constraint

Hi,
The following request select a constraint from TABLE_CONSTRAINT with
the name specified in the where clause:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
constraint_name = 'FK__51OtherParties__51Claims'

It returns:

http://graphicsxp.free.fr/constraint.JPG

So I have TABLE_NAME that correspond to the first table involved in the
constraint, but how do I get 51Claims ??

Thank youHi, Sam

You can use something like this:

SELECT TC.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON TC.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME
AND TC.CONSTRAINT_SCHEMA=RC.UNIQUE_CONSTRAINT_SCHEMA
WHERE RC.CONSTRAINT_NAME = 'FK__51OtherParties__51Claims'

Razvan|||Hi Razvan,

Many thanks, it works !

how to get tables in db?

How do I get the list of user tables in a database?

help plz..

There are three ways to get the list of user tables in a database. The first method is by querying the [dbo].[sysobjects] system table. The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects]

WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables]

WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @.table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @.table_type = "'TABLE'|||

in sql 2005 you have a catalog view called sys.tables

select *From sys.tables

From BOL : Returns a row for each table object, currently only with sys.objects.type = U.

Madhu

|||thx for a reply... sys.tables is a gud way|||

That's all good, but suppose I want to see all databases on a server that contain a certain table. I do that to find a candidate database , on servers where there are user databases that I don't want to SELECT from.

What system table shows me all the databases that have a specific table name?

The code below will work, but is there a better way?

CREATE TABLE ##MyTable(MyDB varchar(80));

EXEC sp_msforeachdb 'INSERT ##MyTable SELECT table_catalog

FROM ?.information_schema.tables

WHERE table_name like ''account%''';

SELECT * from ##MyTable;

how to get tables in db?

How do I get the list of user tables in a database?

help plz..

There are three ways to get the list of user tables in a database. The first method is by querying the [dbo].[sysobjects] system table. The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables] WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @.table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @.table_type = "'TABLE'|||

in sql 2005 you have a catalog view called sys.tables

select *From sys.tables

From BOL : Returns a row for each table object, currently only with sys.objects.type = U.

Madhu

|||thx for a reply... sys.tables is a gud way|||

That's all good, but suppose I want to see all databases on a server that contain a certain table. I do that to find a candidate database , on servers where there are user databases that I don't want to SELECT from.

What system table shows me all the databases that have a specific table name?

The code below will work, but is there a better way?

CREATE TABLE ##MyTable(MyDB varchar(80));

EXEC sp_msforeachdb 'INSERT ##MyTable SELECT table_catalog

FROM ?.information_schema.tables

WHERE table_name like ''account%''';

SELECT * from ##MyTable;

How to get rowid of uncommitted record

I have two tables - header, detail. HeaderID appears in both, is an
auto-incrementing rowID in the header table, and required also in the detail
table. It's a one-to-many relationship from the header table to the detail
table.
The code I have writing to these tables really needs to write the detail
first, and then summariize and write the header infomation, however I can't
write the detail until I get the HeaderID by writing a header record.
So...If I write a record to the header table as a placeholder, how can I
get the HeaderID of that record while I am writing it, to use it in the
detail table (or for that matter reserve a HeaderID to be written later). I
t
is the only unique ID in the header table, and I don't have anything else
that I can uniquely use to go back and get the record just written to get th
e
header table. Here's the further challenge - all of these are uncomitted
transactions until both detail and header records for the transaction have
been written, at which point I will commit them.
Tell me there is some magic way to get the id of a record I am about to
write, am writing, or have written. Help!
Thanks!Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:

> I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail table. It's a one-to-many relationship from the header table to
> the detail table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't write the detail until I get the HeaderID by writing a header
> record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It is the only unique ID in the header table, and I don't have anything
> else that I can uniquely use to go back and get the record just written to
> get the header table. Here's the further challenge - all of these are
> uncomitted transactions until both detail and header records for the
> transaction have been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!
What are you using to create the records? You should have no trouble getting
the rowid of the inserted header record. For instance, I myself use ADO to
create a header record in a table inside a transaction, and pull back the
identity value exactly the same way as if it wasn't in a transaction - I
just read it from the column. I then create the detail lines to go with it,
and at the end commit the transaction. If I have to rollback, both the
header and the detail lines get rolled back. There should be no need to be
doing anything special - this is exactly the same code I was using before I
added a transaction (which was simply of case of adding a BeginTrans ADO
Connection method call before the header record was created, and a
CommitTrans method call at the end).
Dan|||Look at scope_identity() in Books Online.
It doesn't matter if the transaction is not committed.
So, after you do your first insert:
declare @.headerID INT
insert...
set @.headerID = scope_identity()
Don't forget to handle errors.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail
> table. It's a one-to-many relationship from the header table to the
> detail
> table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't
> write the detail until I get the HeaderID by writing a header record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It
> is the only unique ID in the header table, and I don't have anything else
> that I can uniquely use to go back and get the record just written to get
> the
> header table. Here's the further challenge - all of these are uncomitted
> transactions until both detail and header records for the transaction have
> been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!|||Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
command. the HeaderID is an identity colum increments by the DB. I've just
never had to do this beofre so this is new terrirotry. I guess my question
is how either when I am executing the command or after I execute it i can ge
t
the assigned headerID. Understand that this DB will process millions of
trx/day, so I can't just write a record and go back and get the max id as
something/someone else could have easly incremented it in the meantime. How
are you going back to get the id regardless of it being within a transaction
?
Thanks!
"Daniel Crichton" wrote:

> Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
>
>
> What are you using to create the records? You should have no trouble getti
ng
> the rowid of the inserted header record. For instance, I myself use ADO to
> create a header record in a table inside a transaction, and pull back the
> identity value exactly the same way as if it wasn't in a transaction - I
> just read it from the column. I then create the detail lines to go with it
,
> and at the end commit the transaction. If I have to rollback, both the
> header and the detail lines get rolled back. There should be no need to be
> doing anything special - this is exactly the same code I was using before
I
> added a transaction (which was simply of case of adding a BeginTrans ADO
> Connection method call before the header record was created, and a
> CommitTrans method call at the end).
> Dan
>
>|||Thanks!
"Raymond D'Anjou" wrote:

> Look at scope_identity() in Books Online.
> It doesn't matter if the transaction is not committed.
> So, after you do your first insert:
> declare @.headerID INT
> insert...
> set @.headerID = scope_identity()
> Don't forget to handle errors.
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>
>|||Matt wrote on Fri, 2 Dec 2005 08:31:07 -0800:

> Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
> command. the HeaderID is an identity colum increments by the DB. I've
> just never had to do this beofre so this is new terrirotry. I guess my
> question is how either when I am executing the command or after I execute
> it i can get the assigned headerID. Understand that this DB will process
> millions of trx/day, so I can't just write a record and go back and get
> the max id as something/someone else could have easly incremented it in
> the meantime. How are you going back to get the id regardless of it being
> within a transaction? Thanks!
In ADO it's pretty simple - you just use a recordset Keyset cursor type with
a server-side cursor, write the values in to the new row, and after running
the .Update method of the recordset read the value of the identity field.
That's it. No messing around with making other calls back to SQL Server, or
trying to determine the row id in some other arcane way.
Dan

How to get rid of the GUID?

If I decide I don't want to use Queued Updating Subscribers, how do I get the replication guid's added off the tables in my database? A restore?
I am testing all my options, and have completed testing the use of queued updating, and want to move on to another option, but the guid's are causing me some problems, and I want to know if there is a sp to get rid of them or do I just need to restore my database?
Thanx!
JLS,
there is no sp to remove these columns. Usually there is no issue as TSQL is
writte using column names rather than columns by position. However, if you
have this sort of statement:
insert into tableX
select * from tableY
Then admittedly there will be issues.
You ask about restoring the database but I'm not too sure which one you are
referring to. If it is the production database and you are using replication
for failover, it would seem to be redundant?
Regards,
Paul Ibison

How To Get Rid Of Sql Log

Hi,
I have a Data base on Sql Server 2000, sp3a. Database is used to to build summary tables to show reports on web. There are many sub systems, which prepare summarized tables for faster web page serving. Every thing is working fine.

The problem is sql server log gets full. In one given day it could go up to 18 GB. I know, if I back it up it gets truncated. Is there a way, I can stop sql server not to log, as it is not needed?

Can I set up Sql server to automatically truncate it. I do have auto shrink options checked, but log file still keeps growing.

Any idea, what is going on here.
Thanks,
Mashro::Is there a way, I can stop sql server not to log, as it is not needed?

You err, it IS needed.

Read up in the documentation what the log is used for. It is needed.|||Back it up regularly to keep it from growing out of control. You can't eliminate the log. It's a pretty key piece of the puzzle. If you have the db set to simple recovery, it should truncate the log on each checkpoint. Probably no need to use a full recovery model on a 'derived' reporting database like you seem to have.

How to get rid of ONLY ONE publications?

I have got a Publisher with 5 different publications (different tables on each). One of them publicates only one table. I delete that one and then create a new one with the same table and a new table. My final purpose is adding a new table to an existing publication. When I run the merge agent, I got the error "cannot drop one or more tables because they are being used by another publications". I cannont use the sp sp_removedbreplication 'database_name' because I do not want to get rid of all of them, just "remake" one of the. The suscriber is the same for all and all of them are merge.
Thanks in advanced for your ideas.Use sp_droppublication and refer to books online for more information.

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL : “SELECT * FROM fn_my_permissions('TableName', 'OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL :“SELECT*FROM fn_my_permissions('TableName','OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server