Cleaning up server logins

I have a host of server logins, NT and SQL access level on my server. I'd
like to iterate through the databases and find users who have no logins to
non-system databases. Using master.dbo.sysdatabases, I can retrieve a list
of all databases on the server, but not the type (System or User), unless a
SID of 0x01 is an appropriate measure of a system database. After obtaining
a list of databases, how can I iterate through them with T-SQL code?Using cursors.
Example:
use northwind
go
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
print @.db
end
close databases_cursor
deallocate databases_cursor
go
"Kevin Bowker" wrote:

> I have a host of server logins, NT and SQL access level on my server. I'd
> like to iterate through the databases and find users who have no logins to
> non-system databases. Using master.dbo.sysdatabases, I can retrieve a lis
t
> of all databases on the server, but not the type (System or User), unless
a
> SID of 0x01 is an appropriate measure of a system database. After obtaini
ng
> a list of databases, how can I iterate through them with T-SQL code?

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
BlairIf there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
Blair
If there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Cleaning up goldmine Data

Hi,
We are running version 6.5 of Goldmine, which I am migrating to CRM
3.0 - I used access to get the data into SQL, but am now stuck with a
Last Name Column and a Name Column. To get the data into CRM Server
(which the boss is very keen to have done ASAP) I need to have First
Name and Last Name.
Is there an easy (!?) way to get the SQL server to remove the last
name data from name data? For example as it stands at the moment I
would have two cells:
Last Name: Smith
Name: John Smith
What I want to end up with is:
First Name: John
Last Name: Smith
Any suggestions gratefully received!
Many thanks
BlairIf there are only ever two names (one first name followed by one last name,
separated by a space or other distinct charactere) you can simply use
SUBSTRING and CHARINDEX (maybe even PATINDEX). Books Online has several
examples.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Classification crosstab query - 2000

Hello --

I think this is the term for what I want (something that could be generated
in ACCESS using a pivot table, or, maybe Yukon).

We have data for sales by sales people in sales regions. More than one
person sells in a region.

We want to display data as follows:

salesperson's names
---- ---- ---- ---- ----
- ----
region 1
region 2 row/column values are sales amounts for person in
that region
region 3

We will add a WHERE clause for the period of time covered.

I don't want to have to change the query if a new salesperson or new region
is added.

Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?

Can someone direct me to examples of how to do this?

Thanks for any direction.

Larry MehlHi

This previous post should help:
http://tinyurl.com/6rhsj

John

"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:96uyd.7239$9j5.4696@.newsread3.news.pas.earthl ink.net...
> Hello --
> I think this is the term for what I want (something that could be
> generated
> in ACCESS using a pivot table, or, maybe Yukon).
> We have data for sales by sales people in sales regions. More than one
> person sells in a region.
> We want to display data as follows:
> salesperson's names
> ---- ---- ---- ---- ----
> - ----
> region 1
> region 2 row/column values are sales amounts for person
> in
> that region
> region 3
> We will add a WHERE clause for the period of time covered.
> I don't want to have to change the query if a new salesperson or new
> region
> is added.
> Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?
> Can someone direct me to examples of how to do this?
> Thanks for any direction.
> Larry Mehl|||John --

Thanks for pointing me to this article.

It worked right out of the box.

Larry

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:41cace16$0$15616$afc38c87@.news.easynet.co.uk. ..
> Hi
> This previous post should help:
> http://tinyurl.com/6rhsj
> John
> "L Mehl" <mehl_nospam@.cyvest.com> wrote in message
> news:96uyd.7239$9j5.4696@.newsread3.news.pas.earthl ink.net...
> > Hello --
> > I think this is the term for what I want (something that could be
> > generated
> > in ACCESS using a pivot table, or, maybe Yukon).
> > We have data for sales by sales people in sales regions. More than one
> > person sells in a region.
> > We want to display data as follows:
> > salesperson's names
> ---- ---- ---- ---- ---
-
> > - ----
> > region 1
> > region 2 row/column values are sales amounts for person
> > in
> > that region
> > region 3
> > We will add a WHERE clause for the period of time covered.
> > I don't want to have to change the query if a new salesperson or new
> > region
> > is added.
> > Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?
> > Can someone direct me to examples of how to do this?
> > Thanks for any direction.
> > Larry Mehlsqlsql

Class not registered when opening AdomdConnection

I'm just getting started on Analysis Services.

I have installed AS on our SQL Server (2000), and I can access it with Analysis Manager and third-party tools. But when I try to open a connection with ADOMD.NET, I get a "class not registered" error message (error ID = -2147221164).

Here's my code:

AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=Servername;Initial Catalog=MyCatalog;ConnectTo=8.0";
conn.Open(); // exception is thrown here

I have installed Visual C# 2005 Express, MSXML 4.0 SP2, and ADOMD.NET (from the SQL Server 2005 Feature Pack).

Daniel

did you ever resolve this. I get the same issue?

thx

Avi

|||

ADOMD.NET (9.0) that comes with SQL2K5 cannot connect directly to AS2K. It delegates to PTS 8.0. What I think you are seeing is that it isn't available. Try installing PTSLITE.EXE from your SQL2K SP4 (AS distribution) kit (located in the \msolap\install\pts subfolder).

_-_-_ Dave

sqlsql

Class not registered when opening AdomdConnection

I'm just getting started on Analysis Services.

I have installed AS on our SQL Server (2000), and I can access it with Analysis Manager and third-party tools. But when I try to open a connection with ADOMD.NET, I get a "class not registered" error message (error ID = -2147221164).

Here's my code:

AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=Servername;Initial Catalog=MyCatalog;ConnectTo=8.0";
conn.Open(); // exception is thrown here

I have installed Visual C# 2005 Express, MSXML 4.0 SP2, and ADOMD.NET (from the SQL Server 2005 Feature Pack).

Daniel

did you ever resolve this. I get the same issue?

thx

Avi

|||

ADOMD.NET (9.0) that comes with SQL2K5 cannot connect directly to AS2K. It delegates to PTS 8.0. What I think you are seeing is that it isn't available. Try installing PTSLITE.EXE from your SQL2K SP4 (AS distribution) kit (located in the \msolap\install\pts subfolder).

_-_-_ Dave

CIRUCLAR REFERENCE Microsoft SQL Server 2000 Access ON DELETE UPDATE CASCADE

I need some Microsoft Access / SQL Server 2000 help. I have a SQL
Server 2000 database that is using Access as a front end (adp file).
Since SQL server does not allow cycle cascading or circular reference,
I am having problems.
For example, suppose I have tableA, tableB, tableC, and tableD. tableB
and tableC each have a foreign key (tableAID) which is tableA's primary
key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
tableB's primary key and one is tableC's primary Key. The problem is
that I can not have ON UPDATE/DELETE CASCADE because it is
circular/cyclic referential integrity. (I get an error when I try to
set both tableB and tableC to ON DELETE CASCADE... I can only set it
for one of them) What can I do to still have the behavior of ON
UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
but I don't know how to program a trigger. My objective is that when
I delete (or update) a row in tableA, the corresponding rows in tableB,
tableC, and tableD will be deleted/updated. Please help!The best way would be to not depend on cascading updates. Rather, you should
be
using stored procedures and *explicitly* delete referenced data as part of a
conscious design. However, I'd imagine you have your frontend linked directl
y to
the tables in the backend. This, among other reasons, is why this is a weak
design.
Thomas
<mneufeld@.gmail.com> wrote in message
news:1121874815.358446.298010@.f14g2000cwb.googlegroups.com...
>I need some Microsoft Access / SQL Server 2000 help. I have a SQL
> Server 2000 database that is using Access as a front end (adp file).
> Since SQL server does not allow cycle cascading or circular reference,
> I am having problems.
> For example, suppose I have tableA, tableB, tableC, and tableD. tableB
> and tableC each have a foreign key (tableAID) which is tableA's primary
> key. tableD has 2 foreign keys (tableBID and tableCID), one of which is
> tableB's primary key and one is tableC's primary Key. The problem is
> that I can not have ON UPDATE/DELETE CASCADE because it is
> circular/cyclic referential integrity. (I get an error when I try to
> set both tableB and tableC to ON DELETE CASCADE... I can only set it
> for one of them) What can I do to still have the behavior of ON
> UPDATE/DELETE CASCADE? I think there is a way to do it with triggers
> but I don't know how to program a trigger. My objective is that when
> I delete (or update) a row in tableA, the corresponding rows in tableB,
> tableC, and tableD will be deleted/updated. Please help!
>|||Why is this a weak design? Please Explain.
Is there anything wrong with depending on cascading deletes and
updates?
Also, how would I create a stored procedure or a trigger to
automatically delete the corresponding rows in tableB tableC and tableD
when I delete a row in tableA?|||>> Since SQL server does not allow cycle cascading or circular reference, I
am having problems. <<
Other SQLs do not have this problem, so you might want to switch to one
of them. Otherwise, you can use triggers (remember to comment it as
kludge) or write a stored procedure to do the job.
Don't do narratives; do DDL or at least psuedo-code. Is this what you
meant?
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Beta
(beta_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Gamma
(gamma_id INTEGER NOT NULL PRIMARY KEY,
alpha_id INTEGER NOT NULL
REFERENCES Alpha(alpha_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE Delta
(delta_id INTEGER NOT NULL PRIMARY KEY,
beta_id INTEGER NOT NULL
REFERENCES Beta(beta_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
gamma_id INTEGER NOT NULL
REFERENCES Gamma(gamma_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
There is two paths from Alpha to Delta (A -> B -> D) and( A -> C -> D).
SQL Server sees this and chokes.
One ugly trick is to create a BetaGamma table with a compound key so
there is only one path (A ->. BC ->. D). Another trick is split Delta
so you have (A -> B -> D1) and( A -> C -> D2). But we'd need more
information to do one of these.
One of the advantages of DRI is that it gives information to the
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc.|||Thanks Celko for replying. It is actually funny because I was looking
at other threads that you replied to and I was thinking, "Wow, it looks
like Celko knows his stuff and I wish he would reply to my thread."
Then low and behold I checked my thread and you replied! I have a few
questions though.
I'm new to groups and I don't know what DDL is?
Yes
kludge) or write a stored procedure to do the job. <<
I have never written a trigger and I do not know how to. Can you please
explain.
optimizer. You know that a join on a PK-FK will always work and that
an EXISTS () is true or not on sight. You know how the stats will
change, etc. <<
I don't understand this entire paragraph. I'm new to groups and to
databases so can you please explain it to me.
Thanx.|||>> I'm new to groups and I don't know what DDL is? <<
SQL has three sub-languages: DDL (Data Declaration Language), DML (Data
Manipulation Language) and DCL (Data Control Language). DDL is the
CREATE, ALTER and DROP stuff.
For that, you need a book not a newsgroup. A trigger is a kind of
stored procedure that is attachd to a table and it gets "fired"
(invoked) when a database event occurs (update, delete, insert). Three
are some special things about triggers which involve the delted and
inserted rows in the T_SQL model and there are some limits as to what
you can do in a Trigger.
Oh Boy! This way outside a newsgroup! You tell SQL *what" you want
and the optimzer figures out *how* to find it. The optimizer uses
statistics, constraints, and DRI to pick the right access methods.
PK-FK can pass on some ifno. For example, can you look at this
predicate and tell me if it is TRUE or FALSE?
EXISTS
(SELECT *
FROM Delta
WHERE Delta.beta_id = Beta.beta_id).
Likewise if I do a JOIN on (Delta.beta_id = Beta.beta_id), I can pull
up stats to see the statistical distribution, number of disticnt
beta_id values in each table, etc.|||Celko,
Thanx for explaining heres the DDL:
CREATE TABLE tbServer
(lngServerID INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE tbDatabase
(lngDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID )
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplication
(lngApplicationID INTEGER NOT NULL PRIMARY KEY,
lngServerID INTEGER NOT NULL
REFERENCES tbServer(lngServerID)
ON DELETE CASCADE
ON UPDATE CASCADE,
);
CREATE TABLE tbApplicationToDatabase
(lngApplicationToDatabaseID INTEGER NOT NULL PRIMARY KEY,
lngApplicationID INTEGER NOT NULL
REFERENCES tbApplication(lngApplicationID)
ON DELETE CASCADE
ON UPDATE CASCADE,
lngDatabaseID INTEGER NOT NULL
REFERENCES tbDatabase(lngDatabaseID)
ON DELETE CASCADE
ON UPDATE CASCADE,
.);
First of all keep in mind that this is a sample DDL, not the real one.
OK so basically this is keeping track of the server's application and
databases and sometimes an Application connects to a DB for storage
etc.
I have database(s) that are running on server(s) that connect to
application(s) which may be running on different server(s). So when I
delete a server record I want all the applications and databases
running on this particular server deleted AND any connections deleted
(since the DB or App does not exist anymore). How can I do this. If the
only way is to do it with triggers then please show me the code for it.
Thanx a lot!|||
--CELKO-- wrote:
> Other SQLs do not have this problem, so you might want to switch to one
> of them.
Yep, even ACCESS can do this correctly! They've fixed it for SQL Server
2005, I understand.|||It is a weak design because it lacks abstraction and thereby limits your abi
lity
to make design changes. By linking directly to the source tables, you have n
o
ability to effective business rules in the backend without using something l
ike
triggers. Your example is quite illustrative of this design's short comings.
You
want to effect a business rule (delete X when I delete Y). This would be a s
nap
if you were doing all of your deletes through stored procedures which shield
the
UI to some degree from the backend structure. However, since there is no
abstraction between the UI and the backend database, you are left with a han
dful
of ugly choices (e.g. triggers, or UI logic).
A good indication of a weak design is one that prevents you from easily
effecting a simple change.
Thomas
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>|||> Is there anything wrong with depending on cascading deletes and
> updates?
YES!!!!! Cascading referential actions are problematic for a number of
reasons, but the main one involves locks, concurrency, and deadlocks.
Cascading updates and deletes complicate deadlock resolution because the
order in which locks are obtained for a cascading referential action is
undefined. From a programmer's perspective, all locks in the cascade chain
are obtained at the same time. While the problem is more prevalent with
cascading updates, the same principles apply to a cascading delete. The way
to minimize deadlocks is to always obtain locks in the same order in every
transaction. Cascading referential actions limit the control you as a
programmer have in specifying that order. I never use them. I use
surrogate keys so I don't have to worry about cascading updates, and I
explicitly code deletes either in a trigger, or in a stored procedure. That
way I have complete control over the order in which resources are locked,
and I only have to worry about deadlocks caused by a malformed execution
plan. In those very rare cases, I can manipulate the execution plan by
specifying optimizer hints.
CREATE TRIGGER tIOD_tableA ON tableA INSTEAD OF DELETE AS
BEGIN
DELETE tableD
FROM
(SELECT keyD FROM deleted JOIN tableB ON (tableB.keyA =
deleted.keyA)
UNION SELECT keyD FROM deleted JOIN tableC ON (tableC.keyA =
deleted.keyA))) allD
WHERE tableD.keyD = allD.keyD
DELETE tableB FROM deleted WHERE tableB.keyA = deleted.keyA
DELETE tableC FROM deleted WHERE tableC.keyA = deleted.keyA
DELETE tableA FROM deleted WHERE tableA.keyA = deleted.keyA
END
GO
"neuf38" <mneufeld@.gmail.com> wrote in message
news:1121878593.785146.3490@.g44g2000cwa.googlegroups.com...
> Why is this a weak design? Please Explain.
> Is there anything wrong with depending on cascading deletes and
> updates?
> Also, how would I create a stored procedure or a trigger to
> automatically delete the corresponding rows in tableB tableC and tableD
> when I delete a row in tableA?
>

Circle Chart showing percent values automatically

Hi. I have used Circle-Charts in Access Reports. They were able to show
percent values by default, without having the percent values calculated or
delivered by SQL queries.
I am trying to get RSrv doing this also, but no success. Is it possible? Or
do I have to deliver the percent values to the diagram?
I actually am using RSrv without any SP.
Thanks, MarkusYou have to calculate the percentage value. The default data point label for
pie charts would be the absolute value rather than the percentage.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:389ADD61-DBAD-43C4-8576-8D31D819534C@.microsoft.com...
> Hi. I have used Circle-Charts in Access Reports. They were able to show
> percent values by default, without having the percent values calculated or
> delivered by SQL queries.
> I am trying to get RSrv doing this also, but no success. Is it possible?
> Or
> do I have to deliver the percent values to the diagram?
> I actually am using RSrv without any SP.
> Thanks, Markussqlsql

Chosing the best Data Access component for SQL Server Express

Hi all,

I have to chose the best Data Access Component with SQL Server Express edition.

The application that I am going to develope is a simple one but the speed should be as much as possible.

Any help would be highly appreciated.

Thanks & regards

Arun Gupta

The 'best' speed would most likely be attained using the SQL native client and a datareader object.

Choosing decimal symbol (comma, please! :))

Hello!

I'm having a slight problem figuring out how to make a field in an Access ADP accept input using comma as decimal symbol.

The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv). Both machines have regional settings pointing to Denmark and thus decimal symbol is ','.

When entering data through the interface made as forms in the ADP, I'm forced to enter '.' as decimal symbol for fields defined as DECIMAL(12,2) (for instance). ...This is actually a situation I could live with, if it weren't for the problems I face when trying to base calculus on these fields (doing some input checking).

Instead of resorting to making tedious code to handle the situation I know turn to you guys for help!

Regards ...and THANKS in advance! :)
Ult

NB: As an appetizer I can tell that if I multiply 1.00 with 1.00 I get 10000.00! ...So the problem is quite annoying, if you're not into opening a can of coding-with-a-distinct-feeling-of-this-is-not-quite-the-best-solution! :)http://www.dbforums.com/forumdisplay.php?forumid=84 .. post your question here|||Thanx for replying, Enigma!

I admit the problem has a touch of both worlds (Access/MSSQL) but believe/hope the problem has to do with MS SQL - all my 'pure' Access applications have none of the described problems.

I have of course not found any solution in the Access part of this forum.

U
...Still open for bright ideas! :)|||Btw, if anybody has a way to make insert-statements via Analyser accept decimals written with ',' instead of '.' it might just be part of the solution, I'm looking for!

...Or if anybody could 'kill this idea' by confirming it's not possible (at least without using 'format'-functions...)?|||The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv).

Oops .. I missed that (Did not read the entire post :( ) !!!! Let me think about it then !!

Choosing database between MSDE and Access

hi,

I need to choose a database based on the following criteria (using .NET app):
1) a light but fully functional database, preferably with the support of store proc and constraints, less than 8000 transaction a day.
2) portable or the database can be export/import very easily
3) reliable and stable
4) least maintenance

I have two db in my mind, Access and MSDE?
Does anyone have some hand-ons experience on the above two? Or any other better suggestions?

Any advice is appreciated.

thanks,
bryanUsing MSDE will generally be more trouble-free. A file-based database on a Web server is not ideal.

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:
> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>> We wish to look into setting up a mirrored server at another site in case
>> we
>> lose access to our head office.
>> The company is based in London, fairly near potential bomb targets etc.
>> There are three other locations, one having a 4GB leased line to head
>> office.
>> Assuming we are not going to jump to Yukon in the short term it looks
>> like
>> log shipping is probably the way to go but are there other alternatives?
>> Note that currently the system is not set up well for disaster recovery.
>> Simple recovery model with over night backups. No experience here of
>> running
>> proper log backups on recovery mode "full". There's probably some "select
>> into" in the code plus we do some pretty big index rebuilds over night.
>> Database is currently 80GB across raid 10 arrays of 18Gb disks.
>> Thanks
>> Paul Cahill
>>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case
we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of runni
ng
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul Cahill
Hi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.
|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegr oups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>
|||Hi,
I think you can go for Transaction replication.
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>
|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:

chng default loca where new DBs created?

Hello,
My workplace is upgrading Microsoft Access from 97 to 2002
which has the Access Data Project file that can create an
addhoc Sql Server DB on the Sql Server computer on the fly
from remote workstations. The wizard in the Access
2002 .adp file does not offer a location option where the
new Sql DB gets created on the Sql Server computer. The
problem is that these new DBs are getting created on the
server computer at a default location of
C:\Program Files\Microsoft Sql Server\MSSQL\Data
The problem is that the C drive on this server is only 12
gigs and will fill up quickly. The main MDFs reside on
the other drive of the server which has hundreds of gigs
of space. Does EM 2000 have a way to change the default
location where new DBs get created? Or is there a
registry setting I could change on the server?
Thanks,
RonIf you want to see it in EM, look at properties for the server, and click
the database tab. The defaults are in there.
Simon Worth
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:745701c5266b$86508ff0$a601280a@.phx.gbl...
> Hello,
> My workplace is upgrading Microsoft Access from 97 to 2002
> which has the Access Data Project file that can create an
> addhoc Sql Server DB on the Sql Server computer on the fly
> from remote workstations. The wizard in the Access
> 2002 .adp file does not offer a location option where the
> new Sql DB gets created on the Sql Server computer. The
> problem is that these new DBs are getting created on the
> server computer at a default location of
> C:\Program Files\Microsoft Sql Server\MSSQL\Data
> The problem is that the C drive on this server is only 12
> gigs and will fill up quickly. The main MDFs reside on
> the other drive of the server which has hundreds of gigs
> of space. Does EM 2000 have a way to change the default
> location where new DBs get created? Or is there a
> registry setting I could change on the server?
> Thanks,
> Ron|||Ron,
In EM, open server properties windows and go to "Databse Settings" tab, at
the botton you can set "New database default location" for data and log.
AMB
"Ron" wrote:

> Hello,
> My workplace is upgrading Microsoft Access from 97 to 2002
> which has the Access Data Project file that can create an
> addhoc Sql Server DB on the Sql Server computer on the fly
> from remote workstations. The wizard in the Access
> 2002 .adp file does not offer a location option where the
> new Sql DB gets created on the Sql Server computer. The
> problem is that these new DBs are getting created on the
> server computer at a default location of
> C:\Program Files\Microsoft Sql Server\MSSQL\Data
> The problem is that the C drive on this server is only 12
> gigs and will fill up quickly. The main MDFs reside on
> the other drive of the server which has hundreds of gigs
> of space. Does EM 2000 have a way to change the default
> location where new DBs get created? Or is there a
> registry setting I could change on the server?
> Thanks,
> Ron
>

chng default loca where new DBs created?

Well, I changed the default location as advised (to F:
drive). I also restarted Sql Server. But the Access ADP
is still creating the Sql DB's on the C drive. Do I need
to reboot the server computer for the default location
change to take effect?

>--Original Message--
>Thank you all very much. I completely missed that.
>
>2002
an
>fly
the
12
>.
>Perhaps Access doesn't respect SQL Server's default setting?
Try below from Query Analyzer or OSQL.EXE:
CREATE DATABASE filepathtest
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:7b4d01c52677$685a76d0$a401280a@.phx.gbl...
> Well, I changed the default location as advised (to F:
> drive). I also restarted Sql Server. But the Access ADP
> is still creating the Sql DB's on the C drive. Do I need
> to reboot the server computer for the default location
> change to take effect?
>
> an
> the
> 12|||>Perhaps Access doesn't respect SQL Server's default
>setting?

>Try below from Query Analyzer or OSQL.EXE:

>CREATE DATABASE filepathtest
Good idea. Well,l filepathtest went to the correct drive,
F instead of C. But when I create a new Access.adp either
on the same machine as Sql Server or from a remote
workstation, the new DB still ends up on the C drive. I
think Access does not respect Sql Server's default
settings.
Is there something that I could override in the .adp DDL
maybe? It would be a real nightmare if I had to manually
create a bunch of new adhoc DB's for Access2002 users
(since I would have to restrict them from doing it from
their .adp's).

>--Original Message--
>Perhaps Access doesn't respect SQL Server's default
setting?
>Try below from Query Analyzer or OSQL.EXE:
>CREATE DATABASE filepathtest
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>http://www.sqlug.se/
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:7b4d01c52677$685a76d0$a401280a@.phx.gbl...
need
The
the
gigs
default
>
>.
>|||The default database creation location set under Enterprise Manager is
saved in the local computer registry and ONLY applies to databases
created via EM on that computer.
If you create a database without using EM and without specifying a
location, the location will be the same as either the master or model
database files (I do not remember which db controls)
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||> Is there something that I could override in the .adp DDL
> maybe?
I don't know, don't do Access... :-)
I'd ask this in an Access group, perhaps they have some hints about some set
ting inside Access.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:7bb901c52680$7266b450$a401280a@.phx.gbl...
>
>
> Good idea. Well,l filepathtest went to the correct drive,
> F instead of C. But when I create a new Access.adp either
> on the same machine as Sql Server or from a remote
> workstation, the new DB still ends up on the C drive. I
> think Access does not respect Sql Server's default
> settings.
> Is there something that I could override in the .adp DDL
> maybe? It would be a real nightmare if I had to manually
> create a bunch of new adhoc DB's for Access2002 users
> (since I would have to restrict them from doing it from
> their .adp's).
>
>
> setting?
> message
> need
> The
> the
> gigs
> default

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) on
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-b
Why not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) o
n
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-bWhy not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>

Checking if user has a login

Greetings -
I'm working with a SQL Server 2000 database via an Access front-end and I'd
like to check if the user has a login (via a pass through query I imagine) o
n
the database before attempting to pass the connection string. The user
login name will be the same as their PC login name which I can pick up. Any
assistance is appreciated.
Thanks,
-bWhy not just try to login? If the user doesn't have a login, your login
attempt will fail and that will tell you all you want to know. You just have
to catch the error in the Access front-end.
It wouldn't be very secure if you could find out information about login
credentials without actually being logged in.
Jacco Schalkwijk
SQL Server MVP
"Robert Pfister" <rpfister@.hotmail.com> wrote in message
news:bYOdnSXvu-J5v2vcRVn-2w@.giganews.com...
> Greetings -
> I'm working with a SQL Server 2000 database via an Access front-end and
> I'd
> like to check if the user has a login (via a pass through query I imagine)
> on
> the database before attempting to pass the connection string. The user
> login name will be the same as their PC login name which I can pick up.
> Any
> assistance is appreciated.
> Thanks,
> -b
>