Linked Server Excel Import Doesn't Work In Vista
Jan 15, 2008
I was using linked servers to import Excel spreadsheets into SQL Server Express 2005. This worked fine with Windows XP and Office 2003.
I have just migrated all my stuff to Vista and Office 2007. Linked servers just can't be created:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
The parameter values I used are:
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Excel
Data source: D:...somelocalfile.xls
Provider string: Excel 8.0
I gave full access rights to the Data source file and folder to NETWORK SERVICE and SQLServer2005MSSQLUser$MOZART$SQLEXPRESS where mozart is my PC name.
If I change Data source or Provider string to some garbage string, the exact same error message appears. So it appears the error might be in the Provider?
Help help please.
View 4 Replies
ADVERTISEMENT
Dec 7, 2005
Hi there,
I'm having trouble getting an import to work.
Here what i want:
i have created a dsn to extract data from an SQL2000 server into Excel. this all works fine. Now i have edited the data and would like to import the updated fields in my database.
There are no new fields just updated information. I have used the import wizard from SQL Enterprise manager. I have selected to delete the current table and replace is with the one ive created.
This is where the problems begin.
When i finish the wizard i get an error saying there is a conflict "collum reference constraint". wich i think has something to do that there are links to this table wich can not be simply deleted and recreated.
How do i get around this?
Greets Axel
View 2 Replies
View Related
Mar 19, 2007
All,
I have a package that works fine on XP. I copy it (and the databases used within it) to my new Vista Business Edition box (with SQL 05 SP2 with Hotfix.) Both the source and destination databases are on the 05 SP2 with Hotfix installation on this Windows Vista box (this is a completely local transformation.) The account I am logged in with is an Administrator on the box (and I have done the whole Vista provisioning deal.) Furthermore BUILTINAdmins is not disabled. Furthermore I went so far as giving this account every server role.
Short of it is when I use SQL Destination it fails - regardless of the options used. When I use an Ole Db Destination it works.
So, to prove that I am not crazy I recreated this package from scratch, and used a SQL Server Destination. The account I am using has essentially every permission imagineable. And to repeat: yes, I'm transferrring from a local database (on this box) to another database (on this box) and SSIS is running through BIDS on this box.
At this point I'm beginnning to think that it is broken on Vista, but I can't believe someone else hasn't run into this. However, when I look at the groups - nothing. Any ideas?
Thanks,
dcb99
View 1 Replies
View Related
Sep 21, 2001
I administer several servers. My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID. In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:
The object name 'DB1.ogSerial.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Here are the queries I've tried (using DB3 as the host, DB1 as the linked server).
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM MyDB.dbo.TestTable
View 3 Replies
View Related
Apr 24, 2008
I've created a drill down rpt in RS2000, but when I setup subscription and receive it as Excel, the file doesn't contain any drill downs.
Does anyone have any idea the reason for this??
thx,
John
View 1 Replies
View Related
Mar 3, 2007
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010
eibcactive.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
View 12 Replies
View Related
Jul 24, 2015
Im trying to pull a workbook which has a power pivot into SSAS using a remote tabular instance through visual studio 2013. However Im getting an error saying, " We cannot import the workbook XXXXX.xlsx. Try placing the work book on a server that the service account of DBNAME/Tabular has permission to read and that can be reached with a UNC path (//<server>/<shared>/<file>)". Here DBNAME is the server name and Tabular is the instance name.
I tried changing the logon name in SQL server analysis services(Tabular) in Services.msc Microsoft console.Also tried changing the logon to local system. Still the error persists.
how to overcome this error?
View 5 Replies
View Related
Nov 24, 2006
I have the following situation: A webserver in a DMZ which connects to a DbServer in a Domain.
but when I try to make a ODBC connection on the webserver I get the well-known "Server does not exist or access denied" error.
I tried using IP address as well as FQDN. I turned off the firewall in the router (I'm able to access shares and what not from the webserver so the firewall is really turned off).
I think however i have narrowed down the problem to the TCP/IP connection of the SQL Server. Normally when you try from a command prompt
"telnet dbserver 1433" you get a connection. But when I try this to the specific DbServer I get an "unable to connect" error. Even when I try it on the dbserver itself (so telnet localhost 1433). This should work always?
In the network configuration of the sql server the Named Piped as well as the TCP/IP protocols are enabled. TCP/IP is offcourse configured on port 1433.
How can I fix this problem?
View 2 Replies
View Related
Aug 28, 2007
Hello! I'm trying to get caching work with ASP.NET 2.0 and SQL Server 2005, but I have a problem and I can't figure out what I'm doing
wrong.I have tried to do everything according to the instructions. I
have enabled the database for cache notification by running the
aspnet_regsql tool on the command line like this:aspnet_regsql -S server
-E -d database -etThen I have run the aspnet_regsql tool to enable each
table in the database for cache notification:aspnet_regsql -S server -E
-d database -et -t tablenameI have added the necessary parts into
web.config file, so that it is like
this:<connectionStrings><add name="ConnectionString1"
connectionString="DataSource=servername;Database=databasename;Integrated
Security=True"providerName="System.Data.SqlClient"/> </connectionStrings> <system.web>
<caching> <sqlCacheDependency enabled="true"
pollTime="2000"> <databases> <add
connectionStringName="ConnectionString1"
name="KJ"/> </databases>
</sqlCacheDependency> </caching>I have added this
into global.asax file for the application start event:
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())To
test caching I wrote this piece of code where I get data from the table
MAAKUNNAT in
the database.----------SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString(),"MAAKUNNAT")Dim
maakunnatDS As New DataSetIf Cache.Get("maakunnat") Is Nothing
Then Dim myConnection As
NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
myConnection.Open() Dim cmd As New SqlCommand("haemaakunnat",
myConnection) cmd.CommandType =
CommandType.StoredProcedure Dim riippuvuus As New
SqlCacheDependency("KJ", "MAAKUNNAT") Dim aggDep As New
AggregateCacheDependency
aggDep.Add(riippuvuus) Dim maakunnatDA As SqlDataAdapter =
New SqlDataAdapter maakunnatDA.SelectCommand =
cmd maakunnatDA.Fill(maakunnatDS, "MAAKUNTA")
myConnection.Close() Cache.Insert("maakunnat", maakunnatDS,
aggDep) Else Response.Write("The data is in the
cache") maakunnatDS = Cache.Get("maakunnat")End
If-----------------But when I run the page I get this
error.------------------------------------------The 'MAAKUNNAT'
table in the database 'KJ' is not enabled for SQL
cachenotification.Please make sure the table exists, and the table
name used for cachedependency matches exactly the table name used in cache
notificationregistration.To enable a table for SQL cache
notification, please useSqlCacheDependencyAdmin.EnableTableForNotifications
method, or the commandline tool aspnet_regsql. To use the tool, please run
'aspnet_regsql.exe -?'for more information.To get a list of enabled
tables in the database, please
useSqlCacheDependencyManager.GetTablesEnabledForNotifications method, or
thecommand line tool
aspnet_regsql.exe.----------------------------------------------When I
run the aspnet_regsql tool with the -lt parameter to get a list of enabled
tables in the database they are all enabled. I have searched many pages in
the internet and I cannot find out what I'm still doing wrong. I'd
appreciate it very much if someone could help me with this
problem.Toni S.
View 5 Replies
View Related
Jun 27, 2007
My instance of SQL Server Developer stopped working after upgrade of OS from Vista to XP. I have also installed SP1 & SP2. Still it is not working. The message that I get when I start SQL Server Management Studio is "The application cannot start."
View 7 Replies
View Related
Jun 11, 2014
I have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...
UPDATE a
SET a.[omschrijving]=SP.[omschrijving]
,a.[verkoopprijs]=SP.[verkoopprijs]
,a.[gewijzigd]=getDate()
FROM [artikelen] a
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))
View 9 Replies
View Related
Nov 9, 2007
I made a page for a baseball league a couple of years ago using PHP and MySQL, and decided this winter to switch it to ASP.Net and SQL Server Express that comes with VWD 2005 Express Edition.
Anyways, I ran into a problem when trying to convert the query I made with MySQL into SQL Server.
My old code was something like this:
SELECT homeScore, visitorScore, ( homeScore > visitorScore ) AS homeWins, ( visitorScore > homeScore ) AS visitorWins FROM Schedule
This worked fine in MySQL, and returned the home team's score, visiting team's score, and then returned a 1 in homeWins if the home team won. Now, when I try running this query in SQL Server Express, I get an error saying "Invalid column name 'visitorScore > homeScore' ".
Can anyone please help me with what I should be doing differently so this works in SQL Server Express?! Thanks in advance!!!
View 17 Replies
View Related
Oct 15, 2007
Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!
View 2 Replies
View Related
May 19, 2008
Hi.
I have a problem.
I do a basic report with just one simple table:
_id
_name
_comment
_file
I'm able to get all informations into a table. But for the file, I created a URL Link for this file.
But when the reporting is generating in html, if I click on the file's link, nothing happens. But I can dowload the file with a right click. And if I see proprieties of the link, the link is rights, and it's works good if I copy and paste it on the IE url bar.
So for summary, my problem is : nothing happens if I click on the file, but a want a window's opening with the choose "Open with" or "Save on the disk".
Somebody can help me?
Thanks
View 1 Replies
View Related
Sep 22, 2007
Hi,
Can anybody help me with the following problem:
I have Vista Ultimate installed and within Vista .NET Framework 3.0 is installed as a part of the operating system so I can't remove 3.0
Now for MS SQL Server 2005 Express I need .NET Framework 2.0 and the SQL Server 2005 doesn't work with .NET Framework 3.0
Can't install 2.0, get the message that 3.0 is already installed.
Is there another version of SQL Server that works with .NET Framework 3.0 and runs under Vista Ultimate?
Please help me!!
René
View 3 Replies
View Related
Jul 5, 2007
Hi all,
We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night).
Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs"
NB: if using the local drives, it work
Check List
Security:
- The account that I used to create the plan is an sa account
- The location that I dump the TL files, I have full access to the folder
SQL Statement:
exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files)
Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
View 1 Replies
View Related
Aug 22, 2006
I recently created an application that stores 3 variables in the web.config in the "Profile"configuration:
<profile>
<properties>
<add name="Department" defaultValue="" />
<add name="UserName" defaultValue="Anonymous" allowAnonymous="true"/>
<add name="AL" defaultValue="" />
</properties>
</profile>
By doing this my web.config automatically configured itself to create a database in my app_data folder and also filled in all the table rows as necessary. Then I moved the code to my sql server running SQL Server 2005 Standard and found out that it won't generate that database in either the app_data folder or the SQL Server Data directory. I've gotten so desperate to get this database created that I've (temporarily) given "Everyone" full permissions to both directories. I then double checked that my named pipes to that server were enabled, and then also tried moving my database from my working sql express app from a different computer to the server. I still cannot get it to work.
If I have no database in the folders that worked on sql express i get this error:
"SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express ..."
If I have a database in the folder it says it can't communicate with it or can't find it.
My web.config shows:
<add name="LocalServer" connectionString="server=SERVERNAMEWest;database=ASPNETDB.MDF;uid=UserName;pwd=Password;" PoviderName="System.Data.SqlClient" />
Is there a way to successfully generate this database with sql server? Please Help!!!
View 1 Replies
View Related
Apr 24, 2006
Source Database: SQL Server 2000
Destination Database: SQL Server 2005
No matter what table, view, or stored proc I pick, it always says that it doesn't exist at the source. I know it exists because I am picking it from the list of tables, etc. that the GUI provides.
Any suggestions?
Jonathan Allen
View 1 Replies
View Related
Feb 15, 2008
Hello,
I'm trying to add a new connection in server explorer. The server will not appear
in the server drop down list, and when i manually type in the server and database name,
the connection fails saying
"An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - could not open a connection
to SQL Server)"
I made sure the remote connections were on, and I am having no problems open
the sql connection in code.
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
May 31, 2006
The task has a fault if you try to copy a table with a schema like [test].[table1]
The error says
Error: 0xC002F363 at Flyt til DM, Transfer SQL Server Objects Task: Table "table1" does not exist at the source.
Even though i just selected it from the table list minutes before.
View 7 Replies
View Related
Nov 16, 2007
While installing SQL Server 2005 Enterprise Edition on Windows Vista Ultimate
the setup alerts me "there are known compatibility problems, please
install SP2 after this". So ok, all went nice and installed without error.
IIS 7 installed fully, so all green in setup display.
(But after installation i do not see any service which begins with SQL***...)
At the end, the setup wanted to start the administration Tool.
Ok, but my name was not listed, so i could not add me to the permitted users.
I can not use any tool, because i have no rights.
Some Ideas?
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services-Clienttools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16546
Microsoft .NET Framework 2.0.50727.312
Betriebssystem 6.0.6000
thanks Frank
View 6 Replies
View Related
Apr 16, 2008
Hello all,
I have 2 networked PC's both running vista ultimate
1st is Laptop and is running its own SQL Server at laptoplaptopSQL
2nd is Desktop and is running its own SQL Server at desktopdesktopSQL
Now both machines have seperate windows login accounts.
When I go SQL Server management studio I go to browse and each machine can see the other machines SQL Server, but when I go to login I get SQL Login falied for users" The user is not associaed with a trusted SQL server connection".
So I then go to logins new login and try to add my other pc's user account.
The problem I see is that when I go to search and then location it only shows its own PC's location and not the location of my other networked pc? So if I am on Desktop and in my theory want to add laptopuser to the desktop SQL Server logins I get:
"create failed for login laptopuser
An exception occurred while executing Transact SQL statement laptopuser is not a valid windows NT name. give the complete name
Not sure on where to go from here.
Any help would be great
View 10 Replies
View Related
Oct 20, 2006
Hi,
I have a piece of Java code that needs to connect to SQL 2000 (SP4) using Windows Authentication. It's running on Windows Server 2003 SP1.
I tried JDBC v1.1 and followed the code from the following blog:
http://blogs.msdn.com/angelsb/default.aspx?p=1
But still get this error as shown below. Any help appreciated.
I am using JDK1.4.2, "sqljdbc_auth.dll" is located under "E:SQL2005JDBCDrvsqljdbc_1.1enuauthx86", also made a copy under "E:JavaTest" and "C:WindowsSystem32" but still won't work.
Cheers
Allan
===========================================================
E:JavaTest>javac -classpath ".;E:JavaTestsqljdbc.jar" TestW2.java
E:JavaTest>java -classpath ".;E:JavaTestsqljdbc.jar" -Djava.library.path=E:S
QL2005JDBCDrvsqljdbc_1.1enuauthx86 TestW2
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.processLogon(Unknown
Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source
)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknow
n Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover
(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at TestW2.main(TestW2.java:7)
===========================================================
The code is simple (TestW2.java):
import java.sql.*;
public class TestW2 {
public static void main(String[] args) {
try {
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = java.sql.DriverManager.getConnection("jdbc:sqlserver://VMW2k3ENT003.TESTCBFPOC.COM.AU;integratedSecurity=true");
System.out.println("Connected!");
conn.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
==============================================================
View 27 Replies
View Related
Jun 4, 2007
Hello,
I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.
For some tables, it return all data but for another tables, it only return part of the rows.
How it may happen?
Thanks
View 1 Replies
View Related
Jan 7, 2007
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT Ａ.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies
View Related
Nov 1, 2004
I am using the open query method to connect a Oracle server.
Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows.
I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem
SET QUOTED_IDENTIFIER OFF
declare @sql varchar(750)
select @sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER
FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER
" + '")'
EXEC (@SQL)
thanks
Priya
View 1 Replies
View Related
Oct 25, 2013
I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?
View 1 Replies
View Related
Mar 13, 2007
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
Thanks in advance for any help on this
View 1 Replies
View Related
Feb 20, 2005
I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
Here's some particulars:
---
EXEC sp_addlinkedserver
@server='VFP',
@provider='VFPOLEDB',
@datasrc='\hdmcpdctis1 isrnddata',
@srvproduct='Visual FoxPro'
--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \hdmcpdctis1 isrnddata ',
'select * from [tislists.DBF]')
With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================
Any Help is greatly appreciated! Thanks,
peter :confused:
View 5 Replies
View Related
Oct 4, 2007
Hello.
Suddenly OPENQUERY had started to raise an exception 'Msg 7355, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "sql2000" supplied inconsistent metadata for a column. The name was changed at execution time.'
The OPENQUERY stament looks like:
Code Block
SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')
I don't know what has been changed - seems that everyting is OK.
The problem is that sql2000 is MS SQL 2000 server and this statment is executed on the MS SQL 2005 server.
dbo.sc_List is a procedure that selects data and I need insert that data to the temporary table on the sql2005 server to make some calculations.
I've used OPENQUERY to prevent DTC coordinator from starting distributed transaction.
Whole statement originally looks like
Code Block
INSERT INTO #Templates SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')
where #Template is temporary table with columns that exactly match with stored procedure output.
Help me please - what is wrong with sql servers? I know that this query has been working fine for a months and now - such strange errors. I should say also that strored procedure returns always the same columns when called - there is one large select inside it.
Thank you.
s w
View 4 Replies
View Related
Jan 11, 2008
I have imported a TXT file every week on SQL 2000. Options,
Fixed field
File Type: ANSI
Skip rows: 0
Row delimited: {CR}{LF}
Text qualifier: DoubleQuote{"} (default grayed out).
It works fine. On SQL 2005, I cannot.
If I keep same settings as SQL 2000, I cannot see file from Preview. If I change to Delimited, I can see file but there is only one column.
Any suggestion?
Thanks
ZYT
View 1 Replies
View Related
Jul 23, 2005
Hi all,I set up our Oracle Financials as a linked server to one of my SQLServer boxes. On running a test query, I got the following errormessage:OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.Metadata information was changed at execution time.OLE DB error trace [Non-interface error: Column 'TEST_NUM'(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reportedto have aDBTYPE of 130 at compile time and 5 at run time].The Oracle datatype of the column with the supposedly inconsistentmetadata was NUMBER, which according to the Oracle OLE DB documentationactually maps to 139. 130 is a null-terminated unicode characterstring, 5 is a float, and 139 is a variable-length, exact numeric valuewith a signed scale value. Oracle NUMBER is an all-purpose numerictype, apparently they use that instead of int, float, etc. The Oracleguy used it for this column in the test table because in OF it is usedin pretty much every table. (For starters it is the datatype of theiridentity columns.)There is something in the OLE DB spec about all datatypes having to beable to be expressed as DBTYPE_WSTR (130), but what I don't get is thatI can connect to the same Oracle instance using VB6 code and theMSDAORA provider and there is no problem at all interpreting the NUMBERcolumns. So why does it work from VB and not as a linked server? Andmuch more importantly, HOW DO I MAKE THE LINKED SERVER WORK?TIA
View 11 Replies
View Related
