Advice on SQL statement please.

I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.

advice about a worm intrusion alert

XP Home, limited user account. Newbie to this group - I know next to
nothing about ports but am an experienced computer user otherwise.
Can anyone interpret this for me - just started to get these recently -
this is only the second one. Got it while using a user account in my XP
Home machine.
Security Alert - Medium Rick
Norton Internet Worm Protection has detected and blocked an intrusion
attempt.
The text in More Info was as follows:
Intrusion: MS SQL PacketResolution DoS
Intruder: 192.168.1.1 (domain(53))
Risk Level: Medium.
Protocol: UDP
Attacked IP: COMPUTER NAME (192.168.1.2)
Attacked Port: ms-sql-m(1434)
The Intruder address was my router, to which one Win98SE computer is
connected by ethernet (not mentioned in report) and the other on the
192.168.1.2 address is my XP Home machine, wirelessly connected to the
router.
I clicked OK and then the wireless connection lost its IP and
connectivity - and I had no internet access on the wireless XP machine.
Router was still connected to internet fine - all lights glowing properly.
Computer upstairs 192.168.1.3 was on and could connect to the internet -
no one was using it at the time of the alert. It has Zone Alarm free
version to prevent any outgoing stuff, and also NAV and Spybot S&D
resident (teatimer). It is on Win98SE. No alerts showing.
This machine runs Windows XP Home (user account) has NAV, Counterspy and
Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
Log off and on did not restore the wireless (always does usually).
Log off and then on to Admin acct - again wireless network did not work
but I got a windows error - windows is recovering from a serious error.
Still no connection.
Did a warm reboot and then everything was back to normal.
I do a Norton AV and Counterspy scan daily. Clear.
I think all the Windows/wireless hassle was due to the Norton blocking
the request, and I think the "intrusion" was legitimate - but I don't
want to "allow" it unless someone can explain the details to me. Many
thanks to any network gurus who can interpret please.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420Port 1434 is the SQL Browser service used for locating SQL Servers.
I would NOT allow Ports 1434 or 1433 to be open to the outside.
Is this a NAT router directly connected to your DSL/Cable modem?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert M Jones" <robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote in message
news:uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl...
> XP Home, limited user account. Newbie to this group - I know next to
> nothing about ports but am an experienced computer user otherwise.
> Can anyone interpret this for me - just started to get these recently -
> this is only the second one. Got it while using a user account in my XP
> Home machine.
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
> The Intruder address was my router, to which one Win98SE computer is
> connected by ethernet (not mentioned in report) and the other on the
> 192.168.1.2 address is my XP Home machine, wirelessly connected to the
> router.
> I clicked OK and then the wireless connection lost its IP and
> connectivity - and I had no internet access on the wireless XP machine.
> Router was still connected to internet fine - all lights glowing properly.
> Computer upstairs 192.168.1.3 was on and could connect to the internet -
> no one was using it at the time of the alert. It has Zone Alarm free
> version to prevent any outgoing stuff, and also NAV and Spybot S&D
> resident (teatimer). It is on Win98SE. No alerts showing.
> This machine runs Windows XP Home (user account) has NAV, Counterspy and
> Zone Alarm free. Wireless network is WPA-PSK with 63 character pw.
> Log off and on did not restore the wireless (always does usually).
> Log off and then on to Admin acct - again wireless network did not work
> but I got a windows error - windows is recovering from a serious error.
> Still no connection.
> Did a warm reboot and then everything was back to normal.
> I do a Norton AV and Counterspy scan daily. Clear.
> I think all the Windows/wireless hassle was due to the Norton blocking the
> request, and I think the "intrusion" was legitimate - but I don't want to
> "allow" it unless someone can explain the details to me. Many thanks to
> any network gurus who can interpret please.
> --
> Rev Robert M Jones, Wimborne Baptist Church, UK
> http://www.wimborne-baptist.org.uk
> Free trial of Mailwasher Pro - effective email spam filter - (commission
> goes to our partners in Bulgaria)
> http://fta.firetrust.com/index.cgi?id=420|||Arnie Rowland wrote:
> Port 1434 is the SQL Browser service used for locating SQL Servers.
> I would NOT allow Ports 1434 or 1433 to be open to the outside.
> Is this a NAT router directly connected to your DSL/Cable modem?
>
Thanks for the reply. This is all a mystery to me.
Set up is an ADSL Router with NATS firewall incorporated. I have Skype
if that is relevant - the entry for that against its icon in Zone Alarm
is "Listening to Port(s) TCP:80,443,14695"
The router is set with IP Filtering enabled, for filtering inbound
traffic - there are no entries in the table in that section.
The section on Virtual Server Configuration DMZ host has:
"Those IP packets from the Internet that do NOT belong to any
applications configured in the port forwarding table will be: Discarded"
There is nothing set up in the port forwarding section
Any more checking I should do? The router NATS seems to do its job in
terms of the Shields Up tests, but I haven't then disabled the NATS to
test the actual ZA software firewall on the machine itself.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420|||>>> On 11/24/2006 at 11:03 AM, in message
<uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Security Alert - Medium Rick
> Norton Internet Worm Protection has detected and blocked an
> intrusion
> attempt.
> The text in More Info was as follows:
> Intrusion: MS SQL PacketResolution DoS
> Intruder: 192.168.1.1 (domain(53))
> Risk Level: Medium.
> Protocol: UDP
> Attacked IP: COMPUTER NAME (192.168.1.2)
> Attacked Port: ms-sql-m(1434)
Do you even have SQL installed on your machine? My guess is that you
don't.
As a result, port 1434 is not used by any specific program, but is
available for any program that needs a new UDP port to use.
Because of this, the DNS resolver is using it to make a DNS request
(your name server is probably set as 192.168.1.1). Your DNS server
responds to port 1434. However, Norton incorrectly classifies this as
an attack. It probably isn't.|||Joel Maslak wrote:
> <uTWSOL$DHHA.4620@.TK2MSFTNGP04.phx.gbl>, Robert M
> Jones<robert53newsgroups-ms2@.NOSPAMyahoo.co.uk> wrote:
> Do you even have SQL installed on your machine? My guess is that you
> don't.
> As a result, port 1434 is not used by any specific program, but is
> available for any program that needs a new UDP port to use.
> Because of this, the DNS resolver is using it to make a DNS request
> (your name server is probably set as 192.168.1.1). Your DNS server
> responds to port 1434. However, Norton incorrectly classifies this as
> an attack. It probably isn't.
That's sort of what I thought - but until I can be sure I did not want
to give Norton any instructions to allow or remember - just saying "ok"
when I get the block message.
Any advice on checking I can do (other than routine AV and spyware
scans) most welcome.
Rev Robert M Jones, Wimborne Baptist Church, UK
http://www.wimborne-baptist.org.uk
Free trial of Mailwasher Pro - effective email spam filter - (commission
goes to our partners in Bulgaria)
http://fta.firetrust.com/index.cgi?id=420sql

Advanced SQL - Group By With Cube

Need some direction on getting these example results:

I need to group by 2-3 different attributes, one of them a calculated value off of a birthdate attribute, but I want to know whether they are over or under a certain age. I don't want to restrict the result set by age, but rather subgroup results by age ranges.

Simplified Ex:
SELECT class, time, count(nameID) as Total
from students
group by class, time
with cube

Ex RS:
class time >15 Total
-- -- -- --
Math 9:30 true 17
Math 9:30 false 8
PE 7:45 true 5
PE 7:45 false 10

Hopefully you get the idea. I can not figure out how to get the evaluated boolean of whether or not thier age is >15.

It is important that result set be a cube to calculate all the total and subgroup totals. I don't even know if this is possible in my case though.

All help greatly appreciated.

SlezakFirst, assuming you are using SQL Server 2000, you need to create a UDF which will calculate the age. You can find one here: http://www.databasejournal.com/img/UDF_GetAge.sql

Next, you can make use of this function in your query:

SELECT class, time, CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END AS '>15', count(nameID) as Total
from students
group by class, time, dbo.GetAge(DateOfBirth,GETDATE())
with cube

Terri|||That was pretty much what I wanted. Thank you greatly!

I did want to group by 'CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END' and not by 'dbo.GetAge(DateOfBirth,GETDATE())', but I was able to figure that out. :p

Much obliged, and I appreciate that GetAge function too.

Slezak

advanced editor

