We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL 2005 Upgrade - Regular Expression Query

Medium Priority
1,080 Views
Last Modified: 2012-05-11
Hi,

I am in the middle of an upgrade project on our CRM system. It's a c# based front-end, with a SQL backend.

(we're a recruitment company, so this system stores all our candidate, client, job, placement details.)

Currently, the production system is on SQL 2000 which is working OK but I am in the progress of upgrading to SQL 2005.

I've successfully uninstalled SQL 2000 and installed SQL 2005. From there, I re-attached the database files and re-created the required users. Finally, I changed the compatibility level from SQL 2000 to SQL 2005.

At this point, I'm able to log into the system as normal and everything seems OK. However, when I go to save/amend a job record, I get the following error message:

Could not load the DLL C:\Program Files\Microsoft SQL Server\90\Tools\Binn\xp_regex.dll, or one of the DLLs it references. Reason 1114(error not found)

I have registered the DLLs in the Management Studio and can see that the newly created extended stored procedures are under the 'master' database. Yet, despite this the error still persists.

I can see that the UpdateJobRefNo trigger on our jobs table contains the command to call the xp_regex_format stored procedure.

I have attached the code for this procedure, and would really appreciate if someone could have a look at this for me. I've seen a post online referring to loadlib, which checks the DLL's referenced and any related DLL's. Is this worth running?

Is there an equivalent procedure to XP_REGEX (for SQL 2000) or is there another method I can use?

If you require any more information, feel free to ask.

Thanks in advance!

PS. The IT/LEG/ENG refers to our job reference numbers. For example IT12345-PB (PB is the consultants initial)
 
CREATE TRIGGER UpdateJobRefNo ON dbo.Jobs 
FOR INSERT, UPDATE
AS
BEGIN

	IF (SELECT COUNT(*) FROM INSERTED) = 1
	BEGIN
	
		DECLARE @JobRefNo VARCHAR(20)
		DECLARE @Prefix VARCHAR(3)
		DECLARE @RefNumber VARCHAR(5)
		DECLARE @Initials VARCHAR(12)
		DECLARE @JobId INT
		DECLARE @SectorId INT
	
		SELECT @JobRefNo = JOBREFNO FROM INSERTED
		SELECT @SectorId = SECTORID FROM INSERTED
	
		SELECT @JobId = JobId FROM INSERTED
	
		EXEC master.dbo.xp_regex_format @JobRefNo, '(J|IT|LEG|ENG|IG)([A-Z]{2})?([A-Z]{2})?(\d{4,5})[\d]*', '$4', @RefNumber OUTPUT
	
		IF @RefNumber IS NOT NULL
		BEGIN
			SET @Prefix = CASE @SectorId
				WHEN 47 THEN 'IT'
				WHEN 48 THEN 'LEG'
				WHEN 49 THEN 'LEG'
				WHEN 50 THEN 'ENG'
				WHEN 51 THEN 'IT'
				WHEN 52 THEN 'IT'
				WHEN 53 THEN 'IT'
				WHEN 54 THEN 'IT'
				WHEN 59 THEN 'IT'
				ELSE 'IG' END
			
			SET @Initials = ''
	
			SELECT @Initials = @Initials + '-' + (UPPER(SUBSTRING(U.LOGINNAME, 1, 1) + SUBSTRING(U.SURNAME, 1, 1)))
			FROM (
				SELECT TOP 4 LOGINNAME, SURNAME
				FROM USERS U
				INNER JOIN JOBCONSULTANTS JC ON JC.USERID = U.USERID
				WHERE JOBID = @JobId
				ORDER BY JC.USERRELATIONSHIPID
			) U
	
			UPDATE JOBS
			SET JOBREFNO = @Prefix + @RefNumber + @Initials
			WHERE JOBID = @JobId
		END
	
	END

END

Open in new window

Comment
Watch Question

Consultant Software Engineer - .NET Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob SamuelIT Manager

Author

Commented:
This is the website I first went to, I downloaded the DLL's and registered them in SQL Management Studio. From there, I gave the SQL user the execute permissions required and the error still persists.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob SamuelIT Manager

Author

Commented:
Hi, it's the same server that I uninstalled SQL 2000 from.

Once 2005 is installed, and our database system is up to date I will be looking at a fresh Windows 2008 once things settle down a bit, and probably upgrading to SQL 2008.
Rob SamuelIT Manager

Author

Commented:
I've heard of using CLR / regular expressions, how can I find a function that will replace xp_regex_format?
Rob SamuelIT Manager

Author

Commented:
Does anyone have any thoughts on how to replace the use of the ageing "XP_REGEX_FORMAT" code?

This is the only issue holding me back from my SQL 2000 > 2005 upgrade and I need to get it completed before the end of this month.

Any feedback would be greatly appreciated - either what procedure to use instead of xp_regex_format or if there is another method to achieve the same result.

I look forward to your comments
Rob SamuelIT Manager

Author

Commented:
Hi,

I've done a little research this morning and I think I'm halfway there with regard to a solution...

This is the line of code that I need to get working in SQL 2005:

EXEC master.dbo.xp_regex_format @JobRefNo, '(J|IT|LEG|ENG|IG)([A-Z]{2})?([A-Z]{2})?(\d{4,5})[\d]*', '$4', @RefNumber OUTPUT

It basically parses/declares the format of the job ref no, then formats the results.

Our job ref no's are formatted in the following way:

(Prefix)+(RefNo)+(Initials).

E.g. IT16874-RP (IT job, auto-generated ref is 16874, consultant initials - RP)

The link below from Code Project looks like a slightly updated version of the old xp_regex functions:

http://www.codeproject.com/KB/string/SqlRegEx.aspx

However, there doesn't seem to be a function to replace the old one (xp_regex_format).

Does anyone know how I can re-write that one line of code to get the job reference updating as normal? It's literally the only thing holding the project back.

Once complete, I can finish testing the current version of our client then get it up to the latest version within SQL 2005.

I need to get the SQL 2005 upgrade completed by the end of May and am running out of ideas as to how to get this working. I know enough about SQL to build quite advanced queries/views and carry out the required maintenance, but I'm not a SQL/T-SQL developer and need help with this one.

Thanks in advance, any comments are appreciated!

Rob SamuelIT Manager

Author

Commented:
Is it possible to use the RegExMatch function (http://msdn.microsoft.com/en-us/magazine/cc163473.aspx) to format a string?

I can see it's used to validate data that is input into a system, so for example it would work if we manually entered the job ref no. However, SQL is generating the job ref no and it is not customised by the user in any way.

The only change that will occur is if a different consultant is assigned to that job, then SQL will run this code to re-generate the number.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.