Externally triggered DTS to import excel data to SQL server

I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!You could get a stored procedure to start the scheduled job which is running the DTS package. A basic stored procedure to run the job would be:

CREATE PROCEDURE sp_StartDTS

AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The DTS job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax. There is also a success/fail return code which you could use in the ASP page|||Originally posted by jasper627
I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!

to overcome bad data in the Excel spreadsheet you could import the data into a holding table that will allow nulls or other bad data, then run some SQL over the table identitfying good records by updating a bit field in the table. If the types of data errors are known and can be fixed automatically eg NULL should be 0 then you could fix that either in the DTS package with a VB script or later with SQL.

Externally Linked Reports

Let's say I have a report which displays employee details based on employee Id. The Employee Id is passed to the report as a parameter. Lets say you can select this from the dropdown list on the report. All of these reports are deployed on report server 2005. What I also have is another application where you manage the employee data.

What I want to do is put a link in the management application to the eomployee detail report and pass the employee id in the link, so that the user does not have to select the employee id on the report. I know that we can provide a link to the reports, but I don't know if we can also pass parameters to those reports in that link.

Any help is appreciated, thanks!!

This is pretty straight forward. We are doing the same thing like so:

http://domainname/reportserver/Pages/ReportViewer.aspx?%2fOurReports%2fReportName&rs:Command=Render&Parameter=InsertParamValueHere

Essentially you can just add &Parameter=Value to the URL to pass the parameter.

|||

All you need to use is a query string. For example:http://servername/appname/page.aspx?id=EMPLOYEE_ID

Cheers!

Externally Accessing localmachine to view reports

How is it possible for others to browse to my localmachine and view the reports?
They tried browsing to http://machinename/reports
and http://10.1.3.123/reports

But the message the users get is: You are not authorized to view this page.

Then In my localmachine, I changed the IIS setting for the Reports and ReportServer to allow anonymous access. Then the users were able to see the Home page of the reports but not the reports themselves to click on for viewing. Any thoughts please?
Thanks

Have you created role assignments for the remote users?

http://msdn2.microsoft.com/en-us/library/aa337491.aspx

When you enable Anonymous, everyone coming into the Report Server looks like the same user (the anonymous user), but you still need to give the anonymous user RS permissions, but then everyone has those permissions, and you probably don't want that.

External logs collection and monitoring

Essentially I am looking for a way to externally store db
audit logs and to be able to parse the data or filter for specific events an
d
ids for review by a security team. Something less manual than copying trace
files from the server to another server and going over each using profiler
(we're talking about 30 servers here!)... but not necessarily as hands-off a
s
flagging and email alerting only.
In going through support docs and threads here a couple questions have also
arisen...
Does the table the trace dumps to have to be part of the local db? Is it
possible to have the trace dump to an external db server?
In order to have trace dump the output to a table, does this require setting
up a job using SQL Trace stored procedures or can it be done just by changin
g
the server's auditing configuration?Server side traces (i.e. without using the Profiler GUI) can only write to
trace files. You can load trace files into a sql table (for example on a
central "audit" server) using the sytem function fn_trace_gettable. See BOL
for details
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JMBickham" <JMBickham@.discussions.microsoft.com> wrote in message
news:AF4060D6-6986-4999-85E3-5B090EC39A53@.microsoft.com...
> Essentially I am looking for a way to externally store db
> audit logs and to be able to parse the data or filter for specific events
> and
> ids for review by a security team. Something less manual than copying
> trace
> files from the server to another server and going over each using profiler
> (we're talking about 30 servers here!)... but not necessarily as hands-off
> as
> flagging and email alerting only.
> In going through support docs and threads here a couple questions have
> also
> arisen...
> Does the table the trace dumps to have to be part of the local db? Is it
> possible to have the trace dump to an external db server?
> In order to have trace dump the output to a table, does this require
> setting
> up a job using SQL Trace stored procedures or can it be done just by
> changing
> the server's auditing configuration?sql

external file fragmentation

I know that as the data file autogrows, we are subject to fragmentation
externally.
As an example, I created a data file starting at 1 GB and then have autogrow
set at 250 MB. . Say now its around 2 GB which means we may have 5 different
sets of contiguous allocations for this file. i.e one contiguous allocation
for the first time creation of 1 GB and then 4 250 MB allocations
Now say I detach this db and move the 2 GB file to another server.. Will it
copy this 2 GB file as one contiguous file or still have it broken down into
5 ? Trying to understand file level fragmentation.> Now say I detach this db and move the 2 GB file to another server.. Will
it
> copy this 2 GB file as one contiguous file or still have it broken down
into
> 5 ? Trying to understand file level fragmentation.
This depends. If you have on the target server enough contiguous space, the
new file should not be fragmented. Anyway, you can always check the
fragmentation and defragment files on disk with Disk Defragmenter.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com