"transfering" data to test database

What I need to do:
Copy database from one server to another.
Problem:
3rd party application requires the owner of the tables to be different.
Eg in the source db the owner is A so the name of table1 is A.Table1 on
the destination db the owner is supposed to be B so the name of table1 will
be B.Table1.
I have used a DTS package using the Copy SQL Server object task due to the
fact that I have a lot of tables however this also copies the owner accross
so it is named A.Table1 and not the required B.Table1.
Any Ideas?
Thanks.
JonasYou could try this:
1. For each table in your database
(e.g., get all tables
SELECT * FROM INFORMATION_SCHEMA.TABLES)
2. Change owner with sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
You could run a query similar to the one below, to return all "sp_changeobjectowner" statements as a recordset which you can then execute as a batch to change the ownership of all your tables at once:
e.g.,
Select 'exec sp_changeobjectowner @.objname = [youroldowner].[' + table_name + '] , @.newowner = ''yournewowner'''
from INFORMATION_SCHEMA.TABLES
Tea C.
"Jonas Larsen" wrote:
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be different.
> Eg in the source db the owner is A so the name of table1 is A.Table1 on
> the destination db the owner is supposed to be B so the name of table1 will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>
>|||Hi,
You can change the object owner using the system stored procedure
sp_changeobjectowner.
sp_changeobjectowner 'Table_name', 'B_USER'
Instead of doing one by one you could query the sysobjects table for all
objects ownerd by A_user.
set quoted_identifier off
select 'sp_changeobjectowner '+"'"+name+"',"+"'"+'b_user'+"'" +char(10)+'go'
from sysobjects
where user_name(uid)='A_user' and type='u'
Execute the above query in Query Analyzer in with Text result. (Query menu
result in text)
Copy the result in Query screen and execute to change the object owner to
B_USER.
Thanks
Hari
MCDBA
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||I beleive DTS can copy tables between different owners directly instead of
having to to do all ownership changing...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||Ok. Do you know how this is done?
Thanks.
Regards
Jonas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhug4pJdEHA.3216@.TK2MSFTNGP11.phx.gbl...
> I beleive DTS can copy tables between different owners directly instead of
> having to to do all ownership changing...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> > What I need to do:
> >
> > Copy database from one server to another.
> >
> > Problem:
> > 3rd party application requires the owner of the tables to be
> different.
> > Eg in the source db the owner is A so the name of table1 is A.Table1
> on
> > the destination db the owner is supposed to be B so the name of table1
> will
> > be B.Table1.
> >
> > I have used a DTS package using the Copy SQL Server object task due to
the
> > fact that I have a lot of tables however this also copies the owner
> accross
> > so it is named A.Table1 and not the required B.Table1.
> >
> > Any Ideas?
> >
> > Thanks.
> >
> > Jonas
> >
> >
>

"GO" used in a stored procedure

I have a stored procedure in sql 2000 that requires steps to be fully
completed before moving to the next command in the procedure. I have
tried to place the word "GO" after each statement. When I create the
procedure then take a look at it through em, it only shows the code up
until the word "GO".

Example:
CREATE PROCEDURE mytest
as
create table mytable col1 varchar(5), col2 varchar(10)
GO
insert into mytable (col1, col2) values(abc, def)

All i see is:
CREATE PROCEDURE mytest
as
create table mytable col1 varchar(5), col2 varchar(10)
GO

I need to be 100% certain that the table has already been created
before trying to add records to it.

Is there any command that makes t-sql halt until the previous command
has finished?

Thanks,
DaveGO isn't a TSQL command. It marks the end of a batch in Query Analyzer and
therefore signals the end of a stored procedure definition in that batch, so
it cannot be part of an SP.

--
David Portas
SQL Server MVP
--|||Dave wrote:

> I have a stored procedure in sql 2000 that requires steps to be fully
> completed before moving to the next command in the procedure. I have
> tried to place the word "GO" after each statement. When I create the
> procedure then take a look at it through em, it only shows the code up
> until the word "GO".
> Example:
> CREATE PROCEDURE mytest
> as
> create table mytable col1 varchar(5), col2 varchar(10)
> GO
> insert into mytable (col1, col2) values(abc, def)
> All i see is:
> CREATE PROCEDURE mytest
> as
> create table mytable col1 varchar(5), col2 varchar(10)
> GO
>
> I need to be 100% certain that the table has already been created
> before trying to add records to it.
> Is there any command that makes t-sql halt until the previous command
> has finished?
> Thanks,
> Dave

You can be assured that the table will be created before the insert
statement is executed. Each command will be 100% finished and committed
before moving on to the next statement (unless you using transactions).

Zach|||
David,

Thanks for responding so quickly. I did read in several places that the
GO command does exactly what you have stated.

However, I still need to find a way to make sure that my code is
finished executing before moving to the next step within my stored
procedure.

The example I posted earlier was really dummied down from what I really
need to do. We have some procs here that are over 600 lines of code, and
most of them depend on the previous chunk of code to have completed
before they execute.

Any other ideas would be greatly appreciated.
Thanks,
Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dave F wrote:

> David,
> Thanks for responding so quickly. I did read in several places that the
> GO command does exactly what you have stated.
> However, I still need to find a way to make sure that my code is
> finished executing before moving to the next step within my stored
> procedure.
> The example I posted earlier was really dummied down from what I really
> need to do. We have some procs here that are over 600 lines of code, and
> most of them depend on the previous chunk of code to have completed
> before they execute.
> Any other ideas would be greatly appreciated.
> Thanks,
> Dave
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Like I said, SQL will ALWAYS complete a command before moving on to the
next one. What makes you think this doesn't already happen?

Zach|||[posted and mailed, please reply in news]

Dave (funkdm1@.yahoo.com) writes:
> Is there any command that makes t-sql halt until the previous command
> has finished?

To echo what Zach said: there is no command that causes T-SQL to continue
with the next command, before the previous has completed. So you
have no reason to worry.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a catch 22 situation.

Do you really need to create a new table each time you execute the stored proc?
If so, use temporary tables.

Otherwise empty your existing table before you insert rows.

"..Transform requires script Text and Language.." error?

Hi,
I'm trying setup a package to replicate a database table into a another
database's table.
I thought a Data Driven Query Task would work. I got as far as making the
two connections and setting up the Data Driven Query's source SELECT (with
stored procedure I created in the source database) and destination table
under the Bindings tab.
However, when I run the task I get the error below
"ActiveX Scripting Transform requires script Text and Language and at least
one Phase function to be specified."
When I preview the data in under the task's Source tab, I see data AND the
destination table gets created but not data gets transfered. Can someone
please help on what this error means?
Also, I eventually want to update this destination table with several other
subsequent queries from other databases in order to conslidate related data
into one large reporting table but I have to get the initial population of
data to work first. This will be refreshed daily
Does this approach/package task seem to be applicable?
Thanks, Dave.
Dave,
you'd be best off posting in the separate DTS discussion group.
Rgds,
Paul Ibison
|||Open up your package, save it as a bas file and post it here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||I have the exact same problem as this guy. Could you please help?
thanks
marlene
url:http://www.ureader.com/msg/1156295.aspx