A feature available in oracle, is it available in sql server?

Theres a feature in oracle that allows you to modify tables, colums, values and the data from its enterprise console the same way that you can in sql server. In oracle however theres a button called 'show sql' that allows you to see and copy/paste the resulting sql for the changes made via the console.

I would imagine that sql server has a similar option. The reason i ask is that i would like to more fully learn how to do this through the query analyser and get more familiar with sql involved and I would be able to do this if I could see the resulting sql from enterprise manager.

Hope this makes sense.

I did find something in sql server called 'generate sql' but this doesnt update during changes you make automatically.

Thanksyeah if you have downloaded ther BOL ( which you should) look for ALTER TABLE key word. you can add columns, drop 'em modify 'em etc.

hth|||Thanks for the reply. But, whats the BOL. And what are you talking about?!>?! This doesnt answer my question. I'm talking about the ability to see the resulting sql when modifying it in the enterprise console.|||What you are looking for is called "Save Change Script".

If you are in Enterprise Manager, right click on a table name, and choose "Design". This will take you into the Design Table interface. If you hover over the 3rd icon from the left you will see that it says "save change script" (note that you actually have to make a change in order for this to become active). If you click on this you will see the exact commands that EM is going to execute to accomomdate your changes, and you can opt to save them to disk.

Also, BOL is Books Online, an invaluable free SQL Server reference from Microsoft. It is a huge download but well worth it. You can find it here:SQL Server 2000 Books Online (Updated 2004).

Terri

a fcuntion to compare two tables

I need a function witch compares two tables.
can some one help me ?Hi
SELECT OneTable.*, TwoTable.*
FROM OneTable
FULL OUTER JOIN
TwoTable
ON OneTable.c1 = TwoTable.c1
AND OneTable.c2 = TwoTable.c2
...
AND OneTable.cn = TwoTable.cn
WHERE OneTable.key IS NULL
OR TwoTable.key IS NULL;
"olli_d" <info@.dithmer.de> wrote in message
news:1193241328.829023.299090@.y27g2000pre.googlegroups.com...
>I need a function witch compares two tables.
> can some one help me ?
>

a Distinct Query

I have 2 tables as following :

tbl_Articles
3 ArticleID int
0 AuthorID int
0 ArticleTitle
0 ArticleText
0 ArticleDate

tbl_Authors
3 AuthorID
0 AuthorFullName
0 AuthorEmail
0 AuthorDescription
0 AuthorImage

I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate

If anyone knows the solution i will be glad .
Thanks from now onselect AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
from tbl_Authors a
inner join (
select AuthorID, aDate = max(ArticleDate)
from tbl_Articles) x
on a.AuthorID = x.AuthorID
inner join tbl_Articles b
on x.AuthorID = b.AuthorID
and x.aDate = b.ArticleDate|||another version:select AuthorImage
, AuthorFullName
, ArticleTitle
, ArticleDate
from tbl_Authors AUTH
inner
join tbl_Articles ART
on AUTH.AuthorID
= ART.AuthorID
where ART.ArticleDate
= ( select max(ArticleDate)
from tbl_Articles
where AuthorID
= AUTH.AuthorID )

A Database without a name

Hi
By mistake I ran a SQL Script which was not completed successfully. The
script should create a database, and subsequently create tables, views etc.
The bug in the script was quite obvious, but the database was created -
without a name, and without any tables (including system tables). So - in the
server's list of databases, there is a complete empty one - again without a
name.
I cannot delete it. An attempt result in following error:
21776:[SQL-DMO] The name '' was not found in the database collection..
How can I get rid of this empty database?
Anders
How do you know there is are system tables? Have you gotten into the
database?
Can you try changing the name and then deleting it?
What version are you running?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views
> etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in
> the
> server's list of databases, there is a complete empty one - again without
> a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders
|||Just to add to what Tibor said, you can create a database whose name appears
to be empty but not. For instance, you can run the following statement:
create database [ ] -- with a blank inside the brackets
I'd try the following to see what physical files the database is using:
select name, filename from master..sysaltfiles
Linchi
"Anders Balslev" wrote:

> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in the
> server's list of databases, there is a complete empty one - again without a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders
|||Hi Tibor
I did as you told, and one of the rows affected was
**
(no space between the asterix)
b.t.w. I didnt tell that it was SQL Server 2000
I presume that I can't just delete it manually from the
master.dbo.sysdatabases
Anders
"Tibor Karaszi" wrote:

