Category Archives: Management Studio

Transaction replication: Property LoginSecure cannot be changed or read after a connection string has been set

I was tasked with a simple easy peasy transaction replication setup (is there such a thing?) between two countries.Came in, took a cup of coffee, Rammstein on the headphones and thought it was going to be a smooth morning. What could go wrong…

When I “tried” to configure the distributor, the wizard threw the following error at me:Property LoginSecure cannot be changed or read after a connection string has been set. (Microsoft.SqlServer.ConnectionInfo)
Mmm, almost all answers on the web took me to the following solution:

  • Right click local publication followed by Distributor properties
  • Select the publisher in the list, click the browse button
  • In agent connection mode, select “impersonate the agent process account”

Well yes, I am looking at it. “impersonate the agent process account” seems to be the default setting.
I was totally lost at that moment and I knew it was going to be a long “short morning”.

I wanted to generate scripts for replication although I knew no replication was setup at the moment but I just wanted to try for the heck of it.

The generate scripts wizard came up with a warning saying I had to use the proper server name, no IP address or alias and there it was…SSMS was connected to the instance using an IP address. Once I connected using the server name…no more errors when finishing the new distributor wizard.

SQL Server Management Studio Hack

Database administrators are lazy by nature. And although Books Online is a very good resource I”d like to get my own mini overview of most used statements in a new query template such as this one:

 

So whenever I hit “New Query” I get my template. It”s also usefull when you want to send your script to a colleague so you won”t have to tell him/her what server they need to connect to and which database to select.

You might want to give it a try.

1. Open file ”C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDESqlWorkbenchProjectItemsSQLSQLFile.sql” in Management Studio and copy the code below and save.

– ! Please execute in SQLCMD mode ! click SQLCMD Mode on the SQL Server Management Studio Query menu,

SET NOCOUNT ON
:CONNECT <servername>

USE <databasename>
GO

 
/*

 DBCC SQLPERF(LOGSPACE)WITH NO_INFOMSGS
 DBCC OPENTRAN WITH TABLERESULTS
 DBCC INPUTBUFFER ( session_id )
 DBCC SHOW_STATISTICS (“Person.Address”, AK_Address_rowguid) WITH HISTOGRAM;
 DBCC SHOWCONTIG (“HumanResources.Employee”);
 DBCC FREEPROCCACHE WITH NO_INFOMSGS;
 DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]
 DBCC CHECKCONSTRAINTS (“Production.CK_ProductCostHistory_EndDate”);
 DBCC DBREINDEX (“HumanResources.Employee”, ” “, 70);
 DBCC INDEXDEFRAG (AdventureWorks, “Production.Product”, PK_Product_ProductID)
 DBCC CHECKDB;
 DBCC CHECKTABLE (“HumanResources.Employee”) WITH PHYSICAL_ONLY;

 EXEC sp_helpfile
 EXEC sp_spaceused
 EXEC sp_change_users_login ”Report”;
 EXEC master.dbo.xp_fixeddrives

*/