ODBC Creation from commandline

I am trying to get an ODBC connection created from a commandline, I
have created the ODBC connection, exported the registry key, and have
tried doing regedit /s "regkey", problem is after I do that it never
shows us in the ODBC manager, it shows up in the registry under
ODBC.INI\Source, but I dont see it in the manager, any ideas?
"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
>I am trying to get an ODBC connection created from a commandline, I
> have created the ODBC connection, exported the registry key, and have
> tried doing regedit /s "regkey", problem is after I do that it never
> shows us in the ODBC manager, it shows up in the registry under
> ODBC.INI\Source, but I dont see it in the manager, any ideas?
Are you adding a value to the ODBC Data Sources key as well as the database
connection key values?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95
|||dbmonitor wrote:

> "Duane Haas" <techsupport@.suduhaas.com> wrote in message
> news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Are you adding a value to the ODBC Data Sources key as well as the
> database connection key values?
Yes, it creates the key, and all the values. I just export it from
another machine that already has the connection.
|||"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUMF6ZndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> dbmonitor wrote:
>
> Yes, it creates the key, and all the values. I just export it from
> another machine that already has the connection.
Are you able to post the reg settings you are adding?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95

ODBC Creation from commandline

I am trying to get an ODBC connection created from a commandline, I
have created the ODBC connection, exported the registry key, and have
tried doing regedit /s "regkey", problem is after I do that it never
shows us in the ODBC manager, it shows up in the registry under
ODBC.INI\Source, but I dont see it in the manager, any ideas?"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
>I am trying to get an ODBC connection created from a commandline, I
> have created the ODBC connection, exported the registry key, and have
> tried doing regedit /s "regkey", problem is after I do that it never
> shows us in the ODBC manager, it shows up in the registry under
> ODBC.INI\Source, but I dont see it in the manager, any ideas?
Are you adding a value to the ODBC Data Sources key as well as the database
connection key values?
--
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95|||dbmonitor wrote:

> "Duane Haas" <techsupport@.suduhaas.com> wrote in message
> news:uUTjiBQcFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Are you adding a value to the ODBC Data Sources key as well as the
> database connection key values?
Yes, it creates the key, and all the values. I just export it from
another machine that already has the connection.|||"Duane Haas" <techsupport@.suduhaas.com> wrote in message
news:uUMF6ZndFHA.1456@.TK2MSFTNGP15.phx.gbl...
> dbmonitor wrote:
>
> Yes, it creates the key, and all the values. I just export it from
> another machine that already has the connection.
Are you able to post the reg settings you are adding?
David Rowland
DBMonitor version 1.2 out now! http://dbmonitor.tripod.com
Email Alerts, logging, performance stats, process information!
Only $49.95

ODBC ACCESS

I have a VIEW SQL connected to an other database. How I can connect
this VIEWS in ACCESS inserting one key in runtime?
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/"domed" <darrigod@.-NOSPAM-tiscali.it> wrote in message
news:eo7FUPK9DHA.2480@.TK2MSFTNGP12.phx.gbl...
> I have a VIEW SQL connected to an other database. How I can connect
> this VIEWS in ACCESS inserting one key in runtime?
>
I'm not sure I understand your question... Any time you update data
programmatically, I'd recommend using a stored procedure, this you could
code to update tables in other database(s) if required.
Steve

obtaining the license key

Hello:
Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
of ours as this client's soon-to-be old server has run out of disk space.
I hope to obtain the SQL license key in a timely manner from their IT
department. In case I cannot get this key as quickly as I'd like, though, is
there a way to somehow get this key by reviewing files in the SQL folder on
the old server's hard drive?
Thanks, for your time!
childofthe1980s
http://www.nirsoft.net/utils/product_cd_key_viewer.html
Regards,
Trevor Benedict
MCSD
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:B9D5700B-6E99-44B3-BEED-16E2B7C3F200@.microsoft.com...
> Hello:
> Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
> of ours as this client's soon-to-be old server has run out of disk space.
> I hope to obtain the SQL license key in a timely manner from their IT
> department. In case I cannot get this key as quickly as I'd like, though,
> is
> there a way to somehow get this key by reviewing files in the SQL folder
> on
> the old server's hard drive?
> Thanks, for your time!
> childofthe1980s

obtaining the license key

Hello:
Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
of ours as this client's soon-to-be old server has run out of disk space.
I hope to obtain the SQL license key in a timely manner from their IT
department. In case I cannot get this key as quickly as I'd like, though, is
there a way to somehow get this key by reviewing files in the SQL folder on
the old server's hard drive?
Thanks, for your time!
childofthe1980shttp://www.nirsoft.net/utils/product_cd_key_viewer.html
Regards,
Trevor Benedict
MCSD
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:B9D5700B-6E99-44B3-BEED-16E2B7C3F200@.microsoft.com...
> Hello:
> Tomorrow AM, I am going to reinstall SQL 2000 on a new server for a client
> of ours as this client's soon-to-be old server has run out of disk space.
> I hope to obtain the SQL license key in a timely manner from their IT
> department. In case I cannot get this key as quickly as I'd like, though,
> is
> there a way to somehow get this key by reviewing files in the SQL folder
> on
> the old server's hard drive?
> Thanks, for your time!
> childofthe1980s

Obtaining the latest record by date

Hi,

I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.

In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:

SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC

The query is part of a larger query where I obtain more data from other tables.

The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.

My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!

You're absolutely fine to use TOP in the way that you have done.

The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.

Chris

|||

Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.

Migrant

Obtaining Primary and Foreign Key information from a table

