Problem running full text search - it's broken!

I have a 2-tier application
The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
This works fine for ALL database calls, except when it requires a full text
query. The error message received is:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The catalog does not exist or is
currently unavailable. Please retry the action again later and if this
symptom persists, contact the system administrator.
However...I know that the full text catalogue DOES exist on the data tier
(I've rebuilt & repopulated it).
I also have an "identical" pair of machines and it works fine on that
one... By identical, I mean all the ASP code running under IIS is the
same.
No idea what I should be checking. The ADODB connection is identical on
both machines (uses a SQL Server account with identical access permissions)
and SQLServer is running under the ADMINISTRATOR account on both servers.
Thanks
Griff
Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Griff" <Howling@.The.Moon> wrote in message
news:e9f62cw3FHA.3628@.TK2MSFTNGP12.phx.gbl...
> I have a 2-tier application
> The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
> This works fine for ALL database calls, except when it requires a full
text
> query. The error message received is:
> --
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The catalog does not exist or
is
> currently unavailable. Please retry the action again later and if this
> symptom persists, contact the system administrator.
> --
> However...I know that the full text catalogue DOES exist on the data tier
> (I've rebuilt & repopulated it).
> I also have an "identical" pair of machines and it works fine on that
> one... By identical, I mean all the ASP code running under IIS is the
> same.
> No idea what I should be checking. The ADODB connection is identical on
> both machines (uses a SQL Server account with identical access
permissions)
> and SQLServer is running under the ADMINISTRATOR account on both servers.
> Thanks
> Griff
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hi Hilary
I'm afraid not. The only messages state that the catalogues were built
successfully, e.g.:
Master merge has completed on
d:\mssql\ftdata\sql0000700005\build\indexer\cifile s.
The only warning mentioned is:
SuperSocket info: (SpnRegister) : Error 1355.
Griff
|||Griff,
I think we need additional information from the server to understand why
this error OLE DB '80040e14' is being raised and that you have confirmed
that the full text catalogue DOES exist on this server. Could you set trace
flag 7300 on, you can get extended OLEDB error info?
dbcc traceon (3604, 7300)
Could you also post the exact CONTAINS* or FREETEXT* query that you're using
along with the search word or phrase, and re-run the query and post the
extended OLEDB error info?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Griff" <Howling@.The.Moon> wrote in message
news:uUpfG%2343FHA.3628@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary
> I'm afraid not. The only messages state that the catalogues were built
> successfully, e.g.:
> Master merge has completed on
> d:\mssql\ftdata\sql0000700005\build\indexer\cifile s.
> The only warning mentioned is:
> SuperSocket info: (SpnRegister) : Error 1355.
> Griff
>
sql

Problem running full text search - it's broken!

I have a 2-tier application
The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
This works fine for ALL database calls, except when it requires a full text
query. The error message received is:
--
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The catalog does not exist or is
currently unavailable. Please retry the action again later and if this
symptom persists, contact the system administrator.
--
However...I know that the full text catalogue DOES exist on the data tier
(I've rebuilt & repopulated it).
I also have an "identical" pair of machines and it works fine on that
one... By identical, I mean all the ASP code running under IIS is the
same.
No idea what I should be checking. The ADODB connection is identical on
both machines (uses a SQL Server account with identical access permissions)
and SQLServer is running under the ADMINISTRATOR account on both servers.
Thanks
GriffAre there any pertinent messages from MSSearch or MSSCI in the event log?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Griff" <Howling@.The.Moon> wrote in message
news:e9f62cw3FHA.3628@.TK2MSFTNGP12.phx.gbl...
> I have a 2-tier application
> The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
> This works fine for ALL database calls, except when it requires a full
text
> query. The error message received is:
> --
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The catalog does not exist or
is
> currently unavailable. Please retry the action again later and if this
> symptom persists, contact the system administrator.
> --
> However...I know that the full text catalogue DOES exist on the data tier
> (I've rebuilt & repopulated it).
> I also have an "identical" pair of machines and it works fine on that
> one... By identical, I mean all the ASP code running under IIS is the
> same.
> No idea what I should be checking. The ADODB connection is identical on
> both machines (uses a SQL Server account with identical access
permissions)
> and SQLServer is running under the ADMINISTRATOR account on both servers.
> Thanks
> Griff
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hi Hilary
I'm afraid not. The only messages state that the catalogues were built
successfully, e.g.:
Master merge has completed on
d:\mssql\ftdata\sql0000700005\build\indexer\cifiles.
The only warning mentioned is:
SuperSocket info: (SpnRegister) : Error 1355.
Griff|||Griff,
I think we need additional information from the server to understand why
this error OLE DB '80040e14' is being raised and that you have confirmed
that the full text catalogue DOES exist on this server. Could you set trace
flag 7300 on, you can get extended OLEDB error info?
dbcc traceon (3604, 7300)
Could you also post the exact CONTAINS* or FREETEXT* query that you're using
along with the search word or phrase, and re-run the query and post the
extended OLEDB error info?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Griff" <Howling@.The.Moon> wrote in message
news:uUpfG%2343FHA.3628@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
>> Are there any pertinent messages from MSSearch or MSSCI in the event log?
> Hi Hilary
> I'm afraid not. The only messages state that the catalogues were built
> successfully, e.g.:
> Master merge has completed on
> d:\mssql\ftdata\sql0000700005\build\indexer\cifiles.
> The only warning mentioned is:
> SuperSocket info: (SpnRegister) : Error 1355.
> Griff
>

Problem running full text search - it's broken!

I have a 2-tier application
The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
This works fine for ALL database calls, except when it requires a full text
query. The error message received is:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The catalog does not exist or is
currently unavailable. Please retry the action again later and if this
symptom persists, contact the system administrator.
However...I know that the full text catalogue DOES exist on the data tier
(I've rebuilt & repopulated it).
I also have an "identical" pair of machines and it works fine on that
one... By identical, I mean all the ASP code running under IIS is the
same.
No idea what I should be checking. The ADODB connection is identical on
both machines (uses a SQL Server account with identical access permissions)
and SQLServer is running under the ADMINISTRATOR account on both servers.
Thanks
Griff
Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Griff" <Howling@.The.Moon> wrote in message
news:e9f62cw3FHA.3628@.TK2MSFTNGP12.phx.gbl...
> I have a 2-tier application
> The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
> This works fine for ALL database calls, except when it requires a full
text
> query. The error message received is:
> --
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The catalog does not exist or
is
> currently unavailable. Please retry the action again later and if this
> symptom persists, contact the system administrator.
> --
> However...I know that the full text catalogue DOES exist on the data tier
> (I've rebuilt & repopulated it).
> I also have an "identical" pair of machines and it works fine on that
> one... By identical, I mean all the ASP code running under IIS is the
> same.
> No idea what I should be checking. The ADODB connection is identical on
> both machines (uses a SQL Server account with identical access
permissions)
> and SQLServer is running under the ADMINISTRATOR account on both servers.
> Thanks
> Griff
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hi Hilary
I'm afraid not. The only messages state that the catalogues were built
successfully, e.g.:
Master merge has completed on
d:\mssql\ftdata\sql0000700005\build\indexer\cifile s.
The only warning mentioned is:
SuperSocket info: (SpnRegister) : Error 1355.
Griff
|||Griff,
I think we need additional information from the server to understand why
this error OLE DB '80040e14' is being raised and that you have confirmed
that the full text catalogue DOES exist on this server. Could you set trace
flag 7300 on, you can get extended OLEDB error info?
dbcc traceon (3604, 7300)
Could you also post the exact CONTAINS* or FREETEXT* query that you're using
along with the search word or phrase, and re-run the query and post the
extended OLEDB error info?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Griff" <Howling@.The.Moon> wrote in message
news:uUpfG%2343FHA.3628@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary
> I'm afraid not. The only messages state that the catalogues were built
> successfully, e.g.:
> Master merge has completed on
> d:\mssql\ftdata\sql0000700005\build\indexer\cifile s.
> The only warning mentioned is:
> SuperSocket info: (SpnRegister) : Error 1355.
> Griff
>

Problem running full text search - it's broken!

I have a 2-tier application
The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
This works fine for ALL database calls, except when it requires a full text
query. The error message received is:
--
Microsoft OLE DB Provider for SQL Server error '80040e14'
Execution of a full-text operation failed. The catalog does not exist or is
currently unavailable. Please retry the action again later and if this
symptom persists, contact the system administrator.
However...I know that the full text catalogue DOES exist on the data tier
(I've rebuilt & repopulated it).
I also have an "identical" pair of machines and it works fine on that
one... By identical, I mean all the ASP code running under IIS is the
same.
No idea what I should be checking. The ADODB connection is identical on
both machines (uses a SQL Server account with identical access permissions)
and SQLServer is running under the ADMINISTRATOR account on both servers.
Thanks
GriffAre there any pertinent messages from MSSearch or MSSCI in the event log?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Griff" <Howling@.The.Moon> wrote in message
news:e9f62cw3FHA.3628@.TK2MSFTNGP12.phx.gbl...
> I have a 2-tier application
> The top tier is a web tier (IIS/ASP) and the lower tier runs SQLServer.
> This works fine for ALL database calls, except when it requires a full
text
> query. The error message received is:
> --
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Execution of a full-text operation failed. The catalog does not exist or
is
> currently unavailable. Please retry the action again later and if this
> symptom persists, contact the system administrator.
> --
> However...I know that the full text catalogue DOES exist on the data tier
> (I've rebuilt & repopulated it).
> I also have an "identical" pair of machines and it works fine on that
> one... By identical, I mean all the ASP code running under IIS is the
> same.
> No idea what I should be checking. The ADODB connection is identical on
> both machines (uses a SQL Server account with identical access
permissions)
> and SQLServer is running under the ADMINISTRATOR account on both servers.
> Thanks
> Griff
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Are there any pertinent messages from MSSearch or MSSCI in the event log?
Hi Hilary
I'm afraid not. The only messages state that the catalogues were built
successfully, e.g.:
Master merge has completed on
d:\mssql\ftdata\sql0000700005\build\inde
xer\cifiles.
The only warning mentioned is:
SuperSocket info: (SpnRegister) : Error 1355.
Griff|||Griff,
I think we need additional information from the server to understand why
this error OLE DB '80040e14' is being raised and that you have confirmed
that the full text catalogue DOES exist on this server. Could you set trace
flag 7300 on, you can get extended OLEDB error info?
dbcc traceon (3604, 7300)
Could you also post the exact CONTAINS* or FREETEXT* query that you're using
along with the search word or phrase, and re-run the query and post the
extended OLEDB error info?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Griff" <Howling@.The.Moon> wrote in message
news:uUpfG%2343FHA.3628@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ul3Lixw3FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary
> I'm afraid not. The only messages state that the catalogues were built
> successfully, e.g.:
> Master merge has completed on
> d:\mssql\ftdata\sql0000700005\build\inde
xer\cifiles.
> The only warning mentioned is:
> SuperSocket info: (SpnRegister) : Error 1355.
> Griff
>

Problem report xp_MSADEnabled

Hello,
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259).
If I call the same procedure on a Windows 2003 Server machine, it works fine.
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command.
The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error.
greeting, Marco
Hi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marco

Problem report xp_MSADEnabled

Hello
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259)
If I call the same procedure on a Windows 2003 Server machine, it works fine
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command. The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error
greeting, MarcoHi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marcosql

Problem report xp_MSADEnabled

Hello,
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259).
If I call the same procedure on a Windows 2003 Server machine, it works fine.
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command.
The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error.
greeting, Marco
This procedure test to see if the particular SQL Server is Active Directory enabled.
In your code are you publishing your publication to Active Directory? IE in the proc sp_addpublication are you setting
@.add_to_active_directory to true?
Do you need this functionality?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks for reply.
I don't need active directory functionality. I set the @.add_to_active_directory to false but the sp_addmergepublication still calls xp_MSADEnabled.
-- Hilary Cotter wrote: --
This procedure test to see if the particular SQL Server is Active Directory enabled.
In your code are you publishing your publication to Active Directory? IE in the proc sp_addpublication are you setting
@.add_to_active_directory to true?
Do you need this functionality?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marco

problem porting MySQL to SQLServer

Hi I am trying to port a mysql database to microsoft SQL server 2000. I
downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
create the odbc machine source and the connection to the MySQLmachine works
(using access)
but when I try to import a table into Access I get the error Microsoft acces
s
reserved error (-7701) there is no message for this error.
Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
tables are not too large.
thanks.
Paul G
Software engineer.Hello Paul,

> Hi I am trying to port a mysql database to microsoft SQL server 2000. I
> downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
> create the odbc machine source and the connection to the MySQLmachine
works
> (using access)
> but when I try to import a table into Access I get the error Microsoft
access
> reserved error (-7701) there is no message for this error.
> Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
> tables are not too large.
You might try our database tool: Database Workbench ( www.upscene.com )
It includes a Schema Migration and DataPump tool.
Connections to both mysql and MS SQL Server natively.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Hi thanks for the response. Just wondering if you have a demo, if it works
ok I can most likely get management to make a purchase if the price is not
too high.
thanks.
--
Paul G
Software engineer.
"Martijn Tonies" wrote:

> Hello Paul,
>
> works
> access
> You might try our database tool: Database Workbench ( www.upscene.com )
> It includes a Schema Migration and DataPump tool.
> Connections to both mysql and MS SQL Server natively.
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>|||fixed the problem, seemed to work on another machine with more memory so mos
t
likely memory related.
--
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. Just wondering if you have a demo, if it work
s
> ok I can most likely get management to make a purchase if the price is not
> too high.
> thanks.
> --
> Paul G
> Software engineer.
>
> "Martijn Tonies" wrote:
>|||On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>fixed the problem, seemed to work on another machine with more memory so mo
st
>likely memory related.
If all else fails, export the data into flat files and BCP them into
SQLServer. Not a bad strategy in any case.
J.|||Thanks for the information, just wondering how to export the data into flat
files, can you do this using MySQLFront?
Paul G
Software engineer.
"jxstern" wrote:

> On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
> <Paul@.discussions.microsoft.com> wrote:
> If all else fails, export the data into flat files and BCP them into
> SQLServer. Not a bad strategy in any case.
> J.
>|||On Mon, 12 Sep 2005 12:15:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>Thanks for the information, just wondering how to export the data into flat
>files, can you do this using MySQLFront?
Or CSV.
Sorry, I'm rather rusty on MySQL, but I'm sure it's doable.
J.|||
> Thanks for the information, just wondering how to export the data into
flat
> files, can you do this using MySQLFront?
And with our tool as well ;-)
That is, IF it's version mysql 4 and up.
You might want to try the DataPump tool in it -- that should not have
problems with mysql < 4... Haven't tried it though.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

problem porting MySQL to SQLServer

Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
create the odbc machine source and the connection to the MySQLmachine works
(using access)
but when I try to import a table into Access I get the error Microsoft access
reserved error (-7701) there is no message for this error.
Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
tables are not too large.
thanks.
Paul G
Software engineer.
Hello Paul,

> Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
> downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
> create the odbc machine source and the connection to the MySQLmachine
works
> (using access)
> but when I try to import a table into Access I get the error Microsoft
access
> reserved error (-7701) there is no message for this error.
> Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
> tables are not too large.
You might try our database tool: Database Workbench ( www.upscene.com )
It includes a Schema Migration and DataPump tool.
Connections to both MySQL and MS SQL Server natively.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
|||Hi thanks for the response. Just wondering if you have a demo, if it works
ok I can most likely get management to make a purchase if the price is not
too high.
thanks.
Paul G
Software engineer.
"Martijn Tonies" wrote:

> Hello Paul,
> works
> access
> You might try our database tool: Database Workbench ( www.upscene.com )
> It includes a Schema Migration and DataPump tool.
> Connections to both MySQL and MS SQL Server natively.
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
|||fixed the problem, seemed to work on another machine with more memory so most
likely memory related.
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. Just wondering if you have a demo, if it works
> ok I can most likely get management to make a purchase if the price is not
> too high.
> thanks.
> --
> Paul G
> Software engineer.
>
> "Martijn Tonies" wrote:
|||On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>fixed the problem, seemed to work on another machine with more memory so most
>likely memory related.
If all else fails, export the data into flat files and BCP them into
SQLServer. Not a bad strategy in any case.
J.
|||Thanks for the information, just wondering how to export the data into flat
files, can you do this using MySQLFront?
Paul G
Software engineer.
"jxstern" wrote:

> On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
> <Paul@.discussions.microsoft.com> wrote:
> If all else fails, export the data into flat files and BCP them into
> SQLServer. Not a bad strategy in any case.
> J.
>
|||On Mon, 12 Sep 2005 12:15:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>Thanks for the information, just wondering how to export the data into flat
>files, can you do this using MySQLFront?
Or CSV.
Sorry, I'm rather rusty on MySQL, but I'm sure it's doable.
J.
|||
> Thanks for the information, just wondering how to export the data into
flat
> files, can you do this using MySQLFront?
And with our tool as well ;-)
That is, IF it's version MySQL 4 and up.
You might want to try the DataPump tool in it -- that should not have
problems with MySQL < 4... Haven't tried it though.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
sql

problem porting MySQL to SQLServer

Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
create the odbc machine source and the connection to the MySQLmachine works
(using access)
but when I try to import a table into Access I get the error Microsoft access
reserved error (-7701) there is no message for this error.
Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
tables are not too large.
thanks.
--
Paul G
Software engineer.Hello Paul,
> Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
> downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
> create the odbc machine source and the connection to the MySQLmachine
works
> (using access)
> but when I try to import a table into Access I get the error Microsoft
access
> reserved error (-7701) there is no message for this error.
> Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
> tables are not too large.
You might try our database tool: Database Workbench ( www.upscene.com )
It includes a Schema Migration and DataPump tool.
Connections to both MySQL and MS SQL Server natively.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Hi thanks for the response. Just wondering if you have a demo, if it works
ok I can most likely get management to make a purchase if the price is not
too high.
thanks.
--
Paul G
Software engineer.
"Martijn Tonies" wrote:
> Hello Paul,
> > Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
> > downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
> > create the odbc machine source and the connection to the MySQLmachine
> works
> > (using access)
> > but when I try to import a table into Access I get the error Microsoft
> access
> > reserved error (-7701) there is no message for this error.
> > Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
> > tables are not too large.
> You might try our database tool: Database Workbench ( www.upscene.com )
> It includes a Schema Migration and DataPump tool.
> Connections to both MySQL and MS SQL Server natively.
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>|||fixed the problem, seemed to work on another machine with more memory so most
likely memory related.
--
Paul G
Software engineer.
"Paul" wrote:
> Hi thanks for the response. Just wondering if you have a demo, if it works
> ok I can most likely get management to make a purchase if the price is not
> too high.
> thanks.
> --
> Paul G
> Software engineer.
>
> "Martijn Tonies" wrote:
> > Hello Paul,
> >
> > > Hi I am trying to port a MySQL database to microsoft SQL server 2000. I
> > > downloaded the mysql odbc driver, both versions 3.1 and 5.0. I am able to
> > > create the odbc machine source and the connection to the MySQLmachine
> > works
> > > (using access)
> > > but when I try to import a table into Access I get the error Microsoft
> > access
> > > reserved error (-7701) there is no message for this error.
> > > Anyhow just wondering if anyone has any ideas? I am hoping that the MySQL
> > > tables are not too large.
> >
> > You might try our database tool: Database Workbench ( www.upscene.com )
> > It includes a Schema Migration and DataPump tool.
> >
> > Connections to both MySQL and MS SQL Server natively.
> >
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> >
> >
> >|||On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>fixed the problem, seemed to work on another machine with more memory so most
>likely memory related.
If all else fails, export the data into flat files and BCP them into
SQLServer. Not a bad strategy in any case.
J.|||Thanks for the information, just wondering how to export the data into flat
files, can you do this using MySQLFront?
--
Paul G
Software engineer.
"jxstern" wrote:
> On Mon, 12 Sep 2005 11:12:04 -0700, "Paul"
> <Paul@.discussions.microsoft.com> wrote:
> >fixed the problem, seemed to work on another machine with more memory so most
> >likely memory related.
> If all else fails, export the data into flat files and BCP them into
> SQLServer. Not a bad strategy in any case.
> J.
>|||On Mon, 12 Sep 2005 12:15:04 -0700, "Paul"
<Paul@.discussions.microsoft.com> wrote:
>Thanks for the information, just wondering how to export the data into flat
>files, can you do this using MySQLFront?
Or CSV.
Sorry, I'm rather rusty on MySQL, but I'm sure it's doable.
J.|||> Thanks for the information, just wondering how to export the data into
flat
> files, can you do this using MySQLFront?
And with our tool as well ;-)
That is, IF it's version MySQL 4 and up.
You might want to try the DataPump tool in it -- that should not have
problems with MySQL < 4... Haven't tried it though.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

problem making Oracle linked server work

Hi all,

I set up our Oracle Financials as a linked server to one of my SQL
Server boxes. On running a test query, I got the following error
message:
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 reported
to have a
DBTYPE of 130 at compile time and 5 at run time].

The Oracle datatype of the column with the supposedly inconsistent
metadata was NUMBER, which according to the Oracle OLE DB documentation
actually maps to 139. 130 is a null-terminated unicode character
string, 5 is a float, and 139 is a variable-length, exact numeric value
with a signed scale value. Oracle NUMBER is an all-purpose numeric
type, apparently they use that instead of int, float, etc. The Oracle
guy used it for this column in the test table because in OF it is used
in pretty much every table. (For starters it is the datatype of their
identity columns.)

There is something in the OLE DB spec about all datatypes having to be
able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
I can connect to the same Oracle instance using VB6 code and the
MSDAORA provider and there is no problem at all interpreting the NUMBER
columns. So why does it work from VB and not as a linked server? And
much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?

TIAYou might try upgrading the latest MDAC version (2.8) if you haven't already
done so. You can also run the MDAC component checker to ensure the binaries
match for the installed version.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1110695346.557634.70280@.l41g2000cwc.googlegro ups.com...
> Hi all,
> I set up our Oracle Financials as a linked server to one of my SQL
> Server boxes. On running a test query, I got the following error
> message:
> 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 reported
> to have a
> DBTYPE of 130 at compile time and 5 at run time].
> The Oracle datatype of the column with the supposedly inconsistent
> metadata was NUMBER, which according to the Oracle OLE DB documentation
> actually maps to 139. 130 is a null-terminated unicode character
> string, 5 is a float, and 139 is a variable-length, exact numeric value
> with a signed scale value. Oracle NUMBER is an all-purpose numeric
> type, apparently they use that instead of int, float, etc. The Oracle
> guy used it for this column in the test table because in OF it is used
> in pretty much every table. (For starters it is the datatype of their
> identity columns.)
> There is something in the OLE DB spec about all datatypes having to be
> able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
> I can connect to the same Oracle instance using VB6 code and the
> MSDAORA provider and there is no problem at all interpreting the NUMBER
> columns. So why does it work from VB and not as a linked server? And
> much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?
> TIA|||Thanks, Dan.

My desktop and the SQL Server box are currently both running the exact
same version of MDAC 2.7 but I guess moving to 2.8 can't hurt.|||Did 2.8 solve the problem?|||I didn't try it. Honestly I don't see how the problem can be with
MDAC.

As previously noted, using the MSDAORA provider from VB6 I have no
problem talking to Oracle, the problem is only using the MSDAORA
provider to talk to Oracle as a linked server from SQL Server. The
MDAC on the SQL Server box is exactly the same as the MDAC on the box
using VB6.

<shrug|||Hi

Did you check out
http://support.microsoft.com/defaul...kb;en-us;251238
http://support.microsoft.com/defaul...kb;en-us;280106

John

"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1112595163.198646.201490@.g14g2000cwa.googlegr oups.com...
>I didn't try it. Honestly I don't see how the problem can be with
> MDAC.
> As previously noted, using the MSDAORA provider from VB6 I have no
> problem talking to Oracle, the problem is only using the MSDAORA
> provider to talk to Oracle as a linked server from SQL Server. The
> MDAC on the SQL Server box is exactly the same as the MDAC on the box
> using VB6.
> <shrug|||Hi John,

Thank you very much!

The second one has a bunch of links to other ones, at one of which I at
least found a more exact explanation: "The column with Numeric
datatype has no Length specified (no Precision, no Default, allows
NULL). The number datatype without a precision and scale is represented
in Oracle by a variable-length numeric with precision of up to 255.
There is no SQL Server type that this can be mapped to without loss of
precision." And one of the others states "An Oracle numeric type is
now mapped to nvarchar (384) if the precision is too large for a
numeric SQL Server type."

So -- ta-dah! -- this is how the datatype is being converted at
runtime, although none of the articles explains this in so many words.
It's also interesting that even though these articles claim to be about
SQL Server 7, I am having the problem on 2000.

One of the workarounds they mention, specifying precision and scale of
any NUMBER columns, we already thought of and tried and it works... but
I don't think we can go do that to every NUMBER column in Oracle
Financials, it might not be possible to change them at all and even if
so I don't think it would be a very good idea.

The main reason I wanted the linked server was to be able to make a
distributed transaction to set up products in Oracle Inventory, my SQL
Server transactional database, and my SQL Server data warehouse to
ensure that everybody will be in sync. To do the Oracle piece we
created a private table on the Oracle box, to which I will write... the
Oracle guy has a trigger on it that sends the data to the Oracle
product setup process... when it finishes he comes back and writes to a
process flag column, which I can then read to make sure it worked. So
for the private table if he defines the precision and scale we have no
problem.

It would have been nice to be able to read directly from Oracle also
but I can live without that, just code the parts of the data warehouse
ETL that need Oracle data, because as previously noted the problem does
not occur from VB code.

One of the articles did also mention that MSDAORA is in maintenance
mode and not updated for Oracle versions greater than 8i (we are on
9i), but there is now a .Net managed provider... maybe I will try that
when we go to .Net later this year. (Oracle also puts out their own
OLE DB provider, but with that one I can't even connect.)

Meanwhile the part I REALLY don't get is how come *I* didn't find these
articles when I searched on the Microsoft site!!!

Anyway, thanks again,

Ellen :)|||Hi Ellen

Ellen K wrote:
> Hi John,
> Thank you very much!
> The second one has a bunch of links to other ones, at one of which I
at
> least found a more exact explanation: "The column with Numeric
> datatype has no Length specified (no Precision, no Default, allows
> NULL). The number datatype without a precision and scale is
represented
> in Oracle by a variable-length numeric with precision of up to 255.
> There is no SQL Server type that this can be mapped to without loss
of
> precision." And one of the others states "An Oracle numeric type is
> now mapped to nvarchar (384) if the precision is too large for a
> numeric SQL Server type."
> So -- ta-dah! -- this is how the datatype is being converted at
> runtime, although none of the articles explains this in so many
words.
> It's also interesting that even though these articles claim to be
about
> SQL Server 7, I am having the problem on 2000.
I think you will have the same behaviour as this is (probably) more to
do with MDAC versions than SQL Server.
> One of the workarounds they mention, specifying precision and scale
of
> any NUMBER columns, we already thought of and tried and it works...
but
> I don't think we can go do that to every NUMBER column in Oracle
> Financials, it might not be possible to change them at all and even
if
> so I don't think it would be a very good idea.
> The main reason I wanted the linked server was to be able to make a
> distributed transaction to set up products in Oracle Inventory, my
SQL
> Server transactional database, and my SQL Server data warehouse to
> ensure that everybody will be in sync. To do the Oracle piece we
> created a private table on the Oracle box, to which I will write...
the
> Oracle guy has a trigger on it that sends the data to the Oracle
> product setup process... when it finishes he comes back and writes to
a
> process flag column, which I can then read to make sure it worked.
So
> for the private table if he defines the precision and scale we have
no
> problem.

If your Oracle server is male then it will never work!!

> It would have been nice to be able to read directly from Oracle also
> but I can live without that, just code the parts of the data
warehouse
> ETL that need Oracle data, because as previously noted the problem
does
> not occur from VB code.
> One of the articles did also mention that MSDAORA is in maintenance
> mode and not updated for Oracle versions greater than 8i (we are on
> 9i), but there is now a .Net managed provider... maybe I will try
that
> when we go to .Net later this year. (Oracle also puts out their own
> OLE DB provider, but with that one I can't even connect.)

At a guess something to do with SQL*Net or the configuration files, I
have never used the OLEDB driver but it may be worth investigating.

> Meanwhile the part I REALLY don't get is how come *I* didn't find
these
> articles when I searched on the Microsoft site!!!
C'est la vie.

> Anyway, thanks again,
> Ellen :)

John|||Hi John,

If MDAC was the issue, I would not be able to pull NUMBER data with VB
code. SQL Server is the issue, it's because as the one article noted
it doesn't have any datatype with a precision of 255. (I guess maybe
some scientific applications might need precision of 255, but if I were
designing an RDBMS I would make that some special datatype, I wouldn't
default all numerics to such a thing. It's very arrogant.)

I'm going to try using OPENQUERY instead of the four-part identifier,
with TO_CHAR on any NUMBER data elements that don't have a reasonable
precision specified... I can convert them back to the appropriate
numeric types. If this works I will forget about the Oracle brand
provider, since our Oracle guy already has plenty of work to keep him
busy.

And I'm SURE the Oracle server is male! <ggg
Thanks again for your help,

Ellen :)|||Ellen K wrote:

> Hi John,
> If MDAC was the issue, I would not be able to pull NUMBER data with VB
> code. SQL Server is the issue, it's because as the one article noted
> it doesn't have any datatype with a precision of 255. (I guess maybe
> some scientific applications might need precision of 255, but if I were
> designing an RDBMS I would make that some special datatype, I wouldn't
> default all numerics to such a thing. It's very arrogant.)
> I'm going to try using OPENQUERY instead of the four-part identifier,
> with TO_CHAR on any NUMBER data elements that don't have a reasonable
> precision specified... I can convert them back to the appropriate
> numeric types. If this works I will forget about the Oracle brand
> provider, since our Oracle guy already has plenty of work to keep him
> busy.
> And I'm SURE the Oracle server is male! <ggg>
> Thanks again for your help,
> Ellen :)

Why don't you either (A) read the Oracle documentation which is all
readily available at http://tahiti.oracle.com or (B) post your inquiry
at comp.databases.oracle.server?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||The Oracle documentation CD was the FIRST place I looked. I did find
the definition of the NUMBER datatype there, but nothing else useful.
If you found something specific that would be helpful, please post it.|||All,

The solution turned out to be to use OPENQUERY instead of the four-part
qualifier.

I am a very happy camper right now. :)

Thanks to all who helped,

Ellen