Red Gate forums :: View topic - Showing incorrectly that objects are invalid
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

Showing incorrectly that objects are invalid

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Tue Apr 22, 2014 3:19 pm    Post subject: Showing incorrectly that objects are invalid Reply with quote

Within SSMS 2008, SQL Prompt is showing that objects, including the database, are invalid objects even when SSMS shows at the top and bottom of the screen that I am in that database and the query runs.

This does not occur on all my servers, but I do have permissions to run SELECT statements on all my servers.
_________________
Larry M
Cleveland, OH USA
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Wed Apr 23, 2014 11:50 am    Post subject: Reply with quote

Hi Larry,

SQL Prompt requires a few more permissions for Find Invalid Objects to work correctly:

  • Select
  • Execute
  • View Definition
  • Set ShowPlan
  • Set NoExec
  • Create Trigger
  • Create Procedure
  • Create View
  • Create Function

Please let me know if this helps! If you have any more questions let me know.

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Wed Apr 23, 2014 12:36 pm    Post subject: Reply with quote

David,
I am the DBA for the organization. I am the sysadm on all our database servers. I will look at the permissions at a more granular level, but as a beta tester for your product, I knew most of what you stated. I would not have posted this if I thought something else wasn't going on.
Larry
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Wed Apr 23, 2014 2:16 pm    Post subject: Reply with quote

Hi Larry,

I think I may have misunderstood your post! There's a feature in SQL Prompt called Find Invalid Objects.

Are you saying that you're writing a select statement in the query window, but Prompt is not providing suggestions for you?

Are these newly created objects (created while you've had SSMS open)? If so, have you used Refresh Suggestions in the Prompt menu?

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Tue Apr 29, 2014 3:32 pm    Post subject: Reply with quote

That is correct. It happened to me again this morning. In SSMS, on the status line it shows the correct server and database, but SQLPrompt tells me that the identical database is not found in sysdatabases and then the entire sets of objects in the select statement are marked, but the query runs fine.

Sorry for the slow response,
_________________
Larry M
Cleveland, OH USA
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Tue Apr 29, 2014 3:52 pm    Post subject: Reply with quote

Hi Larry,

Would it be possible to have a remote session with you so we can see this issue in action?

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Tue Apr 29, 2014 4:10 pm    Post subject: Reply with quote

Here are some free times for this week;
Today from 3:00 - 4:00 PM EDT
Tomorrow most of the day.
Thurs. 1:00-4:00 PM EDT
Friday 9:00-11:30 AM EDT
_________________
Larry M
Cleveland, OH USA
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Tue Apr 29, 2014 4:30 pm    Post subject: Reply with quote

Hi Larry,

Would it be OK to do it early tomorrow morning for you? Let me know when we can start and I'll send over a meeting invite.

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Tue Apr 29, 2014 5:28 pm    Post subject: Reply with quote

That's fine. And for upfrontness, I just checked. I am on version 6.3.0.345 and I am running SQL Search 2.0.1.8
_________________
Larry M
Cleveland, OH USA
Back to top
View user's profile Send private message
meklembl



Joined: 24 Aug 2007
Posts: 93
Location: Solon, OH, USA

PostPosted: Tue Apr 29, 2014 5:28 pm    Post subject: Reply with quote

Email address is lmeklemburg@intellicorp.net
_________________
Larry M
Cleveland, OH USA
Back to top
View user's profile Send private message
sgtmango333



Joined: 12 May 2014
Posts: 4

PostPosted: Mon May 12, 2014 3:20 pm    Post subject: Reply with quote

Larry, I happen to be working on a project to change the collation of a database and fix references to that database in an uncollated DB. I've found that SQL Prompt is sensitive to those collation differences and can show an object as invalid on the uncollated server due to casing. The query will run fine, but the objects show up as invalid.

It seems a little inconsistent, but I didn't dig real deep and my trial has expired. So I can't really duplicate. Just and FYI and something to look at.
Back to top
View user's profile Send private message
David Priddle



Joined: 28 Jan 2014
Posts: 36
Location: Red Gate Software, Cambridge

PostPosted: Mon May 12, 2014 3:34 pm    Post subject: Reply with quote

Hi sgtmango333,

I'm one of the developers on SQL Prompt. I had a call with Larry a couple of weeks back and it looked like the problem was unlikely to us.

How are the objects coming up as invalid? Through "Find Invalid Objects" or in the query window? If they are being underlined in red in the query window, that is SSMS's behaviour.

If you see the collation issues again, please let us know!

Best regards,

David
Back to top
View user's profile Send private message Send e-mail
sgtmango333



Joined: 12 May 2014
Posts: 4

PostPosted: Mon May 12, 2014 8:04 pm    Post subject: Reply with quote

Thanks David. Yes it was the red underlines, so probably SSMS and not SQL Prompt.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group