Passing a list of integers to a stored procedure.

TSQL stored procedures don't support arrays as input parameters, so in order to pass a list we have to convert it to a supported type and then convert it back to a list. One solution is to serialize the List<int> into an xml string, pass it as nText and then use the SQL Server xml capabilities to transform it into a table.

List<int> results = new List<int>();

XmlSerializer xs = new XmlSerializer(typeof(List<int>));

StringWriter sw = new StringWriter(new StringBuilder());

xs.Serialize(sw, results);

PatientResultsTableAdapter ta = new PatientResultsTableAdapter();

table = ta.GetDataByPatientBins(sw.ToString());


CREATE PROCEDURE [dbo].[SelectList]
    @ids nText
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @hDoc int
 
    --Prepare input values as an XML documnet

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @ids

    select *
    from results
    where result_id in (select id from openxml (@hDoc,
'/ArrayOfInt/int') with (id int 'text()'))
                        

    EXEC sp_xml_removedocument @hDoc
END

Save a diagram in SQL Server 2008

if ($args.length -lt 3)
{
 Write-host "usage: save_diagram db_name diagram_name file_name"
}
else
{
 function Join-String
 {
    begin { $sb = New-Object System.Text.StringBuilder }
    process { $sb.Append($_) | Out-Null }
    end { $sb.ToString() }
 }

 $dbname = $args[0]
 $filename = $args[2]
 New-item $filename -type file -force | out-null

 $declarationsStr = "SET NOCOUNT ON
DECLARE @newid INT
DECLARE @DiagramSuffix          varchar (50)"

 $insertStr = "PRINT 'Suffix diagram name with date, to ensure uniqueness'
SET @DiagramSuffix = ' ' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)

PRINT 'Create row for new diagram'
BEGIN TRY
 PRINT 'Write diagram NAME into new row (and get [diagram_id])'
 INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition]) VALUES ('{0}'+@DiagramSuffix, 1, {1}, 0x)
 SET @newid = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
 PRINT 'XxXxX ' + Error_Message() + ' XxXxX'
 PRINT 'XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'
 RETURN
END CATCH"

 $updateStr = "UPDATE sysdiagrams SET [definition] .Write ({0}, null, 0) WHERE diagram_id = @newid `n"

 $connString = "server=george-b\Advia;Integrated Security=True;Initial Catalog={0}"
 $query = "SELECT [name],[principal_id],[diagram_id],[version],[definition] FROM sysdiagrams"
 $conn = new-object "System.Data.SqlClient.SqlConnection" ($connString -f $dbname)
 $sqlcmd = new-object "System.Data.SqlClient.SqlCommand" ($query, $conn)
 $conn.Open()
 $reader = $sqlcmd.ExecuteReader()
 $reader |  &{
                 begin
                 {
                     $values = new-object "System.Object[]" $reader.FieldCount
                     $declarationsStr | Add-Content $filename
                 }
                 process
                 {
                     $_.GetValues($values) | out-null;
                     $name =  $values[0]
                     $version = $values[2]
                     "Generating sql script for diagram [{0}]." -f $name | write-host
                     $insertStr -f $($name, $version) | Add-Content $filename
                     $i = 0
                     $s = "0x"
                     $values[4] | %{if ($i -eq 32) {$i = 0; $updateStr -f $s; $s = "0x"}
                                    else {$i+=1; $s += "{0:x2}" -f $_ }} | Join-string | Add-Content $filename
                     if ($s -ne "0x") {$updateStr -f $s | Add-Content $filename}
                 }
             }
 $conn.Close()
}

Roundcubemail on Solaris 10 with SJWS 7

Solaris 10 06/06
Sun Java Web Server 7.0
php with php_cgi
move .htaccess php configuration into iniset.php
make cookie always non secure.