Saturday, June 19, 2010

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)



• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.


1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

Wednesday, June 16, 2010

SQL2000 SP4 Installation Error sp_vupgrade_replication(1)


SQL Server 2005 Patching fails CREATE DATABASE failed.


Error Number : 29537

Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.

1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .
2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

1. Reference: a recently applied update, kb955706, failed to install

Thursday, June 10, 2010

Delete files using DOS command FORFILES


To delete te

--Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.


EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '


FORFILES Parameters

Parameter Name Description

/p Path

/m Search Mask (default is *.*)

/s Subdirectories will be searched recursively if this parameter is included

/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"

/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.


Variable Name Description

@FILE File name

@FNAME File name without extension

@EXT File extension

@PATH Full path of the file

@RELPATH Relative path of the file

@ISDIR Evaluates as TRUE if the file type is a directory

@FSIZE File size in bytes

@FDATE Last modified date stamp on the file

@FTIME Last modified timestamp on the file


Tuesday, June 08, 2010

Privileges needed to run SQL Server Profiler without sysadmin privileges


In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,


BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler