 | |
Re: Access to SQL via ".sql" : path of least resistance?
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Access to SQL via ".sql" : path of least resistance? -
12-11-2004
, 11:23 AM
|
A friend has an Access db (handful of tables with just data, no constraints, etc) on his workstation and a hosting package which comes with SQL Server. He can't connect to SQL Server remotely, but can upload ".sql" files to the server. I've got a copy of MSDE running here and to help him out, figured it would be easy to run the Access Upsize into MSDE, then use some Enterprise Manager built-in functionality to spit out the db (schema and data INSERT statements) in one ".sql" file. Well, now it's in MSDE, it's easy enough to export the schema using Enterprise Manager and get a ".sql" file with the necessary commands to recreate the structure, but it doesn't contain the data. If I go down the Export Data... Route and use DTS Import/Export Wizard, and select SQL Server > Text file, the text file just contains the data, without the required SQL statements to execute to import that data at the other end. And, boning up on "bcp", it doesn't seem to provide an option that allows you to dump the data with the required SQL to import it at the other end either. Please tell me there's an easy way to do this? -- Aidan Whitehall Macromedia ColdFusion Developer |
#2
| |||
| |||
|
|
My good friend Vyas has a great way to generate INSERTs http://vyaskn.tripod.com/code.htm#inserts You may want to investigate the licensing around your usage of EM and MSDE. To import the script at the other end somebody is till going to have to run osql or such like. What about a backup and asking the hosting provider to restore it for you? -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - SQL Server 2005 Integration Services. www.Konesans.com "Aidan Whitehall" <aidan (AT) thenetprofits (DOT) co.uk> wrote A friend has an Access db (handful of tables with just data, no constraints, etc) on his workstation and a hosting package which comes with SQL Server. He can't connect to SQL Server remotely, but can upload ".sql" files to the server. I've got a copy of MSDE running here and to help him out, figured it would be easy to run the Access Upsize into MSDE, then use some Enterprise Manager built-in functionality to spit out the db (schema and data INSERT statements) in one ".sql" file. Well, now it's in MSDE, it's easy enough to export the schema using Enterprise Manager and get a ".sql" file with the necessary commands to recreate the structure, but it doesn't contain the data. If I go down the Export Data... Route and use DTS Import/Export Wizard, and select SQL Server > Text file, the text file just contains the data, without the required SQL statements to execute to import that data at the other end. And, boning up on "bcp", it doesn't seem to provide an option that allows you to dump the data with the required SQL to import it at the other end either. Please tell me there's an easy way to do this? -- Aidan Whitehall Macromedia ColdFusion Developer |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.