Intellisense isn’t working? An IntelliSensible list of Common Causes

Having been a fan of SQL Server 2008's Intellisense feature, I was disappointed to find it not working on a new installation of SQL Server 2008 (SP1-10.0.2531.0). I checked the Intellisense options (Tools>Options>Text Editor>Transact-SQL>Intellisense ) and it was showing as being enabled. I reviewed the other common cause of failure which is being connected to a database with a lower compatibility level than SQL Server 2008 (10) but this wasn’t the case.

I had a dig around and found the option to refresh Intellisense ’s local cache (edit>Intellisense >Refresh Local Cache), which worked for me and Intellisense sprang into life.

Curiosity persisted though, and I found the following guide to common causes of Intellisense failure. Hope it saves you some time in your investigations…

 

From MSDN:

There are certain cases when the Intellisense options might not work as you expect.

Conditions That Affect Intellisense

The following conditions might affect the behavior of Intellisense :

• There is a code error above the cursor.
  If there is an incomplete statement or other coding error above the location of the insertion point, Intellisense may be unable to parse the code elements, and therefore will not work. You can comment out the applicable code to enable Intellisense again.
• The insertion point is inside a code comment.
  Intellisense options are not available when the insertion point is within a comment in your source file.
• The insertion point is inside a string literal.
  Intellisense options are not available when the insertion point is inside the quotation marks around a string literal, for example:
  WHERE FirstName LIKE 'Patri%|'
• The automatic options are turned off.
  Many Intellisense features work automatically by default, but you can disable any feature.
  Even when automatic statement completion is disabled, you can use an Intellisense feature. For more information, see How to: Modify Intellisense Options.

Database Engine Query Intellisense

The following issues apply to the Database Engine Query Editor:

• The Intellisense functionality of the Database Engine Query Editor does not support all Transact-SQL syntax elements. Parameter help does not support the parameters in some objects, such as extended stored procedures. For more information, see Transact-SQL Syntax Supported by Intellisense .
• Intellisense is only available when the Database Engine Query Editor is connected to an instance of the SQL Server 2008 Database Engine. Intellisense is not available when the Query Editor is connected to earlier versions of the Database Engine.
• Intellisense is turned off in the Database Engine Query Editor when the SQLCMD mode is set on.
• Intellisense functionality does not cover database objects created by another connection after your editor window connected to the database. You can choose one of these three mechanisms to refresh the cache of objects for your editor window:
• Select the Edit menu, select Intellisense , then select Refresh Local Cache.
• Use the CTRL+Shift+R keyboard shortcut.
• Disconnect your editor window from the instance of the Database Engine and reconnect.
• Completion lists do not include database objects for which you do not have permissions. Intellisense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.
• Completion lists might stop working if you lose the connection to the instance of the SQL Server Database Engine. Reconnect to the instance.

Common Forum Queries: Calculate age from the date of birth

Some questions reappear on forums more than others, my experience suggests none more so than how one should work out an individual’s age based on their date of birth.

I have been using the following logic within a procedure, as I didn’t require specific months and days as part of the returned result.



DECLARE @DOB as DateTime

Set @DOB='29 Jun 1980' -- Date Of Birth

select year(getdate())

- year(@DOB)

- case when month(@DOB) > month(getdate()) then 1

when month(@DOB) < month(getdate()) then 0 when day(@DOB) > day(getdate()) then 1

else 0

end as age



Well now the requirements have changed with the application now expecting the Month and Day part of the calculated age.

The following tsql will fulfil these new criteria:

DECLARE @date datetime,

@tmpdate datetime,

@years int,

@months int,

@days int

SELECT @date = '6/29/80'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN MONTH(@date) > MONTH(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years as Yr, @months as Mnths, @days as [Days]



This seems to do the trick but I’d be interested to hear from anyone with a different (better) way of producing the additional columns for the latter query.

Maintenance Plan clean up task - not working to plan

A colleague of mine who is getting to grips with SQL Server had an issue after creating and implementing a maintenance plan to manage backup files on his local SQL Server instance.


The problem was with the routine maintenance which should have been removing files with a bak extension that are older than two days. He was trying to emulate a plan from one of our staging servers and despite having entered all the details, albeit manually, the job would execute and apparently succeed but there would be no removal of files. And then this morning his hard drive stopped J


I had a look at the plan that had been created and performed a quick test and the issue was exactly as my colleague had reported it. And then it hit me! The value for File Extension had ‘.bak’, and the task was therefore looking for test..bak as it apparently appends its own '.' to the string.


Removing the extra full-stop (or period) resolved the problem, so the entry simply states 'bak' rather than '.bak'.


Having briefly researched (ok, googled) the issue, I note that others have faced similar problems and most of which were resolved by observing the steps below. Hope it saves you some time…

(As posted on http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24757.aspx by SQL Server MVP Jonathan Kehayias)


- Make sure that you have a '\' at the end of the path, so if the backups are in D:\SQLBackups use D:\SQLBackups\



- Make sure that the extension is bak and not .bak For whatever reason it doesn't like having the . before the bak.



- If you are deleting trn files and bak files, have two separate jobs one to cleanup the bak files and one to clean up the trn files.



- If you backed up databases to separate folders, checking the 'Include first-level subfolders' check box is necessary.

Attaching a data file without the log file...

If you have a SQL data (mdf) file without the corresponding log (ldf) file, you can still attach the database to an instance. Use the sp_attach_single_file_db procedure as demonstrated below:



--To attach a .mdf file without its log file,


--use the following syntax



EXEC sp_attach_single_file_db @dbname = 'YourDBname',

@physname = N'd:\Database\Data\YourDBname.mdf'



For more details, including a list of arguments, please visit:

http://msdn.microsoft.com/en-us/library/ms174385.aspx

SERVERPROPERTY Function - How can I find the version,edition and build numbers of SQL Server?

I can find out the version, edition, and build of my SQL Server by using the SERVERPROPERTY function in SQL Server (the full list of options for this function exists at the foot of this post).



SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')



This will return results such as:

10.0.2531.0 SP1 Enterprise Edition


You can then reference against the build list in Steve Jones' post to see the version you are running

http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/


Full details of all the SERVERPROPERTY options can be found here:
http://msdn.microsoft.com/en-us/library/ms174396.aspx