DTS Fails as JOB

Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
Patrick
Patrick
2 things. First make sure that the scheduled task is running either as SA or the Domain ID that the SQL Server is running under.
2 make sure that the ID that SQL is running as has access to the directory in question.
Jeff
MCDBA, MCSE+I
|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>

DTS Fails as JOB

Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
PatrickPatrick
2 things. First make sure that the scheduled task is running either as SA o
r the Domain ID that the SQL Server is running under.
2 make sure that the ID that SQL is running as has access to the directory i
n question.
Jeff
MCDBA, MCSE+I|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>sql

DTS Fails as JOB

Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
PatrickPatric
2 things. First make sure that the scheduled task is running either as SA or the Domain ID that the SQL Server is running under
2 make sure that the ID that SQL is running as has access to the directory in question
Jef
MCDBA, MCSE+I|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>

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

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 for MSDE

I need to create a DTS Export job that can be imported to
MSDE via script. But I need to be able to alter the
destination server in the script.
When I use DTS wizard to create the job and create a script
from it. I do not see how I can change the destination
server within the script, because it looks to be encoded.
Any Ideas?
You will need to use the DTS editor in Enterprise Manager to create a global
variable for your server name and make that a parameter to dtsrun. Use the
dtsrunui utility to create the command line with the global variable
parameter. Then add a Dynamic Properties task to your package that sets the
server name from the global variable and make this task the first thing that
runs in your package. Using this method you can set the names of servers,
databases and other values in the package dynamically.
Jim
"Jahnathan Cain" <cainj@.ritchie.disa.mil> wrote in message
news:2a23901c465ea$02b2ee50$a401280a@.phx.gbl...
> I need to create a DTS Export job that can be imported to
> MSDE via script. But I need to be able to alter the
> destination server in the script.
> When I use DTS wizard to create the job and create a script
> from it. I do not see how I can change the destination
> server within the script, because it looks to be encoded.
> Any Ideas?

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 Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thanks
taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS Email Task Works - But not in JOB

I have a DTS package created in SQL Server 2000 that creates a table,
pumps it to excel then emails the spreadsheet to users. I do not have
exchange. I have Outlook 2003 and I have an email account set up in
that that users POP and SMTP server(s). I can create/send/receive
email using this account I have set up in outlook. I have added 3
people to the address book. These are the people that are being sent
the email via DTS. If I excecute the DTS package from the server or
from my laptop - works fine. If I schedule the DTS in a JOB it get a
MAPI logon error:
Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
MAPI Logon failed. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
9100 Error Detail Records: Error: -2147220352 (80040480);
Provider Error: 0 (0) Error st... Process Exit Code 1. The step
failed..
I'm not sure what I need to do to get this working. I assume it might
be permissions issue but I have no idea how to address it. Any tips
greatly appeciated since this is making me have to manually run a DTS
every morning *barf*.
Thankstaranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book. These are the people that are being sent
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
>
> I'm not sure what I need to do to get this working. I assume it might
> be permissions issue but I have no idea how to address it. Any tips
> greatly appeciated since this is making me have to manually run a DTS
> every morning *barf*.
> Thanks
>
You need to set the Outlook profile up under the context of the login
that the SQL Server service account is running under. Meaning, if the
SQL service is running as MYDOMAIN\SQLServerLogin, then you need to
login to the machine as MYDOMAIN\SQLServerLogin and create the Outlook
profile as that user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||taranbuckley@.hotmail.com wrote:
> I have a DTS package created in SQL Server 2000 that creates a table,
> pumps it to excel then emails the spreadsheet to users. I do not have
> exchange. I have Outlook 2003 and I have an email account set up in
> that that users POP and SMTP server(s). I can create/send/receive
> email using this account I have set up in outlook. I have added 3
> people to the address book.
> the email via DTS. If I excecute the DTS package from the server or
> from my laptop - works fine. If I schedule the DTS in a JOB it get a
> MAPI logon error:
> Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273:
> MAPI Logon failed. Error source: Microsoft Data Transformation
> Services (DTS) Package Help file: sqldts80.hlp Help context:
> 9100 Error Detail Records: Error: -2147220352 (80040480);
> Provider Error: 0 (0) Error st... Process Exit Code 1. The step
> failed..
There is an easier way to send email with SQL 2000. Have a look at
xp_smtp_sendmail (free) from SQLDev.net. With this extended stored
procedure you can send emails without the hassle of setting up a MAPI
profile.
This procedure is a blessing. Also the setup is easy and there is
plenty of documentation. Go to SQLDev.Net, and in less than 5 minutes
you'll be sending all the emails you want and you'll never miss the SQL
Mail nightmare.
Regards,
lucm

DTS cube processing from a non sa account

We are running a DTS package from a non-sa owned job and I am getting the following message:

A problem occurred while attempting to logon as the Windows user 'SQLAgentCmdExec': The parameter is incorrect.

When I set the Sql Agent proxy account to an windows administrator on the sql server it works fine.

However when the Sql Agent proxy account is not administrator I get the above error.

Please note that the Analysis Services is located on a different machine.

Thanks

LiorGrant admin privileges to the SQLAgent service account, which is required to carry on such tasks and to overcome this issue.|||Originally posted by Satya
Grant admin privileges to the SQLAgent service account, which is required to carry on such tasks and to overcome this issue.

Do you mean that the sqlagent stratup account should be windows sys admin?

Can I do it with less power priviliges?

Thanks

Lior|||You can do it, but you may have issues again if any of the jobs have to deal with admin tasks.

Its always better and recommended to keep SQL service accounts with Admin privileges on the box.|||Originally posted by Satya
You can do it, but you may have issues again if any of the jobs have to deal with admin tasks.

Its always better and recommended to keep SQL service accounts with Admin privileges on the box.

Hi,

Thanks again for your help.

Do you know what permissions are required for sql server to run cube processing that resides on a different machine.

Lior

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 Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?
On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.
|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?
|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.
|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1.What is the transaction isolation level of your web application?
2.What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>

DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identit
y
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:

>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1. What is the transaction isolation level of your web application?
2. What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Dave,
The transaction isolation level of your web application depends on your
coding. If you did not set transaction isolation level explicitly, by
default, it was READ COMMITTED.
Since there was no deadlock information in the error log, I think that this
issue should be related to lock escalation. I noticed that your source
table had about 0.5 million records and your DTS inserts batch size was
5000, it is very common that a large batch insert or update will cause
table level lock which is incompatible with shared lock. In this case, your
application could not read any data from the table until the bulk
insert/update finished.
I recommend that you schedule a time to run your DTS job when your web
application is not busy or has no workload.
You may also consider transfer the data incrementally, each time
transferring a few records to the destination table.
The methods can ensure that there is little impact to your application
performance.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

DTS Copy locks web

