Re: SMSS SP2 Script Database objects error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Fri, 1 Jun 2007 21:36:22 +0000 (UTC), Erland Sommarskog wrote:

Chuck Lathrope (computerguy_chuck@xxxxxxxxxxxxxxxx) writes:
I was trying to export all database objects to individual files as SQL
2005 Management studio SP2 allows, but got the following error:

Generate Script Progress
- Determining objects in database 'NameHost' that will be scripted.
(Error) Messages The query uses non-ANSI outer join operators ("*=" or
"=*"). To run this query without modification, please set the
compatibility 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, RIGHT OUTER
JOIN). In the future versions of SQL Server, non-ANSI join operators
will not be supported even in backward-compatibility modes. (Microsoft
SQL Server, Error: 4147)

Is this a known issue?

That's one I've never seen before. And while there are some bugs with
scripting, I find it difficult to believe that it would be that bad. My
initial reaction was that must be a piece of your own code that causes
the error, but I can't see why scripting would invoke your code - and if
it does, that's a bug.

I would suggest that you set up a Profiler traces that traces for
SQL:BatchStarting and SQL:BatchCompleted and the Error:Exception event,
to see exactly how the offending statement looks like. If nothing else,
you should be able to track down the object this happening too. Maybe
it's a very uncommon sort of object which could explain why this slipped.

Thanks Erland for the confirmation. I ran profiler and found a couple of
things:

It looks like the scripting wizard handles errors: 208 and 2601, but not
4147. The code it was running is:


SELECT
v.name AS [Name],
v.object_id AS [ID],
v.create_date AS [CreateDate],
v.modify_date AS [DateLastModified],
SCHEMA_NAME(v.schema_id) AS [Schema],
CAST(
case
when v.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = v.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTYEX(v.object_id,N'ExecIsAnsiNullsOn') AS bit) AS
[AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(v.object_id,N'ExecIsQuotedIdentOn') AS bit) AS
[QuotedIdentifierStatus],
CAST(OBJECTPROPERTYEX(v.object_id, N'IsSchemaBound') AS bit) AS
[IsSchemaBound],
CAST(CASE WHEN ISNULL(smv.definition, ssmv.definition) IS NULL THEN 1 ELSE
0 END AS bit) AS [IsEncrypted],
CAST(OBJECTPROPERTY(v.object_id, N'HasAfterTrigger') AS bit) AS
[HasAfterTrigger],
CAST(OBJECTPROPERTY(v.object_id, N'HasInsertTrigger') AS bit) AS
[HasInsertTrigger],
CAST(OBJECTPROPERTY(v.object_id, N'HasDeleteTrigger') AS bit) AS
[HasDeleteTrigger],
CAST(OBJECTPROPERTY(v.object_id, N'HasInsteadOfTrigger') AS bit) AS
[HasInsteadOfTrigger],
CAST(OBJECTPROPERTY(v.object_id, N'HasUpdateTrigger') AS bit) AS
[HasUpdateTrigger],
CAST(OBJECTPROPERTY(v.object_id, N'IsIndexed') AS bit) AS [HasIndex],
CAST(OBJECTPROPERTY(v.object_id, N'IsIndexable') AS bit) AS [IsIndexable],
v.has_opaque_metadata AS [ReturnsViewMetadata]
FROM
sys.all_views AS v
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id =
v.object_id
WHERE
(v.type = 'V')and(v.name=N'VW_GetProductListFull' and
SCHEMA_NAME(v.schema_id)=N'dbo')

I can reproduce, can you or anyone else?

Thanks,
Chuck
.

Relevant Pages

  • Re: SMSS SP2 Script Database objects error
    ... Determining objects in database 'NameHost' that will be scripted. ... query using ANSI outer join operators (LEFT OUTER JOIN, ... In the future versions of SQL Server, ... scripting, I find it difficult to believe that it would be that bad. ...
    (microsoft.public.sqlserver.tools)
  • Re: SMSS SP2 Script Database objects error
    ... Determining objects in database 'NameHost' that will be scripted. ... query using ANSI outer join operators (LEFT OUTER JOIN, ... scripting, I find it difficult to believe that it would be that bad. ... I would suggest that you set up a Profiler traces that traces for ...
    (microsoft.public.sqlserver.tools)
  • Re: Pipe Delimited files with headers
    ... but if you're happy with scripting and text ... Here's a sample VBScript that shifts data from a text file into a table ... 'a table in an MDB database without opening Access ... Dim oTDef 'As DAO.TableDef ...
    (microsoft.public.access.externaldata)
  • why should i use tcl?
    ... i'd like to ask about tcl. ... while database and communication may be left common to all ... this requirement makes the use of embeddable scripting language the best ... the sripting language i'd like to use should fit some requirements. ...
    (comp.lang.tcl)
  • Re: Registry deletions
    ... Database Developer ... Someone who looks after 10,000 PCs knows about scripting, so why post here? ... Someone who does not know about scripting could not effectively look after 10,000 PCs. ... Time to align your skills with your job requirements? ...
    (microsoft.public.windowsxp.general)