JMR77
asked on
Is it possible to run a SQL script from SharePoint?
I have been trying to accomplish something that I am not even sure is possible. I am trying to automate the process of importing a Excel sheet (Excel 2008) to a SQL 2012 database. I created a custom web part in SharePoint to include a browse feature, text box for path, and a submit button for users to select an excel file from their hard drive. This excel file is be imported into a temp table, be manipulated in the table, and then inserted into another table.
I assume that the SharePoint credential should be assigned to a svc account or active directory that grants the user access to the SQL server to allow the inserting data into the temp DB.
I made the web part, but I am not even sure if I am on the right track because I am having issues with the SQL import script.
I assume that the SharePoint credential should be assigned to a svc account or active directory that grants the user access to the SQL server to allow the inserting data into the temp DB.
I made the web part, but I am not even sure if I am on the right track because I am having issues with the SQL import script.
ASKER
Sorry for the delay, I thought I had it covered and I was wrong. Ended up trashing previous code and starting from square one.
I am using a SharePoint 2010 Enterprise edition, but access to the central administration is limited.
I guess the goal is having a user click a button on a web part to open up a file explorer window and browse for an excel file and click submit. Then this webpart would run a import script to copy the data from the excel file into a database in a external (not the SharePoint SQL server instance) SQL database. I may not need all the data and one of the fields would be created depending on the sys date.
I am a .NET developer with a bit of SQL experiance but I am a SharePoint noob. While researching I thought I might be able to create the interface using web parts, and I see there is a library of these already, but they are fairly vague and obtuse. So am I wasting my time going about in this way or am I over complicating things?
I am using a SharePoint 2010 Enterprise edition, but access to the central administration is limited.
I guess the goal is having a user click a button on a web part to open up a file explorer window and browse for an excel file and click submit. Then this webpart would run a import script to copy the data from the excel file into a database in a external (not the SharePoint SQL server instance) SQL database. I may not need all the data and one of the fields would be created depending on the sys date.
I am a .NET developer with a bit of SQL experiance but I am a SharePoint noob. While researching I thought I might be able to create the interface using web parts, and I see there is a library of these already, but they are fairly vague and obtuse. So am I wasting my time going about in this way or am I over complicating things?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
first of all: which SharePoint version and edition?
That depends a little bit on your code.
If you run your code inside a SPSecurity.RunWithElevated
How do you manipulate the Excel file - Excel COM or Office XML?
Perhaps you could share a little bit more info on your exact solution approach as well as already existing code.
Thanks and HTH
Rainer