|
Hi,
I my Self solved the problem via this strategy
Change the bellow code :
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>@ModuleSettings</code> + ''','
END
With :
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))</code> + ''','
End
conclusion :
you should replace :
@ModuleSettings
with :
replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))
|
|
|
|
|
I believe I've found a very unexpected bug in SQLBulkCopy that took me a while to track down. My test program reads in records from a CSV file with exactly 1 column which is defined as a "numeric(19,5)" for statistical uploads.
Condition 1:
If the input file has 25 rows with the only value being the digit zero, followed by a number with a decimal (like '1.23456'), the data is imported perfectly.
Condition 2:
If the input file has 26 rows with the only value being the digit zero (or any non-decimal number), followed by a number with a decimal (like '1.23456'), the data is imported and the last (and any subsequent) rows have the field imported with truncated decimal digits (like '1.00000').
Condition 3:
If the input file has 26 rows and the first row is '0.0', all of the rows are imported perfectly.
I've checked the table/field definition for Condition 2 and it's the same as 1 and 3 so there's no manipulation of the field type (as far as I can tell).
Is this a bug or am I missing something?
|
|
|
|
|
You say the test file has "1 column".
Then you say that each row has "the digit zero, followed by a number with a decimal (like '1.23456'),"
Does that mean that it has two columns or that the single number is '01.23456'?
Also you should post the following
1. What database version you are using.
2. Example data
3. The relevant code.
4. The database schema you using.
|
|
|
|
|
Re: the numbers
The test only had a single column although I first noticed the behavior with 100 columns. Each row only has 1 value (zero) until the 27th row which is the decimal number, i.e.
0
0
0
...
0
1.23456
Re: other questions
1) The destination was SQL Server 2008 and the SQLBulkCopy came from the .Net 4.5 library. This brings up the question "Is the SQLBulkCopy the culprit or the destination SQL Server instance?"
2) Example data: see above
3) Relevant code
using (SqlConnection connection = new SqlConnection(strConn))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = strSQL_table_name;
bulkCopy.BatchSize = GetBulkCopySize();
bulkCopy.BulkCopyTimeout = 2000;
bulkCopy.NotifyAfter = GetNotifyAfter();
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
try
{
bulkCopy.WriteToServer(results);
}
catch (Exception ex)
{
string error = ex.Message;
MessageBox.Show("Error(CSV_To_SQL-a): " + ex.Message);
return;
}
4) single column in a table, name="BigNumeric", data type="numeric(18,5)
|
|
|
|
|
Where is the reader ('results') close?
|
|
|
|
|
I presume results is a datatable, have you inspected the content of the table before BC?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
As it turns out, the process still isn't working. The .Clone() didn't work so I tried the .FillSchema() method and it's failing too.
adapter.FillSchema(table, SchemaType.Source);
table.Columns[2].DataType = typeof (Int32);
adapter.Fill(table);
The column still gets cast as an Int32 from FillSchema(), then I try to change it to typeof(Decimal) followed by Fill() and the decimal points are still truncated in the resulting DataTable. I even tried to set the DataType to String but that didn't work either.
Next I've discovered another practically hidden Microsoft "feature" called TypeGuessRows (and IMEX=1) which supposedly can be modified in the registry or the OleDbConnection string. I haven't gotten it to work yet but at least I know that others have seen this behavior too.
|
|
|
|
|
Keep your column in an numeric format OR convert it manually in numeric format so it dont create destruction.
|
|
|
|
|
[NM] this[^] and now it works
I would like to connect to my SQL Server 2005 instance running on my Windows Server 2003 at home from my office. I'v never tried this before, so bear with me...
First, I follow all the instructions here[^]
I then open SSMS an another PC and attempt to connect to my IP, which fails with
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
Do I need to do something at my router page to allow access to the server? If so, what?
What else could be wrong??
If it's not broken, fix it until it is
modified 11-Oct-12 17:44pm.
|
|
|
|
|
You'll have to ensure that all the right ports you need are open in the router and make sure that the SQL Server is configured to allow remote connections.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
Do keep in mind that there are people out there who are running portscans in their free time. You might find strange things happening in your database if you hook it up to the internet without some precautions;
Set the thing to "Windows Authentication" and turn the SA-account of. There could be a checklist on Google, didn't check that - but you might want to 
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
When your server is behind the router, then it has a "private" IP address, hasn't it? Then things get complicated. You could create a VPN, such that home and office are virtually in the same network (same range of IP addresses), or you have to configure port forwarding on your router. I cannot give details on how to do that...
|
|
|
|
|
Dear Readers,
Above question is a "small" question about something I'm at a dead end at the moment. How do I design a database to store all kind of addresses from all over the world? All countries have different address specifications. Some countries have more address fields than others, and/or of different structure.
This is just a fraction of a larger problem I'm facing. I need to redesign a database that is country specific to a global design. Addresses might be the most easy thing to globalize.
How do you design/model such a "global" database?
Thanks in advance,
Rémy Samulski
|
|
|
|
|
Cool problem.
The answer is to normalize properly.
One table for countries. One for address types, such as delivery addresses or box addresses and so on, per country.
One for address parts with one row per street housenumber, town, zip and so on.
Another one for holding the combination of addressparts and addresstypes, with ordering, or rather placement on the letter.
You'll need one table to hold the addresses as an entity, with an address type reference, not the actual data.
This table could of course be combined with a person or company if that's fitting, but I woud split it as a company might have more than one address.
And lastly one for holding the actual data with one row per address_type_part and address.
Make a model of this and check if I've forgotten anything.
modified 11-Oct-12 6:18am.
|
|
|
|
|
Jörgen Andersson wrote: street, housenumber
Street and housenumber are a single fact, one entity. You will not use them as separate facts in the database, and it makes little sense to add an extra field for this.
On topic; there are houses without a number on Curacao
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Ok, nitpick  , I'll remove that comma, and I'll remove the name and surname too as they don't belong in the address.
Houses without numbers are quite common also in the UK and Sweden.
|
|
|
|
|
Beep!!! Wrong answer
Not all addresses have a number, for example the UK all of these are quite valid
(House Number + Street - probably the most common)
123 Any Street
(House Name followed by street on the next line of address)
Mayflower Cottage
Some Street
(House Name followed by number + street on the next line of address)
Mayflower Cottage
123 Some Street
(Flat number + Block Name followed by street on the second line)
123 Mandela House
Some Street
(Office addresses are often Office number + Block Name followed by Street number + Street name)
7 Imperial House
123 Any Street
|
|
|
|
|
Well there are of course different ways. One way is to create a dynamic database:
Create a adress-table with fields: DataId INT, FieldName VARCHAR(X), FieldValue VARCHAR(Y) or BINARY
Index is (DataId, FieldName)
Instead of storing "New York" in a column "CITY" you store "City" in "FieldName" and "New York" in FieldValue".
You can also define Field-Groups: like "Adress" which my occur more than one time, by adding another column Line INT, that allows to store an Address 1, Adress 2, ..., Adress n and of course a Phone 1, Phone 2, ..., Phone n
If n isn't large enough you can even store x values
This way ain't efficient for tables with a lot of complex queries, but I think you don't need to have comples queries here, so the responde-time should be good enough in this case.
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
LiQuick wrote: How do you design/model such a "global" database?
A varchar for the name of the recipient, a varchar for both address-lines, and a designation for a country. That's as far as I'd normalize the structure - you will not be using the zipcodes to filter on, and this way you can support very exotic addresses. Even those where they include an extra line that says "deliver on second floor".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Won't work in Japan where they (to my knowledge) write the address in the "wrong" order. Starting with district, then town, zip, subarea, street, streetnumber and name.
I'm writing this from memory though, so you have to give me some slack as it's probably wrong in some part(s).
UK also needs up to six lines in the address.
I have to admit though that we're using your structure in our internal systems, but with extra info for zip and town as we actually do filter on those fields occasionally.
We only work inside our own country.
|
|
|
|
|
Jörgen Andersson wrote: UK also needs up to six lines in the address.
Hence the suggestion to treat it as a single entity and provide a varchar-field.
Jörgen Andersson wrote: I have to admit though that we're using your structure in our internal systems, but with extra info for zip and town as we actually do filter on those fields occasionally.
If you treat the address as a "complete" fact, and add the zip-code as "extra info", then there's little problems; it's merely a bit overhead to have the zipcode twice.
Normalization is hard, and one does not simply cut a piece of information into what looks as being 'atomic blocks'; if normalization would work that way, you'd need 7 fields for each date (Day, month, year, hours, minutes, seconds, timezone), and all the months would be in a single table
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: if normalization would work that way, you'd need 7 fields for each date (Day,
month, year, hours, minutes, seconds, timezone),
I've seen that done, minus the timezone. That was a freaking moment.
|
|
|
|
|
Jörgen Andersson wrote: I've seen that done, minus the timezone. That was a freaking moment.
I imagine! I wanted a far-fetched example, so encountering something like that in the wild is a bit of a wtf-moment.
Codd did not say 'eliminate every repeating group', as that would imply that we cannot even store the day number '12'. It would repeat for each month, after all. Putting the day-numbers in a separate table and linking to them using a Guid would be an option, but also a bit insane.
The 'worst' modeling decision that I remember are storing everything as a varchar, including an array of bits; actually stored as a "11101010001100"-string. When I asked who wrote it (same tone as usual), my boss-for-the-moment responded saying it was an optimization 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Bit array as varchar scores quite high on the list of wtf moments
The really stupid part of storing time in six different fields is that they are all just different representations of the same thing (time) and you can calculate one from another.
If there wasn't a date type available I'd simply store seconds in a number field, which coincidentally is what happens behind the doors in a database.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
assumed