Alexander Kochurov developer's blog

How to install (uninstall) Windows service programmatically

2008-12-03T04:21:00.000-08:00

First, You should add reference to System.Configuration.Install

Install service:

string ServiceFileName = @"c:\myservice.exe";

System.Configuration.Install.AssemblyInstaller Installer = new System.Configuration.Install.AssemblyInstaller();

Installer.Path = ServiceFileName;

Installer.UseNewContext = true;

Installer.Install(null);

Installer.Commit(null);

Uninstall service:

string ServiceFileName = @"c:\myservice.exe";

System.Configuration.Install.AssemblyInstaller Installer = new System.Configuration.Install.AssemblyInstaller();

Installer.Path = ServiceFileName;

Installer.UseNewContext = true;

Installer.Uninstall(null);

SQL Server 2005: how to publish trigger from assembly

2008-08-22T02:24:00.000-07:00

1. Reconfigure server state for CLR assembly use

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

2. Enable TRUSTWORTHY property of database

ALTER DATABASE database_name SET TRUSTWORTHY ON

3. Register assembly in database

CREATE ASSEMBLY assembly_name
FROM assembly_full_path
WITH PERMISSION_SET = UNSAFE;
GO

4. Create trigger on the table
USE database_name
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'trigger_name') DROP TRIGGER trigger_name ON DATABASE
GO
CREATE TRIGGER trigger_name ON table_name AFTER INSERT, UPDATE, DELETE
AS
EXTERNAL NAME assembly_name.trigger_class_name.trigger_method
GO

Trigger in SQL Server Error: MSDTC on server is unavailable

2008-08-20T06:56:00.000-07:00

I found decision here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54705

---

On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

Test the trigger and see if it works. If it still does not work, wrap you trigger in the following transaction code (found below in bold):

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

-- Put all queries in here (SELECT, INSERT, UPDATE, and DELETE)
select * from [SERVER2].[DBASE].[OWNER].[TABLENAME]
update [SERVER2].[DBASE].[OWNER].[TABLENAME]
set [column] = value
where [condition(s)]

COMMIT TRANSACTION
SET XACT_ABORT OFF

How generate CRM4 proxy-class

2008-07-24T00:20:00.000-07:00

Example of command file:

cd "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin"

wsdl /out:c:\CrmProxy.cs /n:YourCrmService http://localhost/MSCRMServices/2007/CrmServiceWsdl.aspx?wsdl

Execute T-SQL script from C# code

2008-07-23T03:58:00.000-07:00

if (filename.Length > 0)
{
try
{
// Open the file to read from.
using (StreamReader sr = File.OpenText(filename))
{
string queryString = sr.ReadToEnd().Replace("GO", "");

using (SqlConnection connection = new SqlConnection(ASSettings.ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
MessageBox.Show("Script executed.", "Result", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error script execution!\n" + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

Repair DBF structure (if damaged file header)

2008-07-17T00:45:00.000-07:00

SET TABLEVALIDATE TO 3
CD "C:\Repair\"

OPEN DATABASE osago EXCLUSIVE
VALIDATE DATABASE TO FILE before.txt

lnDBF = ADIR(laDBF,"*.dbf")
FOR i = 1 TO lnDBF
TRY
USE (laDBF[i,1]) EXCLUSIVE
CATCH
SET TABLEVALIDATE TO 0
USE (laDBF[i,1]) EXCLUSIVE

MESSAGEBOX("Table "+laDBF[i,1]+" could not be opened. "+CHR(13)+;
"Please delete any damaged records (Ctrl-T). "+CHR(13)+;
"Press Ctrl-W when finished",16)

BROWSE
SET TABLEVALIDATE TO 3
FINALLY
PACK
REINDEX
ENDTRY
NEXT
VALIDATE DATABASE RECOVER TO FILE after.txt

CRM 4: user-side call jscript event handler for CRM-entity

2008-06-30T02:09:00.000-07:00

// CRM 4.0 SDK Example
// Define the soapBody for the WhoAmI request.
// Difference from Microsoft CRM 3.0 is requirement to call method GenerateAuthenticationHeader() in SOAP Envelope

var soapBody = "";
soapBody += "";
soapBody += "";
soapBody += "";
//Wrap the Soap Body in a soap:Envelope.
var soapXml = "soapXml += "xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" ";
soapXml += "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" ";
soapXml += "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
soapXml += GenerateAuthenticationHeader();
soapXml += soapBody;
soapXml += "";
var SERVER_URL = "http://crm4.company.ru:5555";
// Create the XMLHTTP object for the execute method.
var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
xmlhttp.open("POST", SERVER_URL + "/mscrmservices/2007/crmservice.asmx", false);
xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
//Send the XMLHTTP object.
xmlhttp.send(soapXml);
// Create an XML object to parse the results.
xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async = false;
xmlDoc.loadXML(xmlhttp.responseXML.xml);
// Get the user's ID.
var userid = xmlDoc.getElementsByTagName("UserId")[0].childNodes[0].nodeValue;
alert(userid);

T-SQL example: "UPSERT" model (Insert + Update) by T-SQL with changeable number of input parameters

2008-06-24T23:04:00.000-07:00

ALTER PROCEDURE [dbo].[spSave]
@InputItems char(2048)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @FilterID INT;
SET @FilterID = -1;

BEGIN TRANSACTION;

DECLARE @TempString varchar(2048)
DECLARE @Temp varchar(2048)
DECLARE @ChPos int

DELETE FROM WorkTable WHERE ID = @FilterID

IF @InputItems <> ''
BEGIN
SET @TempString = ''
SET @Temp = ''
SET @ChPos = -1

SET @TempString = @InputItems

SET @ChPos = CHARINDEX(',', @TempString)

WHILE @ChPos > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@ChPos - 1))
INSERT INTO WorkTable
(ID, YourValue)
VALUES
(@FilterID, CAST(@Temp AS int))

IF @@ERROR != 0 GOTO ErrorHandler

SET @TempString = SUBSTRING(
@TempString,
(@ChPos + 1),
(LEN(@TempString) - @ChPos))
SET @ChPos = CHARINDEX(',', @TempString)
END

INSERT INTO WorkTable
(ID, YourValue)
VALUES
(@FilterID, CAST(@TempString AS int))
IF @@ERROR != 0 GOTO ErrorHandler
END
COMMIT TRANSACTION

SELECT @FilterID AS RetVal
RETURN 0
ErrorHandler:
SELECT @FilterID AS RetVal
IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
RETURN @@ERROR
END

T-SQL example: dynamically generate columns by table

2008-06-24T05:00:00.000-07:00

DECLARE @Seria INT
SET @Seria = 15

DECLARE @Kpcount INT
SELECT @Kpcount = Pairs * Sections FROM S_SeriaTPS WHERE ID = @Seria

DECLARE @Cnt INT
SET @Cnt=1

-- Colums list string
DECLARE @sf_1 VARCHAR(MAX)
SET @sf_1 = ''

-- Join section string
DECLARE @sj_1 VARCHAR(MAX)
SET @sj_1 = ''

DECLARE @ChkVal INT
SET @ChkVal = 0

WHILE (@Cnt <= @Kpcount)
BEGIN
SET @sf_1 = @sf_1 +
', t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.RepDate AS [Date ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '], ' +
't0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.DiamAv AS [Diameter avg ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '] '

-- Generate join section
SET @sj_1 = @sj_1 +
'LEFT JOIN (
SELECT
tk.IDTps,
cm.RepDate,
tk.Position,
DiamAv =
ABS(
COALESCE((bm.DiamL - (bm.TolshBandL - cm.TolshBandL) * 2), bm.DiamL) +
COALESCE((bm.DiamR - (bm.TolshBandR - cm.TolshBandR) * 2), bm.DiamR)) / 2
FROM TpsKp tk
LEFT JOIN BasicMeasurements bm ON bm.IDKp=tk.IDKp
LEFT JOIN Measurements cm ON
cm.ID=(
SELECT TOP 1 cm1.ID
FROM Measurements cm1
WHERE cm1.IDKp=tk.IDKp
ORDER BY cm1.RepDate DESC)
WHERE tk.Position=' + RTRIM(CAST(@Cnt AS CHAR(2))) +
') t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + ' ON t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.IDTps=t.ID '

-- Column counter increment
SET @Cnt = @Cnt + 1
CONTINUE
END

DECLARE @sql VARCHAR(MAX)
SET @sql =
'SELECT RTrim(t.Nomer) AS [Name] {0} FROM Tps t {1} WHERE t.IDSeria=' +
RTRIM(CAST(@Seria AS CHAR(3))) + ' ' +
'ORDER BY Nomer'

IF @sf_1 = ''
BEGIN
SET
@sf_1 = ', ''no data'' AS [Info]'
END

SET @sql = REPLACE(@sql, '{0}', @sf_1)
SET @sql = REPLACE(@sql, '{1}', @sj_1)

EXEC (@sql)

-----------------------
Result seem's like this
Name Date 1 Diameter avg 1 Date 2 Diameter avg 2
316 2007-12-28 13:11:23.000 1254 2007-12-28 13:12:16.000 1255
319 2008-01-19 14:55:00.000 1250 2008-01-19 14:56:30.000 1255
321 2008-02-15 13:15:21.000 1212 2008-02-15 13:15:37.000 1213