cannot edit this cell

when I fetch recods via enterprise manager and try to insert new
record I get the following error
"cannot edit this cell" but when I use "query analyzer" using T-sql commands I can execute DML operations .

it seems that there is no problem in the permissions ,I can not understand what is the problem .

can any one please help me it is very urgent since the problem is in one
of our production databases.

note that I use sql server 2000Your description does not sound this complicated - but check out the following article:

article (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q288969&)|||Maybe the cell in the column is defined as an Identity column?|||give the sp_help 'tablename' and see the owner of the table may be it is the owners name through whch ur not entering to the table .

cannot edit text cell

I am trying to edit a long text cell in Enterprise Manager but when I select
"<long text>" and try to delete it, I get an error saying "cannot edit this
cell".

Could anyone tell me how to edit this cell?

Thanks, AmandaHi

When your text column has more than 900 characters it will not allow you to
enter it via EM.

You can use Query Analyser (which is usually easier and safer IMO) to issue
an update statement, or for more complicated changes look up UPDATETEXT in
books online.

John

"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda|||"Amanda H" <amanda2@.southwind.org> wrote in message
news:K58Pd.1784$VI1.215392@.twister.southeast.rr.co m...
>I am trying to edit a long text cell in Enterprise Manager but when I
>select
> "<long text>" and try to delete it, I get an error saying "cannot edit
> this
> cell".
> Could anyone tell me how to edit this cell?
> Thanks, Amanda

Use an UPDATE statement in Query Analyzer is probably the best thing to do -
EM isn't really a data manipulation or development tool, and it has a number
of 'features'.

http://www.aspfaq.com/show.asp?id=2455

Simonsql

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3033.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3033.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve

cannot delete database

Hello:
I am trying to delete a database from Enterprise Manager 2000 SP4. I am
right-clicking on the database and choosing delete.
But, SQL does not delete the database. It freezes up, in fact.
How can I delete this database? Actually, there are about 15 in total that
I need to delete. This one that I cannot delete is the first of those 15. I
imagine that I will have this same problem with the others.
Thanks!
childofthe1980s
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s
Don't use the GUI.
Use Query Analyzer and use DROP DATABASE 'foo'.
It should return with either the database dropped or a message that it is in
use.
use SP_who to find out who is using it and disconnect the clients or KILL
the spids.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||Rather than writing a cursor for killing the processes, I would use the
ALTER DATABASE command setting the user Mode to SINGLE_USER (or using the
administrative mode, otherwise the users or the applications will login
again) and use the ROLLBACK IMMEDIATE switch to change the status of the
database immediately. Be aware that current transactions are rolled back
then. If you want to have another behaviour, you should use another switch
instead (see the BOL for more information about that). AFterwards use the
DROP DATABASE command to wipe out the database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23J%23tFYDZHHA.1240@.TK2MSFTNGP04.phx.gbl...
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
>
> Don't use the GUI.
> Use Query Analyzer and use DROP DATABASE 'foo'.
> It should return with either the database dropped or a message that it is
> in use.
> use SP_who to find out who is using it and disconnect the clients or KILL
> the spids.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
|||Hello,
While you drop a database using enterprise manager, it deletes all the
Backup and Restore history informations from MSDB database. This
will take a long time being this deleted inside a cursor. So it would be
better to drop the databse using the TSQL command
DROP DATABASE <DBNAME>
Thanks
Hari
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s
sql

cannot delete database

Hello:
I am trying to delete a database from Enterprise Manager 2000 SP4. I am
right-clicking on the database and choosing delete.
But, SQL does not delete the database. It freezes up, in fact.
How can I delete this database? Actually, there are about 15 in total that
I need to delete. This one that I cannot delete is the first of those 15. I
imagine that I will have this same problem with the others.
Thanks!
childofthe1980s"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s
Don't use the GUI.
Use Query Analyzer and use DROP DATABASE 'foo'.
It should return with either the database dropped or a message that it is in
use.
use SP_who to find out who is using it and disconnect the clients or KILL
the spids.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Rather than writing a cursor for killing the processes, I would use the
ALTER DATABASE command setting the user Mode to SINGLE_USER (or using the
administrative mode, otherwise the users or the applications will login
again) and use the ROLLBACK IMMEDIATE switch to change the status of the
database immediately. Be aware that current transactions are rolled back
then. If you want to have another behaviour, you should use another switch
instead (see the BOL for more information about that). AFterwards use the
DROP DATABASE command to wipe out the database.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23J%23tFYDZHHA.1240@.TK2MSFTNGP04.phx.gbl...
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
>> Hello:
>> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
>> right-clicking on the database and choosing delete.
>> But, SQL does not delete the database. It freezes up, in fact.
>> How can I delete this database? Actually, there are about 15 in total
>> that
>> I need to delete. This one that I cannot delete is the first of those
>> 15. I
>> imagine that I will have this same problem with the others.
>> Thanks!
>> childofthe1980s
>
> Don't use the GUI.
> Use Query Analyzer and use DROP DATABASE 'foo'.
> It should return with either the database dropped or a message that it is
> in use.
> use SP_who to find out who is using it and disconnect the clients or KILL
> the spids.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>|||Hello,
While you drop a database using enterprise manager, it deletes all the
Backup and Restore history informations from MSDB database. This
will take a long time being this deleted inside a cursor. So it would be
better to drop the databse using the TSQL command
DROP DATABASE <DBNAME>
Thanks
Hari
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s

cannot delete database

Hello:
I am trying to delete a database from Enterprise Manager 2000 SP4. I am
right-clicking on the database and choosing delete.
But, SQL does not delete the database. It freezes up, in fact.
How can I delete this database? Actually, there are about 15 in total that
I need to delete. This one that I cannot delete is the first of those 15.
I
imagine that I will have this same problem with the others.
Thanks!
childofthe1980s"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s
Don't use the GUI.
Use Query Analyzer and use DROP DATABASE 'foo'.
It should return with either the database dropped or a message that it is in
use.
use SP_who to find out who is using it and disconnect the clients or KILL
the spids.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Rather than writing a cursor for killing the processes, I would use the
ALTER DATABASE command setting the user Mode to SINGLE_USER (or using the
administrative mode, otherwise the users or the applications will login
again) and use the ROLLBACK IMMEDIATE switch to change the status of the
database immediately. Be aware that current transactions are rolled back
then. If you want to have another behaviour, you should use another switch
instead (see the BOL for more information about that). AFterwards use the
DROP DATABASE command to wipe out the database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23J%23tFYDZHHA.1240@.TK2MSFTNGP04.phx.gbl...
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
>
> Don't use the GUI.
> Use Query Analyzer and use DROP DATABASE 'foo'.
> It should return with either the database dropped or a message that it is
> in use.
> use SP_who to find out who is using it and disconnect the clients or KILL
> the spids.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>|||Hello,
While you drop a database using enterprise manager, it deletes all the
Backup and Restore history informations from MSDB database. This
will take a long time being this deleted inside a cursor. So it would be
better to drop the databse using the TSQL command
DROP DATABASE <DBNAME>
Thanks
Hari
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:9DE05088-641D-42B4-AC3B-2687C3F7D7AA@.microsoft.com...
> Hello:
> I am trying to delete a database from Enterprise Manager 2000 SP4. I am
> right-clicking on the database and choosing delete.
> But, SQL does not delete the database. It freezes up, in fact.
> How can I delete this database? Actually, there are about 15 in total
> that
> I need to delete. This one that I cannot delete is the first of those 15.
> I
> imagine that I will have this same problem with the others.
> Thanks!
> childofthe1980s

Cannot create new Subscription?

Hi,

I recently got the September CTP Enterprise version and installed it on box with Windows Server2003 os. When I go to a report and try to create a new subscription by going to SQL Server management studio, that option is disabled for me.

I have already setup the email configurations and started the sql server agent.

Anyone has ideas why this should happen?
Most likely, the credentials for the report aren't stored. What do you see in Report Manager? The same?|||

I'm having a similar problem where:

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Cannot create new Subscription?

Hi,

I recently got the September CTP Enterprise version and installed it on box with Windows Server2003 os. When I go to a report and try to create a new subscription by going to SQL Server management studio, that option is disabled for me.

I have already setup the email configurations and started the sql server agent.

Anyone has ideas why this should happen?
Most likely, the credentials for the report aren't stored. What do you see in Report Manager? The same?|||

I'm having a similar problem where:

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Cannot create linked server

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Try:

EXEC sp_addlinkedserver 'mysql', '', 'MSDASQL', 'your_system_dsn'

EXEC sp_addlinkedsrvlogin 'mysql', 'false', null, 'userid', 'pwd'|||Sorry - have already tried that too. Same error message.

Thanks for your help!

Brad
Ex Nihilo, Nihil Fit|||

There are two possibilities:

1. Incorrect provider-string
2. Incorrect linked server setup

To isolate the issue, try to use the provider string with OPENROWSET syntax. If this works, then try to create a new linked server definition with appropriate login mappings.

|||

Brad,

Sorry, I do not use mySQL so cannot be of much help as far as the correct provider/library.

However, this KB looks like a solution to your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

Cannot create linked server

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Try:

EXEC sp_addlinkedserver 'mysql', '', 'MSDASQL', 'your_system_dsn'

EXEC sp_addlinkedsrvlogin 'mysql', 'false', null, 'userid', 'pwd'|||Sorry - have already tried that too. Same error message.

Thanks for your help!

Brad
Ex Nihilo, Nihil Fit
|||

There are two possibilities:

1. Incorrect provider-string
2. Incorrect linked server setup

To isolate the issue, try to use the provider string with OPENROWSET syntax. If this works, then try to create a new linked server definition with appropriate login mappings.

|||

Brad,

Sorry, I do not use mySQL so cannot be of much help as far as the correct provider/library.

However, this KB looks like a solution to your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard Edition Service Pack 4 running on a Windows 2000 Standard Edition server.

The MySQL server is running on Red Hat - not sure of the DB or OS version. However the DSN I created connects to the server correctly. I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does not work either. It appears to create the linked server correctly each time, but I cannot access any of the tables. Here is the error message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource name not found and no default driver specified] OLD DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!
|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit
|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit
|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!
|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create linked server

I posted this in the Data Access forum already - may have been the wrong place...

I cannot create a linked to a MySql database in Enterprise Manager. Here is the relevant info:

Trying to create a linked server on a SQL Server 2000 Standard

Edition Service Pack 4 running on a Windows 2000 Standard Edition

server.

The MySQL server is running on Red Hat - not sure of the DB or OS

version. However the DSN I created connects to the server correctly.

I can connect using any of the MySQL tools.

I am using the latest ODBC MySQL drive 3.51...Here is the Provider string I am using...

DRIVER={MySQL ODBC 3.51 Driver} ;SERVER=192.168.1.99;DATABASE=bug_tracker; USER=UserID; PASSWORD=Password; OPTION=3

I have also tried using a System DSN to connect with, but that does

not work either. It appears to create the linked server correctly each

time, but I cannot access any of the tables. Here is the error

message I get each time:

Error 7399: OLE DB Provider 'MSDASQL' reported an error. Datasource

name not found and no default driver specified] OLD DB error trace

[OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned

0x80004005: ]

Sure could use a little help!

Thanks!!!!

Brad Feaker

Please reply to forum only! Thanks!

Seems like no one else knows jack about this either!|||

try this

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server

Use Microsoft OLE DB Provider for ODBC Drivers

Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3

And in Provider Options select:

level zero only
Non-transacted updates (something)
Allow InProcess

let me know if you still get an error

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Still getting the same error :-( BTW - I bookmarked your blog. You have some good stuff there. Thanks for the effort. I will keep trying - Almost no experience with MySQL...

Brad
Ex Nihilo, Nihil Fit|||

Do you have the MySQL Connector/ODBC 3.51 installed?

get it here-->http://dev.mysql.com/downloads/connector/odbc/3.51.html

also from a query window, does this work for you?

SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.99;DATABASE=bug_tracker;user=UserID;PASSWORD=Password;OPTION=3',
TestDB ) AS a

change TestDB to your table name

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Yes - I have the latest driver installed - the OPENROWSET query give me the same error message as the linked server. I can open the MySQL Query Browser, connect and run queries with no problem. I just cannot seem to get a linked server to work with SQL Server 2000. This is really frustrating. If I can't get this set up, I am going to have to use DTS and write some real heavy-duty scripts as these tables are going to feed a data warehouse in Analysis Services. It also makes me feel pretty stupid because I cannot get it to work. Back to the drawing board and thanks for your help.

Brad
Ex Nihilo, Nihil Fit|||

The driver is installed on the SQL server box right?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Got it fixed finally...Thanks for everyone's help!|||

I am having a similar problem with SSIS pulling data from MySQL

How did you get it fixed?

Thanks

-Raghu Raman

|||

Hi everybody, I have the same problem that has been treated here, but none of the solutions offered has been good for me.

Does anybody have any help respect to the problem with the linked server?

Thanks, blessings.

Cannot create an ODBC connection to SQl 2000.

Hi all,

I have SQL 2000 enterprise edition(no service pack) installed on a Windows Server 2003 machine. I can remote access into the server and run SQL but I cannot create an ODBC connection to this from my desktop. From some research it looks like installing service packs might correct this but here is the error:

--connection failed

--sqlstate '01000'

--sql server error 10061

--connection failed

--sqlstate 08001

--sql server error 17

--SQL server does not exist or access denied

Any help would be appreciated.

Verify whether the SQL Server is listening on a TCP port. If it's a default instance it is by default 1433, so you can use

netstat -an | findstr 1433

If not but the TCP protocol is turned on for the server (and set to port 1433) then upgrading to the latest service pack will most likely solve your problem.

|||

It'd better for you to upgrade SQL Server 2000 to latest service pack 4 from here:

http://support.microsoft.com/kb/290211#EOADAA

then make sure following things before you make connection:

1) SQL Service is running

2) If make remote connection, make sure named pipe or TCP was enabled and try Peter's suggestion if you want to connect through tcp.

HTH.

cannot create a stable subkey under a volatile parent key

When starting Microsoft SQL Server Management Studio after installing it from the SQL 2005 Enterprise disk, I get the error
cannot create a stable subkey under a volatile parent key
I click Continue and the get another warning box
Failed to open connection dialog. Unable to cast object of type 'Microsoft.SqlServer.Managemt.UI.ConnectionDlg.Personalization' to type 'Microsoft.SqlServer.Management.UI.ConnectionDlg.Personalization'.(ConnectionDlg)
Since I cannot use the old Enterprise Manger that we had for SQL 2000, I now have no way to manage the SQL via GUI. Anyone have any ideas for a fix for this?
Run setup first try repair option see if it works if not uninstall and reinstall. Another thing copy the contents of the CDs into your hardrive before running setup. Hope this helps.|||I'm getting the same thing. Happened during installation, also while using. bhopkins reported the same thing, said uninstaslling and reinstalling does not help. Has anyone seen any other info on this?|||I was getting the second message on startup. I removed the express version of Management Studio and corrected|||

Apparently there is a problem caused by SQL Books Online mixing up with Management Studio, as I reinstalled Management Studio to no avail.

Removing SQL Books Online was not enough in my specific case. I had to remove Management Studio as well. Ldubois, I noticed you did so as well. Did you have SQL Books Online installed also?

Just to be safe, I rebooted after removing SQL Books Online and Management Studio..

Then I reinstalled Management Studio & it works (the pesky startup exceptions went away).

There is another related problem, and that is choosing 'File -> Open -> File' from the Management Studio's menu bar & receiving a nice AV and app exit.

I have not reinstalled SQL Books Online, as likely that will resurface the problem.

For me, the procedure I just described resolved these seriously easy to find bugs that made it out of Microsoft.

Hope this helps. Happy New Year 2007!:))

-yoshiro (mark) aoki

|||

I know this is old. but here is a KB article with the solution.

http://support.microsoft.com/kb/922214

Cannot create a connection to data source

Windows 2003 Enterprise SP1, Sql Server 2005 June CTP.
I created a report on data from another server. I can run the report just
fine on the server with Reporting Services.
When I try to run the report from my computer I get the cannot create
connection error. I have full rights to the server and sa privilages to SQL
Server on the RS server and the remote server on which the data is located.
I have checked with the Network administrator and kerberos is enabled.
I really need to get this working and prove to management that RS will do
all we need in the way of a reporting tool.
Your help will be apprecited.
RoyWith the June CTP comes the Surface Area Configuration tool. Launch the
tool and makes sure that remote connections are enabled for your SQL
Server. By default, SQL is much tighter on security and remote connections
are off after initial install.
--
| Thread-Topic: Cannot create a connection to data source
| thread-index: AcWsvM7xh3SUrYS9QcCFLHwzj12wwg==| X-WBNR-Posting-Host: 65.118.63.88
| From: =?Utf-8?B?Um95?= <Roy@.discussions.microsoft.com>
| Subject: Cannot create a connection to data source
| Date: Mon, 29 Aug 2005 10:12:17 -0700
| Lines: 12
| Message-ID: <EB5CC7FC-F898-48AC-A8CD-E9B6668EF797@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51349
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Windows 2003 Enterprise SP1, Sql Server 2005 June CTP.
| I created a report on data from another server. I can run the report just
| fine on the server with Reporting Services.
| When I try to run the report from my computer I get the cannot create
| connection error. I have full rights to the server and sa privilages to
SQL
| Server on the RS server and the remote server on which the data is
located.
| I have checked with the Network administrator and kerberos is enabled.
| I really need to get this working and prove to management that RS will do
| all we need in the way of a reporting tool.
| Your help will be apprecited.
| Roy
|
||||Brad,
I did set the server to allow remote connections.
Roy
""Brad Syputa - MS"" wrote:
> With the June CTP comes the Surface Area Configuration tool. Launch the
> tool and makes sure that remote connections are enabled for your SQL
> Server. By default, SQL is much tighter on security and remote connections
> are off after initial install.
> --
> | Thread-Topic: Cannot create a connection to data source
> | thread-index: AcWsvM7xh3SUrYS9QcCFLHwzj12wwg==> | X-WBNR-Posting-Host: 65.118.63.88
> | From: =?Utf-8?B?Um95?= <Roy@.discussions.microsoft.com>
> | Subject: Cannot create a connection to data source
> | Date: Mon, 29 Aug 2005 10:12:17 -0700
> | Lines: 12
> | Message-ID: <EB5CC7FC-F898-48AC-A8CD-E9B6668EF797@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51349
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Windows 2003 Enterprise SP1, Sql Server 2005 June CTP.
> | I created a report on data from another server. I can run the report just
> | fine on the server with Reporting Services.
> | When I try to run the report from my computer I get the cannot create
> | connection error. I have full rights to the server and sa privilages to
> SQL
> | Server on the RS server and the remote server on which the data is
> located.
> | I have checked with the Network administrator and kerberos is enabled.
> | I really need to get this working and prove to management that RS will do
> | all we need in the way of a reporting tool.
> | Your help will be apprecited.
> | Roy
> |
> |
>

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
Jono
Jono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono
|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>
|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>

Cannot connect with Enterprise Manager

I am attempting to connect to a named instance of SS 2000 across the
Internet using Enterprise Manager. The machine running SS is behind a NAT
router. According to BOL I should be able to connect if I forward the TCP
port that SS is using (1061 in my case) and UDP port 1434 to the SS
machine. However, I cannot get this to work.
When I attempt to register the server in Enterprise Manager I use the WAN
IP address of the router as the server name. However, registration fails
with the message "SQL Server does not exist or access denied."
I cannot believe that I am the only person trying to do this. Can someone
tell me what I am missing?
Bill Todd
Bill Todd wrote:
> I am attempting to connect to a named instance of SS 2000 across the
> Internet using Enterprise Manager. The machine running SS is behind a
> NAT router. According to BOL I should be able to connect if I forward
> the TCP port that SS is using (1061 in my case) and UDP port 1434 to
> the SS machine. However, I cannot get this to work.
> When I attempt to register the server in Enterprise Manager I use the
> WAN IP address of the router as the server name. However,
> registration fails with the message "SQL Server does not exist or
> access denied."
> I cannot believe that I am the only person trying to do this. Can
> someone tell me what I am missing?
Do you have the Windows Filewall or another software firewall running on
the PC? If so, you may have to open up those ports on the PC.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for replying. SQL Server is running a a Win2k Server machine with
no software firewall. The only firewall is the NAT router.
Bill Todd
David Gugick wrote:

> Do you have the Windows Filewall or another software firewall running on
the PC? If so, you may have to open up those ports on the PC.
|||I should also mention that I have no problem connecting to SQL server
across the LAN that the server machine is on.
Bill Todd
|||On 2005-01-11, Bill Todd <no@.no.com> wrote:
> I should also mention that I have no problem connecting to SQL server
> across the LAN that the server machine is on.
>
If I got you right, you have a situation similair to this:
You have an SQL server in your local network, uses local IP adress pool
(192.168.x.x, or 10.x.x.x or similair), and if that machine wants to go out,
it uses default gateway set up on your network. I'm presuming that NAT is
running on that gateway. So, if you're connecting to host outside your
network, from any of the machines inside your network, the outside host will
'see' the gateway IP address (public one, not the private one). So, there is
no way you'd be able to connect to the inside host.
You can do what BOL suggests, do a port forwarding on your gateway. You
don't need UDP ports, TCP only matters. So, for a default instalation, where
SQL server 'listens' on port 1433, you'd have to choose a port on the
gateway, and forward all incomming connections from that port to the 1433
port on the machine where the SQL server is.
Or, you could use ssh, if you have access to the 'outside' ssh server to
establish ssh tunnels, and then set up port forwarding directly from your
machine to the SQL server in your network. I'd go with SSH, because of the
security level it offers. You'd be having all communication between your SQL
server and you as a client - encrtypted.
I'm using SSH to connect to my work network from any dialup location in the
world. And it works excellent.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Your understanding is exactly right except that the gateway on the network
is a simple Linksys cable/DSL router. The other difference is that I am
trying to connect to a named SS 2000 installation which, according to
Enterprise Manager, uses port 1061.
I have, I think, done exactly what you suggest. I have forwarded TCP port
1061 on the Internet side of the router to port 1061 on the machine
running SQL server. It does not work.
I have no idea how to implement SSH and security is not an issue in this
case since the remote adminstration will be temporary and the only data
exposed will be test data.
Clearly I am missing something. Is there anything else you can think of
that I should check or set? This should be easy.
Bill Todd
Mario Splivalo wrote:

> On 2005-01-11, Bill Todd <no@.no.com> wrote:
> If I got you right, you have a situation similair to this:
> You have an SQL server in your local network, uses local IP adress pool
> (192.168.x.x, or 10.x.x.x or similair), and if that machine wants to go
out,
> it uses default gateway set up on your network. I'm presuming that NAT is
> running on that gateway. So, if you're connecting to host outside your
> network, from any of the machines inside your network, the outside host
will
> 'see' the gateway IP address (public one, not the private one). So,
there is
> no way you'd be able to connect to the inside host.
> You can do what BOL suggests, do a port forwarding on your gateway. You
> don't need UDP ports, TCP only matters. So, for a default instalation,
where
> SQL server 'listens' on port 1433, you'd have to choose a port on the
> gateway, and forward all incomming connections from that port to the 1433
> port on the machine where the SQL server is.
> Or, you could use ssh, if you have access to the 'outside' ssh server to
> establish ssh tunnels, and then set up port forwarding directly from your
> machine to the SQL server in your network. I'd go with SSH, because of
the
> security level it offers. You'd be having all communication between your
SQL
> server and you as a client - encrtypted.
> I'm using SSH to connect to my work network from any dialup location in
the
> world. And it works excellent.
> Mike
|||More information. The problem is connecting to a named instance. SS 2000
is installed as a named instance because the server is also running SS 7.
I _can_ connect through the NAT router to SQL Server 7 using the steps you
described. So, the question is, what do I have to do differently to
connect to the named instance of SS 2000 that is litening on port 1061.
Port 1061 is forwarded exactly like 1433.
I suspect that I need to change some setting on the client machine to get
Enterprise Manager to connect on port 1061 instead of 1433. Any
suggestions?
Bill Todd
|||Bill Todd wrote:
> More information. The problem is connecting to a named instance. SS
> 2000 is installed as a named instance because the server is also
> running SS 7. I _can_ connect through the NAT router to SQL Server 7
> using the steps you described. So, the question is, what do I have to
> do differently to connect to the named instance of SS 2000 that is
> litening on port 1061. Port 1061 is forwarded exactly like 1433.
> I suspect that I need to change some setting on the client machine to
> get Enterprise Manager to connect on port 1061 instead of 1433. Any
> suggestions?
Go into the SQL Server Server Network Utility on the server and assign
the named instance a hard-coded port. With named instances, SQL Server
pulls from a pool and there is no guarantee you'll get the same port
every time the instance is started. You also need to specify the port
when connecting by specifying the SERVER,PORT (e.g. MyServer,1434). You
could also assign an alias from the remote PC to the server to avoid
having to type the server\instance long name and port # by assigning the
instance a simple name, TCP-IP as the protocol, and put in the port
number. Then you use the alias from the remote PC when connecting.
Just make sure you have port forwarding turned on for the assigned TCP
port number on the router.
David Gugick
Imceda Software
www.imceda.com
|||The problem was the comma between the server IP address and the port
number. I found an article on MSN that showed a space between the server
and port. I also tried a colon.
The devil truly is in the details. Thanks.
Bill Todd

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
JonoJono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono|||No, just hangs.
>--Original Message--
>Jono
>Any error message?
>|||No, just hangs.
>--Original Message--
>Jono
>Any error message?
>

Cannot connect with Enterprise Manager

Hi All
I can connect to a server with Query Analyzer but not with
Enterprise manager (from a lot of clients so problem is
not client based). I used to be able to connect using
both, then all of a sudden it stopped. I rebooted the
server and Enterprise Manager connectivity worked for a
few hours and then stopped again (QA access still works!).
Can anyone think of any reason why this may be so?
Thanks
JonoJono
Any error message?
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:ad9901c488df$5d5c9690$a501280a@.phx.gbl...
> Hi All
> I can connect to a server with Query Analyzer but not with
> Enterprise manager (from a lot of clients so problem is
> not client based). I used to be able to connect using
> both, then all of a sudden it stopped. I rebooted the
> server and Enterprise Manager connectivity worked for a
> few hours and then stopped again (QA access still works!).
> Can anyone think of any reason why this may be so?
> Thanks
> Jono|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>|||No, just hangs.

>--Original Message--
>Jono
>Any error message?
>