HELP! New Guy Trying To Use SQL Server Express With ADP For Reports And Forms
Nov 11, 2007
Please forgive my ignorance here. I'm reading as many of the For Dummy and IDIOT's guides as I can. So far, I must brag and say, I've done fairly well. (For a beginner)
Hopefully, this is an easy problem to fix, or at least to explain where I'm going wrong. This is as stripped out as I can make it:
- Using Access Database Project files to provide forms and reports for Customer Service Reps over a Windows network while using SQL Server 2005 Express as my engine.
- I am using a simple combo box in the Access form to allow CSRs to type in a job number and therefore find and fill in related info to the visible fields.
- This used to work when Jet was my database engine. Now, I receive an error, stating "The expression After Update you entered as the event property setting produced the following error: Error accessing file. Network connection may have been lost.
In asterks it says, The expresion may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. There may have been an error evaluationg the function, event, or macro.
I don't care how I get it done, I just want this simple feature to work again, so my CSRs will quit hounding me.
PLEASE HELP! Keep in mind, I don't know Visual Basic or T-SQL very well.
I have a windows forms application that I would like to put in production on several machines. Each machine will contain it's own data and does not need to be networked.
I can use my local connection right now to access the data, but I would like to change my connection so that it will work when installed on a client's machine.
My Current connection string looks like this:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsMYNAMEMy DocumentsVisual Studio 2005ProjectsInterimApplicationInterimApplicationInterim.mdf";Integrated Security=True;User Instance=True
I believe I could use something like this:
Dim connectionString as string = "Data Source=" & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "/InterimApplication/data/Interim.mdf;Integrated Security=True;User Instance=True"
However, I am not sure. Also, if the client does not install to the default directory, then this may cause the application to not work properly.
Lastly, I am not sure if the client machine needs any additional software on their computer in order for the application to be capable of connecting to the data source.
Any recommendations, ideas, comments, or general help is greatly appreciated.
I seem to be having a strange problem with accessing the SQL Express database under the following configuration.
Install Longhorn Server Beta 2 Add WSS role, setup default site, everything seems to be working fine (using NTLM auth for the site). Content database clearly working fine to the embedded SQL Server Express. Want to switch to Forms Based Authentication with SQL Membership Provider. Follow insructions for it and discover the following issues accessing the database:
aspnet_regsql (running locally on same box) fails and says it can't connect to the database try using the surface area configuration tools to enable remote access/ protocols etc. aspnet_regsql still fails and says it can't connect to the database Install the SQL Management Studio Express - try connecting it to the databse to explore the tables etc. SQL Management Studio Express fails to connect to the database Try switching client config to connect via shared memory rather than over TCP/IP etc. Still fails to connect to database Try install a third party tool called MSSQL Maestro (does some similar things to SQL Management Express) Lo and behold - MSSQL Maestro connects to the database just fine, can explore the tables etc. No problems. Try aspnet_regsql again, try SQL management express again after doing lots of things (try restarts etc. restarts of services). No luck.
So I'm confused. WSS seems to connect to the SQL Express config database fine. MSSQL Maestro connects to the database fine and will allow me to explore it. Using the exact same host and database name (the WSS configured default) aspnet_regsql and SQL Management Studio express both fail with the exact same error.
EDIT: Some significant additional details:
- I have two databases .MICROSOFT#SSEE (which is the WSS default internal database) and .SQLEXPRESS (installed when I installed the rest of SQL Express to get the management tools etc.)
- I can connect to them both by command line with sqlcmd -S.MICROSOFT#SSEE and sqlcmd -S.SQLEXPRESS just fine. They're both running.
- I CAN connect to.SQLEXPRESS from SQL Management Express Studio, but I cannot connect to .MICROSOFT#SSEE
- A look at the error log, shows a line like the following about listening on the named pipe for SQLEXPRESS database, but I don't see a similar entry for the MICROSOFT#SSEE (even thought it's configured to do so in configuration manager...)
"Server local connection provider is ready to accept connection on [ \.pipeSQLLocalSQLEXPRESS ]."
- I've run SQL Management Express as administrator - no change
- The error I get is either error 26, or error 40 when using Shared Memory protocol
- I get the same error and error code from aspnet_regsql
I would like my newly converted to .NET 2.0 Windows Forms application to use an Sql Express database instead of MSDE. Previously, development was done on the default SQL Server instance using the full SQL Server 7.0. When deployed, MSDE was used with a named instance. The connection string in the application settings needed to be modified as part of the deployment. Now I've converted to .NET 2.0 and to Sql Server 2005, and/or Sql Express.
What I'd really like to do now is have a project within my solution that, when built, creates the database by running scripts, and deploys that database to the output directory of the executable.
I want this to run as part of a continuous integration solution that, so that every so often, the entire project is checked out, built (code and data), all is copied to a bin/[debug,release] directory, unit tests run, etc.
Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
FORMS.LDF file is 7613952 KBand the growth is 512MB .
By how much should I set the filegrowth? The users are complaining that the application is freezing on them.
Hi, Simple question really - Can I use Crystal Reports v8.5 to attach to databases in SQL Server Express and generate reports? I have done a lot of searches in these forums and using Google and I believe it is possible but if someone could confirm this it would help a lot - I'm in the specification stage of a project and would like to use SQL Sever Express for it.
I am converting an Access 2003 database into SQL 2005 Express for purposes of evaluating the SQL server environment as a future home for my data. One of the motivating factors in the conversion is the integration of LINQ with VB 2008.
Assuming that there was no conversion tool to migrate Access forms for use with SQL and VB 2008, I converted the Access data table and used the VB 2008 form designer to databind textbox controls to fields in the converted SQL database. Here are some basic questions:
The conversion from Access to SQL apparently did not include the default numeric formatting (currency, percentage, etc.) which was part of the Access data table. Is there a place in the SQL server environment to supply a default data format so that forms and reports referencing the field do not need to be manually formatted for each reference?
After converting the data table and spending 2 hours designing the dataform for the 80+ fields, I inadvertantly changed the table structure and found that the dataform was not happy (oops). I corrected the databindings manually for the few errant field references, but wonder if there is some wizard to do this automatically?
Is there a way to print out the dataform itself? I used the following code snippet in my Access form code-behind and I wonder if there is an equivalent VB 2008 function:
Finally, Access can instantly change from dataform to datasheet presentation screens. Can this be done in VB 2008 with two views simultaneously presenting the same SQL data?
Please excuse my naivete, but in contrast to Access 2003 where program functionality is encapsulated into readily apparent controls, menus, and dialogs, the SQL server environment seems foreign, spartan, and all the words are different. Thanks, -BGood
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
Howdy,Does anyone know of any packages that you can create a form and via aODBC connection save the data to a table ?If it is possible to compile the form as I don't want clients to changethe forms ?Any idea/thoughts would be most welcome ?RegardsAndrew
Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database? To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder. But how do i connect with other databases like SQL Server, MS Access, and so on? Thanks in advance.
Hi all I have a form where user input the data and onclick it has to be saved in to the database and dnt want to write on button click inser into table ................ i Want to write a function where i can take table name and columns data how can this be done please its urgent .........Same i want for update,delete etc? please
Is it possible to create reports with Sql Express edition i.e. like you can with SQL full edition? If not are there any other add on free tools that allow you to do this?
Currently, I have a set of data that is available on a SQL Server. I want to be able to display the information in the front-end (ie. Access form, excel, webpage?), and if the user wants to edit any information they may do so and with a click of a button, submits to the sql server.
Hello,I would like to know if it is possible to create a form in Access2000,which would function like a calendar for 8 operating rooms inhospital, showing which hours are those closed for a specific date.For this intersection I would like to be appeared the name of thedoctor, the name of the patient and the kind of operation.Furthemore I would like this intersection to be marked in the calendarwith a colour, showing that this room is closed for that time.The data for this form are going to be extracted from a table storedin SQL Server 2000.Generally the whole application is stored in SQL Server( storedprocedures, tables, diagrams etc.)1)How this can be done? Is this going to be through VB or not? Can Imake a template in Excel spreadsheet put it in a formand apply code on it?I would like also to make forms(in Access) for user-entry data thatwill use stored procedures of SQL Server with pushing the OK button.The stored procedures would take as arguments the user-entry data fromtheforms.I thought to make a pass-through query which would use a VB functionand would take as arguments the user's valuesand after would pass these values in the stored procedures.2)Can this be done through the pass-through query, that is SqlServer "understands" VB or better Access pass-through query processthe VB commands before be connected with the Sql Server?What do you think of this as an idea?My problem is that I really don't know how to combine VB code withTransact Sql code.Can you give an example or any ideas where I can find the relativeinformation?Thanks you in advance.
After remove the previous SP1 installation of Sql Server Express with Advanced and installed the SP2 I've notice the presence of the [Reports] context menu voice.
I can see some report correctly, but for someone, like "Disk usage by table" i receive a NullReferenceException (System.xml) as following:
in System.Xml.XmlReader.CalcBufferSize(Stream input) in System.Xml.XmlTextReaderImpl.InitStreamInput(Uri baseUri, String baseUriStr, Stream stream, Byte[] bytes, Int32 byteCount, Encoding encoding) in System.Xml.XmlTextReaderImpl..ctor(String url, Stream input, XmlNameTable nt) in System.Xml.XmlDocument.Load(Stream inStream) in Microsoft.SqlServer.Management.Reports.StandardReport.LoadRdlDocument() in Microsoft.SqlServer.Management.Reports.Report.LoadReportQueriesFromRdl() in Microsoft.SqlServer.Management.Reports.StandardReportFactory.GetReport(ReportInfo reportInfo, ReportContext context) in Microsoft.SqlServer.Management.Reports.ReportsManager.GetReport(ReportInfo reportInfo, ReportContext context) in Microsoft.SqlServer.Management.Reports.ReportsManager.ShowReport(ReportInfo reportInfo)
Ok, I'm using vb2005 express and I know that the reportviewer control has to be added and reports can't be designed from the IDE. So, I downloaded the business intelligence studio and I can design a report that looks like just what I want. What I can't seem to do is add that report to the application I'm developing. I don't want to use a report server - I want to use the report with client side processsing. I'm wondering if this is possible and if so how? When I try to add that .rdl file or rename it to a .rdcl file to my project I'm not allowed to do so. Any thoughts?
I have a perfectly working application, accessing a SQL Express database, displaying data and images.
Now I am trying to add a Crystal Report. I add a new connection (OLE DB (ADO)) The connection points to my .MDF database. I drag the fields I want to print to the report, including an image. Now I click the Report Preview. I get a message saying "The database table "Table-Name" can not be found, Proceed to remove this table from the report?" Yes/No. I select NO, and the preview shows up correctly.
Happily I continue to execute the program in debug mode. At startup I get errors saying that login failed. To get around this error, I have to close down Visual Studio and then open it up again, running the program without touching the Crystal Report Design. Now the program starts up normaly without problems.
Now comes the real strange behavior. I click on my link to request the Crystal report and I get a request to login to the database. I try to login but nothing is accepted. I again close down Visual Studio and open up SQL Server Management Studio Express. I connect to SQL Express and expand "Databases" MY .MDF file is listed but the + sign in front of it is missing and I have to delete the database and attach it again to get it back.
Where is this problem occuring????? I have no idea. Please help.
I have a Report Server setup on my laptop (XP Pro SP2). I have developed a report which I would like to view from a Windows Forms Application (VB.NET) using a ReportViewer. When I try it from the local machine it works correctly, but if I try to connect and preview the same report from my other PC (Vista Home Premium) I am getting the "Unable to connect to the remote server. A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because the connected host failed to respond." error.
I have setup the Processing mode to Remote and correctly setup the ReportPath and ReportServerUrl using:
With rptRetEmp .ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote .ServerReport.ReportServerUrl = New Uri("http://192.168.1.2/CPMReports") .ServerReport.ReportPath = "/CPM_Reports/Test" .ServerReport.Refresh() .RefreshReport() End With
Where CPMReports is the Report Server virtual directory and CPM_Reports is the folder where the report Test resides.
I do not know if this has anything to do with the fact that Vista Home does not have Report Services enabled or it is just a permissions problem. I have red several posts that describe similiar problems, but I could not find a solution.
Sorry for the long post but I'll try to give a maximum of information.
I've tried to implement Form Authentication for Reporting Services. Right now, just about everything works as I would hope for, i.e.:
When I try to access http://<server>/ReportServer$SQLSERVER_2005, I am redirected to the authentication form. When successfully authenticated, I can access the report I can display reports throught the ReportViewer Web control by passing the authentication cookie. Etc.
(Note: My implementation of Form Authentication was inspired by http://www.devx.com/dotnet/Article/26759/0/page/1 which seems similar to Microsoft Sample)
(The $SQLSERVER_2005 suffix is because SQL Server 2000 is also installed on the server, but I don't think Reporting Services 2000 is installed)
There is one thing which does not work however and it is the Report Manager.
When I try to access http://<server>/Reports$SQLSERVER_2005, I would expect to be redirected to the /Pages/UILogon.aspx page. Instead, I get an error page with the following message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
Both ReportServer and Reports are on the same server, both have the same security on the folder and in IIS (6.0), both have Anonymous security access "checked".
If I revert to the old .config files (prior to my modifications for Form Authentication) and remove Anonymous from IIS for both sites, everything works fine (like it did after the installation).
Here are excerpts from the config files I've modified in ReportManager:
<CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="FullTrust" Description="This code group grants MyComputer code Execution permission. "> <IMembershipCondition class="ZoneMembershipCondition" version="1" Zone="MyComputer" />
Web.config:
<identity impersonate="false" />
Here is the content of the log ReportServerWebApp_*.log (see error in bold):
<Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.1399.00</Product> <Locale>en-US</Locale> <TimeZone>Eastern Standard Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerWebApp__11_10_2006_08_32_34.log</Path> <SystemName>MSCAPP02</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion> </Header> w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing ProcessRecycleOptions to default value of '0' because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing WatsonFlags to default value of '1064' because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing SecureConnectionLevel to default value of '1' because it was not specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!5!11/10/2006-08:32:35:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False' because it was not specified in Configuration file. w3wp!ui!5!11/10/2006-08:32:39:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer. w3wp!ui!5!11/10/2006-08:32:39:: e ERROR: HTTP status code --> 500 -------Details-------- Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) w3wp!ui!5!11/10/2006-08:32:40:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
So that's it. Thanks for any help you can provide.
I have managed to get the Forms Authentication sample to work bu there is a hitch. I am running Report Server and Report Manager on the same(test) PC. I can logon to the Report Server without a problem but when I try logon from the UILogon.aspx page for Report Manager I receive the following error:
An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) An error occurred while attempting to get the ReportServer Url. The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)An error occured while attempting to get the ReportServer Url. The RPC server is unavailable.(Exception from HRESULT: 0x800706BA).
I can register users which suggests that the page can connect to the database.
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
What is the Difference between the SQL Server Express Bundled with C#/VB VS the downloadable SQL Server Express SP1 with advanced Services?
I installed C# with SQL Server Express, however I wanted to add the Full Text Searching and the SQL Server Management Studio Express, so I downloaded and installed the SQL Server 2005 Express Edition with Advanced Services SP1. When I installed it over top of my current installation, it complained of version mismatching, and then C# failed to recognize that I had SQL 2005 Express installed at all.
What I'd like to know is, which version is more current (they have to be different, they had different version numbers, one was 9.xx.xxxx the other was 2005.9.xx.xxxx) The one bundled with C#, or the SP1 downloadable one.
Firstly, I want to be up todate as far as security patches, and Secondly, how do I add the full text searching and SSMSE to the one bundled with C# without breaking it.
Q1: Does Sql Server 2005 Express support Web/Internet to other SQL Server 2005 Express Clients or does it have to Synch across the internet to a fully installed setup SQL Server 2005 with IIS?
Q2: Does SQL Server 2005 Express support Direct Replication between other SQL Server 2005 Express clients?
I downloaded the SSEUtilSetup.EXE and extracted the SSEUtil.exe to a folder of C:drive of my PC that is Windows XP Pro. I plan to learn the CLR programming via user-instance of SQL Server Express. I need to have SQL Server Express Utility installed. Please help and tell me how I can install SSEUtil.exe in my PC and how I use it to interact with SQL Server Express.
I have what I hope is a simple question for you all.
I have been volunteered to be our SQL admin. We've never used SQL here before. I've been studying it for a month or so now, but have unexpectedly been given a project for SQL 2000 with a deadline of next week. I have no problem with creating the database, the table, the view, and setting permissions. My problem is how best to have clients enter information. We don't want them to see previous days' information, or to see other users' information. They should enter their data only. We have a few managers who will look all at the data and perform queries. Can I give the users' a form for this? I'd rather not have them open enterprise manager and navigate through those screens; this will create a whole slew of training issues.
If someone could point me in the right direction on this, and maybe suggest a good reference manual, I'd appreciate it.
