connecting to SSAS: converting

Showing posts with label converting. Show all posts

Monday, March 26, 2012

Problem in Converting Task to DataPumpTask2

Hai All,

I tried to convert a Task to DataPumpTask2.

But i got an Error

An unhandled exception of type 'System.InvalidCastException' occurred

QueryInterface for interface DTS.CustomTask failed

I installed SP3 for SqlServer in my Desktop and in the Server itself.

But i faced the same problem again.

If anyone have idea please enligten me .

Thanx,

Karthik.A

Hai All,

I found the Solution.

What we have to do is,Just install the SP3 for desktop SQL2KDeskSP3.exe in ur Machine.

While installation, it asks for "sa" Password.

Just open the setup.ini in the Extracted folder sql2ksp3/MSDE/setup.ini

and add PWD sa = <Password> below the [options].

then it starts installation.Once it's completed .

Compile ur code.it runs successfully.

Thanx,

Karthik.A

Saturday, February 25, 2012

Problem Converting XML to table in SQL server

Hi There,
I'm new to the .Net environment, and I am currently trying to convert an xml document and its appropriate schema, to a dataset then with this Dataset I would like to load it into the database. This XML file is generated from the Access 2000 format. Then sent over the internet where it is received by an HttpFileCollection object, then the schema and xml file are loaded into a dataset.
I have created a new SQL database in which I imported the table required from Access, so in theory the table that the xml is generated from should be the same as the table that the xml is destined for.
I am getting the data loaded into the dataset however, when I try to execute the data adapter update command

oda.Update(myDS, "MSGLOBAL") it is throwing an Exception, in this exception the message is telling me that there is a problem near the key word 'on'.
Now I have added a watch window to monitor the sql statements, now the oda has an insert statement generated from a query builder, however this same query builder will not create either an update or delete command.
I hope this is a good enough explanation to get a few answers, or a possible example, it seems to be a topic that is quite difficult to research.
Thank you, and if you require more info please indicate this.
Damon

Http://www.ezywiz.biz

Access 2000 is barely relational and XML that old is not relational either so use DTS to move the data to SQL Server. Try the link below for DTS samples. Hope this helps.
http://www.sqldts.com|||Thanks Gary for your quick response.
I'll have a look at the link with the SQL examples.
The dataset isn't coming directly from Access 2000 though, it is coming from the XML file extracted from the Access database on the client side and received at the server. Is DTS still an appropriate option.
Thanks...Damon|||You can move the data to SQL Server with DTS and get the dataset from SQL Server. Hope this helps.|||

Choices aplenty.

1. You can code your own inserts statement rather than using the command builder

2. Use SQL/XML updategrams

3. Use SQL OpenXML and send the XML in

4. Process the XML and send in straight forward insert commands

5. Process the dataset and send in straight forward insert command

Do you really need to use a dataset?

Problem converting VS2003 code to VS2005

Hi:

I am trying to convert my VS2003 project to VS2005 beta 2. The actual conversion has gone fine, but some code that was working fine under to old IDE is now giving me an exception when run under the VS2005 generated code.

The code is:

using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GestionNET00001;Data Source=localhost")

{
...
}

And the error I'm getting is:
{"The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception."}

Has anyone any idea why this is happening?

TIA,
MartinH.

You've got invalid parenthesis in that code sample you know?

Anyway, have you tried a simple declaration instead of using the using statement?
i.e.

SqlConnection conn;
conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GestionNET00001;Data Source=localhost");

Does it fail on either of those lines if it's written out like that?
If not, then merge them together into the same statement and see if that works.
If it does, try putting them back into the using statement and see what happens...|||

plenderj wrote:
You've got invalid parenthesis in that code sample you know?

Yes that was a typo, sorry, but in my code it was correct and compiled correctly.

plenderj wrote:
Anyway, have you tried a simple declaration instead of using the using statement?
i.e.

SqlConnection conn;
conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GestionNET00001;Data Source=localhost");
Does it fail on either of those lines if it's written out like that?

I have since tried to declare and instantiate the connection without a connection string, but still have the same problem. I have also tried to eliminate the 'using' block, still the same.
There seems to be a problem at runtime, but I have checked the 'references' and Syatem.Data is included, I don't think I need anything else for SqlConnection.
Finally, when I installed VS2005 b2, the SqlConnection, SqlCommand and other family members were not installed on the tool pallete, I have to install them manuallly. Is this standard procedure for VS2005? If not this may be related to the problem I have.
TIA,
MartinH.|||I had a problem with VS2005 not automatically including the System.data namespace references for me to use and had to manually add it to the project properties.

Try also including the System.Data.SqlClient namespace|||Just for the record, I have found the solution to the afore mentioned problem.
It was 2 fold. First, I was using the Microsoft Enterprise Library for January 2005, and once I removed this from my project it worked fine. Maybe the June update will work okay, I will have to try it.
Secondly, I use Developer Express's XPO and had the 'trace' feature enabled (within the App.config file). Turning this option off also solved the problem.
I have tried each option individually and they both cause the problem to reappear.
HTH,
MartinH.|||I am having the same issue. The designer generated code is throwing the same error from the InitConnection function of one of my datasets. This only happens when I have a listener configured in the app.config file. If I comment out the Systems.Diagnotics section of the config file, I no longer get the error. The highlighted line below is throwing the error.

This seems to be a bug in how the config file is being processed.

Private Sub InitConnection()

Me.m_connection = New System.Data.SqlClient.SqlConnection

Me.m_connection.ConnectionString = DnD.Settings.Default.DnDConnectionString

End Sub

|||It's not a bug -- the confusing thing was where the error was thrown. Drilling into the exception made it clear that I had a typo in the diagnostics section of the config file.

Problem converting UTC dates in dts

Can anyone help me convert UTC dates in a dts package? I have a db I need to transform, but all the dates are in utc format. Is there a function I can use in an ActiveX transformation script to convert, say, 38166, to a valid datetime value? There seems to be almost no documentation in using utc in SQL.How is that a UTC time?

http://www.dxing.com/utcgmt.htm|||I'll try...but for the record i dont think 38166 is a valid UTC date.
Please correct me if i'm wrong.

here is what i thougght UTC date looked like:

2004-07-01T16:15:30

SQL server CAST and CONVERT will handle this for you, by default.|||But your example does somewhat resemble a Julian date...|||But your example does somewhat resemble a Julian date...
1938?

or 2038?

But yeah it looks more julian..|||My vote is for 2008, since the Unix epoch is based on 1970.

-PatP|||I'll go with thta...(since I wrote a udf for that one once)...god damn toolbox...got to clean this damn thing out...

Wait a minute...|||i bet there'll be a party or 2 ~3:15 on Jan 19 2038

the end of the world as we know it.

:D|||you've got a julian date converting UDF?

Oooooo...i've wanted to do that but never had a project that required it.

Care to share??
:)|||I wrote one ages ago for military style Julian dates, and just posted it a day or two ago. Check here (http://www.dbforums.com/t1003180.html) for details.

-PatP|||thanks much.

sorry for the thread hijack chunky,
did you get a decent answer for the original post?

perhaps you could post an example of the date string you are dealing with, if not?

Problem converting to a date...

I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
Try:
select
convert (datetime, '20040301', 112)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
I have a field in a table that contains date data in the following format:
20040301
what is the best, low impact method for converting this to a useable date
field?
|||Populate a datetime column with the data...
ALTER TABLE YourTable ADD YourDatetimeCol DATETIME
GO
UPDATE YourTable SET YourDateTimeCol=CONVERT(DATETIME, YourNonDateTimeCol)
GO
Optionally, remove the old column, make the new column non-nullable, etc...
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>
|||hi atley,
if the format of datetime string is yyyymmdd then just run following query:
select convert(datetime,'20040301') dt
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi,
Have a look into the below code,
declare @.col1 varchar(20)
declare @.dt datetime
set @.col1 ='20040301'
select @.dt=convert(datetime,@.col1)
select @.dt
Tahnks
Hari
MCDBA
"Atley" <atley_1@.homtmail.com> wrote in message
news:#MwZKCoEEHA.1032@.TK2MSFTNGP09.phx.gbl...
> I have a field in a table that contains date data in the following
format:
> 20040301
> what is the best, low impact method for converting this to a useable date
> field?
>
>

problem converting string to integer

Hi, I need to convert the values entered into textboxes by the users before I can insert into db.

have tried the following.

Dim eventnum As string 'tried using integer makes no difference what i declare the variable as

eventnum = Convert.ToInt32(txteventnum.Text)

This value needs to be inserted into the field event_number wich is datatype int

I get the following error message when I try to insert.

Conversion failed when converting the varchar value 'eventnum' to data type int.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.

Source Error:

Line 62:
Line 63: ' Execute(query)
Line 64: myCommand.ExecuteNonQuery()
Line 65:
Line 66: 'Close the connection

Source File: C:\Inetpub\loans\MemberPages\Request.aspx.vb Line: 64

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
 Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\MemberPages\Request.aspx.vb:64
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

based on where your error is occurring, i suspect that the value from the textbox was successfully converted to an integer. This leads me to think that there's something wrong with how your query is constructed. Please post the code for constructing the query.

|||

thanks for the reply here is the code for query.

'Connection String value
Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString

'Create a SqlConnection instance
Using myConnection As New SqlConnection(conn)
myConnection.Open()

' Specify the SQL query
Const sql As String = "insert into requests ( [User_Name], [NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('username','nhi','eventnum','accnum','reqstdate' )"
', [Required_Date], [NHI], [Event_Number], [ACC_Number], [Request_Date]
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)

' Execute(query)
myCommand.ExecuteNonQuery()

'Close the connection
myConnection.Close()

End Using

|||

resolved it was the query string

was

Const sql As String = "insert into requests ( [User_Name], [NHI],[Event_Number], [ACC_Number], [Request_Date]) values('username','nhi','eventnum','accnum','reqstdate' )"

now

Dim sql As String = "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) " & _
"values ('" & username & "','" & reqrddate & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"

But thanks for replying

|||

glad you're all set.

But, please read up onSQL Injection Attacks
A parameterized query helps protect from sql injection and resolves issues related to quoting.
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

Monday, February 20, 2012

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

Problem converting rows to a string

I try to accomplish the following:

I have two tables which are connected via a third table (N:N
relationship):

Table 1 "Locations"
LocationID (Primary Key)

Table 2 "Specialists"
SpecialistID (Primary Key)
Name (varchar)

Table 3 "SpecialistLocations"
SpecialistID (Foreign Key)
LocationID (Foreign Key)
(both together are the primary key for this table)

Issuing the following command

SELECT
L.LocationID , S.[Name]
FROM
Locations AS L
LEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationID
LEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistID

results in the following table:

LocationID | Name
1Specialist 1
1Specialist 2
2Specialist 3
2Specialist 4
3Specialist 1
4Specialist 4

Now my problem: I would like to have the following output:

LocationID | Names
1Specialist 1, Specialist 2
2Specialist 3, Specialist 4
3Specialist 1
4Specialist 4

...which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

Thank you very much,
Dennis(dnsstaiger@.gmx.net) writes:
> Now my problem: I would like to have the following output:
> LocationID | Names
> 1 Specialist 1, Specialist 2
> 2 Specialist 3, Specialist 4
> 3 Specialist 1
> 4 Specialist 4
> ...which is grouping by LocationID and concatenating the specialist
> names.
> Any idea on how to do this?

This is one of the rare cases where you need to set up a cursor and
iterate. In SQL 2000 there is no defined way to do this. (There is
a shortcut, but it relies on undefined behaviour, so I don't recommend it.)

In SQL 2005, currently in beta, the story is different. There you
actually have a way to this in a set-based statement, although the
syntax is somewhat bewildering. (It's actually a by-product, of all
the XML stuff they thrown in.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.|||pb648174 (google@.webpaul.net) writes:
> I typically do things like this in my application code, i.e. while the
> locationid is the same, keep tacking values onto the other column's
> display in a comma delim format.

Yes, that is also a very common advice. But people insists on asking
about how doing this in SQL, that I've given up telling them to use
application code. (And sometimes the application is not any more
sophisticated than Query Analyzer.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.|||pb648174 (google@.webpaul.net) writes:
> Can you post the SQL 2005 code that will do this? I've been hoping SQL
> 2005 would have an aggregate function for strings that would turn it
> into a delimited string.

Sure, here it is:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

This gives you an output like:

ALFKI 10643,10692,10702,10835,10952,11011
ANATR 10308,10625,10759,10926
ANTON 10365,10507,10535,10573,10677,10682,10856

Now, I did definitely come with this on my own, but I got it from one
of the SQL Server developers.

The part that produces the comma separated list, is the text() function,
which is activated by the XML PATH('') at the bottom. The real point
of text() is probably not to produce a comma separated list, but it's
possible to do it.

Then then comma-separated list is combined with Customers through
CROSS APPLY. APPLY is another operator I have not fully digested
yet, but you use it when you want to call a table-valued functions
with parameters from other columns in the query; something you can't
do in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the strings look like? What format? Also,
you might want to check out: http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column>) = 0
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column>) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> > How far in do you get before this error is thrown? Have you check ALL
the
> > rows for the data type. If one row is wrong then the whole process is
> rolled
> > back. What are you using to do the conversion?
> >
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> > How many rows do you have?
> >
> theres 1144 rows in total
>|||select rowid, convert(datetime, column1) from table1 order by rowid
something similar to that should get you the row or rows that have bad data.
mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine|||mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine
select rowid, convert(datetime, column1) from table1 order by rowid
if you have bad data you'll get an error with that select and then you can
easily track down the row with the bad data. "basically dates" is not good
enough for a data type conversion. sorry if i double posted this.|||This only works if you have a column called "ROWID" I do not believe SQL
server has a rowid column inherent to a table there is a ROWGUIDCOL type but
only if you build your table with it originally.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"ch" <ch@.dontemailme.com> wrote in message
news:40B251FC.A1AC90BF@.dontemailme.com...
> mark wrote:
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > It definitely seems like you have invalid data in there. How does the
> > strings look like? What format? Also,
> > > you might want to check out:
> > http://www.karaszi.com/sqlserver/info_datetime.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> >
> > the strings look like
> > 01/01/1900
> > 01/17/2003
> > etc (ie they are basically dates) - ive checked the data and its fine
> select rowid, convert(datetime, column1) from table1 order by rowid
> if you have bad data you'll get an error with that select and then you can
> easily track down the row with the bad data. "basically dates" is not
good
> enough for a data type conversion. sorry if i double posted this.
>
>|||If Query Analyzer says your data is good, but you're having problems in
Enterprise Manager, try using Query Analyzer to change the datatype using
ALTER TABLE. EM might be doing something else behind the scene.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:BXosc.56$Vp5.20@.newsfe2-win...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> > Hi Mark
> >
> > The data may seem fine when you look at it, but SQL Server might
disagree.
> > You can run have SQL Server inspect the data with the ISDATE( ) function
> to
> > see what values it is not happy with.
> >
> > SELECT <nvarchar column>
> > FROM <mytable>
> > WHERE ISDATE(<nvarchar column>) = 0
> i tried that and it returned 0 results
> mark
>
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:O$5HGtaQEHA.2100@.TK2MSFTNGP11.phx.gbl...
> Can you post a zipped CSV of the data in the column?
>
hi thanks for the help, in the end i exported the data to access (no
problems) converted the field types in access
dropped the data from the sql table - and changed the field types and
re-imported back in - without any hangups
or errors - odd but still its done now!
mark|||select rowid, convert(datetime, column1) from table1 order by rowid
it also only works if you have a table named table1 and that table contains at
least two columns, one column named rowid and another column named column1.
Andrew Madsen wrote:
> This only works if you have a column called "ROWID" I do not believe SQL
> server has a rowid column inherent to a table there is a ROWGUIDCOL type but
> only if you build your table with it originally.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B251FC.A1AC90BF@.dontemailme.com...
> > mark wrote:
> >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > > It definitely seems like you have invalid data in there. How does the
> > > strings look like? What format? Also,
> > > > you might want to check out:
> > > http://www.karaszi.com/sqlserver/info_datetime.asp
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > >
> > > the strings look like
> > > 01/01/1900
> > > 01/17/2003
> > > etc (ie they are basically dates) - ive checked the data and its fine
> >
> > select rowid, convert(datetime, column1) from table1 order by rowid
> > if you have bad data you'll get an error with that select and then you can
> > easily track down the row with the bad data. "basically dates" is not
> good
> > enough for a data type conversion. sorry if i double posted this.
> >
> >
> >|||h
I been in same position once, my date format in string was dd/MM/yyyy
to change the date format you must use convert(datetime,'mm/dd/yyyy') if you change the column format from nvarchar to datetime it will not work. what you should to is create another column with datetime format while your current column is in use. then run a procedure to pick date from the nvarchar date column, use convert and save it into column with datetime format.
Test your data
If everything is fine, backup your table and delete column with nvarchar format
Test your application or modules using that table

Problem converting military time from CHAR column

Hi all,

I have a time column in CHAR(4) format. The contents are stored as 'military time':

1800

1830

2130

I tried using various functions, but was not able to convert to standard time.

I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:

2006-05-31 06:30 PM

Any help would greatly be appreciated.

Thanks.

- gshaf

Your military time conversion wont work because you dont have a colon separating your hours and minutes.

Try this statement:
~~PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)~~

Try this one instead, this will add a zero to your hour:

IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END

Which prints out
2006-05-31 09:30PM

Your time column is going to need that colon. Otherwise your military times will not be output properly.

|||

Worked! Just needed to correct the formatting.

Thanks so much Elliot!

- gshaf

problem converting float into nvarchar

I have a problem converting a float filed into nvarchar.
select cast(sold as varchar(50))
where sold=431597.15 results in 431597 and is ignoring always my decimals.
Do you have any idea how to fix this?
tyHi

The below works for me:
DECLARE @.Sold AS Float
SELECT @.Sold = 431597.15
SELECT CAST(@.Sold AS VarChar(50)) --Wrong
SELECT CAST(CAST(@.Sold AS Decimal(8, 2)) AS VarChar(50))--Correct

HTH

Problem converting decimal values

Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.

Any help on this ? tnks in advance
Anyone ? It's a very urgent problem, my deadline is approaching and this problem remains. Do i have to replace the points by dot's ? It's the only solution ?
|||I don't know anything about locales, but I guess if I had this problem I'd read the amounts in as strings and then use a Derived Column component to do the necessary string manipulations and data conversion. Hopefully you don't have a lot of them or you have a convenient asynchronous component (like a Union) where you can drop out the string artifacts. Otherwise, you can do the transformation/conversion and drop the artifacts at the same time using an asynchronous script.
|||

What locale have you tried to use? Where did you set it? English (United States) should be available on your machine.

Thanks.

|||First of all tnks for your answers. Well the problem was indeed very simple, i was setting English as the locale and not English (United States), that's what i call a stupid error ;-). Anyway thank you very much for the help

Problem converting Crystal Report to RS2005

I've run into a problem while converting an old Crystal report into a
RS2005 version. The report in question gets a dataset from a SQL
stored procedure. The stored procedure passes details on paper
production by day, machine and grade. The report groups the data by
grade and then by machine. In the grade group footer, I perform a
calculation on the data that is as follows:
=sum(GradePeriod.value)*avg(SBK.value). This works the same as in the
crystal report. However, in the crystal report, they declare a global
variable, that basically creates a running sum of this calculation.
So in the Machine footer, it is a sum of all of the
sum(GradePeriod.value)*avg(SBK.value). Now if I try in RS to sum the
values in the footer by using
=sum(sum(GradePeriod.value)*avg(SBK.value)) I get an error that says
you cannot use an aggregate of an aggregate. So my question is, how
do I replicate what is going on in the Crystal Report in RS?In RS you need to give some thing like this in the header or footer you cant
give directly. Try this. (you should use "reportitems")
=Sum(ReportItems!Gradeperiod.Value)
Amarnath, MCTS
"dachrist" wrote:
> I've run into a problem while converting an old Crystal report into a
> RS2005 version. The report in question gets a dataset from a SQL
> stored procedure. The stored procedure passes details on paper
> production by day, machine and grade. The report groups the data by
> grade and then by machine. In the grade group footer, I perform a
> calculation on the data that is as follows:
> =sum(GradePeriod.value)*avg(SBK.value). This works the same as in the
> crystal report. However, in the crystal report, they declare a global
> variable, that basically creates a running sum of this calculation.
> So in the Machine footer, it is a sum of all of the
> sum(GradePeriod.value)*avg(SBK.value). Now if I try in RS to sum the
> values in the footer by using
> =sum(sum(GradePeriod.value)*avg(SBK.value)) I get an error that says
> you cannot use an aggregate of an aggregate. So my question is, how
> do I replicate what is going on in the Crystal Report in RS?
>|||It wants me to put the sum(ReportItems!GradePeriod.value) in the Page
Footer. I need to have these sums at each group footer, because I
cannot have one paper machine per page.
Dave
On Mar 15, 2:33 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> In RS you need to give some thing like this in the header or footer you cant
> give directly. Try this. (you should use "reportitems")
> =Sum(ReportItems!Gradeperiod.Value)
> Amarnath, MCTS
>
> "dachrist" wrote:
> > I've run into a problem while converting an old Crystal report into a
> > RS2005 version. The report in question gets a dataset from a SQL
> > stored procedure. The stored procedure passes details on paper
> > production by day, machine and grade. The report groups the data by
> > grade and then by machine. In the grade group footer, I perform a
> > calculation on the data that is as follows:
> > =sum(GradePeriod.value)*avg(SBK.value). This works the same as in the
> > crystal report. However, in the crystal report, they declare a global
> > variable, that basically creates a running sum of this calculation.
> > So in the Machine footer, it is a sum of all of the
> > sum(GradePeriod.value)*avg(SBK.value). Now if I try in RS to sum the
> > values in the footer by using
> > =sum(sum(GradePeriod.value)*avg(SBK.value)) I get an error that says
> > you cannot use an aggregate of an aggregate. So my question is, how
> > do I replicate what is going on in the Crystal Report in RS... Hide quoted text -
> - Show quoted text -

Problem converting CHAR to DATETIME

hello all,

I am having a problem calculating the difference, in days, between two dates, STARTDATE and ENDDATE. The data is stored in the database as char(8), formatted YYYYMMDD. "Null" values are stored as '00000000'. When I try to use DATEDIFF an exception is thrown: "the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

How can I fix this, and a get a result even if STARTDATE or ENDDATE is '00000000'? Changing the format of the stored data is not an option.

Thanks for any assistance.

Mike

CREATE TABLE dbo.DATETIME1 (
ID1 int,
STARTDATE char(8),
ENDDATE char(8)
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
1, '20070105', '20070108'
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
2, '20070105', '00000000'
)
Select * from DATETIME1
Select DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) ) as Difference
from DATETIME1
WHERE ID1 = 1How about a case statement to check for the '00000000'? Or an isdate check?|||Ahhhh, good idea...can you help with the syntax of the CASE statement in SQA? I've done them in VB.NET, but not directly in SQL.

Thanks|||add this to your test code replacing your datediff select.

select STARTDATE, ENDDATE,
CASE
WHEN isdate(ENDDATE) = 0
THEN 0
ELSE
DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) )
END
from DATETIME1 as DaysDifference|||THANKS! I was doing something similar, but couldn't quite get it to work! :D|||You could also try the following:
ISNULL(DATEDIFF(d, CONVERT(DATETIME, STARTDATE, 112), CONVERT(DATETIME, NULLIF(ENDDATE, '00000000'), 112), 0)
Btw: explicit conversions are always better than implicit ones:)

Problem converting C application from using DBLIB bcp to using ODBC bcp.

I have an application that was converted from using DBLIB bcp to using ODBC bcp. All other field/data types bind are updated correctly, but dates put in the date

