ATTACH DB ERROR

Hello
I try to attach a database and the following error occurs:
Server: Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '16a5eed57f3000300' in index page
(1:29375), index ID 8, database 'mat'.
26 transactions rolled forward in database 'mat' (10).
Connection Broken
Can anyone help me?The database was most probably corrupt when you detached it. Search the new
updated Books Online for
specific recommendations for your particular error number. consider opening
a case with MS Support.
Also, you might want to check http://www.karaszi.com/SQLServer/in...br />
t_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>|||In addition , I'm sure you have last backup of the database.
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>

ATTACH DB ERROR

Hello
I try to attach a database and the following error occurs:
Server: Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '16a5eed57f3000300' in index page
(1:29375), index ID 8, database 'mat'.
26 transactions rolled forward in database 'mat' (10).
Connection Broken
Can anyone help me?The database was most probably corrupt when you detached it. Search the new updated Books Online for
specific recommendations for your particular error number. consider opening a case with MS Support.
Also, you might want to check http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>|||In addition , I'm sure you have last backup of the database.
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>

ATTACH DB ERROR

Hello
I try to attach a database and the following error occurs:
Server: Msg 644, Level 21, State 5, Line 1
Could not find the index entry for RID '16a5eed57f3000300' in index page
(1:29375), index ID 8, database 'mat'.
26 transactions rolled forward in database 'mat' (10).
Connection Broken
Can anyone help me?
The database was most probably corrupt when you detached it. Search the new updated Books Online for
specific recommendations for your particular error number. consider opening a case with MS Support.
Also, you might want to check http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>
|||In addition , I'm sure you have last backup of the database.
"koletsis theo" <koletsis theo@.discussions.microsoft.com> wrote in message
news:393BC089-3F37-4EBA-AA3D-66D51A6E9655@.microsoft.com...
> Hello
> I try to attach a database and the following error occurs:
> Server: Msg 644, Level 21, State 5, Line 1
> Could not find the index entry for RID '16a5eed57f3000300' in index page
> (1:29375), index ID 8, database 'mat'.
> 26 transactions rolled forward in database 'mat' (10).
> Connection Broken
> Can anyone help me?
>
>

Attach a database

Yesterday, I found I could not do a select into in my development staging
database. I received the following message...
Server: Msg 9001, Level 21, State 3, Line 266
The log for database 'DB_ST' is not available.
Connection Broken
I found that there was no log file!!! I have no idea why not. So I decided
to try to detach the database and re-attach using sp_attach_single_file_db a
s
follows and received...
USE MASTER
EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
@.physname = 'D:\Databases\DB_ST_DATA.mdf'
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
Now I am stumped with no database and log. I have just called the
applications manager who thinks we may need to restore from another
environment. I question whether he backed up the development environment!!!!
!
Is this the only option I have?Yes. sp_attach_single_file_db might is only guaranteed to work if you detach
properly the database with sp_detach_db first, that is with the log file
still there. In other situation, like the one you have, it might work
sometimes, but is far from guaranteed. In that case you need to restore from
a backup.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
> Yesterday, I found I could not do a select into in my development staging
> database. I received the following message...
> Server: Msg 9001, Level 21, State 3, Line 266
> The log for database 'DB_ST' is not available.
> Connection Broken
> I found that there was no log file!!! I have no idea why not. So I decided
> to try to detach the database and re-attach using sp_attach_single_file_db
> as
> follows and received...
> USE MASTER
> EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
> @.physname = 'D:\Databases\DB_ST_DATA.mdf'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
> Now I am stumped with no database and log. I have just called the
> applications manager who thinks we may need to restore from another
> environment. I question whether he backed up the development
> environment!!!!!
> Is this the only option I have?|||Thanks Jacco... I thought that would be the case.
"Jacco Schalkwijk" wrote:

> Yes. sp_attach_single_file_db might is only guaranteed to work if you deta
ch
> properly the database with sp_detach_db first, that is with the log file
> still there. In other situation, like the one you have, it might work
> sometimes, but is far from guaranteed. In that case you need to restore fr
om
> a backup.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
>
>sql

At what point is schema information loaded?

I am hoping that someone with a good knowledge of the internals of
SQLServer will be able to help with the following - admittedly bizarre
- question (assume SQLServer 2000 throughout):
We have two SQLServer databases, dbmain and dbslave. An application
calls stored procedures in dbmain, some of which in turn access tables
in dbslave, ie we have something like this:
create procedure some_sp_in_dbmain_called_by_app
as
select * from dbslave.dbo.someTable
Significantly, dbslave is only ever accessed in this way, ie via
dbmain.
The question is:
At what point does the SQLServer engine load information about dbslave
from disk? Presumably in order to fulfil the select query in the
example it needs to have schema information for dbslave at that point,
but is this information loaded on demand at the point of the call, or
is information on all databases loaded when the server is started?
I should explain that this question is motivated by a legal case I am
working on, where it is important that we establish the point at which
any part of dbslave is copied into RAM from disk. For this reason, any
links to pertinent documentation or papers would be particularly
welcome.
TIA for any help.
Some but generally not all the meta-data is loaded when the database is
attached when the sql server service starts - generally when the machine
boots. Given read-ahead, recovery, and shared extents it's pretty much
impossible to predict when a given piece of meta-data is loaded into memory.
If there are a lot of transactions that need to be recovered, a significant
portion of the schema information might be loaded at startup.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177016287.857608.294980@.q75g2000hsh.googlegr oups.com...
> I am hoping that someone with a good knowledge of the internals of
> SQLServer will be able to help with the following - admittedly bizarre
> - question (assume SQLServer 2000 throughout):
> We have two SQLServer databases, dbmain and dbslave. An application
> calls stored procedures in dbmain, some of which in turn access tables
> in dbslave, ie we have something like this:
> create procedure some_sp_in_dbmain_called_by_app
> as
> select * from dbslave.dbo.someTable
> Significantly, dbslave is only ever accessed in this way, ie via
> dbmain.
> The question is:
> At what point does the SQLServer engine load information about dbslave
> from disk? Presumably in order to fulfil the select query in the
> example it needs to have schema information for dbslave at that point,
> but is this information loaded on demand at the point of the call, or
> is information on all databases loaded when the server is started?
> I should explain that this question is motivated by a legal case I am
> working on, where it is important that we establish the point at which
> any part of dbslave is copied into RAM from disk. For this reason, any
> links to pertinent documentation or papers would be particularly
> welcome.
> TIA for any help.
>
|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.
|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.
|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.
|||Profuse apologies for the triplicate post - Google groups appears to
be a bit skittish today ...
|||First, you can't ignore recovery - a database goes through recovery every
time you start it. Second, there's no way to determine when a piece of
schema get loaded into memory. If a page from sysobjects is read to access
a table, it might have information on dozens of other tables on the page.
SQL also does read-ahead so accessing one page might cause a dozen to be
read into memory. I can't see what possible use this would be to anybody
anyway. Whether data is one disk or in memory is a pretty arbitrary
distinction that may be different every time a database starts. You could
detach the database when you shut down and only attach it when you need it
but that seems rather silly.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177076014.570168.111420@.p77g2000hsh.googlegr oups.com...
> On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
> wrote:
> Roger,
> Thanks very much for your reply. I understand that there isn't
> necessarily a simple answer to this, but if I may, let me see if I can
> paraphrase, assuming a simple startup scenario with no recovery:
> = When you start the sql server service, it attaches your databases
> (including master and (from my example) dbmain and dbslave;
> = For each database attached (still at startup time here) sql server
> reads *some* of the data from *some* of the sys- tables and uses this
> to build an internal in-memory representation of the database's schema
> (btw I'm guessing that master is in some senses a special case, but I
> am less interested in this).
> If I have this correct, is it possible to qualify the *some*s any
> further? For example, will it construct a representation of all tables
> using data from sysobjects et al, but delay examining stored procedure
> code from syscomments until they are actually accessed, something of
> that sort? Or is the algorithm more subtle than this?
> Many thanks again.
>
|||On 20 Apr, 15:50, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> First, you can't ignore recovery - a database goes through recovery every
> time you start it.
OK, I hadn't appreciated that.

> Second, there's no way to determine when a piece of
> schema get loaded into memory. If a page from sysobjects is read to access
> a table, it might have information on dozens of other tables on the page.
> SQL also does read-ahead so accessing one page might cause a dozen to be
> read into memory.
Of course. I should have realised this, and this is actually the
clincher.

> I can't see what possible use this would be to anybody
> anyway. Whether data is one disk or in memory is a pretty arbitrary
> distinction that may be different every time a database starts. You could
> detach the database when you shut down and only attach it when you need it
> but that seems rather silly.
I agree that the whole line of inquiry seems bizarre, but we are
labouring under a very precise definition of "copying" that does not
really apply very naturally to software, especially "meta-software"
such as a database schema, and the intention was to try and scope just
how much of the schema is "copied" from disk to memory. But from your
explanations above I can now see precisely how my original question
does not have a sensibly deterministic answer, so that's actually a
big help in and of itself. Many thanks for your help.

At what point is schema information loaded?

I am hoping that someone with a good knowledge of the internals of
SQLServer will be able to help with the following - admittedly bizarre
- question (assume SQLServer 2000 throughout):
We have two SQLServer databases, dbmain and dbslave. An application
calls stored procedures in dbmain, some of which in turn access tables
in dbslave, ie we have something like this:
create procedure some_sp_in_dbmain_called_by_app
as
select * from dbslave.dbo.someTable
Significantly, dbslave is only ever accessed in this way, ie via
dbmain.
The question is:
At what point does the SQLServer engine load information about dbslave
from disk? Presumably in order to fulfil the select query in the
example it needs to have schema information for dbslave at that point,
but is this information loaded on demand at the point of the call, or
is information on all databases loaded when the server is started?
I should explain that this question is motivated by a legal case I am
working on, where it is important that we establish the point at which
any part of dbslave is copied into RAM from disk. For this reason, any
links to pertinent documentation or papers would be particularly
welcome.
TIA for any help.Some but generally not all the meta-data is loaded when the database is
attached when the sql server service starts - generally when the machine
boots. Given read-ahead, recovery, and shared extents it's pretty much
impossible to predict when a given piece of meta-data is loaded into memory.
If there are a lot of transactions that need to be recovered, a significant
portion of the schema information might be loaded at startup.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177016287.857608.294980@.q75g2000hsh.googlegroups.com...
> I am hoping that someone with a good knowledge of the internals of
> SQLServer will be able to help with the following - admittedly bizarre
> - question (assume SQLServer 2000 throughout):
> We have two SQLServer databases, dbmain and dbslave. An application
> calls stored procedures in dbmain, some of which in turn access tables
> in dbslave, ie we have something like this:
> create procedure some_sp_in_dbmain_called_by_app
> as
> select * from dbslave.dbo.someTable
> Significantly, dbslave is only ever accessed in this way, ie via
> dbmain.
> The question is:
> At what point does the SQLServer engine load information about dbslave
> from disk? Presumably in order to fulfil the select query in the
> example it needs to have schema information for dbslave at that point,
> but is this information loaded on demand at the point of the call, or
> is information on all databases loaded when the server is started?
> I should explain that this question is motivated by a legal case I am
> working on, where it is important that we establish the point at which
> any part of dbslave is copied into RAM from disk. For this reason, any
> links to pertinent documentation or papers would be particularly
> welcome.
> TIA for any help.
>|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memor
y.
> If there are a lot of transactions that need to be recovered, a significan
t
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memor
y.
> If there are a lot of transactions that need to be recovered, a significan
t
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memor
y.
> If there are a lot of transactions that need to be recovered, a significan
t
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||Profuse apologies for the triplicate post - Google groups appears to
be a bit skittish today ...|||First, you can't ignore recovery - a database goes through recovery every
time you start it. Second, there's no way to determine when a piece of
schema get loaded into memory. If a page from sysobjects is read to access
a table, it might have information on dozens of other tables on the page.
SQL also does read-ahead so accessing one page might cause a dozen to be
read into memory. I can't see what possible use this would be to anybody
anyway. Whether data is one disk or in memory is a pretty arbitrary
distinction that may be different every time a database starts. You could
detach the database when you shut down and only attach it when you need it
but that seems rather silly.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177076014.570168.111420@.p77g2000hsh.googlegroups.com...
> On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
> wrote:
> Roger,
> Thanks very much for your reply. I understand that there isn't
> necessarily a simple answer to this, but if I may, let me see if I can
> paraphrase, assuming a simple startup scenario with no recovery:
> = When you start the sql server service, it attaches your databases
> (including master and (from my example) dbmain and dbslave;
> = For each database attached (still at startup time here) sql server
> reads *some* of the data from *some* of the sys- tables and uses this
> to build an internal in-memory representation of the database's schema
> (btw I'm guessing that master is in some senses a special case, but I
> am less interested in this).
> If I have this correct, is it possible to qualify the *some*s any
> further? For example, will it construct a representation of all tables
> using data from sysobjects et al, but delay examining stored procedure
> code from syscomments until they are actually accessed, something of
> that sort? Or is the algorithm more subtle than this?
> Many thanks again.
>|||On 20 Apr, 15:50, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> First, you can't ignore recovery - a database goes through recovery every
> time you start it.
OK, I hadn't appreciated that.

> Second, there's no way to determine when a piece of
> schema get loaded into memory. If a page from sysobjects is read to acces
s
> a table, it might have information on dozens of other tables on the page.
> SQL also does read-ahead so accessing one page might cause a dozen to be
> read into memory.
Of course. I should have realised this, and this is actually the
clincher.

> I can't see what possible use this would be to anybody
> anyway. Whether data is one disk or in memory is a pretty arbitrary
> distinction that may be different every time a database starts. You could
> detach the database when you shut down and only attach it when you need it
> but that seems rather silly.
I agree that the whole line of inquiry seems bizarre, but we are
labouring under a very precise definition of "copying" that does not
really apply very naturally to software, especially "meta-software"
such as a database schema, and the intention was to try and scope just
how much of the schema is "copied" from disk to memory. But from your
explanations above I can now see precisely how my original question
does not have a sensibly deterministic answer, so that's actually a
big help in and of itself. Many thanks for your help.

At what point is schema information loaded?

I am hoping that someone with a good knowledge of the internals of
SQLServer will be able to help with the following - admittedly bizarre
- question (assume SQLServer 2000 throughout):
We have two SQLServer databases, dbmain and dbslave. An application
calls stored procedures in dbmain, some of which in turn access tables
in dbslave, ie we have something like this:
create procedure some_sp_in_dbmain_called_by_app
as
select * from dbslave.dbo.someTable
Significantly, dbslave is only ever accessed in this way, ie via
dbmain.
The question is:
At what point does the SQLServer engine load information about dbslave
from disk? Presumably in order to fulfil the select query in the
example it needs to have schema information for dbslave at that point,
but is this information loaded on demand at the point of the call, or
is information on all databases loaded when the server is started?
I should explain that this question is motivated by a legal case I am
working on, where it is important that we establish the point at which
any part of dbslave is copied into RAM from disk. For this reason, any
links to pertinent documentation or papers would be particularly
welcome.
TIA for any help.Some but generally not all the meta-data is loaded when the database is
attached when the sql server service starts - generally when the machine
boots. Given read-ahead, recovery, and shared extents it's pretty much
impossible to predict when a given piece of meta-data is loaded into memory.
If there are a lot of transactions that need to be recovered, a significant
portion of the schema information might be loaded at startup.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177016287.857608.294980@.q75g2000hsh.googlegroups.com...
> I am hoping that someone with a good knowledge of the internals of
> SQLServer will be able to help with the following - admittedly bizarre
> - question (assume SQLServer 2000 throughout):
> We have two SQLServer databases, dbmain and dbslave. An application
> calls stored procedures in dbmain, some of which in turn access tables
> in dbslave, ie we have something like this:
> create procedure some_sp_in_dbmain_called_by_app
> as
> select * from dbslave.dbo.someTable
> Significantly, dbslave is only ever accessed in this way, ie via
> dbmain.
> The question is:
> At what point does the SQLServer engine load information about dbslave
> from disk? Presumably in order to fulfil the select query in the
> example it needs to have schema information for dbslave at that point,
> but is this information loaded on demand at the point of the call, or
> is information on all databases loaded when the server is started?
> I should explain that this question is motivated by a legal case I am
> working on, where it is important that we establish the point at which
> any part of dbslave is copied into RAM from disk. For this reason, any
> links to pertinent documentation or papers would be particularly
> welcome.
> TIA for any help.
>|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> Some but generally not all the meta-data is loaded when the database is
> attached when the sql server service starts - generally when the machine
> boots. Given read-ahead, recovery, and shared extents it's pretty much
> impossible to predict when a given piece of meta-data is loaded into memory.
> If there are a lot of transactions that need to be recovered, a significant
> portion of the schema information might be loaded at startup.
>
Roger,
Thanks very much for your reply. I understand that there isn't
necessarily a simple answer to this, but if I may, let me see if I can
paraphrase, assuming a simple startup scenario with no recovery:
= When you start the sql server service, it attaches your databases
(including master and (from my example) dbmain and dbslave;
= For each database attached (still at startup time here) sql server
reads *some* of the data from *some* of the sys- tables and uses this
to build an internal in-memory representation of the database's schema
(btw I'm guessing that master is in some senses a special case, but I
am less interested in this).
If I have this correct, is it possible to qualify the *some*s any
further? For example, will it construct a representation of all tables
using data from sysobjects et al, but delay examining stored procedure
code from syscomments until they are actually accessed, something of
that sort? Or is the algorithm more subtle than this?
Many thanks again.|||Profuse apologies for the triplicate post - Google groups appears to
be a bit skittish today ...|||First, you can't ignore recovery - a database goes through recovery every
time you start it. Second, there's no way to determine when a piece of
schema get loaded into memory. If a page from sysobjects is read to access
a table, it might have information on dozens of other tables on the page.
SQL also does read-ahead so accessing one page might cause a dozen to be
read into memory. I can't see what possible use this would be to anybody
anyway. Whether data is one disk or in memory is a pretty arbitrary
distinction that may be different every time a database starts. You could
detach the database when you shut down and only attach it when you need it
but that seems rather silly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<dduv@.hotmail.com> wrote in message
news:1177076014.570168.111420@.p77g2000hsh.googlegroups.com...
> On 19 Apr, 22:33, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
> wrote:
>> Some but generally not all the meta-data is loaded when the database is
>> attached when the sql server service starts - generally when the machine
>> boots. Given read-ahead, recovery, and shared extents it's pretty much
>> impossible to predict when a given piece of meta-data is loaded into
>> memory.
>> If there are a lot of transactions that need to be recovered, a
>> significant
>> portion of the schema information might be loaded at startup.
> Roger,
> Thanks very much for your reply. I understand that there isn't
> necessarily a simple answer to this, but if I may, let me see if I can
> paraphrase, assuming a simple startup scenario with no recovery:
> = When you start the sql server service, it attaches your databases
> (including master and (from my example) dbmain and dbslave;
> = For each database attached (still at startup time here) sql server
> reads *some* of the data from *some* of the sys- tables and uses this
> to build an internal in-memory representation of the database's schema
> (btw I'm guessing that master is in some senses a special case, but I
> am less interested in this).
> If I have this correct, is it possible to qualify the *some*s any
> further? For example, will it construct a representation of all tables
> using data from sysobjects et al, but delay examining stored procedure
> code from syscomments until they are actually accessed, something of
> that sort? Or is the algorithm more subtle than this?
> Many thanks again.
>|||On 20 Apr, 15:50, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> First, you can't ignore recovery - a database goes through recovery every
> time you start it.
OK, I hadn't appreciated that.
> Second, there's no way to determine when a piece of
> schema get loaded into memory. If a page from sysobjects is read to access
> a table, it might have information on dozens of other tables on the page.
> SQL also does read-ahead so accessing one page might cause a dozen to be
> read into memory.
Of course. I should have realised this, and this is actually the
clincher.
> I can't see what possible use this would be to anybody
> anyway. Whether data is one disk or in memory is a pretty arbitrary
> distinction that may be different every time a database starts. You could
> detach the database when you shut down and only attach it when you need it
> but that seems rather silly.
I agree that the whole line of inquiry seems bizarre, but we are
labouring under a very precise definition of "copying" that does not
really apply very naturally to software, especially "meta-software"
such as a database schema, and the intention was to try and scope just
how much of the schema is "copied" from disk to memory. But from your
explanations above I can now see precisely how my original question
does not have a sensibly deterministic answer, so that's actually a
big help in and of itself. Many thanks for your help.sql

Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [the
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1> Are the above assumptions true?
Yes and yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index columns
> (in this case, c3) are appended to the end of nonclustered index columns as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.sql

Association Rules Model for problem

What would be the right design approach for the following problem?

I have a single table called SelectionFactors, which has the following columns and sample data:

ProjectID Factor FactorValue 1000 Countries USA 1000 Countries Canada 1000 Countries France 1000 Languages English 1000 Languages French 1000 Company Type Consulting 1000 Company Type Software 2000 Countries India 2000 Countries China 2000 Countries USA 2000 Languages English 2000 Languages Chinese (Simplified) 2000 Languages Chinese (Traditional) 2000 Languages Spanish 2000 Company Type Retail 2000 Company Type Dairy Products

The problem is to allow a descriptive analysis of the data to find patterns in the users selections. For instance,

if Languages->English is selected, what are the counts of projects for other Factor->Factor Value combinations?

Countries->USA = 2, Countries->Canada=1, Company Type->Consulting=1 and so on.

Since all the data is in this single table, are both the case and nested tables the same? What are the keys and inputs? I only need a descriptive analysis (no prediction) and ALL possible combinations MUST be part of the results; how should the model be designed?

Thank you,

Anna.

This is a good problem. What you want to do is to create a model with three nested tables all marked predict and input. Event though you want descriptive analysis, you need to mark them "predict" such that rules will be formed (only items marked predict will show on the right hand side of rules).

The way you will create such a model is to use the "Named Query" feature of the data source view(DSV). You can right-click on the DSV and select "New Named Query" to create these.

The named queries you will want to create are these:

Projects: SELECT DISTINCT ProjectID FROM SelectionFactors

Companies: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Companies'

Languages: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Languages'

Company Type: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Company Type'

You will then need to relate the ProjectID in each of the transaction named queries (Companies, etc) to the ProjectID in the Projects named query. The system will prompt you to set ProjectID as the key of the Projects named query . At this point, the named queries are equivalent to tables as far as Analysis Services is concerned.

When you create the model, you will make the Projects table the case table and the other tables nested tables. ProjectID will be the key of the Projects table and FactorValue will be the key of the other tables. Make sure to mark FactorValue as Key, Input, and Output.

When you process your model you will see rules relating projects, companies, and company types (both between factos and in the same factor).

Note that you may need to adjust the MINIMUM_SUPPORT and MINIMUM_PROBABILITY parameters to get the results you need (in fact, it's recommended)

HTH

-Jamie

|||

Thanks for your response.

However, I should have mentioned that there is an open-ended number of "Factors". New factors are added often and the number of factors is nearly 100.

The type of questions I am trying to answer using this model is:

"Among projects that have Languages-> English, how many (distinct projects) have Company Type->Consulting, Countries->USA" and so on.

Ideally, I would like to provide this analysis in a free, OLAP environment where the user can select one factor-> factor value combination and then see all the other related combinations.

I currently have a Cube with SelectionFactors serving as both the Fact and the dimension table with a single measure - DISTINCT COUNT of projectIDs.

Thanks once again.

|||

Given the information in my previous post, what are my design options?

Thanks.

|||

My question for you, then, is if you have a cube for this, what are you not getting that you need?

If the factor/factorvalue can be viewed as a complete unit, and you don't need to provide analysis between factors - e.g. given Language X, which Countries and Company Types are prevalent, you could create a calculated column in the DSV that combines the factor and factor value.

|||

Perhaps you can help me understand why I am unable to get the expected results. I am fairly new to OLAP and Data Mining and would appreciate any pointers and guidance.

The full picture of my problem is:

Dimension Project Profile - Project ID, (Other Project related attributes)

Dimension Selection Factors - Factor, Factor Value

Fact Selection Factors - Project ID, Factor, Factor Value

Project Cube Measure - DISTINCT COUNT of Project IDs

My understanding is that this is a problem of intersection of factors, which I cannot achieve with a single dimension. In fact there is this other thread http://forums.microsoft.com/msdn/showpost.aspx?postid=856304&siteid=1 that seems to come very close to my problem.

In my case, the following MDX returns the projects that have the intersection of specific factor values.

SELECT {[Measures].[Project Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

ON ROWS

FROM PROJECTCUBE

The questions are:

1. How do I get all the other Factor->Factor Values that are part of the selection factors for the projects returned by this MDX. This, if I understand correctly, is Basket analysis.

and

2. Is there a generic way to perform this analysis through cube design rather than writing MDX for each case?

Thanks once more.|||

The following MDX gives me the contents of the "baskets" of projects that have the selected items. However, the counts of the projects is not restricted to the subset retrieved through the Intersect. Is there a way to get the counts to only be within the subset returned by the Intersect?

With this MDX, I can get very close to what I need. The only thing I am missing is to get the counts (perhaps through a calculated measure). Help on this will be much appreciated!

__

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTY

(

([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children),

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

ON ROWS

FROM PROJECTCUBE

|||

In order to obtain counts restricted to the subset returned by the Intersect, this MDX takes the constraint out of the ROWS into the WHERE clause.

I would still like to know how to design an Association rules model that can "automate" the generation of results by case. Hopefully, the MDX can point directly to the expected results.

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children)

ON ROWS

FROM PROJECTCUBE

WHERE

NONEMPTY(

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

sql

Associating Data from Other Tables

Morning, all.

I have an input record that contains the following fields:

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

And I have a table, that contains the following fields

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

Okay. The kicker here is that in the table, the header_id's are already defined, but the details are blank. I need to insert the details where the header_id's of the input equal the header_id's of the table.

Is there a loop or something? I can *almost* see it in my head if I was just writing a SQL Query, but it involves a cursor, and I'm hoping SSIS will save me some of the aggravation...

Any ideas?
Isn't this just an update?

UPDATE TABLE
SET header_detail_1 = input_header_detail1,
header_detail_2 = input_header_detail2
WHERE header_id_1 = input_header_id_1
AND header_id_2 = input_header_id_2
AND header_id_3 = input_header_id_3

You could achieve this with an OLE DB Command transformation.|||Oh, hey! Yeah! Thanks!

Jim Work
|||You could also load the flat file (or whatever source you may have) to a staging table and then issue a batch update -- which would be faster than using the OLE DB Command to perform your updates...

To perform the batch update inside SSIS, you'd use an Execute SQL task in the control flow after you've loaded the staging table with a data flow.

Inside that Execute SQL task, your query would be something like:

UPDATE destination_table a, staging_table b
SET a.column1 = b.column1, a.column2 = b.column2
WHERE a.key1 = b.key1
AND a.key2 = b.key2
AND a.key3 = b.key3

It's real similar to the above query, except that you're using native SQL to perform the update rather than SSIS.

Assistance with Stored Procedure and ASPX Page Needed

Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind?

Stored Procedure

CREATE PROCEDURE InsertNearMiss @.Branch Int, @.Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@.Branch)

SET @.Identity = SCOPE_IDENTITY()

GO

ASPX Page

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NearMissConnectionString%>" InsertCommand="InsertRecord"InsertCommandType="StoredProcedure"SelectCommand="InsertRecord" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameterControlID="TextBox1"Name="Branch"PropertyName="Text"Type="Int32"/> <asp:ControlParameterControlID="TextBox2"Direction="InputOutput"Name="Identity"PropertyName="Text"Type="Int32"/> </SelectParameters> <InsertParameters> <asp:ParameterName="Branch"Type="Int32"/> <asp:ParameterDirection="InputOutput"Name="Identity"Type="Int32"/> </InsertParameters> </asp:SqlDataSource> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

Here's all about getting the value of the most recently added record:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

SqlDataSource options are about 3/4 of the way down.

|||

It appears you are only doing an insert, so you can get rid of the whole <selectParameters> collection. In this case, I would create a handler for onInserted and have the handler insert the output value from the procedure into the textbox 2 box. To fire the event, put a button that has an onclick event handler that calls SqlDataSource1.Insert(). This fires the insert command of the datasource. Below is an example page and codebehind

12 id="testSource"3runat="server"4InsertCommand="usp_ins_test"5InsertCommandType="StoredProcedure"6ConnectionString='<%$ ConnectionStrings:test %>'7OnInserted="testSource_Inserted">89"TextBox1" Name="prm_b" Direction="Input" Type="int32" PropertyName="Text">10"prm_r" Direction="Output" Type="Int32">111213branch: "TextBox1" runat="server">
14returnedvalue: "TextBox2" runat="server">15"btnSubmit" runat="server" Text="Add New Branch" OnClick="btnSubmit_Click"/>

 Code Behind:

 
protected void btnSubmit_Click(object sender, EventArgs e) {if (Page.IsValid) { testSource.Insert(); } }protected void testSource_Inserted(object sender, SqlDataSourceStatusEventArgs e) {if (e.Exception !=null)//display an error message to the screen e.ExceptionHandled =true;else TextBox2.Text = e.Command.Parameters["@.prm_r"].Value.ToString(); }
 

That should do it.

--D

Assistance with a query

I have 2 tables POheader and POline. I am running the following SQL
statement but am getting 2 records where as I should only be getting one.
Could someone please assist.
s_commar@.hotmail.com
select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
POline.Ponbr and POline.ponbr
= 16916;
I should just be getting one record but I get 2 records. Could someone
advise as to what I am doing wrong.
Thanks
s_commar@.hotmail.comIts hard to comment without seeing DDL+Sample data. My best
guess is that your POline table has 2 matching rows. If you dont want to
reference any columns from the POnline table in the final output,
you can probably rewrite your query like
Select POheader.ponbr from POheader
WHERE EXISTS(
SELECT 1 FROM POline WHERE POheader.Ponbr = POline.Ponbr
AND POline.ponbr = 16916)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"s commar" <scommar@.verizon.net> wrote in message
news:36iJe.1262$D4.785@.trndny07...
>I have 2 tables POheader and POline. I am running the following SQL
>statement but am getting 2 records where as I should only be getting one.
>Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>|||"s commar" schrieb:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
select POheader.ponbr
from POheader
inner join POline
on POheader.Ponbr = POline.Ponbr
WHERE POline.ponbr = 16916|||Since u hv 2 matching rows in the POLine table against the row in the
POHeader table, u r getting 2 records. Try below query
select DISTINCT POheader.ponbr from POheader inner join POline on
POheader.Ponbr = POline.Ponbr and POline.ponbr= 16916;
Rakesh
"s commar" wrote:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>
>

assigning values to fields

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, 1, Desc1
Cust1, 2, Desc1
Cust1, 2, Desc2
How would you make those null values 3's or the MAX(Inv#) for Cust1?
thanks,
rodcharIf this is your entire table, you might need to clean it up to include a
key. Otherwise,
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# ) + 1
FROM tbl t1 )
WHERE Inv# IS NULL ;
Anith|||Try:
SELECT NAME, ISNULL(INV,3), DESCCOL
FROM YOURTABLE
OR
SELECT NAME, ISNULL(INV,(SELECT MAX(INV)FROM YOURTABLE)), DESCCOL
FROM YOURTABLE
OR
DECLARE @.VAL INT
SELECT @.VAL = MAX(INV) FROM YOURTABLE
SELECT NAME, ISNULL(INV,@.VAL), DESCCOL
FROM YOURTABLE
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||UPDATE YourTable
SET [Inv#] =
(
SELECT MAX([Inv#])
FROM YourTable Y1
WHERE Y1.Name = YourTable.Name
)
WHERE YourTable.[Inv#] IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||3 things for everyone:
1st: Thanks for the great replies
2nd:
UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;
This worked for me but what i thought would happen with this is that once it
updated the first null and made it a 3 the 2nd null would become a 4. Can
anyone explain please?
3rd:
This same query didn't work in an access database it said that this wasn't
an updateable query. any ideas?
thanks again,
rodchar
"Anith Sen" wrote:

> If this is your entire table, you might need to clean it up to include a
> key. Otherwise,
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# ) + 1
> FROM tbl t1 )
> WHERE Inv# IS NULL ;
> --
> Anith
>
>|||>> This worked for me but what i thought would happen with this is that once
I am not sure what to explain, since the code is simple and clear. In case,
you are looking for sequentially incrementing value to replace the NULLs,
you'd have to use a "ranking" mechanism like:
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# )
FROM tbl t1 ) +
( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Name = tbl.Name
AND t1.Descr <= tbl.Descr
AND t1.Inv# IS NULL )
FROM tbl
WHERE Inv# IS NULL ;
Depending on the ranking variations you need, you'll have to adjust the
correlations in the subquery using COUNT(*).
Access has different updateability rules and different UDPATE dialect than
SQL Server. If you are using MS Access, consider posting this question in an
Access forum.
Anith|||i'm sorry for not being clear. your code posting is exactly what i needed. i
was just curious why it didn't make the nulls sequential. Because once it
makes the first null record a 3 wouldn't that be the new MAX(InvNo), and in
turn making the last null value a 4.
just trying to understand how the engine thinks and works. this helped a lot
.
"Anith Sen" wrote:

> I am not sure what to explain, since the code is simple and clear. In case
,
> you are looking for sequentially incrementing value to replace the NULLs,
> you'd have to use a "ranking" mechanism like:
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# )
> FROM tbl t1 ) +
> ( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Name = tbl.Name
> AND t1.Descr <= tbl.Descr
> AND t1.Inv# IS NULL )
> FROM tbl
> WHERE Inv# IS NULL ;
> Depending on the ranking variations you need, you'll have to adjust the
> correlations in the subquery using COUNT(*).
>
> Access has different updateability rules and different UDPATE dialect than
> SQL Server. If you are using MS Access, consider posting this question in
an
> Access forum.
> --
> Anith
>
>|||>> i was just curious why it didn't make the nulls sequential.
With no correlation, the value is generated only once for the entire
dataset. With a correlation, the values are generated for each matching row
in the dataset.
No problem.
Anith|||thank you everyone for the help. this has been very productive.
"Anith Sen" wrote:

> With no correlation, the value is generated only once for the entire
> dataset. With a correlation, the values are generated for each matching ro
w
> in the dataset.
>
> No problem.
> --
> Anith
>
>|||On Fri, 14 Oct 2005 10:09:04 -0700, ari wrote:

>2nd:
>UPDATE Transactions
>SET InvNo = ( SELECT MAX( InvNo ) + 1
>FROM Transactions t1 )
>WHERE InvNo IS NULL ;
>This worked for me but what i thought would happen with this is that once i
t
>updated the first null and made it a 3 the 2nd null would become a 4. Can
>anyone explain please?
Hi ari,
In SQL, all operations are done "at once". At least in theory. In
practice, they will eventuelly, somewhere deep in the engine, be
processed one row at a time, but the DB should behave as if the complete
statement is executed at once.
That's why you can swap columns without temp storage to hold the old
value, like you would in procedural languages:
UPDATE SomeTable
SET A = B,
B = A
WHERE ...
The right-hand B and A both refer to the "old" values (before the
update). The DB can process this internally ion any order it wants, as
long as the result looks as if it was all executed at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

assigning values

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust2, null, Desc1
Cust2, null, Desc1
Cust3, null, Desc2
How would I make it:
Cust1, 1, Desc1
Cust1, 1, Desc2
Cust2, 2, Desc1
Cust2, 2, Desc1
Cust3, 3, Desc2
thanks,
rodcharselect name,right(name,1),Desc
from table
This will work for this example
http://sqlservercode.blogspot.com/
"rodchar" wrote:

> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust2, null, Desc1
> Cust2, null, Desc1
> Cust3, null, Desc2
> How would I make it:
> Cust1, 1, Desc1
> Cust1, 1, Desc2
> Cust2, 2, Desc1
> Cust2, 2, Desc1
> Cust3, 3, Desc2
>
> thanks,
> rodchar|||the invoice numbers don't come from the NAME field.
it should find the MAX(InvoiceID) and assign it to all transactions for a
single customer that have a NULL value, then for the next set of transaction
s
for the next customer the Invoice number should be incremented by 1.
CustA, 1, Desc1
CustA, 1, Desc2
CustB, 2, Desc1
CustB, 2, Desc1
CustC, 3, Desc2
"SQL" wrote:
> select name,right(name,1),Desc
> from table
> This will work for this example
> http://sqlservercode.blogspot.com/
> "rodchar" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor -- the keys have nothing whatsoever to do with the display in
the front end.
Let me fix those horrible data element names with some guesses. The
only possible key is (cust_name, item_description), while silly me, I
would have thought that invoice_nbr would be unique in an invoice
table.
What you posted does not make any sense.|||Sorry about that.
Alright, let me give a quick 30,000 ft view because my logic could be off
(it has been before many times.)
I'm trying generate invoices from a transactions table. so here's what i do.
i enter all the transactions for the month for all the customers. when
invoice time comes around here are my steps:
1. I assign invoice numbers to each record in the transactions table where
InvoiceID IS NULL.
2. Then I insert the records into the invoice headers table and invoice
details table.
Transactions table:
rowID int primary key
CustomerID int
InvoiceID int
TransactionType (bill,payment)
Cost money
so my records look like this
1, 122, null, bill, $20
2, 122, null, bill, $20
3, 105, null, bill, $20
4, 101, null, bill, $20
5, 102, null, bill, $20
now if my logic is sound
my transactions table will look like the following after assigning invoice
numbers to them:
1, 122, 1, bill, $20
2, 122, 1, bill, $20
3, 105, 2, bill, $20
4, 101, 3, bill, $20
5, 102, 4, bill, $20
Please advise. Is there a more clearer way to do this whole process that i'm
missing.
thanks,
rodchar
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to havs a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor -- the keys have nothing whatsoever to do with the display in
> the front end.
> Let me fix those horrible data element names with some guesses. The
> only possible key is (cust_name, item_description), while silly me, I
> would have thought that invoice_nbr would be unique in an invoice
> table.
> What you posted does not make any sense.
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:
> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:
> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:
> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> I have the following:
>> schema: xproject
>> Database Role: WebPublisher
>> User: MyUserHandle
>> sproc: xproject.MyProcedure
>> I added the role "WebPublisher" to the schema "xproject" with
>> Delete
>> Execute
>> Insert
>> Select
>> Update
>> I added the user "MyUserHandle" to the database role "WebPublisher"
>>
>> When I tried to execute the stored procedure from an ASP.NET page, I got
>> the
>> error
>> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
>> schema 'xproject'
> This would imply that the user is not actually a member of the role. See
> below:
>> However, when I added the user to the schema and gave it the same
>> permissions as the database role, the page rendered.
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>> My question is why do I have to assign the user to the schema when the
>> database role already has the user?
> No, see below:
>> Thanks, sck10
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assign XML to variable

How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?

I know that the following code works....

select * from tblUsers where userId = 1225 for XML raw

It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."

declare @.strXml nvarchar(1000)

set @.strXml = (select * from tblUsers where userId = 1225 for XML raw)

Ideas?

It is not possible to do this in SQL Server 2000 using TSQL. You cannot consume the XML output on the server-side in any form. This is possible in SQL Server 2005 since there is a native XML data type and there are lot of improvements to FOR XML clause.