ASP Page to run a Report

I am trying to create a report (form) to run from an ASP page with one
single prompt. The ASP pagre would then populate the parameter below
as it passes the data to Report Manager. I am tring to skip the step
of opening IE and go straight to the PDF.
Example
Enter Shipment# [2022222]
[Sumbit] [Quit]
shipnum=variable above
http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
Does anyone have a sample ASP page they would be willing to share?
Thanks
BobThis may be way more than what you need, but search this newsgroup for my
post on 12/11/07 with the terms "Classic ASP" or Google ""Reporting Service
in Classic ASP" to do exactly what I think you want. Once the PDF is
rendered you can open it in IE, save it, or save it directly as a file on a
server (by taking the stream and writing it).
"Bob" <bob@.ebearings.com> wrote in message
news:fa89138c-aa86-41c8-bdc2-c0fe620972d9@.e4g2000hsg.googlegroups.com...
>I am trying to create a report (form) to run from an ASP page with one
> single prompt. The ASP pagre would then populate the parameter below
> as it passes the data to Report Manager. I am tring to skip the step
> of opening IE and go straight to the PDF.
> Example
> Enter Shipment# [2022222]
> [Sumbit] [Quit]
> shipnum=variable above
> http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
> Does anyone have a sample ASP page they would be willing to share?
> Thanks
> Bob

ASP only shows fields which contain numeric data

Hi

I'm using an SQL server database to run a website with ASP.

A new page I'm making will only display the contents on fields containing numeric data and it won't display text.

Other pages work fine. Does anyone have any ideas as to why this is happening - I've never seen in before.

Dave

http://pink-football.com/gossip.aspCould you use one of your ASP variables to be use with a number operator? like "+" or sumthin?

or

How are you calling your SQL server scripts? Through inline with your ASP codes or trough store procedures.

In either case, check your passing of ASP variable into your SQL Script.
Maybe you could miss a '' character.

What is the actual ASP error msg??|||Is it an error or just no data is returned ? Is your query returning both numerics and string data and only the numeric data is showing ? Have you tried to do a response.write to display all fields being returned ? Is it possible that you have single quotes in your string data ? On your web site - what are we looking for - and boy is it pink !!!

ASP Memory Crash