> I suggest you first determine whether the database is truly without name or if the name is a blank
> (space) character or similar. Please remember to post version of SQL Server. I assume 2000 (due to
> the DMO error). Here's what I would start with:
> SELECT '*' + name + '*' FROM master.dbo.sysdatabases
> See if you find your database and whether there is anything inside the asterisks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
> news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
>
|||Take a look here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351489&SiteID=1
...I was going to suggest "DROP DATABASE []", but it looks like there are a
few options on this page, one of which was reported to have worked.
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views
> etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in
> the
> server's list of databases, there is a complete empty one - again without
> a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders
|||No, you can't delete it manually because that won't get rid of all the
associated information, but what you can do in SQL 2000 (but not in 2005) is
to manually update the system table sysdatabases to give it a name, and then
you should be able to get rid of it.
Did you try changing the name as I suggested from the EM tool? What
happened?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:C9214C27-9448-41EB-9C39-3ED346EFDDF8@.microsoft.com...[vbcol=seagreen]
> Hi Tibor
> I did as you told, and one of the rows affected was
> **
> (no space between the asterix)
> b.t.w. I didnt tell that it was SQL Server 2000
> I presume that I can't just delete it manually from the
> master.dbo.sysdatabases
> Anders
> "Tibor Karaszi" wrote:

A Database without a name

Hi
By mistake I ran a SQL Script which was not completed successfully. The
script should create a database, and subsequently create tables, views etc.
The bug in the script was quite obvious, but the database was created -
without a name, and without any tables (including system tables). So - in the
server's list of databases, there is a complete empty one - again without a
name.
I cannot delete it. An attempt result in following error:
21776:[SQL-DMO] The name '' was not found in the database collection..
How can I get rid of this empty database?
AndersHow do you know there is are system tables? Have you gotten into the
database?
Can you try changing the name and then deleting it?
What version are you running?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views
> etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in
> the
> server's list of databases, there is a complete empty one - again without
> a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders|||I suggest you first determine whether the database is truly without name or if the name is a blank
(space) character or similar. Please remember to post version of SQL Server. I assume 2000 (due to
the DMO error). Here's what I would start with:
SELECT '*' + name + '*' FROM master.dbo.sysdatabases
See if you find your database and whether there is anything inside the asterisks.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in the
> server's list of databases, there is a complete empty one - again without a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders|||Just to add to what Tibor said, you can create a database whose name appears
to be empty but not. For instance, you can run the following statement:
create database [ ] -- with a blank inside the brackets
I'd try the following to see what physical files the database is using:
select name, filename from master..sysaltfiles
Linchi
"Anders Balslev" wrote:
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in the
> server's list of databases, there is a complete empty one - again without a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders|||Hi Tibor
I did as you told, and one of the rows affected was
**
(no space between the asterix)
b.t.w. I didnt tell that it was SQL Server 2000
I presume that I can't just delete it manually from the
master.dbo.sysdatabases
Anders
"Tibor Karaszi" wrote:
> I suggest you first determine whether the database is truly without name or if the name is a blank
> (space) character or similar. Please remember to post version of SQL Server. I assume 2000 (due to
> the DMO error). Here's what I would start with:
> SELECT '*' + name + '*' FROM master.dbo.sysdatabases
> See if you find your database and whether there is anything inside the asterisks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
> news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> > Hi
> >
> > By mistake I ran a SQL Script which was not completed successfully. The
> > script should create a database, and subsequently create tables, views etc.
> > The bug in the script was quite obvious, but the database was created -
> > without a name, and without any tables (including system tables). So - in the
> > server's list of databases, there is a complete empty one - again without a
> > name.
> > I cannot delete it. An attempt result in following error:
> > 21776:[SQL-DMO] The name '' was not found in the database collection..
> > How can I get rid of this empty database?
> > Anders
>|||Take a look here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351489&SiteID=1
...I was going to suggest "DROP DATABASE []", but it looks like there are a
few options on this page, one of which was reported to have worked. :)
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
> Hi
> By mistake I ran a SQL Script which was not completed successfully. The
> script should create a database, and subsequently create tables, views
> etc.
> The bug in the script was quite obvious, but the database was created -
> without a name, and without any tables (including system tables). So - in
> the
> server's list of databases, there is a complete empty one - again without
> a
> name.
> I cannot delete it. An attempt result in following error:
> 21776:[SQL-DMO] The name '' was not found in the database collection..
> How can I get rid of this empty database?
> Anders|||No, you can't delete it manually because that won't get rid of all the
associated information, but what you can do in SQL 2000 (but not in 2005) is
to manually update the system table sysdatabases to give it a name, and then
you should be able to get rid of it.
Did you try changing the name as I suggested from the EM tool? What
happened?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in message
news:C9214C27-9448-41EB-9C39-3ED346EFDDF8@.microsoft.com...
> Hi Tibor
> I did as you told, and one of the rows affected was
> **
> (no space between the asterix)
> b.t.w. I didnt tell that it was SQL Server 2000
> I presume that I can't just delete it manually from the
> master.dbo.sysdatabases
> Anders
> "Tibor Karaszi" wrote:
>> I suggest you first determine whether the database is truly without name
>> or if the name is a blank
>> (space) character or similar. Please remember to post version of SQL
>> Server. I assume 2000 (due to
>> the DMO error). Here's what I would start with:
>> SELECT '*' + name + '*' FROM master.dbo.sysdatabases
>> See if you find your database and whether there is anything inside the
>> asterisks.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Anders Balslev" <AndersBalslev@.discussions.microsoft.com> wrote in
>> message
>> news:B4F5FB8B-4191-4E2D-8E19-F1FF84ABD636@.microsoft.com...
>> > Hi
>> >
>> > By mistake I ran a SQL Script which was not completed successfully. The
>> > script should create a database, and subsequently create tables, views
>> > etc.
>> > The bug in the script was quite obvious, but the database was created -
>> > without a name, and without any tables (including system tables). So -
>> > in the
>> > server's list of databases, there is a complete empty one - again
>> > without a
>> > name.
>> > I cannot delete it. An attempt result in following error:
>> > 21776:[SQL-DMO] The name '' was not found in the database collection..
>> > How can I get rid of this empty database?
>> > Anders
>>

A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

sql

A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component

Hi all

I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.

Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?

(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.

Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||

Thanks Phil for the quick response..

I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)

So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?

Guess this is a big question but Im sure it'll help a lot of developers.

thanks

Ravi

|||

If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.

If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.

|||

Hi Darren, I think u didnt get my problem right?

I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..

A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP

A curious case of data corruption

Dear group,

if someone could give me an idea what is going on in one of our
databases, this would really really be helpful.

We have two tables with around 2 / 3 million rows. These tables have no
key and no ID. (This major design flaw will be overcome in some later
version of the application-software working on this DB but right now i
have to live with this).

Now for the funny bit

1) I open one window in the Query-Analyzer and write some code like
Begin transaction INSERT INTO TABLE COMMIT
2) in another window i write "SELECT COUNT(*) from TABLE"

If I perform the insert then afterwards select count(*) the row-count
is incremented by two whereas the Insert-Statement said "1 row(s)
modified.

DBCC gives no errors.
DBCC gives amount of rows 2 million rows
Select count(*) on the same table gives 3 million rows

Exporting the data, truncating the table re-importing data gives no
result, right now the DTS-status is 203 and the machine is "thinking".

Is there any possibility to check the "integrity" of the table?

This problem is on the production machine, but right now i am working
on a copy so it was propagated with backup / restore-mechanism.

Any hint would be very helpful

Thanks and Greetings

Uli(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(uli2003wien@.lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
> Now for the funny bit
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||
Erland Sommarskog schrieb:
> (uli2003wien@.lycos.at) writes:
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> > Now for the funny bit
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli|||(uli2003wien@.lycos.at) writes:
> as always a great help and a hint for the right direction. Actually
> this table had already a clustered index but dropping the index and
> recreating the index did the job for me (and much faster than
> exporting, dropping and importing the table)

That's good to hear. I would keep an eye on the table, in case the
problem would reappear.

By the way, rather than dropping and recreating, DBCC DBREINDEX can
be somewhat quicker. You can also use WITH DROP_EXISTING on CREATE INDEX.

This is particularly important if you have non-clustered indexes on the
table as well, as they will have to be rebuilt if you drop the clustered
index. This is because the NC indexes use the clustered index keys as
their row locator.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

A Challenging Query

Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:

>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.

>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

A basic sql query problem

Suppose we get two following tables:

Table A:
[USER_ID] [varchar] (11) NOT NULL ,
[COURSE_ID] [varchar] (11) NOT NULL ,

Table B:

[COURSE_ID] [varchar] (11) NOT NULL ,
[COURSE_NAME] [varchar] (50) NOT NULL ,
[COURSE_NO] [varchar] (15) NULL ,
[BEGIN_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[CREATER] [varchar] (11) NOT NULL ,

and during the execution of my program, I can get the current use's id (USER_ID), sayU0001.

How can I retrieve the result set containing[COURSE_NAME],[COURSE_ID],but the current user's id (U0001) have Not been assigned in Table A.

Thanks in advance.

Ricky.

if u know what the course will be taken by the userid U0001 then just query from table B and insert the data of user in the table A.say u want to assign the user U0001 to "Eng01" query with select course_id,course_name from tableB where course_name ='Eng01' and then assign this course_id and user_id to tableA...it is not clear to me that what u wants to do ?|||

Are you talking about joining between two table?

If yes, try SELECT TableA.User_ID, TableB.CourseID, TableB.Course_Name FROM TableB LEFT OUTER JOIN TableA ON TableA.CourseID = TableB.CourseID

I haven't test it yet, but try searching for "LEFT OUTER JOIN" if it doesn't work.

Hope that help.

|||

Sorry for my unclear description of my question first.

And nr2ea, you provide me helpful hint.

I have one more question: is there any difference between LEFT JOIN and LEFT OUTER JOIN?

Ricky.

A basic design question

When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?

Then if there is a lookup table on the many side how should it be set?

I generally set up the PK-FK constraints and write my own DELETE statements rather than rely on CASCADE Delete. That way I always know the flow of how the data will/should be deleted.|||I was thinking that too. But I was also thinking about how SQL Server will overwrite my thinking if the cascade is not set properly.|||

Hi Jack,

I would suggest to set Update to CASCADE, because the change to the lookup table will be cascaded to the child table.

It does not make difference whether Insert is set to CASCADE or NO ACTION.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

6GB msdbdata.mdf

Hi all,

On my PC I noticed that my msdbdata.mdf file grew to over 6GB. I did sp_spaceused on each of the tables and they are all OK. I looked at the queues and they also seemed OK. I finally looked at the properties and seen that it cannot be shrink since it is 95 percent used.

Any ideas on how I can get it back to normal size?

Thanks

Avi

you may be knowing that msdb database is used SQL SERVER AGENT ( SQL Scheduller) by for all scheduled work in sql server instance. You backup history, DTS Versions, Job history related table may grow over a period of time and there by your MSDB Database can grow. check all these table and if not required delete the history.

Madhu

|||

Have you changed the amount of job history to keep in the SQL Agent? Do you have lots of DTS packages? Are the DTS packages setup to log there status to the SQL Server?

These things all take up space in the msdb database.

You can use the sp_dump_dtslog_all command to remove the DTS log information.

You can use the sys.destination_data_spaces command to remove SQL Agent Log Information.

5 Table Join

I am trying to join 5 tables in a sql server 2k db. Does anyone know of a good set of guidelines for doing this? Alternately, could someone find the problem in the following query?

The query that I am using is listed here (please let me know if I am violating any programming guidelines on this):

SELECT p.ParticipantID, pr.Age, ir.FnlTime, e.EventDate
FROM Participant p INNER JOIN PartRace pr ON p.ParticipantID = pr.ParticipantID
JOIN IndResults ir ON pr.ParticipantID = ir.ParticipantID
JOIN RaceData rd ON ir.RaceID = rd.RaceID
JOIN Events e ON e.EventID = rd.EventID
WHERE rd.Dist = '5_km' AND p.Gender = 'm' AND ir.FnlTime <> '00:00' AND e.EventGrp = 1
ORDER BY ir.ParticipantID

The problem that I am having is that if a participant shows up multiple times (which they could do since this is designed to get the performances for an event over a series of years) it does not associate the correct data from year to year. Basically some times show up where they shouldn't.http://www.dbforums.com/showthread.php?t=1196943|||Your code looks fine, apart from the gratuitous and obfuscatory use of aliases.

INSERT RIGHTEOUS AND INDIGNANT CANADIAN COMMENT FROM RUDY HERE
I don't understand what you mean by "does not associate the correct data from year to year". Do you need additional joins on the tables to link YEARs?
Also, what is the datatype of FnlTime? This clause: "...AND ir.FnlTime <> '00:00'" looks a little odd.|||Somewhere you need to be storing the date. Logical places might be the Race or Event tables. You need to include that date in the foreign key, which implies you need to carry it forward into the ON clauses.

-PatP|||Thanks to both of you. It is now fixed. The event date is in the events table. How would you use aliases?

Thanks!|||I don't, unless I am referencing a table twice. I think it makes code difficult to read because somebody trying to debug it has to look way down at the bottom of the statement to interpret the column and table references at the top of the statement.
The use of non-descriptive aliases takes you one giant step further away from self-documenting code. And when I am reviewing code I want to be able to conecntrate on what the code is doing, and not spend time trying to figure out what it is.|||Also, I try to use abreviations of the table names in the column names.
For example if you had a table with race tracks, the column for the first address field would be trkAddress1. If the table were for drivers, the column would be drvrAddress1. This way there is no confusion which address I was looking at.|||Ugh. So you have fields called SupplierCity in one table, EmployerCity in another, BusinessCity in another, etc? I certainly don't like that idea. The repetition of table names in column names is completely redundant if you qualify your column references, as you should.

For long-time supportable code, avoid mixing the logical structure of the database (names) with the physical structure (object type or location).|||Ugh. Having trkAddress1 and drvrAddress1, for example, also makes it easier for using Crystal Reports.|||Apply aliases in the output recordset.

And nothing makes Crystal Reports easy. It get a double-ugh.|||Sorry, I meant to say that it makes it easier for clients who use the database and crystal where they want to be able to create reports dynamically. I understand your point though and it is a valid one. I dislike Crystal Reports as well.

I like your signature by the way.

4 Table select statement?

Hello can anyone help me out with an sql statement.
I have an order table, which has 4 tables below it, which hold things that a
user can order.
Orders(table)
Then this order table above is linked to the 4 product tables.
ProductA(table)
ProductB(table)
ProductC(table)
ProductD(table)
each of the Product A to B tables have various different fields inside, but
they all share the following 3 fields: OrderID, ProductName, Pic
The product tables are linked by the OrderID field to the Orders Table.
I would like to to write a statement like this:
Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
ProductA.OrderID = Orders.OrderID and
ProductB.OrderID = Orders.OrderID and
ProductC.OrderID = Orders.OrderID and
ProductD.OrderID = Orders.OrderID ;
Is the above SQL statement Possible? right now it gives me lots of errors,
and i can't seem to get the right output from that statement, i just wone
non-repeating unique records from all 4 tables , so if i had 8 products in
this order, 2 in each of the product tables, then i want to display 8 record
s
only, right now if i play around this statement i get lots and lots of
duplicate records.ARTMIC wrote:
> Hello can anyone help me out with an sql statement.
> I have an order table, which has 4 tables below it, which hold things that
a
> user can order.
> Orders(table)
> Then this order table above is linked to the 4 product tables.
> ProductA(table)
> ProductB(table)
> ProductC(table)
> ProductD(table)
>
> each of the Product A to B tables have various different fields inside, bu
t
> they all share the following 3 fields: OrderID, ProductName, Pic
> The product tables are linked by the OrderID field to the Orders Table.
> I would like to to write a statement like this:
> Select ProductName, Pic from ProductA, ProductB, ProductC, ProductD where
> ProductA.OrderID = Orders.OrderID and
> ProductB.OrderID = Orders.OrderID and
> ProductC.OrderID = Orders.OrderID and
> ProductD.OrderID = Orders.OrderID ;
> Is the above SQL statement Possible? right now it gives me lots of errors,
> and i can't seem to get the right output from that statement, i just wone
> non-repeating unique records from all 4 tables , so if i had 8 products in
> this order, 2 in each of the product tables, then i want to display 8 reco
rds
> only, right now if i play around this statement i get lots and lots of
> duplicate records.
It seems like you want a UNION rather than a join. Here's a guess:
SELECT orderid, productname, pic
FROM
(SELECT A.orderid, A.productname, A.pic
FROM producta AS A
UNION ALL
SELECT B.orderid, B.productname, B.pic
FROM productb AS B
UNION ALL
SELECT C.orderid, C.productname, C.pic
FROM productc AS C
UNION ALL
SELECT D.orderid, D.productname, D.pic
FROM productd AS D) AS T
WHERE ... /* ? not specified */
ORDER BY orderid ;
This looks more than a little strange. Why orderid in the products
tables? And why isn't there a common products table for the common
columns? Maybe I've misunderstood, in which case the best way for you
to get more help is to post DDL and sample data and show your required
end results. DDL and sample data means CREATE TABLE statements for your
tables and INSERT statements for some example data (just the essential
columns will do). Don't forget to include keys and constraints with
your DDL.
Hope this helps.
David Portas
SQL Server MVP
--|||Hello David, thank you for the answer,
i will play with the joins that you mentioned,
i think i didn't express my question the right way, hard to explain,
the 4 product tables, are really orderDetail tables. So in reality ProductA
is really DetailA, DetailB, DetailC, DetailD which is linked to the order
table.
the reason for the above is becuase the uesr will only purchase 4 types of
things... and each thing has their own data fields. ( this setup is for a
manufactuerer)
It is a master-detail type of situation with Order header, and order detail,
but in my case, i have 4 order Detail tables, and i want to display all the
details from the 4 tables in one sql statement, at least the common fields..
.
I woudl have gone with a flat table for the detail table, but iwould have
had like 250+ fields in the table, that is too much to manage...so i split
the order-detail table into 4 distinct productsDetail tables... now i am
paying for it :)
i will play with your sql statements and hopefully it will get me over my
proplem, i will reply if it worked or not,
Thanks again !
"David Portas" wrote:

> ARTMIC wrote:
>
> It seems like you want a UNION rather than a join. Here's a guess:
> SELECT orderid, productname, pic
> FROM
> (SELECT A.orderid, A.productname, A.pic
> FROM producta AS A
> UNION ALL
> SELECT B.orderid, B.productname, B.pic
> FROM productb AS B
> UNION ALL
> SELECT C.orderid, C.productname, C.pic
> FROM productc AS C
> UNION ALL
> SELECT D.orderid, D.productname, D.pic
> FROM productd AS D) AS T
> WHERE ... /* ? not specified */
> ORDER BY orderid ;
> This looks more than a little strange. Why orderid in the products
> tables? And why isn't there a common products table for the common
> columns? Maybe I've misunderstood, in which case the best way for you
> to get more help is to post DDL and sample data and show your required
> end results. DDL and sample data means CREATE TABLE statements for your
> tables and INSERT statements for some example data (just the essential
> columns will do). Don't forget to include keys and constraints with
> your DDL.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||ARTMIC wrote:
> I woudl have gone with a flat table for the detail table, but iwould have
> had like 250+ fields in the table, that is too much to manage...so i split
> the order-detail table into 4 distinct productsDetail tables... now i am
> paying for it :)
>
I would prefer 5 tables. 4 for each unique product type. 1 for the
attributes that are common to all types (name for example).
David Portas
SQL Server MVP
--|||Thank you David,
you are right, i might as well just move the common fields into an
OrderDetail table and then link to the 4 OrderDetailProd tables.
Thanks,
"David Portas" wrote:

> ARTMIC wrote:
> I would prefer 5 tables. 4 for each unique product type. 1 for the
> attributes that are common to all types (name for example).
> --
> David Portas
> SQL Server MVP
> --
>

4 table query returns nothig when one is empty

My problem is as follows: I am trying to pull data from four tables where a one table may not have data. How do I write the query that retrieves all the data from the ETS_STUDENT, ETS_ENROLLMENT, ETS_SCHOOL_DATA tables reguardless of the data in ETS_INTERNAL. ie... If the there is data in ETS_INTERNAL include it, else not. But still print the data in the other three tables.

This is my current code.

SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, ETS_INTERNAL.OTHER_DESC
FROM ((ETS_STUDENT INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
WHERE (((ETS_STUDENT.ETS_ID)=[ETS_ENROLLMENT]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_SCHOOL_DATA]![ETS_ID] And (ETS_STUDENT.ETS_ID)=[ETS_INTERNAL]![ETS_ID]));

Thank you for your time.
MikeI figured it out. I created a sub query as follows then called the sub query from the main query.

Sub:

SELECT ETS_STUDENT.ETS_ID, ETS_INTERNAL.OTHER_DESC
FROM ETS_STUDENT LEFT JOIN ETS_INTERNAL ON ETS_STUDENT.ETS_ID = ETS_INTERNAL.ETS_ID;

Main:

SELECT ETS_STUDENT.LAST_NAME, ETS_STUDENT.FIRST_NAME, ETS_STUDENT.ETS_SCHOOL, ETS_STUDENT.ETS_GRADE, ETS_SCHOOL_DATA.ETS_SCHEDULE_BLOCK, ETS_SCHOOL_DATA.ETS_SCHEDULE_TEACHER, ETS_SCHOOL_DATA.ETS_SCHEDULE_ROOM, ETS_ENROLLMENT.ETS_PROGRAM_STATUS, [MKsPassesQuery(sub)].OTHER_DESC
FROM ((ETS_STUDENT INNER JOIN [MKsPassesQuery(sub)] ON (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID) AND (ETS_STUDENT.ETS_ID = [MKsPassesQuery(sub)].ETS_ID)) INNER JOIN ETS_ENROLLMENT ON ETS_STUDENT.ETS_ID = ETS_ENROLLMENT.ETS_ID) INNER JOIN ETS_SCHOOL_DATA ON ETS_STUDENT.ETS_ID = ETS_SCHOOL_DATA.ETS_ID
WHERE (((ETS_SCHOOL_DATA.ETS_ID)=[ETS_STUDENT]![ETS_ID]) AND ((ETS_ENROLLMENT.ETS_ID)=[ETS_STUDENT]![ETS_ID]));

3-way, 4-way, n-way full outer joins?

/*
NOTE: you can paste this all in QA
i want to perform a 3-way full outer join on 3 tables
(in reality it is against 3 views, but my sample DDL here
is tables).
i want the 3-way join to be Customer,Year,Month
Sample DDL*/
CREATE TABLE #SalesOrderStatistics (
Customer int,
Year int,
Month int,
SalesOrdersCount int)
CREATE TABLE #ProjectStatistics (
Customer int,
Year int,
Month int,
ProjectsCount int)
CREATE TABLE #QuoteStatistics (
Customer int,
Year int,
Month int,
QuotesCount int)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 1, 23)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 2, 59)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 3, 23)
INSERT INTO #SalesOrderStatistics (Customer, Year, Month, SalesOrdersCount)
VALUES (1, 2005, 4, 89)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 1, 23)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 2, 11)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 5, 74)
INSERT INTO #ProjectStatistics (Customer, Year, Month, ProjectsCount) VALUES
(1, 2005, 6, 38)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 1, 11)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 3, 23)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 5, 58)
INSERT INTO #QuoteStatistics (Customer, Year, Month, QuotesCount) VALUES (1,
2005, 7, 12)
/*
DesiredOutput:
Customer Year Month SalesOrders Projects Quotes
======== ==== ===== =========== ======== ======
1 2005 1 23 23 11
1 2005 2 59 11 NULL
1 2005 3 23 NULL 23
1 2005 4 89 NULL NULL
1 2005 5 NULL 74 58
1 2005 6 NULL 38 NULL
1 2005 7 NULL NULL 12
i got the following query, but is there a better say, specifically the
required use of COALESCE
*/
SELECT
COALESCE(s.Customer, p.Customer) AS Customer,
COALESCE(s.Year, p.Year) AS Year,
COALESCE(s.Month, p.Month) AS Month,
s.SalesOrdersCount AS SalesOrders,
p.ProjectsCount AS Projects
FROM #SalesOrderStatistics s
FULL OUTER JOIN #ProjectStatistics p
ON s.Customer = p.Customer
AND s.Year = p.Year
AND s.Month = p.Month
/*That returns two of them combined:
1 2005 1 23 23
1 2005 2 59 11
1 2005 5 NULL 74
1 2005 6 NULL 38
1 2005 4 89 NULL
1 2005 3 23 NULL
Now, if i want to bring in the 3rd table, the join becomes much uglier.
So i think there must be an easier way
*/
SELECT
COALESCE(s.Customer, p.Customer, q.Customer) AS Customer,
COALESCE(s.Year, p.Year, q.Year) AS Year,
COALESCE(s.Month, p.Month, q.Month) AS Month,
s.SalesOrdersCount AS SalesOrders,
p.ProjectsCount AS Projects,
q.QuotesCount AS Quotes
FROM #SalesOrderStatistics s
FULL OUTER JOIN #ProjectStatistics p
ON s.Customer = p.Customer
AND s.Year = p.Year
AND s.Month = p.Month
FULL OUTER JOIN #QuoteStatistics q
ON COALESCE(s.Customer, p.Customer) = q.Customer
AND COALESCE(s.Year, p.Year) = q.Year
AND COALESCE(s.Month, p.Month) = q.Month
/*This works:
1 2005 1 23 23 11
1 2005 2 59 11 NULL
1 2005 5 NULL 74 58
1 2005 6 NULL 38 NULL
1 2005 4 89 NULL NULL
1 2005 3 23 NULL 23
1 2005 7 NULL NULL 12
but i now have to perform a 3-way coalese, and joined the new table against
a coalesce'd value. What if i had to perform a 4-way full outer join,
would i have to keep on coalescing?
ANSI SQL must have thought of this case*/
DROP TABLE #SalesOrderStatistics
DROP TABLE #ProjectStatistics
DROP TABLE #QuoteStatisticsI would start with
select
...
from
(
select Customer, year, month from #SalesOrderStatistics
union
select Customer, year, month from #ProjectStatistics
union
select Customer, year, month from #QuoteStatistics
)all_rows
left outer join #SalesOrderStatistics s
ON all_rows.Customer = s.Customer
AND .Year = s.Year
AND all_rows.Month = s.Month
left outer join #ProjectStatistics p
ON all_rows.Customer = p.Customer
AND all_rows.Year = p.Year
AND all_rows.Month = p.Month
left outer join #QuoteStatistics q
ON all_rows.Customer = q.Customer
AND all_rows.Year = q.Year
AND all_rows.Month = q.Month|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:580388
On Thu, 26 Jan 2006 16:12:23 -0500, Ian Boyd wrote:
(snip)
>but i now have to perform a 3-way coalese, and joined the new table against
>a coalesce'd value. What if i had to perform a 4-way full outer join,
>would i have to keep on coalescing?
Hi Ian,
Yes :-)

>ANSI SQL must have thought of this case*/
That's probably the reason why COALESCE takes an unlimited number of
arguments :-) (Consider how it would look with ISNULL...)
I didn't run your code or even look at it in detail. There might be
alternative ways to get the expected results (Alexander already posted a
suggestion).
In the real world, full outer joins are rare. Threeway full outer joins
are even rarer, and I have never seen or needed a fourway full outer
join.
If you run into a situation where you need one, you might have to
reconsider your design. There might be a better solution.
Hugo Kornelis, SQL Server MVP

3-way joins

I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
ChrisThis is a multi-part message in MIME format.
--=_NextPart_000_0DBC_01C3B4CF.B3825940
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID = ph.HotelID
join
Locations as l on l.LocationID = ph.LocationID
where
l.Location = 'Grand Cayman'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_0DBC_01C3B4CF.B3825940
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you post your DDL, we can give a =better solution:
select
=ph.*
from
PrefHotels =as ph
join
Hotels as h =on h.HotelID =3D ph.HotelID
join
Locations as =l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"CJM" wrote in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various JOINs =and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_0DBC_01C3B4CF.B3825940--|||This is a multi-part message in MIME format.
--=_NextPart_000_006A_01C3B4FB.64A8F050
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom thanks for that... but once again I have been an idoit! For a change =I'm working in Access 2k!
Before I repost to an Access group, would you happen to know the =difference between Access and SQLServer - ie I tried this SQL in Access =but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23jGhfmPtDHA.3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID =3D ph.HotelID
join
Locations as l on l.LocationID =3D ph.LocationID
where
l.Location =3D 'Grand Cayman'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message =news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, =but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists =of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_006A_01C3B4FB.64A8F050
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Tom thanks for that... but once again I =have been an idoit! For a change I'm working in Access 2k!
Before I repost to an Access group, =would you happen to know the difference between Access and SQLServer - ie I tried =this SQL in Access but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" = wrote in message news:%23jGhfmPtDHA.=3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a =better solution:

select
=ph.*
from
PrefHotels =as ph
join
Hotels as =h on h.HotelID =3D ph.HotelID
join
Locations =as l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"CJM" wrote =in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various =JOINs and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_006A_01C3B4FB.64A8F050--|||This is a multi-part message in MIME format.
--=_NextPart_000_0E18_01C3B4D4.32C825F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Well, I haven't done any Access SQL in a long time. You could try changing
the JOIN to INNER JOIN, listing the columns explicitly, instead of ph.* and
using double quotes vs. single quotes.
You're probably best to take my query to the Access newsgroup and see what
you can get.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:udVDmtPtDHA.684@.TK2MSFTNGP09.phx.gbl...
Tom thanks for that... but once again I have been an idoit! For a change I'm
working in Access 2k!
Before I repost to an Access group, would you happen to know the difference
between Access and SQLServer - ie I tried this SQL in Access but it didn't
work - Syntax error of some sort...
Cheers
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23jGhfmPtDHA.3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a better solution:
select
ph.*
from
PrefHotels as ph
join
Hotels as h on h.HotelID = ph.HotelID
join
Locations as l on l.LocationID = ph.LocationID
where
l.Location = 'Grand Cayman'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:epgVifPtDHA.1512@.TK2MSFTNGP10.phx.gbl...
I'm afraid my brain is melting on this one... I've done this before, but
cant remember it now...
I have three tables: Location, Hotels, PrefHotels
Locations: A list of locations that people in my company travel to.
Hotels: A list of hotel chains, inc URL etc that we may use
PrefHotels: Identifies preferred hotels for each location - consists of
LocationID from 1st table, and HotelID from 2nd table.
In my query, I want to list the preferred hotels (chains) for a given
location...
I've tied myself in knots with various JOINs and sub-selects...
Can anyone point me in the right direction please..
Cheers
Chris
--=_NextPart_000_0E18_01C3B4D4.32C825F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Well, I haven't done any Access SQL in =a long time. You could try changing the JOIN to INNER JOIN, listing the =columns explicitly, instead of ph.* and using double quotes vs. single quotes.
You're probably best to take my query =to the Access newsgroup and see what you can get.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"CJM" wrote in message news:udVDmtPtDHA.684@.T=K2MSFTNGP09.phx.gbl...
Tom thanks for that... but once again I =have been an idoit! For a change I'm working in Access 2k!
Before I repost to an Access group, =would you happen to know the difference between Access and SQLServer - ie I tried =this SQL in Access but it didn't work - Syntax error of some sort...
Cheers
"Tom Moreau" = wrote in message news:%23jGhfmPtDHA.=3416@.tk2msftngp13.phx.gbl...
If you post your DDL, we can give a =better solution:

