December 10, 2008 at 4:50 pm
SQL Gurus:
I need to provide data from a SQL 2005 database up to four users at my work. I'm not sure if I should use an HTML or ASP page to provide that data. Moreover, I am unsure as to what method (ADO, RDO) I should access the data. My thought early on was to create a view for the HTML to connect to since the table will be updated multiple times.
Your help would be greatly appreciated.
Dave:)
December 15, 2008 at 8:16 am
You'd probably be better off posting this on web development forum instead of SQL Server as this really applies to asp or asp.net.
In asp, I'd recommend using vbscript and ado.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 15, 2008 at 8:26 am
I think we need more information to help you.
What do you mean by provide? File, View Search, Update, Add or some comgination?
What tools do you have? Visual Studio? SQL Only?
I work in generally in a Visual Studio / SQL environment so I would use C#.NET web pages with the ADO.net but that may be without value based on the answer to question 1.
December 15, 2008 at 9:45 am
I am just quering the database from an asp page. Below is the view I am accessing to include the ASP connection string to the view.
<%
Dim oConn
Dim oRs
Dim filePath
Dim Index
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB;DATA SOURCE=SERVER;UID=SA;PWD=PASSWORD;DATABASE=DATABASE"
Set oRs = oConn.Execute("SELECT Branch, BatchCount FROM BatchCount")
%>
CREATE VIEW [dbo].[BatchCount]
AS
SELECT TOP (100) PERCENT LEFT(sSavedFileName, 6) AS 'Branch', COUNT(iItemCount) AS 'BatchCount', bStatus
FROM dbo.ExternalInputStatus
WHERE (iExternalSourceID = 2) AND (bStatus <> 0)
GROUP BY LEFT(sSavedFileName, 6), bStatus
ORDER BY 'Branch'
Hope this helps.Dave
December 15, 2008 at 9:57 am
That will certainly work for four users but it means code changes for any presentation changes.
I would also limit that technique to an internal only server as there are security questions if this is exposed to the web.
December 15, 2008 at 10:00 am
I wouldn't use sa to connect that's for sure. You are now exposing the sa accoutn and password to a lot of people.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 15, 2008 at 10:45 am
Very Good Catch Jack!!
Didn't even look at the login being used. In my opinion 'sa' should never be used except when working directly with the SQL server. It certainly should never be exposed to any web server.
Alan
December 15, 2008 at 10:51 am
I know sa shouldn't be used. I am more concerned about the code and way I am accessing the SQL server.
December 15, 2008 at 1:31 pm
drodriguez (12/15/2008)
I know sa shouldn't be used. I am more concerned about the code and way I am accessing the SQL server.
Access: If you are still on ASP, then ADO is preferred (ADO.net is preferred for ASP.net). Logon should be through a dedicated account.
Code: Use stored procedures instead of directly executing SQL from your ASP/ASP.net page. The SQL access provided for your dedicated account should only allow the client to execute the stored procedures that you approve for it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply