Friday, March 30, 2012
Named Query and Global Variables
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
Monday, March 19, 2012
Mystery Database IDs in Profiler
problems.
My Profiler view contains (amongst others) the dbid column.
Much of the time, this displays familiar dbids, such as 2 (tempdb) and
5 (my main user db). However, it also regularly displays IDs of 0 and
132.
Using SELECT DB_NAME(), these translate as "master" and "NULL"
respectively.
Does anyone know:
a) why dbid = 0 translates to "master", when the actual id of this
database is 1, and
b) why Profiler reports these dbids in the first place?Hi
Database ID is a default data column and should be populated for all events.
What other columns are you capturing? Which event is displaying this value?
Not directly related to your question but these may help:
http://support.microsoft.com/defaul...kb;en-us;832524
http://msdn.microsoft.com/library/d...atabse_5xrn.asp
http://support.microsoft.com/kb/271509/EN-US/
John
"Phil" <philip.yale@.gmail.com> wrote in message
news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> I'm currently running Profiler sessions to track down Lock Timeout
> problems.
> My Profiler view contains (amongst others) the dbid column.
> Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> 5 (my main user db). However, it also regularly displays IDs of 0 and
> 132.
> Using SELECT DB_NAME(), these translate as "master" and "NULL"
> respectively.
> Does anyone know:
> a) why dbid = 0 translates to "master", when the actual id of this
> database is 1, and
> b) why Profiler reports these dbids in the first place?|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<42299950$0$8744$db0fefd9@.news.zen.co.uk>...
> Hi
> Database ID is a default data column and should be populated for all events.
> What other columns are you capturing? Which event is displaying this value?
Have variously tried capturing just the columns I thought I needed,
and then finally capturing every column possible just in case this
altered any output!
The only event I'm capturing is the LOCK TIMEOUT event. I've since
noticed that these lock timeouts (with the dbid equal to 0 or 255) all
have the same lock type (which I can't remember right now, and I'm
nowhere that I can look it up conveniently), which essentially
translates to a "key range test for a record insert". i.e. they're
not proper locks at all, and so the timeout is almost certainly an
internal mechanism for releasing them
I was just intrigued why these were associated with a dbid at all, why
0 and 255 was chosen, and why 0 additionally translates to 'master'.
BTW - genuine lock timeouts correctly display valid database IDs.
> Not directly related to your question but these may help:
> http://support.microsoft.com/defaul...kb;en-us;832524
> http://msdn.microsoft.com/library/d...atabse_5xrn.asp
> http://support.microsoft.com/kb/271509/EN-US/
>
> John
> "Phil" <philip.yale@.gmail.com> wrote in message
> news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> > I'm currently running Profiler sessions to track down Lock Timeout
> > problems.
> > My Profiler view contains (amongst others) the dbid column.
> > Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> > 5 (my main user db). However, it also regularly displays IDs of 0 and
> > 132.
> > Using SELECT DB_NAME(), these translate as "master" and "NULL"
> > respectively.
> > Does anyone know:
> > a) why dbid = 0 translates to "master", when the actual id of this
> > database is 1, and
> > b) why Profiler reports these dbids in the first place?|||Hi
It sounds like the INDID which will can be 0 or 255.
John
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
MySQL function equivalent
In MySQL, from_unixtime(lonvalue) returns the date format in
'YYYY-MM-DD HH:MM:SS'. Is there any equivalent available in SQL Server, to view the date in man readable format, when the input is an long value in milliseconds.
Eg :
SELECT FROM_UNIXTIME(875996580);
Thanks,
Smitha
Take a look at the CONVERT function in Books Online. It has different styles that you can use. Optionally you can format the string using various datepart calls.select CONVERT(varchar, CURRENT_TIMESTAMP, 121) -- odbc canonical format|||select CONVERT(varchar, '875996580', 121);
Output : 875996580
As I had said earlier, the input is a long value (in milliseconds)|||There is no native function that will understand the Unix format directly. You have to write your own TSQL UDF to do the conversion based on the format.|||
isnt 'from_unixtime(longvalue)' return a date that is longvalue seconds from 1/1/1970 ? not milleseconds?
select dateadd(s, 875996580, '1/1/1970')
yields:
1997-10-04 20:23:00.000
MySQL 5.0 Views wont work with crystal reports
If i click on a normal table its fine, but with the views it says
"Error Opening File: There are no fields in the file: "[View name]".Hi,
I've got a similar problem but using a stored proc. (See earlier posting for more info)
After much hair-pulling and soul searching i think that their might be a compatibility problem with MySQL5, MyODBC 3.51 and Crystal.
Have you tried MyODBC 5.0 yet? If it installs and works on yr side that might take yr problem away. And if it does, please let us know what you did cause it dont wanna install here.
Thanks and good luck|||Thanks
I have tried the 5.0 ODBC Connector, but unfortunatly i had no joy. Still the same error.
Wednesday, March 7, 2012
My view is timing out
See, now there's no reason to use the udf...
Monday, February 20, 2012
My menubar is all messed up
When I open up SQL Server Management Studio Express my menubar has multiple
instances of the File, Edit, View...menu options... and every toolbar
(Standard, Query Designer, Database Diagram, etc..) all have multiple options
as well.
I've gone in an modified those items as well and I get it to a point where
it looks ok, then when I shut it down and re-open it, all my changes are gone
and it's back to the way it was.
Can anyone help me on this one? Is this the right place to post this
question?
Thanks in advance,
John Scott.
SQL Server 2005 stores it's configuration information in following location:
My Documents\SQL Server Management
Studio\Settings\CurrentSettings-2007-06-06.vssettings
Check to make sure there is no writing problems to that.
The path information for the config file is located at
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\90\Tools\Shell\Profile\
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Scott" wrote:
> Ok..this is a weird one.
> When I open up SQL Server Management Studio Express my menubar has multiple
> instances of the File, Edit, View...menu options... and every toolbar
> (Standard, Query Designer, Database Diagram, etc..) all have multiple options
> as well.
> I've gone in an modified those items as well and I get it to a point where
> it looks ok, then when I shut it down and re-open it, all my changes are gone
> and it's back to the way it was.
> Can anyone help me on this one? Is this the right place to post this
> question?
>
> --
> Thanks in advance,
> John Scott.
|||Thank for the reply Mohit,
I checked two things...there are some goofy entries in the vssettings file
in my My Documents...file.
I made changes to it, but there is no change when I open up SQL Express...
I also checked and I don't have the
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\90\Tools\Shell\Profile\ key I have all the way up to the Shell..I
have no Profile in there.
Other suggestions?
Thanks,
John Scott.
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> SQL Server 2005 stores it's configuration information in following location:
> My Documents\SQL Server Management
> Studio\Settings\CurrentSettings-2007-06-06.vssettings
> Check to make sure there is no writing problems to that.
> The path information for the config file is located at
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\90\Tools\Shell\Profile\
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Scott" wrote:
|||Sorry for late reply.
Sorry the proper path in registry is
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\90\Tools\ShellSEM\Profile\.
I can't get the issue to replicate. Here are two options ...
- Delete the CurrentSettings-DATE.vsssettings file.
This will force SSMSEE to reinitialize the settings. Do a search on your
computer for vsssettings delete anything with CurrentSettings in it.
OR
- Try Reinstalling the SSMSEE.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Ok...I did a few things.
1. I deleted my CurrentSettings file in ssmses settings folder...when I
opened up ssmses it poped up a message saying it coudn't find my settings
folder and that it would use the most recent settings for this session
2. I checked that registry path...and i did find it, didn't find anything
out of the ordinary...it had 13 values inside that key
AutoSaveFile
AutoSaveFILETIMEHigh
AutoSaveFILETimeLow
BuildNum
DefaultSettinsDirectory
LastResetSettingsFile
SettingsCachesFILEIMEHigh
SettingsCachesFILEIMELow
Sku
TeamSettingsFile
TeamSettingsFILETIMEHigh
TeamSettingsFILETIMELow
TraackTeamSettings
for the LastResetSettingsFile the value was
'%vsspv_vs_install_directory%\Profiles\ssmsee.vsse ttings'
I checked out this file and there is a node called Caegory
name="Environment_WindowLayout"
inside it, there is another node call "PropertyVale name="WindowLayout">
then after that there is a whole bunch of garbled text (looks like viewstate
data)
then the PropertyValue node closes...
I don't know if that is normal or not...
Any other suggestions?
Thanks,
John Scott.
"Mohit K. Gupta" wrote:
> Sorry for late reply.
> Sorry the proper path in registry is
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\90\Tools\ShellSEM\Profile\.
> I can't get the issue to replicate. Here are two options ...
> - Delete the CurrentSettings-DATE.vsssettings file.
> This will force SSMSEE to reinitialize the settings. Do a search on your
> computer for vsssettings delete anything with CurrentSettings in it.
> OR
> - Try Reinstalling the SSMSEE.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
|||Delete all settings files. I would just try reinstalling though.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Scott" wrote:
[vbcol=seagreen]
> Ok...I did a few things.
> 1. I deleted my CurrentSettings file in ssmses settings folder...when I
> opened up ssmses it poped up a message saying it coudn't find my settings
> folder and that it would use the most recent settings for this session
> 2. I checked that registry path...and i did find it, didn't find anything
> out of the ordinary...it had 13 values inside that key
> AutoSaveFile
> AutoSaveFILETIMEHigh
> AutoSaveFILETimeLow
> BuildNum
> DefaultSettinsDirectory
> LastResetSettingsFile
> SettingsCachesFILEIMEHigh
> SettingsCachesFILEIMELow
> Sku
> TeamSettingsFile
> TeamSettingsFILETIMEHigh
> TeamSettingsFILETIMELow
> TraackTeamSettings
>
> for the LastResetSettingsFile the value was
> '%vsspv_vs_install_directory%\Profiles\ssmsee.vsse ttings'
> I checked out this file and there is a node called Caegory
> name="Environment_WindowLayout"
> inside it, there is another node call "PropertyVale name="WindowLayout">
> then after that there is a whole bunch of garbled text (looks like viewstate
> data)
> then the PropertyValue node closes...
> I don't know if that is normal or not...
> Any other suggestions?
>
> --
> Thanks,
> John Scott.
>
> "Mohit K. Gupta" wrote: