Relace function of mysql

Hi,
Want to know that is their any option of inserting a record if the record does not exist in the table..while firing update query.
In mysql, we have replace which updates or inserts the record.
do let me know
thanx,
vivek.No SQL Server does not have a command like that. In my case all of my commands are done within a stored procedure, so I first check to see if the record exists, if yes then UPDATE else INSERT.

IF EXISTS (SELECT * FROM myTable WHERE pkey = @.pkey) BEGIN
UPDATE myTable
.
.
.
.
END ELSE
INSERT myTable
.
.
.
.
END|||Thanx,

will surely try with Stored procs.

regards,
vivek.

Regular Expressions in SQL 2005 using C#

Hi all,

i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?

here is the C# class that I deploy to a db using VS05.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class RegExTest

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static string RegExMatch(string pattern, string matchString)

{

Regex r1 = new Regex(pattern);

if (r1.Match(matchString.TrimEnd(null)).Success)

{

Regex testReg = new Regex(pattern);

Match regMatch = testReg.Match(matchString);

string runnumber = regMatch.ToString().TrimEnd('_');

runnumber = runnumber.TrimStart('_');

return runnumber;

}

else

{

return null;

}

}

};

Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:

use northwind

go

declare @.teststring varchar(50),

@.pattern varchar(10),

@.out varchar(50)

set @.teststring = 'dw_012_34.ext'

set @.pattern = '_\\d{3}_'

set @.out = dbo.RegExMatch(@.pattern, @.teststring )

select @.teststring

select @.pattern

select @.out

Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?

Hi,

without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!

|||

i am getting the error while calling

dbo.RegExMatch(@.pattern, @.teststring )

this line in stored procedure,

i have already created the assembly.

still i am getting the error!

|||Which error ?

HTH, Jens K. Suessmeyer.

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

the scene is this:

after creating an assembly name Reg_ex,

i need to call the method regmatch which is in Reg_ex, in class regtest.

when i say"

CREATE PROCEDURE RegExMatch

AS external name Reg_Ex.RegTest.RegExMatch

error is:

Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1

Could not find Type 'RegTest' in assembly 'Reg_Ex'.

Regular Expressions in SQL 2005 using C#

Hi all,

i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?

here is the C# class that I deploy to a db using VS05.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class RegExTest

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static string RegExMatch(string pattern, string matchString)

{

Regex r1 = new Regex(pattern);

if (r1.Match(matchString.TrimEnd(null)).Success)

{

Regex testReg = new Regex(pattern);

Match regMatch = testReg.Match(matchString);

string runnumber = regMatch.ToString().TrimEnd('_');

runnumber = runnumber.TrimStart('_');

return runnumber;

}

else

{

return null;

}

}

};

Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:

use northwind

go

declare @.teststring varchar(50),

@.pattern varchar(10),

@.out varchar(50)

set @.teststring = 'dw_012_34.ext'

set @.pattern = '_\\d{3}_'

set @.out = dbo.RegExMatch(@.pattern, @.teststring )

select @.teststring

select @.pattern

select @.out

Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?

Hi,

without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!

|||

i am getting the error while calling

dbo.RegExMatch(@.pattern, @.teststring )

this line in stored procedure,

i have already created the assembly.

still i am getting the error!

|||Which error ?

HTH, Jens K. Suessmeyer.

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

the scene is this:

after creating an assembly name Reg_ex,

i need to call the method regmatch which is in Reg_ex, in class regtest.

when i say"

CREATE PROCEDURE RegExMatch

AS external name Reg_Ex.RegTest.RegExMatch

error is:

Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1

Could not find Type 'RegTest' in assembly 'Reg_Ex'.

Registration of CLR-function sample fails

HI

I'm trying to reproduce the VB CLR sample concat function from the SQL documentation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/5a188b50-7170-4069-acad-5de5c915f65d.htm)

I've installed Visual Studio 2005 and compiled the DLL which seems to work fine, but when I try to register the DLL with this call:

create assembly sqlfuncdll from 'D:\SQLdata\dll\SQLfunctions.dll'

I got this error:

Msg 10301, Level 16, State 1, Line 1

Assembly 'SQLfunctions' references assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

When I remove the system.windows.form reference from the VB project (my suggestion is : I don't need the forms) then the project won't builld, so somehow it needs it.

What sould I do to get this DLL working?

hans

What type of Visual Studio project are you using? If you make a class library rather than a Windows Forms application then just the code in the documentation alone should compile fine without the reference.

--Bonnie

|||

The application type of the project is indeed "Class Library".

When I remove the System.Windows.Forms reference from the project, then the compilation of the DLL fails, I don't get any error message why, it just don't work. When I put the reference back, then the compilation is succesfull. I can't get why it needs this reference.

Some how I should get this project working without the System.Windows.Forms reference, because in the SQL documentation I found that this library is not supported and not linked by default :

http://msdn2.microsoft.com/en-us/library/ms403279.aspx

I started the VB project from scratch and now it does not need the System.Windows.Forms reference I don't understand why, but some how this reference was needed.

|||Most people make the mistake that they want to use MessageBox.Show in their Assemblies, which is sure not supported by the SQL CLR. If you are not sure which command is preventing you from registering the assembly, send the code over and we / I will have a look on it.

Jens K. Suessmeyer.

http://www.sqlserver2005.de