Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SomeUser
Starting Member
3 Posts |
 Posted - 2011-04-21 : 15:44:24
|
| Working with a Visual Studio 2010 database project I've written a quer: UPDATE [s] SET [s].[ValueB] = [s].[ValueB] + [x].[ValueB] FROM [dbo].[Something] [s] INNER JOIN (SELECT [XmlInfo].[Entry].[value] ('@AttributeA', 'int') AS [ValueA], [XmlInfo].[Entry].[value] ('.', 'int') AS [ValueB] FROM @XmlData.[nodes] ('/Node') AS [XmlInfo] ([Entry])) as [x] ON [s].[ValueA] = [x].[ValueA]Now everything works fine. Management Studio runs it and does not say a word.Database Projects compiler however finds two ambiguities:> Warning SQL04151: Procedure: [dbo].[SP_XXX] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [@XmlData].[Entry]::[value], [dbo].[Something].[x]::[ValueA] or [XmlInfo].[Entry].[value].The warning is issued for accessing either `[x].[ValueA]` or `[x].[ValueB]`.I'm perplexed how to deal with it. It believes apparently there are two ways to reach the `value` either via `@XmlData` or via the alias `XmlInfo`.I'm also not sure why it thinks the alias `[x]` goes under `[dbo].[Something].[x]`.I'm asking you for any ideas how to make this warning go away. Anything to trick the compiler into believing that everything is fine.If it is in fact not fine I kindly ask you to point out to my mistake here... |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-22 : 08:07:48
|
I have not used VS DB projects, but I created a brand new project and tried this. It behaves exactly the way you described. I googled for this quite a bit, and didn't find anything at all that is relevant.Even something simple as update dbo.Something set dbo.Something.ValueA = dbo.Something.ValueB; results in the same complaint, although this seems to generate no warnings.with A as( select * from dbo.Something) update A set valueA = valueB; I googled for this for a while, but didn't find anything. So I don't have any useful suggestions to offer, other than to ask you to post back if you find the answer, so others can learn from it.Now I know why VS DB is not wildly popular. |
 |
|
|
SomeUser
Starting Member
3 Posts |
Posted - 2011-04-22 : 10:06:33
|
| Thank you for taking time to investigate.I've tried your approach being skeptical about the "WITH A AS" syntax since I remembered it gave me a problem before. This time was no different and I got an error directly in editor without even trying to compile:SQL80001: Incorrect syntax near 'AS'.Since an error is worse than previously issued warnings I will not be taking this approach.Therefore I can confirm already the second bug in Visual Studio Database Projects. I feel like I won't be recommending them to anybody anymore.For now I've decided to close the matter and leave the code as in my original message. It works fine, it issues no warnings or errors. BTW, if you use the "Validate SQL Syntax" options of VS DP when it connects to the real DB engine to do the job, you get not validation warnings either. So it must be a VS DP bug indeed.If I ever find a solution, I'll report back. |
|
|
|
SomeUser
Starting Member
3 Posts |
Posted - 2011-04-23 : 11:00:56
|
quote:
So I don't have any useful suggestions to offer, other than to ask you to post back if you find the answer, so others can learn from it
Here you go, a working solution that generates no warnings and is even simpler than the original one. Credits go to the guy named Hunchback from the MSDN forums.UPDATE SSET S.ValueB = S.ValueB + XmlInfo.[Entry].value('(./text())[1]', 'int')FROM dbo.Something AS S INNER JOIN @XmlData.[nodes]('/Node') AS XmlInfo([Entry]) ON S.ValueA = XmlInfo.[Entry].value('@AttributeA[1]', 'int');GO |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-23 : 12:04:36
|
Thanks SomeUser! (hm.. that didn't sound quite right. "Thanks Mr/Ms. Some User!").So it looks like VS DB is confusing between the xquery namespace and the "sql namespace"!Nonetheless, I share your sentiment about not recommending VS DB to anyone. |
|
|
|
|
|
|
|
|
Abmiguous reference in VS DB projects for SQL XML