CLR Table valued functions error

Hi,

I'm trying to create a CLR functions

this is the Sql Function attribute and the FillRowMethod signature

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read,

FillRowMethodName = "FillRows",IsPrecise=true,

TableDefinition = "SOCIETA nvarchar(55),CLIENTE nvarchar(150),NUMEROCONTRATTO nvarchar(255),FIRMA datetime,CHIUSURA datetime,AUTORIZZATO float"

)]

publicstaticIEnumerable dbf_Create_RiepilogoAccordi(SqlInt32 commessa, SqlInt32 tipo_commessa, SqlInt32 progetto, SqlInt32 DAC, SqlInt32 figura, SqlDateTime dataFatturazioneDa, SqlDateTime dataFatturazioneA)

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, SqlDouble AUTORIZZATO)

Whe I try to deploy my function, I get the following error:

Error 1 Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function 'dbf_Create_RiepilogoAccordi' due to column 6. CM.Reports.SIA.RiepilogoAccordi

I get this error whichever combination of name/value I use for column 6

Can someone help me?

Thanks

Marco

I forgot to mark parameter AUTORIZZATO as an out parameter. the signature should be

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, out SqlDouble AUTORIZZATO)

CLR DDL Triggers with SqlTrigger Attribute?

Does anyone have an example of using the SqlTrigger attribute for a DDL trigger? Cannot seem to locate one?What do you want to do ?

Jens Suessmeyer.

http://www.sqlserver2005.de
|||

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// what values do you plug into these parms?
// [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

|||

for example...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="DATABASE", Event="CREATE_ASSEMBLY")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

i can build this assembly and even deploy it via VS but no trigger exists with the name of Trigger1? What gives, tell me what I am doing stupid here guys!

|||further more there are no dependencies listed on the assembly called Trigger1?|||

Target="AdventureWorks"

doesnt work either....I am about to the point of assuming you cannot do this via VS and your only option for deploying managed DDL triggers is manual...

|||This one worked for me :

[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database",Event = "FOR CREATE_TABLE")]

public static void ddltrigger()

{

// Replace with your own code

SqlContext.Pipe.Send("Trigger FIRED");

}

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I believe the problem was that I omitted the FOR in my event="" parm...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database", Event = "FOR CREATE_TABLE")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

that code works when I used a new project. Either way you gave me my example. Thread solved!

thx dude. join www.redmondsociety.com we'd love to have u as one of our first members.

|||

BTW...try using this code with autoDeploy...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "all server", Event = "FOR DDL_SERVER_LEVEL_EVENTS")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

error at deployTime: "Server-Level AutoDeployment is not supported"..little tid-bit of knowledge resulting from this.