Configuration Options not saving via Enterprise Manager

Hello,
I'm trying to change some Config options via Enterprise Manager, ie
Memory etc..
I can make the change with no problem but when I go back into the
Configuration options the change hasn't taken effect? It looks like the
sysadmin guys have locked down the server but any ideas where to look? If I
do an Sp_Configure <> Then it works fine?
Thanks
Julian
How do you have SQL Server registered in Enterprise Manager? Right clisk on
the server name and choose Edit SQL Server registration properties. Is the
same account that is running sp_configure from Query Analyzer? If the
account you are using is a sysadmin then you should have no problem from
Enterprise Manager.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Both running as SA
"Rand Boyd [MSFT]" wrote:

> How do you have SQL Server registered in Enterprise Manager? Right clisk on
> the server name and choose Edit SQL Server registration properties. Is the
> same account that is running sp_configure from Query Analyzer? If the
> account you are using is a sysadmin then you should have no problem from
> Enterprise Manager.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
sqlsql

Configuration Options not saving via Enterprise Manager

Hello,
I'm trying to change some Config options via Enterprise Manager, ie
Memory etc..
I can make the change with no problem but when I go back into the
Configuration options the change hasn't taken effect' It looks like the
sysadmin guys have locked down the server but any ideas where to look' If I
do an Sp_Configure <> Then it works fine?
Thanks
JulianHow do you have SQL Server registered in Enterprise Manager? Right clisk on
the server name and choose Edit SQL Server registration properties. Is the
same account that is running sp_configure from Query Analyzer? If the
account you are using is a sysadmin then you should have no problem from
Enterprise Manager.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Both running as SA
"Rand Boyd [MSFT]" wrote:

> How do you have SQL Server registered in Enterprise Manager? Right clisk o
n
> the server name and choose Edit SQL Server registration properties. Is the
> same account that is running sp_configure from Query Analyzer? If the
> account you are using is a sysadmin then you should have no problem from
> Enterprise Manager.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Configuration Options not saving via Enterprise Manager

Hello,
I'm trying to change some Config options via Enterprise Manager, ie
Memory etc..
I can make the change with no problem but when I go back into the
Configuration options the change hasn't taken effect' It looks like the
sysadmin guys have locked down the server but any ideas where to look' If I
do an Sp_Configure <> Then it works fine?
Thanks
JulianHow do you have SQL Server registered in Enterprise Manager? Right clisk on
the server name and choose Edit SQL Server registration properties. Is the
same account that is running sp_configure from Query Analyzer? If the
account you are using is a sysadmin then you should have no problem from
Enterprise Manager.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Both running as SA
"Rand Boyd [MSFT]" wrote:
> How do you have SQL Server registered in Enterprise Manager? Right clisk on
> the server name and choose Edit SQL Server registration properties. Is the
> same account that is running sp_configure from Query Analyzer? If the
> account you are using is a sysadmin then you should have no problem from
> Enterprise Manager.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Conditional query criteria in Access 2003 with SQL Server BE

Hello:
I just upsized an Access 2003 application to a SQL Server 2000 back end.
There is a query that worked fine before the upsize, but does not work now.
The purpose of the query is to give me a list of employees that are
scheduled to work today. The employee master row has 7 checkboxes for the
days of the week.
Here is the SQL for the query. Can someone tell me how to make this work
with the SQL Server BE?
SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
FROM EmployeeQry
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
(((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
(((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
(((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
(((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
(((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
Thanks
Steve
John:
Thanks for the reply. Sorry ... the query returns no rows, when in fact
there are many for each day of the week. I tried your suggestions ...
comments under each suggestion.
Steve
"John Spencer" wrote:

> I would try:
> Adding to the IIF statement.
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True,False)))
>
I tried this with no difference

> Or even simpler, drop the IIF statement
> EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')
>
This returned all rows of the table.

> IF that still fails then try reversing the logic of the test. Just in
> case SQL server is storing the data as 1 (True) and 0 (False).
> EmployeeQry.Tuesday =IIf(Format(Date(),"dddd")<>'Tuesday',False, True)
>
Didn't work either.

> By the way "..., but does not work now." is not very descriptive of the
> problem. Does that mean, you get the wrong results, no results, an
> error, different results than expected at times, a syntax error or ...?
> Please try to be a bit more specific in describing the problem.
> '================================================= ===
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '================================================= ===
>
> Steve Happ wrote:
>
|||What type is EmployeeQry.Monday on SQL Server?
It could be as John alluded to that
for SQL Server
True = 1
False = 0
for Access
True = -1
False = 0
I might misunderstand, but you try "1"
instead of "True"
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
"Steve Happ" wrote:
> I just upsized an Access 2003 application to a SQL Server 2000 back end.
> There is a query that worked fine before the upsize, but does not work
> now.
> The purpose of the query is to give me a list of employees that are
> scheduled to work today. The employee master row has 7 checkboxes for the
> days of the week.
> Here is the SQL for the query. Can someone tell me how to make this work
> with the SQL Server BE?
> SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
> EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
> EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
> EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
> FROM EmployeeQry
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
> (((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
> (((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
> (((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
> (((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
> (((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
>
> Thanks
> Steve
|||or
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,Null))) OR
the point being that if Monday = "True"
on SQL, then its value is 1
which will never be equal to Access "True"
which is equal to -1
"Gary Walter" wrote:
> What type is EmployeeQry.Monday on SQL Server?
> It could be as John alluded to that
> for SQL Server
> True = 1
> False = 0
> for Access
> True = -1
> False = 0
> I might misunderstand, but you try "1"
> instead of "True"
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
>
> "Steve Happ" wrote:
>

Conditional Order by?

Is there a way to do a conditional order by so that a user can give a parameter to a stored proc and it give back results sorted the way they want?

I want it so that the user can do 1 of 4 things,
* sort by "title" ascending,
* sort by "title" descending,
* sort by "synopsis" ascending,
* sort by "synopsis" descending

Can it be done? This is what I have but I get a syntax error:

select * from Blah

Order By
Case
when @.orderId = 1 then title asc
when @.orderId = 2 then title desc
when @.orderId = 3 then synopsis asc
when @.orderId = 4 then synopsis desc
end

Any help is greatly appreciated!

You need to split the query as ASC Query & Desc Query.

You can choose your order by columns (if it is single column) dynamically but Sorting Order you can't.

Use your query as follow as

if @.OrderId in (1,3)

select * from Blah
Order By
Case
when @.orderId = 1 then title
when @.orderId = 3 then synopsis
end asc

else

select * from Blah
Order By
Case
when @.orderId = 2 then title
when @.orderId = 4 then synopsis
end Desc

Or

You can use dynamic SQL

Declare @.SQL as NVarchar(1000)

Select @.SQL = N'select * from Blah

Order By ' +
Case
when @.orderId = 1 then 'title asc'
when @.orderId = 2 then 'title desc'
when @.orderId = 3 then 'synopsis asc'
when @.orderId = 4 then 'synopsis desc'
end

Exec (@.SQL)

|||Why don't you dynamically add the final order by clause to the select query string ?
I guess it is more clear to understand and much more flexible for any future changes.|||Thanks both for the replies, I didnt know it would be so detailed, I have a rather large select query that I am applying this to and I dont really want to create a string then execute it, it just looks messy to me. But I guess if I have no option I guess ill have to.

Thanks again|||In some cases I have done this:

SELECT ....,
SortOrder = Case when @.orderId = 1 then title
when @.orderId = 2 then REVERSE(TITLE)
when @.orderId = 3 then synopsis
when @.orderId = 4 then REVERSE(synopsis) END
ORDER BY SortOrder

The only issue with this is all the vars in the CASE must be the same type, or cast to a certain type.

|||

Easiest is to do below:

order by

case @.orderId when 1 then title end

, case @.orderId when 2 then title end desc

, case @.orderId when 3 then synopsis end

, case @.orderId when 4 then synopsis end desc

Note that you will get the best performance (assuming you have indexes on the column(s) and the plan can use it) if you use dynamic SQL to form the ORDER BY with required columns or use different SELECT statements. But in most cases, I have found that the above construct is easier to use, safe from SQL injection (dynamic SQL is prone to it if you are not careful) and with few conversions which single CASE expression requires.

|||Thanks all for the help with this topic, I think Umachandar's answer will fit my solution best.

conditional color formatting

i'm using SQL Server Reporting Services 2005 and have a simple report that
essentially pulls back a bunch of database records and displays the results
in the excel-like grid. I want to shade an entire row, based on the value
of a field contained in that row, but i can't figure out where (and how) to
do this.
any help is much appreciated..
tia
jtOn Apr 3, 6:26 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> i'm using SQL Server Reporting Services 2005 and have a simple report that
> essentially pulls back a bunch of database records and displays the results
> in the excel-like grid. I want to shade an entire row, based on the value
> of a field contained in that row, but i can't figure out where (and how) to
> do this.
> any help is much appreciated..
> tia
> jt
In the Layout view, select the left-most cell in the table and click
F4 (for the Properties window). Below the Appearance property, in the
Properties window, select '<Expression...>' from the drop-down list to
the right of 'BackgroundColor.' Enter something like the following
below 'Edit Expression:'
=iif(Fields!FieldName.Value <> SomeValue, "BackgroundColorIfTrue",
"BackgroundColorIfFalse")
Then select the cell directly to the right of the left-most one and
repeat the same steps. Continue in this manner until all cells all the
way across have the same expression used.
Regards,
Enrique Martinez
Sr. Software Consultant

concurrent backups

If I have say 15 databases on one server and in order to
back them up when there is little activity, but to get
them backed up before the tape backups begin, will there
be a problem if some of them are begun at the same time,
say i start 5 of them at 10PM and 5 at 11PM, etc?
Please and Thank YouIf you backup to different tapes or different disk files, the concurrent
backups would be fine.
You cannot concurrently backup to the same tape device.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Hi,
No problems, you can execute the backups for different databases in parellel.
Thanks
hari
MCDBA
"Rachel" wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
>|||No, most backup software leaves open files alone and if a file is being
backed up to then its open. Even if the software does backup open files
then it will not cause a problem.
Adrian
Rachel wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Technically this is not a problem if you are backing them all up to disk and
not directly to tape. But depending on the size of the db's, the disk
array's they are on and the disk array's they are being backed up to, the
number of processors, other jobs etc you may not have optimal results. You
may find they will run faster overall if you schedule them one after
another. You can also affect the users and other activities happening if
you attempt to backup too much at one time. Hardware plays a big factor in
this though.
Andrew J. Kelly SQL MVP
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You

Concatinating child field

I'm developing an ASP.NET app with SQL back end. I'm trying to develop a use
r
interface that must reference information from two related tables and would
like to return the following (either in table, but preferably in datagrid):
ParentField1 ParentField2 Parent Field3 ChildField1Record1
ChildField1Record2
ChildField1Record3
ParentField1 ParentField2 ParentField3 ChildFeld1Record4
ChildFeld1Record5
ChildField1Record6 etc.
Is there anyway to write a TSQL function to concatinate the child records
into a dynamic field (there are about 400 parent records)
I could create a denormalized table that mimics the child table by has the
ParentID and ChildRecords concatinated that gets updated everytime the child
table changes, but that's just bad programming!
Any suggestions would be greatly appreciated.
Thanks in advance.well the question is not clear to me. but i guess u are trying to concatinat
e
the fields in the query
ucan do it as
select <field1> + <field2> + ..
FROM <Table>
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"AbeR" wrote:

> I'm developing an ASP.NET app with SQL back end. I'm trying to develop a u
ser
> interface that must reference information from two related tables and woul
d
> like to return the following (either in table, but preferably in datagrid)
:
> ParentField1 ParentField2 Parent Field3 ChildField1Record1
> ChildField1Record2
> ChildField1Record3
> ParentField1 ParentField2 ParentField3 ChildFeld1Record4
> ChildFeld1Record5
> ChildField1Record6 etc.
> Is there anyway to write a TSQL function to concatinate the child records
> into a dynamic field (there are about 400 parent records)
> I could create a denormalized table that mimics the child table by has the
> ParentID and ChildRecords concatinated that gets updated everytime the chi
ld
> table changes, but that's just bad programming!
> Any suggestions would be greatly appreciated.
> Thanks in advance.|||Hi Chandra,
Thanks for the response, and sorry for not being clear. Actually I am trying
to return contents of all records with the ParentID from the child table wit
h
the parent record from the parent table, so actually want to select
record1field1 + record2field1 + record3field1 from chldtable joining
parenttable without repeating the parent data for each record.
Regards,
AbeR
"Chandra" wrote:
> well the question is not clear to me. but i guess u are trying to concatin
ate
> the fields in the query
> ucan do it as
> select <field1> + <field2> + ..
> FROM <Table>
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "AbeR" wrote:
>|||>> Is there anyway to write a TSQL function to concatinate the child records
The terminology you used is a bit confusing.
Assuming that there is a one-to-many relationship that exist between the
tables, what you seem to be asking is to return a resultset with the single
value from the referenced table and all the values from the referencing
table as a contenated string. In general, such processing which demand heavy
looping and formatting of values should be done on the client side -- quite
often one should be able to leverage the functionality of a programming
language or a report-writer in such cases.
Doing this on the server often requires procedural logic, often disguised as
a user defined function or perhaps cursors. For some such workarounds, refer
to: http://tinyurl.com/aka4u
Anith|||Exactly. I was hoping not to create another dataset on the client system, bu
t
that's probably the best approach (While loops in C# within the app)...
DataSet1 will be read by the client and produce dataset two with all the
values from the referenced table as one string and then present in a datagri
d.
Many thanks for the advice and reference.
- AbeR
"Anith Sen" wrote:

> The terminology you used is a bit confusing.
> Assuming that there is a one-to-many relationship that exist between the
> tables, what you seem to be asking is to return a resultset with the singl
e
> value from the referenced table and all the values from the referencing
> table as a contenated string. In general, such processing which demand hea
vy
> looping and formatting of values should be done on the client side -- quit
e
> often one should be able to leverage the functionality of a programming
> language or a report-writer in such cases.
> Doing this on the server often requires procedural logic, often disguised
as
> a user defined function or perhaps cursors. For some such workarounds, ref
er
> to: http://tinyurl.com/aka4u
> --
> Anith
>
>