relationships, primary key sql2000 question.

Hello,

I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.

When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.

Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.

Thanks

Peter

If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.

If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.

|||

What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.

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

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

|||

Thanks guys, working through links now.

Peter

sql

Reinitializing transactional subscribers

In SQL2000, SP3, We want to periodically refresh our read-only
subscribers with the latest schema and data (including FK's, indexes,
etc) whenever we role out a new software release. The database is
relatively small and we can currently disable publishing\remove
subscribers, and add them back in about 15 minutes. As the database
grows, I'm not sure if this would be the best method of synchronizing
the subscribers, but management likes the idea of a complete snapshot.
Is there a typical approach to updating subscribers with all
differences that makes more sense? The subscribers are primarily for
Disaster Recovery, but are also used to reduce the need for our
European customers to come to the US for reads. When I tried the
"start synchronizing" option in the EM gui, it has difficulty
dropping/replacing tables because it can't drop tables w FK's.
Also, can anyone explain the difference between reinitializing and
synchronizing?
thx much!
Gary,
if you have a need to drop FKs then it is possible to apply a prescript
before the snapshot is applied (botom of snapshot tab on publication
properties).
Some schema changes can be carried out using the 'plumbing' of replication,
eg sp_repladdcolumn and sp_repldropcolumn. Some other changes eg addition of
an index, can be done using sp_addscriptexec.
As for the difference between initialization and synchronization, right at
the start when the snapshot is sent to the subscriber there isn't really
one. Once the snapshot has been applied, sending down transactions in the
case of transactional replication is referred to as synchronization, and
reinitialization would refer to a new snapshot being created and propagated.
HTH,
Paul Ibison
|||Thanks Paul... But rather than running a pre-script, and individual
system sp's, do you know if this methodology - dropping/re-establishing
replication as new releases are rolled out is a common approach to
keeping things in-sync?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I have seen it used, but sp_droparticle and sp_addarticle, along with
sp_repladdcolumn and sp_repldropcolumn are more common for small changes.
Regards,
Paul

Reindexing has different behaviour in 2005

I noticed that when I use Maintenace plan wizard to create maintenance plan
to reindex a database, it runs different than it did on SQL2000.
On SQL2000, the job was running about 15 minutes on my 10GB database. During
that time there was a lot of disk activity (loging) and CPU was around 30%.
Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
database, it first utilizes CPU to maximum (one thread on hyper-threaded
CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
Profiler tells me that it selects data about database schema. That runs
about 30 minutes and after that actual reindexing begins that also couses a
lot of disk activity and lasts about 15 minutes (same as on SQL2000).
I'm a bit confused about that first 30 minutes when only CPU is doing all
the work. That part was not there in SQL2000. If it takes 30 minutes on 10GB
database, what will happen on 100GB or larger databases? Has anyone some
info if that behaviour has changed in SQL2005?
TomCan you tell us what command(s) the mp is running? You should be able to
script it out.
"Tom" wrote:

> I noticed that when I use Maintenace plan wizard to create maintenance pla
n
> to reindex a database, it runs different than it did on SQL2000.
> On SQL2000, the job was running about 15 minutes on my 10GB database. Duri
ng
> that time there was a lot of disk activity (loging) and CPU was around 30%
.
> Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
> database, it first utilizes CPU to maximum (one thread on hyper-threaded
> CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
> Profiler tells me that it selects data about database schema. That runs
> about 30 minutes and after that actual reindexing begins that also couses
a
> lot of disk activity and lasts about 15 minutes (same as on SQL2000).
> I'm a bit confused about that first 30 minutes when only CPU is doing all
> the work. That part was not there in SQL2000. If it takes 30 minutes on 10
GB
> database, what will happen on 100GB or larger databases? Has anyone some
> info if that behaviour has changed in SQL2005?
> Tom
>
>

Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or SQL2000 server in SQL Server Management Studio?

You can use enterprise manager from sql 2005 to administer both sql 2000 and sql 2005 databases.

I doubt that the enterprise manager from sql 2000 would work with a sql 2005 database.

|||How should I use enterprise manager in SQL2005?|||

Its Management Studio.

From Management Studio, you can connect to both sql 2000 and sql 2005 databases.

http://www.aspfaq.com/sql2005/show.asp?id=3

|||this SQL Express, is there any stest I can use for SQL 2000?|||

Hi

Below is from previous link:

As for connecting to Express from application code, this should not be any different from connecting to a named instance of SQL Server 2000. Your connection string should look like this, assuming local machine and an instance name of SQLEXPRESS (you might need a different instance name, and you may have to use a machine name, rather than "." (which means local)).

Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or SQL2000 server in SQL Server Management Studio?

you can't use it even if it registers.

here's a story:

i have bothe Enterprise manager and management studio on the same box

what ever i register on the management studio gets registered to EM.

but the problem is i cant open version 9 Db on EM even it i see it on the list of server

Register SQL2005 in SQL2000 EM

I have 2 development machines. SQL2000 is installed on the first one and
SQL2005 is on the second machine. Is it possible to register SQL2005 and
have an access to it on the first machine via Enterprise Manager? I'm trying
to do that and get messages that server doesn't exist or access denied.
Either it's not possible at all or I'm using wrong approach or...
Thank you
Vlacha
Nope.. it is not possible to register sql2005 with sql 2000 em.. you need
management studio..
VT
"Vlacha" <abc@.optonline.net> wrote in message
news:u%23I7rF4IHHA.3872@.TK2MSFTNGP06.phx.gbl...
>I have 2 development machines. SQL2000 is installed on the first one and
>SQL2005 is on the second machine. Is it possible to register SQL2005 and
>have an access to it on the first machine via Enterprise Manager? I'm
>trying to do that and get messages that server doesn't exist or access
>denied.
> Either it's not possible at all or I'm using wrong approach or...
> Thank you
> Vlacha
>
|||Thanks a lot for saving my time
Vlacha
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uZuHLK4IHHA.2236@.TK2MSFTNGP02.phx.gbl...
> Nope.. it is not possible to register sql2005 with sql 2000 em.. you need
> management studio..
> VT
>
> "Vlacha" <abc@.optonline.net> wrote in message
> news:u%23I7rF4IHHA.3872@.TK2MSFTNGP06.phx.gbl...
>
|||You can, however, use Query Analyzer with SQL 2005.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Vlacha" <abc@.optonline.net> wrote in message
news:e3dyaO4IHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot for saving my time
> Vlacha
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uZuHLK4IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>

Register SQL2005 in SQL2000 EM

I have 2 development machines. SQL2000 is installed on the first one and
SQL2005 is on the second machine. Is it possible to register SQL2005 and
have an access to it on the first machine via Enterprise Manager? I'm trying
to do that and get messages that server doesn't exist or access denied.
Either it's not possible at all or I'm using wrong approach or...
Thank you
VlachaNope.. it is not possible to register sql2005 with sql 2000 em.. you need
management studio..
VT
"Vlacha" <abc@.optonline.net> wrote in message
news:u%23I7rF4IHHA.3872@.TK2MSFTNGP06.phx.gbl...
>I have 2 development machines. SQL2000 is installed on the first one and
>SQL2005 is on the second machine. Is it possible to register SQL2005 and
>have an access to it on the first machine via Enterprise Manager? I'm
>trying to do that and get messages that server doesn't exist or access
>denied.
> Either it's not possible at all or I'm using wrong approach or...
> Thank you
> Vlacha
>|||Thanks a lot for saving my time
Vlacha
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uZuHLK4IHHA.2236@.TK2MSFTNGP02.phx.gbl...
> Nope.. it is not possible to register sql2005 with sql 2000 em.. you need
> management studio..
> VT
>
> "Vlacha" <abc@.optonline.net> wrote in message
> news:u%23I7rF4IHHA.3872@.TK2MSFTNGP06.phx.gbl...
>|||You can, however, use Query Analyzer with SQL 2005.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Vlacha" <abc@.optonline.net> wrote in message
news:e3dyaO4IHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot for saving my time
> Vlacha
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uZuHLK4IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>

register sql2000 on SQL2005 manager

I have two servers 2003, one running sqlserver 2005 and the second sqlserver
2000.
I'd like to register the sql2000 on the sqlserver management studio provides
with sql2005.
when I trie to connect to server with sql2000 I get an time out error. I've
already activated all the protocol : tcp/ip, named pipe ...
The target is to restore sql2000 database on sqlserver 2005.
thanks for your help.Hi,
Welcome to MSDE Managed NewsGroup. This is Justin from Microsoft.
It is supported to manage SQL2000 in SQL 2005 Management Studio. However,
if you only need to restore SQL2000 database to SQL2005, I recommend you
backup the SQL2000 database in the SQL2000 Enterprise Manager and copy the
backup file tot he SQL 2005 machine and then restore the backup file to
SQL2005.
I do not see much benefit to manage SQL2000 in Management Studio in this
case.
If you really need to register SQL2000 in Management Studio, please verify
that you can connect to the SQL 2000 from the 2005 machine.
1. Ping the 2000 server from the 2005 box and see if it succeeds
2. Telnet to the particular port and see if it works:
Telnet ServerName 1433
NOTE: Please run the Server Network Utility from the 2000 box to
determine the port SQL Server is listening. It might not be 1433 if it is a
named instance.
3. Create a UDL file to connect to the 2000 and see if it works.
- You can create a .txt file on the desktop, change its extension to
UDL and double-click it to launch the Data Link dialog box
If the step 1 & 2 do not work, you need to contact your network team and
see if there is any problem in the Network.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: register sql2000 on SQL2005 manager
| thread-index: AcYmlKMdyn41f0o1QSWXgimqxWzbcg==| X-WBNR-Posting-Host: 82.228.221.13
| From: "=?Utf-8?B?UEJFTklTVFk=?=" <pbenisty@.newsgroups.nospam>
| Subject: register sql2000 on SQL2005 manager
| Date: Tue, 31 Jan 2006 10:32:06 -0800
| Lines: 11
| Message-ID: <4F8CEDBF-7DBF-4699-BADF-8D20D12B3A15@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419556
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have two servers 2003, one running sqlserver 2005 and the second
sqlserver
| 2000.
| I'd like to register the sql2000 on the sqlserver management studio
provides
| with sql2005.
|
| when I trie to connect to server with sql2000 I get an time out error.
I've
| already activated all the protocol : tcp/ip, named pipe ...
|
| The target is to restore sql2000 database on sqlserver 2005.
|
| thanks for your help.
|

register sql2000 on SQL2005 manager

I have two servers 2003, one running sqlserver 2005 and the second sqlserver
2000.
I'd like to register the sql2000 on the sqlserver management studio provides
with sql2005.
when I trie to connect to server with sql2000 I get an time out error. I've
already activated all the protocol : tcp/ip, named pipe ...
The target is to restore sql2000 database on sqlserver 2005.
thanks for your help.
Hi,
Welcome to MSDE Managed NewsGroup. This is Justin from Microsoft.
It is supported to manage SQL2000 in SQL 2005 Management Studio. However,
if you only need to restore SQL2000 database to SQL2005, I recommend you
backup the SQL2000 database in the SQL2000 Enterprise Manager and copy the
backup file tot he SQL 2005 machine and then restore the backup file to
SQL2005.
I do not see much benefit to manage SQL2000 in Management Studio in this
case.
If you really need to register SQL2000 in Management Studio, please verify
that you can connect to the SQL 2000 from the 2005 machine.
1. Ping the 2000 server from the 2005 box and see if it succeeds
2. Telnet to the particular port and see if it works:
Telnet ServerName 1433
NOTE: Please run the Server Network Utility from the 2000 box to
determine the port SQL Server is listening. It might not be 1433 if it is a
named instance.
3. Create a UDL file to connect to the 2000 and see if it works.
- You can create a .txt file on the desktop, change its extension to
UDL and double-click it to launch the Data Link dialog box
If the step 1 & 2 do not work, you need to contact your network team and
see if there is any problem in the Network.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: register sql2000 on SQL2005 manager
| thread-index: AcYmlKMdyn41f0o1QSWXgimqxWzbcg==
| X-WBNR-Posting-Host: 82.228.221.13
| From: "=?Utf-8?B?UEJFTklTVFk=?=" <pbenisty@.newsgroups.nospam>
| Subject: register sql2000 on SQL2005 manager
| Date: Tue, 31 Jan 2006 10:32:06 -0800
| Lines: 11
| Message-ID: <4F8CEDBF-7DBF-4699-BADF-8D20D12B3A15@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419556
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have two servers 2003, one running sqlserver 2005 and the second
sqlserver
| 2000.
| I'd like to register the sql2000 on the sqlserver management studio
provides
| with sql2005.
|
| when I trie to connect to server with sql2000 I get an time out error.
I've
| already activated all the protocol : tcp/ip, named pipe ...
|
| The target is to restore sql2000 database on sqlserver 2005.
|
| thanks for your help.
|

register sql2000 on SQL2005 manager

I have two servers 2003, one running sqlserver 2005 and the second sqlserver
2000.
I'd like to register the sql2000 on the sqlserver management studio provides
with sql2005.
when I trie to connect to server with sql2000 I get an time out error. I've
already activated all the protocol : tcp/ip, named pipe ...
The target is to restore sql2000 database on sqlserver 2005.
thanks for your help.Hi,
Welcome to MSDE Managed NewsGroup. This is Justin from Microsoft.
It is supported to manage SQL2000 in SQL 2005 Management Studio. However,
if you only need to restore SQL2000 database to SQL2005, I recommend you
backup the SQL2000 database in the SQL2000 Enterprise Manager and copy the
backup file tot he SQL 2005 machine and then restore the backup file to
SQL2005.
I do not see much benefit to manage SQL2000 in Management Studio in this
case.
If you really need to register SQL2000 in Management Studio, please verify
that you can connect to the SQL 2000 from the 2005 machine.
1. Ping the 2000 server from the 2005 box and see if it succeeds
2. Telnet to the particular port and see if it works:
Telnet ServerName 1433
NOTE: Please run the Server Network Utility from the 2000 box to
determine the port SQL Server is listening. It might not be 1433 if it is a
named instance.
3. Create a UDL file to connect to the 2000 and see if it works.
- You can create a .txt file on the desktop, change its extension to
UDL and double-click it to launch the Data Link dialog box
If the step 1 & 2 do not work, you need to contact your network team and
see if there is any problem in the Network.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: register sql2000 on SQL2005 manager
| thread-index: AcYmlKMdyn41f0o1QSWXgimqxWzbcg==
| X-WBNR-Posting-Host: 82.228.221.13
| From: "examnotes" <pbenisty@.newsgroups.nospam>
| Subject: register sql2000 on SQL2005 manager
| Date: Tue, 31 Jan 2006 10:32:06 -0800
| Lines: 11
| Message-ID: <4F8CEDBF-7DBF-4699-BADF-8D20D12B3A15@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419556
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have two servers 2003, one running sqlserver 2005 and the second
sqlserver
| 2000.
| I'd like to register the sql2000 on the sqlserver management studio
provides
| with sql2005.
|
| when I trie to connect to server with sql2000 I get an time out error.
I've
| already activated all the protocol : tcp/ip, named pipe ...
|
| The target is to restore sql2000 database on sqlserver 2005.
|
| thanks for your help.
|