x

How to disable compiling in 2012

Hello All, I am having 2 environments belonging to 2 similar products. Since the products are similar, the code being maintained is the same. In one environment I have 2 databases and in the other I have either 3 or 4 DBs (based on some business settings).

Illustration:

Env1 DB : CommonDB, AccountingDB

Env2 DB : CommonDB, AccountingDB And MirrorDB

The common code checks for the existence of the database in the sysdatabases DB and if that condition passes will the manipulations be done on the (non-mandatory) Database.

The code seems to be working until the SQL Server version 2008-R2. But in case of SQL Server 2012, though the code does not get into the 'if' condition, it throws an error message saying - "Invalid Database ".

So my question is - is there some setting which would make it behave the same way as the version 2008, that it would not throw a compilation error?

Would really appreciate if anybody could guide me the right way.

Thanks Abhilash.KS

more ▼

asked Jul 17, 2013 at 12:36 PM in Default

avatar image

Abhilash.KS
0 1 1 2

Can you edit the question to include the code? Hard to know what to say without knowing how you're doing the check.

Jul 17, 2013 at 01:08 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I hesitate to recommend this for several reasons, but if you use dynamic SQL the server is incapable of compiling or attempting to validate that part of the code until it reaches the exec or sp_executesql to actually execute it.

If I understand what you want correctly, switching those portions of the code to dynamic sql will avoid your error.

more ▼

answered Jul 17, 2013 at 09:14 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

Thanks Timothy. Although using a dynamic query has temporarily solved the issue, I too am not too keen about.

What's bothering me is that why would the same SQL be treated as an error in version 2012 and not in the prior version!

My script:

If Exists( Select 1 From CommonDB.dbo.Settings Where Type = 1)

Begin

    If Not exists (Select 1 from MirrorDB.dbo.SysColumns where name = 'LastPositionReconcileDate' and id = object_id('MirrorDB.dbo.SACH'))

    Begin

           Alter Table MirrorDB.dbo.SACH

           Add LastPositionReconcileDate smalldatetime NULL

    End


End

-Abhi

Jul 18, 2013 at 12:18 PM Abhilash.KS
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402

asked: Jul 17, 2013 at 12:36 PM

Seen: 616 times

Last Updated: Jul 18, 2013 at 12:18 PM

Copyright 2017 Redgate Software. Privacy Policy