December 8, 2004 at 10:16 am
I found a script here that allow me to find different values across any database. Great. Now here is the deal:
I need to find a GUID which is a FK in many tables, but the script returns an error mesage that states:
"[Microsoft][ODBC SQL Server Driver]Syntax error or access violation"
When the script is used with char values everything worked perfectly, Haven't found any other soutions. Any suggestions? Script used was found under "Find string values" no changes done because it works fine.
Any help is very much appreciated
December 8, 2004 at 4:24 pm
I didn't go looking for that script (if you want people to know, post the exact URL)...
but maybe your problem is matching a guid value in a key... if so you need to wrap it in quotes like
where col = 'A2B67F3F-1B4A-4C37-8DE2-2FFDD34748DF'
if your looking for how to find those columns in syscolumns the xtype appears to be 36 for these.
December 8, 2004 at 10:08 pm
Thank you. I did that already. The script worked perfectly for any other string, but GUID. Here is the exact location of the script
http://www.sqlservercentral.com/scripts/contributions/831.asp
I used this one to create a stored proc to allow me to enter any string at any time. Either way it works fine but not with GUID.
December 9, 2004 at 5:06 pm
I have modified the same script to include UNIQUEIDENTIFIERS and it works. If you are searching for GUIDs, don't include the curly braces.
DECLARE
@VAR1 SYSNAME,
@VAR2 SYSNAME,
@VAR3 NVARCHAR(4000),
@search_str NVARCHAR(1000),
@counter INT,
@dbname SYSNAME
-- SET SEARCH CONDITION HERE - If GUID don't include the curly braces
SET @search_str='125F8440-D3F3-4576-86DB-682F299902E1'
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#FTS_TABLE%')
DROP TABLE #FTS_TABLE
CREATE TABLE #FTS_TABLE (TABLE_NAME VARCHAR(150), CONTENT VARCHAR(1000),
CONTAIN VARCHAR(15), SEARCH_STRING VARCHAR(1000))
SET @counter=0
SELECT @dbname= db_name()
DECLARE CUR CURSOR FOR
SELECT SYSOBJECTS.NAME , SYSCOLUMNS.NAME FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE WHERE
SYSOBJECTS.TYPE ='U' AND
OBJECTPROPERTY(OBJECT_ID(SYSOBJECTS.NAME), N'ISMSSHIPPED') = 0 AND
SYSTYPES.NAME IN('CHAR', 'VARCHAR','TEXT','UNIQUEIDENTIFIER') AND
SYSCOLUMNS.NAME LIKE '%guidRequestIdentification%'
ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLID
OPEN CUR
FETCH NEXT FROM CUR INTO @VAR1, @VAR2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @VAR3=N'IF EXISTS( SELECT 1 FROM '+@VAR1+' WHERE '+@VAR2+' LIKE '''+
'%'+@search_str+'%'+''' )INSERT INTO #FTS_TABLE SELECT '
+''''+UPPER(@VAR1+'('+''+@VAR2)+')'+' HAS STRING '''+' ,'+
@VAR2+','+'''CONTAINING '''+','''+UPPER(@search_str)+''' FROM '+@VAR1+
' WHERE '+@VAR2+' LIKE '+''''+'%'+@search_str+'%'+''''
EXEC SP_EXECUTESQL @VAR3
IF @@ROWCOUNT>0
set @counter=@counter+1
FETCH NEXT FROM CUR INTO @VAR1, @VAR2
END
CLOSE CUR
DEALLOCATE CUR
IF @counter=0
BEGIN
IF CHARINDEX('-',HOST_NAME())<>0
PRINT 'Sorry '+REPLACE(HOST_NAME(),SUBSTRING(HOST_NAME(), CHARINDEX('-',HOST_NAME()), 12),'')+
', no records matching your search string '''+upper(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
ELSE
PRINT 'Sorry '+HOST_NAME()+
', no records matching your search string '''+UPPER(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
END
ELSE
SELECT TABLE_NAME 'TABLE(COLUMN)', CONTENT 'VALUE', CONTAIN ' ',
SEARCH_STRING 'YOUR SEARCH STRING'
FROM #FTS_TABLE WHERE TABLE_NAME NOT LIKE '%#FTS_TABLE%'
Rohini
December 9, 2004 at 5:08 pm
Sorry, please remove the line "AND SYSCOLUMNS.NAME LIKE '%guidRequestIdentification%' " from the above script. I had included them to limit my testing.
Rohini
December 9, 2004 at 5:22 pm
IMHO, maybe the problem lies in the 'LIKE' used to build the Sql stmt that searches your value...
The script has:
SET @VAR3=N'IF EXISTS( SELECT 1 FROM '+@VAR1+' WHERE '+@VAR2+' LIKE '''+
'%'+@search_str+'%'+''' )INSERT INTO #FTS_TABLE SELECT '
where you should take out the 2 '%' and change the LIKE clause to '='. This because a column of type ROWGUIDCOL is not a string (I suppose...)
Gigi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply