Internal Architecture of MDF file

--I just created a new datbase. Added one row to the table and an index.
--Looking at the mdf file using a hex editor surprised me.
create database mydb1
go
use mydb1
go
create table table1 (a char(10), b char(10))
go
insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find one (1). Only one.
--Start sql
use mydb1
go
create index idx1 on table1(b)
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find THREE (3). Why?
--Serious replies only please.
/Bob
Bob,
You don't need a hex editor; you use use DBCC PAGE to view the page
structures without going to all of this trouble. Syntax here:
http://www.sql-server-performance.co...ented_dbcc.asp
If you're really interested in this stuff, you should get a copy of Kalen
Delaney's _Inside SQL Server 2000_, which describes the structures in great
detail.
"Bob" <utefan001@.gmail.com> wrote in message
news:40692455.0409281803.4ce67381@.posting.google.c om...
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you
care.
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find THREE (3). Why?
> --Serious replies only please.
> /Bob
|||See
http://www.nigelrivett.net/PageStructure.html
"Bob" wrote:

> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find THREE (3). Why?
> --Serious replies only please.
> /Bob
>
|||Maybe its got to do with additional pointers in the index ?
Dylan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#kbGikcpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Bob,
> You don't need a hex editor; you use use DBCC PAGE to view the page
> structures without going to all of this trouble. Syntax here:
>
http://www.sql-server-performance.co...ented_dbcc.asp
> If you're really interested in this stuff, you should get a copy of Kalen
> Delaney's _Inside SQL Server 2000_, which describes the structures in
great
> detail.
>
> "Bob" <utefan001@.gmail.com> wrote in message
> news:40692455.0409281803.4ce67381@.posting.google.c om...
> care.
> (hackman)
> (hackman)
>

Internal Architecture of MDF file

--I just created a new datbase. Added one row to the table and an index.
--Looking at the mdf file using a hex editor surprised me.
create database mydb1
go
use mydb1
go
create table table1 (a char(10), b char(10))
go
insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find one (1). Only one.
--Start sql
use mydb1
go
create index idx1 on table1(b)
--Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
--Search for string 23571113. You will find THREE (3). Why'
--Serious replies only please.
/BobBob,
You don't need a hex editor; you use use DBCC PAGE to view the page
structures without going to all of this trouble. Syntax here:
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
If you're really interested in this stuff, you should get a copy of Kalen
Delaney's _Inside SQL Server 2000_, which describes the structures in great
detail.
"Bob" <utefan001@.gmail.com> wrote in message
news:40692455.0409281803.4ce67381@.posting.google.com...
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you
care.
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor
(hackman)
> --Search for string 23571113. You will find THREE (3). Why'
> --Serious replies only please.
> /Bob|||See
http://www.nigelrivett.net/PageStructure.html
"Bob" wrote:
> --I just created a new datbase. Added one row to the table and an index.
> --Looking at the mdf file using a hex editor surprised me.
> create database mydb1
> go
> use mydb1
> go
> create table table1 (a char(10), b char(10))
> go
> insert into table1 values ('firstTest','23571113') --primes 2-13 if you care.
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find one (1). Only one.
> --Start sql
> use mydb1
> go
> create index idx1 on table1(b)
> --Stop here..Shut down sql and view the mdf file with a hex editor (hackman)
> --Search for string 23571113. You will find THREE (3). Why'
> --Serious replies only please.
> /Bob
>|||Maybe its got to do with additional pointers in the index ?
Dylan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#kbGikcpEHA.1668@.TK2MSFTNGP14.phx.gbl...
> Bob,
> You don't need a hex editor; you use use DBCC PAGE to view the page
> structures without going to all of this trouble. Syntax here:
>
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
> If you're really interested in this stuff, you should get a copy of Kalen
> Delaney's _Inside SQL Server 2000_, which describes the structures in
great
> detail.
>
> "Bob" <utefan001@.gmail.com> wrote in message
> news:40692455.0409281803.4ce67381@.posting.google.com...
> > --I just created a new datbase. Added one row to the table and an index.
> > --Looking at the mdf file using a hex editor surprised me.
> > create database mydb1
> > go
> > use mydb1
> > go
> > create table table1 (a char(10), b char(10))
> > go
> > insert into table1 values ('firstTest','23571113') --primes 2-13 if you
> care.
> > --Stop here..Shut down sql and view the mdf file with a hex editor
> (hackman)
> > --Search for string 23571113. You will find one (1). Only one.
> > --Start sql
> >
> > use mydb1
> > go
> > create index idx1 on table1(b)
> > --Stop here..Shut down sql and view the mdf file with a hex editor
> (hackman)
> > --Search for string 23571113. You will find THREE (3). Why'
> > --Serious replies only please.
> > /Bob
>sql

Interesting join

I'm looking for some guidance on how to create a join between two tables
given the following...
Table1
ID SeqNum
A 1
A 2
B 1
B 2
B 3
Table2
ID SeqNum
A 33
A 36
B 66
B 67
B 99
The problem here is that Table2's Seqnum field does not contain the same
value as Table1's Seqnum field. However, it is guaranteed to contain the
same sequential ordering within ID's. Therefore, the lowest Seqnum value in
Table1 logically links with the lowest Seqnum value in Table2 for each ID.
For example, given this sequential relationship, the following is the join
we're trying to achieve for ID B:
ID Table1.SeqNum Table2.SeqNum
B 1 66
B 2 67
B 3 99
Is there any way to logically join these two tables given the differing
Seqnum's?
Thanks in advanceSo we are talking about a simple join of the 2 tables on seqnum except that
there is an unknown offset between the actual numbering of seqnum?
Determine (query) what this offset is, store the result in a variable, and
incorporate the offset as needed in the join condition. Of course you know
that this is not a normalized relationship.
declare @.offset as int
select @.offset =
(select max(seqnum) from table2) - (select max(seqnum) from table1)
select
table1.id,
table1.seqnum,
table2.id,
table2.seqnum
from
table1
left join
table2
on table2.seqnum = (table1.seqnum + @.offset )
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
in
> Table1 logically links with the lowest Seqnum value in Table2 for each ID.
> For example, given this sequential relationship, the following is the join
> we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Cipher, try,
SELECT ...
FROM T1 AS T1A JOIN T2 AS T2A
ON T1A.id = T2A.id
AND (SELECT COUNT(*) FROM T1 AS T1B
WHERE T1B.id = T1A.id
AND T1B.seqnum <= T1A.seqnum) =
(SELECT COUNT(*) FROM T2 AS T2B
WHERE T2B.id = T2A.id
AND T2B.seqnum <= T2A.seqnum)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM Table1 t1
INNER JOIN (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table2 t3
INNER JOIN table2 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
if the SeqNums in Table1 are not sequential, you have to repeat the dose for
Table1 as well:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table1 t3
INNER JOIN table1 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t1
INNER JOIN (
SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
FROM Table2 t5
INNER JOIN table2 t6
ON t5.SeqNum <= t6.SeqNum
GROUP BY t5.ID, t5.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
Jacco Schalkwijk
SQL Server MVP
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Let's hope he doesn't have 1M rows or this join would occupy his server's
CPU all night long.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM Table1 t1
> INNER JOIN (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table2 t3
> INNER JOIN table2 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
> if the SeqNums in Table1 are not sequential, you have to repeat the dose
for
> Table1 as well:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table1 t3
> INNER JOIN table1 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t1
> INNER JOIN (
> SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
> FROM Table2 t5
> INNER JOIN table2 t6
> ON t5.SeqNum <= t6.SeqNum
> GROUP BY t5.ID, t5.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
the
value
each
the
>|||Thanks Itzik, this works perfectly.
I was going down the road of using cursors and programmatically replicating
the relationship logic but this has saved me from that pain.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OkUfSSiZFHA.3984@.TK2MSFTNGP10.phx.gbl...
> Cipher, try,
> SELECT ...
> FROM T1 AS T1A JOIN T2 AS T2A
> ON T1A.id = T2A.id
> AND (SELECT COUNT(*) FROM T1 AS T1B
> WHERE T1B.id = T1A.id
> AND T1B.seqnum <= T1A.seqnum) =
> (SELECT COUNT(*) FROM T2 AS T2B
> WHERE T2B.id = T2A.id
> AND T2B.seqnum <= T2A.seqnum)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||With proper indexing on a million row table, it will take a number of
minutes, but nothing close to all night. But at least it provides the
required result.
Jacco Schalkwijk
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:uqwKEYiZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Let's hope he doesn't have 1M rows or this join would occupy his server's
> CPU all night long.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> for
> the
> value
> each
> the
>

Interesting issue

I tried to create a new SQL Agent job today and couldn't. Got this error msg
.
"Error 0: This server has been disconnected. You must reconnect to perform
this operation. The job was not saved." I got this message on every server I
connected to via EM, dozens - EXCEPT the ones that weren't default instances
.
My colleague had the same problem but no-one else in the team did. My
colleague and I have different PC builds in that we have XP SP2 and have SQL
2005 tools installed as well as 2000. Sensing the problem was something to
do with port 1433 as it only affected default instances, I changed our clien
t
connectivity protocols so that Named Pipes was above TCP/IP, and now we don'
t
have a problem. Strange. Anyone shed any light on why connecting to default
instance SQL Servers via EM using TCP/IP connections stops us saving new job
s
in SQL Agent? Many thanks.Solved it. New security software had a 'MS SQL Job Scheduling' intrusion
Prevention rule. When that was omitted, we could create jobs using TCP/IP
connections.
"Shirley" wrote:

> I tried to create a new SQL Agent job today and couldn't. Got this error m
sg.
> "Error 0: This server has been disconnected. You must reconnect to perform
> this operation. The job was not saved." I got this message on every server
I
> connected to via EM, dozens - EXCEPT the ones that weren't default instanc
es.
> My colleague had the same problem but no-one else in the team did. My
> colleague and I have different PC builds in that we have XP SP2 and have S
QL
> 2005 tools installed as well as 2000. Sensing the problem was something t
o
> do with port 1433 as it only affected default instances, I changed our cli
ent
> connectivity protocols so that Named Pipes was above TCP/IP, and now we do
n't
> have a problem. Strange. Anyone shed any light on why connecting to defaul
t
> instance SQL Servers via EM using TCP/IP connections stops us saving new j
obs
> in SQL Agent? Many thanks.|||Just out of curiosity, what kind of security software?|||Symantec.
"Curt" wrote:

> Just out of curiosity, what kind of security software?
>sql

Interesting issue

I tried to create a new SQL Agent job today and couldn't. Got this error msg.
"Error 0: This server has been disconnected. You must reconnect to perform
this operation. The job was not saved." I got this message on every server I
connected to via EM, dozens - EXCEPT the ones that weren't default instances.
My colleague had the same problem but no-one else in the team did. My
colleague and I have different PC builds in that we have XP SP2 and have SQL
2005 tools installed as well as 2000. Sensing the problem was something to
do with port 1433 as it only affected default instances, I changed our client
connectivity protocols so that Named Pipes was above TCP/IP, and now we don't
have a problem. Strange. Anyone shed any light on why connecting to default
instance SQL Servers via EM using TCP/IP connections stops us saving new jobs
in SQL Agent? Many thanks.Solved it. New security software had a 'MS SQL Job Scheduling' intrusion
Prevention rule. When that was omitted, we could create jobs using TCP/IP
connections.
"Shirley" wrote:
> I tried to create a new SQL Agent job today and couldn't. Got this error msg.
> "Error 0: This server has been disconnected. You must reconnect to perform
> this operation. The job was not saved." I got this message on every server I
> connected to via EM, dozens - EXCEPT the ones that weren't default instances.
> My colleague had the same problem but no-one else in the team did. My
> colleague and I have different PC builds in that we have XP SP2 and have SQL
> 2005 tools installed as well as 2000. Sensing the problem was something to
> do with port 1433 as it only affected default instances, I changed our client
> connectivity protocols so that Named Pipes was above TCP/IP, and now we don't
> have a problem. Strange. Anyone shed any light on why connecting to default
> instance SQL Servers via EM using TCP/IP connections stops us saving new jobs
> in SQL Agent? Many thanks.|||Just out of curiosity, what kind of security software?|||Symantec.
"Curt" wrote:
> Just out of curiosity, what kind of security software?
>

Inter-depending parameters

Hello all,
If I want to create a report where there're 3 parameters A, B, C.
While the list of displayed items of A depends on selected value on B & C.
list of displayed items of B depends on selected value on A.
list of displayed items of C depends on selected value on A also.
Is this possible?Sounds to me like you've got a circular dependancy.
If parameters B + C were governed from the selection in the A parameter then fair enough.
If the A parameter were governed from the selection in the B & C parameters then it would also be fair enough.
From what I can gather, you're suggesting that A is governed by B & C but then B & C is also then governed by A. This constanting updating of parameter lists may result in a report never actually getting run as the dependancy is cyclic.
I'd have a re-think about the logic involved and see if this is a clear picture of the situation as i'm a little confused.
DF
"Tnek" wrote:
> Hello all,
> If I want to create a report where there're 3 parameters A, B, C.
> While the list of displayed items of A depends on selected value on B & C.
> list of displayed items of B depends on selected value on A.
> list of displayed items of C depends on selected value on A also.
> Is this possible?

Integration services notification services

Does anybody know of a notification services using SSIS? Is it SendMail or otherwise? Is there a step-by-step practice on how to create one?Not sure I understand your question? Are you looking to trigger an SSIS package from Notification Services, or are you want to send a notification from the SSIS package?|||I'm hoping it's the later one - send the notification from the SSIS package.|||You can send an email by using the Send Mail Task in SSIS. Is that not what you are looking for?|||

That would possibly the same as DTS! Do you know of any step--by-step article on how to do this in SSIS?

Just out of curiosity, if I were to use SQL Server's Notification services, I'd be make use of SSRS(reporting services) as well, wouldn't I?

|||

If you search the forum for "Send Mail", you should find some links. Also, it's really not a very complicated task to use.

I'm not sure why you are tying Notifiation Services to Reporting Services (or Integration Services, for that matter). They are seperate products, and while they can interact with each other, they do it the same way any external application would interact. For example, SSIS can interact with Reporting Services directly by calling the web service to generate reports (just like any other external application).

|||The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Neither of which are related to SSIS though.

From SSIS look at using the Send Mail task, or using a Web Service to handle notifications.|||Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.|||

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=Send+Mail&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

The second result returned has a walkthrough of using a Send Mail Task inside a For..Each loop. I didn't go through the rest, but I'm sure there are more examples.

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example. Maybe it's because I'm faily new to MSDN.

BOL has some information on the Send Mail task inside SSIS.

http://msdn2.microsoft.com/en-us/library/ms142165.aspx|||

onamika wrote:

The reason I mentioned reporting services because I read at a MSDN article that Analysis services uses Repoorting server for Notification Services.

Perhaps the nomenclature is a problem here. Perhaps it should be "notification services" rather than (the product) "Notification Services".

I'd like to read that MSDN article because if that is what it says then it is lying. Analysis Services does not automatically use Reporting Services for anything. You can deliver reports on top of Analysis Services using Reporting Services - but that is something else entirely.

-Jamie

|||

onamika wrote:

Frankly, I don't see any SendMail tasks example or how create an example.

Its very very very very simple. Just try it - witha little perseverance you won't need any documentaiton.

onamika wrote:

Maybe it's because I'm faily new to MSDN.

Not sure what you mean by this. What has MSDN got to do with this? How can you be "new to MDSN"?

-Jamie

|||

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

|||

onamika wrote:

I'd like to stick to the point - question about "SendMail" please!

Ok, bonus question for you. This is mostly text based mail I see. How can I make this email HTML based?

Drop the Send Mail task in the Control Flow tool box onto the work surface. Double click on it to configure it. From there, I think you should be able to figure out what to put where.

As far as HTML e-mail (WHY? WHY? WHY?), please search this forum for examples. This has been discussed before.

As an aside - HTML is VERY BAD design, in my opinion. HTML should be left to Web servers to display. If I had it my way (and many in the security community) e-mails would be limited to TEXT ONLY. Anyway......... That's my rant for today.

Integration Services missing from Management Studio

I have installed SQL Server 2005. I am able to create and edit
Integration services packages from VS but the Management Studio does
not show a folder for Integration Services so I can manage the
packages from within the Management Studio itself. From what I can
tell from the documentation, I should be able to see and manage them
from within Management Studio. What am I missing?
(markthompson23@.gmail.com) writes:
> I have installed SQL Server 2005. I am able to create and edit
> Integration services packages from VS but the Management Studio does
> not show a folder for Integration Services so I can manage the
> packages from within the Management Studio itself. From what I can
> tell from the documentation, I should be able to see and manage them
> from within Management Studio. What am I missing?
To work with SSIS you need to use Business Intelligence Development
Studio, that is VS. There is not much you can do with SSIS from Mgmt
Studio.
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

Integration Services extraction/loading throughput/performance

I'm new to integration services.
I want to create a centralized reporting system for our customers. Some customers have up to 1,000 sites and some are expected to grow past 5,000 sites. The sites are running POS applications and I want to extract the POS sales data from these sites. Is it practical to expect that SSIS can handle the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress databases but the data is also available in XML format.
If it's practical for Integration Services to do this, what frequency is it possible to pull this data?
I realize that the amout of data is relative but just wondering if anyone is attempting to do this with integration services.
If not with integration services, then what method(s) are available and used to extract data from this many remote sites?

SSIS is a very good fit for this type of problem. Loading data from flat files/XML files/databases and inserting to a RDBMS is an every day task for SSIS and something it does very efficiently.

SSIS does not have any restriction on how often you pull the data. This decison depends on many many factors such as:

latency of data|||Thanks for the reply!

Do you know where I may find a case study or know of anyone else extracting data from this many sites? I really do expect some customers to grow to 5,000 sites. So before investing into this strategy I want to see if it's being done.

Thanks!
|||

I don't know of any case studies, no. Fundamentally, SSIS will be able to do this. Whether it will work in your situation is down to the factors that I mentioned, not the lack of functionality.

-Jamie

Integration Services

Is there a way to give customers access to SSIS? They need to be able to create their own SSIS packages. Of course we have more then one customer so it would be nice to have modular security in place where they don't get to see customers abc and customer xyz packages. Only their own.

thanks!

Well, sure. But the only way I know of is to buy and install the SQL Server 2005 client tools, since the SSIS designer is essentially Visual Studio. I don't know if there is a SKU for just the client tools though, or if you have to buy one of the SQL Server editions.This page on MSDN has some information about the various editions.

You might check the licensing to see if you can give access to SSIS through Terminal Services, if that makes sense for your environment. But I'm betting that they'll need their own copy of the client tools to use locally.

And SQL Server 2005 has very granular security. It may take some thought and a little effort to get set up the way you need it, but you should be able to secure almost any scenario you encounter.

Don

|||

but how? when I connect as the sysadmin to SSIS there is no way to create users and set permissions. Do you set the permissions in a different location?

|||

You can set permissions using Management Studio, another of the client tools. Or you can write T-SQL code to do the same thing and run it either using the command line SQLCMD tool or through Management Studio.

But I'm not sure I understand. You aren't saying that you want your clients to create users and set permissions, are you? That's an admin function that should be tightly controlled.

Anyway, two different operations, two different tools.

Does that clarify?

Don

|||

In SSIS packages you can provide the security information. Use the property

EncryptAllWithPassword of a dtsx packages. This will allow the user to open or execute the package with the password. Niether they will be able to see the package without the password.

So basically based on customer you can create password and then you can provide them the respective password.

|||

What I am trying to say is this:

Customer A is an admin on his database, nowhere else. I need to be able to give customer A the ability to create SSIS packages for his database. At the same time I don't need him to be able to modify other peoples SSIS packages.

So, where do I set the permissions to give him the ability to create SSIS packeges?

|||

The SSIS packages can be developed regardless of the Sql server. Its not must to have the SQL Server installed on the same machine to develop package.

Also you can save SSIS packages in two ways one if after you created a SSIS packages you can export it to SQL Server Storage or you can save it on File System as well. Both way you will be able to execute the packages.

There is no need to give any one ability to create SSIS packages. He/She can create packages on his/her machine. So that no person will be able to see others packages because they will be on there machine also.

Please read the link... it will help you to do what you want

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx