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.
| Author |
Topic |
|
macsterling
Yak Posting Veteran
56 Posts |
 Posted - 2010-11-26 : 19:28:24
|
I have a SQL 2005 database called HWG with a table in it called Contacts. There are a number of columns but I am only interested in FirstName, LastName, and Workemail (all nvarchar.)I also have a Access database called “Copy of Main Client DB” (note spaces in name) with a table in it called emails. There are several columns, but I am only interested in “Last Name”, “First Name”, and EmailAddress (note spaces in the column names)I need to insert rows in the SQL database filling only the FirstName, LastName, and Workemail columns. The data will come from the Access database “Last Name”, “First Name”, and EmailAddress columns.I have both databases on each of two computers (trying two different approaches) and have tried the following. On one computer I set up an ODBC link from the Access to the SQL database. I can perform a select * from the Access DB looking into the SQL db. I am not able to do the insert into the SQL db. I get the infamous “syntax error” (That is Microsoft  for “you made a mistake, stupid, and if you are that dumb I am not going to help you by giving you any more information.)On the other computer, I have, from within SQL 2005, created a linked server (using the SQL Below) called HWG. I do not know how to reach into the access database and pull the data for the insert.sp_addlinkedserver 'HWG', 'Access 2007', 'Microsoft.Jet.OLEDB.4.0', 'c:\Macs Stuff\Copy of Main Client DB.mdb'In either case, How do I write a SQL that that will cause a new row to be created in the SQL DB from the Access DB? |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-26 : 19:45:01
|
You can use openquery to get it from access.Select FirstName,LastName,WorkEmailFROM OPENQUERY(HWB, 'Select FirstName,LastName,WorkEmail from emails')
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2010-11-26 : 19:53:29
|
| Thanks - I am getting the following error msgMsg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-26 : 20:01:23
|
Do this, then retry..sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGURE; See if that does the trick...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2010-11-26 : 20:34:23
|
| same error How about from the other (access) side? I can get a select * from the ODBC to work, just not the insert |
|
|
|
MohanKandasamy
Starting Member
9 Posts |
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2010-11-27 : 23:01:14
|
| RATS! I went out and bought a fancy 64 bit machine and it won't do dumb thingsThanks for the link Guess I am going to have to do this from the Access side on a 32 bit machine. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
|
|
|
|
|
|
|
insert into SQL rows from Access db