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


Post New Web Links

Adding a linked server with alias

Posted By:      Posted Date: September 17, 2010    Points: 0   Category :Sql Server
 
I have to add a linked server now with an alias and with NT authentication. I got below code from a blog - I have few questions here, In general tab, Is that sql_server mandatory or can we give any other name? How about Provider string and Catalog information? How about NT authentication, where can we mention it. Please help..There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name?Here is how to do it:1) Step 1:. In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.. Inside of appeared wizard - Select the General tab.. Specify alias name in "Linked server" field.. Select SQL Native Client as provider.. Add sql_server in "Product Name" field (that's the magic).. In "Data Source" - specify name of the host to be used as linked server.2) Step 2:. In Security tab - specify proper security options (e.g. security context)3) Step 3:. In Server Options tab - put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.4) Step 4:. Enjoy.


View Complete Post


More Related Resource Links

Problem when adding Linked Server...

  
Hi @all,I try to add a new Linked Server to my DB in Microsoft SQL Server Management Studio Express...The following error is returned (SERVER2 is the Linked Server to add): There is no remote user 'sa' mapped to local user '(null)' from the remote server 'SERVER2'. (.Net SqlClient Data Provider)I entered the user sa and the correct password for SERVER2 but it doesen't work...When I connect to SERVER2 in Management Studio with the same infos it works fine...So please help...

Adding additional "SQL Server Name" or "Server Alias" for exisiting SQL Server Instance - Standalone

  

Hi All,

 

I would like to add a new network name and IP address for the existing instance of standalone sql server.

Existing Server Details :-

Old Cluster Domain Name = OLDSQLServer (Old SQL Server Name )

Old Cluster Network IP Address = 10.x.x.x (OLD SQL Server IP)

 

New required additions to the Server :-

New Domain Name = NEWSQLServer (This domain entry and IP also should connect to OLDServer)


Please help me with the procedure.


Venugopal S

dynamically adding sql server columns based on number of files in fileuploader

  

I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.

 

I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!

 

Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")

objConn.Open()

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

Problem adding .NET 3.5.1 feature on Server 2008 R2

  
Having an issue with a 2008 R2 64-bit Server becoming unresponsive/locking up when trying to activate the .NET 3.5.1 feature.  Did not have any previous version of .NET installed but ran the .NET Cleanup utility and completed successfully but still have same issue.  It installed .NET 4 via the Microsoft Updates OK but still hangs part way through the 3.5.1 install via the Server Manager/Add Features.  Any ideas?  Thanks!

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

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

Replication Wizard doesn't like SQL Server Alias

  
I tried creating a new replication but I get an error that says, "SQL Server replication requires the actual sever name to make the connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specifiy the actual server name, 'SomeMachineNumber'." I don't get this. Why can't I use an alias? I googled and found few articles but not sure if I understand what others are talking about. Here is how I have things set up and I'm hoping someone can explain to me what is happening. On my machine, I have the following alias set up. Alias Name: SQLDEV, Port No: 2433, Protocol: TCP/IP, Server: SomeMachineNumber In Mgmt Studio, I can connect to the server SomeMachineNumber by using the alias SQLDEV. However, if I try to create a new publication on SQLDEV, I get the above error. Now, just for the heck of it, I created another alias using the same properties as above except, I named the alias the same as the server, "SomeMachineNumber". I connect to the server in Mgmt Studio using the new alias, and now I am able to create the new publication. Does this make sense? Am I not still using an alias in the second example? Why does the replication seemingly like the alias "SomeMachineNumber", but not "SQLDEV"?

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

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 [

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

insert query from ms SQL server into a mysql linked server

  
INSERT into openquery(dbserver1MySQL,'select * from graham.lookup_in_table') select * from NavteqAPAC.dbo.Admin_Names   is giving me the following error! Please help me   OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can't run this command now". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" could not INSERT INTO table "[MSDASQL]".

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Adding second node to SQL Server cluster fails.

  
I have successfully created a MSCS cluster and I have the SQL cluster up and running on the first node.   When I put the install cd in the second node and go through the install it fails with the error SQL Server Setup has encountered the following error:  Object reference not set to an instance of an object. I'm assuming I get this error because on the cluster node configuration screen the "cluster network name" box is blank.   It won't let me select a name or type a name. The SQL cluster is up and running on the first node.   I can ping the sql cluster network name from both boxes.   The setup shows the installed node and the node I"m trying to add, so I don't see any problem. I have Windows 2008 R2 on both nodes and I'm installing SQL Server 2008 R2.   Any idea how I can get past this error?    Thank you.
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