Deserialization failed - how to fix?

Hi,
Have got the following message:
Deserialization failed: The table "table1" has rows that contain a different
number of cells than the number of the columns in the table (including cells
that span more than one column)
The report was fine until: I changed in the Code window the name of a
dataset field as it was used in 15 places for look and feel and it's quicker
to do it in the Code window via Search and Replace (have done this before).
The table 'table1' has a couple of merged cells, but these don't even use
this field. However it seems to me that the problem is related to the merged
cells.
I've changed it back in the code window, but the error remains. I can't
even see the report in Designer view anymore, just this error message. I can
still view the code.
Help! Have just spent days.
CR.Save the RDL, delete the report and re-add the RDL , That should get rid of
any former information in the RSS database and let you start anew.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CR" <CR@.discussions.microsoft.com> wrote in message
news:0799C041-FCE0-41F2-9403-9F42C60800F0@.microsoft.com...
> Hi,
> Have got the following message:
> Deserialization failed: The table "table1" has rows that contain a
different
> number of cells than the number of the columns in the table (including
cells
> that span more than one column)
> The report was fine until: I changed in the Code window the name of a
> dataset field as it was used in 15 places for look and feel and it's
quicker
> to do it in the Code window via Search and Replace (have done this
before).
> The table 'table1' has a couple of merged cells, but these don't even use
> this field. However it seems to me that the problem is related to the
merged
> cells.
> I've changed it back in the code window, but the error remains. I can't
> even see the report in Designer view anymore, just this error message. I
can
> still view the code.
> Help! Have just spent days.
> CR.
>|||Have saved then deleted the RDL from the .NET project, then re-added. Is this
what you meant? The problem remains. Not sure what you mean by 'RSS'
database.
"Wayne Snyder" wrote:
> Save the RDL, delete the report and re-add the RDL , That should get rid of
> any former information in the RSS database and let you start anew.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "CR" <CR@.discussions.microsoft.com> wrote in message
> news:0799C041-FCE0-41F2-9403-9F42C60800F0@.microsoft.com...
> > Hi,
> >
> > Have got the following message:
> > Deserialization failed: The table "table1" has rows that contain a
> different
> > number of cells than the number of the columns in the table (including
> cells
> > that span more than one column)
> >
> > The report was fine until: I changed in the Code window the name of a
> > dataset field as it was used in 15 places for look and feel and it's
> quicker
> > to do it in the Code window via Search and Replace (have done this
> before).
> > The table 'table1' has a couple of merged cells, but these don't even use
> > this field. However it seems to me that the problem is related to the
> merged
> > cells.
> >
> > I've changed it back in the code window, but the error remains. I can't
> > even see the report in Designer view anymore, just this error message. I
> can
> > still view the code.
> >
> > Help! Have just spent days.
> >
> > CR.
> >
>
>|||I think he meant to delete from the server. (Using Report Manager)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"CR" <CR@.discussions.microsoft.com> wrote in message
news:4AC3E1CC-5B9C-426C-877F-7704A66FCA2C@.microsoft.com...
> Have saved then deleted the RDL from the .NET project, then re-added. Is
> this
> what you meant? The problem remains. Not sure what you mean by 'RSS'
> database.
> "Wayne Snyder" wrote:
>> Save the RDL, delete the report and re-add the RDL , That should get rid
>> of
>> any former information in the RSS database and let you start anew.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "CR" <CR@.discussions.microsoft.com> wrote in message
>> news:0799C041-FCE0-41F2-9403-9F42C60800F0@.microsoft.com...
>> > Hi,
>> >
>> > Have got the following message:
>> > Deserialization failed: The table "table1" has rows that contain a
>> different
>> > number of cells than the number of the columns in the table (including
>> cells
>> > that span more than one column)
>> >
>> > The report was fine until: I changed in the Code window the name of a
>> > dataset field as it was used in 15 places for look and feel and it's
>> quicker
>> > to do it in the Code window via Search and Replace (have done this
>> before).
>> > The table 'table1' has a couple of merged cells, but these don't even
>> > use
>> > this field. However it seems to me that the problem is related to the
>> merged
>> > cells.
>> >
>> > I've changed it back in the code window, but the error remains. I
>> > can't
>> > even see the report in Designer view anymore, just this error message.
>> > I
>> can
>> > still view the code.
>> >
>> > Help! Have just spent days.
>> >
>> > CR.
>> >
>>|||Thanks both. I also deleted from ReportServer database. However can't
re-deploy due to Build failure with the original message.
It seems to me there's an issue with using Search and Replace in the Code
Window when merged cells are used in the table. Even when the merged cells
don't use the item you've editted. Have used Search/replace a lot and never
an issue, only in this case.
Wondering if anyone has found this also.
Cheers,
CR.
"Jeff A. Stucker" wrote:
> I think he meant to delete from the server. (Using Report Manager)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "CR" <CR@.discussions.microsoft.com> wrote in message
> news:4AC3E1CC-5B9C-426C-877F-7704A66FCA2C@.microsoft.com...
> > Have saved then deleted the RDL from the .NET project, then re-added. Is
> > this
> > what you meant? The problem remains. Not sure what you mean by 'RSS'
> > database.
> >
> > "Wayne Snyder" wrote:
> >
> >> Save the RDL, delete the report and re-add the RDL , That should get rid
> >> of
> >> any former information in the RSS database and let you start anew.
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "CR" <CR@.discussions.microsoft.com> wrote in message
> >> news:0799C041-FCE0-41F2-9403-9F42C60800F0@.microsoft.com...
> >> > Hi,
> >> >
> >> > Have got the following message:
> >> > Deserialization failed: The table "table1" has rows that contain a
> >> different
> >> > number of cells than the number of the columns in the table (including
> >> cells
> >> > that span more than one column)
> >> >
> >> > The report was fine until: I changed in the Code window the name of a
> >> > dataset field as it was used in 15 places for look and feel and it's
> >> quicker
> >> > to do it in the Code window via Search and Replace (have done this
> >> before).
> >> > The table 'table1' has a couple of merged cells, but these don't even
> >> > use
> >> > this field. However it seems to me that the problem is related to the
> >> merged
> >> > cells.
> >> >
> >> > I've changed it back in the code window, but the error remains. I
> >> > can't
> >> > even see the report in Designer view anymore, just this error message.
> >> > I
> >> can
> >> > still view the code.
> >> >
> >> > Help! Have just spent days.
> >> >
> >> > CR.
> >> >
> >>
> >>
> >>
>
>|||Using visual studio.net, un-merge the cells in your report (from the layout tab), then run the report. Now go back to report in the layout tab and merge your cells. I found that this worked for me.
Cheers
B
--
Message posted via http://www.sqlmonster.com