Hi all,
I am running reporting services on a dedicated server with 2GB of memory,
when ever a report is run that contains over 90,000 records the server
crashes and restarts. We have looked at the reports, and are to reduce the
criteria range to make smaller reports, but it then affects role the system
was designed to play in my organisation. If anyone has come across these
memory crashes and come across any ways of resolving them or ways that
haven't resolved them, including an increase in the memory size, it would be
greatly appreciated if you could reply. We are willing and able to throw
another 2GB at the problem, but are concerned this may only be a short term
solution and the problem will reoccur when the reporting size reaches 180,000
records.
Any assistance you may be able to provide would be greatly appreciatedIf you mean that the result set has 90,000 records or 180,000 records then
you have the wrong product. RS is not designed to generate reports that are
1500+ pages. Rendering is done in RAM so there is a direct correlation to
between number of records and the amount of RAM used. If the rendering
output is Excel or PDF then the amount of RAM consumed is even more. If the
destination is another program then there are better ways to do this. I know
that sometimes people are wanting to get a large amount of rows into Excel
for further analysis but it would be better to be using DTS and getting the
data out in CSV for them. Much much faster process.
If the output is not that many records but you have that many because you
are using filters then try to move away from filters. Filters brings over
all the data and then filters it. Use a query parameter instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> Hi all,
> I am running reporting services on a dedicated server with 2GB of memory,
> when ever a report is run that contains over 90,000 records the server
> crashes and restarts. We have looked at the reports, and are to reduce the
> criteria range to make smaller reports, but it then affects role the
system
> was designed to play in my organisation. If anyone has come across these
> memory crashes and come across any ways of resolving them or ways that
> haven't resolved them, including an increase in the memory size, it would
be
> greatly appreciated if you could reply. We are willing and able to throw
> another 2GB at the problem, but are concerned this may only be a short
term
> solution and the problem will reoccur when the reporting size reaches
180,000
> records.
> Any assistance you may be able to provide would be greatly appreciated|||You might try CSV and see if it takes up less memory than Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> If you mean that the result set has 90,000 records or 180,000 records then
> you have the wrong product. RS is not designed to generate reports that
are
> 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> between number of records and the amount of RAM used. If the rendering
> output is Excel or PDF then the amount of RAM consumed is even more. If
the
> destination is another program then there are better ways to do this. I
know
> that sometimes people are wanting to get a large amount of rows into Excel
> for further analysis but it would be better to be using DTS and getting
the
> data out in CSV for them. Much much faster process.
> If the output is not that many records but you have that many because you
> are using filters then try to move away from filters. Filters brings over
> all the data and then filters it. Use a query parameter instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
message
> news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > Hi all,
> >
> > I am running reporting services on a dedicated server with 2GB of
memory,
> > when ever a report is run that contains over 90,000 records the server
> > crashes and restarts. We have looked at the reports, and are to reduce
the
> > criteria range to make smaller reports, but it then affects role the
> system
> > was designed to play in my organisation. If anyone has come across
these
> > memory crashes and come across any ways of resolving them or ways that
> > haven't resolved them, including an increase in the memory size, it
would
> be
> > greatly appreciated if you could reply. We are willing and able to
throw
> > another 2GB at the problem, but are concerned this may only be a short
> term
> > solution and the problem will reoccur when the reporting size reaches
> 180,000
> > records.
> >
> > Any assistance you may be able to provide would be greatly appreciated
>|||Bruce,
Thanks for your responses.
Do you know if there is away to detect if the report is going to generate
over 200 pages and return a message to the user to say something like 'This
report may crash the server, are you sure you want to continue' ? From your
responses, and others I have read about memory, and RS limitations are the
only issue I am encountering, not programming issues, so if we confirm with
users before they run large reports it could solve our problems, unless they
select 'Yes' of course.
Any further assistance you may be able to provide would be greatly
appreciated.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>|||What you could do is have an intermediary report that does a count and then
provides the appropriate message and link. You can hide the real report from
the user in list view so they have to go through this. Also you could use
jump to url and render to Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||Thanks for the feedback Bruce I will give it a go !|||Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to manage the subscriptions (try to delete the irrelevant
ones) in the RS "Report Manager", for that particular report, System gives
an OutOfMemory exception. I think Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
I posted this question here, because I thought the problem is similar to
what you were talking (Memory management Issue).
Any Suggestions are appreciated.
Regards
Raj Chidipudi
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||I have exact same problem. My report is about 28000 rows and it takes about
15 minutes to render and end user gets frustrated and he tries to "End Task"
the browser - every thing hangs up. I can't put drill down, etc., so that
is not my option. Is there any way I can stream data, instead of wait to
retreive all rows from SQL? - just like SQL Query analyzer window - it starts
producing results as soon as you execute the query. This is really a big
issue in our organization. Because of this, people started to hate RS. I
need some kind of solution asap. Please help.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>

ASP cannot run stored proc until the web user has run the proc in Query Analyzer

I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.

The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.

I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.

I finally got it to work again, but this is not a viable solution for
our production environment:

1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.

After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.central_scrutinizer,

Does the ASP page hang, timeout or just return with no results? If it hangs
or times out, this may be happening:

1. The command is executed from the ASP page.
2. SQL Server needs to allocate more disk space (data or log) and performs
an autogrow of a large amount of disk space. This could take an extended
period of time.
3. The user cancels the ASP page or the page times out.
4. SQL Server cancels the transaction and also cancels the need for
additional disk space.
5. If steps 1-4 are repeated, you get the same result.
6. A user runs the stored procedure in QA allowing it to finish.
7. SQL Server allocates the addional space.
8. The ASP page now runs fine because the space has been allocated (until
the next time the database needs to allocate more space).

-- Bill

1. We would run the stored
"central_scrutinizer" <cbellur@.hotmail.comwrote in message
news:1172185833.914945.318670@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

>I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.
>
The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.
>
I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.
>
I finally got it to work again, but this is not a viable solution for
our production environment:
>
1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.
>
After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.
>

AS400 database access

I am trying to run a SQL query from Query Analyzer or from my desktop to data on our AS400. The query checks out ok, but when I execute the query, it just sits there and never returns any data. Can anyone help?Need some more information, want to know if you are using "Linked Server" or just trying to execute the query using a DSN wiht OSQL.

also would like to know which drivers you have used to make the connection with the database on AS400 server.

Thanks.

As400

Hi, all!
I try run DTS from SQL with connection to AS400 (Client Access 32-bit ver. 8).I know this should be about 9000 records, but after 5000 records
it's stopped.
Plz , explain!
I'll gone to be crazy... :(((Run the profiler on the SQL Server to see that what exactly is going on..

As2000 and Analysis Services provider

I can design the dataset using the graphical tools but the report refuses to
run, is there a work around. Presumably the problem will be fixed in the
next service pack?Hello Phil,
If you change the design mode to text, could you get the proper MDX? If you
run the MDX, could you see the correct results?
It seems you custom the data processing extension. Is this true and how you
perform this customization?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Phil Nicholas" <PhilNicholas@.nospam.nospam>
>Subject: As2000 and Analysis Services provider
>Date: Tue, 7 Mar 2006 10:21:40 -0000
>Lines: 5
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <#8D4eCdQGHA.5592@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: cpc2-norw3-4-0-cust28.pete.cable.ntl.com 82.25.55.28
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:69941
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I can design the dataset using the graphical tools but the report refuses
to
>run, is there a work around. Presumably the problem will be fixed in the
>next service pack?
>
>

'AS IS' Upgrade from SQL 2K to SQL 2K5

My advice is to do functionality or, preferably, load testing in a dev\test
environment. As well as create a detailed project plan. I have run into a
lot of issues with upgrades but the clients that did throrough testing made
the fixes a part of the project plan instead of a Monday morning fire fight.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"sacgar" <sacgar@.discussions.microsoft.com> wrote in message
news:8D9F1A4F-B591-4E8D-8F63-0ACF2D0ED91C@.microsoft.com...
> Hi ,
> We are planning for ‘in place’ SQL Upgrade from SQL 2000(Ent. Edition with
> SP4) to SQL 2005 Ent.Edition with SP2 for
> 1)Replication Distribution Server that serves to PULL subscriptions.
> 2)One for the Subscriber Cluster (Active Passive)Server (its distribution
> server already upgraded to SQL 2005)
> We have gone through the requirements from Microsoft Technical Resource
> Guide but looking if somebody had any issues or any advice other than
> Upgrade
> Advisor reported before we move for Upgrade for both of above cases as
> some
> people indicated they had issue with Password Policy , Appreciate your
> reply…
>
Please go through the below article as I have faced this issue , check if
applicable in your scenario...
Thanks
Ajay
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:26E92972-53B2-4063-8D68-24B72C69FB94@.microsoft.com...
> My advice is to do functionality or, preferably, load testing in a
> dev\test environment. As well as create a detailed project plan. I have
> run into a lot of issues with upgrades but the clients that did throrough
> testing made the fixes a part of the project plan instead of a Monday
> morning fire fight.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "sacgar" <sacgar@.discussions.microsoft.com> wrote in message
> news:8D9F1A4F-B591-4E8D-8F63-0ACF2D0ED91C@.microsoft.com...
>
|||Sorry Missed this link earlier
Please go through this link for DB Move
http://support.microsoft.com/kb/314546/
Thanks
Ajay
"Ajay" <ajudba@.gmail.com> wrote in message
news:OsXeYk0ZIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Please go through the below article as I have faced this issue , check if
> applicable in your scenario...
> Thanks
> Ajay
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:26E92972-53B2-4063-8D68-24B72C69FB94@.microsoft.com...
>

AS Configuration

Hi

I try to run the following statement in “Server A” and it works fine then I make a copy of my cube (with backup & restore) in “Server B” and the MDX statement does not work.

My statement:

With

member [Measures].[Acum_Amount] as

'sum(

( YTD([Time].[Time].CurrentMember)

, [Scenario].[Scenario].&[PLAN])

, [Measures].[ Amount]

)/1000'

Select [Measures].[Acum_Amount] on 0

, [Time]. [Time].[Year].&[2005E3].&[Q1/ 2005].&[2/ 2005]

From [FT_BAB]

If I remove the code in red, it works in both servers. But it is not what i want.

What is wrong with the server configuration?

The SUM() function takes a set as its first argument and you are passing a tuple.

Sum( Set_Expression [ , Numeric_Expression ] )

I think what you are looking for on both servers is the following:

With

member [Measures].[Acum_Amount] as

'sum(

YTD([Time].[Time].CurrentMember),

([Scenario].[Scenario].&[PLAN], [Measures].[ Amount])

)/1000'

|||

You are right. Your syntax works in both of the servers. I will replace it.

I was confused because the other syntax works in one of the servers.

Thanks!!!!!!!!!!!!!!

Arithmetic overflow

Hi,
Every time a run a query where dates are included, I get an 'Arithmetic
overflow error converting expression to data type datetime'.
The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
AS Month' syntax is used.
I think that somehow, some of the dates are corrupted.
How can I found out which of then are corrupted? There're too many to scroll
through.
TIA,
Ana
************************************************** *****************************
"Eres dueo de lo que callas y esclavo de lo que dices"
"Judge your success by what you had to give up in order to get it"
************************************************** *****************************
On Tue, 28 Sep 2004 23:22:39 +0200, Ana wrote:

>The dates are stored as yyyymmdd (nvarchar 8) and usually the 'month(DATE_X)
>AS Month' syntax is used.
>I think that somehow, some of the dates are corrupted.
>How can I found out which of then are corrupted?
Hi Ana,
SELECT YourDateCol
FROM YourTable
WHERE ISDATE(YourDateCol) = 0
And change your database design: use the datetime format to store dates
instead. <g>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Arithmetic error

When I run a view, I am getting an SQL (ODBC) error "Arithmetic overflow
error converting real to data type numeric." Below is the line of code in
the view that I think is causing the error (dbo.WorkerTimesheets.PayRate is
a real data type and has a value of 730). Can anyone help me fix it?
Thanks.
CONVERT(varchar(6), CONVERT(decimal(4, 2), dbo.WorkerTimesheets.PayRate)) +
'/' + dbo.PayRateCodes.RateName AS txtPayRate
DavidOf course it overflows. You reserve 4 places for the entire numeric, 2 of
which are decimal places.
730 already takes up 3 places. Add 2 for decimals to that and you get 5.
Give it more room. :) I'd suggest using decimal(12, 2). If this is used to
store wages or salaries, 8 places is more than enough. :)
Spend more time reading BOL.
ML|||Geez! When I said 8 I meant 10. Yes, it's late...|||Do not use CONVERT() when you have CAST(). Never use FLOAT or REAL
unless you know what you are doing. There are all kinds rounding
errors and they display with the 'E' format.
Why are you converting numerics to strings? The first rule of a tiered
architecture is that you never do formatting in the database.
Re-do your pay rates with DECIMAL() data type; you do not bill to 16
decimal places, do you? . Read up on floating point numbers.|||You are all correct. The "overflow" was my brain. When I look at it
now, it makes sense.
David
*** Sent via Developersdex http://www.examnotes.net ***

are there any MSDE T-SQL code limitations?

I am writing a database app which will run under MSDE on the user's systems.

Other than limitations as to the number of concurrent users, are there any
issues that I need to be aware of when programming the database using SQL
Server 2000? i.e., are there T-SQL programming statements that will run
under SQL Server 2000 as part of my development enviornemnt that won't run
under MSDE on the end users machines, or will MSDE handle anything that SQL
Server can from the database engine standpoint?

Thanks!hi Mike,
"Mike N." <BadDog@.thepound.com> ha scritto nel messaggio
news:r3erb.5780$942.4765@.newssvr25.news.prodigy.co m...
> I am writing a database app which will run under MSDE on the user's
systems.
> Other than limitations as to the number of concurrent users, are there any
> issues that I need to be aware of when programming the database using SQL
> Server 2000? i.e., are there T-SQL programming statements that will run
> under SQL Server 2000 as part of my development enviornemnt that won't run
> under MSDE on the end users machines, or will MSDE handle anything that
SQL
> Server can from the database engine standpoint?

all T-SQL statements SQL Server 2000 supports are supported by MSDE...
you only (only...) have database limitation size, limited replication
support, no mail support...
for further info please visit
http://www.microsoft.com/sql/msde/p...fo/features.asp

hth
Andrea Montanari (Microsoft MVP - SQL Server)
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply

Are sub-subqueries possible?

Hi all,

I've seen mention that you can use nested subqueries down to as many
levels as you like but whenever I run the following:

select * from table1
where tab1ID in
(select tab1ID from table2 where tab2ID in
(select tab2ID from Table3 where Tab3ID=N))

I get the error "incorrect syntax near the keyword 'where'"

Can anyone confirm that sub-subqueries are possible? If so is the
syntax I'm using correct?

I'm running SQL 2000 through a Delphi 5 app.

Regards
Jonyes look at this example in the pubs DB

use pubs
select * from authors
where au_id in(select au_id from authors where au_id in( select au_id
from authors where au_id ='172-32-1176'))

You can also write your query as follows
select t1.* from table1 t1
join table2 on t1.tab1ID =t2.tab1ID
join Table3 t3 on t3.tab2ID =t2.tab2ID
where t3.Tab3ID=N --this should be 'N' or a number probably ?

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis

yeah I just installed Query Analyser & ran my query through that & it
worked fine so it must be a limitation of the Delphi TQuery component.

Oh well.

I'll try a join as you suggested instead.

Thanks for your time
Jon