Converting DTS to Stored procedure

Hi,

I created a DTS package for importing a textfile, parse the data and insert it into some tables.

I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...

Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?

First step is to import only row 0 to a table, second step is to import row 1 -> n

Any examples would be nice... :-)bcp or bulk insert...

what's row 0?

Probably a header...

Can you create a "work" table for your own use?

How many rows are we talking about?

Where is the data coming from (it's Access isn't...grrrrrr)

What do you mean by parse (SUBSTRING)?

Does your dog have fleas?

Why is the sky blue?

What's the name of your dba...(just kidding)

Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?

The file contains a header in the first row and then tab separated data...

I have a rawtable that it is imported to now by DTS.

It's about 20000 rows, textfile...

By parsing i mean several queries that translates the data from the rawtable into other tables.

And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...

The sky is blue beacuse God have his blue underwear on...

My dba is named Sven.

--

I have tried to use xp_cmdshell but I'm not allowed too...

Seems like I have to do the import in my client.. written in C++.|||Hi,

You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.

CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

IF @.hr <> 0
BEGIN
RETURN 1
END

GO|||Got a:

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed

--

Seems like I have no permission to run sp_OACreate either...

Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.

Converting data to be inserted into a database

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 Function

Any help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.The probably is that you are trying to send the literal value 'test' as a parameter value.

Please use this approach instead:
Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder],[AmountSold]) VALUES (@.CatList, @.Type,@.name, @.manufacturer, @.Price, @.Weight, @.Description, @.image, @.OnOffer, @.OfferPrice, @.OfferDescription, @.AmountInStock, @.AmountOnOrder, @.test)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.Parameters.Add("@.CatList,SqlDbType.VarChar,99).Value = CatList.SelectedItem.text
dbCommand.Parameters.Add("@.Type,SqlDbType.VarChar,50).Value =txtType.text
dbCommand.Parameters.Add("@.Name,SqlDbType.VarChar,30).Value =txtmanufacturer.text
' add all parameters in this manner
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Note that for each parameter you will need to use the appropriate SqlDbType. And if it's a character data type you will also need to specify the length.

Here are some links which should help you with using parameters:
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net
How To: Protect From SQL Injection in ASP.NET|||Thanks alot for the reply, Those links will help alot aswell.

converting columns in an INSERT (was "SQL Query")

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...First of all, I'd strongly suggest that you list out the columns. That solves all kinds of problems before they get a chance to happen to you. If you are determined to do things the hard way, you don't have to enumerate the columns yourself, but I'd still recommend it.

You ought to be able to use any of those conversions if you like, but as long as the contents of the column can be converted to MONEY, the SQL Server engine ought to handle the conversion for you.

-PatP|||"SELECT *" is shorthand for "I'm a lazy programmer". I would never leave it in any finished code. Bad. Bad. Bad bad code.

Converting bytes to string

Hi guys,

I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so farwith no luck.

My current code reads:

Dim conn As New Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString
conn.Open()

Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType

Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image, uploadedFile.Length)
sImageData.Value = uploadedFile

cmd.Parameters.Add(sImageName)
cmd.Parameters.Add(sImageType)
cmd.Parameters.Add(sImageData)

Dim reader1 As Data.SqlClient.SqlDataReader

reader1 = cmd.ExecuteReader

Runningthrough debug, everything runs up until the last line, where an erroris caught saying : Failed to convert parameter value from aSqlParameter to a String

I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it.

Any help greatly appreciated!!

In http://www.codeproject.com/useritems/images_in_sql_server.asp
private void GuardarImagen(byte[] matriz)
{
this.cmd.CommandText = "insert into tabla(DESCRIPCION, IMAGEN) " +
"VALUES(@.DESCRIPCION, @.IMAGEN)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Descripcion", this.NombreDeArchivoCorto).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("Imagen", matriz).SqlDbType = SqlDbType.Image;
cmd.ExecuteNonQuery();
}

suggests that yours should be
Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName
Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType
Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image)
sImageData.Value = uploadedFile

If you are using SQL 2005
cmd.Parameters.AddWithValue("sImageName", sImageName).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageType", sImageType).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("sImageData", uploadedFile).SqlDbType = SqlDbType.Image;

sqlsql

converting boolean to bit

I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?

I tried

CType(myChkBox.checked,String)

, which returns the strings "True" or "False". But, I get the error

The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I then tried

"CAST(" & CType(myChkBox.checked,String) & ",bit)"

, but got the same error.

I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.

Thanks
Martindoes this work ?

IIf(CheckBox1.Checked = True, 1, 0)

|||There is, you should be using parameters not on-the-fly sql strings.|||pkr, I agree that I should be using parameters, and I will get around to it before I finalise this control. I am not in front of my code at the moment, do you think that

dim prmChecked as new SqlParameter("@.myParam",SqlDbType.Bit)
prmChecked = myChkBox.checked

will work? According to the doco, the SqlDataType Bit takes an unsigned integer of 1 or 0. So, I would still have the same problem.

ndinaker, thanks for your suggestion. Is this the way its usually done?

Martin|||The checked flag is a boolean. Even *if* it doesn't automatically convert boolean to bit, it's trivial to convert boolean to int.

Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

convert varchar to smalldatetime

A table contains a field of type smalldatetime
The feed has this field as string i.e. '060830'
Would like to insert this string date into the field of the table which has a datatype of smalldatetime.

Is this the correct way to convert a string to smalldatetime?

declare @.DateFeed varchar(6)
declare @.TradeDate smalldatetime

set @.DateFeed = '060830'

set @.TradeDate = convert(smalldatetime, 6 + '-' + 8 + '-' + 30)
print @.TradeDate

Thanks

Use the following query...

Prefixing the century value on your data & casting the result as datetime

Code Snippet

Select Cast('20' + '060830' as smalldatetime)

|||

Use function CONVERT, with style 12. Let SQL Server decides the century (0 - 49 --> 2000 / 50 - 99 --> 1900).

select convert(smalldatetime, '060830', 12)

go

AMB

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0000013)

Select * from A

------
1.3 e-05

I am getting '1.3 e-05' values because datatype is real so that is why I am getting but want to get '0.0000013' values. Please let me know what have to that for that.

Please let me know asap.

Regards,
M Jain
NJ,USAYou can convert "REAL" into "NUMERIC" first and then
convert to varchar.

like,

select convert(varchar, convert(NUMERIC(30,20), id)) from A

Convert real to Varchar.

Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.0013)

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me knowHEY NJ!

isn't it "Jersey"?

Doesn't this work?

USE Northwind
GO

CREATE TABLE A ([ID] Real)
GO

INSERT INTO A ([ID]) values(0.0013)

SELECT CONVERT(varchar(45), [ID]) FROM A
GO
DROP TABLE A
GO|||Hello All,

I am facing problem to get exacltly value using convert function. Please follow below steps.

Crete table A (ID Real)

Insert into A values(0.000013)

select * from a

Ouput
------
1.13 e05

But I want '0.000013' to display so that is why I have written below Query.

select convert(varchar, convert(DECIMAL(30,6), id)) from A

------
0.00001300

I am getting '0.001300' values because scale is 6 in Decimal so that is why I am getting but want to get '0.0013' values. Please let me know

-- Is any function using that I can remove added/padded zero suffix.

Current Expected result.
0.00001300 to 0.000013|||Did you try what I gave you?|||You use 2 different numbers:

0.0013 and 0.000013 - which one is it ?

If it is the first then just change the decimal from 6 to 4.|||Does the precision matter if you're going to varchar?

And I still want to know

What exit?

Convert problem

I have a 'datetime' column and I want to select the count
of today's dates and insert into another table column with
integer type (Only the number as a count). I have the
following query:
Select count(*)from dbo.stats
where convert(varchar(12),accessdate, 112) = convert
(varchar(12),GETDATE(),112) AND accessdate <> ''
I get the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'select count(*)
from dbo.stats
where convert(varchar(12),started, 112) = convert(varchar
(12),GETDATE(),112)
AND accessdate <> '' to a column of data type int.
How do I fix this problem.
Thanks.> Select count(*)from dbo.stats
--^
Missing space here?
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
Did you mean
AND accessdate IS NOT NULL
? If it is a datetime column, it can't be equal to '' (empty string) since
it is *not* a string at all.
Also, if you want to make use of an index on the column, it would probably
be more efficient to say this:
WHERE accessdate >= {fn CURDATE()}
AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
AND accessdate IS NOT NULL
A|||The space is a typo error only in the post. Query works
even with '' alone (Without inserting) and the column is
not indexed column since there will be more than 1 value
for the same day............
>--Original Message--
>> Select count(*)from dbo.stats
>--^
>Missing space here?
>> where convert(varchar(12),accessdate, 112) = convert
>> (varchar(12),GETDATE(),112) AND accessdate <> ''
>Did you mean
>AND accessdate IS NOT NULL
>? If it is a datetime column, it can't be equal to ''
(empty string) since
>it is *not* a string at all.
>Also, if you want to make use of an index on the column,
it would probably
>be more efficient to say this:
>WHERE accessdate >= {fn CURDATE()}
> AND accessdate < DATEADD(DAY, 1, {fn CURDATE()})
> AND accessdate IS NOT NULL
>A
>
>.
>|||> The space is a typo error only in the post.
Then can you show your actual table structure, some sample data, and the
actual query you use (rather than transposing!!!!), then someone can try and
reproduce your error? The error actually doesn't fit with the query you've
shown; I'm guessing you are trying to execute this from EXEC or
sp_executeSQL.
> not indexed column since there will be more than 1 value
> for the same day...
Not sure what that has to do with indexing. Unique index, maybe. But you
can certainly index columns that contain duplicates, and you certainly
*should* consider indexing columns that will be used frequently in exact or
range queries, such as the one you are writing...|||Mike,
Maybe you are having problems with quotes. The error message suggests
that SQL-Server is interpreting your entire query as a varchar value.
Maybe you are building the query in a front-end tool and using single
quotes to delimited the SQL statement. In that case, you need to escape
the quotes of the statement so they end up in the final string.
Hope this helps,
Gert-Jan
Mike wrote:
> I have a 'datetime' column and I want to select the count
> of today's dates and insert into another table column with
> integer type (Only the number as a count). I have the
> following query:
> Select count(*)from dbo.stats
> where convert(varchar(12),accessdate, 112) = convert
> (varchar(12),GETDATE(),112) AND accessdate <> ''
> I get the following error:
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'select count(*)
> from dbo.stats
> where convert(varchar(12),started, 112) = convert(varchar
> (12),GETDATE(),112)
> AND accessdate <> '' to a column of data type int.
> How do I fix this problem.
> Thanks.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert image to hexadecimal

I want to export data from my tables by generating insert statements,
including data of type image. To avoid having to use textcopy.exe or
textptr, I want to have the image data part of the insert-statements by
converting the binary image data to hexadecimal strings. Also, the
image data is larger then 8000 so a simple convert won't work. How do I
do the conversion from image to hex?As part of the DB Ghost evaluation there is a free scipter component for
scripting databases including data into insert statements. It handles image
and binary data by converting to hexidecimal and has a COM interface which
you can use and distribute freely.
http://www.dbghost.com
"Jacques Roumimper" wrote:

> I want to export data from my tables by generating insert statements,
> including data of type image. To avoid having to use textcopy.exe or
> textptr, I want to have the image data part of the insert-statements by
> converting the binary image data to hexadecimal strings. Also, the
> image data is larger then 8000 so a simple convert won't work. How do I
> do the conversion from image to hex?
>|||If you can use c# do this:
public static string BinToString(Byte[] binValue)
{
char[] hexCode =
{'0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F'};
StringBuilder sb = new StringBuilder();
foreach (byte b in binValue)
{
sb.Append(Convert.ToString(hexCode[b >> 4]));
sb.Append(Convert.ToString(hexCode[b & 0xF]));
}
return "0x" + sb.ToString();
}
I am not 100% sure if web data administrator
http://www.microsoft.com/downloads/...&displaylang=en
can script image fields. Worth a try!
Mathias|||I need to do this in SQL, what would be the Transact SQL equivalent of
your C# code?

CONVERT function use

I'll go right to the point:

I have a textbox in wich the user can seize a "money" value.

But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data.

I don't know how to handle this...

objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _
"(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _
"VALUES (@.fld_nom_piece, @.fld_quantite, @.fld_prix, @.fld_description, @.fld_num_appel)", objConn)
objCmd.Parameters.Add("@.fld_nom_piece", champsNomPiece.Text)
objCmd.Parameters.Add("@.fld_quantite", champsQuantite.Text)
objCmd.Parameters.Add("@.fld_prix", champsPrix.Text)
objCmd.Parameters.Add("@.fld_description", champsDescription.Text)
objCmd.Parameters.Add("@.fld_num_appel", champsNumAppel.Text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
afficherPieces()

How and where to do the conversion ? On the VALUES variable ?objCmd.Parameters.Add("@.fld_nom_piece", SqlDbType.Money)
objCmd.Parameters("@.fld_nom_piece").Value = System.Convert.ToDecimal(champsNomPiece.Text)|||Thanks ! It worked well.|||I have a similar problem in that I have a text box which holds a numeric value and when I try to insert the value into the numeric field in the SQL database I get the same error message.

I have tried the suggestion above but then I get another error message saying incorrect input.

Any idea's ??

Thanks

convert from hexadecimal to a decimal

We have a tableA with a varchar field whose contents are
hexadecimal. We want to insert these hexadecimal contents
from a varchar field into different table, tableB in
decimal format.
We tried to use Cast and convert functions to explicitly
convert into int field thinking it will give us right
decimal, which didn't work.
Example of what works:
Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
int) returns value of 42.
This doesn't work:
Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
int)
All the values coming from tableA are in '0x2A' form
because the field is defined as a varchar. We can set the
field name in tableB to whatever we want (int, decimal or
even varbinary)
Could someone please pointers / suggestions on how to
convert hexadecimal to int or even how to remove the
leading and trailing ' ?
Thanks in advance
Skip,
This is not too efficient, but it should do the trick. It doesn't
validate the input at all, either.
create function hexchar(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.n = 0
declare @.digits char(16)
set @.digits = '0123456789ABCDEF'
set @.b = substring(@.b,3,8)
while len(@.b) > 0 begin
set @.n = 16*@.n + charindex(substring(@.b,1,1),@.digits)-1
set @.b = substring(@.b,2,8)
end
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
-- Steve Kass
-- Drew University
-- Ref: 8EB8CE54-6E8E-47C1-93AB-35AB3F7C27F5
Skip wrote:

>We have a tableA with a varchar field whose contents are
>hexadecimal. We want to insert these hexadecimal contents
>from a varchar field into different table, tableB in
>decimal format.
>We tried to use Cast and convert functions to explicitly
>convert into int field thinking it will give us right
>decimal, which didn't work.
>
>Example of what works:
>Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
>int) returns value of 42.
>This doesn't work:
>Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
>int)
>All the values coming from tableA are in '0x2A' form
>because the field is defined as a varchar. We can set the
>field name in tableB to whatever we want (int, decimal or
>even varbinary)
>Could someone please pointers / suggestions on how to
>convert hexadecimal to int or even how to remove the
>leading and trailing ' ?
>Thanks in advance
>
>
|||On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very good, and how about this?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>How about using a numbers table to speed it up?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> declare @.n bigint
> set @.b = substring(@.b,3,8)
> set @.n = (select
>sum((charindex(left(right(@.b,n),1),'0123456789ABC DEF')-1)*POWER(16,(n-1)))
> from dbo.numbers
> where n between 1 and len(@.b))
> return
> case when @.n >= 0X80000000
> then @.n - 0x0100000000
> else @.n end
>end
>go
>
>Best, Hugo
>
|||On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:

>Very good, and how about this?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> return (
> select
>sum((charindex(right(left(@.b,N),1),'123456789ABCD EF'))*POWER(16.,len(@.b)-N))
> from numbers
> where n between 3 and len(@.b))
> - case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
> then 0x0100000000 else cast(0 as bigint) end
>end
>SK
Hi Steve,
Nice!
With the added advantage that it can be used inline in the query, so that
the overhad of calling a function is no longer incurred. (Though I would
comment it if I used it in a query <g>)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You mean this isn't self-documenting? ;)
select
'0x[89abcdef]' lower, '[0-9abcdef]' upper,
0x100000000 parsename, '123456789ABCDEF' power,
cast(0 as bigint) replicate, 16. stuff,
7 charindex, 3 rtrim
into ltrim
select
b, sum(charindex(right(left(b,N),1),power)
* power(stuff,len(b)-N))
- case when lower(b) like lower+replicate(upper,charindex)
then parsename else replicate end
from T, numbers, ltrim
where n between rtrim and len(b)
group by b, lower, upper, parsename, replicate, power, charindex
go
SK
Hugo Kornelis wrote:

>On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>Nice!
>With the added advantage that it can be used inline in the query, so that
>the overhad of calling a function is no longer incurred. (Though I would
>comment it if I used it in a query <g>)
>Best, Hugo
>
|||On Fri, 01 Oct 2004 18:10:12 -0400, Steve Kass wrote:

>You mean this isn't self-documenting? ;)
<snort>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi all,
when i run this query, it will result this :
Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
Invalid column name 'n'.
May i know the 'n' factor is ?

Quote:

Originally posted by Hugo Kornelis
On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

|||On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:

>hi all,
>when i run this query, it will result this :
>Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
>Invalid column name 'n'.
>May i know the 'n' factor is ?
Hi landung,
It's a column in the numbers table I used for this query.
If you don't have a numbers table yet, check out this link:
http://www.aspfaq.com/show.asp?id=2516
If you do have a numbers table, but with another column name, change the
query to reflect the names of your numbers table and column.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Don't forget that if you want something that can be put inline without
a UDF call, you can use something more like this:
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:
>
>
>Hi landung,
>It's a column in the numbers table I used for this query.
>If you don't have a numbers table yet, check out this link:
>http://www.aspfaq.com/show.asp?id=2516
>If you do have a numbers table, but with another column name, change the
>query to reflect the names of your numbers table and column.
>Best, Hugo
>

convert decimal value of persons height into feet/inches

given HeightInches decimal (18, 0))

and

INSERT INTO PatientVisits (PatientID,HeightInches)

select '1234-12', '68.5' -- would like to convert 68.5 to 5' 8 1/2"

how would I extract the value in a select statement of '68.5' to display in feet and inches rather than a decimal value?

Thank you!

Greg

If you put a question about using feet or inch data type, there is no answer.

But you can create a new data type with CLR User-Defined Types.

According this you can transform from inch to feet like this

select cast('68.5' as decimal)*0.08333

|||

Thank you Gigi.

I edited my question to be more clear I hope.

Greg

|||Maybe you can get the source code of converting inches to feet and inches from here.
Then adapt that code in a T-SQL function that have parameter 68.5(decimal) and return 5' 8 1/2 (a string)|||

Use the following code,

Code Snippet

Create function InchesToFeet(@.in float)

returns varchar(20)

as

Begin

declare @.feet varchar(20)

set @.feet= ''

select

@.feet = cast(floor(V) as varchar) + ' ft' +

case

when (@.in - floor(floor(V) * 1/(2.54 / 30.48))) = 0 then

''

else

' and '

+ cast((@.in - floor(floor(V) * 1/(2.54 / 30.48))) as varchar)

+ ' Inches' end

from

(

select

cast(@.in * (2.54 / 30.48) as varchar) as V

) as d;

return @.feet;

End

Go

select dbo.InchesToFeet(68.5)

|||

I would suggest that you use the front end code to do this, rather than SQL code. I am sure the function posted will work (well, I didn't test it, but I have faith it will probably work) but you can see the code is painful at best. In the client you should be able to use far eaiser to manage code, in the UI, which is made to display data for the user.