Advice on security model for XML Web Services for many users in Active Directory role

I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.

The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.

In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.

Am I correct in the above, or is there a more efficient way to achieve these results?

Thanks

-L

Your post seems to have been moved to this forum, as I have not seen it before. Could you please let me know where you posted this originally?

If the individual users are all belonging to a Windows group, you could just grant connection permissions to that group. But I am not familiar with XML Web Services, so I cannot say whether this is the best course of action.

Have you checked the following link?

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

I'd suggest to also post in the XML forum: SQL Server XML

Thanks
Laurentiu

sql

advice on hosting website safely

Hello,

For one of my clients I have built 2 applications. The 1st one is a windows based application that is used for production and employee scheduling. The data for this application is stored in a SQL Express database. The 2nd one is a ASP.NET 2.0 site that contains the clients website and a portal page. The portal page uses forms authentication and redirects a customer to a page that shows the production schedule for this customer. So I have 2 kind of users: local (LAN) and web. Both using the same database.

For test purposes the site is currently hosted on the clients SBS 2003 server (this is the only server). However I don't think it is wise to host the public website on this server. What is the best configuration to host the public website safely? I read about putting a webserver in a DMZ. But I need a connection with the SQL Express server to retrieve the data for the portal section. As well for authenticating the customer and for retrieving the schedule data.

I'm currently using Windows Authentication on the SQL server. Can this still be used when the webserver is in the DMZ?

If I understand it correctly the webserver in the DMZ is not part of the local domain? Also could remote hosting be an option at all?

Any help and advice would be highly appreciated.

Remote hosting would be the 'safest' option. If the box is hacked, the hacker is NOT in your network. Siting in a DMZ 'could' be relatively safe -if you are certain that there are no 'holes' in the firewall.

The public website (application) may be best using Application Pooling security on the IIS box. You can connect to a SQL Server using an IP address instead of a serverName. Use the IP address and the Port in the connection string.

Since a server in a DMZ is not part of the local domain, you cannot use Windows Authentication on the SQL Server.

I suggest that if you were to visit the forums and blogs at www.ASP.NET, you will find folks with a lot of experience with this particular issue.

Advice on Data Warehouse & Web Reporting

I'm about to embark on re-writing a database & bespoke web reporting
application for our call centre & would like a little advice please.
Currently the database has 10 tables containing summaried (<=1 record
per staff member per day) data from different legacy systems,
populated by DTS. There is an 11th table that has staff data in which
is used to link the others together as many have different primary
keys. After the data has been linked together an aggregated table (1
record per person per day) is created once a day.
Currently our intranet site is configured to run a number of stored
procedures that return KPI data from the aggregated table into
datasets which are then rendered in the form of datagrids. Users are
either allowed to specify the parameters for these stored procedures
or they are pre-determined for them depending on who they are (eg
agents in the call centre all see a MTD report for themselves only).
The aim of the re-write is to
(a) cut down on admin when KPI definitions change
(b) make the setup much more generic so that it could be transported
to other areas of the business or even to different companies with
minimum rework
(c) upgrade from SQL 2000 to SQL 2005
(d) tidy the webpages a little & maybe add some gauge type controls
I'm unsure about 2 things -
(1) Should I totally re-design things & use Analysis Services instead
or would I find no benefit as everyone is only given one view of the
truth (ie no slicing & dicing depending upon preference)? I know very
little about this service so it would be a challenge & from what I've
read I'm not so sure whether it would be appropriate for all of the
staff querying the database constantly anyway(there are over 500 of
them & currently the stored procedures use nested temp tables to
calculate everything that needs to be shown on the webpages). I guess
that I couldn't fill a datagrid with their data using this method
either but I'm sure that someone will be able to keep me right.
(2) Should I dump the datagrids in favour of Reporting Services? This
was originally not used as our IT department could get it installed
properly on the SQL 2000 server & the datagrid solution was found to
be both adequate & easy to setup. We have Crystal Reports in the
company also but licence costs are likely to be a problem.
Hope I haven't upset anyone by crossposting the question - I'm just
after a balanced view before I start work & the queries fit with a few
different ng's.
TIA
SteveI think that AS is more important; more critical-- than RS.
there are other tools like RS on the market.
but AS leads the market by a wide margin.
Does that mean it's EASY? no. Does it mean it's SIMPLE? no.
I would reccomend taking a month off of work; immersing yourself in
SSAS and coming back to work to scrap all your existing DB work.
10 million relational developers CAN be wrong and they are.
It's better to build a solution for non technical people-- SSAS is best
utilized using OWC - Office Web Components- and non-technical people...
All of your relational mess just sounds overly complicated.
-Aaron
C4rtm4N wrote:
> I'm about to embark on re-writing a database & bespoke web reporting
> application for our call centre & would like a little advice please.
> Currently the database has 10 tables containing summaried (<=1 record
> per staff member per day) data from different legacy systems,
> populated by DTS. There is an 11th table that has staff data in which
> is used to link the others together as many have different primary
> keys. After the data has been linked together an aggregated table (1
> record per person per day) is created once a day.
> Currently our intranet site is configured to run a number of stored
> procedures that return KPI data from the aggregated table into
> datasets which are then rendered in the form of datagrids. Users are
> either allowed to specify the parameters for these stored procedures
> or they are pre-determined for them depending on who they are (eg
> agents in the call centre all see a MTD report for themselves only).
> The aim of the re-write is to
> (a) cut down on admin when KPI definitions change
> (b) make the setup much more generic so that it could be transported
> to other areas of the business or even to different companies with
> minimum rework
> (c) upgrade from SQL 2000 to SQL 2005
> (d) tidy the webpages a little & maybe add some gauge type controls
> I'm unsure about 2 things -
> (1) Should I totally re-design things & use Analysis Services instead
> or would I find no benefit as everyone is only given one view of the
> truth (ie no slicing & dicing depending upon preference)? I know very
> little about this service so it would be a challenge & from what I've
> read I'm not so sure whether it would be appropriate for all of the
> staff querying the database constantly anyway(there are over 500 of
> them & currently the stored procedures use nested temp tables to
> calculate everything that needs to be shown on the webpages). I guess
> that I couldn't fill a datagrid with their data using this method
> either but I'm sure that someone will be able to keep me right.
> (2) Should I dump the datagrids in favour of Reporting Services? This
> was originally not used as our IT department could get it installed
> properly on the SQL 2000 server & the datagrid solution was found to
> be both adequate & easy to setup. We have Crystal Reports in the
> company also but licence costs are likely to be a problem.
> Hope I haven't upset anyone by crossposting the question - I'm just
> after a balanced view before I start work & the queries fit with a few
> different ng's.
> TIA
> Steve

Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.sql

Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.

Advice needed on creating a test environment

I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
Is it possible to take a backup of prod at the appropriate time and restore
it on your test box?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:78asg1pda4tvee5ug2kh5o5et09bu8m458@.4ax.com...
I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?
|||On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:

>Is it possible to take a backup of prod at the appropriate time and restore
>it on your test box?
I don't think I can do that because a lot of my development is on
stored procedures. If I back it up and restore I'd lose my work so I
would have to save off my work, restore the backup and then recreate
my work. Right now I am thinking that my only solution is going to be
using DTS to copy all the data over after it does its nightly
retrieval of data from corporate (it is a moderate amount of data)
|||Hi,
I think in this case you may go for Transactional replication only for
Selected tables.
U can specify the time at which it needs to Copy the data from Production to
Developement.
Any help reply..
Regards,
Herbert
"Matthew Speed" wrote:

