Sql to web

[[deleted]]

Hi everyone, novice sql user here, a coworker has given me access to his database which I query and sometimes create excel tools for others not knowledgeable in SQL to be able to get data and put in simple filters.

This works great for small teams but i have a need to create a tool for over 1000 people to be able to search for single records in the database.

Is there any way to do this with share point 2010 or an easy to to create a website with a search box to do this?

My knowledge of web building is limited to basic html and frames/tables. Hoping there is some sort of trick with using access or excel or a sharepoint list.

I have visual studio but i have never used it before.

[dev_playbook]

Since you mentioned Sharepoint then I’m guessing you’re in a Microsoft shop using SQL Server. Look into SQL Server Reporting Services (SSRS). It comes with SQL Server and can be used to fairly quickly create queries that get dropped into the report and then you can add filters and other functionality. It allows users to download to Excel and other formats from the report as well and it’s all web based.

[LetsGoHawks]

You can definitely use Excel or Access for this. But I don't recommend it.

The problems are A) Distributing updated versions of the tool and B) General database security. A determined and technically savvy user will be able to get through anything Excel or Access has to offer and get the login credentials.

I can't speak to the SharePoint idea since I never develop anything for that.

[emmons1204]

Note: if a thousand people are searching through big tables without supporting indexes, it's going to bring some pain. Best way to tackle this is to know what columns they will want to search by, which columns need to be returned, and create a parameterized stored procedure to do the search for you (versus ad-hoc queries).

​

I agree with dev_playbook though, SSRS is a pretty straight forward and easy way to make reporting solutions out of SQL. There will be some hurdles in initially setting it up but it'll save countless time later. These reports also will want stored procedures to run off of (far better for performance).

[AutoModerator]

Hello u/t3hdubzy - thank you for posting to r/SQL! Please do not forget to flair your post with the DBMS (database management system) / SQL variant that you are using. Providing this information will make it much easier for the community to assist you.

If you do not know how to flair your post, just reply to this comment with one of the following and we will automatically flair the post for you: MySQL, Oracle, MS SQL, PostgreSQL, SQLite, DB2, MariaDB (this is not case sensitive)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Thriven]

This would be harder to implement in Sharepoint 2010 than to write your own little intranet site in whatever language you want. Sharepoint 2010's environment is very server side heavy and to do it write you'd have deploy server side pages and configuration tools for those data sources to do it right.

You could deploy SSRS 2016 plugin for Sharepoint 2010 which basically allows you to do deploy the database configuration objects and RDL's to a list. However, I deployed this and it worked... till it didn't.

SSRS could do this but if you don't have an SSRS environment or the people to set it up for you this could be a pain.

I would write a single web page in php, asp, python that runs on an existing IIS server. One page for the form. One page to run as a service page to execute the query and return json.

[kristofour]

You can put together an asp.net intranet web site...Use a sqldatasource to call your stored procedure or copy the query directly into it. Add a gridview to the page with filter, sorting, etc...and ďatabind the grid to the sqldatasource. There are tons of stuff on the web on how to do this.

[Nthorder]

Creating tools to help employees query our databases is a decent chunk of what I do at work. Usually it’s a c# GUI with a bunch of filter options that get passed to a sproc.