Setting FMTONLYOff on SqlDataSource (StoredProcedure)

Question

User-664796593 posted

Good Morning,

I am currently working on a StoredProcedure using a asp:SqlDataSource connector.

When running the test through configure datasource on the SqlDataSource Connector everything works fine, when using Refresh Schema, i receive a 'invalid object name #Purchase_data'

From a few hours of research it appears the problem is setting is related to SET FMTONLY OFF

The only problem is, i cant see where to set this.

Here is a sample of my adapter below

With Thanks

Q

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyFaked-ConnectionString %>"
              DataSourceMode="DataReader"
              SelectCommand="usp_Purchasing"
              SelectCommandType="StoredProcedure">
<SelectParameters>
              <asp:Parameter DefaultValue="false" Name="restrictWarehouseFlag" Type="Boolean" /></SelectParameters>

</asp:SqlDataSource>

Answer 1

User-821857111 posted

The only problem is, i cant see where to set this.

It's T-SQL. You would incorporate it into your stored procedure if you need it.

https://msdn.microsoft.com/en-us/library/ms173839.aspx

Answer 2

User-664796593 posted

Thanks Mike,

Unfortunately the database is a third party one, and we don't have an option to modify it.

Do you know if their is  client side option of achieving this ?

With thanks and appreciate your time

Q

Answer 3

User-821857111 posted

If you can't control the SQL that gets executed (which can only happen on the database), then you need to talk to the third party. 

However, you might want to double check in a dedicated SQL Server forum (e.g. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver) that your conclusion as to the root of the problem is correct, and see if they can offer better advice.