Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I use OLEDB to connect to a SharePoint List from MS SQL using DTS?

Posted on 2008-11-06
17
Medium Priority
?
2,631 Views
Last Modified: 2013-11-30
I have been given a connection string which I am told will allow me to connect to a SharePoint list. I wish to connect using DTS in SQL and work with data in the sharepoint list. Can you advise how to set this up? I do not have much experience of DTS and am using wizards and cannot see where or how to use this connection info.
The Connection string : Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};

Thanks!
0
Comment
Question by:BriCog
  • 9
  • 5
  • 3
17 Comments
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 600 total points
ID: 22894557
You are going to need that Data Provider installed to see it in DTS. I think this is it: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
After that you can drag over the SQL Server connection and change your data provider to Microsoft.ACE.OLEDB.12.0
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22894604
Here is the screenshot for editing the connection properties.
Microsoft.ACE.OLEDB.bmp
0
 

Author Comment

by:BriCog
ID: 22894785
Thanks, I have installed the drviers and I can now select the data source you advise. From the connection string I have been given, how do I complete the connection properties? What goes in the Data Source field?

Thanks again, I appreciate this is real basic stuff...

ConnProp.JPG
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22895051
Whoever provided the connection string should be able to give you the Data Source, which is what this provider requires. You may also need to make changes to Extended Properties in the All tab. This value is generated by MS and probably doesn't mean anything to DTS at this point. {08F43A76-41E2-4EE5-B4B6-207510F05F16}
0
 

Author Comment

by:BriCog
ID: 22896286
the {08F43A76-41E2-4EE5-B4B6-207510F05F16} is the GUID of the SharePoint list..

I will ask about the datasource
0
 

Author Comment

by:BriCog
ID: 22896675
Hi,
Feedback from our SharePoint guys:
Quote
The data part should be http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};. And you need to use windows authentication.
I am not sure about this, as we have never used this type of connection before in our development work. We used the connection string in the default APIs provided by OLEDB class of .NET Framework.
EndQuote
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 1000 total points
ID: 22918928
First of all test your connection string, external to anything else:
1. Create a blank text file
2. Paste in your connection string:
Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};
3. Save the file
4. Rename the file extension to .UDL instead of .TXT
5. Double click it and test it
This verifies that its a valid string and it can connect.
If that is successful, you start up DTS and use a connection type of 'Microsoft Data Link' and then point it at your text file (.UDL file).
If you get that far, post back.
 
Note that neither DTS or UDL are recommended for new development. If you have the opportunity you should use SSIS (the version of DTS that comes with SQL 2005)
 
0
 

Author Comment

by:BriCog
ID: 22921360
Hi,
I have tried as you suggested, but get an error on step 5.,  attached: "...File Cannot be opened. Ensure it is a valid Data Link file'.
I guess I need to query this with whoever provided it?
I can access the ShaePoint site and can see that the GUID is correct...

I am trying this from a SQL server 2000 that also has a front end that is not yet compatible with 2005. So it will not be 2005 for some time...
0
 

Author Comment

by:BriCog
ID: 22921368
attached...
UDL-error.JPG
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22926559
I get the same error but I thinks its because the driver won't install in Vista x64.
Basically you've got to be able to see that provider listed in the first page (The 'Provider' tab) of the data link properties. I think you have commented earlier that you can actually see it in the provider tab? Can you screenshot it and post it up?
 
Asuuming that in your screenshot above you have indeed selected the ACE provider in the first tab, I assume that
Data Source = http://teamupeudev/CAEP/
Catalog = {08F43A76-41E2-4EE5-B4B6-207510F05F16}
 
This page at least verifies that the connection string is correct:
http://www.connectionstrings.com/default.aspx?carrier=sharepoint
0
 

Author Comment

by:BriCog
ID: 22959518
Hi,
Sorry for late reply...

See SP1 & 2, I believe this is the correct provider.

In SP3 I enter as the data source:
DATABASE=http://teamupeudev/CAEP/; LIST= {08F43A76-41E2-4EE5-B4B6-207510F05F16};

I cannot select NT as it is greyed out.
When I key in my domain\userid & pass , and test connection I get error, SP4: 'Not valid accout...'
These are my network credentials and can browse the list normally through sharepoint.

SP1.JPG
SP2.JPG
SP3.JPG
SP4.JPG
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23009176
Just one more thing to try: don't put the DATABASE= part into the field.
Then press OK and then open your .UDL file in notepad and see what it looks like. Can you post it here also as I'm interested to see what it comes up with. It should be roughly similar to what you were given by your developer team.
You may need to make some manual changes to it to get it to work.
 
To summarise, there are two simple ways to build and test a connection string:
1. Type the connection string into notepad, save as .UDL and open and test it.
2. Create a blank .UDL, open it and fill in the fields
 
Option 1 doesn't work because we get a "File Cannot be opened. Ensure it is a valid Data Link file" error. From the net this seems to imply that the driver isn't installed properly or is inaccessible, though I have no doubt that you have installed it properly.
Option 2 doesn't work because the input fields don't match what is in the required connection string - the connection string does not require credential information, but the UDL window does.
0
 

Author Comment

by:BriCog
ID: 23033294
Hi,
Thanks for your efforts here.

I have not been able to save the set up due to the errors. In my previous posting I got the 'Not vlaid filename' when using the 'Test Connection' option. This is all within DTS.
When I try to save the Datalink either with or without the DATABASE= part, I get the 2 errors attached (SP5 & SP6) when I click OK, OK.  I cannot save the details I have entered. These are with my valid network credentials. When I leave it as default (Admin with blank password) I get SP5 and then SP7.

Now I don't know if this has any relevance, but I've tried using Microsoft OLE DB Provider for Internet Publishing with datasource http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};. When I use 'test connection' it suceeds and I can save it. I have no idea what this indicates though... See SP8.
SP5.JPG
SP6.JPG
SP7.JPG
SP8.JPG
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23039528
For the SP5 error, you could open the UDL file on notepad and delete the 'persist security info' element and try again. The UDL editor will probably put it back in though. Could you paste the contents of the UDL file up in a comment?
Regarding Microsoft OLE DB Provider for Internet Publishing, that actually sounds like a much more likely driver to work. Can you actually select data from that one if you use it in DTS?
 
Just to confuse things further, these posts indicate that it is the ACE driver which should be used:
http://cosier.wordpress.com/2008/11/19/connect-to-a-sharepoint-list-via-ole-db/
http://www.connectionstrings.com/?carrier=sharepoint
Which is bizzare because ACE is used for connecting to MS Access and Excel, not SQL Server.
Sorry I hope I'm not adding to the confusion here.
 
0
 

Author Comment

by:BriCog
ID: 23099510
Will post more, maybe next year...I am travelling for the next 2 weeks and then the holdiays...

With Inter. Pub. the conenction tests ok, but the subsequent select fails (will post error next time...)

More confusion I can handle, and would hardly notice...
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23100864
hehe enjoy your travels.
0
 

Accepted Solution

by:
BriCog earned 0 total points
ID: 23447760
Cancelled my membership for now, thanks for the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question