Named Query and Global Variables

Hello,
I want to filter records right in the Data Source View.
I did a "Replace Table with new Named Query" but how can I get it to filter
records by User!UserID?
Thanks,
-Will"I want to filter records right in the Data Source View."
What do you mean by this?
you want to filter your DATA? or do you want to set different
permissions for different users?|||I want to filter my data.
So "select * from table where field1=user!userid"
"sorcerdon@.gmail.com" wrote:
> "I want to filter records right in the Data Source View."
> What do you mean by this?
> you want to filter your DATA? or do you want to set different
> permissions for different users?
>|||Query parameters do not have to map to report parameters. RS automatically
creates a report parameter for every query parameter but you can delete them
and use an expression.
So, do this:
select * from mytable where field1 = @.User
RS will create automatically a report parameter called User. Instead of
using this mapping, you want to change it.
Click on the ... in the dataset designer, go to the parameters tab. On the
right side change from using a report parameter to using an expression. This
will bring you to the expression builder where you can set it to =User!UserID
Now, in the layout tab, report parameters menu, delete the report parameter
which was created for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>I want to filter my data.
> So "select * from table where field1=user!userid"
>
> "sorcerdon@.gmail.com" wrote:
>> "I want to filter records right in the Data Source View."
>> What do you mean by this?
>> you want to filter your DATA? or do you want to set different
>> permissions for different users?
>>|||I'm trying to use report models though.
So f I follow the example from msdn here:
http://msdn2.microsoft.com/en-us/library/ms156284.aspx
So after I create "Data Source View" I click on a Product table and click
replace with a named query, this let's me edit the sql or use the query
builder to filter records. In the "Filer" column for a field called user_id I
put =User!UserID, hoping that only products that user_id = User!UserId will
be returned. It does not like that filter, in fact I see nothing about global
variables or variables at all.
**The above is the way I'd like to do my filtering, but if not then:
I tried filtering in the "Report Model":
Looked at the fields, click "Add Group" and All must contain
dragged the user_id field into that "All must contain" box and set it to
user_id must contain GetUserID()
Not sure about the exact area's I clicked because I'm not by my development
pc. But I could not get the Report Model to Filter either.
I like the DataSource View filter because it's a universal way for anyone
who creates reports, in reportbuilder or dev. studio, to only have access to
records that have their userid.
-Will
.
"Bruce L-C [MVP]" wrote:
> Query parameters do not have to map to report parameters. RS automatically
> creates a report parameter for every query parameter but you can delete them
> and use an expression.
> So, do this:
> select * from mytable where field1 = @.User
> RS will create automatically a report parameter called User. Instead of
> using this mapping, you want to change it.
> Click on the ... in the dataset designer, go to the parameters tab. On the
> right side change from using a report parameter to using an expression. This
> will bring you to the expression builder where you can set it to => User!UserID
> Now, in the layout tab, report parameters menu, delete the report parameter
> which was created for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "WillG" <WillG@.discussions.microsoft.com> wrote in message
> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
> >I want to filter my data.
> > So "select * from table where field1=user!userid"
> >
> >
> > "sorcerdon@.gmail.com" wrote:
> >
> >> "I want to filter records right in the Data Source View."
> >>
> >> What do you mean by this?
> >>
> >> you want to filter your DATA? or do you want to set different
> >> permissions for different users?
> >>
> >>
>
>|||Sorry, I don't have any experience with report models. Can't help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:E1041A66-B463-4011-9EF6-0C0D11BF2E12@.microsoft.com...
> I'm trying to use report models though.
> So f I follow the example from msdn here:
> http://msdn2.microsoft.com/en-us/library/ms156284.aspx
> So after I create "Data Source View" I click on a Product table and click
> replace with a named query, this let's me edit the sql or use the query
> builder to filter records. In the "Filer" column for a field called
> user_id I
> put =User!UserID, hoping that only products that user_id = User!UserId
> will
> be returned. It does not like that filter, in fact I see nothing about
> global
> variables or variables at all.
> **The above is the way I'd like to do my filtering, but if not then:
> I tried filtering in the "Report Model":
> Looked at the fields, click "Add Group" and All must contain
> dragged the user_id field into that "All must contain" box and set it to
> user_id must contain GetUserID()
> Not sure about the exact area's I clicked because I'm not by my
> development
> pc. But I could not get the Report Model to Filter either.
> I like the DataSource View filter because it's a universal way for anyone
> who creates reports, in reportbuilder or dev. studio, to only have access
> to
> records that have their userid.
> -Will
>
> .
> "Bruce L-C [MVP]" wrote:
>> Query parameters do not have to map to report parameters. RS
>> automatically
>> creates a report parameter for every query parameter but you can delete
>> them
>> and use an expression.
>> So, do this:
>> select * from mytable where field1 = @.User
>> RS will create automatically a report parameter called User. Instead of
>> using this mapping, you want to change it.
>> Click on the ... in the dataset designer, go to the parameters tab. On
>> the
>> right side change from using a report parameter to using an expression.
>> This
>> will bring you to the expression builder where you can set it to =>> User!UserID
>> Now, in the layout tab, report parameters menu, delete the report
>> parameter
>> which was created for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "WillG" <WillG@.discussions.microsoft.com> wrote in message
>> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>> >I want to filter my data.
>> > So "select * from table where field1=user!userid"
>> >
>> >
>> > "sorcerdon@.gmail.com" wrote:
>> >
>> >> "I want to filter records right in the Data Source View."
>> >>
>> >> What do you mean by this?
>> >>
>> >> you want to filter your DATA? or do you want to set different
>> >> permissions for different users?
>> >>
>> >>
>>sql

Mysterious query problem?

Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, it updates
every single record in the lta table, what's going on here? any
thoughts?

1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe

2.)
update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!

update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe|||On 10 Jan 2005 07:25:14 -0800, Roy wrote:

>Hey all,
>strange problem here... query #1 displays 357 records correctly and all
>is well. However, when placed within query #2 as a subquery, it updates
>every single record in the lta table, what's going on here? any
>thoughts?
>
>1.) select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe
>
>2.)
>update lta
>set lta.LL_RCVD = 'N'
>where exists (select *
>from LTA INNER JOIN new_list
>ON lta.voy = new_list.voy AND
>lta.poe = new_list.poe)

Hi Roy,

The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.

You probably need

UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 10 Jan 2005 07:42:14 -0800, Roy wrote:

>Gah! What am I doing wrong? The query below is a modification of query
>#2 above, yet updates 32 records (all of which are NOT located in table
>new_list)??!
>Please help!
>update lta
>set LL_RCVD = 'j'
>from lta a, new_list b
>where a.voy = b.voy AND
>a.poe = b.poe

Hi Roy,

This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').

An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I love you forever, thanks Hugo!

mysql immediate assistance needed please.

Objective:
The primary table I loaded into MySql has 2.5 MM records: ID, Ticker,
Date, Price; and all works well. My need is to write a QUERY to export
outfile?) multiple text files. For example, I have 6 years worth of
data, and need to generate 1 file per day that contains all the Tickers
and Prices for that day. Furthermore, I need the text file name to be
the name of the date (e.g. April 4, 1998 with 1000 Tickers & Prices
would result in a file that was named "040498.txt" (either csv or tab
delimited).

Is this possible? If so, can someone please help me in this effort...the
alternative is not pretty.

Thanks in advance!
> Trevor

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!tread (trevor_read@.hotmail.com) writes:
> The primary table I loaded into MySql has 2.5 MM records: ID, Ticker,
> Date, Price; and all works well. My need is to write a QUERY to export
> outfile?) multiple text files. For example, I have 6 years worth of
> data, and need to generate 1 file per day that contains all the Tickers
> and Prices for that day. Furthermore, I need the text file name to be
> the name of the date (e.g. April 4, 1998 with 1000 Tickers & Prices
> would result in a file that was named "040498.txt" (either csv or tab
> delimited).
> Is this possible? If so, can someone please help me in this effort...the
> alternative is not pretty.

The easiest way to do this for MS SQL Server, would be to use the
BCP command-line utility with the queryout function, packaged in
some script that handles the file naming.

But since you are using MySQL and need immediate assistance, you should
probably look around and try to find a MySQL forum to get some more
relevant response.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

My view is timing out

What could be the reason for my view to timeout? I thought it was because of the number or records, but i guessed wrong. The view is grabbing data from a UDF i have created.You still doing that?

See, now there's no reason to use the udf...

My SQL7.0 Problems

I have a SQL 7.0 DB that is working fine, except for some records that a
user deleted. She wants me to restore them. My normal process for
restoring SQL data is to restore last night's backup to a test DB and let
the user access this DB for their information. My problem is that the test
DB is corrupt. When I try to access the DB through Enterprise Manager, I
get this: An error occurred while trying to access the database information.
When I try to get properties on the database, I get this: Error 945:
Database 'database' cannot be opened because some of the files could not be
activated.
I was able to try to restore (and import) data to the DB, but got various
error messages.
My questions:
1. Can this DB be repaired?
2. If this DB can't be repaired, can restore or import to it?
I made a new blank DB and tried to restore (and import) to it, but got
errors when I tried that too.
Any ideas?
Thanks,
JohnHi
Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE" is
the command.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
news:11411o8s8vrbibf@.corp.supernews.com...
> I have a SQL 7.0 DB that is working fine, except for some records that a
> user deleted. She wants me to restore them. My normal process for
> restoring SQL data is to restore last night's backup to a test DB and let
> the user access this DB for their information. My problem is that the
test
> DB is corrupt. When I try to access the DB through Enterprise Manager, I
> get this: An error occurred while trying to access the database
information.
> When I try to get properties on the database, I get this: Error 945:
> Database 'database' cannot be opened because some of the files could not
be
> activated.
> I was able to try to restore (and import) data to the DB, but got various
> error messages.
> My questions:
> 1. Can this DB be repaired?
> 2. If this DB can't be repaired, can restore or import to it?
> I made a new blank DB and tried to restore (and import) to it, but got
> errors when I tried that too.
> Any ideas?
> Thanks,
> John
>|||Thanks for the response!
How do I use the 'DROP DATABASE' command? After I delete and restore the
database, do I go into 'Query Analyzer' and issue the command there? What
does it do?
Thanks again,
John
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u7J0bPzLFHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi
> Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE"
is
> the command.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
> news:11411o8s8vrbibf@.corp.supernews.com...
> > I have a SQL 7.0 DB that is working fine, except for some records that a
> > user deleted. She wants me to restore them. My normal process for
> > restoring SQL data is to restore last night's backup to a test DB and
let
> > the user access this DB for their information. My problem is that the
> test
> > DB is corrupt. When I try to access the DB through Enterprise Manager,
I
> > get this: An error occurred while trying to access the database
> information.
> > When I try to get properties on the database, I get this: Error 945:
> > Database 'database' cannot be opened because some of the files could not
> be
> > activated.
> >
> > I was able to try to restore (and import) data to the DB, but got
various
> > error messages.
> >
> > My questions:
> >
> > 1. Can this DB be repaired?
> >
> > 2. If this DB can't be repaired, can restore or import to it?
> >
> > I made a new blank DB and tried to restore (and import) to it, but got
> > errors when I tried that too.
> >
> > Any ideas?
> >
> > Thanks,
> >
> > John
> >
> >
>

My SQL7.0 Problems

I have a SQL 7.0 DB that is working fine, except for some records that a
user deleted. She wants me to restore them. My normal process for
restoring SQL data is to restore last night's backup to a test DB and let
the user access this DB for their information. My problem is that the test
DB is corrupt. When I try to access the DB through Enterprise Manager, I
get this: An error occurred while trying to access the database information.
When I try to get properties on the database, I get this: Error 945:
Database 'database' cannot be opened because some of the files could not be
activated.
I was able to try to restore (and import) data to the DB, but got various
error messages.
My questions:
1. Can this DB be repaired?
2. If this DB can't be repaired, can restore or import to it?
I made a new blank DB and tried to restore (and import) to it, but got
errors when I tried that too.
Any ideas?
Thanks,
John
Hi
Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE" is
the command.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
news:11411o8s8vrbibf@.corp.supernews.com...
> I have a SQL 7.0 DB that is working fine, except for some records that a
> user deleted. She wants me to restore them. My normal process for
> restoring SQL data is to restore last night's backup to a test DB and let
> the user access this DB for their information. My problem is that the
test
> DB is corrupt. When I try to access the DB through Enterprise Manager, I
> get this: An error occurred while trying to access the database
information.
> When I try to get properties on the database, I get this: Error 945:
> Database 'database' cannot be opened because some of the files could not
be
> activated.
> I was able to try to restore (and import) data to the DB, but got various
> error messages.
> My questions:
> 1. Can this DB be repaired?
> 2. If this DB can't be repaired, can restore or import to it?
> I made a new blank DB and tried to restore (and import) to it, but got
> errors when I tried that too.
> Any ideas?
> Thanks,
> John
>
|||Thanks for the response!
How do I use the 'DROP DATABASE' command? After I delete and restore the
database, do I go into 'Query Analyzer' and issue the command there? What
does it do?
Thanks again,
John
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u7J0bPzLFHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi
> Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE"
is[vbcol=seagreen]
> the command.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
> news:11411o8s8vrbibf@.corp.supernews.com...
let[vbcol=seagreen]
> test
I[vbcol=seagreen]
> information.
> be
various
>

My SQL7.0 Problems

I have a SQL 7.0 DB that is working fine, except for some records that a
user deleted. She wants me to restore them. My normal process for
restoring SQL data is to restore last night's backup to a test DB and let
the user access this DB for their information. My problem is that the test
DB is corrupt. When I try to access the DB through Enterprise Manager, I
get this: An error occurred while trying to access the database information.
When I try to get properties on the database, I get this: Error 945:
Database 'database' cannot be opened because some of the files could not be
activated.
I was able to try to restore (and import) data to the DB, but got various
error messages.
My questions:
1. Can this DB be repaired?
2. If this DB can't be repaired, can restore or import to it?
I made a new blank DB and tried to restore (and import) to it, but got
errors when I tried that too.
Any ideas?
Thanks,
JohnHi
Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE" is
the command.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
news:11411o8s8vrbibf@.corp.supernews.com...
> I have a SQL 7.0 DB that is working fine, except for some records that a
> user deleted. She wants me to restore them. My normal process for
> restoring SQL data is to restore last night's backup to a test DB and let
> the user access this DB for their information. My problem is that the
test
> DB is corrupt. When I try to access the DB through Enterprise Manager, I
> get this: An error occurred while trying to access the database
information.
> When I try to get properties on the database, I get this: Error 945:
> Database 'database' cannot be opened because some of the files could not
be
> activated.
> I was able to try to restore (and import) data to the DB, but got various
> error messages.
> My questions:
> 1. Can this DB be repaired?
> 2. If this DB can't be repaired, can restore or import to it?
> I made a new blank DB and tried to restore (and import) to it, but got
> errors when I tried that too.
> Any ideas?
> Thanks,
> John
>|||Thanks for the response!
How do I use the 'DROP DATABASE' command? After I delete and restore the
database, do I go into 'Query Analyzer' and issue the command there? What
does it do?
Thanks again,
John
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u7J0bPzLFHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi
> Delete the DB and re-create it, then re-restore the Data. "DROP DATABASE"
is
> the command.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "AnytimeAnywhere" <gm79ta@.yahoo.com> wrote in message
> news:11411o8s8vrbibf@.corp.supernews.com...
let[vbcol=seagreen]
> test
I[vbcol=seagreen]
> information.
> be
various[vbcol=seagreen]
>