data that in one table column and not in the other table column

dear all

i have 2 tables, lets say table A and Table B

both tables has column ID

i wonder how can i find records that appears in B.ID and not appear in A.ID

what is the SQL command in this case?

Thnks alot

Please post T-SQL question in the Transact-SQL forum at:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can use the following query:

SELECT B.ID from B where B.ID NOT IN

(SELECT A.ID FROM A)

And this will return records from B which are not in A

Data script

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Picture of type image so the
insert is wrong in this way. How do i correct it?

/*****MarinLIf TAble ********************/
DECLARE @.ID INT
DECLARE @.typeID INT
DECLARE @.NAME NVARCHAR(50)
Declare @.scName nvarchar(50)
Declare @.distribution nvarchar(600)
Declare @.maxSize nvarchar(200)
Declare @.env nvarchar(200)
Declare @.climate nvarchar(200)
Declare @.country nvarchar(2000)
Declare @.desc nvarchar(4000)
Declare @.pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic)
VALUES (' + convert(varchar,@.ID) + ','
+ convert(varchar,@.typeID) + ','
+ '''' + @.NAME + '''' +
+ '''' + @.scName + '''' + ','
+ '''' + @.distribution + '''' + ','
+ '''' + @.maxSize + '''' + ','
+ '''' + @.env + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.country + '''' + ','
+ '''' + @.desc + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.pic + '''' + ')'
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!MC (anonymous@.discussions.microsoft.com) writes:
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>...
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?

You can't assign to image variables, so this approach is not going
to work.

You are probably better off using BCP, a command-line which is designed
for importing and exporting data. In this case you could try:

bcp yourdb..MarinLif out MarinLif.bcp -N -T -S source_server
bcp yourotherdb..MarinLif in MarinLif.bcp -N -T -S target_server

-N here means that you are using native datatypes with Unicode. -T is for
trusted connection. -S specifies the server.

For -N to work, the tables must be identical, including column order.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, if both databases are on the same server, you
can use the INSERT INTO... SELECT syntax:

INSERT INTO MyOtherDatabase.dbo.MarinLif
(
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
)
SELECT
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
FROM MyDatabase.dbo.MarinLif

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <anonymous@.discussions.microsoft.com> wrote in message
news:40c4c158$0$165$c397aba@.news.newsgroups.ws...
>
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>
> /*****MarinLIf TAble ********************/
> DECLARE @.ID INT
> DECLARE @.typeID INT
> DECLARE @.NAME NVARCHAR(50)
> Declare @.scName nvarchar(50)
> Declare @.distribution nvarchar(600)
> Declare @.maxSize nvarchar(200)
> Declare @.env nvarchar(200)
> Declare @.climate nvarchar(200)
> Declare @.country nvarchar(2000)
> Declare @.desc nvarchar(4000)
> Declare @.pic image
> DECLARE CURS CURSOR STATIC FOR
> SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic
> FROM MarinLif
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> PRINT 'SET IDENTITY_INSERT MarinLif ON'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic)
> VALUES (' + convert(varchar,@.ID) + ','
> + convert(varchar,@.typeID) + ','
> + '''' + @.NAME + '''' +
> + '''' + @.scName + '''' + ','
> + '''' + @.distribution + '''' + ','
> + '''' + @.maxSize + '''' + ','
> + '''' + @.env + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.country + '''' + ','
> + '''' + @.desc + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.pic + '''' + ')'
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> END
> PRINT 'SET IDENTITY_INSERT MarinLif OFF'
> CLOSE CURS
> DEALLOCATE CURS
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Wale
wale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach
sql

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Walewale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want
to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach

Data Retrieval

How does SQL handle data retrieval from a specific record? I want to be able
to capture 100 records from any given point in a table by sending a single
column value as a starting point.
So if I have a table with primary_key with values 1-1000 and I want to get
1-100 I can use TOP 100. If I then want 101 - 200, 201 - 300, etc, how can I
get the set(s)?
Can this be done?
Thank you,
AnthonyUse WHERE clause.
declare @.i int, @.j int
set @.i = 201
set @.j = 300
...
where pk_col between @.i and @.j;
AMB
"Anthony W DiGrigoli" wrote:

> How does SQL handle data retrieval from a specific record? I want to be ab
le
> to capture 100 records from any given point in a table by sending a single
> column value as a starting point.
> So if I have a table with primary_key with values 1-1000 and I want to get
> 1-100 I can use TOP 100. If I then want 101 - 200, 201 - 300, etc, how can
I
> get the set(s)?
> Can this be done?
> Thank you,
> Anthony

Data regions are not allowed inside a table detail ..

Developing using Visual Studio .Net (2003).
I have a simple table on my report showing data from a dataset. Works
fine. Displays a bunch of records.
One of the values from my dataset is a percentage value (integer from 0
to 100). I'm attempting to illustrate this using a chart. So I'm
attempting to put a chart in the table detail row. It doesn't like me
doing this, though. I get a build error:
"The chart 'chartx' is contained inside a table detail row. Data
regions are not allowed inside a table detail...".
Is there a way to do what I'm attempting with Visual Studio .Net (i.e.
SQL Server 2000 Reporting Services)? I know it can be done with Visual
Studio 2005, as that just makes you define a group expression when
attempting to "use a data region in a list".
Any help/info would be great.
Thanks.Have you tried putting it inside a matrix?
I tried putting a chart inside a table detail row, but vs didn't let me
do it "Cannot place a chart at this location in a table"
However, putting it inside a matrix works just fine.
eamon wrote:
> Developing using Visual Studio .Net (2003).
> I have a simple table on my report showing data from a dataset. Works
> fine. Displays a bunch of records.
> One of the values from my dataset is a percentage value (integer from 0
> to 100). I'm attempting to illustrate this using a chart. So I'm
> attempting to put a chart in the table detail row. It doesn't like me
> doing this, though. I get a build error:
> "The chart 'chartx' is contained inside a table detail row. Data
> regions are not allowed inside a table detail...".
> Is there a way to do what I'm attempting with Visual Studio .Net (i.e.
> SQL Server 2000 Reporting Services)? I know it can be done with Visual
> Studio 2005, as that just makes you define a group expression when
> attempting to "use a data region in a list".
> Any help/info would be great.
> Thanks.sql

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank you
Bringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you
|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my loca
l
machine it takes 14 minutes (slams my machine) to get back the whole dataset
.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such ma
ny rows.
Start by retrieving only the data you need, then tune the query by adding in
dexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:
> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data Partioning...Urgent help req.

hi,
i have a table with 5 million records which has been horizonatal partitioned
into 3 tables. Each of these tables are located on different logical drives
using filegroups in the same database on a single SQL Server Instance.
Each table structure is:
InvID int Identity,
InvDate datetime
InvNumber varchar(50)
InvID and InvDate together form the primary key for each of the tables.
InvDate is my partioning column...which partitions my data according to
years.
I have then created a view using a select * and union all clause which
selects the data from these tables.
When querying data selectively i.e. for a specific year using the partioned
view i get a performance gain of around 10 % over selection of data from a
non partioned view i.e. from a single table containing all the 5 million
records.
Is their someway in which i can get a better performance gain, as i feel 10%
gain is not much...some of the articles i have read claim that we can get a
performance gain of upto 35%.
I also do not get any performance gain when loading data or when firing a
select * i.e. selecting all 5 million records.
Can i get performace gain in these through the concept of horizontal
partitioning.
Plz help.
Regards,
Harman.Harman,
I don't see any value in partitioning the table with such small amount of
data. I am a bit surprised that you saw any performance gain. If you have
much larger amount of data, and you have multiple physical drives, you are
more likely to see the performance benefit especially when multiple
partition elements are hit. The administrative overhead far far outstrip
any (if any) performance improvement even if you want the fastest response
with such small amount of data.
Better solution in your case would be using a clustered covering index --
here basically putting all the three columns into the index.
hth
Quentin
"Harman Dhillon" <harmand@.grapecity.com> wrote in message
news:#d5wIoVcDHA.2632@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have a table with 5 million records which has been horizonatal
partitioned
> into 3 tables. Each of these tables are located on different logical
drives
> using filegroups in the same database on a single SQL Server Instance.
> Each table structure is:
> InvID int Identity,
> InvDate datetime
> InvNumber varchar(50)
> InvID and InvDate together form the primary key for each of the tables.
> InvDate is my partioning column...which partitions my data according to
> years.
> I have then created a view using a select * and union all clause which
> selects the data from these tables.
> When querying data selectively i.e. for a specific year using the
partioned
> view i get a performance gain of around 10 % over selection of data from a
> non partioned view i.e. from a single table containing all the 5 million
> records.
> Is their someway in which i can get a better performance gain, as i feel
10%
> gain is not much...some of the articles i have read claim that we can get
a
> performance gain of upto 35%.
>
> I also do not get any performance gain when loading data or when firing a
> select * i.e. selecting all 5 million records.
> Can i get performace gain in these through the concept of horizontal
> partitioning.
> Plz help.
> Regards,
> Harman.
>
>
>
>

Data not display in dropdown lists

I have 2 drop down lists in my report. When you select an item from
the 1st list, the 2nd list gets populated accordingly. For some
records in list 1 you get only a single record in the 2nd drop down.
In these cases, you don't see the item in the drop downlist, but I
tried checking in the source of the page n could see the particular
item. Any idea why this is happening?On Mar 1, 10:01 am, cham...@.gmail.com wrote:
> I have 2 drop down lists in my report. When you select an item from
> the 1st list, the 2nd list gets populated accordingly. For some
> records in list 1 you get only a single record in the 2nd drop down.
> In these cases, you don't see the item in the drop downlist, but I
> tried checking in the source of the page n could see the particular
> item. Any idea why this is happening?
It sounds kind of like you are returning a null value in the 2nd drop-
down list (and possibly the correct one after that). Have you checked
the dataset to determine what the possibilities are for the second
drop-down list given the first one's values?
Enrique Martinez
Sr. SQL Server Developer

Data Migration :- SQL Server1 - SQL Server2

Hi,
I have 2 similar sql server databases DB1 and DB2 with around 450 tables and much data. My problem is I need to copy specific records from all tables in DB1 to corresponding tables in DB2. What I have done right now is, running seperate INSERT scripts for each table like

INSERT INTO DB2..table1 SELECT * from DB1..table1 where code='XX'

I would like to know whether this is the right approach or any other better way or tool available to do so. Also since the no of records are very high, I insert it in blocks say 30,000 records each, so that log file limit will not create problem.

Thanks in advance. Please help

I would use integration Service for a job like this. I nice easy way to start this is to rightclick the database -> tasks ->export data and follow the wizard.

The outcome from this will be an integration service packagde that following can be edited in SQL Server Business Intelligence Development Studio as a normal Integration service object.

Take a look into the toturials in there. They helped me a lot.

see also this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=631236&SiteID=1

|||

other alternatives could be

define views and bcp the data out from DB1 and in to DB2

copy the database files (they should not be accessible) and attach them as DB2

Integration Services and use the Database Copy Task

Regards

Norbert

|||

you can use snapshot replication with row filter

Data Layout in Matrix

Repost from an earlier question. I am trying to use a matrix to display records in three columns per row, like this:
Record1 Record2 Record3
Model Model Model
Descript Descript Descript
Price Price Price
Unable to get the dynamic columns expanding to the right, or set the # of columns.
Below is the conversation so far...
Any help would be greatly appreciated!
---
Thank you for the response Chris. I've been out and unable to test this until today. I set it up as you suggested - I have a matrix with two three static rows and a column group on =RowNumber(Nothing).
I get the following error message:
"A group expression for the grouping â'matrix1_ColumnGroup1â' uses the RowNumber function with a scope parameter that is not valid. When used in a group expression, the value of the scope parameter of RowNumber must equal the name of the group directly containing the current group."
Using the List group name as the RowNumber parameter throws more errors.
What am I missing?
Michael
"Chris Hays [MSFT]" wrote:
> You'll need to use a matrix to get a horizontially growing layout.
> It should have one dynamic column grouping (group on =RowNumber(Nothing))
> and three static rows (right-click in the data area and select "Add Row"
> twice).
> To limit this to three columns per matrix, put the matrix in a list and
> group on something like this: =Ceiling(RowNumber(Nothing)/3)
>
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "OTB6" <OTB6@.discussions.microsoft.com> wrote in message
> news:7EC2C1E0-98C2-4069-B951-D40123C37ABF@.microsoft.com...
> > Trying to find a way to display returned records in (n) rows of 3 columns
> or less like this:
> >
> > Record 1 Record 2 Record 3
> > col 1 col 1 col 1
> > col 2 col 2 col 2
> > col 3 col 3 col 3
> >
> > Record 4 Record 5
> > col 1 col 1
> > col 2 col 2
> > col 3 col 3
> >
> > Can someone point me in the right direction as far as the tools
> (table/matrix/list)?
> >
> > Thanks!
> >
> > Michael<-- okay really feeling like an idiot now, but...
where/how is this attachment retrieved? if sent to the email listed in my profile, I'm not receiving it. I'd REALLY like to see your solution.
m
"Chris Hays [MSFT]" wrote:
> Using the list group name should have worked.
> I'm attaching a working example so you can compare to what you've done to
> find where they differ.
>|||You really should use a real newsreader (like Outlook Express) so you can
receive and post attachments.
(Most web-based newsreaders fail to implement support for attachments.)
I'll be posting this example to my weblog later today so you'll be able to
get it there if you can't use a real newsreader for some reason:
http://blogs.msdn.com/chrishays
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"OTB6" <OTB6@.discussions.microsoft.com> wrote in message
news:8994534E-8F27-4102-832A-071AEC7D1978@.microsoft.com...
> <-- okay really feeling like an idiot now, but...
> where/how is this attachment retrieved? if sent to the email listed in my
profile, I'm not receiving it. I'd REALLY like to see your solution.
> m
> "Chris Hays [MSFT]" wrote:
> > Using the list group name should have worked.
> > I'm attaching a working example so you can compare to what you've done
to
> > find where they differ.
> >
>

Data in dataset, no data in CR?

Hi,
I am using VS.net 2003 with emb. Crystal reports.

I retrieve data from a database using VB.net and populate a dataset. The records in the dataset are OK, but no data is shown in the report. How come? I use the following code:

I created a schema, called DSUrenPerProject.xsd. In the IDE I put 6 tables from a SQLserver-database on it.

Then I created the Crystal report and put some field from different tables on the report.

================ Then I created the next class============

Imports System.Data
Imports System.Data.OleDb
Public Class DataSetConfiguration

Public Shared ReadOnly Property CustomerDataSet() As DataSet
Get
Dim myDataSet As DSUrenPerProject = New DSUrenPerProject
Dim myOleDBConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(DB4D)
Dim myOleDbDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(mSQL, myOleDBConnection)

Try
myOleDbDataAdapter.Fill(myDataSet, "Uren")
Catch ee As Exception
MsgBox(ee.Message)
End Try
Return myDataSet
End Get
End Property
End Class
===========Then I created the next procedure in another form=====
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
Dim m_Year As Integer
Dim m_Month As Integer
Dim m_Day As Integer
m_Year = DatePart(DateInterval.Year, Date.Parse(DTDatum.Text))
m_Month = DatePart(DateInterval.Month, Date.Parse(DTDatum.Text))
m_Day = DatePart(DateInterval.Day, Date.Parse(DTDatum.Text))

Dim m_Str As String
Dim m_Positie As Integer
m_Positie = InStr(cmbProjecten.Text, "-")
m_Str = Mid(cmbProjecten.Text, 1, m_Positie - 1)
Call init_Pr_PrNr(m_Str)
m_ProjectID = CInt(ds.Tables("Projecten").Rows(0).Item("ProjectID"))
Call Close_init()
If RBDag.Checked = True Then
m_Projectnr = Trim(m_Str)
m_Datum = CDate(Format(Date.Parse(DTDatum.Text), "dd/MM/yyyy"))
'Tbv 4DUren
mSQL = "SELECT Medewerkers.Voornaam as MedNaam, Medewerkers.Achternaam, Opdrachtgevers.Naam as OGNaam, "
mSQL = mSQL & "Contactpersonen.Achternaam as CPNaam, Projecten.Projectnummer, Projecten.Projectomschrijving, "
mSQL = mSQL & "CONVERT(char(10),Urenverantwoording.Datum, 120), Urenverantwoording.Uren100, Urenverantwoording.Uren150, "
mSQL = mSQL & "Urenverantwoording.Ziek, Urenverantwoording.Verlof, Urenverantwoording.Diverse "
mSQL = mSQL & "from Medewerkers, Opdrachtgevers, Contactpersonen, Urenverantwoording, Projecten "
mSQL = mSQL & "WHERE Urenverantwoording.ProjID= " & m_ProjectID & " AND DATEPART(YEAR, Urenverantwoording.Datum) =" & m_Year & " AND DATEPART(MONTH, Urenverantwoording.Datum) = " & m_Month & " AND DATEPART(DAY, Urenverantwoording.Datum) = " & m_Day
mSQL = mSQL & " AND Urenverantwoording.MedID = Medewerkers.MedewerkerID "
mSQL = mSQL & "AND Urenverantwoording.OGID = Opdrachtgevers.OpdrachtgeverID "
mSQL = mSQL & "AND Urenverantwoording.CPID = Contactpersonen.ContactpersoonID "
mSQL = mSQL & "AND Urenverantwoording.ProjID = Projecten.ProjectID "
mSQL = mSQL & "ORDER BY Urenverantwoording.Datum"

End If
frmPrint.ShowDialog()
End Sub
=================at the end I created the next class=========

Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class frmPrintForm
Inherits System.Windows.Forms.Form
Private customerReport As ReportDocument
Dim myDataSet As DataSet

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()
'ConfigureCrystalReports()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents myCrystalReportViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.myCrystalReportViewer = New CrystalDecisions.Windows.Forms.CrystalReportViewer
Me.SuspendLayout()
'
'myCrystalReportViewer
'
Me.myCrystalReportViewer.ActiveViewIndex = -1
Me.myCrystalReportViewer.Dock = System.Windows.Forms.DockStyle.Fill
Me.myCrystalReportViewer.Location = New System.Drawing.Point(0, 0)
Me.myCrystalReportViewer.Name = "myCrystalReportViewer"
Me.myCrystalReportViewer.ReportSource = Nothing
Me.myCrystalReportViewer.Size = New System.Drawing.Size(560, 421)
Me.myCrystalReportViewer.TabIndex = 0
'
'frmPrintForm
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(560, 421)
Me.Controls.Add(Me.myCrystalReportViewer)
Me.Name = "frmPrintForm"
Me.Text = "Printen van..."
Me.WindowState = System.Windows.Forms.FormWindowState.Maximized
Me.ResumeLayout(False)

End Sub

#End Region
Private Sub ConfigureCrystalReports()
customerReport = New ReportDocument
Dim reportPath As String = ""
'reportPath = Application.StartupPath.Substring(0, Len(Application.StartupPath) - 3) & "Test.rpt"

reportPath = Application.StartupPath.Substring(0, Len(Application.StartupPath) - 3) & "CR_UrenPerProject.rpt"
customerReport.Load(reportPath)
Dim myDataSet As DataSet = DataSetConfiguration.CustomerDataSet
customerReport.SetDataSource(myDataSet)
myCrystalReportViewer.ReportSource = customerReport

End Sub

Private Sub frmPrintForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
ConfigureCrystalReports()
End Sub
End Class
=============================================

Thank you for your patience. Can someone help me ?

regards, Ger.Open the report and do very database

Data Generator for Sql Server 2005.

Hi !!

I am given the task to make an application in C# of filling the database ( made in sql server 2005) so that we can afterwards use those records for mining etc.. I dont have the slightest clue of how to go about making the data generator. Any ideas?

Thanks .

Checkout out the Visual Studio for Database professional, it has several data generators included.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Data flow task to delete records and then insert records in transaction

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
SunilTry setting RetainSameConnection to TRUE on the connection manager.

|||It was already set to TRUE. Please help meeee..

|||Based on some other threads about similar issues, this may not be SSIS, but related to DTC and the core relational engine. You might try checking some of the other forums to see if they have any suggestions.

|||

Have you tried using a SQL Task (set based) for the deletes followed by your Insert Data Flow Task?

Larry

|||

Hey Larry,

Yes. I did use it.but still the blocking happens. Please suggest

Thanks,

Sunil