.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

Help connecting with Linked Server !?

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 

Hello,

I need to set up a linked server.

I connect to a remote desktop, that has JXExplore (Active directory) data store on it.  I connect to the RD with UserName= abc Password=123.

Then I connect to another remote desktop that has SQL 2008 on it. This is where I need to set up the linked server.  I have read a million forums/articles on this but I CANNOT get connected for some reason.  I have tried to query it and I have also tried using SQL Server Business Intelligence method and can't connect.

I created the linked server like so...

sp_addlinkedserver 'ADSI', 'Active Directory Service Interface', 'ADSDSOObject', 'adsdatasource'    <--Should I put my host name in for adsdatasource??

I created the Login like... (Here is where I 'Think' I go wrong)

sp_addlinkedsrvlogin 'ADSI', false, 'ls9', 'summit.local\abc', '123'  <-- ls9 is the login name I use for SQL, summit.local is my host name, abc is the username to connect to the remote desktop the JXExplore is on and 123 is the password

 

I wrote some openquery queries to try


View Complete Post


More Related Resource Links

Help connecting with Linked Server!?

  

Hello,

I need to set up a linked server.

I connect to a remote desktop, that has JXExplore (Active directory) data store on it.  I connect to the RD with UserName= abc Password=123.

Then I connect to another remote desktop that has SQL 2008 on it. This is where I need to set up the linked server.  I have read a million forums/articles on this but I CANNOT get connected for some reason.  I have tried to query it and I have also tried using SQL Server Business Intelligence method and can't connect.

I created the linked server like so...

sp_addlinkedserver 'ADSI', 'Active Directory Service Interface', 'ADSDSOObject', 'adsdatasource'    <--Should I put my host name in for adsdatasource??

I created the Login like... (Here is where I 'Think' I go wrong)

sp_addlinkedsrvlogin 'ADSI', false, 'ls9', 'summit.local\abc', '123'  <-- ls9 is the login name I use for SQL, summit.local is my host name, abc is the username to connect to the remote desktop the JXExplore is on and 123 is the password

 

I wrote some openquery queries to try and pull the data but my "LDAP://...." might have been wrong.

I do have all the Base DN, User DN, password, port number to connect to the JXExplore. I just have no idea where to input it all in my SQL statements


Linked Server: error connecting using link

  

I'm pretty much at wits end because I can't see anything that would cause the problem after searching through several blogs.

So my question is can anyone help me with a SS2008 link service to a SS2005 instance?

The scenario is this:::

I am having trouble with a SS2008 linked server connection to a SS2005 (limited install) server.  The SS2005 server has been running perfectly fine for the past few years and we are upgrading our development environments to SS2008. 

The SS2005 BOUNCEBAK server is linked to by several of our other SS and Oracle databases and continues to work perfectly as a linked server to other servers.

The SS2008 server I am working with has working linked server connections to other SS2005 servers and is not giving any errors.  Named pipes is turned on and TCP is higher in the configuration order for communications protocols.

I have added a synonym name for the target server called BOUNCEBAK which is our company standard and is identical to what other servers have in their configuration.

I am able to connect to the BOUNCEBAK SS2005 server using the synonym in a management studio connection, so I'm pretty sure its not the synonym.

I have setup and verified by login a common user on the two servers (i.e. TestUser) with the same password on both (so as to make the test process simpler).&n

Connecting to MS SQL Server database using webmatrix.

  

Hi,

I am novice  user to raizor syntax using webmatrix and i am trying to connect MS Sql server database (instead of using .sdf file i am using .mdf file) using the tool webmatrix for startersite.

The connection string that i setup as follows.

<?xml version="1.0"?>

<configuration>

    <system.web>
        <compilation debug="false" targetFramework="4.0" />
    </system.web>
    <connectionStrings>
    <add name="OESConnectionString" connectionString="Data Source=.\SQLServer2005;AttachDbFilename=|DataDirectory|\OES_Data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
    </connectionStrings>

</configuration>

And i am using websecurity helper as well and the setup is as follows.

WebSecurity.InitializeDatabaseConnection("Data Source=INLTE5556\\SQLServer2005;Initial Catalog=OES;Integrated Security=True", "System.Data.SqlClient", "UserProfile", "UserId", "Email", true);

When i am trying to insert the user details into database i am getting the below error.

Format of t

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

SQL Server 2008 not connecting to servlet using netbeans

  
using latest driver from MS site.   added jar and dll in jdk/bin and dll in windows\systems32 , also added library in netbeans added -Djava.library.path="D:\libraries\mssql\auth\x86" in netbeans run. Still getting com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication exception.

Linked Server to access Excel 2007

  
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???

Linked server to access 2010

  
I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file).  (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).  What are the proper connection string settings?  (This is after right clicking linked servers in ssms and selecting "add new linked server")  The below settings don't work: Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider Product name: Access Data source: C:\foopath\foo.mdb  --also doesn't work for foo.accdb Provider string: Microsoft.ACE.OLEDB.12.0 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303) I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver). For example: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa

SQL Server doesn't exist or access denied while connecting with Access 2010 ODBC

  
Hello all. I am using Access 2010 and SQL Server 2008 (locally). I can easily connect to server using SSMS and it works excellent. But when I want to import a table to Access 2010 using ODBC, it says that "SQL Server doesn't exist or access denied". It worked previously - almost 3 days ago, but now, it doesn't! Please help! Thanks Mohammad Javahery

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

  
Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

Distributed Transactions fail on Linked server

  
We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.   We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.   Need your expert guidance in resolving this issue     Error:   OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.     Test code:   begin distributed transaction   select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects   ROLLBACK     Source server :      Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)        Nov 12 2008 12:10:04        Copyright (c) 1988-2008 Microsoft Corporation        Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)     Target server :     Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)        Jul  9 2008 14:43:34        Copyright (c) 1988-2008 Microsoft Corporation        Enterprise Edition

Problem connecting to SQL Server 2008 R2 Express from Access 2007

  
I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

Connection problem while connecting from client to server.

  
Hello, i am trying to connect sql server 2005 express using c#.net 3.5. my connection string is "Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\db_naren.mdf;Database=db_naren; Trusted_Connection=Yes;" this is working fine when i am connecting to sql server from the local machine. When i am trying to connect from someother machine to server it is giving error like A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) i added previliges in add new administrator on SQL Server 2005 Surface Area Configuration . and i made changes on SQL Server 2005 Surface Area Configuration  page, on Database Engine --> Remote Connections -->Local and remote connections --> Using both TCP/IP and named pipelines. still same error i am getting from client machine i am using connection string as "Server=MyServer\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\db_naren.mdf;Database=db_nare; Trusted_Connection=Yes;" is there any thing i need to do to connect from client machine to server?please

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Error Connecting to Server Using SQL Server Management Studio

  
Hello all, I am trying to connect to a SQL Server 2005 instance using SQL Server Management Studio and I am receiving the following error message. =================================== Cannot connect to VTWIN. =================================== A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476 ------------------------------ Error Number: 53 Severity: 20 State: 0 ------------------------------ Program Location:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)  &

MDX query using linked server - performance question

  
Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

OpenQuery() to Linked Server hangs, leaving SPID with open tran, then templog.ldf grows without limi

  
Hi, We have a customer that is using SQL Server 2000, and experiencing the following intermittent problem -- occasionally templog.ldf will grow and grow until it fills the entire disk. This is a rare problem, it has happened less than 10 times in the past two years, for a job that runs once a minute. But it has happened on 3 different SQL Servers, (two production servers, and one test server). Our suspicion is that the root cause is an OPENQUERY() to pull data from a linked server (Oracle database on Unix). We have seen that these OPENQUERY() statements occasionally hang and cannot be killed. The OPENQUERY() is used to populate a local table, and when we originally populated permanent tables with OPENQUERY(), then there would be a lock on this permanent table and nothing could be done until SQL Server was stopped and restarted. To workaround the immediate problem, last year we modified all OPENQUERY() statements so that the local table was a #temp table. In that case, cancelling the job left the hung process, but the hung process had a lock on a #temp table and so that didn't keep the next execution of the job from creating a new #temp table and populating it. However... it appears that the hung process, because it leaves an SPID with an open transaction, keeps the tempdb log file from being truncated. And eventually you run out of disk space, no matter how much disk space is
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend