Need Help: With Dutch Regional Options, MS SQL Server BCP Incorrectly Delivers Values
Aug 27, 2004
Hi all,
With Windows regional options are set to Dutch/Netherlands (or any other country using comma as the decimal symbol), MS SQL Server BCP Delivers incorrect figures to the target table.
The source table is a text file containing commas as the decimal character (As an example, in the text file 100.000 is represented as 100,000).
The target table is a Sql Server database table, with the column in question defined as a FLOAT datatype.
When BCP is run to deliver the data from the text file to Sql Server,
all figures are multiplied by 1000 because the input file for BCP contains a comma as the decimal symbol.
Thus, instead of the data being delivered with a value of 100, it ends up having a value of 100000
Is there anyone out there who is aware of any command line parameters that can be added to the bcp command in order to avoid this ? Note, the field in question is defined as SQLCHAR in the BCP format file.
Thanks for any help anyone may be able to offer.
View 2 Replies
ADVERTISEMENT
Jul 23, 2005
For some reason a stored procedure which I have created is incorrectlysaving the date to the table. It seems the day and month are beingswapped around e.g. a date which should be the 12th April (12/04/2005)is saving as the 4th December (04/12/2005).The parameter used in the stored procedure comes from a VB6 app, Iamended this so the format was "yyyymmdd hh:mm:ss". The full line in VBbeing,Parameters.Append .CreateParameter("date_of_call", adChar, , 17,Format(firstCallDateTime, "yyyymmdd hh:mm:ss"))When I run my VB app it works fine, the syntax in the stored procedureis,CREATE PROCEDURE dbo.spUpdValues@data_id int,@date_of_call datetimeasupdate dataSET date_of_call = CONVERT(char, @date_of_call, 101)where data_id=@data_idIs it because the convert format is using an american date format ? Ican't see why as I can't reproduce this error using my own PC as thedate saves correctly, I can also confirm it's not happening to everybodywho uses the app. If it is happening for specifc users then what couldbe the cause. I've checked Regional Settings and all seems fine there.Any ideas on what could be doing this as I'm struggling to investigateany further.To debug I ran the stored procedure direct, manually inputting thevariable - again no problem. Also, the following SQL statment shows noproblem...declare @date_of_call datetimeset @date_of_call = '20041101 08:30:00'select CONVERT(char, @date_of_call, 101)select CONVERT(char, @date_of_call, 106)------------------------------11/01/2004(1 row(s) affected)------------------------------01 Nov 2004(1 row(s) affected)Any help would be much appreciated.*** Sent via Developersdex http://www.developersdex.com ***
View 10 Replies
View Related
Feb 15, 2005
Hello,
Does the SOUNDEX function only apply to english (surnames) or can it be used for other languages e.g. Dutch.
Well, it can be used ofc. but does it produce meaningful output ?
Thanks,
Rick
View 3 Replies
View Related
Oct 18, 2007
Hi
We have a bunch of servers running server 2000 & 2003 along with many sql server (versions 2000 & 2005) databases in a production environment pulling transactions then doing warehousing & reporting.
An audit has shown up 1 production server using English_US 'mmddyyyy' system date formats, all others being English_Australian 'ddmmyyyy'.
Is it safe to simply change the regional settings on this English_US server to English_Australian or will it mess up the data in our SQL 2005 databases? I've not been able to get a definitive answer from anyone yet!
many thanks in advance; GaryP
View 1 Replies
View Related
Jul 23, 2005
I have a .NET application with a sql server database backend. Ourclient has been having problems lately with data being returned fromthe database with wrong data. We have error logging in our storedprocedures and even this is reporting wrong. In every stored procedurewe have the following:IF @@Error <> 0BEGINEXEC dbo.HandleError@ErrorNum = @@Error,@ProcID = @@PROCIDENDThen, HandleError looks like:CREATE PROCEDURE dbo.HandleError@ErrorNum int,@ProcID intAS/************************************************** ****************** PROCEDURE: HandleError* PURPOSE: Handles logging an error* CALLED FM: Other stored procedures************************************************** ******************/SET NOCOUNT ONDECLARE @UserID intDECLARE @Host varchar(50)DECLARE @Len intDECLARE @SPName VARCHAR(100)DECLARE @ErrorMsg varchar(500)DECLARE @ErrorTrace varchar(4000)-- Get our host nameSET @Host = HOST_NAME()SET @Len = LEN(@Host) - CHARINDEX(':', @Host)IF @Len > 0 AND CHARINDEX(':', @Host) > 0SET @UserID = RIGHT(@Host, LEN(@Host) - CHARINDEX(':', @Host))ELSESET @UserID = NULLSET @SPName = OBJECT_NAME(@ProcID)SET @ErrorMsg = 'SQL Error: ' + @SPNameSET @ErrorTrace = 'Error: '+ CAST(@ErrorNum AS VARCHAR(50))EXEC dbo.InsertApplicationErrors@ErrorMessage = @ErrorMsg,@ExceptionMessage = '',@ErrorStackTrace = @ErrorTrace,@UserID = @UserID,@HostID = @Host,@Logfile = '';SET NOCOUNT OFFGOInsertApplicationErrors inserts a log into a table we have that wemonitor. We got an error back in the ErrorStackTrace column with'Error: 0'. That shouldn't happen, as the only time we log error is ifit is not 0.Does anyone have any ideas on what might be wrong?Steve
View 7 Replies
View Related
Mar 19, 2008
Hello All:I tried many times to install SQLserver 2005 on my My PC (VISTA Ultimate version). I always find configuration tools and working properly and can start any services and stop but I searched everywhereto find sql server management studio (SSMS) to connect but I don't find it ..... can someone help me because I've uninstalled and re-installed it many times but not found........
View 13 Replies
View Related
Apr 5, 2007
Could someone from the SQL Server Express team confirm that SQL Server Express SP2 w/Advanced Services shows up as "SQL Server 2005" in the installed programs list? The "Express" portion seems to be missing. And how would one tell the difference between the Standard Edition and the Express Edition based on this?
Thanks.
View 1 Replies
View Related
Jul 30, 2007
I am trying to restore a 2005 backup to a 2005 database on another server. This has worked for me before. I have tried to take the backup 5 times now and each time I get the error media family incorrectly formed. Since I have successfully backed up and restored before between these same two databases I do not understand what is wrong now.
I found other questions with this error; however, they were taking a 2000 backup to a 2005 database. I am using a backup of a 2005 database to a 2005 restore.
How should I begin to debug this problem?
Thanks
View 3 Replies
View Related
Aug 8, 2007
I have a rather complicated report with lots and lots of textbox and line controls. When I preview the report on the Report Server the layout is all kinds of skewed and all kinds of stuff is out of place. But when I export the report to PDF or TIFF, the output reverts to it's proper form. Why is it doing this? Is there anything that I can do to not make it so ugly when previewing?
View 2 Replies
View Related
Feb 10, 2006
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition):
"Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again."
When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives?
Thanks,
Scott
View 36 Replies
View Related
Jan 30, 2007
Hello,
I am running reporting server on a "english" server with dutch regional settings.
My client and the client of the users are also a "english" client with dutch regional settings.
A dutch number should be formatted like 1.234.567,89
Unfortenately when I run a report I get the number back (non formated) like 1234567.89
How do I get it in the dutch format ?
With regards,
Constantijn Enders
View 2 Replies
View Related
Oct 19, 2007
Hello,
I have a problem with date parameters in my Visual Studio reports. I have set parameters €œStart date€? and €œend date€? as €œdate time€? fields, and the report works fine on my computer, but as soon as I put it in report server and start opening from other computers in the costumers place, the date stays right only about half of the times, I gues it depends on regional settings of the computer that opens the report. For example date 02/12/2007 changes to 12/02/2007 that in effect changes the month from February to December, and if the day is 13 or higher I do not get any result at all.
How should I set the date parameters that they could be correctly open and wieved from any computer no matter what regional settings are?
View 3 Replies
View Related
Jan 30, 2008
Hi All,
I have been playing around with SSIS for a bit, and i am running into the following problem.
I am trying to import 3 worksheets from an excel file.
The import must be performed on several servers.
I created a config file for this which contains the path to the excel file and the connectionstring, and the LocaleID's of the different tasks.
I have the following tasks :
1. preparation SQL task ( this calls a few sql statements which cleanup certain import tables )
2. data flow task ( this the actual import from the excel file to several tables, this what goes wrong )
3. execute SQL task ( this calls a stored proc which does some filtering the imported data into other tables ).
point 2 is giving problems.
Point 2 will only work when i change my computers regional settings to English.
If i have my settings set to Dutch, it won;t work. Even specifying the english localeID's seem to have no effect.
The problem is a decimal field in the database, and the difference in decimal separator. When i set my regional settings to English the values in the excel are notated like 74.54 which works. And when i have the dutch regional settings the same value in excel is displayed like 74,54. I haven't got a clue how to solve this.
Is there a way to tell the SSIS to not use the regional settings, but use the settings specified in the config file ?
And if this is possible how can i check if the correct settings are used ?
View 1 Replies
View Related
Aug 12, 2007
Hi,
I have a date field in my report which takes the current date as the value. My default regional settings is en-US. If I change my regional settings to Germany, on executing the report, I get rsReportParameterTypeMismatch for the Date parameter.
Any help in this regard is welcome.
Thanks.
View 2 Replies
View Related
Feb 7, 2008
Here's the situation:
I have a ssis package which receives 3 dates as input parameters (3 datetime variables), executes different data flows and at the end inserts (among some other values) these 3 dates into a custom log table.
The problem comes while inserting the values into a log table. I added an Execute SQL Task that calls a stored procedure which inserts a record into a log table (sqlStatement exec dbo.InsertIntoLog date1=?, date2=?, date3=?).
In Parameter mapping section I set parameters = variables that hold the datetime values. The problem is that the dates are inserted into the log table in American format (mm/dd/yyyy, that's the server setting), my dates are in the european format...
Any ideas how to avoid it? Is there a way to write an expression instead of the sqlStatement in which I'd do some datepart-ing?
View 25 Replies
View Related
Dec 23, 2006
I tried Data Mining Add-Ins for Office 2007 - CTP December 2006.
Test settings: Windows XP SP2 english with Italian regional settings, Office 2007 english (RTM), SQL Server 2005 Developer (with SP2 CTP Dec06) and Data Mining Add-ins for Office 2007 (CTP Dec06).
If I keep regional settings in Italian, I get error like this:
Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
If I change regional settings to English, the Add-in works.
I found this description as the possible cause of the problem: http://msdn2.microsoft.com/en-us/library/ms178780(vs.80).aspx - if this is the issue, it would be necessary to change the ExcelLocale1033Attribute on the component.
Is there another workaround other than to install the Office 2007 MUI?
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
View 10 Replies
View Related
Dec 11, 2007
How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.
View 1 Replies
View Related
Jan 2, 2008
Hi all,
I have an odd issue. I have a query that returns one row from SQL Server. The value for one column (payment) in this row is 5.00. There are no other rows returned, only one row. However, when I do a sum on payment it returns 195! I dont know where its getting this from, especially since I can see all the rows by removing the SUM() and there only is one row. Here is the query:Select v_payments.tripid, Instransporttotal, lastName, firstName, tripDate, v_payments.payment,credit, translation, inscoid, v_payments.checkid
from v_billbuckets
left join v_patient on v_billbuckets.patientID = v_patient.ssnleft join v_payments on v_billbuckets.tripid = v_payments.tripid
left join v_InsCompany on v_billbuckets.inscompanyid= v_inscompany.inscoidleft join v_billRecItem on v_payments.tripid = v_billRecItem.tripid
WHERE v_payments.checkID = 315898GROUP BY v_payments.tripid, Instransporttotal, lastName, firstName, tripDate, v_payments.payment, v_payments.credit, translation, inscoid, v_payments.checkid
The above query returns one row. However, If I put SUM() around v_payments.payment in the SELECT line then it is 195. I dont know why it is doing this. Any ideas?
Thanks!
John
View 2 Replies
View Related
Apr 3, 2007
I have a report that is deployed at multiple sites without any problems, except one. At one site, when I render it to pdf, it doesn't look at all right, and the alignment on a table goes from center to right aligned for no obvious reason. It then stretches a textbox horizontally to force it onto another page. I've doublechecked and made sure that all the expand options on every single thing on the report is off, and I really don't understand why it would happen at only one site, even though the data is almost the exact same. Not only that, table cells don't stretch horizontally, they stretch vertically, so again, I'm stumped about why this one site would render incorrectly to a .pdf. Even in the report viewer, everything looks fine.
I am wondering if anyone has had a problem with rendering to pdf incorrectly when the rdl and data are correct, and what your solution was.
P.S. IT isn't a problem with margining, so please, don't give the generic "CHECK YOUR MARGINS" response.
View 3 Replies
View Related
Apr 10, 2004
Hi folks, Recently i've installed a fresh Installation of Opertaing Sys and SQL.
Win 2000 server, sp4
SQl 2000 Enterprise, sp3.
I am using Domain user service startup for sql and is member of domain admin. I've manually added the user in Local-Admin group of the system. Obviously it's also has Sys-Admin server roles.
The problem is; when i use enterprise manager and change any of the server settings; Priority Boost or Fix Memory Allocation for the Sql server, nothing happens, the options dialog box closes and doesn't ask for restarting the server neither does the settings take effect when i restart SQL.
However if i change the settings using sp_configure using the same user; it works. i've assigned a fixed memory to SQL but the option "Reserver Physical Memory For SQL" won't work. Couldn't find this option in SP_Configure.
Any ideas, what has gone wrong.
View 6 Replies
View Related
Apr 10, 2006
Hi,I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.What is a good way to not save blank date fields as "01/01/1900"?ThanksStephen
View 1 Replies
View Related
Feb 25, 2005
I get this message in the SQL Server logs after I restore a db. I copied this backup from another server is that why it's telling me it formatted incorrectly? The strange thing is in QA it restores successfully but when I look @ EM it's remains in Loading mode and never completes
The backup data in 'D:SQLServerMSSQLackupjan_prod_db_20050214173 0.BAK' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
View 3 Replies
View Related
Aug 29, 2004
I have a customer who is running a script that generates a custom report. IT is not populating as it should and is returning zeros for everything.
I have tested the the script in the office and it is populating as intended. I have run a debug on the script and it is executing the correct SQL commands. The debug results for the customer and for the one tested in the office are identical. For some reason, the script is not writing to file but is looking at the correct data. I suspect that it is an environment issue most likely on the SQL level.
Could this be an issue with character set? How can I check their character set and language preferences? I understand they are set during installation.
They are using the same collation as us.
What else can I check as I am running low on ideas.
I advised the client to create a new DB and restore over the top. The script was then tested and it was found to be working fine. As it was given an inappropriate name (ie test), I advised to create a new DB with a production name and restore over the top again. We have since returned back to where we started as the report is generating only zeros.
H E L P !
View 1 Replies
View Related
Jul 10, 2007
I downloaded this 7/6/07 and am trying to debug. I have a field that is PICs9(5) and has 00 00 1D as its hex value. The component returns a dt_decimal field value of 20201-
I believe it thinks the 00 are spaces and converts them to ASCII 20. Any ideas on what is wrong here? Is there another step I am missing after the conversion? Any help would be appreciated.
View 1 Replies
View Related
Aug 3, 2007
I have run into a strange issue that I believe is a SQL Reporting Services issue.
I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.
The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.
When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.
What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.
I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.
If anyone has run into this and found a work around, let me know.
Thank you,
T.J.
View 1 Replies
View Related
Jul 20, 2007
We have an intranet site (.NET 2.0) that needs to access to a SQL Server 2005. We've created a named user on the server, WebUser, and use it's credentials in the connection string to connect to the server.
Due compliance issues, we're being asked to remove all the named users from the server. Now the users will have to connect to the database using their security context. That means all the users need to be in some server role, should be given access to the server, etc which is a security risk and a maintenance nightmare.
What other alternatives do we have to solve this problem?
Your help is much appreciated.
View 1 Replies
View Related
Apr 4, 2001
Is it possible to leave this parameter (max server memory (MB)) on default(2147483647) if on my Cluster I have Oracle, Lotus Notes and some other things running, or I have to calculate the amount of memory SQL Server needs?
Thanks for any suggestions!
View 2 Replies
View Related
Aug 3, 2002
What does this error message mean ? We have SQL2000, with database FULL RECOVERY. Other databases on the server are fine. Backups stopped working a week ago. Can't remeber anything changing
+++++++++++++++++++++++++++
The backup data in 'D:BackupTI backup.BAK' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
View 1 Replies
View Related
Jul 31, 2002
One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..
Using T-SQL, how are these set?
sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go
i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?
Sorry, but I don't feel BOL is clear on how to set these options.
TIA Gary
View 5 Replies
View Related
Apr 3, 2007
I'm logged in as 'sa' and I'm using Microsoft Sql Server Mgmt Studio. I've established a Linked Server using Provider MSDASQL, and I'm able to retrieve data from the linked server. However, when I attempt to set the properties for my linked server, I get the following error message.
Adhoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)
I'm not directly accessing the system catalogs (as far as I can tell), I'm actually using the Linked Server Properties user dialog to change the Server Options. Any help is appreciated.
View 3 Replies
View Related
Aug 5, 2015
I am trying to install SQL Server 2014 on W10. SQL Server installed, but failed to create a server that be connected to. SQL Server Configuration Manager has no options for creating a server either. How do I create the server.
View 4 Replies
View Related
Jun 12, 2002
hi,
among our server-agent jobs is one for the log-reader-agent and its run status is displayed as error though the log-reader is working correctly, replication is working fine.
it had hit an error some days ago after an unexpected shutdown - but it has been auto-restarted correctly on restart of the sql-server agent.
now - what can i do to get back to a sensible run-status report?
i have already deleted the job-history with the hope that this might help - but it didn't. should i just stop and restart the job again?
can i somehow delete the status in the jobhistory?
i would gladly appreciate any ideas because it's making me just mad to have a 'failed' job status on the monitor *g*
thank you,
kerstin
View 1 Replies
View Related
Oct 26, 2007
All,
I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)
So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.
Anyway, I'm curious if anyone else has seen this?
Thanks.
View 10 Replies
View Related
