get an error on a sample code

This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:

USE AdventureWorks;
GO
DECLARE @.tablename sysname
SET @.tablename = N'Person.AddressType'
table_loop:
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.tablename = RTRIM(UPPER(@.tablename))
EXEC ('SELECT ''' + @.tablename + ''' = COUNT(*) FROM '
+ @.tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @.tablename
IF (@.@.FETCH_STATUS <> -1) GOTO table_loop
GO

The errors are:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist

The database is connected. Table Person.AddressType is a part of it.

What is wrong?

Thanks.

Did you get this from the GOTO help topic? This note is in the topic right above the example:

Note:

The tnames_cursor cursor is not defined. This example is for illustration only.

so effectively this example is expected not to work. If you need some specific help on using the GOTO syntax, I would suggest asking in the T-SQL forum, that's where the syntax gurus hang out.

Mike

generating XML to validated against the schema

hi,
I have a sample xml data and sample xsd as follows: the xsd is really big
and runs into 4 pages. I need to stored data into the data base
and generate xml that validates against this schema.
the sqlxml bulk load and view mapper fails to recognize the xsd fine.
Can some one point me to the right direction .
Infact storing is working but generating the xml
using EXPLICIT option is really a pain.
Regards,
Balajee
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="StrategicPerformanceContract"
targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:documentation>Strategic Performance Contract</xs:documentation>
</xs:annotation>
<xs:element name="root" type="contractType" />
<xs:complexType name="contractType">
<xs:sequence>
<xs:element name="transactionHeader" type="transactionHeaderType" />
<xs:element name="documentHeader" type="documentHeaderType" />
<xs:element name="strategicDirection" type="strategicDirectionType" />
<xs:element name="strategicChoices" type="strategicChoicesType" />
<xs:element name="riskManagement" type="riskManagementType" />
<xs:element name="performanceContract" type="performanceContractType" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="transactionHeaderType">
<xs:sequence>
<xs:element name="userName" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="documentHeaderType">
<xs:sequence>
<xs:element name="contractType" type="xs:ID" />
<xs:element name="abbr" type="xs:string" />
<xs:element name="leftTitle1" type="xs:string" />
<xs:element name="leftTitle2" type="xs:string" />
<xs:element name="rightTitle1" type="xs:string" />
<xs:element name="asofDate" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="strategicDirectionType">
<xs:sequence>
<xs:element name="header" type="headerType" />
<xs:element name="strategicObjective" type="strategicObjectiveType"
minOccurs="0" maxOccurs="unbounded" />
<xs:element name="footnotes" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="headerType">
<xs:sequence>
<xs:element name="vpuMission" type="xs:string" />
<xs:element name="strategicContext" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="strategicObjectiveType">
<xs:sequence>
<xs:element name="objective" type="objectiveType" />
<xs:element name="subObjective" type="objectiveType" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
What are the problems you get when using the Schema and adding mapping
annotations?
Thanks
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
> hi,
> I have a sample xml data and sample xsd as follows: the xsd is really big
> and runs into 4 pages. I need to stored data into the data base
> and generate xml that validates against this schema.
> the sqlxml bulk load and view mapper fails to recognize the xsd fine.
> Can some one point me to the right direction .
> Infact storing is working but generating the xml
> using EXPLICIT option is really a pain.
> Regards,
> Balajee
>
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="StrategicPerformanceContract"
> targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:documentation>Strategic Performance Contract</xs:documentation>
> </xs:annotation>
> <xs:element name="root" type="contractType" />
> <xs:complexType name="contractType">
> <xs:sequence>
> <xs:element name="transactionHeader" type="transactionHeaderType" />
> <xs:element name="documentHeader" type="documentHeaderType" />
> <xs:element name="strategicDirection" type="strategicDirectionType" />
> <xs:element name="strategicChoices" type="strategicChoicesType" />
> <xs:element name="riskManagement" type="riskManagementType" />
> <xs:element name="performanceContract" type="performanceContractType" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="transactionHeaderType">
> <xs:sequence>
> <xs:element name="userName" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="documentHeaderType">
> <xs:sequence>
> <xs:element name="contractType" type="xs:ID" />
> <xs:element name="abbr" type="xs:string" />
> <xs:element name="leftTitle1" type="xs:string" />
> <xs:element name="leftTitle2" type="xs:string" />
> <xs:element name="rightTitle1" type="xs:string" />
> <xs:element name="asofDate" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="strategicDirectionType">
> <xs:sequence>
> <xs:element name="header" type="headerType" />
> <xs:element name="strategicObjective" type="strategicObjectiveType"
> minOccurs="0" maxOccurs="unbounded" />
> <xs:element name="footnotes" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="headerType">
> <xs:sequence>
> <xs:element name="vpuMission" type="xs:string" />
> <xs:element name="strategicContext" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="strategicObjectiveType">
> <xs:sequence>
> <xs:element name="objective" type="objectiveType" />
> <xs:element name="subObjective" type="objectiveType" minOccurs="0"
> maxOccurs="unbounded" />
> </xs:sequence>
> </xs:complexType>
|||Michael,
I am trying to map the schema using sql viewmapper and
it gives me an error :
Error while parsing
:Incorrect definition for the root element in schema.
Line 7 Position 63
xmlns:xs="http://www.w3.org/2001/XMLSchema">
regards,
Balajee
"Michael Rys [MSFT]" wrote:

> What are the problems you get when using the Schema and adding mapping
> annotations?
> Thanks
> Michael
> "balajee" <balajee@.discussions.microsoft.com> wrote in message
> news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
>
>
|||Unfortunately, the SQL Viewmapper only works for the older Microsoft schema
language known as XDR and not with the W3C XML Schema language (that you
seem to be using). This unfortunately means that you either need a
third-party tool vendor or notepad.
Best regards
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:0EA7E4AD-F94D-4021-8A22-FE327F1B786B@.microsoft.com...[vbcol=seagreen]
> Michael,
> I am trying to map the schema using sql viewmapper and
> it gives me an error :
> Error while parsing
> :Incorrect definition for the root element in schema.
> Line 7 Position 63
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> regards,
> Balajee
>
> "Michael Rys [MSFT]" wrote:
sql

Generating Reports From LoadTest Database

Dear All,

I have configured reproting services in Sql Server 2005.

I have also created the LoadTest Database.

I have seen Sean Lumley's Blog sample report also visited the link provided by him 'http://blogs.msdn.com/slumley/archive/2006/02/16/533356.aspx' however i am not able to figure out what to do next with this link ,how togenerate report in the format as he has suggested from my load test store.

Regards,

Follow the link to gotdotnet, the report are downloadable from there and can be easily customized to your needs.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Generate Xml in hierarchical order from DB

Hi

I have a table named UserProfile with following structure :

(TypeIdbigint,TypeNamenvarchar(100),ParentTypeIdbigint)

Have some following sample data :

TypeId TypeName ParentTypeId

1User0

2Artist1

3Singer2

4DJ1

5Band1

6Partner0

7Movies6

8GoodMovies7

9BadMovies7

10Producer6

I want to generate the XML in following form

<Main>

<TypeId>1</TypeId>

<TypeName>User</TypeName>

<ParentTypeId>0</ParentTypeId>

<SubProfiles>

<SubProfile>

<TypeId>2</TypeId>

<TypeName>Artist</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>4</TypeId>

<TypeName>DJ</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>5</TypeId>

<TypeName>Band</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

</SubProfiles>

</Main>

I am usign CTE for recursive query and then using for xml auto mode to generate xml. But the data is not generating when the parent child relation is in depth level.

Kindly anybody tell me the query so that I can generate the xml in heirarchical order of data.

CREATE FUNCTION dbo.GetProfileSubTree(@.TypeId int)
RETURNS XML
BEGIN RETURN
(SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=@.TypeId
ORDER BY TypeId
FOR XML PATH('SubProfile'),ROOT('SubProfiles'),TYPE)
END

GO

SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=0
ORDER BY TypeId
FOR XML PATH(''),ROOT('Main'),TYPE

|||

Hi,

This solution helps me to generate the heirarchical data

Thanks

|||

Note that SQL Server 2005 has a maximum limit of 32 recursively nested function invocations.

This is mentioned here at

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate method for unlimited depth?

Generate Xml in hierarchical order from DB

Hi

I have a table named UserProfile with following structure :

(TypeId bigint,TypeNamenvarchar(100),ParentTypeIdbigint)

Have some following sample data :

TypeId TypeName ParentTypeId

1User0

2Artist1

3Singer2

4DJ1

5Band1

6Partner0

7Movies6

8GoodMovies7

9BadMovies7

10Producer6

I want to generate the XML in following form

<Main>

<TypeId>1</TypeId>

<TypeName>User</TypeName>

<ParentTypeId>0</ParentTypeId>

<SubProfiles>

<SubProfile>

<TypeId>2</TypeId>

<TypeName>Artist</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>4</TypeId>

<TypeName>DJ</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

<SubProfile>

<TypeId>5</TypeId>

<TypeName>Band</TypeName>

<ParentTypeId>1</ParentTypeId>

</SubProfile>

</SubProfiles>

</Main>

I am usign CTE for recursive query and then using for xml auto mode to generate xml. But the data is not generating when the parent child relation is in depth level.

Kindly anybody tell me the query so that I can generate the xml in heirarchical order of data.

CREATE FUNCTION dbo.GetProfileSubTree(@.TypeId int)
RETURNS XML
BEGIN RETURN
(SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=@.TypeId
ORDER BY TypeId
FOR XML PATH('SubProfile'),ROOT('SubProfiles'),TYPE)
END

GO

SELECT TypeId AS "TypeId",
TypeName AS "TypeName",
ParentTypeId AS "ParentTypeId",
dbo.GetProfileSubTree(TypeId)
FROM UserProfile
WHERE ParentTypeId=0
ORDER BY TypeId
FOR XML PATH(''),ROOT('Main'),TYPE

|||

Hi,

This solution helps me to generate the heirarchical data

Thanks

|||

Note that SQL Server 2005 has a maximum limit of 32 recursively nested function invocations.

This is mentioned here at

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate method for unlimited depth?

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/features/mssql/article.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > I'm looking for script sample allowing me to do the same things than
> > "Generate Script" from Enterprise Manager.
> > I want to be able to generate the scripts from e.g, a SP.
> Here is a nice article:
> http://www.databasejournal.com/features/mssql/article.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/feat...le.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Here is a nice article:
> http://www.databasejournal.com/feat...le.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis
> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/featu...e.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Here is a nice article:
> http://www.databasejournal.com/featu...e.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury822.34602709630
Standard433.34602709630
Custom397.34602709630
Modified285.34602709630
Other222.34602709630
More...676.34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury822.10032954961
Standard433.05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
Hi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury822.34602709630
> Standard433.34602709630
> Custom397.34602709630
> Modified285.34602709630
> Other222.34602709630
> More...676.34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury822.10032954961
> Standard433.05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>
|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com
|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunct
ion
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comHi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for eac
h
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFun
ction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass
,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunctio
n
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

Generate .sql ?

Hi!

When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?

Jarod

The quick answer is you can't and the reason is when there is data you need to Backup and Restore because it moves everything including permissions and data in the database, the other option is to Detach and Attach with code, the last option use DTS/SSIS to move the database because it is a data transfer and cleaning tool. I have a faq of all the methods to move a database, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||I agree with Caddre. For database objects, you can only generate DDL statements for them. There is no way to generate .sql for data.

Generate "RDL" xml file with XMLA.

HI,,,,,,,,,,EveryBody...
Does anybody have code sample for on the fly report generation , generate
"RDL" xml file with XMLA.
pls help me,
I am really stuck with this problem , becoz I have posted many posts but
still I didnt get a helpfull machanizam.
Thanks
Summa.Hi Sumudu,
We are working on this project and we have an alpha product now you can
generate your RDL on the fly no XML no VS
www.rdlcomponents.com
Jerry
"Sumudu Prasad" wrote:
> HI,,,,,,,,,,EveryBody...
> Does anybody have code sample for on the fly report generation , generate
> "RDL" xml file with XMLA.
> pls help me,
> I am really stuck with this problem , becoz I have posted many posts but
> still I didnt get a helpfull machanizam.
> Thanks
> Summa.
>
>