Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

t-sql query

Posted on 2014-01-27
4
383 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 143

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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