Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. tlbroadbent

    Selecting Grouped Rows With Max Value

    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...
  2. tlbroadbent

    bcp command from SSMS not working

    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...
  3. tlbroadbent

    SQL Backup File Extension

    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...
  4. tlbroadbent

    Pausing Replication

    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...
  5. tlbroadbent

    xp_sendmail Column Separator Suppressed

    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...
  6. tlbroadbent

    xp_sendmail Column Separator Suppressed

    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...
  7. tlbroadbent

    Multiple AFTER INSERT triggers on same table?

    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...
  8. tlbroadbent

    Which collation to use

    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...
  9. tlbroadbent

    Performance and Tempdb questionw with Complex Query

    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...
  10. tlbroadbent

    SQL Server 2005 languages

    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...
  11. tlbroadbent

    Which collation to use

    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...
  12. tlbroadbent

    xp_sendmail Column Separator Suppressed

    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...
  13. tlbroadbent

    Rollback the delete

    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)
  14. tlbroadbent

    Rollback the delete

    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)...
  15. tlbroadbent

    Return the last 3 months on SQL - how?

    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...
  16. tlbroadbent

    Data Type error

    The advice you received to use single quotes will solve the problem. SQL Server is interpreting 156e as a floating point number in scientific notation. 156e is equivalent to 156e0. If you enclose the string in quotes, SQL will treat it as a string. Terry L. Broadbent - DBA "The most likely way...
  17. tlbroadbent

    Reporting on System Users

    SQLSister, Thanks. It has been a long while since I participated here. I'm glad to see some familiar names are still actively involved. 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...
  18. tlbroadbent

    Pull value from previous record

    Use a query with a self join of the table. Something like this should work though you may need to adjust to fit your situation. SELECT c1.Claim, MAX(c1.[trans date]) AS CurrDate, c1.examiner AS CurrExaminer, MAX(c2.[trans date]) AS PrevDate, c2.examiner AS PrevExaminer FROM Claims c1 JOIN Claims...
  19. tlbroadbent

    Single Distinct Column ?

    Is this what you need? SELECT DISTINCT [Email] FROM @tblname 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)
  20. tlbroadbent

    Reporting on System Users

    EXEC sp_helplogins 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)

Part and Inventory Search

Back
Top