SQLAgentMail: 2012

Friday, March 30, 2012

Question(s) about memory management

I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.this is not bad, your queries are well parameterized and also your SPs,so th
ousands of querys are using the same plan, the problem is when you have a l
arge proc cache and a litle data cache.
--
Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message news:584554A5-
708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.

Question(s) about memory management

I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing that
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.This is a multi-part message in MIME format.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
this is not bad, your queries are well parameterized and also your =SPs,so thousands of querys are using the same plan, the problem is when =you have a large proc cache and a litle data cache.
-- Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message =news:584554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm =noticing that the procedure cache is shrinking at an alarming rate and the buffer =cache is growing. This is surprising to me as this is a proc intensive server =and I know SQL Server dynamically manages the memory. My question is there =anyway to change the amount of memory allocated to the proc cache? I went =through books online, but I only saw things relating to setting the min/max =memory amounts but nothing directly related to the type of management that =I'm wanting to do.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

this is not bad, your queries are well parameterized and also your SPs,so thousands of querys are using the =same plan, the problem is when you have a large proc cache and a litle data cache.
-- MauroMCTS - SQL Server 2005
"Big Ern" wrote in message news:584=554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing =that the procedure cache is shrinking at an alarming rate and the =buffer cache is growing. This is surprising to me as this is a proc intensive =server and I know SQL Server dynamically manages the memory. My question =is there anyway to change the amount of memory allocated to the proc cache? =I went through books online, but I only saw things relating to setting =the min/max memory amounts but nothing directly related to the type of = management that I'm wanting to do.

--=_NextPart_000_02CE_01C76B0A.1C9DB110--sql

Question!

We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!

Question!

We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle th
e
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!

Question!

We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!
Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!

question with type of replication

Hello,
I have a question on the subscriptions and type of replication to use.
We have about 5 subscriptions to a published db setup as merge replication
via the websync on sql2k5. We would actually like the subscriptions (all
running sql express) to just download changes to the publisher. We do not
need the publisher to pass any data up to the subscribers. Is there a way to
do this? We need to use the functionality of web synchronization, if at all
possible. Thanks in advance.
Jake
Use the exchangetype of uploadonly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jake" <noreply@.nowhere.com> wrote in message
news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a question on the subscriptions and type of replication to use.
> We have about 5 subscriptions to a published db setup as merge replication
> via the websync on sql2k5. We would actually like the subscriptions (all
> running sql express) to just download changes to the publisher. We do not
> need the publisher to pass any data up to the subscribers. Is there a way
> to do this? We need to use the functionality of web synchronization, if at
> all possible. Thanks in advance.
> Jake
>
|||Hilary,
Where would I add this script setting in SQL2k5? Since the clients are
sql express there really isn't a scheduled agent to run the replication,
we're using a .net app to start the replication process. Prior to this we
are running the following scripts to setup replication. Any further
clarification would be appreciated. Thanks in advance.
--BEGIN: Script to be run at Subscriber FOR
WAN--
sp_addmergepullsubscription
@.publication = 'pub_subscriber_db', --the name given to the publication
during the creation of the publication via the wizard
@.publisher_db = 'subscriber_db', --the name of the actual database running
on the server instance
@.publisher = 'computer-db', --the name of the server instance
@.subscriber_type = 'global',
@.sync_type='automatic'
--BEGIN: Script to be run at Publisher FOR
WAN--
use [subscriber_db]
exec sp_addmergesubscription @.publication = N'pub_wcpc_arizona',
@.subscriber = N'subscriber-01\subscriber',
@.subscriber_db = N'subscriber_db',
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PnLtc3RGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Use the exchangetype of uploadonly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jake" <noreply@.nowhere.com> wrote in message
> news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||Jake,
in SQL Server 2005 there is the @.subscriber_upload_options parameter of
sp_addmergearticle which takes the following values:
0 : No restrictions. Changes made at the Subscriber are uploaded to the
Publisher.
1 : Changes are allowed at the Subscriber, but they are not uploaded to the
Publisher.
2 : Changes are not allowed at the Subscriber
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Is there a way to have changes made on the subscriber downloaded the the
publisher, but additions on the publisher are not uploaded to the
subscriber?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
Let me re-phrase.
Scenario:
We have two (actually more but there shouldn't be a difference between two
or more) subscribers and want their data to download to the publisher, but
we do not want subscriber 1 data to be uploaded to subscriber 2 if
subscriber 2 replicates after subscriber 1 has.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jake,
you could possibly modift the exchangetype parameter on the commandline
dynamically when initiating a pull subscription. However I'd be afraid that
the data will soon become corrupt this way. Merge is really designed for
data to be fully partitioned amongst all the nodes involved. Partitioning is
ok, and not replicating deletes to a subscriber is also ok - but what you
are suggesting could lead to a complete spaghetti of data. Perhaps you could
explain further the business requirements and we might arrive at a different
way of realising them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the responses so far. I think we have it. We're going to add
a column for the workstation and use filter rows with the host_name. If I am
correct this will only replicate the items for that subscriber to the
publisher, back and forth. Is this correct?
Also is there a way to limit the amount of RAM that replication can use? We
have a couple of subscribers, using sql express with little RAM and are
running out of VM. My thought is if we can limit the RAM being used it will
avoid this issue, but increase the amount of time it takes to replicate.
What are your thoughts?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSj%23pL4RGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Jake,
> you could possibly modift the exchangetype parameter on the commandline
> dynamically when initiating a pull subscription. However I'd be afraid
> that the data will soon become corrupt this way. Merge is really designed
> for data to be fully partitioned amongst all the nodes involved.
> Partitioning is ok, and not replicating deletes to a subscriber is also
> ok - but what you are suggesting could lead to a complete spaghetti of
> data. Perhaps you could explain further the business requirements and we
> might arrive at a different way of realising them.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Jake,
for the first part, you could use dynamic filtering, but if you only have a
few subscribers, a separate publication for each subscriber works better.
Limiting the RAM for the replication exe - interesting. To be honest, I
don't know how this can be done. I'll look into it though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the help so far. If you find a way to limit the Ram I'd be
very appreciative.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23mgw854RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Jake,
> for the first part, you could use dynamic filtering, but if you only have
> a few subscribers, a separate publication for each subscriber works
> better. Limiting the RAM for the replication exe - interesting. To be
> honest, I don't know how this can be done. I'll look into it though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

question with trigger

i have a table which has columns scheduledrent,actualrent beside other columns.
the actual rent is based on some calculations but uses scheduledrent for the calculations.

everytime there is a change to scheduledrent, the actualrent should also change. so if i write a trigger saying
if updated(scheduledrent)
do some calculations and change the actualrent..using an update stmt.

would it make the change only for that row or for all the rows in the table? if it does it for all the rows, how do i make it apply only to that row. how should i have my update stmt in such a case. my primarykey is a combination of 4 rows. so can i get all the 4 values into some variables and while i do the update do it on that condition ? or is there any easier way of doing it.

thanks.
D.Inside the trigger code you have access to a logical table named "inserted". You can run your update logic with that table and it will only update the rows that were updated. The logical table "inserted" is available for update triggers and, naturally enough, insert triggers.

The logical table "deleted" is available for delete triggers and shows the records to be deleted. In an update trigger the "deleted" table shows the original values before they are to be changed.|||ok it works if i change it to inserted.
have one q though. i have 10 ppl accessing the db at any time. so if all of them make an update to the db (not to the same record though) each one will have an entry in the logical inserted table. so how would the system differentiate between them, since they all login to sql server through the same asp.net account.

thanks for the tip.|||The inserted logical table will only be for the record(s) that triggered that specific update. So everyone will have their own inserted logical tables and won't be stepping on one another.|||oh so sql server creates an inserted table for each user ?
but how does the sql server differentiate between users ?

lets say userA logged onto mysite and made an update to a row. the row gets inserted into the inserted table. userB also logs in at the same time and makes an update to another row. the new row also gets into the inserted table. now both these users are logged into the sqlserver through the same ASP.NET account. so how does sql server differentiate betwen userA and userB and their updated rows?

thanks again.|||It's a "logical" table meaning that it acts like a table but isn't really one. It is there to allow the trigger code to inspect the state of the old and new records so that the trigger code can take whatever action is appropriate. The logical table inside one trigger event isn't visible or accessible to the logical table in any other trigger event, even for the same user logged on multiple times simultaneously updating different record(s).|||i dont think i have understood completely. do you know any article that xplains in detail how the inserted table actually works...?
thanks anyway. will leave it for now. hope it works. will get back incase i have any prb.

thanks McMurdoStation.sql

Question With SQLDATASOURCE and User.identity.name

is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this

select * from tblUsers wherevcUserName=@.vcUserName

asp:ParameterName="user.identity.name"Type="String/>

</selectParameters>

Hi,

The way you are trying will not work.

Here is a link from Peter with a solution to handle this case by using ExpressionBuilder:

http://peterkellner.net/2006/09/18/expressionbuilderidentity/

Another way, I think you can try to assign the user to the selectParameter from your code under SqlDataSource's selecting event programatically. Also, you can assign the login user to a session varable and access to it through SessionParameter

Question with SQL String

I have this code below to parse the string ''00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'' into Multiple columns.

Here is the code, but the code is splititng the columns incorrectly.

I need it to appear as

col1 col2 col3 col4 col5
00120212 pendin mod pen ria te 3/6/2007 3:51:49 pm
Can someone pl assist with this code below.

DECLARE @.str varchar(8000)
SET @.str = '00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'

DECLARE @.columns TABLE (
col1 varchar(8000)
,col2 varchar(8000)
,col3 varchar(8000)
)

SET @.str = LTrim(RTrim(@.str))

DECLARE @.col1 int
,@.col2 int
,@.col3 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)

INSERT INTO @.columns
VALUES (
SubString(@.str, 2, @.col1 - 2)
,SubString(@.str, @.col1 + 3, Len(@.str) - @.col2 - 4)
,SubString(@.str, @.col2 + 3, Len(@.str) - @.col3 - 1)
)

SELECT * FROM @.columns

You may find Jen Suessmeyer's Split function to be very useful for this situation.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Please check out the link below:

http://www.sommarskog.se/arrays-in-sql.html

It contains few TVFs that can be used to split a string based on a delimiter. You can use it along with PIVOT for example to get the individual values easily like:

SELECT p.[1], p.[2], p.[3], p.[4], p.[5]

FROM split_str(@.str, '~') AS t

PIVOT (min(t.value) for t.idx in ([1], [2], [3], [4], [5])) as p

Alternatively, you can do these type of operations easily on the client side and send the values individually. This way you can use SQL for what it is supposed to do.

|||

This is by code below, Delimiter is ~. I need to split the values, into multilple field , based on the ~ being the delimiter. How do I loop throu this string, until the end of the string, and then split it one - by-one into multiple fileds? PL ADVISE?

Declare @.Str Varchar(1000),@.I Int

set @.str='0001232~PENDING~MOD PENDING~Trad Jane~3/29/2007 5:03:30 PM~0001232~PENDING~MODIFICATION PENDING~ Jane Delder~3/29/2007 5:05:06 PM~0001232~PENDING~Approved~ Mon Savy~3/29/2007 5:05:27 PM~0001232~PEND'
SET @.str = LTrim(RTrim(@.str))

DECLARE @.columns TABLE (
LoanNum varchar(8000)
,ConvertedFromStatus varchar(8000)
,ConvertedToStatus varchar(8000)
,ConvertedName varchar(8000)
,StatusChangedDate text
)

begin
Begin
DECLARE @.col1 int
,@.col2 int
,@.col3 int,
@.col4 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)
SET @.col4 = CharIndex('~', @.str, @.col3 + 1)
--print @.col1
--print @.col2
--print @.col3
--print @.col4

INSERT INTO @.columns
VALUES (
Left(@.Str, @.Col1 - 1),
SubString(@.Str, @.Col1 + 1, @.col2 - @.Col1 - 1),
SubString(@.Str, @.Col2 + 1, @.col3 - @.Col2 - 1),
SubString(@.Str, @.Col3 + 1, @.col4 - @.Col3 - 1),
SubString(@.Str, @.Col4 + 1, @.col4 )
)
End

--Select @.I = 0

End
SELECT * FROM @.columns

|||Does the Split function I previously posted for you not work properly?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?
Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>
|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
>> IP Addresses tab of the sql server configuration manager.
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
> > hi
> > i have a sql 2000 default instance and i just installed a sql 2005
> > named instance. now i want to assign a specific port to my sql 2005
> > instance
> > i am trying to figure out the IPAll section of the TCP/IP Properties --
> >> IP Addresses tab of the sql server configuration manager.
> > my entries look like this
> > IP1 has the following settings (i've masked out the IP addresses with
> > x's)
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.xxx.x.xxx
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IP2 has the following settings:
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.x.x.x
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IPAll
> > TCP Dynamic Ports: 1500
> > TCP Port:
> > it looks like sql server 2005 named instance now listens on port
> > 1500. I then read this in the books on line:
> > To configure a static port, leave the TCP Dynamic Ports box blank and
> > provide an available port number in the TCP Port box
> > TCP Dynamic Ports
> > Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> > 0.
> > For IPAll, displays the port number of the dynamic port used.
> > which is correct?
Hi Jasper, thank you. is there a reason why to choose this method
over the other?sql

Question with Flattening / Denormalizing a Hiearchy Dynamically

Hello,

I've been trying to figure out a way to handle flattening a Reports To hierarchy table dyamically. The end result I need is this. Example assumes a two person / two level hiearchy. Emplid of 1 is the CEO, Emplid of 2 is the direct report. I am unable to get this result.

Emplid Reports To Level 1 Emplid Reports To Level 2 Id

1 1

2 1 2

Much kudos to Adam Machanic and Itzik Ben-Gan for their code samples and articles (links below). They have gotten me quite far but I am not there yet.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1107414,00.html

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

After reading these articles I have been able to find out the materialized path and the level in which an employee resides in the Reports To hierarchy. Here is what the data looks like after the recursive CTE (I'll put the code down at the bottom of the blog)

Listing 1:

Emplid Level thePath (Materialized reporting path)

1 1 .1

2 2 .1.2

I have then dynamically invoked the PIVOT command via a stored procedure thanks to help from Itzik again (http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html). This is allowing me to get a result like this below, but it only has the column for the level in the hierarchy in which the employee resides popululated and not the columns for the levels about the employee (Emplid 2 should have a 1 in the second column) See result below.

Listing 2:

Emplid Reports To Level 1 Emplid Reports To Level 2 Id

1 1

2 2

Is there a way to populate the second column for Emplid 2 all in one shot? Right now I am thinking I'll have to land the result of pivot function call to a table and then parse out the thePath column and update the unpopulated columns that way. It seems like this is too complicated. Any suggestions?

Thanks in advance!

Sean

Code is below. Below has a view that contains my recursive CTE and a select from the view (Like Listing 1 Results). The function that dynamically executes the pivot function and then the function call (Like Listing 2 Results).

--Recursive CTE view definition

Use AdventureWorks
;

create view HumanResources.V_EMP_HIER
as

WITH EmployeeCTE
AS
(
SELECT
EmployeeID,
1 AS Level,
CAST('.' + CAST(EmployeeID AS VARCHAR(10)) + '.'
AS VARCHAR(MAX)) AS thePath
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

SELECT
E.EmployeeID,
x.Level + 1 AS Level,
x.thePath + '.' + CONVERT(VARCHAR(MAX), E.EmployeeID) AS thePath
FROM HumanResources.Employee E
JOIN EmployeeCTE x ON x.EmployeeID = E.ManagerID
)
SELECT
EmployeeID,
Level,
thePath
FROM EmployeeCTE

;
GO

--Snipet to list the hierarchy
select
EmployeeID,
Level,
thePath,
REPLICATE(' | ', Level) + CONVERT(varchar,EmployeeID) AS EmpSort

FROM
HumanResources.V_EMP_HIER
order by thePath
;

Dynamic Pivot Function. Thanks Itzik!

Use AdventureWorks
;

IF EXISTS
(SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usp_pivot]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_pivot]
;
GO

CREATE PROC [dbo].[usp_pivot]
@.schema_name AS sysname = N'dbo', -- schema of table/view
@.object_name AS sysname = NULL, -- name of table/view
@.on_rows AS sysname = NULL, -- group by column
@.on_cols AS sysname = NULL, -- rotation column
@.agg_func AS NVARCHAR(12) = N'MAX', -- aggregate function
@.agg_col AS sysname = NULL -- aggregate column
AS

DECLARE
@.object AS NVARCHAR(600),
@.sql AS NVARCHAR(MAX),
@.cols AS NVARCHAR(MAX),
@.newline AS NVARCHAR(2),
@.msg AS NVARCHAR(500);

SET @.newline = NCHAR(13) + NCHAR(10);
SET @.object = QUOTENAME(@.schema_name) + N'.' + QUOTENAME(@.object_name);

-- Check for missing input
IF @.schema_name IS NULL
OR @.object_name IS NULL
OR @.on_rows IS NULL
OR @.on_cols IS NULL
OR @.agg_func IS NULL
OR @.agg_col IS NULL
BEGIN
SET @.msg = N'Missing input parameters: '
+ CASE WHEN @.schema_name IS NULL THEN N'@.schema_name;' ELSE N'' END
+ CASE WHEN @.object_name IS NULL THEN N'@.object_name;' ELSE N'' END
+ CASE WHEN @.on_rows IS NULL THEN N'@.on_rows;' ELSE N'' END
+ CASE WHEN @.on_cols IS NULL THEN N'@.on_cols;' ELSE N'' END
+ CASE WHEN @.agg_func IS NULL THEN N'@.agg_func;' ELSE N'' END
+ CASE WHEN @.agg_col IS NULL THEN N'@.agg_col;' ELSE N'' END
RAISERROR(@.msg, 16, 1);
RETURN;
END

-- Allow only existing table or view name as input object
IF COALESCE(OBJECT_ID(@.object, N'U'),
OBJECT_ID(@.object, N'V')) IS NULL
BEGIN
SET @.msg = N'%s is not an existing table or view in the database.';
RAISERROR(@.msg, 16, 1, @.object);
RETURN;
END

-- Verify that column names specified in @.on_rows, @.on_cols, @.agg_col exist
IF COLUMNPROPERTY(OBJECT_ID(@.object), @.on_rows, 'ColumnId') IS NULL
OR COLUMNPROPERTY(OBJECT_ID(@.object), @.on_cols, 'ColumnId') IS NULL
OR COLUMNPROPERTY(OBJECT_ID(@.object), @.agg_col, 'ColumnId') IS NULL
BEGIN
SET @.msg = N'%s, %s and %s must'
+ N' be existing column names in %s.';
RAISERROR(@.msg, 16, 1, @.on_rows, @.on_cols, @.agg_col, @.object);
RETURN;
END

-- Verify that @.agg_func is in a known list of functions
-- Add to list as needed and adjust @.agg_func size accordingly
IF @.agg_func NOT IN
(N'AVG', N'COUNT', N'COUNT_BIG', N'SUM', N'MIN', N'MAX',
N'STDEV', N'STDEVP', N'VAR', N'VARP')
BEGIN
SET @.msg = N'%s is an unsupported aggregate function.';
RAISERROR(@.msg, 16, 1, @.agg_func);
RETURN;
END

-- Construct column list
SET @.sql =
N'SET @.result = ' + @.newline +
N' STUFF(' + @.newline +
N' (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]' + @.newline +
N' FROM (SELECT DISTINCT('
+ QUOTENAME(@.on_cols) + N') AS pivot_col' + @.newline +
N' FROM ' + @.object + N') AS DistinctCols' + @.newline +
N' ORDER BY pivot_col' + @.newline +
N' FOR XML PATH('''')),' + @.newline +
N' 1, 1, N'''');'

EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.result AS NVARCHAR(MAX) OUTPUT',
@.result = @.cols OUTPUT;

-- Check @.cols for possible SQL injection attempt
IF UPPER(@.cols) LIKE UPPER(N'%0x%')
OR UPPER(@.cols) LIKE UPPER(N'%;%')
OR UPPER(@.cols) LIKE UPPER(N'%''%')
OR UPPER(@.cols) LIKE UPPER(N'%--%')
OR UPPER(@.cols) LIKE UPPER(N'%/*%*/%')
OR UPPER(@.cols) LIKE UPPER(N'%EXEC%')
OR UPPER(@.cols) LIKE UPPER(N'%xp[_]%')
OR UPPER(@.cols) LIKE UPPER(N'%sp[_]%')
OR UPPER(@.cols) LIKE UPPER(N'%SELECT%')
OR UPPER(@.cols) LIKE UPPER(N'%INSERT%')
OR UPPER(@.cols) LIKE UPPER(N'%UPDATE%')
OR UPPER(@.cols) LIKE UPPER(N'%DELETE%')
OR UPPER(@.cols) LIKE UPPER(N'%TRUNCATE%')
OR UPPER(@.cols) LIKE UPPER(N'%CREATE%')
OR UPPER(@.cols) LIKE UPPER(N'%ALTER%')
OR UPPER(@.cols) LIKE UPPER(N'%DROP%')
-- Look for other possible strings used in SQL injection here
BEGIN
SET @.msg = N'Possible SQL injection attempt.';
RAISERROR(@.msg, 16, 1);
RETURN;
END

-- Create the PIVOT query
SET @.sql =
N'SELECT *' + @.newline +
N'FROM' + @.newline +
N' ( SELECT ' + @.newline +
N' ' + QUOTENAME(@.on_rows) + N',' + @.newline +
N' ' + QUOTENAME(@.on_cols) + N' AS pivot_col,' + @.newline +
N' ' + QUOTENAME(@.agg_col) + N' AS agg_col' + @.newline +
N' FROM ' + @.object + @.newline +
N' ) AS PivotInput' + @.newline +
N' PIVOT' + @.newline +
N' ( ' + @.agg_func + N'(agg_col)' + @.newline +
N' FOR pivot_col' + @.newline +
N' IN(' + @.cols + N')' + @.newline +
N' ) AS PivotOutput;';

EXEC sp_executesql @.sql;
GO

Call of the function that flattens the hierarchy, but doesn't populate of of the reporting level columns.

Use AdventureWorks
;

EXEC dbo.usp_pivot
@.schema_name = N'HumanResources',
@.object_name = N'V_EMP_HIER',
@.on_rows = N'EmployeeID',
@.on_cols = N'Level',
@.agg_func = N'MAX',
@.agg_col = N'EmployeeID';
;

Sean, we talked at the Microsoft BI Conference last week in Seattle and you were looking for some help on this posting. I’ve followed up with an internal alias that had some T-SQL gurus on it and hopefully one of the following solutions may help you get going in the right direction. Alternatively, you could consider using Microsoft SQL Server Analysis Services (AS) for this kind of reporting which may make it easier and faster. There is the notion of Parent\Child dimensions built into AS.

Good Luck!

-- Scott

Perhaps this would help…

set nocount on;

use northwind;

-- create helper function fn_nums

-- returning an auxiliary table of numbers

if object_id('dbo.fn_nums', 'if') is not null

drop function dbo.fn_nums;

create function dbo.fn_nums(@.max as int)

returns table as return

with

c0 as(select 0 as const union all select 0),

c1 as(select 0 as const from c0 as a, c0 as b),

c2 as(select 0 as const from c1 as a, c1 as b),

c3 as(select 0 as const from c2 as a, c2 as b),

c4 as(select 0 as const from c3 as a, c3 as b),

c5 as(select 0 as const from c4 as a, c4 as b),

c6 as(select 0 as const from c5 as a, c5 as b)

select top(@.max) row_number()

over(order by const) as n

from c6;

-- create helper function fn_split

-- splitting a string

if object_id('dbo.fn_split') is not null

drop function dbo.fn_split;

create function dbo.fn_split

(@.string varchar(max), @.separator char(1) = ',') returns table

return

select

n - len(replace(left(array, n), @.separator, '')) + 1 as pos,

substring(array, n,

charindex(@.separator, array + @.separator, n) - n) as element

from (select @.string as array) as d

join dbo.fn_nums(900)

on n <= len(array)

and substring(@.separator + array, n, 1) = @.separator;

-- Solution query

with empscte as

(

select employeeid, reportsto,

'.' + cast(employeeid as varchar(max)) + '.' as thepath

from employees

where reportsto is null

union all

select sub.employeeid, sub.reportsto,

mgr.thepath + cast(sub.employeeid as varchar(max)) + '.'

from empscte as mgr

join employees as sub

on sub.reportsto = mgr.employeeid

)

select *

from (select employeeid, pos, element as managerid

from empscte as e

cross apply dbo.fn_split(stuff(e.thepath, 1, 1, ''), '.') as s) as d

pivot(max(managerid) for pos in([1],[2],[3],[4],[5]/*add more levels here*/)) as p;

From: Ty Balascio
Sent: Monday, May 14, 2007 11:09 AM
Subject: RE: T-SQL Question from customer

I was part of a Usenet thread on this many years ago. Here was the preferred solution.

<paste>

-- If a node's parent is 0, then it's the root node

-- Each node has a unique id and a name

CREATE TABLE Tree

(

parent INT NOT NULL DEFAULT 0 CHECK (parent >= 0),

node INT NOT NULL CHECK (node > 0) PRIMARY KEY,

name VARCHAR(20) NOT NULL

)

-- UDF to return all descendants of a given node

-- Node is identified by its id number

-- Distance is the number of links between two nodes

CREATE FUNCTION Descendants(@.root_node INT)

RETURNS @.nodes TABLE

(node INT NOT NULL PRIMARY KEY CHECK (node > 0),

name VARCHAR(20) NOT NULL,

distance INT NOT NULL CHECK (distance >= 0))

BEGIN

IF NOT EXISTS (SELECT * FROM Tree WHERE node = @.root_node)

RETURN

DECLARE @.distance INT,

@.next_distance INT

SELECT @.distance = 0,

@.next_distance = 1

INSERT INTO @.nodes (node, name, distance)

SELECT node, name, @.distance

FROM Tree

WHERE node = @.root_node

WHILE EXISTS (SELECT * FROM @.nodes WHERE distance = @.distance)

BEGIN

INSERT INTO @.nodes (node, name, distance)

SELECT T.node, T.name, @.next_distance

FROM @.nodes AS N

INNER JOIN

Tree AS T

ON N.distance = @.distance AND

N.node = T.parent

SELECT @.distance = @.next_distance,

@.next_distance = @.next_distance + 1

END

RETURN

END

-- Sample tree

INSERT INTO Tree (parent, node, name)

SELECT 0, 1, 'A' -- root node

UNION ALL

SELECT 1, 2, 'B'

UNION ALL

SELECT 1, 3, 'C'

UNION ALL

SELECT 2, 4, 'D'

UNION ALL

SELECT 2, 5, 'E'

UNION ALL

SELECT 3, 6, 'F'

UNION ALL

SELECT 5, 7, 'G'

UNION ALL

SELECT 5, 8, 'H'

UNION ALL

SELECT 5, 9, 'I'

-- All nodes

SELECT *

FROM Descendants(1)

ORDER BY distance, node

-- All nodes from node id 2 (named B)

SELECT *

FROM Descendants(2)

ORDER BY distance, node

</paste>

|||

Below are simpler and more efficient solutions. You don't really need the UDFs or PIVOT clause and other objects. You can do it with a single CTE. The level can be encoded in the hierarchy path so it is just a matter of decoding it the easiest way.

Code Snippet

-- Using Northwind Employees table:

with EmpTree
as
(
select e.EmployeeID, cast(cast(e.EmployeeID as binary(4)) as varbinary(max)) as EmpHier
from Northwind.dbo.Employees as e
where e.ReportsTo is null
union all
select c.EmployeeID, cast(p.EmpHier + cast(c.EmployeeID as binary(4)) as varbinary(max))
from EmpTree as p
join Northwind.dbo.Employees as c
on c.ReportsTo = p.EmployeeID
)
select EmployeeID
, nullif(cast(substring(EmpHier, 1, 4) as int), 0) as lvl_1
, nullif(cast(substring(EmpHier, 5, 4) as int), 0) as lvl_2
, nullif(cast(substring(EmpHier, 9, 4) as int), 0) as lvl_3
, nullif(cast(substring(EmpHier, 13, 4) as int), 0) as lvl_4
, nullif(cast(substring(EmpHier, 17, 4) as int), 0) as lvl_5
, nullif(cast(substring(EmpHier, 21, 4) as int), 0) as lvl_6
, nullif(cast(substring(EmpHier, 25, 4) as int), 0) as lvl_7
, nullif(cast(substring(EmpHier, 29, 4) as int), 0) as lvl_8
, nullif(cast(substring(EmpHier, 33, 4) as int), 0) as lvl_9
, nullif(cast(substring(EmpHier, 37, 4) as int), 0) as lvl_10
from EmpTree;

-- Using AdventureWorks Employee table:

with EmpTree
as
(
select e.EmployeeID, cast(cast(e.EmployeeID as binary(4)) as varbinary(max)) as EmpHier
from AdventureWorks.HumanResources.Employee as e
where e.ManagerID is null
union all
select c.EmployeeID, cast(p.EmpHier + cast(c.EmployeeID as binary(4)) as varbinary(max))
from EmpTree as p
join AdventureWorks.HumanResources.Employee as c
on c.ManagerID = p.EmployeeID
)
select EmployeeID
, nullif(cast(substring(EmpHier, 1, 4) as int), 0) as lvl_1
, nullif(cast(substring(EmpHier, 5, 4) as int), 0) as lvl_2
, nullif(cast(substring(EmpHier, 9, 4) as int), 0) as lvl_3
, nullif(cast(substring(EmpHier, 13, 4) as int), 0) as lvl_4
, nullif(cast(substring(EmpHier, 17, 4) as int), 0) as lvl_5
, nullif(cast(substring(EmpHier, 21, 4) as int), 0) as lvl_6
, nullif(cast(substring(EmpHier, 25, 4) as int), 0) as lvl_7
, nullif(cast(substring(EmpHier, 29, 4) as int), 0) as lvl_8
, nullif(cast(substring(EmpHier, 33, 4) as int), 0) as lvl_9
, nullif(cast(substring(EmpHier, 37, 4) as int), 0) as lvl_10
from EmpTree;

|||

Scott, Ty and Umachandar.

Thank you all for your options. The solutions give us what we need and a few alternatives too.

I really appreciate you time and efforts.

Sean

|||

Two other options that were forwarded to me:

Here's another option:

with empscte as
(
select employeeid, managerid, 1 as lvl,
cast(employeeid as varbinary(max)) as binpath
from humanresources.employee
where managerid is null

union all

select sub.employeeid, sub.managerid, mgr.lvl + 1,
mgr.binpath + cast(sub.employeeid as binary(4))
from empscte as mgr
join humanresources.employee as sub
on sub.managerid = mgr.employeeid
)
select replicate(' | ', lvl-1) + cast(employeeid as varchar(10)) as emp,
nullif(0+substring(binpath, 1, 4), 0) as l1,
nullif(0+substring(binpath, 5, 4), 0) as l2,
nullif(0+substring(binpath, 9, 4), 0) as l3,
nullif(0+substring(binpath, 13, 4), 0) as l4,
nullif(0+substring(binpath, 17, 4), 0) as l5,
nullif(0+substring(binpath, 21, 4), 0) as l6,
/* add more levels here if needed */
lvl
from empscte
order by binpath;

Itzik

Another response – this one from Adam.

Maybe I'm missing something--how dynamic do you want this to be? Can you use placeholder columns for levels that may not be there? If so, wouldn't something like the following work fine (you can run it in AW):

with emps as

(

select

e.employeeid,

null as ReportsToLevel1,

null as ReportsToLevel2,

null as ReportsToLevel3,

null as ReportsToLevel4,

null as ReportsToLevel5,

null as ReportsToLevel6,

null as ReportsToLevel7,

null as ReportsToLevel8,

1 as theLevel

from humanresources.employee e

where managerid is null

union all

select

e.employeeid,

case theLevel when 1 then e.managerid else x.ReportsToLevel1 end,

case theLevel when 2 then e.managerid else x.ReportsToLevel2 end,

case theLevel when 3 then e.managerid else x.ReportsToLevel3 end,

case theLevel when 4 then e.managerid else x.ReportsToLevel4 end,

case theLevel when 5 then e.managerid else x.ReportsToLevel5 end,

case theLevel when 6 then e.managerid else x.ReportsToLevel6 end,

case theLevel when 7 then e.managerid else x.ReportsToLevel7 end,

case theLevel when 8 then e.managerid else x.ReportsToLevel8 end,

theLevel + 1 as theLevel

from humanresources.employee e

join emps x on x.employeeid = e.managerid

)

select *

from emps