Linked Server Query

When I execute the SQL Statement in the SQL Query Analyzer, I get the result
s
returned in 4 seconds but when I use the same query from a stored procedure
and execute it from SQL Analyzer, it is taking too long about 50-55 seconds.
Am I missing something? I have a join between external and internal table.
Thanks!!
Vibs
SELECT
AAPSUser.[ID]
, AAPSUser.First_Name
, AAPSUser.Last_Name
, AAPSUser.Second_Sections,
[UserExtraInfo].[title],
[UserExtraInfo].[profile1_text],
[UserExtraInfo].[profile2_text],
[UserExtraInfo].[profile3_text]
FROM
AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
WHERE
AAPSUser.[ID] = 003479Are you running the query and the proc containg the query locally i.e, same
SQL Server? (Same security creditials as well?) I.e, log into QA and run
the query then directly below (same window) exectue the proc (same query in
proc right?). You can also compare the execution plans as well for any
visable differences.
HTH
Jerry
"vibs" <vibs@.discussions.microsoft.com> wrote in message
news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
> When I execute the SQL Statement in the SQL Query Analyzer, I get the
> results
> returned in 4 seconds but when I use the same query from a stored
> procedure
> and execute it from SQL Analyzer, it is taking too long about 50-55
> seconds.
> Am I missing something? I have a join between external and internal table.
> Thanks!!
> Vibs
> SELECT
> AAPSUser.[ID]
> , AAPSUser.First_Name
> , AAPSUser.Last_Name
> , AAPSUser.Second_Sections,
> [UserExtraInfo].[title],
> [UserExtraInfo].[profile1_text],
> [UserExtraInfo].[profile2_text],
> [UserExtraInfo].[profile3_text]
> FROM
> AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> WHERE
> AAPSUser.[ID] = 003479|||Yes the query and the proc is running from the same query analyzer window on
the same SQL Server. I'll look into the execution plans
Thanks
"Jerry Spivey" wrote:

> Are you running the query and the proc containg the query locally i.e, sam
e
> SQL Server? (Same security creditials as well?) I.e, log into QA and run
> the query then directly below (same window) exectue the proc (same query i
n
> proc right?). You can also compare the execution plans as well for any
> visable differences.
> HTH
> Jerry
> "vibs" <vibs@.discussions.microsoft.com> wrote in message
> news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
>
>

Linked Server Problem from SQL 2005 to SQL 2000 Database

I have been unable to get my linked server to work when running queries against it in SQL Server 2005.

Example SQL Statement on my SQL 2005 server that I'm trying to run:

select top 100 * from [linkedservername].databsename.dbo.tablename

Error Message:

Msg 7416, Level 16, State 1, Line 1

Access to the remote server is denied because no login-mapping exists.

Print screen of the security properties of my SQL 2005 Linked Server:

http://www.webfound.net/linkedserver.jpg

The user sqladmin definitely has the right permissions to the right databases on the target linked server.

On your screenshot, your login is dschinkel, it doesn't have a mapping. If you want all of your accounts to automatically use the apexfm\sqladmin account, you should select the radio button that says "Be made using this security context:" and fill in the login information there.

Be advised that if SQLAdmin has full administrative privileges on the remote server than ANY user with access privileges to the local server will have full admin privileges to the remote server. A very scary proposition indeed.

sql

Linked Server pointing to Access DB

I have added a linked server on my sql 2000 db server, pointing to MS Access 2000 database.

when I fire a select statement :

SELECT * FROM OPENQUERY(NewVerification, 'SELECT id,verifier FROM newverification...accounts')

I get the following error :

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT id,verifier FROM newverification...accounts'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,verifier FROM newverification...accounts'].

I am able to see all the tables thru EM which to means I am successfully able to connect to that DB. But then why is not letting me query that linked server ?

Any help is appreciated.

ThanksHave you tried the query without OPENQUERY? OPENQUERY passes the query to the remote server for processing, which, I believe with Access will not work, as it is not actually a server.

Try:

SELECT id, verifier FROM [newverification]...[accounts]|||I get the following thing :

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].|||Couple of questions...

Is the Access DB local to your SQL server, or on a remote system? I have sometimes had problems accessing remote Access databases, though those were usually on a different subnet, and it was Access preventing the connection because of it's "security" settings.

Is the database password protected?
Have you set up the proper security context?

If it is not protected, right-click on your Linked Server, and select Properties.
On the Security tab, select the last radio button "Be made with the security context:", and enter Admin as the remote login with no password.

Retry the query.

Here's a link for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.aspsql

Linked Server gives Internal SQL Server error

Hi All,

I am accessing a linked server (also a SQL Server) from a stored
procedure. There is an insert statement that I run on a table in the
linked server. This statement causes the Internal SQL Server error. But
if I run the insert statement separately from Query Analyzer, it works
fine! Also, I put the stored procedure directly into the database on
the linked SQL Server and it ran fine on it. Has anybody come across
something of this sort?

Thanks,
Sanjeevmahajan.sanjeev@.gmail.com napisa(a):
> Hi All,
> I am accessing a linked server (also a SQL Server) from a stored
> procedure. There is an insert statement that I run on a table in the
> linked server. This statement causes the Internal SQL Server error. But
> if I run the insert statement separately from Query Analyzer, it works
> fine! Also, I put the stored procedure directly into the database on
> the linked SQL Server and it ran fine on it. Has anybody come across
> something of this sort?
> Thanks,
> Sanjeev

When you start procedure from query analyzer you do it as some user
but when this procedure is started by sql agent as job it is another
user which doesn't have an access to the database linked by linked
server, and this is the problem.

You can try to run this procedure from vbs for example with ado
connection the same as in query analyzer

Linked Server

Is there a SQL statement that can detect if a linked server exists?You can query the sysservers system table in SQL 2000, or the sys.servers
system view in SQL 2005.
--
"Derek Hart" wrote:

> Is there a SQL statement that can detect if a linked server exists?
>
>|||... and if the definition exists and you want to see "if it is there", you
can use the new
sp_testlinkedserver
in 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D97EAED7-F0C4-4956-BE52-576FE29AC5EF@.microsoft.com...
> You can query the sysservers system table in SQL 2000, or the sys.servers
> system view in SQL 2005.
> --
>
> "Derek Hart" wrote:
>|||Most excellent!
"Tibor Karaszi" wrote:

> ... and if the definition exists and you want to see "if it is there", yo
u can use the new
> sp_testlinkedserver
> in 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>