Converting SQL Server Data Into XML

Apr 11, 2006

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

View 10 Replies

Converting Data From MySQL To SQL Server 2005

Jun 28, 2007

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

View 2 Replies View Related

Converting SQL 2000 Data Into A SQL 2005 Server

May 4, 2006

I have a large (huge) database that I want to copy onto my new slq 2005 test server. I'm leaning toward detaching the data on my 2000 box, duplicating it, copying it to my new 2005 machine, and attaching it. Is it possible that it could be that simple? If not, how is it done? Thanks a bunch for any help or pointers to the articles I was totally unable to find on the subject.

View 3 Replies View Related

SQL Server 2012 :: Converting Large Excel Spreadsheet To Normalized Data

Aug 7, 2014

I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.

Here is a small sample:

Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062

I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.

The desired output:

Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696

and so on ......

The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.

View 9 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Varchar Value To Data Type Int

Aug 12, 2014

I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.

I am also including example of how the account_numbers are formatted.

R222209
R222220
R222222
R222212
R221123
F707768

[Code] .....

View 5 Replies View Related

SQL Server 2008 :: Error Converting Data Type Varchar To Numeric

Jun 17, 2015

As per my requirment using below script to convert from varchar to numeric.

declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)

Getting error : Error converting data type varchar to numeric

View 6 Replies View Related

Converting DB2 Timestamp Data To SQL Server 2005 - Problems With Unique Index

May 24, 2007

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

View 5 Replies View Related

SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float

Jun 11, 2014

So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.

Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

SQL Server 2012 :: Arithmetic Overflow Error Converting Varchar To Data Type Numeric

Oct 30, 2014

OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.

Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))

View 1 Replies View Related

SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?

Aug 14, 2014

I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

My problem is that Line 6 is:

set @brn_pk = '0D4BDE66347C440F'

so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?

View 3 Replies View Related

Data Access :: Arithmetic Overflow Error Converting Expression To Data Type Int

Jul 24, 2015

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)

View 10 Replies View Related

System.Data.SqlClient.SqlException: Error Converting Data Type Numeric To Decimal.

Aug 31, 2004

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

View 1 Replies View Related

System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int

Aug 31, 2006

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 Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Data Flow: Converting Data In Multiple Columns

Sep 28, 2006

Hi,

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.

View 3 Replies View Related

Converting Data From Access 2000 To SQL Server 2000

Oct 18, 2004

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks

View 1 Replies View Related

Converting Text Data To Ntext Data

Mar 27, 2006

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

View 2 Replies View Related

Converting Data

Dec 7, 2006

Hi I am converting data from old DB to NEW DBIn 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 spaceOtherwise I have to clean them up manually as I have 1000000 records
cheers

View 1 Replies View Related

Converting Data

May 27, 2008

hi,
can any one tell me how to convert my access data to sql server data. using sql server express edition

View 2 Replies View Related

Converting Data Types

Jul 3, 2007

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?

View 4 Replies View Related

Converting Data Types

Sep 11, 2004

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

View 1 Replies View Related

Converting Read Only Data From SQL 6.5 To SQL 7

Aug 11, 2000

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.

View 1 Replies View Related

Converting Data From One Datatype To Another.

May 1, 2000

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

View 1 Replies View Related

Converting Hex Data To String

Jan 21, 2001

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...

View 1 Replies View Related

Converting Relational Data In XML??

Apr 2, 2008

Hello everyone

When converting From Relational data into XML which
method ie RAW, AUTO, PATH, EXPLICIT is mostly used and why please

Regards

Rob

View 5 Replies View Related

Converting Data Types

Jun 1, 2006

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.

View 3 Replies View Related

Converting .sql File Data

Oct 29, 2006

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!???

View 5 Replies View Related

Converting Data Format

Nov 16, 2007

View 3 Replies View Related

Converting Data Question

Oct 11, 2006

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 Related

Converting Data Types

Aug 17, 2006

I 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

View 3 Replies View Related

Converting The Value To Data Format

Aug 14, 2007

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

View 2 Replies View Related

Serious Converting Data Problem

Mar 13, 2006

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

View 5 Replies View Related