• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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

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
rpinson
Asked:
rpinson
  • 2
1 Solution
 
Melih SARICAOwnerCommented:
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
 
rpinsonAuthor Commented:
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
 
Melih SARICAOwnerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now