select
=ph.*
from
PrefHotels =as ph
join
Hotels as =h on h.HotelID =3D ph.HotelID
join
Locations =as l on l.LocationID =3D ph.LocationID
where
l.Location ==3D 'Grand Cayman'
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"CJM" wrote =in message news:epgVifPtDHA.1512=@.TK2MSFTNGP10.phx.gbl...I'm afraid my brain is melting on this one... I've done this before, =butcant remember it now...I have three tables: Location, Hotels, PrefHotelsLocations: A list of locations that people in my =company travel to.Hotels: A list of hotel chains, inc URL etc that we may usePrefHotels: Identifies preferred hotels for each location - =consists ofLocationID from 1st table, and HotelID from 2nd table.In =my query, I want to list the preferred hotels (chains) for a givenlocation...I've tied myself in knots with various =JOINs and sub-selects...Can anyone point me in the right direction please..CheersChris

---

--=_NextPart_000_0E18_01C3B4D4.32C825F0--|||Hi Cjmwork, Tom
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I noticed this issue is duplicated with one in
microsoft.public.sqlserver.programming. I think it is an issue of
programming and I will close this one here.
You can still discuss the problem in microsoft.public.sqlserver.programming
and thanks for using MSDN Newsgroup!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

3rd party software

Does anyone know any software to backup single tables ? i recall one software that extracts the data into a text file called SQLinsert or something but wondering if they are others around ?Rey SQLLiteSpeed from DBAssociates (I think it has been bought by IMCEDA).