We have an app that uses ASP.NET MVC3 and SQL Server 2008 that works fine. We need to retrieve data from a SQL 2000 database for use in the MVC app. We have linked the SQL 2000 to SQL 2008 and created views on the SQL 2008 machine to read data, however performance is horrible. We even tested putting the two databases on the same machine and using views to read form one to the other, but the results were similar. It seems using views to read from a second datasource is simply not an option.
我们有一个使用ASP的应用。NET MVC3和SQL Server 2008运行良好。我们需要从SQL 2000数据库中检索数据，以便在MVC应用程序中使用。我们已经将SQL 2000链接到SQL 2008，并在SQL 2008机器上创建视图来读取数据，但是性能很糟糕。我们甚至测试将两个数据库放在同一台机器上，并使用视图从一个数据库读取另一个数据库，但结果是相似的。似乎使用视图从第二个数据源读取数据并不是一个选项。
What is the best way to read data from the second machine? We only require read access, but can't have a dedicated connection from the MVC app, only a secure SQL to SQL connection (it's an accounting system)
从第二台机器读取数据的最佳方式是什么?我们只需要读访问，但不能从MVC app中获得专用连接，只能获得安全的SQL到SQL连接(它是一个记帐系统)
UPDATE: SSMS selects the views as expected (no joins, under 1000 rows), however other clients connections take 10-15 seconds to display the view. Provider possibly?
更新:SSMS按预期选择视图(没有连接，在1000行以下)，但是其他客户端连接需要10-15秒才能显示视图。提供者可能吗?
1 个解决方案
#1
2
Found the answer. ANSI_NULLS needed to be set to TRUE on the database. This issue is difficult to Google for since SQL Server "views" get hijacked by ASP.NET MVC "views". All select statements from native tables worked flawlessly, however an MVC app using DBMSSOCN that selects from a SQL view needs ANSI_NULLS to be set to TRUE on the database. Hopefully that saves someone else a lot of time.
找到了答案。ANSI_NULLS需要在数据库上设置为TRUE。由于SQL Server的“视图”被ASP劫持，这个问题很难被谷歌解决。净MVC“视图”。所有本地表中的select语句都运行良好，但是使用DBMSSOCN从SQL视图中进行选择的MVC应用程序需要在数据库中将ANSI_NULLS设置为TRUE。希望这样能节省很多时间。
#1
2
Found the answer. ANSI_NULLS needed to be set to TRUE on the database. This issue is difficult to Google for since SQL Server "views" get hijacked by ASP.NET MVC "views". All select statements from native tables worked flawlessly, however an MVC app using DBMSSOCN that selects from a SQL view needs ANSI_NULLS to be set to TRUE on the database. Hopefully that saves someone else a lot of time.
找到了答案。ANSI_NULLS需要在数据库上设置为TRUE。由于SQL Server的“视图”被ASP劫持，这个问题很难被谷歌解决。净MVC“视图”。所有本地表中的select语句都运行良好，但是使用DBMSSOCN从SQL视图中进行选择的MVC应用程序需要在数据库中将ANSI_NULLS设置为TRUE。希望这样能节省很多时间。