Packaging reports as a product?

I've written an application that generates a couple of stored procs and
views, and installs them into a DB. I also have a series of reports in
Reporting Services that pull data from those views.
Its easy enough for me to distribute the .exe for the app that generates SPs
and views, but how do I distribute the reports without giving the installer
access to them? Basically, I dont want anyone to poke around through the
formulas, etc.
Is there a way to do this?Henry,
Even if you secure the installation process the administrator can always see
the report definition in the Report Manager. So, I would rather gravitate
toward moving the sensitive information in an external .NET assembly and
possibly obfuscating it.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> I've written an application that generates a couple of stored procs and
> views, and installs them into a DB. I also have a series of reports in
> Reporting Services that pull data from those views.
> Its easy enough for me to distribute the .exe for the app that generates
SPs
> and views, but how do I distribute the reports without giving the
installer
> access to them? Basically, I dont want anyone to poke around through the
> formulas, etc.
> Is there a way to do this?|||Youre suggesting an assembly that spits out a dataset, and reporting on that?
Are there any examples of this around?
"Teo Lachev [MVP]" wrote:
> Henry,
> Even if you secure the installation process the administrator can always see
> the report definition in the Report Manager. So, I would rather gravitate
> toward moving the sensitive information in an external .NET assembly and
> possibly obfuscating it.
> --
> Hope this helps.
> ----
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
> news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > I've written an application that generates a couple of stored procs and
> > views, and installs them into a DB. I also have a series of reports in
> > Reporting Services that pull data from those views.
> >
> > Its easy enough for me to distribute the .exe for the app that generates
> SPs
> > and views, but how do I distribute the reports without giving the
> installer
> > access to them? Basically, I dont want anyone to poke around through the
> > formulas, etc.
> >
> > Is there a way to do this?
>
>|||Well, depends on what you want to protect. You mentioned that you want to
protect the formulas so I imagined that you have some sensitive information
embedded in your formulas that you don't want to reveal.
What exactly do you want to protect?
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <HenryZektser@.discussions.microsoft.com> wrote in message
news:D95E5500-FB7D-4596-A92B-09E4A6F58981@.microsoft.com...
> Youre suggesting an assembly that spits out a dataset, and reporting on
that?
> Are there any examples of this around?
> "Teo Lachev [MVP]" wrote:
> > Henry,
> >
> > Even if you secure the installation process the administrator can always
see
> > the report definition in the Report Manager. So, I would rather
gravitate
> > toward moving the sensitive information in an external .NET assembly and
> > possibly obfuscating it.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in
message
> > news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > > I've written an application that generates a couple of stored procs
and
> > > views, and installs them into a DB. I also have a series of reports in
> > > Reporting Services that pull data from those views.
> > >
> > > Its easy enough for me to distribute the .exe for the app that
generates
> > SPs
> > > and views, but how do I distribute the reports without giving the
> > installer
> > > access to them? Basically, I dont want anyone to poke around through
the
> > > formulas, etc.
> > >
> > > Is there a way to do this?
> >
> >
> >

packaging MSDE 2000 with PDW

How can i package MSDE 2000 (not MSDE 7) with my application using package and deployment wizard?
Burlar:
I have been on this list for a year and installing MSDE with an application
has been the number one problem since then.
As far as I know, it cannot be done reliably.
Paul
"Burlar" <bolanoble@.yahoo.com> wrote in message
news:0E323F53-0F9E-47AB-84A5-70705257E508@.microsoft.com...
> How can i package MSDE 2000 (not MSDE 7) with my application using package
and deployment wizard?
|||Paul,
Then how have you been able to solve the problem. I don't want to have to install msde on every target machine. I feel it is bad practice.
|||Burlar:
I haven't. I am rolling out my app. in a month and seriously considering
telling
users that they must be on Win 2K or above as this will solve a lot of
problems
apparently ...
Paul
"Burlar" <anonymous@.discussions.microsoft.com> wrote in message
news:2B577F83-7918-4F82-ACCA-442C7F4F3C42@.microsoft.com...
> Paul,
> Then how have you been able to solve the problem. I don't want to have to
install msde on every target machine. I feel it is bad practice.
|||Hi Burlar,
In addtion to Paul's advice, check out my comments to anaximan (in this
folder) regarding the PDW. It should now not be being used.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Burlar" <bolanoble@.yahoo.com> wrote in message
news:0E323F53-0F9E-47AB-84A5-70705257E508@.microsoft.com...
> How can i package MSDE 2000 (not MSDE 7) with my application using package
and deployment wizard?

Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

HI, Mehdi:

I hope this article can help:

Deploying Reports and ReportViewer Controls

http://msdn2.microsoft.com/en-us/library/ms251723(VS.80).aspx

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

hi,

i to got struct. i have same problem as described above. i have a web setup of web project that consist of a form that uses report viewer whose report server url is read from web.config. i have a sql reporting services report project which consist of reports. when comes to installation on the client side i can carry web setup of web project but when come to report server project i don't want to carry sourse code to deploy. can it be possible to make websetup of report server project if yes plz reply how to make the setup else say the alternative methods so that i can install at the client side with integration issuess my email id isabdulhaseeb_1201@.yahoo.com. waiting for ur reply. the link which u have provied could not help me a lot, so dont mind plz explain it in your way

Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

Hi Mehdi,

We use this tool for deploying the reports to report server, its easy to use and solved our problem,

Reporting Services Scripter -http://www.sqldbatips.com/showarticle.asp?ID=62

Overview

Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters.

Not sure whetehr you can use the command script file with the installer or not, but having seprate deployment script for reports is better as you may have different web and database servers (as in our case).

Now regarding hardcoding the ReportServerUrl and report path you are right its not good practice to do that, web.config file should be used for storing these values so that you can update them on web server itself.

Mehdi6002:

it installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

Can you tell how you run the database script through installer? :)

|||

Hello akjoshi,

Thanks for your reply. Would you just add a key to your web config file in the <appSettings> for the reports folder and reportServerUrl or there is a specific place that you have to include these, like in <httpHandlers> or < buildProviders> or ...

at the moment I just have 2 keys in <appSettings>

<add key="ReportServerUrl" value="http://..." />
<add key="ReportFolder" value="/ABC" />

is that a right way of doing it...?

To run SQL script while installing your DB, have a look at this article:

http://msdn2.microsoft.com/en-us/library/49b92ztk(VS.80).aspx

Regards,
Mehdi

|||

Yes Mehdi you are right, Using the <appSettings> section to add these keys is the right approch.

<appSettings>

<addkey="ReportServerUrl"value="http://ServerName/ReportServer"/>

<addkey="ReportPath"value="/ReportPath/"/>

</appSettings>

and acces it like this -

ReportServerUrl =newUri(ConfigurationManager.AppSettings["ReportServerUrl"]);

thanks for the link.

|||

Deploying a SQL Server Reporting Services 2005 report via an MSI -

http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

Package Execution with Custom UI

Hi:

I need a user to pass an input to a package from a VB form, and then want to show progress/errors in the form.

I have been able to use Application. LoadFromSqlServer and I then set pkg.Variables.(MyVariable) to the required value before calling Execute.

Works fine so far.

I dont know how to show progress. The DTSExecResult just returns a Cryptic Success /Failure status message.

Is there a way to log the errors if any to my Windows form? I have seen samples on Console Apps(see below) but I would prefer to show it in my Windows form in a text Box or something, appending each error result to the text.

TIA

Kartik

Code Snippet

Class EventListener
Inherits DefaultEvents

Public Overrides Function OnError(ByVal source As Microsoft.SqlServer.Dts.Runtime.DtsObject, _
ByVal errorCode As Integer, ByVal subComponent As String, ByVal description As String, _
ByVal helpFile As String, ByVal helpContext As Integer, _
ByVal idofInterfaceWithError As String) As Boolean

' Add application�Cspecific diagnostics here.
Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description)
Return False

End Function

End Class

That's the code you want - you need to capture the events, and write to a text box instead of the console.

Package Configuration in SSIS

Hi,

I am new to SSIS. I have an SSIS package which we are storing it in the application server ( not in SQL Server ). We are storing the database name etc.. in package configuration. Where should I store the configuration file? In app server or in the database server. Also please tell how to execute the package stored as file system.

Thanks

ramesh

You can store the config file in any file system location you want; but you have to tell the pckage where to find it. When you create a package configuration using a XML file; you have to provide the path to the file (direct method) or the environment variable name that hold that path (indirect method)|||

Can you please elaborate on what you mean by "application server" in this context?

If you're storing the DTSX package files on the file system, it probably makes sense to do the same with your config files. Store them as dtsConfig XML configuration files, ideally as "indirect" XML file configurations, where the fully qualified path to the files are stored in Windows environment variables. This should give you all of the flexibility you need.

To execute the packages from the file system, use the DTEXEC command line utility. The easiest way to do this is to use the DTEXECUI utility to build the command-line parameters for DTEXEC. It's much easier than doing everything yourself by hand.

|||

Thanks for the reply.

I have creatred ait using XML file. But how to tell it to the package.

|||

Ramesh Chandran wrote:

Thanks for the reply.

I have creatred ait using XML file. But how to tell it to the package.

The best way to answer this is to point you to the SSIS tutorials in SQL Server Books Online:

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

These are great starting points to get your hands dirty with many different aspects of SSIS, including adding configurations to your packages.

Another, somewhat shorter answer, is to right-click on the package's control flow design surface and select "package Configurations" from the pop-up menu. This dialog is how you "tell the package" about the configurations.

|||

Two servers we have, one for database and in the other one all exe's,... resides. I am calling it as "MYSERVER"

I am storing the ssis package in MYSERVER. I thought of using dtexec \File option to run the dtsx package.

I will store the package configuration file in the "MYSERVER".

This is the config file generated.

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="User" GeneratedFromPackageName="Package" GeneratedFromPackageID="{0F0A1F60-3552-4BFA-891C-D60F72C28AA1}" GeneratedDate="5/29/2007 12:52:52 PM"/></DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[testserver\I01.PUBLISHER_Database].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PUBLISHER_Database</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[testserver,2048\I01.PUBLISHER_Database].Properties[ServerName]" ValueType="String"><ConfiguredValue>testserver,2048\I01</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[pmastrep.PSC].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pmastrepTest;User ID=PSC;Provider=OraOLEDB.Oracle.1;</ConfiguredValue></Configuration>
</DTSConfiguration>

Now how I need to run the package?

|||

Ramesh Chandran wrote:

Now how I need to run the package?

To execute the packages from the file system, use the DTEXEC command line utility. The easiest way to do this is to use the DTEXECUI utility to build the command-line parameters for DTEXEC. It's much easier than doing everything yourself by hand.

The syntax will look something like this:

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

|||

If I am running using this command

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Where I need to specify about the package configuration file. Will it takes automatically?

or explicitly i need to specify the path.

|||

Ramesh Chandran wrote:

If I am running using this command

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Where I need to specify about the package configuration file. Will it takes automatically?

or explicitly i need to specify the path.

Please refer to my earlier reply from 12:53 PM UTC, above.

|||

Ramesh Chandran wrote:

If I am running using this command

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Where I need to specify about the package configuration file. Will it takes automatically?

or explicitly i need to specify the path.

That is when you are editing the package in BIDS. When you create a package configuration in the package; the PAckage configuration Organizer will prompt you for either a file location (direct) or an Env variable name (indirect)

|||You can also specify the configuration file to use at runtime using the /CONFIG switch for DTEXEC. This is useful when your production configuration path is different than the one you use during development.

P2P replication

Hi,
I understand that P2P replication does not do automatic conflict detection.
I do not have conflict scenarios in my application except for the following
single requirement:
For a test plan i will add cases and will give numbering like case 1, 2, 3,
4 etc.
Any new case added to a plan will have the number as max(number) for the
plan + 1.
There are 4 servers with P2P replication setup. Consider the below scenario
1) Assume that max number for the plan at second t1 was 4.
Server1 adds new case. Data gets committed in DB server1 as 5. User gets
success message.
In the same instant server2 adds new case, data gets committed in DB server2
as 5. User gets success message.
Due to replication lag, it will take couple of section for data in db
server1 to reach dbserver2.
When data synchronization happens there are two 5's and this will cause
conflict.
How will this kind of conflict during replication be handled in
1) Merge Repln
2) P2P repln
I want to go with P2P here as there is only one less probable conflict
scenario to get the better performance that P2P offers. Any suggestion how
this will behave or should be handled in P2P.
Thanks,
Lalitha
Lalitha,
in merge the selected conflict resolver will run and you'll get a message
like this: "The row was inserted at 'computer1.Sub1' but could not be
inserted at 'computer2.Northwind'. Violation of PRIMARY KEY constraint
'PK_PublicationName'. Cannot insert duplicate key in object 'tyourtable'."
In P2P you'll have to modify the stored procedures to avoid an error which
will stop the distribution agent from running, or use the SkipErrors
parameter to avoid it.
However, if the PK is PlanID and a geographic identifier, you'll have the
data partitioned and no resulting PK errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

P.S. Tasks > Export

I have spent the last 2 months or so designing and modeling my new application backend in SQL Server 2005 Express edition.

I am ready to move it to a production copy of SQL Server 2005.

How do I go about this?

What kind of words can I put into google to bring some sort of results.

I have been looking in many places but no luck on how to take all the tables I have made (some with data, some not) and copy them to a live SQL 2005 Server.

Thanks in Advance for any help.

When I right-click on the Database in the Manager, I choose Tasks..

But Import / Export options are NOT THERE.

What do I do?

Why is this option not in my instance of SQL Express.

Thank you.

|||Depending on how you want the database hosted you could use detach/attach, backup/restore or scripting of schema and data.|||

How can I produce a schema? I'm assuming that is what I need to do. I am moving the database from my local machine (SQL Server Express) to a hosted SQL Server 2005 machine.

Thank you for your help,

Jon

|||You would need to write a few lines of code that call SMO.|||Jon,
I am in the same boat with my Express db. I have signed up with CrystalTech for hosting. Did you find a good solution to your problem?

Greg Lamb

Owner of table creation in SP

Hi everyone and Happy Holidays!

I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?

Thanks in advance,
CatI never write application code that creates permanent database objects on the fly. it's bad news more often than not.|||Hi Thras,

They're not actually permanent, but they need to stick around for longer than the stored proc that's creating them.

Cat|||How about this then. Create the table as dbo.<tablename> with the needed columns, a guid as the PK, and a DatePopulated column as an indexed column. When the user needs to insert data, grab a guid and getdate() and put it all in the table. Save the guid for later use.

If the user needs to retrieve the data, use the held guid to access it.

Have a scheduled job that runs periodically (daily, hourly, whatever) that deletes from the table after the desired retention period has expired.

No more tables created by users, schema gets backed up with your backups, everyone is happy!|||????

What happens when the same sproc executes at the same time?|||Each spid gets a different guid ... unique dataset per loser ... err I mean user.

ALso have seen that technique used for delayed paging ... sweep data every 20 minutes or so ro remove stale data.|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?

ka boom

why not a temp table?|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.|||I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.

Was the boss short?|||no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?

ka boom

why not a temp table?

Sorry Brett ... real live dba creates one and only one table before any proc runs. Proc inserts into the table, and retrieves data as needed. Scheduled job clears the table of stale data.

Proc does not create table, that way no loser tables to clean up!


Was the boss short?

With pointy hair!|||Was the boss short?

no. he was rather tall.|||The table name has an identifier imbedded into it, it works like Tom's suggestion but the data is distributed into separate tables with a look-up for the TableID. Please note, this was not my design so please don't shoot me! :shocked:

There is a nightly process to go through and drop the tables (which is one place where I'm running into the difficulty of having them created by the user login).

Cat|||got some examples?|||It's all very ugly because any stored procs that access these tables have to use dynamic SQL statements like...

declare @.cmd varchar(1000)
set @.cmd = 'select * from tDynBrokerage' + convert(varchar(10), @.tabid)
exec @.cmd

where @.tabid is passed in from the application. I don't like it but I'm stuck with it for the time being. That's what I get for going on maternity leave! So... back to the original question. Any thoughts on that?

Thanks,
Cat|||Why can't these be global temps again?

OWC scans all partitions (SSAS 2005)

Hello,

I have a cube with three partitions. Each partition holds data for the years 2005,2006 and 2007 respectively. In our web application we are creating OWC Pivot Table and putting "date dimension" in the filter area automatically. Altough only "2007-01-01" is included in filter, when we trace the query from profiler we saw that all partitions are read. Is there anyone encountered the same thing?

By the way, storage modes for all partitions are MOLAP.

Nilgun Celikok

Hi Nilgun,

First of all, have you set the Slice property on your partitions? Although in general AS is able to detect what data is in each partition, read the following article for details on when it will and won't work:

http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

In your case it sounds like it will be quite straightforward to set the slice, and this will also detect whether your partitions hold the data you think they hold (which could be another reason why you're seeing AS scan all partitions).

If you have set the slice, however, you could be running into an issue I've seen before and which I think is fixed in the latest cumulative hotfix build for SP2:

http://support.microsoft.com/kb/936305

Look for the brief description under issue 50000890.

HTH,

Chris

Overcoming the connection limitations of MSDE....

I have a Windows application I have developed in VB using a MSDE
database. I will be distributing this to clients, most of which will
have only about 2-4 terminals, but there are many that will have 10-15
terminals running my application. I am afraid of what the performance
is going to be like with clients running a high number of terminals
because of the limitations of MSDE. I looked into the price of the
full version of SQL server for clients with a larger number of
terminals but it would cost way to much so that simply is not an
option for any of them.
I have spoken to other companies who develop products like mine and
they say they have clients running close to 30 terminals off of MSDE
with no slow down. They say they have done this by using connection
pooling. I have looked around on this issue and most people post
articles on connection pooling and the MSDE workload governor but I
have not seen any practical examples on specifically how to setup a
client application to overcome the MSDE workload limit.
As far as connection pooling is concerned, how exactly will this help
in limiting the workload? Does anyone have an specific examples of
how I would do this? I don't want to keep looking around and having
to experiment around with things that may or may not work because I
have no real way of testing this other than giving it to clients to
test. I'd really like to see an actual example of how someone has
done specifically what I'm doing here and has gotten it to work. I'm
surprised there are not more articles on this specific subject, but I
could not find any.
Any help would be appreciated, thanks...
On 15 Apr 2004 08:45:47 -0700, bostonpartykid@.yahoo.com (Ray Lavelle)
wrote:

>I have a Windows application I have developed in VB using a MSDE
>database. I will be distributing this to clients, most of which will
>have only about 2-4 terminals, but there are many that will have 10-15
>terminals running my application. I am afraid of what the performance
>is going to be like with clients running a high number of terminals
>because of the limitations of MSDE. I looked into the price of the
>full version of SQL server for clients with a larger number of
>terminals but it would cost way to much so that simply is not an
>option for any of them.
>I have spoken to other companies who develop products like mine and
>they say they have clients running close to 30 terminals off of MSDE
>with no slow down. They say they have done this by using connection
>pooling. I have looked around on this issue and most people post
>articles on connection pooling and the MSDE workload governor but I
>have not seen any practical examples on specifically how to setup a
>client application to overcome the MSDE workload limit.
>As far as connection pooling is concerned, how exactly will this help
>in limiting the workload? Does anyone have an specific examples of
>how I would do this? I don't want to keep looking around and having
>to experiment around with things that may or may not work because I
>have no real way of testing this other than giving it to clients to
>test. I'd really like to see an actual example of how someone has
>done specifically what I'm doing here and has gotten it to work. I'm
>surprised there are not more articles on this specific subject, but I
>could not find any.
>Any help would be appreciated, thanks...
Don't keep your connection open. Open a connection, get the desired
data and then close the connection. Open a connection, update/insert
the desired data and then close the connection. If your app doesn't
do really intensive data access and you aren't keeping the connection
open, chances of having more than 5 users hit the DB at the same time
are pretty slim.
HTH,
Bryan
__________________________________________________ __________
New Vision Software "When the going gets weird,"
Bryan Stafford "the weird turn pro."
alpine_don'tsendspam@.mvps.org Hunter S. Thompson -
Microsoft MVP-Visual Basic Fear and Loathing in LasVegas
|||hi Ray,
"Ray Lavelle" <bostonpartykid@.yahoo.com> ha scritto nel messaggio
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application.
> CUT
as Bryan already pointed out, keep your transactions as short as possible...
it's not a matter of connections, becouse users are "usually" idle, and
connection pooling is on by default, but can be not that useful, becouse
connections in the pool can be reused only if the connection string is exact
the same as the last used...
but, again, keep your batches as short as possible.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ray I have one question though about your scenario as I am in the same boat as you are...
How do you plan to deploy your app with MSDE. I know Microsoft has dismissed merge modules way and is advising the bootstrapper. I am still investigating the bootstrapper but was wondering how will you be setting up your desktop app (I believe it is des
ktop app) so that the data (MSDE database) is on shared server. Are their any specific customizations that you are doing to the setup. Do you have any code for this which you can post for all...
Thanks a goodluck
dev
|||The number of connection that you can have with MSDE are the same as SQL
Server (32767) . The limits in MSDE are related to the amount of work that
it can do at any given time and the size of the database. If your clients
are not hammering the database server with constant requests then it is
certainly possible to scale to possibly hundreds of connections. For
information about how this workload governor works see this link:
http://msdn.microsoft.com/library/?u...asp?frame=true
Your application should only hit the database when it needs to and you
should strive to get all the data you need for a function of your
application in one request. Use stored procedures to handle the logic of
what data elements you need; rather than getting a piece of data returning
it to the application, deciding you need an additional pieces of data and
then fetching them repeatedly from the database.
We have customers using MSDE with our application and running many client
workstations with no problems. On the other hand, many of our customers
already have one or more installations of SQL Server on site which they
install our database to and are able to scale to dozens of workstations.
Jim
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0404150745.2f9911ec@.posting.google.c om...
> I have a Windows application I have developed in VB using a MSDE
> database. I will be distributing this to clients, most of which will
> have only about 2-4 terminals, but there are many that will have 10-15
> terminals running my application. I am afraid of what the performance
> is going to be like with clients running a high number of terminals
> because of the limitations of MSDE. I looked into the price of the
> full version of SQL server for clients with a larger number of
> terminals but it would cost way to much so that simply is not an
> option for any of them.
> I have spoken to other companies who develop products like mine and
> they say they have clients running close to 30 terminals off of MSDE
> with no slow down. They say they have done this by using connection
> pooling. I have looked around on this issue and most people post
> articles on connection pooling and the MSDE workload governor but I
> have not seen any practical examples on specifically how to setup a
> client application to overcome the MSDE workload limit.
> As far as connection pooling is concerned, how exactly will this help
> in limiting the workload? Does anyone have an specific examples of
> how I would do this? I don't want to keep looking around and having
> to experiment around with things that may or may not work because I
> have no real way of testing this other than giving it to clients to
> test. I'd really like to see an actual example of how someone has
> done specifically what I'm doing here and has gotten it to work. I'm
> surprised there are not more articles on this specific subject, but I
> could not find any.
> Any help would be appreciated, thanks...
|||Jim,
Just curious, how do you change the connection string to specify where the database as some of your clients use MSDE and some use SQL Server.. does it happen during the installation. Also do you prompt them during installation on whether they want to ins
tall MSDE or use SQL Server they already have...
thanks
dev
|||You'd be much better off just disabling the workload governor. It's really
easy to do, all you need is change one byte in a hex editor.
Here's the method:
1. The file you will be changing is SQLBOOT.DLL. This can be found in
Microsoft SQL Server\MSSQL\Binn. The rest of this method only applies to
version 2000.080.0194.00 of the file, as included in Service Pack 3a.
Check the version of the file by looking at the Version tab of its
Properties window, and the size of the file, which should be 33,340 bytes.
You may also want to check the MD5 sum of the file, which is
175b236765fb446f46da5da635681ab8.
2. Obtain a suitable hex editor from somewhere. A fairly decent one that can
do the job is available here:
http://www.catch22.org.uk/software/hexedit.asp
3. Make a backup copy of SQLBOOT.DLL somewhere, just in case.
4. Stop MSDE's MSSQLSERVER service.
5. Open the original SQLBOOT.DLL in the hex editor.
6. Find the byte at location 10145 decimal / 0x27A1 hexadecimal.
7. The current value of the byte should be 08. Change it to 00.
This changes the target benchmark users / concurrent workload limit value
from eight to zero. When the SQL Server reads this as being zero, it
disables the workload governor.
8. Save SQLBOOT.DLL.
9. Start the MSSQLSERVER service.
If it worked, the SQL statement "DBCC CONCURRENCYVIOLATION" should return
without any output (normally it would display a summary of when the workload
governor has been active.)
Hope this helps!