Problem scheduling DTS packages

I am getting the following error whenever I schedule a new DTS package:
SQL Server does not exist or access denied
This is new, all of my older scheduled packages run without any problem.
This just started occuring during the past week and is occuring on all of my
SQL Servers (7 and 2000).
Any ideas?John
Well , Perhaps you are created the DTS package on your computer and when
you execute the package it runs under account of your computer (SQL Agent
service)
By default, the owner of that service is LocalSystem (with has many
Administrator-style permission, but *NO* access to any network resources).
So, change the account of that service to a Domain Admin account (or other
suitable account with permissions on both computers (server and yours)
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> I am getting the following error whenever I schedule a new DTS package:
> SQL Server does not exist or access denied
> This is new, all of my older scheduled packages run without any problem.
> This just started occuring during the past week and is occuring on all of
my
> SQL Servers (7 and 2000).
> Any ideas?
>|||I'm just curious why it was working, I haven't changed anything and now it
does not. The network guys applied some security patches about the time
this started.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eERZIZVnDHA.3612@.TK2MSFTNGP11.phx.gbl...
> John
> Well , Perhaps you are created the DTS package on your computer and when
> you execute the package it runs under account of your computer (SQL Agent
> service)
> By default, the owner of that service is LocalSystem (with has many
> Administrator-style permission, but *NO* access to any network resources).
> So, change the account of that service to a Domain Admin account (or other
> suitable account with permissions on both computers (server and yours)
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I am getting the following error whenever I schedule a new DTS package:
> >
> > SQL Server does not exist or access denied
> >
> > This is new, all of my older scheduled packages run without any problem.
> > This just started occuring during the past week and is occuring on all
of
> my
> > SQL Servers (7 and 2000).
> >
> > Any ideas?
> >
> >
>

Problem scheduling a package

Hi,
I have a package saved in the msdb database. When I right click on it and execute, it works fine through the dts utility program.

When I schedule it, it always errors out with the message "The command line parameters are invalid".

The package is password protected. The command line in the dts utility reads: /DTS "\MSDB\<package name>" /SERVER aawork2/DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I have to enter my password to view this.

In the job step, if I click the command line, it reads: /DTS "/MSDB/<package name>" /SERVER aawork2/DECRYPT <password> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I suspect its something to do with the password protection and the fact the job runs it under the service account.

Thanks in advance for any help.

Asim.
No, the problem (most likely) is that the package path has forward slashes instead of backward slashes. Try changing them to back slashses. Also put a space between the server name and the /DECRYPT option. These were bugs that should be fixed by RTM.

Thanks,
Matt|||Matt, it works. Thanks! This was very helpful.

Asim.

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed
."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/defaul...kb;en-us;818899
http://support.microsoft.com/defaul...kb;en-us;829444
http://support.microsoft.com/defaul...kb;en-us;812995
http://support.microsoft.com/defaul...kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step fail
ed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SC
SC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default...b;en-us;818899
http://support.microsoft.com/default...b;en-us;829444
http://support.microsoft.com/default...b;en-us;812995
http://support.microsoft.com/default...b;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
http://support.microsoft.com/default.aspx?scid=kb;en-us;829444
http://support.microsoft.com/default.aspx?scid=kb;en-us;812995
http://support.microsoft.com/default.aspx?scid=kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem Replication With DTS

I have transactional replication with DTS in SQL 2000.
In the initial snapshop i got de next error (trasnlate fron de spanish) " The process found invalid column date in the bcp file -path-".
I review the bcp file but i didn't found spacial characters. In order to discard DTS problems, the DTS had only the defult transformations that the SQL creates for it.
The problem does not appear when i don't use DTS with the same data and conditions.
Somebody has anay idea about this error?
Thank you.
what was the error number?
I am afraid your English is too poor for me to understand (and my Spanish is
too bad as well) what you are trying to do and what the problem is. Can you
post this in Spanish for me, and I'll have it translated into English and
then post back in Spanish?
cul era el nmero del error? Estoy asustado que su ingls es demasiado
pobre para que entienda lo que usted est intentando hacer y cules es el
problema. Puede usted fijar esto en espaol para m, y m voluntad para
tenerla traducida a ingls y despus para fijarla detrs en espaol?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Rsantacruz" <Rsantacruz@.discussions.microsoft.com> wrote in message
news:E6D5EDE0-1481-4FDA-94B6-9C21AA24EB4A@.microsoft.com...
> I have transactional replication with DTS in SQL 2000.
> In the initial snapshop i got de next error (trasnlate fron de spanish) "
The process found invalid column date in the bcp file -path-".
> I review the bcp file but i didn't found spacial characters. In order to
discard DTS problems, the DTS had only the defult transformations that the
SQL creates for it.
> The problem does not appear when i don't use DTS with the same data and
conditions.
> Somebody has anay idea about this error?
> Thank you.
|||Thank you for your time Hilary and sorry for my English.
I have the next Transactional replication problems:
1) I performed transactional replication to Oracle subscriber.
The subscriber was configured using Micrososft OLE DB provider for Oracle, because I need to use DTS to transform the data before applying it to Oracle.
In the initial snatshop I got error (windows error) with distrib.exe:
The instruction at '<address>' referenced memory at '<address>'.
The memory could not be "read"
Click OK to terminate the program.
This problem appears with a table with a few rows. The only that I saw in the bcp file is that, there is some char(255) fields with blanks (like char(13), or returns in Word), when I removed some of this blanks from the bcp file and restart the synchro
nization the things go fine.
When I do the same replication of the same data to SQL subscriber it's fine too.
2) The second problem with other table that I got: having the same conditions as described previously
In the initial snatshop I got error (replication error)
"The process encountered invalid column data in bcp file -PATH-"
Not error number and nothing more in the error description.
And the same, when I saw the bcp file and cut some blanks in field (character field) the things go fine.
With SQL subscriber, it's fine too.
I did not use DTS in this case but the result is the same.
I reinstalled SQL SP3 and Win2000 SP4 and I got the same result.
I don't know if there is a some deficiency with OLE DB providers when you used them with replication.
If you know how to resolve this problem, please say me.
Thank yoo for your time, and sorry for this long post.

Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks..

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

Problem Reading .CSV file data from reporting services

Hi,

In our application we are using DTS to import data from .csv file. now they came up with a requirement to compare data in .csv is exact match with imported data in the table using a report.

i had hard time to implement this. we tried two options to do so..

1. reading .csv file with custom extensions and rendering it on report (problem is datasource file name was standard, where a i am planning to read it from a table).

2. We have a separate DTS to read data from .csv but when we are accessing it from reproting services i am getting "Multistep OLE DB access error".

Can some one guide me right way to implement my requirement.

Thanks,
-Geeks..

You can use the XML rendering extension to output just the data in the report. then use an Integration Services (formerly DTS) package to compare the input and output data.

Hope that helps,

-Lukasz

problem on importing datas from .csv file to sql 2005 database using DTS

Hello,

I'm running into an issue on importing datas from a .csv file to sql 2005 database.
All my datas has been imported successfully but the datas with doublequotes(") are imported along with additional double quotes.
For example, a cell has the data, iron rod - 34" and this has been imported as "iron rod - 34"""

Can anyone please help me to come out of this issue.

Thanks.

Raviraj Danasekaran

If you are using integration services wizard then set the text qualifier to " in the flat file source connection settings

problem olap process

Hi,
I have a cube in analysis manager. This cube is processed via a DTS in sql server 2000 with an incremental update.
The sql task finishes successfully but there is no data moreover in the cube.
So, I proccess manually the cube with the dialog box "process a cube" and I select Incremental Update : the step finishes successfully but there is still not data moreover!!
I have to process the cube with a full process each time!!
Why that doesn't work?? any ideas??There is no quick way to resolve but you may try review this http://www.databasejournal.com/features/mssql/article.php/1582491 link to troubleshoot the issue.

HTH

Problem looping DTS and BCP within a Stored procedure

All,

This is the scenerio

SP=Stored Procedure

I call SP1 which calls a DTS package.

DTS package calls SP2 and SP2 calls SP3 and SP 4 using an IF condition

SP3 has 3 BCP OUT commands after which it calls SP1 again

Now, the problem is that if I follow all the steps, then only 1 of the BCP command executes and the control exits out of SP3 and returns control to SP1.

If I ignore SP1 and DTS and run SP2 independently, then all BCP steps in SP3 are executed and control returns to SP2

I ma not sure if using DTS and BCP in SQL stored procedures might cause any problems

Appreciate your helpIt seems like your DTS is executing under a different security context than when you execute your SP3 from QA. Are you checking for errors after each BCP in your SP3? If you're calling your BCP using xp_cmdshell, - consider doing INSERT @.temp_tbl exec master.dbo.xp_cmdshell [....].

Problem ith DTS and delimited file with empty last column

Hello,

I'm not getting any response to this on the SQLDTS newsgroup, so I
thought that I would try here:

I just ran into this problem and I can't find any other mention of it
through Google. I have a text file that is comma-delimited. It also
uses double quotes as text identifiers. A new column has been added to
the file, but currently has no values. I would like to finish my
development so that when it does finally get some values, they will be
imported as well. The problem is, the last column does not show up in
DTS.

I can reproduce this problem easily enough... create a text file with
the following two lines in it:

1,"test",
2,"test2",

Now, create a new DTS package and add a text file connection. Point it
to the new file and go through the properties for the file. You will
notice that on the second screen where it displays the preview of the
data there are only two columns shown.

This does not happen if there is no text qualifier or if at least one
row has the final column value filled. Is there any way around this
problem?

Thanks!
-Tom.On Jan 18 2006, 11:44 am, "Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote
in news:1137602647.984522.221090@.g14g2000cwa.googlegr oups.com:

> I just ran into this problem and I can't find any other mention of it
> through Google. I have a text file that is comma-delimited. It also
> uses double quotes as text identifiers. A new column has been added to
> the file, but currently has no values. I would like to finish my
> development so that when it does finally get some values, they will be
> imported as well. The problem is, the last column does not show up in
> DTS.

As a kludgy workaround, you can try accessing the text file via ODBC rather
than DTS native text file provider. This will effectively use MS Jet Text
driver, which I believe does not have this problem. It will likely be
slower, and you will need to set up a DSN on both your workstation, for
development, and the server, for production runs of the DTS package.

--
remove a 9 to reply by email|||Thanks for the suggestion. I ended up appending a header record to the
file with a simple copy command in DOS. I had to make sure that the
column names were all "Col001", "Col002", etc. (and capitalization DID
matter) so that my existing DTS package would still work without trying
to remap all of the columns. Hopefully by next month they will actually
be populating the column with data, otherwise I will build this header
append into my package.

Thanks again,
-Tom.

Dimitri Furman wrote:
> As a kludgy workaround, you can try accessing the text file via ODBC rather
> than DTS native text file provider. This will effectively use MS Jet Text
> driver, which I believe does not have this problem. It will likely be
> slower, and you will need to set up a DSN on both your workstation, for
> development, and the server, for production runs of the DTS package.