Data tab works great but i can't get it work in Preview tab in reporting services

This code works great in data tab but when i go to preview it i get a len function error.
I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
ON A.FPO_ID = C.FPO_ID
WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
AND (LEN(@.year)<1 OR LEN(@.year)>=1)
AND (LEN(@.organization)<1 OR LEN(@.organization)>=1
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat exactly is the error ? Why I am asking is that len must be evaluating
some numeric value e.g year parameter must be having numeric and Len will
take string expression only. So Just have a look at all the parameters again
and see their data type.
Amarnath
"Dre" wrote:
> This code works great in data tab but when i go to preview it i get a len function error.
> I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
> SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
> FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
> ON A.FPO_ID = C.FPO_ID
> WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
> AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
> AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
> AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
> AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
> AND (LEN(@.year)<1 OR LEN(@.year)>=1)
> AND (LEN(@.organization)<1 OR LEN(@.organization)>=1)
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Data Source Views

I'm looking for examples of using Data Source Views as data source within a Data Flow. I have looked through the code examples supplied with the CTP but no joy.

Thanks
Al

You do not have to write code to use DSVs with Data Flows in SSIS.

You need to create a new Data Source and then a new DSV based on that Data Source.
Now add a new connection based on the Data Source to the Data Flow.
Add a OLEDB Source component and then you will have the choice of using the DSV in the OLEDB Source component UI.|||This will hopefully help

http://wiki.sqlis.com/default.aspx/SQLISWiki/DataSourceViewsAndSourceAdapters.html

Allan

"Alasdair Anderson@.discussions.microsoft.com" Anderson@.discussions.microsoft.com> wrote in message

news:88786eb8-c940-4590-b62a-485ae15b1803@.discussions.microsoft.com:

> I'm looking for examples of using Data Source Views as data source

> within a Data Flow. I have looked through the code examples supplied

> with the CTP but no joy.

>

> Thanks

> Al|||Very helpful guys thanks.

I have followup. How would you construct a data source view to look across data sources.

For example I have a Customers Dimension but the customer information sits across 3 hetrogeneous sources. Is it possible to sit a DSV across the 3 different databases to provide one view of customer to be used in the ETL?|||

Alasdair,
yes, this is possible. I don't know how to set it up specifically but I know you can definately do it.

-Jamie

Data Source Views

I'm looking for examples of using Data Source Views as data source within a Data Flow. I have looked through the code examples supplied with the CTP but no joy.

Thanks
Al

You do not have to write code to use DSVs with Data Flows in SSIS.

You need to create a new Data Source and then a new DSV based on that Data Source.
Now add a new connection based on the Data Source to the Data Flow.
Add a OLEDB Source component and then you will have the choice of using the DSV in the OLEDB Source component UI.|||This will hopefully help http://wiki.sqlis.com/default.aspx/SQLISWiki/DataSourceViewsAndSourceAdapters.html Allan "Alasdair Anderson@.discussions.microsoft.com" wrote in message news:88786eb8-c940-4590-b62a-485ae15b1803@.discussions.microsoft.com:
> I'm looking for examples of using Data Source Views as data source
> within a Data Flow. I have looked through the code examples supplied
> with the CTP but no joy. >
> Thanks
> Al|||Very helpful guys thanks.

I have followup. How would you construct a data source view to look across data sources.

For example I have a Customers Dimension but the customer information sits across 3 hetrogeneous sources. Is it possible to sit a DSV across the 3 different databases to provide one view of customer to be used in the ETL?|||

Alasdair,
yes, this is possible. I don't know how to set it up specifically but I know you can definately do it.

-Jamie

Data returned from .WriteXML is different than what is returned in query analyzer.

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.

Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).

Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.

If someone is able to help me figure this out, I would appreciate it.

