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.
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
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 CHECKTABLE (“HumanResources.Employee”) WITH PHYSICAL_ONLY;
EXEC sp_change_users_login ”Report”;