DTS failed to run automaticlly

Hello,
I created a DTS to transfer data from one SQL server to another SQL server.
However, I can only run the job manually. If I scheduled this job to be run
everyday automaticlly, It failed.
Please help.
Thanks.
FuhlSounds like a permissioning problem. When you manually run the package it executes using your username and permisions when it executes as a job I believe it runs as the SQL Agent (not certain on this but it is definately not using your permissions).

HTH|||rokslide is right.
When the dts or jobs run automatically they use SQL Server Agents startup accounts credentials. Just that that the SQL Agent startup account has permissions to do the job what you have put in the DTS.

Also is should have permission to write on the other server don't forget that.

DTS Failed To Run

Hi all,

I have almost 50 DTS and its was scheduled run daily at a specific
time. Since last week all the jobs was failed. Error "The process could
not be created for step 1 of job 0x43434..... (reason : The system
cannot find the file specified). The step failed.". The jobs was run
find before. If I manually execute package under Local Package it ok.
I'm not a DBA. Any help are welcome.Find the solution under and it work now.
http://support.microsoft.com/kb/322746/en-ussql

DTS Failed

SQL 2K
We have a job to run a DTS which loads and computes data from a database in
the same server..
This jobs worked till yesterday, failed today with error message
Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
failed.
No changes made to server or job..
Looked at the server Event viewer , found nothing ..
Any thoughts ?
S
I'm guessing this is the output from the job history. The job history ouput
only show part of the DTS log, so you may be missing the critical
information you need to help you debug this problem. Did you output the
entire DTS output to a file with either the advanced tab option when editing
the job step or the dtsrun /L option? If so look at the log file, in there
and you might find additional information. If not modify your job to create
the log file and rerun.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"MS User" <sqlman@.sql.com> wrote in message
news:uHOlG$eMEHA.1188@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a job to run a DTS which loads and computes data from a database
in
> the same server..
> This jobs worked till yesterday, failed today with error message
> Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_7
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
> 1000 Rows have been transformed or copied.; PercentComplete = 0;
> ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
> Rows have been transformed or copied.; PercentComplete = 0; ProgressCount
=
> 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
> transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
> OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
> copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
> DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
> PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
> failed.
> No changes made to server or job..
> Looked at the server Event viewer , found nothing ..
> Any thoughts ?
> S
>

DTS Failed

SQL 2K
We have a job to run a DTS which loads and computes data from a database in
the same server..
This jobs worked till yesterday, failed today with error message
Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
failed.
No changes made to server or job..
Looked at the server Event viewer , found nothing ..
Any thoughts '
SI'm guessing this is the output from the job history. The job history ouput
only show part of the DTS log, so you may be missing the critical
information you need to help you debug this problem. Did you output the
entire DTS output to a file with either the advanced tab option when editing
the job step or the dtsrun /L option? If so look at the log file, in there
and you might find additional information. If not modify your job to create
the log file and rerun.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"MS User" <sqlman@.sql.com> wrote in message
news:uHOlG$eMEHA.1188@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a job to run a DTS which loads and computes data from a database
in
> the same server..
> This jobs worked till yesterday, failed today with error message
> Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_7en">
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
> 1000 Rows have been transformed or copied.; PercentComplete = 0;
> ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
> Rows have been transformed or copied.; PercentComplete = 0; ProgressCount
=
> 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
> transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
> OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
> copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
> DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
> PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
> failed.
> No changes made to server or job..
> Looked at the server Event viewer , found nothing ..
> Any thoughts '
> S
>

DTS Failed

SQL 2K
We have a job to run a DTS which loads and computes data from a database in
the same server..
This jobs worked till yesterday, failed today with error message
Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
failed.
No changes made to server or job..
Looked at the server Event viewer , found nothing ..
Any thoughts '
SI'm guessing this is the output from the job history. The job history ouput
only show part of the DTS log, so you may be missing the critical
information you need to help you debug this problem. Did you output the
entire DTS output to a file with either the advanced tab option when editing
the job step or the dtsrun /L option? If so look at the log file, in there
and you might find additional information. If not modify your job to create
the log file and rerun.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"MS User" <sqlman@.sql.com> wrote in message
news:uHOlG$eMEHA.1188@.TK2MSFTNGP12.phx.gbl...
> SQL 2K
> We have a job to run a DTS which loads and computes data from a database
in
> the same server..
> This jobs worked till yesterday, failed today with error message
> Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_7
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1;
> 1000 Rows have been transformed or copied.; PercentComplete = 0;
> ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000
> Rows have been transformed or copied.; PercentComplete = 0; ProgressCount
=> 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been
> transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun
> OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
> copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress:
> DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
> PercentComplete = 0; ProgressCount = ... Process Exit Code 1. The step
> failed.
> No changes made to server or job..
> Looked at the server Event viewer , found nothing ..
> Any thoughts '
> S
>

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I am using a DTS created by the Export Wizard to send data to an Excel
> > spreadsheet. The problem is that each time the DTS is run, the data gets
> > appended to the spreadsheet, rather than replaced. I used the Drop and
> > Create Destination Table option on the Transformation window.
> >
> > I found an earlier post regarding this same problem. The suggestion was to
> > use the Delete option in the Transformation window of the wizard. I rebuilt
> > the DTS based on that idea, but get an error message about "Deleting data in
> > a linked table is not supported by this ISAM".
> >
> > Can someone offer some ideas on how to get the data replaced in the Excel
> > spreadsheet?
> >
> > Thanks.
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I cannot rename the spreadsheet because it is tied into other processes. I
> > need to replace the data that already exists in the spreadsheet.
> >
> > If it helps, I did some research since my original posting and here is what
> > I found:
> > 1) When DTS creates the range name in the spreadsheet, it is only the
> > headings of the data. The data itself is not included in the range name.
> > Somewhere, the last line of data is being tracked versus the last line in the
> > range name. Subsequent runs of the DTS appear to be using the last line of
> > data, not the last line in the range.
> >
> > 2) If I manually expand the range name to include the last line of data,
> > then rerun the DTS, the new data is still appended to the bottom of the old
> > data. The old data is cleared leaving blank rows, but the new data is still
> > appended to the bottom; again based on the last line of data. The area
> > covered by the range name returns to being just the headings.
> >
> > Could the fact that Excel is not installed on the machine running the DTS
> > have any bearing?
> >
> > Thanks.
> >
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

ThanksPost the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||Here goes the Stored Procedure

CREATE PROCEDURE sp_ProductivityReport_ByMonth

@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

DECLARE @.@.total1 decimal(20,2)
DECLARE @.@.total2 decimal(20,2)
set @.@.total1 = 0
set @.@.total2 = 0

select B.T_FName as "First_Name", B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"

into #CustomTable1
from dbo.BillingInfo A (nolock), dbo.TInfo B (nolock)
where
A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY
A.T_ID, B.T_FName, B.T_LName

DECLARE Total_Cursor CURSOR Local
FOR SELECT Lines_Changed FROM #CustomTable1
OPEN Total_Cursor
FETCH next From Total_Cursor
INTO @.@.total2
while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor
INTO @.@.total2
END
CLOSE Total_Cursor
DEALLOCATE Total_Cursor

/**** Calculating Total*******/
DECLARE @.Reports_Total int
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1

DECLARE @.T_M_Total decimal(15,2)
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1

select "xxx - Total" as First_Name, ' ' as Last_Name, @.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed, @.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2

set nocount on

SELECT First_Name, Last_Name,Reports_Changed,
Minutes_Changed, Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.@.total1) AS decimal(5,2))) + '%'
as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT * FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

Post the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||You really need to learn more about TSQL, and principles of database application design before attempting something like this. Why? Because you are taking the wrong approach to solving a problem that, really, you should not be trying to solve with SQL in the first place.

Lets begin...

Dump the cursor and learn how to write set-based logic. This entire sectionDECLARE @.@.total2 decimal(20,2)
set @.@.total2 = 0
.
.
.
DECLARE Total_Cursor CURSOR Local FOR
SELECT Lines_Changed
FROM #CustomTable1

OPEN Total_Cursor
FETCH next From Total_Cursor INTO @.@.total2

while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor INTO @.@.total2
END

CLOSE Total_Cursor
DEALLOCATE Total_Cursor...can be replace by one line:SET @.@.totall = sum(Lines_Changed) from #CustomTable1

Next, all of these lines and more...SET @.@.totall = sum(Line_Changed) from #CustomTable1
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1
.
.
.
select "xxx - Total" as First_Name,
' ' as Last_Name,
@.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed,
@.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
...can be run as a single statementselect "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

Then, I have to ask, what is with the use of the double ampersands?

Lastly, it poor programming practice to be calculating subtotals to a report within SQL, as you are doing with CustomTable2. This is best handled by whatever reporting application you are using. Not because of a limitation within SQL, but because you are essentially mixing record types (raw and total) within a single dataset. Bad form.

So, re-read the Books Online sections on SELECT statements and aggregate queries. And if you find yourself using cursors again, be confident you are doing something wrong because you probably are.

But for the heck of it, go ahead and try this shortened code:CREATE PROCEDURE sp_ProductivityReport_ByMonth
@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

select B.T_FName as "First_Name",
B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"
into #CustomTable1
from dbo.BillingInfo A (nolock),
dbo.TInfo B (nolock)
where A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY A.T_ID,
B.T_FName,
B.T_LName

/**** Calculating Total*******/
select "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

SELECT First_Name,
Last_Name,
Reports_Changed,
Minutes_Changed,
Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.total1) AS decimal(5,2))) + '%' as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT *
FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export format, please help

I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
I want to run a DTS export nightly that export the records out to a file.
The kick is that I would like the text file to list the fields on their own
line like:
Fname
Lname
City
Zip
Instead of across like: Fname, Lname, City, Zip.
How can I accomplish this?
Thanks for your time!
TonyHopefully this will solve your issue. write the code in Axtivex script in DT
S.
========================================
=========
'***************************************
*********************
' Visual Basic ActiveX Script
'***************************************
*********************
Function Main()
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "sqloledb"
Conn.Properties("Data Source").Value = "(local)"
Conn.Properties("Initial Catalog").Value = "Testing"
Conn.Properties("Integrated Security").Value = "SSPI"
Conn.CommandTimeout = 0
Conn.Open
'--get students record set
SqlQuery ="select * from tblStudent"
Set RsStudent = Conn.Execute(SQLQuery)
'--ourt vars
ourputString = ""
Path = "C:\test\"
'-- create output folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FolderExists(path) Then
objFSO.CreateFolder (path)
End If
'--Create Traget file
Set TargetFile = objFSO.CreateTextFile(Path &"Report.txt", True)
IF not RsStudent.eof Then
While not RsStudent.eof
ourputString=""
ourputString="Fisrt Name: " & RsStudent("FName") & vbcrlf & _
"Last Name:" & RsStudent("Lname") & vbcrlf & _
"City :" & RsStudent("City") & vbcrlf & _
"Zip :" & RsStudent("Zip") & vbcrlf & _
"----
--" & vbcrlf
TargetFile.WriteLine(ourputString)
RsStudent.moveNext
Wend
End If
Set TargetFile=Nothing
Set objFSO=Nothing
Set RsList =Nothing
Conn.close
set conn = nothing
Main = DTSTaskExecResult_Success
End Function
========================================
=========
"tony tuso" wrote:

> I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
> I want to run a DTS export nightly that export the records out to a file.
> The kick is that I would like the text file to list the fields on their ow
n
> line like:
> Fname
> Lname
> City
> Zip
> Instead of across like: Fname, Lname, City, Zip.
> How can I accomplish this?
> Thanks for your time!
> Tony
>
>

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.

Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.

"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:

> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus

The Oracle client is required. So is paying attention to the license
agreement.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.
Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.
"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.com...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Unfortunately you will need the Oracle connectivity on the server/or
anywhere else that tells this package to execute
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Marcus wrote:
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus
The Oracle client is required. So is paying attention to the license
agreement.
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

Dts Execute Problem

Hi all,
My dts fails in step 3 when i run it from asp.net. But it works fine in when i run in Designer.
In step 3, it reads some data from a text source (on a shared place). And it gives me error :

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error code: 80004005
Step Error Help FileTSFFile.hlp
Step Error Help Context ID:0

I know that when i run it on Designer, it works by my current Windows Loginname. And when i execute dts in asp.net page, it works by SqlAgent's login name. And also i made SqlAgent's Username (as a Domain UserName) a Local Admin in the file shared server.

Any idea?

Regards..

It appears that your ASP.NET application does not have permission to access the flat file.

However, it is very unlikely that your ASP.NET application is running in the context of the SQL Agent account.

How are you launching the package - using the API in your code, or calling sp_start_job?

|||I think my ASP:NET application has permission because i can get list of that folder's files and can edit one of them.
My application runs with my current AD username/password.
I use Interop.DTS class to run Dts. I can run other dts correctly with this method but i can't run this one.

Regards..

DTS Error problems driving me mad!

I have a DTS scheduled to run daily. It has run OK for the past few months, but now always fails, the following error appears:

dtsrun.exe - application error

the instruction at 'xxxxxxx' referenced memory at 'yyyyyyy'. The memory could not be 'read'. click on ok to terminate the program.

The dts itself runs fine if started manually, but always fails as a scheduled job, even if its started manually.

Help!!!!!Eventhough a serious violation like this shouldn't happen, it may be caused by insufficient permissions. The job is run with the permissions the user has who is configured to run sqlserver (EM, Server properties, Security-tab).|||I have never encountered this particular error myself; however if I may toss in a couple of thoughts:

1. Is there an increased load being placed on the server of late?
2. Are there a number of DTS tasks scheduled to run simultaneously?
3. Are there other OS or application tasks scheduled to run at or near the same time as this package?
4. Do you know precisely what step DTS is failing on?
5. Is it possible to schedule and run this task from another SQL Server?

Some additional thoughts (which you may already know and I apologize if you do):

1. DTS runs in a separate memory space from SQL Server; multiple DTS packages running simultaneously will degrade the amount of RAM available.
2. There is a windows registry setting for throttling the number of DTS (or was it command -- .exe -- objects that can run simultaneously). I think the default is 10. Search MSDN.
3. If you use DTS heavily on one server, consider throttling back the amount of memory available to SQL Server; I know that this is NOT recommended by MS, but I have done this in the past with a good deal of success.

I hope that you can trace the roots of your issue; good luck.

Regards,

hmscott

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was WIndows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?
Mitch,
[vbcol=seagreen]
In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was WIndows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?
|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Mitch,
> In the context of the error message, I think it refers to SQL2000 licensing
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
sql

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was Windows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?Mitch,

In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx
.gbl...[vbcol=seagreen]
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was Windows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2
MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Mitch,
>
> In the context of the error message, I think it refers to SQL2000 licensin
g
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx
.gbl...

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was WIndows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?Mitch,
>> I thought the error was WIndows 2000 licensing error.
In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was WIndows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2MSFTNGP12.phx.gbl>...
> Mitch,
> >> I thought the error was WIndows 2000 licensing error.
> In the context of the error message, I think it refers to SQL2000 licensing
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> > In trying to run a step in a DTS package, I get this error
> > on SQL 2000 running on Windows 2000.
> >
> > "The DTS cannot copy or transform data from a desktop or
> > MSDE Server to a standard, Enterprise of small business
> > version of SQL SERVER unless your destination server is
> > per user licensing mode."
> >
> > I thought the error was WIndows 2000 licensing error. The
> > licensing is set to per seat. Anyone know how I could
> > fixe this?

DTS Error

I am trying to run a scheduled package in the DTS and keep getting a the error message in the job history:
'Executed as user: UTSQL01\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from ProForm1099 to "SCOTT"."PROFORM1099" Step DTSRun OnError: Copy Data from ProForm1099 to "SCOTT"."PROFORM1099" Step, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from ProForm1099 to "SCOTT"."PROFORM1099" Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.'
Is there someone that knows what this means and how I can correct it?
Thanks.I'm assuming that when you run it manually it works...

It sounds like a security issue...the sql agent account needs permission

Yes?|||It is a security issue. I can't seem to get around it though. I log on as administrator and I get a whole new set of errors. I am now trying to export to Excel and get the error that the path to the Excel sheet is wrong. How do I give permissions?
Thanks.|||This should answer your question:

link (http://support.microsoft.com/?id=269074)

Let me know if you still have the problem.|||I can't change the owner info in the Owner drop-down combo box which is what it tells me to do.
Thanks for your help. The link you sent was the right one.|||Are you stuck ... ?|||Very. How can I change the owner?|||What happens when you change the owner and select apply ? Do you have administrator rights ?|||The owner combobox is grayed and I can't even access it.|||Do you have sys admin access ? Can you login as the owner ?|||You can also try the stored procedure to change the ownership:
sp_update_job|||I can't run it for some reason. I don't know much about this stuff.|||Try msdb.dbo.sp_update_job ... What is your access level in sql server ? Have you tried logging in as sa ?|||I can run the procedure in Query Analyzer but I get the message:
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 24
Supply either @.job_id or @.job_name to identify the job.
Stored Procedure: msdb.dbo.sp_update_job
Return Code = 1

Where do I put the job_id or job_name?

Thanks again for your help.|||The job now says it succeeded but when I open the destination file, there is no data there. I'm lost.
And I ran the procedure msdb.dbo.sp_update_job and got a:
Stored Procedure: msdb.dbo.sp_update_job
Return Code = 0
message.|||Did you get the sp to execute ? What is the destination file ? What happens when you execute the dts manually - does the destination file contain something ?|||Manually it works fine. And I did run the SP.|||Can you break down what the dts is doing (step-by-step) and what is supposed to be in the file ?|||When I export to Excel on my desktop I get:

Executed as user: UTSQL01\SYSTEM. ...art: Drop table PROFORM1099 Step DTSRun OnFinish: Drop table PROFORM1099 Step DTSRun OnStart: Create Table PROFORM1099 Step DTSRun OnFinish: Create Table PROFORM1099 Step DTSRun OnStart: Copy Data from ProForm1099 to PROFORM1099 Step DTSRun OnProgress: Copy Data from ProForm1099 to PROFORM1099 Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: Copy Data from ProForm1099 to PROFORM1099 Step; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: Copy Data from ProForm1099 to PROFORM1099 Step; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: Copy Data from ProForm1099 to PROFORM1099 Step; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: Copy Data from ProForm1099 to PROFORM1099 Step... Process Exit Code 0. The step succeeded.

Everything worked, but there is no data in the destination file.

If I try to export to an Excel sheet on my C:\ drive I get an error that the path is incorrect and to check it again.

There is nothing major in the source table. Just 4 columns with 15000 rows of num,and char strings.|||Was that last one scheduled?

If it was, it's on the server...

What happens when you use the export wizard?

What's the data destination in the transformation?

Betcha it's on the server...can you map to it?|||I don't understand what you mean if the last one was scheduled. I always use the export wizard. The destination is an Excel sheet on my desktop. I can map to it.|||When you execute dts using enterprise manager from your desktop, the file will appear on your local drive (if that is where the destination is located - for example c:\). When you execute the dts as a job, it will execute on the server - so the file should be on the server (not your desktop).

Let me know if this is what happened.|||It looks like the file was saved on the server. How can I get it to save on my local machine?
Thanks again.|||You can do the following:

\\computername\c$\file.txt - assuming permissions are ok.

Rather than the server pushing to your machine - I would recommend pulling the file from the server.

DTS copy truncates bigint values

I just discovered that a bigint column in one of my tables is getting it's values truncated when I run a DTS job to copy the data to another database. The DTS job is designed to refresh our test environments from our production database. All the other tables copy fine. This include another table that also contains a bigint column. But this one table consistenly has a problem where 60 or so records are translated from positive number to negative values. The only explaination I came up with was truncation at some point.

Anyone know of a way to fix or work around this?Is it the same 60 records, or different ones? Have you tried perhaps converting this column to a large varchar field, to see what happens to those records then? Unfortunately I don't know what this is off the top of the head, but these might help figure out what it is.|||The number of records will change every so often. I think I first noticed 64 and then it was 62. I refined my query and discovered that in reality I have 192 records out of 6025 records where the bigint field was mangled. I did a join between the two databases to find the mismatches.

One thing I noticed is that it appears to work when going from our production server to our test server. Those servers are SQL Server 2K Standard SP2 while my machine is SQL Server 2K Developer SP3.|||Well maybe if you copied it to your machine to a temp table with a varchar as the datatype (converting the bigint to varchar in your DTS), then converting it back (just altering the table may do the trick since bigint to varchar is an implicit conversion). That may be a workaround for your machine anyways.

DTS completion time jump

Hi,
I have a DTS package that runs 4 other packages on a nightly basis. This
package usually takes about 2 hours to run, but ocasionally it jumps to
around 5 hours to complete. There have not been any changes to the
parameters or coding. It is the same process that causes this each time
which is a cursor run strored procedure. There have not been any huge jumps
in the number of records in the table or changes to the source data that
would cause the cursor to take longer to run through. This seems to be
intermittent, I can not easily check to see if something else in the network
on that server is happening since it is a customer site. Would you think
that something outside of the package is affecting this since it only happen
s
once in a while (about once a w or so), like I mentioned - backups, serve
r
processes, other jobs etc.? I just don't know where to look if I have not
changed anything within my package.
Thanks,
PatriceThe are many reasons why performance can change over time. One is job
contention, where multiple jobs are running at the same time. The following
will dump a listing of jobs, when they started and ended out of the
msdb.dbo.sysjobhistory system table, and store it in a temporary table:
select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
'1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
INTO #jobsched
FROM (
select t1.[name],
CAST(
start_date + ' ' + LEFT(t1.start_time,2) + ':' +
SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
) AS "job_start",
LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
RIGHT(t1.job_duration,2) AS job_duration
FROM
(
select
j.[name],
LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date",
REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
"start_time",
REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6))
"job_duration"
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
ON h.job_id = j.job_id
WHERE h.step_id = 0
) t1
) t2
SELECT * FROM #jobsched
You could take a look at the output of the above and "eyeball" it for job
conflicts, or you could try (*untested*)
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
RIGHT JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
to get a listing of jobs that are conflicting with the job named 'Job to
analyze for conflicts' .
It's also possible that the additional time for the DTS task to process is
due to locking. You could try adding another stop to the DTS job that dumbs
the output of sp_who2 into a table for further analyses.
Hope that the above helps.
--
"Patrice" wrote:

> Hi,
> I have a DTS package that runs 4 other packages on a nightly basis. This
> package usually takes about 2 hours to run, but ocasionally it jumps to
> around 5 hours to complete. There have not been any changes to the
> parameters or coding. It is the same process that causes this each time
> which is a cursor run strored procedure. There have not been any huge jum
ps
> in the number of records in the table or changes to the source data that
> would cause the cursor to take longer to run through. This seems to be
> intermittent, I can not easily check to see if something else in the netwo
rk
> on that server is happening since it is a customer site. Would you think
> that something outside of the package is affecting this since it only happ
ens
> once in a while (about once a w or so), like I mentioned - backups, ser
ver
> processes, other jobs etc.? I just don't know where to look if I have not
> changed anything within my package.
> Thanks,
> Patrice|||Sorry, that last query should have been
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
INNER JOIN --INNER replaced RIGHT for the JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
"Mark Williams" wrote:
> The are many reasons why performance can change over time. One is job
> contention, where multiple jobs are running at the same time. The followin
g
> will dump a listing of jobs, when they started and ended out of the
> msdb.dbo.sysjobhistory system table, and store it in a temporary table:
>
> select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
> '1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
> INTO #jobsched
> FROM (
> select t1.[name],
> CAST(
> start_date + ' ' + LEFT(t1.start_time,2) + ':' +
> SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
> ) AS "job_start",
> LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
> RIGHT(t1.job_duration,2) AS job_duration
> FROM
> (
> select
> j.[name],
> LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
> char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date
",
> REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
> "start_time",
> REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6
))
> "job_duration"
> from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
> ON h.job_id = j.job_id
> WHERE h.step_id = 0
> ) t1
> ) t2
> SELECT * FROM #jobsched
> You could take a look at the output of the above and "eyeball" it for job
> conflicts, or you could try (*untested*)
> SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
> t2.job_start, t2.job_end
> FROM
> ( SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] = 'Job to analyze for conflicts'
> ) t1
> RIGHT JOIN
> (
> SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] <> 'Job to analyze for conflicts'
> ) t2
> ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
> OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
> to get a listing of jobs that are conflicting with the job named 'Job to
> analyze for conflicts' .
> It's also possible that the additional time for the DTS task to process is
> due to locking. You could try adding another stop to the DTS job that dumb
s
> the output of sp_who2 into a table for further analyses.
> Hope that the above helps.
> --
> "Patrice" wrote:
>