Create view "disconnected"

I occasionally need to create or alter a view that references a linked
server, where the linked server is not currently online (or my Internet
connection is not currently connected, or whatever).
An "Alter View" statement that references a linked server name will fail
with "Could not find server", "Login Failed" or other errors, if the server
is not accessible at the moment.
I sometimes don't care about this problem at the time that I *define* the
view; when I *run* the view, the server will be there, and things will
work.
I can't find how to get around this, which seems like a limitation of the
"Create View" and "Alter View" commands.
Thanks for any suggestions.
David Walkerare you runng sql 2000 or 2005?|||David,
Although it is always good to mention your version of SQL Server, I don't
think this behavior has changed between 2000 and 2005. What you are looking
for is 'deferred name resolution' which is only available in certain
circumstances for stored procedures.
http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For 2000
FWIW, creating a stored procedure that refers to a table on an unavailable
linked server also fails.
RLF
"DWalker" <none@.none.com> wrote in message
news:%23ZPNF7g1HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I occasionally need to create or alter a view that references a linked
> server, where the linked server is not currently online (or my Internet
> connection is not currently connected, or whatever).
> An "Alter View" statement that references a linked server name will fail
> with "Could not find server", "Login Failed" or other errors, if the
> server
> is not accessible at the moment.
> I sometimes don't care about this problem at the time that I *define* the
> view; when I *run* the view, the server will be there, and things will
> work.
> I can't find how to get around this, which seems like a limitation of the
> "Create View" and "Alter View" commands.
> Thanks for any suggestions.
> David Walker|||Hi David,
I think that the problem is when a view is being created or modified, SQL
Server will check the objects names, relations etc. Assuming that the
linked server is offline, if we type an error column name for a remote
database table and if offline linked server is allowed in this situation,
how can SQL Server check if the view is valid?
Also, as Russell mentioned that deffered name resolution (DNR) is only
available certain circumstances (table objects) for stored procedures. Even
if SQL Server allows DNR, it still need to store the information in SQL
Server. In this case, if the linked server is offline, the operation will
fail.
Hope that these two points could explain the limitation. If you have any
other questions or concerns, please feel free to post back.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi David,
I am interested in this issue. Could you please let us know if you need
further assistance on this issue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:#5CRGkq1HHA.3916@.TK2MSFTNGP02.phx.gbl:
> David,
> Although it is always good to mention your version of SQL Server, I
> don't think this behavior has changed between 2000 and 2005. What you
> are looking for is 'deferred name resolution' which is only available
> in certain circumstances for stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
> http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For
> 2000
> FWIW, creating a stored procedure that refers to a table on an
> unavailable linked server also fails.
> RLF
>
Thanks for the references. There are cases where I would like to have SQL
defer the name resolution of all objects until I actually run the query.
Apparently, I can't do that. Oh well.
Thanks.
David Walker|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:Plq66h91HHA.360@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I think that the problem is when a view is being created or modified,
> SQL Server will check the objects names, relations etc. Assuming that
> the linked server is offline, if we type an error column name for a
> remote database table and if offline linked server is allowed in this
> situation, how can SQL Server check if the view is valid?
In a few rare cases, I want to tell SQL server NOT to check if the view is
valid until it is run. (For example, if I am working on views or stored
procedures while not connected to the remote server that has the data that
the views or procs refer to.) Apparently I can't do that.
I meant to say earlier that this applies to both SQL 2000 and SQL 2005,
since someone asked me that earlier.
It does explain the limitation, and what I'm asking for is probably an
enhancement. I'll post a suggestion somewhere.
Thanks.
David Walker|||Ken <kshapley@.sbcglobal.net> wrote in news:1186174524.738575.238770
@.r34g2000hsd.googlegroups.com:
> are you runng sql 2000 or 2005?
>
I am running both.
David|||Hi David,
Since this is a product limitaion, I recommend that you give Microsoft
feedback via https://connect.microsoft.com/sql, your feedback will be
routed to Microsoft product team and hope that this feature will be
included in the next release of SQL Server.
Also, I got an idea of using a stored procedure with dynamic T-SQL
statements to perform the query, since dynamic T-SQL can avoid object and
data type check. For example:
CREATE PROCEDURE proc_getRemoteTable()
AS
BEGIN
DECLARE @.strSQL nvarchar(500)
SET @.strSQL = N'SELECT * FROM linked_server_name.dbo.remote_table_name'
EXECUTE sp_executeSQL @.strSQL
END
Could you please let us know whether or not this workaround is acceptable
for your scenario?
We appreciate your understanding that for such product limitaion issues, we
will try our best to work with you for getting a workaround, however
sometimes it may not be able to meet your real expectations. In this case,
your feedback will be greatly appreciated and we really value having you as
a Microsoft customer.
If you have any other questions or concerns, please feel free to let us
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi David,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================