Logo 
Search:

MOSS Forum

Ask Question   UnAnswered
Home » Forum » MOSS       RSS Feeds

Datasources in Sharepoint Designer

  Asked By: Sampat    Date: Aug 28    Category: MOSS    Views: 1017

I am using WSS3, SQL Server 2005, and Sharepoint Designer 2007 on the
same Windows Server 2003 machine.

I can create a data source that connects to a table (containing 4 rows)
without issue, but when I modify the data source (or create a new one)
to connect to a stored procedure it throws the following message:

"The server returned a non-specific error when trying to get data from
the data source. Check the format and content of your query and try
again. If the problem persists, contact the server administrator."

I have googled far and wide I have not found a working solution. Can
anyone point me in the right direction, please?



I have listed the steps I have taken below. Maybe I have missed something?

1. Launch SQL Server Management Studio
2. Go into Properties for your local server, configure Security to use
"SQL Server and Windows Authentication Mode".
3. Create a new Login named "TestUser" for your local server, using SQL
Server Authentication.
4. Create a new database named "TestDB" on your local server. Add a
table, some columns, and some rows to the DB so there's test data. Also
create the stored procedure for later:

CREATE TABLE Employees(
LastName nvarchar(20)
FirstName nvarchar(10)
Extension nvarchar(10)
Country nvarchar(10)
)

CREATE PROCEDURE StaffSearch (
@LastName nvarchar(20),
@FirstName nvarchar(10)
)
AS
SET @LastName = ISNULL(RTRIM(@LastName) + '%','%');
SET @FirstName = ISNULL(RTRIM(@FirstName) + '%','%');
SELECT LastName, FirstName, Extension, Country
FROM Employees
WHERE LastName LIKE @LastName
AND FirstName LIKE @FirstName
ORDER BY LastName, FirstName

5. Give "TestUser" access to the "TestDB" database.
6. Test your new database and account by disconnecting from the local
server, then reconnecting using the "TestUser" account.
7. Assuming the new account works, quit Studio and launch SharePoint
Designer 2007. Connect to a local SharePoint site.
8. Select "Data Source Library" from the "Task Panes" menu.
9. Under "Database Connections", click "Connect to a database..."
10. Click "Configure Database Connection..."
11. Enter your local SQL Server name, choose Data Provider for SQL
Server, and enter your TestUser info.
12. Click "Next" and "OK" to dismiss the authentication warning.

13. Select your TestDB, then select the table you created. Click
"Finish" then click "OK" to finalize the new Data Source.
14. Drag the datasource onto the web page, and watch as the table
appears complete with data


Now, if we do the following instead:
13. Select to specify custom commands, then select the stored procedure
you created. Click "Ok", then click "OK" to finalize the new Data Source.
14. Drag the datasource onto the web page, and watch as it fails. The
Data Source Details reads "The server returned a non-specific error when
trying to get data from the data source. Check the format and content of
your query and try again. If the problem persists, contact the server
administrator."

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Datasources in Sharepoint Designer Or get search suggestion and latest updates.