RESOLVED - Users Cannot Connect to SQL Server 2000 SP4 After Applying Hotfix (build 8.00.2148)

Users are reporting that they cannot connect to SQL Server using ODBC and Windows Authentication after I applied a SQL Server 2000 SP4 hotfix. The client-side error is:

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied

I installed hotfix for SQL Server 2000 SP4 (sets version to SQL Server 2000 build 8.00.2148 (SP4)); see KB894905. This hotfix was intended to resolve the following issues occurring on the server:

902955 (http://support.microsoft.com/kb/902955/) FIX: You receive a "Getting registry information" message when you run the Sqldiag.exe utility after you install SQL Server 2000 Service Pack 4

895123 (http://support.microsoft.com/kb/895123/) FIX: You may receive error message 701, error message 802, and error message 17803 when many hashed buffers are available in SQL Server 2000

Has anyone else experienced this, found a solution, etc.?

Thanks,

David

The server is SQL Server 2000 SP4 on Windows 2003 SP1. The client PC has MDAC 2.8 SP1 on Windows XP SP 2.

|||

double check that windows firewall isn't blocking on both client and server.

on clients run "C:\windows\System32\CliConfg.exe" and make sure that tcp/ip is a configured protocol

on Server, run "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe" and make sure that tcp/ip is configured

|||

Thanks for your reply.

Neither client or server is running Windows Firewall. Both are enabled for TCP/IP. I also ran the MDAC Component Checker on both and neither reported mismatches.

|||

sanity check. . . is the service running?

what happens when you do this from a client -

telnet servername 1433

does the screen go blank, as if waiting for or input? or does it say - "Connecting to servername" and then time out? or something different?

what happens when you do this on the server -

telnet localhost 1433

|||

Yes, the MSSQLSERVER and SQLSERVERAGENT services are both running on the server and I can connect fine using EM from my PC. In fact, I can connect using ODBC from my PC, but a user cannot connect from his PC using the same ODBC setup. An app. server running Windows 2000 SP1 also cannot connect. I've been validating the Windows and MDAC versions and making sure no firewalls are running, etc.

When I run telnet servername 1433 from my PC the window opens and reports "Connecting to servername" and then it disappears. When I run telnet localhost 1433 on the server it does the same thing.

|||

wait a second. . .

sitting at the server, from the start menu, click run . . .

you do:

telnet localhost 1433

a command window opens up and you get a message "connecting to localhost"

and then the command window closes?
If so the connection is being refused from the machine/service.

the telnet should go to a blank/black command window as the sql telnet session awaits more commands.

Is it a named instance of sql server? or default instance?

|||This is a default instance and yes, the telnet window disappears.|||

when you are sitting at the server and trying the telnet, you are logged in as a machine administator and the telent fails?

using enterprise manager, check the server from your machine and confirm that 'servername\Administrators' have server admin permissions.

double check all your logins and db users

are you running NT Authentication or Mixed mode authentication?

|||Yes, I am logged in as an administrator on the server when I run telnet. 'BUILTIN\Administrators' has been removed from the SQL Server logins, but that should not affect users trying to connect with valid registrations. The instance uses mixed-mode authentication (Windows and SQL Server).|||

well, double check your logins and users. . . . no one is denied.

|||

This issue has been resolved. It was related to permissions for the SQL Server Service Account. That account needed permissions adjustments, as described in KB283811.

The permissions changes resolved the ODBC TCP/IP connection failures, but the root cause is still a little perplexing. Connections worked fine for many months, then the failure. The MS Support Engineer that I worked with was convinced that the 2 SP4 hotfixes did not cause the issue. He thinks it may have been the result of a change in Group Policy, which I will have to do more research on.

Reserver Characters

When saving characters to text or char fields are there any reserved
characters which i should watch out for when using ODBC to store data to SQL
2000 server.
I MySql you have to add and escape character before before the character to
avoid problems.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004THe only character is the quote.. SQL Strings are quoted... Just double the
quote ( 2 single quotes) to include a single quote in a quoted string.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:eX08dq47DHA.1804@.TK2MSFTNGP12.phx.gbl...
> When saving characters to text or char fields are there any reserved
> characters which i should watch out for when using ODBC to store data to
SQL
> 2000 server.
> I MySql you have to add and escape character before before the character
to
> avoid problems.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004
>

Reserver Characters

When saving characters to text or char fields are there any reserved
characters which i should watch out for when using ODBC to store data to SQL
2000 server.
I mysql you have to add and escape character before before the character to
avoid problems.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004THe only character is the quote.. SQL Strings are quoted... Just double the
quote ( 2 single quotes) to include a single quote in a quoted string.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:eX08dq47DHA.1804@.TK2MSFTNGP12.phx.gbl...
> When saving characters to text or char fields are there any reserved
> characters which i should watch out for when using ODBC to store data to
SQL
> 2000 server.
> I mysql you have to add and escape character before before the character
to
> avoid problems.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.580 / Virus Database: 367 - Release Date: 6/02/2004
>

Request for confirmation: This bug is fixed

Hello World,
One of our customers keeps getting the following error:
ODBC error code: -1
Net library error code: 2746x (10054)
SQLState: 01000
Description: ConnectionWrite(send())
I have looked up on the Microsoft web pages and have found:
http://support.microsoft.com/kb/q303120/
The description on this page states that this bug was fixed with SQL
Server 2000, service pack 2, so I asked the customer about the server
version number of his SQL Server 2000.
He has 8.00.818 (and lightweight pooling to default, which is off).
Accorind to the SQL Server 2000, service pack 3 description, an
installed SP3 has number 8.00.760.
Due to this circumstances I suppose our customer has a fixed version of
SQL Server 2000 and the problem is located somewhere else. Am I right?
Thanks for your help and best regards
Andreas
--
Andreas Tscharner andreas.tscharner@.metromec.ch
---
Das Denken eines Programmierers ist stets logisch (IF 1=2 THEN CALL
MainProgram), immer strukturiert und von keinerlei Vorurteilen
beeinträchtigt (ON Hunger GOSUB Aldi ELSE RETURN).
-- aus Murphy's Computergesetzen8.00.818 is the version number for the MS03-031 security patch. It was released after SP3a.
/risun
>--Original Message--
>Hello World,
>One of our customers keeps getting the following error:
>ODBC error code: -1
>Net library error code: 2746x (10054)
>SQLState: 01000
>Description: ConnectionWrite(send())
>I have looked up on the Microsoft web pages and have found:
>http://support.microsoft.com/kb/q303120/
>The description on this page states that this bug was fixed with SQL >Server 2000, service pack 2, so I asked the customer about the server >version number of his SQL Server 2000.
>He has 8.00.818 (and lightweight pooling to default, which is off). >Accorind to the SQL Server 2000, service pack 3 description, an >installed SP3 has number 8.00.760.
>Due to this circumstances I suppose our customer has a fixed version of >SQL Server 2000 and the problem is located somewhere else. Am I right?
>Thanks for your help and best regards
> Andreas
>-- >Andreas Tscharner andreas.tscharner@.metromec.ch
>----
--
>Das Denken eines Programmierers ist stets logisch (IF 1=3D2 THEN CALL
>MainProgram), immer strukturiert und von keinerlei Vorurteilen
>beeintr=E4chtigt (ON Hunger GOSUB Aldi ELSE RETURN).
> -- aus Murphy's Computergesetzen
>.
>|||Anthony,
Please stop confusing people by referring to build 817 (or 818 or 819
or MS03-031) as the "Slammer Worm build." This has nothing to do with
Slammer. As I've already pointed out, and as you can verify by reading
the security bulletins, MS03-031 addresses three post-Slammer
vulnerabilities. Like all Microsoft hotfixes, it is cumulative, but it
is definitely not the "Slammer Worm build," and it was released long
after the patch and service pack addressing the Slammer vulnerability
were released.
SK
AnthonyThomas wrote:
>Actually, 817 is the MS03-031 Slammer Worm build. 818 fixes an error MS03-031 introduced into the DTSGUI.dll and build 819 fixes a SQLEM password error introduced by the 818 fix. You should have build 819 to have all the tools fixed.
>Also, there have been three other publicly available hot fixes since then, 839, 859, and the latest, 879.
>Sincerely,
>
>Anthony Thomas
>
>|||Thanks for clearing this up, Anthony!
SK
AnthonyThomas wrote:
>My apologies to everyone. You, of course, are absolutely right. The Sapphire/Slammer Worm attacked Jan. 25, 2003, but the patches were available the prior summer but had not been applied by many. Obviously, MS03-031 was deployed until later 2003. Both deal with protocol hijacking and buffer overrun issues and, since we had been applying SP3, then shortly thereafter, SP3a, this seemed like the proactive release.
>The confusion was entirely my own.
>Sincerely,
>
>Anthony Thomas
>
>

Repost for discussion on the error of SQLNCLI.1

Hi There,

I still got the following error:

OLE DB error: OLE DB or ODBC error: Could not locate registry entry for OLE DB provider 'SQLNCLI.1'.; 42000; OLE DB error trace [Non-interface error: Provider not registered.].; 01000.

I am running sql 2005 sp2, and the error happens when I try to drop one of attribute in my dimension table to the cube browser inside AS 2005, for example, I have one dimension call Customer with two attribute customercode and customername, along with fact table with sales amount, and I can drop customercode attribute into the cube browser without any problem, display cross join correctly with customercode as rows and sales amount.

But when I drop customername attribute into it, it gave me the above error, is this the bug? I did the .udl file to test the SNAC and everything works, I can connect to the data source using SNAC.

Running sql profiler againist AS 2005 showing same error. I did have mutiple data sources, but one DSV in the project.

Any other advice?

Thanks

Don

Are you using a ROLAP dimension? If so do you have the SQL native client installed on the server?|||

Hi Darren,

Thanks for the reply, no, I am not using rolap, everything is molap. sql native client is installed since other attribute works fine when dropping to the cube browser. This just drives me nuts.

Thanks for any pointer.

Don

|||Sorry, but that was really the only reason I could think of that browsing a cube would try to access the SQLNCLI.1 (SQL Native client) provider. If you are only using MOLAP storage it should only need the MSOLAP OLE DB provider.|||

Thanks Darren for your help.

All my data sources connect to SQL 2000 databases in another server, is this going to be the issue?

|||

Also here is the MDX query I capture from Profiler for the error message, notcie OPENROWSET(N'SQLNCLI.1',N'',N'SELECT is used:

SELECT SUM ( [FactPhysicianProcedure].[FactPhysicianProcedureTotalCharge0_0] ) AS [FactPhysicianProcedureTotalCharge0_0],
SUM ( [FactPhysicianProcedure].[FactPhysicianProcedureBalance0_6] ) AS [FactPhysicianProcedureBalance0_6],COUNT_BIG ( [FactPhysicianProcedure].[FactPhysicianProcedure0_7] )
AS [FactPhysicianProcedure0_7],[dbo_MisService_6].[dbo_MisServiceName1_0] AS [dbo_MisServiceName1_0] FROM
(SELECT [TotalCharge] AS [FactPhysicianProcedureTotalCharge0_0],[Balance] AS
[FactPhysicianProcedureBalance0_6],1 AS [FactPhysicianProcedure0_7],[InptService] AS [FactPhysicianProcedureInptService0_8] FROM
(SELECT w.OperSurgeon, u.AccountNumber, w.OperProcSeqNoAbsPat AS OperProcSeqNo, w.OperProc, CAST(o.Drg AS varchar(4)) AS Drg, u.Age, u.DisMonth,
u.Sex, u.Zip_PostalCode, g.InptService, u.FinancialClass, v.AdmitPhysMnemonic, g.PtStatus, u.DisDate, u.DisYear, u.DisQuarter,u.DisMonth AS Month, DATEPART(day, u.DisDate) AS DisDay,
g.TotalCharge, u.LOS, g.TotalInsExpReimb, g.TotalInsRcpt, g.TotalCost, g.TotalInsAdj, g.Balance FROM dbo.TSG_PatientInformation AS u INNER JOIN
dbo.TSG_Procedures AS w ON u.UrnABS = w.UrnABS INNER JOIN dbo.TSG_Physicians AS v ON u.UrnABS = v.UrnABS INNER JOIN dbo.TSG_DrgData AS o ON u.UrnABS = o.UrnABS INNER JOIN dbo.TSG_BarAccountData AS g ON u.UrnABS = g.UrnABS) AS [FactPhysicianProcedure]) AS [FactPhysicianProcedure],

OPENROWSET(N'SQLNCLI.1',N'',N'SELECT [Name] AS [dbo_MisServiceName1_0],[MnemonicMISSVCDICT]
AS [dbo_MisServiceMnemonicMISSVCDICT1_1]
FROM [dbo].[MisService]') AS [dbo_MisService_6] WHERE (([FactPhysicianProcedure].[FactPhysicianProcedureInptService0_8]
=[dbo_MisService_6].[dbo_MisServiceMnemonicMISSVCDICT1_1]))
GROUP BY [dbo_MisService_6].[dbo_MisServiceName1_0]

Repost for discussion on the error of SQLNCLI.1

Hi There,

I still got the following error:

OLE DB error: OLE DB or ODBC error: Could not locate registry entry for OLE DB provider 'SQLNCLI.1'.; 42000; OLE DB error trace [Non-interface error: Provider not registered.].; 01000.

I am running sql 2005 sp2, and the error happens when I try to drop one of attribute in my dimension table to the cube browser inside AS 2005, for example, I have one dimension call Customer with two attribute customercode and customername, along with fact table with sales amount, and I can drop customercode attribute into the cube browser without any problem, display cross join correctly with customercode as rows and sales amount.

But when I drop customername attribute into it, it gave me the above error, is this the bug? I did the .udl file to test the SNAC and everything works, I can connect to the data source using SNAC.

Running sql profiler againist AS 2005 showing same error. I did have mutiple data sources, but one DSV in the project.

Any other advice?

Thanks

Don

Are you using a ROLAP dimension? If so do you have the SQL native client installed on the server?|||

Hi Darren,

Thanks for the reply, no, I am not using rolap, everything is molap. sql native client is installed since other attribute works fine when dropping to the cube browser. This just drives me nuts.

Thanks for any pointer.

Don

|||Sorry, but that was really the only reason I could think of that browsing a cube would try to access the SQLNCLI.1 (SQL Native client) provider. If you are only using MOLAP storage it should only need the MSOLAP OLE DB provider.|||

Thanks Darren for your help.

All my data sources connect to SQL 2000 databases in another server, is this going to be the issue?

|||

Also here is the MDX query I capture from Profiler for the error message, notcie OPENROWSET(N'SQLNCLI.1',N'',N'SELECT is used:

SELECT SUM ( [FactPhysicianProcedure].[FactPhysicianProcedureTotalCharge0_0] ) AS [FactPhysicianProcedureTotalCharge0_0],
SUM ( [FactPhysicianProcedure].[FactPhysicianProcedureBalance0_6] ) AS [FactPhysicianProcedureBalance0_6],COUNT_BIG ( [FactPhysicianProcedure].[FactPhysicianProcedure0_7] )
AS [FactPhysicianProcedure0_7],[dbo_MisService_6].[dbo_MisServiceName1_0] AS [dbo_MisServiceName1_0] FROM
(SELECT [TotalCharge] AS [FactPhysicianProcedureTotalCharge0_0],[Balance] AS
[FactPhysicianProcedureBalance0_6],1 AS [FactPhysicianProcedure0_7],[InptService] AS [FactPhysicianProcedureInptService0_8] FROM
(SELECT w.OperSurgeon, u.AccountNumber, w.OperProcSeqNoAbsPat AS OperProcSeqNo, w.OperProc, CAST(o.Drg AS varchar(4)) AS Drg, u.Age, u.DisMonth,
u.Sex, u.Zip_PostalCode, g.InptService, u.FinancialClass, v.AdmitPhysMnemonic, g.PtStatus, u.DisDate, u.DisYear, u.DisQuarter,u.DisMonth AS Month, DATEPART(day, u.DisDate) AS DisDay,
g.TotalCharge, u.LOS, g.TotalInsExpReimb, g.TotalInsRcpt, g.TotalCost, g.TotalInsAdj, g.Balance FROM dbo.TSG_PatientInformation AS u INNER JOIN
dbo.TSG_Procedures AS w ON u.UrnABS = w.UrnABS INNER JOIN dbo.TSG_Physicians AS v ON u.UrnABS = v.UrnABS INNER JOIN dbo.TSG_DrgData AS o ON u.UrnABS = o.UrnABS INNER JOIN dbo.TSG_BarAccountData AS g ON u.UrnABS = g.UrnABS) AS [FactPhysicianProcedure]) AS [FactPhysicianProcedure],

OPENROWSET(N'SQLNCLI.1',N'',N'SELECT [Name] AS [dbo_MisServiceName1_0],[MnemonicMISSVCDICT]
AS [dbo_MisServiceMnemonicMISSVCDICT1_1]
FROM [dbo].[MisService]') AS [dbo_MisService_6] WHERE (([FactPhysicianProcedure].[FactPhysicianProcedureInptService0_8]
=[dbo_MisService_6].[dbo_MisServiceMnemonicMISSVCDICT1_1]))
GROUP BY [dbo_MisService_6].[dbo_MisServiceName1_0]

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
Alan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/default...b;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact[vbcol=seagreen]
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.
> that
> missing
>
|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File[vbcol=seagreen]
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/default...b;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen]
> run
> data.
files).
>
|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling
> files).
>
|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and[vbcol=seagreen]
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is
>
|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When[vbcol=seagreen]
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file
> is
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driv
er] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initializ
e
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> CortexAlan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/defaul...kb;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
>
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.[vbcol=seagreen]
> that
> missing
>|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/defaul...kb;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen
]
> run
> data.
files).[vbcol=seagreen]
>|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling[vbcol=seagreen]
> files).
>|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is[vbcol=seagreen]
>|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file[vbcol=seagreen]
> is
>