Re: "SET FMTONLY ON select .." locks table

This can be true for views or store procedure but when you do bulk
inserts you use tables.

Frediano


2014-10-03 15:46 GMT+01:00 Yavuz Gökırmak <[email protected]>:
> I think this query may have some unexpected behaivours..
>
> http://social.msdn.microsoft.com/Forums/sqlserver/en-US/395f333e-78f7-49a3-8220-f952368153f2/fmtonly-long-running-?forum=transactsql
>
> But actually I don't understand why it locks the table too..
>
> 2014-10-03 17:41 GMT+03:00 Frediano Ziglio <[email protected]>:
>
>> Actually not but as this query is just used to get metadata and as
>> lock last only the time to get this information I don't see the point
>> of locking here.
>>
>> Frediano
>>
>>
>> 2014-10-03 15:30 GMT+01:00 Yavuz Gökırmak <[email protected]>:
>> > Thanks Frediano,
>> >
>> > nolock may solve but this query ( or logic )
>> > is hardcode implemented in freetds,
>> > is it configurable?
>> >
>> > 2014-10-03 17:28 GMT+03:00 Frediano Ziglio <[email protected]>:
>> >
>> >> 2014-10-03 15:21 GMT+01:00 Yavuz Gökırmak <[email protected]>:
>> >> > Hi all,
>> >> >
>> >> > I am using freetds to do bulk load from linux to mssql,
>> >> > it is interesting that we get some problem during "SET FMTONLY ON.."
>> >> query,
>> >> > it is just getting table columns but somehow it gets the table lock
>> and
>> >> > blocks other bulk load operations..
>> >> >
>> >> > do you have any idea,
>> >> > is it possible to disable this "set fmtonly" query and give table
>> column
>> >> > names manually instead..
>> >> > regards.
>> >> >
>> >>
>> >> Hi,
>> >>   probably adding a (nolock) to the query like
>> >>
>> >> SET FMTONLY ON select * from clickstream.dbo.clicks_t(nolock) SET
>> FMTONLY
>> >> OFF
>> >>
>> >> can help.
>> >>
>> >> > dblib.c:1369:dbsqlexec(0xaba400)
>> >> > dblib.c:6862:dbsqlsend(0xaba400)
>> >> > mem.c:615:tds_free_all_results()
>> >> > util.c:156:Changed query state from IDLE to QUERYING
>> >> > write.c:139:tds_put_string converting 73 bytes of "SET FMTONLY ON
>> select
>> >> *
>> >> > from clickstream.dbo.clicks_t SET FMTONLY OFF"
>> >> > write.c:167:tds_put_string wrote 146 bytes
>> >> > util.c:156:Changed query state from QUERYING to PENDING
>> >> > net.c:743:Sending packet
>> >> > 0000 01 01 00 9a 00 00 01 00-53 00 45 00 54 00 20 00 |........ S.E.T.
>> .|
>> >> > 0010 46 00 4d 00 54 00 4f 00-4e 00 4c 00 59 00 20 00 |F.M.T.O. N.L.Y.
>> .|
>> >> > 0020 4f 00 4e 00 20 00 73 00-65 00 6c 00 65 00 63 00 |O.N. .s.
>> e.l.e.c.|
>> >> > 0030 74 00 20 00 2a 00 20 00-66 00 72 00 6f 00 6d 00 |t. .*. .
>> f.r.o.m.|
>> >> > 0040 20 00 65 00 76 00 61 00-6d 00 63 00 6c 00 69 00 | ..........
>> >> ..c.l.i.|
>> >> > 0050 63 00 6b 00 73 00 74 00-72 00 65 00 61 00 6d 00 |c.k.s.t.
>> r.e.a.m.|
>> >> > 0060 2e 00 64 00 62 00 6f 00-2e 00 63 00 6c 00 69 00 |..d.b.o.
>> ..c.l.i.|
>> >> > 0070 63 00 6b 00 73 00 5f 00-74 00 20 00 53 00 45 00 |c.k.s._. t.
>> .S.E.|
>> >> > 0080 54 00 20 00 46 00 4d 00-54 00 4f 00 4e 00 4c 00 |T. .F.M.
>> T.O.N.L.|
>> >> > 0090 59 00 20 00 4f 00 46 00-46 00                   |Y. .O.F. F.|
>> >> >
>> >> > dblib.c:4639:dbsqlok(0xaba400)
>> >> > dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
>> >> > token.c:540:tds_process_tokens(0xabaee0, 0x7ffff1375b30,
>> 0x7ffff1375b34,
>> >> > 0x6914)
>> >> > util.c:156:Changed query state from PENDING to READING
>> >> >
>> >>
>> >> Regards,
>> >>   Frediano
>> >> _______________________________________________
>> >> FreeTDS mailing list
>> >> [email protected]
>> >> http://lists.ibiblio.org/mailman/listinfo/freetds
>> >>
>> >
>> >
>> >
>> > --
>> > Yavuz Gökırmak
>> > Co-Founder & Chief Data Architect
>> > Infoowl Software Solutions & Consultancy
>> >
>> > [email protected]
>> > +90 530 420 00 59
>> > www.infoowl.net
>> > _______________________________________________
>> > FreeTDS mailing list
>> > [email protected]
>> > http://lists.ibiblio.org/mailman/listinfo/freetds
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>
>
>
> --
> Yavuz Gökırmak
> Co-Founder & Chief Data Architect
> Infoowl Software Solutions & Consultancy
>
> [email protected]
> +90 530 420 00 59
> www.infoowl.net
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds