Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

Question transport-level error

Hi all,

I'm having a little problem with my sqlserver 2005.

I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.

Like this:
-
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO

USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO

Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO

And drops the user by executing following:

Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO

USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]

-Then if I from a query, first creates the user, then drops the user, I can't create him again? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I'f i then runs the createscript again, the user is created correctly.

Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?

Best Regards
Henrik

Have you tried executing your scripts from sqlcmd instead of Management Studio? Are you seeing the same behavior?

Thanks
Laurentiu

|||

Hi Cristofor,

Thanks for you reply. I'v just tried that and get following:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

But under "Server Properties" - "Connections"

The "Allow Remote connections to this server" is checked, with a query timeout on 600

|||

In some SQL Server SKUs the remote connection is disabled by default. Use SQL Server Configuration Manager to enable TCP/IP (or named pipes) on your SQL Server instance (requires to restart SQL Server service). Also make sure the Windows firewall is configured to allow SQL Server to accept connections.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I have just enabled this it was disabled, but still im getting the same error.

But by executeing the script localy from the database server, it works fine. And can do it as many times as i please, whithout errors.
like this:

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql"
And
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql"

But it still drops the connection to the user elsewhere, on the first time it's used after creation.
Eg. First i drop the user, then i create it again with the scripts. Then i open a browser and goes to my website, wich uses this user to connect to the database to retrive and update information. Then the first time i try to login i get the error below. Then i push "F5" to refreash the page and all works fine again?

Soucre:

.Net SqlClient Data Provider

Error Message:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

|||

My only guess is that your web application is somehow caching/reusing the old connection (with the old login), and when you refresh the page it reestablishes the connection. Remember that when you drop and create a login it is a whole new principal, not the same one (even if the name and password is the same), and any established connection will be invalidated and the connection will be forcedly terminated by the server the next time any application tries to use it.

I would recommend using SQL profiler to trace back new/closed connections as well as existing sessions to try to detect if this is indeed the case.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

Thats also what im comming to. I haven't tryed the SQL profiler yet, but will do this later.

But i have found out that it's not enough only to stop and start the web site again on IIS. I have to restart the hole "IIS" to make the "refreash". So im thinking that the session is not terminated correctly from the website, when closing the browser/session?.

|||

Besides your investigation on why (and if) the web application is reusing the connection instead of reconnecting, I have one question for you: are you dropping and recreating this user in order to create a whole new principal (i.e. a new SID, with no existing objects, etc.), or just as a mechanism to prevent this principal from establishing a new connection?

If you are using these scripts to prevent new connections, you may also want to consider disabling the login instead. By disabling the login, you prevent new connections without affecting existing ones, and without the need to cleanup any objects or references (i.e. users) this principal may have in the system. You can find the syntax for this operation in ALTER LOGIN (http://msdn2.microsoft.com/en-us/library/ms189828.aspx).

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually im useing thise scripts as a part of my deploymentkit. This is to ensure that all securitysettings to any databases, stored procedures etc are droped. So i afterwards can replace them with either the same or new ones, without leaving anything behind.

But i took the change and implemented it into my deployment. The first time i got the same error about the "transport-level error". But that was because i've missed another scipt later in the installation, it was some inserts-stament i tried to execute from a vb SqlConnection. Then i moved those two things around, so it was the last code and then it worked fine, i can now open my webpage without the "transport-level error" :) yeah .. even after installing, uninstalling, installing ....

So i have to go back to the websession-closing, in my case, because under the "Activity Monitor" in sql, i can see a connection long after closing the browser. So this is now another problem for me, to find a way to kill this one.

But im stil interested in fixing the other problem, because it's annoying that things has to come in the correct order.. And the differens between executing it from a sql-query (in sql 2005), from command promt and from my msi-package. -Wich by the way executes it by calling a shell in vb wich runs a .cmd-file containing the two sql-scripts.

sqlcmd -sMYDBSERVER -E -i "C:\drop.sql" -o "C:\DropDBUser.log"
sqlcmd -sMYDBSERVER -E -i "C:\Create.sql" -o "C:\CreateDBUser.log"

But for the moment im happy :)

|||

I’m glad to hear that. Feel free to let us know if you have further security questions in SQL Server, we are always looking forward to help our customers whenever we have a chance.

-Raul Garcia

SDE/T

SQL Server Engine

sql

Question on the evacuate of the datafile

Hi
I am using sqlserver 2000. I want to evacuate and drop a datafile.
It is around 10gb. My question is
Is it a online operation? and is there a performance impact on the database
druing this.
Are the tables available for DML during the time of evacuation?
TKs
Mangesh
Mangesh,
What do you mean by evacuation? Do you want to empty a data file? If so use,
DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to do.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Mangesh Deshpande" wrote:

> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the database
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangesh
|||Yes. It is referred in some books as evacuation of datafiles.
If i run DBCC SHRINKFILE WITH EMPTYFILE can I have DML on the tables whose
data is emptied on the other datafile.
Thanks
Mangesh
"Mark Allison" wrote:
[vbcol=seagreen]
> Mangesh,
> What do you mean by evacuation? Do you want to empty a data file? If so use,
> DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to do.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Mangesh Deshpande" wrote:
|||Yes it is basically an online operation except for the extents it is
physically moving at any particular time. And yes there can be a
performance impact since you are moving large quantities of data about.
This kind of operation is best done in off peak hours.
Andrew J. Kelly SQL MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:6E692DBD-5CA9-4243-995F-3B4BF3D9F8F2@.microsoft.com...
> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the
> database
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangesh

Question on the evacuate of the datafile

Hi
I am using sqlserver 2000. I want to evacuate and drop a datafile.
It is around 10gb. My question is
Is it a online operation? and is there a performance impact on the database
druing this.
Are the tables available for DML during the time of evacuation?
TKs
MangeshMangesh,
What do you mean by evacuation? Do you want to empty a data file? If so use,
DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to do
.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Mangesh Deshpande" wrote:

> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the databas
e
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangesh|||Yes. It is referred in some books as evacuation of datafiles.
If i run DBCC SHRINKFILE WITH EMPTYFILE can I have DML on the tables whose
data is emptied on the other datafile.
Thanks
Mangesh
"Mark Allison" wrote:
[vbcol=seagreen]
> Mangesh,
> What do you mean by evacuation? Do you want to empty a data file? If so us
e,
> DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to
do.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Mangesh Deshpande" wrote:
>|||Yes it is basically an online operation except for the extents it is
physically moving at any particular time. And yes there can be a
performance impact since you are moving large quantities of data about.
This kind of operation is best done in off peak hours.
Andrew J. Kelly SQL MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:6E692DBD-5CA9-4243-995F-3B4BF3D9F8F2@.microsoft.com...
> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the
> database
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangeshsql

Question on the evacuate of the datafile

Hi
I am using sqlserver 2000. I want to evacuate and drop a datafile.
It is around 10gb. My question is
Is it a online operation? and is there a performance impact on the database
druing this.
Are the tables available for DML during the time of evacuation?
TKs
MangeshMangesh,
What do you mean by evacuation? Do you want to empty a data file? If so use,
DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to do.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Mangesh Deshpande" wrote:
> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the database
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangesh|||Yes. It is referred in some books as evacuation of datafiles.
If i run DBCC SHRINKFILE WITH EMPTYFILE can I have DML on the tables whose
data is emptied on the other datafile.
Thanks
Mangesh
"Mark Allison" wrote:
> Mangesh,
> What do you mean by evacuation? Do you want to empty a data file? If so use,
> DBCC SHRINKFILE WITH EMPTYFILE. Let us know exactly what you're trying to do.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Mangesh Deshpande" wrote:
> > Hi
> >
> > I am using sqlserver 2000. I want to evacuate and drop a datafile.
> > It is around 10gb. My question is
> > Is it a online operation? and is there a performance impact on the database
> > druing this.
> >
> > Are the tables available for DML during the time of evacuation?
> >
> > TKs
> > Mangesh|||Yes it is basically an online operation except for the extents it is
physically moving at any particular time. And yes there can be a
performance impact since you are moving large quantities of data about.
This kind of operation is best done in off peak hours.
--
Andrew J. Kelly SQL MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:6E692DBD-5CA9-4243-995F-3B4BF3D9F8F2@.microsoft.com...
> Hi
> I am using sqlserver 2000. I want to evacuate and drop a datafile.
> It is around 10gb. My question is
> Is it a online operation? and is there a performance impact on the
> database
> druing this.
> Are the tables available for DML during the time of evacuation?
> TKs
> Mangesh

Question on SQL Server 2000 - 2005

I am a DB2 DBA that has been asked to become familiar enough with SQL
Server in order to become actively involved in its installation,
implementation, and to review database backup/recovery procedures. SQL
Server will be used by a vendor packaged application.

The problem is that we are awaiting word on whether it will be SQL
Server 2000 or 2005 we will be working with. I have been told that
there are considerable differences between the two versions.

There is an upcoming local 5 day class on Administering SQL Server 2000
that I would like to take. (outline below)

Question is, are the differences between the two versions so drastic as
to render this class less than useful, especially in terms of
installation and basic db maintenance functions?

Many thanks in advance.
Gerry

Course Outline: 2072 Administering a Microsoft SQL Server 2000 Database

Lesson 1: SQL Server Overview

What Is SQL Server
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server

Top

Lesson 2: Planning to Install SQL Server

Hardware Installation Considerations
SQL Server 2000 Editions
Software Installation Considerations
Methods of Installing SQL Server
Verifying the Installation
Configuring SQL Server Enterprise Manager
Troubleshooting

Lesson 3: Managing Database Files

Introduction to Data Structures
Creating Databases
Managing Databases
Placing Database Files and Logs
Optimizing the Database Using Hardware-based RAID
Optimizing the Database Using Filegroups
Optimizing the Database Using Filegroups with Hardware-based
RAID
Capacity Planning
Performance Considerations

Lesson 4: Managing Security

Implementing an Authentication Mode
Assigning Logins to Users and Roles
Assigning Permissions to Users and Roles
Managing Security Within SQL Server
Managing Application Security
Managing SQL Server Security in the Enterprise

Lesson 5: Performing Administrative Tasks

Configuration Tasks
Routine SQL Server Administrative Tasks
Automating Routine Maintenance Tasks
Creating Alerts
Troubleshooting SQL Server Automation
Automating Multiserver Jobs

Top

Lesson 6: Backing Up Databases

Preventing Data Loss
Setting and Changing a Database Recovery Model
SQL Server Backup
When to Back Up Databases
Performing Backups
Types of Backup Methods
Planning a Backup Strategy
Performance Considerations

Lesson 7: Restoring Databases

SQL Server Recovery Process
Preparing to Restore a Database
Restoring Backups
Restoring Databases from Different Backup Types
Restoring Damaged System Databases

Lesson 8: Monitoring SQL Server for Performance

Why to Monitor SQL Server
Performance Monitoring and Tuning
Tools for Monitoring SQL Server
Common Monitoring and Tuning Tasks

Lesson 9: Transferring Data

Introduction to Transferring Data
Tools for Importing and Exporting Data in SQL Server
Introduction to DTS
Transforming Data with DTS

Top

Lesson 10: Maintaining High Availability

Introduction to Availability
Increasing Availability Using Failover Clustering
Standby Servers and Log Shipping

Lesson 11: Introducing Replication

Introduction to Distributed Data
Introduction to SQL Server Replication
SQL Server Replication Agents
SQL Server Replication Types
Physical Replication ModelsIn my humble opinion you better get training on SQL 2005. But knowledge
of SQL 2000 will add on to SQL 2005|||(datapro01@.yahoo.com) writes:
> Question is, are the differences between the two versions so drastic as
> to render this class less than useful, especially in terms of
> installation and basic db maintenance functions?

Everything that works in SQL 2000 works in SQL 2005, at least almost. But
SQL 2005 may offer new and better ways that what you learn on the SQL 2000
class.

The one area where the SQL 2000 class may be more confusing than helpful
if you settle on SQL 2005, is installation. The setup program is different,
there are different configuration issues etc.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the replies and the info. New Horizons is telling me that
the training materials for 2005 have not yet been released. At least to
them.

I will certainly do my best to understand the installation differences
between the versions.

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on merge replication

We are working on an application that is running data on a local sql
server 2000 database. There is a second sql server on a different
domain, connected via vpn. Database on main domain sql server is
replicated / merge / push published to the 2nd domain sql server
The goal is to be available 24/7 for 100% so the plan is when the main
server becomes unavailable users are rerouted and the 2nd server takes
over. When the 1st becomes available again the data on the 1st server
should be updated from the 2nd and is supposed to resum
We are wondering now if Merge replication is is the way to go AND how
to set it up.
TIA, Martin
Merge replication can be use for this the caveats are
1) latency. Latency can be 1 minute or longer depending on the volume of
your transactions. This means if your publisher goes belly up, and your
standby (subscriber) server comes on line, it may not have all the records
which your publisher has
2) replication will add a guid column to your tables which may break the
application which is using these tables
Bi-directional replication is ideal for this as
1) latency is typically much less
2) you don't have to add a guid column
3) bi-directional replication works best when the transactions originate at
one node at a time
To set up merge replication go to Tools, point to replication, and click
create and manage publications and follow the prompts
To set up bi-directional transactional replication you are best to get the
updated BOL, and search on Implementing Nonpartitioned, Bidirectional,
Transactional Replication.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:4thvi0d35qmir83nnri6ud7tpcclqu6b79@.4ax.com...
>
> We are working on an application that is running data on a local sql
> server 2000 database. There is a second sql server on a different
> domain, connected via vpn. Database on main domain sql server is
> replicated / merge / push published to the 2nd domain sql server
> The goal is to be available 24/7 for 100% so the plan is when the main
> server becomes unavailable users are rerouted and the 2nd server takes
> over. When the 1st becomes available again the data on the 1st server
> should be updated from the 2nd and is supposed to resum
> We are wondering now if Merge replication is is the way to go AND how
> to set it up.
> TIA, Martin
>

Question on FOR XML issue

Hi, all.

I'm trying to become smarter about the use of the FOR XML clause in SQL
Server. There's one question I'd like to ask that I've not found
directly answered elsewhere, probably due to my own inaccurate or
feeble (or both) searching, so I thought I'd ask the good folks here
for a little information if I may.

I've played with the FOR XML AUTO, ELEMENTS variation of the XML
capability, and it is marvelous for what I would call "cleanly"
hierarchical data, and by that I naively mean always one-to-many
relationships.

The data I'm trying to model has, at a basic level, several one-to-many
relationships, eg Parent has many children of type X, and many children
of type Y. X and Y, themselves, are unrelated. When I write the query
to dump this data, however, SQL Server tries to wrap Y records as
children of X records, which is not correct. Another way of thinking
about it might be a list of PERSONS, and there is a BOOKS table of
favorite books for each person, and a table of favorite TV shows for
each person. BOOKS and TV are unrelated.

EG

Select Person.Lastname,Parent.Firstname,Books.Title,TV.Sh owTitle
from Person
join BOOKS
on Person.PersonID=Books.PersonID
join TV
on Person.PersonID=TV.PersonID
for XML AUTO, ELEMENTS

And I'd like to see:

<PERSON>
<Lastname>somevalue</Lastname>
<Firstanme>somevalue</Lastname>
<BOOKS>
<TITLE>Moby Dick</TITLE>
</BOOKS>
<BOOKS>
<TITLE>Hunt for Red October</TITLE>
</BOOKS>
<TV>
<ShowTItle>Laverne and Shirley</ShowTitle>
</TV>
</PERSON
But I get something more on the order of this:

<PERSON>
<Lastname>somevalue</Lastname>
<Firstname>somevalue</Firstname>
<BOOKS>
<TITLE>MOBY DICK</TITLE>
<TV>
<SHOWTITLE>Laverne and Shirley</ShowTitle>
</TV>
</BOOKS>
</PERSON
At a first glance, it doesn't seem to me there's a way to solve
this, because the joins inevitably create the appearance of a
relationship between the two "inner" pieces of data even though it
doesn't really exist. Perhaps I'm merely writing the query incorrectly,
but as I try to think of ways to rearrange it, it seems I'm just moving
the same problem around. Is there a way to solve it? Would it be
smarter just to break the queries up into separate XML exports, eg a
BOOKS.XML and a TV.XML, then combine them?

If I'm being unyieldingly stupid in not seeing the answer to this,
please accept my humble apologies in advance.

Your help is greatly appreciated. I would ask that replies be posted to
the newsgroup; the email referenced in this message is dead.

Thanks again,
David<intrepid_dw@.hotmail.com> wrote in message
news:1111166461.577036.179350@.l41g2000cwc.googlegr oups.com...
> Hi, all.
> I'm trying to become smarter about the use of the FOR XML clause in SQL
> Server. There's one question I'd like to ask that I've not found
> directly answered elsewhere, probably due to my own inaccurate or
> feeble (or both) searching, so I thought I'd ask the good folks here
> for a little information if I may.
> I've played with the FOR XML AUTO, ELEMENTS variation of the XML
> capability, and it is marvelous for what I would call "cleanly"
> hierarchical data, and by that I naively mean always one-to-many
> relationships.

<snip
I don't have an answer myself, but you might get a better response in
microsoft.public.sqlserver.xml

Simon|||(intrepid_dw@.hotmail.com) writes:
> The data I'm trying to model has, at a basic level, several one-to-many
> relationships, eg Parent has many children of type X, and many children
> of type Y. X and Y, themselves, are unrelated. When I write the query
> to dump this data, however, SQL Server tries to wrap Y records as
> children of X records, which is not correct. Another way of thinking
> about it might be a list of PERSONS, and there is a BOOKS table of
> favorite books for each person, and a table of favorite TV shows for
> each person. BOOKS and TV are unrelated.

I'm by no means an expert in XML, but I would guess that you need to
use FOR XML EXPLICIT in this case. Below is an example for your situation.
I should add that I mainly made this example for my own exercise, so
this may not be the "proper" way to write it.

SELECT 1 as Tag,
NULL as Parent,
OrderID as [Order!1!OrderID],
NULL as [Customer!2!CustomerID!element],
NULL as [Customer!2!CompanyName!element],
NULL as [Customer!2!City!element],
NULL as [Employee!3!FirstName!element],
NULL as [Employee!3!LastName!element]
FROM Orders
WHERE OrderID BETWEEN 11000 AND 11020
UNION ALL
select 2, 1,
Orders.OrderID, c.CustomerID, c.CompanyName, c.City, NULL, NULL
from Orders
join Customers c ON Orders.CustomerID = c.CustomerID
WHERE Orders.OrderID BETWEEN 11000 AND 11020
UNION ALL
select 3, 1, o.OrderID, NULL, NULL, NULL, e.FirstName, e.LastName
from Orders o
join Employees e ON o.EmployeeID = e.EmployeeID
WHERE o.OrderID BETWEEN 11000 AND 11020
ORDER BY [Order!1!OrderID], Tag
FOR XML EXPLICIT

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

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

The more I study this, the more I think you're right on the money. I
suspected that the problem was at either end of the spectrum; either I
wrote the query horribly wrong to begin with, and was missing something
obvious, or I needed to use the EXPLICIT alternative and the universal
table. Unfortunately, the universal table and query that would be
necessary for the data I'm actually modeling would be HUGE and just
about incomprehensible.

I think what I'm going to do is to design queries to return the "pure"
one-to-many relationships, and then merge the resultant XML into a
larger file. That's not at all complicated and strikes me as a
substantially smarter alternative than trying to write the ELEMENTS
version of this query.

As an irrelevant aside, its interesting to design an XML layout for
existing data because it out almost reminds me of designing a grammar,
eg decompositions, generations of repetitive sections, etc.

Thanks for your assistance, Erland!

-David

Erland Sommarskog wrote:
> (intrepid_dw@.hotmail.com) writes:
> > The data I'm trying to model has, at a basic level, several
one-to-many
> > relationships, eg Parent has many children of type X, and many
children
> > of type Y. X and Y, themselves, are unrelated. When I write the
query
> > to dump this data, however, SQL Server tries to wrap Y records as
> > children of X records, which is not correct. Another way of
thinking
> > about it might be a list of PERSONS, and there is a BOOKS table of
> > favorite books for each person, and a table of favorite TV shows
for
> > each person. BOOKS and TV are unrelated.
> I'm by no means an expert in XML, but I would guess that you need to
> use FOR XML EXPLICIT in this case. Below is an example for your
situation.
> I should add that I mainly made this example for my own exercise, so
> this may not be the "proper" way to write it.
> SELECT 1 as Tag,
> NULL as Parent,
> OrderID as [Order!1!OrderID],
> NULL as [Customer!2!CustomerID!element],
> NULL as [Customer!2!CompanyName!element],
> NULL as [Customer!2!City!element],
> NULL as [Employee!3!FirstName!element],
> NULL as [Employee!3!LastName!element]
> FROM Orders
> WHERE OrderID BETWEEN 11000 AND 11020
> UNION ALL
> select 2, 1,
> Orders.OrderID, c.CustomerID, c.CompanyName, c.City, NULL,
NULL
> from Orders
> join Customers c ON Orders.CustomerID = c.CustomerID
> WHERE Orders.OrderID BETWEEN 11000 AND 11020
> UNION ALL
> select 3, 1, o.OrderID, NULL, NULL, NULL, e.FirstName, e.LastName
> from Orders o
> join Employees e ON o.EmployeeID = e.EmployeeID
> WHERE o.OrderID BETWEEN 11000 AND 11020
> ORDER BY [Order!1!OrderID], Tag
> FOR XML EXPLICIT
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp