local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

Local instances being used for all linked (remote) instances

I'm getting this bizarre behavior where the local instances are being used i
ntead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other
systems on another domain.
On the Windows 2003 server that has this problem it can only connect to remo
te instances that have similar names and then it overrides it with its own i
nstance. For example:
2003 server domain D2:
Instance: SRV1 (default, using maching name only)
Other systems on other domain D1:
Instance: SRV2 (default, using maching name only)
SRV2\Test
SRV3
SRV3\Test2
Assume all default instances have same sa password.
In enterprise manager on SRV1 you can register remote default instances and
it seems to connect up to them but when you view the databases you realize i
t connected to the local default instance.
Now if you try to connect up to an remote instance name that isn't on the lo
cal SRV1 system or if the password is different, it can't register it.
Finally if you go on SRV2 or SRV3, there is no conflicts registering instanc
es from the other systems including SRV1.
I tried to remove/reinstall SQL (and all instances) from SRV1, but same thin
g occurred.
Any ideas?Try registring the server using the IP and port number instead of the DNS
name. See if that helps..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MJF" <anonymous@.discussions.microsoft.com> wrote in message
news:F7B2A2D9-5304-4B3A-8F39-04C4AB9F390B@.microsoft.com...
> I'm getting this bizarre behavior where the local instances are being used
intead of the remote instances I'm trying to attach to. For background sake,
I'm running on a Windows 2003 Server that is a Domain controller. The other
SQL instances are on other systems on another domain.
> On the Windows 2003 server that has this problem it can only connect to
remote instances that have similar names and then it overrides it with its
own instance. For example:
> 2003 server domain D2:
> Instance: SRV1 (default, using maching name only)
> Other systems on other domain D1:
> Instance: SRV2 (default, using maching name only)
> SRV2\Test
> SRV3
> SRV3\Test2
> Assume all default instances have same sa password.
> In enterprise manager on SRV1 you can register remote default instances
and it seems to connect up to them but when you view the databases you
realize it connected to the local default instance.
> Now if you try to connect up to an remote instance name that isn't on the
local SRV1 system or if the password is different, it can't register it.
> Finally if you go on SRV2 or SRV3, there is no conflicts registering
instances from the other systems including SRV1.
> I tried to remove/reinstall SQL (and all instances) from SRV1, but same
thing occurred.
> Any ideas?
>
>|||Also look at the SQL Client Network Utility on the SRV1 server and see if
there is an alias that points to SRV1. It could be using that alias when
you rgister the other SRV1 servers. If there is one there, remove it and
see what happnes. You should not need an alias on the actual server itself.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Local and Remote Index Servers returning different resultsets

I have a project that uses a linked server in SQL Server 2000 to query a
local Index Server. This works fine.
I've been experimenting with moving the catalog to a remote Index Server and
querying it via Index Server running on the SQL Server machine (using the
four-part SCOPE syntax). I've followed plenty of other pointers relating to
syntax and security and seem to be getting there, but have noticed some
strange behaviour.
I seem to be getting a different number of results returned from the local
and remote index servers when I query them through Query Analyzer. My test
queries (which run over a catalog of about 12,000 documents) are very
simple:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope()')
This returns 12,012 results from NTS-1 (where the Index Server actually is)
and only 9,915 results when run from NTS-2 (where the SQL Server instance
and linked server are set up).
As soon as I add a CONTAINS clause:
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope() where contains(''microsoft'')')
...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
term makes no difference - no results (or errors) are returned from NTS-2 as
soon as I add the CONTAINS clause.
The two machines are setup very similarly - two Win2K Servers with SQL
Server 2000 Ent. Ed. Could the database and collation settings of the linked
server be acting up? Does anyone know what this strangeness is due to? I
seem to be passed all the permissions problems but this one is weird.
Thanks,
Alan
NZ! I used to live in New Plymouth!
results are trimmed by security and scope. You have to verify that
1) you are using the same catalogs
2) the account you are querying with in SQL Server is present on the remote
server and on both servers they have identical rights
3) the content is the same on both servers
4) the default scope (\) is the same on both servers. To verify this open up
ciadv.msc and expand your default catalog. expand the directories folder and
ensure the directories which show up there are the same and they are both
physical, ie don't have globes on them
5) when you are not doing a contains, you are basically enumerating the file
system on the local and remote servers. If AllowEnumeration is turned off,
this will account for this behavior
6) try something like this
select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope(''shallow traversal of ""c:\""'')')
thats scope ( ' ' deep traversal of " " c:\ " " ' ' ) ' )
I think your problems are due to different content and probably that the
index is not finished building on one or more servers.
BTW - are all IS servers on the same version? IE is one on IS 2 (NT 4.0) and
the other on IS 3.x?
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:%23iKyiIYaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server
and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating
to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually
is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the
search
> term makes no difference - no results (or errors) are returned from NTS-2
as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the
linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
|||Hi Alan,
can you share the "..plenty of other pointers relating to syntax and security.."
to set up the configuration you used: Index Server and SQLServer on different
machines ?
Many guys (including me..) posted in this group questions on the argument, but
got no aswer..
Many THX,
Max
"Alan Howard" wrote:

> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
> I've been experimenting with moving the catalog to a remote Index Server and
> querying it via Index Server running on the SQL Server machine (using the
> four-part SCOPE syntax). I've followed plenty of other pointers relating to
> syntax and security and seem to be getting there, but have noticed some
> strange behaviour.
> I seem to be getting a different number of results returned from the local
> and remote index servers when I query them through Query Analyzer. My test
> queries (which run over a catalog of about 12,000 documents) are very
> simple:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope()')
> This returns 12,012 results from NTS-1 (where the Index Server actually is)
> and only 9,915 results when run from NTS-2 (where the SQL Server instance
> and linked server are set up).
> As soon as I add a CONTAINS clause:
> select * from openquery (ImpelHR, 'select filename from
> "NTS-1".ImpelHR..scope() where contains(''microsoft'')')
> ...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
> term makes no difference - no results (or errors) are returned from NTS-2 as
> soon as I add the CONTAINS clause.
> The two machines are setup very similarly - two Win2K Servers with SQL
> Server 2000 Ent. Ed. Could the database and collation settings of the linked
> server be acting up? Does anyone know what this strangeness is due to? I
> seem to be passed all the permissions problems but this one is weird.
> Thanks,
> Alan
>
>