ODBC - FMTONLY

Hi There

I need to know if the "perform translation for character data" odbc connection option is responsible for SET FMTONLY statements, i think it is but i cannot find 100% confirmation from knowledge base articles or various searches.

Secondly if this option is unchecked will it stop FMTONLY statements completely.

Thirdly if it is enabled, what is responsible for FMTONLY statements not having a where clause ? the odbc driver or the application using the odbc connection, i am sure it is the application but once again , i need confirmation.

Thank YouNo, the two are seperate. 'SET FMTONLY' is used when the driver needs metadata before a statement has been executed. The driver turns FMTONLY OFF as soon as it has the metadata. You can check what's happening with SQL Profiler. The driver uses FMTONLY internally inresponse to some sequences of ODBC calls. An application could also execute SET FMTONLY statements itself. An ODBC trace would show if the application is doing this.|||Hi Chris

That is my issue.Millions of set fmtonly statements are being generated.
So this is directly application related?
My issue is that no FMTONLY statements have where clauses, is this also application responsible?

I have issues where sometimes a FMTONLY statements that normally takes 0 Duration takes 60-90 seconds, if you know about this please check out the TSQL forum for my question.

Do you have a good link or article about ODBC and specifically FTMONLY?

Thank You|||The following looks as though it may be related to your problem

http://support.microsoft.com/kb/836830/

FMTONLY is used by ODBC and OLE DB to get metadata for a query qithout actually executing the query. Sometimes the query would get executed and this would explain the long execution times you are seeing.

What versions of software are you using?

ODBC - FMTONLY

Hi There

I need to know if the "perform translation for character data" odbc connection option is responsible for SET FMTONLY statements, i think it is but i cannot find 100% confirmation from knowledge base articles or various searches.

Secondly if this option is unchecked will it stop FMTONLY statements completely.

Thirdly if it is enabled, what is responsible for FMTONLY statements not having a where clause ? the odbc driver or the application using the odbc connection, i am sure it is the application but once again , i need confirmation.

Thank YouNo, the two are seperate. 'SET FMTONLY' is used when the driver needs metadata before a statement has been executed. The driver turns FMTONLY OFF as soon as it has the metadata. You can check what's happening with SQL Profiler. The driver uses FMTONLY internally inresponse to some sequences of ODBC calls. An application could also execute SET FMTONLY statements itself. An ODBC trace would show if the application is doing this.|||Hi Chris

That is my issue.Millions of set fmtonly statements are being generated.
So this is directly application related?
My issue is that no FMTONLY statements have where clauses, is this also application responsible?

I have issues where sometimes a FMTONLY statements that normally takes 0 Duration takes 60-90 seconds, if you know about this please check out the TSQL forum for my question.

Do you have a good link or article about ODBC and specifically FTMONLY?

Thank You|||The following looks as though it may be related to your problem

http://support.microsoft.com/kb/836830/

FMTONLY is used by ODBC and OLE DB to get metadata for a query qithout actually executing the query. Sometimes the query would get executed and this would explain the long execution times you are seeing.

What versions of software are you using?

Occurs in a field

Hi,

How do find out how many times a character occurs in a field thru an expression? Like the occurs() function in FoxPro

Thanks

Any suggestions?

occurences of a character

How can I get a count of occurences of a specific character in a string?By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

You put the statements together, and then come back for a review.|||Not sure how to put all of that together.

By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

You put the statements together, and then come back for a review.|||omg plz, not a while loop

ur doing it wrong (http://www.google.com/search?q=%22ur+doing+it+wrong%22+%2Blolcats) ;)

select len(str) -
len(replace(str,'x',''))
as number_of_xs
:)|||Thanks, that is doing it. I have never been very good at loops, and try to avoid them. Knew there has to be a quick way to do this.|||omg plz, not a while loop

ur doing it wrong (http://www.google.com/search?q=%22ur+doing+it+wrong%22+%2Blolcats) ;)


select len(str) -
len(replace(str,'x',''))
as number_of_xs
:)

naw ... not wrong ... just misguided. String character inspection was the first thing that came to mind. I obviously like your code much better than the cobbled contraption mine would be. And it would even work as a set based solution.

Thanks rudy - this one goes into the toolkit!|||Una caveat...

Be aware that LEN does not count any trailing blanks, so if that possibility exists (as in this example, when there is a blank and then a couple of the target characters), you'll have to code to handle it...
DECLARE @.str varchar(24)

SET @.str = 'xx xuxs ssx xx'

select @.str as OrigStr,
len(@.str) as lenOfOrigStr,
'!' + replace(@.str,'x','') + '!' as XLessStr,
len(replace(@.str,'x','')) as LenOfXLessStr,
len(@.str) - len(replace(@.str,'x','')) as number_of_xs

OrigStr lenOfOrigStr XLessStr LenOfXLessStr number_of_xs
----- ---- ---- ---- ---- ----
xx xuxs ssx xx 14 ! us ss ! 6 8 Mind you, I found this by accident, but I R shtill shmart.

might I suggest something cludgy, like:select len(@.str) - len(replace((@.str + '!'),'x','')) + 1 as number_of_xs