Hi,
I would like to query the system tables in order to obtain all the Primary
and Foreign keys for any particular table.
I have tried using the syscolumns.colstat field but it only outputs zero. I
understand it should output 1 if the column is a primary key.
Also I do not know I do not know how to obtain Foreign key info on a table.
All help is much appreciated.
Kind regards,
Polly AnnaWhat version of SQL Server are you using?
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> Hi,
> I would like to query the system tables in order to obtain all the Primary
> and Foreign keys for any particular table.
> I have tried using the syscolumns.colstat field but it only outputs zero.
> I
> understand it should output 1 if the column is a primary key.
> Also I do not know I do not know how to obtain Foreign key info on a
> table.
> All help is much appreciated.
> Kind regards,
> Polly Anna|||Hi Aaron,
I am using my query against both a SQL2000 and SQL2005 db.
I attach the query below.
Many thanks for your help.
Polly Anna
select distinct column_name as Column_Name,
data_type,
c.length,
CASE c.colStat
WHEN 1 THEN 'Y' ELSE ''
END AS PrimaryKey
from information_schema.columns as isc
left join sysobjects o
on isc.table_name = o.name
left join syscolumns c
on o.id = c.id and c.name = isc.column_name
where table_name = 'Dentist'
"Aaron Bertrand [SQL Server MVP]" wrote:
> What version of SQL Server are you using?
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> > Hi,
> >
> > I would like to query the system tables in order to obtain all the Primary
> > and Foreign keys for any particular table.
> >
> > I have tried using the syscolumns.colstat field but it only outputs zero.
> > I
> > understand it should output 1 if the column is a primary key.
> >
> > Also I do not know I do not know how to obtain Foreign key info on a
> > table.
> >
> > All help is much appreciated.
> >
> > Kind regards,
> >
> > Polly Anna
>
>|||Polly
SS2005
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
ORDINAL_POSITION;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> Hi Aaron,
> I am using my query against both a SQL2000 and SQL2005 db.
> I attach the query below.
> Many thanks for your help.
> Polly Anna
> select distinct column_name as Column_Name,
> data_type,
> c.length,
> CASE c.colStat
> WHEN 1 THEN 'Y' ELSE ''
> END AS PrimaryKey
> from information_schema.columns as isc
> left join sysobjects o
> on isc.table_name = o.name
> left join syscolumns c
> on o.id = c.id and c.name = isc.column_name
> where table_name = 'Dentist'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> What version of SQL Server are you using?
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> > Hi,
>> >
>> > I would like to query the system tables in order to obtain all the
>> > Primary
>> > and Foreign keys for any particular table.
>> >
>> > I have tried using the syscolumns.colstat field but it only outputs
>> > zero.
>> > I
>> > understand it should output 1 if the column is a primary key.
>> >
>> > Also I do not know I do not know how to obtain Foreign key info on a
>> > table.
>> >
>> > All help is much appreciated.
>> >
>> > Kind regards,
>> >
>> > Polly Anna
>>|||Hi Uri,
yes thank you, that is brilliant. It works in SS2005. You don't happen to
have the script for SQL2000?
Thank you very much indeed.
Kind regards,
Polly Anna
"Uri Dimant" wrote:
> Polly
> SS2005
> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> from sys.key_constraints as k
> join sys.tables as t
> on t.object_id = k.parent_object_id
> join sys.schemas as s
> on s.schema_id = t.schema_id
> join sys.index_columns as ic
> on ic.object_id = t.object_id
> and ic.index_id = k.unique_index_id
> join sys.columns as c
> on c.object_id = t.object_id
> and c.column_id = ic.column_id
> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> ORDINAL_POSITION;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> > Hi Aaron,
> >
> > I am using my query against both a SQL2000 and SQL2005 db.
> >
> > I attach the query below.
> >
> > Many thanks for your help.
> >
> > Polly Anna
> >
> > select distinct column_name as Column_Name,
> > data_type,
> > c.length,
> > CASE c.colStat
> > WHEN 1 THEN 'Y' ELSE ''
> > END AS PrimaryKey
> >
> > from information_schema.columns as isc
> > left join sysobjects o
> > on isc.table_name = o.name
> > left join syscolumns c
> > on o.id = c.id and c.name = isc.column_name
> > where table_name = 'Dentist'
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> What version of SQL Server are you using?
> >>
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> > Hi,
> >> >
> >> > I would like to query the system tables in order to obtain all the
> >> > Primary
> >> > and Foreign keys for any particular table.
> >> >
> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> > zero.
> >> > I
> >> > understand it should output 1 if the column is a primary key.
> >> >
> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> > table.
> >> >
> >> > All help is much appreciated.
> >> >
> >> > Kind regards,
> >> >
> >> > Polly Anna
> >>
> >>
> >>
>
>|||SELECT t.TABLE_SCHEMA,
t.TABLE_NAME, c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE, k.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
AND c.TABLE_NAME = k.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY 1,2,4 DESC,3;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> Hi Uri,
> yes thank you, that is brilliant. It works in SS2005. You don't happen to
> have the script for SQL2000?
> Thank you very much indeed.
> Kind regards,
> Polly Anna
> "Uri Dimant" wrote:
>> Polly
>> SS2005
>> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
>> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
>> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
>> from sys.key_constraints as k
>> join sys.tables as t
>> on t.object_id = k.parent_object_id
>> join sys.schemas as s
>> on s.schema_id = t.schema_id
>> join sys.index_columns as ic
>> on ic.object_id = t.object_id
>> and ic.index_id = k.unique_index_id
>> join sys.columns as c
>> on c.object_id = t.object_id
>> and c.column_id = ic.column_id
>> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
>> ORDINAL_POSITION;
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
>> > Hi Aaron,
>> >
>> > I am using my query against both a SQL2000 and SQL2005 db.
>> >
>> > I attach the query below.
>> >
>> > Many thanks for your help.
>> >
>> > Polly Anna
>> >
>> > select distinct column_name as Column_Name,
>> > data_type,
>> > c.length,
>> > CASE c.colStat
>> > WHEN 1 THEN 'Y' ELSE ''
>> > END AS PrimaryKey
>> >
>> > from information_schema.columns as isc
>> > left join sysobjects o
>> > on isc.table_name = o.name
>> > left join syscolumns c
>> > on o.id = c.id and c.name = isc.column_name
>> > where table_name = 'Dentist'
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> >> What version of SQL Server are you using?
>> >>
>> >>
>> >>
>> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I would like to query the system tables in order to obtain all the
>> >> > Primary
>> >> > and Foreign keys for any particular table.
>> >> >
>> >> > I have tried using the syscolumns.colstat field but it only outputs
>> >> > zero.
>> >> > I
>> >> > understand it should output 1 if the column is a primary key.
>> >> >
>> >> > Also I do not know I do not know how to obtain Foreign key info on a
>> >> > table.
>> >> >
>> >> > All help is much appreciated.
>> >> >
>> >> > Kind regards,
>> >> >
>> >> > Polly Anna
>> >>
>> >>
>> >>
>>|||Hi Aaron,
it works like a charm. Thank you so much for your help.
Kind regards,
Polly Anna
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT t.TABLE_SCHEMA,
> t.TABLE_NAME, c.CONSTRAINT_NAME,
> c.CONSTRAINT_TYPE, k.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLES t
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
> ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
> AND t.TABLE_NAME = c.TABLE_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
> ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
> AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
> AND c.TABLE_NAME = k.TABLE_NAME
> WHERE
> c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
> ORDER BY 1,2,4 DESC,3;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> > Hi Uri,
> >
> > yes thank you, that is brilliant. It works in SS2005. You don't happen to
> > have the script for SQL2000?
> >
> > Thank you very much indeed.
> >
> > Kind regards,
> >
> > Polly Anna
> >
> > "Uri Dimant" wrote:
> >
> >> Polly
> >> SS2005
> >> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> >> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> >> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> >> from sys.key_constraints as k
> >> join sys.tables as t
> >> on t.object_id = k.parent_object_id
> >> join sys.schemas as s
> >> on s.schema_id = t.schema_id
> >> join sys.index_columns as ic
> >> on ic.object_id = t.object_id
> >> and ic.index_id = k.unique_index_id
> >> join sys.columns as c
> >> on c.object_id = t.object_id
> >> and c.column_id = ic.column_id
> >> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> >> ORDINAL_POSITION;
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> >> > Hi Aaron,
> >> >
> >> > I am using my query against both a SQL2000 and SQL2005 db.
> >> >
> >> > I attach the query below.
> >> >
> >> > Many thanks for your help.
> >> >
> >> > Polly Anna
> >> >
> >> > select distinct column_name as Column_Name,
> >> > data_type,
> >> > c.length,
> >> > CASE c.colStat
> >> > WHEN 1 THEN 'Y' ELSE ''
> >> > END AS PrimaryKey
> >> >
> >> > from information_schema.columns as isc
> >> > left join sysobjects o
> >> > on isc.table_name = o.name
> >> > left join syscolumns c
> >> > on o.id = c.id and c.name = isc.column_name
> >> > where table_name = 'Dentist'
> >> >
> >> >
> >> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >> >
> >> >> What version of SQL Server are you using?
> >> >>
> >> >>
> >> >>
> >> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I would like to query the system tables in order to obtain all the
> >> >> > Primary
> >> >> > and Foreign keys for any particular table.
> >> >> >
> >> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> >> > zero.
> >> >> > I
> >> >> > understand it should output 1 if the column is a primary key.
> >> >> >
> >> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> >> > table.
> >> >> >
> >> >> > All help is much appreciated.
> >> >> >
> >> >> > Kind regards,
> >> >> >
> >> >> > Polly Anna
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>