> I have built a sizable (for me anyway) SQL application for a client on
> which they now base their entire business. I need to set up a
> parallel installation of the database for development purposes, the
> idea being I can make changes to the development version without
> impacting day to day operations until it is confirmed that all changes
> are working correctly. My problem is this: the application gets
> information from their headquarters every night along with information
> from employees throughout the day. Every night the system performs an
> hour of batch processing on the combined data. In order for this
> sandbox to work, I need to be able to import into the test system an
> exact copy of the data only on which the various stored procedures are
> going to do their work.
> It makes sense that the time to copy the data over is after the
> poduction system finishes getting its data and before the batch
> processing starts.
> Is the best way to do this to create a bunch of DTS packages to
> replace the development data tables with the production using an
> append?
>
|||Why don't you store all your work do disk? I wouldn't dare to work in an environment where my latest
revision is in the database? There are also tools that can compare schemas and create a diff DDL
script based on that. This way, you have your source code in .sql files and after restoring the
backup, you just run that .sql file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:bm4tg15kphl0966uf4ie311ov86q72jgui@.4ax.com...
> On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote:
>
> I don't think I can do that because a lot of my development is on
> stored procedures. If I back it up and restore I'd lose my work so I
> would have to save off my work, restore the backup and then recreate
> my work. Right now I am thinking that my only solution is going to be
> using DTS to copy all the data over after it does its nightly
> retrieval of data from corporate (it is a moderate amount of data)
|||On Thu, 25 Aug 2005 16:34:37 -0400, Matthew Speed <mspeed@.mspeed.net>
wrote:
>I have built a sizable (for me anyway) SQL application for a client on
>which they now base their entire business. I need to set up a
>parallel installation of the database for development purposes, the
>idea being I can make changes to the development version without
>impacting day to day operations until it is confirmed that all changes
>are working correctly. My problem is this: the application gets
>information from their headquarters every night along with information
>from employees throughout the day. Every night the system performs an
>hour of batch processing on the combined data. In order for this
>sandbox to work, I need to be able to import into the test system an
>exact copy of the data only on which the various stored procedures are
>going to do their work.
>It makes sense that the time to copy the data over is after the
>poduction system finishes getting its data and before the batch
>processing starts.
>Is the best way to do this to create a bunch of DTS packages to
>replace the development data tables with the production using an
>append?
Well, is that the only change made to the production data, appends?
Probably not!
Herbert's suggestion to consider replication is a good one.
But, do you really need up-to-date data every day? Usually there is
development that goes on for some days or weeks (or months or years),
often to a different schema than production is using (!!!), and only
then is even somewhat up-to-date data needed for testing.
It's pretty easy to script out all your SPs, restore a backup of
production onto your test machine, then script the SPs back in. Or
keep the SPs in a separate database.
Or, arrange some DTS logic sufficient to do your updates on demand,
but probably more than just appends.
Lots of ways to go, but frankly, none of them is painless!
J.

Advice needed on creating a test environment

I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?Is it possible to take a backup of prod at the appropriate time and restore
it on your test box?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:78asg1pda4tvee5ug2kh5o5et09bu8m458@.4ax.com...
I have built a sizable (for me anyway) SQL application for a client on
which they now base their entire business. I need to set up a
parallel installation of the database for development purposes, the
idea being I can make changes to the development version without
impacting day to day operations until it is confirmed that all changes
are working correctly. My problem is this: the application gets
information from their headquarters every night along with information
from employees throughout the day. Every night the system performs an
hour of batch processing on the combined data. In order for this
sandbox to work, I need to be able to import into the test system an
exact copy of the data only on which the various stored procedures are
going to do their work.
It makes sense that the time to copy the data over is after the
poduction system finishes getting its data and before the batch
processing starts.
Is the best way to do this to create a bunch of DTS packages to
replace the development data tables with the production using an
append?|||On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>Is it possible to take a backup of prod at the appropriate time and restore
>it on your test box?
I don't think I can do that because a lot of my development is on
stored procedures. If I back it up and restore I'd lose my work so I
would have to save off my work, restore the backup and then recreate
my work. Right now I am thinking that my only solution is going to be
using DTS to copy all the data over after it does its nightly
retrieval of data from corporate (it is a moderate amount of data)|||Hi,
I think in this case you may go for Transactional replication only for
Selected tables.
U can specify the time at which it needs to Copy the data from Production to
Developement.
Any help reply..
Regards,
--
Herbert
"Matthew Speed" wrote:
> I have built a sizable (for me anyway) SQL application for a client on
> which they now base their entire business. I need to set up a
> parallel installation of the database for development purposes, the
> idea being I can make changes to the development version without
> impacting day to day operations until it is confirmed that all changes
> are working correctly. My problem is this: the application gets
> information from their headquarters every night along with information
> from employees throughout the day. Every night the system performs an
> hour of batch processing on the combined data. In order for this
> sandbox to work, I need to be able to import into the test system an
> exact copy of the data only on which the various stored procedures are
> going to do their work.
> It makes sense that the time to copy the data over is after the
> poduction system finishes getting its data and before the batch
> processing starts.
> Is the best way to do this to create a bunch of DTS packages to
> replace the development data tables with the production using an
> append?
>|||Why don't you store all your work do disk? I wouldn't dare to work in an environment where my latest
revision is in the database? There are also tools that can compare schemas and create a diff DDL
script based on that. This way, you have your source code in .sql files and after restoring the
backup, you just run that .sql file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:bm4tg15kphl0966uf4ie311ov86q72jgui@.4ax.com...
> On Thu, 25 Aug 2005 21:11:34 -0400, "Tom Moreau"
> <tom@.dont.spam.me.cips.ca> wrote:
>>Is it possible to take a backup of prod at the appropriate time and restore
>>it on your test box?
> I don't think I can do that because a lot of my development is on
> stored procedures. If I back it up and restore I'd lose my work so I
> would have to save off my work, restore the backup and then recreate
> my work. Right now I am thinking that my only solution is going to be
> using DTS to copy all the data over after it does its nightly
> retrieval of data from corporate (it is a moderate amount of data)|||On Thu, 25 Aug 2005 16:34:37 -0400, Matthew Speed <mspeed@.mspeed.net>
wrote:
>I have built a sizable (for me anyway) SQL application for a client on
>which they now base their entire business. I need to set up a
>parallel installation of the database for development purposes, the
>idea being I can make changes to the development version without
>impacting day to day operations until it is confirmed that all changes
>are working correctly. My problem is this: the application gets
>information from their headquarters every night along with information
>from employees throughout the day. Every night the system performs an
>hour of batch processing on the combined data. In order for this
>sandbox to work, I need to be able to import into the test system an
>exact copy of the data only on which the various stored procedures are
>going to do their work.
>It makes sense that the time to copy the data over is after the
>poduction system finishes getting its data and before the batch
>processing starts.
>Is the best way to do this to create a bunch of DTS packages to
>replace the development data tables with the production using an
>append?
Well, is that the only change made to the production data, appends?
Probably not!
Herbert's suggestion to consider replication is a good one.
But, do you really need up-to-date data every day? Usually there is
development that goes on for some days or weeks (or months or years),
often to a different schema than production is using (!!!), and only
then is even somewhat up-to-date data needed for testing.
It's pretty easy to script out all your SPs, restore a backup of
production onto your test machine, then script the SPs back in. Or
keep the SPs in a separate database.
Or, arrange some DTS logic sufficient to do your updates on demand,
but probably more than just appends.
Lots of ways to go, but frankly, none of them is painless!
J.

Advice needed - MSDE vs. Access

Hi, I currently have a application that is being used throughout the
country at different customers. Currently it is a VB6 application accessing
DBF/CDX standalone tables via ODBC. We are developing the 'next
generation' of the program in VB.NET and will do data access via OLEDB.
Here is the issue: Stand alone Visual Foxpro DBF tables do not return
'primary key' information, so we lose a lot of potential functionality by
not being able to set relations in a Dataset. Therefore, we are looking
for a new database to host the application. The application DB requirements
are basic INSERT, UPDATE, DELETE. No data replication, or anything fancy.
No "DB security" required. In reality, the application is coded so that it
will actually run on a SQL server or Oracle enterprise DB, too. But
customers that choose that option have an IT dept with appropriate
expertise, so I'm not worried about them.
Here's the problem... most of our customers are in small shops and are
barely computer literate. We need something REALLY simple. With the DBF
files it couldn't be simpler... install the program and then run it. No
real maintenance (except backup of course) is needed. I'm looking at
either MS Access or MSDE as a replacement database. Given that my
customers are the types that use the CD drawer as a cup holder, I'm
concerned that MSDE might be too much for them, and that Access might be
easier for them. Right now this is just my initial thought, as I'm not
familiar with MSDE. Before I get too far down the road, I wanted to tap
some expertise for advise.
So, the application will be coded to the 'lowest common denominator' but
will able to run on SQL server and ORACLE also. The question is, given the
small shops I need to cater to, what should that 'lowest common denominator'
be? Access or MSDE?
Any advice graceously accepted... Thanks.
John
hi John,
JohnR wrote:
>...
> So, the application will be coded to the 'lowest common denominator'
> but will able to run on SQL server and ORACLE also. The question is,
> given the small shops I need to cater to, what should that 'lowest
> common denominator' be? Access or MSDE?
>
difficult question, as the 2 engines can not be compared... ok they are both
database engines, but very different.... from my point of view I'd go with
MSDE as it can be easily scaled to full blown SQL Server editions with no
harm at all... and as you already know, JET database engine is in
maintenance and no additional features will be provided for it..
on the other side, MSDE, and soon SQLExpress, could require some more
maintenace (you can include in you application, for quite all administrative
tasks)... but they are another level of db engines, comparable with Oracle,
where the JET engine is not...
for SQL Server connections I'd go for the SQLClient name space versus the
OLEDB conterpart asi it provides better and targeted support for the SQL
Server worls, where the OLEDB provider is quite generic... but this is
another story..
more... SQLExpress will support easy setup and deployment for your database,
as long as XCopy support...
http://msdn.microsoft.com/library/de...seoverview.asp
provides additional info..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Advice for grouping stored procedures?

I am preparing to upgrade a fairly large application, so I'm looking for some advice before I begin my design. I have quite a bit of experience in Oracle, but I am required to use SQLServer for data storage in this application. When using Oracle, it is easy to group related stored procedures into packages. In SQLServer; however, I don't seem to have that option. Any opinions on a good way to group code for easy maintenance and navigation (i.e. in Management Studio - I'd prefer not to see a list of several hundred stored procedures)? I've already thought about naming conventions, but I'd prefer a solution that is more like the Oracle package solution.In SQL 2005 you can create a project and store all scripts for procs in it also you can use Schemas to group your database objects and rights to them.

Good Luck.sql

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advanced
Hi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database may
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overload
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:

> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>
|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:

>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.
|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenario
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>
|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
--
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advancedHi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database ma
y
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overloa
d
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:

> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the databas
e.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:

>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database
.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenari
o
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
--
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advancedHi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database may
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overload
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:
>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenario
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>> Hello All,
>> I am writing a database application that will serve multiple companies,
>> each company contains users that will store/retrieve data from the
>> database.
>> I am in the process of deciding whether I should have one big database
>> that stores data for all users of all companies or have a separate
>> database for each company.
>> What would be the advantage/disadvantage of selecting one over the other
>> with the following considerations?
>> Considerations:
>> --
>> 1. failover support
>> 2. performance
>> 3. load balancing via adding multiple database servers
>> 4. clustering
>> 5. backup (full and incremental)
>> 6. database upgrade (eg. adding more tables and relationships, modifying
>> tables and relationships)
>> 7. special equipment considerations
>> Any advised would be greatly appreciated.
>> Thanks in advanced
>|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advice

I have an application that connects to my database and
stores in my database ICONS. The application is using the
data type SQL_VARIANT. Was wondering, is sql_variant "ok"
to use... is there anything better... how about pointers?
Can anyone give me some advice on this...
ThanxsIf you by icons mean images (pictures), then I guess you should be using the
image datatype or varbinary. Only
use sql_variant if you really need *different* underlying datatypes for each
row. I've never had to use
sql_variant, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:1696601c4172c$9b93c890$a101280a@.phx
.gbl...
> I have an application that connects to my database and
> stores in my database ICONS. The application is using the
> data type SQL_VARIANT. Was wondering, is sql_variant "ok"
> to use... is there anything better... how about pointers?
> Can anyone give me some advice on this...
> Thanxs

Adverse effects of too may "sleeping" connections

Hi,
I have an application which maintains around 25-30 connections to the
database.
The status for all those are "sleeping" most of the time.
The database is MSDE.
I would like to know what are the effects on performance, locking and any
other issuses due to this "sleeping" connections.
Any technical document or link would be of great help
hi,
LazyDBA wrote:
> Hi,
> I have an application which maintains around 25-30 connections to the
> database.
> The status for all those are "sleeping" most of the time.
> The database is MSDE.
> I would like to know what are the effects on performance, locking and
> any other issuses due to this "sleeping" connections.
> Any technical document or link would be of great help
no effects on on performance and/or locking... just memory footprint and
"wasted" resources...
each connection, live or sleeping, will eat about 24kb of memory, calculated
as 12 KB + 3 times the Network Packet Size (default setting that can be
partially customized via sp_configure system stored procedure modifying the
'user connections' setting), used to store the data structures holding the
connection 's context, as long as for buffer used to send and receive the
relative associated network streams (default to 4KB network packet setting),
that can be stolen from the buffer pool memory region and/or the MemToLeave
memory area... as these memory regions are not infinite (:D) you are wasting
some bytes.. :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Adversarial Vendors

I'm working with a vendors (Stockamp & Assoc.) We hired them for the Clinic Ontrac application (SQL Server based) and they highly customized it to work in our hospital. They left after implementing the software in 3 of our 46 clinics. As I add more clincs to the system I continuously run into issues. Stockamps response to the problems is : "The system was working fine when we left what have you done with it?"
I'm continually given contradictory solutions to problems, then being blamed for modifying their system.
What are others doing when working with vendors that assume this type of adversarial position?This is difficult to answer - can you be more specific. However, in my experiences - go up the chain until it is addressed and resolved. Also, if possible, make sure you are involved with the contract process (most companies make assumptions and end up getting burned).|||Originally posted by rnealejr
This is difficult to answer - can you be more specific. However, in my experiences - go up the chain until it is addressed and resolved. Also, if possible, make sure you are involved with the contract process (most companies make assumptions and end up getting burned).

Here is my latest example. They have a form that allows me to add new clinics and specifics for that clinic. I added 2 new clinics into a test system and it failed. After spending 8 hours running through the whole process of adding a clinic (many forms are used and they have drop down boxes that are very error prone) , checking my data entry and trying to understand the strange error I was receiving, I finally sent an email to their tech support with the error, what I had done and asked for recommendations.
It turns out the forms' data are sent through to a SQL variable that only holds 8000 characters. Stockamp & Associates had known of the problem for many months and had not notified us, posted an issue to a log ... I escalated the problem up their ladder and the response from the high ups is that I should have been monitoring the number of characters I put into their form. I asked for documentation or a utility for monitoring the characters and they have none. They fixed the problem and now the variable holds 16,000 characters. I now have 16 of 46 clinics clinics in the system so I'm less than half way done, so even though they have doubled the limitation I am certain to hit the limit again. To monitor I will now have to count characters in their cludgy forms and hope I don't accidently modify something in the dropdowns and pray that I don't lose count!

In another instance, I was testing adding a new clinic and had modified one of Stockamps' stored procedures in a test database to view dates beyond those available in their forms (as I was shown by their trainer). One of Stockamps' analysts is also working on fixing other bugs. I had no idea they were using my test database because I set up a different database for them to use. The next day when I was told they implemented some fixes into our live server. By 10AM the live server was no longer functioning. I started looking into the problem and found that the analyst had modified some lines of the same stored procedure, gotten them to work, then replaced the sproc on the live server with the entire sproc from the test server - which included my modification. I found the problem and asked that they fix it (I'm afraid to touch sprocs in the live server for fear of blame) and was told that I had created the problem and needed to fix it myself, and that I was not communicating modifications I was making.

In another instance Stockamps DBA told me I could run sp_spaceused and truncate any large history tables to recoop some space (the system grows over 100 mb/day and although they have a purge, I was told it didn't function properly so I was not to use it). I truncated some tables in my test database to see the results. Again Stockamp used my test database to test some of their fixes and they kept failing. I found the problem and realized that the truncation was causing the problem. Again I was told that the problem was caused by me and the modifications I was making to their system.

I acknowledge that my truncation caused the problem, but I was acting under Stockamps directions and when the problem arose I researched the problem and figured it out. I admit my fault and want to move on and keep working with them to get the system functional, but anytime something goes wrong I am blamed.|||Well part of the problem seems to be that they are unwilling to take blame/responsibility for anything...

The first step in resolving this is to get them to document everything they recommend to you. Get everything in writing and communicate back to them in writing. Then when an issue arises you can point to the communication that has taken place and show that they were at fault. Then they realy have no choice but to accept some responsibility (or look like total dickheads).|||I would start an email chain - letting the tech support know that you will email any recommendations from them for confirmation. Next, disable their ability to touch YOUR test database - that will eliminate both of you from overlapping code. Lastly, email their vp of sales/tech support with the list of issues - carbon copy the highest ranking official in your company that was responsible for the contract with the vendor(s) (but communicate with this person the problems and get their support). As far as the forms issue - that I would let your company know as a major flaw in the product. They should have validation on the forms based on their limitations - which should also be documented in their product. I would investigate that further.

AdventureWorks vs AdventureWorksDW

I am new to SQL Server.
There are 2 databases for AdventureWorks sample application, AdventureWorks and AdventureWorksDW.
Is the AdventureWorksDW database generated from AdventureWorks or it is design manually?
Can anyone explain to me how to produce the AdventureWorksDW database? Thanks.

You can download both databases from this link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

In this forum, we make use of the Adventure Works OLAP database built off the AdventureWorksDW relational database. When you download the samples from the link above, you will be given a Visual Studio solution that will build the OLAP database for you. Read the README files that come with the download for details.

Good luck,
Bryan

adventureworks application error

I am using ms sql server 2005 Enterprise Evaluation Edition, and I can use the other sample dtabases, like pubs, northwind, but not adventureworks
I tried diffrent ways,
I am very new to this, and I try before I ask, and I had to give up,
Here is what I did
when I installed adventureworks it was like 166 mb. big
And when I tried to use it with visual studio, every time I want to drag a table from: data connections/adventureworks, and try to veiw it in borwser it gives me an application server error, and invalid object name

with northwind database works fine

then I tried executing instawdb.sql located C:\Progam files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP, from Sql server management studio,
And it finishes with errors, and is only 122 mb. big

This line in red:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AWDB\Address.csv" could not be opened. Operating system error code 3(error not found).

and at the end this:
DBCC SHRINKDATABASE: File ID 1 of database ID 6 was skipped because the file does not have enough free space to reclaim.

last night I was thinking wether it might be my OS( usning windows xp), or server evalution edition

Does anybody know something about this?
I already google search and only found one person with the same problem, but there was no solution posted

You can download an installer for the AdventureWorks database or the new smaller AdventureWorksLT database here

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004sql

ADS: Sample application cannot access desktop database

The sample application provided with Access Database Synchronizer fires a "A request to send data to the computer running IIS has failed. For more information, see HRESULT." exception on my WinCE 4.2 device.

I debugged it with VS2005 to see what was going on and found that the problem resides in the line:

m_connDesktop.Pull("MSysObjects","SELECT Name FROM MSysObjects WHERE Type=1 and flags=0",accessSysConnectString,RDATrackOption.TrackingOff,"Err_MSysObjects");

It seems that connection m_connDesktop is ok but calling Pull method fails.

I've verified that SSEvAccessSync service is started when ActiveSync detects device connection and that firewall rules are correctly updated.

Also, i verified my System.mdw and i discovered that there is no "MSysObjects" table (only MSysGroupList, MSysGroupMembers, MSysUserList and MSysUserMemberships queries are there).

Do you think the problem is there?

How can i solve it without damaging System.mdw?

I cannot figure out what's the purpose of this line of code, can you help me?

Just a summary of what i've tried so far (unsuccessfully):

1. Once discovered MSysObjects hidden table, i changed "Tools->Security->Users and groups auth" to grant read privileges to the user used for connection

2. I've tried to change connection string to include "User Id=...". Anyway, connection object is created successfully: the error appears on Pull method invocation

3. I've configured SQL Server (SQLEXPRESS) to accept network connections, enabled SQL Server Browser and changed firewall settings accordingly

Feedback from someone that put it to work needed, please...

|||

Hi Fabio,

MSysObjects is not a hidden table inside System.mdw. It is a system table available with every access database.

The error message you are getting indicates desktop is not accepting packets.You can try the following scenario:

1) Before doing the Sync from Given Sample App. Just check if you have written the configuration information like Desktop URL, User Name etc. correctly. Default port Number through which we communicate is 1024. If you have changed the port number by editing registry , then you need to change your firewall setting and Desktop URL accordingly.

2) System.mdw file is located at "C:\Document and setting\<LOGIN ID>\System.mdw". If your Windows installation drive is not C:\ drive. you can change the driver letter by editing config.xml file located in device at "\Program File\AccessSync\Config.xml" under WIN_DIR node.

3) If above information is not helpful, you can open the internet explorer browser at device side and put the desktop URL in the address bar. If you are getting information "Page cannot be found. check the name and try again". It means desktop is not accepting packet at given port. you can try the same URL at desktop side internet explorer. If it is working at desktop side and not working at device side, it means connection from desktop is not proper through active sync. If desktop side also URL is not working, it means service is not started or you are having incorrect port number in URL.

Let me know if it doesn't help.

|||

Thank you pakumar,

it seems a connection through activesync problem (see 3.):

1. Here's my Config.xml:

<?xml version="1.0" encoding="UTF-8"?>

<SystemNode>

<AccessDB>C:\TestDB.mdb</AccessDB>

<DeviceDB>\My Documents\TestDB.sdf</DeviceDB>

<Login>Admin</Login>

<URL>http://PCNAME:1024/</URL>

<WinDir>C:</WinDir>

</SystemNode>

"Admin" is the Windows user logged in during synchronization.

I have opened port 1024 (not changed any registry key and checked it anyway) and granted access to "svcssevas31.exe" application. Anyway from now on i decided to shutdown windows firewall until problem solved.

2. My Windows installation drive is C:\. Debugging the app, i've verified that the connection string points to the correct location of System.mdw. I've found MSysObjects table and granted r/w data and schema access to Admin user.

3. From desktop it DOES work. From device it DOESN'T. I'm confused about this because:

From device's shell, i can successfully ping desktop IP address (seen as 127.0.0.1) and machine name|||

Further tests:

URL http://PCNAME:1024/ is accessible from desktop machines on the same network (also with firewall enabled).|||

Hi Fabio,

1) I can see you have installed both 2.0 and 3.0 device cabs. You can uninstall all 2.0 cabs sql.ppc.wce4.armv4.CAB and sql.dev.ENU.ppc.wce4.armv4.CAB . you should keep only 3.0 cab bits. That should solve the problem.

2) You have installed Wince 5.0 cab of sample application to WinCE 4.2 device. You can build the given sample application for WinCE 4.2 or PPC 2003 SE and copy the exe generated to \Program Files\AccessSync\ folder in device.

3) You can try the sample application on device emulator also if you have it, just to gain confidence in the product.

Let me know if you require more help.

ADS Wizard, tutorials and examples - Please Help.

Hi,

I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.

I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:

"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."

I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?

Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.

This paragraph also confuses me:

"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".

Does this mean SQL Everywhere won't work on mobile devices until November?

Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.

Hi

Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.

Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.

If you have installed ADS at the default installation path, you will find the ADS wizard at the following path

C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync

Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details

Hope this helps. If you have any more questions let me know

Regards

Manish

|||

Hi,

Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!

I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.

Does this sound possible? I have tried connection strings like below but it doesn't seem to work.

Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")

m_connDevice.Open()

I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"

The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?

Thanks

|||

"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.

Thanks,

Laxmi

|||

I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?

The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?

|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||

"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.

|||

Hi mhawb,

No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.

I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx

Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.

ADS Wizard, tutorials and examples - Please Help.

Hi,

I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.

I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:

"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."

I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?

Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.

This paragraph also confuses me:

"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".

Does this mean SQL Everywhere won't work on mobile devices until November?

Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.

Hi

Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.

Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.

If you have installed ADS at the default installation path, you will find the ADS wizard at the following path

C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync

Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details

Hope this helps. If you have any more questions let me know

Regards

Manish

|||

Hi,

Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!

I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.

Does this sound possible? I have tried connection strings like below but it doesn't seem to work.

Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")

m_connDevice.Open()

I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"

The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?

Thanks

|||

"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.

Thanks,

Laxmi

|||

I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?

The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?

|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||

"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.

|||

Hi mhawb,

No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.

I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx

Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.