Solved

t-sql query

Posted on 2014-01-27
4
377 Views
Last Modified: 2014-02-03
I’m using Sql Server 2008 R2.

I have a stored procedure that takes in 4 parameters. The stored procedure contains a query that uses Dynamic SQL.

My database is set toCOMPATIBILITY_LEVEL = 100

When I run my stored procedure it gives me the following message.


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, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. 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.


But if I set toCOMPATIBILITY_LEVEL = 80
on my database and then run the stored procedure again then the stored procedure works fine.

I don’t want to rewrite the query because it’s pretty complex.

So I thought maybe I could put a command  in my stored procedure, so when my stored procedure is run, this is the order of things that will occur.

1. Set toCOMPATIBILITY_LEVEL = 80
2. Run my Query.
3. set toCOMPATIBILITY_LEVEL = 100

Query 1
In this query, the query inside the comments called MAIN SELECT is just a sample query. In this example it's just a place holder for where I real query goes.
So when I write the query like this, it executes fine and I get my result set.

-- change compatibility_level
ALTER DATABASE [WebReports]
SET COMPATIBILITY_LEVEL = 80;
GO

DECLARE @Region varchar(50), @EmployeeID varchar(50), @LastName varchar(50),
SET @Region = 1
SET @EmployeeID = 4565
SET @LastName = 'Smith'

SELECT * FROM MYTESTTABLE 
WHERE Region = @Region AND EmployeeID = @EmployeeID AND LastName = @LastName


GO
-- change compatibility_level
ALTER DATABASE [WebReports]
SET COMPATIBILITY_LEVEL = 100;

Open in new window



Query 1 inside of Stored Procedure

So then I wanted to use query from Query 1 and create a stored procedure for it.
My code to create the sproc looks like this:

CREATE PROCEDURE [dbo].[sp2Report_Test1]
	(@Region varchar(50)
      ,@EmployeeID varchar(50)
      ,@LastName varchar(50)
      )
AS

BEGIN	

-- change compatibility_level to 80
ALTER DATABASE [WebReports]
SET COMPATIBILITY_LEVEL = 80;

declare @mSQL char(8000)

------ MAIN SELECT -----
Set @mSQL = 'SELECT * FROM MYTESTTABLE'
Set @mSQL = 'WHERE Region = @Region AND EmployeeID = @EmployeeID AND LastName = @LastName'
------------------------

--print(@mSQL)
EXEC(@mSQL)

-- change compatibility_level change it back to 100
ALTER DATABASE [WebReports]
SET COMPATIBILITY_LEVEL = 100;


END

Open in new window


But when I try to execute this stored procedure I still get the same error message I mentioned above.

Does anyone know why my query 1 works outside of the stored procedure when I just run that query in Sql Server Management Studio but it won't work in a stored procedure?
Are alter database statements allowed in a stored procedure?
How do I fix my sproc code?


Is this even a good strategy to use(change compatibility_level, run query, change, change compatibility_level back)?
0
Comment
Question by:maqskywalker
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39813113
you cannot alter db compatibility inside a stored procedure.

>I don’t want to rewrite the query because it’s pretty complex.
I fear that you only have those 2 options:
* put (and keep) the db in compatibility mode (80)
* rewrite your query
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 250 total points
ID: 39813118
Check this link to see if can solve your problem. Looks like you will have to re-write your query or change the compatibility level of your database or create and store procedure for change the compatibility level, execute your store procedure after that, run another procedure to change back the compatibility level.

Looks like you can not change the compatibility level in the store procedure because block the execute. When you run the store procedure, it will get the compatibility level of the database regarding if you change it on the SP.

http://www.sqladmintips.com/2012/06/sql-stored-procedure-to-change-database.html

http://www.mydigitallife.info/how-to-set-compatibility-level-for-sql-server-2008-or-2005-database/
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39813152
As well as what the other 2 experts have said that both hold true, I see a problem with the way you are creating your dynamic sql in that in the stored procedure code shown above you are actually overwriting the value of @mSQL variable so that the select statement will be missing from the query and only the where clause will be left.
Also it will not correctly include the values of the variable that you require.

Change lines 17 & 18 to :

Set @mSQL = 'SELECT * FROM MYTESTTABLE WHERE Region = ' + @Region + ' AND EmployeeID = ' + @EmployeeID + ' AND LastName = ' + @LastName

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39814159
I don't see any code that would cause the error message
"The query uses non-ANSI outer join operators ("*=" or "=*"). "...

Nor do I see anything that would be complex to re-write.

I can only assume therefore that the facts have been "simplified" for this question; but this inhibits our ability to answer fully.

Looks to me (based on limited information) that you must re-write the query to work with compatibility level 100
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now