SQLServerCentral / Discuss Content Posted by Michael Coles / Article Discussions / Article Discussions by Author

Free Encryption

Mike C — Mon, 11 Jul 2005 13:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp

Get the new SQL 2000 DBA Toolkit with five encryption algorithms, hashing, phonetic matching, regular expressions and more at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

Blowfish encryption

ubaid_zia14 — Mon, 02 Apr 2012 14:17:25 GMT

Friends. Please help me . i want to encrypt data using blowfish algorithm in MATLAB, but i dont understand, how user enter secret key,which only sender and receiver knows, plz plz.help. thanx

A Google-like Full Text Search

Mike C — Mon, 06 Oct 2008 23:53:03 GMT

Comments posted to this topic are about the item [B]A Google-like Full Text Search[/B]

Double Metaphone Phonectic Matching

Mike C — Mon, 29 Aug 2005 20:57:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/doublemetaphonephonecticmatching.asp

SQL 2000 DBA Toolkit Part 1

Mike C — Thu, 06 Apr 2006 11:51:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

SQL 2000 DBA Toolkit Part 4

Mike C — Thu, 06 Apr 2006 12:18:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart4.asp

Sound Matching and a Phonetic Toolkit

Mike C — Mon, 11 Jul 2005 12:53:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/soundmatchingandaphonetictoolkit.asp

Fulltext search and date column

kathy-481885 — Thu, 31 Mar 2011 08:51:20 GMT

I noticed the date column has a huge impact on my query.to my surprise and newer data take more than a minute to return a result like date >'04-02-2011' however any older date like 1999 up to 2010 returns result in less than a second.why?I set up a FTS catalogue for a text column and my query includes multiple AND conditions.Everything is great except when I search for more recent dates. Example1- dbo.forum_messages.MDate >'2011-02-01' this take over 1:20 minutes to return the result2- dbo.forum_messages.MDate >'2011-02-01' or dbo.forum_messages.MDate <'2000' this takes less than a second to return the resultCan someone explain to me why?Many thanks in advance.

XQuery - using nodes & values - select the attribute vaules

sridevi_mahalingam — Fri, 19 Jun 2009 00:03:47 GMT

I have the table which has the two fieldsCREATE TABLE AgentQueue ( customerId VARCHAR(15) NOT NULL, QueueXML XML NOT NULL )Queue XMl has the format like I am using SQL server 2005.I need the output in below mentioed format using Xquery with in SQL server stored Procedure.Number Name Date1 Srikar12 20090113T17052 nikil123 20090113T17053 supa123 20090113T1705Please let me know if any one knows.

Weird error

rickyTW — Tue, 08 Feb 2011 10:36:54 GMT

When I run a simple query in SQL 2005 I get:Msg 244, Level 16, State 1, Line 1The conversion of the varchar value '102001000010' overflowed an INT1 column. Use a larger integer column.I have combed the data with a fine toothcomb to find the erroneous data but to no avail. The two different people have copied the data (only 4.1 GB, no big deal) to their systems and they have run it without any error. The only difference was that the two systems without the error were SQL 2008 R2. Although I cannot believe that the difference in SQL version would explain the error.What do you think?Thanks!

Annoying problem with XQuery

zoranrdc — Thu, 06 Jan 2011 03:14:13 GMT

Hi,I have following xml document:<Root> <Header> <SomeTag1></SomeTag1> <SomeTag2></SomeTag2> </Header> <Data> <Item ID="1" AdditionalInfo="Some info"> <SubItem ID="1">1</SubItem> <SubItem ID="2">2</SubItem> <SubItem ID="3">3</SubItem> </Item> <Item ID="2" AdditionalInfo="Some info"> <SubItem ID="1">1</SubItem> <SubItem ID="2">2</SubItem> </Item> </Data></Root>I need to extra each SubItem from the xml document into the following table:Data (SubItemId sometype,SubItemValue sometype,ItemId sometype,AdditionalInfo somety),where 'ItemId' and 'AdditionalInfo' are parent's values and 'SubItemValue' is text value of 'SubItem' tag.How to solve this problem in the most efficient way ?Thanks in advance,Zoran

XML Query

Mike C — Tue, 29 Jul 2008 00:14:26 GMT

Comments posted to this topic are about the item [B]XML Query[/B]

base64encode / base64decode datasize limits

djspub — Thu, 07 Oct 2010 14:05:19 GMT

Nice post and useful toolkit.I notice that the function definitions in your ADD.SQL script use varchar(2000) rather than varchar(max). Are there limits on the size of the data that can be passed to the base64encode and base64decode procedures? From a brief look at the .cpp files, it would seem like there are not limits, but I did not do a thorough code review. Would be helpful to know that these routines can arbitrarily large strings.Thank you,David

SQL 2000 DBA Toolkit Part 3

Mike C — Thu, 06 Apr 2006 12:06:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp

Service broker with certificate creation

shannbala — Wed, 25 Aug 2010 06:05:53 GMT

while creating servicebroker endpoint, with certificate name error throws like "Cannot find the object "Certificate name here" because it does not exist or you do not have permissions." how do i rectify

The Joy of Numbers

Mike C — Sun, 13 Aug 2006 20:22:00 GMT

Comments posted to this topic are about the content posted at temp

NULL Equals NULL?

Mike C — Thu, 25 Jan 2007 09:20:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/2829.asp

Some minor problems with grammar.

mceitlin — Mon, 31 May 2010 08:11:09 GMT

Hi Mike, great code! It works fine for me in general but I´m having problems when a word begins with "and" or with "or" like "orthopedics". I´ve change, in SearchGrammar.cs [b]OrOperator.Rule = Symbol("or")[/b] for [b]OrOperator.Rule = Symbol("or ") [/b] (note the blank space after the "or") and it works, but I´m not sure its a good solution.I´m also having problems is with one letter words, if you search "when I grow up" doesn´t return any result, but if you search "when grow up" it returns matching records including the one who has "when I grow up". [b]FORMSOF (INFLECTIONAL, I)[/b] returns nothing.Thanks,Marcelo.

Four Rules for NULLs

Mike C — Sat, 04 Jun 2005 13:33:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp

Does the query engine guarantee index order of numbers table in the join..

stephen mew — Tue, 08 Jan 2008 18:16:52 GMT

Does the query engine guarantee index order of numbers table in the joinwith reference to the second example cited in your article.discussion round the campfire is speculating that if the index isnt in numeric order then the application in the second example will fail with eroneous replacements within the string..Does anyone have any deeper knowledge to dispell this concern ?Many ThanksCurious SQL People....

Not searching text with numbers?

sanpan123 — Tue, 10 Nov 2009 13:20:44 GMT

While testing this against AdventureWorks, if I enter the complete text for title with a number for example Front Reflector Bracket and Reflector Assembly 3 it returns no results. Anything I am doing wrong?

Sic Semper NULL

Mike C — Tue, 13 Mar 2007 07:51:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/2921.asp

Support to search portuguese caracters

ruifrazao — Wed, 19 Aug 2009 05:47:53 GMT

Hi,Have you used the Irony library (Irony.dll) like it is in [url=http://irony.codeplex.com]http://irony.codeplex.com/[/url], or have you made same changes?I am trying to look in Irony code to incorporate portuguese caracters (not suported until now), but when I try to run you code with Irony.dll from [url=http://irony.codeplex.com]http://irony.codeplex.com[/url] gives some error.Thanks & Regards,Rui

Difference between Stuff and Partial update clause write

ezhil-649633 — Thu, 30 Jul 2009 23:29:46 GMT

Hi,Can any body tell me the main difference between the partial update clause WRITE and the STUFF() function.ThanksEzhil

SQL 2000 DBA Toolkit Part 2

Mike C — Thu, 06 Apr 2006 12:01:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

FTS Performance

robw — Wed, 29 Jul 2009 02:27:47 GMT

Hi,I love the post, and its works great , with the caveat on data size.I have this working fine on 100M records, I want to get it to 5 Billion. Once I go above 200M records (av len 15 bytes) performance drops to unacceptable levels. Rebuilding the index takes forever, and doesn't actually seem to work properly once its rebuilt.Is there any way to break up/partition a full text index?Are there any guidelines on scaling FTS up?

transform SQL Server FTS feature into C#

cghersi — Wed, 15 Jul 2009 08:09:52 GMT

Dear Michael,first of all, thank you very much for your articles, always very interesting for me.Let's talk about your article related to the FTS features of SQL server (Google-like search).I have ths problem: I would like to emulate the FTS feature inside a C# application, without calling my SQL server DB.That is, I simply want to have a routine taking in input a text string and a search string, and returning true or false if the search string matches or not the text string.Do you know anything about this issue? Perhaps someone has already solved my problem...Please, let me know something, I'm struggling about this dilemma!!!my email address is: cghersi@foldier.comthank you very much!!Cristiano

special way of inserting XML data

sridevi_mahalingam — Tue, 23 Jun 2009 03:10:48 GMT

Is there any special way of inserting XML data into SQL server / DB2 native XML database using XQuery? If so let me know. I tried with CAST function. Will it raise any performace issue if the size of the XML if too big?

The Hype about SQL 2005 Encryption

rvbhatia1-780122 — Wed, 13 May 2009 08:09:29 GMT

1 out 10 sites or discussion forums highlight about SQL 2005 Encryption but nobody is actually talking about problems/issues with DATA TYPES. Please share samples for encryption/decryption of NUMERIC/DATE datatypes...

Disallowed implicit conversion from data type varchar to data type varbinary

nate.aughe — Wed, 24 Dec 2008 09:49:10 GMT

We used the 'SQL_Encryption_Toolkit.zip' AES process to encrypt SSN in a database and it worked just fine in our development and test environments. When we attempt to move this into our production environment we receive the following error when we attempt to run a script to convert the existing data. Error -2147217913 occurred in Microsoft OLE DB Provider for SQL Server: Disallowed implicit conversion from data type varchar to data type varbinary, table 'TRAININGREGISTRATIONPROD.dbo.Attendee', column 'SsnNbrEnc'. Use the CONVERT function to run this query.All 3 environments are on SQL 2000 SP4 we have verified they are all the same release levels. We are doing the AES 256 encryption. Any ideas?

SQL 2005 Symmetric Encryption

Mike C — Fri, 10 Feb 2006 15:46:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp

Could I get the xp_ source code please

ricva — Fri, 14 Nov 2008 15:53:38 GMT

The link to the code does not wok. I am hoping to source code in order to see if I can get around the export/import issue I am having.Thanks

Error for xp_blowfish dll on SQL 2005 (64bit)

lily-134267 — Mon, 17 Nov 2008 11:43:41 GMT

I used the xp_blowfish dll on SQL 2005 (64bit)? We just moved to a new server and we're getting error message to the effect.Could not load the DLL C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll, or one of the DLLs it references. Reason: 193(C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll is not a valid Win32 application.).

Could I get the xp_ source code please

ricva — Fri, 14 Nov 2008 15:53:27 GMT

The link to the code does not work. I am hoping to source code in order to see if I can get around the export/import issue I am having.Thanks

SQL Server Barcodes

Mike C — Thu, 04 Aug 2005 18:38:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sqlserverbarcodes.asp

Using salts with base 64 encoding decoding

mohammed rafeeq — Tue, 24 Jun 2008 18:19:01 GMT

Hi the article http://www.sqlservercentral.com/articles/Security/freeencryption/1980/deals with base64 based encoding / decoding. but is there any article that deals with salted passphrase based encoding ? i have such a need now.

XML Storage

Mike C — Mon, 21 Jul 2008 20:38:23 GMT

Comments posted to this topic are about the item [B]XML Storage[/B]

SQL Server XML

Mike C — Tue, 08 Jul 2008 23:02:50 GMT

Comments posted to this topic are about the item [B]SQL Server XML[/B]

Name Spaces

Mike C — Tue, 15 Jul 2008 00:11:24 GMT

Comments posted to this topic are about the item [B]Name Spaces[/B]

SQL 2005 Encryption

r.pe — Wed, 25 Jun 2008 15:18:17 GMT

I'm new to this and I was just wondering if there is anything that needs to be considered when restoring a database that uses encryption to another server. Where would the service master keys and database keys come into play when 2 different servers are involved? Let's say the encryption is being tested in as TEST box that will eventually make it to a production box. What do I need to cover?Thanks in advance.