COPY DB

I'm trying to copy a database using the copy db wizard. The source and
destination are both sql 2000. I'm getting the following error message.
CDW Database task Step Fails
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
TASK DETAIL
FAILED TO CREATE THE SHARE OMWWIZCThe SQL Admin account has to have permissions to access teh file system on
the other machine, not just the other SQL Server.
Robert.
"sql" wrote:
> I'm trying to copy a database using the copy db wizard. The source and
> destination are both sql 2000. I'm getting the following error message.
> CDW Database task Step Fails
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Unspecified error
> Step Error code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
>
> TASK DETAIL
> FAILED TO CREATE THE SHARE OMWWIZC

Copy Database, can't attach it?

I'm used the Copy Database Wizard to copy a database from one server to
another. While in the copy operation the destination machine was hanging and
I had to reset it.
Now when I want to attach the source database again, I get the following
error:
"CREATE FILE encountered operating system error 5 (Access is denied.)..."
What is going on here? Why was the source machine affected in this
destructive manner?
What do I need to do in order to attach my source again?
OlavI generally find that it's 10x easier to shoot yourself in the foot and get
into serious trouble with SQL Server 2005 than it was with SQL Server 2000.
Olav
"Olav" <x@.y.com> wrote in message
news:O0Fj8YZXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> I'm used the Copy Database Wizard to copy a database from one server to
> another. While in the copy operation the destination machine was hanging
> and I had to reset it.
> Now when I want to attach the source database again, I get the following
> error:
> "CREATE FILE encountered operating system error 5 (Access is denied.)..."
> What is going on here? Why was the source machine affected in this
> destructive manner?
> What do I need to do in order to attach my source again?
> Olav
>|||I read somewhere detaching databases is very risky, and can infact
corrupt the entire database if something goes wrong... Good luck to
you.|||?
Nice if that is the default option the Copy Database Wizard is using then?
Olav
"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
>|||Stop me if im boring you ...
Basically a database created using created database Test consists of 2 files
mdf for tables
ldf for transaction logs
In effect detaching a database is what happens when you stop sql server
attaching is what happens when you start sql server
so dont be afraid of this process
Be aware though that ...
Any transactions not written down to the mdf file and still in the ldf
transaction logs will need to be recovered by SQL server.
to limit this complication we
stop all users
run checkpoint 3 times to for those transactions down into mdf
then detach
at this point the logfile is emptied and can be ditched on attach
Ever wanted to shink you log file. then this method can be employed.
Note I would not used this method on prodction systems crital to business..
Lifes too short and its a hassel geting another job.
If you detached thdatabase then you basicall have 2 files
If a file gets chopped on a copy you would normally tidy up and resend file.
hope this helps
"Olav" wrote:

> ?
> Nice if that is the default option the Copy Database Wizard is using then?
> Olav
> "KBuser" <Kyle.Buser@.gmail.com> wrote in message
> news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>
>|||"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
No, detaching a database should be perfectly fine.
But you need to copy both the MDF and LDF files and attach both.

>

Copy Database, can't attach it?

I'm used the Copy Database Wizard to copy a database from one server to
another. While in the copy operation the destination machine was hanging and
I had to reset it.
Now when I want to attach the source database again, I get the following
error:
"CREATE FILE encountered operating system error 5 (Access is denied.)..."
What is going on here? Why was the source machine affected in this
destructive manner?
What do I need to do in order to attach my source again?
OlavI generally find that it's 10x easier to shoot yourself in the foot and get
into serious trouble with SQL Server 2005 than it was with SQL Server 2000.
Olav
"Olav" <x@.y.com> wrote in message
news:O0Fj8YZXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> I'm used the Copy Database Wizard to copy a database from one server to
> another. While in the copy operation the destination machine was hanging
> and I had to reset it.
> Now when I want to attach the source database again, I get the following
> error:
> "CREATE FILE encountered operating system error 5 (Access is denied.)..."
> What is going on here? Why was the source machine affected in this
> destructive manner?
> What do I need to do in order to attach my source again?
> Olav
>|||I read somewhere detaching databases is very risky, and can infact
corrupt the entire database if something goes wrong... Good luck to
you.|||?
Nice if that is the default option the Copy Database Wizard is using then?
Olav
"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
>|||Stop me if im boring you ...
Basically a database created using created database Test consists of 2 files
mdf for tables
ldf for transaction logs
In effect detaching a database is what happens when you stop sql server
attaching is what happens when you start sql server
so dont be afraid of this process
Be aware though that ...
Any transactions not written down to the mdf file and still in the ldf
transaction logs will need to be recovered by SQL server.
to limit this complication we
stop all users
run checkpoint 3 times to for those transactions down into mdf
then detach
at this point the logfile is emptied and can be ditched on attach
Ever wanted to shink you log file. then this method can be employed.
Note I would not used this method on prodction systems crital to business..
Lifes too short and its a hassel geting another job.
If you detached thdatabase then you basicall have 2 files
If a file gets chopped on a copy you would normally tidy up and resend file.
hope this helps
"Olav" wrote:
> ?
> Nice if that is the default option the Copy Database Wizard is using then?
> Olav
> "KBuser" <Kyle.Buser@.gmail.com> wrote in message
> news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> >I read somewhere detaching databases is very risky, and can infact
> > corrupt the entire database if something goes wrong... Good luck to
> > you.
> >
>
>|||"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
No, detaching a database should be perfectly fine.
But you need to copy both the MDF and LDF files and attach both.
>

Copy Database Wizard Default Destination Folder

We have installed Microsoft SQL Server 2005 to the deafult folder. However, we have another larger drive for our database files. We have changed the Server Properties > Database Settings to have the correct "Database default locations" that we want. However, when we use the Copy Database Wizard to bring over the many user databases on our existing server, the wizard always uses the original installation folder as the Destination Folders.

Is this a bug, or is there some way to change the defaults for these folders so we don't have to on every one?

Thanks

I have the same problem but by mistake I've found a way to modify the destination folder. On the grid with the destination folder click the one with the path. A label will appear over the grid and then you could edit the path. Cheers and hope that this will help you.

copy database from sql server 2000 to 2005

I am trying to copy a database from 2000 to 2005 using the copy database wizard. I am a sys admin on both the source and destination server and am using the windows auth method and sql management object method. I am not copying any database objects.

Following is the report: Any suggestions?

Performing operation...

- Add log for package (Success)

- Add task for transferring database objects (Success)

- Create package (Error)

Messages

No description found (Copy Database Wizard)

- Start SQL Server Agent Job (Stopped)

- Execute SQL Server Agent Job (Stopped)

Rookie,

did you install SQL05 to anything other than C: ?

Is your SQL05 running on Win03r2 ? Is the machine a AD DC ?

See:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=185560&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=182988&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=127672&SiteID=1

MikeC

|||

It is installed on c: I am using the client machine.

Windows 2003 enterprise edition 64 bit

It's not a ad dc machine.

cheers

|||

Rookie,

Thanks, for the info. So, all of SQL05 (std, ent or dev?) is installed on C:? Default location? ("C:\Program Files\Microsoft SQL Server" and "C:\Program Files\Microsoft Visual Studio 8", etc)

Like you, I would really like to know the root cause of this problem. I poked around for a while and did not find anything that pointed me in the right direction to fix it - other than uninstalling/reinstalling VS and SQL with common components on C: rather than on E:. I did a complete format/install twice that produced the same failure to save a package - fresh installs of MS products.

Did you install a full version of Visual Studio 2005 pro on the same machine as SQL Server 2005?

Installing VS05 is the step that broke saving a package for me (worked before, broke after VS05 install). Maybe the 64bit C++ tools have something to do with this. I installed these in VS.

You might try opening a command prompt on the "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727" directory and running "ngen executeQueuedItems" then "ngen update" to check that the pre-JIT queue is clear and that there are no broken dependencies.

I called M$ support on this one and they seemed to think that it was just a bad setup... I did not want to waste a support call on it... Yet...

MikeC

Full text of the errors:

Error from create SSIS package (save to server):
===================================

No description found (Microsoft Visual Studio)
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword) at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package) at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

===================================

Error from copy db wizard:
===================================

No description found (Copy Database Wizard)
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword) at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()

===================================

|||

Yes everything is installed on the same default location c:

yes visual studio 2005 is installed on the same machine as sql server 2005

ngen executeQueuedItems works fine but ngen update gave the following recurring error:

Failed to load dependency Microsoft.ReportingServices.Modeling of assembly Micro
soft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, Publi
cKeyToken=89845dcd8080cc91 because of the following error : The system cannot fi
nd the file specified. (Exception from HRESULT: 0x80070002)

should i try re installing both sql server and vs.

thanks for all the help

|||

RookieDBA,

Ok, so SQL05 installed with VS05 is one commonality to the problem. I'm not sure why installing to default locations on C: would cause this problem for you, as I get this "save package error" only when I tried to install to a non-default E: volume. It would be nice for someone at Microsoft to look into this error and AT LEAST give us a better understanding of what the underlying problem is.

Ok, "ngen executeQueuedItems" completed - meaning all queued items from the install were completed, that's good.

The "cannot file file" for Microsoft.ReportingServices.Modeling dependancy of Microsoft.ReportingServices.QueryDesigners means that ngen cannot find a reference to the Microsoft.ReportingServices.Modeling.dll file for the Microsoft.ReportingServices.QueryDesigners.dll file that was installed into the Global Assembly Cache (GAC).

I'm not sure why the RS installation puts only some of the RS "private" assemblies into the GAC creating these reference errors. Again Microsoft should answer this question.

If you would like to remove this ngen error (and likely speed RS loading/designing), add the Microsoft.ReportingServices.modeling.dll file to the GAC (i.e. drag the file to the C:\Windows\Assembly folder - use two explorer windows).

But, you will find that a whole bunch of RS dlls will need to be added to the GAC (generally it won't hurt to do this) to resolve all dll dependency errors as reported by ngen.

Reinstalling, both SQL05 and VS05 to C: will likely fix the package saving problem.

MikeC

Copy Database from 2000 to 2005 using Copy database wizard

I am trying to copy a database from 2000 to 2005 using the copy database wizard. I am a sys admin on both the source and destination server and am using the windows auth method and sql management object method. I am not copying any database objects.

Following is the report: Any suggestions?

Performing operation...

- Add log for package (Success)

- Add task for transferring database objects (Success)

- Create package (Error)

Messages

No description found (Copy Database Wizard)

- Start SQL Server Agent Job (Stopped)

- Execute SQL Server Agent Job (Stopped)

You might try posting on the "Tools General" or "SSIS" forums.

Copy Database from 2000 to 2005 using Copy database wizard

I am trying to copy a database from 2000 to 2005 using the copy database wizard. I am a sys admin on both the source and destination server and am using the windows auth method and sql management object method. I am not copying any database objects.

Following is the report: Any suggestions?

Performing operation...

- Add log for package (Success)

- Add task for transferring database objects (Success)

- Create package (Error)

Messages

No description found (Copy Database Wizard)

- Start SQL Server Agent Job (Stopped)

- Execute SQL Server Agent Job (Stopped)

You might try posting on the "Tools General" or "SSIS" forums.

COPY & schema change

Hi,

I need to copy a DB into another changing the schema at the destination DB, I try to do it by exporting the DB and change the schema at the table selection screen but with alot of tables is to complex do it manualy, any idea on how can i do it?.

Thanks.

You have two choices.

a. The way you are finding is "to (sic) complex"

b. Create scripts for the database, and alter the scripts, then run the scripts.

|||As suggested you can take help of generate scripts from SSMS and then use that file by changing relevant schemas then apply it on destination database, if you need data then you can take help of SSIS in this case.|||

Thanks for youy help, I created the new DB changing the schema as you said with SSMS, but I need to take the data into the new DB also but I don't know anything about SSIS that you mention, do you have any documentation or something to help me?.

Thanks again.

|||

To move data between databases/servers, you can use the DTSWizard. (It is a version of the previous Import/Export Wizard.) And does not have the learning curve of SSIS.

Find it at {installedLocation}\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

It should execute from a command prompt.

|||

That's the way I was copying the DB in the beginning but I have to change the schema manually for each table and that's what is very annoying to change a thousand lines, thanks anyway.

Converting nchar to int (or numeric/decimal)

Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

I tried all the ways i can and still i get the same error..:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".

Can we actually do it...? any help would be appreciated.

thanks

ravi

you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?|||

Hi,

No ..No....Its Numeric in the source though the data type is nchar(3).

The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send records if the column in the extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

thanks

ravi

|||Try a derived column to cast the records. (Why not make them integers?)

(DT_I4)[Column]|||

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

|||

Adamus Turner wrote:

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

Yeah, but Ravi's source is AS400, not SQL Server. Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.|||

Hey phil,

I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

DECLARE @.X nchar(3)

DECLARE @.Y int

SET @.X ='GGG'

SELECT @.X

SET @.Y =Cast(@.X asint)

SELECT @.Y

thanks for all you views/immediate response's...

let me know if i am doing a mistake here..

thanks,

ravi

|||Then Adamus' example should apply.|||

Hi ,

I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR' which was causing all the problem... guess...

thanks for the quick responses...

thanks..!!

Ravi

|||

Then check isnumeric before the cast and set non-numerics to 0:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'AAA'

INSERTINTO #temp SELECT'311'

SELECTCASEISNUMERIC(test)WHEN 1 THENCAST(test asint)ELSE 0 END

FROM #temp

DROPTABLE #temp

Adamus

|||Just a tweak. You may want to set them to NULL, versus a 0. NULLs would be excluded from any math/aggregation totals you may be creating, such as an average. Using a 0 would allow that value to be used. So it's up to you.

In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire. Or, you can assign new values to the non-numerics. Up to you.