I am used to DTS packages in sql server 2000 and now I want to use Integrated
services to create 'DTS' packages. Going to a .net users group meeting, I was
told to use the 'Advanced editor' since it would help me alot. However, I
can not find
the 'advanced editor' in sql server 2005. Thus can you tell me where the
'advanced editor'is and what are some good uses of the advanced editor?
Thanks!
Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I am used to DTS packages in sql server 2000 and now I want to use
> Integrated services to create 'DTS' packages. Going to a .net users
> group meeting, I was told to use the 'Advanced editor' since it would
> help me alot. However, I can not find the 'advanced editor' in sql
> server 2005. Thus can you tell me where the 'advanced editor'is and what
> are some good uses of the advanced editor?
Are you looking in SQL Server Mangement Studio? You find precious little
support for Integration Services there. You should have a look at
Business Intelligence Development Studio (BIDS), which really is Visual
Studio under a custom name.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did find it was in Business Intelligence part of Visual studio.net. But
once you are in BIS, where do you find the advanced editor?
Thanks!
Diane Stein
"Erland Sommarskog" wrote:

> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Are you looking in SQL Server Mangement Studio? You find precious little
> support for Integration Services there. You should have a look at
> Business Intelligence Development Studio (BIDS), which really is Visual
> Studio under a custom name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I did find it was in Business Intelligence part of Visual studio.net. But
> once you are in BIS, where do you find the advanced editor?
Now you asking me questions that goes over my head. :-) I don't use SSIS
much, or rather not at all. I just wanted to save you from looking in
SSMS. But isn't this "Advanced Editor" simply the Package Designer you
get when you create an Integration Services project?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The "Advanced Editor' most likely refers to a property dialog for SSIS
components that allows you to set any property available on that component
and configure a lot of settings beyond what's typically available on the
regular Properties dialog for the same component.
In general, to use new SSIS designer, you start BIDS application, and create
a new SSIS project (click New -> Project, and pick SSIS project from the
list). This will create a new SSIS package within a project and open it up.
From there you will see the package's design surface, where you can drop
control flow components from a toolbox window, connect them into a flow and
configure. If you right click on any component, you will see an 'Advanced
Editor' option. If you have more questions, there is a separate SSIS forum
where you will find more experts ready to answer those.
Regards,
Maciek Sarnowicz
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99E4C3B06E16BYazorman@.127.0.0.1...
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Now you asking me questions that goes over my head. :-) I don't use SSIS
> much, or rather not at all. I just wanted to save you from looking in
> SSMS. But isn't this "Advanced Editor" simply the Package Designer you
> get when you create an Integration Services project?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

ADSI Group Members and SQL

Does anyone have a good example of querying group members out of Active
Directory Group. The error seems to happen when the field I am querying has
multiple vales
This is what I have so far:
exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
'ADsDSOObject', 'adsdatasource'
select
convert(varchar(50), [Name]) as GroupName,
convert(varchar(500), member) as member
from openquery(xxx,
'select name, member
from ''LDAP://DC=domain,DC=com''
where objectClass = ''Group''')
This is the error message I am getting:
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
Could not convert the data value due to reasons other than sign mismatch or
overflow.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData returned
0x40eda: Data status returned from the provider: [COLUMN_NAME=member
STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
STATUS=DBSTATUS_S_OK]].
Thanks,
Kevin E.Hi,
The "member" attribute is multi-valued. ADO returns this as an array. I
assume the OLE/DB driver does as well. It can have no values, one, or many.
You should test your code with all 3 possibilities. I would have to
experiment using linkedserver.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:tvqdnWW6j--KbwjfRVn-jg@.centurytel.net...
> Does anyone have a good example of querying group members out of Active
> Directory Group. The error seems to happen when the field I am querying
has
> multiple vales
> This is what I have so far:
> exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
> 'ADsDSOObject', 'adsdatasource'
>
> select
> convert(varchar(50), [Name]) as GroupName,
> convert(varchar(500), member) as member
> from openquery(xxx,
> 'select name, member
> from ''LDAP://DC=domain,DC=com''
> where objectClass = ''Group''')
>
> This is the error message I am getting:
> Server: Msg 7346, Level 16, State 2, Line 1
> Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
> Could not convert the data value due to reasons other than sign mismatch
or
> overflow.
> OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData
returned
> 0x40eda: Data status returned from the provider: [COLUMN_NAME=member
> STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
> STATUS=DBSTATUS_S_OK]].
>
> Thanks,
> Kevin E.
>
>

ADO and ADO.NET

Ok, maybe I am more lost than I realized.
(I am a "new user" of SQL Server 2000 and posted this question in the
"newusers" group, then thought that perhaps I should have posted it to
"programming" instead. So, this is a delayed cross-post, sorry if you had
to see it twice.)
I have the documentation from the Platform SDK, and I started to read about
ADO. The first hits that I got on searching on ADO.NET were warnings not to
mix and match ADO and the .NET framework. Assuming that I don't actually
care what object model or set of methods I use, I just want to write code
that will work, what should I be looking at? The documentation on my screen
talks about ADO 2.8 and I think this is from an MDAC 2.8 SDK that I
installed. The topic "ADO history" doesn't even mention ADO.NET so I am
thinking I am not in the right place yet ... :)
Since I don't really care what technology I use so long as I can produce
results, should I read about ADO 2.8 or would I be a lot more productive to
find my documentation on ADO.NET and stay away from ADO 2.8?
I just found my documentation for the .NET Framework 1.1 SDK, so now I can
read about the "Overview of ADO.NET" and deeper stuff.
Suggestions? Use ADO.NET and ignore ADO 2.8, or the other way around? I
really don't care what I use, I just need to write some code that can read
and write to SQL Server 2000. My programming language will be VB.NET and my
development environment will be Microsoft Visual Studio 2003, and I'm coding
just for myself, and I have lots of programming experience but almost none
in VB or databases, and exactly zero in ADO or ADO.NET.
Thanks! Feel free to point out how clueless I am, this idea is sinking in
... :)
TadHi
If you are using .NET, forget about ADO 2.x and only read ADO.NET informatio
n.
ADO.NET is the new way of doing things.
Regards
Mike
"Tad Marshall" wrote:

> Ok, maybe I am more lost than I realized.
> (I am a "new user" of SQL Server 2000 and posted this question in the
> "newusers" group, then thought that perhaps I should have posted it to
> "programming" instead. So, this is a delayed cross-post, sorry if you had
> to see it twice.)
> I have the documentation from the Platform SDK, and I started to read abou
t
> ADO. The first hits that I got on searching on ADO.NET were warnings not
to
> mix and match ADO and the .NET framework. Assuming that I don't actually
> care what object model or set of methods I use, I just want to write code
> that will work, what should I be looking at? The documentation on my scre
en
> talks about ADO 2.8 and I think this is from an MDAC 2.8 SDK that I
> installed. The topic "ADO history" doesn't even mention ADO.NET so I am
> thinking I am not in the right place yet ... :)
> Since I don't really care what technology I use so long as I can produce
> results, should I read about ADO 2.8 or would I be a lot more productive t
o
> find my documentation on ADO.NET and stay away from ADO 2.8?
> I just found my documentation for the .NET Framework 1.1 SDK, so now I can
> read about the "Overview of ADO.NET" and deeper stuff.
> Suggestions? Use ADO.NET and ignore ADO 2.8, or the other way around? I
> really don't care what I use, I just need to write some code that can read
> and write to SQL Server 2000. My programming language will be VB.NET and
my
> development environment will be Microsoft Visual Studio 2003, and I'm codi
ng
> just for myself, and I have lots of programming experience but almost none
> in VB or databases, and exactly zero in ADO or ADO.NET.
> Thanks! Feel free to point out how clueless I am, this idea is sinking in
> ... :)
> Tad
>
>
>|||Thanks, Mike. I will ignore ADO 2.8 and focus on ADO.NET. There is no
point in my getting up to speed on anything that is basically obsolete
already.
What I am doing is really simple in some ways, and I would not be pushing
either ADO or ADO.NET to their limits in any way, but learning one set of
API/method calls is less work than learning two, and picking the one with
the longer shelf life just makes sense.
Thanks for your advice!
Tad
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:047C22C3-1AE6-4817-A395-4ED74B98517B@.microsoft.com...
> Hi
> If you are using .NET, forget about ADO 2.x and only read ADO.NET
> information.
> ADO.NET is the new way of doing things.
> Regards
> Mike
> "Tad Marshall" wrote:
>|||Here's a short list of some topics in MSDN Library to help you look into
ADO.NET on SQL Server.
SqlClient namespace (contains SqlConnection, SqlDataAdapter, SqlCommand,
SqlDataReader, etc.)
DataSets
DataGrid
Data Binding
This is just a short list, but enough to create a fully-functional ADO.NET
app in VB.NET.
"Tad Marshall" <tad@.tadmarshall.com> wrote in message
news:e9WHprSOFHA.3144@.tk2msftngp13.phx.gbl...
> Thanks, Mike. I will ignore ADO 2.8 and focus on ADO.NET. There is no
> point in my getting up to speed on anything that is basically obsolete
> already.
> What I am doing is really simple in some ways, and I would not be pushing
> either ADO or ADO.NET to their limits in any way, but learning one set of
> API/method calls is less work than learning two, and picking the one with
> the longer shelf life just makes sense.
> Thanks for your advice!
> Tad
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:047C22C3-1AE6-4817-A395-4ED74B98517B@.microsoft.com...
>|||Thank you Michael! This is perfect for starting me on my reading of the
docs, reading the right stuff will save me a lot of time!
My needs are not very complicated, so this will probably get me all the way
to working code, thanks!
Tad
"Michael C#" <howsa@.boutdat.com> wrote in message
news:eWy5Q8SOFHA.3848@.TK2MSFTNGP14.phx.gbl...
> Here's a short list of some topics in MSDN Library to help you look into
> ADO.NET on SQL Server.
> SqlClient namespace (contains SqlConnection, SqlDataAdapter, SqlCommand,
> SqlDataReader, etc.)
> DataSets
> DataGrid
> Data Binding
> This is just a short list, but enough to create a fully-functional ADO.NET
> app in VB.NET.|||Tad,
Check this out, this is the data access application block for .Net, this
will be very usefull to you if you are just beginning ADO.NET and wanting to
understand best practices.
http://msdn.microsoft.com/library/d...aab.
asp
"Tad Marshall" wrote:

> Ok, maybe I am more lost than I realized.
> (I am a "new user" of SQL Server 2000 and posted this question in the
> "newusers" group, then thought that perhaps I should have posted it to
> "programming" instead. So, this is a delayed cross-post, sorry if you had
> to see it twice.)
> I have the documentation from the Platform SDK, and I started to read abou
t
> ADO. The first hits that I got on searching on ADO.NET were warnings not
to
> mix and match ADO and the .NET framework. Assuming that I don't actually
> care what object model or set of methods I use, I just want to write code
> that will work, what should I be looking at? The documentation on my scre
en
> talks about ADO 2.8 and I think this is from an MDAC 2.8 SDK that I
> installed. The topic "ADO history" doesn't even mention ADO.NET so I am
> thinking I am not in the right place yet ... :)
> Since I don't really care what technology I use so long as I can produce
> results, should I read about ADO 2.8 or would I be a lot more productive t
o
> find my documentation on ADO.NET and stay away from ADO 2.8?
> I just found my documentation for the .NET Framework 1.1 SDK, so now I can
> read about the "Overview of ADO.NET" and deeper stuff.
> Suggestions? Use ADO.NET and ignore ADO 2.8, or the other way around? I
> really don't care what I use, I just need to write some code that can read
> and write to SQL Server 2000. My programming language will be VB.NET and
my
> development environment will be Microsoft Visual Studio 2003, and I'm codi
ng
> just for myself, and I have lots of programming experience but almost none
> in VB or databases, and exactly zero in ADO or ADO.NET.
> Thanks! Feel free to point out how clueless I am, this idea is sinking in
> ... :)
> Tad
>
>
>|||Thanks for the pointer, this looks like great stuff!
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:A6CB1895-2142-4AFB-90D6-1F58E7CE0089@.microsoft.com...
> Tad,
> Check this out, this is the data access application block for .Net, this
> will be very usefull to you if you are just beginning ADO.NET and wanting
> to
> understand best practices.
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp[/u
rl]|||I downloaded and installed the Application Block and will get going on
reading about it.
Is there a quick summary that you can give me of what I have here? It looks
like a big set of code and documentation, but I am not even clear on what an
"Application Block" is ...
I'll start reading, perhaps this will be obvious to me in five minutes ...
thanks for the tip!
Tad
"Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
news:A6CB1895-2142-4AFB-90D6-1F58E7CE0089@.microsoft.com...
> Tad,
> Check this out, this is the data access application block for .Net, this
> will be very usefull to you if you are just beginning ADO.NET and wanting
> to
> understand best practices.
> http://msdn.microsoft.com/library/d...aa
b.asp
> "Tad Marshall" wrote:
>|||"Tad Marshall" wrote:

> I downloaded and installed the Application Block and will get going on
> reading about it.
> Is there a quick summary that you can give me of what I have here? It loo
ks
> like a big set of code and documentation, but I am not even clear on what
an
> "Application Block" is ...
Application blocks are basically components that encapsulate common
enterprise application development tasks and best practices such as data
access, logging, exception management, caching and several other areas.
Travel one step up the tree from this link to find out about patterns and
practices which contain some important architectural concepts in application
development. If your just beginning this is a great place to start.
http://msdn.microsoft.com/library/d...hab.
asp

> I'll start reading, perhaps this will be obvious to me in five minutes ...
> thanks for the tip!
> Tad
> "Alien2_51" <dan.billow.remove@.monacocoach.removeme.com> wrote in message
> news:A6CB1895-2142-4AFB-90D6-1F58E7CE0089@.microsoft.com...
>
>|||No problem. Another nifty learning tool is the Data Form Wizard. If you
use VB.NET to create a Data Form using the wizard, you can look at the code
it generates to see how it does what it does. The code it generates is
somewhat generic and can be cleaned up a little, but it will give you a good
idea of how the different pieces interact with one another.
"Tad Marshall" <tad@.tadmarshall.com> wrote in message
news:OOck9CTOFHA.3788@.tk2msftngp13.phx.gbl...
> Thank you Michael! This is perfect for starting me on my reading of the
> docs, reading the right stuff will save me a lot of time!
> My needs are not very complicated, so this will probably get me all the
> way to working code, thanks!
> Tad
>

administrators group is deleted...

I deleted "administrators" group and "administrator" user in sql server by
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>

administrators group is deleted...

I deleted "administrators" group and "administrator" user in sql server by
chance.
How can I create them back.
Thanks in advance.I mean;
MyServer\administrator
BUILTIN\administrators
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>|||Log is as SA and you should be able to do it.
"haode" <haode@.hao.com> wrote in message
news:u8R%234Tf9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I deleted "administrators" group and "administrator" user in sql server by
> chance.
> How can I create them back.
> Thanks in advance.
>

Administrating content with integrated security?

I have a two tier application with a .net client that access data from a SQL
Server. Depending on what windows user group the user belong to I want them
to get different data. The solution I would like to have is one where a could
change only in the database and nothing in the client and still get different
users to get different data.
There is only one table that should differ for different the users. One
solution would therefore be to overload the table, i.e. create one table for
each user all with the same name but with different owners. Because the
client doesn't explicitly state the owner of the object it would get the
table owned by the user running the client.
This solution would lead to far to many identical copies of the table and
for each new user at the company you would have to create a new table on the
server. A better solution would be if it were possible to connect tables to
server roles and if the client would get access the table that is connected
to the server role that the user belongs to. To me, it seems this solution is
not possible because SQL Server will identify the table with the user and
never try with the role.
Regards,
Joeluse a trigger or a view on the table to match the user with a column to
select only the rows that pertain to them (current_user)
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:64B7A8C1-FF4A-4CF4-B3B7-8BE713D61E68@.microsoft.com...
>I have a two tier application with a .net client that access data from a
>SQL
> Server. Depending on what windows user group the user belong to I want
> them
> to get different data. The solution I would like to have is one where a
> could
> change only in the database and nothing in the client and still get
> different
> users to get different data.
> There is only one table that should differ for different the users. One
> solution would therefore be to overload the table, i.e. create one table
> for
> each user all with the same name but with different owners. Because the
> client doesn't explicitly state the owner of the object it would get the
> table owned by the user running the client.
> This solution would lead to far to many identical copies of the table and
> for each new user at the company you would have to create a new table on
> the
> server. A better solution would be if it were possible to connect tables
> to
> server roles and if the client would get access the table that is
> connected
> to the server role that the user belongs to. To me, it seems this solution
> is
> not possible because SQL Server will identify the table with the user and
> never try with the role.
> Regards,
> Joel

Administering many SQL Servers

We have 25+ SQL Servers our DBA group administers. Each is installed under a
domain account, call it Domain1\SQLAdmin. When we change the password to
Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server Agent
password properties on each box to be the new password. Is there any way to
automate this process?Hi
No. We have a similar problem with 1400 servers and are going to have each
server have it's own account and password.
This can reduce the likelihood of on compromised server being used to
compromise other, have developers connect from development to production
though linked servers or xp_cmdshell, and a denial of service (account
lockout) against the service account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> We have 25+ SQL Servers our DBA group administers. Each is installed under
> a
> domain account, call it Domain1\SQLAdmin. When we change the password to
> Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> Agent
> password properties on each box to be the new password. Is there any way
> to
> automate this process?|||I was afraid that was the answer. Fortunately we don't have 1400 nodes to
worry about. Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. We have a similar problem with 1400 servers and are going to have each
> server have it's own account and password.
> This can reduce the likelihood of on compromised server being used to
> compromise other, have developers connect from development to production
> though linked servers or xp_cmdshell, and a denial of service (account
> lockout) against the service account.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
> news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> > We have 25+ SQL Servers our DBA group administers. Each is installed under
> > a
> > domain account, call it Domain1\SQLAdmin. When we change the password to
> > Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> > Agent
> > password properties on each box to be the new password. Is there any way
> > to
> > automate this process?
>
>

Administering many SQL Servers

We have 25+ SQL Servers our DBA group administers. Each is installed under a
domain account, call it Domain1\SQLAdmin. When we change the password to
Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server Agent
password properties on each box to be the new password. Is there any way to
automate this process?
Hi
No. We have a similar problem with 1400 servers and are going to have each
server have it's own account and password.
This can reduce the likelihood of on compromised server being used to
compromise other, have developers connect from development to production
though linked servers or xp_cmdshell, and a denial of service (account
lockout) against the service account.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> We have 25+ SQL Servers our DBA group administers. Each is installed under
> a
> domain account, call it Domain1\SQLAdmin. When we change the password to
> Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> Agent
> password properties on each box to be the new password. Is there any way
> to
> automate this process?
|||I was afraid that was the answer. Fortunately we don't have 1400 nodes to
worry about. Thanks.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No. We have a similar problem with 1400 servers and are going to have each
> server have it's own account and password.
> This can reduce the likelihood of on compromised server being used to
> compromise other, have developers connect from development to production
> though linked servers or xp_cmdshell, and a denial of service (account
> lockout) against the service account.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
> news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
>
>

Administering many SQL Servers

We have 25+ SQL Servers our DBA group administers. Each is installed under a
domain account, call it Domain1\SQLAdmin. When we change the password to
Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server Agent
password properties on each box to be the new password. Is there any way to
automate this process?Hi
No. We have a similar problem with 1400 servers and are going to have each
server have it's own account and password.
This can reduce the likelihood of on compromised server being used to
compromise other, have developers connect from development to production
though linked servers or xp_cmdshell, and a denial of service (account
lockout) against the service account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
> We have 25+ SQL Servers our DBA group administers. Each is installed under
> a
> domain account, call it Domain1\SQLAdmin. When we change the password to
> Domain1\SQLAdmin we then have to go edit the SQL Server and SQL Server
> Agent
> password properties on each box to be the new password. Is there any way
> to
> automate this process?|||I was afraid that was the answer. Fortunately we don't have 1400 nodes to
worry about. Thanks.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No. We have a similar problem with 1400 servers and are going to have each
> server have it's own account and password.
> This can reduce the likelihood of on compromised server being used to
> compromise other, have developers connect from development to production
> though linked servers or xp_cmdshell, and a denial of service (account
> lockout) against the service account.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "LRSmith" <LRSmith@.discussions.microsoft.com> wrote in message
> news:4A097433-5BE9-4383-BB6E-9A8FDE099103@.microsoft.com...
>
>

Admin access to SQL without server/domain admin access

We are using Win2k3 R2 with SQL 2000 in a domain environment.

Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?

It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.

Any clarification would be greatly appreciated.

Thanks!

Part of the problem may be from using a more generic phrase like "admin level access to SQL". What that means to you, I'm not sure. That could be interpreted as administering the server from the windows level or it could mean being a member of the sysadmins server role in SQL Server.

But you don't need to be a member of a windows windows administrator group (local administrator on the server or domain administrator in the domain) to be a member of the sysamin server role in SQL Server. A member of the sysadmin role in SQL Server can add other logins to the sysadmin role - and anything thing else they want to do as members of the sysadmins role bypass security checks and can do anything in SQL Server. This is what it sounds like you are asking about.

I have seen some similar confusion coming from this type of scenario:

You have your SQL Server running under a service account and that account is a member of the local administrators group on the server on which SQL Server is running

You are a member of the sysadmin server role in SQL Server

Some of the command you execute as a member of the sysadmin role will execute under the security context of the service account. Being that the service account is a member of the local administrators group, you are executing some of the commands as if you are a member of the local administrators group on that server.

But that scenario doesn't mean you are a member of the local administrators group or that you need to be a member of the local administrators group.

-Sue