Search

VS DBPro - Four-part Table Referencing at Odds with SQL Parser by Geoff Adcock

Closed
as Fixed Help for as Fixed

0
Sign in to vote
0
Sign in to vote
Sign in
 to vote
Type: Bug
ID: 279363
Opened: 5/31/2007 2:11:39 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
VSDBPro resolves cross-databse references using 4-part table references. However, this causes a syntax exception when applied to table ALTER statements, as these are not permitted across server. For example, I have a stored procedure containing an ALTER statement to a table on a different database, though on the same server; I still have to make a 4-part reference to the table to enable DBPro to resolve the reference, but this then causes an incorrect sysntax exception.
Details (expand)
Product Language
English

Version

Visual Studio 2005 (All Products and Editions) Service Pack 1
Operating System
Windows XP Professional
Operating System Language
English
Steps to Reproduce
Create two SQL Server 2005 projects in VS DBPro, ProjectA and ProjectB.

Set ProjectA up to reference ProjectB.

Create a table, Table1 in ProjectB. Create a trigger, tr_TestTrigger on Table1

Create a stored procedure in ProjectA (substituting variable names) containing the following statement:

ALTER TABLE [$(testBServer)].[$(testBDatabase)].[dbo].[Table1]
ENABLE TRIGGER tr_TestTrigger


See attached solution.



Actual Results
TSD2010

If you remove "[$(testBServer)]." from the table refernce in the stored procedure, you get
TSD3012 and TSD3025
Expected Results
No exception - warnings or errors
TAP Code (if applicable)
 
      You can indicate your satisfaction with how Microsoft handled this issue by completing this quick 3 question survey. [Details]

 

File Attachments
1 attachments
ReallySmall_CrossDatabaseTriggerReferences.zip
Sign in to post a comment.
Posted by Microsoft on 5/31/2007 at 2:38 AM
Thank you for your feedback. We are currently investigating. If this issue is urgent, please call support directly (see http://support.microsoft.com). Thank you, Visual Studio Product Team.
Posted by Microsoft on 5/31/2007 at 3:01 AM
Thanks for your feedback. We have reproduced this bug on Win2003 SP2 and VSTS2005 SP1, and we are sending this bug to the appropriate group within the Visual Studio Product Team for triage and resolution. Thank you, Visual Studio Product Team.
Posted by Geoff Adcock on 5/31/2007 at 8:15 AM
I have now observed this same behaviour with SELECT INTO statements
Posted by Microsoft on 6/7/2007 at 12:31 PM
We don't require you to use 4 part names. When creating the database reference, you can leave the server part out of the reference definition and only specify the database part. When you do this, we'll allow you to use 3 part names. That should solve your problem. Please let us know if it does not.
Posted by Geoff Adcock on 6/8/2007 at 1:10 AM
I'm not sure what you're saying here, it sounds asthough this is current behaviour (Status=By Design), which isn't borne out by my experience (see attached project) - 3 part names are not resolved and produce a warning.

However, if you are saying you will fix this, that's great, this would resolve my issues. Would you alter the status to clarify this?

Regards,

Geoff
Posted by Microsoft on 6/8/2007 at 4:23 PM
I'm sorry, I think I misunderstood what the exact problem was. I thought you were just trying to get rid of the errors in the first line of the ALTER TABLE statement. By only using 3 part names in alter table statement, you can get rid of the errors, but are left with one warning. For example, with this statement:

CREATE PROCEDURE [dbo].[usp_EnableTriggerAcrossDatabase_Errors]
AS
     ALTER TABLE [$(testBDatabase)].[dbo].[Table1]
         ENABLE TRIGGER tr_TestTrigger
RETURN 0;

This statement will still result in one warning, 3012, because we are not inferring the database name when trying to resolve tr_TestTrigger. That is a bug. We will look into fixing this in a future release. Unfortunately, for now you will have to live with the warning. I'll re-activate this bug and make sure it gets fixed.
© 2010 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement