Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
--
David Portas
SQL Server MVP
--

No Transaction Log Backup after installing SP1

Hello,
after installing SP1 for SQL Server 2005 the sql agent cant execute the
existing management job to backup the transaction log. even if the job is
created new by deleting it in the maintanance plans and recreate it the sql
agent job can not be executed (nor manually or scheduled) the job is always
interrupted in step 2 'Execute Job' with the error "The package execution
failed. The step failed."
Has anyone else an idea to resolve this problem or should i uninstall sp1
(=reinstall sql)
ThanksThe question is WHY is the step failing. There should be extended error
messages in the log files that are generated for the job execution. What
are those messages saying?
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sorcerer" <Sorcerer@.discussions.microsoft.com> wrote in message
news:52149920-7656-4419-A9C2-CA4A641EF232@.microsoft.com...
> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the
> sql
> agent job can not be executed (nor manually or scheduled) the job is
> always
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>|||Hello,
this is what is written to the log:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,0,RMCNAVI1\INSTANZ1,TransactionLog Sicherung,(Job
outcome),,The job failed. The Job was invoked by Schedule 9 (20). The last
step to run was step 1 (Subplan).,00:00:11,0,0,,,,0
04/21/2006 08:20:00,TransactionLog
Sicherung,Error,1,RMCNAVI1\INSTANZ1,TransactionLog
Sicherung,Subplan,,Executed as user: RMC-DE\cluster. The package execution
failed. The step failed.,00:00:11,0,0,,,,0
To be more specific to the problem:
the sp1 was installed on a cluster system (windows 2003 x64 R2 and sql 2005
x64) with three instances (two clustered and one local). after installing the
sp1 the agent job could not be executed any longer. on a test system (not
clustered, only one instance) the sp1 was installed and there is no problem
with the agent jobs...|||my fault: forgot to make an initial full backup of the database.
sorry for that.
"Sorcerer" wrote:
> Hello,
> after installing SP1 for SQL Server 2005 the sql agent cant execute the
> existing management job to backup the transaction log. even if the job is
> created new by deleting it in the maintanance plans and recreate it the sql
> agent job can not be executed (nor manually or scheduled) the job is always
> interrupted in step 2 'Execute Job' with the error "The package execution
> failed. The step failed."
> Has anyone else an idea to resolve this problem or should i uninstall sp1
> (=reinstall sql)
> Thanks
>