owen983 (3/29/2010)

---

Running SQL 2005 Standard Edition, 64-bit, 28GB RAM assigned to SQL. We have 134 databases on this instance. Friday afternoon, around 5:20PM we started getting these error...

It's normally a very bad idea to programatically add a column as a matter of course and it's usually really bad if it's accomplished by a sproc that a user...

Michael Valentine Jones (3/17/2010)

---

Hide the update in an execute statement:

if not exists (select * from syscolumns

where id=object_id('Calendar') and name='RDATE')

BEGIN

ALTER TABLE Calendar add RDATE DATETIME

exec ('UPDATE calendar set RDATE = DATE...

The update cannot occur there.

IF NOT EXISTS (SELECT * FROM syscolumns

WHERE id=OBJECT_ID('Calendar') AND name='RDATE')

BEGIN

ALTER TABLE Calendar ADD RDATE DATETIME

END

ELSE

BEGIN

PRINT 'ALREADY THERE'

END

GO

UPDATE calendar SET RDATE = DATE + 36163

-- Add some...

I would consider creating an index - Something with ID, and startdate.

Also, reworked the last select.

SELECT *

FROM [dbo].[test_data] AS a

INNER JOIN...

what you are doing at the end is dynamic sql - http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

you will want to change:

SELECT COUNT(AField) AS RecCount

FROM @DBTbl

To something like this:

EXEC( 'SELECT COUNT(AField) AS RecCount FROM...

Per BOL:

[ @subject = ] 'subject'

Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

I bet...

I am sure there are other ways, but here is one:

on the affected machine:

Start -> run

Type "Services.msc" without the quotes

find your sql server service, right click, properties (or dbl click)

click...

I would check to see what context the sql server is running under - I think it is local user, and that user has changed its password recently, but did...

Okay - even though there seems to be enough correct answers, I wanted to post my results as well, because I took the time to do this 😀

I used 5...

I am not sure what you are trying to ask - perhaps some more detail would help.

sp_helpdb?

it lists the DBname, DBSize, and some other stats.

Another option is SQLCMD.

example

sqlcmd -S "servername" -Q "some query" -o \\Output_location\outputfile.txt

there are plenty other options.

Thanks for getting back to timely with the solution -

Since we are talking about next version things, I had to make one other modification to not backup snapshots:

in the...

I have started testing and implementing this process, and so far, I like it! The issue I currently am having is "The statement BACKUP LOG is not allowed while...