Data source synchronization issue: Connection string conflict?

I was NOT using data source till now. I was directly using Ole DB Connection. Everything works as expected.

Today, I created a new Data Source based on the existing Ole Db connection.

I changed my tasks to use this Data Source.

Now, everytime I open the dtsx file, a screen pops-up with title "Synchronize Connection Strings". It reads: "This package contains at least one connection which is based on a data source. The connection strings for the connection and the data source listed below are not identical. Connection string of the connection will be updated to reflect those on the data source.

Connection : XYZ

Data Source: XYZ

Old Connection String: machine_name\instance_name; User ID=sa; Initial Catalog = MyDB; Provider=SQLOLEDB.1

New Connection String: Provider=SQLOLEDB.1; Data Source = machine_name\instance_name; Persist Security Info = true; Password=; User ID=; Initial Catalog = MyDB

I press the OK button, but the screen repeats if I close the dtsx file and re-open it. The underlying error is:

Error 1 Validation error. Data Flow Task - MyTaskName: OLE DB Destination - mytable [214]: The AcquireConnection method call to the connection manager "machine_name\instance_name.MyDB.sa" failed with error code 0xC0202009. mypackage.dtsx 0 0
Any idea?looks like a bug... can you open one, please?

thanks|||Sorry, I did not look at this post and therefore your reply till today (when I thought about using Data Sources again).

How do I submit this bug?

data secruity for Report Builder Models

Does anyone know how to implement data secruity based on user id (forms
authenication) for the report builder models.
Basically, I have created an report model. And i want certain people to have
access to certain data. The users are organized by the office where they
work. Is there a way to do, this without creating an different model for each
group of users?read this:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> Does anyone know how to implement data secruity based on user id (forms
> authenication) for the report builder models.
> Basically, I have created an report model. And i want certain people to
> have
> access to certain data. The users are organized by the office where they
> work. Is there a way to do, this without creating an different model for
> each
> group of users?|||When I try to add a new expression or filter, i get the following error
message
Access to the path 'C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\config' is denied. (Microsoft Visual Studio)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>|||so, i wanted to do something like this as a filter or expression. But i don't
know how to get this into the expression. Anything you can do would help
SELECT *
FROM tblBureauCode INNER JOIN
tblPerson ON tblBureauCode.Bureau_CodeID =tblPerson.Bureau_CodeID INNER JOIN
tblDataAccess ON tblBureauCode.Bureau_Code =tblDataAccess.Bureaus
WHERE (tblDataAccess.Login = user!userid)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>

Data Purging

Hi

We have a requirement for purging the historical data based on the specified criteria.We have a complex data model with Parent Child Hirearchy goes to multiple levels.

Does any body knows information on how data purging is done in production environments with characteristics such as

Data Volume is huge (500 GB)

Multiple level of realtionships (It can go up to 6 levels)

Physical relationship exists between tables

thanks in advance :)

Regards
Samsudeen B

My 20 cents worth:

One of the reasons data purging may be a pain is the dilemma of associated indexes. You need them to locate the records to delete and they can make the whole process slow because they may have to be rebuilt (not forgetting any locking issues).

The other issue would be whether you have cascading referential integrity set up.

I would not embark on an all-at-once deletion. I would do small/medium range deletions and I might also disable any indexes on the underlying tables. I you have cascading referential integrity, all you have to do is just delete the relevant parent records.

DELETE FROM parent_table

WHERE date_column BETWEEN a AND b

GO

DELETE FROM parent_table

WHERE date_column BETWEEN b AND c

GO

.......................

........................

DELETE FROM parent_table

WHERE date_column BETWEEN y AND z

|||

Hi Nathan,

Thanks for the response.

I don't have the cascading referential integrity setup in my model.As I told earlier my model is complex with child tables been refered with multiple foreign keys..

Do you have any idea on how these kind of situations handled in pruging data in production environment.

Thanks,

Samsudeen B

|||

Without cascading referential integrity, you will have to delete data in the correct order to avoid constraint violations. Another headache.

Anyway, in a production environment, you will have to be much more cautious as it is very easy to slow down the server to a crawl due to table locking and excessive transaction log growth.

Here is what I have done before:

1. Script out the DROP and CREATE index statements for the tables concerned.

2. Script out the DROP and CREATE foreign key constraints for all child tables.

3. Make a script containing piece-meal delete statements (deleting a few thousand rows per table per batch starting with the child tables first). You need to understand the dependencies between your tables

During my "off-peak" hours

4. Change the database recovery model to "simple"

5. Disable all Triggers on the tables.

6. Drop the indexes and foreign keys (all in a script)

7. Run the "delete" script. Delete as many rows as possible within my "free" time window.

8. Enable the triggers

9. Run the script to recreate the indexes for the tables

10. Shrink the log file (DBCC SHRINKFILE)

11. Revert to "Full" Recovery model

12. Run a script to update statistics on the tables.

You don't have to delete all the data all at once. You can do this as many times as necessary if you have a very busy server. And you can put all that work (4-12) into a script and make some coffee. My server is not used for at least 4 hours in the night.

If your server never "chills out" then a carefully scripted delete operation and a simple recovery model may be of help. You may also save yourself a few headaches by adding cascading referential integrity to the tables. That way you script only the delete statements for the parent tables.

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data not Exported to CSV/XML

I have a table where one of the columns is hidden based on the following criteria:
=IIF(Fields!ChildIsEmpty5.Value, true, false)

If the Fields!ChildIsEmpty5.Value is false (so the column is showing), and I export the report to CSV or XML, the data in that column is not exported.

Is there another property I can select to get this data to be exported? Or is this functionality not supported?

Thanks for any help you can provide!
Jessica

Jessica,

This functionality is by design. Fields where visibility (hidden) is expression-based are not output to CSV/XML by default.

If you always want this column present in the CSV/XML output, you can set DataElementOutput property of the column to 'Yes'(or in Data Output tab, set data output to 'Yes')

Thanks!

|||But what to do if i need to show or hide object, depending on some condition(expression) in export also?|||

Still not working.

I selected the report item (textbox), right click, select properties, data output tab and I set the output from auto to yes. This item still does not show up in the xml output file.

thanks,

Helmut.

Data not Exported to CSV/XML

I have a table where one of the columns is hidden based on the following criteria:
=IIF(Fields!ChildIsEmpty5.Value, true, false)

If the Fields!ChildIsEmpty5.Value is false (so the column is showing), and I export the report to CSV or XML, the data in that column is not exported.

Is there another property I can select to get this data to be exported? Or is this functionality not supported?

Thanks for any help you can provide!
Jessica

Jessica,

This functionality is by design. Fields where visibility (hidden) is expression-based are not output to CSV/XML by default.

If you always want this column present in the CSV/XML output, you can set DataElementOutput property of the column to 'Yes'(or in Data Output tab, set data output to 'Yes')

Thanks!

|||But what to do if i need to show or hide object, depending on some condition(expression) in export also?|||

Still not working.

I selected the report item (textbox), right click, select properties, data output tab and I set the output from auto to yes. This item still does not show up in the xml output file.

thanks,

Helmut.

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data Migration

I have 2 Tables

Table 1 has

Id

Type

Table 2 has

Id

Type

PartNumber

I need to migrate data from Table 1 to Table 2 based on Type.

Type can be 1 or 2.

Example of Source.

Table 1 has

Id Type

1 1

2 1

3 1

3 2

Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different.

Example of Destination.

Id Type PartNumber

1 1 10

1 2 10

2 1 11

2 2 11

3 1 12

3 2 13

================================

As you see above

1. I need to duplicate records which had just one type in the Source table.

1 1 10

1 2 10

I will have 2 (1 and 2) types now, but everything else the same for the same id.

In my case I will have Part Numbers as guid. I would create them on my own. They will be the same here.

2. I need to have 2 records as it was before in Source table for id's with 2 types, but with different Part Numbers.

3 1 12

3 2 13

In my case I will have Part Numbers as guid. I would create them on my own.They will be different here.

How do I create 2 records for each Id and certain rules for them? How do I find if there is one or there are two records in Source and how do I apply my rules in looping through records? What control in SSIS tool can I use?

Thanks.

This looks like a job for T-SQL, not SSIS. If you can elaborate on the "Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different" statement, I may be able to help more. The logic you describe here isn't precise enough to implement (or perhaps I'm just not following it correctly) but this looks like a simple INSERT .. SELECT to me.|||

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

|||

Vita wrote:

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

"Part Number will be different" is what was unclear to me. Different in what way? In each case, what should it be?

I think your most recent post provides the needed clarity - let me play with it for a while...

|||

Ok... that was more annoying than I figured - it was the NEWID() function that was giving me trouble, so I needed to use a temp table to get the desired results. Here's the deal:

Code Snippet

USE tempdb

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))

DROP TABLE [dbo].[Table1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))

DROP TABLE [dbo].[Table2]

GO

CREATE TABLE Table1

(

Id INT NOT NULL,

Type INT NOT NULL

)

go

CREATE TABLE Table2

(

Id INT NOT NULL,

Type INT NOT NULL,

PartNumber UNIQUEIDENTIFIER NULL

)

GO

INSERT INTO Table1 VALUES (1, 1)

INSERT INTO Table1 VALUES (2, 1)

INSERT INTO Table1 VALUES (3, 1)

INSERT INTO Table1 VALUES (3, 2)

GO

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT #ExistsOnce.*

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

The final SELECT statement is a verification of the contents of Table2, and based on the inputs you provided, produces this output:

Code Snippet

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Does this give you what you need?

|||

Thank you for your hard work.

The only thing I see wrong is the type.

You got

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

and I need

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 2 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 2 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Type always needs to be 1 and 2.

Everything else looks great.

|||

Here you go:

Code Snippet

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT Id

,Dup

,PartNumber

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

This yields:

Code Snippet

Id Type PartNumber

-- --

1 1 7578AB32-6F38-4D23-887B-D81DD9D71DBC

1 2 7578AB32-6F38-4D23-887B-D81DD9D71DBC

2 1 F554FFA0-B0DB-4C53-9D3F-181678617A9B

2 2 F554FFA0-B0DB-4C53-9D3F-181678617A9B

3 1 165DEB39-D6DA-4DD3-B577-DD295BAF7455

3 2 55389A91-EAF6-4360-BFAA-10032EA1AB67

|||Thank you very much!

Data is there BUT could NOT find it!

Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
could not get the same record if I give the BO_ID Value. Please hava a look
at the following query and other details.
Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
ON/OFF Setting. Let me know should you need more info. Thanks.
USE ZADMIN
--BO_ID is there.
SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
--BO_ID ROW_ID
--898509508329674401118020031483 576
--Column_name Type
--BO_ID varchar
--ROW_ID int
--But, could NOT find it if BO_ID is given.
SELECT * FROM ZADMIN..CB_INST_IMPORT
WHERE BO_ID = '898509508329674401118020031483'
SELECT * FROM ZADMIN..CB_INST_IMPORT
WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
--Length of BO_ID is 30 but query says as 32.
SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
WHERE ROW_ID = 576 --32
--SeequellI'm curious about those two phantom characters. Try running something
like:
SELECT *,
ASCII(SUBSTRING(BO_ID),01,1)),
ASCII(SUBSTRING(BO_ID),02,1)),
ASCII(SUBSTRING(BO_ID),03,1)),
ASCII(SUBSTRING(BO_ID),04,1)),
ASCII(SUBSTRING(BO_ID),05,1)),
...
ASCII(SUBSTRING(BO_ID),29,1)),
ASCII(SUBSTRING(BO_ID),30,1)),
ASCII(SUBSTRING(BO_ID),31,1)),
ASCII(SUBSTRING(BO_ID),32,1))
FROM ZADMIN..CB_INST_IMPORT
WHERE ROW_ID = 576
Roy Harvey
Beacon Falls, CT
On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
<Seequell@.discussions.microsoft.com> wrote:

>Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I
>could not get the same record if I give the BO_ID Value. Please hava a look
>at the following query and other details.
>Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER
>ON/OFF Setting. Let me know should you need more info. Thanks.
>USE ZADMIN
>--BO_ID is there.
>SELECT BO_ID, ROW_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> --BO_ID ROW_ID
> --898509508329674401118020031483 576
> --Column_name Type
> --BO_ID varchar
> --ROW_ID int
>--But, could NOT find it if BO_ID is given.
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE BO_ID = '898509508329674401118020031483'
>SELECT * FROM ZADMIN..CB_INST_IMPORT
>WHERE RTRIM(LTRIM(BO_ID)) = '898509508329674401118020031483'
>--Length of BO_ID is 30 but query says as 32.
>SELECT LEN(BO_ID) FROM ZADMIN..CB_INST_IMPORT
>WHERE ROW_ID = 576 --32
>--Seequell|||Hi Roy,
I will test it soon and post the result. Thanks for replying.
--
--Seequell
"Roy Harvey" wrote:

> I'm curious about those two phantom characters. Try running something
> like:
> SELECT *,
> ASCII(SUBSTRING(BO_ID),01,1)),
> ASCII(SUBSTRING(BO_ID),02,1)),
> ASCII(SUBSTRING(BO_ID),03,1)),
> ASCII(SUBSTRING(BO_ID),04,1)),
> ASCII(SUBSTRING(BO_ID),05,1)),
> ....
> ASCII(SUBSTRING(BO_ID),29,1)),
> ASCII(SUBSTRING(BO_ID),30,1)),
> ASCII(SUBSTRING(BO_ID),31,1)),
> ASCII(SUBSTRING(BO_ID),32,1))
> FROM ZADMIN..CB_INST_IMPORT
> WHERE ROW_ID = 576
> Roy Harvey
> Beacon Falls, CT
> On Thu, 29 Jun 2006 15:05:01 -0700, Seequell
> <Seequell@.discussions.microsoft.com> wrote:
>
>|||Found it. Hope I am right.
Characters were Line feed (char(10)) and Carriage return (char(13)).
Have a look at the query and result. Now, how do I remove it?
Thanks a lot.
SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
BO_ID
----
898509508329674401118020031483
--***--
SELECT ASCII(SUBSTRING(BO_ID,01,1)),
ASCII(SUBSTRING(BO_ID,02,1)),
ASCII(SUBSTRING(BO_ID,03,1))
FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
-- -- --
56 57 56
--***--
SELECT ASCII(SUBSTRING(BO_ID,29,1)),
ASCII(SUBSTRING(BO_ID,30,1)),
ASCII(SUBSTRING(BO_ID,31,1)),
ASCII(SUBSTRING(BO_ID,32,1))
FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
-- -- -- --
56 51 13 10
--***--
--Seequell
"Seequell" wrote:
> Hi Roy,
> I will test it soon and post the result. Thanks for replying.
> --
> --Seequell
>
> "Roy Harvey" wrote:
>|||Seequell wrote:
> Found it. Hope I am right.
> Characters were Line feed (char(10)) and Carriage return (char(13)).
> Have a look at the query and result. Now, how do I remove it?
> Thanks a lot.
> SELECT BO_ID FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> BO_ID
> ----
> 898509508329674401118020031483
> --***--
> SELECT ASCII(SUBSTRING(BO_ID,01,1)),
> ASCII(SUBSTRING(BO_ID,02,1)),
> ASCII(SUBSTRING(BO_ID,03,1))
> FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
>
> -- -- --
> 56 57 56
> --***--
> SELECT ASCII(SUBSTRING(BO_ID,29,1)),
> ASCII(SUBSTRING(BO_ID,30,1)),
> ASCII(SUBSTRING(BO_ID,31,1)),
> ASCII(SUBSTRING(BO_ID,32,1))
> FROM ZADMIN..CB_INST_IMPORT WHERE ROW_ID = 576
> -- -- -- --
> 56 51 13 10
> --***--
>
UPDATE CB_INST_IMPORT
SET BO_ID = REPLACE(BO_ID, CHAR(13)+CHAR(10), '')
WHERE ROW_ID = 576

Data input problem

In Visual Web Developer Ihave created a data input form based on the documentation I found in the .NETFramework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes(FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.

When I added a checkbox Iget an error that"Stringwas not recognized as a valid Boolean" when the checkbox is checked. When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.

Here is my VB code, InsertParameters for my SqlDataSource1, and button code:

<scriptrunat="server">

PrivateSub InsertData(ByValSourceAsObject,ByVal eAsEventArgs)

SqlDataSource1.Insert()

EndSub

</script>

<InsertParameters>

<asp:formParameterName="FirstName"Type="String"FormField="txtFirstName"/>

<asp:formParameterName="LastName"Type="String"FormField="txtLastName"/>

<asp:formParameterName="Phone"Type="String"FormField="txtPhone"/>

<asp:formParameterName="Email"Type="String"FormField="txtEmail"/>

<asp:formParameterName="FreeInfo"Type="Boolean"FormField="checkbox1"/>

</InsertParameters>

<asp:Buttonid="Button1"runat="server"text="Submit"OnClick="insertdata"/>

I suspect that the problemis with the VB code which will only accept string input data, but don't knowhow to fix it. Any thoughts on how tofix this problem? Thanks in advance forany help provided.

Check box return "On" whener page is postback

It does'nt return checked or unchecked.

You have to check this checkBox by code before inserting the data.

If checkBox is checked set the parametervalue = 1 else 0 .

Thank

Kunal

|||

Thanks for your feedback. I tried to implement your suggestions, but get this error message:

String was not recognized as a valid Boolean.

Here is my new VB code:

Private Sub InsertName(ByVal Source As Object, ByVal e As EventArgs)
If (CheckBox1.Checked = True) Then
CheckBox1.Equals(1)
Else : CheckBox1.Equals(0)
End If
SqlDataSource1.Insert()
End Sub

I am a newbie to VB, so my codeprobably doesn't accurately implement your recommendations. Any helpyou can provide in getting this to work will be greatly appreciated.

|||

Hi,

The formParameter always gets value from the Text property of the controls. In this case, you can try to use ControlParameter instead.

<asp:controlParameter Name="FreeInfo" ControlId="checkbox1" PropertyName="Checked"/>

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thanks Kevin. That's exactly what I needed.

Data from versant into MS SQL

Hi all,

I'm testing some software based on versant database
(only on an application level) with Winrunner.

On the other hand I'm a business intelligence consultant and doing a lot of datawarehousing and cube building.

Now I'm curious if there is a simple way of getting data
from the versant database into MS SQL databases to
use the data for reporting/olap purpose.

Do you know any connection possibilities, ODBC drivers or
connection string examples to
a) get metadata from versant
b) get data from versant.

thanks in advance

MichaelGoogle (http://www.google.com/search?hl=en&q=versant+odbc) is your friend!

-PatP