adding a query as parameter using 'contains' logic

Hello,
Setting parameter ranges for a query is very useful.
How can I specify : begins with ..... and make that a parameter (in this
case first 5 digits of a project name)?
Liek '%[parameter3]%' does not work.
What should it be?
Help much appreciated,
An 'over-his-head' Texas Tonie
Texas Tonie,
If you want a range of values, look at the BETWEEN operator. For character
strings, however, the whole issue of collation and sort order needs to be
considered when defining what is between.
For your example problem, assuming that the 'number' is actually a character
string (not some sort of numeric datatype) and that the number may have
leading spaces, then you could use:
DECLARE @.Value = ' 12345'
SELECT @.Value WHERE LTRIM(@.Value) LIKE '12345%'
FWIW,
RLF
"Texas Tonie" <TexasTonie@.discussions.microsoft.com> wrote in message
news:72C622EC-3C49-41C7-8259-2274E9396CD3@.microsoft.com...
> Hello,
> Setting parameter ranges for a query is very useful.
> How can I specify : begins with ..... and make that a parameter (in this
> case first 5 digits of a project name)?
> Liek '%[parameter3]%' does not work.
> What should it be?
> Help much appreciated,
> An 'over-his-head' Texas Tonie

Adding a .sql to MSDE Server

Hi All,
I have a .SQL script file that contains database definitions. How can i run
the .sql script file to add this database to my MSDE SQL server?
Thanks for your help
mustafa
"Mustafa Rabie" <mrabie007@.hotmail.com> wrote in message
news:%23vKwbfJBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a .SQL script file that contains database definitions. How can i
run
> the .sql script file to add this database to my MSDE SQL server?
>
Start a command prompt and use OSQL... enter: OSQL -? to get the available
options.
Steve
|||Hi, this is from "Microsoft SQL Server 2000 Sample Database Scripts"
[http://www.microsoft.com/downloads/d...displaylang=en]
To run the scripts using osql:
1.. Open a Command Prompt window.
2.. From the command prompt, use the cd command to navigate to the folder
containing the SQL Server 2000 Sample Database Scripts:
cd c:\program files\SQL Server 2000 Sample Databases
3.. Run osql specifying either Instpubs.sql or Instnwnd.sql as the input
file. These are some examples:
a.. To install the Northwind database on a default instance on the same
computer:
osql -E -i instnwnd.sql
b.. To install the pubs database on a default instance on a different
computer:
osql -E -S ComputerName -i instpubs.sql
c.. To install the Northwind database on a named instance:
osql -E -S ComputerName/InstanceName -i instnwnd.sql
J.Alberto
[ccs-ve]
Nota: no enviar mensajes a mi direccion e-mail,
solo spam. Avisar para asignarle un filtro y no sea
eliminado.

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

add new row to existing table

I have about 600 records that I want to add to an existing table in a DB. T
he table row contains about 48 columns. I have data for 4 of those columns
in an excel spreadsheet. All other columns except 3 can be null. If I put
a value in the non-null col
umns, then I assume I can use the DTS import wizard to import the data from
the spread sheet to the desired table in the DB. Am I correct?
Thanks
JoelJoel,
You should be able to do that. You can also try something like this:
insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
select A,B,C,D,'whatever', 'whatever', 'whatever'
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
HDR=YES;
IMEX=1;
Database=c:\path_to\yourSpreadsheet.xls',
'select * from [Sheet1$]'
)
If the other not null columns are not strings, use constants of the
appropriate type. The DTS wizard might be easier - there are some
issues importing Excel with OpenRowset if the column types aren't
obvious from the data, and with blank values, but with only 600 rows,
you can probably just run the select from the insert above and look to
see if what you see is right.
A quick and dirty way to get data from Excel to SQL Server is to use
string functions and concatenation in Excel to create a column something
like
="insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
values('"&A1&"','"&A2 ... and so on,
then copy and paste the insert statements into Query Analyzer and run them.
Steve Kass
Drew University
joel wrote:

>I have about 600 records that I want to add to an existing table in a DB. The tabl
e row contains about 48 columns. I have data for 4 of those columns in an excel spr
eadsheet. All other columns except 3 can be null. If I put a value in the non-null
co
lumns, then I assume I can use the DTS import wizard to import the data from the spread she
et to the desired table in the DB. Am I correct?
>Thanks
>Joel
>

Add javascript code to report page inside the report manager

Hi all,
Does anyone knows how can I add some javascript code into the html page
which contains a report into the report manager?.
Let's the following scenario, suppose that any user wants to execute some
reports from report manager, first the user goes to http://server/reports
after that he/she will be at home page configured due to his/her profile, the
user select a report with a click and, he/she executes it. The report will be
rendered in html inside the same window where the report manager is, but in
different frame.
I know using both htc and css files I can modify some behavior of the html
page but, the problem is that both report manager and the rendered report
coexist in the same window( I mean different frames for each of them ) I
don't know how to add some javascript code into the frame or page or whatever
the report lives in.
Any help will be appreciated.Hi
in the htc you can access the frame page which contain the report and the
report manager using the javascript dhtml code.
you can use like
frame[0].document.
or window.document.getelementbyID['asd'].
you dont have to insert script in the frame
you can put all the code in htc to call the code..
"jhcp" wrote:
> Hi all,
> Does anyone knows how can I add some javascript code into the html page
> which contains a report into the report manager?.
> Let's the following scenario, suppose that any user wants to execute some
> reports from report manager, first the user goes to http://server/reports
> after that he/she will be at home page configured due to his/her profile, the
> user select a report with a click and, he/she executes it. The report will be
> rendered in html inside the same window where the report manager is, but in
> different frame.
> I know using both htc and css files I can modify some behavior of the html
> page but, the problem is that both report manager and the rendered report
> coexist in the same window( I mean different frames for each of them ) I
> don't know how to add some javascript code into the frame or page or whatever
> the report lives in.
> Any help will be appreciated.|||Hi Bava,
thanks for your answer but, how can I know which frame contains the rendered
report?.
The htc file I coded is the following
<public:component tagName="test1">
<PUBLIC:ATTACH EVENT="oncontextmenu" onevent="f_CancelEvent()" />
<PUBLIC:ATTACH EVENT="onselectstart" onevent="f_CancelEvent()" />
<PUBLIC:ATTACH EVENT="ondragstart" onevent="f_CancelEvent()" />
</public:component>
<SCRIPT language="javascript">
function f_CancelEvent()
{
event.returnValue = false;
}
</script>
After that, I reference that piece of code in a css file as following
body {
margin: 0;
background-color: White;
font-family: Verdana, Sans-Serif;
font-size: x-small;
behavior: url(../js/test1.htc);
}
"Bava Mani" wrote:
> Hi
> in the htc you can access the frame page which contain the report and the
> report manager using the javascript dhtml code.
> you can use like
> frame[0].document.
> or window.document.getelementbyID['asd'].
> you dont have to insert script in the frame
> you can put all the code in htc to call the code..
> "jhcp" wrote:
> > Hi all,
> > Does anyone knows how can I add some javascript code into the html page
> > which contains a report into the report manager?.
> > Let's the following scenario, suppose that any user wants to execute some
> > reports from report manager, first the user goes to http://server/reports
> > after that he/she will be at home page configured due to his/her profile, the
> > user select a report with a click and, he/she executes it. The report will be
> > rendered in html inside the same window where the report manager is, but in
> > different frame.
> > I know using both htc and css files I can modify some behavior of the html
> > page but, the problem is that both report manager and the rendered report
> > coexist in the same window( I mean different frames for each of them ) I
> > don't know how to add some javascript code into the frame or page or whatever
> > the report lives in.
> > Any help will be appreciated.|||Hi,
are you using 2000 or 2005 reporting service?
in 2000 there is only one iframe so you can just use
window.frames.item(0)
or
window.document.getElementById(framid) if it is 2005
hope it helps
"jhcp" wrote:
> Hi Bava,
> thanks for your answer but, how can I know which frame contains the rendered
> report?.
> The htc file I coded is the following
> <public:component tagName="test1">
> <PUBLIC:ATTACH EVENT="oncontextmenu" onevent="f_CancelEvent()" />
> <PUBLIC:ATTACH EVENT="onselectstart" onevent="f_CancelEvent()" />
> <PUBLIC:ATTACH EVENT="ondragstart" onevent="f_CancelEvent()" />
> </public:component>
> <SCRIPT language="javascript">
> function f_CancelEvent()
> {
> event.returnValue = false;
> }
> </script>
> After that, I reference that piece of code in a css file as following
> body {
> margin: 0;
> background-color: White;
> font-family: Verdana, Sans-Serif;
> font-size: x-small;
> behavior: url(../js/test1.htc);
> }
>
> "Bava Mani" wrote:
> > Hi
> > in the htc you can access the frame page which contain the report and the
> > report manager using the javascript dhtml code.
> > you can use like
> > frame[0].document.
> > or window.document.getelementbyID['asd'].
> > you dont have to insert script in the frame
> > you can put all the code in htc to call the code..
> >
> > "jhcp" wrote:
> >
> > > Hi all,
> > > Does anyone knows how can I add some javascript code into the html page
> > > which contains a report into the report manager?.
> > > Let's the following scenario, suppose that any user wants to execute some
> > > reports from report manager, first the user goes to http://server/reports
> > > after that he/she will be at home page configured due to his/her profile, the
> > > user select a report with a click and, he/she executes it. The report will be
> > > rendered in html inside the same window where the report manager is, but in
> > > different frame.
> > > I know using both htc and css files I can modify some behavior of the html
> > > page but, the problem is that both report manager and the rendered report
> > > coexist in the same window( I mean different frames for each of them ) I
> > > don't know how to add some javascript code into the frame or page or whatever
> > > the report lives in.
> > > Any help will be appreciated.|||Hi Bava,
I'm using SSRS 2000.
Actually, I don't know how can I reference the iframe from htc file. Let me
explain.
What I want to do is to disable select any text or image inside the report
frame and, disable contextual menu too, that's why I posted the code
yesterday.
Could you please help me to do that?
I really appreciated your help.
"Bava Mani" wrote:
> Hi,
> are you using 2000 or 2005 reporting service?
> in 2000 there is only one iframe so you can just use
> window.frames.item(0)
> or
> window.document.getElementById(framid) if it is 2005
> hope it helps
> "jhcp" wrote:
> > Hi Bava,
> > thanks for your answer but, how can I know which frame contains the rendered
> > report?.
> > The htc file I coded is the following
> >
> > <public:component tagName="test1">
> > <PUBLIC:ATTACH EVENT="oncontextmenu" onevent="f_CancelEvent()" />
> > <PUBLIC:ATTACH EVENT="onselectstart" onevent="f_CancelEvent()" />
> > <PUBLIC:ATTACH EVENT="ondragstart" onevent="f_CancelEvent()" />
> > </public:component>
> > <SCRIPT language="javascript">
> > function f_CancelEvent()
> > {
> > event.returnValue = false;
> > }
> > </script>
> > After that, I reference that piece of code in a css file as following
> >
> > body {
> > margin: 0;
> > background-color: White;
> > font-family: Verdana, Sans-Serif;
> > font-size: x-small;
> > behavior: url(../js/test1.htc);
> > }
> >
> >
> >
> > "Bava Mani" wrote:
> >
> > > Hi
> > > in the htc you can access the frame page which contain the report and the
> > > report manager using the javascript dhtml code.
> > > you can use like
> > > frame[0].document.
> > > or window.document.getelementbyID['asd'].
> > > you dont have to insert script in the frame
> > > you can put all the code in htc to call the code..
> > >
> > > "jhcp" wrote:
> > >
> > > > Hi all,
> > > > Does anyone knows how can I add some javascript code into the html page
> > > > which contains a report into the report manager?.
> > > > Let's the following scenario, suppose that any user wants to execute some
> > > > reports from report manager, first the user goes to http://server/reports
> > > > after that he/she will be at home page configured due to his/her profile, the
> > > > user select a report with a click and, he/she executes it. The report will be
> > > > rendered in html inside the same window where the report manager is, but in
> > > > different frame.
> > > > I know using both htc and css files I can modify some behavior of the html
> > > > page but, the problem is that both report manager and the rendered report
> > > > coexist in the same window( I mean different frames for each of them ) I
> > > > don't know how to add some javascript code into the frame or page or whatever
> > > > the report lives in.
> > > > Any help will be appreciated.

Add Date only to Time only = datetime ?

I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
CarlIf I understand you, you want use date from EVT_DT, use time from EVT_TM put them together and retrieve datetime: '2005-12-05 13:59:00'

if I'm right, transform dates to varchar, cut what u need, concatenate strings and transform it back to date time:

declare @.EVT_DT datetime
declare @.EVT_TM datetime

set @.EVT_DT = '2005-12-05 00:00:00'
set @.EVT_TM = '2005-12-06 13:59:00'

select
cast(
cast(@.EVT_DT as varchar(11)) + ' ' +
cast(datepart(hh, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(mi, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(ss, @.EVT_TM) as varchar(2))
as datetime)

2005-12-05 13:59:00.000|||I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
Carl

Just so you are aware, using your examples above, SQL would "treat" EVT_DT as 38689.00000 and EVT_TM as 38690.58264. So, when you try to add them together, you're getting an unexpected value. A better practice would be to store the completed Date/Time together in a single column and then deal with the issue of selecting for a specific data by using BETWEEN.

Regards,

hmscott

Add control in TreeView control

Hey all .net programmers..

I am thinking about Tree View control which contains controls. any idea?

---

Microsoft Tree View control only support add nodes as text. i think. but istead of text, putting Button or link or any usercontrol.. what do u think?

I suspect that one of the Visual Studio forums would be a more likely place to post this question and get a meaningful response.|||True and check(search_) that forum as this might have discussed there already.|||

http://www.codeproject.com/Purgatory/Button_TreeView_Control.asp

Found this link. Still looking for the other solution.

Felice

Add control in TreeView control

Hey all .net programmers..

I am thinking about Tree View control which contains controls. any idea?

---

Microsoft Tree View control only support add nodes as text. i think. but istead of text, putting Button or link or any usercontrol.. what do u think?

I suspect that one of the Visual Studio forums would be a more likely place to post this question and get a meaningful response.|||True and check(search_) that forum as this might have discussed there already.|||

http://www.codeproject.com/Purgatory/Button_TreeView_Control.asp

Found this link. Still looking for the other solution.

Felice

add a column if product is in discrepancies table (was "sql query")

I have a transaction table (which contains details of all products on all orders)
and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...

Now I want to write a query returning details of all products from a specfic orders.. pretty simple

SELECT product, description, qty
from TRANSACTIONS
where order_no = 'xxx'

but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit...

I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...

Be garteful for any help :confused:

Thanksselect product
, description
, qty
, case when exists
( select 937
from discrepancies
where product_id = t.product_id )
then 'oui'
else 'non'
end as discrepancy_exists
from TRANSACTIONS as t
where order_no = 'xxx'|||thanks worked like a treat :)

Ad hoc reporting Model and Security Filters

I am having some problems with creating security filters.

The way my system is setup is that I have a main table that contains all my data. I also have a security table that tells me what records each user has access to. I created an entity for my security filter and have a security filter defined that limits the data to the rows that have the logged on users id. This works fine if I look at the data I see the rows that are for the signed on user. Next I created a filter on my main table that limits the rows to the rows the user has access to. I did this by setting up another filter that says the UserCode from the main table must equal the user code from the security table. (I did this by right clicking and choosing edit as formula). This will then limit my data to the rows this user has access to. This all works but it is very slow. I captured the sql code that was running and noticed that the filter was doing a left outer join on my security table and joining on the unfiltered code then using a where clause to limit the data. When I run this logic it basically does a scan of my main table. If I change this join to be an inner join and join on the filtered data my results come back quickly.

Now to my question. Is this the way the system should work? Am I doing something wrong? Are there other ways to do this?

Thanks in advance for any help.Any success on the security filters? Mind sharing how do you exactly implement this? These security filters are really hard to understand and implement for me.

Ad hoc reporting Model and Security Filters

I am having some problems with creating security filters.

The way my system is setup is that I have a main table that contains all my data. I also have a security table that tells me what records each user has access to. I created an entity for my security filter and have a security filter defined that limits the data to the rows that have the logged on users id. This works fine if I look at the data I see the rows that are for the signed on user. Next I created a filter on my main table that limits the rows to the rows the user has access to. I did this by setting up another filter that says the UserCode from the main table must equal the user code from the security table. (I did this by right clicking and choosing edit as formula). This will then limit my data to the rows this user has access to. This all works but it is very slow. I captured the sql code that was running and noticed that the filter was doing a left outer join on my security table and joining on the unfiltered code then using a where clause to limit the data. When I run this logic it basically does a scan of my main table. If I change this join to be an inner join and join on the filtered data my results come back quickly.

Now to my question. Is this the way the system should work? Am I doing something wrong? Are there other ways to do this?

Thanks in advance for any help.Any success on the security filters? Mind sharing how do you exactly implement this? These security filters are really hard to understand and implement for me.

AD - SQL Service account - local or not?

Hi All,
I am looking for 'Best Practice' advise on how SQL Service accounts should b
e set up in an environment that contains many SQL 2000 Servers in an Active
Directory environment.
Is it a better practice to have the SQL Service (and SQL Agent services) run
off of Locally defined accounts (accounts defined on the SQL Server box its
elf) or AD/domain accounts? I can see how it would be a better security pol
icy to have each SQL Server
have locally defined service accounts as opposed to running off of domain/AD
accounts, but I am looking to see if there are any Best Practices advise on
this.If you do not need to access other servers on the network (like an exchange
server) then use a local account or the local system
Ray Higdon MCSE, MCDBA, CCNA
--
"DBA" <anonymous@.discussions.microsoft.com> wrote in message
news:92EC3F12-05F0-402A-8F93-01AC6113F7D6@.microsoft.com...
> Hi All,
> I am looking for 'Best Practice' advise on how SQL Service accounts should
be set up in an environment that contains many SQL 2000 Servers in an Active
Directory environment.
> Is it a better practice to have the SQL Service (and SQL Agent services)
run off of Locally defined accounts (accounts defined on the SQL Server box
itself) or AD/domain accounts? I can see how it would be a better security
policy to have each SQL Server have locally defined service accounts as
opposed to running off of domain/AD accounts, but I am looking to see if
there are any Best Practices advise on this.
>
>|||Also, if you are using virtual SQL Servers the startup account has to be
domain account.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Microsoft SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non
-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes
.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still wa
y
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Som
ething like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can chang
e even between service
packs. So, in other words, a variable is *not* the same thing as the optimiz
er knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to re
ly on parameter
sniffing. This value in the plan might of course be off for the subsequent u
sages of the plan.
Or, you would have to get a new plan for each execution. Either break out th
e critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 y
ou can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain
value.
You can of course add WITH RECOMPILE for the whole procedure, but then none
of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...[vbcol
=seagreen]
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two n
on-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
to
> about 1.6 million reads using some dynamic sql and adding additional index
es.
> Still, in the execution plan, several of the user tables have estimated vs
.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropria
te
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated r
ow
> counts closer. I have ran "update statistics <table_name> with fullscan" o
n
> all the major tables used in the query and especially on those tables whos
e
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on th
e
> tables, sp_recompile, but in each case, the estimated vs. actual is still
way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs
.
> estimated more in line?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>[/vbcol]|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:[vbcol=seagreen]
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for you
r
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its ow
n
>generated plan.
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1

Actual vs. Estimated Row Counts

I have a stored procedure that contains a complex query of approximately 16
joins, (a mixture of inner and left joins). These joins are mostly on
standard user tables, but includes joins on two temporary tables and two non-
indexable views.
I have rewritten the query so as to reduce the reads from 5 million plus, to
about 1.6 million reads using some dynamic sql and adding additional indexes.
Still, in the execution plan, several of the user tables have estimated vs.
actual row counts that are quite different, such as 1 Estimated row versus
135,000 actual rows. These tables are using, what I consider the appropriate
index, but, as stated, the actual vs. estimated are way off.
The procedure takes several arguments and I set those arguments as local
variables in the procedure to prevent parameter sniffing.
I was hoping to get a better plan by getting the actual versus estimated row
counts closer. I have ran "update statistics <table_name> with fullscan" on
all the major tables used in the query and especially on those tables whose
actual vs. estimated are way off. This had no effect on the actual vs.
estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
tables, sp_recompile, but in each case, the estimated vs. actual is still way
off.
I have considered reordering some of the inner and left joins, but was not
sure if that would have any affect.
Would you be able to advise on what else one might do to get the actual vs.
estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1its difficult to get a better and more accurate plan after your actions
(updating stats etc...)
Specially with left joins...
the only way is to force the way you want to do your joins (manually
specify the hash/ merge option in the joins, setup the better order for your
joins etc...)
in this case you force SQL Server to use your hints instead of using its own
generated plan.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two
> non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus,
> to
> about 1.6 million reads using some dynamic sql and adding additional
> indexes.
> Still, in the execution plan, several of the user tables have estimated
> vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the
> appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated
> row
> counts closer. I have ran "update statistics <table_name> with fullscan"
> on
> all the major tables used in the query and especially on those tables
> whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on
> the
> tables, sp_recompile, but in each case, the estimated vs. actual is still
> way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual
> vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
That way, the optimizer uses hard-coded values to determine selectivity. Something like:
= 10% (actually, it might go by density in the statistics, if available)
> 30%
BETWEEN 25%
The percentage for the values differs from release to release, and can change even between service
packs. So, in other words, a variable is *not* the same thing as the optimizer knowing the value you
are looking for.
For the optimizer to *know* the value and have a static plan, you have to rely on parameter
sniffing. This value in the plan might of course be off for the subsequent usages of the plan.
Or, you would have to get a new plan for each execution. Either break out the critical parts of the
procedure to its own procedures and create them WITH RECOMPILE, or in 2005 you can add RECOMPILE
hint at the query level. Or you can in 2005 even say OPTIMIZE FOR a certain value.
You can of course add WITH RECOMPILE for the whole procedure, but then none of the DML statements in
the procedure will be pre-optimized.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:679360284efd2@.uwe...
>I have a stored procedure that contains a complex query of approximately 16
> joins, (a mixture of inner and left joins). These joins are mostly on
> standard user tables, but includes joins on two temporary tables and two non-
> indexable views.
> I have rewritten the query so as to reduce the reads from 5 million plus, to
> about 1.6 million reads using some dynamic sql and adding additional indexes.
> Still, in the execution plan, several of the user tables have estimated vs.
> actual row counts that are quite different, such as 1 Estimated row versus
> 135,000 actual rows. These tables are using, what I consider the appropriate
> index, but, as stated, the actual vs. estimated are way off.
> The procedure takes several arguments and I set those arguments as local
> variables in the procedure to prevent parameter sniffing.
> I was hoping to get a better plan by getting the actual versus estimated row
> counts closer. I have ran "update statistics <table_name> with fullscan" on
> all the major tables used in the query and especially on those tables whose
> actual vs. estimated are way off. This had no effect on the actual vs.
> estimated row counts. I have also run sp_updatestats, dbcc dbreindex on the
> tables, sp_recompile, but in each case, the estimated vs. actual is still way
> off.
> I have considered reordering some of the inner and left joins, but was not
> sure if that would have any affect.
> Would you be able to advise on what else one might do to get the actual vs.
> estimated more in line?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||Thanks for your reply Jeje.
In regards to your comment of setting up a better order on my joins, is the
guide to setting up joins...ordering joins with the most selective join
first, followed by the next most selective join, etc., etc.?
Jeje wrote:
>its difficult to get a better and more accurate plan after your actions
>(updating stats etc...)
>Specially with left joins...
>the only way is to force the way you want to do your joins (manually
>specify the hash/ merge option in the joins, setup the better order for your
>joins etc...)
>in this case you force SQL Server to use your hints instead of using its own
>generated plan.
>>I have a stored procedure that contains a complex query of approximately 16
>> joins, (a mixture of inner and left joins). These joins are mostly on
>[quoted text clipped - 36 lines]
>> vs.
>> estimated more in line?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1