Copying/Transferring Tables

Is there a way to copy all tables (not the data, just the structure) and their relationship diagrams from one SQL server db to another?
What is the easiest way?
ThanksRight click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||IMHO DTS is the best choice here as it will take care of all dependent objects and constraints. In this case you would elect NOT to transfer the data or probably user id and permissions. The only problem with this is it will not transfer the relationship diagram.|||I just want to transfer Tables and diagrams. I don't want other objects.
I know I can use a wizard to transfer the tables but it doesn't have an option to transfer the diagrams.

Originally posted by sqlserver2k
Right click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||I am not aware of any tool or wizard that will transfer a relationship diagram, however the data is contained in a table called dtProperties and you could use the following to transfer a diagram:

set identity_insert dtproperties on
INSERT INTO <target db>.dbo.dtproperties(id, objectid, property, value, uvalue, lvalue, version)
select * from <source db>.dbo.dtproperties
set identity_insert dtproperties offsql

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!
nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegro ups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegroups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegroups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying/ Deleting Huge Data from a table

Hi,
I have a problem about "userlogs" table . this table contains some
information about users actions .The table has nearly 800.000.000 record in
it and ~100Gb data , 50GB index.
i have tried to copy some data ( like 1M records) to a temporary database, i
couldnt manage it
This is the query , the table has a index contains only "id"
declare @.start_index int, @.end_index int
set @.start_index = 800000050
set @.end_index = 800000250
insert into userlogsbackup select * from userlogs where id > @.start_index
and id < @.end_index
but the select fails unless specifying "top 10" for example. What else can
i try ?
Thanks in advanceIf the index is not clustered then it can't do a range scan without also
doing bookmark lookups for each row found. So the more rows you will touch
the less the chances of using the index. But in your case the optimizer does
not know how many rows will be affected since it does not know the values of
the two variables. As such it guesses and the guess for a range is quite a
large percentage (can't remember off hand) which will negate the use of an
index s. Do you get the proper plan if you hard code the two values?
You can try creating a stored procedure that has the two values as
parameters. That way the optimizer will use the actual values passed the
first time to create the plan. Does it work with a TOP 250?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a problem about "userlogs" table . this table contains some
> information about users actions .The table has nearly 800.000.000 record
> in it and ~100Gb data , 50GB index.
> i have tried to copy some data ( like 1M records) to a temporary database,
> i couldnt manage it
> This is the query , the table has a index contains only "id"
> declare @.start_index int, @.end_index int
> set @.start_index = 800000050
> set @.end_index = 800000250
>
> insert into userlogsbackup select * from userlogs where id >
> @.start_index and id < @.end_index
> but the select fails unless specifying "top 10" for example. What else
> can i try ?
> Thanks in advance
>|||Hi Andrew ,
thanks for your answer.
yes index is not clustered . it doesnt work with top 70 .
I think i am gonna write while loop for selecting , and inserting one by one
like this
while @.start_index and < @.end_index
begin
1
2 insert into userlogsbackup select * from userlogs where id =
@.start_index
3 delete from userlogs where id = @.start_index
4 set @.start_index = @.start_index + 1
5
end
i am also thinking to use "begin transaction and commit transaction" . Can
you tell me if which lines transactions contains
thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> If the index is not clustered then it can't do a range scan without also
> doing bookmark lookups for each row found. So the more rows you will touch
> the less the chances of using the index. But in your case the optimizer
> does not know how many rows will be affected since it does not know the
> values of the two variables. As such it guesses and the guess for a range
> is quite a large percentage (can't remember off hand) which will negate
> the use of an index s. Do you get the proper plan if you hard code the
> two values? You can try creating a stored procedure that has the two
> values as parameters. That way the optimizer will use the actual values
> passed the first time to create the plan. Does it work with a TOP 250?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hasan O." <hozavalsiz@.gmail.com> wrote in message
> news:esQBHkKgGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||Maybe if you post the actual DDL for the table including all the indexes and
tell us exactly what you are tyring to accomplish we can suggest something
better. Do you want to move any 1 million rows or a specific set? Is this a
one time thing or will it be repeated? Can it be done in off hours?
Andrew J. Kelly SQL MVP
"Hasan O." <hozavalsiz@.gmail.com> wrote in message
news:eJs5eNMgGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi Andrew ,
> thanks for your answer.
> yes index is not clustered . it doesnt work with top 70 .
> I think i am gonna write while loop for selecting , and inserting one by
> one like this
> while @.start_index and < @.end_index
> begin
> 1
> 2 insert into userlogsbackup select * from userlogs where id =
> @.start_index
> 3 delete from userlogs where id = @.start_index
> 4 set @.start_index = @.start_index + 1
> 5
> end
> i am also thinking to use "begin transaction and commit transaction" . Can
> you tell me if which lines transactions contains
> thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uaZyrgLgGHA.4940@.TK2MSFTNGP05.phx.gbl...
>|||>but the select fails unless specifying "top 10" for example.
What is the exact nature of the failure? What error code and message
is returned? Or is it simply taking a long time?
Roy Harvey
Beacon Falls, CT
On Fri, 26 May 2006 12:44:41 +0300, "Hasan O." <hozavalsiz@.gmail.com>
wrote:

>Hi,
>I have a problem about "userlogs" table . this table contains some
>information about users actions .The table has nearly 800.000.000 record in
>it and ~100Gb data , 50GB index.
>i have tried to copy some data ( like 1M records) to a temporary database,
i
>couldnt manage it
>This is the query , the table has a index contains only "id"
>declare @.start_index int, @.end_index int
>set @.start_index = 800000050
>set @.end_index = 800000250
>
>insert into userlogsbackup select * from userlogs where id > @.start_index
>and id < @.end_index
>but the select fails unless specifying "top 10" for example. What else can
>i try ?
>Thanks in advance
>|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cu3e72d978qc99vfo1mclnqhudtgh31r71@.
4ax.com...
> What is the exact nature of the failure? What error code and message
> is returned? Or is it simply taking a long time?
taking too much time so i cancel the query.
i am using one by one move operation now .
thanks for the answer.

copying/ backing up a database

Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..?

ive used tables and stored procedures and a diagram btw.

thanks for any advice

Look up sp_detach_db in Books Online. Detach the database, copyit, and submit it. Then they can re-attach it using sp_attach_db.

sql

Copying XML file into SQL

Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction.

In green below is the full source code, I think that the trouble is with the try statement part here:

Try

bulk.WriteToServer(xd)

This comes up with the error :

Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'

Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it

I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating

Protected Sub Button1_Click1(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.ClickDim objXMLDim objXSL' destination tableDim connectionStringAs String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionStringDim myNewConnectionAs Data.SqlClient.SqlConnection =New Data.SqlClient.SqlConnection(connectionString) myNewConnection.Open()Dim productreaderAs XmlReaderDim bulkAs Data.SqlClient.SqlBulkCopy =New Data.SqlClient.SqlBulkCopy(myNewConnection) bulk.DestinationTableName ="Product"Dim productcountAs Integer = 0Dim settingsAs New System.Xml.XmlReaderSettings() settings.IgnoreWhitespace =True settings.IgnoreComments =True Dim xsAs String = (Server.MapPath("~/App_Data/XMLfile.xml")) Using readerAs XmlReader = XmlReader.Create(xs, settings)While (reader.Read())If (reader.NodeType = XmlNodeType.ElementAnd"product" = reader.LocalName)Then productcount += 1End If End While End Using Response.Write(String.Format("found {0} products!", productcount)) Using bulk bulk.DestinationTableName ="Product"Dim xdAs New System.Xml.XmlDocument() xd.Load("C:\Documents and Settings\Simon\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\xmlfile.xml")Dim xrAs New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml")) bulk.ColumnMappings.Add("TDProductId","TDProductId")' map First to first_name bulk.ColumnMappings.Add("name","name")' map Last to last_name bulk.ColumnMappings.Add("description","description")' map Date to first_sale bulk.ColumnMappings.Add("imageUrl","imageUrl")' map Amount to sale_amount bulk.ColumnMappings.Add("productUrl","productUrl")' map UserID from Session to user_id bulk.ColumnMappings.Add("price","price") bulk.ColumnMappings.Add("currency","currency") bulk.DestinationTableName ="Affilaite_Product_new"Try bulk.WriteToServer(xd)Catch exAs Exception Response.Write(ex.Message)Finally myNewConnection.Close() bulk.Close() xd =Nothing End Try End UsingEnd Sub

That's because bulk.WriteToServer method will not accept an XmlDocument as parameter. Why not use a DataSet in this case? So you can use such code:

Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Dim ds As New DataSet
ds.ReadXml(xs, XmlReadMode.Auto)
--...add your code
bulk.ColumnMappings.Add("TDProductId", "TDProductId")
--...
bulk.WriteToServer(ds.Tables(0))

BTW, if you're using SQL2005, you can load XML files (actually all BLOB data, including images) into SQL database by using simply OPENROWSET with SINGLE_BLOB option. You can take a look at:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5xml.asp?frame=true

|||

This works a treat, many thanks for thank.

I've notice it doesn't like inserting into nulls or primary keys, but I shall look into that now.

Cheers again great advice

copying website: database doesnt work?

Since VWD Express doesn't support Publish Website, I used 'Copy Website' to deploy my local site to the hosting server. The static page works fine, but when it gets to accessing database, I kept getting the following error:

An attempt to attach an auto-named database for file I:\Data\Web\dnh.sk\lingling_15726c71-b2bf-479f-bcc3-b7ae43318f3c\www\App_Data\Personal.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Anyone has the same experience? How to resolve this?

Thanks,

When you deploy your project, you have to upload your database separately.

You cannot use the db from the app_data folder. Execute the SQL against the db that you have in your hosting server and change the connectionstring for that.

Regards

|||

smiling4ever wrote:

When you deploy your project, you have to upload your database separately.

You cannot use the db from the app_data folder. Execute the SQL against the db that you have in your hosting server and change the connectionstring for that.

How do you do this when you only have VS.2005 and the basic SQL Express tools? Is it possible?

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
Sridhar
Yes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridharsql

Copying Views and Stored Procedures

Hello All,
I have two SQL databases which have the exact same tables - just different data. In database A there are only tables. In database B there are tables, stored procedures and views. I need to transfer the stored procedures and views from database B into database A. Is there a way to do this?
Thanks in advanceDTS or Script it. Your pick.|||Thank You.

I was playing around with it and I used a script. Thanks for your reply.|||What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?|||Originally posted by Brett Kaiser
What about contraints?

Just make sure you do them in the correct order...ie if a sproc references a view...

I guess if you did you'd just get a warning message...and once it was reference it would be resolved...

Anyone experience this?

I believe no entries would be inserted into sysdepends for the sproc corresponding to the view... which would lead to the view not showing up when you do a sp_depends on the sproc ...

Copying views

I have two databases A & B, I copied all of the tabels and data from A
to B. I can't figure out how to copy the Views and Stored procedures
from A to B. As you can see I'm new at this, can anyone point me in
the right direction? Oh... forgot running SQL 2000.

Thanks[posted and mailed, please reply in news]

Jim Davidson (raccoon@.icubed.com) writes:
> I have two databases A & B, I copied all of the tabels and data from A
> to B. I can't figure out how to copy the Views and Stored procedures
> from A to B. As you can see I'm new at this, can anyone point me in
> the right direction? Oh... forgot running SQL 2000.

If you want to make a complete copy of a database, there are certainly
easier ways to go. The below assumes that you run Query Analyzer:

exec sp_helpdb yourdb
-- Make notice of the values in the name and filename columns. (Cut
-- and paste to query window.)

BACKUP DATABASE yourdb TO DISK = 'C:\temp\yourbackup.bak'
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\temp\yourbackup.bak'
WITH MOVE 'yourdb' TO 'C:\MSSQL\Data\copyofyourdb.mdf',
MOVE 'yourdblog' TO 'C:\MSSQL\Data\copyofyourdb.ldf',
REPLACE
EXEC master..xp_cmdshell 'DEL C:\temp\yourbackup.bak'

In the RESTORE command replace yourdb and yourdblog with the values
from the name column in the sp_helpdb output, and in paths, replace
the directory paths with the value from the filename columns. You
must change the file name.

Note that RESTORE DATABASE creates the database if it does not exist.

You can also do the backup and restore stuff from Enterprise Manager,
but I am more confident with the T-SQL commands, so I cannot describe
those dialogs.

An alternative, is to use sp_detach_db, copy the database files and
then use sp_attach_db on the copy and the original.

As for the original question, the answer is that you should maintain
all your SQL objects under version control and reload from the source
there. You can also use the scripting facilities in Enterprise
Manager. Right-click the database, and select All Tasks and then
Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Usin
g
> the import/export wizard tries to copy the data in a View to a Table in th
e
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing
).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Using
> the import/export wizard tries to copy the data in a View to a Table in the
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
>> Sure, that makes sense. The thing is this isn't a usual process. I am
>> needing to create a non-production (test) database off of the production
>> database so I thought there would be a way to simply copy the entire
>> database (including objects such as views, stored procedures, etc) in one
>> fell swoop. I guess another option would be to detach the production
>> database and make a copy of the db files before reattaching it. The only
>> downside to this is downtime on the production side. Thanks for the
>> info.
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
>> Because the database structure doesn't exist on the Dev server. To do a
>> restore doesn't the database structure have to already exist? At least I
>> couldn't find a way to do it otherwise. I just did a detach, copy file,
>> reattach to both the dev and production server. This seems to be the
>> easiest solution.
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

copying views

I have created several views via Enterprise Manager in a
SQL database that supports software provided by an outside
source. These views will be effected during software
version upgrades and may have some nagative impact on the
execution of upgrades.
Is there some simple way to preserve these views during a
software upgrade?
I hoped that I would just be able to cut and paste the
views (perhaps into another database where they didn't
really belong) temporarily during an upgrade and then move
them back. I don't see any way to do this. I can right
click on the views and copy them, but I don't see any way
to paste it anywhere for temporary storage.
I would prefer not to copy the query text from each view,
save it somewhere temporarily, and then recreate each view
and paste the query text back into it. I have a lot of
views created and this would be a time consuming solution.
Thanks.generate scripts and run those after the update
"allen" <anonymous@.discussions.microsoft.com> wrote in message
news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
> I have created several views via Enterprise Manager in a
> SQL database that supports software provided by an outside
> source. These views will be effected during software
> version upgrades and may have some nagative impact on the
> execution of upgrades.
> Is there some simple way to preserve these views during a
> software upgrade?
> I hoped that I would just be able to cut and paste the
> views (perhaps into another database where they didn't
> really belong) temporarily during an upgrade and then move
> them back. I don't see any way to do this. I can right
> click on the views and copy them, but I don't see any way
> to paste it anywhere for temporary storage.
> I would prefer not to copy the query text from each view,
> save it somewhere temporarily, and then recreate each view
> and paste the query text back into it. I have a lot of
> views created and this would be a time consuming solution.
> Thanks.|||Thanks!
>--Original Message--
>generate scripts and run those after the update
>"allen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
>> I have created several views via Enterprise Manager in a
>> SQL database that supports software provided by an
outside
>> source. These views will be effected during software
>> version upgrades and may have some nagative impact on
the
>> execution of upgrades.
>> Is there some simple way to preserve these views during
a
>> software upgrade?
>> I hoped that I would just be able to cut and paste the
>> views (perhaps into another database where they didn't
>> really belong) temporarily during an upgrade and then
move
>> them back. I don't see any way to do this. I can right
>> click on the views and copy them, but I don't see any
way
>> to paste it anywhere for temporary storage.
>> I would prefer not to copy the query text from each
view,
>> save it somewhere temporarily, and then recreate each
view
>> and paste the query text back into it. I have a lot of
>> views created and this would be a time consuming
solution.
>> Thanks.
>
>.
>sql

Copying variables from another package

HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?

Thank you,

Ccote

There's no way to do this using the Designer. However a package is just an XML document so you try adding a new variable by processing those XML documents using XQuery.

If you work out how to do that - reply here and let me know

-Jamie

|||

HI Jamie, I have been able to achieve something interesting by using a script component with the test code below:

Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections("ChildPackage").AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
If Not package.Variables.Contains("VarTestNum") Then
package.Variables.Add("VarTestNum", False, "User", 0)
application.SaveToXml(packagename.ToString, package, Nothing)
End If

Dts.TaskResult = Dts.Results.Success

End Sub

The basis of the code I am using comes from Brian Knight web site.So far, I am not able to specify the type of the variable; It seems that SSIS resolve it using the Value method parameter (last one). I do not know if it will bw able to parse a date value correctly. But at least, if all I have to do is to change a type of a couple of variables in all package, the worst is done.

Ccote

|||

You're right about the variable type It is determined by the type of the object that you pass in that parameter.

-Jamie

Copying users to new server

I'm new to SQL server and am installing a new server that I will be copying
from a old server to new. I've restored the databases but am trying to cop
y
over the users and security.
Is there a simple way to copy over the users and security to the new server?> I'm new to SQL server and am installing a new server that I will be
copying
> from a old server to new. I've restored the databases but am trying to
copy
> over the users and security.
> Is there a simple way to copy over the users and security to the new
server?
Yes, script them. In EM, right-click on your database, select All Tasks -
Generate SQL Script; on the Options tab, select
Script database users and database roles and
Script object-level permissions.
Generate the script and execute it in your new db.
BTW, I suppose you have the same logins.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Dejan
I think although he/she has the same logins moving users to the new server
may cause the problem due to the different SID of the logins.
I'd go with two stored procedures provided by Microsoft to move logins.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eeySs4qLFHA.2988@.TK2MSFTNGP14.phx.gbl...
> copying
> copy
> server?
> Yes, script them. In EM, right-click on your database, select All Tasks -
> Generate SQL Script; on the Options tab, select
> Script database users and database roles and
> Script object-level permissions.
> Generate the script and execute it in your new db.
> BTW, I suppose you have the same logins.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>|||http://support.microsoft.com/defaul...kb;en-us;246133
"kcsheff" wrote:
[vbcol=seagreen]
> I'm new to SQL server and am installing a new server that I will be copyin
g
> from a old server to new. I've restored the databases but am trying to c
opy
> over the users and security.
> Is there a simple way to copy over the users and security to the new server?[/vbco
l]

Copying users from one database to another

how do i copy all the users, roles, permissions etc from on database to
another database?JT
Not easy task. If you have the same structure of the database the best thing
will be restoring the database with a different name
Otherwise, you will have to generate a script for adding users to be mapped
with the logins
sp_grantdbaccess @.login
sp_addrolemember 'db_owner',@.login
"JT" <col@.newsgroup.nospam> wrote in message
news:ec9iL$pNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> how do i copy all the users, roles, permissions etc from on database to
> another database?

copying users between roles

I would like to copy all users currently from RoleA into RoleB. I know there
must be a nice way to script this. Any ideas?Robert
There is no a "nice" script to move users from one Role to another at least
I am not aware.
You will have to deal with system tables that hold the info about users but
it is not recommended.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:66EA482B-0FD9-49A9-B049-6CADCA5B5BD1@.microsoft.com...
>I would like to copy all users currently from RoleA into RoleB. I know
>there
> must be a nice way to script this. Any ideas?|||Hi Robert,
This may help u......a nice script to move all users from roleA to roleB
create table #users (
UserName sysname,
GroupName sysname,
LoginName sysname,
DefDBName sysname,
UserID smallint,
SID smallint
)
insert #users
exec('sp_helpuser')
select identity(int,1,1) as idn, * into #usersInRoleA from #users where
GroupName like 'roleA'
declare @.i as int,
@.maxusers as int,
@.username as sysname
select @.maxusers = max(idn) from #usersInRoleA
set @.i = 1
while (@.i <= @.maxusers)
begin
select @.username = username from #usersInRoleA where idn = @.i
exec sp_addrolemember 'roleB',@.username
exec sp_droprolemember 'roleA',@.username
set @.i = @.i + 1
end
drop table #usersInRoleA
drop table #users
enjoy and keep going...
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?|||Hi Robert
Maybe something like:
CREATE TABLE #Rolemembers ( DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )
INSERT INTO #Rolemembers ( DbRole, MemberName, MemberSID )
EXEC sp_helprolemember 'ISDLOAD_ROLE'
DECLARE @.member sysname
DECLARE Member_Cursor CURSOR FOR SELECT MemberName FROM #Rolemembers
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor INTO @.member
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_executesql N'EXEC sp_addrolemember @.rolename= ''ROLEB'' ,
@.membername = @.membernm' ,
N'@.membernm sysname',
@.membernm = @.member
FETCH NEXT FROM Member_Cursor INTO @.member
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
DROP TABLE #Rolemembers
John
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?