Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2005 Upgrade - Regular Expression Query

Posted on 2011-04-19
8
Medium Priority
?
1,048 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

0
Comment
Question by:robsamuel2k8
  • 6
8 Comments
 
LVL 16

Accepted Solution

by:
Imran Javed Zia earned 1800 total points
ID: 35425114
Hi,
Please follow the link, hope you will get solution:

http://www.codeproject.com/KB/mcpp/xpregex.aspx

thanks
0
 

Author Comment

by:robsamuel2k8
ID: 35425137
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.
0
 
LVL 6

Assisted Solution

by:hyphenpipe
hyphenpipe earned 200 total points
ID: 35426196
Is the machine you installed 2K5 on the same one that you uninstalled 2K from?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:robsamuel2k8
ID: 35431276
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.
0
 

Author Comment

by:robsamuel2k8
ID: 35501746
I've heard of using CLR / regular expressions, how can I find a function that will replace xp_regex_format?
0
 

Author Comment

by:robsamuel2k8
ID: 35730704
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
0
 

Author Comment

by:robsamuel2k8
ID: 35736397
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!

0
 

Author Comment

by:robsamuel2k8
ID: 35736453
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question