Newbie Question on DTS Object Trasnfer

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)

I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.

While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.

While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

I DID copy base indexes.

Thanks in advance.

GerryDataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)
>
I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.
>
While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.
>
While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

You should certainly copy the keys. I don't use full-text, so I don't know
if there is any good reason why they are not copied by default. Then
again, with a little luck your database does have full-text indexes.

--
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|||Thanks...ran a few tests looking at the dll before and after trying
different options and kept the keys. Worked out fine...makes you wonder
why Microsoft would use an example of a dts move in its book for
newbies that would only confuse the issue.

Erland Sommarskog wrote:

Quote:

Originally Posted by

DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

Very new to SQL Server. Was asked to copy some tables from a Prod to a
Test database in the same instance. (This system is not yet in
production.)

I used the Microsoft course book guidance for course 2072 A, the lab
exercise in Module 9, page 33 to give me some guidance. In the exercise
they were copying tables as well as views.

While in the wizard the guidance said to uncheck 'copy primary and
foreign keys' and uncheck 'copy full text indexes'.

While I realize that might be because of the nature of copying views, I
used that guidance to copy the tables. Is this correct or should I do
it over and copy both the keys and full text indexes?

>
You should certainly copy the keys. I don't use full-text, so I don't know
if there is any good reason why they are not copied by default. Then
again, with a little luck your database does have full-text indexes.
>
--
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

Newbie question about sprocs

When I run my stored proceedure in query analyzer and copy the text to the pane in the report designer, all is well, but if I run the same stored proceedure as a stored procedure in the report designer it doesn't return all the fields that it does as text. What's the difference? What do I do to fix this?Two things come to mind. Ownership and location of the stored procedure.
OWNERSHIP:
Objects like tables, views, and stored procedures can be owned by different
logins.
Lets say you have two users in your database
user1
user2
They can both create a table called FOO
Each table can have different columns.
Each user can also create a stored procedure named procFOO
These stored procedures can return different columns.
LOCATION OF THE STORED PROCEDURE:
It is possible to create stored procedures in multiple databases. For
example, procFOO within northwind could select from the Products table and
procFOO within pubs could select from the authors table. Are you calling
the stored procedure from the database that you created it in (and are
running it from Query Analyzer)?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> When I run my stored proceedure in query analyzer and copy the text to the
pane in the report designer, all is well, but if I run the same stored
proceedure as a stored procedure in the report designer it doesn't return
all the fields that it does as text. What's the difference? What do I do to
fix this?|||So I hit the nail on the head; was it an ownership or a location issue?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8EC3378E-037A-4C6A-93BE-7367603FDBE3@.microsoft.com...
> Thanks you were a great help. All is well!
> "Keith Kratochvil" wrote:
> > Two things come to mind. Ownership and location of the stored
procedure.
> >
> > OWNERSHIP:
> > Objects like tables, views, and stored procedures can be owned by
different
> > logins.
> > Lets say you have two users in your database
> > user1
> > user2
> > They can both create a table called FOO
> > Each table can have different columns.
> > Each user can also create a stored procedure named procFOO
> > These stored procedures can return different columns.
> >
> > LOCATION OF THE STORED PROCEDURE:
> > It is possible to create stored procedures in multiple databases. For
> > example, procFOO within northwind could select from the Products table
and
> > procFOO within pubs could select from the authors table. Are you
calling
> > the stored procedure from the database that you created it in (and are
> > running it from Query Analyzer)?
> >
> > --
> > Keith
> >
> >
> > "Arly" <Arly@.discussions.microsoft.com> wrote in message
> > news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> > > When I run my stored proceedure in query analyzer and copy the text to
the
> > pane in the report designer, all is well, but if I run the same stored
> > proceedure as a stored procedure in the report designer it doesn't
return
> > all the fields that it does as text. What's the difference? What do I do
to
> > fix this?
> >
> >

Newbie question about Object transfer

Hi,

I'm new to Integration Services and I have a problem when I try to make a simple transfer of tables and views between 2 DB.

The idea is to copy tables and views from DB1 to DB2.

If the object exists in the target DB, it must be dropped, then created and the data must be copied.

The problem occurs when a new object is created in the source DB and thus does not exist in the target DB. This throws an error because the system cannot drop an object wich does not exist.

What is the workaround ?

The idea is to execute this package every hour to have a cached DB. In this case, replication is not a solution.

Thanks in advance,

Patrice.

This issue is a bug in SQL Server 2005 RTM. Hopefully it will be fixed in one of the SP releases in the near future.

NEWBIE Question - How do I take an existing Database and Create an exact Copy of it under

Ladies / Gentlemen
How do I take an existing Database and Create an Exact Copy of it
under a new Database Name and having the name of the Database .MDF and .LOG
files changed also?
Thanks for your help.
Mark Moss
How about backup/restore (using the MOVE option for the RESTORE command)?
Or detach, copy files, then attach?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Moss" <markmoss@.adelphia.net> wrote in message news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it under a new Database Name
> and having the name of the Database .MDF and .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>
|||Hi Mark
Since you did not mention your version number I will assume SQL Server 2005.
Please refer to the BOL (Books Online) documentation for syntax details:
Detach the original database using sp_detach_db
Copy the .mdf and .ldf files and give them new names.
Use CREATE DATABASE ... FOR ATTACH to create the new database using the
copied files.
Also see the following in BOL:
How to: Move a Database Using Detach and Attach (Transact-SQL)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it
> under a new Database Name and having the name of the Database .MDF and
> .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>
|||Another new thing for me! "... FOR ATTACH" option of CREATE DATABASE.
TheSQLGuru
President
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ul1ggW%23mHHA.5032@.TK2MSFTNGP02.phx.gbl...
> Hi Mark
> Since you did not mention your version number I will assume SQL Server
> 2005.
> Please refer to the BOL (Books Online) documentation for syntax details:
>
> Detach the original database using sp_detach_db
> Copy the .mdf and .ldf files and give them new names.
> Use CREATE DATABASE ... FOR ATTACH to create the new database using the
> copied files.
> Also see the following in BOL:
> How to: Move a Database Using Detach and Attach (Transact-SQL)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Mark Moss" <markmoss@.adelphia.net> wrote in message
> news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
>

NEWBIE Question - How do I take an existing Database and Create an exact Copy of it under

Ladies / Gentlemen
How do I take an existing Database and Create an Exact Copy of it
under a new Database Name and having the name of the Database .MDF and .LOG
files changed also?
Thanks for your help.
Mark MossHow about backup/restore (using the MOVE option for the RESTORE command)?
Or detach, copy files, then attach?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Moss" <markmoss@.adelphia.net> wrote in message news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it under a new Database Name
> and having the name of the Database .MDF and .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>|||Hi Mark
Since you did not mention your version number I will assume SQL Server 2005.
Please refer to the BOL (Books Online) documentation for syntax details:
Detach the original database using sp_detach_db
Copy the .mdf and .ldf files and give them new names.
Use CREATE DATABASE ... FOR ATTACH to create the new database using the
copied files.
Also see the following in BOL:
How to: Move a Database Using Detach and Attach (Transact-SQL)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it
> under a new Database Name and having the name of the Database .MDF and
> .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>|||Another new thing for me! "... FOR ATTACH" option of CREATE DATABASE.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ul1ggW%23mHHA.5032@.TK2MSFTNGP02.phx.gbl...
> Hi Mark
> Since you did not mention your version number I will assume SQL Server
> 2005.
> Please refer to the BOL (Books Online) documentation for syntax details:
>
> Detach the original database using sp_detach_db
> Copy the .mdf and .ldf files and give them new names.
> Use CREATE DATABASE ... FOR ATTACH to create the new database using the
> copied files.
> Also see the following in BOL:
> How to: Move a Database Using Detach and Attach (Transact-SQL)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Mark Moss" <markmoss@.adelphia.net> wrote in message
> news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
>> Ladies / Gentlemen
>>
>> How do I take an existing Database and Create an Exact Copy of it
>> under a new Database Name and having the name of the Database .MDF and
>> .LOG files changed also?
>>
>> Thanks for your help.
>>
>> Mark Moss
>>
>

Newbie Help - Express exporting

I have been happily developing a site in Visual Express utilising SQL Express
and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
database. After some mucking around I have worked out that my App Data folder
has nothing to do with the ISP Sql Server DB.
The connection Strings neded for eth ISP do not go to the Express App_data
and it appears I have to recreate my DB in the ISP's SQL Server. Is there any
way of converting / exporting my SQL Express info to the SQL Server DB
including the content not just the structure?
Sorry if it is a really daft question but ... I will get smarter eventually!
Hi
It is not clear if you have an user instance or not! If you are using a user
instance then you should change the connection string so that you don't have
the AttachDbFilename clause. You can either restore a backup of the database
or copy the mdf and ldf files and re-attach hem as described in
http://support.microsoft.com/kb/224071/
John
"Dajuroka" wrote:

> I have been happily developing a site in Visual Express utilising SQL Express
> and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
> database. After some mucking around I have worked out that my App Data folder
> has nothing to do with the ISP Sql Server DB.
> The connection Strings neded for eth ISP do not go to the Express App_data
> and it appears I have to recreate my DB in the ISP's SQL Server. Is there any
> way of converting / exporting my SQL Express info to the SQL Server DB
> including the content not just the structure?
> Sorry if it is a really daft question but ... I will get smarter eventually!

Newbie Help - Express exporting

I have been happily developing a site in Visual Express utilising SQL Expres
s
and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
database. After some mucking around I have worked out that my App Data folde
r
has nothing to do with the ISP Sql Server DB.
The connection Strings neded for eth ISP do not go to the Express App_data
and it appears I have to recreate my DB in the ISP's SQL Server. Is there an
y
way of converting / exporting my SQL Express info to the SQL Server DB
including the content not just the structure'
Sorry if it is a really daft question but ... I will get smarter eventually
!Hi
It is not clear if you have an user instance or not! If you are using a user
instance then you should change the connection string so that you don't have
the AttachDbFilename clause. You can either restore a backup of the database
or copy the mdf and ldf files and re-attach hem as described in
http://support.microsoft.com/kb/224071/
John
"Dajuroka" wrote:
[vbcol=seagreen]
> I have been happily developing a site in Visual Express utilising SQL Expr
ess
> and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
> database. After some mucking around I have worked out that my App Data fol
der
> has nothing to do with the ISP Sql Server DB.
> The connection Strings neded for eth ISP do not go to the Express App_data
> and it appears I have to recreate my DB in the ISP's SQL Server. Is there
any
> way of converting / exporting my SQL Express info to the SQL Server DB
> including the content not just the structure'
> Sorry if it is a really daft question but ... I will get smarter eventually![/vbc
ol]

Newbie Help - Express exporting

I have been happily developing a site in Visual Express utilising SQL Express
and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
database. After some mucking around I have worked out that my App Data folder
has nothing to do with the ISP Sql Server DB.
The connection Strings neded for eth ISP do not go to the Express App_data
and it appears I have to recreate my DB in the ISP's SQL Server. Is there any
way of converting / exporting my SQL Express info to the SQL Server DB
including the content not just the structure'
Sorry if it is a really daft question but ... I will get smarter eventually!Hi
It is not clear if you have an user instance or not! If you are using a user
instance then you should change the connection string so that you don't have
the AttachDbFilename clause. You can either restore a backup of the database
or copy the mdf and ldf files and re-attach hem as described in
http://support.microsoft.com/kb/224071/
John
"Dajuroka" wrote:
> I have been happily developing a site in Visual Express utilising SQL Express
> and then tried to copy to my ISP (GoDaddy). They provide a SQL Server
> database. After some mucking around I have worked out that my App Data folder
> has nothing to do with the ISP Sql Server DB.
> The connection Strings neded for eth ISP do not go to the Express App_data
> and it appears I have to recreate my DB in the ISP's SQL Server. Is there any
> way of converting / exporting my SQL Express info to the SQL Server DB
> including the content not just the structure'
> Sorry if it is a really daft question but ... I will get smarter eventually!

Newbie Alert !

Dear Folks,

First time here.

Have just installed a 120-day evaluation copy of MSSQL 2000.

In the 'blur' and confusion of installing this on my WinXP Laptop, I
forgot to record the password.

Finally, I got into the 'logins' tab on the Enterprise Manager, and
modified the 'sa' password to 'ivlem1', and I think it was recorded
properly.

I tried to test my installation with the following at the Command
Prompt:

osql /Usa /P ivlem1

It failed saying I wasn't 'Trusted'

I would appreciate any help offered !

TIA,

-Mel SmithDid you select "mixed" mode for authentication. I would test this
differently...fire up QA and use your SA account/passwd to account.
There are too many things that could go wrong with establishing a
connection using osql.

HTH

MJKulangara
http://sqladventures.blogspot.com|||Mel Smith (medsyntel@.aol.com) writes:
> Have just installed a 120-day evaluation copy of MSSQL 2000.
> In the 'blur' and confusion of installing this on my WinXP Laptop, I
> forgot to record the password.
> Finally, I got into the 'logins' tab on the Enterprise Manager, and
> modified the 'sa' password to 'ivlem1', and I think it was recorded
> properly.
> I tried to test my installation with the following at the Command
> Prompt:
> osql /Usa /P ivlem1
> It failed saying I wasn't 'Trusted'

SQL Server have to means of authentication: Windows authentication (a.k.a
Integrated Security or Trusted Connection) and SQL Server authentication.
Windows authentication is always there, SQL authentication is optional,
and usually referred to as "mixed mode".

Windows authentcation means that you log into SQL Server with your
Windows credentials. This is very simple, you just say "osql /E" and
you are on the air. No need for password, as you are already logged
into Windows.

By default, only Windows authentication is enabled. This is because
SQL Server authentication is less secure. However, many enable
SQL authentiation for various reasons. For instance, if you are a
developer you may work with Windows authentication as long as you
develop and have sysadmin rights. But when you need to test, you
want a low-priv user, and testing different users is much easier
with SQL authentication.

You can change authentication mode from Enterprise Manager. Right-
click the server, select Properties, and then use the Security tab.

--
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|||Erland and MJK,

Thank you both for your advice !

Erland:

I tried "osql /E" and it worked ! (i.e., I got correct correct results
for the two queries: select @.@.servername and select @.@.version)

(now I'm going to go blind reading all there is on Books Online to get
myself up to speed )

btw, I'm fairly expert in xBase databases (my job is tech support for a
structural steel company that uses my software, but is now moving to MSSQL)
and I have to show that I'm up to the task to help them make that move. In
fact, they have purchased AccPac for Windows, and have installed MSSQL 2000.
But I'm at a remote location from the headquarters of this company and must
get used to MSSQL all by myself in the 'sunny south'.

Thanks again.

-Mel Smith
Mesa, Arizona, USA

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?Hi Paul
"Paul" wrote:

> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
[url]http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp[/u
rl]
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegroups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/col...gaccesstosql...

Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Databases
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?Hi Paul
"Paul" wrote:
> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegroups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosql...
Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Databases
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.

Newbie - Copy table from SQL Server 2005 to Access

I need to copy a farily large table (+200,000 records) to Access. I
need to do this from a VB.NET 2005 program. I can get this to work
using .NET code. But it's too slow.
I've heard of DTS, but know nothing about it. Is this what I should
be using? Does any one have some good code that I can use?
Hi Paul
"Paul" wrote:

> I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
In SQL 2005 SQL Server Integration Services supersedes DTS, you can find out
more about this by looking in Books online. Sites such as
http://www.sqlis.com/ are also useful. You could use the Export Wizard to
create the initial SSIS package for you invoke this by right clicking the
database node in SSMS choose Tasks, then Export Data and step through the
wizard specifying your Access database as the destination.
Another possibile solution may be to use a linked server.
John
|||Paul,
Why not link the table directly to the Access database and eliminate the
copy step?
http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosqlserver.asp
-- Bill
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174497377.108640.157780@.l77g2000hsb.googlegr oups.com...
>I need to copy a farily large table (+200,000 records) to Access. I
> need to do this from a VB.NET 2005 program. I can get this to work
> using .NET code. But it's too slow.
> I've heard of DTS, but know nothing about it. Is this what I should
> be using? Does any one have some good code that I can use?
>
|||> Why not link the table directly to the Access database and eliminate the
> copy step?http://www.sqlservercentral.com/columnists/awarren/linkingaccesstosql...
Thanks for the replies. AlterEgo, the application that I'm creating
is a VB.NET 2005 executable that will run at night via a scheduled
task. So running a query from Access is not possible. I thought
about running a query to do that but do not know how this is done from
within VB.NET. Plus, I didn't really want to do that.
|||I successfully created an Access object within my VB.NET app and was
able to run the necessary queries to copy the data from the SQL Server
table to the Access table. It is fast and efficient. The code was
simple:
Dim accessMdb As New Access.Application
accessMdb.OpenCurrentDatabase("P:\WebSite\Database s
\WebDataDatabase.mdb", True)
accessMdb.DoCmd.OpenQuery("Delete_Web_Data_Table")
accessMdb.DoCmd.OpenQuery("Append_SQL1_WebData_to_ Web_Data")
accessMdb.CloseCurrentDatabase()
Only other thing I needed was to add a reference to the Microsoft
Access 10.0 Object Library.

newbee question on cloning a database

Hi,
I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
efore I do that I would like to have a copy of CTX_dev, in case something
goes wrong .
My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
export CTX_stg to CTX_dev. However I don't have enough disk space in C:
drive, so is there away I can create a database in D: and then Export to it?
Or are there better ways to do this?
TIA
You can use BACKUP DATABASE to back it up to D:, do it through enterprise
manager or the syntax in Query Analyser...
BACKUP DATABASE CTX_dev TO DISK = 'd:\CTX_Dev.bak' WITH INIT
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||why not use backup and restore?
It does sound to me like you have database change management problems -
perhaps we could help...
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"Raymond Du" wrote:

> Hi,
> I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
> in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
> efore I do that I would like to have a copy of CTX_dev, in case something
> goes wrong .
> My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
> export CTX_stg to CTX_dev. However I don't have enough disk space in C:
> drive, so is there away I can create a database in D: and then Export to it?
> Or are there better ways to do this?
> TIA
>
>
|||Other options are log shipping or Replication

newbee question on cloning a database

Hi,
I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
efore I do that I would like to have a copy of CTX_dev, in case something
goes wrong .
My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
export CTX_stg to CTX_dev. However I don't have enough disk space in C:
drive, so is there away I can create a database in D: and then Export to it?
Or are there better ways to do this?
TIAYou can use BACKUP DATABASE to back it up to D:, do it through enterprise
manager or the syntax in Query Analyser...
BACKUP DATABASE CTX_dev TO DISK = 'd:\CTX_Dev.bak' WITH INIT
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||why not use backup and restore?
It does sound to me like you have database change management problems -
perhaps we could help...
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"Raymond Du" wrote:

> Hi,
> I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
> in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
> efore I do that I would like to have a copy of CTX_dev, in case something
> goes wrong .
> My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
> export CTX_stg to CTX_dev. However I don't have enough disk space in C:
> drive, so is there away I can create a database in D: and then Export to i
t?
> Or are there better ways to do this?
> TIA
>
>|||Other options are log shipping or Replication

newbee question on cloning a database

Hi,
I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
efore I do that I would like to have a copy of CTX_dev, in case something
goes wrong .
My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
export CTX_stg to CTX_dev. However I don't have enough disk space in C:
drive, so is there away I can create a database in D: and then Export to it?
Or are there better ways to do this?
TIAYou can use BACKUP DATABASE to back it up to D:, do it through enterprise
manager or the syntax in Query Analyser...
BACKUP DATABASE CTX_dev TO DISK = 'd:\CTX_Dev.bak' WITH INIT
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||why not use backup and restore?
It does sound to me like you have database change management problems -
perhaps we could help...
regards,
Mark Baekdal
www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"Raymond Du" wrote:
> Hi,
> I have 2 databases: CTX_stg and CTX_dev in a SQL sever. Each is about 3 GB
> in size, I am trying to copy all the content of CTX_stg to CTX_dev, but
> efore I do that I would like to have a copy of CTX_dev, in case something
> goes wrong .
> My plan is to use DTS Export wizard export CTX_dev to CTX_dev_bak, then
> export CTX_stg to CTX_dev. However I don't have enough disk space in C:
> drive, so is there away I can create a database in D: and then Export to it?
> Or are there better ways to do this?
> TIA
>
>|||Other options are log shipping or Replication