[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

URGENT:  Interface between SQL Server linked server definitions and ASP.NET?

Posted on 2005-05-12
6
Medium Priority
?
217 Views
Last Modified: 2010-03-19
I am stumped on this one.  Let me explain what I am trying to do:

From a client PC which has a MSDE instance running with a database already defined, I am accessing my ASP.NET Web application which has a back-end SQL Server database.   My Web app creates a linked server definition to the MSDE database by calling a a stored procedure which executes sp_addlinkedserver.  My intent is to eventually be able to transfer selected data from the 'master' db down to the MSDE db.

The linked server definition appears on Enterprise Manager, but attempts to run queries against it from the ASP.NET page return a 'SQL Server does not exist or access denied' message.

I added a loopback to my ASP.NET query so that, if it fails the first time, it just tries it again.  This did not work, no matter how many times I re-try it.

BUT, if I do a query against the definition directly from the SQL Server Query Analyzer, the first time it times out but the second time it works.  Once it works, if I go back to the ASP.NET page and run the query against the linked server again, now it works as well!

Can someone explain what is going on?  Is there anyway from my ASP.NET page to fire off the same function as if I were selecting the linked server 'refresh' option from Enterprise Manager or running multiple queries against the linked server from the Query Analyzer?

Thanks for your help!
0
Comment
Question by:rpinson
  • 2
3 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13993420
did u Add login Information for ur Linked Server?

use sp_addlinkedsrvLogin

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]


Melih SARICA
0
 

Author Comment

by:rpinson
ID: 13998711
I am running the following stored proc to try and find the right login to use.  Right now, I have no password on the 'RemoteUser' account on the MSDE side:

CREATE PROCEDURE dbo.lsp_AddLinkedServerLogin
@UserIDParm VARCHAR(50),
@MasterDBNameParm VARCHAR(50),
@SQLOutParm VARCHAR(1000) OUTPUT
AS
DECLARE
@SQL VARCHAR(1000);

SELECT @SQL = 'EXEC sp_AddLinkedSrvLogin ';
SELECT @SQL = @SQL + '@rmtsrvname=''' + @MasterDBNameParm + '_' + @UserIDParm + '''';
SELECT @SQL = @SQL + ',';

SELECT @SQL = @SQL + '@useself=''false''';    
SELECT @SQL = @SQL + ',';


SELECT @SQL = @SQL + '@locallogin=null';  
SELECT @SQL = @SQL + ',';

SELECT @SQL = @SQL + '@rmtuser=''RemoteUser''';

/*SELECT @SQL = @SQL + ',';
SELECT @SQL = @SQL + '@rmtpassword=''REMOTE''';  */

select @SQLOutParm = @SQL;
EXEC(@SQL);

IF @@ERROR <> 0
            BEGIN
                  RETURN -100;
            END

SELECT @SQL = 'EXEC sp_AddLinkedSrvLogin ';
SELECT @SQL = @SQL + '@rmtsrvname=''' + @MasterDBNameParm + '_' + @UserIDParm + '''';
SELECT @SQL = @SQL + ',';

SELECT @SQL = @SQL + '@useself=''true''';      
SELECT @SQL = @SQL + ',';

SELECT @SQL = @SQL + '@locallogin=''Development2\ASPNET''';  
SELECT @SQL = @SQL + ',';

SELECT @SQL = @SQL + '@rmtuser=''RemoteUser''';

select @SQLOutParm = @SQL;
EXEC(@SQL);

IF @@ERROR <> 0
            BEGIN
                  RETURN -200;
            END
GO


FYI, I run the following stored proc to create the linked server definition in the first place:

CREATE PROCEDURE dbo.lsp_AddLinkedServer
@UserIDParm VARCHAR(50),
@MasterDBNameParm VARCHAR(50),
@LocalDBServerParm VARCHAR(50),
@LocalDBNameParm VARCHAR(50),
@SQLOutParm VARCHAR(1000) OUTPUT
AS
DECLARE
@SQL VARCHAR(1000);

SELECT @SQL = 'EXEC sp_AddLinkedServer ';
SELECT @SQL = @SQL + '@Server=''' + @MasterDBNameParm + '_' + @UserIDParm + '''';
SELECT @SQL = @SQL + ',';
SELECT @SQL = @SQL + '@SrvProduct=''''';    
SELECT @SQL = @SQL + ',';
SELECT @SQL = @SQL + '@Provider=SQLOLEDB';  
SELECT @SQL = @SQL + ',';
SELECT @SQL = @SQL + '@Datasrc=''' + @LocalDBServerParm + '''';
SELECT @SQL = @SQL + ',';
SELECT @SQL = @SQL + '@Catalog=''' + @LocalDBNameParm + '''';

select @SQLOutParm = @SQL;
EXEC(@SQL);

IF @@ERROR <> 0
            BEGIN
                  RETURN -100;
            END
GO
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 2000 total points
ID: 14001275
U can Use OpenRowset Function Instead..

Here in an example..
A. Use OPENROWSET with a SELECT and the Microsoft OLE DB Provider for SQL Server
This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

B. Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
   pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO

C. Use the Microsoft OLE DB Provider for Jet
This example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.



Note  This example assumes that Access is installed.


USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS a
GO

D. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.



Note  This example assumes that Access is installed.


USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS o
   ON c.CustomerID = o.CustomerID
GO


this ll work better then creating a linked server everytime..


0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question