how to excluded the intersection of 2 dimensions ?

Hi,

I have the following simplified problem.

I have a fact table and two dimension tables, colors and weekdays.

I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays ?

They query tool doesn't give you really the options.

Any suggestions ?

Constantijn Enders

You can do it in MDX, but only a few query tools will allow you to construct queries like this. I think vendors tend to refer to this capability as 'asymmetic sets'; I can only think of one tool I've seen recently that did this (Intelligencia - http://www.it-workplace.co.uk/i4wfeatures.aspx) but I'm sure that if you hunt around there will be others. Does anyone else know of one?

Here's an MDX query to prove it's possible:

select

measures.[internet sales amount] on 0,

except(

[Date].[Day Name].[Day Name].members

*

[Product].[Color].[Color].members

,{([Date].[Day Name].&[2], [Product].[Color].&[Red])}

) on 1

from [Adventure Works]

HTH,

Chris

How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want to
write a query that will allow me to do this.
Thank you.
S
I posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>
sql

How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want t
o
write a query that will allow me to do this.
Thank you.
SI posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>

How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want to
write a query that will allow me to do this.
Thank you.
SI posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
--
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>

how to enforce precedence at data flow level?

I wish to use the same data to update 2 different tables.

There is no green arrow output from the OLE DB data destination, so I can't have another component following on from the first insert.

This means I have to use the Multicast to 'copy' the data prior to the first table insert.

I can then use the data to perform inserts to both tables.

However, there is an FK constraint between these two tables, so I need to wait until the first table insert has finished before performing the second table insert.

How can I do this? How can I make the second insert dependent on the first?

Hi ya,

Unfortunately there is no precedence at data flow level. Your best bet would be to put it in 2 data flows and make sure that the first data flow is on success.

If you do want to run it in a transaction then put a sequence container and put the transaction property for container as Required whille both data flows should be supported.

Sorry but i couldn't find anything else........ probably the big guys would answer if there is anything else?

Hope that helps

Cheers

Rizwan

|||That's a pain.

That means i'll have to build the dataset up all over again......and no i daren't cut and paste due to the endless xml/serialization errors I always/randomly seem to get that completely blow up the IDE

I'm surprised there is no 'rendezvous' component in the toolbox.

do you know if anyone has written a custom component like this?|||

Sure this is but then again you always have the option to select a custom script task and then programatically do the importing and saving of data........ though this is just an idea as i haven't tried it myself yet.

leave the thread open and we'll see if Moderators or MVPs has something else in their mind.

Cheers

Rizwan

|||

One way to do this would to have the DataFlow drop the data for the child table to a RAW file, then in a 2nd dataflow read the RAW and piopulate the table|||

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

|||

desibull wrote:

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

I just want to let my opinion be known on this topic:

There is no reason to have precedence constraints in a data flow. A data flow is designed to move data in buffers -- as fast as it can. To be able to enforce ordering is non-sense. Just break the data up into two or more data flows. I do not support the notion of precedence constraints in a data flow.|||

I agree with Paul - the easiest way to do this without having to reprocess the dataset is to drop it to a RAW destination. It's also extremely fast.

|||

Hi,

That means i was right. Great

Cheers

Rizwan

How To Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to drop merge replication system tables

Hi, we have a database wich used merge replication.
We disabled merge on this db, but '%onflict%' system tables persists,
leading to error when trying to drop them.
How can I drop those tables?
TIA,
Roberto Souza.
You could use sp_subscription_cleanup
There's a fairly good list of sp's used in replication at this site:
http://doc.ddart.net/mssql/sql70/sp_00.htm
"Roberto Souza" wrote:

> Hi, we have a database wich used merge replication.
> We disabled merge on this db, but '%onflict%' system tables persists,
> leading to error when trying to drop them.
> How can I drop those tables?
> TIA,
> Roberto Souza.
>
>
|||AFAIR this won't remove them. You should be able to use
drop table and the tablename from Query Analyser though.
Rgds,
Paul Ibison

How to drop all primary key of all tables in DB?

How to drop all primary key of all tables in DB?
After reading a document for explaning how to use clustered index, I knew
set all primary key to be clustered is not correct for me.
I would like to drop them. recreate a new nonclustered primary key and
create other clustered indexes.
Now I would like to know how to drop all primary key of all tables in DB by
T-SQL, because there are too many tables to drop primary key by hand.
Thanks
--UsingSQL2005DevFrank
declare @.pklist table
(
ident int identity(1, 1),
pkname sysname,
tablename sysname
)
insert into
@.pklist
(
pkname,
tablename
)
select
constraint_name,
table_name
from
information_schema.table_constraints
where
constraint_type = 'primary key'
set @.counter = @.@.rowcount
while @.counter > 0
begin
select @.constraint = pkname, @.table = tablename from @.pklist where ident =
@.counter
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
set @.counter = @.counter - 1
end
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||Hi
You should extend Uri's solution so that all Foreign Keys that reference
your Primary Key is removed before trying to remove the Primary Key.
John
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||I see. Thx.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:eNvQsxGEGHA.3892@.TK2MSFTNGP10.phx.g
bl...
> Hi
> You should extend Uri's solution so that all Foreign Keys that reference
> your Primary Key is removed before trying to remove the Primary Key.
> John
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
>

How to drop all PKs on tables in database?

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanksdeclare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||I think it would be safer to do this inside the loop:

print 'alter table ' + @.Table + ' drop constraint ' + @.PK

rather than this:

exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )

that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.|||the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
1 step first
drop all foreign keys relation ship
declare @.Table varchar(100)
declare @.FK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',...)
open cur
fetch next from cur into @.FK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.FK )
fetch next from cur into @.FK, @.Table
end
close cur
deallocate cur
2. step second now drop all primary key relation ship
declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||right... we need to drop the FKs first.
but the code from greenindia is having the following problems

1) xtype for foriegn-key is "F" and not "FK"
2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
object_name(parent_obj) in ('Table1','Table2',...)

u need a trip from sysforeignkeys to trap the relation properly :rolleyes:|||you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?

What would be the fun in that?|||Hi all,

Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
print ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur

print 'alter table ' + @.Table + ' drop constraint ' + @.PK|||does this query return anything? if not, there's your answer.

select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||Are you referring to killing the connection and getting rid of #temp tables?
If so, they should be
removed when the connection is terminated.
If you mean removing the users, and that user owns regular tables in the dat
abase, use DROP TABLE to
get rid of those tables (if that is what you want to do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!|||and there are no connections
"totoro" wrote:

> after running the DROP TABLE and getting another error, I am not sure thes
e
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:

> Ok, assuming that you are querying sysobjects to find these, then sysobjec
ts
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the databas
e
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>
>|||Did you include the owner of the object?
DROP TABLE ownername.tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...[vbcol=seagreen]
> there is a "U"
> but the drop table command retrieved an error saying it couldn't find any
> table with the name (TMP_SYS_etc) I had in the query, so my sysntax must b
e
> wrong
> lurnin a lot tuday!
> "Russell Fields" wrote:
>|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:

> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
>

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!
Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!
|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!
|||and there are no connections
"totoro" wrote:

> after running the DROP TABLE and getting another error, I am not sure these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!
|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!
|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:

> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the database
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>
>
|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:

> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
>

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||Are you referring to killing the connection and getting rid of #temp tables? If so, they should be
removed when the connection is terminated.
If you mean removing the users, and that user owns regular tables in the database, use DROP TABLE to
get rid of those tables (if that is what you want to do).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!|||and there are no connections
"totoro" wrote:
> after running the DROP TABLE and getting another error, I am not sure these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:
> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the database
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> > after running the DROP TABLE and getting another error, I am not sure
> > these
> > objects are tables.
> > Their naming convention is "TMP_SYSA_1234"
> > and I am running SQL Server 2K
> >
> > thanks for your fast responses!
>
>|||Did you include the owner of the object?
DROP TABLE ownername.tblname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
> there is a "U"
> but the drop table command retrieved an error saying it couldn't find any
> table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
> wrong
> lurnin a lot tuday!
> "Russell Fields" wrote:
>> Ok, assuming that you are querying sysobjects to find these, then sysobjects
>> also contains a code showing you the type of object:
>> U = user table
>> P = stored procedure
>> V = view
>> et cetera.
>> You will need to use the proper DROP statement for whatever the object is.
>> In Enterprise Manager you can go to each of the collections in the database
>> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
>> you can delete them from there.
>> RLF
>> "totoro" <totoro@.discussions.microsoft.com> wrote in message
>> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>> > after running the DROP TABLE and getting another error, I am not sure
>> > these
>> > objects are tables.
>> > Their naming convention is "TMP_SYSA_1234"
>> > and I am running SQL Server 2K
>> >
>> > thanks for your fast responses!
>>|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:
> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
> > there is a "U"
> >
> > but the drop table command retrieved an error saying it couldn't find any
> > table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
> > wrong
> >
> > lurnin a lot tuday!
> >
> > "Russell Fields" wrote:
> >
> >> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> >> also contains a code showing you the type of object:
> >> U = user table
> >> P = stored procedure
> >> V = view
> >> et cetera.
> >>
> >> You will need to use the proper DROP statement for whatever the object is.
> >>
> >> In Enterprise Manager you can go to each of the collections in the database
> >> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> >> you can delete them from there.
> >>
> >> RLF
> >> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> >> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> >> > after running the DROP TABLE and getting another error, I am not sure
> >> > these
> >> > objects are tables.
> >> > Their naming convention is "TMP_SYSA_1234"
> >> > and I am running SQL Server 2K
> >> >
> >> > thanks for your fast responses!
> >>
> >>
> >>
>

How to do this?

Hey all!

I have a problem. On Sql server 7.0 I have a database with about 250 tables. This database is on 5 different servers. What I have to do is combine all the 5 databases on the 5 servers into one database on a sixth server. In addition, 80 of the 250 tables have to be filtered before going into the sixth server. All a unique column must be added to each table in the database before is it copied to the final database.

I have tired this with replication, but say an additionaly table is added to one of the 5 databases. The replication wont pik this table up. I ahve tried DTS packages (Export/Import) but that become unmanagable with 250 tables in the package. I'm new to this but this is very frustrating. Please help.Ummm..sounds like a full time job...

Coordination alone...

Who uses this "database"...

(How is one db distributed over multiple servers btw)|||Do you need to keep the 5 original database or the 5 servers or have you the option of just bringing all 5 servers + their tables into 1 server/database ?|||Originally posted by mtracey
Do you need to keep the 5 original database or the 5 servers or have you the option of just bringing all 5 servers + their tables into 1 server/database ?

I have to get the data from the 5 database into 1 but i cant change the data on the 5 databases (they remain untouched). This database is used for consolidated reporting purposes.|||I don't think that there are any easy solutions to this. You've identified DTS or Replication. Replication may be easier, however as you've pointed out if a new table is added to one of the databases, you will have to setup replication for it. I guess though you would have to do the same for DTS. Its whichever you're more comfortable with. Either way you'll have to have a strict change management process in place.

If I had to choose between the 2, then replication would be the way to go.

We have a reporting server, pulling data from 3 SQL Servers + 1 AS/400, using a combination of Replication, DTS + Datamirror.
Fun huh ;-)

Datamirror is very expensive btw.

Mark|||I tested this process using replication but I have to add a hostname unique column to all the tables and for a few tables i have to filter the tables so only a selected recrods are replicated. I used the snapshot pulling replication method. Are ne tips for the replication?

For DTS packages i'm using the xport/import wizard to do the transfer.

It seems like i'm going in circles with this problem

How to do this Query ?

Hello Everyone,

I have 2 sampel tables something like this :

Table : UserInfo

Email - PK

Name

Address

Table : UserRegistration

Email - FK

Course

Sample Data for UserInfo

harsimrat@......... , Harsimrat Thukral ,Some Street

data for UserRegistration

harsimrat@........ , CS 101

harsimrat@........,CS 201

harsimrat@.........,,CS301

harsimrat@........, CS401

Now If I say something like this

SELECT * FROM UserInfo, UserRegistration

WHERE Email = 'hthukral@.......'

It returns me 4 rows with repeated data, which is correct ....But I want to have somthing like this:

Columns : Name, Address, Email, Course1, Course 2, Course 3, Course 4

There can only be maximum of 4 courses, How can I get the desired results ?

Thanks,

Harsimrat Thukral

Harsimrat:

You ought to be able to use ROW_NUMBER() and either MAX / CASE or ROW_NUMBER() and PIVOT to get what you want. I am assuming here that you are using SQL Server 2005 and not SQL Server 2000; please verify which version you are using.

An example of a pivot version:

Code Snippet

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select EMail,
row_number() over
( partition by EMail order by Course
) as Seq,
Course
from ( select 'harsimrat@.' as EMail, 'CS101' as Course union all
select 'harsimrat@.', 'CS201' union all
select 'harsimrat@.', 'CS301' union all
select 'Harsimrat@.', 'CS401'
) baseTable
) a
pivot( max(Course) for seq in ([1],[2],[3],[4])
) pv

/*
Email course1 course2 course3 course4
- - - - -
harsimrat@. CS101 CS201 CS301 CS401
*/

The part in red is a derived table that I used instead of creating a table, populating the table and then using the table. For your query simply substitute

Code Snippet

from userRegistration

You will also need to join to your other table as appropriate.

|||

Sorry, the above query worked, but I was wondering how to do a little imporvement.

The User Registration Table has another column which tells how the Courses are numbered, when user saves the, it is saved as which course is Course 1 and Course 2 and so on...But when I execute the above query Course 1 is differnt then what it is originally saved.

This is the actual table :

Table : UserRegistration

Email - FK

Course

Number New Column

data for UserRegistration

harsimrat@........ , CS 101, 1

harsimrat@........,CS 201, 2

harsimrat@.........,,CS301, 3

harsimrat@........, CS401, 4

Now how can I use the new column and form the query and still get the desired results....

Thanks for the help.

Harsimrat

|||

Well, I would suppose that you would have some form of control table to define the structure of the report:

create table reportControl
(
course char(5),
seq int
)
insert into reportControl
select 'CS101',1
union all
select 'CS201',2
union all
select 'CS301',3
union all
select 'CS401',4
go

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select baseTable.EMail,
reportControl.Seq,
reportControl.Course
from ( select 'harsimrat@.' as EMail, 'CS101' as Course
union all
select 'harsimrat@.', 'CS201'
union all
select 'harsimrat@.', 'CS301'
union all
select 'Harsimrat@.', 'CS401') as baseTable
join reportControl
on baseTable.course = reportControl.course
) a
pivot( max(Course) for Seq in ([1],[2],[3],[4])
) pv

How to do this Query ?

Hello Everyone,

I have 2 sampel tables something like this :

Table : UserInfo

Email - PK

Name

Address

Table : UserRegistration

Email - FK

Course

Sample Data for UserInfo

harsimrat@......... , Harsimrat Thukral ,Some Street

data for UserRegistration

harsimrat@........ , CS 101

harsimrat@........,CS 201

harsimrat@.........,,CS301

harsimrat@........, CS401

Now If I say something like this

SELECT * FROM UserInfo, UserRegistration

WHERE Email = 'hthukral@.......'

It returns me 4 rows with repeated data, which is correct ....But I want to have somthing like this:

Columns : Name, Address, Email, Course1, Course 2, Course 3, Course 4

There can only be maximum of 4 courses, How can I get the desired results ?

Thanks,

Harsimrat Thukral

Harsimrat:

You ought to be able to use ROW_NUMBER() and either MAX / CASE or ROW_NUMBER() and PIVOT to get what you want. I am assuming here that you are using SQL Server 2005 and not SQL Server 2000; please verify which version you are using.

An example of a pivot version:

Code Snippet

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select EMail,
row_number() over
( partition by EMail order by Course
) as Seq,
Course
from ( select 'harsimrat@.' as EMail, 'CS101' as Course union all
select 'harsimrat@.', 'CS201' union all
select 'harsimrat@.', 'CS301' union all
select 'Harsimrat@.', 'CS401'
) baseTable
) a
pivot( max(Course) for seq in ([1],[2],[3],[4])
) pv

/*
Email course1 course2 course3 course4
- - - - -
harsimrat@. CS101 CS201 CS301 CS401
*/

The part in red is a derived table that I used instead of creating a table, populating the table and then using the table. For your query simply substitute

Code Snippet

from userRegistration

You will also need to join to your other table as appropriate.

|||

Sorry, the above query worked, but I was wondering how to do a little imporvement.

The User Registration Table has another column which tells how the Courses are numbered, when user saves the, it is saved as which course is Course 1 and Course 2 and so on...But when I execute the above query Course 1 is differnt then what it is originally saved.

This is the actual table :

Table : UserRegistration

Email - FK

Course

Number New Column

data for UserRegistration

harsimrat@........ , CS 101, 1

harsimrat@........,CS 201, 2

harsimrat@.........,,CS301, 3

harsimrat@........, CS401, 4

Now how can I use the new column and form the query and still get the desired results....

Thanks for the help.

Harsimrat

|||

Well, I would suppose that you would have some form of control table to define the structure of the report:

create table reportControl
(
course char(5),
seq int
)
insert into reportControl
select 'CS101',1
union all
select 'CS201',2
union all
select 'CS301',3
union all
select 'CS401',4
go

select Email,
[1] as course1,
[2] as course2,
[3] as course3,
[4] as course4
from ( select baseTable.EMail,
reportControl.Seq,
reportControl.Course
from ( select 'harsimrat@.' as EMail, 'CS101' as Course
union all
select 'harsimrat@.', 'CS201'
union all
select 'harsimrat@.', 'CS301'
union all
select 'Harsimrat@.', 'CS401') as baseTable
join reportControl
on baseTable.course = reportControl.course
) a
pivot( max(Course) for Seq in ([1],[2],[3],[4])
) pv

How to do this calculation?

I have 3 tables , these table contain field as follow

- Dim_Product

- DecVolume

- Dim_Category

- DecVolume

- Fact_SalesTrx

- DecUnitPrice

If i would like to calculate a Price Per Unit with this formula, how should i do it?

Price Per Unit for specific Unit of Measurement (UOM)= (Unit Price/ UOM for that product) * UOM for that Category

I had try to do in the Calculation tab of the cube, but the result generate is weird (= Infinity).

This is the script that i had try

--

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Price Per Unit]

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume])*[Dim Category].[Dec Volume],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Dec Unit Price] },

VISIBLE = 1 ;

--

Appreciate some one can leave some clue here for me to go on...

Thanks

Woon How

You can do that inside datasourceview as named calculation!

NamedCalculation_PricePerUnit = (UnitPrice/DecVolume)*DecVolume

Try it.

Helped?

regards!

|||

Assuming that the "Value Column" Source and DataType have been correctly configured for the [Dim Product].[Dec Volume] and [Dim Category].[Dec Volume] attributes, you could try .MemberValue, like:

Code Snippet

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume].MemberValue)

* [Dim Category].[Dec Volume].MemberValue,

|||

Thanks for your hlp

Do you mean:

"Value Column" - mean insert some data in? (I'm had put some value for this column)

"Data Type: - mean set the data type ? )(I'm created it in design and set it to decimal)

I try had you approach and result return "#VALUE!". and i highlight the cell, it mention type mismatch.

Any idea?

Thanks In Advance Deepak Puri..

|||

Thanks PedroCGD,

If doing in this way, i would need to create another 2 columns into the Fact_SalesTrx Tables (decVolume Of Product,DecVolume Of Category)

Would that be a redundacy? since i had the columns in the Dim_Product, Dim_Category. And what should be the best way to design this, sorry im new in SSAS, so a lot of stuff im not sure.

|||

It's easier to illustrate this with a sample Adventure Works query - note that the .MemberValue of [Promotion].[Min Quantity] and [Product].[Reorder Point] attributes is only meaningful at the appropriate granularity of those dimensions:

Code Snippet

With

Member [Measures].[WeightedQuantity] as

iif(IsEmpty([Measures].[Order Quantity]) or

[Promotion].[Min Quantity].MemberValue = 0,

Null,

([Measures].[Order Quantity] /

[Product].[Reorder Point].MemberValue)

* [Promotion].[Min Quantity].MemberValue),

FORMAT_STRING = '#,0.0'

select

{[Measures].[WeightedQuantity]} on 0,

Non Empty [Product].[Product].[Product]

* [Promotion].[Promotion].[Promotion] on 1

from [Adventure Works]

How to do these joins

Pardon my newbieness.
I have four tables to join. I'll just call them Table1, Table2, Table3
and Table4.
Table1 is the main table. I want to do outer joins pointing from fields
in Table1 to fields in each of the other tables. So all records in
Table1 regardless of whether they match in the other tables.
todSelect t1.* from Table1 t1
LEFT JOIN Table2 t2
ON t1.SomeiD = t2.someID
LEFT JOIN Table3 t3
ON t1.SomeiD = t3.someID
...
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--
"Tod" <todtown@.swbell.net> schrieb im Newsbeitrag
news:1115757100.280539.176950@.o13g2000cwo.googlegroups.com...
> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||select * from
Table1 left join Table2 on Table1.fld1 = Table2.fld1
left join Table3 on Table1.fld1 = Table3.fld1
left join Table4 on Table1.fld1 = Table4.fld1
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join... (and
then the third Join...)
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>

How to do search on two or three data tables at once!

Hi
Looking at the vb.net code below how can I modify the code so that when using SQL Servers
Full Text Search it will search two or three data tables at once Jobs Jobs2 and jobs3 all
tables are in the same database aspnetjobs and share the same job_id column. I can perform a search on one table but not two. Please look
at the vb.net code below and help if you can.

thanks!

sub button_click(s as object, e as eventargs)

Dim conaspnetjobs as sqlconnection
Dim strsearch as string
Dim cmdsearch as sqlcommand
Dim dtrsearch as sqldatareader

conaspnetjobs = new sqlconnection ( "server=mark\mark;uid=sa;pwd=clr_fcl;database=aspnetjobs" )

strsearch ="select job_fulldesc from Jobs, jobs2 where freetext( job_fulldesc, @.searchphrase )"
cmdsearch = new sqlcommand( strsearch, conaspnetjobs )
cmdsearch.parameters.add( "@.searchphrase", txtsearchphrase.text )

conaspnetjobs.open()
dtrsearch = cmdsearch.executereader()

while dtrsearch.read
lblresults.text &= "<li><br>" & dtrsearch( "job_fulldesc" )
end while

conaspnetjobs.close
end subHi,

Can't you simply join the other job tables in you query?

select job_fulldesc from Jobs a join jobs2 b on a.job_id = b.job_id where freetext( job_fulldesc, @.searchphrase )"

:-D

JB|||I think you need a UNION ALL. This will search each of the tables and combine the results into one resultset. I added a "source" column so you'd know which table the match come from; you may not need this.

"select job_fulldesc from Jobs, source="Jobs" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs2, source="Jobs2" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs3, source="Jobs3" where freetext( job_fulldesc, @.searchphrase )"

Terri|||Hi
I tryed the UNION ALL as above and recieved error message "UNION" is not declared!

please help!|||Something ran amok with the code I suggested. No wonder it didn't work. This is what I meant:

"select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

I tried this out by creating a duplicate of Products called Products2 on Northwind, and then creating a full text index on the ProductName field. Then I ran this successfully:

DECLARE @.searchphrase varchar(20)
SET @.searchphrase = 'Ale'
SELECT productname, source='Jobs' FROM products WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs2' FROM products2 WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs3' FROM products WHERE FREETEXT(productname, @.searchphrase )

|||I get this error message "statement is not valid inside a method"

Declare @.sreachphrase varchar(20 )

Please Help!|||That bottom block of code I posted was strictly for Query Analyzer use as proof of concept, not to copy and paste into your ASP.NET page.

This block of code is what should work for you:

strsearch ="select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

Terri