1753-01-01 00:00:00.000 when it should be NULL. The variable is a char [24] and the first item is set to \0 ( dbLoanBankruptcy.MotionforReliefReqDate[0] = NullChar; ).

Is there any example or way to put the date in as null when the variable is null and an actual date as needed?

bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefReqDate, 0, 23, NULL, 0, SQLCHARACTER, 13)

Thank you,

Joel

Following site maybe helpful:

http://msdn2.microsoft.com/en-us/library/aa198011(sql.80).aspx

|||The link above only discusses dates enough to get an actual date into a table, but does not discuss how to get a NULL date stored. Is there a way to save <NULL>?

Would it be wrong to use the bcp_bind statement you needed for each row/record and re-bind each time? As in...

if(dbLoanBankruptcy.MotionforReliefFiledDate[0]==NullChar) {
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, SQL_NULL_DATA, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}else{
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, 23, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}

|||

You don't need to rebind each time

http://msdn2.microsoft.com/en-gb/library/aa177853(SQL.80).aspx

struct

{

int iIndicator;

char szDatetime[30];

} urBCPData;

if (bcp_bind(om_hDbc2, (LPCBYTE) &urBCPData, 4, SQL_VARLEN_DATA , NULL, 0,SQLVARCHAR, 1) == FAIL)

{

// Raise error and return.

return;

}

// Insert NULL

urBCPData.iIndicator = SQL_NULL_DATA;

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

return;

}

// Insert 1753-01-01 00:00:00.000

urBCPData.iIndicator = 23;

strcpy(urBCPData.szDatetime, "1753-01-01 00:00:00.000");

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

ShowSQLError(om_hDbc2, om_hStmt2);

return;

}

|||Tested and works... thanks.

The normal bind statement but the data portion is now (byte *)&data.datetime_struct

Code Snippet

if(dbLoanBankruptcy.MotionforReliefReqDate[0]==NullChar) {
dbLoanBankruptcy.bounddate.iIndicator = SQL_NULL_DATA;
}else{
dbLoanBankruptcy.bounddate.iIndicator = 24;
strcpy(dbLoanBankruptcy.bounddate.iValue, dbLoanBankruptcy.MotionforReliefReqDate);
}

Problem convert varbinary to float

Hello I have some problems with converting varbinary to float in T-SQL
stored procedure. In my C# application i have table of structures with
double type fields. Size of this table is variant. I have to send this
table to SP. Because of performance i want to send it only once and
whole. So I have to cut this varbinary blob in my Stored procedure to
receive structure field values. But I have problems with double fields
- conversion from varbinary to float in T-SQL is not allowed. Please
halp me - thx for any advise.
MaciejMaciej (maciej_ty@.poczta.onet.pl) writes:
> Hello I have some problems with converting varbinary to float in T-SQL
> stored procedure. In my C# application i have table of structures with
> double type fields. Size of this table is variant. I have to send this
> table to SP. Because of performance i want to send it only once and
> whole. So I have to cut this varbinary blob in my Stored procedure to
> receive structure field values. But I have problems with double fields
> - conversion from varbinary to float in T-SQL is not allowed. Please
> halp me - thx for any advise.

It is not clear how you send this table to SQL Server, but it sounds
like you are sending a binary string. This sounds messy.

The best is probably to format the data as XML, and then use
sp_xml_prepearedocument and OPENXML to unpack the data on the SQL Server
side.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

Problem in Converting Task to DataPumpTask2

Saturday, February 25, 2012

Problem Converting XML to table in SQL server

Problem converting VS2003 code to VS2005

Problem converting UTC dates in dts

Problem converting to a date...

problem converting string to integer

Conversion failed when converting the varchar value 'eventnum' to data type int.

Monday, February 20, 2012

problem converting smalldatetime to 12hr time value

problem converting smalldatetime to 12hr time value

Problem converting rows to a string

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

Problem converting military time from CHAR column

problem converting float into nvarchar

Problem converting decimal values

Problem converting Crystal Report to RS2005

Problem converting CHAR to DATETIME

Problem converting C application from using DBLIB bcp to using ODBC bcp.

Problem convert varbinary to float

connecting to SSAS

Blog Archive

About Me