My Techie Blog

  Home  |   Contact  |   Syndication    |   Login
  110 Posts | 5 Stories | 406 Comments | 0 Trackbacks

News

Archives

Post Categories

ADO.NET

Ajax

API/WebServices

ASP.NET Resources

BizTalk Server

Blogs I read

Free Books

How to

iOT

JavaScript

JQuery

JSLink - SharePoint

JSON

LINQ

Misc

Mobile BI

MS Business Inteliigence

Reporting Services

Responsive/MVC ASP.NET

SEO

Sharepoint Resources/Tools

SharePoint 2010 Branding

SharePoint 2010 Videos

SharePoint 2013

SharePoint Online

SilverLight

SPServices

SQL Server

Sql Server 2012

SSIS

Web Optimization Tools

Web Performace Tools

Windows 8

Windows Azure

Windows Azure

Windows Phone

WorkFlows

WSS V3

xml

 Many of us working with MOSS or WSS v3 must have been battling with this new cool product called Sharepoint Designer.
 And to be frank its really cool when you get to play with it well.
 
 On my Dev WSS site i wanted to get some sample data from Sql server 2005 and host it on my WSS site.
 So i first started by firing up sharepoint designer > Datasource Library and of course clicked on database connections
 It actually allows you to just type in your DATABASE name you want to connect to and then username and password and behind the scenes it creates a connection string for you and bingo you might get in or not.

Anyway let me get onto reality when connecting to sql server 2005 database i didn't have any sucees using Windows Authentication. so i recommend creating a username and password in your database and use it to connect which most of the times works.

The second problem i encountered after connecting  to sql server 2005 was that when i clicked on SHOW DATA on my select datasource it returns an error saying "the server returned a non specific error when trying to get data bla bla "

After pulling my hairs i don't have out and couldn't find a solution i decided to connect to sql server 2000 instance  and all went well to my suprise  and could see some data.

After some searching i found Karine's Blog which saved the day.

It seems there is some bad communication going between SPD and sql server 2005. And the workaround is to use
a custom query preferably store procs which doesn't work sometime because i think SPD does some caching


Read the blog for more info.

Happy sharepointing

posted on Thursday, August 2, 2007 12:29 AM

Feedback

# re: The Database Connections horrors using Sharepoint Designer 8/20/2007 6:20 AM Carla
I had that same problem, created the user and it worked!

now you saved my day! ;)

# re: The Database Connections horrors using Sharepoint Designer 12/3/2007 4:19 PM nancyg
I've had the same problem. In demo database, Microsoft use schemas... and in the wizard, the schema is not added to the string.

# re: The Database Connections horrors using Sharepoint Designer 1/11/2008 11:19 PM Sanjay
I have the same problem, though I'm able to connect to the database but when I click on Show Data, I get error. I followed Karine's Blog ..but no help.

I'm using the following SQL query:
SELECT P.[ProductID], P.[ProductName], P.[UnitPrice], P.[UnitsInStock] FROM [dbo].[Products] P

any idea why it thorws error?
--Sanjay

# re: The Database Connections horrors using Sharepoint Designer 1/13/2008 11:56 AM Patrick
Sanjay convert it to a store proc and try using it.
If not how are you connecting?Are you using Windows Auth or Sql Server username and password?Use the latter and try it shoulbd be fine

# re: The Database Connections horrors using Sharepoint Designer 6/14/2008 4:19 AM Gabriel
The problem is that only accepts sql server authentication in oledb format like this

Provider=SQLOLEDB;Data Source=LOCAL;Integrated Security=SSPI;Initial Catalog=yourdb

That is the solution
Bye

# re: The Database Connections horrors using Sharepoint Designer 6/15/2008 4:20 PM Patrick
I guess thats what i stated above in my POST:)

# re: The Database Connections horrors using Sharepoint Designer 4/2/2009 4:00 AM Martin
Please, does someone know how to include parameters when you use a sql query?

How do you concat that parameter? I need a query like this:

select p.code, p.descript from dbo.products p where p.status=@the_parameter

I used tha parameter wizard but it got lost.

Thanks a lot for a example.

# re: The Database Connections horrors using Sharepoint Designer 4/7/2009 7:40 AM sam
re: Left by Gabriel on 6/14/2008 4:19 AM

thank you Gabriel the OLE DB option worked for me MOSS/sql2000 SPs. thanks for the tip!!

# re: The Database Connections horrors using Sharepoint Designer 5/7/2009 7:33 AM Dylan Baxter
MOSS/SQL 2008
I ran into the same error when creating a data source on one of our larger tables. Turned out the query was running too long. Adding a where clause helped of course, but I wonder how to prevent timeouts during workflow execution when a user retrieves a large result set?

# re: The Database Connections horrors using Sharepoint Designer 6/3/2009 4:43 AM wwf
non of above work

# re: The Database Connections horrors using Sharepoint Designer 9/15/2009 1:55 PM Vishvesh
HI I have similar problem I am unable to connect to SQL Server 2005 from Microsoft share point designer. After entering Server Name, user name and password, When I click next it gives me following error;

Server Error: An error occurred while retrieving the list of databases from syssrv113/sqlexpress(server name): The data retrieval service encountered an error during connection to the data source. Contact server Administrator for more information.

I have tried to connect by making many different combinations:
I tried connecting to it by keep it closed, keeping it connected. I also have all the admin right but still I am unable to connect.
Please help me with this issue.

Thanks,

VJ

# re: The Database Connections horrors using Sharepoint Designer 11/19/2009 11:26 AM Rob B
VJ please ensure that SQL Server Authentication is enabled on your DB server: http://kbase.gfi.com/showarticle.asp?id=KBID002804


Post A Comment
Title:
Name:
Email:
Comment:
Verification: