Question Re: Error using SQL Server Express in Technology Q&A

Error using SQL Server Express

aaron_thomas — Fri, 16 Dec 2005 15:21:18 GMT

I have MDM Server code running locally on my desktop for development. I was using local MSDE database as my RBMS. Everything was working fine.

I uninstalled MSDE and installed SQL Server 2005 Express. Now when using the MDM Console to try and Unarchive a repository connecting to the SQL Server Express database, I'm receiving errors. I can establishing a connection to database and the repository gets created to a certain point and then fails with error message stating "Unable to modify database".

I'm using SQL Server Express 'sa' account to connect with so I don't feel like it is a permission problem.

Below is the MDM Server log entries:

744 2005/12/16 14:39:01.639 GMT Creating Repository: Demo, on DBMS Server: localhost\SQLEXPRESS

744 2005/12/16 14:39:14.697 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT TableId FROM A2i_CM_Tables WHERE TableType=0

744 2005/12/16 14:39:14.697 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT DataTableId FROM A2i_CM_Data_Tables WHERE DataTableType=6

744 2005/12/16 14:39:16.840 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT DataTableId FROM A2i_CM_Data_Tables WHERE DataTableType=6

744 2005/12/16 14:39:18.743 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT DataTableId FROM A2i_CM_Data_Tables WHERE DataTableType=6

744 2005/12/16 14:39:36.098 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT DataTableId FROM A2i_CM_Data_Tables WHERE DataTableType=6

744 2005/12/16 14:39:38.191 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: SELECT DataTableId FROM A2i_CM_Data_Tables WHERE DataTableType=6

744 2005/12/16 14:39:56.558 GMT Init Database Tag: DemoLOCALHOST\SQLEXPRESSMSQL_4_20_4_3

744 2005/12/16 14:39:58.340 GMT Repository Demo creation completed OK

744 2005/12/16 14:40:19.370 GMT UnArchive starting to server: localhost\SQLEXPRESS, target Repository: Demo, archivePath: C:\PROGRA_1\SAPMDM1.5\Server\Archives\InQuera Demo_051214.a2a

744 2005/12/16 14:40:25.129 GMT OLEDB error on localhost\SQLEXPRESS/Demo_m000 from statement: DROP TABLE user_token

744 2005/12/16 14:40:25.129 GMT ErrorRecord->HResult/NativeErr: 0x80040e37/0

Description: Cannot drop the table 'user_token', because it does not exist or you do not have permission.

Source/File/Line: Microsoft OLE DB Provider for SQL Server/.\OLEDB_DBConnection.cpp/1668

744 2005/12/16 14:40:25.129 GMT UnArchive to server: localhost\SQLEXPRESS, target Repository: Demo, archivePath: C:\PROGRA_1\SAPMDM1.5\Server\Archives\InQuera Demo_051214.a2a ended in error

Re: Error using SQL Server Express

Former Member — Fri, 16 Dec 2005 15:53:42 GMT

Hi Aaron,

have you set the User Security Settings to "SQL Server and Windows" authentication?

You can find it in the SQL Enterprise Manager under:

Properties --> Security

Greetings

Re: Error using SQL Server Express

aaron_thomas — Fri, 16 Dec 2005 16:07:31 GMT

Yes, this option is selected. In the SQL Manager I can even see that the databases were created, however there are no tables created in the databases.

Re: Error using SQL Server Express

Former Member — Wed, 24 May 2006 22:52:54 GMT

Anyone has a solution for this already?

I'm having exactly the same problem, except that I'm using SQL Server 2005 (Instead of SQL Server Express).

Thanks in advance!

Re: Error using SQL Server Express

Former Member — Wed, 24 May 2006 23:01:36 GMT

Hi,

Could you please specify the exact error message you got.

Is it like "Error initializing the database" or

"Could not connect to DBMS" etc.,

Thanks and Regards

Subbu

Re: Error using SQL Server Express

Former Member — Wed, 24 May 2006 23:12:44 GMT

Hi Subbu: The error text is: "Unable to modify database" so when I go in to the MDM server logs I see the same error log that Aaron gets.

Of course I am using the "Security Setting: SQL Server and Windows".

When I go to the MS SQL Server 2005 Management Studio, I don't see any table named "user_token".

Thank You!

Re: Error using SQL Server Express

Former Member — Wed, 24 May 2006 23:22:10 GMT

Hi jorge,

Did you check in Microsoft website are there any recent patches for MSSQL 2005. I feel may be that will fix. Its a guess.

Let me also try it and see.

Thanks and Regards

Subbu

Re: Error using SQL Server Express

Former Member — Wed, 24 May 2006 23:55:23 GMT

Bad news Subbu, I've just installed SP1 but problem persists.

Thank you!

Re: Error using SQL Server Express

Former Member — Thu, 25 May 2006 00:00:04 GMT

Jorge Tello,

Please check the Product Availability Matrix in Service Market Place. I think SQL Server 2005 support is released only this month. You may have to upgrade your MDM to the latest available version.

Regards,

Rajani Kumar

Re: Error using SQL Server Express

Former Member — Thu, 25 May 2006 00:34:19 GMT

Hello Rajani:

I think you are right.

As a matter of fact, I can see that SQL 2005 is only supported from 08.05.2006, and only for a 32 bit OS.

We are using x64, which is not supported for using with SQL 2005, only SQL 2000.

Thank you for your help.

Regards,

Jorge Tello.

Re: Error using SQL Server Express

Former Member — Thu, 25 May 2006 02:03:56 GMT

Hi Jorge,

I had the same problem now.

I have SQL Server Personal Edition 8.0 SP4.

This is how I fixed. Apart from the Product Availability Matrix. Try this one and see whether it works for you or not.

1. Right Click on the Server->DBMS Settings->Select the DBMS Server,Username, and Password.

2. In the next step it will show you the log file path and data file path.

<b>Make sure that directory structure exists.</b>

In my case

teh value was C:\Program files\Microsoft SQL Server\MSSQL\Data\

But this does not exist. It was actually there in D:\

I did chage this setting and it worked fine for me.

Probably if you are doing it(DBMS Settings) for the first time , make sure you create the same directory structure or leave it default.

Just give it a try.

Thanks and Regards

Subbu