> What's happening here?

"steve9" <steve9@gmail.com> wrote in message
news:1149226347.631426.284620@u72g2000cwu.googlegroups.com...
> Hello,
>
> I'm querying a text file after adding a directory as a linked server.
> Works fine when executed as separate statements:
>
> --separate statements
> --first this
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> 'C:\temp', NULL, 'Text'
> EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
>
> --then this
> select * from txtsrv...[text#txt]
>
> When I execute as a single statement I receive the following error:
>
> Server: Msg 7202, Level 11, State 2, Line 14
> Could not find server 'txtsrv' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
>
> --single statement
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> 'C:\temp', NULL, 'Text'
> EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> select * from txtsrv...[text#txt]
>
> I tried using a waitfor statement to no avail:
> 1)
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> 'c:\temp', NULL, 'Text'
> EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> WAITFOR DELAY '00:00:05'
> SELECT srvname FROM master..sysservers where srvname = 'txtsrv'
> WHILE NOT EXISTS (SELECT srvname FROM master..sysservers where srvname
> = 'txtsrv')
> BEGIN
> WAITFOR DELAY '00:00:05'
> END
> select * from txtsrv...[text#txt]
>
> 2)
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> 'c:\temp', NULL, 'Text'
> EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> SELECT srvname FROM master..sysservers where srvname = 'txtsrv'
> WHILE NOT EXISTS (SELECT srvname FROM master..sysservers where srvname
> = 'txtsrv')
> BEGIN
> WAITFOR DELAY '00:00:05'
> END
> select * from txtsrv...[text#txt]
>
> What's happening here?
>
> thx, steve
>

Dan Guzman wrote:
> > What's happening here?
>
> The entire batch must be compiled before any of the statements can be
> executed. In this case, the batch can't be compiled due to the missing
> server in the SELECT statement. You can run into similar problems when you
> execute DDL statements in the same batch.
>
> The fix is to specify the SELECT in a separate batch or use dynamic SQL.
>
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> 'C:\temp', NULL, 'Text'
> EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> GO
> select * from txtsrv...[text#txt]
> GO
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "steve9" <steve9@gmail.com> wrote in message
> news:1149226347.631426.284620@u72g2000cwu.googlegroups.com...
> > Hello,
> >
> > I'm querying a text file after adding a directory as a linked server.
> > Works fine when executed as separate statements:
> >
> > --separate statements
> > --first this
> > EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> > 'C:\temp', NULL, 'Text'
> > EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> >
> > --then this
> > select * from txtsrv...[text#txt]
> >
> > When I execute as a single statement I receive the following error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 14
> > Could not find server 'txtsrv' in sysservers. Execute
> > sp_addlinkedserver to add the server to sysservers.
> >
> > --single statement
> > EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> > 'C:\temp', NULL, 'Text'
> > EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> > select * from txtsrv...[text#txt]
> >
> > I tried using a waitfor statement to no avail:
> > 1)
> > EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp', NULL, 'Text'
> > EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> > WAITFOR DELAY '00:00:05'
> > SELECT srvname FROM master..sysservers where srvname = 'txtsrv'
> > WHILE NOT EXISTS (SELECT srvname FROM master..sysservers where srvname
> > = 'txtsrv')
> > BEGIN
> > WAITFOR DELAY '00:00:05'
> > END
> > select * from txtsrv...[text#txt]
> >
> > 2)
> > EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp', NULL, 'Text'
> > EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
> > SELECT srvname FROM master..sysservers where srvname = 'txtsrv'
> > WHILE NOT EXISTS (SELECT srvname FROM master..sysservers where srvname
> > = 'txtsrv')
> > BEGIN
> > WAITFOR DELAY '00:00:05'
> > END
> > select * from txtsrv...[text#txt]
> >
> > What's happening here?
> >
> > thx, steve
> >