Import Data Problem

Hi all...
I am trying to copy all tables, views, and stored procedures (except for only
one table the user does not want copied) from a database on a remote server to
another database on a local server. Every time I try to run this copy procedure,
it runs for a while and the throws the following error:
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
'Local_Table_Name'.
The source tables have multiple relationships defined to multiple fields in each
table, and I think this is where it is failing.
What can I do to work around this to get the object copied?
Thanks!
Do you get this error when you try to create the script or when you run the
script?
If you get it when you run the script you have a dependency problem. You
will have to investigate when the problem occurs and order these elements
of your script accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:%235fjmbdFFHA.3616@.TK2MSFTNGP10.phx.gbl...
> Hi all...
> I am trying to copy all tables, views, and stored procedures (except for
only
> one table the user does not want copied) from a database on a remote
server to
> another database on a local server. Every time I try to run this copy
procedure,
> it runs for a while and the throws the following error:
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
> 'Local_Table_Name'.
> The source tables have multiple relationships defined to multiple fields
in each
> table, and I think this is where it is failing.
> What can I do to work around this to get the object copied?
> Thanks!
|||I just modify and click through the Import Data Wizard, then click the button to
make it go, and sit back and wait. It shows progress, then after a loooong while
(because this is a large database), the error I described pops up. I also have a
DTS package built that I can alter and run. It only has the one task "Copy SQL
Server Objects". So, I am not quite sure what you mean by "order these elements."
Hilary Cotter wrote:
> Do you get this error when you try to create the script or when you run the
> script?
> If you get it when you run the script you have a dependency problem. You
> will have to investigate when the problem occurs and order these elements
> of your script accordingly.
>
|||take the problem table out and try to DTS it again, unfortunately this will
mean you are starting from scratch again. So you might want to find out what
tables have been successfully exported and then only DTS the tables which
are missing.
You might want also want to look at backing up the database and restoring it
on the destination server.
The problem is SQL Server uses a table called sysdepends to track
dependencies. This table can get out of sync by various database
operations - like alter proc statements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:exNXBHfFFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I just modify and click through the Import Data Wizard, then click the
button to
> make it go, and sit back and wait. It shows progress, then after a loooong
while
> (because this is a large database), the error I described pops up. I also
have a
> DTS package built that I can alter and run. It only has the one task "Copy
SQL
> Server Objects". So, I am not quite sure what you mean by "order these
elements."[vbcol=seagreen]
> Hilary Cotter wrote:
the[vbcol=seagreen]
elements[vbcol=seagreen]

import accessfiles with stored procedures

Hi,
Could somebody tell me how to import access-files to a sql server database?
I would like to import all tables from access to the temp, do some
data-manipulation and then insert into a database. I know that dts can
import, but i want to address the tempdb so i don't have to create
temptables in my databases.
I hope someone can help me.Jason
One option is
INSERT INTO .....
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Could somebody tell me how to import access-files to a sql server
database?
> I would like to import all tables from access to the temp, do some
> data-manipulation and then insert into a database. I know that dts can
> import, but i want to address the tempdb so i don't have to create
> temptables in my databases.
> I hope someone can help me.
>|||Hi Uri,
I've tried to use your example, but i'm getting errors which says that it
cannot find the path, but i'm sure the file is there?
Here's the statement:
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
And here's the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Jason
> One option is
> INSERT INTO .....
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\northwind.mdb";
> User ID=Admin;Password='
> )...Customers
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:esocaw2EFHA.3416@.TK2MSFTNGP09.phx.gbl...
> database?
>|||Jason
Have you ran on the server or client machine?
Where is the file's location on the server?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi Uri,
> I've tried to use your example, but i'm getting errors which says that it
> cannot find the path, but i'm sure the file is there?
> Here's the statement:
> SELECT *
> FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data
> Source="D:\klient.mdb";User ID=Admin;Password=')...sysdata
> And here's the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Could not find file 'D:\klient.mdb'.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:etAE$62EFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Uri,
The files resides on a client pc.
Greetings,
J
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Jason
> Have you ran on the server or client machine?
> Where is the file's location on the server?
>
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:OY5T7zAFFHA.1564@.TK2MSFTNGP09.phx.gbl...
it
can
>|||Then you need to specify an UNC path for the file, and the share need to acc
essible to SQL Server
and the account that SQL Server is using need to have permissions on the sha
re.
SQL Server is a *server* product, the queries are executed on the server, no
t the client. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotrmail.com> wrote in message news:Oub81JCFFHA.3940@.tk2msftngp13.phx.g
bl...
> Hi Uri,
> The files resides on a client pc.
> Greetings,
> J
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eU3lf$AFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> it
> can
>