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
Hi does anyone know how to make MS SQL server 2000 calculate the right weeknumber around new year ?According to the help file, MSSQL should follow ISO8601, which ensures thati.e. 2003 has only 52 weeks. but these two prove it wrong:Select datepart(ww,'2003/12/31') --(wednesday -> returns 53)Select datepart(ww,'2004/1/1') --(thursday -> returns 1)Well - the weeknumber does not change in the middle of the week. Since2003/12/31 is the third day in the week (with Datefirst set to 1, monday),the week with the two above dates should have number 1.Does anyone know the cause or maybe a workaround to this problem ?Thanks in advance,Stig Andersen
View 4 Replies View RelatedI have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
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?
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 ?
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.
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?
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.
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
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedHow 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.
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedI have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
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
Is there a way to store a formatted text (bold, italicized etc) in a text field on SQL Server 7.0 ?
View 2 Replies View RelatedCan somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
View 8 Replies View RelatedHi,Is there a way to get stored procedure texts out in a formatted waylike defncopy in Sybase????? The way they print it out reallysucks............RegardsSubhas
View 1 Replies View RelatedI have a table for articles that I want as
the basis for a blog. I have a field of description where the actual
article will go, I have only ever really
used tables to put in 'blocks' of text, how would I go about storing/displaying
data that is in my database table in a more formatted way, for example line
breaks, indents etc?
Hi,
I am trying to write the output of an sql query to a text file using bcp. Now the problem am facing is i have to format the text like i the following order...
<DATETIMESTAMP>08:39 Thursday, March 15, 2007</DATETIMESTAMP><CATEGORY>Quarterly Sales</CATEGORY><HEADLINE>Quarterly Corporate Earnings (03/15/07)</HEADLINE><BODY> Quarterly Corporate Profits (03/15/07) PER-SHARE ($) NET EARNINGS (mil$) REV. (mil$)COMPANY CURRENT YEAR-AGO CURRENT YEAR-AGO CURRENT YEAR-AGOabc 0.33 n/a 8.60 (13.30) 144.40 112.50 bcdf 0.16 (0.15) 7.80 (7.40) 101.90 81.20 gha n/a n/a (90.00) (80.00) 488.00 462.00 qwqw (0.10) (0.15) (3.30) (4.30) 2.90 2.20
Copyright(c) 2007 mycompany.com, Inc. All Rights Reserved</BODY>
Would somebody please advice whther this sort of formatting is possible with bcp..Please give some pointers am completely stuck....the company data is coming from a database table...am using sql 2005...
Thanks in advance
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.
Hi All,
I am kindly seeking for help.
I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
Date ID R P M D... Y
1/1/90 A 1 2 3 4... 5
1/2/90 A 2 3 4 5... 1
...
2/11/05 A 3 4 5 6... 2
1/1/90 B 1 2 3 4... 5
1/2/90 B 2 3 4 5... 1
...
2/11/05 B 3 4 5 6... 2
...
The expected query results look like: ( this results from Date, ID and R fields)
Date A B
1/1/90 1 1
1/2/90 2 2
...
2/11/05 3 3
The SQL I wrote:
select date, ID,
A=sum(case when ID=A then R else 0 end),
B=sum(case when id=B then R else 0 end)
from MyTable
Group by date
I would also like to get another set of results with the same format but from date,ID and P fields:
Date A B
1/1/90 2 2
1/2/90 3 3
...
2/11/05 4 4
select date, ID,
A=sum(case when ID=A then P else 0 end),
B=sum(case when id=B then P else 0 end)
from MyTable
Group by date
The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.
Any suggestion/comments are highly appreciated!
shiparsons
Hi All ,
Is it possible to insert the below text into a column and retrieve it in the same format ?
Thanks,
Hari Haran Arulmozhi
TEXT :
CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI
================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600
RECEIVED ADVANCE :Rs.10000
I have 265 word documents which I use with mail merge to create a master document.We are going to auto generate this master document going forward so I want to store these 265 word documents, these range in size from 10kb to 50kb. As I mentioned these are formatted and also contain a merge field/marker so what I will be doing is using a value in the database, my application will take the document which is stored as a string/blob and will replace the "merge field" with the value from the database.
I can write an application to import each of these documents but I am not sure whats the best way to store them in order to be able to find and replace the merge marker.Also I will be joining a number of these together to make the master document. So for example I take 10 of these and I replace the marker fields in these with the values from the database, I will then push that string out to a PDF/Word document for printing.
I need to export data to a worksheet and format the resultant sheet appropriately. I kow server side automation is a no-no. What would be the best alternative to accomplish this?
View 16 Replies View RelatedHi, that's the problem:
I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.
When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:
exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1'
The fields are defined as follows:
<SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters>
The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'.
I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
Thanks in advance,
Anderson
Using the stored proc XP_Sendmail i am able to send mails successfully. But i need to format the mail.. so for formatting i am using HTML tags.. so when i am sending the mail the html tags are getting displayed as it is. So pls can any body tell me how i can send the HTML formatted mail.
View 2 Replies View RelatedWhat 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.
I have some textual data that comes preformatted with some extra spaces and line feeds. I am attempting to put it in a Report using SSRS 2005.
When ever I view the report in HTML Mode all of the extra whitespace is suppressed into a single space. However when I export to PDF or Word it all comes back. I have tried using the .Replace() method to put in and <BR> tags into my code, but then it just displays the extra text and doesn't display it as HTML.
Is there anyway either replace the items with the appropriate HTML, or add the "PRE" whitespace CSS attribute to a Textbox so that the text displays the same in HTML and in a PDF?
Thanks.
I am developing a resume storage system, and don't know the best way to store the resumes that come in to our company in both MS Word and text files. Should I store the files in the original format they come in, and reference the file name in the databse that points to a directory where they are stored, or should I store the text of the resumes directy in the database. There are 2 things that I must follow.
1: I need to have the documents keep their formatting.
2: I also need the capibility of conducting a full text search to pull out key words from the documents.
What is the best way?
I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output
esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)
Hello,I thought this would be easy, and I've read a lot of posts, but Ican't seem to find exactly what I'm trying to do.
Quote: