anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.
We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
|||it works! thanks
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it i
s
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it i
s
Null in all 3 tables I will just use 0 for the value. So use maxi from tabl
e
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day fr
om
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if n
ot
> null, then if null use value from table c for maxi.
> tableA
> ****************************************
**
> pri key * color_id * size_id * date* maxi *
> ****************************************
**
> * 1 * 2 * 5 * 2/1/07* 7 *
> ****************************************
**
> * 2 * 4 * 6 * 2/2/07* NULL *
> ****************************************
**
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

Anybody recognize these tables?

First, apologies if this is the wrong forum for this post - if so please
gently redirect me to the correct place to post.
I have taken over as the DBA/developer on a ProjectServer instance. In my
database there are some groups of talbes that I don't think should be their.
I can't link them to anything in MSPS and they are probably from some
tutorial. The three sets begin with DS_, OBJ_, and UNV_.
The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
As best as I can tell - reviewing MSPS documentation - these have nothing to
do with ProjectServer. They look like they might have been involved in some
sort of Use Case modeling tool or something.
Is anybody familiar with these tables? Am I free to delete them?
Thanks
Bob,
Looks like Business Objects to me. All ProjectServer tables are prefixed
with MSP_.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob wrote:
> First, apologies if this is the wrong forum for this post - if so please
> gently redirect me to the correct place to post.
> I have taken over as the DBA/developer on a ProjectServer instance. In my
> database there are some groups of talbes that I don't think should be their.
> I can't link them to anything in MSPS and they are probably from some
> tutorial. The three sets begin with DS_, OBJ_, and UNV_.
> The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
> The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
> The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
> As best as I can tell - reviewing MSPS documentation - these have nothing to
> do with ProjectServer. They look like they might have been involved in some
> sort of Use Case modeling tool or something.
> Is anybody familiar with these tables? Am I free to delete them?
> Thanks
>

Anybody recognize these tables?

First, apologies if this is the wrong forum for this post - if so please
gently redirect me to the correct place to post.
I have taken over as the DBA/developer on a ProjectServer instance. In my
database there are some groups of talbes that I don't think should be their.
I can't link them to anything in MSPS and they are probably from some
tutorial. The three sets begin with DS_, OBJ_, and UNV_.
The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
As best as I can tell - reviewing MSPS documentation - these have nothing to
do with ProjectServer. They look like they might have been involved in some
sort of Use Case modeling tool or something.
Is anybody familiar with these tables? Am I free to delete them?
ThanksBob,
Looks like Business Objects to me. All ProjectServer tables are prefixed
with MSP_.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob wrote:
> First, apologies if this is the wrong forum for this post - if so please
> gently redirect me to the correct place to post.
> I have taken over as the DBA/developer on a ProjectServer instance. In my
> database there are some groups of talbes that I don't think should be their.
> I can't link them to anything in MSPS and they are probably from some
> tutorial. The three sets begin with DS_, OBJ_, and UNV_.
> The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
> The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
> The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
> As best as I can tell - reviewing MSPS documentation - these have nothing to
> do with ProjectServer. They look like they might have been involved in some
> sort of Use Case modeling tool or something.
> Is anybody familiar with these tables? Am I free to delete them?
> Thanks
>sql

Anybody recognize these tables?

First, apologies if this is the wrong forum for this post - if so please
gently redirect me to the correct place to post.
I have taken over as the DBA/developer on a ProjectServer instance. In my
database there are some groups of talbes that I don't think should be their.
I can't link them to anything in MSPS and they are probably from some
tutorial. The three sets begin with DS_, OBJ_, and UNV_.
The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
As best as I can tell - reviewing MSPS documentation - these have nothing to
do with ProjectServer. They look like they might have been involved in some
sort of Use Case modeling tool or something.
Is anybody familiar with these tables? Am I free to delete them?
ThanksBob,
Looks like Business Objects to me. All ProjectServer tables are prefixed
with MSP_.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob wrote:
> First, apologies if this is the wrong forum for this post - if so please
> gently redirect me to the correct place to post.
> I have taken over as the DBA/developer on a ProjectServer instance. In my
> database there are some groups of talbes that I don't think should be thei
r.
> I can't link them to anything in MSPS and they are probably from some
> tutorial. The three sets begin with DS_, OBJ_, and UNV_.
> The DS_ tables are DS_PENDING_JOBS and DS_USER_LIST
> The OBJ_ tables have names like OBJ_M_ACTOR and OBJ_M_CHANNEL
> The UNV_ tables have names like UNV_RELATIONS and UNV_TAB_OBBJ
> As best as I can tell - reviewing MSPS documentation - these have nothing
to
> do with ProjectServer. They look like they might have been involved in so
me
> sort of Use Case modeling tool or something.
> Is anybody familiar with these tables? Am I free to delete them?
> Thanks
>

Any way to protect our Report design

Is there any way...we can hide the report design. I do not want anyone to see my report design or formula or tables or sql expression i have used. Even if i can hide my formula and sql expression it will be of great help. Any help greatly appreciated
Thanx thakkarJust hide the report itself|||But I have to distribute in the company. so i have to show the file so that it can be run through our report scheduler. Any other way to protect the hard work we have done...

Thanx madhi once again
Thakkar|||No Ideas Guys!!!!!
Should be something to protect the report.sql

Any way to obtain changes in tables?

Hi
I'm Using Merge Replication to replicate info from a Pocket PC app. This
info must be integrated with legacy systems in FOXPRO. Is there a way to
obtain changes (new rows and updated rows) in tables to pass it by textfile
to these legacy systems?
For now, i'm experimenting with a status Column in each table. When
inserting or updating in SQLCE i update this column to 0. Once i replicate I
update it to 1(in the pocket). In the server, a memory resident program is
running every two minutes checking for new records (status=0) and exporting
them to a textfile. This, even when functional, i don't think is the best way
to do it. So I ask you if this can be done by a native way in SQL.
Thank you
Regards!
Omar Rojas
It has been a little while since I've used Merge Replication from a CE-based
device, and I'm a little vague on the restrictions but:
Although ugly and possibly undesirable, a trigger on each table to be
tracked could be used to "catch" changes. it could possible update a
"control" table of table name and key values to be processed later by a
formatter that writes the text file, or other, really quick and simple
processing.
You could get trickier and delve into the replication metadata to capture
this information and avoid the triggers, but that requires a relatively
in-depth understanding of the internals of replication.
Cheers.
"Omar Rojas" wrote:

> Hi
> I'm Using Merge Replication to replicate info from a Pocket PC app. This
> info must be integrated with legacy systems in FOXPRO. Is there a way to
> obtain changes (new rows and updated rows) in tables to pass it by textfile
> to these legacy systems?
> For now, i'm experimenting with a status Column in each table. When
> inserting or updating in SQLCE i update this column to 0. Once i replicate I
> update it to 1(in the pocket). In the server, a memory resident program is
> running every two minutes checking for new records (status=0) and exporting
> them to a textfile. This, even when functional, i don't think is the best way
> to do it. So I ask you if this can be done by a native way in SQL.
> Thank you
> Regards!
> Omar Rojas

Any way to insert page breaks between tables?

I have about 5 tables on a report.
Is there a way to insert a break between the tables to prevent tables
from being chopped on a page break?
Thank you!Pull up table properties dialog and check "insert page break after this
table" checkbox and also "fit this table on one page if possible" checkbox.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:bg48i09pocfef9h7ot6rmklf02uas8dl49@.4ax.com...
> I have about 5 tables on a report.
> Is there a way to insert a break between the tables to prevent tables
> from being chopped on a page break?
> Thank you!
>sql

Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checking the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the cake and have it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing the
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK and
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

Any way to delete rows from table and ignore Foreign Keys

I am trying to delete data from a table which is being referenced by a lot
of tables by Foreign Keys. There are 2 tables in particular that do not
have an index on the Foreign Key field referencing the table that I am
deleting from.
I am certain that there are no rows in this dependant table referencing the
rows that I want to delete.
Is it possible to somehow perform the delete without checking the Foreign
Key? Since this is a production database - I don't want to drop the FK and
then redefine it later because I will experience blocking while the create
of the Foreign Key is running.
Any help would be appreciated.
ThanksYou can disable the FK, delete and then enable it. But that leave the FK non
-trusted, unless you
enable it with the CHECK option, but that leaves you with SQL Server checkin
g the data when enabling
it (basically same as dropping and creating). Same old story, can't eat the
cake and have it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TJT" <TJT@.nospam.com> wrote in message news:O9VnXOyiGHA.1552@.TK2MSFTNGP03.phx.gbl...seagreen">
>I am trying to delete data from a table which is being referenced by a lot
> of tables by Foreign Keys. There are 2 tables in particular that do not
> have an index on the Foreign Key field referencing the table that I am
> deleting from.
> I am certain that there are no rows in this dependant table referencing th
e
> rows that I want to delete.
> Is it possible to somehow perform the delete without checking the Foreign
> Key? Since this is a production database - I don't want to drop the FK an
d
> then redefine it later because I will experience blocking while the create
> of the Foreign Key is running.
> Any help would be appreciated.
> Thanks
>

Any way logging or tracking method for dropped tables

Most of my tables is dropped after they are created. I cannot tell when this
happen. This include stored procedures as well. I checked the maintenance job
and none are dropping tables or stored procedures.
The funny part is my tables and stored procedure which is published out as
articles remain and not dropped as others. Why?
Is there any logs or tracking that can be put in place to find out who
deleted my tables?
Paul,
I'm using SQL Server 2000 so I guess I would need to use the log explorer
tool. Are there any recommendation which I can try first?
Another question which hopefully can narrow down my suspect here is..Do you
think replication dropped the tables?
Cheers,
Philip
"Paul Ibison" wrote:

> If you're on SQL Server 2005 you could set up ddl triggers to log who/what
> is performing the drops. Alternatively a 3rd party log explorer tool will
> have the info in it.
> HTH,
> Paul Ibison
>
>
|||Replication by default will drop tables on the subscriber, but never on the
publisher so i'd look elsewhere. The reason replicated objects haven't been
dropped is probably because once they're published, they have to be removed
from the publication to be allowed to be dropped, so that distinguishes them
from your other objects. (this is a bit of guesswork, but it makes sense).
Lumigent Log Explorer will audit DDL commands to help you figure out who did
the drop (http://lumigent.com/products/le_sql_faq.html#_I_do_not). If it
happens regularly then you could use profiler yourself to monitor for these
drops.
HTH,
Paul Ibison

Any valid login can access Enterprise Manager

Hi.
When creating a SQL Server2000 login (NT Authen) with read-only rights to
user tables in a user database, this very same login can:
1. Login into EM
2. Though cannot change any objects, but can
- 1. view all system objects (logins, DTS etc)
3. STOP SQL Server Agent
4. RESTART SQL SERVER!!!!
This all seem to be traced back to the fact every login is a member of the
PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
Can anyone tell me how to:
1. Prevent user (not DBA, DBO's etc) login into EM?
2. Prevent user login into QA?
Cheers!> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
prevent access to msdb. This will prevent viewing DTS packages. See
http://support.microsoft.com/defaul...b;en-us;282463.
You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users from
enumerating logins via EM.

> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
The ability to stop and start services is controlled through Windows
permissions, not SQL Server security. If the account is a member of the
Windows 'Administrators' or 'Power Users' groups, then the user can stop and
start services using any tool or command. EM will not allow non-privileged
users to stop/start services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
> Hi.
> When creating a SQL Server2000 login (NT Authen) with read-only rights to
> user tables in a user database, this very same login can:
> 1. Login into EM
> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
> This all seem to be traced back to the fact every login is a member of the
> PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
> Can anyone tell me how to:
> 1. Prevent user (not DBA, DBO's etc) login into EM?
> 2. Prevent user login into QA?
> Cheers!|||Thks Dan - it sure works - but still no way of preventing a valid SQL Login
to access other objects on EM or seeing them using other tools (such as
Visual Studio).
Thks again!
"Dan Guzman" wrote:

> You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
> prevent access to msdb. This will prevent viewing DTS packages. See
> http://support.microsoft.com/defaul...b;en-us;282463.
> You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users fro
m
> enumerating logins via EM.
>
> The ability to stop and start services is controlled through Windows
> permissions, not SQL Server security. If the account is a member of the
> Windows 'Administrators' or 'Power Users' groups, then the user can stop a
nd
> start services using any tool or command. EM will not allow non-privilege
d
> users to stop/start services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
>
>|||By default, SQL 2000 users can read catalog meta data in those databases
they have permissions to access. It's possible to revoke public permissions
from some of the catalog objects but this can break data access API's so
proceed at your own risk. SQL 2005 provides more control over meta data
access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...[vbcol=seagreen]
> Thks Dan - it sure works - but still no way of preventing a valid SQL
> Login
> to access other objects on EM or seeing them using other tools (such as
> Visual Studio).
> Thks again!
> "Dan Guzman" wrote:
>|||Thks Dan for all your help!
"Dan Guzman" wrote:

> By default, SQL 2000 users can read catalog meta data in those databases
> they have permissions to access. It's possible to revoke public permissio
ns
> from some of the catalog objects but this can break data access API's so
> proceed at your own risk. SQL 2005 provides more control over meta data
> access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...
>
>

Any stored procedures to check a database's free space and its allocated space ?

Can anybody tell me are there are stored procedures or system tables show me
:
(1)The hard disk space allocated to a database.
(2)The hard disk space used on a database
(3)The hard disk space allocated to a database's transaction log
(4)The hard disk space used on a database's transaction log
Thanks.cpchan
(1)The hard disk space allocated to a database.
(2)The hard disk space used on a database
(3)The hard disk space allocated to a database's transaction log
(4)The hard disk space used on a database's transaction log
1)CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
2) sp_helpdb 'pubs'
3,4) sp_helpfile pubs_log,dbcc sqlperf(logspace)
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1scnn$pq12@.imsp212.netvigator.com...
> Can anybody tell me are there are stored procedures or system tables show
me
> :
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> Thanks.
>
>|||Hi,
For database execute the below procedure with out parameter,
sp_spaceused
For transaction log ,
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1scnn$pq12@.imsp212.netvigator.com...
> Can anybody tell me are there are stored procedures or system tables show
me
> :
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> Thanks.
>
>|||Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#XViSBr$DHA.3184@.TK2MSFTNGP09.phx.gbl...
> cpchan
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> 1)CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> INSERT into Drivetable Exec xp_fixeddrives
> SELECT * FROM DriveTable
> 2) sp_helpdb 'pubs'
> 3,4) sp_helpfile pubs_log,dbcc sqlperf(logspace)
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c1scnn$pq12@.imsp212.netvigator.com...
show
> me
>|||Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#Rs09Fr$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> For database execute the below procedure with out parameter,
> sp_spaceused
> For transaction log ,
> DBCC SQLPERF(LOGSPACE)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c1scnn$pq12@.imsp212.netvigator.com...
show
> me
>

Any stored procedures to check a database's free space and its allocated space ?

Can anybody tell me are there are stored procedures or system tables show me
:
(1)The hard disk space allocated to a database.
(2)The hard disk space used on a database
(3)The hard disk space allocated to a database's transaction log
(4)The hard disk space used on a database's transaction log
Thanks.cpchan
(1)The hard disk space allocated to a database.
(2)The hard disk space used on a database
(3)The hard disk space allocated to a database's transaction log
(4)The hard disk space used on a database's transaction log
1)CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
2) sp_helpdb 'pubs'
3,4) sp_helpfile pubs_log,dbcc sqlperf(logspace)
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1scnn$pq12@.imsp212.netvigator.com...
> Can anybody tell me are there are stored procedures or system tables show
me
> :
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> Thanks.
>
>|||Hi,
For database execute the below procedure with out parameter,
sp_spaceused
For transaction log ,
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c1scnn$pq12@.imsp212.netvigator.com...
> Can anybody tell me are there are stored procedures or system tables show
me
> :
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> Thanks.
>
>|||Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#XViSBr$DHA.3184@.TK2MSFTNGP09.phx.gbl...
> cpchan
> (1)The hard disk space allocated to a database.
> (2)The hard disk space used on a database
> (3)The hard disk space allocated to a database's transaction log
> (4)The hard disk space used on a database's transaction log
> 1)CREATE table DriveTable (Drive varchar(10),[MB Free] int)
> INSERT into Drivetable Exec xp_fixeddrives
> SELECT * FROM DriveTable
> 2) sp_helpdb 'pubs'
> 3,4) sp_helpfile pubs_log,dbcc sqlperf(logspace)
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c1scnn$pq12@.imsp212.netvigator.com...
> > Can anybody tell me are there are stored procedures or system tables
show
> me
> > :
> >
> > (1)The hard disk space allocated to a database.
> > (2)The hard disk space used on a database
> > (3)The hard disk space allocated to a database's transaction log
> > (4)The hard disk space used on a database's transaction log
> >
> > Thanks.
> >
> >
> >
>|||Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#Rs09Fr$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> For database execute the below procedure with out parameter,
> sp_spaceused
> For transaction log ,
> DBCC SQLPERF(LOGSPACE)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c1scnn$pq12@.imsp212.netvigator.com...
> > Can anybody tell me are there are stored procedures or system tables
show
> me
> > :
> >
> > (1)The hard disk space allocated to a database.
> > (2)The hard disk space used on a database
> > (3)The hard disk space allocated to a database's transaction log
> > (4)The hard disk space used on a database's transaction log
> >
> > Thanks.
> >
> >
> >
>

any SQL gurus out there?

Hello, this probably isnt the best place to ask but I can't find a more
suitable sql newsgroup so I hope y'all dont mind too much.

I have 2 tables; Cellar and Colour

CELLAR contains the wine name, its year and the no.of bottles.

Wine Year Bottles
Chardonnay 87 4
Fume Blanc 87 2
Pinot Noir 82 3
Zinfandel 84 9

COLOUR contains wine name and it's colour

Wine Colour
Chardonnay White
Fume Blanc White
Pinot NoirRed
Zinfandel Rose

This is from a past exam paper btw

One of the questions was:
Write the sql to count how many white wines there are in the table cellar.

The solution that the lecturers included is:

SELECT count(wine)

FROM cellar

WHERE colour='White'

Now i havent' been able to try out this sql yet but to me that looks wrong.

My solution would be:

SELECT count(wine)

FROM cellar

WHERE cellar.wine = colour.wine and colour.colour='White'

Can anyone tell me which one is correct, and if mine isn't correct then why
isn't it?

Thanks>> Write the sql to count how many white wines there are in the table
cellar. <<

The first answer is wrong; look at the missing table in the FROM
clause. And the quesiton is vague. Do I want the actual bottle count
or a count by wine_type

SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
bottle_count
FROM Cellar AS C, WineColours AS W
WHERE C.wine_type = W.wine_type
AND W.colour = 'White' ;

I have a total of six bottles of whites in two varieties.|||> The first answer is wrong; look at the missing table in the FROM
> clause. And the quesiton is vague. Do I want the actual bottle count
> or a count by wine_type
> SELECT COUNT(DISTINCT type_wine) AS type_count, COUNT(*) AS
> bottle_count
> FROM Cellar AS C, WineColours AS W
> WHERE C.wine_type = W.wine_type
> AND W.colour = 'White' ;
> I have a total of six bottles of whites in two varieties.

thanks for your answer

yeh the question is poorly worded however I believe it simply refers to the
number of types, i.e 2 (chardonnay, fume blanc).|||Well, you don't need an "SQL guru" for that query. Since the question is
not really clear, you can pick the column you need.

SELECT COUNT(Distinct Wine) NumberOfWhiteWineBrands
, SUM(Bottles) NumberOfBottlesOfWhiteWine
FROM Cellar
INNER JOIN Colour
ON Colour.Wine = Cellar.Wine
WHERE Colour.Colour = 'White'

If it is the column "NumberOfWhiteWineBrands" that you need, then you
could also write

SELECT COUNT(*)
FROM Colour
WHERE Colour = 'White'
AND EXISTS (
SELECT 1
FROM Cellar
WHERE Cellar.Wine = Colour.Wine
)

HTH,
Gert-Jan

Jay wrote:
> Hello, this probably isnt the best place to ask but I can't find a more
> suitable sql newsgroup so I hope y'all dont mind too much.
> I have 2 tables; Cellar and Colour
> CELLAR contains the wine name, its year and the no.of bottles.
> Wine Year Bottles
> Chardonnay 87 4
> Fume Blanc 87 2
> Pinot Noir 82 3
> Zinfandel 84 9
> COLOUR contains wine name and it's colour
> Wine Colour
> Chardonnay White
> Fume Blanc White
> Pinot NoirRed
> Zinfandel Rose
> This is from a past exam paper btw
> One of the questions was:
> Write the sql to count how many white wines there are in the table cellar.
> The solution that the lecturers included is:
> SELECT count(wine)
> FROM cellar
> WHERE colour='White'
> Now i havent' been able to try out this sql yet but to me that looks wrong.
> My solution would be:
> SELECT count(wine)
> FROM cellar
> WHERE cellar.wine = colour.wine and colour.colour='White'
> Can anyone tell me which one is correct, and if mine isn't correct then why
> isn't it?
> Thanks

Any SQL Gods out there

I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.

History:
3 Tables: Tooldb - Employee - ToolUserdb

Scenario:
I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.

My SP:
ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS

INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO ToolUsersdb (@.Creator) SELECT + @.eid + ',[ID] FROM Employee + @.tdbid + ',[ID] FROM ToolDB IN (@.Creator)

Error:
Incorrect syntax near keywork IN (referring to last insert statement).

Can someone tell me how I can get this multi-insert stmt. to work?

Thank you!

TimWhat's the DDL for ToolUsersDB table?

INSERT INTO ToolUsersdb (EmpID, ToolID, Creator) values (@.eid, @.tdbid, @.Creator) ?|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToolUsersdb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ToolUsersdb]
GO

CREATE TABLE [dbo].[ToolUsersdb] (
[id] [int] NOT NULL ,
[eid] [int] NULL ,
[tdbid] [int] NULL
) ON [PRIMARY]
GO|||After examining my SP closer I have made the following changes. I think this is closer to the end result. The problem is that I am getting an error:

The select list for the insert statement contains fewer items than the insert list. Not sure why this is.

ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS

INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO [ToolUsersdb] (eid, tdbid) select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t|||Huh?

INSERT INTO [ToolUsersdb] (eid, tdbid)
select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t

What this?

You can't do that|||Brett,

Could you expand on your thoughts?

Do you have any ideas on how this can be done. I am sure that I am not the first person to be presented with a one to many realtionship between tables.

Even a link would be helpful if you know of one.

Thanks,

Tim|||It looks like, and correct me if I'm wrong, and INSERT statement with 2 SELECTs...

I guess you could do it this way...

INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id, t.id
FROM employee e JOIN Tooldb t
ON e.id = t.id
WHERE e.id = @.Creator

The IN error message is erroneous...|||Brett,

Thanks for helping out. You thoughts are appreciated.

Here is the modification I have made (with your thoughts in mind):

INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id FROM employee e WHERE e.id = (@.Creator)
UNION
SELECT t.id FROM Tooldb t

I put the UNION in place because my understanding is that without it, the second SLELECT statement would be ignored.

With the above script I am getting the following error:
The Select list for the insert statment contains fewer items than the Insert list.

There are only two columns in the ToolUsersdb outside of the ID column.

Any additional ideas?

Have I explained what I am trying to do clearly enough in the beginning? Let me know if you need any further clarification.

Thanks again.

Tim|||Concepts!

You're mixing rows with columns.

For example, your INSERT expects 2 columns per row.

Your result set is producing 1 column per row.

If you want e.id and t.id on the same row, you have to relate them somehow.