Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fact Table SQL Query - adding a prefix to a value

Hi there, I have a question regarding a query to extract measures from a fact table. The fact table from the source system contains delivery notes with turnover in one dataset. The primary key is the delivery note number plus a consecutive number. The same table contains also the cancellations of the delivery note with the same turnover and the same primary key as the delivery note, only differed by another consecutive number. The cancellation is represented by another column which contains either a "0" or a "1" (cancellation no/yes).

The problem is that I'd like to change the turnover value for cancellations with a prefix instead of using another column in the fact table. Cause then I'd be able to group those numbers...

Is there a SQL function that allows me to

Example datasets:

Code Snippet

ConsNo DelNNo Canc Turnover Amount

001 200 0 1000 500

002 200 1 1000 500

I'd like to achieve this output by a query:

Code Snippet

ConsNo DelNNo Turnover Amount

001 200 1000 500

002 200 -1000 -500

Any ideas? I've heard of a function called "decode" but I think it doesn't work in SQL Server 2005...

Just create a view that depending on the Canc column multiplies the Turnover and Amount by 1 or -1.|||But how to use IF-statement and mathematical functions within a SELECT-statement? That would be T-SQL right?
|||

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

|||

Tiago Rente wrote:

In SQL you can do something like:

select ConsNo,

DelNNo,

Turnover = case

when Canc = 0

then Turnover

else Turnover * -1

end,

Amount = case

when Canc = 0

then Amount

else Amount * -1

end

from <table name>

There is also the possibility to use isnull and nullif to simulate the previous cases.

It works, but when I add the GROUP BY function I always get an error message that "Canc" and "Turnover" aren't groupable!

Surprisingly I didn't even select the "Canc"-attribute, and I don't use Turnover within the GROUP-function.

|||

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

|||

Tiago Rente wrote:

Unfortunately with this solution you have to copy the case statement to the group by section. In Sybase IQ you could use the name of the column in the group by without repeting the code.

In SQL Server the other option is to create a User Define Function that receives 2 columns (Canc, Turnover) or (Canc, Amount) and returns the correct value. This way you still need to copy the call to the UDF in the group by, but is less error prune. However, this will cost you in performance, since the UDF will be executed for each row in the table (as if you had open a cursor ).

Or you can create a view and then do the group by to the result of the view, this way you do not need to repeat the case or UDF in the group by since you already have a column name to do the group by.

I tried to use the case-statement in the GROUP BY function but the result is the same. Weird...

|||

Summing the case statements should do the trick.

Code Snippet

select ConsNo,

DelNNo,

Turnover = SUM(case

when Canc = 0

then Turnover

else Turnover * -1

end),

Amount = SUM(case

when Canc = 0

then Amount

else Amount * -1

end)

from <table name>

GROUP BY

ConsNo

, DelNo

Extracting XSD Collection

I've got a really quick - and I'm sure easy - question...

I'm wondering is there any way to extract an XSD Schema collection so that it appears as a well formed XML document?

The only thing I've seen is if you right click on a sche,a collection, you can either script it as a CREATE or a DROP. The CREATE script has no formatting whatsoever - it's all one one line.

If anyone has done this or knows of an effective way to accomplish this, your help would be greatly appreciated!

Thanks!

SELECT xml_schema_namespace('db_SchemaName','XmlSchemaCollectionName')

e.g.: SELECT xml_schema_namespace('dbo','MyXmlSchema')

|||Thanks Phe....!!

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract all
the data into a table. Can anyone give me some tips on the easiest way to do
this. The data is written to the table and will be extracted in real time.
Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.
|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column in
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract al
l
the data into a table. Can anyone give me some tips on the easiest way to d
o
this. The data is written to the table and will be extracted in real time.Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column i
n
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting XML data from SQL 2000 to SQL 2005 database table.

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

VDeevi wrote:

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

What methods have you tried so far and why do they fail?

-Jamie|||Hi Jamie,

Thanks for your reply, We tried with OPENXML, sp_xml_prepareddocument etc. but they are all round about things we want to know is there any simple technique in SSIS in order to retrieve the data from XML stored in SQL Server 2000 TEXT field.

Thanks,|||I've just had a go at this...

When you select the data a TEXT field appears in the SSIS pipeline as a DT_TEXT. To insert it into a XML field use a derived column transform to convert it to DT_NTEXT.
If you don't like DT_NTEXTs in your pipeline then convert it to DT_WSTR and then to DT_STR which can also be inserted into an XML field.

So yes, SSIS can do this very very well.

-Jamie

Extracting Users for restore

I'm refreshing (restoring) a database and would like to keep the users from
the original database. Is there a efficient way to extract users, restore
the database and then re-apply the users. I'm not talking about logins as
I'm familiar with sp_help_revlogin.
Thanks.
RonThe Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||Ron,
You can get the information from the database prior to the restore and save
it somewhere for scripting back into the database later.
For SQL 2000:
sp_helpuser
sp_helprotect
Although these still work in SQL 2005, they miss some securables. You can
also look at the new dynamic management views. Start by examining:
sys.database_permissions
sys.database_principals
sys.database_role_members
Here are a couple of queries that may help you get started.
-- Permissions
select u.name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
-- Role Memberships
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u
on m.role_principal_id = u.principal_id
join sys.database_principals u2
on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from a
database, then 2) have that database refreshed from another databases, then
3) re-apply the users from step 1.
"Andrew J. Kelly" wrote:

> The Users stay with the db when you back it up and will be there after the
> restore.You may need to remap the login to the users an can do that with
> sp_change_users_login. Here is a bunch of info related to that topic.
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Movi
ng
> system dbs 2005
> http://www.databasejournal.com/feat...cle.php/3379901 Movin
g
> system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Resto
re
> http://www.sqlservercentral.com/col...
ses.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs afte
r
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scr...utions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
>
>|||You can script the users with Enterprise Manager. Right click on the db and
choose All tasks - Generate SQL Scripts. One of the tabs has an option to
script users and permissions for the db.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...[vbcol=seagreen]
> No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> a
> database, then 2) have that database refreshed from another databases,
> then
> 3) re-apply the users from step 1.
> "Andrew J. Kelly" wrote:
>|||Thanks Andrew - it seems to work fine in SQL2000. However in SQL2005, I
don't see where the database roles get extracted.
For example here's the output from SQL2000 extracting users and database
roles:
if not exists (select * from dbo.sysusers where name = N'abrown')
EXEC sp_grantdbaccess N'abrown'
GO
exec sp_addrolemember N'db_datareader', N'abrown'
GO
exec sp_addrolemember N'db_datawriter', N'abrown'
GO
Here's the output from SQL2005 extracting users and schemas (no mention of
extracint db roles):
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'abrown')
EXEC sys.sp_executesql N'CREATE SCHEMA [abrown] AUTHORIZATION [abrow
n]'
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'abrown')
CREATE USER [abrown] FOR LOGIN [abrown] WITH DEFAULT_SCHEMA=[abr
own]
GO
I don't see how in SQL2005 users get added to datareader?
Thanks
Ron
"Andrew J. Kelly" wrote:

> You can script the users with Enterprise Manager. Right click on the db an
d
> choose All tasks - Generate SQL Scripts. One of the tabs has an option to
> script users and permissions for the db.
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
>
>

Extracting Users for restore

I'm refreshing (restoring) a database and would like to keep the users from
the original database. Is there a efficient way to extract users, restore
the database and then re-apply the users. I'm not talking about logins as
I'm familiar with sp_help_revlogin.
Thanks.
RonThe Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||Ron,
You can get the information from the database prior to the restore and save
it somewhere for scripting back into the database later.
For SQL 2000:
sp_helpuser
sp_helprotect
Although these still work in SQL 2005, they miss some securables. You can
also look at the new dynamic management views. Start by examining:
sys.database_permissions
sys.database_principals
sys.database_role_members
Here are a couple of queries that may help you get started.
-- Permissions
select u.name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
-- Role Memberships
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u
on m.role_principal_id = u.principal_id
join sys.database_principals u2
on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron|||No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from a
database, then 2) have that database refreshed from another databases, then
3) re-apply the users from step 1.
"Andrew J. Kelly" wrote:
> The Users stay with the db when you back it up and will be there after the
> restore.You may need to remap the login to the users an can do that with
> sp_change_users_login. Here is a bunch of info related to that topic.
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
> system dbs 2005
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> > I'm refreshing (restoring) a database and would like to keep the users
> > from
> > the original database. Is there a efficient way to extract users, restore
> > the database and then re-apply the users. I'm not talking about logins as
> > I'm familiar with sp_help_revlogin.
> >
> > Thanks.
> >
> > Ron
>
>|||You can script the users with Enterprise Manager. Right click on the db and
choose All tasks - Generate SQL Scripts. One of the tabs has an option to
script users and permissions for the db.
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
> No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> a
> database, then 2) have that database refreshed from another databases,
> then
> 3) re-apply the users from step 1.
> "Andrew J. Kelly" wrote:
>> The Users stay with the db when you back it up and will be there after
>> the
>> restore.You may need to remap the login to the users an can do that with
>> sp_change_users_login. Here is a bunch of info related to that topic.
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
>> Moving
>> system dbs 2005
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's 2000
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
>> Moving Users
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after
>> a Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery
>> Articles
>> for SQL Server
>> http://www.support.microsoft.com/?id=320125 Moving a Diagram
>> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
>> 2000
>> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
>> Roles and Permissions
>> --
>> Andrew J. Kelly SQL MVP
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
>> > I'm refreshing (restoring) a database and would like to keep the users
>> > from
>> > the original database. Is there a efficient way to extract users,
>> > restore
>> > the database and then re-apply the users. I'm not talking about logins
>> > as
>> > I'm familiar with sp_help_revlogin.
>> >
>> > Thanks.
>> >
>> > Ron
>>
>>|||Thanks Andrew - it seems to work fine in SQL2000. However in SQL2005, I
don't see where the database roles get extracted.
For example here's the output from SQL2000 extracting users and database
roles:
if not exists (select * from dbo.sysusers where name = N'abrown')
EXEC sp_grantdbaccess N'abrown'
GO
exec sp_addrolemember N'db_datareader', N'abrown'
GO
exec sp_addrolemember N'db_datawriter', N'abrown'
GO
Here's the output from SQL2005 extracting users and schemas (no mention of
extracint db roles):
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'abrown')
EXEC sys.sp_executesql N'CREATE SCHEMA [abrown] AUTHORIZATION [abrown]'
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'abrown')
CREATE USER [abrown] FOR LOGIN [abrown] WITH DEFAULT_SCHEMA=[abrown]
GO
I don't see how in SQL2005 users get added to datareader?
Thanks
Ron
"Andrew J. Kelly" wrote:
> You can script the users with Enterprise Manager. Right click on the db and
> choose All tasks - Generate SQL Scripts. One of the tabs has an option to
> script users and permissions for the db.
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
> > No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> > a
> > database, then 2) have that database refreshed from another databases,
> > then
> > 3) re-apply the users from step 1.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The Users stay with the db when you back it up and will be there after
> >> the
> >> restore.You may need to remap the login to the users an can do that with
> >> sp_change_users_login. Here is a bunch of info related to that topic.
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
> >> Moving
> >> system dbs 2005
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's 2000
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> >> Moving Users
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after
> >> a Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >> http://www.support.microsoft.com/?id=307775 Disaster Recovery
> >> Articles
> >> for SQL Server
> >> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> >> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
> >> 2000
> >>
> >> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> >> Roles and Permissions
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> >> > I'm refreshing (restoring) a database and would like to keep the users
> >> > from
> >> > the original database. Is there a efficient way to extract users,
> >> > restore
> >> > the database and then re-apply the users. I'm not talking about logins
> >> > as
> >> > I'm familiar with sp_help_revlogin.
> >> >
> >> > Thanks.
> >> >
> >> > Ron
> >>
> >>
> >>
> >>
>
>

Extracting Users for restore

I'm refreshing (restoring) a database and would like to keep the users from
the original database. Is there a efficient way to extract users, restore
the database and then re-apply the users. I'm not talking about logins as
I'm familiar with sp_help_revlogin.
Thanks.
Ron
The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||The Users stay with the db when you back it up and will be there after the
restore.You may need to remap the login to the users an can do that with
sp_change_users_login. Here is a bunch of info related to that topic.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||Ron,
You can get the information from the database prior to the restore and save
it somewhere for scripting back into the database later.
For SQL 2000:
sp_helpuser
sp_helprotect
Although these still work in SQL 2005, they miss some securables. You can
also look at the new dynamic management views. Start by examining:
sys.database_permissions
sys.database_principals
sys.database_role_members
Here are a couple of queries that may help you get started.
-- Permissions
select u.name, p.permission_name, p.class_desc,
object_name(p.major_id) ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
-- Role Memberships
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u
on m.role_principal_id = u.principal_id
join sys.database_principals u2
on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
> I'm refreshing (restoring) a database and would like to keep the users
> from
> the original database. Is there a efficient way to extract users, restore
> the database and then re-apply the users. I'm not talking about logins as
> I'm familiar with sp_help_revlogin.
> Thanks.
> Ron
|||No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from a
database, then 2) have that database refreshed from another databases, then
3) re-apply the users from step 1.
"Andrew J. Kelly" wrote:

> The Users stay with the db when you back it up and will be there after the
> restore.You may need to remap the login to the users an can do that with
> sp_change_users_login. Here is a bunch of info related to that topic.
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
> system dbs 2005
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's 2000
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
> a Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
> http://www.support.microsoft.com/?id=320125 Moving a Diagram
> http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
> http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
> Roles and Permissions
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:0E1183FA-9C0B-4939-9207-72BAC730A206@.microsoft.com...
>
>
|||You can script the users with Enterprise Manager. Right click on the db and
choose All tasks - Generate SQL Scripts. One of the tabs has an option to
script users and permissions for the db.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...[vbcol=seagreen]
> No I'm sorry, I guess I'm not clear. I'm looking to 1) extract users from
> a
> database, then 2) have that database refreshed from another databases,
> then
> 3) re-apply the users from step 1.
> "Andrew J. Kelly" wrote:
|||Thanks Andrew - it seems to work fine in SQL2000. However in SQL2005, I
don't see where the database roles get extracted.
For example here's the output from SQL2000 extracting users and database
roles:
if not exists (select * from dbo.sysusers where name = N'abrown')
EXEC sp_grantdbaccess N'abrown'
GO
exec sp_addrolemember N'db_datareader', N'abrown'
GO
exec sp_addrolemember N'db_datawriter', N'abrown'
GO
Here's the output from SQL2005 extracting users and schemas (no mention of
extracint db roles):
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'abrown')
EXEC sys.sp_executesql N'CREATE SCHEMA [abrown] AUTHORIZATION [abrown]'
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'abrown')
CREATE USER [abrown] FOR LOGIN [abrown] WITH DEFAULT_SCHEMA=[abrown]
GO
I don't see how in SQL2005 users get added to datareader?
Thanks
Ron
"Andrew J. Kelly" wrote:

> You can script the users with Enterprise Manager. Right click on the db and
> choose All tasks - Generate SQL Scripts. One of the tabs has an option to
> script users and permissions for the db.
> --
> Andrew J. Kelly SQL MVP
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:C9A98902-DE02-44F0-B0EB-9019C001DF78@.microsoft.com...
>
>

extracting the duplicate values

hi all
i want to extract only the duplicate values from a table
can any one know the sql syntax for that
thnking u
jagselect foo from yourtable
group by foo having count(*) > 1|||thnk u rudy thak u very much
i got it!!!!!!!!!!!!!!!

Extracting the date from a datetime value

Hi,
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:

>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:

> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:

> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>

extracting table schemas with nvarchar column(on 2000) from SSMS

Hi ,
when I use SSMS to extract the script for a table if the table is on a sql 2000 database and table has got nvarchar column then I will get following error message.

property Ansipaddingstatus is not available for column XXXX. this property may not exist
for this object,or may not be retrivable due to insufficent access rigths (microsoft.sqlserver.smo)
and I am sysadmin on both server

also I have to mention I have tried from another 2005 machine to extarct the script the same way and it did work. so the problem is do my sql 2005 edition.
Many thanksDifferent SP level ? The SMO classes are updated during the SP update as well.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks for your reply,
that's a very good point, which I should have thought before posting this issue
but
on the machine that it is failing I have got SP2 of SQL Server 2K5 installed and on the one that runs successfully I haven't got any Service pack!!!
which it doesn't make sence
Thanks|||Did you try having the same Service Pack level on both machines ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

extracting sql table column names

I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query

SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')

The following query uses the Information_Schema.Columns to list all columns, data type, and the table names.

Select C.Table_Catalog DB,C.Table_Schema, C.Table_Name, Column_Name, Data_Type

from Information_Schema.Columns C

join

Information_Schema.Tables T

on C.table_name = T.table_name

Where Table_Type = 'BASE TABLE'

Karl

|||

Thanks Karl,

I am sure this is a silly question, but what do the letters C and T represent?

KEB

|||

These are Aliases for the Views mentioned, its much easier to reference them in the select statement than typing the whole name as identifier again for the case that the views / tables have the same columnnames.

But anyway, use the following add-in to make sure that the columns you are getting are only from user created tables:

<Condition>

AND OBJECTPROPERTY(OBJECT_ID(table_name),'IsMSShipped') = 0

--its makes sure that only user (non microsoft shipped) created tables are displayed.

HTH, Jens Suessmeyer.

Extracting SQL fields from XML Data stored in NText field

I have a interesting situation, where XML is stored inside of SQL NText
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
...
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
...
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael Powell
Parsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegr oups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>
|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?
|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegro ups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>
|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?
|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)
|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
..NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx
|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegr oups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>
|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegro ups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
..
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
..
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael PowellParsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegroups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegroups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegroups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegroups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>

extracting SP header information

Greetings,
We have a business requirement to somehow extract the SP header from
syscomments where available. This is done from a COM+ component using
ADO in order to verify whether SP arguments are specified correctly
from a client application, primarily during integration testing. We
know to use sp_helptext, but there is a slight issue...
Parsing around comments is not a problem using block (/* ... */)
commenting style, however single line (-- ...) comments may pose a
problem. It appears as though syscomments spans such comment lines
across table rows. So we end up with something like this:
...
-- blah blah blah (exceeds 255 characters)
blah blah blah (remaining comment
...
This could present some challenges. Is there a safe (and sane) method
of determining when this is the case and making the appropriate
adjustments to the COM+ code?
If we can get past this "little" issue then I am fairly confident we
can parse the remainder of the SP header for the desired argument
information.
Any insight anyone can offer wouldb e much appreciated.
Thanks,
Michael PowellParsing syscomments text seems a bit of a kluge to me. If all you are
interested in is parameter names and data types, it would probably be better
to query INFORMATION_SCHEMA.PARAMETERS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133291509.605075.241180@.f14g2000cwb.googlegroups.com...
> Greetings,
> We have a business requirement to somehow extract the SP header from
> syscomments where available. This is done from a COM+ component using
> ADO in order to verify whether SP arguments are specified correctly
> from a client application, primarily during integration testing. We
> know to use sp_helptext, but there is a slight issue...
> Parsing around comments is not a problem using block (/* ... */)
> commenting style, however single line (-- ...) comments may pose a
> problem. It appears as though syscomments spans such comment lines
> across table rows. So we end up with something like this:
> ...
> -- blah blah blah (exceeds 255 characters)
> blah blah blah (remaining comment
> ...
> This could present some challenges. Is there a safe (and sane) method
> of determining when this is the case and making the appropriate
> adjustments to the COM+ code?
> If we can get past this "little" issue then I am fairly confident we
> can parse the remainder of the SP header for the desired argument
> information.
> Any insight anyone can offer wouldb e much appreciated.
> Thanks,
> Michael Powell
>|||Wow, you are correct. This is about as direct a route as one can expect
querying for parameter information. One thing, however, how do we get
at information such as the default arguments? This doesn't appear to be
available from INFORMATION_SCHEMA.PARAMETERS?|||Aside from parsing the proc header, you can use SMO to get stored procedure
default parameters. SMO is a .Net-based API introduced in SQL 2005 and
version 2.0 of the .Net framework so it needs to be called from managed
code. If your existing COM component is strictly unmanaged, you might find
it easier to wrap the needed SMO stuff in a separate assembly so that you
invoke it using COM interop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133327307.157086.42280@.g47g2000cwa.googlegroups.com...
> Wow, you are correct. This is about as direct a route as one can expect
> querying for parameter information. One thing, however, how do we get
> at information such as the default arguments? This doesn't appear to be
> available from INFORMATION_SCHEMA.PARAMETERS?
>|||Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?|||We may pursue the SMO option (research on the topic pending). Back to
our original query though... Are there any practical ways to address
the above scenario? Short of assuming that the code must be good
syntactically or it wouldn't have been applied to the syscomments
table. If that is true, then any tokens which fall outside the scope of
available SQL tokens are probably invalid and belong to the comment.
Then we're looking for the first token which appears with a '@.' and
which is followed by a valid type token? (Thinking out loud...)|||<quote>
Please forgive my ignorance, the only reference I see to SMO is in the
MS SOAP toolkit. Is there a primer section that could introduce me to
it? Something that explains what it is, how to begin using it, etc,
etc? From Visual Studio .NET (2002) I found the references. From VS
.NET 2003 I found no such references. Or maybe I'm missing it?
</quote>
For all,
http://msdn2.microsoft.com/en-us/library/ms162169.aspx|||Parsing ad-hoc text is a real pain, even when limited to the header.
Personally, I wouldn't even go there, given the SMO alternative. Consider
cases like:
CREATE PROC MyProc
--@.MyParm1,
@.MyParm2 int = 1,
@.MyParm3 varchar(10) = '@.MyParm4 int',
@.MyParm5 int = 1, @.MyParm6 int = 1 --@.MyParam7 int
AS
...
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133363064.039670.317640@.g14g2000cwa.googlegroups.com...
> We may pursue the SMO option (research on the topic pending). Back to
> our original query though... Are there any practical ways to address
> the above scenario? Short of assuming that the code must be good
> syntactically or it wouldn't have been applied to the syscomments
> table. If that is true, then any tokens which fall outside the scope of
> available SQL tokens are probably invalid and belong to the comment.
> Then we're looking for the first token which appears with a '@.' and
> which is followed by a valid type token? (Thinking out loud...)
>|||As you've probably discovered by now, .Net Framework 2.0 requirement implies
that you'll need to develop using Visual Studio 2005 or at least the 2.0
framework SDK.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike" <mwpowellnm@.comcast.net> wrote in message
news:1133362835.430233.58550@.g47g2000cwa.googlegroups.com...
> Please forgive my ignorance, the only reference I see to SMO is in the
> MS SOAP toolkit. Is there a primer section that could introduce me to
> it? Something that explains what it is, how to begin using it, etc,
> etc? From Visual Studio .NET (2002) I found the references. From VS
> .NET 2003 I found no such references. Or maybe I'm missing it?
>