sql server db: dts

Showing posts with label dts. Show all posts

Sunday, March 25, 2012

copying tables in SQL SERVER

I have a 100m row table that I need to come from one database to another database in SQL SERVER.

The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)

and on a 100m row table it is taking around 52 hours. Not acceptable.Is it on the same server?

bcp out in native format and bcp in is probably the fastest

But the SELECT * INTO is a minimally logged operation...and if it's on the same server...

I'm not so sure bcp would beat it since it's 1 operation as compared to 2.|||Yes it is on the same server.|||For 100 million rows, I think I'd partition it up anyway...

What's the DDL of the table...is it a heap or does it have a pk?

If it's got something unique, I'd split it up in to 10 tables and thread out the SELECT Collist INTO Table1
SELECT Collist INTO Table2
SELECT Collist INTO Table3
SELECT Collist INTO Table4
ect

And run all 10 at the same time from 10 separate osql bat files...

thats 1 select per file....

How long does a backup take?|||Hello Everyone,

If you would like to copy records within the same database between the different databases the you can use this query,

insert into <table Name> select * from <Destination Table>|||insert into <table Name> select * from <Destination Table>The only problem is that this syntax is fully logged, while the SELECT INTO syntax is only minimally logged. Since the SELECT INTO appears to be too slow and I'd expect this to be even slower, I don't think it would be a good solution.

-PatP|||Damn...I wonder if that belongs here...

http://thedailywtf.com/archive/2004/09/01/1511.aspx

copying tables DTS vs SSIS - speed!!!

Hi,
I'm trying to create a package with SSIS to replace the DTS process that we have in place already.
DTS package copy four table content from one server to another. I have created a simple SSIS to do the same processes but the process it alot slower than DTS!!

I did ran the SSIS package using ctrl+F5 and also from command prompt but still it's quite slow.
SSIS uses SMO to access to server and both are running on 2005

ThanksUse the "Table or view - fast load" option in your OLE DB Destinations.|||sorry for my ignorance, but is this an SSIS property? and if yes where can I find it
Thanks|||In the OLE DB Destination, it is a drop down option titled "data access mode". Double click on the OLE DB destination and it's on the main page.|||the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers|||

Kolf wrote:
the problem is that I am using the transfer SQL server object task which it uses SMO by default not OLEDB. unless there is another way to copy the content of a table.
p.s. the tables do not exist on the remote server the transfer SQk server objects task, creates the table as well as copying the content.
cheers

Remember that SSIS is a data manipulation tool - not a schema manipulation tool. Hence, there isn't THAT much support for moving schema objects about.

If I were you I would create the tables using conventional methods (i.e. CREATE TABLE scripts run from the Execute SQL Task) and then use data-flows to pump data between them. This will not be slower than DTS. It will be alot more maintainable as well.

-Jamie

|||Thanks for your advise,
the problem is the table schema changes each time as the selected tables will be different. Is there a task to be able to extract the schema of the source table, so I can apply it to the destination server.

and then maybe use the data flow to push the data across.

Thanks again|||Data flows don't handle changing metadata, unless you are building them dynamically.|||Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow.

The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.

cheers|||

Kolf wrote:
Thanks , but even if I build the metadata for the tables in the destination in the control flow (which is not a problem) then I should be able to feed the data with the dataflow.
The problem is I have different tables and they change each time so I should be able to write one generic dataflow and change the parameter with a script each time I'm running.
cheers

As I think we have discussed on other threads - you can do this.

Apologies if I've missed any of your replies. The alerting functionality of these forums is broken (for me anyway).

-Jamie

Thursday, March 22, 2012

copying stored procedures in SQL Server 2005?

Is there a quick way to copy a set of stored procedures in SQL Server 2005
from one database engine to another, as quickly as using DTS in SQL Server
2000? Other than restoring from a backup.
Thanks,
Dean SDean
In objects explorer details select stored procedures you want to copy to,
then right click and Script Stored Procedure as -- CREATE TO -- new
query, make sure that you under scope a target database and press F5
"Dean Slindee" <slindee@.charter.net> wrote in message
news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S|||On Mar 4, 11:23 pm, "Dean Slindee" <slin...@.charter.net> wrote:
> Is there a quick way to copy a set of stored procedures in SQL Server 2005
> from one database engine to another, as quickly as using DTS in SQL Server
> 2000? Other than restoring from a backup.
> Thanks,
> Dean S
I suggest that you can find a stored procedure named
dbo.sp_generate_script.
Using dbo.sp_generate_script, you can get all stored procedures in a
database.
GOOD LUCK.|||Does this procedure copy one stored procedure at a time, or multiple stored
procedures?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
> Dean
> In objects explorer details select stored procedures you want to copy
> to, then right click and Script Stored Procedure as -- CREATE TO -- new
> query, make sure that you under scope a target database and press F5
>
>
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in SQL
>> Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>|||Dean
multiple stored procedures
"Dean Slindee" <slindee@.charter.net> wrote in message
news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
> Does this procedure copy one stored procedure at a time, or multiple
> stored procedures?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Dean
>> In objects explorer details select stored procedures you want to copy
>> to, then right click and Script Stored Procedure as -- CREATE TO --
>> new query, make sure that you under scope a target database and press F5
>>
>>
>>
>> "Dean Slindee" <slindee@.charter.net> wrote in message
>> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in SQL
>> Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>>
>|||Uri
One answer to my question could have been:
Create a new SSIS package using the "Transfer SQL Server Objects" tool.
In the tool, fill in the parameters and connections and execute the package.
This is conceptually similar to the former DTS process.
One just has to keep redefining a "temp" SSIS package, or create several
"work" packages, per database.
Your process would be more useful for a quick transfer; no package required.
However, I have been unable to accurately follow your brief instructions.
Would you please expand on the instructions with numbered bullet points?
Like:
1. In SQL Server 2005 Management Studio, click on the Source database table's
Stored Procedures folder.
2. Select the stored procedures to be copied (I can only select one at a
time).
3. Right click and select Script Stored Procedure as CREATE TO:
4. Now, click on the Destination database table's Stored Procedures folder
5. Press F5 at..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBLTdbofIHA.4140@.TK2MSFTNGP04.phx.gbl...
> Dean
> multiple stored procedures
>
> "Dean Slindee" <slindee@.charter.net> wrote in message
> news:2B130575-80A1-40DA-A83C-C5A71D2AA39F@.microsoft.com...
>> Does this procedure copy one stored procedure at a time, or multiple
>> stored procedures?
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OwmbByffIHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Dean
>> In objects explorer details select stored procedures you want to copy
>> to, then right click and Script Stored Procedure as -- CREATE TO --
>> new query, make sure that you under scope a target database and press F5
>>
>>
>>
>> "Dean Slindee" <slindee@.charter.net> wrote in message
>> news:7D625CC4-05B4-4BF6-87F1-BA670532B06B@.microsoft.com...
>> Is there a quick way to copy a set of stored procedures in SQL Server
>> 2005 from one database engine to another, as quickly as using DTS in
>> SQL Server 2000? Other than restoring from a backup.
>> Thanks,
>> Dean S
>>
>

Tuesday, March 20, 2012

copying server objects

I want to copy a database from one server to another. I'm happy about
how to do this but also want to copy a number of DTS packages, jobs and
alerts that relate to this database. Is there any way that I can copy
them or will I need to create them again on the new server.

Many Thanks

Laurence BreezeYou can copy DTS packages by opening up the current package and choosing
"Package/Save As..." from the menu bar. Be careful to make sure your
database references within the DTS package are still appropriate on the
new server.

Both jobs and alerts can be scripted and that script executed on the new
server - right click the jobs or alerts and choose "All Tasks/Generate
SQL Script...". More than one job or alert can be selected at a time if
you desire.

Good luck,
Tony Sebion

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk:

> I want to copy a database from one server to another. I'm happy about
> how to do this but also want to copy a number of DTS packages, jobs and
> alerts that relate to this database. Is there any way that I can copy
> them or will I need to create them again on the new server.
> Many Thanks
> Laurence Breeze|||Hi

You may want to read
http://support.microsoft.com/defaul...b;en-us;Q314546

John

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk...
>I want to copy a database from one server to another. I'm happy about how
>to do this but also want to copy a number of DTS packages, jobs and alerts
>that relate to this database. Is there any way that I can copy them or
>will I need to create them again on the new server.
> Many Thanks
> Laurence Breezesql

Monday, March 19, 2012

Copying DTS Packages to new server

Is there any way/an easy way to copy dts packages from one server to another? I cant seem to find a way to script them, is there any other way other than recreating them on the new server?
Regards JimPackages are saved in msdb..sysdtspackages - you just need to copy the relevant rows from that table.
Note that sysdtspackages is undocumented so this may not work in the future.

If you prefer this is an SP to load all packages and save them to files - just change the save to save to a server. This will lose the graphics as there is no context for it and no way at present to load the stream.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@.Path varchar(128)
as
/*

*/

set nocount on

declare @.objPackage int
declare @.PackageName varchar(128)
declare @.rc int
declare @.ServerName varchar(128)
declare @.FileName varchar(128)
declare @.FilePath varchar(128)
declare @.cmd varchar(2000)

select @.ServerName = @.@.ServerName ,
@.FilePath = @.Path

if right(@.Path,1) <> '\'
begin
select @.Path = @.Path + '\'
end

-- create output directory - will fail if already exists but ...
select @.cmd = 'mkdir ' + @.FilePath
exec master..xp_cmdshell @.cmd

create table #packages (PackageName varchar(128))
insert #packages
(PackageName)
select distinct name
from msdb..sysdtspackages

select @.PackageName = ''
while @.PackageName < (select max(PackageName) from #packages)
begin
select @.PackageName = min(PackageName) from #packages where PackageName > @.PackageName

select @.FileName = @.FilePath + @.PackageName + '.dts'

exec @.rc = sp_OACreate 'DTS.Package', @.objPackage output
if @.rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @.rc)
return
end

exec @.rc = sp_OAMethod @.objPackage, 'LoadFromSQLServer' , null,
@.ServerName = @.ServerName, @.Flags = 256, @.PackageName = @.PackageName
if @.rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

-- delete old file
select @.cmd = 'del ' + @.FileName
exec master..xp_cmdshell @.cmd, no_output

exec @.rc = sp_OAMethod @.objPackage, 'SaveToStorageFile', null, @.FileName
if @.rc <> 0
begin
raiserror('failed to save package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

exec @.rc = sp_OADestroy @.objPackage
end
go

Copying DTS Packages across Servers

HI All,

Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.

I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.

moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.

Any assistance here is greatly appreciated.

Cheers
TroyHi Troy,

You can save the packages as a structure file and save them to disk and then transfer them to your new server...

Or,.. you could get cunning and write a quick app to do it. I have some asp pages that I have written that will do this and I can flick them your way if you want... they will probably need tweaking to suit your needs...

What's the weather like in Auckland today??|||Hey thanks for that.
I will go and have a play and see what I can do - I assume it is a pretty straight forward process dumping them to file? And then reimporting them?

As for the ASP page - ummm errr, would love to take you up on that offer, but sadly I am about as literate with that sort of stuff as Cinderalla with a grenade laucher. (unless it is blatantly obvious). What are your thoughts on this?

Hmmm as for the weather - well all in all okay, and it is meant to be good through the weekend. Which will make a nice change because our new pool should have a chance to dry up and return to what was our lawn :(
Hopefully better in Aust.|||It's a relatively simple asp page,... the key thing is the SQL that we use. You could probably write it up as another stored proc or something to carry out the same function... ASP is a piece of cake to understand assuming some general knowledge of scripting languages...

Have a think about it and let me know.

Weather in Sydney today is overcast and cool,... not raining yet though... I think my cousin who is visiting from Chch brought this cloud with him...

Sunday, March 11, 2012

Copying database using "Copy Database" - still failing after SP2

I am struggling with the "Copy Database Wizard" in SQL Server 2005. I am used to working with DTS to copy data from one server to another, but with the new tools, I am having serious problems (in fact, it's unusable)

1. When copying a database using the "SQL Management Object" method, I always get errors concerning logins. If I select not to copy logins, I get errors "sp_grantdbaccess failed". If I do select to copy logins, the logins can't be created because the machine names are different (one the source machine, the login is called MACHINE1/mylogin, but on the destination machine, such a login cannot be created)

2. For small databases, I ALWAYS get the error "CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database". There is no way to specify a larger size for the database in the wizard.

Note: copying databases using detach/attach is not an option (the servers cannot see eachother's shares)

I must say that in general I hate the new management tools. Enterprise Manager had its flaws too, but the quality of the "SQL Server Management Studio" seems very low IMHO.

Regards,
Philippe Leybaert

I can get the copy to work but it will not copy any of my stored procedures or functions.|||did you ever get a resolution on this. I am having the same issue.|||Yeah - this sucks. Has anyone seen where SP's can be copied? Sure we can script them and run in teh new DB - but it's a pain in the butt, especially if you can see shared drivespace.|||

Blake05 wrote:
I can get the copy to work but it will not copy any of my stored procedures or functions.

This is actually very simple, but the error messages are of no help. The problem is that there is a bug in at least one stored procedure or function. Go through them all until you find the ones that you can't save/update, and repair or delete (if obsolete) them. Once they are all functioning, the copy will work without a hitch.

|||I notice that no one from Microsoft has answered this problem.

It seems to me the Copy Database Wizard is completely ignoring the fact that I am always trying to deselect the logins. It just happily tries to copy them even though it's going to a development machine that will only see 4 of the 100 logins on the production server.

I struggled with this problem for days and was crossing my fingers that it would be solved in SP1, sadly it's not.

Detaching is not an option for production databases!!!

Fix this Microsoft!!!|||

We are aware of many issues in the Copy Database Wizard. We're doing a lot of work on the wizard in SP2 to address these.

If you have a specific scenario you would like to see addressed, please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx. Defects reports entered at the feedback center are used to prioritize work for future versions and service packs.

Thanks,
Steve

|||

Hi Philippe,

You can now install the latest version of the Copy Database Wizard from the following location:

http://www.microsoft.com/sql/ctp.mspx

This CTP version includes many improvements and hopefully they should address most of your current issues.

Thanks,
Oscar.

|||

Hello Oscar

I have a problem with Copy Database Wizard to.

Have agent account as sysadmin in both source and target database

Target server has CTP Dec 2006 installed. Bat I can’t install it to the source “production” system

Managed to transfer five databases fixing the error found at the error log file

At the sixth database I have an error that I can not understand

The error appears when trying to create a view at the target system.

errorCode=-1073548784 description=Executing the query "create view [dbo].[VShift_Cost]

select sf.ShiftID, SFD.UnitID, sum(sfd.charge) as Shift_Charge

from shiftcostdetail sfd inner join shiftcost sf on sfd.shiftcostid = sf.shiftcostid

group by sf.shiftid, SFD.unitID

" failed with the following error: "Invalid object name 'shiftcostdetail'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The shiftcostdetail is an existing table and the view works fine at the source system.

Can you help?

Thanks

|||

Hi Panzin,

Could you please tell me where are you launching CDW from? You should launch CDW from the target machine is this machine is the one that has the latest version installed. From here, you can select the source the database and perform the copy. Could you please confirm that this is what you are doing? You could also select the "Save Transfer Logs" option before clicking "Finish" and send me the corresponding TransferDump*.sql files generated in :

C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\Smo

It would be useful too to select "Write log to a file" before performing the copy and check for any other errors.

I will investigate the error that you described above, but it seems that it could be a dependency issue. I will get back to you once I find more information.

Thanks,
Oscar.

|||

Hi Oscar

Thank you for your reply

The CDW is running from the target system.

The target system is the one with the CTP installed

The posted error is from the log file

I can't find the "Save Transfer Logs" option

Thank's again

|||

Hi Panzin,

Are you using the SMO Transfer method to copy the database or Detach/Attach? I believe you are using the SMO Transfer method...

The "Save transfer logs?" checkbox is in the "Configure the Package" page of the wizard.

By the way, are you also copying any extra objects apart from the database? If so, which ones? Are you referencing any objects that are outside this view that might require the selection of some particular extra objects?

Cheers,
Oscar.

|||

Hello Oscar

Thank you for your help

I'm using the SMO transfer method and the related objects that i'm copying are the "logins"

At the configure the Pacage page I'm using

Logging options "Text File" and the posted error is from the text file ...

also show that

1. db created at target server is at compatibility level 90 (source level=80)

2. when I changed the view script and add "dbo." in front of table names the error move to next view creation.

!!! this does not work for all views

Cheers

Nick

|||

Hi Nick,

I would really need to see the full log files (i.e. TransferDump*.sql and SMO Tranfer log) in order to be able to help more. You could send them directly to me at oscar.naim@.microsoft.com.

By the way, are you defining extended properties for this particular database?

Thanks,
Oscar.

|||

Hello oscar

I do not have any extended properties at my database.

I realy can;t find any files named TransferDump*.* in my disk

I found a directory SMO but it is empty

the directory is created under the SQLAgent acount documents and settings...

I monitor the directory as the CDW is executed and a file was created

but when the execution stops with or with out an error the file is deleted

so I can;t postit to you

Thank you

Copying database using "Copy Database"