Ms Access Upload To SQL Server 2005 BE && Sharepoint FE
Jun 21, 2006
I have about 20 Ms Access Databases to need to be upload onto a SQL Server
2005.
Currently the are on a network/server residing on a specific drive. The goal
is to keep the user face the same (ms access) and put all of the tables onto
a SQL Server 2005 BE, and yet make those databases accessible to open/view
through Ms Sharepoint.
What is the simple (properties) way to grab all of those db's = 20 and upload
to server?
With out having to write code.
Pull all db's over to SQL then store the Ms Access databases on Sharepoint
where the user face remains the same and the tables are on the server and the
db's are accessible now through the company Intranet.
--
Message posted via http://www.sqlmonster.com
View 2 Replies
ADVERTISEMENT
Apr 5, 2007
I want to upload data from ACCESS database( contains more than 60 tables) to SQL server 2005 database( same table structure).
If I use the method of copying table by table and Recordset to Recordset, It takes TOO long ( like 8 hrs).
Could you pls suggest me the efficient way of doing this task. I heard something like DTS for this.But I never
used this.. help me
Thanks
Goldie
View 1 Replies
View Related
Mar 2, 2007
I 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!
View 22 Replies
View Related
Jun 30, 2007
Dear SirI am using SQL Server express edition for my database.i have downloaded sql server mangement studio also .i have my data in access(.mdb) i want to import those data to my sqlserver express .One option i am using using odbc datasource but it doesnot take care for my constraints applied i.e primarykey,indexing no duplicate etc, .Any way if can do it again also in sql server express but the problem is when i try to do it with 2 lacs of data it shows error "time expires " ..Can any body tell how to upload data to an existing table of sql server .since i could not find DTS in express edition.Thanks and regardsmukesh
View 2 Replies
View Related
Dec 13, 2006
My hosting gives me ASP.NET 2.0 and a shared SQL Server 2005 package.
I have Visual Studio 2005 on my machine - which installs SQL Server 2005 Express. I have also installed SQL Server 2005 Management Studio Express.
I wish to develop my database locally (mdf file) as I won't always have internet access (and therefore can't always work directly with the remote shared database).
My question is, how do I upload my database info from my local machine to my remote shared database?
Also I shall wish to download my remote database to my local machine in order to work on the database (and then be able to re-upload it) sometimes.
Please can somebody tell me how I can do this?
Will SQL Server 2005 Management Studio Express permit me to do this and if so how?
If I need the full version of SQL Server 2005 Management Studio then I do have SQL Server 2005 and this install with it (but it all looks a bit complicated to install for me).
Many thanks for all help given,
Graham
View 1 Replies
View Related
Jan 28, 2008
Would that be compatible or do I need SQL Server 2008 to display the report?
Thanks,
Steven
View 5 Replies
View Related
Mar 3, 2008
Can SQL Server 2005 query results be output to a list or document library in SharePoint?
View 1 Replies
View Related
Jun 9, 2008
Every month, a huge data need to upload from a Access table to a SQL server table.
If I use odbc to do it will cause a heavy network traffic.
What is the best way to do it?
View 4 Replies
View Related
Nov 27, 2006
Setup: 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?
View 6 Replies
View Related
Feb 7, 2008
Hello,
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
View 2 Replies
View Related
Jan 18, 2007
Hi,
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
View 3 Replies
View Related
Apr 20, 2006
Hello, 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
View 1 Replies
View Related
Jun 30, 2007
Hello 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
View 4 Replies
View Related
Sep 27, 2007
hello 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 Related
Feb 8, 2006
Hello 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
View 13 Replies
View Related
Dec 6, 2007
Hi 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.
View 1 Replies
View Related
Dec 6, 2007
Hi 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.
View 1 Replies
View Related
Jun 10, 2007
Hi guys. Im having alot of trouble with this. I have created a simple access database, uploaded to a doc library on a sharepoint site. Then I made a infopath form based on this by using the url: //207.245.47.37/sites/Safety_and_Training/ai/db2.mdb
I succeeded in creating the form. I then published it onto the sharepoint site. Now, when I try to fill out the form so the database can be filled in, I hit submit and it opens up a ADO warning telling me that the website is using my identity to connect to the datasource, I hit ok and then I get another window saying "Please enter MS JET OLE DB Initialization." In the fields it has 1) Datasource is \207.245.47.37sitesSafety_and_Trainingaidb2.mdb, the 2)username is Admin, 3)password is blank, 4)provider string is blank and the 5)open mode is on DB_Mode_Readwrite.
1. One thing I tried is in the username and password I give my administrator login as CorpdynatecRGill, and then the password I always use to login onto the site. It then gives me:
InfoPath cannot connect to the data source.
Not a valid account name or password.
2. Next thing I try is just leaving the username as admin and password blank but then I get the message:
InfoPath cannot connect to the data source.
The Microsoft Jet database engine cannot open the file '\207.245.47.37sitesSafety_and_Trainingaidb2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
I think it might have something to do with the permissions, but nothing has been working yet. Please help me.
View 2 Replies
View Related
Jul 25, 2007
hi,
I am not sure whether this is the place for this post.
I am developing a Vb.net search functionality which is accessing data from the SQL SERVER sharepoint portal database.
I have troubles in connecting to the particular server and retrieving each table from the database.I have succes in importing tables into Access.
I want to query the portal sql database itself and need to provide a connection string which will made the access easier.
Any suggestion or help to do would be much apprecited.
thanks in Advance.
Barti
View 6 Replies
View Related
Sep 11, 2007
I am trying to run a Reporting services report and pass a parameter using url access and SP Integrated mode.
I have managed to come up with the following: http://DashBrdRptSite/reportserver?http://npvmdashbrd1:2764/Reports/dashbrdrptstore/real%20time%20running/test.rdl&rs:Command=Render&Year=2006&Year=2007&Month=1
This works perfect as long as I run it from the actual server that has Sharepoint and reporting services installed.
If I try to run it from my desktop, I get prompted for a user name and password. And now matter how many times I enter my user/pass... it does not work.
If I just run the sharepoint link that appears on the report when I run it from within sharepoint... such as
http://npvmdashbrd1:2764/Reports/DashBrdRptStore/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=%2fReports%2fDashBrdRptStore%2fReal+Time+Running%2ftest.rdl&Source=http%3a%2f%2fnpvmdashbrd1%3a2764%2fReports%2fDashBrdRptStore%2fReal%2520Time%2520Running%2fForms%2fAllItems.aspx&DefaultItemOpen=0
That runs no problem no matter where I run it from (my desktop or the Sharepoint Server), but it looks like I cannot pass it parameters. I guess becuase it is useing RSViewerPage.aspx.
Can someone please help me out?
Thanks
View 5 Replies
View Related
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.
View 3 Replies
View Related
Jun 29, 2007
I want to create some links in Sharepoint that will take me directly to a report, display it within the SharePoint full page report viewer and set some parameter values along the way.
I've tried:
http://<sharepoint server>/<portal name>/<report library>/<report name>.rdl?Param=value
No luck.
Anyone have suggestions? Workarounds?
View 4 Replies
View Related
Oct 20, 2006
I am rewritting our DTS that upload tables in FoxPro to SQL Server using SSIS. I am finding the that SSIS is way slower. My uploads using DTS take just 7 minutes where doing the same thing in SSIS is taking 1 hour 15 minutes.
View 2 Replies
View Related
Jun 5, 2008
Hi all, I am using ixwebhosting.com for a host and they say they are using sql 2005 servers, well any ways I have sql 2005 express edition, and sql 2000, I am able to export data from mysql2005 express using my 2000, but am having issues tryig to upoad the data to the 2005 sql server on the web host company. Any ideas on how to fix this or a workaround would be nice.Thanks All
Jonny
View 6 Replies
View Related
Apr 21, 2008
I use to have Enterprise Manager and had no problem uploading new table.
But I downloaded 2005 SQL Server Management Studio Express adn I can't figure out how to upload...
How do you upload new tables to my database using 2005 SQL?
Thanks
View 1 Replies
View Related
Feb 15, 2007
Hi, how are you! I'm a beginner of database. I have some really "novice" questions here. Currently I'm trying to upload my database to a share windows host server. I have problems uploading my database files. I'm using Visual Studio 2005 and SQL 2005 express version. I was told that in order to upload my database, I need to have.bak file of my database.
The problems are:
1. under my visual studio server explorer, it seems I have two databases, one is ASPNETDB.MDF, another one is (mycomputername)sqlexpress.(databasename).dbo. I dont know from which one I can create the .bak file, and how?
2. I can not find where the phsyical location of my files (such as tables or stored procedures) from my (mycomputername)sqlexpress.(databasename).dbo database. Anyone can help? thank you!
View 3 Replies
View Related
Dec 18, 2006
I am trying to connect through ODBC connectivity, but it will not allow me to do so. I have investigated this matter. It leads me back to the server, because as I was configuring my client side database. It kept asking for the DSN(datasource name), but I was unable to choose one because there wasn't one to choose. Which is my current dilemma, How can I do this and have it available to choose from the server to satisfy the Access database?
I went to the domain where the software resides but I don't know what steps to take? I also found an interesting piece on microsoft about Kerberos, but I can't follow along according to the instructions it has. I have Access 2003 & SQL SERVER 2005, HELP...!
Basically, this is right off the heels of the install. I setup the server without the connectivity, but it is running the current configuration.
View 7 Replies
View Related
Feb 2, 2007
Does 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
View 6 Replies
View Related
Jan 17, 2008
Since 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.
View 7 Replies
View Related
Jan 9, 2008
I 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
View 1 Replies
View Related
Mar 18, 2008
We 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.
View 12 Replies
View Related
Mar 6, 2007
Up 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
View 9 Replies
View Related
