URGENT: Exporting table to an SQL script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

musosdev - 21 Oct 2007 13:48 GMT

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

Reply to this Message

Alex Dybenko - 22 Oct 2007 04:50 GMT

Hi,
you can use upsizing wizard to export whole database, or Export tables to
ODBC connection to your sql server

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Hi guys
>
[quoted text clipped - 13 lines]
>
> Dan

Reply to this Message

Peter Yang[MSFT] - 22 Oct 2007 08:38 GMT

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.

Reply to this Message

musosdev - 22 Oct 2007 10:53 GMT

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

> Hello Dan,
>
[quoted text clipped - 44 lines]
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Alex Dybenko - 23 Oct 2007 15:39 GMT

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

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Hi Peter (and Alex)
>
[quoted text clipped - 73 lines]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.

Reply to this Message

Peter Yang[MSFT] - 24 Oct 2007 03:57 GMT

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.
======================================================

Reply to this Message

musosdev - 25 Oct 2007 11:59 GMT

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

> Hi,
> yes, then I think running upsizing wizard from access will be the best
[quoted text clipped - 79 lines]
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.

Reply to this Message

Alex Dybenko - 25 Oct 2007 17:17 GMT

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

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Hi again Alex,
>
[quoted text clipped - 120 lines]
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.

Reply to this Message

musosdev - 30 Oct 2007 11:16 GMT

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

> hi,
> not sure that you can attach mdf the same way like in VS connection string.
[quoted text clipped - 126 lines]
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.

Reply to this Message

musosdev - 30 Oct 2007 13:21 GMT

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,

> Hi Alex,
>
[quoted text clipped - 140 lines]
> > >> >> This posting is provided "AS IS" with no warranties, and confers no
> > >> >> rights.

Reply to this Message

Alex Dybenko - 30 Oct 2007 15:03 GMT

Hi,
instead of .\SQLEXPRESS try PCName\SQLEXPRESS

actually when export fails - wizard reports where was a problem, see other
report pages for more info

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Hi,
>
[quoted text clipped - 189 lines]
>> > >> >> no
>> > >> >> rights.

Reply to this Message

Peter Yang[MSFT] - 31 Oct 2007 10:28 GMT

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.
======================================================

Reply to this Message

ABC - 29 Nov 2007 04:58 GMT

> Hi guys
>
[quoted text clipped - 13 lines]
>
> Dan

i fie how aARae you uiam from army school ranchi

Reply to this Message

paulasantos da costa rocha ramalho - 10 Jan 2008 15:42 GMT

> Hi guys
>
[quoted text clipped - 13 lines]
>
> Dan

Reply to this Message