0

I had the following SQL query:

select substring(PostOfficeBox,1,4) as 'Postcode'
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

I now am trying to set this up as a nightly job to run and email myself the results but have had problems when trying to convert this to an automated stored procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from AccountExtensionBase as a
JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.txt'

When I run this query it is saying 'AccountExtensionBase' is not a valid object but it is contained within the TestCRM database that my SSMS is set to. Any help would be greatly appreciated,

1
4

The query will be executed by sp_send_dbmail asynchronously, on a separate session that has msdb as current database. So you must use three-part names in your query:

@query = N'select substring(PostOfficeBox,1,4) as [Postcode]
...
from TestCRM.dbo.AccountExtensionBase as a
JOIN TestCRM.dbo.CustomerAddressBase as b ON a.AccountId = b.ParentId
...'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.