Error using SQL Server Express

[aaron_thomas]

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

[Former Member · Accepted solution]

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

[aaron_thomas · Accepted solution]

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.

[Former Member · Accepted solution]

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!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

Thank you!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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