DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

datasource on remote SQL server?

Disclaimer: I'm a web developer, not a DBA or network admin, so if the following is a totally stupid question, please be gentle--

I am trying to come up with a somewhat decent testing environment (they have no staging area) for a client for whom I'm converting a static HTML site to ASP pulling content from SQL Server.

I'm running Windows 2000 Professional at home and its slightly-watered-down version of IIS webserver, which is fine for testing ASP that doesn't talk to a database. My question is, is there any way that my home webserver can talk to their production SQL Server installation?

My working setup is this--I can connect via Remote Desktop to their webserver machine, which has a copy of Enterprise Manager installed, which has a database registration for a SQL Server installation on a physically separate machine. Given those couple of degrees of separation, I have no idea how locked-down the actual SQL Server box might or might not be as far as the outside world goes.

So, several questions:

Is there a way, without access to the machine console, to figure out if the SQL Server box has an Internet IP address I can try to set up as an ODBC DSN?

Given access to Enterprise Manager for that SQL Server install, are there settings I can check and/or configure to make this happen?

And if this is the sort of thing I wouldn't be able to resolve myself, is there anything specific I can ask the client's tech staff to see if I can get what I need?

I hope this is coherent. If the answer is "dude, you need to talk to the network admin" I'll do that... it's just I'd always rather learn how to do something myself when it's possible :)Since you can get a terminal session going between yourself and them, it sounds like you can get onto their network. If you are using the webserver's name to connect to the webserver, then you are using their DNS, and you are practically all set.

begin rant()
As a rule, no IP addresses should be used in code, except for raw
testing. Any server should be addressed by it's DNS or other network
name. This way, if an IP address changes, or a machine is replaced,
the application is not affected.
end rant;

If you can not use a DNS name (I have seen some VPN connections forbid using others' DNS Servers) for the SQL server on your local machine, and you must use IP addresses, I would say ping the name of the SQL Server, and see what comes back. Then ping that address from your local machine to make sure that you can get there.

As for how to connect to the DB itself, you will have to talk to their administrator. Ideally you should get a read-only login for the particular database you want to connect to. Many small shops use the sa login for all of their apps, but that is not very secure.

Hope this helps.|||Hi, thanks for your response.

I had tried creating a new datasource using the SQL server machine name while connected via terminal session to the webserver. I get an error:

Connection failed:
SQLState '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.

I feel your pain on IP addresses in code, but I only want it to try to create a datasource on my home machine, since creating a datasource using the machine name doesn't seem to be working. Pinging the SQL box from the webserver console just gets me an internal network IP; a ping of that IP from my home machine (with terminal session running) times out.

In any event, using an external IP, if it exists, would seem preferable to a machine name for my purposes, because it means I don't have to keep a remote desktop session open 24/7 for the next month.

I have no problem connecting to the DB when using Enterprise Manager from the webserver console--it's set up to use Windows authentication--I believe I could ask for & receive SA access if it were necessary though.|||When you get to the second screen of the ODBC Configuration (where it asks for a user and password), click on the Client Configuration button. See if you can change the NetLib to TCP/IP. Sometimes the NetLibs can be a little funny about actually finding a server. This would be on the client's webserver, of course.|||I did try that, in spite of not knowing what the heck I was doing--figured I couldn't break what wasn't work in the first place. No love, though--thanks for the suggestion.

My client is using <a href="http://links.10026.com/?link=http://www.neoteris.com/products.html">Neoteris</a> to manage remote sessions. I don't know much about the technical details of how it works (and because their support pages require a customer id and password, it seems they don't WANT me to know) but I found a diagnostic button to click and I can see where it is mapping a number of machine names on their network to IP addresses in my home network's internal space. I see the webserver machine name there; I don't see the SQL box machine name.

I broke down and emailed my contact to ask if they can set up remote access authorization to the SQL box too--I still don't like the notion of having to keep the Neoteris session (based in a java applet in a web browser) running 24/7. It's gonna be fun the first time my DSL hiccups and the client calls me all "I can't connect to the test site" and I'm on the other side of town--but it would be a solution.

datasource failed to logon

Hi, I realy don't know what happened. But from this morning I receive logon
failures on my datasourse.
I use NTLM security but receive following error when opening a report:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'transportnet'.
(rsErrorOpeningConnection) Get Online Help
Cannot open database "transportnet" requested by the login. The login
failed. Login failed for user 'mydomain\SERVER$'.
The report server is configured for forms authentication. The datasource is
connecting to a sql server on another machine but in the same domain.
When I use the sql security (sa account) everything is working fine.
What could be the problem?
Thanks
BartHello Bart,
I understand that you could not use the server credential to connect to the datasource you use in the
report. If I misunderstood your concern, please feel free to let me know.
I would like to suggest you check the credential in the remote sql server whether it has the proper
permission or not first.
Also, please let me know whether you could use a UDL file to connect to the SQL server.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wie,
Thats correct.
Via an udl file is no problem.
What found strange is the account Server$. The server that is performing the
request is indeed called 'SERVER'. Could it be that this is the account for
the 'named pipes'? or something.
Thanks Bart
"Wei Lu [MSFT]" wrote:
> Hello Bart,
> I understand that you could not use the server credential to connect to the datasource you use in the
> report. If I misunderstood your concern, please feel free to let me know.
> I would like to suggest you check the credential in the remote sql server whether it has the proper
> permission or not first.
> Also, please let me know whether you could use a UDL file to connect to the SQL server.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Bart,
It seems that your application pool of reporting services is using a local system or local service
account. Thus, it will use the machine account to connect to the remote database.
Please use the Network Service for the application pool identity and make sure it has proper
permission in the remote database.
Otherwise, you could use a domain account as the identity.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
I already tried that, but just the same error.
What else?
Thanks
Bart
"Wei Lu [MSFT]" wrote:
> Hello Bart,
> It seems that your application pool of reporting services is using a local system or local service
> account. Thus, it will use the machine account to connect to the remote database.
> Please use the Network Service for the application pool identity and make sure it has proper
> permission in the remote database.
> Otherwise, you could use a domain account as the identity.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Bart,
How you configure the datasource? If you use the domain account as the identity of the application
pool, did you still get the same error message state that you could not open the database with login
domain\server$ ?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei,
I changed the application pool identity with the administrator account of
the domain, but the same error.
What else can I try?
Bart
"Wei Lu [MSFT]" wrote:
> Hello Bart,
> How you configure the datasource? If you use the domain account as the identity of the application
> pool, did you still get the same error message state that you could not open the database with login
> domain\server$ ?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello bart,
I would like to know the following things:
1. What's the datasource authentication mode you use? Do you use the
Windows Integrated Security?
2. What's the directory security setting for the Reports and ReportServer
virtual directories? Please do not enable anonymouse access and do enable
the Integrated Windows Authentication.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
1. NTLM security
2. I use forms authentication, so I had to enable anonymouse access, but
this was never a problem.
"Wei Lu [MSFT]" wrote:
> Hello bart,
> I would like to know the following things:
> 1. What's the datasource authentication mode you use? Do you use the
> Windows Integrated Security?
> 2. What's the directory security setting for the Reports and ReportServer
> virtual directories? Please do not enable anonymouse access and do enable
> the Integrated Windows Authentication.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Bart,
Since you are using the Form Authentication, by default, form
authentication will use the local Network Service account to access the
datasource. So when you set the datasource to use the windows integrated
authentication mode, you will use the Network Service to access the remote
datasource, that pass the Server$ account to the remote server.
To resolve this issue, since you are using the Form Authentication, I
suggest you create a new SQL Login in the remote server and use this SQL
Login in the data source to access the remote server. You don't have to use
the Windows Integrated Authentication in this scenario.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
This is not an option because I use the logged in user for filtering data in
some views
Bart
"Wei Lu [MSFT]" wrote:
> Hello Bart,
> Since you are using the Form Authentication, by default, form
> authentication will use the local Network Service account to access the
> datasource. So when you set the datasource to use the windows integrated
> authentication mode, you will use the Network Service to access the remote
> datasource, that pass the Server$ account to the remote server.
> To resolve this issue, since you are using the Form Authentication, I
> suggest you create a new SQL Login in the remote server and use this SQL
> Login in the data source to access the remote server. You don't have to use
> the Windows Integrated Authentication in this scenario.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Bart,
Since you are using the Form Authentication, when you use the windows
integrated authentication mode, it will use the working process account to
connect to the windows resources.
So I am not sure how you are using the login user to filter the data in the
view. I assume you are using the filter in the report directly. If I am
offset, please feel free to let me know.
In this scenario, you still you could use the SQL Login because Reporting
Services is using the SQL Login to access the datasource to get the data
and you could filter as the Form User in the reporting services.
If you are using stored procedure to filter the data in the view by the
login user, I assume you add some parameters in the stored procedure to
filter the data. You still could use the stored procedure to filter the
data when you use the SQL Login and pass the parameter to it.
Please let me know how you use the login user to filter the data in the
view so that I can provide further suggestions. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
You are 100% correct.
I use the User.UserID from the default properties in reporting server to
filter data based on the user logged in.
I was misleaded by a report that was running before on an internal server
that used ntlm security.
I changed it to use the user.userid instead of the sql suser_name() function
and it worked again.
Thanks a lot for you help
Bart
"Wei Lu [MSFT]" wrote:
> Hello Bart,
> Since you are using the Form Authentication, when you use the windows
> integrated authentication mode, it will use the working process account to
> connect to the windows resources.
> So I am not sure how you are using the login user to filter the data in the
> view. I assume you are using the filter in the report directly. If I am
> offset, please feel free to let me know.
> In this scenario, you still you could use the SQL Login because Reporting
> Services is using the SQL Login to access the datasource to get the data
> and you could filter as the Form User in the reporting services.
> If you are using stored procedure to filter the data in the view by the
> login user, I assume you add some parameters in the stored procedure to
> filter the data. You still could use the stored procedure to filter the
> data when you use the SQL Login and pass the parameter to it.
> Please let me know how you use the login user to filter the data in the
> view so that I can provide further suggestions. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello bart,
Thanks for the update and if you have any question, please feel free to let
me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Datasets that rely on parameters

