SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

29-Sep-1111 Leave a comment

ERROR:
SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

SOLUTION
Add the Database Name into the @query

BACKGROUND
This may be because of the default database of the account which executes the SQL Agent job.

EXAMPLE
If @query does not include the database name (three-part naming), it is possible that although the EXISTS may fire, the @query does not.
To solve add the Database Name into the @query.

BEGIN TRY

IF EXISTS(SELECT * FROM Production.vAuditSOCDBTable)
  BEGIN
   DECLARE @MyBody nvarchar(100)
   SET @MyBody = ‘<message>’ + char(13) + char(10)

   EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘<>’,
    @recipients = ”,
    @blind_copy_recipients = ”,
    @query = ‘SELECT * FROM <databasename>..’,
    –or possibly @execute_query_database = ‘databasename’,
    @subject = ‘subject’,
    –@attach_query_result_as_file = 1 ,
    @body = @MyBody;

   PRINT ‘message’
  END
 ELSE
  PRINT ‘nothing in audit’

END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]
 PRINT ‘FAILED’
END CATCH

Filed under Errors, SQL Server Tagged with , ,

Calculate Code Metrics – Troubleshooting

26-Sep-1111 Leave a comment

Project A references B
Code metrics work fine for B. Project A builds OK in Visual Studio. However “Calculate Code Metrics” on project produces error.

Message: An error occurred while calculating code metrics for target file ‘<My Assembly1 path>’ in project <My Assembly1>. The following error was encountered while reading module ‘<My Assembly2>’: Could not resolve member reference: <My Assembly3>.Models.IModel::get_FunctionTable.

SOLVED
It is possible that Code Metrics is using a different reference to Visual Studio. In my particular case my “Common Assemblies” folder had an older reference for Project B. By deleteing the reference from Common Assemblies, Code Metrics was forced to use the more up to date Project Reference, and then worked.

Filed under Errors, Visual Studio Tagged with ,

SQL Server Table Fragmentation

22-Sep-1111 Leave a comment

USE AdventureWorks;
GO
DBCC SHOWCONTIG (‘HumanResources.Employee’)
GO

DBCC SHOWCONTIG scanning ‘Employee’ table…
Table: ‘Employee’ (869578136); index ID: 1, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 7
– Extents Scanned…………………………: 3
– Extent Switches…………………………: 2
– Avg. Pages per Extent……………………: 2.3
– Scan Density [Best Count:Actual Count]…….: 33.33% [1:3]
– Logical Scan Fragmentation ………………: 14.29%
– Extent Scan Fragmentation ……………….: 33.33%
– Avg. Bytes Free per Page…………………: 172.6
– Avg. Page Density (full)…………………: 97.87%

  • sys.dm_db_index_physical_stats DMV example:

USE AdventureWorks
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’), OBJECT_ID(‘HumanResources.Employee’), NULL, NULL, NULL);

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

As a guideline from above

 

avg_fragmentation_in_percent value Corrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

 

Filed under SQL Server

1.Do you have a consistent .NET Solution Structure?

15-Sep-1111 Leave a comment

See http://rules.ssw.com.au/SoftwareDevelopment/RulesToBetterDotNETProjects/Pages/default.aspx

While I like many projects and it solves some problems, it also causes others for instance if changing the connection string at run-time this must be done in all projects.

Filed under Design

Computing Frustrations – Annoyances

15-Sep-1111 Leave a comment

Written to see if this presents any ways of reducing frustration
List is:

  1. Computerstaking a long time to turn on
  2. Always updating whether you want to do so now or not
  3. You are working on one job when the computer/software another problem intervenes nothing to do with this work

Things that help:

  1. Blog solutions to problems for future reference
  2. Do things which are satisfying
    • Work on code generation
    • Use code generation
    • DRY – make something re-useable
  3. Satisfaction is a function of expectation

 

Filed under Troubleshooting

LINQ errors

14-Sep-1111 Leave a comment

Error:

  • ‘First’ is not a member of ‘System.Data.EnumerableRowCollection(Of System.Data.DataRow)
  • “Definition of method ‘GroupBy’ is not accessible in this context”
  • ‘ToList’ is not a member of ‘System.Linq.Iqueryable(Of T)’

Solution:
Imports System.Linq
Also ensure that the DataSet is up to date. Use Run Custom Tool on the DataSet to ensure this.
Then ensure there is a reference to “System.Data.DataSetExtensions”.

See MSDN “How to Create a LINQ Project”
http://msdn.microsoft.com/en-us/library/bb546156.aspx

See MSDN 101 LINQ Samples
http://msdn.microsoft.com/en-us/vbasic/bb688088

Filed under ADO.Net, Errors Tagged with ,

Windows XP icons all hi-lighted AND Office 2003 loses color

13-Sep-1111 Leave a comment

My NVidia Control Panel was set for dual monitors. However one was detached, so it seemed to have switched it to Main monitor name + TV.
Changed back to single monitor and both of the above problems disappeared

Other sites have different answers for the Windows XP icons all hi-lighted. This was not the issue in my case.

Filed under Computers, MS Office, Troubleshooting

Windows 7 SDK – Hilo

12-Sep-1111 Leave a comment

Cool sample http://code.msdn.microsoft.com/Hilo-0e6f10bf
http://msdn.microsoft.com/en-us/library/ff708696%28v=MSDN.10%29.aspx

Initially had error “cmd.exe exited with code 9009”
The code.msdn page where Hilo is in Q+A had this solution:

Hey, Try to open Properties for Annotator (right click) and select Configuration Properties –> General. Then set Platform Toolset to “Windows7.1SDK” (need to install Windows SDK 7.1 first). Good Luck
 
Then I had a message saying that the SDK was not installed. It seems that even though Windows SDK is installed with Visual Studio, I needed to install v7.1 to get Hilo sample working. The SDK did not install completely first time. But on trying to install it again, it suggested it was installed, did I want to Repair it, which I did, this seemed to work and Hilo then built and ran.

Filed under C++, Errors Tagged with

SSMS 2008 R2 is losing Intellisense after installing Visual Studio 2010 SP1

09-Sep-1111 Leave a comment

http://connect.microsoft.com/SQLServer/feedback/details/650569/ssms-2008-r2-is-losing-intellisense-after-installing-visual-studio-2010-sp1

Apply cumulative update to SQL Server 2008 R2

http://support.microsoft.com/kb/981356

 

Filed under SQL Server, Troubleshooting, Visual Studio

SQL Server PowerPivot

09-Sep-1111 Leave a comment

Installation seemed easy enough – If Excel 2010 32-bit edition as you should, then need 32-bit x86 version of PowerPivot

Import

If a table has a timestamp (binary) data then exclude that otherwise you get an error

Group by date

So far this has been disabled for me

How do I … Import more tables using an Existing Connection

At first glance “Home” tab, section “Get External Data” is the only place to get new tables from. However it is not possible to use an existing connection from here, so I landed up with several connections 1,2,3 to the same SQL Server. To add a new import to an existing connection:

Design tab > Existing Connections > Open or Double click the connection you want to get a new import from

How do I … Refresh after a Schema Change

Design Tab > Table Properties
Use Switch to “Table Preview” or “Query Editor” to validate

Insert Pivot Chart

This puts the Data for Sheet 1 Chart 1 on a different tab.
To move it to the same tab as the chart use “PivotTable Tools” > Move PivotTable

Trouble Shoot every sum as same number – no apparent grouping

I had WO -> Reject Card on WO -> Reject Detail on WO
I found this created no proper grouping, so sum was same number for every group. Changed to:
WO – > Reject Card
and
WO -> Reject Detail
Problem solved

Sort by Date workaround

Sorting by date does not work, to workaround this create a Computed/Calculated column and use this:
= Year([WorkOrder!CompletionDate) * 100 + Month([WorkOrder]![CompletionDate]
YearMonth = Date(Year([WorkOrder!CompletionDate), Month([WorkOrder]![CompletionDate], 1)
YearMonthSort = Format(YearMonth, “yyyy-mm”)

 

Error 1

“Could not add the field “<field>” to the PivotTable.  Please ensure the field exists and is calculated, and try refreshing the PivotTable.”

Solution 1

Close the file and then open it again

 

Possible improvements for Microsoft

  1. If an import fails you should be able to go back to the previous step, rather than having to start again
  2. On Diagram View good to have zoom, but why is minimum 100 %
  3. The table import wizard should be resizable
  4. Make sort by date work

Filed under SQL PowerPivot

← Older posts