Exporting MS Access to SQL Server

hi,
how can export an Access Data bank to sql Server with all definition (the
relationship between the tables)
You can use the Access Upsizing wizard (available on MS web site). But most
people prefer to use DTS and do things manually..THe upsizing wizard makes
some assumptions which must be manually handled anyway..Look at DTS with
Access as a data source.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zaid" <Zaid@.discussions.microsoft.com> wrote in message
news:6D71D63A-FE51-4066-9171-60DC7A1A7A11@.microsoft.com...
> hi,
> how can export an Access Data bank to sql Server with all definition (the
> relationship between the tables)
>

Exporting MS Access to SQL Server

hi,
how can export an Access Data bank to sql Server with all definition (the
relationship between the tables)You can use the Access Upsizing wizard (available on MS web site). But most
people prefer to use DTS and do things manually..THe upsizing wizard makes
some assumptions which must be manually handled anyway..Look at DTS with
Access as a data source.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zaid" <Zaid@.discussions.microsoft.com> wrote in message
news:6D71D63A-FE51-4066-9171-60DC7A1A7A11@.microsoft.com...
> hi,
> how can export an Access Data bank to sql Server with all definition (the
> relationship between the tables)
>

Exporting a table and importing it

I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
--
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>

Export to Excel - Why a blank Sheet 1 with data on Sheet 2?

I'm wondering what I might have done in my report definition to cause this.
When I export a couple of my reports to Excel, it ends up on Sheet 2, while
sheet 1 is a blank spreadsheet. Can anyone point me at what I need to look
into? oh. I've got other reports that seem to export just fine.
Thanks
Rob MillerHave you tried to export to PDF? Does is go to a 2nd page?
If so, your margins or body size are incorrect.
Immy
"Rob Miller" <robert.miller@.acoa-apeca.gc.ca> wrote in message
news:eKKuiCsEFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I'm wondering what I might have done in my report definition to cause
> this. When I export a couple of my reports to Excel, it ends up on Sheet
> 2, while sheet 1 is a blank spreadsheet. Can anyone point me at what I
> need to look into? oh. I've got other reports that seem to export just
> fine.
> Thanks
> Rob Miller
>|||Do you have any document map entries? They would create a title page. Also,
if you have explicit page breaks in your report they will show up as
multiple workbooks.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Miller" <robert.miller@.acoa-apeca.gc.ca> wrote in message
news:eKKuiCsEFHA.3376@.TK2MSFTNGP12.phx.gbl...
> I'm wondering what I might have done in my report definition to cause
> this. When I export a couple of my reports to Excel, it ends up on Sheet
> 2, while sheet 1 is a blank spreadsheet. Can anyone point me at what I
> need to look into? oh. I've got other reports that seem to export just
> fine.
> Thanks
> Rob Miller
>|||Rob,
You've got something causing a page break. When exporting to Excel it
treats page breaks as new sheets. Check the setting of the
'PageBreakAtStart' property for any data regions you've got. I suspect
one is set to 'True'.
Regards
Chris
Rob Miller wrote:
> I'm wondering what I might have done in my report definition to
> cause this. When I export a couple of my reports to Excel, it ends
> up on Sheet 2, while sheet 1 is a blank spreadsheet. Can anyone
> point me at what I need to look into? oh. I've got other reports
> that seem to export just fine.
> Thanks
> Rob Miller

export to excel - formatting number output

Hi all,

Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).

I

have a relatively simple task - create an excel spreadsheet with 3

columns of data - Id, Description and Sales. ID and Description are

text, sales is int.

So my SP aggregates and creates my resultset

in my OLE DB Source in the Data Flow. It proceeds to the Excel

destination, and that all seems fine. My issue is that the data is

being written as text. Looking at the excel destination in Advanced

editor:
the Excel Destination Input, Input columns are formatted as

I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and

DT_I4 for the Sales.
Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.

The

target excel spreadsheet is being created from a template file. That

template file has header columns. The target column for the Sales has

the entire column formatted to NUMBER (0 decimals). Yet to now avail.

When

I check the spreadsheet, the column has retained the cell formatting,

and I have a 'I' pop-up to inform me that 'someone' has inserted text

data into the number column (even though the data IS number).

Since

the SP spits out INT, it isn't a case of receiving a text value, imho.

While trying to change the external column data type in the advanced

editor, SSIS is quite happy to let me change the value for the Sales

output to DT_I4, apply, and ok. Then, when I open it immedaitely

aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't

handle it, at least tell me when I try and change it. don't let me

change it, and then revert back without telling me! Grumble grumble...

So - anyone know a way around this?

There are no perfect solutions for many behaviors of Jet with the Excel driver. However the driver is always happiest when the destination "table" has been created by the driver itself, which could be done through the Excel Destination or the Import and Export Wizard or ADO code. Excel column formatting seems to be disregarded completely.

The most comprehensive list of Excel driver-related issues is in 257819 How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819. Many of these will surface in SSIS in one form or another.

-Doug

|||

Hi Douglas,

As I read

the links I’ve found so far, IMEX=1 specifies text? Is there a way to have the

IMEX setting per column? If so, what would the correct IMEX setting for numeric

be?

TIA

|||

IMEX makes all data one type. By default that's Text (based on a Registry entry), and I've never heard of anyone changing it.

Please trust me on this -- if you want more predictable behavior, create the destination table in Excel by using the Excel driver. There appear to be hidden Excel column or cell settings that the driver is aware of but that are not exposed in the Excel UI (and not through cell formatting options).

-Doug

|||Thanks Douglas. I'll try that approach. It would seem to imply something like this:

CREATE TABLE `Sales Rank` (

`CatalogueItemReference` NVARCHAR(8),

`DescriptionText` NVARCHAR(100),

`TotalSalesForPeriod` INTEGER

)

that seem about right?|||

I'm not sure whether Jet SQL recognizes NVARCHAR but always have trouble finding the list. Somewhere on your disk there's a JETSQL40.CHM or SQLJET40.CHM or similar. I'd be more inclined to let the Import and Export Wizard or the Excel Destination write the statement for me.

-Doug

|||Thanks Doug, the sample I wrote was a slightly modified version of the

table create that the ssis excel destination task generated. I'll post

if it works :-)

Export Stored procedure to Access Error

When trying to export the output of a stored procedure to an Access database
,
I get the error
"Syntax Error in field definition"
at the first step of the process (output table creation)
The procedure runs fine in QA, and has the following general structure:
...
select * into #MyTempTable
from ...
...
select * ] this is what I need
from ... ] to be the output
join #MyTempTable on ... ] of the procedure
... ]
Could the use of a temporary table be the cause of the problem?Try using SET NOCOUNT ON at the beginning of the procedure.
ML
http://milambda.blogspot.com/|||I did and still get the same error
"ML" wrote:

> Try using SET NOCOUNT ON at the beginning of the procedure.
>