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

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