.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

64-bit ODBC support for Linked Server queries

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

I have a number of applications that require data from a TeraData DW.  Linked Server queries run fine on the 32-bit Windows Server 2003 instances, but our organization is pushing to move to 64-bit (X64, not Itanium) server OS, for obvious resource managemetn reasons.  The problem *appears* to be that Microsoft did not include the 64-bit ODBC provider in the OS (that used to come with MDAC, which no longer exists -?)

 

I've heard rumor that this was reinstated with Server 2008, but I haven't been able to get it to work. 

 

Will the x64 SQL Server 2008 include the missing shared DLL to enable the use of third-party ODBC drivers as data source for Linked Server queries?

 




View Complete Post


More Related Resource Links

SQL Linked Server using ODBC System DSN

  
Hi All,

I'm trying to add a Linked Server using Microsoft OLE DB Provider for ODBC Drivers, but all I've got is the following error message:
"Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "HEDDV1".
OLE DB provider "MSDASQL" for linked server "HEDDV1" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"

The system DSN connection succeeds upon tests, as well as data extractions from either Excel or Access through the same DSN.
Facts:
- OS: MS Windows Server 2003 R2 Standard x64 Edition SP2
- SQL: MS SQL Server Standard Edition (64-bit) 9.00.3042.00
- ODBC Driver: MERANT 3.60 32-BIT Progress SQL92 v9.1D
- I've tried all possible Linked Server configurations (leaving both Provider String and Catalog, blank or not, etc), though that should not impact the connection as the dsn provides all information needed.
- I've informed the security context by providing remote login and password.
Please note the System DSN Data Source was added to the 32-bit version of ODBC

deploying to msdeploy does not support a proxy server

  

I am trying to deploy through webmatrix but my system Proxy server configuration is not being used. I assume that webmatrix would detect the proxy server settings and use that for the msdeploy calls under the hood. Can anyone confirm this is a know issue? 


Under The Table: Spatial Data Support In SQL Server 2008

  

New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009


Data Points: SQL Server 2005 XML Support, Exception Handling, and More

  

SQL Server 2005 includes several important improvements to the Transact-SQL (T-SQL) language. One added feature is a new kind of trigger that fires when data definition language (DDL) statements run.

John Papa

MSDN Magazine May 2006


SQLXML 3.0: Build Data-Driven Web Services with Updated XML Support for SQL Server 2000

  

XML is becoming the ubiquitous data format on the Web, and XML support in SQL Server is evolving to meet the additional demand. Using XML, SOAP, HTTP, and SQL Server, you can now build powerful Web Services easily. To show just how simple it is with SQLXML 3.0, this article walks the reader through the process step by step, from setting up a virtual directory enabling data access via HTTP to executing queries and building Web Services. Finally, the author illustrates the creation of two Web Services clients-one with C# that works with the Microsoft .NET Framework and one with the SOAP Toolkit 2.0 for anyone still using earlier development tools.

Christian Thilmany

MSDN Magazine May 2002


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

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

Can't Create ODBC to Local SQL Server Express 2008

  
Hello. I have SQL Server Express 2008 SP1 LOCALLY on my Windows XP Professional SP3 box. I have databases already created in Access 2007 that I imported into SQL. Now I want to keep the front end Access forms for data entry but have them connect to the databases in SQL. What I can't seem to do is connect from Access to the SQL db's because I can't create the ODBC connection. When I try to connect, I get the following two 'Connection failed' errors: 1. SQLState: '08001' SQL Server Error: 10061 [Microsoft][SQL Server Native Client 10.0]TCP Provider: No connection could be made because the target machine actively refused it. 2. SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login timeout expired Additionally, this information may help. 1. Windows firewall is turned off. 2. In SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS, Shared Memory, Named Pipes and TCP/IP are all 'Enabled'. The order is Shared Memory (1), Named Pipes (2) and TCP/IP (3). 3. In SQL Server Configuration Manager > SQL Native Client 10.0 Configuration > Client Protocols, the above three are also 'Enabled'. The order is Shared Memory (1), Named Pipes (2) and TCP/IP (3). Double-clicking TCP/IP tells me that the default port is 1433. 4. Running "netstat -ano", I see no entries for [myIPaddress]:1433.

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 Express 2008 Setup fails. Support Rule "Restart Computer" Failed

  
Hi, I have been trying to install SQL Server Express for most of the day now without any luck.  Whatever I do setup fails at the stage of Setup Support Rules, Rule "Restart Computer" - Failed.  Needles to say I have restarted (several times!) and I have tried downloading  and installing the "Advanced", "Tools" and "Basic" versions, all give the same error.  Trying a repair does the same.  I have been searching the net for an answer without success, any help would be MUCH appreciated. Further info: I am trying to install a stand-alone installation on to a single PC running under XP SP 3 with .NET 3.5 SP1 installed.  The PC did have some earler versions of SQL server installed but I (?hopefully?) removed all of these through Control Panel.  Before trying the first SQL install I installed Windows installer 4.5 and PowerShell 1.0 as per the Microsoft Instructions. Running SQL Server Configuration Manager results in an error (MMC could not create the snap-in. The snap in might not have been installed correctly).  A directory structure for SQL server exists in "Program Files".  Services MSC shows SQL Server (SQLEXPRESS) as Automatic Statup (not running).  Trying to start this manually gives error 14001: "application configuration incorrect - re-install to fix" .......... Which is precisely what I'm trying to do.........:(

ODBC Driver for SQL Server Compact Edition

  
I know that there is no ODBC driver available for SQL Server Compact Edition version 3.5.  Do you plan to add an ODBC driver in future release? If yes, is it high in your priority list?   The scenario why I am looking for an ODBC driver is to connect an MS Access application to a SDF file instead of MDB file.  This will enable me to easily sync the SDF file with SQL server repository using the new Data Sync Services.   Mario Cardinal MVP Solutions Architect

SQL Server 2008, ASP Classic, IIS and ODBC.

  
Hi.   I'm using Windows XP SP3 SQL Server 2008 Login:sa Password:12345 http://img2.pict.com/9d/b2/65/3153366/0/sql.jpg   ODBC - Am I doing it correctly? http://img2.pict.com/ee/6b/8b/3153363/0/800/odbc.jpg   Loading the 'Main.asp' file and got this error;   http://img2.pict.com/cd/8e/ad/3153435/0/800/ptb.jpg   Line 106 refers to the underline text below; (cn.Open "DSN=Trains;UID=sa;PWD=;DATABASE=Trains") &lt;%@ Language=VBScript %&gt; &lt;% Response.Buffer = True Response.Expires = -1000 %&gt; &lt;HTML&gt;&lt;HEAD&gt;&lt;TITLE&gt;Main Page&lt;/TITLE&gt; &lt;base target="_self"&gt; &lt;BODY bgProperties=fixed bgColor=#FFFFFF leftMargin=0 topMargin=0 bottommargin10&gt; &lt;DIV style="LEFT: 5px; POSITION: absolute; TOP: 80px"&gt;&lt;/DIV&gt; &lt;style type="text/css"&gt; body           {font-size: 8pt; font-family: verdana; color:white;} table, tr, td  {font-size: 10pt; font-family: verdana; color: black;} a:link         {font-size: 8pt; font-family: verdana; color:black; text-decoration: none;} a:visited      {font-size: 8pt; font-family: verdana; color:black; text-decoration: none;} a:

Does MOSS 2007 Support SQL Server 2008 R2 RTM version?

  
Hi, We have MOSS 2007 SP1 databases in SQL Server 2005 EE x64 with SP3. Now, we want to upgrade the SQL Server 2005 to SQL server 2008 R2 EE x64 RTM version. Does MOSS 2007 Support SQL Server 2008 R2 RTM version? If yes, please take me to the MS link where I can find this information. Thanks
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