October 20, 2011 at 5:16 pm
Hi All,
I have a problem that seems to be in the configuration of the PC's that we have running Management Studio.
It seems that not all of them process the SQL in a query the same way.
As an example I have a query that essentially does the following.
IF
Table T contains Field F
THEN
Update Table T
Set Field F = 'Some Changes'
ELSE
print 'Field F does not exist. No changes made.'
Table T does not contain Field F.
On some of the PC's the query completes and prints that Field F does not exist as I would expect but on some others it fails and returns the error
Msg 207, Level 16, State 1, Line 22
Invalid column name 'Field F'.
What is the option that is causing this difference in behaviour and how do I adjust it?
Thanks for any help you can provide.
Scott
October 20, 2011 at 5:27 pm
I have never seen such thing in T-SQL before.
Are you sure it's running properly on one of your PCs?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 20, 2011 at 5:41 pm
Sorry about that Andre, I guess that I wasn't really clear. The code example is just psudo-code for what the query does, it isn't the actual SQL that is run.
The issue isn't really the code more that it seems to be pre-parsed and fail to run on some machines and not on others.
Thanks for taking the time to look.
Scott
October 20, 2011 at 5:49 pm
Can you please post the actual query, DDL and sample data?
October 20, 2011 at 5:50 pm
I see. 🙂
It'd be nice to see the actual code though to see if there's a problem with different database compatibility levels.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 20, 2011 at 6:43 pm
OK I've included the SQL below but just to clarify I don't think that SQL is the problem, rather it is an issue with the configuration of Management Studio on the different PC's.
On each PC I connect to the same server open the same file and execute, in all cases the column TFN_ENCRYPT does not exist.
On some PC's the query completes and prints
Either column [TFN] or [TFN_ENCRYPT] does not exist. No action taken
On others it does not run and returns the error
Msg 207, Level 16, State 1, Line 22
Invalid column name 'TFN_ENCRYPT'.
It seems on some of the PC's it pre-validates all the SQL statement and stops due to TFN_ENCRYPT not being a valid column name before it even runs the query. On the other PC's it processes the query and doesn't error on the TFN_ENCRYPT column as it never tries to execute that section, instead it prints the message in the else section.
Hope that makes sense.
Scott
use [vsSit]
go
set ansi_nulls on
go
OPEN SYMMETRIC KEY vTfnKey DECRYPTION BY CERTIFICATE vsEncryptionCert
go
If exists (
select *
from [sys].[tables] a
,[sys].[columns] b
where [a].[object_id] = object_id(N'[plasup].[MEMBER_T]')
and [a].[name] = N'MEMBER_T'
and .[object_id] = [a].[object_id]
and .[name] = 'TFN_ENCRYPT'
) and exists (
select *
from [sys].[tables] a
,[sys].[columns] b
where [a].[object_id] = object_id(N'[plasup].[MEMBER_T]')
and [a].[name] = N'MEMBER_T'
and .[object_id] = [a].[object_id]
and .[name] = 'TFN'
)
begin
update [plasup].[MEMBER_T]
set TFN_ENCRYPT = ENCRYPTBYKEY(KEY_GUID('vTfnKey'),CONVERT(VARCHAR(11),TFN))
end
else
begin
print 'Either column [TFN] or [TFN_ENCRYPT] does not exist. No action taken'
end
go
CLOSE SYMMETRIC KEY vTfnKey
October 20, 2011 at 7:51 pm
This seems to be a parsing error and I don't think it has to do with SSMS as it's just a GUI (frontend) and it doesn't have control over query parsing errors.
I'd ask you the questions below:
1) Are both PC's executing the same SQL Server version (with equivalent service packs)? Sometimes a service pack introduces a new feature or changes an existing feature.
2) Are both databases set to the same compatibility level?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 20, 2011 at 8:22 pm
Andre, both PC's are connecting to the same database on the server and as far as I know they are both running the same version of Management Studio.
October 20, 2011 at 8:55 pm
Scott Levy (10/20/2011)
Andre, both PC's are connecting to the same database on the server and as far as I know they are both running the same version of Management Studio.
Sorry but without enough information about the SQL Server versions, editions, if both PC's are connecting to the same database on the same server or each one is a server on its own, if the database compatibility level is the same for both PC's and other information that could give us some hints we'd just be shooting in the dark here. 🙂
More technical information about your PC's setup would make things clearer.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
October 20, 2011 at 9:00 pm
Scott Levy (10/20/2011)
Andre, both PC's are connecting to the same database on the server and as far as I know they are both running the same version of Management Studio.
Are they both running at the same service pack level, etc? And are you absolutely sure that both are pointed to the same server AND database? I know... stupid questions. But I have to ask because I've seen this type of thing be overlooked before. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2011 at 9:15 pm
OK, thanks to everyone for your interest in this.
I'll collect the details on a sample of the PC's around here as there is a bit of a mix of OS's as we are testing Win7.
They are all defiantly pointing to the same DB on the same server though.
October 23, 2011 at 5:47 pm
I'm going to have to admit a mea culpa here as returning to the other PC I can't get it to behave any differently than mine.
In my defence it also has the owner of that PC stumped, he witnessed the inconsistent behaviour as well, we must have both missed something, or we are both delusional. 🙂
On a positive note I have worked out how to work around my issue by using dynamic SQL and storing the update code in a string before executing it.
Once again thanks to those who took the time to ponder my problem, it was very much appreciated.
Scott
November 9, 2011 at 11:10 pm
Just a last update on this if anyone is interested as I think I may have found the source of the inconsistency.
I believe that it was the local intellisense cache of the DB schema that management studio holds. I have been having issues with it flagging tables and fields as invalid when they are definitely there, a force refresh of the local cache and the issue goes away.
Scott
November 9, 2011 at 11:25 pm
Scott Levy (11/9/2011)
Just a last update on this if anyone is interested as I think I may have found the source of the inconsistency.I believe that it was the local intellisense cache of the DB schema that management studio holds. I have been having issues with it flagging tables and fields as invalid when they are definitely there, a force refresh of the local cache and the issue goes away.
Scott
I am not convinced. Intellisense helps developers to write SQL code in SSMS with ease. It’s IDE feature. It should not affect your query results.
November 10, 2011 at 3:14 am
Dev @ +91 973 913 6683 (11/9/2011)
I am not convinced. Intellisense helps developers to write SQL code in SSMS with ease. It’s IDE feature. It should not affect your query results.
I was able to reproduce the behaviour where intellisense would be outdated and sometimes would show syntax errors while editing the code.
But it would execute the commands with no erros as that issue doesn't affect the SQL parser. I can't see how a intellisense bug could influentiate the parser's behaviour.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply