.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

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

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

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

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.Net custom resolver

Can anyone point me towards a source code listing for a working replication
custom resolver written in .Net (C, VB etc dont mind which)?
I just need the skeletion of it and not the actually implementation of the
conflict resolution.
cheers
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
news:%23nNkqybcFHA.1556@.TK2MSFTNGP10.phx.gbl...
> Can anyone point me towards a source code listing for a working
replication
> custom resolver written in .Net (C, VB etc dont mind which)?
> I just need the skeletion of it and not the actually implementation of the
> conflict resolution.
> cheers
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
news:%23nNkqybcFHA.1556@.TK2MSFTNGP10.phx.gbl...
> Can anyone point me towards a source code listing for a working
replication
> custom resolver written in .Net (C, VB etc dont mind which)?
> I just need the skeletion of it and not the actually implementation of the
> conflict resolution.
> cheers
>
|||Try chapter 30 from the resource kit. You can get it online at
http://www.microsoft.com/resources/d...rt8/c3061.mspx
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
news:%23nNkqybcFHA.1556@.TK2MSFTNGP10.phx.gbl...
> Can anyone point me towards a source code listing for a working
replication
> custom resolver written in .Net (C, VB etc dont mind which)?
> I just need the skeletion of it and not the actually implementation of the
> conflict resolution.
> cheers
>
|||Been there.... its only covers vb (not dotnet) and no sample code either
(the samples from SQL devtools are old vb - again not in even in dotnet)
I'm *really* after an experienced developer who has done SQL with
replication and has some code as BOL, RK, Google etc has turned up nothing
of use
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eSqhs6bcFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Try chapter 30 from the resource kit. You can get it online at
> http://www.microsoft.com/resources/d...rt8/c3061.mspx
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
> news:%23nNkqybcFHA.1556@.TK2MSFTNGP10.phx.gbl...
> replication
>
|||But you must use COM, the merge replication subsystem doesn't understand
anything else - even in SQL 2005 it still (from my reading of the
documentation) uses COM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
news:%239nYDNccFHA.2768@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Been there.... its only covers vb (not dotnet) and no sample code either
> (the samples from SQL devtools are old vb - again not in even in dotnet)
> I'm *really* after an experienced developer who has done SQL with
> replication and has some code as BOL, RK, Google etc has turned up nothing
> of use
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eSqhs6bcFHA.3504@.TK2MSFTNGP12.phx.gbl...
http://www.microsoft.com/resources/d...rt8/c3061.mspx
>
|||Exactly - but I need a resolver written in dotnet (so I can call managed
code) but need it to expose itself as com for SQL. The more I'm reading it
does seem to me that again the next version of SQL is not the major upgrade
that it should be. I'm all for backward compatibility but it would be nice
if we could take a step forward without nailing one foot to floor.
Still on the look out for a solution for what is a really simple problem -
the lines of code I want to call amount to probably no more than 5 yet SQL
seems to hinder at every turn - perhaps I should swap out the database
backend from SQL and use Exchange instead ;-)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23%23VjOGecFHA.2520@.TK2MSFTNGP09.phx.gbl...
> But you must use COM, the merge replication subsystem doesn't understand
> anything else - even in SQL 2005 it still (from my reading of the
> documentation) uses COM.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "msnews.microsoft.com" <nospam_nige66_@.hotmail.nospam> wrote in message
> news:%239nYDNccFHA.2768@.tk2msftngp13.phx.gbl...
> http://www.microsoft.com/resources/d...rt8/c3061.mspx
>
|||All you have to do is creating a COM component that implements the
ICustomResolver interface. Make sure you have selected the 'Register for COM
interop' setting in the project-properties in Visual Studio. And you have to
use the regasm utility.
good luck,
Marco
// just a small example:
using System;
using System.Runtime.InteropServices;
using SQLResolver;
using System.Data;
using System.Xml;
namespace ReplicationConflictResolver
{
[GuidAttribute("12877204-7044-4abc-9833-3B839427972C")]
public class ThisConflictResolver : ICustomResolver
{
public ThisConflictResolver()
{
}
#region ICustomResolver Members
public void Reconcile(IReplRowChange pRowChange, int dwFlags,
IReplRowChange pvReserved)
{
// Determine the type of change condition
REPOLE_CHANGE_TYPE changeType;
pRowChange.GetChangeType(out changeType);
// Handle the conflict
switch (changeType)
{
case REPOLE_CHANGE_TYPE.REPOLEChange_DownloadDeleteFail ed:
{
// Handle this particular conflict, by rollbacking the change
pRowChange.DoDummyUpdate(REPOLE_BOOL.REPOLEBool_TR UE,
REPOLE_BOOL.REPOLEBool_FALSE);
}
case .......
}
public void GetHandledStates(ref int ResolverBm)
{
ResolverBm = (int) (REPOLE_CHANGE_TYPE.REPOLEAllConflicts |
REPOLE_CHANGE_TYPE.REPOLEAllErrors );
}
#endregion
}
}
"msnews.microsoft.com" wrote:

> Can anyone point me towards a source code listing for a working replication
> custom resolver written in .Net (C, VB etc dont mind which)?
> I just need the skeletion of it and not the actually implementation of the
> conflict resolution.
> cheers
>
>

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab
|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.NET 2.0/SQL 2000 Named Pipes Provider, error: 40

I have an app that I've written in .NET 2.0 using SQL 2000 as the back end. It works fine in development but when I move it out to our QA server I get the following error.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

One of the odd things I notice is that the error message references SQL Server 2005 when that's not installed on our QA box, only SQL 2000 is on it.

I've checked the server and TCP/IP and Named Pipes are enabled.

Here is my connection string

<add name="myConnectionString" connectionString="Data Source=(local);Integrated Security=false;Initial Catalog=TestDatabase;User Id=myUsername;Password=myPassword;" providerName="System.Data.SqlClient"/>

There's at least one other .NET 2.0 application running on this server that accesses the database so I know that it can work! However, have access to the source code for that application so it's not any help with my troubleshooting.

Any thoughts or ideas?

Is the SqlServer 2000 service running on the same machine? You connection configuration is pointed to a (local) copy of SqlServer. Also, make sure that the myUsername exists on the QA server.

|||

Yes. Sql 2000 is on the same box.

And I double checked that myUsername exists in that QA SQL server and has rights to my database.

Thanks though.

I'll post back when I figure it out.

If anyone else wants to weigh in I'm all ears.

|||

Hi,

I had the same problem.

Check this Registry entry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo.

Are there any entries that reference the SQL server you want to use (DBMPNTW = named pipes)? If so remove them.

Regards,

Thierry