April 28, 2005 at 4:02 pm
This should be an easy question (and dumb for most of you) but I'm wondering how to export a table (Schema and data) into an SQL script file? (either though SQL or Enterpirse manager)
the 'issue' is that my web hosting gives me access to an SQL database, but no non-web access to it. So I can't use DTS to copy the data from my Access database(s) into my
new SQL server tables.
Since web based admin does have a built in Query analyzer, I thought this would be the best way to go about transfering the data.
TIA!!!!
April 29, 2005 at 1:09 am
You can script the table structure by using generate sql script.
this option can be found by right clicking the database and under All task you will be able to find generate sql script
when it comes to data scriptsing unfortunatly MSSQL Server does not have this functionality. THere are some tools available . google the net and find.
My Blog:
April 29, 2005 at 3:24 am
check out DB Ghost, it has a scripter component so you can script both schema and data and put into your source control, a build component so you can build a database using scripts which you can extract from source control which builds a database in the correct order, a comparison component to compare databases and a synchronization component to make two databases the same and record a SQL delta file for later use. Combined this facilitates true database change management.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
April 29, 2005 at 5:38 am
Does this need to be totally automated? What are your rights on the sql server?
1. You said you have Enterprise Manager. Using EM you can export the table into Access. TABLE-ALL TASK-EXPORT DATA
The problem might be can you even see your local access database when you are performing the export; since you are connected to your web host.
2. Since this is a web host do you have a few folders on their server to FTP your web files? If so you could always Export the data to a txt file in one of those folders and then FTP the files back to the machine which has ACCESS installed. Then you would import the txt file back into ACCESS. You could script the FTP in a .bat file, but I'm not sure about the whole process; again depending on rights.
April 29, 2005 at 8:32 am
Ok, I'll take a look. What would be good keywords to google on?
April 29, 2005 at 8:33 am
I'll take a look, but I'm hoping for more of a free solution.. =)
April 29, 2005 at 8:37 am
No it does not need to be automated. ALL I'm looking for is a why to get my data from my Access databases over to my host. (GoDaddy, in case that might help anyone)
as for the other solutions, none of those will work. The host doesn't allow for direct access to the databases, and there's 'no import from access' function of the web based SQL-Server manager. The only way to get data into the Database is either:
A) by Code (write a program in server script that imports the data)
B) By comma delimited file (Which right now 1500 records times out the log in session)
C) By SQL script.
I'm trying for option C, so what I'm looking for is a way to export the table structre AND the data itself into an SQL script so that I can run that on the host.
April 29, 2005 at 9:08 am
You said you can use Enterprise Manager (web based?). What if you FTP'd your Access database to folder on the web server that you have permissions. Then you would be able to use the Import Wizard to import the data from the Access db into the sql server db.
What you need is a way to get at the Access database from the sql server (host). Even with a sql import script you would need the connectivity between the two. You have to establish some sort of connectivity between the two. Either they need to expose the IP address (doubtfull), or you need to put the Access db on their server.
April 29, 2005 at 9:22 am
A quick way to get just one table schema is to use SQL Query Analyzer, browse to the table in the Object Browser (F8 if the Object Browser is not already displayed), then right-click the table and choose Script Object to New Window As Create. This can be faster than using SQL Enterprise Manager's script generation.
April 29, 2005 at 9:33 am
pogowolf is going from access to sql server, so the access table will be created in sql server; not scripted from an existing sql server table
April 29, 2005 at 11:17 am
it's not EM, it's just a web based front end. Think of MySQLadmin for MS-SQL. (just not as good)
But you are right.. I could do that. BUt then it would need to be via an Classic ASP or ASP.NET script to do it. WOuld you happen to know of any ASP scripts that could read from an Access DB, and dump the tables/data into SQL?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply