Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

Having problems deploying a report.

I am working with a MS 2005 SQL Server database. I have the following error message. A connection could not be made to the report server http://localhost/ReportServer. I am trying to develop these on a network server. Is this a configuration issue, that it is expecting this on the local machine.

Thanks!

Terry B

Hi,

are you sure that the user (which deploy the report) have access rights to the report server. You can administrate this under http://localhost/reports and then under security, there you must add the AD-user.

I hope this help.

|||

When I go on the internet and type in Http://localhost I receive a not found error message. It claims to be Under Construction and references the IIS Help. But other reports from the server do deploy and I cannot see them from either machine

Thanks!

Terry

|||

Hi,

when localhost the SQL-Reporting-Server is, you must take the url http://localhost/Reports! It is importen to give the subweb Reports to go to the administrationwebsite of the reporting server...

Having Problem with SQL Express 2005

I just started working with sql server 2005 and i have a windows-based application in C# with an attached sql server database file.Everything is ok but i don't know if i can create a password for that database.
there is no IDE for sql server 2005 like Enterprise Manager in SQL Server 2000 to do such things(Creating roles,passwords,...).In my computer I've installed VS 2005 Professional and SQL Express.I think i should do all thease thing with system stored procdures right? But it's really boring do all those things without an IDE.

Thanks in advance.

If you have a look at the following link you will find a download for SQL Server Management Studio Express, this is an IDE based of the full management studio (Replacement for Enterprise manager and Query tools) that can be used for the express editions.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||Thank you

Having a hard time connecting to sample SQL database from ASP.Net Page

I am working through the book ASP.Net Unleashed Second Addition. I am trying to open a simple database connection. (using the sample "pubs" db from SQL Server)
I have copied the author's code exactly, but it just won't work. I keep getting this error:
What am I doing wrong???
Description:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message:BC30182: Type expected.
Source Error:

Line 5: Line 6: Sub Page_LoadLine 7: Dim conPubs As SqlConnectionLine 8: conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )Line 9: conPubs.Open()

What namespaces have you imported?|||Coding for my namespace is
<%@. Import Namespace="System.Data.SqlClient" %>
This is copied directly from the authors example.... so I don't know if it is right lol
Then under the page_load event
I typed:
<script Runat="server">
Sub Page_Load
Dim conPubs as SqlConnection
conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" )
conPubs. Open()
End Sub
</script>
The text "Connection Opened!" Is supposed to show up if the coding is correct...
Help hehe just a beginner :)|||Does anyone know some awesome websites that teach you Visual Studio.Net 2003 and database stuff? I have the hardest time finding things in the MSDN library....|||Try:
http://learnvisualstudio.net/|||

I don't know if VB.NEt support the continuation stuff.
If it does, may be u have to put a continuation sign at the end of line 7.
Cheers.

sql

Have two active SQL servers

Right now I am working on a project that requires two active SQL servers with
different local web servers accross geographic locations. I read an article
on Active-Active SQL servers and am seeing some possiblity in SQL 2005 for
this situation to work but in SQL 2000 is there any way to have two servers
with independent RAID arrays sync over the network to have different users
modifying records?
Thanks,
Joseph Ronzio
You are basically asking if you can use Majority Node Set (MNS) clustering.
SQL 2000 does not support it. Some third parties does have excellent
products that work. NSI is one that comes to mind, LeftHand software, etc.
Check my website for more.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:772EC28E-A275-42B5-A610-3925F0C3542B@.microsoft.com...
> Right now I am working on a project that requires two active SQL servers
> with
> different local web servers accross geographic locations. I read an
> article
> on Active-Active SQL servers and am seeing some possiblity in SQL 2005 for
> this situation to work but in SQL 2000 is there any way to have two
> servers
> with independent RAID arrays sync over the network to have different users
> modifying records?
> Thanks,
> Joseph Ronzio
|||And SQL Server Clustering does not support 2 nodes modifying data in the
same physical DB (neither does Oracle or Sybase clustering).
If you need 2 nodes modifying data, look at replication.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> You are basically asking if you can use Majority Node Set (MNS)
clustering.[vbcol=seagreen]
> SQL 2000 does not support it. Some third parties does have excellent
> products that work. NSI is one that comes to mind, LeftHand software, etc.
> Check my website for more.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:772EC28E-A275-42B5-A610-3925F0C3542B@.microsoft.com...
for[vbcol=seagreen]
users
>
|||So MySQL, Lotus Domino and IBM DB2 can do this but MS SQL can't? From my
reading it looks like SQL 2005 might be able to do this will it?
Thanks,
Joseph Ronzio
"Mike Epprecht (SQL MVP)" wrote:

> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
>
|||Hi
No, SQL Server 2005 will not. There will still be one primary node that
handles all the work.
Clustering by definition, is for high availability, not load balancing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:E2BF74E7-ADEE-4C9F-B183-44D1D6C87122@.microsoft.com...[vbcol=seagreen]
> So MySQL, Lotus Domino and IBM DB2 can do this but MS SQL can't? From my
> reading it looks like SQL 2005 might be able to do this will it?
> Thanks,
> Joseph Ronzio
> "Mike Epprecht (SQL MVP)" wrote:
etc.[vbcol=seagreen]
servers[vbcol=seagreen]
2005[vbcol=seagreen]
|||That's only true about SQL Server!
Oracle RAC (and 10g grid) does support modifying the same shared physical DB
from any node. Basically, the same physical database is exposed to multiple
nodes with consistency controlled via distributed lock manager. I believe
Sybase has also just started to offer such a feature.
In theory at least, this approach offers both failover (HA) and scalability
(dynamic load balancing over more nodes since a query can be routed to any
node for processing).
Linchi
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:O6S7HdG5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
|||Ok apparently there was some confusion when I posted this I'm not talking the
same physcial database I am talking about replication where two database
servers have the same database but different copies. Can both database
servers have modification made to the database and replicate changes?
"Mike Epprecht (SQL MVP)" wrote:

> And SQL Server Clustering does not support 2 nodes modifying data in the
> same physical DB (neither does Oracle or Sybase clustering).
> If you need 2 nodes modifying data, look at replication.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:#0Y8BfF5EHA.2624@.TK2MSFTNGP11.phx.gbl...
> clustering.
> for
> users
>
>
|||You can do two-way replication, but what do you gain? Both servers not only
have to handle all the updates but they have to handle synching them with
their partner. The nodes won't split the work unless the database uses
partitioned views, something that only fits a very few scenarios.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:41FDC5C9-93E1-4639-BDAC-BE3648F322A2@.microsoft.com...
> Ok apparently there was some confusion when I posted this I'm not talking
the[vbcol=seagreen]
> same physcial database I am talking about replication where two database
> servers have the same database but different copies. Can both database
> servers have modification made to the database and replicate changes?
> "Mike Epprecht (SQL MVP)" wrote:
etc.[vbcol=seagreen]
servers[vbcol=seagreen]
2005[vbcol=seagreen]
|||I would get the capability to have two servers in different geographic
regions with two different web farms for referencing and modifying data.
Granted there could be conflicts generated but in Lotus Domino today I don't
have that problem with our typical business records and I'd be migrating the
same application to SQL. I would just need the resources that would instruct
me how to do this for SQL.
"Geoff N. Hiten" wrote:

> You can do two-way replication, but what do you gain? Both servers not only
> have to handle all the updates but they have to handle synching them with
> their partner. The nodes won't split the work unless the database uses
> partitioned views, something that only fits a very few scenarios.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:41FDC5C9-93E1-4639-BDAC-BE3648F322A2@.microsoft.com...
> the
> etc.
> servers
> 2005
>
>
|||Have you considered Merge Replication?
Or if you want to use Transactional Replication then you can use Transactional Replication with Updating Subscribers.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation Result precision Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

|||

I have the same question. How SQL decide the number of decimal in division operator. the formulation in the online book does not make sense. I got 6 decimal when I used (28,18) /(28,18) but over 20 decimal when I used (28,18)/(18,4).

looks like there is no problem for addtion, subtraction, and multiplication operators.

can anyone help ? thx.

sql

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

Has anyone resolved [sqsrvres] StartResourceService: StartService?

Hello all,
I am working with a 2 node SQL Server 2000 cluster on Windows Server 2003.
Each node is a primary node for one of two SQL instances (Active/Active).
Everything worked fine until I applied the latest SQL Server service pack
(MS03-031). When I try to move a group (disks and SQL resources) from a
primary node to the secondary node the disks transfer fine but the SQL Server
service fails to start and the group goes back to its original owner after 3
tries. The following errors are in the event log. Any ideas? -Phil
[sqsrvres] StartResourceService: StartService (MSSQL$Inst1) failed. Error:
41d
[sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 41d)
[sqsrvres] OnlineThread: Error 41d bringing resource online.
Error 41d corresponds to message:
The service did not respond to the start or control request in a timely
fashion.
So the problem seems the sql service itself is unable to start on the other
node. To troubleshoot further you will need to
1- check app + sys logs for any other clues, what errors do you see when
attempting to start the MSSQL$Inst1
do you see that the MSSQL$Inst1 started but then shut down? Are there any
errors stating some dll could not be loaded? Or do you only see the errors
you reported?
2. check the sql error logs(c:\program files\microsoft sql
server\mssql$INST1\LOG\errorlog), if SQL Server started a new errorlog file
should have created during this time. Was one created, if so check for any
errors
3. Check for: "The application failed to initialize properly" error when
starting SQL - ID: 326571.KB.EN-US
4. can you start sql on from a command prompt on the second node? To do
this you will need to fail over to node2, bring online the network name and
sql disks, now run (sql resource would still be offline/failed state)
c:\program files\microsoft sql server\mssql$INST\binn\sqlservr -iINST -c
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
sql

