URGENT: Exporting table to an SQL script

Discussion:

(too old to reply)

musosdev

2007-10-21 12:48:00 UTC

Hi guys

I have a database in Access 2007 which I'm currently trying to rebuild in
SQL Server Express in Visual Studio.

Problem is, I can't seem to find an easy way of exporting tables. I've been
using copy/paste on the data, but that only works with simple tables where I
can build the table in SQLEx first.

Is there a way in Access 2007 (or an Add-in I can download) to export a
table as a list of SQL commands? Yknow.. CREATE TABLE, INSERT, etc!?

Thanks,

Dan

Alex Dybenko

2007-10-22 03:50:52 UTC

Permalink

Hi,
you can use upsizing wizard to export whole database, or Export tables to
ODBC connection to your sql server
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi guys
I have a database in Access 2007 which I'm currently trying to rebuild in
SQL Server Express in Visual Studio.
Problem is, I can't seem to find an easy way of exporting tables. I've been
using copy/paste on the data, but that only works with simple tables where I
can build the table in SQLEx first.
Is there a way in Access 2007 (or an Add-in I can download) to export a
table as a list of SQL commands? Yknow.. CREATE TABLE, INSERT, etc!?
Thanks,
Dan

Peter Yang[MSFT]

2007-10-22 07:38:13 UTC

Permalink

Hello Dan,

As Alex indicates you could use upsizing wizard to do the job. Also, you
may consider use integration service (SSIS) or import/export wizard of SQL
Server 2005 to achieve the goal. I have inlcuded the following articles for
your reference:

How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>

Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>

If the issue is urgent and you'd like to talk to MS support directly, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Please let's know if you have any further questions or concerns. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

musosdev

2007-10-22 09:53:04 UTC

Permalink

Hi Peter (and Alex)

Thank you both for your input so far. However, I'm still confused..

I'm running the SQL Express which is contained within Visual Studio. DTS
seems like the way to go, but I neither have SQL Server Management Studio or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.

In Visual Studio, even when I'm on the Server Explorer tab, I can't find any
options for importing.

I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk to the
database?

Thanks for your help!

Dan

Post by Peter Yang[MSFT]
Hello Dan,
As Alex indicates you could use upsizing wizard to do the job. Also, you
may consider use integration service (SSIS) or import/export wizard of SQL
Server 2005 to achieve the goal. I have inlcuded the following articles for
How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>
Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>
If the issue is urgent and you'd like to talk to MS support directly, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
http://support.microsoft.com/directory/overview.asp
Please let's know if you have any further questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Alex Dybenko

2007-10-23 14:39:03 UTC

Permalink

Hi,
yes, then I think running upsizing wizard from access will be the best
option
.\SQLEXPRESS - will be a server name, you also need a database name in order
to run it
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi Peter (and Alex)
Thank you both for your input so far. However, I'm still confused..
I'm running the SQL Express which is contained within Visual Studio. DTS
seems like the way to go, but I neither have SQL Server Management Studio or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.
In Visual Studio, even when I'm on the Server Explorer tab, I can't find any
options for importing.
I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk to the
database?
Thanks for your help!
Dan

Peter Yang[MSFT]

2007-10-24 02:57:47 UTC

Permalink

Hello Dan,

SQL management studio is part of SQL client tools of SQL Server
standard/entprise editions. If you have any of the programs, you could
install the client tool component on this machine.

If not, as Alex suggested, upsizing wizard might be a better opotion.

Please let's know if you have any further questions or comments.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

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

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

musosdev

2007-10-25 10:59:00 UTC

Permalink

Hi again Alex,

Ok, I managed to get access to see the .\SQLExpress Instance I think, but
there is no database.

Looking at my connection string in VS, it uses "AttachDbFilename=myfile.mdf".

In Acess Upsizing Wizard, I got to the page where it allows you to "change
the default database to" and "attach database filename" - but whatever I put
in there, it says it's invalid.

I *assume* that's where I put the myfile.mdf, but I've tried it on it's own,
the full path (c:\users\dhnash\documents\visual studio 2005\websites\my
site\App_Data\myfile.mdf) - but it says that's invalid?

First of all, am I doing the right thing? Secondly, how do I get it to find
my mdf file?

Cheers

Dan

Post by Alex Dybenko
Hi,
yes, then I think running upsizing wizard from access will be the best
option
..\SQLEXPRESS - will be a server name, you also need a database name in order
to run it
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Alex Dybenko

2007-10-25 16:17:10 UTC

Permalink

hi,
not sure that you can attach mdf the same way like in VS connection string.
I suggest that you ask upsizing wizard to "create new database" at the first
screen, and it will do it for you. You can use master database as default
database for odbc connection string
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi again Alex,
Ok, I managed to get access to see the .\SQLExpress Instance I think, but
there is no database.
Looking at my connection string in VS, it uses
"AttachDbFilename=myfile.mdf".
In Acess Upsizing Wizard, I got to the page where it allows you to "change
the default database to" and "attach database filename" - but whatever I put
in there, it says it's invalid.
I *assume* that's where I put the myfile.mdf, but I've tried it on it's own,
the full path (c:\users\dhnash\documents\visual studio 2005\websites\my
site\App_Data\myfile.mdf) - but it says that's invalid?
First of all, am I doing the right thing? Secondly, how do I get it to find
my mdf file?
Cheers
Dan

Post by musosdev
Hi Peter (and Alex)
Thank you both for your input so far. However, I'm still confused..
I'm running the SQL Express which is contained within Visual Studio. DTS
seems like the way to go, but I neither have SQL Server Management
Studio
or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.
In Visual Studio, even when I'm on the Server Explorer tab, I can't
find
any
options for importing.
I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk
to
the
database?
Thanks for your help!
Dan

Post by Peter Yang[MSFT]
Hello Dan,
As Alex indicates you could use upsizing wizard to do the job. Also, you
may consider use integration service (SSIS) or import/export wizard of SQL
Server 2005 to achieve the goal. I have inlcuded the following
articles
for
How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>
Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>
If the issue is urgent and you'd like to talk to MS support directly, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
http://support.microsoft.com/directory/overview.asp
Please let's know if you have any further questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

musosdev

2007-10-30 10:16:00 UTC

Permalink

Hi Alex,

That seems to make sense, I'll try that.

The only issue then is whether I can get VS to attach this new mdf file to
my Visual Studio project, or at least connect to it. That's a seperate issue
though.

I'll try to upsize to a new file and let you know the result!

Cheers

Dan

Post by Alex Dybenko
hi,
not sure that you can attach mdf the same way like in VS connection string.
I suggest that you ask upsizing wizard to "create new database" at the first
screen, and it will do it for you. You can use master database as default
database for odbc connection string
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi Peter (and Alex)
Thank you both for your input so far. However, I'm still confused..
I'm running the SQL Express which is contained within Visual Studio. DTS
seems like the way to go, but I neither have SQL Server Management
Studio
or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.
In Visual Studio, even when I'm on the Server Explorer tab, I can't
find
any
options for importing.
I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk
to
the
database?
Thanks for your help!
Dan

Post by Peter Yang[MSFT]
Hello Dan,
As Alex indicates you could use upsizing wizard to do the job. Also, you
may consider use integration service (SSIS) or import/export wizard of SQL
Server 2005 to achieve the goal. I have inlcuded the following
articles
for
How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>
Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>
If the issue is urgent and you'd like to talk to MS support directly, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
http://support.microsoft.com/directory/overview.asp
Please let's know if you have any further questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

musosdev

2007-10-30 12:21:00 UTC

Permalink

Hi,

I just tried the Upsizing Wizard using a new database as suggested.

The wizard completes, but gives me a report which basically tells me that
every table in my database "was skipped, or export failed".

I tried to create a new database on the instance .\SQLEXPRESS, it said it
couldn't connect to (local), but let me continue with .\SQLEXPRESS.

The Upsizing report doesn't appear to give me any other information. Anyone
have an idea why it won't export any of my tables?!

I'm starting to feel really dense with this now - surely it shouldn't be
this hard?!

Thanks for any help you can give,

Post by musosdev
Hi Alex,
That seems to make sense, I'll try that.
The only issue then is whether I can get VS to attach this new mdf file to
my Visual Studio project, or at least connect to it. That's a seperate issue
though.
I'll try to upsize to a new file and let you know the result!
Cheers
Dan

Post by musosdev
Hi Peter (and Alex)
Thank you both for your input so far. However, I'm still confused..
I'm running the SQL Express which is contained within Visual Studio. DTS
seems like the way to go, but I neither have SQL Server Management
Studio
or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.
In Visual Studio, even when I'm on the Server Explorer tab, I can't
find
any
options for importing.
I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk
to
the
database?
Thanks for your help!
Dan

Post by Peter Yang[MSFT]
Hello Dan,
As Alex indicates you could use upsizing wizard to do the job. Also, you
may consider use integration service (SSIS) or import/export wizard of SQL
Server 2005 to achieve the goal. I have inlcuded the following
articles
for
How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>
Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>
If the issue is urgent and you'd like to talk to MS support directly, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
http://support.microsoft.com/directory/overview.asp
Please let's know if you have any further questions or concerns. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Alex Dybenko

2007-10-30 14:03:21 UTC

Permalink

Hi,
instead of .\SQLEXPRESS try PCName\SQLEXPRESS

actually when export fails - wizard reports where was a problem, see other
report pages for more info
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi,
I just tried the Upsizing Wizard using a new database as suggested.
The wizard completes, but gives me a report which basically tells me that
every table in my database "was skipped, or export failed".
I tried to create a new database on the instance .\SQLEXPRESS, it said it
couldn't connect to (local), but let me continue with .\SQLEXPRESS.
The Upsizing report doesn't appear to give me any other information. Anyone
have an idea why it won't export any of my tables?!
I'm starting to feel really dense with this now - surely it shouldn't be
this hard?!
Thanks for any help you can give,

Post by musosdev
Hi again Alex,
Ok, I managed to get access to see the .\SQLExpress Instance I think, but
there is no database.
Looking at my connection string in VS, it uses
"AttachDbFilename=myfile.mdf".
In Acess Upsizing Wizard, I got to the page where it allows you to "change
the default database to" and "attach database filename" - but
whatever I
put
in there, it says it's invalid.
I *assume* that's where I put the myfile.mdf, but I've tried it on
it's
own,
the full path (c:\users\dhnash\documents\visual studio
2005\websites\my
site\App_Data\myfile.mdf) - but it says that's invalid?
First of all, am I doing the right thing? Secondly, how do I get it
to
find
my mdf file?
Cheers
Dan

Post by Alex Dybenko
Hi,
yes, then I think running upsizing wizard from access will be the best
option
..\SQLEXPRESS - will be a server name, you also need a database name
in
order
to run it
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by musosdev
Hi Peter (and Alex)
Thank you both for your input so far. However, I'm still confused..
I'm running the SQL Express which is contained within Visual
Studio.
DTS
seems like the way to go, but I neither have SQL Server Management
Studio
or
"Import and Export Data" options on my Start Menu, as discussed in the
document link Peter gave regarding DTS.
In Visual Studio, even when I'm on the Server Explorer tab, I can't
find
any
options for importing.
I have however found that my DataSource in my connection string is
.\SQLEXPRESS. Can I use this in the ODBC connection in Access to talk
to
the
database?
Thanks for your help!
Dan

Post by Peter Yang[MSFT]
Hello Dan,
As Alex indicates you could use upsizing wizard to do the job.
Also,
you
may consider use integration service (SSIS) or import/export
wizard of
SQL
Server 2005 to achieve the goal. I have inlcuded the following
articles
for
How to use data transformation services to export data from a Microsoft
Access database to an SQL server database.
<http://support.microsoft.com/kb/285829/en-us>
Upsizing wizard fails to upsize data in large tables.
<http://support.microsoft.com/kb/295231/en-us>
If the issue is urgent and you'd like to talk to MS support
directly,
I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
http://support.microsoft.com/directory/overview.asp
Please let's know if you have any further questions or concerns.
Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that
each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to
reach
the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature
are
best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers
no
rights.

Peter Yang[MSFT]

2007-10-31 09:28:48 UTC

Permalink

Hello Dan,

I understand you'v expreienced a lot for this issue. You may wish to
consider contacting CSS for a more timely resolution as I mentioned.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

I wasn't able to reproduce the issue on my side. If you try
machinename\sqlexpress as Alex mentioned, do you still encounter the
problem? If the issue still happens, please send me a copy of your database
for testing on my side.

Also, if you are migrating mdb/mde type Access database, you may consider
SQL Server Migration Assistant for Access which is useful based on my test.

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

I look forward to your reply. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

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

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

ABC

2007-11-29 04:58:51 UTC

Permalink

i fie how aARae you uiam from army school ranchi

paulasantos da costa rocha ramalho

2008-01-10 15:42:20 UTC

Permalink