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 big, big SSIS for everything?

Dear all,

I would like to hear point of views, thoughts and ideas about the following request.

As some of us would have seen from a couple a days ago I’ve been setting posts one after one in order to obtain some answers about our business basis.

We’ve got a service running all day long (to accomplish this it was wrote by Visual Basic 6.0) which is checking up a file structure system for a several requirements such a verify if exists a .nul or .txt in each folder and therefore fires a DTS package. Up to here that’s fine, that system works amazing and even is be able to run till three packages at the same time. 400 Dts are scheduled this way and 100 are scheduled by Agent.

Problem comes naturally with SQL2005 where such control is not possible (it seems so) from old tools belonging to Visual Studio 6.0 because the aforementioned version uses assemblies instead of dll, bla,bla.

Keeping in mind that .NET is not possible for reasons that going beyond of any technical matter or whatever I’ve thought do that service by SSIS. A big SSIS running forever. Its responsabilities might be:

1-Look in each folder for the criteria specified in the scheduling of that SSIS

2-Launch a specified SSIS in the case that criteria has matched.

3-Update SQL tables with LOGS, times, executions, etc,

4-Look again

The point 3 will be visible from our Intranet via ASP 3.0 pages as currently we do.

Pros and cons?

Sorry for this painful request but I don’t believe that such issue can be of that kind of odd issues that nodoby suffer in its organizations.

Who else is needed for answers?

Thanks a lot for your time,

I'm not quite sure what your question is. Are you saying you want to run a SSIS package forever that simply keeps looping whenever a file is found? I don't see a problem with that. it would be interesting to see how it goes.

-Jamie

|||

When finds a Jamie.Nul file in d:\server25k\a\ launch SSIS package B and then would delete that .NUL.

Sensible information attached in that SSIS launched as flat file for example, accounts info would be stored in a backup folder. After one execution come back to verify and so on

8K/page limit

Hi there,
Does the 8K/page (row) limit also involve views? We're trying to insert a
record into a view (via nested instead of insert triggers) and the query
results in:
Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line 1
String or binary data would be truncated.
The statement has been terminated.
The view is made up of tables of which none are exceeding the 8K limiet. The
query does not pass [n][var]char values bigger than defined on table level.
Any clues?
Thanks,
SLE"SLE" <info@.NOSPAM.dataworx.be> wrote...
> Does the 8K/page (row) limit also involve views? We're trying to insert a
> record into a view (via nested instead of insert triggers) and the query
> results in:
> Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line
1
> String or binary data would be truncated.
> The statement has been terminated.
> The view is made up of tables of which none are exceeding the 8K limiet.
The
> query does not pass [n][var]char values bigger than defined on table
level.
>
FYI:
Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
insert (and all columns are populated correctly) but we don't feel quite
comfortable yet :-|
I've checked books online: there are no aggregate functions involved, nor
binary or varbinary data, nor distributed queries.
Any thoughts?
Thanks,
SLE|||The error indicates that you _are_ passing character values that are larger
than the columns they are stored in. If the row size was the problem you
would get a different error message. Are you not accidentally passing a char
to a varchar column? I assume you have checked your code already, but the
only explanation I can give at this moment is that you have missed
something. If you're sure you haven't can you please post your code so we
can have a look through it?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:OqGhJqOrDHA.2688@.TK2MSFTNGP09.phx.gbl...
> "SLE" <info@.NOSPAM.dataworx.be> wrote...
> >
> > Does the 8K/page (row) limit also involve views? We're trying to insert
a
> > record into a view (via nested instead of insert triggers) and the query
> > results in:
> >
> > Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD,
Line
> 1
> > String or binary data would be truncated.
> > The statement has been terminated.
> >
> > The view is made up of tables of which none are exceeding the 8K limiet.
> The
> > query does not pass [n][var]char values bigger than defined on table
> level.
> >
> FYI:
> Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
> insert (and all columns are populated correctly) but we don't feel quite
> comfortable yet :-|
> I've checked books online: there are no aggregate functions involved, nor
> binary or varbinary data, nor distributed queries.
> Any thoughts?
> Thanks,
> SLE
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> The error indicates that you _are_ passing character values that are
larger
> than the columns they are stored in. If the row size was the problem you
> would get a different error message. Are you not accidentally passing a
char
> to a varchar column? I assume you have checked your code already, but the
> only explanation I can give at this moment is that you have missed
> something. If you're sure you haven't can you please post your code so we
> can have a look through it?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Okay, a bit lengthy, but here we go. From Profiler:
SET ANSI_WARNINGS OFF
exec sp_executesql
N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
[DriveIntegrity], [FileSystemFlags],
[FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
[MediaLoaded], [MediaType],
[MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
[SCSIPort], [SCSITargetId],
[Size], [TransferRate], [VolumeName], [VolumeSerialNumber], [Capabilities],
[CapabilityDescriptions],
[ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
[MaxMediaSize], [DefaultBlockSize],
[MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
[Security], [LastCleaned],
[MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
[MountCount], [TimeOfLastMount],
[TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning], [UnitsUsed],
[PowerManagementCapabilities],
[OtherIdentifyingInfo], [IdentifyingDescriptions], [AdditionalAvailability],
[SystemCreationClassName],
[SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
[Availability], [StatusInfo],
[LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
[TotalPowerOnHours], [MaxQuiesceTime],
[EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
[OperationalStatus],
[StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
[Description], [ElementName],
[LastModifiedBy], [ToArchive], [TimeOfCreation])
VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
@.FileSystemFlagsExValue, @.IdValue,
@.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
@.MfrAssignedRevisionLevelValue,
@.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
@.SCSITargetIdValue,
@.SizeValue, @.TransferRateValue, @.VolumeNameValue, @.VolumeSerialNumberValue,
@.CapabilitiesValue,
@.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
@.CompressionMethodValue,
@.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
@.MaxBlockSizeValue,
@.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
@.LastCleanedValue, @.MaxAccessTimeValue,
@.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
@.MountCountValue, @.TimeOfLastMountValue,
@.TotalMountTimeValue, @.UnitsDescriptionValue, @.MaxUnitsBeforeCleaningValue,
@.UnitsUsedValue,
@.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
@.IdentifyingDescriptionsValue,
@.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
@.SystemNameValue, @.CreationClassNameValue,
@.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
@.LastErrorCodeValue, @.ErrorDescriptionValue,
0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
@.EnabledStateValue,
@.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
@.OperationalStatusValue,
@.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
@.DescriptionValue,
@.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
int,@.FileSystemFlagsExValue bigint,
@.IdValue varchar(2),@.ManufacturerValue
varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue varchar(6),
@.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
varchar(8000),@.SCSIBusValue bigint,
@.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
int,@.SizeValue bigint,
@.TransferRateValue float,@.VolumeNameValue
varchar(8),@.VolumeSerialNumberValue varchar(8),
@.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
varchar(8000),@.ErrorMethodologyValue varchar(8000),
@.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
bigint,@.MaxMediaSizeValue bigint,
@.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
bigint,@.MediaIsLockedValue bigint,
@.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
bigint,@.UncompressedDataRateValue bigint,
@.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
bigint,@.TimeOfLastMountValue datetime,
@.TotalMountTimeValue bigint,@.UnitsDescriptionValue
varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
@.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
@.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
varchar(8000),
@.SystemCreationClassNameValue varchar(20),@.SystemNameValue
varchar(6),@.CreationClassNameValue varchar(16),
@.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
int,@.LastErrorCodeValue bigint,
@.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
bigint,@.TotalPowerOnHoursValue bigint,
@.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
varchar(8000),
@.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
varchar(8000),
@.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
varchar(2),
@.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
varchar(8000),
@.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
@.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue = 0,
@.FileSystemFlagsExValue = 0,
@.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
@.MaximumComponentLengthValue = 110,
@.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
@.RevisionLevelValue = '',
@.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
@.SCSITargetIdValue = 0,
@.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
@.VolumeNameValue = 'BTS2004B',
@.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
@.CapabilityDescriptionsValue = NULL,
@.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
@.NumberOfMediaSupportedValue = 0,
@.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue = 0,
@.MinBlockSizeValue = 0,
@.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue = NULL,
@.MaxAccessTimeValue = NULL,
@.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue =NULL, @.MountCountValue = NULL,
@.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
@.UnitsDescriptionValue = NULL,
@.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
@.PowerManagementCapabilitiesValue = NULL,
@.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
@.AdditionalAvailabilityValue = NULL,
@.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue ='WS9843',
@.CreationClassNameValue = 'Win32_CDROMDrive',
@.DeviceIDValue ='IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
0',
@.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
@.ErrorDescriptionValue = '',
@.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
@.MaxQuiesceTimeValue = NULL,
@.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
@.RequestedStateValue = NULL,
@.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
@.StatusDescriptionsValue = NULL,
@.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK', @.CaptionValue
= 'HL-DT-ST DVD-ROM GDR8161B',
@.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
@.LastModifiedByValue = 'DOSIM000\SIDLECS',
@.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
And these are the affected tables contained within the view (from bottom to
top):
CREATE TABLE [dbo].[Win32_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DriveIntegrity] [bit] NULL ,
[FileSystemFlags] [int] NULL ,
[FileSystemFlagsEx] [bigint] NULL ,
[Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaximumComponentLength] [bigint] NULL ,
[MediaLoaded] [bit] NULL ,
[MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
NULL ,
[RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[SCSIBus] [bigint] NULL ,
[SCSILogicalUnit] [int] NULL ,
[SCSIPort] [int] NULL ,
[SCSITargetId] [int] NULL ,
[Size] [bigint] NULL ,
[TransferRate] [float] NULL ,
[VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[NumberOfMediaSupported] [bigint] NULL ,
[MaxMediaSize] [bigint] NULL ,
[DefaultBlockSize] [bigint] NULL ,
[MaxBlockSize] [bigint] NULL ,
[MinBlockSize] [bigint] NULL ,
[NeedsCleaning] [bit] NULL ,
[MediaIsLocked] [bit] NULL ,
[Security] [int] NULL ,
[LastCleaned] [datetime] NULL ,
[MaxAccessTime] [bigint] NULL ,
[UncompressedDataRate] [bigint] NULL ,
[LoadTime] [bigint] NULL ,
[UnloadTime] [bigint] NULL ,
[MountCount] [bigint] NULL ,
[TimeOfLastMount] [datetime] NULL ,
[TotalMountTime] [bigint] NULL ,
[UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaxUnitsBeforeCleaning] [bigint] NULL ,
[UnitsUsed] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[PowerManagementCapabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL
,
[SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[PowerManagementSupported] [bit] NULL ,
[Availability] [int] NULL ,
[StatusInfo] [int] NULL ,
[LastErrorCode] [bigint] NULL ,
[ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ErrorCleared] [bit] NULL ,
[PowerOnHours] [bigint] NULL ,
[TotalPowerOnHours] [bigint] NULL ,
[MaxQuiesceTime] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[EnabledState] [int] NULL ,
[OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[RequestedState] [int] NULL ,
[EnabledDefault] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[InstallDate] [datetime] NULL ,
[Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[ToArchive] [bit] NULL ,
[TimeOfCreation] [datetime] NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]|||Can you also post the definition of and trigger on
DATAVIEW_Win32_CDROMDrive?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:%23J0t3FQrDHA.648@.TK2MSFTNGP11.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> > The error indicates that you _are_ passing character values that are
> larger
> > than the columns they are stored in. If the row size was the problem you
> > would get a different error message. Are you not accidentally passing a
> char
> > to a varchar column? I assume you have checked your code already, but
the
> > only explanation I can give at this moment is that you have missed
> > something. If you're sure you haven't can you please post your code so
we
> > can have a look through it?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> Okay, a bit lengthy, but here we go. From Profiler:
> SET ANSI_WARNINGS OFF
> exec sp_executesql
> N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
> [DriveIntegrity], [FileSystemFlags],
> [FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
> [MediaLoaded], [MediaType],
> [MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
> [SCSIPort], [SCSITargetId],
> [Size], [TransferRate], [VolumeName], [VolumeSerialNumber],
[Capabilities],
> [CapabilityDescriptions],
> [ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
> [MaxMediaSize], [DefaultBlockSize],
> [MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
> [Security], [LastCleaned],
> [MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
> [MountCount], [TimeOfLastMount],
> [TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning],
[UnitsUsed],
> [PowerManagementCapabilities],
> [OtherIdentifyingInfo], [IdentifyingDescriptions],
[AdditionalAvailability],
> [SystemCreationClassName],
> [SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
> [Availability], [StatusInfo],
> [LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
> [TotalPowerOnHours], [MaxQuiesceTime],
> [EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
> [OperationalStatus],
> [StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
> [Description], [ElementName],
> [LastModifiedBy], [ToArchive], [TimeOfCreation])
> VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
> @.FileSystemFlagsExValue, @.IdValue,
> @.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
> @.MfrAssignedRevisionLevelValue,
> @.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
> @.SCSITargetIdValue,
> @.SizeValue, @.TransferRateValue, @.VolumeNameValue,
@.VolumeSerialNumberValue,
> @.CapabilitiesValue,
> @.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
> @.CompressionMethodValue,
> @.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
> @.MaxBlockSizeValue,
> @.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
> @.LastCleanedValue, @.MaxAccessTimeValue,
> @.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
> @.MountCountValue, @.TimeOfLastMountValue,
> @.TotalMountTimeValue, @.UnitsDescriptionValue,
@.MaxUnitsBeforeCleaningValue,
> @.UnitsUsedValue,
> @.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
> @.IdentifyingDescriptionsValue,
> @.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
> @.SystemNameValue, @.CreationClassNameValue,
> @.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
> @.LastErrorCodeValue, @.ErrorDescriptionValue,
> 0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
> @.EnabledStateValue,
> @.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
> @.OperationalStatusValue,
> @.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
> @.DescriptionValue,
> @.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
> N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
> int,@.FileSystemFlagsExValue bigint,
> @.IdValue varchar(2),@.ManufacturerValue
> varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue
varchar(6),
> @.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
> varchar(8000),@.SCSIBusValue bigint,
> @.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
> int,@.SizeValue bigint,
> @.TransferRateValue float,@.VolumeNameValue
> varchar(8),@.VolumeSerialNumberValue varchar(8),
> @.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
> varchar(8000),@.ErrorMethodologyValue varchar(8000),
> @.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
> bigint,@.MaxMediaSizeValue bigint,
> @.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
> bigint,@.MediaIsLockedValue bigint,
> @.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
> bigint,@.UncompressedDataRateValue bigint,
> @.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
> bigint,@.TimeOfLastMountValue datetime,
> @.TotalMountTimeValue bigint,@.UnitsDescriptionValue
> varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
> @.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
> varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
> @.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
> varchar(8000),
> @.SystemCreationClassNameValue varchar(20),@.SystemNameValue
> varchar(6),@.CreationClassNameValue varchar(16),
> @.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
> int,@.LastErrorCodeValue bigint,
> @.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
> bigint,@.TotalPowerOnHoursValue bigint,
> @.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
> varchar(8000),
> @.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
> varchar(8000),
> @.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
> varchar(2),
> @.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
> varchar(8000),
> @.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
> @.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue =0,
> @.FileSystemFlagsExValue = 0,
> @.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
> @.MaximumComponentLengthValue = 110,
> @.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
> @.RevisionLevelValue = '',
> @.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
> @.SCSITargetIdValue = 0,
> @.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
> @.VolumeNameValue = 'BTS2004B',
> @.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
> @.CapabilityDescriptionsValue = NULL,
> @.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
> @.NumberOfMediaSupportedValue = 0,
> @.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue =0,
> @.MinBlockSizeValue = 0,
> @.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue =NULL,
> @.MaxAccessTimeValue = NULL,
> @.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue
=> NULL, @.MountCountValue = NULL,
> @.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
> @.UnitsDescriptionValue = NULL,
> @.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
> @.PowerManagementCapabilitiesValue = NULL,
> @.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
> @.AdditionalAvailabilityValue = NULL,
> @.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue => 'WS9843',
> @.CreationClassNameValue = 'Win32_CDROMDrive',
> @.DeviceIDValue =>
'IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
> 0',
> @.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
> @.ErrorDescriptionValue = '',
> @.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
> @.MaxQuiesceTimeValue = NULL,
> @.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
> @.RequestedStateValue = NULL,
> @.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
> @.StatusDescriptionsValue = NULL,
> @.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK',
@.CaptionValue
> = 'HL-DT-ST DVD-ROM GDR8161B',
> @.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
> @.LastModifiedByValue = 'DOSIM000\SIDLECS',
> @.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
> And these are the affected tables contained within the view (from bottom
to
> top):
> CREATE TABLE [dbo].[Win32_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [DriveIntegrity] [bit] NULL ,
> [FileSystemFlags] [int] NULL ,
> [FileSystemFlagsEx] [bigint] NULL ,
> [Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaximumComponentLength] [bigint] NULL ,
> [MediaLoaded] [bit] NULL ,
> [MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
> NULL ,
> [RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [SCSIBus] [bigint] NULL ,
> [SCSILogicalUnit] [int] NULL ,
> [SCSIPort] [int] NULL ,
> [SCSITargetId] [int] NULL ,
> [Size] [bigint] NULL ,
> [TransferRate] [float] NULL ,
> [VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [NumberOfMediaSupported] [bigint] NULL ,
> [MaxMediaSize] [bigint] NULL ,
> [DefaultBlockSize] [bigint] NULL ,
> [MaxBlockSize] [bigint] NULL ,
> [MinBlockSize] [bigint] NULL ,
> [NeedsCleaning] [bit] NULL ,
> [MediaIsLocked] [bit] NULL ,
> [Security] [int] NULL ,
> [LastCleaned] [datetime] NULL ,
> [MaxAccessTime] [bigint] NULL ,
> [UncompressedDataRate] [bigint] NULL ,
> [LoadTime] [bigint] NULL ,
> [UnloadTime] [bigint] NULL ,
> [MountCount] [bigint] NULL ,
> [TimeOfLastMount] [datetime] NULL ,
> [TotalMountTime] [bigint] NULL ,
> [UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaxUnitsBeforeCleaning] [bigint] NULL ,
> [UnitsUsed] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [PowerManagementCapabilities] [varchar] (1024) COLLATE
Latin1_General_CI_AS
> NULL ,
> [OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
> [IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
> NULL ,
> [AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [PowerManagementSupported] [bit] NULL ,
> [Availability] [int] NULL ,
> [StatusInfo] [int] NULL ,
> [LastErrorCode] [bigint] NULL ,
> [ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ErrorCleared] [bit] NULL ,
> [PowerOnHours] [bigint] NULL ,
> [TotalPowerOnHours] [bigint] NULL ,
> [MaxQuiesceTime] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EnabledState] [int] NULL ,
> [OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [RequestedState] [int] NULL ,
> [EnabledDefault] [int] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [InstallDate] [datetime] NULL ,
> [Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [ToArchive] [bit] NULL ,
> [TimeOfCreation] [datetime] NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> Can you also post the definition of and trigger on
> DATAVIEW_Win32_CDROMDrive?
>
Here it is, this is the "bottom" trigger (triggers on views are nested
(cascaded) all the way up to the top level tabel):
create trigger [DATAVIEW_Win32_CDROMDrive-InsertTrigger] on
[DATAVIEW_Win32_CDROMDrive] instead of INSERT as
INSERT INTO DATAVIEW_CIM_CDROMDrive ("GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation") SELECT "GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation" FROM inserted
INSERT INTO Win32_CDROMDrive ("GUID", "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber") select guid, "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber" from inserted
Thanks,
SLE

64bit to 32bit connectivity

Main Point: Views I have on my 64bit server will UNION ALL to my 32bit server
to gather core data. The 32bit server view needs some staic data
(CountryCode) so it INNER JOINs back to the 64bit server to get the staic
data. This is extremely slow. On my 32bit => 32bit server it works fine, but
on this new 64bit => 32bit server it's dog slow.
Details:
My 32bit server has SP3a and the InstCat.sql from the 64bit
\SETUP\IA64\Install directory, I can now call my stored procedures that use
distributed views.
My previous server setup was:
Primary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterprise
Secondary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterprise
32bit Server (Primary;One Year of Data) + 32bit Server (Secondary; Seven
Years of Data)
My new server setup is:
Primary = OS Window Advanced Server 2000 64bit; 64bit SQL Server Enterprise
Secondary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterprise
64bit Server (Primary;One Year of Data) + 32bit Server (Secondary; Seven
Years of Data)
The big problem: The views on the 64bit machine (64Views) UNION ALL to the
views on the 32bit machine (32Views) to allow me to retrieve data from both
machines. The concept of distributed views won't be discussed here for
brevity. These work fine.
But, as soon as I add to the 32Views, the SQL code that will INNER JOIN to
the CountryCodes static table on the 64bit machine it takes "forever"; 28
minutes to return a handful of records (162 to be exact). The reason for
doing this because I only want one location to store CountryCode details and
the 64bit machine is the primary machine to update these. I do have them on
the 32bit machine and I could change all my production machine databases to
look there, but I don't want to have to deal with this.
Tests:
On my previous setup of 32Views => 32Views => 32CountryCode, it works fine.
On a test of 64Views => 32Views, it works fine
Now, on the new setup of 64Views => 32Views => 64CountryCode, it is DOG SLOW.
Anyone help me. If you need some code I can probably put together a small
sample (instead of my big view sets). I will plan on that, but thought I
would at least get the question out into the newsgroup.
--
dbArchitect
Greg L. Wright"dbArchitect" <dbArchitect@.discussions.microsoft.com> wrote in message
news:EE5AFA0D-BE0C-43F6-B4AE-37D52498A6B2@.microsoft.com...
> Main Point: Views I have on my 64bit server will UNION ALL to my 32bit
> server
> to gather core data. The 32bit server view needs some staic data
> (CountryCode) so it INNER JOINs back to the 64bit server to get the staic
> data. This is extremely slow. On my 32bit => 32bit server it works fine,
> but
> on this new 64bit => 32bit server it's dog slow.
>
Apart from figuring out why this is slow (or figuring out how on earth it
was ever _not_ slow), this seems like a case for replicating the static data
to the 32bit servers. You could use one of the built-in Replication types
or just schedule a job that runs
insert into CountryCode
select * from 64bitServer.MyDB.CountryCode
David

64bit to 32bit connectivity

Main Point: Views I have on my 64bit server will UNION ALL to my 32bit serve
r
to gather core data. The 32bit server view needs some staic data
(CountryCode) so it INNER JOINs back to the 64bit server to get the staic
data. This is extremely slow. On my 32bit => 32bit server it works fine, but
on this new 64bit => 32bit server it's dog slow.
Details:
My 32bit server has SP3a and the InstCat.sql from the 64bit
\SETUP\IA64\Install directory, I can now call my stored procedures that use
distributed views.
My previous server setup was:
Primary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterprise
Secondary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterpris
e
32bit Server (Primary;One Year of Data) + 32bit Server (Secondary; Seven
Years of Data)
My new server setup is:
Primary = OS Window Advanced Server 2000 64bit; 64bit SQL Server Enterprise
Secondary = OS Window Advanced Server 2000 32bit; 32bit SQL Server Enterpris
e
64bit Server (Primary;One Year of Data) + 32bit Server (Secondary; Seven
Years of Data)
The big problem: The views on the 64bit machine (64Views) UNION ALL to the
views on the 32bit machine (32Views) to allow me to retrieve data from both
machines. The concept of distributed views won't be discussed here for
brevity. These work fine.
But, as soon as I add to the 32Views, the SQL code that will INNER JOIN to
the CountryCodes static table on the 64bit machine it takes "forever"; 28
minutes to return a handful of records (162 to be exact). The reason for
doing this because I only want one location to store CountryCode details and
the 64bit machine is the primary machine to update these. I do have them on
the 32bit machine and I could change all my production machine databases to
look there, but I don't want to have to deal with this.
Tests:
On my previous setup of 32Views => 32Views => 32CountryCode, it works fine.
On a test of 64Views => 32Views, it works fine
Now, on the new setup of 64Views => 32Views => 64CountryCode, it is DOG SLOW
.
Anyone help me. If you need some code I can probably put together a small
sample (instead of my big view sets). I will plan on that, but thought I
would at least get the question out into the newsgroup.
dbArchitect
Greg L. Wright"dbArchitect" <dbArchitect@.discussions.microsoft.com> wrote in message
news:EE5AFA0D-BE0C-43F6-B4AE-37D52498A6B2@.microsoft.com...
> Main Point: Views I have on my 64bit server will UNION ALL to my 32bit
> server
> to gather core data. The 32bit server view needs some staic data
> (CountryCode) so it INNER JOINs back to the 64bit server to get the staic
> data. This is extremely slow. On my 32bit => 32bit server it works fine,
> but
> on this new 64bit => 32bit server it's dog slow.
>
Apart from figuring out why this is slow (or figuring out how on earth it
was ever _not_ slow), this seems like a case for replicating the static data
to the 32bit servers. You could use one of the built-in Replication types
or just schedule a job that runs
insert into CountryCode
select * from 64bitServer.MyDB.CountryCode
David

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