SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 link to file server secure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heze
Posting Yak Master

USA
192 Posts

Posted - 10/04/2006 :  16:11:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5514 Posts

Posted - 10/05/2006 :  00:40:06  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 10/05/2006 :  13:47:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000