I built a simple DTS job to copy the contents of a table in a SQL Server
2000 database from one server to another.
The transformation is a simple column copy from source table to destination
table.
I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
Insert and Always Commit Final Batch (inerst batch size=5000).
The source table has about 0.5 million records
As soon as I start the DTS job, any web page with a database connection
freezes up (i.e. you cannnot load it into a browser)
I do not have the Table Lock option set in the DTS package.
Does anyone have any ideas why a copy package would lock up the application
like this?On Sat, 11 Aug 2007 08:44:51 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:
>I built a simple DTS job to copy the contents of a table in a SQL Server
>2000 database from one server to another.
>The transformation is a simple column copy from source table to destination
>table.
>I checked the DTS options: Use Fast Load, Keep Null Values , Enable Identity
>Insert and Always Commit Final Batch (inerst batch size=5000).
>The source table has about 0.5 million records
>As soon as I start the DTS job, any web page with a database connection
>freezes up (i.e. you cannnot load it into a browser)
>I do not have the Table Lock option set in the DTS package.
>Does anyone have any ideas why a copy package would lock up the application
>like this?
Is the DTS package copying from or to the same table the pages are
accessing?
J.|||It copies from the table that the web page is accesssing.
But a copy is just a read.
Why would this apparently lock the table?|||On Sat, 11 Aug 2007 18:35:05 -0700, "Dave"
<davefrick@.newsgroup.nospam> wrote:
>It copies from the table that the web page is accesssing.
>But a copy is just a read.
>Why would this apparently lock the table?
Because the web page connection has an isolation level of repeatable
read or serializable. Yes, it's excessive caution, but I suspect it's
the answer.
J.|||Hi Dave,
I understand that when you executed your SQL 2000 DTS package in parallel
with your web application, all the web pages that used a database
connection froze up.
If I have misunderstood, please let me know.
From the issue appearance, it seemed that a deadlock was caused. To narrow
down this issue, I would like to collect more information from you:
1. What is the transaction isolation level of your web application?
2. What is the transaction isolation level of your DTS package?
Open your DTS package in Enterprise Manager, right click the panel in DTS
package designer, select "Package Properties", switch to the Advanced tab,
check the Transaction isolation level.
3. Refer to this article to enable the trace flag T1204 and T3605 so that
you can get more information in the error log.
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/kb/832524/en-us
After the above operation, run your DTS package again to reproduce your
issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
error logs for further research.
By default, the SQL error logs are located in the folder
%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
Look forward to your response and we are glad to work with you for further
research.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks Charles.
The DTS package resides on the local server. I do not see any errors in
either the ERRORLOG or SQLAGENT files.
I uncheck the "Use Transactions" box in the DTS package properties but I
still get the app locking up until I cancel the DTS operation.
Can you tell me where I check for the transation isolation level on thec web
server?
Thanks
Dave
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:0OayAjW3HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Dave,
> I understand that when you executed your SQL 2000 DTS package in parallel
> with your web application, all the web pages that used a database
> connection froze up.
> If I have misunderstood, please let me know.
> From the issue appearance, it seemed that a deadlock was caused. To narrow
> down this issue, I would like to collect more information from you:
> 1. What is the transaction isolation level of your web application?
> 2. What is the transaction isolation level of your DTS package?
> Open your DTS package in Enterprise Manager, right click the panel in DTS
> package designer, select "Package Properties", switch to the Advanced tab,
> check the Transaction isolation level.
> 3. Refer to this article to enable the trace flag T1204 and T3605 so that
> you can get more information in the error log.
> SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/kb/832524/en-us
> After the above operation, run your DTS package again to reproduce your
> issue. And then please mail me (changliw_at_microsoft_dot_com) the SQL
> error logs for further research.
> By default, the SQL error logs are located in the folder
> %ProgramFiles%\Microsoft SQL Server\MSSQL\LOG.
> Look forward to your response and we are glad to work with you for further
> research.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Dave,
The transaction isolation level of your web application depends on your
coding. If you did not set transaction isolation level explicitly, by
default, it was READ COMMITTED.
Since there was no deadlock information in the error log, I think that this
issue should be related to lock escalation. I noticed that your source
table had about 0.5 million records and your DTS inserts batch size was
5000, it is very common that a large batch insert or update will cause
table level lock which is incompatible with shared lock. In this case, your
application could not read any data from the table until the bulk
insert/update finished.
I recommend that you schedule a time to run your DTS job when your web
application is not busy or has no workload.
You may also consider transfer the data incrementally, each time
transferring a few records to the destination table.
The methods can ensure that there is little impact to your application
performance.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Dave,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
Thanks
Yes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:

>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>

DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
ThanksYes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:
>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>

DTS and permissions

Under what account does a DTS package or SQL Agent job run under?
Is it the Service Startup Account for SQL Agent?
A DTS package under a scheduled job needs to access a network share and I
need to know which account I should give share access permissions to.
ThanksYes...the service account for SQL Agent.
-Sue
On Tue, 1 Mar 2005 14:40:25 -0800, "Dave" <dave@.nospam.ru>
wrote:

>Under what account does a DTS package or SQL Agent job run under?
>Is it the Service Startup Account for SQL Agent?
>A DTS package under a scheduled job needs to access a network share and I
>need to know which account I should give share access permissions to.
>Thanks
>