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 the database

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
UdayIf you have Enterprise Manager, right-click on the database, All Tasks -> Export Data.

The source should already be selected, Click next.

Set up the destination to a SQL Server that you have control over and create a <new> database. Type in a name, click OK, then click Next.

Select Copy objects and data, click Next.

Select run immediately, click Next. Then you'll need to click OK somewhere in there.|||Hi,

I did that but I am not able to copy the stored procedures and views, when I try to generate query it gives me

/****** Encrypted object is not transferable, and script can not be generated. ******/

How can I copy this stored procedures and views.

Thanks in advance,
Uday.

Copying procedures in SQL from one server to another using VBA in excel

Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...

You can use SMO to do that and in SQL Server SMO/DMO section you can find , here , an example to copy a table between different instances; i think you can adapt it for sp.

|||SMO/DMO will certainly work as ggciubuc has mentioned if you'd like to continue using VBA, however there are a number of other tools out there that will make it easier.

Management Studio has the generate scripts menu item that will script out the database object so you can run the script against another server.

Red-Gate and other tools manufacturers have compare utilities that examine the database objects from one server and compare them to the objects of another server, scripting out the differences and even applying them if you wish.

Data Transformation Services (DTS) in SQL Server 2000 has a task that allows you to migrate database objects from one server to another.

HTH...

Joe

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
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
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> 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
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

copying data to SQL Server from an Oracle database

What is the most straightforward way to copy tables and views from an oracle database into a sql server 2005 database?

thanks for any suggestions.

Marilyn

Mount your Oracle server and database as a linked database in your SQLServer environment. Then perform a standard insert-select (or select into).

If you also must make data transformations you should use DTS (or what's the name nowadays is).

Best regards

Nils Nordenbrink

|||

Probably the easiest method is to use the SQL Server 2005 Import and Export Wizard. Create the database you want to hold the Oracle data in SQL Server 2005. Right click on the database from Management Studio, select Tasks, Import Data. Click Next on the first Wizard dialog. Choose either .Net Framework Data Provider for Oracle or Oracle Provider for OLEDB. Proceeding further with the wizard should allow you to select the data of interest.

Best of luck!

|||

hi steve,

i'm using sql server express edition and have the same problem, however the "import data" task is not available on this version.

are you aware of any other options for express edition?

this is my current set up:

sql server 2005 express edition,
sql server management studio express,
oracle tables (source data currently linked through odbc)
adp database file (new db where i'm trying to import the oracle tables)

i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.

i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

|||

It really depends on your purpose for making a copy of 1m record tables. That is something that should be avoided if possible. For a one-time shot ease of use is the prime consideration, even if it take a (little) longer. But to do this every week or every day is not a great design.

DTS is essentially the SQL Server 8.0 version of the "import data" functionality and if you have SQL Server 8.0 you might try that option.

If your primary purpose is simply to back up the data, then you might just use a backup process which will probably be much faster. If the primary purpose is mainly to query the data, then linked servers are probably what you want - no copy required, just link the Oracle server and execute your query in SQL Server against the Oracle data. This is the "fastest" option for occasional queries or to join data in SQL Server. But of course each query to Oracle will transfer the result over the wire to SQL Server, which is not the best for high performance applications that are doing many large queries repetitively. In that case copying the data once will then allow better performace as the application runs, at the expense of the copy and the issue of "aging" of the data. With the linked server the data is accurate at the time of the query.

One suggestion would be simply to upgrade your server if the import data functionality is not supported on SQL Express. Spending a bunch of time trying to create alternative workarounds with associated maintenance is probably not really cost effective.

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canadaFirst, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 20
05
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 200
0.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
>|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>|||db wrote:
> access data from a remote server from within a query. Should a linked serv
er
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and
I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed que
ry
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039
)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
--
ontario, canadaFirst, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 2005
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:
> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
> > Hi
> >
> > I have few views on SQL server 2000, which I want to copy to sql server 2005
> > database. I want to schedule a job to do it every evening.
> >
> > What is the best method to do this?
> >
> > Thanks
> >
> > ontario
> >
> >
> >
> >
> >
> >
> >
>|||access data from a remote server from within a query. Should a linked server
be a good idea.
--
ontario, canada
"db" wrote:
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 2000.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
> > First, why do you need to copy the views every day? A view is just a
> > definition, and really shouldn't change on a daily basis. With that
> > said, the process is:
> >
> > Script the view definition to a file from SQL 2000
> > Copy script file to new server
> > Execute script on SQL 2005
> >
> > Another option is to use SSIS on the 2005 box and create a package to
> > move the objects. Search BOL for additional information on how to set
> > this up.
> >
> > Jeff
> >
> > db wrote:
> > > Hi
> > >
> > > I have few views on SQL server 2000, which I want to copy to sql server 2005
> > > database. I want to schedule a job to do it every evening.
> > >
> > > What is the best method to do this?
> > >
> > > Thanks
> > >
> > > ontario
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
>> Hi Jeffery
>> I want to copy data that is generated by view defination on sql server
>> 2000.
>> When I execute the script to create table on another server I have
>> specify
>> correct path (Server name, database name etc) Example to execute
>> CREATE VIEW viewname AS
>> select a,b,c
>> from d,e
>> where d.y=e.c
>> What would be correct syntex (With server name, database name etc)
>> CREATE table tablename AS
>> select a,b,c
>> from server.database.d, server.database.e
>> where d.y=e.c
>> Can I do this directly by executing a query or have to use SSIS,
>> import/export or replication.
>>
>>
>> --
>> ontario, canada
>>
>> "Jeffrey Williams" wrote:
>> > First, why do you need to copy the views every day? A view is just a
>> > definition, and really shouldn't change on a daily basis. With that
>> > said, the process is:
>> >
>> > Script the view definition to a file from SQL 2000
>> > Copy script file to new server
>> > Execute script on SQL 2005
>> >
>> > Another option is to use SSIS on the 2005 box and create a package to
>> > move the objects. Search BOL for additional information on how to set
>> > this up.
>> >
>> > Jeff
>> >
>> > db wrote:
>> > > Hi
>> > >
>> > > I have few views on SQL server 2000, which I want to copy to sql
>> > > server 2005
>> > > database. I want to schedule a job to do it every evening.
>> > >
>> > > What is the best method to do this?
>> > >
>> > > Thanks
>> > >
>> > > ontario
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> >|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > access data from a remote server from within a query. Should a linked server
> > be a good idea.
> >
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed query
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > I want to move some data from one server to another server by an automated
> > process every evening. On server one that data reside in three tables and I
> > select desired information by a view defination.
> >
> > The server name has a "-", like "abc-def" because of which distributed query
> > is giving me a error.
> >
> >
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
--
ontario, canada
"Jeffrey Williams" wrote:
> db wrote:
> > I want to move some data from one server to another server by an automated
> > process every evening. On server one that data reside in three tables and I
> > select desired information by a view defination.
> >
> > The server name has a "-", like "abc-def" because of which distributed query
> > is giving me a error.
> >
> >
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy views from one database server to another

Hi
I have few views on SQL server 2000, which I want to copy to sql server 2005
database. I want to schedule a job to do it every evening.
What is the best method to do this?
Thanks
ontario
ontario, canada
First, why do you need to copy the views every day? A view is just a
definition, and really shouldn't change on a daily basis. With that
said, the process is:
Script the view definition to a file from SQL 2000
Copy script file to new server
Execute script on SQL 2005
Another option is to use SSIS on the 2005 box and create a package to
move the objects. Search BOL for additional information on how to set
this up.
Jeff
db wrote:
> Hi
> I have few views on SQL server 2000, which I want to copy to sql server 2005
> database. I want to schedule a job to do it every evening.
> What is the best method to do this?
> Thanks
> ontario
>
>
>
>
|||Hi Jeffery
I want to copy data that is generated by view defination on sql server 2000.
When I execute the script to create table on another server I have specify
correct path (Server name, database name etc) Example to execute
CREATE VIEW viewname AS
select a,b,c
from d,e
where d.y=e.c
What would be correct syntex (With server name, database name etc)
CREATE table tablename AS
select a,b,c
from server.database.d, server.database.e
where d.y=e.c
Can I do this directly by executing a query or have to use SSIS,
import/export or replication.
ontario, canada
"Jeffrey Williams" wrote:

> First, why do you need to copy the views every day? A view is just a
> definition, and really shouldn't change on a daily basis. With that
> said, the process is:
> Script the view definition to a file from SQL 2000
> Copy script file to new server
> Execute script on SQL 2005
> Another option is to use SSIS on the 2005 box and create a package to
> move the objects. Search BOL for additional information on how to set
> this up.
> Jeff
> db wrote:
>
|||access data from a remote server from within a query. Should a linked server
be a good idea.
ontario, canada
"db" wrote:
[vbcol=seagreen]
> Hi Jeffery
> I want to copy data that is generated by view defination on sql server 2000.
> When I execute the script to create table on another server I have specify
> correct path (Server name, database name etc) Example to execute
> CREATE VIEW viewname AS
> select a,b,c
> from d,e
> where d.y=e.c
> What would be correct syntex (With server name, database name etc)
> CREATE table tablename AS
> select a,b,c
> from server.database.d, server.database.e
> where d.y=e.c
> Can I do this directly by executing a query or have to use SSIS,
> import/export or replication.
>
>
> --
> ontario, canada
>
> "Jeffrey Williams" wrote:
|||Views do not contain data, they just reference them. Copying the views
won't copy the data.
"db" <db@.discussions.microsoft.com> wrote in message
news:94D1A6B0-5FE4-4C5A-B3D0-090CA9E770DC@.microsoft.com...[vbcol=seagreen]
> access data from a remote server from within a query. Should a linked
> server
> be a good idea.
> --
> ontario, canada
>
> "db" wrote:
|||db wrote:
> access data from a remote server from within a query. Should a linked server
> be a good idea.
>
I do not know what you are trying to accomplish. Are you trying to move
data from one server to another? Access data on server1 from server2?
If all you need to do is access data on a different server, then a
linked server might be the solution. Once the linked server is setup,
you can access the data using four-part naming (e.g. select <columns>
from server2.database.schema.table)
If you need to move the data to the other server, I would suggest
looking at SSIS to extract/import the data. This can also be done using
linked servers, but you have much more control using SSIS.
Jeff
|||I want to move some data from one server to another server by an automated
process every evening. On server one that data reside in three tables and I
select desired information by a view defination.
The server name has a "-", like "abc-def" because of which distributed query
is giving me a error.
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> I do not know what you are trying to accomplish. Are you trying to move
> data from one server to another? Access data on server1 from server2?
> If all you need to do is access data on a different server, then a
> linked server might be the solution. Once the linked server is setup,
> you can access the data using four-part naming (e.g. select <columns>
> from server2.database.schema.table)
> If you need to move the data to the other server, I would suggest
> looking at SSIS to extract/import the data. This can also be done using
> linked servers, but you have much more control using SSIS.
> Jeff
>
|||db wrote:
> I want to move some data from one server to another server by an automated
> process every evening. On server one that data reside in three tables and I
> select desired information by a view defination.
> The server name has a "-", like "abc-def" because of which distributed query
> is giving me a error.
>
Well, that is not copying views - that is moving data which can be done
through a linked server or through SSIS. I would recommend SSIS because
you have many more options.
What version of SQL are you using? If you are using SQL Server 2005 you
can create the linked server and then setup synonyms for each object you
want to access on the other system. Using synonyms you could setup the
following:
Linked Server name: abc-def
Synonym: ServerA.ObjectA
As [abc-def].remotedatabase.schema.object
And then access that object in code with:
Select <columns> From ServerA.ObjectA.
Jeff
|||On source server I am using: SQL server 2000 standard edition SP4 (8.00.2039)
On destination server I am using: SQL server standard edition 2005
(9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>
|||Source server : SQL server 2000 standard edition SP4 (8.00.2039)
Destination server: SQL server standard edition 2005 (9.00.1399.06)
ontario, canada
"Jeffrey Williams" wrote:

> db wrote:
> Well, that is not copying views - that is moving data which can be done
> through a linked server or through SSIS. I would recommend SSIS because
> you have many more options.
> What version of SQL are you using? If you are using SQL Server 2005 you
> can create the linked server and then setup synonyms for each object you
> want to access on the other system. Using synonyms you could setup the
> following:
> Linked Server name: abc-def
> Synonym: ServerA.ObjectA
> As [abc-def].remotedatabase.schema.object
> And then access that object in code with:
> Select <columns> From ServerA.ObjectA.
> Jeff
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'
Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>
|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>> We have been using the 'Copy SQL Server Objects' with success for some
>> time to copy an entire database to another server. Recent changes to our
>> database infrastructure cause DTS to fail:
>> 1) We have a check constraint which uses a user function (which refers to
>> the table on the check constraint
>> 2) We have a view which refers to another view
>> DTS fails on both. DTS fails on the check constraint and stops. if I
>> remove the check constraint it will fail on the view. See errors below.
>> Any hints how to solve this would be appreciated.
>> Jonathan Orgel
>> Errors:
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'dbo.SRS_NumberRWSubjects'
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'VMSGRECIPIENTS2'
>>
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>