03.17.07

Dedicated Administrator Connection

Posted in DBA, Microsoft Sql server, TSQL at 1:44 pm by

Microsoft SQL Server 2005 provides a dedicated administrator connection (DAC). The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server-even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and

SQL Server Management Studio, only one person can login on it.
By default it only accepts local connections. To change it to accept remote

connections use:

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Using a DAC
Open Management studio, and open a new Query connection to admin:instance using a

sys admin account. You cannot use the Object Explorer with DAC. If you just open an

try to connect to the server you end up with this error:

Dedicated administrator connections are not supported. (ObjectExplorer)

You need to open the management console then cancel the connect to server window

and select a new querry and go admin:servername\instance

Here you can execute querries like

select Session_id, login_time, cpu_time, memory_usage, reads, writes, login_name

from sys.dm_exec_sessions WITH (NOLOCK)

03.15.07

Add & remove columns & constraints to tables

Posted in DBA, Microsoft Sql server, TSQL at 9:13 pm by

–Sample Making a column not null and make it default to a value
–When the table has been in use and the existing values shouldn’t change.
update test1 set code = 0 WHERE code is null;
alter table test1
alter column code tinyint not null;
ALTER TABLE test1 ADD CONSTRAINT DF_test1_code DEFAULT ((0)) FOR code;

–Sample Adding a column with a default value
ALTER TABLE test1
ADD code tinyint not null
CONSTRAINT DF_test1_code DEFAULT ((0));

–Removing a constraint
ALTER TABLE test1
DROP CONSTRAINT DF_test1_code;

–Removing a column
ALTER TABLE test1
DROP column code;

03.14.07

The art of scripting replications.

Posted in DBA, Microsoft Sql server, TSQL at 9:00 pm by

Open the Microsoft sql server management studio.
In the object explorer pane you connect to the base server.
You open the replication node then local publications and Rightclick on the desired replication.
Select generate script from the pop up menu.
Make sure “Create or enable the components.” is selected.
Script to file…
Then open this file replace the name of original server in the whole file with the name of the serevr you want to deploy to.

In my case it only was written once in the :exec sp_addpublication

Then look for @job_password = null if the @job_login isn’t null either you have to provide the password for that account.

Also look for
exec sp_grant_publication_access @publication = N’My publication’, @login = N’oldserverSQLServer2005SQLAgentUser$oldserver$MSSQLSERVER’

Change the oldserver to newserver name. (better check for the correct account first!

First enable distribution on your new server.
Then run the file you crafted.

03.13.07

Creating a distributor with the sql server management studio and losing connection with the server

Posted in DBA, Microsoft Sql server, TSQL at 11:35 pm by

Scenario: Using the sql server management studio you are setting up a server to act as distributor. Then while you have clicked finished the connection is lost between your workstation and the server. Now the server thinks he’s a distributor but then again he’s not sure! You try to redo the previous action but that won’t work. What you need to do is tell the server to give up being a distributor.


use master

exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

Now just restart the distribution creation wizard.

03.12.07

Generating insert statements from data(manual way)

Posted in DBA, Microsoft Sql server, TSQL at 9:12 pm by

Sample :

DECLARE @replunit as char(2);
DECLARE @kantoor_nr as varchar(4);
DECLARE @kas_nr as varchar(4);
DECLARE @bediende_nr as varchar(4);

SET @replunit = ‘BE’;
SET @kantoor_nr = ’141′;
SET @kas_nr = ’1′;
SET @bediende_nr = ’996′;

SELECT
INSERT INTO KasBeginSaldo
(
KBS_ReplUnit,
KBS_KantoorNr,
KBS_KasNr,
KBS_ItemCode,
KBS_SubCode,
KBS_TijdStip,
KBS_BediendeNr,
KBS_Aantal,
KBS_Waarde,
KBS_VerschilWaarde,
KBS_BediendeNrTeller,
KBS_TijdstipTelling
) VALUES
( @replunit,
@kantoor_nr,
@kas_nr, ‘ +
”” + CAST(KBS_ItemCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(KBS_SubCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(getdate() AS varchar(8000)) + ”’, ‘ +
‘@bediende_nr, ‘ +
CAST(KBS_Aantal AS varchar(8000)) + ‘, ‘ +
CAST(KBS_Waarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_VerschilWaarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_BediendeNrTeller AS varchar(8000)) + ‘, ‘ +
”” + CAST(KBS_TijdstipTelling AS varchar(8000)) + ”” +
‘);’ as statement
FROM
KasBeginSaldo
WHERE
KBS_KantoorNr = @kantoor_nr AND
KBS_KasNr = @kas_nr AND
KBS_ReplUnit = @replunit;

03.11.07

Changing a property for all articles on a publication.

Posted in DBA, Microsoft Sql server, TSQL at 1:30 pm by

This week I had to change the pre_creation_cmd from ‘delete’ to ‘drop’ on all articles of a publication. I could do this manually or I could find all articles by using sp_helparticle. Lazy as I’m I opt for the latest option.

DECLARE @articles table
(
[article id] int,
[article name] varchar(255),
[base object] varchar(255),
[destination object] varchar(255),
[synchronization object] varchar(255),
[type] int,
status varchar(255),
filter varchar(255),
description varchar(255),
insert_command varchar(255),
update_command varchar(255),
delete_command varchar(255),
[creation script path] varchar(255),
[vertical partition] varchar(255),
pre_creation_cmd varchar(255),
filter_clause varchar(255),
schema_option varchar(255),
dest_owner varchar(255),
source_owner varchar(255),
unqua_source_object varchar(255),
sync_object_owner varchar(255),
unqualified_sync_object varchar(255),
filter_owner varchar(255),
unqua_filter varchar(255),
auto_identity_range varchar(255),
publisher_identity_range varchar(255),
identity_range varchar(255),
threshold varchar(255),
identityrangemanagementoption varchar(255),
fire_triggers_on_snapshot varchar(255)

);
DECLARE @article_name varchar(255);

INSERT INTO @articles
exec sp_helparticle  'Your replication publication name';

DECLARE article_cursor CURSOR FOR
SELECT [article name] as base_object FROM @articles;

OPEN article_cursor;

FETCH NEXT FROM article_cursor
INTO @article_name;

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_changearticle
@publication = 'Your replication publication name',
@article = @article_name,
@property = N'pre_creation_cmd',
@value = 'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription= 1;

FETCH NEXT FROM article_cursor
INTO @article_name;

END

CLOSE article_cursor;
DEALLOCATE article_cursor;
GO

After this you need to reinit all the subscriptions.