DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS Design Error - Access is denied

I am pretty new to MS SQL Server ...

I have installed SQL Server Enterprise Manager Version 8.0. I am getting the following error while try to create a new DTS Package.

"DTS designer Error ... Access Denied"

getting the DTS packge design window but there is no TASK icon visible
either in the tool bar or menu....

Enterprise Manager Version 8.0

OS : Windows XP pro with SP1If you go to the msdb database, the user you are using needs to have EXEC permissions on the procs that have dts in the name. This is by default granted to public, so I'm not sure why your user wouldn't have that. ?|||See whether this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;814113 is any help to you.

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
************************************************** *********
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
************************************************** ***********
Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:

> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ************************************************** *********
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> ************************************************** ***********

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
***********************************************************
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
Package execution complete. Process Exit Code 1. The step failed.
*************************************************************Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:
> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ***********************************************************
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun:
> Package execution complete. Process Exit Code 1. The step failed.
> *************************************************************

DTS

Hi folks,
I am running an Active-X in DTS package and it works perfect when execute
from the DTS design area but it fails when run as JOB. This is the error i
get. Any idea? I couldnt figure out the clue from this error msg. Any help
appreciated. TIA
****************************************
*******************
Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Hel
p
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
:
Package execution complete. Process Exit Code 1. The step failed.
****************************************
*********************Either because it's now runnnig as user ABC\SYSTEM rather than as your login
or because all paths are now relative to the server (and using different
mapped drives) instead of relative to your workstation.
Those are the usual problems when executing on the server.
"rupart" wrote:

> Hi folks,
> I am running an Active-X in DTS package and it works perfect when execute
> from the DTS design area but it fails when run as JOB. This is the error
i
> get. Any idea? I couldnt figure out the clue from this error msg. Any help
> appreciated. TIA
> ****************************************
*******************
> Executed as user: ABC\SYSTEM. DTSRun: Loading... DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError:
> DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B) Error
> string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Erro
r:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The tas
k
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp H
elp
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSR
un:
> Package execution complete. Process Exit Code 1. The step failed.
> ****************************************
*********************

dtabase design for storing true/false questions

Hai,

I am trying to design a database for storing trule/false questions.
I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses.
So does the following tables and fields suffice to achieve that?.

Questions_table(id(autogenerated),course_id,Questi on(text type))
id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.

Response_table(id,response)
Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.

So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.

Thanks,yes. You might also want to add a NOT NULL constraint to the column|||I'd use smallint rather than int, for saving space and for efficiency.

I would not use bit, as you would have to convert to smallint every time you wanted to run an aggregate function.