Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcException:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and
no default driver specified at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution?
Scott
I've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to SQL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setup
> as a System DSN on the server) tests ok when manually tested. However, when
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcException:
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and
> no default driver specified at
> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Informix
> database while configuring the column mappings etc, but fails upon execution?
> Scott
>

Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExceptio
n:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name
not found and
no default driver specified at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConne
ction(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(
Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections
(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution
?
ScottI've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to S
QL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setu
p
> as a System DSN on the server) tests ok when manually tested. However, wh
en
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExcept
ion:
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source nam
e not found and
> no default driver specified at
> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon
nection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection
s(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component fail
ed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation
.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Inform
ix
> database while configuring the column mappings etc, but fails upon executi
on?
> Scott
>

Data Source Credentials problem

Hi guys.

I've got an Xml DataSource in my report server, used to talk to a web service to retrieve it's data. Now, since this is an Xml DataSource that talks to a Web service, i shouldn't have to give it credentials to run with, or - since the web service runs on the machine right next to my report server - I could use Windows Integrated Auth.

However ... when I try to use Windows Integrated auth. I get back the error:

"This

data source is configured to use Windows integrated security. Windows integrated

security is either disabled for this report server or your report server is

using Trusted Account mode. "


And when I tried to use no credentials, before I can even see the report, I get the error:

"the user data source credentials that are required to execute this report are

not stored in the report server database." - where I just told it that it doesn't need credentials to run this datasource !!!

For the first error you got, there are two possible causes:

1. If you are running RS in sharepoint integrate mode, make sure the authentication mode you set in sharepoint central admin is Windows Integrated, not Trusted Account mode.

2. If you are running RS in non-sharepoint integrate mode, make sure this system properties is set to be true: "EnableIntegratedSecurity".

For the second error, if you set to be none, RS uses a fixed credential configured in Report Server config file to connect to your data source, you have to add it.

|||For the second issue make sure that unattended execution account is initialized. Open Reporting Services Config Manager and goto Execution Account tab|||No, I'm not running in Sharepoint Integrated mode, but I'll try the second resolution and let you guys know what happened, thanx :)|||

Well, here goes the whole story ...

First of all, we're using the Forms Authentication sample in our installation. Thought I should mention that in view of the error I'm about to write ...

Well, I've used the R. Services Configuration tool to set the execution account to a user in my domain. The config tool accepted it fine, only when I tried to run the report I got back:

|||

Finally, i got it to work !!! I created a new user account fot the execution account, one that had no xml special characters in it's uname/pwd and it worked fine.

Now, you guys should forward this to the implementing team because it's just rediculous ...

If the username / password of the Execution Account has any sort of "XML special characters" in it, the WMIProvider will fail to set the exec. account details. Although those credentials actually get written encrypted in the xml config file, so that doesn't seem really necessary.

Anyway, that WOULD be ok, u n l e s s ... if you try to type in the credentials, replacing those special characters with their &xxx; equivalents, the tool will succeed, and write out those details encrypted in the xml file. BUT !!! It won't replace the & to an & in the encrypted string it writes out, so when you try to run reports, login will fail, and you're left there wondering what went wrong.

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

Our production environment has multiple SQL Servers installed and running and
i need to retrieve data from multiple different servers and databases. We
have established multiple Web Service DAL's to retrieve data for out
applications but i cannot see how to leverage that into Reporting Services.
Any ideas?
Thanks in advance
Goaler...Read up on data processing extentions. That should fullfill your needs.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> Our production environment has multiple SQL Servers installed and running
and
> i need to retrieve data from multiple different servers and databases. We
> have established multiple Web Service DAL's to retrieve data for out
> applications but i cannot see how to leverage that into Reporting
Services.
> Any ideas?
> Thanks in advance
> Goaler...|||Thaks Bruce
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||As a follow-up, I think i need a bit more clarification and understanding.
Here is the issue
I need to be able to read multiple predefined shared data sources from
within MS-Reporting Services, to avoid the configuration of connection
information out side of the product. Then to combine the result set and
return it into a new report object.
There must be a way to do this, the designer allows you to create multiple
data sources but you cannot join the data together. Additionally, linked
servers are not an option in my environment.
Thanks again,
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||You can not join datasets together.
Your clarification confused me, I thought you had web services that you
wanted to use to return the data? If so then data processing extensions are
the way to go.
Bruce Loehle-Conger
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:6C959BD5-C681-4E15-96EE-78EA69C282BA@.microsoft.com...
> As a follow-up, I think i need a bit more clarification and understanding.
> Here is the issue
> I need to be able to read multiple predefined shared data sources from
> within MS-Reporting Services, to avoid the configuration of connection
> information out side of the product. Then to combine the result set and
> return it into a new report object.
> There must be a way to do this, the designer allows you to create multiple
> data sources but you cannot join the data together. Additionally, linked
> servers are not an option in my environment.
> Thanks again,
> "Bruce L-C [MVP]" wrote:
>> Read up on data processing extentions. That should fullfill your needs.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "goaler" <goaler@.discussions.microsoft.com> wrote in message
>> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
>> > Our production environment has multiple SQL Servers installed and
>> > running
>> and
>> > i need to retrieve data from multiple different servers and databases.
>> > We
>> > have established multiple Web Service DAL's to retrieve data for out
>> > applications but i cannot see how to leverage that into Reporting
>> Services.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance
>> > Goaler...
>>

data processing extension sample

I'm trying to write some code to retrieve a reports list from a report folder.
Is it possible to create data processing extension in old asp? Either yes
or no, could someone post a link to some sample code, aspx samples would be
just fine?I forgot to mention that I only know VB. So please provide the samples in
VB. Thx a mil!
"JL" wrote:
> I'm trying to write some code to retrieve a reports list from a report folder.
> Is it possible to create data processing extension in old asp? Either yes
> or no, could someone post a link to some sample code, aspx samples would be
> just fine?|||http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
try this one. the best i have found.
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> I forgot to mention that I only know VB. So please provide the samples in
> VB. Thx a mil!
> "JL" wrote:
> > I'm trying to write some code to retrieve a reports list from a report
folder.
> >
> > Is it possible to create data processing extension in old asp? Either
yes
> > or no, could someone post a link to some sample code, aspx samples would
be
> > just fine?|||Thx a lot. But I only want to retrieve the reports list from a report
folder. Do I need to use the same extension? Or is there an alternative
(easier) way? Thx.
"Renato Aloi" wrote:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> try this one. the best i have found.
> []s
> Renato
> "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > I forgot to mention that I only know VB. So please provide the samples in
> > VB. Thx a mil!
> >
> > "JL" wrote:
> >
> > > I'm trying to write some code to retrieve a reports list from a report
> folder.
> > >
> > > Is it possible to create data processing extension in old asp? Either
> yes
> > > or no, could someone post a link to some sample code, aspx samples would
> be
> > > just fine?
>
>|||I saw a sample that demonstrate this... But I don't remmember where... What
I remmember is that you can write some code to do that, using ListChildren
method of Reporting Service Web Server, like this:
dim items() as CatalogItem
items = (new ReportingService).ListChildren("/", True)
then populate a list:
for each item as CatalogItem in items
cboWhatever.Items.Add(new ReportItem(item.name, item.Path))
next
That is it. Do not forget to reference the RS web service...
[]s
Renato
"JL" <JL@.discussions.microsoft.com> escreveu na mensagem
news:F408C4D6-DB48-40F1-A6A5-22BE8E958114@.microsoft.com...
> Thx a lot. But I only want to retrieve the reports list from a report
> folder. Do I need to use the same extension? Or is there an alternative
> (easier) way? Thx.
> "Renato Aloi" wrote:
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > try this one. the best i have found.
> >
> > []s
> > Renato
> >
> > "JL" <JL@.discussions.microsoft.com> escreveu na mensagem
> > news:1E1F4C39-7C3E-4263-B883-D2865507C968@.microsoft.com...
> > > I forgot to mention that I only know VB. So please provide the
samples in
> > > VB. Thx a mil!
> > >
> > > "JL" wrote:
> > >
> > > > I'm trying to write some code to retrieve a reports list from a
report
> > folder.
> > > >
> > > > Is it possible to create data processing extension in old asp?
Either
> > yes
> > > > or no, could someone post a link to some sample code, aspx samples
would
> > be
> > > > just fine?
> >
> >
> >

Data leaked a buffer with ID 1 of type 1

We are using a datareader component to retrieve data from a Pervasive 8.6 database. We have four separate datareader components in various packages retrieving data into our datawarehouse in SQL2005. One of the components has started to fail regularly with the following error.

Date 6/8/2007 3:05:00 AM
Log Job History (LoadMAXDailyBookings)

Step ID 1
Server US-CO-DEN-101
Job Name LoadMAXDailyBookings
Step Name Load Bookings Step
Duration 00:00:37
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Destination Write Bookings Detail" (121)" wrote 0 rows.
End Info
Log:
Name: PipelineBufferLeak
Computer: US-CO-DEN-101
Message: component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
End Log
Log:
Name: OnTaskFailed
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnPostExecute
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnWarning
Computer: US-CO-DEN-101
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

End Log
Warning: 2007-06-08 03:05:36.92
Code: 0x80019002
Source: LoadMAXDailyBookings
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution...

The other components run without any problems as did this one up until we installed service pack 2. We then started getting these occasional failures. Any thoughts on what is happening here?

Thanks,

Phil

Do you have package logging turned on? The PipelineBufferLeak, I believe, is a warning, not an error, and hence would not stop execution.

Turning on package logging might help get a more clear error message....|||

I do have logging turned on and the error in the log is more vague than within the job history. I'm copying in the first rows indicating the error from the log. The execution is halted due to whatever error is happening.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookings,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

|||Make a copy of that package and strip everything out of the data flow except for the data reader source. Then hook it into a row count transformation. Re-run the package. What happens?|||The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.|||

pkdenver wrote:

The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.

The only reason I suggested this is because the error message returned is a DataReader failure message, so this takes out all processing that might unduly influence things. Can you use something other than a DataReader Source? OLE DB Source, for instance?|||

I see your point regarding isolating the datareader. As recommended last week, I deployed a package which performs the query and dumps to a rowcounter. We get the same failure as the regular job. Both jobs failed on one day (Sat). It ran fine on Sun and Mon. Here are the error rows from the logger: I'm wondering if we are getting a timeout on this query as it is a more complex query than the other three jobs running against the DB.

Regarding an alternate driver, the Pervasive OLE DB driver is very unstable in the SSIS environment and so it can't be used at all. We are looking at the timeout value to see if the Pervasive ODBC driver can be tweaked.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016268,0x,Execute phase is beginning.

OnPipelinePrePrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,PrimeOutput will be called on a component. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnPipelinePostPrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,A component has returned from its PrimeOutput call. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,50,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Post Execute
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,50,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Cleanup
PipelineBufferLeak,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
OnTaskFailed,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnPostExecute,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnWarning,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
PackageEnd,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1,0x,End of package execution.

Data integrity

Hi,
Does any one know how to apply/enhance the database's integrity.
During study, i learn the theory of how to make sure the data retrieve and
update is correct. But i dont know how to apply in database development.
For example: I have a table. 2 persons are accessing the table in the
following sequence:
Person A, access the table.
Person B, access the table.
Person A update new value (such as rental_fee)
Person B also update the rental_fee value.
Person A exit
Person B also exit
At this moment, record updated by person A has been overwrited by person B.
How to prevent it?
Thanks in advance.
Best regards,
GLOne method is to use optimistic concurrency. This technique checks to see
if the original value was modified by another user:

> Person A, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person B, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person A update new value (such as rental_fee)
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above succeedes

> Person B also update the rental_fee value.
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above raises an error
A common practice to use a rowversion data type (formally timestamp) for the
concurrency check. This simplifies concurrency checking because the value
is automatically changed by SQL Server whenever any row value changes and
you don't need special handling of NULLs.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
> Hi,
> Does any one know how to apply/enhance the database's integrity.
> During study, i learn the theory of how to make sure the data retrieve and
> update is correct. But i dont know how to apply in database development.
> For example: I have a table. 2 persons are accessing the table in the
> following sequence:
> Person A, access the table.
> Person B, access the table.
> Person A update new value (such as rental_fee)
> Person B also update the rental_fee value.
> Person A exit
> Person B also exit
> At this moment, record updated by person A has been overwrited by person
> B.
> How to prevent it?
> Thanks in advance.
> Best regards,
> GL|||Thanks for ur help.
I have another question, do i need to configure the sqlserver 2000 in order
to do that?
In addition, creating a column (auto generate number) act as one of the
primary key is a good practice?
"Dan Guzman" wrote:

> One method is to use optimistic concurrency. This technique checks to see
> if the original value was modified by another user:
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above succeedes
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above raises an error
> A common practice to use a rowversion data type (formally timestamp) for t
he
> concurrency check. This simplifies concurrency checking because the value
> is automatically changed by SQL Server whenever any row value changes and
> you don't need special handling of NULLs.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
>
>|||Dan's code will work straight out of the box. There is no extra SQL
Server config needed. Timestamp is a native SQL datatype, so you don't
need to reconfigure anything to use the timestamp for the row versioning
either.
With regards to using an identity column (auto generated sequential
integer) as a primary key for a table, there are various debates about
that. Many people would say that's often the best way to define primary
keys in real life, while others (the most vocal being one Joe Celko, who
has spent some time on the ANSI committee and published some material on
the subject) would disagree and advise you to use a natural key as your
primary key for the table (like a SSN or VIN or ActionType/ActionDate
combination for example).
I won't get into the whole debate (it's a rather raging topic) except to
say both sides of the argument can be justified. If you do create a
clustered index on an identity column, however, you can substantially
reduce pages splits (and therefore I/O) on inserts because all new data
will be located at the end of the index and no reordering within the
index will be necessary. This will create a "hotspot" in effect at the
end of the index. But there are many other factors to consider, not the
least of which is personal preference, when declaring one or more
columns to be the primary key of a table.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Daniel wrote:
>Thanks for ur help.
>I have another question, do i need to configure the sqlserver 2000 in order
>to do that?
>In addition, creating a column (auto generate number) act as one of the
>primary key is a good practice?
>
>"Dan Guzman" wrote:
>
>|||> I have another question, do i need to configure the sqlserver 2000 in
> order
> to do that?
There's no special SQL Server configuration needed since optimistic
concurrency is handled by the application.

> In addition, creating a column (auto generate number) act as one of the
> primary key is a good practice?
This subject is often discussed in this forum so you can fund many pros and
cons with a google search. It's a common practice to use a surrogate value,
such as an IDENTITY column, as the primary key. Whether or not this
practice is good depends on the specific situation. There are those who
believe religiously that one should always use natural keys and others who
always employ surrogate keys as a knee-jerk reaction. Personally, I
evaluate each situation individually.
Hope this helps.
Dan Guzman
SQL Server MVP|||ok. i got it..thanks for ur respond.
"Dan Guzman" wrote:

> There's no special SQL Server configuration needed since optimistic
> concurrency is handled by the application.
>
> This subject is often discussed in this forum so you can fund many pros an
d
> cons with a google search. It's a common practice to use a surrogate valu
e,
> such as an IDENTITY column, as the primary key. Whether or not this
> practice is good depends on the specific situation. There are those who
> believe religiously that one should always use natural keys and others who
> always employ surrogate keys as a knee-jerk reaction. Personally, I
> evaluate each situation individually.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
>

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 from tables not likned

Hi ;
Can some please tell me how to retrieve data from a table which is not linked For example i want total sale for aperiod to get the sale percentage of respective departments with respect to total sale or is there a way to call a sectionHow did the tables relate to each other? If you are not able to join, make use of sqlquery property and write that as sub query