SQL Server 2005 Output To Sharepoint
Mar 3, 2008Can SQL Server 2005 query results be output to a list or document library in SharePoint?
View 1 Replies
ADVERTISEMENT
Ms Access Upload To SQL Server 2005 BE && Sharepoint FE
Jun 21, 2006I have about 20 Ms Access Databases to need to be upload onto a SQL Server2005.Currently the are on a network/server residing on a specific drive. The goalis to keep the user face the same (ms access) and put all of the tables ontoa SQL Server 2005 BE, and yet make those databases accessible to open/viewthrough Ms Sharepoint.What is the simple (properties) way to grab all of those db's = 20 and uploadto server?With out having to write code.Pull all db's over to SQL then store the Ms Access databases on Sharepointwhere the user face remains the same and the tables are on the server and thedb's are accessible now through the company Intranet.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedSQL Server 2005 Reporting Services Add-in For SharePoint, Installation
Nov 27, 2006Setup: Windows server 2003 SP1, SQL 2005 SP2, Sharepoint Office server 2007, Report Services addin (CTP v9.00.3027).
Using the Report server configuration tool, setting Report Server Virtual directory, end up with RS Configuration tool crashing.
It appears to be happening on the task "Changing Secure connection level", & returns "System.NullReferenceException was unhandled
Message: Object reference not set to an instance of an object."
any ideas?
Server SQL-Server 2005 Crash While Indexing By The Server SharePoint 2007
Feb 7, 2008Hello,
I have an intranet environment consists of two servers:
- An application server windows 2003 server with 64-bit server Sharepoint 2007 SP1 (Standard version for search)
- A database server windows 2003 server with 64-bit SQL Server 2005 SP2 64-bit (Standard - 9.0.3054)
It happens regularly that the database server crash (frozen black screen) while the application server indexes (crawl) the content of the intranet site based on sharepoint. There is no alert / error in the observer of events, nor in the sql logs.
The crash is uncertain: dice once all goes well, soon after that crash. When i set the parameter search service (service management research), I can define a regulatory impact of the robot to change the number of documents at the same time : "Request 2 documents at the same time, the crash is more rare, "ask 64 documents at a time, the crash is more common.
My Intranet under sharepoint is composed of a collection site with a dozen sub-sites. There are 3 large library of PDF document. All done in the 4 go in terms of volume.
My problem for about 3-4 months. (Maybe more. Before, indexing was not yet in place).
Someone could help me? Thank you
SharePoint Server 2007 Integration With Reporting Service 2005
Jan 18, 2007Hi,
I am working on sharepoint 2007 integration with reporting service 2005. There are few facts which i am trying to understand and need some clarifications. Please provide your comments
1) While configuring "Database setup" in Repoting Service Configuring Manager" i found two modes 1) Native and 2) SharePoint Integration. When creating a report server database if we check "Create the report server database in Sharepoint Integration Mode" then it set the mode to "SharePoint Integration Mode". There is no option of switching the same database to native mode. So i have to create another database in "Native Mode". (IS THIS CORRECT. DO WE NEED TO CREATE TWO REPORT SERVER DATABASE SEPERATE FOR SHAREPOINT INTEGRATION AND NATIVE)
2) I am using the new report viewer web part in sharepoint 2007. When i am in sharepoint integration mode i can create datasource, report model and with report builder i can create rdl file. On selecing any RDL file in doument library and it gets displayed in Report Viewer Web Part. When i switch to Native mode i want to configure the web part to use the report created with SQL Server Bussiness Intelligence Dev Studio and deployed on http://localhost:808/ReportServer. (IS THERE ANY WAY SO THAT I CAN EXPORT AND IMPORT ALL .RDL FILE FROM http://localhost:808/ReportServer to http://localhost/ReportServer)
I AM NOT ABLE TO CONFIGURE WEB PART WHEN I AM IN NATIVE MODE i.e giving path of .RDL in report textbox throw a error saying "Item not found" i tried all combination. THIS can be related to datasource not reading in .RDL file in native mode while confuring report viewer web part.
http://localhost:808/ReportServer (Report Server)
http://localhost/ReportServer (SharePoint Mode) (Default Top Level Site)
3) LAST : I want to create a .RDL file report from the data in sharepoint, say lists or news etc. So when creating a RDL file how can i connect to sharepoint database. I read some where that in previous release they used to DATA EXTENSIONS and get the data from list in sharepoint with something like http://<servername>/<sitename>/List=Announcement when creating RDL file. Is the alternative in new release to create the report based on sharpoint list data.
Thanks and i would really appreciate your help
How To Setup Sharepoint Portal Server 2003 With SQL Sever 2005
Apr 20, 2006Hello, everyone
I try to set up Sharepoint Portal Server 2003 in SQL Server 2005 database (on Windows 2003 Server R2). But It cannot create new portal site... the Error message told about "cannot access instance..." (in fact that I input the correct instance, user, and password)...??? I wanna know that How to setup it...
Setapong
SQL Server 2005 Reporting Services Integration With SharePoint 2007
Jun 30, 2007Hello Everyone,
I have been banging my head against the wall with trying to integrate SharePoint 2007 and SQL Reporting Services. I have been following these 2 websites down to the letter in trying to configure everything:
1. http://msdn2.microsoft.com/en-us/library/bb326356.aspx <---Microsoft's Steps
2. http://blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with- wss-3-0-and-moss-2007.aspx <---SharePoint Blog Group
Basically my setup is as follows:
Server 1 = SharePoint 2007
Server 2 = SQL Server Reporting Services SP2 / SharePoint 2007 services
Server 3 = Database
Server1 is setup and running SharePoint 2007 fine with no problems. It was installed under the complete option with its own server farm. Then with the Server2, SharePoint 2007 is installed again, also under the complete complete option, and configured to join Server1's server farm. In this way, Server2 is able to supply Server1 with services and it has been said that if you are using 2 different servers, one for SharePoint and one for Reporting Services, you need to have an instance of SharePoint on the reporting services box. With a SharePoint "front-end" the reporting-services now has a way to also join Server1s farm.
This is where the headache begins....the SharePoint install is successful on Server2 and the services are started as evident through the Central Administration page. The next task is to install SQL Server 2005 Reporting Services and then the SQL Server 2005 SP2 patch.
After installing SQL Server and the SP2 patch, you go into the SQL Reporting Services Configuration Manager and setup the Report Server, Database, and SharePoint integration. I configure everything to run under the "DefaultAppPool" under IIS, and I get green accepted checkboxes on everything.
Finally, the last step is to install Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies on Server1. This add-in actually allows the connection between SharePoint on Server1 to the Reporting Services on Server2. The install is successful and the extra options for reporting pop up under Central Administration page. When configuring the final connection under the Central Admin I referred to the following Microsoft Page:
http://msdn2.microsoft.com/en-us/library/bb326213.aspx
For the "manage integration settings", I set the report server to Server2 and use Windows Authentication. Then for the "grant database access" I use Server2 and login with my credentials...
PROBLEM: Then for the final step of setting the server defaults, I click on the link and get the following error:
"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. "
I have googled and tried installing/uninstalling several times, but it seems that I keep getting hung up here. When trying to access reports on the ReportServer, the same error message comes up.
If anyone is familiar with integration between SharePoint and SQL Reporting Services, and may have any ideas/comments/advice, I would greatly appreciate it. Thanks in advance! =)
--A4orce84
SQL Server 2005 And SharePoint Portal Server 2003
Nov 3, 2005Hello all,
View 1 Replies View RelatedMigrating Reporting Services 2005 With Integrated Sharepoint 2007 To Another Server
Sep 27, 2007hello good afternoon, I am having this issue, I need to move reporting services from one server to another, but I need that credentials, roles and reports stay the same. But as the reporting services is integrated with sharepoint, dont know if I can move both at the same or it has to be done one at a time. this is just data migration, but still I will like to Know how it can be done having the same structure with the roles and credentials. or it is prefered to do a ghost?? but the issue will be that we have the reporting services in one place and the data in another... donīt know how this will affect. thanks in advanced for the help.
View 1 Replies View RelatedReport Viewer/Explorer-WebPart Missing Under SharePoint V3+SQL Server 2005
Feb 8, 2006Hello all,
I'm new with SP v3 and the Reporting Services of SQL Server 2005.
I did a prototyp report and show it with "SQL Reporting Services
Report"-WebPart, but I can't find the WebParts Report Viewer and
Explorer in the WebPart Gallery.
Please, could somebody give me a clue where the problem could be, or
how I could isolate the problem area.
Cheers
Marc
Sharepoint Server 2007 And SQL Server 2005
Dec 6, 2007Hi all!
I'm VERY new to MOSS2007 and I have couple of questions about it and SQL Server 2005. The basic idea is to receive data from MOSS2007 into SQL Server 2005 in my own database and make some reports from there.
Which are the best practices to receive data from MOSS2007 into SQL Server 2005? Do I have to write some dlls for example with VB.NET (and use Sharepoint API calls) and use those dlls in stored procedures in SQL Server 2005? Or can I just use normal T-SQL (I believe that data in MOSS2007 is saved in lists and lists are saved in DB)?
Waiting for your replies. The more detail your answer is the more grateful I am!
Thanks!
Best Regards,
Harry F.
Sharepoint Server 2007 And SQL Server 2005
Dec 6, 2007Hi all (and sorry for crossposting)!
I'm VERY new to MOSS2007 and I have couple of questions about it and SQL Server 2005. The basic idea is to receive data from MOSS2007 into SQL Server 2005 in my own database and make some reports from there.
Which are the best practices to receive data from MOSS2007 into SQL Server 2005? Do I have to write some dlls for example with VB.NET (and use Sharepoint API calls) and use those dlls in stored procedures in SQL Server 2005? Or can I just use normal T-SQL (I believe that data in MOSS2007 is saved in lists and lists are saved in DB)?
Waiting for your replies. The more detail your answer is the more grateful I am!
Thanks!
Best Regards,
Harry F.
Need To Schedule A Web Service To Truncate And Populate SharePoint Lists (2007 && 2003) From A SQL Server 2005 Table
Jun 24, 2007
I couldn't find any tutorials/articles on integrating a SharePoint web service call with an SSIS package
I need to schedule a web service to truncate and populate SharePoint Lists (2007 & 2003) from a SQL Server 2005 table.
I guess I'm just ignorant about integrating web services with SSIS. How do I make a call to the web service through my SSIS package? (script task?)
Any links to articles/tutorials or pointers would be apreciated.
Reporting Services And Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)
Mar 2, 2007I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:
System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.
Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?
Many thanks to anyone who can help!
Sql Server 2005 Sql Statment To Output Query Results To A File
Jan 21, 2008
I am using vs 2005 and sqlserver 2005 to manage a large database. I need to create a series of text files based on the value of one of the columns. Is there an sql command that will pipe the output directly to a file?
Thanks,
Mike
Sharepoint Integration Microsoft.ReportingServices.SharePoint.UI.WebParts.dll
Feb 2, 2007Does anyone know the location of Microsoft.ReportingServices.SharePoint.UI.WebParts.dll after you install the web part? I am wondering if I can inherit from this web part to wrap some other functionality into it. I know that i see it in the GAC buyt can't seem to find it so I can look at the assembly using reflector.
Also, how easy is it to pass in parameters to the Report Viewer?
Thanks!
Erik
Problems With SharePoint Integration And Reporting On SharePoint With SSRS
Jan 17, 2008Since I wasn't getting any replies with answers or suggestions to my initial posting, I figured I would continue to list the problems and solutions I've encountered while trying to setup and utilize SharePoint integration mode, and read data from SharePoint with SSRS reports (developed in VS2005). Hopefully this is helpful to anyone that is also trying to learn all this stuff and figure it out.
(Single server - W2K3R2_SP2 as: DC / SQL2005 Standard / MOSS2007 Enterprise - see the bottom of this posting for the steps I took to set that up)
Note: After I get this to work, I intend to post my experiences for setting up SharePoint Integration with SQL and SP on separate boxes.
1. In the new reporting section under SharePoint application management in the Central Admin Console, the Grant database AND Set Server Defaults work when using the domain admin account, but not when using the SQLSPS service account I used to setup both SQL and SharePoint. Why can't I use the service account? Is the domain admin account now going to be used as a service account for accessing the sharepoint database?
This question remains unanswered as of yet.
2. Reporting Services configuration tool shows Sharepoint integration as red x when I create a new database in integration mode. (the service account SQLSPS and the Server object are members of the group WSS_WPG). If I switch back to native mode (the original reportserver database), the red x goes to a blue exclamation instead. Using Service Credentials or Windows credentials (as either the service account SQLSPS or as domain admin) doesn't change anything.
RESOLVED by using a domain user account for the ReportServer WebApp. Even though everything is installed on the same machine, it seems using the default of NETWORK SERVICE wasn't acceptable. Possibly because SharePoint is configured to allow additional machines in the farm?
3. http://servername:8080/reportserver will display Report Server information as it did before the integration on port 80, but http://servername/reportserver doesn't display anything through SharePoint.
Resolved. I needed to define a repository before I could view it (go figure). When I created a sharepoint site based on a report center template, created a data connection library and report library, and then referenced that by http://servername/sites/myReportCenterSite/myReportLibrary, I was able to see the equivalent of http://servername/reports (which is now disabled due to integration mode).
4. Unable to deploy to new report center sharepoint site I mention above using Visual Studio. I kept getting "A connection could not be made to the report server http://servername/sites/ReportCenter. Server was unable to process request. The request failed with HTTP status 503: Service unavailable (System.Web.Services). In Visual Studio, the project properties were: TargetDataSourceFolder = http://servername/sites/myReportCenterSite/myDataConnectionLibrary, TargetReportFolder = http://servername/sites/myReportCenterSite/myReportLibrary, TargetServerURL = http://servername/sites/myReportCenterSite/
Found these errors in the event logs on the server.
Event Type: Warning
Event Source: W3SVC
Event ID: 1021
Description:
The identity of application pool, 'ReportServer' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.
Event Type: Warning
Event Source: W3SVC
Event ID: 1057
Description:
The identity of application pool 'ReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
Event Type: Error
Event Source: W3SVC
Event ID: 1059
Description:
A failure was encountered while launching the process serving application pool 'ReportServer'. The application pool has been disabled.
RESOLVED the issue by changing the Application Pool used by reporting services from ReportServer to DefaultAppPool and changing the account DefaultAppPool used from NETWORK SERVICE to my service account SQLSPS. I have no idea why ReportServer is invalid, it was set to use the service account SQLSPS the same as DefaultAppPool is. Perhaps the reporting services add-on makes a change to invalidate this?
5. Attempting to read a SharePoint list using a SSRS report. This should be fun.
First, created a new custom list in the ReportCenter SharePoint sites I've been using and called it myCustomList. I added one item with a title of myCustomListItem1. I don't know if I need to do more than that for a simple test of reporting or not yet.
Second, configured new shared data source. Name = myCustomList1DataSource, type=XML, Connection String = http://servername/_vti_bin/lists.asmx (this URL is viewable in IE), Using Windows authenticaion.
Third, in VS2005 on a workstation in the domain, create new report item (report1.rdl). New dataset for report; Name=Report1DataSet, Data Source=myCustomList1DataSource (Shared), Command type = text, Query String = [See Queries and associated errors below]:
Initial query obtained from: Connecting SQL Reporting Services to a SharePoint List (David Wise)
http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx
Note: Queries when executing the dataset (which errors out) and when executing a query in CAML Viewer for the same information (which does work just fine) both initially return 401.1 and 401.2 errors (as viewed in Fiddler). Since the CAML query works, I have not tracked this down yet.
A. Original format from posting
<Query>
<Method Namespace=http://schemas.microsoft.com/sharepoint/soap/ Name="GetListItems"/>
<SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
Parameter; Name => listName, Value => =myCustomList
Was NOT prompted to define the query parameters
- Gets this error: "An error occurred while setting the command text property of the data extension command. The XmlDP query is invalid. (Microsoft.ReportingServices.DataExtensions)."
- Advanced information from the error => 'http' is an unexpected token. The expected token is '"' or '''. Line 2, position 19. (System.Xml)
B. Changed: Placed the namespace URL in quotes (also tried with and without / in namespace after /soap)
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <faultstring>Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListItems/.</faultstring>
C. Try the query string that seems to work for Stramit CAML Viewer to read myCustomList
Stramit CAML query:
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
</Query>
Also tried
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
</Query>
And tried entering no query...
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <soap:Fault><soap:Code><soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:lang="en">Server was unable to process request. ---> Data at the root level is invalid. Line 1, position 1.</soap:Text></soap:Reason><soap: Detail /></soap:Fault>
RESOLVED. Went back to the query used in B above, and mysteriously it works now. This could be the "funny things happen" symptom I've been reading about. We'll see if it continues to function.
Here's what I've learned so far on Issue #5:
1. XML is confusing. XmlDP is even more confusing...whatever that is. Apparently it's new to/with Reporting Services in SharePoint integration mode?
2. There is far too little documentation regarding XmlDP and querying SharePoint lists from SQL Reporting Services.
3. According to http://msdn2.microsoft.com/en-us/library/ms159741.aspx: You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box.
Tried this query:
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems" />
<Parameters>
<Parameter Name="listName">
<DefaultValue>myCustomReport2</DefaultValue>
</Parameter>
<OrderBy>
<FieldRef name="ows_Date" />
</OrderBy>
</Parameters>
</Query>
I was still prompted for the listName and the list returned with ows_Date still out of order. So maybe that proves this statement is true?
<translation: why make it convenient when it's already [somewhat] functional?>
4. When building the dataset, anything I place in the "Query string" box has to be enclosed in <query></query> tags or it errors out. I am trying to understand how to translate the CAML viewer query (which does work but isn't wrapped in query tags) into something that fits between query tags, and it hasn't gone well so far.
<translation: To be at one with your reports Grasshopper, you must understand xml>
5. According to http://srv1/sites/ReportCenter/_vti_bin/lists.asmx, "GetListItems" operation is supported
<translation: you're getting warmer...>
6. According to the soap output of my site http://srv1/sites/ReportCenter/_vti_bin/lists.asmx?op=GetListItems, I am only allowed to specify the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions and webID.
<translation: that and $3.00 will get you a cup of coffee>
7. According to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) (query and queryOptions may not be working properly with SSRS).
<translation: that kind of sucks>
8. When this started mysteriously working again, I tested using the "/" at the end of the namespace (wouldn't work without) and the "=" sign in front of the parameter value as defined on the parameter tab of the dataset (turns out to be optional).
As long as I can query SharePoint and get my list information back, I'm going to consider this issue closed and move on to actually using this information in a report. I'm sure that won't work right either, but why stop now?!
6. Ok, now that I can read SharePoint list data with SSRS, I need to try and actually do something with it.
A. Begin by setting up new data to report on
1. Create a new custom list (myCustomList2) and add a test record. This list will simulate the data we intend to submit to SharePoint using InfoPath, so it has a number of custom/created columns in it
2. Modify the working information from #5 above; add a new shared data source (myCustomList2DataSource), tell the dataset to use the new shared data source, change the information set in the parameter tab (Name => listName, Value => =myCustomList2) and test the dataset. Lucky me, it actually returned the record I entered on the first try.
B. Begin to build the report with the dataset returned
1. So, as I'm feeling good that it's actually reading the list in Step A without me threatening to destroy the machine, I look to the left in the Dataset results navigation pane in VS2005 and realize there are NO fields for me to choose to report on.
2. At this point, I have no clue what to do about getting this XML data into a report. Not that I have a large clue about SSRS otherwise, but I have at least been able to build SSRS reports when using a SQL database as the datasource. Fyi, http://msdn2.microsoft.com/en-us/library/ms159741.aspx states: "Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database.", in case anyone is trying to do that....
RESOLVED. Since the fields were not appearing automatically, I had started to add new fields to the dataset manually (if it doesn't work, beat it with a hammer, right?). I entered half a dozen or so fields, each one with the exact name as displayed in the query results, and then decided it would be fun to hit the refresh fields icon. As soon as I did that, all the fields shown in the query immediately appeared in the dataset and I was able to add them to a report. I don't know if I hit the refresh fields when working in issue #5, so I can't say if they are there now because of that, or because of adding some fields manually first, a combination of the two, or if it's just one of those mystery items I'm reading about with sharepoint reporting.
7. Rows are being returned even though they have NULL values (so far anyway), and most data is returning nicely for the bulk of the columns I created in the list I'm reporting on. Here are the current issues:
A. I have one column in my SharePoint list that is multi-lined text. If the text type is either rich text or enhanced rich text, the value returned in the query is formatted like this: "<div class=ExternalClassyadayada<div>My notes display here". If I set the column to be plain text, this doesn't happen. I will need to figure out how to filter that for my report.
RESOLVED. David Wise had some code listed at http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx that addressed this issue somewhat. I modified it to match my circumstances. Disclaimer: I'm not a programmer, so use this at your own risk.
function ExtractMultiLineText(myField as string) as string
dim strBegin as string
dim strEnd as string
dim myBegin as integer
dim myEnd as integer
dim myTextLength as integer
strBegin = "<div>"
strEnd = "</div>"
if myField = nothing then return ""
if myField = "" then return ""
myBegin = Instr(myField, strBegin) + Len(strBegin)
myEnd = Instr(myField, strEnd)
myTextLength = myEnd - myBegin
if myBegin < 1 then return myField
return mid(myField, myBegin, myTextLength)
end function
B. I have two columns in the SharePoint list that are set as number values with 0 decimal places. When I query those however in SSRS, they return with the number plus like 12 zeroes behind the decimal. Not a major issue, but I'll put it on the list anyway.
RESOLVED. Used the Int() function; =Int(Fields!ows_myProblemNumber)
C. If I go to "/_vti_bin/lists.asmx?op=GetListItems", it appears from the sample soap output that I am allowed to ask GetListItems for is: listName, viewName, query, viewFields, rowLimit, queryOptions and webID. As I mentioned in issue #5, according to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) and query and queryOptions may not be working properly with SSRS.
So, if I can't put anything other than this in my query string;
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
and I only have a couple parameters to pass on top of that, then it appears to me that my options for filtering the amount of data returned from the SharePoint list is to figure out a way to do it with expressions in the parameters or in the report itself. Am I missing something? Is this really all I get to work with out-of-the-box?
More to come...
==================================
Installation steps taken to build the server VM:
==================================
(Using VirtualPC 2007)
1. Build OS (2003R2SP2) and promote to DC
1a. Create domain user service account SQLSPS
2. Prep for SQL
2a. Install ASP.NET, IIS (www) and .NET 2.0
3. Install SQL/SSRS 2005 Std
3a. Verify SSRS works
3b. RS config tool shows SP integration as blue exclamation
3c. Use service account SQLSPS for anything requiring Windows credentials
4. Install SQL2005 SP2
4a. Verify SSRS works
4b. RS config tool shows SP integration as blue exclamation
5. Prep for SharePoint
5a. Install .NET 3.0
6. Install MOSS2007 Enterprise and configure
6a. Webapps, SSP and anything else use svc acct SQLSPS
6b. Move Default website to port 8080 and re-activate (SharePoint stopped the site)
6c. Verify SSRS works on 8080
7. Install Reporting Services Add-in
8. Create new RS database in integration mode (with service credentials)
8a. RS config tool shows SP integration as red x
9. SP central admin /application management / manage integration settings
9a. reports server = http://srv1:8080/reportserver (since default port has been moved to 8080)
10. SP central admin /application management/grant database access
10a. using the FQDN, confirm servername and default instance - click OK
10b. Prompted for an account to use to retrieve information; svc acct SQLSPS did not work, domain admin did though
11. SP central admin / application management/set server defaults
11a. Accepted all defaults
12. Verify reportserver answers at http://srv1:8080/reportserver (via reportserver AppPool and DefaultAppPool)
13. Changed the application pool identity for the ReportServer AppPool from Network Service to the domain user service account I used for all SQL and SharePoint configurations (SQLSPS) and rebooted. The red x for sharepoint integration in the RS configuration tool is now a green checkmark.
Sharepoint And SQL Express 2005
Jan 9, 2008I found out the battery in my UPS isn't working anymore. My server down a couple of times with power outages before I figured out it wasn't doing its job.
I make backups of my Sharepoint databases using the gui interface in Sharepoint, so I have backups and have used them for a couple of the outages.
What I was wondering (I'm a newb to SQL), if I do a checkdb and get no errors when the databases (sharepoint_config and sharepoint_data) are marked suspect, is there an easy way to get them back online without using the backups. The only reason I ask, there is data entered in the 23hrs (daily backups) since the last backup that I wouldn't mind keeping.
Also, I seem to have gotten (from reading some of the posts here) my sharepoint_config stuck in emergency mode and can't find a post on how to get it back to normal/online mode ;)
-Sony
SQL Server 2005 JDBC Driver Output Parameter/Result Set Issue
Mar 21, 2006I'm having an issue with the JDBC driver when I execute a stored procedure that both has a return value and also returns a result set. If I attempt to retrieve the return value (registered as an output parameter) after I execute the stored procedure, then any subsequent attempts to retrieve the result set always return null. Is this by design? If I use the result set first and then later get the return value that works; however, in my situation I need to first check the return value before I work on the result set. Am I'm I doing something wrong?
Code:
CallableStatement cs = connection.prepareCall("{? = call spGetCustomer(?, ?) }");
cs.registerOutputParameter(1, Types.INTEGER);
cs.setString(2,"blahblahblah");
cs.setBoolean(3,false);
cs.execute();
int retVal = cs.getInt(1);
ResultSet rs = cs.getResultSet(); // Always returns null, even though the SP actually returns a result set.
Filtering Sharepoint Data In Sql 2005
Mar 18, 2008We are pulling data from a sharepoint list and we have created a report using business development studio in sql 2005 and have created a table with data that we want to display.
We are trying to filter this data so that we can get the report to show entries for the day or for a week, but im not sure how to achieve this?
Thanks in advance.
RS 2005 SP2 In Sharepoint Integrated Mode
Mar 6, 2007Up until an hour ago, I was using the Sql Server Reporting Services Report Viewer webpart in Sharepoint with forms authentication, and was not having an issue. The only thing that I did (or remember doing) before things went sour was upload a new report to the document list and since then whenever I try to access a report, manage the data source on a report, change the report URL in the webpart, I get the following message:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=%2f_vti_bin%2fReportServer%2fReportService2006.asmx">here</a>.</h2>
</body></html>
--.
I found some related posts and have tried the following, but none have helped:
Removed all reports and reuploaded only the one that was definitely working.
I changed the application pool for the report server to be the same app pool as Sharepoint (runs under domain admin account).
I changed the application pool for the report server back to it's original (which the Reporting Services Configuration prefers - was running under Network Service acct before the problem started, now running under domain admin acct)
I can browse to http://server/reportserver.
I'm running under forms authentication, which had been working.
I can access the three configs for Reporting Services under Application Management in Central Admin - I've tried changing the settings.
I can access http://server/_vti_bin/reportserver/reportservice2006.asmx as well as http://server/_vti_bin/reportserver/reportserviceauthentication.asmx (that was suggested in one post)
I've restarted IIS numerous times, and also finally rebooted the server.
I've created a new document list and set it for report content, just like the other list.
I also just noticed that even though I've set up my admin account as a site collection administrator, I now no longer see my report options under the New button in the document library (both the old and new). I don't know if this is related.
I'm sure that this is a case of something minor that I did that I forgot about. Any suggestions or other troubleshooting that I can do?
Thanks!
daw
SQL 2005 Express And Sharepoint Services 2.0 SP2
May 31, 2006
If I upgrade my sharepoint Database from MSDE that comes with sharepoint, to SQL 2005 Express, will I lose my no limit on the Database size?
Normally MSDE has limit of 2GB but I guess it doesn't when installed with Sharepoint services. So if I upgrade to 2005 express, will I then get a 4 GB limit on size?
Thanks for your help
SQL 2005 Mirroring With Sharepoint 2007
Dec 7, 2006Hi, iam from Mexico,
somebody has installed SQL 2005 with mirroring and sharepoint 2007?
regards.
thanks
SSRS 2005 && Sharepoint Portal
Apr 30, 2006how to integrate reporting services 2005 with Sharepoint portal
View 3 Replies View RelatedViewing Sql 2005 Reports Externally Through Sharepoint
Mar 25, 2008We have sql 2005 setup pulling sharepoint list data and we have created a report from this data. We can view this report through sharepoint internally however when we try to view the report externally we cant view it?
Has anyone else had the same problem?
Any help would be much appreciated, thank you.
SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.
Oct 26, 2006I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
Sharepoint Upgrade Fails During First Time Sql 2005 Workgroup Ed. Setup
Dec 21, 2007I have installed SBS 2003 Premium with Exchange 2003 SP1 and now I want to install SQL server 2005 workgroup edition.
Heres what I noticed during setup, following the steps of the Server 2005 Workgroup Edition instructions, starting with Upgrading the Sharepoint instance of WMSDE.
Preparing the http://companyweb website:
Step 4. I should make a backup of the data directory within program filesMicrosoft SQL ServerMSSQL$SHAREPOINT directory. However there is no data-directory there, so nothing to backup there. This is probably the reason for all the rest of the issues, however what does it mean, why is there no such directory present? I checked before doing the sql install that by going to http://companyweb I did indeed land on the sharepoint internal website.
Rest goes all normal, until :
Attaching the databasefiles to the new SHAREPOINT instance of SQL server 2005.
Step 4.: go to MSSQL$SHAREPOINTdata. Not only is there no such subdirectory now, the whole MSSQL$SHAREPOINT directory has in fact now disappeared. So steps 4 till 6 cannot be performed now.
Then further to
Enable full text search in Windows Sharepoint services
Step 2. Remark: I get the error message as described there, however the solution would be to start the SQL server (Shareoint service. However that service is up and running
Furthermore when I go to http://companyweb now, I also get the errormessage indicating no connection can be established with the configuration database.
When I check for services, I find that the whole MSSQL$SHAREPOINT service has disappeared from the list of services.
Reporting Services 2005 In SharePoint Integrated Mode With Forms Based Authentication
Feb 6, 2008Well, I have SQL Server 2005 SP2 Reporting Services installed and configured in sharepoint integrated mode, so I am deploying reports in sharepoint libraries. Also I configured sharepoint to use forms authentication with builtin AspNetSqlMembership provider, but after this action I couldnot open reports through sharepoint library, instead of this, error was displayed mentioned below. I searched and found hotfix Cumulative Update 3 build 3186 for this issue, request it from microsoft support and applied it, but nothing has changed!
Error I have been experiencing is following:
An unexpected error occurred while connecting to the report server.
Verify that the report server is available and configured for
SharePoint integrated mode. --> The request failed with the error
message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=
%2f_vti_bin
%2fReportServer%2fReportService2006.asmx">here</a>.</h2>
</body></html>
I continued to dig and find out that this hotfix should update Microsoft.ReportingServices.SharePoint.UI.WebParts.dll from version 9.0.3042.0 to new version 9.0.3180.0, but when I checked file version after applying hotfix, it remained the same as was before i.e. 9.0.3042.0. Apparently this cumulative update does not fix this issue.
anyone has any idea how to solve it? Thanks in advance.
sorry for my poor english :-)
Sharepoint SQL Server Databases
Apr 30, 2007Hi:
I have several sharepoint sql server databases that have a double backslash in their
path name which is causing the backup exec software to fail. The log file path is:
C:logs\DBName.ldf
Whats the best way to fix this? Can I detach and reattach the database or run some alter command to correct this?
Thanks
SharePoint Set Server Defaults
Feb 22, 2007Hi guys,
I have a problem when configuring SharePoint and RS on two boxes. (If install everything in one box is fine)
I have SQL SP2 + RS in one server and SharePoint on the other server. I've followed every step in the SQL 2005 Online Book to configure the RS and SharePoint including:
How to: Install the Windows SharePoint Services Object Model on a Report Server Computer. (Done)
How to: Configure Service Accounts (Reporting Services Configuration). (Done)
However, I got an error when click "Set server defaults" in SharePoint Central Admin:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.
In ReadMe of SQL SP2, see below: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm#_rsshare_known_problems_7
---
6.4 Service account requirements for Reporting Services
Restrictions on using built-in accounts apply to some deployment topologies of Reporting Services that include a report server running in SharePoint integrated mode. The following combination of factors will result in service account requirements:
The report server is integrated with a SharePoint farm comprising more than one computer.
The report server and SharePoint Central Administration Web site run on separate computers.
In this scenario, if either the Report Server Web service or Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration will not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site will result in the following error:
"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'."
To avoid this error, choose one of the following approaches:
On the computer that hosts the report server, continue to run the Report Server Web service as NetworkService and add the built-in account, such as NT_AUTHORITYNetworkService to the WSS_WPG Windows group.
Configure the service accounts to run under a domain user account as follows:
Start the Reporting Services Configuration tool and connect to the report server.
Click Windows Service Identity, click Windows Account, type a domain user account, and click Apply.
Click Web Service Identity, for Report Server, click New, type an application pool name, click Windows Account, type a domain user account, and click Apply.
Reset IIS.
Restart the Windows service.
---
I did try it out but still get the error when clicking "Set server defaults" link in SharePoint Central Admin.
Is there something wrong with the two boxes installation???
Hope the MS guys can help.
Paul
SQL Server Express And Sharepoint SQL Version
Oct 10, 2006I created an ASP application using Web Developer Express and SQL Server Express on my workstation. Everything works great. I copied the application and all files to my intranet server which is a 2003 server running Sharepoint. When I attempt to run the application from the server.Access to the ADO.net Managed Provider 'SqlClientFactory' was denied in the data source with ID 'SqlDataSource' because of security settingsIs there something in the web config that I must change or settings on the server that must be changed for the application to run?
View 2 Replies View RelatedSQL Server Certified For Vista And MS SharePoint
Oct 29, 2007Hello,
I have (2) questions about SQL versions and (1) question about "Standard Log Sizing" for SharePoint directory. I am pretty sure about SQL 2000 versions "Not being Certified".
1) Which versions of MS SQL Server 2000 and 2005 are certified to run on Vista?
2) Which versions of MS SQL Server 2000 and 2005 are certified to run on Internet Explorer 7 (IE7)?
3) What should the "Standard Log Sizing" be for MS SharePoint Server drive be? Or is there an actual standard?
Thank you.
SQL Server Compatibility With SharePoint Workflows
Dec 7, 2007I am a beginner with SQL Server and SharePoint. I am trying to migratethe database to other servers that may be running differentplatforms. I know that the other servers have SharePoint so they arerunning SQL Server 2005, SQL Server 2003, and Access. Is there a way Icould get data from my system (with SQL Server 2005) to another system(that may be running 2005, 2003, or Access) without affecting my dataor SharePoint workflow? I want the people running the other servers tobe able to access the same data and see the same workflow I do. Thanksin advance!
View 1 Replies View Related