Recent content by tlbroadbent

An alternative. SELECT EmployeeID, EvalID, CompletionDate FROM tablename AS t WHERE CompletionDate = ( SELECT MAX(CompletionDate) FROM tablename WHERE EmployeeID = t.EmployeeID) Make sure you have an index on EmployeeID. For best performance index EmployeeID, CompletionDate. You should...

Remove the line feed in the BCP command. declare @sql varchar(8000) select @sql = 'bcp master..sysobjects out E:\utility\sysobjects.txt -c -t -T -S' + @@servername exec master..xp_cmdshell @sql Terry L. Broadbent - DBA "The most likely way for the world to be destroyed, most experts agree...

Extension won't matter to SQL Server. It is good to follow convention or know the other person's standard. We use a third party backup product that utilizes a different extension. This has not been a problem. Terry L. Broadbent - DBA "The most likely way for the world to be destroyed, most...

In order to alter a replicated table you have to drop the subscriptions and article, alter the table and add article and subscriptions. You can easily accomplish this with script like this. USE PublicationDatabase --Drop Subscription EXEC DBName.dbo.sp_dropsubscription...

It appears that the problem is a defect in xp_sendmail. If the @separator parameter is specified as a single space, the space is suppressed in the output. If the parameter is omitted, the result contains a single space column separator. Way to go Microsoft! Terry L. Broadbent - DBA "The most...

Jay, Thanks. I tried the change. It didn't make any difference. This may be a settings issue because I can now get the correct output from one SQL 2000 server instance but still get suppressed spaces from another instance. I'll keep you informed if I find anything. Did you encounter the...

1- Yes - multiple triggers work and you can easily set the order of the triggers if needd. 2- Inserted table is the same for all triggers. The caveat being, if a trigger inserts another row in the same table, the triggers will fire again. 3- I prefer not to send mail from triggers as this can...

According to BOL the default for US English is the SQL collation. English (United States) 0x409 SQL_Latin1_General_CP1_CI_AS As far as performance goes there should be no difference. Terry L. Broadbent - DBA "The most likely way for the world to be destroyed, most experts agree, is by...

First - Are all columns used in joins and where clauses indexed? If not, make it so. Second - Use of functions in where clauses can degrade performance, especially if the column is indexed. Eliminate the SUBTRING function. Change substring(skill.xskillcode, 1, 3) = 'stq' TO...

The syslanguages table is deprecated in SQL Server 2005. In fact, there is no syslanguages table. syslanguages is a system view. Check SQL BOL for more information. So far as I know, you cannot add another language to SQL Server 2005. Which language is missing? Terry L. Broadbent - DBA...

The best collation to use depends on your locale. Check out the information regarding collation setup in SQL BOL. http://msdn.microsoft.com/en-us/library/ms143508(SQL.90).aspx Terry L. Broadbent - DBA "The most likely way for the world to be destroyed, most experts agree, is by accident...

Question: Has anyone encountered the following situation? Does anyone know of a fix? Thus far my searches haven't turned up anything. Situation: Use xp_sendmail in SQL Server 2000 to send results of a T-SQL query. The result can be sent in the body of the email or as an attachment. If the...

See my reply in thread183-1519149. Terry L. Broadbent - DBA "The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

You can only ROLLBACK data if you performed the delete in a transaction and have not committed the transaction. If your database is in FULL or BULK-LOGGED recovery mode, you have a few recovery options. 1- If there is no danger of data loss (i.e., no rows have been updated since the delete)...

Assuming the table contains multiple rows per month you could write a query like this to summarize three months of data. SELECT Mnth = CONVERT(CHAR(3), datefield, 7), counthits = SUM(counthits) FROM Tablename WHERE datefield >= DATEADD(mm, -2, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) GROUP BY...