Obtaining data from different tables in the same database

Hi

I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.

Thanks

Here's a sample query:

TableA contains 3 columns: Data1, Data2, ForeignKey
TableB contains 2 columns: RecordId, SomeOtherData

The RecordId value from TableB has been stored in the ForeignKey column of TableA

SELECT TableA.Data1, TableA.Data2, TableB.SomeOtherDataFROM TableAINNERJOIN TableBON TableA.ForeignKey = TableB.RecordId

Each row of data that is retrieved will look like:

Data1,Data2, [ForeignKey] <--> [RecordId],SomeOtherData

obtain the next Identity seed?

Hi,
I'm using identity seed (increment by 1) as primary key in my tables.
Is there any way to determine what will be the next available identity for a specified table?
Thanks for the info
GauthierHi,

In 2000 you can use IDENT_CURRENT('table name ') + 1 to get next possible value. but in multi user env , need to check ...

best of luck|||that's it!

thanks alot.

Gauthier

Obtain a list of all rows insert via a select statement

With Table A being the parent table and having an identity column as the
primary key.
With Table B being a child of table A
I need to do an insert based on a select statement (multiple row returned)
in Table A. Then I need to insert multiple child rows in Table B for every
row inserted in Table A.
What is the best strategy? Is there a way to have a list of all the rows
inserted?
I'm open to any suggestion.
Thank you in advance
MartinYou can access those within a trigger, and have it populate a temp table
that the calling batch created, if such a temp table exists, e.g.,
create table t(k int not null identity primary key, d varchar(10));
go
create trigger trg_i_t on t for insert
as
if object_id('tempdb..#t') is not null
insert into #t select k from inserted;
go
-- test
create table #t(k int);
insert into t(d)
select 'a'
union all select 'b'
union all select 'c';
select * from #t;
drop table #t;
-- Output:
k
--
4
5
6
In SQL Server 2005 it will be much easier with the DML with results
enhancements.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:35F4A835-AC48-4896-B535-B550A768CF74@.microsoft.com...
> With Table A being the parent table and having an identity column as the
> primary key.
> With Table B being a child of table A
> I need to do an insert based on a select statement (multiple row returned)
> in Table A. Then I need to insert multiple child rows in Table B for every
> row inserted in Table A.
> What is the best strategy? Is there a way to have a list of all the rows
> inserted?
> I'm open to any suggestion.
> Thank you in advance
> Martin|||On Tue, 22 Mar 2005 15:51:02 -0800, Martin Rajotte wrote:

>With Table A being the parent table and having an identity column as the
>primary key.
>With Table B being a child of table A
>I need to do an insert based on a select statement (multiple row returned)
>in Table A. Then I need to insert multiple child rows in Table B for every
>row inserted in Table A.
>What is the best strategy? Is there a way to have a list of all the rows
>inserted?
>I'm open to any suggestion.
>Thank you in advance
>Martin
Hi Martin,
I'm not entirely sure if I understand you. Do you mean that you have
some data in one or more tables that needs to be inserted in two new
tables, where the second table links to the identity column of the
first?
The proper way to find the identity value of inserted rows after a
multirow insert is to query the table with the natural key as search
argument. That should return you the identity values.
If you need more specific advise, then please code DDL, sample data and
desired results. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)