Failure when trying to call stored proc using WCF-SQL

i have searched 2 days , seen nothing me out error. bit of weird issue. had db on local bts sql instance , worked fine. don't want db on server, moved dev sql server , getting error while running consume adapter service wizard.

configuration of adapter:

uri: mssql://cakeezsql13//unicasource?inboundid=myid

binding properties:

i have both polleddataavailable , polling statement, inboundoperationtype typedpolling

note: if chose other type other typedpolling; i.e.: polling or notification, able pull wsdl down , generate files.. need use typed polling solution.

error:

an error has occurred while retrieving properties selected operation. retrieval of operation metadata has failed while building wsdl @ 'typedpolling'.

stack trace:

microsoft.servicemodel.channels.common.metadataexception: retrieval of operation metadata has failed while building wsdl @ 'typedpolling' ---> system.data.sqlclient.sqlexception: statement or function must executed in context of user transaction.

   @ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection)

   @ system.data.sqlclient.sqlinternalconnection.onerror(sqlexception exception, boolean breakconnection)

   @ system.data.sqlclient.tdsparser.throwexceptionandwarning()

   @ system.data.sqlclient.tdsparser.run(runbehavior runbehavior, sqlcommand cmdhandler, sqldatareader datastream, bulkcopysimpleresultset bulkcopyhandler, tdsparserstateobject stateobj)

   @ system.data.sqlclient.sqldatareader.hasmorerows()

   @ system.data.sqlclient.sqldatareader.hasmoreresults()

   @ system.data.sqlclient.sqldatareader.nextresult()

   @ microsoft.adapters.sql.sqladaptermetadataresolverhandler.resolveoperationmetadata(sqladapterconnection sqladapterconnection, metadatalookup metadatalookup, string operationid, isqladapterbindingproperties bindingproperties, timespan timeout)

   @ microsoft.adapters.sql.asdkconnectionhandler.microsoft.servicemodel.channels.common.imetadataresolverhandler.resolveoperationmetadata(string operationid, timespan timeout, typemetadatacollection& extratypemetadataresolved)

   @ microsoft.servicemodel.channels.common.design.metadatacache.getoperationmetadata(string uniqueid, guid clientid, timespan timeout)

   @ microsoft.servicemodel.channels.common.design.wsdlbuilder.searchbrowsenodes(metadataretrievalnode[] nodes, wsdlbuilderhelper helper, timeouthelper timeouthelper)

   --- end of inner exception stack trace ---

server stack trace:

   @ microsoft.servicemodel.channels.common.design.adapterexceptions.throwmetadataexception(string errormessage, object arg, object source, exception innerexception)

   @ microsoft.servicemodel.channels.common.design.wsdlbuilder.searchbrowsenodes(metadataretrievalnode[] nodes, wsdlbuilderhelper helper, timeouthelper timeouthelper)

   @ microsoft.servicemodel.channels.common.design.wsdlbuilder.generateoperationschemas(wsdlbuilderhelper helper, metadataretrievalnode[] nodes, timespan timeout)

   @ microsoft.servicemodel.channels.common.design.wsdlbuilder.getwsdl(metadataretrievalnode[] nodes, uri uri, timespan timeout)

   @ microsoft.adapters.sql.sqladapterwsdlretrieval.microsoft.servicemodel.channels.common.iwsdlretrieval.getwsdl(metadataretrievalnode[] nodes, uri uri, timespan timeout)

   @ microsoft.servicemodel.channels.common.design.metadataexchanger.processmetadataget(message message, uri target, timespan timeout, metadatalookup metadatalookup)

   @ microsoft.servicemodel.channels.common.design.metadataexchanger.processmetadatamessage(message message, uri target, timespan timeout, metadatalookup metadatalookup, message& replymessage)

   @ microsoft.servicemodel.channels.common.channels.adapterrequestchannel.request(message message, timespan timeout)

   @ system.servicemodel.dispatcher.requestchannelbinder.request(message message, timespan timeout)

   @ system.servicemodel.channels.servicechannel.call(string action, boolean oneway, proxyoperationruntime operation, object[] ins, object[] outs, timespan timeout)

   @ system.servicemodel.channels.servicechannelproxy.invokeservice(imethodcallmessage methodcall, proxyoperationruntime operation)

   @ system.servicemodel.channels.servicechannelproxy.invoke(imessage message)

exception rethrown @ [0]:

   @ system.runtime.remoting.proxies.realproxy.handlereturnmessage(imessage reqmsg, imessage retmsg)

   @ system.runtime.remoting.proxies.realproxy.privateinvoke(messagedata& msgdata, int32 type)

   @ microsoft.servicemodel.channels.imetadataretrievalcontract.getmetadata(metadataretrievalnode[] nodes)

   @ microsoft.servicemodel.channels.tools.metadatasearchbrowse.metadatapanel.getwsdl(metadataretrievalnode[] nodes)

   @ microsoft.servicemodel.channels.tools.metadatasearchbrowse.metadatapanel.btnproperties_click(object sender, eventargs e)

any appreciated. -chuck

 

 

 

---

charles

found error!!!

the error in stored proc created. dba put row locking statements causing issue.. don't use code

declare @result int, @msg nvarchar(1000) 			exec @result = sp_getapplock @resource = 'messagequeue$pop', @lockmode = 'exclusive' 			if @result < 0 			 begin  				set @msg = n'failed application lock. reason: ' +  								case @result 									when -1 'the lock request timed out.' 									when -2 'the lock request canceled.' 									when -3 'the lock request chosen deadlock victim.' 									else 'indicates parameter validation or other call error.' 								end  				raiserror (@msg,16,1); 			 end

---

charles

BizTalk Server  >  BizTalk Server Adapters and Adapter Pack