Copy info from one table two another with conditions

I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.

basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.

INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1

this is what my actual code is

INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID

this is the actual error that I get.

Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.

--
thanks for the helpWell I think that I got it...

INSERT INTO USER_INFO (id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id NOT IN (SELECT id FROM USER_INFO)

Let me know if there is a better way

Thanks

Copy Flat File between two server; Help me please.

Hello All,

I want to copy flat file(.txt) from server to another server.

server A(Source Folder) ====> server B(Achive Folder)

Can I do it?

Please, help me.

Thank you very much.

Chonnathan

Do you want to perform this copy once or on a regular schedule? There are probably better utilities out there to perform this server-to-server copy, other than SSIS regardless.|||

Hi again,

Now, I want to use SSIS to process data from text(banking transactions, credit card) in daily process.

but source file was in another server.

In this case how can i do to get source file to precess in ssis on other server.

thank you very much.

My english skill is not well, sorry.

|||You'll have to use FTP or some other means of getting that file off of the source server. There is an FTP task in the control flow of SSIS. Or, if you can map a network drive to the source server, you can simply use a flat file source connection in the data flow against that mapped drive/file.|||

Great job,

Thank you for your helpful,

Regards,

Chonnathan

|||

Chonnathan Parnprom wrote:

Great job,

Thank you for your helpful,

Regards,

Chonnathan

I've I've answered your question, please mark it as answered using the "Mark as Answer" button.

Thanks,

Phil

Copy file on network

I have a job which call a SP and that SP generates a excel file dump using
bcp. Now form my website i want to access that excel file. Both my webserver
and db server is on different meachine.
How can i achive this?
can BCP generate output on network. Or can i copy generated file to
webserver using some command through SP ?Vikram
If I understood properly , you can create a linked server to EXCEL file.
HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"Vikram" <aa@.aa> wrote in message
news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
>I have a job which call a SP and that SP generates a excel file dump using
> bcp. Now form my website i want to access that excel file. Both my
> webserver
> and db server is on different meachine.
> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
>
>|||But in linked server i have to have excel file. But i do not want this, i
want to have excel file generated by bcp...
any other way ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> Vikram
> If I understood properly , you can create a linked server to EXCEL file.
> HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
>
> "Vikram" <aa@.aa> wrote in message
> news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
using
>|||DTS?
"Vikram" <aa@.aa> wrote in message
news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> But in linked server i have to have excel file. But i do not want this, i
> want to have excel file generated by bcp...
> any other way ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> using
>|||NO I am suing BCP , calling it from sp using xp_cmdshell
I dont want to use DTS as sp whic return data use temp table and also i have
many sp whose data i have to export to excel
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2370krlPPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> DTS?
>
> "Vikram" <aa@.aa> wrote in message
> news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
i
file.
>|||> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
UNC path depending on your permissions.
EXEC master..xp_cmdshell "bcp DB.dbo.Table out
\\SERVER1\Share\test.xls -c -T"
ftp via xp_cmdshell could work.

copy file in stored procedure

All I want to do is copy a text file and add a 1 after the name in a stored procedure that already cretes this file.

CREATE PROCEDURE [dbo].[sp_export_req] AS
DECLARE @.trenutniRed varchar(30),
@.tableRow1 varchar(3000),
@.sql varchar(4000),
@.sql1 varchar(4000),
@.firstColumnName varchar(30),
@.HTMLfile varchar(1000),
@.columnNumber varchar(30),
@.fs int,
@.ole int,
@.file int
declare @.body as nvarchar(225)
CREATE TABLE #temptableHTML (code nvarchar(225))
select @.HTMLfile = 'c:\test_new.txt'

This is the start of the procedure that creates the test_new.txt file. I want to copy the test_new.txt file and name it test_new1.txt. Any help is appreciated. thank you

you could manipulate file name with string functions and build command for xp_cmdshell to copy file XXX.txt in XXX1.txt... or not?sqlsql

copy file automatically

I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,

Thanksxcopy utility would help..|||Can you give me more detail about xcopy utility? Where is it? how can i learn it? Thanks in advance.|||open a dos window.

at the prompt type: help xcopy|||i try this in cmd command:
C:\>xcopy c:YTEST \\Server25702\C:\YTEST1

i want to copy the YTEST file to another machine (called Server25702). but it gave me a error message:
Invalid drive specification
0 File(s) copied

What wrong with it, also i change Server25702 to IP address, it also gave me a same message. Can you help me again? Thanks.|||You have to map a drive to \\server25702\c:\ytest1 before you can use xcopy.

So if you use the map command to map drive letter D - your syntax would be:

xcopy c:\ytest d:\ytest

i try this in cmd command:
C:\>xcopy c:YTEST \\Server25702\C:\YTEST1

i want to copy the YTEST file to another machine (called Server25702). but it gave me a error message:
Invalid drive specification
0 File(s) copied

What wrong with it, also i change Server25702 to IP address, it also gave me a same message. Can you help me again? Thanks.|||i try to use map drive, i type \\Server25702, it give me message, the network drive can't be found. Why? Thanks.|||you will need to share the drive on server25702|||Thanks, i got it.
But after i copied, i found all the folder in the source file didn't copy to destination, Can this copy anything in the folder i selected to the destination?

I am going to save it as .bat file, How can i use window to schedule it to run from Monday to Friday. Thanks.

copy file

hi guys,

anybody know the syntax in SQL to copy file to other server.

we want to copy our db backup files automaticaly to other server.

thanks

-wei

Hi

It wud be helpful if u can be more specific, like if u wanna back up ur entire DB or just copy data from one table to another table in another DB

|||

hi,

I want to copy file to other server using SQL.

I found out that we can use xp_cmdshell but I can make it work.

my syntax is " exec xp_cmdshell 'copy c:\folder\test.xxx

\\server1\folder1, no_ouput ;

on the result pane it said succesful, but when I look to the destination folder there is no file that I copied.

|||

hi dedy,

hi try mapping "\\server1\folder1" to qa drive letter

to map

1. open windows explorer.

2. click tools.

3. clcik on map network drive

4. enter necessary permission

you can assign a specific user (using different username option) to your mappings

to make sure you wont run on a permission problem with NTFS

run

exec xp_cmdshell 'copy c:\folder\test.xxx x:' <--where x: is the mapped network drive

regards,

joey

|||

hi joey,

I have tries your suggestion.

but there is an error that said "The system cannot find the drive specified"

I'm using SQL 2005.

I already map the folder using administrator account.

please advice.

Copy external files to different locations

Hi,

I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.

I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.

Many thanks in advanceMark,

this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.

As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).

You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.

Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.

Regards,

hmscott|||Thanks for getting back to me.

What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.

I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations

To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.

If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.

Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.

Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).

Regards,

hmscott

Copy datafile on server to server?

Hi all,
Is there a command to copy a file on a sql server directory to another
directory on the same server?
I want to take the database off-line, then copy the datafile from one
directory to another. I am remote to the server and want a speedy copy made
by the server not my client.
After, I would bring the database back on-line and have the copy available
to download locally.
Thanks,
John.You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
and then sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Hi,
To Add on to Tibers post, you can create a batch file in SQL Server and
schedule as as Job.
1. Perform the back of database (Use Restore Database)
2. Map the drive of remote server ( Net use command)
3. Copy the file to remote server and remove the mapping (Use net use drive
letter /d)
4. Load this backup file in remote server
This dont require your server to go offline.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Is this command run from Query Analyzer? If not where is it to be ran from?
John.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> and then sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||This is great idea; one that I keep forgetting about because my backup file
is over a gig and is too big for me to copy. What do you mean by "(Use
Restore Database)? Are you just saying that I would want to use the Restore
Database functions once I have the backup local?
John.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> Hi,
> To Add on to Tibers post, you can create a batch file in SQL Server and
> schedule as as Job.
> 1. Perform the back of database (Use Restore Database)
> 2. Map the drive of remote server ( Net use command)
> 3. Copy the file to remote server and remove the mapping (Use net use
drive
> letter /d)
> 4. Load this backup file in remote server
> This dont require your server to go offline.
> Thanks
> Hari
> MCDBA
>
>
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message news:%232JAATwwDHA.560@.TK2MSFTNGP11.phx.gbl...
> Is this command run from Query Analyzer? If not where is it to be ran from?
> John.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> > and then sp_attach_db.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||John
Another appoach
After backuping your database you can compres it by using WINRAR or WINZIP
and then copy it to another server.
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||Hi,
Yes John, you are correct.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>

copy databse to another server

Hello,,

I backed up my database and it created a BAK file. Now I move this file to other server and trying to restore from it. It is giving me error: Device activation error. Do I have to create database before restoring. I do not have this database available in other server and I need to copy database with all constrains and tables.

Thanks,

no you do not have to create teh database first...
the .bak file sounds corrupted...
back up the db again only this time check the box to validate/verify the file.
HTH,
SQLPoet|||Create the backup and take the .bak file and paste it in the Backup subfolder in Microsoft SQL Server folder under programs before going into Enterprise manager and use the backup and restore wizard and choose the restore from device option. Hope this helps.sqlsql

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
Chad
Hello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad
|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.
|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:
> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> > Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> > original is composed of 2 or 3 data files and 1 translog file, total of about
> > 12GB. I connected each server -- separate boxes -- to my internal network,
> > the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> > the CDW is still processing and I can see that the largest of the ldf files
> > has copied over to the 2005 server, but basically I'm wondering, what's the
> > metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> > seen it work faster? Just seems a bit slow to me . . .
> >
> > Thanks,
> >
> > Chad
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of abou
t
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours
,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of ab
out
> 12GB. I connected each server -- separate boxes -- to my internal network
,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hou
rs,
> the CDW is still processing and I can see that the largest of the ldf file
s
> has copied over to the 2005 server, but basically I'm wondering, what's th
e
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CD
W
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so
I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

copy database via tapefile no SQL 2005

Hello,

if you got a tape file how can you restore the contained database onto a
newly installed server? I choose Restore Database From Device then I choose
DB and select same DB in combobox "To Database" but error occured:

TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '...'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\KidDatabase.mdf" failed with
the operating system error 3(error not found). (Microsoft.SqlServer.Smo)

what can I do to handle this problem?

regards
MarkMark (Scollop027@.gmx.net) writes:

Quote:

Originally Posted by

if you got a tape file how can you restore the contained database onto a
newly installed server? I choose Restore Database From Device then I
choose DB and select same DB in combobox "To Database" but error
occured:
>
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '...'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\KidDatabase.mdf" failed with
the operating system error 3(error not found). (Microsoft.SqlServer.Smo)
>
what can I do to handle this problem?

First do

RESTORE FILELISTONLY FROM TAPE = 'tapedevice'

this will give you the logical names of the files of the database. Then
do:

RESTORE DATABASE db FROM TAPE = 'tapedevice' WITH
MOVE 'datafile' TO
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\KidDatabase.mdf',
MOVE 'logfile' TO
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\KidDatabase.ldf',
REPLACE

Here I've used the path from your error message. Howver, the error message
indicates that this is a non-existing path, so you may have to examine
where there is place on the server to put the databases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
good tip, thank you very much !
I havent seen it :-/
regards Mark

"Erland Sommarskog" <esquel@.sommarskog.seschrieb im Newsbeitrag
news:Xns98203142E059Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mark (Scollop027@.gmx.net) writes:

Quote:

Originally Posted by

if you got a tape file how can you restore the contained database onto a
newly installed server? I choose Restore Database From Device then I
choose DB and select same DB in combobox "To Database" but error
occured:

TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '...'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file

"C:\Program

Quote:

Originally Posted by

Quote:

Originally Posted by

Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\KidDatabase.mdf" failed

with

Quote:

Originally Posted by

Quote:

Originally Posted by

the operating system error 3(error not found). (Microsoft.SqlServer.Smo)

what can I do to handle this problem?

>
First do
>
RESTORE FILELISTONLY FROM TAPE = 'tapedevice'
>
this will give you the logical names of the files of the database. Then
do:
>
RESTORE DATABASE db FROM TAPE = 'tapedevice' WITH
MOVE 'datafile' TO
C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\KidDatabase.mdf',

Quote:

Originally Posted by

MOVE 'logfile' TO
C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\KidDatabase.ldf',

Quote:

Originally Posted by

REPLACE
>
Here I've used the path from your error message. Howver, the error message
indicates that this is a non-existing path, so you may have to examine
where there is place on the server to put the databases.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please help
Hi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. Wh
en I drag and drop the file it tells me "insufficient amount of space" even
though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Data to excel file using dts package in sql server 2000

Friends

Any one of you share your knowledge how to transfer data from a database to a excel using dts packages in sqlserver 2000.

I want clear steps how to create a dts package

Appreciate your help

Thanks

satish

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

You might try this group instead, as this forum is for SQL Server Integration Services

copy content of txt file

Hi Guys,

What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?

Example of the text file's content:

Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90

In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.

Regards,

Lars

Hi,

You could use DTS for this. Define a new DTS package, with your database in source, and a text file for output.

You can use an SQL order to filter your Data (..Where Date = "20060101" AND ....).

The result will be formatted as a CVS file by default. If you need to personalize your output format, you can use ActiveX scripts in transformation tasks to do this

ex :

Function Main()
DTSDestination("Date") = "Date, " & DTSSource("Date")
Main = DTSTransformStat_OK
End Function

|||

hi,

if you know the location of the row you want to copy you can use

the -f anf -l switch of BCP which stands for

-f = the first row

-l = the lastrow

regards,

joey