INTO with a linked server (ACCESS)

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 2005 Forums

Transact-SQL (2005)

INTO with a linked server (ACCESS)

Author

Topic

Dobe
Starting Member

12 Posts

Posted - 2009-08-06 : 17:32:08

Thanks to this forum's help, I've linked to Access. I would like to be able to use the INTO statement. How can I do this?

select top 10 *
from daychgdb...geo works fine.

select top 10 *
into daychgdb/'test' ? doesn't work
into daychgdb...geo ? doesn't work
from daychgdb...geo

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-06 : 19:54:26

Is "daychgdb/'test'" an Access specific syntax?

The SQL syntax to identify an object is Server.Database.Schema.Object. Have you tried:

select top 10 *
into daychgdb...test
from daychgdb...geo

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Dobe
Starting Member

12 Posts

Posted - 2009-08-06 : 20:35:46

Yes, I have. the above was type in incorrectly.

Each of the above should have been:
inyo daychgdb/'test'
into daychgdb...test

Dobe
Starting Member

12 Posts

Posted - 2009-08-06 : 22:19:26

This is the error I get "Msg 117, Level 15, State 1, Line 3
The object name 'daychgdb...tt' contains more than the maximum number of prefixes. The maximum is 2."

When running this "select * into daychgdb...tt
from daychgdb...comRl"

Dobe
Starting Member

12 Posts

Posted - 2009-08-07 : 10:41:09

Still looking for help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-07 : 18:08:51

I don't know Access well enough to say definitively but could it require a three part name (Database.schema.object) instead of the SQL four part name?

Try removing one of the three periods.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Dobe
Starting Member

12 Posts

Posted - 2009-08-08 : 16:53:39

I'll give it a shot.

Dobe
Starting Member

12 Posts

Posted - 2009-08-08 : 23:50:56

Didn't work.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-10 : 12:25:08

Don't use a into. Create the table first and use OpenQuery, it is far easier to control and less confusing(At least for me).

Step 1 make sure your server is setup for rpc


exec sp_serveroption @server='daychgdb', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='daychgdb', @optname='rpc out', @optvalue='true'

Step 2 Create the table.


exec ('Create Table test(MyCol TEXT,MyCol2 TEXT)') at daychgdb

Step 3 write your query.


INSERT
OPENQUERY(daychgdb,'select col1Name,col2Name from test') --Change the column Names to the ones you are importing to.
SELECT COL1Name,Col2Name --Change to the column names importing from.
from daychgdb...geo

Hope this helps, but the "Select Into" method you mentioned, to the best of my knowledge is not a option. You will need to do it the long way (Which is also a better practice than select into statements.)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Dobe
Starting Member

12 Posts

Posted - 2009-08-10 : 16:15:17

Thanks,
I'll give this a shot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources