{"id":998,"date":"2010-01-27T10:24:59","date_gmt":"2010-01-27T09:24:59","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=998"},"modified":"2010-01-27T10:24:59","modified_gmt":"2010-01-27T09:24:59","slug":"sqlclr-deplyment-hiba","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2010\/01\/27\/sqlclr-deplyment-hiba\/","title":{"rendered":"SQLCLR deplyment hiba"},"content":{"rendered":"

Error: Incorrect syntax near valami.
\nAkkor j\u00f6n el\u0151, ha az SQLCLR assemblyt \u00e9s benne a f\u00fcggv\u00e9nyeket akarja az VS deployolni. T\u00f6bb oka lehet, most az volt, hogy egy .NET oldalon double-t visszaad\u00f3 f\u00fcggv\u00e9ny v\u00e9letlen\u00fcl \u00edgy lett deklar\u00e1lva:<\/p>\n

[SqlFunction(…, TableDefinition = “Datum datetime, Szazalek double”)]<\/p>\n

Mi a hiba benne? SQL Serverben nincs double, csak real \u00e9s float. R\u00e1ad\u00e1sul a C# float az az SQL real \u00e9s a C# double az SQL Server float (kb.). :)<\/p>\n

Az el\u0151bbi helyesen:<\/p>\n

[SqlFunction(…, TableDefinition = “Datum datetime, Szazalek float”)]<\/p>\n

Mi\u00e9rt kellett SQLCLR f\u00fcggv\u00e9nyt \u00edrni? A fut\u00f3 aggreg\u00e1l\u00e1sok (\u00e9n legal\u00e1bbis nem tudok jobbat kurzor n\u00e9lk\u00fcl) o(n2)-es algoritmusok, ezt CLR-ben k\u00f6nnyen meg lehet \u00edrni o(n)-re. Pl:<\/p>\n

\u00ef\u00bb\u00bfusing System;
\nusing System.Collections;
\nusing System.Collections.Generic;
\nusing System.Data;
\nusing System.Data.SqlClient;
\nusing System.Data.SqlTypes;
\nusing Microsoft.SqlServer.Server;<\/p>\n

public class UserDefinedFunctions
\n{
\n internal class Result
\n {
\n public long RowId;
\n public double CumDollarGain;
\n public double TopDollarGain;
\n public double DollarDrawDown;
\n }<\/p>\n

\/\/Input table: create table #trades(RowId long, DollarGain money, other columns possible)
\n [SqlFunction(FillRowMethodName = “FillRow”, DataAccess = DataAccessKind.Read,
\n TableDefinition = “RowId bigint, CumDollarGain float, TopDollarGain float, DollarDrawDown float”)]
\n public static IEnumerable Cumul()
\n {
\n using (var conn = new SqlConnection(“Context connection=true”))
\n {
\n using (var cmd = new SqlCommand(“select RowID, DollarGain from #trades”, conn))
\n {
\n var res = new List();
\n conn.Open();
\n double cumulPrice = 0, topPrice = 0, drawDawn = 0;<\/p>\n

using (SqlDataReader r = cmd.ExecuteReader())
\n {
\n int idCol = r.GetOrdinal(“RowID”);
\n int gainCol = r.GetOrdinal(“DollarGain”);<\/p>\n

while (r.Read())
\n {
\n var price = r.GetDouble(gainCol);<\/p>\n

cumulPrice += price;
\n topPrice = Math.Max(price, topPrice);<\/p>\n

drawDawn += price;
\n drawDawn = Math.Min(drawDawn, 0);<\/p>\n

res.Add(new Result
\n {
\n RowId = r.GetInt64(idCol),
\n CumDollarGain = cumulPrice,
\n TopDollarGain = topPrice,
\n DollarDrawDown = drawDawn
\n });
\n }
\n return res;
\n }
\n }
\n }
\n }
\n public static void FillRow(object obj,
\n out long id,
\n out double cumDollarGain,
\n out double topDollarGain,
\n out double dollarDrawDown)
\n {
\n var r = (Result)obj;
\n id = r.RowId;
\n cumDollarGain = r.CumDollarGain;
\n topDollarGain = r.TopDollarGain;
\n dollarDrawDown = r.DollarDrawDown;
\n }
\n};<\/p>\n

Sajnos nem lehet \u00e1tpasszolni a megnyitott SqlDataReadert a k\u00e9t met\u00f3dus k\u00f6z\u00f6tt, ez\u00e9rt k\u00e9nytelen az ember let\u00e1rolni az eredm\u00e9nyhalmazt. Persze p\u00e1r ezer sorn\u00e1l ez nem gond.<\/p>\n","protected":false},"excerpt":{"rendered":"

Error: Incorrect syntax near valami. Akkor j\u00f6n el\u0151, ha az SQLCLR assemblyt \u00e9s benne a f\u00fcggv\u00e9nyeket akarja az VS deployolni. T\u00f6bb oka…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,25,6,4,21,58],"tags":[],"class_list":["post-998","post","type-post","status-publish","format-standard","hentry","category-net","category-adonet","category-adatbazisok","category-szakmai-elet","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=998"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/998\/revisions"}],"predecessor-version":[{"id":999,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/998\/revisions\/999"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}