When running the following script, will get you a verbose of the SQL server installed. This will get you the Service Pack number, version and other information which includes Product Level and other interesting stuff.

Note: if you would like to get a simplified information on the SQL server simply run the SELECT @@VERSION on your SQL while using the Master database, but if you want to know more in detail, use the script below.

SELECT @@VERSION
GO

-- get version info from the registry (SQL 2008 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='SP'
GO

(722)

Find members of db_owners in all databasesThis script is aim to provide all members of the db_owner database role in all databases in an instance. It
The following code returns the role name and the member name filtered for the db_owner database

This script is aim to provide all members of the db_owner database role in all databases in an instance. It uses sp_MSForEachDb with a custom script using sys.database_principals DMV. The following code returns the role name and the member name filtered for the db_owner database role.

exec sp_msForEachDb ' use [?]
select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
where
r.name = ''db_owner'''

(678)

Often when having SQL Express Editions installation I would like to schedule and execute stored procedures. Using Express editions you cannot make use of the SQL Agent, but you can run them manually using a batch file and using the SQLCMD.EXE file as below

SQLCMD /Q "USE mydb; EXEC mystoredprocedure"

This will use the mydb that you specify and run the mystoredprocedure. You can also use the > filename.txt to make the job log.

(673)

When coming accross SQL Express editions, you will not be able to set a job in the Agent to execute a stored procedure. I usually do custom stored procedures to run them on Express editions.

There is a way to execute them by using the SQLCMD command

sqlcmd -Q "exec myprocedure" -S mysqlserver -d fromthisdatabase -E -o C:\log.txt

This will execute your stored procedure and you can put it in a batch file and schedule it in the Task Scheduler

(6144)

After changing the password of the SA or the user which is in charge of running jobs on the SQL server, the jobs will not work and you will not be able to delete the jobs with the error ‘Login failed for user SA‘. This can temporary solved by changing the SA or the user used password back to it’s original state and delete the job, but still you will not be able to run the job.

To solve this permanently you have to open the ‘Management Studio’, right-click on the job and select ‘Modify’. Click on the ‘Manage Connections’ and replace the username and password on each connection if you have more than the default one using the new password that you have recently changed.

This modification you have to do it for every-time you change the password of the ‘sa’ or user you use to run the jobs in the Manage Connections.

(658)

To Backup a database from command-line and using SQL 2000 MSDE version, you must use the SQLMAINT.EXE file situated in the ‘C:\Program Files\Microsoft SQL Server\MSSQL\Binn’

The backup cannot be done for all databases at once and it must be done individually. This can be done by the line below and then just duplicate it for multiple databases.

Create a batch file and run the following:

CD "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
sqlmaint -D "MyDB" -BkUpDB "X:\Backups\Data" -Rpt "X:\Backups\Reports\MyDB.txt" -BkUpMedia DISK


(1557)

Sometimes when you need to automate some stuff from the SQL server you need to create a batch file and run it in the Task Scheduler. This way you can run the jobs you need to do and then run the executable or batch file you need from a Stored Procedure.

Firstly you need to make sure that the XP_CMDSHELL is enabled. This will allow you to execute the files from SQL.

This can be easily done from the Surface Area Configuration tool or by executing the following code:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

This should enable the XP_CMDSHELL. Now to execute a file you need to add the following SQL code to your present Stored procedure:

DECLARE @myfile varchar(200)
SET @myfile = ‘C:\sysfiles\copytootherserver.bat’
EXEC master..xp_cmdshell @myfile

(8427)

Making a scheduled automated shrink logs for the databases with specific space to shrink.

DECLARE @Step1 VARCHAR(200)
DECLARE @Name varchar(50)

CLOSE DBNames
DEALLOCATE DBNames


(54858)