link to file server secure? - SQL Server Forums

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

SQL Server Administration (2000)

link to file server secure?

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-10-04 : 16:11:17

Hi, our SQL Server is currently in one server and the file server of the department is in a different one. The file server contains several Access tables I want to extract data from to be used in sql server. To select * from one of these tables I tried
------------------------
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\myserver\myPath\mySourceAccessDB.mdb";
User ID=Admin;Password='
)...myTable
-------------------------
the latter yielded errors in the following lines:
-----------------
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: 'G:\DBFILES\CB_RPTS\CBM001\CBM001.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaRowset::GetRowset returned 0x80004005: ].
---------------------

I tried linking the access table in my SQL Server Server and query it from there, there is no problem if i make a copy, the query works but I dont want to make a copy I want to query either the linked or the real source table
------------
Since I was unsuccesfull for several hours I tried DTS and it worked fine, but I dont feel very confortable when I cant see the code, and my knowledge of DTS is mainly of its wizards.

I have 3 questions:

1-I suspect that if I map the file server from the SQLServer Server so that this latter gets data directly from there the query above may work, my queston is, is this secure? Mapping a drive in the sql server to where the access database resides in the file server?

2-Why is my query not working the way I described in the beggining?

3-is there a way to program DTS using code or to see the code generated rather than using only wizards?

Thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 00:40:06

What you really want to do on the Access table? Query it or modifying its contents or copy it?

1) Creating linked server will be the best solution if you frequently query the access database. Security of this link depends on the target database security implementation (in your case, MS Access)

2) Your code is not working because it seems from the error message, you are using mapped drive to connect to Access. If you give path in the form of "\\servername\sharedfolder\...", it should work

3) DTS is entirely programmable. You can program it using ActiveX script or Visual Basic 6

Harsh Athalye
India.
"Nothing is Impossible"

heze
Posting Yak Master

192 Posts

Posted - 2006-10-05 : 13:47:52

thanks harsh_,

My source table is the access table and my target table is the SQL Server table. I dont want to do anything in the access, just select some specified records from it and insert them into an SQL Server Table. The process of transfering data will be continual and asynchronous.
1) when you say:
--Security of this link depends on the target database security implementation (in your case, MS Access)--
my target table is SQL server so I will assuume this is the table you are refering to
What factors should I consider in the "depends" part?

2) I think I need to do whats suggested in step 1

3)I m working on this, now I have a question what is the advantage of binding column by column individually over executing a simple "insert into targettable select [field set] from source table"?

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources