Been searchin but ain't finding!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Been searchin but ain't finding!

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-06 : 11:24:06

I have tables with data in Access and the required tables created in SQL Server.

I can import using the import wizard in EM but would rather write a stored procedure to so the importing of all the tables so I can start it off and go and watch some TV.

So...

insert into <mySQLTable> select * from <myAccessTable>

But how do I get SQL Server to know of the Access database?

Thx

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-06 : 11:36:49

You have to set it upp as a linked server.
see sp_addlinkedserver in BOL for examples.

rockmoose

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-06 : 11:49:41

Thanks :)

Shall play with this. I kept trying to link to it in EM but got the error 'cannot link to local server'

did not know about sp_addlinkedserver

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-06 : 14:39:04

I have run the following:

EXEC sp_addlinkedserver
@server = 'MyServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Documents And Settings\peter\My Documents\trappings.mdb'

And I get the result (1 row(s) affected) (1 row(s) affected)

I cannot see the link in the object browser or EM or get a query to reference the linked file?

I am getting confused with the servername/instance syntax.

I tried MyServer.trappings.items but get errors?

Have searched here and books online for sp_addlinkedserver but cannot find the answer?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 15:33:57

>> @datasrc = 'C:\Documents And Settings\peter\My Documents\trappings.mdb'
That will be on the c drive of the server - is that where the access database is?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-06 : 15:49:32

Yes, the server is running on my laptop and the Access database is on my C drive.

I have since found the server in EM under Security Linked Servers and can access the data thus

SELECT * FROM [MyServer]...items

This returns all the data in the Access table. But when I try to do this:

INSERT INTO items SELECT * FROM [MyServer]...items

I get diferent errors depending on the source table from column not matching to data conversion errors.

But if I do:

INSERT items (fld1, fld2, fld3, etc)
SELECT fld1, fld2, fld3, etc
FROM FROM [MyServer]...items

It works a treat.

Down side is some I have many tables and they have hundreds of columns and it will take an age to list all the columns!!!

Any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 17:42:55

run this (after correcting any errors)

select 'insert Items ('
union all
select name + ','
from syscolumns where id = object_id('items') order by colid
union all
select ') from'
union all
select name + ','
from syscolumns where id = object_id('items') order by colid
union all
select 'from myserver...'

Get rid of the odd comma unless you feel like coding it out.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 19:45:42

"would rather write a stored procedure to do the importing of all the tables"

Wouldn't it be easier to use DTS? Or is that what you tried already with the Import wizard?

You should be able to save the "package" for later - which would let you watch TV whilst it runs through all the tables!

Kristen

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-07 : 05:02:45

NR, you say correcting any errors but I don't know the cause of these errors?

I generate the creat table scripts from the source SQL Server host.
I have these tables linked in an Access database and import the complete tables into Acccess.
I link successfully to this Access database and can run SELECT * FROM [MyServer]...items OK.
But if I try INSERT INTO items SELECT * FROM [MyServer]...items I get errors 'O' plenty.
Don't understand if I use a generated script to create taget tables they should match?

Kristen, cheers I did not know I could do that and have just done so :) RESULT! :) :)

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-07 : 06:40:30

A problem arise tho! Once I have saved the DTS import thru Import Wizard how do I open it another time? Each time I go to import it opens the wizard and I cannot see where I go to choose to load my import script?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-07 : 07:41:32

Found it! :)

Kristen
Test

22859 Posts

Posted - 2006-02-07 : 08:34:29

"Found it!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources