hi
well I am working on a project that our database has 2, 8000 record table that they have triggers on the update of their feilds
our application must update these tables but it takes a long time to do this
I know there is something named bulk insert but I couldn't find sth similar to this command for update
so would you please help me to find a faster way to update these tables?
thanks for your attention
Best Regards
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
hi,
netman Mo wrote:
> hi
> well I am working on a project that our database has 2, 8000 record
> table that they have triggers on the update of their feilds our
> application must update these tables but it takes a long time to do
> this
> I know there is something named bulk insert but I couldn't find sth
> similar to this command for update
> so would you please help me to find a faster way to update these
> tables?
nope.. update syntax is not overloaded with bulk operators..
if the cause of your delay is dependent on the trigger fired by the update
statement, you should perhaps check it's code... or... if you are sure the
updates you are performing do not involve the trigger check, you can disable
it before executing the statements..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
Showing posts with label project. Show all posts
Showing posts with label project. Show all posts
Tuesday, March 27, 2012
A duplicate value cannot be inserted into a unique index
I'll first give the caveat that I've been away from this project for many
weeks (but at least it is my own creation). Having said that, I'm not sure
if this is a replication problem or exactly what I have on my hands here.
When I submit an Insert to a particular table, I can submit as many Inserts
as I would like with no exceptions -- until I replicate my changes back to
the server, then download that table again. It would appear that I have
identical data in both the SQLCE table and the SQL2k table, but for a reason
that I have yet to figure out, the downloaded table will no longer accept
Inserts. I get the message: "A duplicate value cannot be inserted into a
unique index. [,,,,,].
I do not have indexes on any of the columns. I do have a primary key. Any
advice would be appreciated.
It means you are trying to insert identical values into your PK.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'll first give the caveat that I've been away from this project for many
> weeks (but at least it is my own creation). Having said that, I'm not sure
> if this is a replication problem or exactly what I have on my hands here.
> When I submit an Insert to a particular table, I can submit as many
> Inserts as I would like with no exceptions -- until I replicate my changes
> back to the server, then download that table again. It would appear that I
> have identical data in both the SQLCE table and the SQL2k table, but for a
> reason that I have yet to figure out, the downloaded table will no longer
> accept Inserts. I get the message: "A duplicate value cannot be inserted
> into a unique index. [,,,,,].
> I do not have indexes on any of the columns. I do have a primary key. Any
> advice would be appreciated.
>
|||Nope. Identical values into unique indexed columns. I just dropped the
indexes for now.
"ChrisR" <noemail@.bla.com> wrote in message
news:uFug$fHNFHA.576@.TK2MSFTNGP15.phx.gbl...
> It means you are trying to insert identical values into your PK.
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
>
weeks (but at least it is my own creation). Having said that, I'm not sure
if this is a replication problem or exactly what I have on my hands here.
When I submit an Insert to a particular table, I can submit as many Inserts
as I would like with no exceptions -- until I replicate my changes back to
the server, then download that table again. It would appear that I have
identical data in both the SQLCE table and the SQL2k table, but for a reason
that I have yet to figure out, the downloaded table will no longer accept
Inserts. I get the message: "A duplicate value cannot be inserted into a
unique index. [,,,,,].
I do not have indexes on any of the columns. I do have a primary key. Any
advice would be appreciated.
It means you are trying to insert identical values into your PK.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'll first give the caveat that I've been away from this project for many
> weeks (but at least it is my own creation). Having said that, I'm not sure
> if this is a replication problem or exactly what I have on my hands here.
> When I submit an Insert to a particular table, I can submit as many
> Inserts as I would like with no exceptions -- until I replicate my changes
> back to the server, then download that table again. It would appear that I
> have identical data in both the SQLCE table and the SQL2k table, but for a
> reason that I have yet to figure out, the downloaded table will no longer
> accept Inserts. I get the message: "A duplicate value cannot be inserted
> into a unique index. [,,,,,].
> I do not have indexes on any of the columns. I do have a primary key. Any
> advice would be appreciated.
>
|||Nope. Identical values into unique indexed columns. I just dropped the
indexes for now.
"ChrisR" <noemail@.bla.com> wrote in message
news:uFug$fHNFHA.576@.TK2MSFTNGP15.phx.gbl...
> It means you are trying to insert identical values into your PK.
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:uXndNKDNFHA.2020@.TK2MSFTNGP10.phx.gbl...
>
A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||
Thanks Phil for the quick response..
I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)
So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?
Guess this is a big question 
thanks
Ravi
|||If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.
If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.
|||Hi Darren, I think u didnt get my problem right?
I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..
A Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||
Thanks Phil for the quick response..
I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)
So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?
Guess this is a big question
thanks
Ravi
|||If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.
If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.
|||Hi Darren, I think u didnt get my problem right?
I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..
sqlA Custom component for use as a VIEW in SSIS- Is it possible to create one MERGE like component
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
You have a few options. You can implement views in the database engine (not sure why it breaks your data flow, but it's perhaps the best option), you can join your tables via SQL in an OLE DB Source component, or you can use multiple OLE DB Source connections and then use one or more merge join transformations.Just an FYI - If your data is sorted with an ORDER BY clause in the SQL statement, you can set the ISSORTED flag to true (1) on the OLE DB source and a SORT component won't be required.|||
Thanks Phil for the quick response..
I said "using Views breaks my data flow" - I meant all views cannot be represented in SSIS data flow right? so it becomes difficult later on to understand/change the data flow coz I'll have to check views in database manager and other stuff in SSIS. OR is there a way we can export the whole data flow into some kinda diagram so I can see it all at once? (I don't think there is right?)
So my problem boils down to creating a VIEW type custom component. I have seen other custom component samples but don't know how to do JOINs via code. Can you give me a brief idea for, say, if I have to join 2 tables how many PipelineBuffers I'll need (2 for input and 1 for output?) and how do I match values between the two joining columns(col1.value==col2.value?)?
Guess this is a big question
thanks
Ravi
|||If you want to power and performance gain of views and T-SQL, then use T-SQL. Use the relational engine for what it is good for is my opinion. A component to avoid having a view/SELECT when it would do the job best of all is just daft.
If you want some of the re-use of a view but without the object itself, you may want to look at Data Source Views. They are a design-time only feature, but are stored in your SSIS project so may meet your requirement. So you would use the same SELECT statement as in the view, but it would not be a SQL object.
|||Hi Darren, I think u didnt get my problem right?
I just want help creating a MERGE type component which can join more than 2 inputs. I understand I can use more than one MERGEs to do the same, which ill do if creating the component is a pain ..
Sunday, March 25, 2012
A connection could not be made to the reporting server http://localhost/reportserver
Hi all,
When I build a simple project using the report wizard project.
When i deploy the report i get the following error.
TITLE: Microsoft Report Designer
A connection could not be made to the report server http://localhost/ReportServer.
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<?xml version="1.0" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Server Unavailable
</title>
</head>
<body>
<h1><span style="font-family:Verdana;color: #ff3300">Server Application Unavailable
</span></h1>
<span style="font-family:Verdana;">
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
</span>
<b>Administrator Note:
</b> An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
</body>
</html>
--. (Microsoft.ReportingServices.Designer)
BUTTONS:
OK
Could some one come to my rescue.
Thanx in advance.
Ronald
Is Reporting services installed on the machine you are designing on? If not, go to the project properties and change the report server url to the machine where Report Server is installed on.|||I'm getting the exact same error, but I'm working off of the same machine. To clarify, SQL and MS Visual Studio are on the same machine -- I've created the report and tried to deploy in MSVS.
Note that this machine is part of a network governed by a file & print server.
I can get a valid web page at http://localhost that changes to http://localhost/default.aspx and displays a Home page to a Team Web Site.
But when I try http://lolcalhost/reportsserver, I get the following errors:
Server Error in '/ReportServer' Application.
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly 'Microsoft.BusinessFramework' or one of its dependencies. The system cannot find the file specified. (c:\inetpub\wwwroot\web.config line 77)
Source Error:
Line 75: <add name="WindowsAuthentication" type="System.Web.Security.WindowsAuthenticationModule" /> Line 76: <!--<add name="Session" type="System.Web.SessionState.SessionStateModule"/>--> Line 77: <add name="EnterpriseInitializationModule" type="Microsoft.BusinessFramework.Security.EnterpriseInitializationModule,Microsoft.BusinessFramework" /> Line 78: <add name="BPAdminRedirectModule" type="Microsoft.BusinessFramework.Portal.Administration.BPAdminRedirectModule,Microsoft.BusinessFramework.Portal" /> Line 79: <add name="BPHomeRedirectModule" type="Microsoft.BusinessFramework.Portal.Shell.BPHomeRedirectModule,Microsoft.BusinessFramework.Portal" />Source File: c:\inetpub\wwwroot\web.config Line: 77
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
Can you help?
A connection could not be made to the reporting server http://localhost/reportserver
Hi all,
When I build a simple project using the report wizard project.
When i deploy the report i get the following error.
TITLE: Microsoft Report Designer
A connection could not be made to the report server http://localhost/ReportServer.
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<?xml version="1.0" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Server Unavailable
</title>
</head>
<body>
<h1><span style="font-family:Verdana;color: #ff3300">Server Application Unavailable
</span></h1>
<span style="font-family:Verdana;">
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
</span>
<b>Administrator Note:
</b> An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
</body>
</html>
--. (Microsoft.ReportingServices.Designer)
BUTTONS:
OK
Could some one come to my rescue.
Thanx in advance.
Ronald
Is Reporting services installed on the machine you are designing on? If not, go to the project properties and change the report server url to the machine where Report Server is installed on.A connection could not be made to the report server http://localhost/ReportServer.
Hello Guys,
Please take me out from this hell. I created report using VS2005 Business Intelligence Project. It builds fine. I can also see data in the design time. But when I am trying to deploy it gives me this error.
TITLE: Microsoft Report Designer
A connection could not be made to the report server http://localhost/ReportServer.
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>Configuration Error</title>
<style>
body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;}
p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px}
b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px}
H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }
H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }
pre {font-family:"Lucida Console";font-size: .9em}
.marker {font-weight: bold; color: black;text-decoration: none;}
.version {color: gray;}
.error {margin-bottom: 10px;}
.expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; }
</style>
</head>
<body bgcolor="white">
<span><H1>Server Error in '/ReportServer' Application.<hr width=100% size=1 color=silver></H1>
<h2> <i>Configuration Error</i> </h2></span>
<font face="Arial, Helvetica, Geneva, SunSans-Regular, sans-serif ">
<b> Description: </b>An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
<br><br>
<b> Parser Error Message: </b>An error occurred loading a configuration file: Failed to start monitoring changes to 'c:\inetpub\wwwroot' because access is denied.<br><br>
<b>Source Error:</b> <br><br>
<table width=100% bgcolor="#ffffcc">
<tr>
<td>
<code><pre>
[No relevant source lines]</pre></code>
</td>
</tr>
</table>
<br>
<b> Source File: </b> c:\inetpub\wwwroot\web.config<b> Line: </b> 0
<br><br>
<hr width=100% size=1 color=silver>
<b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
</font>
</body>
</html>
<!--
[HttpException]: Failed to start monitoring changes to 'c:\inetpub\wwwroot' because access is denied.
at System.Web.FileChangesMonitor.FindDirectoryMonitor(String dir, Boolean addIfNotFound, Boolean throwOnError)
at System.Web.FileChangesMonitor.StartMonitoringFile(String alias, FileChangeEventHandler callback)
at System.Web.Configuration.WebConfigurationHost.StartMonitoringStreamForChanges(String streamName, StreamChangeCallback callback)
at System.Configuration.BaseConfigurationRecord.MonitorStream(String configKey, String configSource, String streamname)
at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()
[ConfigurationErrorsException]: An error occurred loading a configuration file: Failed to start monitoring changes to 'c:\inetpub\wwwroot' because access is denied. (c:\inetpub\wwwroot\web.config)
at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
at System.Web.Configuration.RuntimeConfig.GetSectionObject(String sectionName)
at System.Web.Configuration.RuntimeConfig.GetSection(String sectionName, Type type, ResultsIndex index)
at System.Web.Configuration.RuntimeConfig.get_HealthMonitoring()
at System.Web.Configuration.HealthMonitoringSectionHelper..ctor()
at System.Web.Management.HealthMonitoringManager..ctor()
at System.Web.Management.HealthMonitoringManager.Manager()
at System.Web.Management.WebBaseEvent.RaiseRuntimeError(Exception e, Object source)
at System.Web.HttpResponse.ReportRuntimeError(Exception e, Boolean canThrow, Boolean localExecute)
at System.Web.HttpRuntime.FinishRequest(HttpWorkerRequest wr, HttpContext context, Exception e)
-->
--. (Microsoft.ReportingServices.Designer)
BUTTONS:
OK
Hello Rahul,
Since you're getting access denied errors on 'c:\inetpub\wwwroot', can you make sure you have access to that directory? Also, check the security settings within IIS.
Jarret
|||What happens when you try to navigate to http://localhost/ReportServer ?
|||Try using www.sysinternals.com filemon to determine what the cause of the access denied error is.
Here is a similar message with resolution:
http://weblog.gpconsulting.com/archive/2005/08/26/181.aspx
cheers,
Andrew
|||Hello Guys,
Thanks fro your reply.
Jarret: Security Settings witht the IIS is fine.
GregSQL : Here is the error when I run on IE
Server Error in '/ReportServer' Application.
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: An error occurred loading a configuration file: Failed to start monitoring changes to 'c:\inetpub\wwwroot' because access is denied.
Source Error:
[No relevant source lines]Source File: c:\inetpub\wwwroot\web.config Line: 0
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Andrew - T4G : I already gone throgh that link, but does not worth.
|||Did you try filemon?
Here are some detailed steps to troubleshoot.
http://tinyurl.com/3647cg
|||Your report server is not configured properly. Most likely has something to do with IIS and/or the web publishing directory c:\Inetpub\wwwroot.|||Below is the output of Filemon When I try to run http://localhost/Reports/ in IE,
47 11:04:36 AM aspnet_wp.exe:632 QUERY INFORMATION C:\inetpub\wwwroot\web.config NOT FOUND Attributes: Error
48 11:04:36 AM aspnet_wp.exe:632 QUERY INFORMATION C:\inetpub\wwwroot\web.config NOT FOUND Attributes: Error
49 11:04:36 AM aspnet_wp.exe:632 QUERY INFORMATION C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\PrecompiledApp.config NOT FOUND Attributes: Error
This is just for the information, no web.comfig is present in wwwroot and no PrecompiledApp.config is present in C:\...\ReportManager directory. The ReportManager and ReportServer directories is the virtual directory on the IIS, which is automatically made, if you choose, default configuration while installing SQL Server 2005.
The only config files present in the C:\...\ReportManager directory are:
RSWebApplication.config, Web.config, rsmgrpolicy.config
and in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer are:
rsreportserver.config, rssrvpolicy.config, Web.config
What I can trouble shoot with the above information?
Monday, March 19, 2012
95th percentile calculation
Hi *,
For our needs, we have to integrate on a Visual Studio project (a Report Server Project, which gives a report dealing with AVG, MAX counters' values from a SQL DB), a calculation of a '95th percentile' average for a counter (in place of a 'normal' average).
(In another words, we have to automatically substract the 5th 'highest values' of the total of the value, and so only make the average on the remaining 95th of the value).
And this treatment should appear as a function, that we could integrate on the Report.
Does this function already exist ?
Does anyone have any idea to help me to find how to do ?
Tks a lot to everybody.
HI,snakeoli :
Can you elaborate more about your requirment? Do you need something like top 5 percent?
|||Hi,
No, in fact, we have to calculate the average, in the case for several ranges of values.
But to have 'relevant' results, for each sorted ranges of values, we have to exclude 5% of the highest values before calculating the average.
This maneer is called '95th percentile calculation'.
(for ex : in this range of 20 values : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
the 95th percentile give : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 !!
=> exclusion of the 5thhighest values of the list (5% of '20' gives '1' ==> 1 value)
And so, calculation of the average only on 19 values !)
The goal is, in some case, to supress values that can influence the result of a average.
Imagine that in this example, we have '1250' in the place of '20' !
So the average of the 20 values gives a result not relevant !
(1+ 2+ 3+ 4 + ...+ 18 + 19 + 1250) / 20
And so, finaly, that what we need to create, throught Visual Studio :
a function (similar as 'AVG()') that can calculate automaticaly this !
(for a sorted range of value, a average excluding the 5th highest values !)
I whisper sincerely that I was clearly in my explanation.
Thanks a lot for your help.
|||Hi,snakeoli:
I got to know what you want to do in this scenario. However AVG() can not deal with such comlicated calculation for you. I just recommend you to do this in the sql statement. It will be much reasonable.
Monday, February 13, 2012
401: Unauthorized.
Dear all,
I have 2 server, Server_A is IIS, Server_B is Reporting Server(SQL2005).
I create a web project under Server_A and drop a reportviewer on the
default.aspx.
I then enter the Server URL and Report path. And build the project.
I can then browse the report without any problem if URL is typing
localhost/web_project/default.aspx
But problem comes up if change into Server_A/webproject/default.aspx.
The message is
The request failed with HTTP status 401: Unauthorized.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Net.WebException: The request failed with HTTP
status 401: Unauthorized.
Regards,
Ken Linhai
this ragu
u have iis server security problem u verify ur admin
i have the same problem occur i verify my admin
i sent the coading
private void button1_Click_1(object sender, EventArgs e)
{
RunRemote();
}
private void RunRemote()
{
try
{
// this.Cursor = Cursors.WaitCursor;
reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri(Settings.Default.ReportServerEndPoint);
reportViewer1.ServerReport.ReportPath = "/Report Project3/TestReortForFunds";
reportViewer1.RefreshReport();
}
catch (Exception ex)
{
throw ex;
}
finally
{
//this.Cursor = Cursors.Default;
}
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||use this code easily retrive your report
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportPath = "/Report Project3/TestReortForFunds";
ReportViewer1.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerEndPoint"]);
Microsoft.Reporting.WebForms.ReportParameter [1]=new Microsoft.Reporting.WebForms.ReportParameter (0);
}
}
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||No, I can sucess to view the report from the IE in Web Server.
But when I go to toher computers, got the 401 error.
Regards,
Ken Lin
<ragu> wrote in message news:2006122114446ragu1100@.yahoo.com...
> use this code easily retrive your report
>
> protected void Page_Load(object sender, EventArgs e)
> {
>
> if (!Page.IsPostBack)
> {
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.ReportPath = "/Report
> Project3/TestReortForFunds";
> ReportViewer1.ServerReport.ReportServerUrl = new
> Uri(ConfigurationManager.AppSettings["ReportServerEndPoint"]);
> Microsoft.Reporting.WebForms.ReportParameter [1]=new
> Microsoft.Reporting.WebForms.ReportParameter (0);
>
> }
> }
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
I have 2 server, Server_A is IIS, Server_B is Reporting Server(SQL2005).
I create a web project under Server_A and drop a reportviewer on the
default.aspx.
I then enter the Server URL and Report path. And build the project.
I can then browse the report without any problem if URL is typing
localhost/web_project/default.aspx
But problem comes up if change into Server_A/webproject/default.aspx.
The message is
The request failed with HTTP status 401: Unauthorized.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Net.WebException: The request failed with HTTP
status 401: Unauthorized.
Regards,
Ken Linhai
this ragu
u have iis server security problem u verify ur admin
i have the same problem occur i verify my admin
i sent the coading
private void button1_Click_1(object sender, EventArgs e)
{
RunRemote();
}
private void RunRemote()
{
try
{
// this.Cursor = Cursors.WaitCursor;
reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri(Settings.Default.ReportServerEndPoint);
reportViewer1.ServerReport.ReportPath = "/Report Project3/TestReortForFunds";
reportViewer1.RefreshReport();
}
catch (Exception ex)
{
throw ex;
}
finally
{
//this.Cursor = Cursors.Default;
}
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||use this code easily retrive your report
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportPath = "/Report Project3/TestReortForFunds";
ReportViewer1.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerEndPoint"]);
Microsoft.Reporting.WebForms.ReportParameter [1]=new Microsoft.Reporting.WebForms.ReportParameter (0);
}
}
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||No, I can sucess to view the report from the IE in Web Server.
But when I go to toher computers, got the 401 error.
Regards,
Ken Lin
<ragu> wrote in message news:2006122114446ragu1100@.yahoo.com...
> use this code easily retrive your report
>
> protected void Page_Load(object sender, EventArgs e)
> {
>
> if (!Page.IsPostBack)
> {
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.ReportPath = "/Report
> Project3/TestReortForFunds";
> ReportViewer1.ServerReport.ReportServerUrl = new
> Uri(ConfigurationManager.AppSettings["ReportServerEndPoint"]);
> Microsoft.Reporting.WebForms.ReportParameter [1]=new
> Microsoft.Reporting.WebForms.ReportParameter (0);
>
> }
> }
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
Saturday, February 11, 2012
3-tier deployment
I'm looking into deploying Reporting Services on a new project. The reports
in question would be accessed from the Internet. We have a requirement to
separate the HTTP server from the application logic, but all of the RS
deployment examples I have seen show the RS server on the same box as the
HTTP server. Is it possible to separate the two functions and set a firewall
in between, or does RS have to be on the same machine as the HTTP server?The Web part of RS goes on the same Box as IIS, it is a web app... The
database can be somewhere else... The article below is just what you need.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> I'm looking into deploying Reporting Services on a new project. The
reports
> in question would be accessed from the Internet. We have a requirement to
> separate the HTTP server from the application logic, but all of the RS
> deployment examples I have seen show the RS server on the same box as the
> HTTP server. Is it possible to separate the two functions and set a
firewall
> in between, or does RS have to be on the same machine as the HTTP server?|||I had found that but I don't think it's what I'm talking about. I need to
separate the IIS that runs the publicly accessible website from the machine
that's running RS and put a firewall between them. If the RS machine needs
IIS (and I think it does) that's fine, but the public webserver needs to be
separated from RS if possible.
"Wayne Snyder" wrote:
> The Web part of RS goes on the same Box as IIS, it is a web app... The
> database can be somewhere else... The article below is just what you need.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
> news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> > I'm looking into deploying Reporting Services on a new project. The
> reports
> > in question would be accessed from the Internet. We have a requirement to
> > separate the HTTP server from the application logic, but all of the RS
> > deployment examples I have seen show the RS server on the same box as the
> > HTTP server. Is it possible to separate the two functions and set a
> firewall
> > in between, or does RS have to be on the same machine as the HTTP server?
>
>|||To do this you have to have your own asp.net app that controls what report
runs, its parameters, etc. I.e. you can not use the portal that ships with
RS (Report Manager) and have to create your own. Note that you can have RS
outside your firewall but have the data inside your firewall (since really
what the goal is protecting your data). Assuming you create your own front
end to the reports then you have a choice between URL integration and web
services. With RS 2005 you will have the option of a webform and winform
controls for integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
news:428106E8-EE5E-41F2-AC8E-70DAC03FBD3B@.microsoft.com...
> I had found that but I don't think it's what I'm talking about. I need to
> separate the IIS that runs the publicly accessible website from the
machine
> that's running RS and put a firewall between them. If the RS machine
needs
> IIS (and I think it does) that's fine, but the public webserver needs to
be
> separated from RS if possible.
> "Wayne Snyder" wrote:
> > The Web part of RS goes on the same Box as IIS, it is a web app... The
> > database can be somewhere else... The article below is just what you
need.
> >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
> > news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> > > I'm looking into deploying Reporting Services on a new project. The
> > reports
> > > in question would be accessed from the Internet. We have a
requirement to
> > > separate the HTTP server from the application logic, but all of the RS
> > > deployment examples I have seen show the RS server on the same box as
the
> > > HTTP server. Is it possible to separate the two functions and set a
> > firewall
> > > in between, or does RS have to be on the same machine as the HTTP
server?
> >
> >
> >|||I just want to follow up because this is exactly the question I had (thanks
Web Mayfield whoever you are).
We deploy our web-based application on three physical servers. The web
server has no access to data and very little business logic. Incoming web
requests are forwarded to our business logic server via .NET remoting. The
business logic server has access to the DB server. For security reasons
neither the business logic server nor the DB server have IIS installed.
It looks like RS requires me to either (a) put a database on my web server
box, or (b) put IIS on either my business logic box or my database box. I
don't like either one of these alternatives. Am I correctly understanding
that there are no other possibilities?
Thanks
Jeff Berkowitz
Serveron
Hillsboro (Portland), OR
"Bruce L-C [MVP]" wrote:
> To do this you have to have your own asp.net app that controls what report
> runs, its parameters, etc. I.e. you can not use the portal that ships with
> RS (Report Manager) and have to create your own. Note that you can have RS
> outside your firewall but have the data inside your firewall (since really
> what the goal is protecting your data). Assuming you create your own front
> end to the reports then you have a choice between URL integration and web
> services. With RS 2005 you will have the option of a webform and winform
> controls for integration.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||No, you are incorrect. Note that licensing is different than deployment
options. Licensing requires a SQL Server license every place that RS is
installed. RS requires SQL Server for an object store. It does not require
that object store be on the same physical server as RS. As a matter of fact,
this is how web farms are implemented, they all share the same SQL Server
database. RS requires IIS, it is an asp.net application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
>I just want to follow up because this is exactly the question I had (thanks
> Web Mayfield whoever you are).
> We deploy our web-based application on three physical servers. The web
> server has no access to data and very little business logic. Incoming web
> requests are forwarded to our business logic server via .NET remoting.
> The
> business logic server has access to the DB server. For security reasons
> neither the business logic server nor the DB server have IIS installed.
> It looks like RS requires me to either (a) put a database on my web server
> box, or (b) put IIS on either my business logic box or my database box. I
> don't like either one of these alternatives. Am I correctly understanding
> that there are no other possibilities?
> Thanks
> Jeff Berkowitz
> Serveron
> Hillsboro (Portland), OR
>
> "Bruce L-C [MVP]" wrote:
>> To do this you have to have your own asp.net app that controls what
>> report
>> runs, its parameters, etc. I.e. you can not use the portal that ships
>> with
>> RS (Report Manager) and have to create your own. Note that you can have
>> RS
>> outside your firewall but have the data inside your firewall (since
>> really
>> what the goal is protecting your data). Assuming you create your own
>> front
>> end to the reports then you have a choice between URL integration and web
>> services. With RS 2005 you will have the option of a webform and winform
>> controls for integration.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||I need to do the same thing as the people in this post. I do not quite
understand the thread of "solutions" posted here. What exactly is the
solution?
Create an asp.net or vb.net app on the middle tier box? What does it do?
Call this app from the iis server outside of the firewall? How do you do
this? Virtual folder? How does it get rendered to the client?
What does the .net app do in on the middle tier box that calls the report?
I am not using the portal. I have an asp.net app that runs on front end iis
box (outside of the firewall), but it connects to the database. I need it to
connect to the middle tier box. We cannot have the iis server accessing the
database server directly (2 tier).
"Bruce L-C [MVP]" wrote:
> No, you are incorrect. Note that licensing is different than deployment
> options. Licensing requires a SQL Server license every place that RS is
> installed. RS requires SQL Server for an object store. It does not require
> that object store be on the same physical server as RS. As a matter of fact,
> this is how web farms are implemented, they all share the same SQL Server
> database. RS requires IIS, it is an asp.net application.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
> >I just want to follow up because this is exactly the question I had (thanks
> > Web Mayfield whoever you are).
> >
> > We deploy our web-based application on three physical servers. The web
> > server has no access to data and very little business logic. Incoming web
> > requests are forwarded to our business logic server via .NET remoting.
> > The
> > business logic server has access to the DB server. For security reasons
> > neither the business logic server nor the DB server have IIS installed.
> >
> > It looks like RS requires me to either (a) put a database on my web server
> > box, or (b) put IIS on either my business logic box or my database box. I
> > don't like either one of these alternatives. Am I correctly understanding
> > that there are no other possibilities?
> >
> > Thanks
> > Jeff Berkowitz
> > Serveron
> > Hillsboro (Portland), OR
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> To do this you have to have your own asp.net app that controls what
> >> report
> >> runs, its parameters, etc. I.e. you can not use the portal that ships
> >> with
> >> RS (Report Manager) and have to create your own. Note that you can have
> >> RS
> >> outside your firewall but have the data inside your firewall (since
> >> really
> >> what the goal is protecting your data). Assuming you create your own
> >> front
> >> end to the reports then you have a choice between URL integration and web
> >> services. With RS 2005 you will have the option of a webform and winform
> >> controls for integration.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >
>
>|||I must be dense but I'm not getting it.
You do understand that our web head has no database access at all, correct?
It only has access to our "proprietary" business facade via .NET remoting.
We do not want to implement any kind of direct DB access from the web head to
our existing SQL Server installation for any purpose whatsoever. Our
remoting facade provides auditing and other services that we would lose if we
did this.
Assuming you understood this from my original post, you must be suggesting
that RS should be installed in our business layer. We do not have IIS
installed at this layer and do not want to.
So my misunderstanding must be that I don't understand how to get API-style
access to RS in response to incoming requests without installing IIS on the
business layer.
I appreciate your help and If I'm just being dense you can be blunt about
it. ;-)
Jeff
"Bruce L-C [MVP]" wrote:
> No, you are incorrect. Note that licensing is different than deployment
> options. Licensing requires a SQL Server license every place that RS is
> installed. RS requires SQL Server for an object store. It does not require
> that object store be on the same physical server as RS. As a matter of fact,
> this is how web farms are implemented, they all share the same SQL Server
> database. RS requires IIS, it is an asp.net application.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
> >I just want to follow up because this is exactly the question I had (thanks
> > Web Mayfield whoever you are).
> >
> > We deploy our web-based application on three physical servers. The web
> > server has no access to data and very little business logic. Incoming web
> > requests are forwarded to our business logic server via .NET remoting.
> > The
> > business logic server has access to the DB server. For security reasons
> > neither the business logic server nor the DB server have IIS installed.
> >
> > It looks like RS requires me to either (a) put a database on my web server
> > box, or (b) put IIS on either my business logic box or my database box. I
> > don't like either one of these alternatives. Am I correctly understanding
> > that there are no other possibilities?
> >
> > Thanks
> > Jeff Berkowitz
> > Serveron
> > Hillsboro (Portland), OR|||Have you looked at the link I provided previously. It has very good diagrams
on how it all works. The architecture requires IIS somewhere. RS does not
exist without it. RS is an asp.net application. Also it requires SQL Server
as an object store somewhere.
So, let's say you have your server facing the internet. It calls your
business objects on another server. If you want to use RS then you have two
choices, either you install IIS on your server with your business objects OR
you have a third server with RS. Your business objects can use web services
to integrate with RS. If you don't want IIS running somewhere AND where it
is running it has to have access to SQL Server at a minimum for an object
store. The SQL Server used can be on another server. It is also possible to
write your own data extension that RS would use your business objects.
My feeling is that with the architecture you have and your design goals you
should not use RS at this time. What you should consider are the new
controls that have just come out with Widbey (the lastest beta for the next
version of Visual Studio). The web control allows you to hand a dataset to
the control and the report to the control. In local mode it does not require
any server at all.
My suggestion is to first closely read this link:
http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
My feeling is that it will be a whole lot of work for you to do what you
want to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Berkowitz" <JeffBerkowitz@.discussions.microsoft.com> wrote in message
news:39953487-5243-4857-B42D-86C255C5ABCC@.microsoft.com...
>I must be dense but I'm not getting it.
> You do understand that our web head has no database access at all,
> correct?
> It only has access to our "proprietary" business facade via .NET remoting.
> We do not want to implement any kind of direct DB access from the web head
> to
> our existing SQL Server installation for any purpose whatsoever. Our
> remoting facade provides auditing and other services that we would lose if
> we
> did this.
> Assuming you understood this from my original post, you must be suggesting
> that RS should be installed in our business layer. We do not have IIS
> installed at this layer and do not want to.
> So my misunderstanding must be that I don't understand how to get
> API-style
> access to RS in response to incoming requests without installing IIS on
> the
> business layer.
> I appreciate your help and If I'm just being dense you can be blunt about
> it. ;-)
> Jeff
> "Bruce L-C [MVP]" wrote:
>> No, you are incorrect. Note that licensing is different than deployment
>> options. Licensing requires a SQL Server license every place that RS is
>> installed. RS requires SQL Server for an object store. It does not
>> require
>> that object store be on the same physical server as RS. As a matter of
>> fact,
>> this is how web farms are implemented, they all share the same SQL Server
>> database. RS requires IIS, it is an asp.net application.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in
>> message
>> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
>> >I just want to follow up because this is exactly the question I had
>> >(thanks
>> > Web Mayfield whoever you are).
>> >
>> > We deploy our web-based application on three physical servers. The web
>> > server has no access to data and very little business logic. Incoming
>> > web
>> > requests are forwarded to our business logic server via .NET remoting.
>> > The
>> > business logic server has access to the DB server. For security
>> > reasons
>> > neither the business logic server nor the DB server have IIS installed.
>> >
>> > It looks like RS requires me to either (a) put a database on my web
>> > server
>> > box, or (b) put IIS on either my business logic box or my database box.
>> > I
>> > don't like either one of these alternatives. Am I correctly
>> > understanding
>> > that there are no other possibilities?
>> >
>> > Thanks
>> > Jeff Berkowitz
>> > Serveron
>> > Hillsboro (Portland), OR
>|||Jeff,
I think your question is very reasonable. The difficulty here is that the
SQL Server 2000 version of Reporting Services doesn't work the way you want
it to.
You want a public web server running IIS, but for security reasons, little
logic and no database access. Makes perfect sense. You want a middle tier
server running your business logic. This server will be allowed database
access, but for security reasons it will not run IIS. Once again, that's
exactly how I would recommend securing public web sites.
Here's the problem. Reporting services is implemented as web services. It
is not a straight DLL and can not be accessed via .NET Remoting. You have to
have IIS to run these (or any other) web services.
If you don't want logic on your public web server, and you don't want IIS on
your business logic server, you have two somewhat unattractive choices.
First, you could put IIS on your database server. The big downside is that
this exposes IIS bugs on your SQL Server, but at least it's 2 steps removed
from your public web server. Second choice is to install the RS web services
on a fourth server. The downside is that it requires a fourth server and
does still put an IIS presence on your network. But, at least it's as secure
as you're going to get.
Here's what that 4-tier solution would look like. Your public web server is
limited to hitting your business server. The business server runs a custom
DLL you write, and can be accessed via .NET remoting. The Reporting Services
server has IIS and is accessed by your DLL via SOAP. Finally, the Reporting
Services server accesses SQL Server for it's store. This is ugly, but it's
fairly secure.
I recommend you look at using the SQL Server 2005 version of Reporting
Services, which is due out at the end of the year. In that version, you will
be able to render reports without running a web service, so you can simply
have the same vanilla 3-tier architecture that you have for everything else.
If you have to implement now and don't want to use Beta 2, you will have to
sacrifice some security or use a fourth server.
Hope this clears things up.
Ted
"Jeff Berkowitz" wrote:
> I must be dense but I'm not getting it.
> You do understand that our web head has no database access at all, correct?
> It only has access to our "proprietary" business facade via .NET remoting.
> We do not want to implement any kind of direct DB access from the web head to
> our existing SQL Server installation for any purpose whatsoever. Our
> remoting facade provides auditing and other services that we would lose if we
> did this.
> Assuming you understood this from my original post, you must be suggesting
> that RS should be installed in our business layer. We do not have IIS
> installed at this layer and do not want to.
> So my misunderstanding must be that I don't understand how to get API-style
> access to RS in response to incoming requests without installing IIS on the
> business layer.
> I appreciate your help and If I'm just being dense you can be blunt about
> it. ;-)
> Jeff|||Thanks (very much) both Ted and Bruce. We may choose to put IIS on one of
our backend boxes and go with the current version of RS. At least we are not
religiously opposed to the idea; I just wanted to make sure I understood the
situation clearly. You've both been extremely helpful.
Jeff Berkowitz
"Ted K" wrote:
> Jeff,
> I think your question is very reasonable. ...
in question would be accessed from the Internet. We have a requirement to
separate the HTTP server from the application logic, but all of the RS
deployment examples I have seen show the RS server on the same box as the
HTTP server. Is it possible to separate the two functions and set a firewall
in between, or does RS have to be on the same machine as the HTTP server?The Web part of RS goes on the same Box as IIS, it is a web app... The
database can be somewhere else... The article below is just what you need.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> I'm looking into deploying Reporting Services on a new project. The
reports
> in question would be accessed from the Internet. We have a requirement to
> separate the HTTP server from the application logic, but all of the RS
> deployment examples I have seen show the RS server on the same box as the
> HTTP server. Is it possible to separate the two functions and set a
firewall
> in between, or does RS have to be on the same machine as the HTTP server?|||I had found that but I don't think it's what I'm talking about. I need to
separate the IIS that runs the publicly accessible website from the machine
that's running RS and put a firewall between them. If the RS machine needs
IIS (and I think it does) that's fine, but the public webserver needs to be
separated from RS if possible.
"Wayne Snyder" wrote:
> The Web part of RS goes on the same Box as IIS, it is a web app... The
> database can be somewhere else... The article below is just what you need.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
> news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> > I'm looking into deploying Reporting Services on a new project. The
> reports
> > in question would be accessed from the Internet. We have a requirement to
> > separate the HTTP server from the application logic, but all of the RS
> > deployment examples I have seen show the RS server on the same box as the
> > HTTP server. Is it possible to separate the two functions and set a
> firewall
> > in between, or does RS have to be on the same machine as the HTTP server?
>
>|||To do this you have to have your own asp.net app that controls what report
runs, its parameters, etc. I.e. you can not use the portal that ships with
RS (Report Manager) and have to create your own. Note that you can have RS
outside your firewall but have the data inside your firewall (since really
what the goal is protecting your data). Assuming you create your own front
end to the reports then you have a choice between URL integration and web
services. With RS 2005 you will have the option of a webform and winform
controls for integration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
news:428106E8-EE5E-41F2-AC8E-70DAC03FBD3B@.microsoft.com...
> I had found that but I don't think it's what I'm talking about. I need to
> separate the IIS that runs the publicly accessible website from the
machine
> that's running RS and put a firewall between them. If the RS machine
needs
> IIS (and I think it does) that's fine, but the public webserver needs to
be
> separated from RS if possible.
> "Wayne Snyder" wrote:
> > The Web part of RS goes on the same Box as IIS, it is a web app... The
> > database can be somewhere else... The article below is just what you
need.
> >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx#EEAA
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "Web Mayfield" <WebMayfield@.discussions.microsoft.com> wrote in message
> > news:22618170-C83F-489B-8AE5-52ADE0C124A4@.microsoft.com...
> > > I'm looking into deploying Reporting Services on a new project. The
> > reports
> > > in question would be accessed from the Internet. We have a
requirement to
> > > separate the HTTP server from the application logic, but all of the RS
> > > deployment examples I have seen show the RS server on the same box as
the
> > > HTTP server. Is it possible to separate the two functions and set a
> > firewall
> > > in between, or does RS have to be on the same machine as the HTTP
server?
> >
> >
> >|||I just want to follow up because this is exactly the question I had (thanks
Web Mayfield whoever you are).
We deploy our web-based application on three physical servers. The web
server has no access to data and very little business logic. Incoming web
requests are forwarded to our business logic server via .NET remoting. The
business logic server has access to the DB server. For security reasons
neither the business logic server nor the DB server have IIS installed.
It looks like RS requires me to either (a) put a database on my web server
box, or (b) put IIS on either my business logic box or my database box. I
don't like either one of these alternatives. Am I correctly understanding
that there are no other possibilities?
Thanks
Jeff Berkowitz
Serveron
Hillsboro (Portland), OR
"Bruce L-C [MVP]" wrote:
> To do this you have to have your own asp.net app that controls what report
> runs, its parameters, etc. I.e. you can not use the portal that ships with
> RS (Report Manager) and have to create your own. Note that you can have RS
> outside your firewall but have the data inside your firewall (since really
> what the goal is protecting your data). Assuming you create your own front
> end to the reports then you have a choice between URL integration and web
> services. With RS 2005 you will have the option of a webform and winform
> controls for integration.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services|||No, you are incorrect. Note that licensing is different than deployment
options. Licensing requires a SQL Server license every place that RS is
installed. RS requires SQL Server for an object store. It does not require
that object store be on the same physical server as RS. As a matter of fact,
this is how web farms are implemented, they all share the same SQL Server
database. RS requires IIS, it is an asp.net application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
>I just want to follow up because this is exactly the question I had (thanks
> Web Mayfield whoever you are).
> We deploy our web-based application on three physical servers. The web
> server has no access to data and very little business logic. Incoming web
> requests are forwarded to our business logic server via .NET remoting.
> The
> business logic server has access to the DB server. For security reasons
> neither the business logic server nor the DB server have IIS installed.
> It looks like RS requires me to either (a) put a database on my web server
> box, or (b) put IIS on either my business logic box or my database box. I
> don't like either one of these alternatives. Am I correctly understanding
> that there are no other possibilities?
> Thanks
> Jeff Berkowitz
> Serveron
> Hillsboro (Portland), OR
>
> "Bruce L-C [MVP]" wrote:
>> To do this you have to have your own asp.net app that controls what
>> report
>> runs, its parameters, etc. I.e. you can not use the portal that ships
>> with
>> RS (Report Manager) and have to create your own. Note that you can have
>> RS
>> outside your firewall but have the data inside your firewall (since
>> really
>> what the goal is protecting your data). Assuming you create your own
>> front
>> end to the reports then you have a choice between URL integration and web
>> services. With RS 2005 you will have the option of a webform and winform
>> controls for integration.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||I need to do the same thing as the people in this post. I do not quite
understand the thread of "solutions" posted here. What exactly is the
solution?
Create an asp.net or vb.net app on the middle tier box? What does it do?
Call this app from the iis server outside of the firewall? How do you do
this? Virtual folder? How does it get rendered to the client?
What does the .net app do in on the middle tier box that calls the report?
I am not using the portal. I have an asp.net app that runs on front end iis
box (outside of the firewall), but it connects to the database. I need it to
connect to the middle tier box. We cannot have the iis server accessing the
database server directly (2 tier).
"Bruce L-C [MVP]" wrote:
> No, you are incorrect. Note that licensing is different than deployment
> options. Licensing requires a SQL Server license every place that RS is
> installed. RS requires SQL Server for an object store. It does not require
> that object store be on the same physical server as RS. As a matter of fact,
> this is how web farms are implemented, they all share the same SQL Server
> database. RS requires IIS, it is an asp.net application.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
> >I just want to follow up because this is exactly the question I had (thanks
> > Web Mayfield whoever you are).
> >
> > We deploy our web-based application on three physical servers. The web
> > server has no access to data and very little business logic. Incoming web
> > requests are forwarded to our business logic server via .NET remoting.
> > The
> > business logic server has access to the DB server. For security reasons
> > neither the business logic server nor the DB server have IIS installed.
> >
> > It looks like RS requires me to either (a) put a database on my web server
> > box, or (b) put IIS on either my business logic box or my database box. I
> > don't like either one of these alternatives. Am I correctly understanding
> > that there are no other possibilities?
> >
> > Thanks
> > Jeff Berkowitz
> > Serveron
> > Hillsboro (Portland), OR
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> To do this you have to have your own asp.net app that controls what
> >> report
> >> runs, its parameters, etc. I.e. you can not use the portal that ships
> >> with
> >> RS (Report Manager) and have to create your own. Note that you can have
> >> RS
> >> outside your firewall but have the data inside your firewall (since
> >> really
> >> what the goal is protecting your data). Assuming you create your own
> >> front
> >> end to the reports then you have a choice between URL integration and web
> >> services. With RS 2005 you will have the option of a webform and winform
> >> controls for integration.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >
>
>|||I must be dense but I'm not getting it.
You do understand that our web head has no database access at all, correct?
It only has access to our "proprietary" business facade via .NET remoting.
We do not want to implement any kind of direct DB access from the web head to
our existing SQL Server installation for any purpose whatsoever. Our
remoting facade provides auditing and other services that we would lose if we
did this.
Assuming you understood this from my original post, you must be suggesting
that RS should be installed in our business layer. We do not have IIS
installed at this layer and do not want to.
So my misunderstanding must be that I don't understand how to get API-style
access to RS in response to incoming requests without installing IIS on the
business layer.
I appreciate your help and If I'm just being dense you can be blunt about
it. ;-)
Jeff
"Bruce L-C [MVP]" wrote:
> No, you are incorrect. Note that licensing is different than deployment
> options. Licensing requires a SQL Server license every place that RS is
> installed. RS requires SQL Server for an object store. It does not require
> that object store be on the same physical server as RS. As a matter of fact,
> this is how web farms are implemented, they all share the same SQL Server
> database. RS requires IIS, it is an asp.net application.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in message
> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
> >I just want to follow up because this is exactly the question I had (thanks
> > Web Mayfield whoever you are).
> >
> > We deploy our web-based application on three physical servers. The web
> > server has no access to data and very little business logic. Incoming web
> > requests are forwarded to our business logic server via .NET remoting.
> > The
> > business logic server has access to the DB server. For security reasons
> > neither the business logic server nor the DB server have IIS installed.
> >
> > It looks like RS requires me to either (a) put a database on my web server
> > box, or (b) put IIS on either my business logic box or my database box. I
> > don't like either one of these alternatives. Am I correctly understanding
> > that there are no other possibilities?
> >
> > Thanks
> > Jeff Berkowitz
> > Serveron
> > Hillsboro (Portland), OR|||Have you looked at the link I provided previously. It has very good diagrams
on how it all works. The architecture requires IIS somewhere. RS does not
exist without it. RS is an asp.net application. Also it requires SQL Server
as an object store somewhere.
So, let's say you have your server facing the internet. It calls your
business objects on another server. If you want to use RS then you have two
choices, either you install IIS on your server with your business objects OR
you have a third server with RS. Your business objects can use web services
to integrate with RS. If you don't want IIS running somewhere AND where it
is running it has to have access to SQL Server at a minimum for an object
store. The SQL Server used can be on another server. It is also possible to
write your own data extension that RS would use your business objects.
My feeling is that with the architecture you have and your design goals you
should not use RS at this time. What you should consider are the new
controls that have just come out with Widbey (the lastest beta for the next
version of Visual Studio). The web control allows you to hand a dataset to
the control and the report to the control. In local mode it does not require
any server at all.
My suggestion is to first closely read this link:
http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
My feeling is that it will be a whole lot of work for you to do what you
want to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Berkowitz" <JeffBerkowitz@.discussions.microsoft.com> wrote in message
news:39953487-5243-4857-B42D-86C255C5ABCC@.microsoft.com...
>I must be dense but I'm not getting it.
> You do understand that our web head has no database access at all,
> correct?
> It only has access to our "proprietary" business facade via .NET remoting.
> We do not want to implement any kind of direct DB access from the web head
> to
> our existing SQL Server installation for any purpose whatsoever. Our
> remoting facade provides auditing and other services that we would lose if
> we
> did this.
> Assuming you understood this from my original post, you must be suggesting
> that RS should be installed in our business layer. We do not have IIS
> installed at this layer and do not want to.
> So my misunderstanding must be that I don't understand how to get
> API-style
> access to RS in response to incoming requests without installing IIS on
> the
> business layer.
> I appreciate your help and If I'm just being dense you can be blunt about
> it. ;-)
> Jeff
> "Bruce L-C [MVP]" wrote:
>> No, you are incorrect. Note that licensing is different than deployment
>> options. Licensing requires a SQL Server license every place that RS is
>> installed. RS requires SQL Server for an object store. It does not
>> require
>> that object store be on the same physical server as RS. As a matter of
>> fact,
>> this is how web farms are implemented, they all share the same SQL Server
>> database. RS requires IIS, it is an asp.net application.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jeff Berkowitz" <Jeff Berkowitz@.discussions.microsoft.com> wrote in
>> message
>> news:10CBA5E3-4FC8-406D-B0D7-6CBBF327FF06@.microsoft.com...
>> >I just want to follow up because this is exactly the question I had
>> >(thanks
>> > Web Mayfield whoever you are).
>> >
>> > We deploy our web-based application on three physical servers. The web
>> > server has no access to data and very little business logic. Incoming
>> > web
>> > requests are forwarded to our business logic server via .NET remoting.
>> > The
>> > business logic server has access to the DB server. For security
>> > reasons
>> > neither the business logic server nor the DB server have IIS installed.
>> >
>> > It looks like RS requires me to either (a) put a database on my web
>> > server
>> > box, or (b) put IIS on either my business logic box or my database box.
>> > I
>> > don't like either one of these alternatives. Am I correctly
>> > understanding
>> > that there are no other possibilities?
>> >
>> > Thanks
>> > Jeff Berkowitz
>> > Serveron
>> > Hillsboro (Portland), OR
>|||Jeff,
I think your question is very reasonable. The difficulty here is that the
SQL Server 2000 version of Reporting Services doesn't work the way you want
it to.
You want a public web server running IIS, but for security reasons, little
logic and no database access. Makes perfect sense. You want a middle tier
server running your business logic. This server will be allowed database
access, but for security reasons it will not run IIS. Once again, that's
exactly how I would recommend securing public web sites.
Here's the problem. Reporting services is implemented as web services. It
is not a straight DLL and can not be accessed via .NET Remoting. You have to
have IIS to run these (or any other) web services.
If you don't want logic on your public web server, and you don't want IIS on
your business logic server, you have two somewhat unattractive choices.
First, you could put IIS on your database server. The big downside is that
this exposes IIS bugs on your SQL Server, but at least it's 2 steps removed
from your public web server. Second choice is to install the RS web services
on a fourth server. The downside is that it requires a fourth server and
does still put an IIS presence on your network. But, at least it's as secure
as you're going to get.
Here's what that 4-tier solution would look like. Your public web server is
limited to hitting your business server. The business server runs a custom
DLL you write, and can be accessed via .NET remoting. The Reporting Services
server has IIS and is accessed by your DLL via SOAP. Finally, the Reporting
Services server accesses SQL Server for it's store. This is ugly, but it's
fairly secure.
I recommend you look at using the SQL Server 2005 version of Reporting
Services, which is due out at the end of the year. In that version, you will
be able to render reports without running a web service, so you can simply
have the same vanilla 3-tier architecture that you have for everything else.
If you have to implement now and don't want to use Beta 2, you will have to
sacrifice some security or use a fourth server.
Hope this clears things up.
Ted
"Jeff Berkowitz" wrote:
> I must be dense but I'm not getting it.
> You do understand that our web head has no database access at all, correct?
> It only has access to our "proprietary" business facade via .NET remoting.
> We do not want to implement any kind of direct DB access from the web head to
> our existing SQL Server installation for any purpose whatsoever. Our
> remoting facade provides auditing and other services that we would lose if we
> did this.
> Assuming you understood this from my original post, you must be suggesting
> that RS should be installed in our business layer. We do not have IIS
> installed at this layer and do not want to.
> So my misunderstanding must be that I don't understand how to get API-style
> access to RS in response to incoming requests without installing IIS on the
> business layer.
> I appreciate your help and If I'm just being dense you can be blunt about
> it. ;-)
> Jeff|||Thanks (very much) both Ted and Bruce. We may choose to put IIS on one of
our backend boxes and go with the current version of RS. At least we are not
religiously opposed to the idea; I just wanted to make sure I understood the
situation clearly. You've both been extremely helpful.
Jeff Berkowitz
"Ted K" wrote:
> Jeff,
> I think your question is very reasonable. ...
Subscribe to:
Posts (Atom)