Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code
1. The step failed.
I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
>DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error:
18456 (4818) Error string: Login failed for user 'sa'. Error
source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.
sql

Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' passw
ord. Now all of my scheduled jobs are failing with the error:
DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18
456 (4818) Error string: Login failed for user 'sa'. Error source
: Microsoft OLE DB Provider for SQL Server Help file: Help cont
ext: 0. Process Exit Code
1. The step failed.
I strange thing is that the 'sa' password is not referenced in any connectio
n in any of my DTS Packages. Can anyone shed some light on this. I have hu
ndreds of scheduled jobs and I don't want to have to recreate them all. Tha
nks.The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' pass
word. Now all of my scheduled jobs are failing with the error:
>DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (48
18) Error string: Login failed for user 'sa'. Error source: Microsoft OL
E DB Provider for SQL Server Help file: Help context: 0. Process Exit
Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in an
y of my DTS Packages. Can anyone shed some light on this. I have hundreds of sched
uled jobs and I don't want to have to recreate them all. Thanks.|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error:
18456 (4818) Error string: Login failed for user 'sa'. Error
source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.

Change datatype from varchar to bigint not working

Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi

This should help you:
http://www.sqlteam.com/article/default-constraint-names

Casting Int to Varchar

I want to cast an In to varchar with a specific number of decimal places. So 10 will come across as 10.00.
Is there an easy way of doing this?Would you be llooking for something like this?

Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:

select cast(cast(id as decimal(5,2)) as varchar(10)) from table

Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.

Cast - differences between SQL2000 and SQL2005

I came across a problem when migrating from SQL2000 to SQL2005.

Table1 - large table with multiple columns including columns (X21 varchar(50), Start datetime, Complete datetime)

View1 - Only returns integer values into the dwpId column

SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate
FROM Table1
WHERE (X21 IS NOT NULL) AND (X21 LIKE '[0-9]%') AND
(ProjectID NOT LIKE '%_WI')

View2

SELECT cast(dwpId as int) as dwpId, startDate, endDate
FROM View1

In SQL2005, when selecting values from View2 an error is returned indicating that the statement failed when converting a varchar value to a data type int. In SQL2000, this same statement would return the appropriate rows from the view.

I dont think this is a problem with cast..

Problem is with your data, see the Select statement for Creation of view1,

If the data contains any non numerics also, It will select ex: '123A'

Modify your Select Query to fetch only Integers like this

SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate
FROM Table1
WHERE (X21 IS NOT NULL) AND ISNUMERIC(X21)=1 AND
(ProjectID NOT LIKE '%_WI')

|||

IsNumeric does not guarantee that the data can be converted to an integer. IsNumeric simply gurantees that the value can be converted to 'some type' of numeric data type. There is a trick that you can use with IsNumeric to assure that you have an integer.

The following can be copy/pasted to a query analyzer window and run so that you can see the difference.

Declare @.Temp Table(Data VarChar(20))

Insert Into @.Temp Values('1')

Insert Into @.Temp Values('1.3')

Insert Into @.Temp Values('1e4')

Insert Into @.Temp Values('2d3')

Insert Into @.Temp Values('$43.3')

Insert Into @.Temp Values('abc')

Select Data, IsNumeric(Data), IsNumeric(Data + '.0e0')

From @.Temp

Only the first value is an actual integer. By adding .0e0 to the string before checking for IsNumeric, you are guaranteed to have a valid integer.

I recommend changing your query. .. And IsNumeric(X21 + '.0e0') = 1 and ...

|||

Good Suggestion, IsNumeric returns 1, If the data contains valid integer, floating point number, money or decimal type

Change the Query ,according to the mastros suggestion

|||

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

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

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)

2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate

FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.