IT and business subjects
11-Feb-1111 Leave a comment
SBS Server Standard 2008 has SQL Server Management Studio Express tools on it.
When installing SQL Server 2008 R2 Standard one of the checks was for the above and would not let you pass until this was removed. My approach was to uninstall SSMS Express. Checks then passed.
As SBS is using SBSMonitoring as a database, websites suggest installing SQL Server as a new instance. I installed it as SQL2008 instance. SUCCESS.
09-Feb-1111 Leave a comment
How do I … work with Guid in ADO.Net?
( in this case to support SQL Server replication using dataset )
26-Dec-2011
Hypothesis: That DataSet could act unaware of replication column (do not import it on datatable creation) (or delete it after). Then use default in SQL Server (newsequentialid())
Experiment: Worked out of the box – straight away
During creation of SQL Replication, SQL Server will add a rowGuid column which is a uniqueidentifier and places a default of (newsequentialid()) onto this column
Insert statements for replication must include all columns, except the rowGuid column.
ERROR
However if you then add a new row to this table in ADO.Net then program will give error message ‘Column rowGuid does not allow nulls’
SOLUTION
[MyDataTable].rowguidColumn.DefaultValue = Guid.NewGuid
EXPLANATION
SQL will have inserted a Unique index on rowGuid. However when the table is dropped onto the dataset the unique key is not there. Therefore although the above default set once will give each new row the same Guid, the ADO.net sees no errors. Because the insert statement does not insert the Guid, SQL does, then ADO.Net immediately returns the correct newly applied Guid from the database. This is because the Dataset insert statement also has a SELECT statement on it to get table updates immediately after the INSERT.
Updating ERROR
Updating columns with the rowguidcol property is not allowed.
The transaction ended in the trigger. The batch has been aborted.
Removed the rowguid column from the dataset update sql and it worked. Found no online instruction for this in short search.
09-Feb-1111 Leave a comment
http://en.wikipedia.org/wiki/The_Goal_(novel)
http://en.wikipedia.org/wiki/Theory_of_Constraints
Great book on Theory of Constraints told as a piece of fiction with a story of a Production Manager who is always expediting jobs.
At the end of the book the ‘hero’ asks the consultant, paraphrased here ‘.. and would you have not been able to have told these conclusions at the beginning’
The answer to this was that no, if he had done that then you would never truly understood the solution. Too many paradigm shifts to work through.
TOC includes bottleneck management and line-balancing.
The underlying premise of Theory of Constraints is that organizations can be measured and controlled by variations on three measures: throughput, operational expense, and inventory. Throughput is the rate at which the system generates money through sales. Inventory is all the money that the system has invested in purchasing things which it intends to sell. Operational expense is all the money the system spends in order to turn inventory into throughput
07-Feb-1111 1 Comment
PUBLISHER
SQL Server Agent account must be running on publisher
Snapshot folder must be a UNC folder which is shared
SUBSCRIBER
Agent process account could be Subscriber Computer or Domain name \ User Name
Distributer connection and Publisher connection I used a SQL Authentication account which will already work for normal login
ERROR: On Subsciber
Msg 14058, Level 16, State 1, Procedure sp_addmergesubscription, Line 381
Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.
SOLUTION: On Publisher
SELECT * FROM sysmergesubscriptions
DELETE FROM sysmergesubscriptions WHERE subscriber_server = ‘ServerName’
ERROR: On Publisher
Unable to start execution of step 2 (reason: Error authenticating proxy ComputerName\UserName, system error: Logon failure: unknown user name or bad password.). The step failed.
SOLUTION: On Publisher
Agent Security use a windows account. Password is the windows password for that account.
This account must have at least db_owner for the database being published.
It must also have write permissions on the publication folder.
http://msdn.microsoft.com/en-us/library/ms186515(v=SQL.105).aspx
STATUS: Should now have files on Publisher in Snapshot folder
ERROR: On Subsriber
the schema script ‘….sch’ could not be propagated to the subscriber
Go to subscriber view job history. Message in there may be above plus
‘The process could not read file ‘…\MSSQL\ReplData\unc\…’ due to OS error 3.
SOLUTION: On Publisher
Use a UNC path on the publication. To do this > Publisher > Publication > Right click properties > Snapshot > Put files in the following folder
Then use \\ server or computer name \ .. \ MSSQL \ReplData or which ever folder
Should now work
07-Feb-1111 Leave a comment
If unit tests are testing against the database using Integrated Security then when these automated tests are put onto the build machine authentication may fail.
SOLUTION. Create a SQL login for a machine which is possible.
CREATE LOGIN [MyDomain\MyComputer$] FROM WINDOWS;
http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/315b5780-c131-4867-88b9-ebac82e68d77
06-Feb-1111 Leave a comment
Believe this problem is because I had SQL Server 2008 on my machine and then added SQL Server 2008 R2. Today I had renamed the old MSSQL10 directories with a zz at front to mark them as old. Then SQL Server Agent did not start.
First I had:
Error message when you use SQL Server Configuration Manager to restart the SQL Server Agent service in SQL Server 2008:
“The request failed or the service did not respond in a timely fashion”
http://support.microsoft.com/kb/955494
Suggested using Start > Administrative Tools > Services or Services.msc and start from there.
This led to next problem
The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/12/unable-to-start-sql-server-agent.aspx
Found that the registry entry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
ErrorLogFile key was pointing at a location which was 2008 not 2008 R2
Chnaged the key value and SQL Server Agent started OK
SOLVED
06-Feb-1111 Leave a comment
“locate backup file cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the file name control in the locate dialog box.”
Solution:
Start > Programs > SQL Server Conifguration Manager > SQL Server Services
Find SQL Server in list and identify the Log On As account
Then go to the backup file location and give permissions to that account. Should now work
06-Feb-1111 Leave a comment
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer
BackupDirectory key modify. Works
05-Feb-1111 Leave a comment
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterEmail.aspx
Delayed send possible. Create message then go to options. Send only after a date time.
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterEmail.aspx#2WaysToFollowUp
Recall email is possible.
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterEmail.aspx#RecallEmails
31-Jan-1111 Leave a comment
All TableAdapters managed by a TableAdapterManager must use the same connection string
Can happen because one connection string has Persist Security Info=True and one does not
Make sure each connection string is the same.
Stephen Shakespeare 