Hi,
I'm trying to create a report under the following conditions:
1. I have a central database that has stored procedures that require @.db as
the parameter so you can specify where the data is coming from.
2. The report I have only wants data from a particular record, so I created
a second dataset to use in a dropdown list.
3.That second dataset uses a stored proc that requires a parameters @.id and
@.db
The problem is that the columns returned from the stored proc has the @.id
are not recognized when I try to specify them as the "value field" and the
"label field" in the Report Parameters Dialog box.
I suspect this is because it needs to know what the @.db is.
1. How do I get the second stored proc to be recognized by the Report
Parameters Dialog box?
2. How do I pass the @.db to the second stored proc without user intervention?
thanks,
-TrishIf I understand you correctly, what you are trying to do is called cascading
parameters. Search books on line for that and see if it answers you
question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:FCF8157D-2E15-4FF7-B70D-DFB3955C9FE8@.microsoft.com...
> Hi,
> I'm trying to create a report under the following conditions:
> 1. I have a central database that has stored procedures that require @.db
> as
> the parameter so you can specify where the data is coming from.
> 2. The report I have only wants data from a particular record, so I
> created
> a second dataset to use in a dropdown list.
> 3.That second dataset uses a stored proc that requires a parameters @.id
> and
> @.db
> The problem is that the columns returned from the stored proc has the @.id
> are not recognized when I try to specify them as the "value field" and the
> "label field" in the Report Parameters Dialog box.
> I suspect this is because it needs to know what the @.db is.
> 1. How do I get the second stored proc to be recognized by the Report
> Parameters Dialog box?
> 2. How do I pass the @.db to the second stored proc without user
> intervention?
> thanks,
> -Trish

dataset question

hi,
i am trying to run the following query in the dataset which works fine when
i have a number in the branch_code
SELECT tbl_calendar.date, tbl_day_type.day_type
FROM tbl_calendar INNER JOIN
tbl_day_type ON tbl_calendar.day_type = tbl_day_type.type_id
WHERE (tbl_calendar.date BETWEEN
(SELECT opened_date
FROM
HOBISINT.account_statistics.dbo.t_branches
WHERE branch_code = 56) AND GETDATE())
Problem occurs when i add a branch parameter (int) & make the following change
WHERE branch_code = @.branch)
i get the following error message
Application uses a value of the wrong type for the current operation.
thanks in advanceIs branch_code actually defined as an int in your table?
Can you run (SELECT opened_date FROM
HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
itself without an error?
"Tango" wrote:
> hi,
> i am trying to run the following query in the dataset which works fine when
> i have a number in the branch_code
> SELECT tbl_calendar.date, tbl_day_type.day_type
> FROM tbl_calendar INNER JOIN
> tbl_day_type ON tbl_calendar.day_type => tbl_day_type.type_id
> WHERE (tbl_calendar.date BETWEEN
> (SELECT opened_date
> FROM
> HOBISINT.account_statistics.dbo.t_branches
> WHERE branch_code = 56) AND GETDATE())
> Problem occurs when i add a branch parameter (int) & make the following change
> WHERE branch_code = @.branch)
> i get the following error message
> Application uses a value of the wrong type for the current operation.
> thanks in advance|||Yes it is int
yes i can run below without any errors
thanks for your interest
"daw" wrote:
> Is branch_code actually defined as an int in your table?
> Can you run (SELECT opened_date FROM
> HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
> itself without an error?
> "Tango" wrote:
> > hi,
> > i am trying to run the following query in the dataset which works fine when
> > i have a number in the branch_code
> >
> > SELECT tbl_calendar.date, tbl_day_type.day_type
> > FROM tbl_calendar INNER JOIN
> > tbl_day_type ON tbl_calendar.day_type => > tbl_day_type.type_id
> > WHERE (tbl_calendar.date BETWEEN
> > (SELECT opened_date
> > FROM
> > HOBISINT.account_statistics.dbo.t_branches
> > WHERE branch_code = 56) AND GETDATE())
> >
> > Problem occurs when i add a branch parameter (int) & make the following change
> > WHERE branch_code = @.branch)
> > i get the following error message
> > Application uses a value of the wrong type for the current operation.
> >
> > thanks in advance

Dataset query in rdl

Hi,
I have a report that is going to have to show the following.
Columns 1-3 will be counts of a certain activity.
Columns 4-12 will have calculations based on the data contained in the 3rd
count.
I can get the counts, and I can perform the calculations just fine. Where I
run into a problem is on the display. I have a table with some grouping that
will do the calculations and show columns 3-12, with the data available if
the user wishes to drill down. If I have second data set with my counts how
can I get the data from it where my keys are equal? I know that I could put
all the counts in my data set but I donâ't want to have them in each row
because I am expecting to only have around 100 groups, but over 8000 rows of
data will be need to calculate columns 4-12.
I guess what I am really after is a way to query a dataset, or to join them
inside of the rdl.This may help explain what I am trying to accomplish.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AKAIMS Production">
<rd:DataSourceID>af566fca-304d-4f11-bc74-505d4c0175b4</rd:DataSourceID>
<DataSourceReference>AKAIMS Production</DataSourceReference>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>0e5aad5b-e956-4edb-8be1-9dbe4893dc7d</rd:ReportID>
<DataSets>
<DataSet Name="Counts">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountA">
<DataField>CountA</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountB">
<DataField>CountB</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 13 AS CountA, 21 As CountB
UNION
SELECT 2 As agency_id, 14 AS CountA, 24 As CountB</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="Detail">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Data">
<DataField>Data</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 1 AS Data
UNION
SELECT 1 As agency_id, 2 AS Data
UNION
SELECT 2 As agency_id, 3 AS Data
UNION
SELECT 2 As agency_id, 4 AS Data</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>5.5in</Width>
<Body>
<ReportItems>
<Table Name="table1">
<DataSetName>Detail</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1_agency">
<GroupExpressions>
<GroupExpression>=Fields!agency_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!agency_id.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>23</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountA.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>22</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"13","14")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>21</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountB.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<rd:DefaultName>textbox25</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>20</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"21","24")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="agency_id">
<rd:DefaultName>agency_id</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>19</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!agency_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>18</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<rd:DefaultName>textbox22</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox27">
<rd:DefaultName>textbox27</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>7</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>5</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<rd:DefaultName>textbox21</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<rd:DefaultName>textbox26</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Data">
<rd:DefaultName>Data</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Data.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>29</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count A</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>28</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>27</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count B</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>26</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>25</ZIndex>
<CanGrow>true</CanGrow>
<Value>agency id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>24</ZIndex>
<CanGrow>true</CanGrow>
<Value>Data</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.75in</Width>
</TableColumn>
</TableColumns>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>17</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<rd:DefaultName>textbox18</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<rd:DefaultName>textbox23</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>15</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<rd:DefaultName>textbox28</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Double</Top>
</BorderStyle>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>12</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Height>1.25in</Height>
</Body>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
"AKCodeRat" wrote:
> Hi,
> I have a report that is going to have to show the following.
> Columns 1-3 will be counts of a certain activity.
> Columns 4-12 will have calculations based on the data contained in the 3rd
> count.
>
> I can get the counts, and I can perform the calculations just fine. Where I
> run into a problem is on the display. I have a table with some grouping that
> will do the calculations and show columns 3-12, with the data available if
> the user wishes to drill down. If I have second data set with my counts how
> can I get the data from it where my keys are equal? I know that I could put
> all the counts in my data set but I donâ't want to have them in each row
> because I am expecting to only have around 100 groups, but over 8000 rows of
> data will be need to calculate columns 4-12.
> I guess what I am really after is a way to query a dataset, or to join them
> inside of the rdl.
>

DataSet Problems

Hi,
We have noticed a problem with following:
1. creating a new report
2. adding a DataSet which pulls from a shared data source and displays
information correctly
3. choosing 'Save' and not pulling the fields into the layout form
When we open the report again the dataset is there but all references to the
table we selected are gone.
Is this supposed to happen? We have tried this on 2 separate installations,
one with SP2 and one without.
Any suggestions?Sometimes RS does not recognize that anything has changed in the data tab
and does not save. Just go to the layout tab and click on save all and you
will be good to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> Hi,
> We have noticed a problem with following:
> 1. creating a new report
> 2. adding a DataSet which pulls from a shared data source and displays
> information correctly
> 3. choosing 'Save' and not pulling the fields into the layout form
> When we open the report again the dataset is there but all references to
> the
> table we selected are gone.
> Is this supposed to happen? We have tried this on 2 separate
> installations,
> one with SP2 and one without.
> Any suggestions?|||Bruce - Thank you, that worked! Would this be fixed in a future release?
Although we were working on a small dataset I would hate to have lost
development time on a larger query?!
"Bruce L-C [MVP]" wrote:
> Sometimes RS does not recognize that anything has changed in the data tab
> and does not save. Just go to the layout tab and click on save all and you
> will be good to go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> > Hi,
> > We have noticed a problem with following:
> > 1. creating a new report
> > 2. adding a DataSet which pulls from a shared data source and displays
> > information correctly
> > 3. choosing 'Save' and not pulling the fields into the layout form
> > When we open the report again the dataset is there but all references to
> > the
> > table we selected are gone.
> > Is this supposed to happen? We have tried this on 2 separate
> > installations,
> > one with SP2 and one without.
> >
> > Any suggestions?
>
>|||I have no idea. I have lost work so I do this compulsively now. I'll
probably keep doing it when the bug no longer exists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:DC256683-1D12-4C55-B578-8A4440F1B41E@.microsoft.com...
> Bruce - Thank you, that worked! Would this be fixed in a future release?
> Although we were working on a small dataset I would hate to have lost
> development time on a larger query?!
> "Bruce L-C [MVP]" wrote:
>> Sometimes RS does not recognize that anything has changed in the data tab
>> and does not save. Just go to the layout tab and click on save all and
>> you
>> will be good to go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
>> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
>> > Hi,
>> > We have noticed a problem with following:
>> > 1. creating a new report
>> > 2. adding a DataSet which pulls from a shared data source and displays
>> > information correctly
>> > 3. choosing 'Save' and not pulling the fields into the layout form
>> > When we open the report again the dataset is there but all references
>> > to
>> > the
>> > table we selected are gone.
>> > Is this supposed to happen? We have tried this on 2 separate
>> > installations,
>> > one with SP2 and one without.
>> >
>> > Any suggestions?
>>

Dataset CommandType: TableDirect (Error)

In Visual Studio Report Designer data tab, whenever I try to create a new
dataset with the CommandType "TableDirect" I get the following message:
"An error has occurred while setting the Command Type property of the data
extension command. CommandType.TableDirect is not supported by the .Net
SqlClient Data Provider."
when I click OK to the dataset. I wonder why Visual Studio.NET would contain
a CommandType that is not supported. Any ideas what is causing this?
MalikSee
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommandTypeClassTopic.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:uiM%23UDybEHA.1000@.TK2MSFTNGP12.phx.gbl...
> In Visual Studio Report Designer data tab, whenever I try to create a new
> dataset with the CommandType "TableDirect" I get the following message:
> "An error has occurred while setting the Command Type property of the data
> extension command. CommandType.TableDirect is not supported by the .Net
> SqlClient Data Provider."
> when I click OK to the dataset. I wonder why Visual Studio.NET would
contain
> a CommandType that is not supported. Any ideas what is causing this?
> Malik
>sql

Dataset cannot be generated error

When editing the data driven subscription query I SOMETIMES get the following error: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Invalid object name '##XX'. The datasource is ok, authentication also, the query works... Why can't it see the temporary table?

|||How would I make it work with a temporary table? To update the problem: when I validate the query I get the error described above however if I run the same stored procedure that is used in a query in let's say management studio and leave it open, the query in data driven subscription web interface is validated succesfully. I'm running RS2005 on a Win2003Server.
|||I really woul appreciate any suggestions, cause it's basically messing with my report server stability...thanks
|||

Did you ever find a solution? I have the same problem. My data set involves concatenated SQL which causes the SP to not return the columns, but if I don't do it this way, I will be creating a monumental amount of work to have individual tables.

Dataset cannot be generated error

When editing the data driven subscription query I SOMETIMES get the following error: The dataset cannot be generated. An error occurred while connecting to a data

source, or the query is not valid for the data source. (rsCannotPrepareQuery) Invalid object name '##XX'. The datasource is ok, authentication also, the query works... Why can't it see the temporary table?

How would I make it work with a temporary table? To update the problem:

when I validate the query I get the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio and leave it open, the query in data driven

subscription web interface is validated succesfully. I'm running RS2005

on a Win2003Server.|||I really woul appreciate any suggestions, cause it's basically messing with my report server stability...thanks|||

Did you ever find a solution? I have the same problem. My data set involves concatenated SQL which causes the SP to not return the columns, but if I don't do it this way, I will be creating a monumental amount of work to have individual tables.

Dataset cannot be generated error

When editing the data driven subscription query I SOMETIMES get the following error: The dataset cannot be generated. An error occurred while connecting to a data

source, or the query is not valid for the data source. (rsCannotPrepareQuery) Invalid object name '##XX'. The datasource is ok, authentication also, the query works... Why can't it see the temporary table?

How would I make it work with a temporary table? To update the problem:

when I validate the query I get the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio and leave it open, the query in data driven

subscription web interface is validated succesfully. I'm running RS2005

on a Win2003Server.|||I really woul appreciate any suggestions, cause it's basically messing with my report server stability...thanks|||

Did you ever find a solution? I have the same problem. My data set involves concatenated SQL which causes the SP to not return the columns, but if I don't do it this way, I will be creating a monumental amount of work to have individual tables.

dataset and identity of new record inserted

Hi,

I have 2 tables in my databasePrescriptionHeader and PrescriptionDetails.

My PrescriptionHeader table has the following fields:

PrescriptionID -identity field

PatientID

PatientfName

Patientlname

PrescriptionDetails table has the following fields:

PrescriptionDetailID -identity

PrescriptionID -from PrescriptionHeader table

MedicineDosage

The functionInsertPrescription inserts values into the tablePrescriptionHeader. I want the same function to then insert the value ofMedicineDosage intoPrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?

Thanks

Function InsertPrescription(ByVal PatientIDAsString, _

ByVal PatientFnameAsString, _

ByVal PatientlnameAsString, ByValMedicineDosage as String)

Dim DBAdapterAs SqlDataAdapter

Dim DBDataSetAs DataSet

Dim SQLStringAsString

Dim DBCommandBuilderAs SqlCommandBuilder

SQLString ="SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"

DBAdapter =New SqlDataAdapter(SQLString, DBConnection)

DBDataSet =New DataSet

DBAdapter.Fill(DBDataSet)

Dim AddedRowAs DataRow = DBDataSet.Tables(0).NewRow()

AddedRow("PatientID") = PatientID

AddedRow("PatientfName") = PatientFname

AddedRow("Patientlname") = Patientlname

DBDataSet.Tables(0).Rows.Add(AddedRow)

DBCommandBuilder =New SqlCommandBuilder(DBAdapter)

DBAdapter.Update(DBDataSet)

EndFunction

The following article is very helpful in your case

Inserting relational data using DataSet and DataAdapter

HTH
Regards

Datareader, DBNull and ? Operator

Hi!

I was trying to use the ?? operator with an DBDatareader. eg:

long lValue = Convert.ToInt32(objReader["ParentID"] ?? -1);

which throws the following exception:"... Object cannot be cast from DBNull to other types."
I think the reason is, that DBNull.Value isn′t actually null. Therefore I changed my code to:

long lValue = objReader["ParentID"] != DBNull.Value ? Convert.ToInt32(objReader["ParentID"]) : -1;

Is there a better way of doing this ? Can one use the ?? operator with a Datareader anyway?

Thanks for your help!

PS: sorry for my poor english.

Hi,

the ?? operator is used in conjunction with nullable types. The way you wrote it using the ?: operator is just fine.

Grz, Kris.

|||

Thanks a lot!

DataReader Output skipping first line

I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.

Any help would be greatly appreciated.

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

1reader = cmd.ExecuteReader()2If Not reader.Read()Then3 Return False4Else5 For i = 0To reader.FieldCount - 16 strLine += reader.GetName(i).ToString & Chr(9)7Next8 objStreamWriter.WriteLine(strLine)9 strLine =""10With reader11While .Read12For x = 0To .FieldCount - 113 strLine = strLine & reader.GetValue(x) & Chr(9)14Next15 objStreamWriter.WriteLine(strLine)16 strLine =""17End While18 End With19End If

line 2 of you code is wasting the first row of data by performing a reader.Read that you ignore.

change line 2 to:

If Not reader.HasRows()Then

|||

Perfect!

Thank you!!!!

Datareader not referencing connection object

Please see following code :

SqlConnection conn=new SqlConnection(@."something...;");
SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);

conn.Open();
SqlDataReader rd;
conn=null;

try
{
rd = comm.ExecuteReader();
rd.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

This Code works fine. I have set conn=null, still datareader is able to read the data. Why?

Thank you.

You set conn to null, you are setting thereference to null. Since your SqlCommand has it's own reference to the connection, the connection is still around. You should use a Finally block for the .Close() to ensure it is called.|||

Thank you for answering.

Yes it is bad code that I have set it null.

Please look following code:

rd=comm.ExecuteReader(<<behaviour close connection>>);
do someting.. donot close rd
comm.CommandText="another query"
rd=comm.ExecuteReader(<<behaviour close connection>>);
rd.close()

Is there First SQL Connection is still open?
Thank you.

|||

The only provider that will even allow such a thing is SQL Server 2005, otherwise you will get an error when you attempt to execute the second ExecuteReader.

Regardless of what it actually does, I wouldn't recommend assuming anything. It's ambiguous on what it SHOULD do, so don't rely on what it really does, and don't code that.

|||

Hi Motley,
Yes, we do not code like this way. But this is already done by other developers, and we are reviewing it.

Please have a look to following code (Assume try/catch is properly placed) :

privatevoid Form1_Load(object sender, System.EventArgs e)
{
SqlDataReader dr1=GetDataReader("Select 1"); //Line1
SqlDataReader dr2=GetDataReader("Select 2"); //Line2
dr1=GetDataReader("Select 3"); //Line3
dr1.Close(); //Line4
dr2.Close(); //Line5
}

private SqlDataReader GetDataReader(string qry)
{
SqlConnection conn=new SqlConnection(@."Server=localhost;uid=user;pwd=password;database=northwind;;");
SqlCommand comm=new SqlCommand(qry,conn);
conn.Open();
SqlDataReader rd;
rd = comm.ExecuteReader(CommandBehavior.CloseConnection);
comm=null;
conn=null;
return rd;
}

This works withSQL2000 also. Each time new instance of sqlDatareader is created and reference is returned.
At Line3, we are assigning new datareader to dr1. Will it close the dr1 previously opened (for "Select 1") automatically?
If 2 datareaders are not allowed open at the same time, how it is working?
At Line4 we are closing the dr1, but I think there are 2 different connections opened for dr1 and this will lose the last one. Right?

Thank you,

|||In this example, you are using a new connection for each data reader, and so you can get an additional datareader and assign it to dr1. However, in this case, dr1 will be closed (and in this example, the underlying connection) whenever the data reader object is garbage collected, which is a terrible idea.|||What doug said is correct.

Datareader does not return result

the following is the code,...When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...

Connection is open and the query is simple select statement

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks

Niranch

niranch,

can you show me exact syntax of your query string within the context of how you are using it in the c# code? You may have some quotes being escaped incorrectly or something such that when run from the app code, it does not return any results and therefore you are getting your error. so, make sure that your query string is written correctly, particularly how you have used single or double quotes...--jp

|||

hi jp,

thank you for your suggestion...I just figured out the problem...

It is just that, when I run the same set of statements in my code behind file, I get the result, but when I run it in a seperated .CS file(DAL), I get the error mentioned in my post...I am trying to return datareader object to the Business tier...

I dont know why...

Thanks

Niranch

|||ahh so you are having trouble with the data moving through tiers. can you show some code of how your are interacting between the tiers?|||

hi this is my code...the same set to commands ,connections works fine in codebehind file

codebehid file

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database)

class where i have database access

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader);

|||

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader); <-- at this point is your reader null? if you put a throw statement here like if(myReader == null) throw new Exception("Null Reader") will you get an exception? is the reader already null here, or are you getting results here and just not when you set

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database) you get the null reader here? that make sense? at what point are you not getting any results? -- jp

|||

I get the problem, Exactly after executing the command

DAL Page

myreader = mycommand.executereader(), i get a null reader. "Invalid read attempt when there is no data" is the error...only in this page, i have my connection open...

i dont know if it is ok to send a datareader object as return object to another page............

|||

thankyou, i got the problem solved,,,

I transfered the contents from reader to datatable and passed as return objec to presentation tier...It worked fine.............