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

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

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