Multiple values contained in query string to be used as parameter in Report. HELP!

Hi,

I'm a bit desperate here so any solutions would be much appreciated. I'm using SQL reporting services 2005 & .NET2.0 & Oracle DB.

I need to pass multiple values in the query string, or any other suggested way, to my report to be used as a parameter. The idea is a user on the web app will do a search & select an item to open a new form.

The user can print the report from here, by pressing a button and being redirected to the report's URL. In this form there will be many (could be alot) of IDs that I will need to pass to the report and the report will use these as a parameter. The problem is that I am passing it at the moment as a comma deliminated string so instead of the SQL doing this : "ID in ('1', '2')", it's doing this: "ID in ('1, 2').

What are my alternatives? Thanks!

RS 2005 supports multi-value report parameters (e.g. http://msdn2.microsoft.com/en-us/library/aa337292.aspx).

If you use the "Oracle" data extension and write your Oracle query with a query parameter like:
select * from emp where ename IN (:EmployeeName)

RS will generate a report parameter (which you need to mark as multi-value report parameter) linked to the query parameter and perform an automatic multi-value query rewrite so that the Oracle query will be executed with correctly generated IN clause contents.

-- Robert

|||Cheers Robert.

Multiple time stamp values in MSDE

Hi - I am a bit of an amatuer, but I am trying to use the EMS data Pump to get all of the data out of an Interbase 6 DB into an MSDE DB. I have managed to instal an Interbase ODBC driver, and I ca connect to it and then automatically create the tables in my MSDE DB, but I get an error stating that there cannot be multiple Timestamp columns in the MSDE databse. Is this true ?Are you sure you need TIMESTAMP fields? Could it be that you only need multiple DATETIME fields? Besides, the reason you can't have more than 1 true TIMESTAMP field per table is because ... it wouldn't make sense to have more than 1.|||I will look into that. I am just converting an old interbase databse to MSDE so that I can write another app to use its data.

Why whould it mkae no sense to have more than one timestamp field ?

I will try to convert the column to a datetime field. Thanks|||TIMESTAMP is often confused for GETDATE(). The value of TIMESTAMP field is used by the engine as a row version identifier, rather then as a DATE and TIME. It's actually an auto-generated binary number that stays unique within a database, and as per definition has nothing to do with neither date nor time.

Multiple TempDB files for Multi_CPU

Hi all,
I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
have as many files for tempDB as I have CPU's - ie, eight.
I have a few questions about this:
1. I am assuming that these files are created in SSMS by right-clicking
tempdb and going to the Properties\Files tab.
--Should I add one tempdev_n and one templog_n for each CPU, or just the
tempdev file (the mdf)?
--Does it make sense to leave all of the new tempdev_n files in the
PRIMARY filegroup (same as Partition?), or should I create a new filegroup
for each tempdev_n instance?
2. Will performance actually have the potential to improve if all of these
tempdev_n files are on the same RAID 1 disc array? Does anyone actually
spread these files to dedicated discs per instance?
Thanks. These questions probably expose my experience level (rank amateur),
but that's why I am asking them. I appreciate any help.
JT
--
JohnHi
Increasing the number of data files will reduce the contention in SGAM and
PFS pages in tempdb. This is described in
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Also check out
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
This is more likely to occur if your application uses an excessive number of
temporary table or other features that require tempdb.
You do not have to increase the number of log files. You can use management
studio or use the ALTER DATABASE command to increase the number of files.
Also make sure that the initial size of the tempdb files are large enough for
normal use so you avoid it having to grow.
John
"JT" wrote:
> Hi all,
> I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
> have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
> array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
> have as many files for tempDB as I have CPU's - ie, eight.
> I have a few questions about this:
> 1. I am assuming that these files are created in SSMS by right-clicking
> tempdb and going to the Properties\Files tab.
> --Should I add one tempdev_n and one templog_n for each CPU, or just the
> tempdev file (the mdf)?
> --Does it make sense to leave all of the new tempdev_n files in the
> PRIMARY filegroup (same as Partition?), or should I create a new filegroup
> for each tempdev_n instance?
> 2. Will performance actually have the potential to improve if all of these
> tempdev_n files are on the same RAID 1 disc array? Does anyone actually
> spread these files to dedicated discs per instance?
> Thanks. These questions probably expose my experience level (rank amateur),
> but that's why I am asking them. I appreciate any help.
> JT
> --
> John|||Hello John,
I agree with john that it is necessary if you do have load that requres big
tempdb. Also, setting the database size to a large value is suggested.
For SQL Server 2005 it is recommended that you create tempdb files striped
across fast disks. Since you have raid1 disk, the performance improvement
may not as much as you expect though it should improve performance when the
problem is caused by tempdb. Generally, you may want to monitor the system
by using performon as described in the articles recommended by John and add
files when necessary. Also, you may want to configure trace flag 1118 to
eliminate mixed extent allocations. Please see the following article for
more details:
328551 FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
If you have further questions or concerns on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Peter and John. One follow-on question...
Given current resource restrictions, I have two FC disks to devote to the
tempdb for this SQL instance on the SAN. For reliability sake, I choose to
go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
array). Would it make sense to go with RAID 0? What would happen if one of
the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
Thanks.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I agree with john that it is necessary if you do have load that requres big
> tempdb. Also, setting the database size to a large value is suggested.
> For SQL Server 2005 it is recommended that you create tempdb files striped
> across fast disks. Since you have raid1 disk, the performance improvement
> may not as much as you expect though it should improve performance when the
> problem is caused by tempdb. Generally, you may want to monitor the system
> by using performon as described in the articles recommended by John and add
> files when necessary. Also, you may want to configure trace flag 1118 to
> eliminate mixed extent allocations. Please see the following article for
> more details:
> 328551 FIX: Concurrency enhancements for the tempdb database
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> If you have further questions or concerns on the issue, please feel free to
> let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi
With Raid 0 you would have some down, while you either added a new disc or
reconfigured tempdb (I think SQL Server would not restart until you did!) .
The database should roll back any usage of tempdb when the system stopped so
your database should remain consistent but you would need to reapply the
transactions that were in progress.
John
"JT" wrote:
> Thank you Peter and John. One follow-on question...
> Given current resource restrictions, I have two FC disks to devote to the
> tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> array). Would it make sense to go with RAID 0? What would happen if one of
> the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> Thanks.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I agree with john that it is necessary if you do have load that requres big
> > tempdb. Also, setting the database size to a large value is suggested.
> >
> > For SQL Server 2005 it is recommended that you create tempdb files striped
> > across fast disks. Since you have raid1 disk, the performance improvement
> > may not as much as you expect though it should improve performance when the
> > problem is caused by tempdb. Generally, you may want to monitor the system
> > by using performon as described in the articles recommended by John and add
> > files when necessary. Also, you may want to configure trace flag 1118 to
> > eliminate mixed extent allocations. Please see the following article for
> > more details:
> >
> > 328551 FIX: Concurrency enhancements for the tempdb database
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> >
> > If you have further questions or concerns on the issue, please feel free to
> > let's know. Thank you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Community Support
> > ==================================================> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications
> > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hello John,
As John mentioned, raid 0 disk failure of tempdb shall not bring data
consistency issue. However, transactions related to tempdb will roll back
and you shall reapply these transactions.
Usually it is suggested that you use RAID 1 or RAID 1+0, not RAID 0 for
tempdb, unless you are prepared to lose SQL Server access should either
drive fail in a RAID 0 striped array.
Please see:
http://www.microsoft.com/sql/prodinfo/previousversions/scalabilityfaq.mspx
and the section on tempdb
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
917047 Microsoft SQL Server I/O subsystem requirements for the tempdb
database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917047
If you have further questions, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"any usage of tempdb" does not read well! Try substituting "any uncommitted
transactions in progress"
John
"John Bell" wrote:
> Hi
> With Raid 0 you would have some down, while you either added a new disc or
> reconfigured tempdb (I think SQL Server would not restart until you did!) .
> The database should roll back any usage of tempdb when the system stopped so
> your database should remain consistent but you would need to reapply the
> transactions that were in progress.
> John
> "JT" wrote:
> > Thank you Peter and John. One follow-on question...
> > Given current resource restrictions, I have two FC disks to devote to the
> > tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> > go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> > array). Would it make sense to go with RAID 0? What would happen if one of
> > the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> >
> > Thanks.
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> > > Hello John,
> > >
> > > I agree with john that it is necessary if you do have load that requres big
> > > tempdb. Also, setting the database size to a large value is suggested.
> > >
> > > For SQL Server 2005 it is recommended that you create tempdb files striped
> > > across fast disks. Since you have raid1 disk, the performance improvement
> > > may not as much as you expect though it should improve performance when the
> > > problem is caused by tempdb. Generally, you may want to monitor the system
> > > by using performon as described in the articles recommended by John and add
> > > files when necessary. Also, you may want to configure trace flag 1118 to
> > > eliminate mixed extent allocations. Please see the following article for
> > > more details:
> > >
> > > 328551 FIX: Concurrency enhancements for the tempdb database
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> > >
> > > If you have further questions or concerns on the issue, please feel free to
> > > let's know. Thank you.
> > >
> > > Best Regards,
> > >
> > > Peter Yang
> > > MCSE2000/2003, MCSA, MCDBA
> > > Microsoft Online Community Support
> > > ==================================================> > > Get notification to my posts through email? Please refer to
> > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > > ications
> > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > > where an initial response from the community or a Microsoft Support
> > > Engineer within 1 business day is acceptable. Please note that each follow
> > > up response may take approximately 2 business days as the support
> > > professional working with you may need further investigation to reach the
> > > most efficient resolution. The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are best
> > > handled working with a dedicated Microsoft Support Engineer by contacting
> > > Microsoft Customer Support Services (CSS) at
> > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > > ==================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >|||Hello John,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Multiple results from single column

Ok this subject sounds a bit vague, but here's my question:

I have a table containing a user id column, a column with events (like log in) and a date column (mm/dd/yy hh/mm/ss).

What i would like to do is to count the number of users who have logged into my system, sorted by month.

Example: January - 20 users
Februari - 42 users
March - 13 users etc.

Can this be done with just one SQL statement? If so, can anyone give me an example query?

Thanks!

SanderHello,

what do you think about

SELECT TO_CHAR(date_field, 'MONTH'), count(*) FROM table_name
GROUP BY TO_CHAR(date_field, 'MONTH')


if you are using an Oracle database.

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hi Manfred Peter,

I'm sorry to say i don't have Oracle, i run MSSQL7. So the statement you provided won't work (i know for sure the to_char function won't work) :rolleyes:

What i have so far is:

select count(users) from table where ops='login' and date between dateA and dateB

This will return me one number from the given timeframe. I want to create a statement that will give me the number of logins over the time period of a year for each month, so 12 numbers. I can't just copy/paste the same statement 11 times...can I :confused:

Thanks!

Sander.|||Hello Sander,

of course you can, but this means 12 times parsing the sqlstatement and reqeusting the datas from the database.
There must be an equivalent to the TO_CHAR function in MSQL Server.

You just need the function that gives only the complete month of a date value (lets say this function is called MONTH(x));

Then the statement

SELECT MONTH(date_field), COUNT(*) from table
GROUP BY MONTH(date_field)

This is the better way ... in my opinion
Search the doku for such a command :)

Let me know if you have further problems.

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com