Has anyone resolved [sqsrvres] StartResourceService: StartService?

Hello all,
I am working with a 2 node SQL Server 2000 cluster on Windows Server 2003.
Each node is a primary node for one of two SQL instances (Active/Active).
Everything worked fine until I applied the latest SQL Server service pack
(MS03-031). When I try to move a group (disks and SQL resources) from a
primary node to the secondary node the disks transfer fine but the SQL Server
service fails to start and the group goes back to its original owner after 3
tries. The following errors are in the event log. Any ideas? -Phil
[sqsrvres] StartResourceService: StartService (MSSQL$Inst1) failed. Error:
41d
[sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status 41d)
[sqsrvres] OnlineThread: Error 41d bringing resource online.Error 41d corresponds to message:
The service did not respond to the start or control request in a timely
fashion.
So the problem seems the sql service itself is unable to start on the other
node. To troubleshoot further you will need to
1- check app + sys logs for any other clues, what errors do you see when
attempting to start the MSSQL$Inst1
do you see that the MSSQL$Inst1 started but then shut down? Are there any
errors stating some dll could not be loaded? Or do you only see the errors
you reported?
2. check the sql error logs(c:\program files\microsoft sql
server\mssql$INST1\LOG\errorlog), if SQL Server started a new errorlog file
should have created during this time. Was one created, if so check for any
errors
3. Check for: "The application failed to initialize properly" error when
starting SQL - ID: 326571.KB.EN-US
4. can you start sql on from a command prompt on the second node? To do
this you will need to fail over to node2, bring online the network name and
sql disks, now run (sql resource would still be offline/failed state)
c:\program files\microsoft sql server\mssql$INST\binn\sqlservr -iINST -c
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Hardware requirements

I would like to know what are the suggested hardware
requirements for a Windows 2000 server box with SQL 2000
working in a very much busy environment.This question is so broad I doubt anyone could respond with a good answer.
You would need to perform some baseline testing with hardware to see how
your app performs then purchase appropriately.
-Lars
"Ricardo Bruno" <ricardo_bruno@.btsincusa.com> wrote in message
news:424401c3e435$7ef63490$a101280a@.phx.gbl...
> I would like to know what are the suggested hardware
> requirements for a Windows 2000 server box with SQL 2000
> working in a very much busy environment.|||All together now...
"It Depends"
Seriously, you need to define just what 'busy' means. Batch
requests/second, transactions, client connections, database size and
throughput are all part of the mix. Most of the major server vendors have
tools to 'estimate' the size of a host server for SQL. They do a failrly
good job as long as your situation is not too unusual. Without a lot more
information I cannot give even a wild guess as to what you need.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Ricardo Bruno" <ricardo_bruno@.btsincusa.com> wrote in message
news:424401c3e435$7ef63490$a101280a@.phx.gbl...
> I would like to know what are the suggested hardware
> requirements for a Windows 2000 server box with SQL 2000
> working in a very much busy environment.|||Ricardo,
It is a loaded question.
It is very hard to give any recommendation or suggestions based ont eh
information you have given.
Please check out the Operations Guide put together by Microsoft at the
following link.
Look at Chapter 6 which deals with Capacity planning. This is a good start.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
HTH
Satish Balusa
Corillian Corp.
"Ricardo Bruno" <ricardo_bruno@.btsincusa.com> wrote in message
news:424401c3e435$7ef63490$a101280a@.phx.gbl...
> I would like to know what are the suggested hardware
> requirements for a Windows 2000 server box with SQL 2000
> working in a very much busy environment.

Hang while trying to browse thr cube

I am working on the Analysis Services

When I am in Business Intelligence Studio and I right-click on the

cube and I choose browse to open the cube-browser, the Cube-Browser

HANGS FOREVER. it never finishes opening. I always end up using the

taskmgr to kill off Business Intelligent Studio. How is this HANGING

problem fixed?

Please make sure whether cube was processed sucesfully before you are browsing it. and also you have sificient permissions to browse the cube.

Thanks

|||Hello,

I have the same problems.
We have a central MSSQL 2005 (OS: Windows 2000) instance and several clients without a local MSSQL Installation. The people create cubes and there exist 2 problems:
- When the user wants to create/deploy the cube on the central instance he needs administrator rights on the base directory of the mssql installation. (I gave the user that right but it is not the way i want to fix the problem)
- It is not possible that a user can browse a cube. I tested the same process on a machine without any firewall (cleint side) and it works. This machine was not in the same network like the other clients. I tested it again and turned off the windows firewall on the clients in the same network, but nothing changed.

Could anybody help me?

Tim

Handling GUID/UniqueIdentifier

Hi,

I am presently working on a ETL Process of importing data from XML source to the database (SQL Server 2000/2005).

I have GUID data in the XML file and i need to import that data into the database tables.

My Clarification is, if i import the GUID from XML to the database table, in the future, if the Database Engine generates new GUID's for new data, is there a posibility that the database engine might generate SAME guid as the one i imported from XML? As the GUID's in XML were not generated by the target Database Engine, can the database engine possibly generate the same GUID similar to the one i imported from XML?

Regards,

Vikram

If the Guid column in your table has a UNIQUE constraint applied, then you will have no problem.|||GUIDs have a theoritical possibility of duplication once every 100 years somewhere in one computer in the istalled base of every computer in the world. (approximate)|||

Thanks. I just wanted to have it confirmed.

Regards,

Vikram

Handling errors in DTS packages

HI!!

I've been working for an year or so with DTS, but it still makes me mad with it's cryptic error messages!!!!

"The task reported failure on execution" is one of the "funny" error messages I retrieve. I've tried with the log option, but error messages stored there are as cryptic as the one shown on the screen!!!!!

Timothy Peterson in "MS SQL Server 2000 DTS" provides code chunks that can be used to "decode" numerical error messages into something readable and understandable, but I really don't realize where should I put that code :( It seems to work only if you are executing packages via Visual Basic, and not using the MMC

That's it, I really do need help with this!!!!!!! I beleive that there's someone out there that had faced and solved this problem !

Thanks in advance
lorenaWhy do you have to use DTS?|||I'm working on an ETL process for a Data Warehouse
Oracle "holds" the source databases and the DW is implemented on SQL Server 2000.


Originally posted by Brett Kaiser
Why do you have to use DTS?

HANDLE ERRORS IN SP_XML_PREPAREDOCUMENT

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT an
d
handle any error messages returned.
The problem is, when the procedure reports a parse error my procedure stops.
I can't get my procedure to go on and just report the error. ...
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML code the procedure stops when executing
SP_XML_PREPAREDOCUMENT with an error:
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
Can I prevent MY procedure from stopping when I send bad XML-data to
SP_XML_PREPAREDOCUMENT?Hi
I am guessing you are using SQL Server 2000.
You could try creating a wrapper sproc for the sp_xml_preparedocument, that
returns the handle number, calling it in your outer sproc and handling error
s
generated where your wrapper sproc fails.
The errors will still be thrown but your outer sproc will carry on. A word
of caution - make sure you clean up after it. with sp_xml_removedocument
otherwise you will have trouble with memory getting full of junk XML DOMs.
In fact this may happen anyway with bad XML since you will not get a valid
handle back from sp_xml_preparedocument if it fails and it will have grabbed
some memory. I found the only way to avoid this is to reboot your server
when you start getting memory errors.
Regards
John Harrison
"LakritZtrollet" wrote:

> I'm working on a procedure to import XML-data in a database. First I want
to
> verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT
and
> handle any error messages returned.
> The problem is, when the procedure reports a parse error my procedure stop
s.
> I can't get my procedure to go on and just report the error. ...
> My (simplified) code looks like this:
> CREATE PROCEDURE SP_ADD_XML
> @.XML_XMLSTR TEXT
> AS
> BEGIN
> DECLARE @.FILEHANDLE INT
> DECLARE @.RESULT INT
> EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
> ...
> END
> GO
>
> If I try to parse a bad XML code the procedure stops when executing
> SP_XML_PREPAREDOCUMENT with an error:
> "Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Li
ne
> 2
> XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
> Can I prevent MY procedure from stopping when I send bad XML-data to
> SP_XML_PREPAREDOCUMENT?

Handle Errors In Sp_xml_preparedocument

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT a
nd handle error messages returned.
The problem is, when the procedure reports a parse error the procedure stops
.
I can't get my procedure to go on and just report the error.
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML document the procedure stops when executing SP_X
ML_PREPAREDOCUMENT with an error:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'.
Can I prevent MY procedure from stopping when I send wrong XML-data to SP_XM
L_PREPAREDOCUMENT?Hello LakritZtrollet,
SQL 2000 or 2005?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL2000 SP3
"Kent Tegels" skrev:

> Hello LakritZtrollet,
> SQL 2000 or 2005?
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello LakritZtrollet,
Sorry, no good suggestions here.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/