Deserialization failed

I created a table with merged cells - etc. tried to render it and got the
following error:
Deserialization failed: The table "table2" has rows that contain a different
number of cells than the number of the columns in the table (including cells
that span more than one column)
Is there a way to fix this without redoing the whole report. The designer
won't show it in the layout window.
Thanks,
DavidI don't have a solution to your problem per se but I can tell you when it
seems to occur.
This happens to me when I cut/copy and paste columns that contain merged
cells. The merged cells themselves are ok. It seems that the copy/paste of
them doesn't work properly.
If I need to copy them now I unmerge them first copy then and merge them
back.
If this happens before you have exited the designer, you can usually salvage
your work by deleting the pasted rows in design view. After you have exited
the designer, it will no longer load the report into design view so short of
editing the rdl file manually (Which seems like it would be painful) I do
not know how to recover from this.
This appears to be a bug in the designer. Has anyone else experienced this?
General Patton
"CapitalEMR" <CapHS@.hvif.com> wrote in message
news:uCe3$gqVFHA.544@.TK2MSFTNGP15.phx.gbl...
> I created a table with merged cells - etc. tried to render it and got the
> following error:
> Deserialization failed: The table "table2" has rows that contain a
different
> number of cells than the number of the columns in the table (including
cells
> that span more than one column)
> Is there a way to fix this without redoing the whole report. The designer
> won't show it in the layout window.
> Thanks,
> David
>|||Yes, this is a known problem in Report Designer.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"General_Patton" <general_patton@.community.nospam.com> wrote in message
news:OyR9In$VFHA.2196@.TK2MSFTNGP09.phx.gbl...
>I don't have a solution to your problem per se but I can tell you when it
> seems to occur.
> This happens to me when I cut/copy and paste columns that contain merged
> cells. The merged cells themselves are ok. It seems that the copy/paste of
> them doesn't work properly.
> If I need to copy them now I unmerge them first copy then and merge them
> back.
> If this happens before you have exited the designer, you can usually
> salvage
> your work by deleting the pasted rows in design view. After you have
> exited
> the designer, it will no longer load the report into design view so short
> of
> editing the rdl file manually (Which seems like it would be painful) I do
> not know how to recover from this.
> This appears to be a bug in the designer. Has anyone else experienced
> this?
> General Patton
>
>
> "CapitalEMR" <CapHS@.hvif.com> wrote in message
> news:uCe3$gqVFHA.544@.TK2MSFTNGP15.phx.gbl...
>> I created a table with merged cells - etc. tried to render it and got the
>> following error:
>> Deserialization failed: The table "table2" has rows that contain a
> different
>> number of cells than the number of the columns in the table (including
> cells
>> that span more than one column)
>> Is there a way to fix this without redoing the whole report. The
>> designer
>> won't show it in the layout window.
>> Thanks,
>> David
>>
>|||"Albert Yen [MSFT]" wrote:
> Yes, this is a known problem in Report Designer.
That's really great you already know that. The question is, if there is some
RDL hack how one can remedy the problem. I've worked on a report for four
hours now and suddenly I've got that "Deserialization failed" error message.
Please, don't tell me I've got to do that work again!

Description Metadata

When creating columns in MS-SQL there is a description field. How can I quer
y
this description field? I know how to return the Table Name, Column Name,
Data Type, Is_Nullable, etc. but I don’t know how to return the Descriptio
n.> When creating columns in MS-SQL there is a description field.
This is only ifyou create the columns in Enterprise Manager *and* choose to
use that property. Personally, I don't think you should do either, mostly
because your CREATE TABLE statements should be something that you can
script, modify, store in source control, etc.
In any case:
http://www.aspfaq.com/2244|||Aaron, good point! So where would you store the description of individual
columns? Would you create separate tables to store the descriptions of the
tables and the columns?|||> Aaron, good point! So where would you store the description of individual
> columns? Would you create separate tables to store the descriptions of the
> tables and the columns?
Why do you feel the description of your database schema needs to be stored
in the database?
We create separate human readable documentation. This allows us much more
freedom in how we are describing the schema, e.g. definitions > 64
characters, diagrams, tables, etc.
A|||I was thinking of creating a web app so that the descriptions, rules, etc.
can be documented, searched, and studied by several disparate groups. As of
now the database is completely undocumented. Then to complicate matters even
more, the knowledge of this database is scattered amongst many different
groups and individuals who won’t always work together. I keep running into
columns named TX5_RRT_SDescr and no one knows what the hell that column is
used for. The DBA is too busy to answer most questions and his answers are
too flipped to be of much use.
What form in your documentation in?|||Most of ours are in Word format. But you can make your job easier using an
extraction tool, for example Enterprise Architect.
Hey, if the DBA is too busy to answer questions that need to be answered,
why do you think he'd be willing to populate some table with the answers?
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won’t always work together. I keep running
> into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Lol, he won’t! The rest of us peons will have to collaboratively piece thi
ngs
together. There are about 5 of us who have volunteered for this project and
I
just need to figure out how to best do it. I have a zero budget to work with
.
Ok then your suggestion is not to store the description in database
properties. Thanks.
The answer to my original post is:
Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
SELECT name, CONVERT(varchar(2000), [value]) AS Description
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
'admin_Users', 'Column', 'User_ID')|||> The answer to my original post is:
> Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
> SELECT name, CONVERT(varchar(2000), [value]) AS Description
> FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
> 'admin_Users', 'Column', 'User_ID')
Yep, I posted a link to code samples earlier:
http://www.aspfaq.com/2244|||You could store your meta data in SQL Server tables and then develop your
own custom GUI and reports around it, but there are many 3rd party
applications would do a better job.
Tools like ERWin, Viseo (or even Enterprise Managers Diagramer) can be used
to create annotated diagrams of the database model
http://www.databaseanswers.com/modelling_tools.htm
Reporting Services 2005 has a Model Designer that provides end users with a
high level view of the database model for use with Report Builder.
http://www.devx.com/dbzone/Article/28047/1954?pf=true.
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won't always work together. I keep running into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Aaron, yes you did and I missed it at first. I found
http://developer.com/db/article.php/3361751 which is basically the same.
Hey thanks Aaron for all your help and insight.sql

Description field in Subscriptions Table

The Description field in SQL Server 2005 Reporting Services just defaults to "Send e-mail to email@.domain.com" (obviously a dummy e-mail address) when you create an e-mail subscription. You cannot edit the Description when you select "Edit" beside a subscription.

The Description may be edited directly in the Subscriptions table under the ReportServer database but this is not end-user friendly.

I thought of using an add/update trigger to set the Description to the "Subject" out of the Extension Settings field. The Extension Settings field is XML so you would have to parse the Subject out of there first. The "Subject" is the e-mail Subject for the e-mail that is sent to the subscriber and it much more adequately describes the report.

My boss understood what I wanted to do but wanted to make sure in the industry literature that the product was not that lacking. Hard to believe that you cannot directly edit the Subscription Description through the web browser interface - would love for someone to prove me wrong.

Hello,

I'm wondering why you are wanting to change the description of a subscription? The description tells you what the subscription does, "Send email to..." for emails, or "Save as..." for files.

For adding a description to the report, you could set it in the properties of the report itself.

You can change the description directly, but it is reset after the subscription is updated.

Jarret

|||

Well, the reason that I want to change the Subscription Description is that I have ten of them that say the same thing - you cannot tell one from the other unless you click on edit. This is not the Report Description

What we are going to do is add a trigger that fires when an update or add takes place and we will set the description equal to the parsed subject from the Extension Settings. Well, unless someone comes up with a better idea.

Thank you very much for responding.

William

|||I have solved the problems by modifying the CreateSubscription and UpdateSubscription stored procedures so that they parse the Subject out of the ExtensionSettings column and then set the Description equal to the parsed substring.sql

Description Column in Table Creation

Hello,
When you add a column you can put in a description. What
is the maximum size of this ?
Thanks
Peteruse extended properties, plenty of help in BOL
regards,
mark baekdal
www.dbghost.com
>--Original Message--
>Hello,
>When you add a column you can put in a description. What
>is the maximum size of this ?
>Thanks
>Peter
>.
>|||From BOL
[ @.value = ] { 'value' }
Is the value to be associated with the property. value is sql_variant, with
a default of NULL. The size of value may not be more than 7,500 bytes;
otherwise, SQL Server raises an error.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0c8801c3d5de$45f3d6d0$a101280a@.phx.gbl...
> Hello,
> When you add a column you can put in a description. What
> is the maximum size of this ?
> Thanks
> Peter|||Thanks Guys
>--Original Message--
>Hello,
>When you add a column you can put in a description. What
>is the maximum size of this ?
>Thanks
>Peter
>.
>

Describing a table structure

Hi Jig,
I too had the same problem, coming from an Oracle
background. The SQL Server equivalent of 'desc' is
sp_help, followed by the table name in single quotes.
e.g. To descibe the table employees in the Northwind
database:
Use Northwind
sp_help 'employees'
This give the same info as the Oracle 'desc'
Mark

>--Original Message--
>Hi,
>I would like to know the syntax in MS SQL 2K for
describing a table, without
>having to expand everything in the object browser.
>I know in Oracle it is: desc <table_name>;
>Thanks,
>Jig.
>.
>
Thanks.
"Mark Laffey" wrote:

> Hi Jig,
> I too had the same problem, coming from an Oracle
> background. The SQL Server equivalent of 'desc' is
> sp_help, followed by the table name in single quotes.
> e.g. To descibe the table employees in the Northwind
> database:
> Use Northwind
> sp_help 'employees'
> This give the same info as the Oracle 'desc'
> Mark
> describing a table, without
>

Describing a table structure

Hi,
I would like to know the syntax in MS SQL 2K for describing a table, without
having to expand everything in the object browser.
I know in Oracle it is: desc <table_name>;
Thanks,
Jig.
Do you mean
sp_help tblname
"Jig Bhakta" wrote:

> Hi,
> I would like to know the syntax in MS SQL 2K for describing a table, without
> having to expand everything in the object browser.
> I know in Oracle it is: desc <table_name>;
> Thanks,
> Jig.
|||is that the only way?
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Do you mean
> sp_help tblname
> "Jig Bhakta" wrote:
|||This will create a script for it
http://www.mindsdoor.net/DMO/DMOScripting.html
and this will do a database or all databases on a server
http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi,
there may be other ways, but this gives the info in more
or less the same format as you get in Oracle. Just make
sure that you select 'Results in grid' from the Query tab.
Jig,
that is the correct format
Mark
[vbcol=seagreen]
>--Original Message--
>is that the only way?
>"Nigel Rivett" wrote:
describing a table, without
>.
>

describe table

Oracle has describe table, which will return the SQL Definition of that tabl
e...what is the equivalent in MSSQL? I am aware of sp_help table, but that d
oesn't cut it.
I want to create a table using one of the various GUI tools that are "out th
ere" but I then want to save the resulting SQL DDL, so that I can script fut
ure database creates/drops/etc.
Any help appreciated.
BOBBoth Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
David Portas
SQL Server MVP
--|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually,
I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:

> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
>
>|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for
an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to
start at:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am
using MSDE, so EM is not
an option...)[vbcol=seagreen]
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
>|||One of the ten commandments at our shop is to SCRIPT EVERYTHING.
That means we make a TEXT file with an extension of .SQL and put our TABLE c
reate right in there. We building indexes and constraints right in that scr
ipt. We have control over naming our indexes.
We script STORED PROCEDURE creation. We script the GRANTing of rights to SP
ROCS in the same script.
Changes made in the GUI can get lost and never make it to production servers
.
Having EM or QA create a script from the database is flawed - it doesn't hav
e everything associated with that table or sproc...
At least that's the way we do it - and it's been very successful in a rapid
development/get the software to the customer scenario over the past 3 years.
"David Portas" wrote:

> Query Analyzer will script any table individually. Just right-click on the
> table in the Object Browser and select Script to New Window As > Create.
> Enterprise Manager will allow you to script multiple objects to a single
> file or script an entire database if you wish.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Describe Table

Hi Guys,
What is the equivalent of Describe table of Oracle in SQL server.
I want to see the table fields for a table in SQL server any command???
TIAsp_help <tablename>

describe table

Oracle has describe table, which will return the SQL Definition of that table...what is the equivalent in MSSQL? I am aware of sp_help table, but that doesn't cut it.
I want to create a table using one of the various GUI tools that are "out there" but I then want to save the resulting SQL DDL, so that I can script future database creates/drops/etc.
Any help appreciated.
BOB
Both Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB
|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB
|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
David Portas
SQL Server MVP
|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:

> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
>
>
|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to start at:
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not
an option...)[vbcol=seagreen]
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
|||One of the ten commandments at our shop is to SCRIPT EVERYTHING.
That means we make a TEXT file with an extension of .SQL and put our TABLE create right in there. We building indexes and constraints right in that script. We have control over naming our indexes.
We script STORED PROCEDURE creation. We script the GRANTing of rights to SPROCS in the same script.
Changes made in the GUI can get lost and never make it to production servers.
Having EM or QA create a script from the database is flawed - it doesn't have everything associated with that table or sproc...
At least that's the way we do it - and it's been very successful in a rapid development/get the software to the customer scenario over the past 3 years.
"David Portas" wrote:

> Query Analyzer will script any table individually. Just right-click on the
> table in the Object Browser and select Script to New Window As > Create.
> Enterprise Manager will allow you to script multiple objects to a single
> file or script an entire database if you wish.
> --
> David Portas
> SQL Server MVP
> --
>
>

describe table

Oracle has describe table, which will return the SQL Definition of that table...what is the equivalent in MSSQL? I am aware of sp_help table, but that doesn't cut it.
I want to create a table using one of the various GUI tools that are "out there" but I then want to save the resulting SQL DDL, so that I can script future database creates/drops/etc.
Any help appreciated.
BOBBoth Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
--
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
--
David Portas
SQL Server MVP
--|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:
> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> > Oracle has describe table, which will return the SQL Definition of that
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> >
> > I want to create a table using one of the various GUI tools that are "out
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
> >
> > Any help appreciated.
> >
> > BOB
>
>|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to start at:
http://www.karaszi.com/sqlserver/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not
an option...)
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
> > If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> > generate a create table script with the third button from the left.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "bob" <bob@.discussions.microsoft.com> wrote in message
> > news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> > > Oracle has describe table, which will return the SQL Definition of that
> > table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> > that doesn't cut it.
> > >
> > > I want to create a table using one of the various GUI tools that are "out
> > there" but I then want to save the resulting SQL DDL, so that I can script
> > future database creates/drops/etc.
> > >
> > > Any help appreciated.
> > >
> > > BOB
> >
> >
> >|||if you want full database change management for SQL
Server check out www.dbghost.com. They also give away a
free scripting utility which scripts out data and schema.
>--Original Message--
>Oracle has describe table, which will return the SQL
Definition of that table...what is the equivalent in
MSSQL? I am aware of sp_help table, but that doesn't cut
it.
>I want to create a table using one of the various GUI
tools that are "out there" but I then want to save the
resulting SQL DDL, so that I can script future database
creates/drops/etc.
>Any help appreciated.
>BOB
>.
>

Desc as column name

Hi,
When I run the following sql statement
CREATE TABLE t1 (Desc varchar(50))
I get an error saying Incorrect syntax near keyword Desc.
However, I can run the same command on other database engines.
Where can I find situations like this where an sql statement will work
on other databases but not SqlServer?
The statement
CREATE TABLE t1 ([Desc] varchar(50))
works fine.
Do you suggest that I enclose column names in square brackets ([]) for
all sql statements (select, insert, update, create, delete etc)?
Regards,
PrakashSince you mention other database engines, you should get into the habit of using the ANSI SQL
compliant way to delimit identifiers: double-quotes and not square brackets.
There's a list in Books Online of all the reserved keywords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>|||Desc is a reserved keyword in SQL Server. It's short for 'descending' and is
used in syntax such as ORDER BY column_name DESC. If you want to use
reserved words as object or column names, you must delimit the name by
either using [ ] as you show below are by using double quotes " ". Keep in
mind that you will need to delimit this name in every statement in which you
reference the name. You might consider just choosing a longer (and more
descriptive) name such as "Description".
For a list of reserved keywords, see this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms189822.aspx
For more information about delimiting names see this Books Online topic
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
<prakash_bande@.hotmail.com> wrote in message
news:1189098048.395246.253570@.w3g2000hsg.googlegroups.com...
> Hi,
> When I run the following sql statement
> CREATE TABLE t1 (Desc varchar(50))
> I get an error saying Incorrect syntax near keyword Desc.
> However, I can run the same command on other database engines.
> Where can I find situations like this where an sql statement will work
> on other databases but not SqlServer?
> The statement
> CREATE TABLE t1 ([Desc] varchar(50))
> works fine.
> Do you suggest that I enclose column names in square brackets ([]) for
> all sql statements (select, insert, update, create, delete etc)?
>
> Regards,
> Prakash
>sql

Derived Table Problem

I've created this:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

and I get this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 99
adv_1047185 185
adv_110566 66
boh_107134 34

Instead, I want this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 14
adv_1047185 82
adv_110566 17
boh_107134 12

The "ProjectID" and "Submissions" columns are produced when I run the
derived table (dt, above) as a standalone query. By the same token,
the "Project ID" and "Registrants" columns are produced when I run the
"outer" query, above.

Am I on the right track here?

TIA,

-- Bill[posted and mailed, please reply in news]

Bill (w.white@.snet.net) writes:
> I've created this:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID

COUNT(dt.Hits) returns the number of rows where this column is not null.
I would guess that you want SUM(dt.Hits) here instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9462F13D69581Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Bill (w.white@.snet.net) writes:
> > I've created this:
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> COUNT(dt.Hits) returns the number of rows where this column is not null.
> I would guess that you want SUM(dt.Hits) here instead.

Using SUM(dt.Hits) yields:

ProjectID Registrants Submissions
--- -- --
adv_104699 1881
adv_1047185 2960
adv_110566 462
boh_107134 952
boh_112238 608
boh_113637 444
brw_106544 1012

which I suspect is closer to my desired result, since the value in the
Submissions column = (Registrants * Submissions) for that ProjectID;
so the proper Submissions value is "in there somewhere". My need is
for the correct Submissions value to appear within the Submissions
column:

ProjectID Registrants Submissions
--- -- --
adv_104699 19
adv_1047185 16
adv_110566 7
boh_107134 28
boh_112238 16
boh_113637 12
brw_106544 23

Happy New Year!

-- Bill|||Bill (w.white@.snet.net) writes:
> Using SUM(dt.Hits) yields:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 1881
> adv_1047 185 2960
> adv_1105 66 462
> boh_1071 34 952
> boh_1122 38 608
> boh_1136 37 444
> brw_1065 44 1012
> which I suspect is closer to my desired result, since the value in the
> Submissions column = (Registrants * Submissions) for that ProjectID;
> so the proper Submissions value is "in there somewhere". My need is
> for the correct Submissions value to appear within the Submissions
> column:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 19
> adv_1047 185 16
> adv_1105 66 7
> boh_1071 34 28
> boh_1122 38 16
> boh_1136 37 12
> brw_1065 44 23

Indeed it seems that diving the Submissions column with the Registratns
column gives the result you are asking for. That is:

SUM(dt.Hits) / COUNT(c.ID)

Moral: when you ask a question like this, it is always a good idea to
provide:

o CREATE TABLE statements of the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

With this infomation, anyone who takes a stab with your problem can post a
tested solution. Without this information, the answer you get is more or
less guesswork.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9463F1E595160Yazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > Using SUM(dt.Hits) yields:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 1881
> > adv_1047 185 2960
> > adv_1105 66 462
> > boh_1071 34 952
> > boh_1122 38 608
> > boh_1136 37 444
> > brw_1065 44 1012
> > which I suspect is closer to my desired result, since the value in the
> > Submissions column = (Registrants * Submissions) for that ProjectID;
> > so the proper Submissions value is "in there somewhere". My need is
> > for the correct Submissions value to appear within the Submissions
> > column:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 19
> > adv_1047 185 16
> > adv_1105 66 7
> > boh_1071 34 28
> > boh_1122 38 16
> > boh_1136 37 12
> > brw_1065 44 23
> Indeed it seems that diving the Submissions column with the Registratns
> column gives the result you are asking for. That is:
> SUM(dt.Hits) / COUNT(c.ID)
> Moral: when you ask a question like this, it is always a good idea to
> provide:
> o CREATE TABLE statements of the involved tables.
> o INSERT statements with sample data.
> o The desired output given the sample.
> With this infomation, anyone who takes a stab with your problem can post a
> tested solution. Without this information, the answer you get is more or
> less guesswork.

Alrighty, then! Here we go:

CREATE TABLE CME_TBL_dev
(
ID int IDENTITY (1, 1) NOT NULL,
ProjectID varchar (50) NULL,
registrationDate datetime NULL DEFAULT (getdate()),
lastName varchar (60) NULL,
testDate datetime NULL,
evalDate datetime NULL
)

--------------

INSERT INTO CME_TBL_dev
(
ProjectID,
lastName,
testDate,
evalDate
)

SELECT 'pmw_1129', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'pmw_1129', 'wilkins', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'pmw_1129', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'pmw_1129', 'fife', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'fonebone', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'brw_1065', 'wilkins', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'brw_1065', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'brw_1065', 'fife', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'fonebone', NULL, NULL
UNION ALL
SELECT 'any_8930', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'any_8930', 'wilkins', NULL, NULL
UNION ALL
SELECT 'any_8930', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'any_8930', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'any_8930', 'fife', NULL, NULL
UNION ALL
SELECT 'any_8930', 'fonebone', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'hir_1093', 'wilkins', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'hir_1093', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'hir_1093', 'fife', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'fonebone', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'yth_9804', 'wilkins', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'yth_9804', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'yth_9804', 'fife', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'fonebone', NULL, NULL

---------------
-- This is the query I'm hoping I can get to yield
-- the desired results (see below).

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

--------
-- The following two queries are for utility purposes.

SELECT
c.ProjectID, Count(c.ID) as 'Registrants'
FROM
CME_TBL_dev c
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

--------

SELECT
c.ProjectID, Count(c.ID) as 'Submissions'
FROM
CME_TBL_dev c
WHERE
c.evalDate Is Not NULL OR
c.testDate Is Not NULL
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

------------

What I seek is this:

ProjectID Registrants Submissions
--- ---- ----
any_89306 3
brw_10656 3
hir_10936 3
pmw_11296 3
yth_98046 3

But what I get instead is this (per the derived table query above):

ProjectID Registrants Submissions
--- ---- ----
any_89306 6
brw_10656 6
hir_10936 6
pmw_11296 6
yth_98046 6

Solving this would have major positive impact on many aspects of my
reporting efforts.

Thanks in advance!

-- Bill|||Bill (w.white@.snet.net) writes:
> -- This is the query I'm hoping I can get to yield
> -- the desired results (see below).
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID
>...
> What I seek is this:
> ProjectID Registrants Submissions
> --- ---- ----
> any_8930 6 3
> brw_1065 6 3
> hir_1093 6 3
> pmw_1129 6 3
> yth_9804 6 3

It does indeed seem that my suggest to take sum divided by count
gives the desired result:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID
GROUP BY c.ProjectID
ORDER BY c.ProjectID

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9464EE208FB4EYazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > -- This is the query I'm hoping I can get to yield
> > -- the desired results (see below).
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL_dev c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> > GROUP BY c.ProjectID
> > ORDER BY c.ProjectID
> >...
> > What I seek is this:
> > ProjectID Registrants Submissions
> > --- ---- ----
> > any_8930 6 3
> > brw_1065 6 3
> > hir_1093 6 3
> > pmw_1129 6 3
> > yth_9804 6 3
> It does indeed seem that my suggest to take sum divided by count
> gives the desired result:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID

Solved it! I took a different approach. I think the crux of my
difficulty lay in "overprocessing" dt.Hits:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
dt.Hits as 'Submissions'
FROM
CME_TBL_dev c,
(
SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt

WHERE
c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID, dt.Hits

ORDER BY
c.ProjectID

Yields:

ProjectID Registrants Submissions
--- ---- ----
any_8930 6 3
brw_1065 6 3
hir_1093 6 3
pmw_1129 6 3
yth_9804 6 3

Thanks for priming my mental pump!

-- Bill

Derived table not updatable

I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.

Thanks in advance!It would appear that either the dbo.vw_MasterView.MonthsUnbilled column is either a constant, a computed column, or derived from one of them.

What is the DDL for dbo.vw_MasterView? What is the DDL for the table that contains the column referenced in dbo.vw_MasterView.MonthsUnbilled ?

-PatP|||Thank you Pat for your response.
I can update dbo.vw_MasterView.MonthsUnbilled without using a derived table. In other words, the following query runs just fine.
update dbo.vw_MasterView set MonthsUnbilled =99999888
FROM dbo.vw_MasterView
WHERE (RecordID =8377396)
But if I use a derived table like this:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
I get that error.|||Is there either a PRIMARY KEY or a UNIQUE constraint on the table column that eventually populates dbo.vw_MasterView.RecordID ? I think that there needs to be a constraint of one of those two types to make the virtualized view (the derived table created from an existing view) updatable.

-PatP|||Pat, you're right. RecordID is the primary key and an identity field. But is this the reason why I got the error?

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price
,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
MauryMaury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury|||"Russell Fields" wrote:

> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster al
l
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest pri
ce,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (addi
ng
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts asql

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
MauryMaury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury|||"Russell Fields" wrote:
> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>> I think you are probably try to do something like this (untested) code:
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster all
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts a

derived table fails, I'm doing something wrong...

I am trying to join a list of stocks with a list of (optional) prices,
getting the newest price. I am using a derived table to get the latest price,
but when I join, I always get NULL even though there are prices...
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
pr.accountId=a.accountid
ORDER BY a.code
If you run the inner query (the derived table) for a given accountId (adding
it to the where) it works fine, returning the correct row from the prices
table. But when used as above, it always returns null for pr.
Any ideas?
Maury
Maury,
The problem is that you are asking for the TOP 1 record, thus getting only
one record. Then you are comparing the pr.accountID of that one record with
a.accountID. So, there could be some rows joining, but all the other rows
from tblAccounts would not find a match.
I think you are probably try to do something like this (untested) code:
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN
(SELECT accountId, price1, pricedate, pricenote
FROM tblPrices tp1
JOIN (SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID) AS tp2
ON tp1.accountID = tp2.accountID
AND tp1.pricedate = tp2.pricedate) AS pr
ON pr.accountId=a.accountid
ORDER BY a.code
If I did not blow this, what it is trying to do is:
tp2 = Give me the accountId and maximum pricedate foroevery account
pr = Join tblPrices to tp2 to return all information that matches the
maximum pricedate for each accountId
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:0F47DDED-8044-4F41-BBDE-C5734329D153@.microsoft.com...
>I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest
> price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId
> (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
|||"Russell Fields" wrote:

> I think you are probably try to do something like this (untested) code:
This is how I did it the first time actually, but the performance was
terrible.
You know, it's about time the SQL vendors added some syntax for time series!
Is it too much to ask for NEWEST and OLDEST?
Maury
|||Maury,
I feel your pain, but the right answer is better than a fast answer. (Sort
of :-)) One thing that we did not discuss is indexes and how they may help
such a query. Having said that, however, what I would probably do is create
a temporary table with the innermost tp2 select, something like:
CREATE #Temp
(accountId int,
maxpricedate datetime)
INSERT INTO #temp
SELECT accountId, MAX(pricedate)
FROM tblPrices
WHERE pricedate <= GETDATE( )
GROUP BY accountID
-- Then use the temp table in the last select.
SELECT a.AccountID, a.code, pr.price1, pr.pricedate
FROM tblAccounts a
LEFT JOIN #temp t
ON a.accountId = t.accountId
JOIN tblPrices pr
ON t.accountId = pr.accountId
AND t.pricedate = pr.pricedate
ORDER BY a.code
Put an index on accountId in #temp and so forth.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6A1230A2-19DC-4D1C-A297-DC4A56C1E149@.microsoft.com...
> "Russell Fields" wrote:
>
> This is how I did it the first time actually, but the performance was
> terrible.
> You know, it's about time the SQL vendors added some syntax for time
> series!
> Is it too much to ask for NEWEST and OLDEST?
> Maury
>
|||You know, I think what I should really do is create a non-temp table called
tblCurrentPrices and keep the "latest" version in there. This sort of thing
is spread all over my code, I could probably make my whole app run faster all
over the place...
Wait, I even have a table to put it in already. Ignore me, I'm rambling.
|||On Jul 6, 8:26 pm, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> I am trying to join a list of stocks with a list of (optional) prices,
> getting the newest price. I am using a derived table to get the latest price,
> but when I join, I always get NULL even though there are prices...
> SELECT a.AccountID, a.code, pr.price1, pr.pricedate
> FROM tblAccounts a
> LEFT JOIN (SELECT TOP 1 accountId, price1, pricedate, pricenote FROM
> tblPrices WHERE pricedate <= GETDATE() ORDER BY pricedate desc) pr ON
> pr.accountId=a.accountid
> ORDER BY a.code
> If you run the inner query (the derived table) for a given accountId (adding
> it to the where) it works fine, returning the correct row from the prices
> table. But when used as above, it always returns null for pr.
> Any ideas?
> Maury
Hi, I made made slight changes to your qiery:
Select a.AccountID, a.code, price1 = (SELECT TOP 1 price1 FROM
tblPrices pr WHERE pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc),
pricedate = (SELECT TOP 1 pricedate FROM tblPrices WHERE
pr.accountId=a.accountid and
pricedate <= GETDATE() ORDER BY pricedate desc)
from tblAccounts a

Derived table and adding another column problem

Hi
I have this query below, which I'm trying to add and group data on the w
number (ISO w, taken from Books online) fn_getISOW returns an integer
and works independently of this query. This query also works as I want when
there is no wno invloved (the 3 places)
The error I get is "Invalid column name 'wno'", I think what I'm doing is
a "copy" of what has been done in the count(case.....) as xx
Can anyone see what I'm doing wrong and how to fix it?
regards
Henry
declare @.ext char(4)
declare @.site int
declare @.calltype char(1)
SET DATEFORMAT mdy
set @.site = 1
set @.calltype = 'E'
set @.ext = '6810'
SELECT wno***, network, centre, center, team, total, besvaret, optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0 THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
GROUP BY wno***, network, centre, center, team) AS D
ORDER BY 1Try this
SELECT wno***, network, centre, center, team, total, besvaret,
optaget,
opgivet, ubesvaret
FROM (
SELECT network, centre, center, team,
COUNT(*) AS total,
COUNT(CASE WHEN durationofconversation > 0
THEN 1
END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo.fn_getISOw(DATECALLINITIATED) as
wno
***
FROM V2tickets
INNER JOIN
(SELECT s.serie, a.nodename as network, cs.nodename as
centre, c.nodename as center, t.nodename as team
FROM areas a, centers cs, center c, teams t, series s
WHERE (t.nodeid = 1 or t.nodeid=8 or t.nodeid=2 or
t.nodeid=72 or t.nodeid=73) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid)
AS H
ON V2tickets.digits = H.serie
WHERE siteid = @.site
AND calltype = @.calltype
AND LEN(digits) = 4
) AS D
GROUP BY wno***, network, centre, center, team ORDER BY 1
Madhivanan|||> Try this
> Madhivanan
>
Hi
No I'm sorry that doesn't do the trick either :(
regards
Henry|||> > Try this
> Hi
> No I'm sorry that doesn't do the trick either :(
> regards
> Henry
Solved... I had to use the same syntax in the group by part since wno is
not know at that time.
regards
Henrik

Derived Table Alternative

I have an application that has two different database backends, one is SQL Server Compact Edition and the other is SQL Server. The reason is because the application may run at home on one of our sales agent's computers or here in the office.

I have a query that uses a derived table and works just fine in SQL Server, however when I run it in the compact edtion (having the exact same table structures) it will not run. My question is...does the Compact Edtion or the Mobile Edition allow derived tables. If not is there a way to work around this? I will happily give an example if it will help.

Thank you,

Adam

Hi Adam,

a sample would be very useful, thanks.

|||

The three tables used for this are Assignment, Activity, and User. The Assignment table can have multiple Activities entered by different Users (only 1 user per activity). I need a list of all of the active assignments for a given User along with the last activity that was added to that assignment. The assignment may or may not have an activity but the assignment still needs listed. The activity returned must be one entered by the same user that the assignment belongs to.

Here is an example that works in SQL Server but not SqlCE:

select assignment.assignmentID, activityDateStamp
from assignment
left join
(
select assignment.assignmentID, max(activityDateStamp) as activityDateStamp
from activity
join assignment on assignment.assignmentID = activity.assignmentID
where assignment.userID = 40
and activity.userID = 40
and activityActive = 1
and assignmentActive = 1
group by assignment.assignmentID
) maxActivity on maxActivity.assignmentID = assignment.assignmentID
where assignment.userID = 40
and assignmentActive = 1

The output would look similar to this:

assignmentID activityDateStamp
-
123 NULL
4322 2006-06-23
423 2006-12-15
431 NULL

Thanks again for any help.

|||

Derived tables are not supported in SSCE.

|||I guess if we can provide some feedback to the team for future versions having derived/nested queries would definitely be something worth having. This limitation currently prevents us doing more than the simplest of queries.|||

Thanks Nick.

What I did not mention earlier was that support for derived tables aka nested queries would be there with the next release of Orcas!!

derived table

Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.Your syntax looks fine.
Is it not doing what you expect it to?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses whe
re
> program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
to
> do this in a query?
> Please help. Thanks.
>
>
>|||Please post the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.|||I got error:
No column was specified for column 3 of 'DB1'?
what's that mean? Thanks.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
> Your syntax looks fine.
> Is it not doing what you expect it to?
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>|||Is 'mydb' the name of the database you want information about?
program_name refers to the application that is accessing the database.
If so:
The Database is in the field dbid.
Try:
select count(*) from (select hostname, program_name from sysprocesses where
dbid=db_id('mydb') group by hostname, program_name) as DB1
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses
> where program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
> to do this in a query?
> Please help. Thanks.
>
>|||You must have posted a different query than what you are trying to run.
That error is telling you that all columns specified in a derived table need
a name, so that they can be referenced by the outer query.
If you had a query like:
select count(*) from (select hostname, program_name, count(1) from
sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
I'd expect the error. Because the third column does not have a referencable
name for the outer query. It would need to be re-written like:
select count(*) from (select hostname, program_name, count(1) as rowCount
from sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>|||your post is missing the aggregate for DB1, btw
however, this is the problem: all columsn in in a derived table must
have a name, e.g. [using max(spid) for example]
select count(*) from (select hostname, program_name, max(spid) as spid
from sysprocesses where program_name='mydb' group by hostname,
program_name) as DB1
or
select count(*) from (select hostname, program_name, max(spid)from
sysprocesses where program_name='mydb' group by hostname, program_name)
as DB1 (hostname, program_name, spid)
js wrote:
> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>
>|||Thanks Trey.
I got it. need to assign name: count(*) as counters first
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u3fb5gjFGHA.984@.tk2msftngp13.phx.gbl...
> your post is missing the aggregate for DB1, btw
> however, this is the problem: all columsn in in a derived table must have
> a name, e.g. [using max(spid) for example]
> select count(*) from (select hostname, program_name, max(spid) as spid
> from sysprocesses where program_name='mydb' group by hostname,
> program_name) as DB1
> or
> select count(*) from (select hostname, program_name, max(spid)from
> sysprocesses where program_name='mydb' group by hostname, program_name) as
> DB1 (hostname, program_name, spid)
>
> js wrote:|||Thanks Ryan.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:6026DBD4-29D4-426C-BF58-DCAB853CB8E0@.microsoft.com...
> You must have posted a different query than what you are trying to run.
> That error is telling you that all columns specified in a derived table
> need
> a name, so that they can be referenced by the outer query.
> If you had a query like:
> select count(*) from (select hostname, program_name, count(1) from
> sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> I'd expect the error. Because the third column does not have a
> referencable
> name for the outer query. It would need to be re-written like:
> select count(*) from (select hostname, program_name, count(1) as rowCount
> from sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>sql