1. public DataSet GetMarketList(string region, string marketRegion)
2. {
3. string sql = @."SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode";
5. DataSet dsMarketList = new DataSet();
6. SqlConnection sqlConn = new SqlConnection(intranetConnStr);

7. SqlCommand cmd = new SqlCommand(sql,sqlConn);
8. sqlConn.Open();
9. SqlDataAdapter adapter = new SqlDataAdapter(cmd);

10. try
11. {
12. adapter.Fill(dsMarketList);

13. String bling = adapter.SelectCommand.CommandText;//BRG
14. dsMarketList.DataSetName="RegionMarket";
15. dsMarketList.Tables[0].TableName = "MarketList";
16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4
18. // from the SQL query
19. }
20. catch(Exception e)
21. {
22. // Handle the exception (Code not shown)

I think that you need to look at what data isn't being returned in your app. Can you load the XML file into Excel? You could then paste the SQL Analyzer results into Excel and look at the difference.

Also, there isn't an order by clause in your SQL, so SQL Server is free to return the results in a different order between invocations of the same SQL. (It rarely happens, but it does occasionally.)

|||Ugh !!! I figured out what the problem is. Thanks !sql

data reader problem

Hi, i dont know whats gone wrong! but all of a sudden it seems to be throwing an error, i have looked at my previous code and it matches exactly when it was working, here is the code below

int i = 0;for (i = 1; i <= 3; i++)

{

//This gets the stock ID from the textbox.

string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text;

//This is the sql statement.

string sql ="SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmd =newSqlCommand(sql, myConn);

myConn.Open();

//This is a reader for the results to go in.

SqlDataReader dr = sqlCmd.ExecuteReader();

//This reads the first result from the sqlReader

dr.Read();

//This sets the title label text to the value of the description column.

TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i);

string strtxtDesc = currentBox1.Text;

strtxtDesc = dr["title"].ToString();

};

myConn.Close();

i = 0;

the error its throwing is this

CS1519: Invalid token '(' in class, struct, or interface member declaration for the line myConn.Open()

does anybody have any idea how to solve this?

Jez

First thing is, you need to close the reader inside the loop, and for execute reader method passCommandBehavior.CloseConnection.Hopefully this fixes u r errors

|||

hi thanks for the tips, i have closed it inside the loop, but i dont get what you mean about the .closeconnection, could you please expalin more or give me an example in my code that i posted?

|||

http://msdn2.microsoft.com/en-us/library/aa326246(VS.71).aspx check this out

|||

Unmatched braces somewhere.

Data Reader Problem

Hi All,

I got an error while running this code.There is already an open DataReader associated with this Command which must be closed first

How can I resolve the error?

protected void Page_Load(object sender, EventArgs e)
{
string sql;

SqlConnection Connection = new SqlConnection("ConnectionString");
sql = "SELECT PO_SE_Line_ID FROM PO_STOCK_QUERY_LINE_DETAILS WHERE TRANS_NUM ='TR-A-00-01-93'";

SqlCommand command = new SqlCommand(sql, Connection);
SqlDataReader Dr;
Connection.Open();
Dr = command.ExecuteReader();


while (Dr.Read())
{

sql = "SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93' and PO_SE_Line_ID=" + Dr["PO_SE_Line_ID"].ToString();

SqlCommand command1 = new SqlCommand(sql, Connection);

SqlDataReader Dr1;

Dr1 = command1.ExecuteReader();

while(Dr1.Read())
{

Response.Write(Dr["SUPPLIER_ITEM_CODE"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_MAN_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_PAT_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ITEM_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ADDIT_DESC"].ToString());

}
Dr1.Close();

}
Dr.Close();

I tried to close the first data reader before opening the second data reader.still the error persists.

I suggest that you open a second data connection.

|||

Hi,

If I open a second connection it is showing the error there is already an open connection......

When I tried to close and open the connection Iam getting the error as Invalid attempt to read data when Datareader is closed.

|||

Hi

I guess that your facing problem because your using same sql string for both the command.

the first command already uses the string and you are trying to change that script while datareading, so obviously it gives error

so my suggestion is to use another string named like sql1, so your problem will be solved out.

I hope this will helpful to u...

|||

It's better create a stored procedure and return the results once. In your design, the performance could be a problem.

|||

Howdy!

I generally think nested queries within queries in the code is bad form (like running a query than gens a value to use in another recursion of queries).

I noticed that your first query runs against PO_STOCK_QUERY_LINE_DETAILS tables with a TRANS_NUM filter, then your 2nd query uses a field value from the first to run against the same table. I'm not sure what the design goal was, but it looks like you could simply run the query one time?

In other words, you're running a query againt a table to get a list of values to run against the same table, just run this query with a single data reader:

SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93'

(If you wind up needing for example, a master/detail view, you'd be better off using a join or creating a relationship object in code after returning 2 tables via a dataset).

DT

Data Read / Write problem with concurrent users on with dll

Hi,

I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder.

We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack:

06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:\My Path\Validator\Validator.NET\Data\DatabaseEvents\DatabaseEventManager.cs:line 92
at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\CATCard.cs:line 94
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 215
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 147
at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 369
at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 353
at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\PrePrintedLetter.cs:line 282
at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:\My Path\Projects\Validator\Validator.NET\ValidationProcessor.cs:line 82
at clear_applications_scan_applications.ProcessValidation()

It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?

I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!!

Any ideas/suggestions would be greatly appreciated.

Thanks

Hi Assimalyst,

Based on the exception message, we can see that this is not because of reusing of the SqlDataReader, but reusing the SqlConnection.

Generally, on a single SqlConnection, there can only be 1 open SqlDataReader exist at the same time. If a using is trying to open another one, this exception is thrown.

In ADO.NET 2.0, a new concept is involved called Multiple Active Result Sets (MARS). This enables you open multiple DataReaders on a single connection. To achieve this, you only need to add "MultipleActiveResultSets=True" in your connection string. That will prevent the exception from being thrown.

Please check the following links for more information:

http://msdn2.microsoft.com/en-us/library/cfa084cz.aspx

HTH. If anything is unclear, please feel free to mark this post as Not Answered and post your reply. Thanks!

sql

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 is truncated /SQL query help

Please help me to understand why the data is being truncated. Please run
this code. If you notice, the final @.select does not show all the names
which are in the table. It is cutting off. (even though the length of
the @.Name is declared to varchar(8000)). What I am missing?
Thanks for your help.
create table #Names
(Name sysname)
insert into #Names values('user_audit_log 13-Mar-2006.xls')
insert into #Names values('user_audit_log 14-Mar-2006.xls')
insert into #Names values('user_audit_log 15-Mar-2006.xls')
insert into #Names values('user_audit_log 16-Mar-2006.xls')
insert into #Names values('user_audit_log 17-Mar-2006.xls')
insert into #Names values('user_audit_log 18-Mar-2006.xls')
insert into #Names values('user_audit_log 19-Mar-2006.xls')
insert into #Names values('user_audit_log 20-Mar-2006.xls')
insert into #Names values('user_audit_log 21-Mar-2006.xls')
insert into #Names values('user_audit_log 22-Mar-2006.xls')
declare @.Name varchar(8000)
select @.Name = ''
select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
[Name]
from Names
select * from #Names
select @.Name --final
*** Sent via Developersdex http://www.examnotes.net ***Test wrote on Thu, 23 Mar 2006 07:32:49 -0800:

> Please help me to understand why the data is being truncated. Please run
> this code. If you notice, the final @.select does not show all the names
> which are in the table. It is cutting off. (even though the length of
> the @.Name is declared to varchar(8000)). What I am missing?
> Thanks for your help.
> create table #Names
> (Name sysname)
> insert into #Names values('user_audit_log 13-Mar-2006.xls')
> insert into #Names values('user_audit_log 14-Mar-2006.xls')
> insert into #Names values('user_audit_log 15-Mar-2006.xls')
> insert into #Names values('user_audit_log 16-Mar-2006.xls')
> insert into #Names values('user_audit_log 17-Mar-2006.xls')
> insert into #Names values('user_audit_log 18-Mar-2006.xls')
> insert into #Names values('user_audit_log 19-Mar-2006.xls')
> insert into #Names values('user_audit_log 20-Mar-2006.xls')
> insert into #Names values('user_audit_log 21-Mar-2006.xls')
> insert into #Names values('user_audit_log 22-Mar-2006.xls')
> declare @.Name varchar(8000)
> select @.Name = ''
> select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
> [Name]
> from Names
> select * from #Names
> select @.Name --final
> *** Sent via Developersdex http://www.examnotes.net ***
Works fine here. I'll assume you are using Query Analyzer - it's got a
default max length on display. Go to the options and increase it.
Dan|||Where are you testing your code, in Query Analyzer?
See Tools - Options... - Results - Maximum characters per column (Max value
8k)
AMB
"Test Test" wrote:

> Please help me to understand why the data is being truncated. Please run
> this code. If you notice, the final @.select does not show all the names
> which are in the table. It is cutting off. (even though the length of
> the @.Name is declared to varchar(8000)). What I am missing?
> Thanks for your help.
>
> create table #Names
> (Name sysname)
> insert into #Names values('user_audit_log 13-Mar-2006.xls')
> insert into #Names values('user_audit_log 14-Mar-2006.xls')
> insert into #Names values('user_audit_log 15-Mar-2006.xls')
> insert into #Names values('user_audit_log 16-Mar-2006.xls')
> insert into #Names values('user_audit_log 17-Mar-2006.xls')
> insert into #Names values('user_audit_log 18-Mar-2006.xls')
> insert into #Names values('user_audit_log 19-Mar-2006.xls')
> insert into #Names values('user_audit_log 20-Mar-2006.xls')
> insert into #Names values('user_audit_log 21-Mar-2006.xls')
> insert into #Names values('user_audit_log 22-Mar-2006.xls')
>
> declare @.Name varchar(8000)
> select @.Name = ''
> select @.Name = @.Name + case when len(@.Name) > 0 then ',' else '' end +
> [Name]
> from Names
> select * from #Names
> select @.Name --final
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Alejandro. It works!
I am running this code in QA on my workstation. The length was set to
256. I changed it to 8K. But this code is going to run on two others
servers as a job. Do I need to reset the length everytime? Can I do it
though the code?
Thanks for your hgelp!
*** Sent via Developersdex http://www.examnotes.net ***|||> I am running this code in QA on my workstation. The length was set to
> 256. I changed it to 8K. But this code is going to run on two others
> servers as a job. Do I need to reset the length everytime? Can I do it
> though the code?
This has nothing to do with the server or the query... this is just the
default behavior of the client presentation tool you happen to be using. If
you use the same installation of QA, the setting should stick if you run the
same query against any server. Is this report always going to be returned
to QA?|||Aaron,
It would be a stored proc that will run as a job. The proc calls
xp_sendmail for sending multiple files to the recipents. I am building a
string (see my code for @.Name) that will contain multiple file names so
I can pass it to xp_sendmail. It is a sending multiple attachments thru
email task!
As you said, it is only client workstation settings, so in my case, I
should be okay, rite?!
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||> As you said, it is only client workstation settings, so in my case, I
> should be okay, rite?!
Yes. Rite.

Data from a recordset

Hi !
I'm new to CR 8.0, so here's my question:

I have a recordset in my VB6 code and want to make a report showing that information. How do I specify in the report's design which fields to show?

If the report used data from a table, then I could select the database fields and paste them in the "Details" section, but as I use a recordset created from VB6, I don't know how to "paste" the fields...

Just in case, this is my code:

Set rstData = db.Execute ("Select * From ...")

Dim Report As New CRAXDRT.Report
Dim CRApp As New CRAXDRT.Application

Set Report = CRApp.OpenReport(gstrPathReports & "\Persons.rpt")

Report.Database.SetDataSource rstData

CRApp.LogOnServer "P2SSQL.DLL", gstrCfgServerName & "\" & gstrCfgInstanceName, _
gstrCfgDBName, "sa", "mypwd"

CRViewer.ReportSource = Report

CRViewer.ViewReportYOu can create a ttx file for your fields, then pass the recordset to Crystal and Crystal will populate the fields in the ttx file in order.

To make a ttx file, use either Method 1 or Method 2:

Method 1: Pass your recordset to this function:

Declare Function CreateFieldDefFile Lib "p2smon.dll" (lpUnk As Object, _
ByVal fileName As String, ByVal bOverWriteExistingFile As Long) As Long

'--------------------------
'LpUnk - The active data source used to create the field definition file.
' In C or C++, this is a pointer to an IUnknown derived COM interface
' relating to a DAO or ADO Recordset. In Visual Basic, this is a
' Recordset or Rowset object.
'Filename - The path and file name of the field definition file to be created.
'bOverWriteExistingFile - If a field definition file already exists with the
' specified path and file name, this flag indicates whether or not to
' overwrite that file.
'--------------------------

'rsRecordset is an ADO Recordset (I haven't tried it with DAO and it doesn't look like it would work with RDO
Call CreateFieldDefFile(rsRecordset, App.Path & "\" & strTtxFileName, True)

Method 2: The manual way

Open Notepad and type your FieldName (doesn't have to match the name of the column, but it makes it easier if it does), then hit Tab and type the DataType (if it's a string, hit Tab again, then type the Max length of the field), then (if you want, it's not necessary) hit Tab again and put in sample data (like what you see for Boolean, True would be the sample data). Sample Data has no effect on your report, I usually leave it out since it's not necessary.

Your ttx file will look like this (I have included all data types it supports):

BLOB BLOB
Boolean Boolean True
Byte Byte
Currency Currency
Date Date
Long Long
Memo Memo
Number Number
Short Short
String String 50
;Comment

If you forget to use a tab in between the FieldName and the DataType, you won't get an error, but that column won't show up in your report. Also, you can include a comment by preceding it with a semicolon.

Save the file as FileName.ttx. (Notepad will default the filename to FileName.ttx.txt, so make sure you delete the .txt from the end or you won't be able to use the file.

After your ttx file is created, open Crystal Reports, choose Add Database, then expand More Data Sources, Active Data. Browse to your ttx file that you created. Now use those fields as you normally would.

The code you have should get your recordset to Crystal.|||I'll try it out, thank you very very much !!!!