I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:
> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
DATATYPE PROBLEM(cross)
I have a column in my table BizdekiFiyat . The datatype = float length =8
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).
> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...
> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al
(to save money values).. It is impossible to change these attributes for
some reasons.
It has records like This
BizdekiFiyat
110
24
29.5
31.35
I use Vb.Net . I use ExecuteReader To select values from my db..
After first attemp
Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
IT returns
110
24
295
3135
Dim BizdekiFiyat As String
It returns right results.
110
24
29.5
31.35
There is a problem with decimal records when i want to evaluate this
values..
For example
Dim BizdekiFiyat As String
BizdekiFiyat = BizdekiFiyat * 1.05
It is supposed to be
29.5 * 1.05 =30.975
31.35*1.05=32.9175
but it returns
309,75
3291,75
How can i solve this problem ?"Savas Ates" <in da club> wrote in message
news:OEhAfpwLGHA.648@.TK2MSFTNGP14.phx.gbl...
>I have a column in my table BizdekiFiyat . The datatype = float length =8
>(to save money values).. It is impossible to change these attributes for
>some reasons.
>
> Dim BizdekiFiyat As Integer OR Dim BizdekiFiyat As Decimal
> IT returns
> 110
> 24
> 295
> 3135
Integer datatype will always truncate your decimal fraction values.
I've had data dimension problems trying to use the Decimal datatype for
holding (SQL) decimal data returned through parameters using MS's EntLib
DAAB. I resolved this by using .NET's Double datatype (though I'd prefer to
know why .NET's decimal gave me the problem in the first place).
> Dim BizdekiFiyat As String
> It returns right results.
Because the value is being represented and stored as a string (just like
typing into a textbox), not a numeric type, so...
> There is a problem with decimal records when i want to evaluate this
> values..
> For example
> Dim BizdekiFiyat As String
> BizdekiFiyat = BizdekiFiyat * 1.05
> How can i solve this problem ?
You're expecting .NET to intelligently convert your datatypes for you, which
it is valiantly trying to do. You should consider setting Option Strict on
(Tools | Options | Projects | VB Defaults) to prevent loose data typing and
late binding. You should strongly type your datatypes as a matter of god
practice. When you need to convert datatypes, dothis explicitely using
CType(sourceObj, targetType), or the shorthand versions such as Cint(value),
CDbl(value), etc.
As for your calculations: e.g. using Double to store your values, create a
function which you'll call when necessary to do your calculations:
private function MultiplyBizdekiFiyat(Byval origValue as double, Byval
MultiplyBy as double) As Double
'Perform the calculation
MultiplyBizdekiFiyat = origValue * MultiplyBy
'Return the value to the calling method.
return MultiplyBizdekiFiyat
end function
Hope that helps
Al
datatype problem
how can i transfer incoming data from flat file which would be a string to my sql table of column int...
i have a problem with datatype can i conver string to int how should i do it...new to it
please help!!
This is what the Data Conversion Task is for. U could also use the Derived Column Task. Place it between source and destination, convert your column there.
What more can I say?
Pipo1
|||Use a derived column transformation.This is one example of an expression you could use: (DT_I4)[Your_Column]sql
Datatype nvarchar(max) not accessible
Hi,
trying to input
create table T (c1 nvarchar(max));
in MS SQL Server Manangement Studio Express results in an error :
Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]
create table T (c1 nvarchar(4000));
is processed w/o errors.
I′ve installed SQL Server 2005 Express Ed. SP2
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft .NET Framework 2.0.50727.42
Betriebssystem 5.1.2600
Any ideas?
Thanks in advance
Werner
This command should work without problems, and did on my computer. Could you check the error log to see if there is more informaiton about the failure there?
Mike
|||Hi,
thanks for your reply. I think I found the reason.
Being a newcomer I accidently selected "SQL Server Everywhere" and generated a compact sdf-DB. This type of server doesn′t support nvarchar(max) (which is not mentioned in any documentation available in the net).
Setting up a new db (conversion of the sdf to mdf format is not possible (?)) fixed the problem.
btw, I can imagine using large datafields even on a PDA, so what′s the reason for not supporting this datatype (I do see a parallel to those guys of TI in the late 70s who invented the datetime format for PCs, saving 2 byte...)
Thanks anyway for your fast reaction and your help offered
Werner
Datatype mismatch in export
I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.
When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.
What do I do?
Thanks
As per the guidelines of Microsoft, the equivalent of 'VARCHAR' in Sql server is 'TEXT' in MS-Access. How are you exporting Sql server data to Access? Are you using SSIS? On a side note, equivalent of 'MEMO' is 'TEXT' in Sql Server. I wouldn't recommend you to change the data type in Sql Server to change to 'TEXT'.
Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.
http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true
DataType converting, char to float
I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.
Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..
Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0
/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/
--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)
--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.
The following post discusses this problem:
post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:
Dim InsertQuery As String
Set oConn = New Connection
Set oRec = New Recordset
oRec.Open InsertQuery, oConn
is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?
-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"
Is a test-query I use
And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.
create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.
Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..
Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0
/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/
--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)
--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.
The following post discusses this problem:
post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:
Dim InsertQuery As String
Set oConn = New Connection
Set oRec = New Recordset
oRec.Open InsertQuery, oConn
is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?
-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"
Is a test-query I use
And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.
create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.
Datatype Conversion Problem.
Hi ALL!
I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.
--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql
I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.
--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql
Datatype Conversion Problem.
Hi ALL!
I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?
Regards,
hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.
If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.
You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.
I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).
When i try to change its datatype to binary by trying following code
ALTER TABLE Table1 Alter Column [Name] Binary(5000)
It gives following error.
" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "
So, how can i change the datatype of this column ?
Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?
Regards,
hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.
If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.
You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.
Datatype change INT to BIGINT on a large table
Hi folks,
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql
Datatype <uniqueidentifier>
When creating table, now we usually include the colunm(Id) whose datatype is uniqueidentifier.
My first question is why choosing datatype as uniqueidentifier instead of int[identity(1,1)] if no replication needed.
My 2nd question is for the uniqueidentifier column, which index is appropriate for the column, cluster or non-cluster?
And the third question is how to set the default for the uniqueidentifier.which function is better, NewID() or NEWSEQUENTIALID()?
Thanks in advance.
uniqueidentifier is a type of GUID which means that it is unique. It is larger than int style fields and unless you are using NEWSEQUENTIALID() the new values are going to be random (so don't cluster on it unless you use that - or your insertion point will be random in your index). Even with NEWSEQUENTIALID() the values will not normally be consecutive, just increasing.
Generally I would use an identity column for a single table with no multi-source issues (uniqueidentifier is good if you are taking records from various places and merging them). It is smaller (4bytes for int, 8bytes for bigint as opposed to 16bytes for uniqueidentifier), and it is much easier to type in a SQL statement. It is also easier for the processor and memory access to deal with as a value.
As noted it is not a good idea to cluster on an random uniqueidentifier. Even if it increasing why do you want to cluster on it. Generally you do not query by a range of GUIDs. As clustering determines the grouping of the records on disk it is normally better to cluster by something that matches your common querying - to minimise the amount of disk access to return the record. Single record access (if you do retrieve by GUID) is not really affected by clustering as you are only after one record on one datapage.
|||Here is some additional information that will help you resolve your question. (Generally, avoid GUIDs in situations where Replication is not involved.
GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599
GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx
GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1
GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283
|||
1. Identity is better to use if you never planned for replication. It is one of the biggest datatype (16 Bytes) in the sql server. If you are going to use to identitfy your row then better use it Identity (int/smallint/bigint,1,1) is more enough. But GUID allocates 16 Bytes on each row.
2. Creating a index on GUID is really bad idea unless its required. Since the datatype is huge the indexes will occupy more space and the manipulation also slow.
3. Setting default value ColumnName UniqueIdentifier Default NEWID()
Tuesday, March 27, 2012
Datatime order by Problem
Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:
F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------
To do this Im using the follow syntax :
SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))
But it gives the follow message erro trying to select data :
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?
Thansk for attention.
Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:
F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------
To do this Im using the follow syntax :
SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))
But it gives the follow message erro trying to select data :
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?
Thansk for attention.
Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql
datatable from gridview
hi all
the usual way to bid a gridview is to data soursce
is there a way to do the folowing , creat a data table from the gridview shown valus " currunt page "
thanks
Wops , this is the solution
http://forums.asp.net/thread/1362057.aspx
DataTable and stored Procedure insertion
hi,
How can I insert a DataTable as a whole into the Database (into an existing table) using a stored procedure?
can I just send the DataTable as a parameter to the procedure?
how can I do it?
thanx
Hello,
You need to iterate through the Rows (DataRows) of that DataTable and to insert them one by one to the database.
HTH
Regads
Sunday, March 25, 2012
Datasource
How can I synchronize a table inside a list if both have different
datasources?
I need to synchronize the table by the list details grouping expression. If
I use the table filter, the table fieldID is always aggregated when inside
a list with a different datasource and it results in error.
and
if I add a field to a table from another datasource it is always in the form
Sum(Fields!Field.Value, "datasource")
which is the total sum of the values of the datasource even though the
datasource is grouped on the same field the table is grouped
how can I break this aggregate by the same field the table is grouped so
that I did not get the always the same total sum of all the values of that
aditional datasource in all rows of the table?
Thank you in advance
Martin Suchy
CBSolutionsWithin a data region you cannot use more than one dataset. You have to write
a single query that supplies data to the list and to the table. In most
cases, this involves a JOIN SQL query.
HTH
Charles Kangai, MCT, MCDBA
"Martin" wrote:
> How can I synchronize a table inside a list if both have different
> datasources?
> I need to synchronize the table by the list details grouping expression. If
> I use the table filter, the table fieldID is always aggregated when inside
> a list with a different datasource and it results in error.
> and
> if I add a field to a table from another datasource it is always in the form
> Sum(Fields!Field.Value, "datasource")
> which is the total sum of the values of the datasource even though the
> datasource is grouped on the same field the table is grouped
> how can I break this aggregate by the same field the table is grouped so
> that I did not get the always the same total sum of all the values of that
> aditional datasource in all rows of the table?
> Thank you in advance
> Martin Suchy
> CBSolutions
datasources?
I need to synchronize the table by the list details grouping expression. If
I use the table filter, the table fieldID is always aggregated when inside
a list with a different datasource and it results in error.
and
if I add a field to a table from another datasource it is always in the form
Sum(Fields!Field.Value, "datasource")
which is the total sum of the values of the datasource even though the
datasource is grouped on the same field the table is grouped
how can I break this aggregate by the same field the table is grouped so
that I did not get the always the same total sum of all the values of that
aditional datasource in all rows of the table?
Thank you in advance
Martin Suchy
CBSolutionsWithin a data region you cannot use more than one dataset. You have to write
a single query that supplies data to the list and to the table. In most
cases, this involves a JOIN SQL query.
HTH
Charles Kangai, MCT, MCDBA
"Martin" wrote:
> How can I synchronize a table inside a list if both have different
> datasources?
> I need to synchronize the table by the list details grouping expression. If
> I use the table filter, the table fieldID is always aggregated when inside
> a list with a different datasource and it results in error.
> and
> if I add a field to a table from another datasource it is always in the form
> Sum(Fields!Field.Value, "datasource")
> which is the total sum of the values of the datasource even though the
> datasource is grouped on the same field the table is grouped
> how can I break this aggregate by the same field the table is grouped so
> that I did not get the always the same total sum of all the values of that
> aditional datasource in all rows of the table?
> Thank you in advance
> Martin Suchy
> CBSolutions
Datasets and Text Boxes
I have a table referring to a dataset called ds1 . I have txtbx1,2,3 in
the table.
all text boxes are now referred to different ds1 columns. I created
another Dataset called DS2 (becuase of business needs). Now I wanted
one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
column although the whole table is under DS1 (under which these
txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
Please let me know the best way to accomplish this.
Also wanted to see if we can do some logical conditions in the txt
boxes.
Anyyy Help would be greatly appreciated !!!!!!!!!!
ThanksHi,
Try like this. e.g.
=(Fields!emp.Value, "DS2")
Amarnath
"Sen" wrote:
> I have a table referring to a dataset called ds1 . I have txtbx1,2,3 in
> the table.
> all text boxes are now referred to different ds1 columns. I created
> another Dataset called DS2 (becuase of business needs). Now I wanted
> one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
> column although the whole table is under DS1 (under which these
> txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
> Please let me know the best way to accomplish this.
> Also wanted to see if we can do some logical conditions in the txt
> boxes.
> Anyyy Help would be greatly appreciated !!!!!!!!!!
> Thanks
>|||When I do that the Fields!emp.Value gets repeated - just because the
coverig table dataset area is DS1.
the table.
all text boxes are now referred to different ds1 columns. I created
another Dataset called DS2 (becuase of business needs). Now I wanted
one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
column although the whole table is under DS1 (under which these
txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
Please let me know the best way to accomplish this.
Also wanted to see if we can do some logical conditions in the txt
boxes.
Anyyy Help would be greatly appreciated !!!!!!!!!!
ThanksHi,
Try like this. e.g.
=(Fields!emp.Value, "DS2")
Amarnath
"Sen" wrote:
> I have a table referring to a dataset called ds1 . I have txtbx1,2,3 in
> the table.
> all text boxes are now referred to different ds1 columns. I created
> another Dataset called DS2 (becuase of business needs). Now I wanted
> one Text box txtbx1 refer to ds2 column and the txtbx2 refer to DS1
> column although the whole table is under DS1 (under which these
> txtboxes are) - when I do that the txtbx1 and ds2 column gets repeated.
> Please let me know the best way to accomplish this.
> Also wanted to see if we can do some logical conditions in the txt
> boxes.
> Anyyy Help would be greatly appreciated !!!!!!!!!!
> Thanks
>|||When I do that the Fields!emp.Value gets repeated - just because the
coverig table dataset area is DS1.
Datasets and SQL Injection
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
Hey,
Welcome to the forum. The key is, do you have any dataset queries that take user input and passes it to a SQL query directly? Then you may want to beware if that is true. I like the use of SPs because SQL Server will cache them for increased performance.
In addition, you should be able to use @.Parameters within a SQL query, and pass in the values as parameters, which is safer than embedding the response directly into the SQL query.
|||
wendycarpenter@.polk-county.net:
I am wondering if there is any 'built-in' functions in the C files for sql injection prevention?
Use parameterized queries!
http://aspnet.4guysfromrolla.com/articles/030106-1.aspx
|||I am using parameterized queries - I've included a sample below. SQL Injection prevention has been "in the air" a lot lately and I guess I want to make sure that I am doing what I need to do to keep my customers' data safe.
INSERT INTO [Case] ([cseYear], [cseNumber], [cseStatus], [csePartI], [csePartII], [cseDestruction])
VALUES (@.cseYear, @.cseNumber, @.cseStatus, @.csePartI, @.csePartII, @.cseDestruction);
select SCOPE_IDENTITY()
|||
I appreciate the link. Maybe I wasn't specific enough. I am using the built in dataset (.xsd), so I am not creating any sql statement within my code - I strictly call the method within the datatable of the table adapter within the dataset. Most of the work is done within the auto generated code within Visual Studio 2005.
|||Hey,
OK, which used the parameter approach I believe. That should be OK; I've seen several projects where Adapters were used even with secure info. Note that table adapters can also use stored procedures in case you want to be sure.
sql
DataSet Xml DateTime incompatible with Sql 2000
This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:
> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was
and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:
> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:
> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was
and trying to put the logic/function in the WITH clause - nowonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:
> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>
DataSet Xml DateTime incompatible with Sql 2000
This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
>
|||Thanks for the quick response. It was exactly what I needed to know. I have
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since this
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:
> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first, if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>
|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
|||I was confused and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, but
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:
> Here is a sample. Note that you can inline the TSQL function into the select
> clause directly. You may also want to add some more complex logic to adjust
> the date time based on the timezone if you expect more than one timezone to
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
>
|||Thanks for the quick response. It was exactly what I needed to know. I have
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since this
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:
> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first, if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>
|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
|||I was confused and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, but
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:
> Here is a sample. Note that you can inline the TSQL function into the select
> clause directly. You may also want to add some more complex logic to adjust
> the date time based on the timezone if you expect more than one timezone to
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>
Thursday, March 22, 2012
dataset to sql server
How to transfer data in a table in dataset to a table in sql server ?
i.e. I want to transfer the table values in excel to a table in sql server. I have populated the excel table in a dataset. I just want to know how to transfer from dataset to sql server.
Thanks
You can use the INTO clause to move the data into SQL Server because it creates a new table on destination or use the code below to load the data into SQL Server then load that table into a dataset. The reason is Excel since XP/2003 can use sql statements. Hope this helps.
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
http://msdn2.microsoft.com/en-us/library/ms188029.aspx
|||I cant understand ur code. Kindly explain it.
I want to export the data in excel to sql server table. I have fetched the data from excel and stored in dataset. Then i want to export it to sql server. How to do that?
|||That code is to insert Excel into SQL Server table before you put the table into a dataset, if you don't want that then use the link and create a SELECT INTO statement it create a table for you and now that I think about it you can create a SQL Server Table with ExecuteNonQuery in ADO.NET run a search in the forums you should find some links with code. Hope this helps.
DataSet table as DataFlow Destination Component
Is it possible to use a datatable as destination component in dataflow ?
Sample code Fr MS:
Create the source component.
IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.ComponentClassID = WHAT ? for dataset Table ?
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
thanks in advance
PatrickI saw that the datareader can be used as Destination.
So, my "ask for" is closing.
Thanks
Patricksql
Subscribe to:
Posts (Atom)