SQL Server Katmai

MSDN Forums for Katmai - nice discussion going on. Click the link to participate: SQL Server Katmai

Tables and their row counts

Implication of using different methods of getting Tables and thier row counts in SQL server database :
Tables and their row counts

SQL Server 2008

Latest version of SQL Server 2008 CTP is out this june. More info on this version of SQL server can be found in SQL server 2008 CTP

Latest version of SQL server

To get an idea of what the latest version of SQL server will be, you can ask a SQL guru @Ask SQL Guru

Renaming a SQL Server

The following steps will help you in avoiding most of the issues that arise from renaming a SQL Server.

Open query analyser and to the following simple three steps

1. Drop the old server name (sp_dropserver 'old server name' )
2. Add new server name (sp_addserver 'new server name', 'local')
3. Update sysjobs table column that contain the original server name to new server name

SQL server script library

Very useful SQL server script on SQL server central. For more info SQL server script library

Enable output logging for a pull subscription

The following steps will help you in Enabling output logging for a pull subscription.
In SQL Enterprise Manager, click the Subscriber database.
2. Open the Pull Subscriptions folder.
3. In the right-hand pane of SQL Enterprise Manager you will see the pull subscription.
4. Open the subscription properties by double-clicking the subscription.
5. Click the General tab to open the agent properties dialog box. Click Distribution Agent Properties for a transactional pull subscription and click Merge Agent Properties for a merge pull subscription.
6. Click the Steps tab, and then edit the Run Agent step.
7. At the end of the string under command, add:

-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel [012]

Specify either 0, 1, or 2 after the -Outputverboselevel parameter.
8. Click OK to save the changes, and then close the Edit Job Step dialog box.
9. Click OK to save changes, and then close the Replication Agent Properties dialog box. If the agent is set to run continuously, stop and then restart the replication agent so that SQL Server logs the messages to the log file you specified in step 7. If file already exists, the agent appends the output to a file.

Source : http://www.microsoft.com

SQL Server Replication

Good article on how to setup SQL server replication over the internet. SQL Server Replication Across Domains and the Internet

Pull subscription errors

The process could not read file '\\servername\replfolder\unc\_Data sch' due to OS error 1326. The step failed.

The work around can be found on microsoft support site. Click here to view Pull Subscription

Mathematics for Database Professionals

Has anybody read this book? Click here Applied Mathematics for Database Professionals . Would like to hear your views.

Column collation to Server Default

The following script will help you to change all columns in your database to server default collation. You can modify this script to do all your databases on particular server.

set nocount on
declare @AlterId int
declare @serverColl varchar(100)
declare @AlterString varchar(300)
set @serverColl = convert(sysname, serverproperty('collation'))
declare @Testing table
(AlterId int identity(1,1) not null primary key clustered,
AlterString varchar(300))

INSERT INTO @Testing(AlterString)
select 'ALTER TABLE [' + so.name + '] ' +
'ALTER COLUMN [' + sc.name + '] ' +
st.name +
'(' + cast(sc.length as varchar) + ') ' +
'COLLATE '+ @serverColl + ''
from syscolumns sc
join sysobjects so
on so.id = sc.id
join systypes st
on st.xtype = sc.xtype
where sc.collation is not null
and OBJECTPROPERTY(Object_id(so.name), 'IsTable') = 1
and st.name!= 'sysname'
and sc.collation not like @serverColl
select @AlterId = Max(AlterId) from @Testing

WHILE @AlterId IS NOT NULL
BEGIN
SELECT @AlterString = AlterString FROM @Testing
WHERE AlterId = @AlterId
EXEC (@AlterString)
SELECT @AlterId = Max(AlterId) from @Testing
WHERE AlterId < @AlterId
END

Renaming Database server

You may receive error 14274 - cannot update or delete job (or its steps or schedules) that originated from an MSX server. You can run the following script on MSDB database to correct:
update sysjobs
set originating_server = 'new serverName'

Stored procedure tips

Have come across this Stored procedures in SQL Server: A dozen must-have tips. From tuning to how to perform cross tab queries. You can find by clicking this link Stored procedure tips

Best practice analyser

I have just downloaded and played with the new SQL server 2005 best practice analyser (CTP). First impression is that much better than previous version. Will post more details in near future. For now you can download it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en and play with it

All tables with identity

I thought the following script is useful if you want to find out all tables with identity columns in a specific database.
use
go
SELECT
DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

SQL Server and Data Mining

SQL Server and Data Mining

Name value pair and Replication ?

Have you ever tried to design your database with the extreme end of using name value pair ? Name vaule pair if used in database design is like job done for ever. It sounds simple but in the next two parts, I will try to explain the benefits and the drawbacks of using named value pair in database design.

The secod part of the title is about replication. Is it good or bad ? I will also try to put together the advantages and disadvantages of using database replication in work environment.

So, watch out this page in the next two to four weeks.


Tesh

Modifying MSDB system procedures

I personally do not recommend this on production environment but have to do myself to fix the problem of not allowing some users to have sysadmin rights for the sake of creating packages and dropping packages. After a few investigations, I have come up with this idea of what if modifying some of the stored procedures in msdb database that is used to add packages.

I needed to change only two of the system-stored procedures. The two system stored procedures that are modified and the lines commented out are shown below. It just prevents the stored procedure not to check if a user is a member of sysadmin or owner of the package.
1 . sp_add_dtspackage

Commented script
/*
ELSE
BEGIN
--// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may create new versions of it.
IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))
BEGIN
RAISERROR (14586, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

2. sp_drop_dtspackage
/*
IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
BEGIN
IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))
BEGIN
SELECT @name = name FROM sysdtspackages WHERE id = @id
RAISERROR (14587, -1, -1, @name)
RETURN(1) -- Failure
END
END
*/

Once all the changes are saved to msdb database, I have given exec permission to the above stored procedures and it did at least served the purpose. Now, I don’t have to give sysadmin permissions to a user/developer who just need to change the dts package.

Jobs running under

The following sql will help you to find out SQL job name and owner of the job.

select sl.name, j.name from sysjobs j
join master.dbo.syslogins sl
on sl.sid = j.owner_sid

Random password Generator

The following has helped me in generating random and complex passwords. I want to share with you and if you have any comments and would like to improve on it, you are welcome.

declare @type tinyint, @Length tinyint
DECLARE @password varchar(250)
set @password = ''
set @Length = 250
while @Length > 0
BEGIN
SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1
SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @type = 2
SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @type = 3
SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @type = 4
SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0))
SET @Length = @Length - 1
END
SELECT @password As ComplexPassword