Dataset Query IN Clause issue

Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
want to use a variable in my IN clause that that holds the different
values (strings) but I can't seem to get it to work. I've posted the
SQL statement as it currently is and what I tried to do to get it to
work.
Thanks for any help in advance.
Mike
Current Statemenet:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotos
What I tried:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotosMike,
The string variable is treated as a single data element, not a list or array
of elements. So, if:
SET @.PhotoSet = '1,23,56,78'
Your query would only return a row with the value '1,23,56,78' in
vewStatsInnerSummary.Batch.
You can slice up the string into a temporary table and then use that table
in your query, or use dynamic SQL, etc.
Vyas has a write up on your issue.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
RLF
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>|||The type of (Reporting Services) parameter you are using is for a
multivalued parameter. It would show up as a drop down with several values,
each value having a checkbox next to it. Then, whatever values are checked
will be in your "IN" clause. The top checkbox is used for "Select All".
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>

dataset object dispose after first use

I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?

Thanks!

Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.

.

.// read value from row

.HAHAHAHA!! Not to laugh at your problem, but search a bit and you'll find a VERY recent discussion on this very topic.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1406350&SiteID=1|||

Use a MULTICAST if you need to work with same rows again.

There is a code work-around (shown in the forum post Phil referenced), but it's ugly.

|||I don't understand how multicast would work if we have multiple dataflow.|||

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

|||

jwelch wrote:

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

Exactly. Or simply use a multicast to populate 2 variables in the first place with exactly the same data.

-Jamie

dataset into a table or matrix with fixed number of columns, variable rows

Hi

I have a dataset with 2 columns, a rownumber and a servername - eg

rownumber servername

1 server1

2 server2

....

15 server15

I want to display the servernames in a report so that you get 3 columns - eg

server1 | server2 | server3

server4 | server5 | server6

...

server13 | server14 | server15

I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap

I have also tried using a matrix control but cant find a way to do this.

Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005

Thanks

Anthony

dont you hate it when you figure out the solution just after you have asked the question.

Here is how i managed to do it if anybody else is interested.

First put the data in a matrix control so you have all the servernames on one row.

Then add a list control and put the matrix in the list control.

Finally set the grouping expression on the list control to be

=Ceiling(RowNumber(Nothing)/3)

Anthony

DataSet - Should be easy but I dont know how

I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.

This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how? Any help would be awesome.

Sub Button1_Click(sender As Object, e As EventArgs)
?? = MyQueryMethod(txtPhone.Text)

Thanks,
Matt

Dim oDataSet as System.Data.DataSet = MyQueryMethod(txtPhone.Text)
If oDataSet = Nothing Then
 ' your dataset is jacked up
Else
 ' You got a dataset to work with
End If
' Release memory allocations to the variable.
oDataSet.Dispose()

|||Simply amazing! It's so easy once someone tells you how it is. Worked like a charm...Now I'm all jacked up!!!

Much Respect!!!!

Thanks,
Matt|||Well actually, it didn't quite do as I expected but I'm close. When I execute this code, I am expecting the dataset to have no records or one record, but either way the DataSet is never nothing (at least from my testing).

Maybe I'm just going about my problem the wrong way. I am trying to validate phone number so that I don't have someone enter a duplicate key in my database. So I run an SQL select statement that returns a dataset. The dataset should have one phone number or no phone number and this is how I'm trying to ward off any duplicate keys.

Is there an easier way to prevent a duplicate key entry? Sorry, I'm new at this stuff!!|||You can code as

If oDataSet.Tables(0).Rows.Count = 0 Then
 ' your have no records
Else
 'Response.Write(oDataSet.Tables(0).Rows(0)(0).ToString())
End If

HTH|||Perfect...thanks.

Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian

DataFlow Task & Filters

Hi,

I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:

Filtered_type_List = 2,4,8,10,11

If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?

Thanks

You could do this in an expression. Something like:

"SELECT * FROM MyTable " + (LEN(MySSISVariable) != 0 ? "WHERE MyColumn IN (" + MySSISVariable + ")" : "" )

That expression will (I think) add a WHERE clause if the length of the string inside the variable (which I have called MySSISVariable) is not zero.

HTH

-Jamie

|||

Hi Jamie,

Where should I put this "Select" statement,

1. Source using SQL Command as variable using OLE DB Source or

2. Lookup transformation

Thanks

|||

OLE DB Source. Set it to 'SQL Command from variable' and paste the expression that I provided above into the variable expression. The variable will require EvaluateAsExpression=TRUE.

-Jamie

Databasename Variable

declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'SRVDB1@.mydomain.com',
@.TO = N'gmatteson@.mydomain',
@.priority = N'HIGH',
@.subject = N'Process Success',
@.message = N'Database Backup Succeeded.', - Rather than putting Database
Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
@.server = N'mail.mydomain.com',
@.attachment = N'F:\TEST.txt'
select RC = @.rc
GO
Anyone know how to do this, this is my first stored procedure and I really
have no idea how the syntax works! thanks.
You can't concatenate there, you'll have to build the message first.
DECLARE @.msg VARCHAR(255), @.rc INT
SET @.msg = @.databasename + ' Backup Succeeded.'
EXEC @.rc = master..xp_smtp_sendmail
...
@.message = @.msg,
What does this have to do with DTS, setup, security? Followups set to
..server only.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:##3QnDqGFHA.544@.TK2MSFTNGP12.phx.gbl...
> declare @.rc int
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'SRVDB1@.mydomain.com',
> @.TO = N'gmatteson@.mydomain',
> @.priority = N'HIGH',
> @.subject = N'Process Success',
> @.message = N'Database Backup Succeeded.', - Rather than putting Database
> Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
> @.server = N'mail.mydomain.com',
> @.attachment = N'F:\TEST.txt'
> select RC = @.rc
> GO
> Anyone know how to do this, this is my first stored procedure and I really
> have no idea how the syntax works! thanks.
>

Databasename Variable

declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'SRVDB1@.mydomain.com',
@.TO = N'gmatteson@.mydomain',
@.priority = N'HIGH',
@.subject = N'Process Success',
@.message = N'Database Backup Succeeded.', - Rather than putting Database
Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
@.server = N'mail.mydomain.com',
@.attachment = N'F:\TEST.txt'
select RC = @.rc
GO
Anyone know how to do this, this is my first stored procedure and I really
have no idea how the syntax works! thanks.
You can't concatenate there, you'll have to build the message first.
DECLARE @.msg VARCHAR(255), @.rc INT
SET @.msg = @.databasename + ' Backup Succeeded.'
EXEC @.rc = master..xp_smtp_sendmail
...
@.message = @.msg,
What does this have to do with DTS, setup, security? Followups set to
..server only.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:##3QnDqGFHA.544@.TK2MSFTNGP12.phx.gbl...
> declare @.rc int
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'SRVDB1@.mydomain.com',
> @.TO = N'gmatteson@.mydomain',
> @.priority = N'HIGH',
> @.subject = N'Process Success',
> @.message = N'Database Backup Succeeded.', - Rather than putting Database
> Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
> @.server = N'mail.mydomain.com',
> @.attachment = N'F:\TEST.txt'
> select RC = @.rc
> GO
> Anyone know how to do this, this is my first stored procedure and I really
> have no idea how the syntax works! thanks.
>

Databasename Variable

declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'SRVDB1@.mydomain.com',
@.TO = N'gmatteson@.mydomain',
@.priority = N'HIGH',
@.subject = N'Process Success',
@.message = N'Database Backup Succeeded.', - Rather than putting Database
Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
@.server = N'mail.mydomain.com',
@.attachment = N'F:\TEST.txt'
select RC = @.rc
GO
Anyone know how to do this, this is my first stored procedure and I really
have no idea how the syntax works! thanks.You can't concatenate there, you'll have to build the message first.
DECLARE @.msg VARCHAR(255), @.rc INT
SET @.msg = @.databasename + ' Backup Succeeded.'
EXEC @.rc = master..xp_smtp_sendmail
...
@.message = @.msg,
What does this have to do with DTS, setup, security? Followups set to
.server only.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:##3QnDqGFHA.544@.TK2MSFTNGP12.phx.gbl...
> declare @.rc int
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'SRVDB1@.mydomain.com',
> @.TO = N'gmatteson@.mydomain',
> @.priority = N'HIGH',
> @.subject = N'Process Success',
> @.message = N'Database Backup Succeeded.', - Rather than putting Database
> Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
> @.server = N'mail.mydomain.com',
> @.attachment = N'F:\TEST.txt'
> select RC = @.rc
> GO
> Anyone know how to do this, this is my first stored procedure and I really
> have no idea how the syntax works! thanks.
>

Databasename Variable

declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'SRVDB1@.mydomain.com',
@.TO = N'gmatteson@.mydomain',
@.priority = N'HIGH',
@.subject = N'Process Success',
@.message = N'Database Backup Succeeded.', - Rather than putting Database
Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
@.server = N'mail.mydomain.com',
@.attachment = N'F:\TEST.txt'
select RC = @.rc
GO
Anyone know how to do this, this is my first stored procedure and I really
have no idea how the syntax works! thanks.You can't concatenate there, you'll have to build the message first.
DECLARE @.msg VARCHAR(255), @.rc INT
SET @.msg = @.databasename + ' Backup Succeeded.'
EXEC @.rc = master..xp_smtp_sendmail
..
@.message = @.msg,
What does this have to do with DTS, setup, security? Followups set to
.server only.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:##3QnDqGFHA.544@.TK2MSFTNGP12.phx.gbl...
> declare @.rc int
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'SRVDB1@.mydomain.com',
> @.TO = N'gmatteson@.mydomain',
> @.priority = N'HIGH',
> @.subject = N'Process Success',
> @.message = N'Database Backup Succeeded.', - Rather than putting Database
> Backup Succeeded, I would like to put "@.databasename Backup Succeeded".
> @.server = N'mail.mydomain.com',
> @.attachment = N'F:\TEST.txt'
> select RC = @.rc
> GO
> Anyone know how to do this, this is my first stored procedure and I really
> have no idea how the syntax works! thanks.
>

Database Variable

Hi,

I have a sql script that references a database explicitly, but the name of that database changes. Rather then have to find anywhere I reference that database and change it, i would like to just set it in one spot.

For example.

SELECT * FROM [V1_Database].[a_table]

I then decide to upgrade from V1 to V2.

Instead of having to find the above code in all of my stored procs and manually change it, I would like to do the following.

SET dbName = 'V2_Database' (Database wide variable)

SELECT * FROM [dbName].[a_table]

But I don't want to have to make all of the places that reference a specific database into string executions. ( strSQL = 'SELECT * FROM [' + dbName + ']' )

I hope i havent made this too confusing, but any help is appreciated.

Thanks,

Brian

You could put the connectionstring in web.config file and use it across your application. That way you would only need to change at one place.