Hi all - am in a bit of a quandry over this one!
My application is all up and running - it's an despact & accounts app - I've missed one thing though - the ability to deal with half pennies (or half cents for those of you over the pond ;))
All of my tables are set up as decimal (19,2) along with all the params in my stored procs (a lot!). Is there an easy way to change all of these in 1 swoop?
Thanks in advance,
Stephen.
Hi,
What I mean is:
I have a decimal(19,10) variable called @value and it has the value 66.6666666666
I can round it using ROUND(@value, 0) and the result is 67.000000000
But what I really want is to get rid of all the zero's and the decimal point so that I end up with just 67
Thanks in advance,
Ian.
I'm trying to change the background color in a field based on its number value being greater than another number value in an adjacent field. The issue I am having is the format, it's like a software version string or IP address (in this case, it is A/V Signature Versions) where the length of the numbers between the decimal places can be different, and that seems to throw off the comparison.
For example (these are actual values I am comparing);
1.201.1054.0 should evaluate as greater than 1.201.2.0 but when compared, evaluates as FALSE.
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?
For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component.
The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "¶mname=value&parm2=value..." format.
The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code:
Dim paramBlob as Byte()
paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length)
The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters.
THIS IS A PROBLEM!!!
Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in!
Thanks for your help in advance,
LOSTlover
I am trying to narrow down this problem. Basically, I added 3 columns to my article table. It holds the article id, article text, author and so on. I tested my program before adding the additional field to the program. The program works fine and I can add an article, and edit the same article even though it skips over the 3 new fields in the database. It just puts nulls into those columns.So, now I have added one of the column names I added in the database to the code. I changed my businesslogic article.vb code and the addarticle.aspx, as well as the New article area in the addartivle.aspx.vb page. The form now has an additional textbox field for the ShortDesc which is a short description of the article. This is the problem now: The command parameters.length is 9 and there are 10 parameter values. Right in the middle of the 10 values is the #4 value which I inserted into the code. It says Nothing when I hover my mouse over the code after my program throws the exception in 17 below. Why is command parameters.length set to 9 instead of 10? Why isn't it reading the information for value 4 like all the other values and placing it's value there and calculating 10 instead of 9? Where are these set in the program? Sounds to me like they are hard coded in someplace and I need to change them to match everything else. 1 ' This method assigns an array of values to an array of SqlParameters.2 ' Parameters:3 ' -commandParameters - array of SqlParameters to be assigned values4 ' -array of objects holding the values to be assigned5 Private Overloads Shared Sub AssignParameterValues(ByVal commandParameters() As SqlParameter, ByVal parameterValues() As Object)6 7 Dim i As Integer8 Dim j As Integer9 10 If (commandParameters Is Nothing) AndAlso (parameterValues Is Nothing) Then11 ' Do nothing if we get no data12 Return13 End If14 15 ' We must have the same number of values as we pave parameters to put them in16 If commandParameters.Length <> parameterValues.Length Then17 Throw New ArgumentException("Parameter count does not match Parameter Value count.") 18 End If19 20 ' Value array21 j = commandParameters.Length - 122 For i = 0 To j23 ' If the current array value derives from IDbDataParameter, then assign its Value property24 If TypeOf parameterValues(i) Is IDbDataParameter Then25 Dim paramInstance As IDbDataParameter = CType(parameterValues(i), IDbDataParameter)26 If (paramInstance.Value Is Nothing) Then27 commandParameters(i).Value = DBNull.Value28 Else29 commandParameters(i).Value = paramInstance.Value30 End If31 ElseIf (parameterValues(i) Is Nothing) Then32 commandParameters(i).Value = DBNull.Value33 Else34 commandParameters(i).Value = parameterValues(i)35 End If36 Next37 End Sub ' AssignParameterValues38 39 40 41
View 2 Replies View RelatedI am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View RelatedHello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies View RelatedHi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies View RelatedHow can I measure the length of a field of type text?
View 1 Replies View RelatedWe ran into a problem loading access where tables where if we did notselect the property allow zero length we got an error message whenloading data with some empty values.We are now ramping up SQL server and the question came up will SQLserver have the same problem with empty data values.Is there an SQL server equivalent to allow zero length?
View 2 Replies View Relatedwhat the max length that I can run query to sql Server?example:
View 2 Replies View RelatedIs there a function how I can see how many caracters a word from my sql is?
for example:
if I want the lenght of the word "sql" => 3
if I want the lenght of the word "forum" => 5
Hi folks,
I'm trying to load a flat file (text) into a table with 83 columns in it. The record length on the flat file is 251 characters long. For some reason when running the DTS import utility, DTS can't read the past the 142 character on the flat file, it spills on to the next line. The red bar is set on the 143 character when i use the fix field options in DTS import. Can the red bar be moved out further? Or is there a max field length using DTS?
anyone out there that can help?
Joe
Hi Guys
I am having a problem with the execution of a query.This is how it is working,if i include all the clounms(26 columns) for the output of 34,000 records it is taking around 4 Min. but if i exclude one column or reduce the size of the column i.e from text to varchar(100) it is taking 23 Sec.
If i am trying to change the size of the column from varchar(100) to varchar(150) it is taking the same time i.e, 4 Min.
Looking at this i am wondering if there are any system setting where we can increase the size of the rows for the out put or are there any other things i have to look at to increase the speed of the Query.
Did anyone come across this problem.
Any help is appriciated.
Thanks
Chak.
Hi,
I am sending the output of a query to a file and unfortunately every row is containing only a certain number of characters. My query has rows having length of 1500 characters for each row. When I get the query and send it to a file I am seeing only the first 250 characters for each row instead of 1500 characters.
I will be grateful if anyone can help me suggesting a solution for this problem.
I appreciate your help in advance.
thanks,
Sravan.
Hi,
SQL Intrduction book on page 269 says that the max.amount of data contained in a single row is 8060 bytes. But I have no difficulty creating a table with two varchar 8000 columns. How can this be ? Can somebody explain how to interpret the statement about max.8060 bytes please?
Thanks.
Hi
Has anyone come across SQL Server giving errors like A row on 'page 52995' was accessed that has illegal length of 0 in database? I believe that it is due to some data on the page has been corrupted. Is there a way to prevent such problem? Can daily backup be able identify such problem?
What is the syntax to count the # of characters in any given field in Transact SQL?
View 1 Replies View RelatedHi
When writing a stored procedure,sometimes the line of sql code could be very long and be more than 128 caracthers.
I got an error message saying that the code is vey long and the maximum is 128.
But at the same time, this is erratic in MSSQL server because it works sometimes even with a line of code of more than 800 caracters...
Have you ever experienced the same problem??
What is the solution?
Thanks
Is there a string handling function is SQL which will return the length of the contents of a field?
View 1 Replies View Relatedi have problem regarding the row length and varchar.
my problem is on every new row i have +6 more character on one of my field then the last record. and BOL says i can only have 8060 character per row.
What i can not use the full lenght of varchar(8000) on field.
Can anybody help?????????
Hi
I am quite new in DTS and I want to calculate the length of a string. I am trying to use ActiveX script - Java Script and here is my code
if(length(DTSSource("MaNo")) ==5). It parse fine but when I run a test it brings an error "Error Description: Object Expected"
Please help
What is the maximum text length that can be entered into a sql server field and what data type should be used for it?
Thanks!
Hi,
I am creating a piece of code to create audit tables based on my current tables. I'm using the info in sysobjects to get this done, but I have one problem.
I can't seem to find the actual lenght of a column, I tried with the systypes.length, but that was obviously not what I needed. Does someone know how (or where) I can retrieve the length that I need?
thx in advance.
is there a property in the sql server where i can track the length of the value in a field.
Funnyfrog