Best Possible Solution

Hi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.

Howdy!What do you mean downloaded from you website?|||Hi, sir.
An offline scheduler type application that could be downloaded from our website. All the data of the clients is at the db server running our website. Now for the first time when the log on to the app they could be offered the facility to download all their data locally from the db. I suspect there could b a better way around to give the data to each provider in an automated way rather than selecting the data for each client; creating a database for each client and shipping it manually with the software.

Howdy!

Best Method for Web Access to SQL

Hi,
I manage a public website for a bank. We just upgraded the website from a M
icrosoft Access database backend to a SQL Server database backend. I initia
lly installed SQL with Windows Authentication only. But then I ran into pro
blems when the website was
configured with Anonymous access because SQL Server was requiring a Windows
User ID and Password. I changed the security to use SQL Server authenticati
on, I created an account in SQL Server, and put the User ID and Password in
my connection string. Now
it works but I am concerned with the security. I don't know if the other SQ
L Server IDs are vulnerable to hacking, e.g. sa.
I was thinking it might be possible to use Windows Authentication only on th
e database and grant access to the IUSR_* account. But I didn't test this t
o see if it would work.
Which is the most secure method of securing SQL Server when the website is c
onfigured with Anonymous access? The current configuration described in the
first paragraph or something similar to what I described in the second para
graph? The website must be
configured with Anonymous access. That is one requirement I can not change.
KB articles would help me.
Thanks.
DaveAdd the IUSR_* account as a login on your SQL Server. Grant it access only
to the relevant databases and objects therein. If your SQL Server and IIS
server are on separate machines - as they should be - you'll need a domain
account for IUSR_*.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"David" <david_eastes@.yahoo.com> wrote in message
news:CB7D44CE-A18F-41AA-BA9B-E60560743DE2@.microsoft.com...
Hi,
I manage a public website for a bank. We just upgraded the website from a
Microsoft Access database backend to a SQL Server database backend. I
initially installed SQL with Windows Authentication only. But then I ran
into problems when the website was configured with Anonymous access because
SQL Server was requiring a Windows User ID and Password. I changed the
security to use SQL Server authentication, I created an account in SQL
Server, and put the User ID and Password in my connection string. Now it
works but I am concerned with the security. I don't know if the other SQL
Server IDs are vulnerable to hacking, e.g. sa.
I was thinking it might be possible to use Windows Authentication only on
the database and grant access to the IUSR_* account. But I didn't test this
to see if it would work.
Which is the most secure method of securing SQL Server when the website is
configured with Anonymous access? The current configuration described in
the first paragraph or something similar to what I described in the second
paragraph? The website must be configured with Anonymous access. That is
one requirement I can not change. KB articles would help me.
Thanks.
Dave

Best DataType for content system

Hello,
On our corporate website, we will be using email notifications for
various things. I would like for our marketing guy to be able to edit
the email templates over the web so that I don't have to keep updating
them myself for every little change. I want to store the templates
within the database (since they will be small). The templates will be
HTML based and not more than a few thousand bytes in length. Should I
use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
not sure.

Thanks,
WillFoehammer (foehammer@.hotmail.com) writes:
> On our corporate website, we will be using email notifications for
> various things. I would like for our marketing guy to be able to edit
> the email templates over the web so that I don't have to keep updating
> them myself for every little change. I want to store the templates
> within the database (since they will be small). The templates will be
> HTML based and not more than a few thousand bytes in length. Should I
> use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
> not sure.

Unless you intend to compress the templates to be able handle case
that the marketing guys enters more than 8000 characters, there is
no reason to use VARBINARY, use VARCHAR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Best approach?

I have a problem. My website hosting company can only host sql server datab
ase that are less than 100 mb. Usually that is not a problem for most of my
applications, however I have one client who has a database with over 270000
records which makes the database over 100MB. What can I do? I thought of
purchasing another database from the host so I'll have 200MB then I can prog
ramatically split the tables up based on ID, for instance if recordID < 1500
00 then move this data to sql server 1. If recordID > 150000 then move data
to sql server 2. Then when I want to query the system for a student, I wou
ld first check the recordID for greater than or less than 150000 then query
the proper table. The data in the system is only used for retrieval (read o
nly), there are no updates to the system.
Do you think this approach is incorrect or do you have any other suggestions
? Thanks!I would find another host for this particular client rather than do a dog an
d
pony dance. I actually like to spend time with my family, so your mileage ma
y
vary. :-)
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Shawn Ferguson" wrote:

> I have a problem. My website hosting company can only host sql server database th
at are less than 100 mb. Usually that is not a problem for most of my applications,
however I have one client who has a database with over 270000 records which makes t
he
database over 100MB. What can I do? I thought of purchasing another database from the hos
t so I'll have 200MB then I can programatically split the tables up based on ID, for instan
ce if recordID < 150000 then move this data to sql server 1. If recordID
> 150000 then move data to sql server 2. Then when I want to query the system for a
student, I would first check the recordID for greater than or less than 150000 then
query the proper table. The data in the system is only used for retrieval (read on
ly
), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>|||Any suggestions for economical SQL Server hosting?

I would find another host for this particular client rather than do a dog an
d
pony dance. I actually like to spend time with my family, so your mileage ma
y
vary. :-)
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Shawn Ferguson" wrote:
> I have a problem. My website hosting company can only host sql server dat
abase that are less than 100 mb. Usually that is not a problem for most of
my applications, however I have one client who has a database with over 2700
00 records which makes the database over 100MB. What can I do? I thought o
f purchasing another database from the host so I'll have 200MB then I can pr
ogramatically split the tables up based on ID, for instance if recordID < 15
0000 then move this data to sql server 1. If recordID > 150000 then move da
ta to sql server 2. Then when I want to query the system for a student, I w
ould first check the recordID for greater than or less than 150000 then quer
y the proper table. The data in the system is only used for retrieval (read
only), there are no updates to the system.
>
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>|||You could have a second db and then use a Partitioned View. That way you do
n't have to put any logic into the app at all.
--
Andrew J. Kelly SQL MVP
"Shawn Ferguson" <SFergus2@.cscc.edu> wrote in message news:uBn5eDuFGHA.216@.T
K2MSFTNGP15.phx.gbl...
I have a problem. My website hosting company can only host sql server datab
ase that are less than 100 mb. Usually that is not a problem for most of my
applications, however I have one client who has a database with over 270000
records which makes the database over 100MB. What can I do? I thought of
purchasing another database from the host so I'll have 200MB then I can prog
ramatically split the tables up based on ID, for instance if recordID < 1500
00 then move this data to sql server 1. If recordID > 150000 then move data
to sql server 2. Then when I want to query the system for a student, I wou
ld first check the recordID for greater than or less than 150000 then query
the proper table. The data in the system is only used for retrieval (read o
nly), there are no updates to the system.
Do you think this approach is incorrect or do you have any other suggestions
? Thanks!|||I use Webhost4life... really cheap...
www.webhost4life.com ( $10 Month 300 MB)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Andrew J. Kelly" wrote:

> You could have a second db and then use a Partitioned View. That way you
don't have to put any logic into the app at all.
> --
> Andrew J. Kelly SQL MVP
>
> "Shawn Ferguson" <SFergus2@.cscc.edu> wrote in message news:uBn5eDuFGHA.2
16@.TK2MSFTNGP15.phx.gbl...
> I have a problem. My website hosting company can only host sql server database
that are less than 100 mb. Usually that is not a problem for most of my application
s, however I have one client who has a database with over 270000 records which makes
th
e database over 100MB. What can I do? I thought of purchasing another data
base from the host so I'll have 200MB then I can programatically split the t
ables up based on ID, for instance if recordID < 150000 then move this data
to sql server 1. If record
ID > 150000 then move data to sql server 2. Then when I want to query the s
ystem for a student, I would first check the recordID for greater than or le
ss than 150000 then query the proper table. The data in the system is only
used for retrieval (read on
ly), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggest
ions? Thanks!
>
>|||If your constraint is definitely the 100MB limit, your approach is fine. The
method you used is called horizontal partitioning. As Andrew Kelly mentioned
in the other reply, you should use a view to do it. Goto SQL Server Books
Online and search for "partitioning data".
Dennis
"Shawn Ferguson" wrote:

> I have a problem. My website hosting company can only host sql server database th
at are less than 100 mb. Usually that is not a problem for most of my applications,
however I have one client who has a database with over 270000 records which makes t
he
database over 100MB. What can I do? I thought of purchasing another database from the hos
t so I'll have 200MB then I can programatically split the tables up based on ID, for instan
ce if recordID < 150000 then move this data to sql server 1. If recordID
> 150000 then move data to sql server 2. Then when I want to query the system for a
student, I would first check the recordID for greater than or less than 150000 then
query the proper table. The data in the system is only used for retrieval (read on
ly
), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>

Benefits of using SQL over XML

Hi,

I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.

The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.

The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.

We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .

Any advice would be gratefully recieved

Lee

It really depends on two things: The application and the version of SQL Server you are using. For certain input/retrieval methods, XML can actually be faster than using direct database calls. SQL Server 2005 has native XML features, which you can read more about here:

http://www.sqlsummit.com/People/MRys.htm

Buck

|||

The thing is that our website is taking considerably longer to return results using XML. Our software provider can provide a website which uses Oracle and an example website using test data seems to query and return the data back in far less time then ours using XML. But this site is a lot more costly option and does not provide all the functionalty we require. The main reason for the performance increase is that we want to be able to use the website internally for our branches and front office staff, so performance is key it will have about 20 - 30 users. We are planning to do this because we are unable to restrict access to parts of our back office system from the front office staff. The problem with the performance of the website currently means that the staff will have to deal with a sluggish system.

Our website designer has said that he would have to rewrite the website to change it from XML to SQL, would XQuery be a simpler solution. We are within reason happy to purchase whatever software is required to make this work.

|||

Again, it all depends on how the application is coded. Simply changing from XML to an RDBMS query doesn't guarentee that one will be faster than the other. In other words, you can code an application to be faster in either case.

If performance is key, then for large data sets a database platform might be the way to go. If you need to share data between multiple systems, then XML might be the way to go. It all depends on your needs, but in either case you'll want to evaluate your code to ensure that it is as optimal as possible for your situation.