Once you have a user profile in SQL server 2008 that can send email saving something along the lines of the below as a job will attach a textfile containing the output of a query and send it to an email address in a rather tidy fashion.

DECLARE @Filename VARCHAR(50) = ‘Somefilename’ + REPLACE(CONVERT(VARCHAR,GETDATE(),103),’/',’-') + ‘.csv’

EXEC msdb.dbo.sp_send_dbmail
@execute_query_database= [NAMEOFDATABASE],
@profile_name = [USERPROFILENAME],
@recipients = ‘[email protected];[email protected]’,
@body = ‘Something light and fluffy for email body text’,
@sensitivity = ‘Confidential’,
@subject = ‘The subject of your email’,
@query = ‘SELECT * from SomeTable’,
@query_attachment_filename = @Filename,
@attach_query_result_as_file = 1,
@query_result_width=32500,
@query_result_separator=’,',
@query_result_no_padding = 1

@attach_query_result_as_file is a boolean that makes the output either an attachment or if you prefer set to false (default) to become the email body text

@query_result_width to the upper range of integer gets around wrapping text problems to make output nicer

@query_result_separator defines the column delimiters for your output

@query_result_no_padding trims white space from column output if you desire

So you want to update a single row in your table and then return the row identifier that was affected, here’s one way of going about it. Remember SCOPE_IDENTITY() is about inserting so as we are updating here we need a little something different.

My usual way to find the ID of a database to reliably filter a trace running in profiler is to look through the sysobjects table in master.

I found this way is far nicer select db_id(‘yourdatabasename’)

DOH!

Perhaps there’s a point to this, but not for me today – but maybe in future or for you today?

create table #t (a int)
create table #A (a int, b int)
create table #B (a int, b int, C int)
create table #C (a int, b int, c int, D int)
create table #D (a int, b int, c int, D int, e int)
create table #E (a int, b int,c int,d int,e int,f int)
insert into #t
select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10
– 100
insert into #A
select t.a,t2.a
from #t t cross join #t t2
–10000
insert into #C
select t.a,t.a,t2.a,t2.a from #A t cross join #A t2
– 1000000
insert into #E
select t.a,t.a,t2.a ,t2.a ,t2.a ,t2.a from #A t cross join #C t2

/*– clean up
drop table #t
drop table #A
drop table #B
drop table #C
drop table #D
drop table #E
*/

Today I found out something that I found out years ago, if you like me need to use multiple SQL servers but can’t remember things by IP address or would prefer not having to type login information every time you want to connect then use registered servers.

Registered servers allow you to easily check if a server is running, create groups of servers, use user-friendly names for your servers, and import/export your registered server groups.

To view and add a registered server, start up SQL Server Management Studio go to View > Registered Servers. Then in the Registered Servers pane, right-click Local Server Groups and click New Server Registration. Alternatively if you already have them open right now you can just right click the instance and select register then enter your friendly name… super easy!

Then just enter the server name, select the authentication, key in the User name and Password if necessary, and check Remember password.

Now each time you start up SQL Server Management Studio, your registered servers will be viewable in the Registered Servers pane. Simply double-click the server you wish to navigate and that server will be opened within the Object Explorer.

Now I need to find out how to make this the default view when opening SSMS..