So I have been tasked with converting a pilot timesheet application (written in Access) into an all singing all dancing web app. I'm planning on using SQL Server to provide some scale but wondered if anyone had any advice about processing time calcs.
I can of course use the smalldatetime datatype to store all the start at, finish at and elapsed hours times but wonderd if there was any comparative advantage to using numerics instead and converting to/from HH:MM representation.
Maybe I need to finally figure out how to create and use custom data types?
There's lots of math to do on these things once the data is captured for reporting and such and smalldattime looks like a fairly big waste of space for something that really only deals in hours.
Anyone doen things like this before? Any advice on what not to do?
I am working on two versions of an application, one of which will be awindows forms application (which will need to be redistributable) andthe other will be a web application.I have MS Visual Studio 2005 (along with the developer's edition of MSSQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun'sapplication server, Tomcat and Apache web server. I am working onWindows XP Pro, and have installed the .NET 3 SDK and all relevantrelated products I could find (e.g. 2 extensions packages for VisualStudio).I have one MS Access database, to which my users should have read onlyaccess. I have, and have used, a tool for importing MS Accessdatabases into MySQL. I expect that SQL Server has a similar utilityhidden somewhere (where I haven't yet looked, though I HAVE beenlooking - obviously in the wrong places). I have located a similarutility for importing MS Access databases into PostgreSQL. I have notyet decided which servers to use for the web version, but that isanother story, for which I may raise another thread in due course (butI welcome suggestions which may reduce the effort required givenrequired effort for the windows forms app).My problem is for the windows form aplication (intended for use by asingle family). I expect to use ADO.NET. The question is, should Iimport the Access database into MS SQL, and redistribute it, along withMS SQL Server Express (or is that necessary), or distribute it just asan Access database and use the jet engine to access it. A relatedquestion is, "Does ADO.NET support creating new databases for a givenengine?" Imagine a recipe database. It is easy enough to create a SQLscript that creates all the required tables, indices, foreign keys,&c., but can I submit that SQL script to an ADO.NET object, along witha file name, and have it create, e.g., an Access database with thesupplied name. Or do I have to create a database file with nothing init other than the schema?I have more questions, but they'll have to wait.ThanksTed
Hello all,I've been recruited to assist in diagnosing and fixing a performance problemon an application we have running on SQL Server 7.The application itself is third party software, so we can't get at thesource code. It's a Client Management system, where consultants all overthecountry track their client meetings, results, action plans, etc. , and hasapparently been problematic for a long time now. I came into thisinvestigationin mid-stream, but here's the situation as I understand it:We have users reporting it's slow, with no discernable pattern with respectto what part of the application they're using or now particular time of day.I am told that it doesn't appear to be a bandwith or computer resourceproblem. They apparently added two app servers a year or so ago, whichtemporarilyimproved the performance. We're using a nominal percentage of CPU andmemory.There are three large tables (approx 8 million rows) that are queried often,as users click to see their calendar of appointments or review past meetingswith a client, etc. The activity on these tables is over 90% reads(SELECTS) with about 10% INSERTS/UPDATES. We have attempted to run the IndexAnalyzer Wizard twicebut so far it just seems to hang (it could be that the workload file is toobig?) . So, what we're doing now is isolating the SELECT statements thattake a long time to run and manually comparing them to the indexes thatexist on these large tables. Since we can't alter the SQL source code,we're trying to alter the indexes to improve performance.What I would like to know is, is there a good way to get benchmarkmeasurements so we can explicitly measure any performance changes? Also, doyou thinkwe're going about this the right way, or is there some other avenue we couldbe looking at to improve performance?I recognize that performance questions are tricky to post/answer in anewsgroup, because usually you need more information than is provided. Theproblem isthat this is a high profile investigation (they're hauling us into meetingsevery two days to report our progress) and I need to be able to convincinglystate that we have either improved performance by X% , or that it is theapplication itself that's the problem and we're stuck with it.Any thoughts would be deeply appreciated.Thanks and best regards,Steve
I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.
I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?
If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.) Replication past two months working fine, Now 1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber). Iam attaching thw file. Thanks Sandeep
The following is my code. What I am trying to do is find all the students a teacher as assessed during a give time. Then find out which assessment was done the most recently. After that I will then be aggregating those results. I have never written any pl/sql or T-SQL... heck I don't even know what to call it!The first sql command is doing what I want it to. I can only assume the cursor is working correctly. Any help would be greatly appreciated.BryanALTER procedure Domain@UserID numeric,@StartDate datetime,@EndDate datetime AS-- Variable DeclerationDECLARE @SessionID varchar(1000)DECLARE @EachSessionID numeric --Cursor to find all Children that the teacher has assessed for the given timeDECLARE ChildID_cursor CURSORFORSELECT DISTINCT childID FROM capsessionWHERE userid = @UserIDAND sessiondate BETWEEN @StartDate AND @EndDate-- looping through all Children to find there most recent assessment.OPEN ChildID_cursorDECLARE @ChildID numeric FETCH NEXT FROM ChildID_cursor INTO @ChildIDWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DECLARE SessionID_cursor CURSOR FOR SELECT TOP 1 CAPSessionID FROM CapSession WHERE (ChildID = @ChildID) AND (SessionDate BETWEEN @StartDate AND @EndDate) ORDER BY SessionDate DESC END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE ChildID_cursor OPEN SessionID_cursorFETCH NEXT FROM SessionID_cursor into @EachSessionID SET @SessionID = ''WHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SET @SessionID = @SessionID + @EachSessionID+ ',' END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE SessionID_cursorRETURN @EachSessionID
Hi, I wanna know if one has to undergo the 'sql video tutorials' on this website before trying to understand data access tutorials, again on this site. Are the two related by any chance. Also I am not able to find the customer database files he has created in tutorial # 5, dumb to ask, but what to do?,...so anyone willing to shed light!!!
as you might have seen, I'm a total newbie to this Site and M$ SQL. I browsed the forum to see if there are already matching answers to my question, with no success.
My question: As a MySQL & PHP user I ask myself if there is a possibility to use M$ SQL the way I use MySQL and PHP!? Is there a way to generate dynamic websites with M$ SQL 2000 and a webserver running PHP sources? A brief yes or no would really help me, if you have any links or resources on this topic, I'd be glad to hear about it.
I get the error "General SQL Error" when reading or writing to a memo field in Sql Server through a frontend build in Delphi 4. I'm Using the latest BDE. I have recently installed this onto a NT enviroment in the same manner as my own enviroment but get the error message above.
On my own enviroment and a friends enviroment I do not get any errors.
Can someone please give me some help on this or a direction to run in ?
Hi, folks. I've a production SQL machine with more than 20 users making transactions 24 hrs in 6 days a week. I've only Sunday for maintenance. The server has fixed 2 GB RAM allocation for SQL. Is it good to Restart SQL ( or machine) to clear the Buffer-Cache( or is it good to keep the cache) .... :rolleyes:
Maximum Number of rows allowed in sql server 2005 tables ? Maximum Number of columns allowed in sql server 2005 tables ? What are all the built in string functions can be allowed for 'TEXT' DataType in sql server 2005?
Before I go into specifics, this is my problem. I've have a tablethat gets updated with large amounts of data on a monthly basis.Sometimes (rarely) identical rows of data are on one months import thatalready exist from the previous month. I can identify these rows from acombination of two fields (sampleID and testname).My question is this. Would it be an appropriate 'fix' if I created anew 'formula' field on the table comprising of a concatentation ofthese two fields and then made that an index field (no duplicates)? Myguess would be that if we then tried to import a record with a sampleIDand testname that already existed, then the import for that recordwould simply fail.Would this work? Is there a better way? My background is more withAccess so apologies if I'm not using the right terminology.Simon Harris
hi...i would like to know best practice when assigning a value to a variableexample belowsetorselectdeclare @var1 varchar(25)set @var1='abc'select @var1='abc'to meset is implicitselect implies conditionsi see both used all over the place here at my new company....so witch is better practicetksMJ
Is comp.databases.ms-sqlserver more active thanmicrosoft.public.sqlserver.server, or is it the other way around??Which NG is better for general and 'learning SQL 2005' types ofquestions??Thank you, Tom
New to using databases that are NOT Access. New to MS SQL Server. I'dlike help understanding some concepts.Firstly, I'd like to know how to connect to a database using MS SQLServer on a remote web server using Windows XP; I know the IP and havethe username and password to connect...but what program do I use?Suggestions, appreciated.Secondly, I'd like to make changes to the remote database - anyonesuggest programs to use?Regards,OZ
I am currently upgrading my companies database from 6.5 to 2000. Including the replication. I am curious about something. Currently we are using the commands in 6.5 to do the replication instead of stored procedures. (except for one table that requires custome filters). When I reestablished replication on 2000, the default is to create stored procs on the subscribers to hande the replication. (sp_MSins_tablename). My question whether anybody knows why it defaults to using sp's? Is there a reason or is that just the way it is. I want to know if using sp's is better than just sing the commands before I change my setup.
Can someone please answer the following questions:
1) Am I correct that SSRS 2005 can access a remote data source on SQL Server 2000? (this one I'm pretty sure about). I'm also trying to determine whether I can move up to SSRS 2008 when it is released and keep running all the same report types against SQL 2000.
2) Where is the bulk of the reporting services calculations performed (e.g. NOT the database query itself)? The Reporter Server? Or the Reporter Server Database?
3) Is it advisable to have the Report Server and Report Server Database on the same server? How do people typically handle this?
4) Is it advisable to have the Report Server and the Datasource database on the same server?
I have a website. It is a SAS Site. It has a structure similar to
tbClient (ID, Username, password)
tbData (ID, ClientID, info)
tbDatax(ID, tbDataID, info)
tbDatay(ID, tbDataXID, info)
This is a reporting nightmare for me. I Ihave a sollution, but i was wondering if SRS has an out of the box sollution or if anyone knows of an out of the box sollution.
It is a reporting nightmare because clients want to be able to create reports out of the system, but I can't let them see other clients data.
My sollution is give them a username CLientA
They log into The database with CLientA login
I have views that mask the data by enforcing all of the relationships through the view.
create view vutbDatax as
Select tbDatax.*
from tbDataX
Inner Join tbData on tbData.ID = tbDataX.tbDataID
Inner Join tbClient on tbClient.ID = tbDataX.ClientID
where tbCLient.Username = User
User is a Database function that returns me the Username they logged into the database as.
Imagine doing that for 100 views. And managing the security on it. I have done it and it is in the process of testing right now but Does anyone have a better sollution.
Hi, I've got the query below which works as needed, but it performs the same inner select twice at present. What is the best way of eliminating the second evaluation of the inner select statement? SELECT TOP 1 condition FROM (SELECT condition, COUNT(condition) AS total FROM [JCM].[dbo].[jcmresults] GROUP BY condition) WHERE total = (SELECT MIN(total) FROM (SELECT condition, COUNT(condition) AS total FROM [JCM].[dbo].[jcmresults] GROUP BY condition))
I have a website that is probably going to hold a sizable amount of data. The data will be specific to groups of users based on login credentials. Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database? Any thoughts on the topic would be appreciated. Thanks
If I have a foriegn Key stored in a primary table, are triggers used to automate routines in the foreign table when ever the foreign key is changed. Is there a forum reference with practicle examples of where to use triggers?
Hello. I'm running into a rather intermittent problem when a search query is run on an ASP.Net web application. The error is 'General Network Error' Check your Network Documentation... As I said, it doesn't happen everytime which leads me to beleive that there is a problem with the database connection There's probably no more than 50-60 max users hitting the app at any one time. Anybody familiar with this?
hi guys i am developing my application on framework 1.1 on asp .net i am trying to connect to sql server 2005 but it gives me error like general network error check your network documentation.
does any one know how to solve this error then pl reply fast
I'm new to ASP2.0 development (not new to programming though) and have been grappling with general paradigm questions.
So far, I'm using a SQL database to store my applications data. On UI front, I've been using the SQLDataSource in combination with a gridview control. VS2005 thus far has been really easy to use when it comes to these controls. However, I see myself having to know knowledge of the database in UI layer which screams as a classical mistake. The right thing to do seems to be to have an intermediate layer, an object model, abstracting the database. However, if I do create an object model representing tables in my database and then expose these to the UI layer, I lose the ability to easily display data in my gridview (i will no longer be using a sqldatasource. So, after much run around my question is this, what is the preferred paradigm when programming applications for ASP.NET 2.0 and can you still use the nicities offered by VS2005?
Over the course of many weeks the frequency of the following error has been occurring. I have been trouble shooting this problem since I started with the company some 3 weeks ago and I am just stumped.
I'm running SQL7/SP3 on NT4 with current SPs and hotfixes. My default library is NamedPipes with an alias allowed for TCP/IP.
Any help or leads would be appreciated. I've checked for everything the MS KB suggests and it still hasn't resolved my problem!
example of the error:
Microsoft OLE DB Provider for SQL Server error '80004005' [DBMSSOCN]General network error. Check your network documentation. /dir/file.asp, line 29
Would you say that it's ok for a web site code to make ALL of it's access to a db through SP and views? And I mean everything including inserting new records and updating others with no use with SQL in the code.
The advantage would be very strict control over the access, but in order to achieve this it would take many many SP and views to cover all types of actions, can you think about a disadvantage except all the work creating those SP?? what about the server resources and performance? how demanding it would be?
