Exporting Primary Keys Into Access

Hi There

I have an SQL database which I need to export into Access, I know how to this however the Primary Key's does not transfer over and more importantly once exported I cannot set the primary key to auto number as their is already information in the table.

I know I can just set up the table, with a primary key set to an autonumber and import the other fields but as the primary key's are foreign keys in other tables I need the rows in the access table to have the same ID number as in the SQL database.

Hope the above makes sense to someone and any help would be greatly appreciated.you should be able to import key values into an access autonumber column with no problem

i've tested it sucessfully, and it works

what error did you get?|||Thanks for the feedback, it works fine now. I was advised wrongly elsewhere that it couldn't be done.

Very simple in the end!|||you gotta be careful with stuff you find on the internet, eh

Exporting data and keep the primary key

Hi all,

I am rather fresh to SQL2000, so...

When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?

How can I keep the primary key in the copied tables ?

Help is appreciated, Ger.

I have the same problem. Is there an answer to this?|||How did you export ? If you make a SQL copy objects task via DTS you can select if you also want to preserve the PK / index structure etc. Its one of the advanced options of the DTS Wizard.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I am using Sql Server 2005 Management Studio, I run the DTSWizard by running DTSWizard.exe from the "Run" menu. But I can not copy my database with the primary keys and relations.

After reading your post I have tried to find the advanced options of DTS Wizard but couldnt managed to do that. If you can give detailed information I will be glad.

Thanks in advance

Exporting data and keep the primary key

Hi all,

I am rather fresh to SQL2000, so...

When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?

How can I keep the primary key in the copied tables ?

Help is appreciated, Ger.

I have the same problem. Is there an answer to this?|||How did you export ? If you make a SQL copy objects task via DTS you can select if you also want to preserve the PK / index structure etc. Its one of the advanced options of the DTS Wizard.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I am using Sql Server 2005 Management Studio, I run the DTSWizard by running DTSWizard.exe from the "Run" menu. But I can not copy my database with the primary keys and relations.

After reading your post I have tried to find the advanced options of DTS Wizard but couldnt managed to do that. If you can give detailed information I will be glad.

Thanks in advance

sql

export to exce1

I am using subreport techique to include in my report layout. I can see all
the report and subreport (by clicking the toggle key) from the internet
browser. After exporting to EXCEL format, i can't see the subreport items.
What went wrong'
Thanks.What version/service pack are you using? Is your subreport contained in a
table?
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Theresa" <Theresa@.discussions.microsoft.com> wrote in message
news:94D59D45-FE59-4B88-8FE0-7A33080AFD31@.microsoft.com...
>I am using subreport techique to include in my report layout. I can see
>all
> the report and subreport (by clicking the toggle key) from the internet
> browser. After exporting to EXCEL format, i can't see the subreport
> items.
> What went wrong'
> Thanks.|||Hi Andy,
Thanks for your email.
I am using SQL 2005 (version 9.00.1399.00) and Excel 2003 SP2. The subreport
is contained in the detailed row of my table. This subreport is perfected
for my application, it return different number of rows and with hidden
property set to true. Each time click the toggle key it display the result
from the subreport. Unfortunately, it just could not display the contents of
the subreport when exported to Excel. Thanks for your attention. And would
like to hear your response.
"Andy Potter" wrote:
> What version/service pack are you using? Is your subreport contained in a
> table?
> --
> Andy Potter
> blog : http://sqlreportingservices.spaces.live.com
> info@.(NOSPAM)lakeclaireenterprises.com
> "Theresa" <Theresa@.discussions.microsoft.com> wrote in message
> news:94D59D45-FE59-4B88-8FE0-7A33080AFD31@.microsoft.com...
> >I am using subreport techique to include in my report layout. I can see
> >all
> > the report and subreport (by clicking the toggle key) from the internet
> > browser. After exporting to EXCEL format, i can't see the subreport
> > items.
> > What went wrong'
> > Thanks.
>
>

Export some tables from one DB to another WITHOUT lost of primary key?

Hi,
i am using the enterprise manager of our sql server 2000. i want toexport some (not all) tables from one DB to another one with all thedata inside these tables AND the primary keys. if i do i only receivethe tables with the data but all primary keys and indexes will be lost.
how to do it?
thanks, tobi
Script the tables and indexes and execute them in the new database, and create a linked server between the two and do SELECT INSERT INTO. Hope this helps.|||You should be able to use the DTS Export Wizard. Right-click on a database, All Tasks, Export Data.
Select your source and target server/databases, and select "CopyObjects and Data" instead of the default "Copy Tables and Views".
De-select "Copy All Objects" and click the Select Objects button toselect the tables you want. The option to export primary keys isautomatically selected, but can be modified by de-selecting the "Usedefault options" checkbox.

|||AdamMachanic, thanks for your reply.
in 99% i'll get an error (translated by me from german ;):
"[microsoft][odbc sql server driver][sql server]the windows nt-user orthe -group "servername\username" where not found. please check thename."
whats wrong with this?
|||In the DTS Wizard, you need to uncheck 'Use default options', click Options and uncheck both 'Copy database users and roles' and 'Copy object level permissions'.
Unless the Users exist on both SQL Servers, this will generate errors. Objects such as PKs, FKs, Tables, SPs etc will usually copy fine, but Users will not.|||thanks!!! it worked finally!
|||hi,
it worked for a while...but now i have some big trouble with it.
if i do the same steps today:

-"CopyObjects and Data"
-De-select "Copy All Objects" and click the Select Objects button toselect the tables i want.
-uncheck 'Usedefault options', click Options and uncheck both 'Copy database usersand roles' and 'Copy object level permissions'.
all data will be overwritten on the destination DB. i tried thisseveral times. even in the overview (the last step) it only show"Tables" at the end of the overview, not the table-names to copy.
i get mad on this!
isnt there an option in the dts wizard to generate in sql scripts so ican check what it will do before executing it? (like mysql tools do)

|||yesterday again i tried to export some tables from the live DB to my development DB and again i had this problem. is there a known bug with enterprise manager? maybe someone could give me a step by step tut about how to do this. i get mad on this DTS stuff - in mysql this all is so easy.