convert a database from SQL6.5 to SQL2000

I have the same question that was asked last week by
another user but never answered. The question was...
"If I convert a database from SQL6.5 to SQL2000 will I
need to increase the size of the database in SQL2000? Is
SQL2000 more space hungry than SQL6.5?
I know that if I do an inplace upgrade I need to reserve
extra space but once the upgrade is done will space
requirements be the same?"The space requirement will be heavily dependent on the database design. In
SQL Server 6.5, columns declared as CHAR (50) NULL, if they did contain a
NULL,
essentially took very little space. In SQL Server 2k, such columns take up
50
bytes regardless of the data value in the column. If your applications have
many
tables with many such columns, the size of the database might well be
significantly larger. In general, however, SQL Server 2k utilizes disk space
more efficiently, so most databases do not grow, and frequently shrink, in
size.
--
HTH
Ryan Waight, MCDBA, MCSE
"Sarah Roberts" <robertss@.unknown.com> wrote in message
news:a11401c380a7$db3bd0d0$a601280a@.phx.gbl...
> I have the same question that was asked last week by
> another user but never answered. The question was...
> "If I convert a database from SQL6.5 to SQL2000 will I
> need to increase the size of the database in SQL2000? Is
> SQL2000 more space hungry than SQL6.5?
> I know that if I do an inplace upgrade I need to reserve
> extra space but once the upgrade is done will space
> requirements be the same?"
>|||Hello
> "If I convert a database from SQL6.5 to SQL2000 will I
> need to increase the size of the database in SQL2000? Is
> SQL2000 more space hungry than SQL6.5?
As far as I remember, after converting database from 6.5
format to 7.0 (I don't think that version 2000 differs from 7.0
therein) my database became 20-25% smaller.
Serge Shakhov

ControlParameter and Stored Procedures

I'm sure I'm missing something silly. I have 3 textboxes, a stored procedure and a gridview. The user will put something in the 3 boxes, click submit, and see a grid with stuff (I hope). However, the grid will only return data is I EXCLUDE the controlparameters and only use the sessionparameter. It's like the stored proc won't even fire!

HTML:

<formid="form1"runat="server">
<div>
Lastname: <asp:textboxid="Lastname"runat="server"></asp:textbox>
Hobbies: <asp:textboxid="Hobbies"runat="server"></asp:textbox><br/>
Profession: <asp:textboxid="Profession"runat="server"></asp:textbox>
<asp:buttonid="Button1"runat="server"text="Button"/><br/>
<asp:gridviewskinid="DataGrid"id="GridView1"runat="server"allowpaging="True"allowsorting="True"autogeneratecolumns="False"datasourceid="SqlDataSource1">
<columns>
<asp:boundfielddatafield="Username"headertext="Username"sortexpression="Username"/>
<asp:boundfielddatafield="Lastname"headertext="Lastname"sortexpression="Lastname"/>
<asp:boundfielddatafield="Firstname"headertext="Firstname"sortexpression="Firstname"/>
</columns>
</asp:gridview><asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:HOAConnectionString %>"
selectcommand="spAddressBookSelect"selectcommandtype="StoredProcedure">
<selectparameters>
<asp:sessionparameterdefaultvalue="0"name="CommunityID"sessionfield="CommunityID"type="Int32"/>
<asp:controlparametercontrolid="Lastname"name="Lastname"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Profession"name="Profession"propertyname="Text"type="String"/>
<asp:controlparametercontrolid="Hobbies"name="Hobbies"propertyname="Text"type="String"/>
</selectparameters>
</asp:sqldatasource></div></form>

sp signature:
ALTER PROCEDURE[dbo].[spAddressBookSelect]
@.CommunityIDint= 0,
@.Lastnamevarchar(200) =NULL,
@.Professionvarchar(200) =NULL,
@.Hobbiesvarchar(200) =NULL

I'm guessing that you mean it doesn't work when all three textboxes aren't filled in. It should work if you fill out all of them. If that is the case, then your problem is that you are passing a NULL value if the textbox is blank, and you haven't set the "CancelSelectOnNullParameter" property of the sqldatasource control to false, so yes, it's not firing the SELECT.

|||Nicely done! That was it, thanks!

Controlling User Privileges

Hi,
I'm designing an application which uses SQL Server 2000 as
the database. At the application level, I have features to
maintain & control application level users.
Approach-1
Users login to the application using their application
logins and the application will authenticate their
privileges. But internally the application use a SINGLE
SQL Server login to access the database(s) in the server.
Approach-2
At the SQL Server level, we create logins and database
users for each application user and map them. So depending
upon the application user, the corresponding sql server
login/user will be used to access the database(s).
Now, I want to assess the Pros & Cons of these two
approaches.
Sorry for making this posting so big.
Expecting guidance and pointers to any relevant resources.
TIA,
HariHave you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
> Approach-1
> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
> Approach-2
> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
> Now, I want to assess the Pros & Cons of these two
> approaches.
> Sorry for making this posting so big.
> Expecting guidance and pointers to any relevant resources.
> TIA,
> Hari|||Hi Hari,
Hope you have considered the number of users logging in and the kind of changes they are going to make through your application?
If the users are going to be Windows Domain users, then you can go for what Dejan has suggested. And if the users are in large numbers and do not have windows domain authentication, it is better to go with your first approach. Beware that you will not know what changes the users are going to make, unless you are logging it somewhere at the application level.
And the second approach will be useful if you want to control user access at the database level.
Thanks
GYK
-- Dejan Sarka wrote: --
Have you considered 3rd possibility - using trusted authentication. i.e.
Windows users and groups? Check the "Managing Security" topics in Bokks
OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm).
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:09c701c3a421$b9541fc0$a101280a@.phx.gbl...
> Hi,
>> I'm designing an application which uses SQL Server 2000 as
> the database. At the application level, I have features to
> maintain & control application level users.
>> Approach-1
>> Users login to the application using their application
> logins and the application will authenticate their
> privileges. But internally the application use a SINGLE
> SQL Server login to access the database(s) in the server.
>> Approach-2
>> At the SQL Server level, we create logins and database
> users for each application user and map them. So depending
> upon the application user, the corresponding sql server
> login/user will be used to access the database(s).
>> Now, I want to assess the Pros & Cons of these two
> approaches.
>> Sorry for making this posting so big.
>> Expecting guidance and pointers to any relevant resources.
>> TIA,
> Hari

Controlling Trigger actions based on user

Is it possible to control the actions of a trigger based on the user who
updates the record?
In pseudo code, I'm trying to do the following:
on update:
if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
{ newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
Could someone shead some light on if/how this could be done in "real" code?
Any help would be GREATLY appreciated.
Thanks,
_KThe trigger's code should look very similar to your pseudo code (not
tested):
IF SUSER_SNAME() = 'User_A'
BEGIN
UPDATE T1
SET PIC_FIELD = D.PIC_FIELD
FROM T1 JOIN deleted AS D
ON T1.key = D.key
WHERE T1.Artist_Type IN('DJ', 'CL')
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control the actions of a trigger based on the user who
> updates the record?
> In pseudo code, I'm trying to do the following:
> on update:
> if (updating user = "User_A") and (Artist_Type = "DJ" or "CL") then
> { newrecord.PIC_FIELD = oldrecord.PIC_FIELD }
>
> Could someone shead some light on if/how this could be done in "real"
> code?
> Any help would be GREATLY appreciated.
> Thanks,
> _K
>|||Thanks VERY much.
Would it still be deleted if this is an update trigger?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
> The trigger's code should look very similar to your pseudo code (not
> tested):
> IF SUSER_SNAME() = 'User_A'
> BEGIN
> UPDATE T1
> SET PIC_FIELD = D.PIC_FIELD
> FROM T1 JOIN deleted AS D
> ON T1.key = D.key
> WHERE T1.Artist_Type IN('DJ', 'CL')
> END
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "KBryan" <kbryan@.noyouwont.com> wrote in message
> news:%237v6ttWNFHA.2716@.TK2MSFTNGP10.phx.gbl...
>|||Yes; deleted holds the old image of the modified data.
BG, SQL Server MVP
www.SolidQualityLearning.com
"KBryan" <kbryan@.noyouwont.com> wrote in message
news:%23WFXaMXNFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks VERY much.
> Would it still be deleted if this is an update trigger?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23pug97WNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>

Controlling export formats based on user role

I'm using Reporting Services 2000 and I have a situation where I need to specify the file formats available to export. I know I can change the RSReportServer.config file to change these at a global level, but what I really need is to be able to configure these differently for different user groups. For example:

group 1 - has all available export formats available

group 2 - only has the ability to export to excel

group 3 - has no option to export to any format.

Is there a way for this to be done? If so, can anybody give me any pointers on how it can be done?

Any help given will be most appreciated!!

In Report Manager there is no way to limit export formats by role.

You would need to create a custom application to host the reporting services control, and limit your users in this way.

cheers,

Andrew

|||

ok thanks I thought this might be the case but just wondered if any simpler solutions were about.

Thanks again for your reply

Controlling create & drop proc, view privilege

Hi,
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiMani
You can EXECUTION permission on the stored procedure for the user
Also ,you can remove him/her from sysadmin fixed server role but he/she
should be member db_owner fixed database and must qualified User.sp
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:0C3FBF63-843E-465E-98C0-4BE9152BF08F@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
trap.
> since the user can create procedures with dbo as the owner, if the SP has
a
> drop table command, that would execute in the owners context and hence
would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||1. A user needs to be a member of db_owner or db_ddladmin
roles (or sysadmin) to create a objects owned by dbo.
Members of db_owner and db_ddladmin need to qualify the
owner as dbo.object when they create the objects to be owned
by dbo.
2. It depends first on ownership the ownership chain. If the
ownership chains are intact, the secuirty is checked for
permissions to execute the stored procedure only. If the
ownership chain is broken, permissions are checked on each
branch where the owner of the object is different. You can
find more information in books online under ownership chains
-Sue
On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
<Mani@.discussions.microsoft.com> wrote:

>Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
>db_datareader, to only create & drop stored procs and views in DevDB. What
>extra permissions does the user need ?
>I tried playing with the "grant create proc to user" command. But it lets
>the user create procs with him as owner. In the current case, the applicati
on
>needs all objects to be owned by dbo, so the user needs to be able to run
>"create proc dbo.tempProc as ..."
>In case there is a solution to the above, we might fall into the next trap.
>since the user can create procedures with dbo as the owner, if the SP has a
>drop table command, that would execute in the owners context and hence woul
d
>drop the table. Is that right ? I guess the question is when an SP is
>executed does it use the permissions of the owner of the SP or the user
>executing the SP|||Thanks Uri and Sue for your responses.
"Sue Hoegemeier" wrote:

> 1. A user needs to be a member of db_owner or db_ddladmin
> roles (or sysadmin) to create a objects owned by dbo.
> Members of db_owner and db_ddladmin need to qualify the
> owner as dbo.object when they create the objects to be owned
> by dbo.
> 2. It depends first on ownership the ownership chain. If the
> ownership chains are intact, the secuirty is checked for
> permissions to execute the stored procedure only. If the
> ownership chain is broken, permissions are checked on each
> branch where the owner of the object is different. You can
> find more information in books online under ownership chains
> -Sue
>
> On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
> <Mani@.discussions.microsoft.com> wrote:
>
>

Controlling a Transaction by User in SQL Server 2000

Hey Folks!

I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.

Please let me know in case if there is a way out.

Thanks, in advance.

Regards,

Uday.D

Hi ,

Where u want to handle transactions ...

from vb.net / C# or in Store Procedure (in SQL Server 2000) itself ...i prefers in Store Procedure

following links may help you

http://www.codeproject.com/database/sqlservertransactions.asp

http://www.samspublishing.com/articles/article.asp?p=27225&rl=1

|||

Thanks Amit for the same.

But, the links you had given does not give me the solution.

I am looking at the functionality something similar to the "Cancel executing Query Method" button in Query Anlayser in Sql Server 2005. If we run a huge query and click it when the query is processing the SQL execution engine can be stopped by clicking the button.

I hope this throws some more light.

Control Result of ExecuteScalar

The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL.

For Example:

If this doesn't find a match:

select amount from Lookup Where application = @.app

Then I'd like to return:

select amount from Lookup Where application = "DEFAULT"

My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value? Or someone determine how many records are in the last SQL to execute?

Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query?

ExecuteScalar returns only one value -what that value is depends upon the stored procedure.

Yes, you can return any one value from any combination of queries.

To best assist you, please post the entire stored procedure, a description of what results are desired.

|||

I'm including my SP below. The Lookup table has a column named amount. If the CASE statement, when method="LIST", I'd like to return Lookup.amount if there are no matching records in the LookupList table.

USE [SharedDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PR_LookupPrice]

(

@.app varchar(8),

@.billcode varchar(4),

@.code varchar(20),

@.value sql_variant

)

AS

SET NOCOUNT OFF;

SELECT CASE

WHEN method = 'SET'

THEN (select amount from Lookup Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'VAR'

THEN (Select LookupVariable.amount from Lookup INNER JOIN LookupVariable

On Lookup.lookupid = LookupVariable.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and low <= @.value and high >= @.value)

WHEN method = 'COMP'

THEN (select SUM(LookupCompounding.amount) from Lookup INNER JOIN LookupCompounding

On Lookup.lookupid = LookupCompounding.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'LIST'

THEN (select LookupList.amount from Lookup INNER JOIN LookupList

On Lookup.lookupid = LookupList.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and lookuplist.value = @.value)

END AS 'result'

FROM Lookup WHERE application = @.app AND billcode = @.billcode AND code = @.code

|||

Thanks, that helps.

I've revised the procedure for readibiltiy.

Code Snippet

ALTER PROCEDURE [dbo].[PR_LookupPrice]
( @.App varchar(8),
@.BillCode varchar(4),
@.Code varchar(20),
@.Value sql_variant
)
AS

BEGIN

SET NOCOUNT OFF;

DECLARE @.ReturnValue decimal(10,2)

SELECT @.ReturnValue = CASE Method
WHEN 'SET'
THEN Amount
WHEN 'VAR'
THEN (SELECT lv.Amount
FROM Lookup l
INNER JOIN LookupVariable lv
ON l.LookupID = lv.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ( lv.Low <= @.Value
AND lv.High >= @.Value
)
)
)
WHEN 'COMP'
THEN (SELECT sum( lc.Amount )
FROM Lookup l
INNER JOIN LookupCompounding lc
ON l.LookupID = lc.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
)
)
WHEN 'LIST'
THEN (SELECT isnull( ll.Amount, l.Amount )
FROM Lookup l
INNER JOIN LookupList ll
ON l.LookupID = ll.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ll.Value = @.Value
)
)
END
FROM Lookup
WHERE ( Application = @.App
AND BillCode = @.BillCode
AND Code = @.Code
)

IF @.ReturnValue IS NULL
SELECT Amount
FROM Lookup
WHERE Application = "DEFAULT"

RETURN @.ReturnValue

END

The changes are in yellow, I think that everything else is just formatting.

Control over creation of procs & views owned by dbo

Hi,
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiThey would have to be a member of the db_ddladmin or db_owner fixed database
roles to create objects in the dbo schema which would give them too many
rights (they would also be able to create tables etc).It's not possible to
give them just a subset of the rights if you want them to create objects in
the dbo schema
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||Thanks Jasper.
"Jasper Smith" wrote:

> They would have to be a member of the db_ddladmin or db_owner fixed databa
se
> roles to create objects in the dbo schema which would give them too many
> rights (they would also be able to create tables etc).It's not possible to
> give them just a subset of the rights if you want them to create objects i
n
> the dbo schema
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Mani" <Mani@.discussions.microsoft.com> wrote in message
> news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
>
>|||To add on to Jasper's response, you could also change object ownership to
'dbo' with sp_changeobjectowner.
Regarding the second part of your question, stored procedures run in the
security context of the invoking user, not the object owner. Due to
ownership chains, permissions on indirectly referenced objects are not
checked as long as the objects involved have the same owner. Users only
need permissions on only directly referenced objects.
Note that ownership chains apply only to object permissions, not statement
permissions like CREATE. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani

Control Charting??

I have a need to provide a control-chart-like appearance to the user
community.
Basically I need to track costs over a period of months against a
previous year (or some historical range) and show where the current
costs are tracking above/below the corresponding historical values.
I was thinking about using a standard line chart and somehow
incorporate two horizontazl lines (one high/one low) within the chart
area (don't know if this is possible with SRS). So, far I'm still
trying to figure out the SQL Query...
Has anyone tried to do a control-chart (simulate) or... is there a
better approach?
Thanks
GlennIf you just want constant values for the high/low horizontal lines, you can
easily achieve it by just adding a new "value" in the chart properties
dialog / "Data" tab. You would use e.g. =100 as value expression for this
new series value.
If you have historical data from a query, you would add e.g. a series
grouping based on the date values, e.g. =Year(Fields!SomeDateField.Value).
Therefore your values will be automatically grouped per year and shown as
independent series in the chart.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Glenn Owens" <gowens@.nixonpeabody.com> wrote in message
news:bc4291aa.0408131137.7653fdb5@.posting.google.com...
> I have a need to provide a control-chart-like appearance to the user
> community.
> Basically I need to track costs over a period of months against a
> previous year (or some historical range) and show where the current
> costs are tracking above/below the corresponding historical values.
> I was thinking about using a standard line chart and somehow
> incorporate two horizontazl lines (one high/one low) within the chart
> area (don't know if this is possible with SRS). So, far I'm still
> trying to figure out the SQL Query...
> Has anyone tried to do a control-chart (simulate) or... is there a
> better approach?
> Thanks
> Glenn

Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.

Content Manager Security

I have a user I have given permission to folder and reports as a
Content Manager, but when the user clicks on the security tab for a
folder or report they get an error of:
The permissions granted to user 'DOMAIN\USER_ID' are insufficient for
performing this operation. (rsAccessDenied)
I am an administrator on the machine and fall into the
BUILTIN\Administrators who are Content Managers, and have no problems
whatsoever in viewing and setting security.
What can I do to get this user permission short of making them an
administrator on the machine? Any help would be greatly appreciated.jdcamp,
Did you try wiggling the power cord on the server? Another one that
works for me is to open and close the CD-ROM drive 5 or 6 times then
restart the machine.
Hope I could help!
Mickey|||Are you kidding me? Does anyone have a serious answer on this matter?

Containstable Filter Input

Hi
We are using the CONTAINSTABLE function in a query, the search condition of
the query is derived from a free text for the user to enter whatever they
please.
I am attempting to replace or filter out text that has been input to resolve
potential errors.
We are replacing double and single spaces with " AND "
We are replacing comma's and apostrophes with ""
But is there a more effective way of doing this?
Thanks
BSorry this has been answered in another of my posts regarding a slightly
different problem, answer:
B
Quote
There have been lots of posts in microsoft.public.sqlserver.fulltext with
solutions to this, many using regular expressions to quickly create clauses.
I myself use a lump of code I wrote about 10 years ago which deals with this
and parentheses and quoted phrases, but it's messy and I'd rather clean it
up before posting.
A search on google groups for fulltext parsing should pull up some useful
info, such as
http://groups.google.co.uk/group/mi...fulltext&hl=en
Dan
Dan
"Ben" <Ben@.NoSpam.com> wrote in message
news:eL3IrikUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi
> We are using the CONTAINSTABLE function in a query, the search condition
> of the query is derived from a free text for the user to enter whatever
> they please.
> I am attempting to replace or filter out text that has been input to
> resolve potential errors.
> We are replacing double and single spaces with " AND "
> We are replacing comma's and apostrophes with ""
> But is there a more effective way of doing this?
> Thanks
> B
>

Constraints

Hi,
How do I create a Constraints for a table column.
I want to limit the user to enter a W or an S how can I do that?
Thanks
Dib
You want a CHECK constraint. You can create a check constraint using the
ALTER TABLE statement, or from Enterprise Manager. See the topic Creating
and Modifying CHECK Constraints in Books Online for details.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:e1bbC8fOFHA.3376@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I create a Constraints for a table column.
> I want to limit the user to enter a W or an S how can I do that?
> Thanks
> Dib
>

Constraints

Hi,
How do I create a Constraints for a table column.
I want to limit the user to enter a W or an S how can I do that?
Thanks
DibYou want a CHECK constraint. You can create a check constraint using the
ALTER TABLE statement, or from Enterprise Manager. See the topic Creating
and Modifying CHECK Constraints in Books Online for details.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:e1bbC8fOFHA.3376@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I create a Constraints for a table column.
> I want to limit the user to enter a W or an S how can I do that?
> Thanks
> Dib
>

Constraints

Hi,
How do I create a Constraints for a table column.
I want to limit the user to enter a W or an S how can I do that?
Thanks
DibYou want a CHECK constraint. You can create a check constraint using the
ALTER TABLE statement, or from Enterprise Manager. See the topic Creating
and Modifying CHECK Constraints in Books Online for details.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:e1bbC8fOFHA.3376@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I create a Constraints for a table column.
> I want to limit the user to enter a W or an S how can I do that?
> Thanks
> Dib
>

Constraint expression not working

Hi!
I have a table with users in which I would like to have a constraint making
sure that for active users (IsActive=1) a valid user name always exists
(OSUserName <> '' AND OSUserName IS NOT NULL).
I enter the following constraint expression in the table designer:
(([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
But when I look at what gets saved, the paranthesis separating the two AND
clauses are gone:
([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
[OSUserName] is null)
And this also make my constraint fail and allowing IsActive = 0 and
OSUserName = 'XYZ'. How can I write the expression to get the desired
effect?
Brgds
JonasYou can write the same expression in the following way:
SIGN(LEN(ISNULL([OSUserName], ''))) = [IsActive]
But I don't understand your logic. Do you really mean that if you change a
user from being active to non-active that you have set the OSUserName to
NULL as well? That is what you constraint enforces now. If you don't need
that, you can simplify your check to LEN(ISNULL([OSUserName], '')) > 0 OR
[IsActive] = 0. I.e. you either have to provide a username or the user must
be inactive.
Jacco Schalkwijk
SQL Server MVP
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
> making sure that for active users (IsActive=1) a valid user name always
> exists (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Instead of using query designer, do your work from Query Analyzer...
ALTER TABLE YourTable
ADD CONSTRAINT CK_IsActive_UserName CHECK
((([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
making
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Jonas,
Create the constraint from QA.
Example:
use northwind
go
create table t (
IsActive smallint not null check (IsActive = 0 or IsActive = 1) default (0),
OSUserName varchar(50) null,
)
go
alter table t
add constraint chk_OSUserName check (([IsActive] = 1 and
isnull([OSUserName], '') > '') or ([IsActive] = 0))
go
insert into t default values
insert into t values(1, 'MSSQLSERVER')
go
insert into t values(1, null)
go
update t
set IsActive = 1
where OSUserName is null
go
select * from t
go
drop table t
go
AMB
"Jonas" wrote:

> Hi!
> I have a table with users in which I would like to have a constraint makin
g
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||No you are not crazy: SQL Server does re-write the check constraint
source.
If a check is defined as "Column in ('a', 'b' )" it is re-written to
"[Column] = 'a' or [Column] = 'b'"
For complex nested conditions, the re-write includes awareness of the
precedence of NOT, AND and OR as defined in Books On Line.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Constraint between sysusers table and a user defined table

I want to create a user defined table with Foreign Key constrained to uid of
sysusers table. How can this constraint be enforced?
I am using Server Authentication on SQL Server 2000 v8.
Thank you for your assistance.I think you will need to use a trigger or self-coded "preventative" measures
because you cannot set up a constraint against a system table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>I want to create a user defined table with Foreign Key constrained to uid
>of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||wolf
Why?Please explain what is your purpose?
"wolfv" <wolfv@.discussions.microsoft.com> wrote in message
news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||Hi
FK's can not span databases.
You need to enforce this through a trigger.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"wolfv" wrote:

> I want to create a user defined table with Foreign Key constrained to uid
of
> sysusers table. How can this constraint be enforced?
> I am using Server Authentication on SQL Server 2000 v8.
> Thank you for your assistance.
>|||The purpose of the FK constraint (which is not allowed) is to prevent orphan
s
in the userdefined table when someone attempts to delete a user from the
sysusers table.
"Uri Dimant" wrote:

> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||
"Uri Dimant" wrote:

> wolf
> Why?Please explain what is your purpose?
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
> of
>
>|||The sysusers table and the user-defined table reside in the same database.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> FK's can not span databases.
> You need to enforce this through a trigger.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "wolfv" wrote:
>|||Yes, I see what you mean. I found a place in SQL Server Manual that says
constraints against a system table are not permitted.
Thank you.
"AB - MVP" wrote:

> I think you will need to use a trigger or self-coded "preventative" measur
es
> because you cannot set up a constraint against a system table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "wolfv" <wolfv@.discussions.microsoft.com> wrote in message
> news:54518189-345A-47BF-B990-9ADAA03036C0@.microsoft.com...
>
>

Consolidation problem

I have 2 tables.
In the User Table I have a UserID and the CompanyID of the Company he
belongs to.
In the second table (EmailBlocked), are the users or company a particular
user has blocked. If a user blocks a company, then all users in that
company are blocked. If the company is not blocked, then only the
particular user in the record is blocked.
I can do this in 2 selects, but I am trying to get it to work in one.
DROP TABLE UserTable
go
CREATE TABLE UserTable
(
UserID int,
CompanyID varChar(15)
)
go
DROP TABLE EmailBlocked
go
CREATE TABLE EmailBlocked
( UserID int,
BlockedUserID int,
CompanyID int,
CompanyBlocked int
)
go
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(309,150,5,1)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(168,130,Null,0)
INSERT EmailBlocked (UserID,BlockedUserID,CompanyID,CompanyB
locked) VALUES
(171,120,250,0)
INSERT UserTable (UserID,CompanyID) VALUES (152,4)
INSERT UserTable (UserID,CompanyID) VALUES (150,5)
go
This Select is close, but not quite right.
There are 2 tests;
1) Check if the 2 users match (UserID and BlockedUserID are in a record
in the table).
2) Check to see if the User (UserID) has the company blocked that the
2nd user (BlockedUserID) belongs to.
Pass back the BlockedUserID and null for the companyID #1 and Pass back Null
for the BlockedUserID and the CompanyID for #2. If neither (nothing is
blocked for these users), I should get no record back.
In the following, I am getting no record back. Test #1, there is no match.
Test #2, 309 has blocked Company 5, and 150 is part of company 5 so he
should have been blocked and I would like to get the record back as
"NULL,5". This tells me that the company is what is being blocked.
Declare @.UserID bigint,@.BlockedUserID bigInt
Select @.UserID = 309,@.BlockedUserID = 152
Select BlockedUserID,CompanyID
from EmailBlocked
where (UserID = @.UserID and
BlockedUserID = @.BlockedUserID) or
(CompanyID in (select CompanyID
from UserTable
where UserID = @.BlockedUserID) and CompanyBlocked = 1)
Thanks,
TomHi, Tom
I think this query gives the expected results:
select
case when CompanyID is null
then BlockedUserID
end as BlockedUserID,
CompanyID
from (
select (
Select BlockedUserID
from EmailBlocked
where UserID = @.UserID
and BlockedUserID = @.BlockedUserID
) as BlockedUserID,
(
select CompanyID
from EmailBlocked
where UserID = @.UserID and CompanyBlocked = 1
and CompanyID in (
select CompanyID
from UserTable
where UserID = @.BlockedUserID
)
) as CompanyID
) x where BlockedUserID is not null or CompanyID is not null
However:
1. It's pretty complicated; I would rather use two queries.
2. The DDL is somewhat strange:
a) the data type of CompanyID should be the same in both tables
b) you have no primary keys, foreign keys, check constraints, etc. You
should have (at least) primary keys for each table, foreign keys where
appropriate, and "not null" columns where a value is required. Ideally,
you should have check constraints (and/or other verifications) so you
cannot insert any inconsistent data in the database. For example, you
should have a check constraint that says: "CompanyBlocked=0 OR
CompanyID is not null", so you cannot block an unspecified company.
Also, a primary key in the UserTable, would make it clear if a user
belongs to only one company or if it is allowed that a user belong to
more than one company.
c) to block a company is it really necessary to specify a blocked user
(within that company) ? If no, a different DDL would be more useful. If
yes, a constraint should be added so to make sure that the blocked user
is really in the blocked company. This may be implemented using a
foreign key on two columns referencing the UserTable.
d) when you block only a user (i.e. CompanyBlocked=0), what is the
purpose of specifying the company where he works ? If there is no
purpose, a different DDL would be more useful, again.
Razvan|||Hi Razvan,
Razvan,
This seems to do exactly what I needed.
I thought I would need to use a derived table somehow, but couldn't figure
out how to make it work.
I am trying to figure out the thought process that leads to building the
first table and then building the table around that.
below:
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135236075.969255.167240@.o13g2000cwo.googlegroups.com...
> Hi, Tom
> I think this query gives the expected results:
> select
> case when CompanyID is null
> then BlockedUserID
> end as BlockedUserID,
> CompanyID
> from (
> select (
> Select BlockedUserID
> from EmailBlocked
> where UserID = @.UserID
> and BlockedUserID = @.BlockedUserID
> ) as BlockedUserID,
> (
> select CompanyID
> from EmailBlocked
> where UserID = @.UserID and CompanyBlocked = 1
> and CompanyID in (
> select CompanyID
> from UserTable
> where UserID = @.BlockedUserID
> )
> ) as CompanyID
> ) x where BlockedUserID is not null or CompanyID is not null
> However:
> 1. It's pretty complicated; I would rather use two queries.
Why would you use 2 queries if one query works, as yours seems to?

> 2. The DDL is somewhat strange:
> a) the data type of CompanyID should be the same in both tables
You're right. This was a mistake. The companyID is supposed to be an Int.
In my actual table, it is created as an Identity.

> b) you have no primary keys, foreign keys, check constraints, etc. You
> should have (at least) primary keys for each table, foreign keys where
> appropriate, and "not null" columns where a value is required.
I agree. But this is just a quick DDL to allow you (and others) to see what
I am trying to do. It isn't exactly the same as my normal tables (which do
have primary keys, foreign keys and constraints). My UserTable does use
UserID as the Primary key. The EmailBlocked Table does have a Primary key
(EmailBlockedID Int Identity). My Foreign key would be UserID referencing
UserID in the UserTable.

>Ideally,
> you should have check constraints (and/or other verifications) so you
> cannot insert any inconsistent data in the database. For example, you
> should have a check constraint that says: "CompanyBlocked=0 OR
> CompanyID is not null", so you cannot block an unspecified company.
True, but I am enforcing this in my Code.

> Also, a primary key in the UserTable, would make it clear if a user
> belongs to only one company or if it is allowed that a user belong to
> more than one company.
And it is. It is an identity as I mentioned earlier. But I may change this
to some random number so it cannot be guessed.

> c) to block a company is it really necessary to specify a blocked user
> (within that company) ?
No. Actually, the UserID is irrelavant if CompanyBlocked = 1. Actually, in
my code, if I have 3 users defined from the same company (none have
CompanyBlocked = 1) and I later Block the company - I delete 2 of the
records and set the CompanyBlocked=1 on the record I choose to keep. This
way there is only one record with the CompanyBlocked set for any User.

> If no, a different DDL would be more useful. If
> yes, a constraint should be added so to make sure that the blocked user
> is really in the blocked company. This may be implemented using a
> foreign key on two columns referencing the UserTable.
> d) when you block only a user (i.e. CompanyBlocked=0), what is the
> purpose of specifying the company where he works ? If there is no
> purpose, a different DDL would be more useful, again.
It isn't. And the CompanyID is irrelavant if the CompanyID = 0.
As a matter a fact, if it were necessary to check the CompanyBlocked field
first to see if a company blocked before doing the UserID/BlockedID test, I
assume this would need to be done using 2 separate queries.
Thanks,
Tom
> Razvan
>