data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed th
e
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly adviseTiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

data conversion

Hi!
I would be grateful for some advice, when getting error. I have 4 Lookups and one Data Conversion, getting the follwing error. Product.articlenr is a 13 number+letter productnumber.

[Lookup Demo [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[Data Conversion [9467]] Error: Data conversion failed while converting column "articlenr" (8559) to column "Copy of Lookup Product.articlenr" (10059). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [9467]] Error: The "output column "Copy of Lookup Product.articlenr" (10059)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Lookup Product.articlenr" (10059)" specifies failure on error. An error occurred on the specified object of the specified component.

curiousss wrote:

[Lookup Demo [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Right. So adjust your keys in the lookup transformation such that you eliminate duplicates. If you are using SQL, then use a "SELECT DISTINCT" statement. The error message should be pretty clear in what the problem is.

curiousss wrote:

[Data Conversion [9467]] Error: Data conversion failed while converting column "articlenr" (8559) to column "Copy of Lookup Product.articlenr" (10059). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

You must be converting to something that is causing a truncation to occur on real data. In order for the conversion to occur, you would experience data loss. Double check your data types and lengths, if applicable.

|||Thank you!
I have following data types and lengths in conversion
Input column DT_STR lenght 30
Output column (DT_14)

so could this cause a problem?

|||

curiousss wrote:

Thank you!
I have following data types and lengths in conversion
Input column DT_STR lenght 30
Output column (DT_14)

so could this cause a problem?

Yes. 30 bytes overflows a DT_I4.

|||I am confused.. How can I change the Destination Input column DT_14 to length 30?

Conversion Input column DT_STR lenght 30, so probably Output column need to be (DT_14). But how can I change it to DT_14 to length 30?|||

curiousss wrote:

I am confused.. How can I change the Destination Input column DT_14 to length 30?

Conversion Input column DT_STR lenght 30, so probably Output column need to be (DT_14). But how can I change it to DT_14 to length 30?

30 bytes is too large for a DT_I4. See this page: http://msdn2.microsoft.com/en-us/library/ms187745.aspx

You would need to use a DT_DECIMAL(30,0) to be able to hold all of your 30 byte's worth of data. Or, you'll need to substring an appropriate amount of data from the originating character string.

|||Thank you.. how do I do this substring?

|||

curiousss wrote:

Thank you.. how do I do this substring?

substring([column],startlocation,length)|||

curiousss wrote:

Thank you.. how do I do this substring?

You also need to read the BOL when you have questions regarding functions and other things in SSIS. The particular section on functions can be found here: http://msdn2.microsoft.com/en-us/library/ms141671.aspx

data conversion

Hi
i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy
csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.
Kindly advise
Tiffany
declare @.dt varchar(20)
set @.dt='Feb-06'
select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@.dt,2)+case when left(@.dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@.microsoft.com...
> Hi
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
> Kindly advise
>

data acess class

hi
i was reading book about asp.net and i found example for a dataacess class but i didn't understand this part

Private m_FieldData As New NameObjectCollection
Private _m_ConnectionString As String
Private m_dbConnection As SqlConnection
Private Sub AddParameters( _
ByVal objCommand As SqlCommand, _
ByVal objValues() As Object)

Dim objValue As Object
Dim I As Integer
Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

I = 0
For Each objParameter In objCommand.Parameters
If objParameter.Direction = ParameterDirection.Input _
Or objParameter.Direction = _
ParameterDirection.InputOutput Then

objValue = objValues(I)
objParameter.Value = objValue
I = I + 1
End If
Next
End Sub

Private Sub AddFieldParameters _
(ByVal objCommand As SqlCommand)

Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

For Each objParameter In objCommand.Parameters
objParameter.Value = _
_FieldData.Item(objParameter.ParameterName. _
Substring(1))
Next
End Sub
Public Function ExecDataReader _
(ByVal strStoredProc As String, _
ByVal ParamArray objValues() As Object) _
As SqlDataReader

Dim objCommand As SqlCommand
Dim objReader As SqlDataReader

objCommand = New SqlCommand

objCommand.CommandText = strStoredProc
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = dbConnection

Try
objCommand.Connection.Open()
If (objValues.Length = 0) Then
AddFieldParameters(objCommand)
Else
AddParameters(objCommand, objValues)
End If
objReader = objCommand. _
ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
If objCommand.Connection.State.Open Then
objCommand.Connection.Close()
End If
End Try

Return objReader
End Function
can anybody help me what the author want to doTamer, can you be more specific?

Thanks|||hi i don't understand sub AddParameters and addfieldparameter
and why to add values as an array
and the parameter have only one single value why to pass an array of object i can pass a single value hope u understand me|||I'm going to have to kind of guess here as I don't know the source or the context of the script. That being said

Looking at just the AddFieldParameters sub:
---------------------------------
Private Sub AddFieldParameters (ByVal objCommand As SqlCommand)

Dim objParameter As SqlParameter

objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)

For Each objParameter In objCommand.Parameters
objParameter.Value = FieldData.Item(objParameter.ParameterName.Substring(1))
Next
End Sub
---------------------------------

I read this as the SQL command is being passed into the sub as a value and that value can have multiple lines to it. The FieldData object is being created with each line of the SQL command as a different entry in the array.

The AddParameters looks pretty much the same.

Does this answer your question?

I can look into this further if you provide me with the origional source and location of the script.

Thanks

Data Acess aplication block

Hi
I have Microsoft Enterprise Library 2005 installed on my local system.
I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
Server 2000.
I am developing a web application in which i am using data Access
Application Block for data access.
I have a remote web n data server with asp.net 1.1 support
the application is working fine here on the local server(web & data).
when i change the data server path to the remote server it also working
fine but
When I uploaded the application on the remote server n tested there it
gives me the following err
ex.message was
[vbcol=seagreen]
ex.Source was
.........Microsoft.Practices.EnterpriseLibrary.Data
ex.trace was
at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionFai
ledEvent.Fire(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumentati
onFacade.ConnectionFailed(String
connectionString) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBCommandW
rapper
command) at property.m4.Button2_Click(Object sender, EventArgs e)
Plz help me why this is happening.........
Mukesh Kumar agarwal
Delhi
IndiaMukesh,
Are you sure that the user that ASP.NET is running under has access to
the database you are trying to reach? It seems as if this is an error on
connecting.
Hope this helps.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Mukesh" <efextra@.newsgroups.nospam> wrote in message
news:u6JBICB1GHA.4264@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have Microsoft Enterprise Library 2005 installed on my local system.
> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> I am developing a web application in which i am using data Access
> Application Block for data access.
> I have a remote web n data server with asp.net 1.1 support
> the application is working fine here on the local server(web & data).
> when i change the data server path to the remote server it also working
> fine but
> When I uploaded the application on the remote server n tested there it
> gives me the following err
>
> ex.message was
>
> ex.Source was
> .........Microsoft.Practices.EnterpriseLibrary.Data
> ex.trace was
> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionF
ailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumenta
tionFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBComman
dWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
>
> Plz help me why this is happening.........
> Mukesh Kumar agarwal
> Delhi
> India
>
>
>|||The error you are getting is because the enterprise library wants to write
events to the event log and setting this up requires registry permissions.
You need to installutil the dlls for the enterprise library (this is covered
in the docs somewhere). The reason it is writing to the event log is because
the connection failed. If you setup the event log access correctly then you
will have an eventlog entry telling you why the connection failed.
HTH
Ciaran O'Donnell
"Mukesh" wrote:

> Hi
> I have Microsoft Enterprise Library 2005 installed on my local system.
> I m also using ASp.net 1.1 And C3 as coding language , I have MS Sql
> Server 2000.
> I am developing a web application in which i am using data Access
> Application Block for data access.
> I have a remote web n data server with asp.net 1.1 support
> the application is working fine here on the local server(web & data).
> when i change the data server path to the remote server it also working
> fine but
> When I uploaded the application on the remote server n tested there it
> gives me the following err
>
> ex.message was
>
> ex.Source was
> ..........Microsoft.Practices.EnterpriseLibrary.Data
> ex.trace was
> at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataConnectionF
ailedEvent.Fire(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataInstrumenta
tionFacade.ConnectionFailed(String
> connectionString) at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at
> Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DBComman
dWrapper
> command) at property.m4.Button2_Click(Object sender, EventArgs e)
>
> Plz help me why this is happening.........
> Mukesh Kumar agarwal
> Delhi
> India
>
>
>

DAO Error code 0xbd7

Hi
I have a button in vb calling crsytal reports 9 . These reports are opening in cr viewer. I have an access database secured with a password. I can open the report but when I go to enter new parameters in the cr viewer, I get this error.

"LOGON FAILED"
Details: DAO Error Code 0xbd7
Source: Dao.Workspace
Description: Not a valid Password

Im lost on this one and I would be grateful for some help.
Thanks!!Hello,
This is in C#, I thing it can help you :

crystalReportDocument1.DataSourceConnections[0].SetConnection(SERVER, Database, "USERNAME", "YOUR PASSWORD"); crystalReportViewer1.ReportSource = crystalReportDocument1;

I use Microsoft Access file, so I left the Username blank, and for Server and Database put the new path to my .mdb file.

I hope it helps.

Damaged databases

Hi

I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.

This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.

Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.

All help will be very much appreciated.

Rob

Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?

You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.

Thanks

Damaged databases

Hi

I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.

This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.

Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.

All help will be very much appreciated.

Rob

Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?

You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.

Thanks

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob
=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob
|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bobexamnotes <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:

> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists> ) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is tha
t
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bang?s" wrote:

> examnotes <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
>
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Customizing Toolbar in Reporting Service

Hi

I use SSRS 2005; i have a report with Drill down feature, connecting to another report. when i preview the report in the VS2005; i get a backup button in the RS toolbar to move from the second report to the main report after the drill down.

but once i deploy it on the server and when the user views the report on the web i dont get to see the BACK button.

Is it possible to do so?

anybody have tried and seen before....

/chandresh soni

There is no back button on the web-based toolbar. We thought that people would get confused with having a back button in the browser along with one on the toolbar. The back button on the browser will generally work except it will include other actions like toggles and sorts.|||

thanks Brain, for replying.

i will try something alternative. or ask the user to use the browser back button.