Msdeploy dbfullsql data export - invalid decimal point

Question

User674935837 posted

I have this schema:

-- Script Date: 04-03-2011 21:03  - Generated by ExportSqlCe version 3.5.0.9
CREATE TABLE [Order Details] (
  [Order ID] int NOT NULL
, [Product ID] int NOT NULL
, [Unit Price] money NOT NULL
, [Quantity] smallint NOT NULL
, [Discount] real NOT NULL
);
GO

 How do i get proper decimal number (with no comma as decimal separator)

(Running Windows 7 English, with danish regional settings) 

 I use the following command line:

 msdeploy -verb:sync -source:dbFullSql="Data Source=C:\data\sqlce\test\nw40.sdf",sqlCe=true,NoCollation=true,SchemaQualify=false -dest:dbFullSql="c:\data\scriptnw.sql"

And get this (Northwind) (notice to many commas in second INSERT):

INSERT INTO [Order Details]([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10000,17,27,4,0)

GO

INSERT INTO [Order Details]([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10001,25,9,8,30,0,15)

GO

Thanks in advance!

Answer 1

User674935837 posted

Anyone??

Answer 2

User-1672167363 posted

Hi,

Maybe this http://erikej.blogspot.com/2011/03/migrate-sql-server-compact-database-to.html 

General suggestion: You might consider getting the SQL compact Books from the Microsoft Download Center for reference use.

Martin

 

Answer 3

User674935837 posted

Thanks Martin, but I am the author of that blog post.

Answer 4

User-1672167363 posted

Ok,

My Bad. Pardon the mistake.

Maybe delimter problem ?

HTH

Martin

 

Answer 5

User674935837 posted

No, SQL Server always uses . as delimiter on decimal numbers. I am reporting a bug in MSDeploy!

Answer 6

User-1672167363 posted

Hi,

MS Deploy  validation section maybe where your sql commands are not working.

HTH

Martin

 

 

 

Answer 7

User-1672167363 posted

Hi,

I don't think that this is a bug yet.

I use plain "Jane" SQL Statements for most things.

Below is the information for the NorthWind database on my system.

This is the Northwind CE40 version information.

-- Script Date: 08/04/2011 2:19 PM  - Generated by ExportSqlCe version 3.5.0.8
-- Database information:
-- Locale Identifier: 1033
-- Encryption Mode:
-- Case Sensitive: False
-- Database: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Samples\Northwind.sdf
-- ServerVersion: 4.0.8482.1
-- DatabaseSize: 1466368
-- Created: 02/23/2011 2:20 PM

This is the Schema for Orders

CREATE TABLE [Order Details] (
  [Order ID] int NOT NULL
, [Product ID] int NOT NULL
, [Unit Price] money NOT NULL
, [Quantity] smallint NOT NULL
, [Discount] real NOT NULL
);

These are the Data  inserts in to Orders:

This does look correct and operates in SQL Server CE 40.

--start of inserts

INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10030,43,32.2,6,0.05);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10031,71,15,10,0.25);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10031,72,24.3,30,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10032,13,4,20,0.25);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10032,24,3.1,15,0.25);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10033,72,24.3,35,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10034,6,17,18,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10034,26,21,21,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10035,10,21,50,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10035,11,14.7,35,0.15);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10035,34,9,30,0.15);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10035,41,6.7,40,0);
GO
INSERT INTO [Order Details] ([Order ID],[Product ID],[Unit Price],[Quantity],[Discount]) VALUES (10036,51,37,1,0);

--end of inserts

After seeing the orginal schema for Orders Table 

 it looks like the inserts might not have been not formatted correctly before export ?

HTH

Martin

 

 

 

Answer 8

User-1672167363 posted

Hi,

I have posted the Schema for Orders Table  from my northwind  CE 40 database.

Could you post the orginal Schema for the Orders Table with the inserts ?

It is hard to tell if the problem might be in the orginal insert statements or after export and MS Deploy.

Martin

 

 

Answer 9

User674935837 posted
Yes, it works for you, as you are using English REgional Settings, but try to change to Danish regional settings, and run msdeploy again! (As noted in my blog post) - then the msdeply export file contains wrongly formatted decimal numbers.

Answer 10

User-1672167363 posted

OK,

Just trying to confirm what is happening sorry.

 then maybe post the information at Microsoft Connect or Email the IIS Team.

Martin

 

Answer 11

User674935837 posted

I assumed that this forum was monitored by the IIS product team !?

Answer 12

User-1672167363 posted

Hi @ erikej,

Wow put me on the spot this is a "Best Effort" reponse.

If you check the  Web Platform Forum http://forums.iis.net/default.aspx?GroupID=52 which

has several sub Forums an example.

The list of Top Answers by Group you have a partial answer.

The IIS Team has Members who are Project Managers Developers Interns Testers and also contribute to Forum threads.

I can not speak for the IIS team. I can only explain by what I have seen.

What I have observed is Monitoring the Forums while doing the assigned jobs is a difficult balance for time and energy.

I am here just trying to help users fix problems that is why my attempt to help with your thread. Sorry was not able to help.

I do have to balance time to Monitor the Forums and help users

        along with Classes Administator Development jobs with (minimal sleep). 

So there is no easy answer and nothing can be assumed. 

Martin

 

Answer 13

User-1672167363 posted

Hi,

Could you look this

 http://blogs.msdn.com/b/webdevtools/archive/2010/08/09/an-example-of-packaging-web-application-containing-database-upgrade-sql-file.aspx 

Blog post. It looks like the Example would take care of the Collation issues ?

Martin