Hi
I am converting data from old DB to NEW DB
In the OLD table fields like "PhoneNumber" the data enterd are [ 657 985-986, (03)-987-543, 675(89)00, ect]
Is their any function in sql where I can get rid of all those spaces and () and - between the numbers as my new field is only numbers and with out space
Otherwise I have to clean them up manually as I have 1000000 records
cheers
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)
Hi There,
I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line:
Length = Convert.ToDouble( txtLength.Text )
I'm also using the following lines to prepare my stored procedure call:
arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5);
arParms[9].Value = record.Length;
My stored procedure has the following parameter definition:
@Length decimal(9,3)
My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error:
System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.
I don't know how to either make sql or C# to truncate the value or catch the exception to automatically assign 0 to the parameter.
Please Help.
Moshe
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View 3 Replies View RelatedHi,
I'm just wondering what's the best approach in Data Flow to convert the following input file format:
Date, Code1, Value1, Code2, Value2
1-Jan-2006, abc1, 20.00, xyz3, 35.00
2-Jan-2006, abc1, 30.00, xyz5, 6.30
into the following output format (to be loaded into a SQL DB):
Date, Code, Value
1-Jan-2006, abc1, 20.00
1-Jan-2006, xyz3, 35.00
2-Jan-2006, abc1, 30.00
2-Jan-2006, xyz5, 6.30
I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
Hi all,
My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.
I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.
If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.
Thanks a mil, in advance.
Eddie
hi,
can any one tell me how to convert my access data to sql server data. using sql server express edition
Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.
I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.
any ideas?
I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?
Thanks
I am using SQL 6.5 and we are going to upgrade to SQL 7. Has anyone
had any experience converting "read only" data? If so, I am looking
for tips and tricks. thanks in advance.
I am populating tables with Columns of fixed length (of a text format)
from a different table with some Columns of Money ( DataType).
It would not allow me to populate these Columns.
Can anyone please give me a hint?????
Thanks in advance.
M. Khan
I am writing a stored procedure that generates a computername for new workstations. Our naming standards, based on terminal ID's, are as follows:
'R' + X + Y + ZZZ, where 'R' is the letter 'R', X is a hex digit indication a locatoin, Y is a hex digit indicating business line, and ZZZ is a hexadecimal sequential number assigned within each location/busines line, for up to 4096. (This isn't a perfect naming standard, but is an improvement over the one that preceded it and is a requirement for our legacy applications).
I can do everything fine, and calculate the sequential number using an int. Once I have the new number, I am trying to convert the integer to a varbinary, then convert that to a string, then use RIGHT to get the rightmost three characters of the hex string.
set @SEQNUM = 4095
set @HEXSEQNUM = CONVERT(varbinary ,@SEQNUM)
PRINT CONVERT(varbinary ,@SEQNUM) -- displays 0x00000FFE etc.
PRINT 'HEXSEQNUM ' + @HEXSEQNUM -- displays 'HEXSEQNUM'
Any ideas? I am very new to TSQL...
Hi Folks!
I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.
My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C: est.xml" -U -P -c -r -t'
Is there any way I can tune my query to fix that error?
Thanks so much for your help!
-Parul
Hello everyone
When converting From Relational data into XML which
method ie RAW, AUTO, PATH, EXPLICIT is mostly used and why please
Regards
Rob
I have a table where one of the columns is defined as numeric (12,0). This table already contains rows.
I want to modify the attribute so that it is numeric (12,2).
When I execute the following :-
alter table test3
alter column amt1 numeric (12,2)
I get the errors :-
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Can it be done via a cast/convert statement and if so, can somehow please enlighten me?
Many thanks.
Hi,
I have two postcode databases in .sql ready to dump into my existing database, the first sql file is 300megs and
the second one is 300k, the reason the second is so small is because it misses out the last 3 letters of the post
code, i dont need theese last 3 digits anyway.
Basically i want to convert the second file so that it is compatible for me to import it into my database and use.
the first file looks like this
INSERT INTO `ZIPCodes` VALUES ('AB101GY',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1476,-2.0975);
INSERT INTO `ZIPCodes` VALUES ('AB101NP',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1467,-2.1074);
INSERT INTO `ZIPCodes` VALUES ('AB101RQ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1467,-2.1107);
INSERT INTO `ZIPCodes` VALUES ('AB101TT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1431,-2.114);
INSERT INTO `ZIPCodes` VALUES ('AB101WX',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0);
INSERT INTO `ZIPCodes` VALUES ('AB106AA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.135,-2.1156);
INSERT INTO `ZIPCodes` VALUES ('AB106DE',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,57.1377,-2.1173);
And the second file looks like this:
insert into `hwz_postcodes` values ('postc',0,0,0,0),('AB10',392900,804900,57,-2),('AB11',394500,805300,57,-2),('AB12',393300,801100,57,-2),('AB13',385600,801900,57,-2),('AB14',383600,801100,57,-2),('AB15',390000,805300,57,-2),('AB16',390600,807800,57,-2),('AB21',387900,813200,57,-2),('AB22',392800,810700,57,-2),('AB23',394700,813500,57,-2),('AB25',393200,806900,57,-2),('AB30',370900,772900,56,-2),('AB31',368100,798300,57,-2),('AB32',380800,807200,57,-2),('AB33',355200,815100,57,-2),('AB34',350800
Is there any utility out there that can do this for me? What about the co-ordinates systems, they look incompatible,
so the converter would need to be able to convert the co-ordinates aswell. It would also need to add in all those nulls.
once the file is converted i would then import it through the sql command line.
Why am I such an SQL n00b!???
hi guys how can I change this format
date|amount
02/25/07| 00.00
02/26/07| 00.00
To
date|amount
02/25/07| 0.00
02/26/07| 0.00
thanks!
I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.
View 5 Replies View RelatedI have a table with a field of Char(10) Data type
this field contains records of work time Attendance in a decimal format
Ex. I attend today for 8.30 this means eighthours and thirty mintutes
the main problem faced me to make some calculations on those records (sum, subtract, etc)
so I want to convert the data from char type to decimal or real using the next code but it doesn't work
select (cast (satreg, decimal) + cast (satot, decimal)) from timecard
can u please help me in the main issue how to sum char type records or at least how to convert them to decimal
Regards
Hello Everyone,
Please guide me in converting the value to date format.
from source i'm getting the value (which is acchally a data value) '20070730'.
I need this value to be in date format '2007/07/30'
Please help me in getting it done.
thank you
Hi,
I am going to map a SQL Server 2005 table to a SQL Server 2000 table.
Here are databases specifications:
SQL Server 2000:
Collation: SQL_Latin1_General_CP1256_CI_AS
Table: Party
Feilds:
Serial int not null
FullName varchar(50)
SQL Server 2005:
Collation: Arabic_CI_AS
Table: CMN_tblParty
Fields:
PartyID int not null
FullName nvarchar(50)
I used SSIS to map data from 2005 to 2000 but it failed. Actually it can't convert unicode field to non unicode one. Therefore I used a Data Conversion Block and I converted the FullName from CMN_tblParty to string with CodePage 1256. But when I connect this block to OLEDB Destination block, a validation error happens which mentions that it can't map data from CodePage 1256 to a 1252 one.
This shows that the SQL Server 2005 assume the SQL_Latin1_General_CP1256_CI_AS as 1252 (I don't know why) and if I change my data conversion block that it converts the FullName from CMN_tblParty to Codepage 1252, it gives an error in runtime which shows that it can't convert nvarchar from Codepage 1256 to a varchar - codepage 1252.
I used also the export data wizard in order it generate the package itself. But this also failed due to conversion problem.
As another solution I checked the DTS on SQL Server 2000 and it could successfully export data to SQL Server 2005 with no conversion problem.
Please let me know how I can run my conversion using SSIS since SSIS enhancements are greate and I am using much of it features in Conversion and Lookups.
Samy
We will be getting a disk from a company that we have been working with that will have all our data on it in
MS SQL format. The problem is, that we do not have SQL anything here to be able to utilize our data!
Are there any good programs on how to convert this data to something we could use? What programs can we convert this data to?
Any insight appreciated. I'm fairly computer literate - but not with SQL at all.
Hi,
I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.How may this be done?I am able to create a Data Component with the ACCESS mdb file.
Likewise, how may I convert EXCEL data to SQL2005 table?Thanks.
David
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer
test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
For our DataWarehouse, we get several date fields from our mainframe system in a character format. When this data was loaded into SQL Server 6.5 using the CONVERT(DATETIME...) function, any dates containing 'bad' data would simply be replaced by a NULL automatically by the DBMS. We are now going to SQL 7.0, and I have found that when it hits a bad date it terminates the stored procedure, resulting with no update.
Is there a straightforward way around this? Possibly a script that will scrub the data, replacing bad data with NULLs? I'm trying to avoid writing something that will take the number of days per month and determine if it is valid.
Thanks
Michael
Hi all,
My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.
If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.
Thanks a mil, in advance.
Eddie
I have a table as follows
opendate (datetime) callnumber (int) closed (bit)
I want to find how many calls were opened today and of those how many are closed
I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days
create table #holdit1
(opencount int)
create table #holdit2
(closedcount int)
insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed
insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed
select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2
DROP TABLE #holdit1
DROP TABLE #holdit2
this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4
I have a column in a table I want to convert all data to UCASE. Currently I have some that are lower case, some Uppercase, and some mixed. Considering there are over 700,000 records and these are just manufacturer names, I want to convert these to uppercase so all displayed information is consistent and I do not have to go through my scripts and convert to UCASE anywhere I pull a manufacturer name.
Is it possible to update a column data to UCASE? I understand how to do this via a row and with a condition, but an entire column has me confused.
Thanks for your help.
Hello Everyone,
Please guide me in converting the value to date format.
from source i'm getting the value (which is acchally a data value) '20070730'.
I need this value to be in date format '2007/07/30'
Please help me in getting it done.
thank you
Hello.
I have data in a SSIS package that I need to alter to something else.
The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".
The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.
I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.
Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?
Thanks
Hi,
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);
this.mycourtsDataSet1.AcceptChanges();
this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);
This did not happen with VS2005/CE3.01.
I have tried changing all references to decimal (or money in CE3.5) without luck.
I'm beginning to think that string may be the way to go!!!!!!!
Can someone shed some light on my problem.
Thanks,
Later:
It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Regards
RH
Hello,
Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated.
Thanks