Archive for the ‘SQLServer2000’ Category

Problem
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

DATE FORMATS

Format # Query (current date: 12/30/2006) Sample

1 select convert(varchar, getdate(), 1) 12/30/06

2 select convert(varchar, getdate(), 2) 06.12.30

3 select convert(varchar, getdate(), 3) 30/12/06

4 select convert(varchar, getdate(), 4) 30.12.06

5 select convert(varchar, getdate(), 5) 30-12-06

6 select convert(varchar, getdate(), 6) 30 Dec 06

7 select convert(varchar, getdate(), 7) Dec 30, 06

10 select convert(varchar, getdate(), 10) 12-30-06

11 select convert(varchar, getdate(), 11) 06/12/30

101 select convert(varchar, getdate(), 101) 12/30/2006

102 select convert(varchar, getdate(), 102) 2006.12.30

103 select convert(varchar, getdate(), 103) 30/12/2006

104 select convert(varchar, getdate(), 104) 30.12.2006

105 select convert(varchar, getdate(), 105) 30-12-2006

106 select convert(varchar, getdate(), 106) 30 Dec 2006

107 select convert(varchar, getdate(), 107) Dec 30, 2006

110 select convert(varchar, getdate(), 110) 12-30-2006

111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS

8 or 108 select convert(varchar, getdate(), 8) 00:38:54

9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM

14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output

select replace(convert(varchar, getdate(),101),’/’,”) 12302006

select replace(convert(varchar, getdate(),101),’/’,”) + replace(convert(varchar, getdate(),108),’:’,”)

12302006004426

Next Steps

  • The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available
  • These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently
  • Find more tips on MSSQLTIPS
site statistics
Advertisements

Problem
I have noticed some of my processes are failing because spids are already connected to the database. This happens specifically when I need to a restore database. I catch this problem pretty quick when I am working on it during the data and can fix it, but during nightly processing existing spids become problematic. I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance Plans. I have found this issue in my logs specifically related to performing integrity checks (DBCC CHECKDB (‘YourDatabaseName’) REPAIR_FAST) when the database needs to be in single user mode before the integrity check commands run. How can I kill these spids prior to running my processes?

Solution
Killing the spids is the process that needs to occur prior to issuing DBCC CHECKDB or performing the database restore process. Killing the spids can be accomplished by adding another step to your SQL Server Agent Jobs or in your script\stored procedure calling the code below to perform the KILL process prior to executing code that needs exclusive use of the database.

SQL Server 2000 and SQL Server 2005

USE Master
GO
SET NOCOUNT ON

— 1 – Variable Declaration
DECLARE @DBID int
DECLARE @CMD1 varchar(8000)
DECLARE @spidNumber int
DECLARE @SpidListLoop int
DECLARE @SpidListTable table
(UIDSpidList int IDENTITY (1,1),
SpidNumber int)

— 2 – Populate @SpidListTable with the spid information
INSERT INTO @SpidListTable (SpidNumber)
SELECT spid
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) — Master, Tempdb, Model, MSDB
AND spid > 50
ORDER BY spid DESC

— 3b – Determine the highest UIDSpidList to loop through the records
SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

— 3c – While condition for looping through the spid records
WHILE @SpidListLoop > 0
BEGIN

— 3d – Capture spids location
SELECT @spidNumber = spidnumber
FROM @spidListTable
WHERE UIDspidList = @SpidListLoop

— 3e – String together the KILL statement
SELECT @CMD1 = ‘KILL ‘ + CAST(@spidNumber AS varchar(5))

— 3f – Execute the final string to KILL the spids
— SELECT @CMD1
EXEC (@CMD1)

— 3g – Descend through the spid list
SELECT @SpidListLoop = @SpidListLoop – 1
END

SET NOCOUNT OFF
GO

Next Steps

  • Check your Database Maintenance Plan logs or SQL Server Agent Job Step History to determine when existing spids are preventing your automated processes from succeeding.
  • Depending on the user and/or automated processes that are running dictates how the scripts above should be modified to meet your needs.
  • Although the scripts above are beneficial for you to execute your process, you may also need to consider the other processes that are running and determine if 1 or more of the processes need to be executed at another time period for all of the processes to complete in a reasonable time period. For example, you may not want to kill a process at 4:00 AM that needs 4 hours to complete and must be finished by 8:00 AM. In this circumstance, re-scheduling the process may be a better bet.
  • Check out these related tips from MSSQLTips.com:
  • Stay tuned for another tip on situational alternatives to issuing the KILL command.

Preface:

When you are working with Microsoft SQL Server 2000, you may find it a very cool DBMS as my opinion. But some times, it drives you mad because of some troubles that are hard to find documents or solutions for them. This post reveals one of the most noticeable problem that I have to dealt wit.

Problem:

  • How to attach the MDF file on the query analyzer
  • How to bring database back on operation when the LDF file is damaged

Solution:

If you need to ignore the current LDF file and if your database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

  1. Create a new database with the same name and same MDF and LDF files
  2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
  3. Start SQL Server
  4. Now your database will be marked suspect
  5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start upSp_configure “allow updates”, 1
    go
    Reconfigure with override
    GO
    Update sysdatabases set status = 32768 where name = “BadDbName”
    go
    Sp_configure “allow updates”, 0
    go
    Reconfigure with override
    GO
  6. Restart sql server. Now the database will be in Emergency mode
  7. Now execute the undocumented DBCC to create a log fileDBCC REBUILD_LOG(dbname,’c:\dbname.ldf’) — Undocumented step to create a new log file.

    (replace the dbname and log file name based on ur requirement)

  8. Execute sp_resetstatus <dbname>
  9. Restart SQL server and see the database is online.

site statistics