Mapping one table to another in diffirent databases

I want to map a table1 in one database to a table2 in another database. That way I can populate the table2 with the information that table1 has. How would you go about doing this. Very new at this and need help! Thanks alot .Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?|||Originally posted by Brett Kaiser
Do you mean that if an INSERT, UPDATE or DELETE Occurs you wan the action reflected in that table?

yeah, whenever something getts updated in one table in the database, the results will also reflect on the other table that lies it the other database. I am under the impression that yu have to map the databases together for that to happen. Is this something that is done with DTS? hopefully that makes a little more sence.|||Sounds like a job for triggers.|||Transactional replication can also be an answer.

mapping

hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?

thanks

Hi,

you can create a Linked server with Oracle. and query the oracle table as local tables. or you can use OpenRowset to query oracle database

|||

ahm, I'm just new at using oracle and i'm a little bit confused.. could you please explain a little more?

thanks!

|||If you create a linked server for you Oracle Server in SQL Server (See the Books online for SQL Server for detailed information) you can access the tables of the Oracle instance using the four part notation of SQL Server:

SELECT * FROM OracleLinkedServerName..Schema.ObjectName

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

However, I found that in order to get it to work, I needed to use brackets around each element such as the following: SELECT * FROM [LINKEDSERVERNAME]..[DATABASENAME].[TABLENAME]. I wouldn't have found it out had I not used the "Script table as..." command by right-clicking the table in the Object Explorer! This wasn't mentioned in BOL.

Brian J. Matuschak

|||

Actually there is no need to use the brackets unless you have special characters in the names.

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

Mapped Drive

Can I make my "default location" for new databases be a mapped drive? When
I go into the properties and select the database tab and select the ...
box to list my drives, the shared drive does not show up. If I go ahead
and enter the path of where I want the files to be located on the "mapped"
drive, it accepts it, but does not use it when I create a new database.
My situation is I have 2 computers in a single room and I want then to be
pointed at the same file location for all databases... Both machines have
MSDE version of sql server loaded...Can I somehow point one machine to the
second machines SQL server such that we are working on the same database?
Any ideas/suggestions'> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
I don't think you can share databases between engines (even between
instances on the same machine).
Why not just point one machine's client tools to the other machine's SQL
Server? Then one machine will be working locally, and the other will be
working "remotely" so to speak. But they will be working on the same copy
of exactly one database.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||No.
First, SQL does not support mapped drives for database or transaction log
files.
Second, when SQL does open a database file, it is completely exclusive for
that server instance. Even a second instance on the same host nod could not
access the same underlying database files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns9495971974DBBJHeaveyBDUP@.207.46.248.16...
> Can I make my "default location" for new databases be a mapped drive?
When
> I go into the properties and select the database tab and select the ...
> box to list my drives, the shared drive does not show up. If I go ahead
> and enter the path of where I want the files to be located on the "mapped"
> drive, it accepts it, but does not use it when I create a new database.
> My situation is I have 2 computers in a single room and I want then to be
> pointed at the same file location for all databases... Both machines
have
> MSDE version of sql server loaded...Can I somehow point one machine to
the
> second machines SQL server such that we are working on the same database?
> Any ideas/suggestions'

Maping loging to users databases in SQL2000

I defined some jobs to move production database in analyst database on other
server. When jobs restore the database on analisys server, the login to the
user are not preserve. I've run an DTS to copy login from production server
to analisys server. Have anybody some clues about this problem. Thank you!Hi,
it is always better to make a script of all user logins created to restore
it over another server , BTW have you include users into that DTS package to
move !
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"tudor" wrote:

> I defined some jobs to move production database in analyst database on oth
er
> server. When jobs restore the database on analisys server, the login to th
e
> user are not preserve. I've run an DTS to copy login from production serve
r
> to analisys server. Have anybody some clues about this problem. Thank you!
>
>|||Hi,
one more thing make me curious that why you make DTS to move DATABASE , is
backup /restore , attach / detach not working, how ever here is a article
FYI :
http://vyaskn.tripod.com/moving_sql_server.htm
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"tudor" wrote:

> I defined some jobs to move production database in analyst database on oth
er
> server. When jobs restore the database on analisys server, the login to th
e
> user are not preserve. I've run an DTS to copy login from production serve
r
> to analisys server. Have anybody some clues about this problem. Thank you!
>
>|||Hi,
here is one more link for your joy , it will list associated role for users:
http://www.sql-server-performance.c...?TOPIC_ID=10504
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"tudor" wrote:

> I defined some jobs to move production database in analyst database on oth
er
> server. When jobs restore the database on analisys server, the login to th
e
> user are not preserve. I've run an DTS to copy login from production serve
r
> to analisys server. Have anybody some clues about this problem. Thank you!
>
>

Many to Many replication in sql server 2000

Hi

I'm trying to determine if it is possible to do many to many replication in sql server 2000.

What i basically want is to have n databases share the same basedate (share a common database) and allow updates in any database to be replicated to all the other databases (with a simple conflict resolution, like last update wins).

My goal is total autonomy, without a single point of failure. If any node goes down, the other nodes will continue to work and continue to replicate their data to the remaining nodes. When a node comes back up it will catch up with the over nodes (or get reinitialized if it was a serious crash).

The amount of data i want to replicate is not that big (less than 100MB) and does not change that often. All servers are sql server 2000 instances connected by a gigabit network and the number of nodes involved is less than 10. Some latency is also acceptable.

the question is: is this at all possible? I have read i bit in 'SQL Server High Availability By Paul Bertucci' and some other resources and it looks like a multiple publishers or multiple subscribers with merge replication setup should work, but i'm not too sure if it will work for n > 2 nodes (where all nodes publish and subscribe to each other) and it also mentions constraints on which data a given node is allowed to update (i hope this could be handled by simple conflict resolution).

And if it is not possible in 2000, could it be accomplished en 2005?

TIA Jens

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

|||

Martin_McNally wrote:

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

This was what i thought, but in the book i mention there is examples of a Multiple Publishers/Multiple Subscribers setups, where each node is both subscriber, publisher and distributer, to quote from the book (full book and figures available here):

Microsoft? SQL Server High Availability By Paul Bertucci wrote:

In the multiple publishers or multiple subscribers scenario, as shown in Figure 7.15, a common table (such as the Customers table) is maintained on every server participating in the scenario. Each server publishes a particular set of rows that pertain to it—usually via filtering on something that identifies that site to the data rows it owns—and subscribes to the rows that all the other servers are publishing. The result is that each server has all the data at all times, and can make changes to its data only. You must be careful when implementing this scenario to ensure that all sites remain synchronized. The most frequently used applications of this configuration are regional order processing systems and reservation tracking systems. When setting up this configuration, make sure that only local users update local data. This check can be implemented through the use of stored procedures, restrictive views, or a check constraint.

But i guess setting up multiple servers as both subscriber, publisher and distributer and letting them all subscribe to each other will cause havoc without the above mentioned constraints.

But i guess i'm not the first person to want this kind of setup and if it was possible i would have found some references somewhere on the net (i just did another search and found this article which describes what i'm looking for, but it is only for 2005 and when looking further into it, it does not support conflict resolution, which i think is required, guess we will have to build it ourselves if we really want it).

Best regards Jens

Many databases vs. 1 database

Hi,

I am trying to determine what the overhead is per database in SQL
Server 2000 Standard. I have the option to put several customers in one
database, or give each customer their own database. I would like to put
each customer in their own database to simplify maintenance and
strengthen security.

I have found the following document which shows the memory used by
various objects in SQL Server:

http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp

Based on this info I get the following *additional* memory requirements
per database:

Open Database (1 file, 1 filegroup): 6k
Open Objects (250 objects, 30 indexes): 692k
Total: 698k

Is this an accurate calculation of the overhead? Is there something
else that would affect the overhead that I am overlooking? Are there
any other downsides to having many databases versus a few databases?

Thanks,
MikeHi

Having 100's of databases does slow EM down as it need to list them all.
That is about it.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<mike@.rumblegroup.com> wrote in message
news:1112124274.309091.156730@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.
> I have found the following document which shows the memory used by
> various objects in SQL Server:
>
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp
> Based on this info I get the following *additional* memory requirements
> per database:
> Open Database (1 file, 1 filegroup): 6k
> Open Objects (250 objects, 30 indexes): 692k
> Total: 698k
> Is this an accurate calculation of the overhead? Is there something
> else that would affect the overhead that I am overlooking? Are there
> any other downsides to having many databases versus a few databases?
> Thanks,
> Mike|||You said that you want to have many databases to simplify maintenance.
I'm not sure that I understand that. Especially with regards to change
control. With multiple databases you run the risk of one or more
databases becoming out of sync, either intentionally or
unintentionally. This can turn into a real headache if you aren't very
careful.

Also, will you ever want to research information on your customers as a
whole? You didn't include any information as far as what these
databases actually hold (this would have been useful to know), but
assuming that they hold sales data as an example... if you wanted to
find your total sales across all customers then you would have to
select across many databases. If you got a new customer you would now
have to change any queries that select across these databases to
include the new database.

Good luck,
-Tom.|||(mike@.rumblegroup.com) writes:
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.

Putting all customers in the same database may be a good idea if
the customers does not access the data themselves.

But since you say "security", I assume that the customers will access
the databases.

One can handle security for customers in a shared database, so that
they only see their own data, but:

o If there is a slip somewhere, a customer can by mistake get access
to someone else's data.
o Even if correctly implemented, "Row-level security" is not waterproof,
since the views that typically implement such scheme can be provoked
to leak information.

And if the overhead of many databases are your only concern, there is
no reason for doubt. As Mike said, the overhead is negligible. You
will have to automate backups and all that, but that is not a major
issue.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> You said that you want to have many databases to simplify maintenance.
> I'm not sure that I understand that. Especially with regards to change
> control. With multiple databases you run the risk of one or more
> databases becoming out of sync, either intentionally or
> unintentionally. This can turn into a real headache if you aren't very
> careful.

Version control and a automated way of propagating changes.

Note also that this cuts both ways. If you have one single database and
you want to upgrade from version 1.2 to 1.3 and big-whiz says no? Or what
of big-whiz wants special features that are useless to most other
customers? With one big database, how do you beta-test? And what if
you find that the server does not cut it anymore, and you want to
scale out? Move a bunch to another server, easy as a piece of cake
with multiple databases. The monolith is more difficult to deal with.

> Also, will you ever want to research information on your customers as a
> whole? You didn't include any information as far as what these
> databases actually hold (this would have been useful to know), but
> assuming that they hold sales data as an example... if you wanted to
> find your total sales across all customers then you would have to
> select across many databases. If you got a new customer you would now
> have to change any queries that select across these databases to
> include the new database.

Views with a whole bunch of unions can easily be build dynamically on
demand.

But I the most decisive factor in this question is security. A multi-
customer database is more or less destined to leak data among customers.
Whether this is acceptable or completely unacceptable could be different
from business to business case.

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

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

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate database
s
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times!
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2G
B
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:

> Hi there. We currently have an application which requires separate databa
ses
> for each "project" we work on. We are in the process of replacing our 2 S
QL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previousl
y,
> we implemented with 2 servers due to performance reasons. That was back o
n
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times!
> So, do I get 2 servers or a single (larger in processor and memory) server
?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
[vbcol=seagreen]
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users wi
th
> an acceptable level of performance. The hardware is a 4-way server with 6
GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via
2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in su
ch
> an implementation. If you can buy a decent server with 6GB or so of RAM a
nd
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this i
s
> just my "professional" opinion based on the limited information you gave u
s.
> Bryan
>
> "John J. Berlo" wrote:
>

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate databases
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times! :)
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databases
> for each "project" we work on. We are in the process of replacing our 2 SQL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> we implemented with 2 servers due to performance reasons. That was back on
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times! :)
> So, do I get 2 servers or a single (larger in processor and memory) server?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users with
> an acceptable level of performance. The hardware is a 4-way server with 6GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in such
> an implementation. If you can buy a decent server with 6GB or so of RAM and
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this is
> just my "professional" opinion based on the limited information you gave us.
> Bryan
>
> "John J. Berlo" wrote:
> > Hi there. We currently have an application which requires separate databases
> > for each "project" we work on. We are in the process of replacing our 2 SQL
> > 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> > we implemented with 2 servers due to performance reasons. That was back on
> > SQL 7 though.
> >
> > We have approximately 400 databases which range in size from .5GB to about
> > 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> > option at this time (gotta love software companies that don't keep up with
> > the times! :)
> >
> > So, do I get 2 servers or a single (larger in processor and memory) server?
> >
> > --John J. Berlo
> > Lend Lease Corportation
> > Collaboration Technologies

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.
Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our databases.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correctly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>
|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:

> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report file?
> Did you define an output file for the jobstep/Report file for the Maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our databases.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correctly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:
> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance Plan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report file?
> Did you define an output file for the jobstep/Report file for the Maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
> >I am not able to perform automatic scheduled backups of one of our databases.
> >
> > This database is called StarTeam_stardraw60_db and is located within
> > VSDEV\STARTEAM instance of MS SQL server.
> > I also tried to perform an automatic backup of another database within
> > VSDEV\STARTEAM instance and no luck.
> >
> > However, if I try to perform an automatic scheduled backup of any database
> > that's located under a different instance of MS SQL Server, for example,
> > ProjectServer database under local instance, then everything works correctly.
> >
> > I'm not clear on why one works correctly, while the other doesn't.
> >
> > Thanks.
> >
>

Manual backup works, automatic scheduled doesn't

I am not able to perform automatic scheduled backups of one of our databases
.
This database is called StarTeam_stardraw60_db and is located within
VSDEV\STARTEAM instance of MS SQL server.
I also tried to perform an automatic backup of another database within
VSDEV\STARTEAM instance and no luck.
However, if I try to perform an automatic scheduled backup of any database
that's located under a different instance of MS SQL Server, for example,
ProjectServer database under local instance, then everything works correctly
.
I'm not clear on why one works correctly, while the other doesn't.
Thanks.Is Agent started for that instance?
How do you define the backup? TSQL job step in an Agent job? Maintenance Pla
n?
What version of SQL Server?
Is there any error messages in the Agent/Maint plan output file/Report file?
Did you define an output file for the jobstep/Report file for the Maint plan
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>I am not able to perform automatic scheduled backups of one of our database
s.
> This database is called StarTeam_stardraw60_db and is located within
> VSDEV\STARTEAM instance of MS SQL server.
> I also tried to perform an automatic backup of another database within
> VSDEV\STARTEAM instance and no luck.
> However, if I try to perform an automatic scheduled backup of any database
> that's located under a different instance of MS SQL Server, for example,
> ProjectServer database under local instance, then everything works correct
ly.
> I'm not clear on why one works correctly, while the other doesn't.
> Thanks.
>|||Thank you - it was the Agent not started.
"Tibor Karaszi" wrote:

> Is Agent started for that instance?
> How do you define the backup? TSQL job step in an Agent job? Maintenance P
lan?
> What version of SQL Server?
> Is there any error messages in the Agent/Maint plan output file/Report fil
e?
> Did you define an output file for the jobstep/Report file for the Maint pl
an?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sarah Baker" <Sarah Baker@.discussions.microsoft.com> wrote in message
> news:BDDB1DB0-C1AD-4850-BB0E-BA7C16BB229B@.microsoft.com...
>

Manipulating SQL server databases dynamically

Hi friends,

I have problem in sending my T-SQL statements, which i generate dynamically with the help of "user entered attributes", to SQl Server.

I need my T-SQL statements to get passed to the SQl Server ,which i enter in a "Rich text Box " in my appication that i develop using Vc# .net 2005,when i click a button which i placed in my "winform".And the queries Should be executed exectly as it is, and i should get the output back in my Winform...

But,Make sure that the queries are being obtained from a rich text box placed in the Winform...

So, please assist me regarding this issue...

R.Rajaraman

Hi,

Here is an example:

string query = txt.Text;

string connStr = "";//your connection string..

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);

System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = query;

cmd.CommandType = CommandType.Text;

System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

//continue..

Another option is to use SqlDataAdapter that will fill a dataset. If you need help with that let me know.

Regards,

|||

hi

i found your answer very usefull .....

But ,i need the queries directly to be passed from my rich text box control that i use in my winform....

please help me with this issue...

|||

Hi,

Perhaps I don't undestand what you mean. What do you mean directly?

If I got it right it is when you click the button that the query should get executed.

If so you should add the sample i sumbitted to the onclick event.

Regards,

|||

hi,

Thanks for the help guy... actually what i want to be done is as follows..

"I am going to enter some T-SQL statements in my Rich text Box that i placed in my form.And, i have a button named "Execute" in my win form. If I click on that button, the entire set of statements placed in the rich text box should get passed to SQL Server Query analyzer and my query batch should get executed.And ,i have to get back the results in my winform itself back..."

In Short, i need the functionality of a "Query Analyzer"..

Could you please help me with this issue......

|||

Hi,

First let me say that way you execute a query in the query analyzer you get for each statement (select/insert and ect.) a table. You can look at it as dataSet.

So, you need to fill a dataset with the query result and display it. In order to do that you first need to use the SqlDataAdapter and set the commands for it. Then you can use the fill method in the adapter to fill the result. An other option that you can try is using the data application block (or enterprise libraries) and you will get a method ExecuteDataSet(...).

Let me know if that helps.

Regards,

|||

This thread was moved to this forum (SQL Server Data Access) as the topic is more relevant here. The forum where it originally was posted (.NET Framewotk Inside SQL Server) deals with writing and running .NET code inside SQL Server (stored procs, funtions etc).

Niels

Manipulate various databases with only one connection

hi guys.

i want to know if is it possible to connect various databases with only one connection?

thx.

Yes if those "various databases" are linked.|||

ok...

but... how do i link them?

give me an example, or post a link, please.

thx.

|||Check out BOL for Linked Servers.|||

tell me, BOL means Books online?

thx.

|||Yes sir.|||

ok...

i am gonna check it out...

thx for the advice.

if i can't resolve my problem with this tip, i'll ask you again.

thx, and keep the good job.

Managing transactional replication

Hi everybody,

I wish to manage the process of transactional replication between two SQL databases using a c# (or any other possible language, does not differ) written tool. More accurately, I have a database in my server, and I want to have the program to be run from any other computer and does the replication between the local pc(where the program is run) and my server. More to say is that the connection between the server and pc(s) is over the internet.

I would be so thankful if anyone would help me to solve the issue,

with regards

farshad

Hi FArshad.

Look at: http://msdn2.microsoft.com/fr-fr/library/ms146966.aspx

It provides more links to create your publication, subscription and synchronization using RMO (C#).

For Transaction replication, you cannot just use Internet to do your synchronization. You would need to have VPN established. However you dont need VPN and can just synchronization over the Internet using Merge replication. If you are interested, search for eb Synchronization on msdn.

Hope that helps.

|||

Hi Mahesh and thanks for answering,

The exact thing I'm looking for is a way to start the replication in a subscriber machine; where replication is transactional and subscriber can be any machine that has sql server 2000 installed and has my database and its schema configured. Because I will have new entries in database and subscribers must not change their own databases, and also to save time, I'm going to use transactional replication. For now, I know how to generate snapshot and then synchronize the subscriber with publisher's data, but as you know, after a while when database becomes larger, generating snapshot would take a lot of time, so I'm searching a better solution. Is there any way to do so ?

Cheers,

farshad

|||

If I understand right you are saying that you do not want the snapshot to be donloaded but start the subscriber with a database and data.

If so search for 'Initialize from Backup' in BOL.

|||

Thanks Mahesh,

I think I didn't say what I meant, of course this was a good help, but not exactly the solution to my problem. Thanks a lot again,

cheers

farshad

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!
|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?
|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?
|||Hmm anybody? No response yet....

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?|||Hmm anybody? No response yet....

Managing SQL 2000 databases on Vista...

I have one database at an ISP running on a SQL 2000 server. Under Windows XP Pro, I installed SQL 2000 administrative tools then I could use the database manager as well as the query tool to manage my database.

I have upgraded my machine to Windows Vista Ultimate. SQL 2000 client services won't install under Windows Vista.

Under Windows Vista, I was able to create an ODBC component that connected successfully to the SQL 2000 remote database, but I no longer have a transaction tool or a gui to manage the tables.

My goals are simple: I want to be able to view/add/drop tables and data using some sort of GUI. I would also like to have some sort of SQL transaction client. I don't need to do any high level database management, just view/change/add/delete tables in the one database that I own on that server.

My isp is NOT going to upgrade their SQL. I do not own Microsoft Access or Microsoft Excel. What FREE software options do I have, given my goals?

You can use SQL Server Management Studio Express, which you can obtain for free at http://msdn.microsoft.com/vstudio/express/sql/download/

Thanks,

Peter Saddow

|||That works absolutely perfectly. Thank you!|||Although now I have another question since I've played with it a bit.

Is there an easy way to import and export data from tables using this tool? Like... if the data was in a comma delimited text file, for example? So far I've not been able to find a way to do this... I can edit the table data which is good but what if i have a massive text file I want to import....?|||

You can use the bcp utility: http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Additional, you can download the eval version of the full SQL Server product.

Thanks,

Peter Saddow

|||The BCP utility is part of the database install disk for SQL server. I only have the 2000 version, not the 2005 version.

Downloading Eval version isnt' a permanent solution...

I did purchase MS Office Excel 2007... and I can create an ODBC link to the database and grab table data... but if I edit the table I can't seem to find a way to get it to save those changes back to the table directly?|||Hmm anybody? No response yet....

Managing multiple SQL Server databases

Hi,

I have to manage many SQL Server databases on several servers. How can I manage the jobs, backups and the space on the disc without going to each and every server and database and job? Is there any script to run this? It will be very helpful if you can provide me the sample script or point me to any web site where I can get the info/script for this. Thanks in advance...

My preferable way to do this is to create the job on the server, script it out, parameterize it and deploy it with changed parameters on the other servers, this is also very helpful as you can tweak the paths to the log files, as they can differ from server to server as well as in the drive location as in the path.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

When running scripts to manage the system you are going to have to run them on the server they are needed on... Even if they are stored on the one server. I would start by looking at Linked Servers so that you can run your queries and scripts on the remote machines from the one server. In regards to monitoring the servers you could also look at MOM (Microsoft Operations Manager) and have the agents installed on each of your SQL Servers. This way the MOM System can monitor the OS Level parameters as well as some of the SQL Systems (Using the SQL Management Packs). At the same time you can also create custom scripts and tasks that MOM Can run that can be targeted to use the different servers.

With SSIS You should be able to also confugre your SQL Jobs to run from the one server and execute the different commands on the different servers.

|||

For Multiserver administration u need to create

Master server

Target Server

Enlist Traget server ................

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

I hope this helps

|||Thank you Jens, Glenn and admindba for your valuable inputs...

Managing backup files using SQL Agent

My client has about 3 servers with several databases on each. These databases
are backed up to disk using maintenance plans. This client wants to copy SQL
backup files on the disks of these servers to an additional server with a
tape drive to do consolidated backup to tape.
Is the best way to copy backup files already on disk to a network share to
use a DOS copy command through SQL Agent?
If so, is it best to run SQL Agent using a domain account with substantial
rights to ensure that SQL agent has appropriate file permissions both locally
and on network paths? When SQL agent runs under the local system account,
file copy operations fail with "Access denied" messages.
Does anyone have suggestions on how to do this task?
Larry Menzin
American Techsystems Corp.
I run my SQL Agent using a Domain User account that is local administrator
on the server.
In your case, just add a step to the jobs that the maintenance plans created
that calls XP_Cmdshell to do a DOS copy of the files after the backup is
complete.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:B52A63D9-A817-430C-9B0F-17139F121FBC@.microsoft.com...
> My client has about 3 servers with several databases on each. These
> databases
> are backed up to disk using maintenance plans. This client wants to copy
> SQL
> backup files on the disks of these servers to an additional server with a
> tape drive to do consolidated backup to tape.
> Is the best way to copy backup files already on disk to a network share to
> use a DOS copy command through SQL Agent?
> If so, is it best to run SQL Agent using a domain account with substantial
> rights to ensure that SQL agent has appropriate file permissions both
> locally
> and on network paths? When SQL agent runs under the local system account,
> file copy operations fail with "Access denied" messages.
> Does anyone have suggestions on how to do this task?
> --
> Larry Menzin
> American Techsystems Corp.
|||Hi
Do it as a OperatingSystemCommand (cmdExec) in SQL Agent.
Something like:
xcopy *.bak \\servername\sharename\*.* /E /Y
SQL Server has to use a domain account to run under, otherwise it does not
have a way to authenticate itself with the other server.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:B52A63D9-A817-430C-9B0F-17139F121FBC@.microsoft.com...
> My client has about 3 servers with several databases on each. These
> databases
> are backed up to disk using maintenance plans. This client wants to copy
> SQL
> backup files on the disks of these servers to an additional server with a
> tape drive to do consolidated backup to tape.
> Is the best way to copy backup files already on disk to a network share to
> use a DOS copy command through SQL Agent?
> If so, is it best to run SQL Agent using a domain account with substantial
> rights to ensure that SQL agent has appropriate file permissions both
> locally
> and on network paths? When SQL agent runs under the local system account,
> file copy operations fail with "Access denied" messages.
> Does anyone have suggestions on how to do this task?
> --
> Larry Menzin
> American Techsystems Corp.