First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.
What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:
alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO
Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "
I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.
Or is there a way to make those changes using bcp?In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
When you are finished, set the recovery model back to "full" and run an immediate backup.|||Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!|||I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Friday, March 16, 2012
"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
> >
> >
>
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
> >
> >
>
Sunday, March 11, 2012
"SQL: The Complete Reference" source?
I purchased a used copy of the SQL book listed in the subject but the
CD is missing. I have access to SQL Server 2000 and so don't need the
trial software but would like to get the source for the database used
in the examples. Does anyone know where I can get this?What's the name of the sample database used in the examples?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"SteveH" <steve.holle@.gmail.com> wrote in message
news:1139507451.169408.5450@.f14g2000cwb.googlegroups.com...
>I purchased a used copy of the SQL book listed in the subject but the
> CD is missing. I have access to SQL Server 2000 and so don't need the
> trial software but would like to get the source for the database used
> in the examples. Does anyone know where I can get this?
>|||SteveH wrote:
> I purchased a used copy of the SQL book listed in the subject but the
> CD is missing. I have access to SQL Server 2000 and so don't need the
> trial software but would like to get the source for the database used
> in the examples. Does anyone know where I can get this?
Have you checked the publisher's web site. Many publishers post the
source code from books. Also, you might try the author's home page (if
you have it), which may have some information as well.
--
David Gugick - SQL Server MVP
Quest Software
CD is missing. I have access to SQL Server 2000 and so don't need the
trial software but would like to get the source for the database used
in the examples. Does anyone know where I can get this?What's the name of the sample database used in the examples?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"SteveH" <steve.holle@.gmail.com> wrote in message
news:1139507451.169408.5450@.f14g2000cwb.googlegroups.com...
>I purchased a used copy of the SQL book listed in the subject but the
> CD is missing. I have access to SQL Server 2000 and so don't need the
> trial software but would like to get the source for the database used
> in the examples. Does anyone know where I can get this?
>|||SteveH wrote:
> I purchased a used copy of the SQL book listed in the subject but the
> CD is missing. I have access to SQL Server 2000 and so don't need the
> trial software but would like to get the source for the database used
> in the examples. Does anyone know where I can get this?
Have you checked the publisher's web site. Many publishers post the
source code from books. Also, you might try the author's home page (if
you have it), which may have some information as well.
--
David Gugick - SQL Server MVP
Quest Software
Thursday, March 8, 2012
"requested conversion is not supported" error in DTS data copy.
I have a DTS that pulls data in from a table in MysQL and inserts it into a tablle in SQL Server. it's failing when I run the step with the "requestd conversion is not supported" error, but when testing each single column copy in the Data Transformation step, it passes.
I just want an exact copy of the data.
the source MySQL table is...
CREATE TABLE `entry` (
`web_id` int(11) NOT NULL auto_increment,
`web_name` varchar(40) NOT NULL default '',
`web_email` varchar(60) NOT NULL default '',
`web_cli` varchar(50) NOT NULL default '',
`web_pu_id` varchar(60) default NULL,
`web_answer` varchar(60) NOT NULL default '',
`web_ddi` varchar(30) default NULL,
`web_time_entered` datetime NOT NULL default '0000-00-00 00:00:00',
`web_ip_address` varchar(20) default NULL,
`web_entry_status` set('failed','success') NOT NULL default '',
`web_polled` enum('true','false') default 'false',
PRIMARY KEY (`web_id`)
) ENGINE=MyISAM;
and the SQl Server destination is...
CREATE TABLE [entry] (
[web_id] [int] NOT NULL ,
[web_name] [varchar] (23) NOT NULL ,
[web_email] [varchar] (37) NOT NULL ,
[web_cli] [varchar] (15) NOT NULL ,
[web_pu_id] [varchar] (15) NULL ,
[web_answer] [text] NOT NULL ,
[web_ddi] [varchar] (11) NULL ,
[web_time_entered] [datetime] NOT NULL ,
[web_ip_address] [varchar] (15) NULL ,
[web_entry_status] [varchar] (15) ,
[web_polled] [varchar] (15) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [temp_webentry] WITH NOCHECK ADD
CONSTRAINT [PK_tmpwebentry] PRIMARY KEY CLUSTERED
(
[web_id]
) ON [PRIMARY]
GO
sample line of data ...
352711, 'some name', 'email@.addre.ss', '0123456789', 'AA_XX-Y-012345-2', '', '098765432', '2005-10-19 23:17:42', '192.168.28.165', 'failed', 'false'scratch that.. in compairing the DDLs it was obvious..
varchar -> text doesn't work well :)
a
I just want an exact copy of the data.
the source MySQL table is...
CREATE TABLE `entry` (
`web_id` int(11) NOT NULL auto_increment,
`web_name` varchar(40) NOT NULL default '',
`web_email` varchar(60) NOT NULL default '',
`web_cli` varchar(50) NOT NULL default '',
`web_pu_id` varchar(60) default NULL,
`web_answer` varchar(60) NOT NULL default '',
`web_ddi` varchar(30) default NULL,
`web_time_entered` datetime NOT NULL default '0000-00-00 00:00:00',
`web_ip_address` varchar(20) default NULL,
`web_entry_status` set('failed','success') NOT NULL default '',
`web_polled` enum('true','false') default 'false',
PRIMARY KEY (`web_id`)
) ENGINE=MyISAM;
and the SQl Server destination is...
CREATE TABLE [entry] (
[web_id] [int] NOT NULL ,
[web_name] [varchar] (23) NOT NULL ,
[web_email] [varchar] (37) NOT NULL ,
[web_cli] [varchar] (15) NOT NULL ,
[web_pu_id] [varchar] (15) NULL ,
[web_answer] [text] NOT NULL ,
[web_ddi] [varchar] (11) NULL ,
[web_time_entered] [datetime] NOT NULL ,
[web_ip_address] [varchar] (15) NULL ,
[web_entry_status] [varchar] (15) ,
[web_polled] [varchar] (15) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [temp_webentry] WITH NOCHECK ADD
CONSTRAINT [PK_tmpwebentry] PRIMARY KEY CLUSTERED
(
[web_id]
) ON [PRIMARY]
GO
sample line of data ...
352711, 'some name', 'email@.addre.ss', '0123456789', 'AA_XX-Y-012345-2', '', '098765432', '2005-10-19 23:17:42', '192.168.28.165', 'failed', 'false'scratch that.. in compairing the DDLs it was obvious..
varchar -> text doesn't work well :)
a
Friday, February 24, 2012
"Invalid Object.." error when using Copy SQL Server Objects task
SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
"Invalid Object.." error when using Copy SQL Server Objects task
SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIA
Lindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegr oups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>
|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIA
Lindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegr oups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>
|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
"Invalid Object.." error when using Copy SQL Server Objects task
SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.
Sunday, February 19, 2012
"hashed" password on alter login command
Hi,
I need to synchronise logins between 2 SQL2005 instances on a regular basis
- while you can copy the passwords to the destination using the "HASHED"
keyword on the CREATE LOGIN command, it appears that "HASHED" is missing fro
m
the ALTER LOGIN command. This means that I can't just update the password
when a user on the source instance changes the password on their SQL
authenticated account.
I also looked at using the transfer logins task in SSIS, but then read the
following in book online:
"At the destination, the transferred logins are disabled and assigned random
passwords. A member of the sysadmin role on the destination server must
change the passwords and enable the logins before the logins can be used. "
Apart from recording the login's roles, database access, and permissions,
dropping the login, creating with the hashed password, and re-applying the
roles/database access/permissions - has anybody found a way to just apply a
new hashed password to the login?
AndyHello Andy,
Thank you for posting in MSDN newsgroup!
I understand that you'd like to transfer logins and passwords between SQL
2005 instances. You just want to apply a new hashed password to the logins.
If I'm off-base, please let me know.
I think there is no simple method to apply a new hased password as you
want. You need to drop/recreate the logins by using script and grant it the
proper permission. You have to know the original password so that you could
change it to the new password by using "Alter login" statement.
You may want to refer to the following articles for more details:
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/defaul...KB;EN-US;246133
Your feedback on this feature is routed to the proper channel. In the
meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have further questions on the issue, please
feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
I need to synchronise logins between 2 SQL2005 instances on a regular basis
- while you can copy the passwords to the destination using the "HASHED"
keyword on the CREATE LOGIN command, it appears that "HASHED" is missing fro
m
the ALTER LOGIN command. This means that I can't just update the password
when a user on the source instance changes the password on their SQL
authenticated account.
I also looked at using the transfer logins task in SSIS, but then read the
following in book online:
"At the destination, the transferred logins are disabled and assigned random
passwords. A member of the sysadmin role on the destination server must
change the passwords and enable the logins before the logins can be used. "
Apart from recording the login's roles, database access, and permissions,
dropping the login, creating with the hashed password, and re-applying the
roles/database access/permissions - has anybody found a way to just apply a
new hashed password to the login?
AndyHello Andy,
Thank you for posting in MSDN newsgroup!
I understand that you'd like to transfer logins and passwords between SQL
2005 instances. You just want to apply a new hashed password to the logins.
If I'm off-base, please let me know.
I think there is no simple method to apply a new hased password as you
want. You need to drop/recreate the logins by using script and grant it the
proper permission. You have to know the original password so that you could
change it to the new password by using "Alter login" statement.
You may want to refer to the following articles for more details:
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/defaul...KB;EN-US;246133
Your feedback on this feature is routed to the proper channel. In the
meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have further questions on the issue, please
feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, February 13, 2012
"Drop User" statement does not seem to work
I restored a copy of the production database onto the staging db server. Som
e
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone encoun
tered
this issue? Thanks.Check out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:FF46C63C-56E8-41F7-B238-2E0DA602E209@.microsoft.com...
I restored a copy of the production database onto the staging db server.
Some
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone
encountered
this issue? Thanks.
e
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone encoun
tered
this issue? Thanks.Check out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:FF46C63C-56E8-41F7-B238-2E0DA602E209@.microsoft.com...
I restored a copy of the production database onto the staging db server.
Some
of the production database users which need to be removed did not seem to
work when I issued "drop user [username]" statement. I also tried
"sp_dropuser [username]" and that did not work either. Has anyone
encountered
this issue? Thanks.
Thursday, February 9, 2012
"Cannot Open Database" problem when copy project to IIS
Hi all.
I use VWD 2005 Express with SQL Server 2005 Express as a database.
I copy my project to IIS and try to run it at the IIS Web Server.
I already amended the connection string at web.config file as below:
<connectionStrings> <add name="GeekSpeakConnectionString" connectionString="Data Source=SEN-M09\SQLEXPRESS;Database=GeekSpeak.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /></connectionStrings>
But, I got this error:
Cannot open database "GeekSpeak.mdf" requested by the login. The login failed.
Login failed for user 'SEN-M09\ASPNET'.
I also has granted to userSEN-M09\ASPNET .Still, I got this error. Why?
I manage to solve this problem after reading the articlehere.
Thanks.
"Bulk copy insert failed"
I'm copying data from tables in one server to tables in another via DTS.
This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.
When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.
Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.
But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?
This package have worked fine before, but now (after having refreshed the database information, and re-selected the tables), I'm getting a SQL-DMO: The Bulk Copy execution failed, without no further details.
When selecting a limited subset of the table, I got no errors.
Might be just one of the tables maybe that's causing the error, but there are so many tables so it takes some time to pinpoint which one.
Any hints?Can you post the actual dts package or be more specific in how the package works ?|||I was just going to delete my posting. It was really simple - a column in a table was defined as varchar(50) in the source database but varchar(20) in the destination. I thought the data would "just" be truncated.
After altering the destination column to 50, the package worked fine.
But, the error message really gave no help at all, and the error message info stored in msdb was just blank.
Is the only way to get a proper error message to do some coding in VBscript?
Friday, January 27, 2012
Database Scripting Question
I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.
DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>
|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>
|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
Database Scripting Question
I just used Enterprise Manager to create a script of my database. My goal is
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
to take this script and run it on another server to create a copy of the
database. When I ran the script to re-create the database, I got numerous
errors because Enterprise Manager created the script in such a way that
database objects are not created in the correct order. For example, in the
script EM created, it attempts to create a stored procedure which references
a view that hasn't been created yet. Obviously, EM should have scripted the
view first and then the stored proc.
Is there anything I can do about this? Or, is there a tool I can purchase
that scripts objects in the correct order? My goal here is to script my
database and then execute the script and create a database that is a clone
of the original.
Thanks very much.DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
script out the db in the right order.
Andrew J. Kelly SQL MVP
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>I just used Enterprise Manager to create a script of my database. My goal
>is to take this script and run it on another server to create a copy of the
>database. When I ran the script to re-create the database, I got numerous
>errors because Enterprise Manager created the script in such a way that
>database objects are not created in the correct order. For example, in the
>script EM created, it attempts to create a stored procedure which
>references a view that hasn't been created yet. Obviously, EM should have
>scripted the view first and then the stored proc.
> Is there anything I can do about this? Or, is there a tool I can purchase
> that scripts objects in the correct order? My goal here is to script my
> database and then execute the script and create a database that is a clone
> of the original.
> Thanks very much.
>|||It sounds like you depenency records are messed up.
Check this site out, the tools are nice will do what you need.
http://www.wingenious.com/sqltools/index.html
Hello Amos,
> I just used Enterprise Manager to create a script of my database. My
> goal is to take this script and run it on another server to create a
> copy of the database. When I ran the script to re-create the database,
> I got numerous errors because Enterprise Manager created the script in
> such a way that database objects are not created in the correct order.
> For example, in the script EM created, it attempts to create a stored
> procedure which references a view that hasn't been created yet.
> Obviously, EM should have scripted the view first and then the stored
> proc.
> Is there anything I can do about this? Or, is there a tool I can
> purchase that scripts objects in the correct order? My goal here is to
> script my database and then execute the script and create a database
> that is a clone of the original.
> Thanks very much.
>|||Hello Andrew.
not quite - but almost. The scripter tool will script out the individual
objects and the build component will build a database (give it any name you
like) using these scripts and building it in the correct order and this all
can be recorded into a script to use which is the goal of this discussion.
"Andrew J. Kelly" wrote:
> DBGhost from http://www.innovartis.co.uk/home.aspx claims to be able to
> script out the db in the right order.
> --
> Andrew J. Kelly SQL MVP
>
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:Hcmdnb5cILblc8bfRVn-sQ@.buckeye-express.com...
>
>
Subscribe to:
Posts (Atom)
