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



Comments

Popular posts from this blog

BizTalk Server 2013 Azure VM Log Shipping and HA for hosts

Azure DocumentDB Owner resource does not exist

SQL Server 2008 - High Memory Usage