Saturday, June 19, 2010

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

 

Error

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

clip_image002

Resolution:
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:

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.

CAUSE:
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.

RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .
clip_image002[4]
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.

Variables

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,

GRANT ALTER TRACE to [Login]

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