How to setup Parent>>Child>>Child relationship...?

Hello,

SQL newby looking for some advice. I have created the three tables below. XXParent is the master table, XXParentChild is the child table to XXParent and it should have a one-to-many relation to its parent. XXParentChildChild is the child table to XXParentChild, and it will likewise have a one to many relation to XXParentChild. In effect one XXParent row can have many XXParentChild rows assigned to it and one XXParentChild row can have many XXParentChildChild rows assigned to it.

What I'm missing is how to create the table so that once I've entered a row in XXParent, I can insert multiple rows in XXParentChild and subsequently insert multiple rows in XXParentChildChild for each of its parent rows, while maintaining referential integrity.

First, not sure what record id style to use, whether IDENTITY, or UNIQUEID, etc..
Second, not sure how to set up the FK's and Relationships between the tables.

Any advice appreciated greatly!!

Thanks in advance!

CREATE TABLE [XXParent] (

[XXSuiteID] [int] IDENTITY (1, 1) NOT NULL ,

[XXDateRun] [datetime] NULL ,

[XXStartTime] [datetime] NULL ,

[XXEndTime] [datetime] NULL ,

[XXsSucceeded] [int] NULL ,

[XXsWarned] [int] NULL ,

[XXsFailed] [int] NULL ,

[XXMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXClientMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [PK_XXSuite] PRIMARY KEY CLUSTERED

(

[XXSuiteID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [XXParentChild] (
[XXSuiteID] [int] NOT NULL ,
[XXID] [int] IDENTITY (1, 1) NOT NULL ,
[XXIDInternal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXTier] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XXStart] [datetime] NULL ,
[XXEnd] [datetime] NULL ,
[XXWFBTime] [datetime] NULL ,
[XXWFBCalled] [int] NULL ,
[XXSearches] [int] NULL ,
[XXSearchesTime] [datetime] NULL ,
[XXResult] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [XXParentChildChild] (

[XXID] [int] NOT NULL ,

[XXMssgType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[XXMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GOAnswered my own question:

CREATE TABLE XXParent (
XXSuiteID int IDENTITY (1, 1) NOT NULL,
XXDateRun datetime NULL ,
XXStartTime datetime NULL ,
XXEndTime datetime NULL ,
XXsSucceeded int NULL ,
XXsWarned int NULL ,
XXsFailed int NULL ,
XXMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXClientMachine varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLogin varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXLabel varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT PK_XXSuite PRIMARY KEY CLUSTERED
(
XXSuiteID
) ON PRIMARY
) ON PRIMARY
GO

CREATE TABLE XXParentChild (
XXID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
XXSuiteID int NOT NULL ,
XXIDInternal varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXDescription varchar (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXTier text COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXNo varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXStart datetime NULL ,
XXEnd datetime NULL ,
XXWFBTime datetime NULL ,
XXWFBCalled int NULL ,
XXSearches int NULL ,
XXSearchesTime datetime NULL ,
XXResult varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXSuiteID) REFERENCES XXParent(XXSuiteID)
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO

CREATE TABLE XXParentChildChild (
XXCHILDID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
XXID int NOT NULL ,
XXMssgType varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
XXMessage varchar (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FOREIGN KEY (XXID) REFERENCES XXParentChild(XXID)
) ON PRIMARY
GOsql

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and[vbcol=seagreen]
> and
> query
> ServerA,
> allow
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be[vbcol=seagreen]
>

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank you
Steve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>
|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'[vbcol=seagreen]
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and
> and
> query
> ServerA,
> allow
>
|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject[vbcol=seagreen]
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be
>

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > background sql2k on nt5.
> >
> > sorry to post this again. didn't get a good response for my last post
and
> > really need some advice on this.
> >
> > I have read that subject in SQL BOL.
> > and it doesn't make much sense to me.
> >
> > If I were to have a NT group called sqladmins, and i assign Bob, Chris,
> and
> > Doug to that group.
> > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> query
> > ServerB (any database, such as sysobjects in master database) from
> ServerA,
> > but not for other users not belong to that group.
> > What should I do?
> >
> > the bottom line is, I'd like to set up linked server securely and only
> allow
> > certain users in certain NT group to use it. (since sa password will be
> > supplied when set up a linked server, i don't want any user to use an
> > established linked server as they were sa to other servers). is that
> > possible?
> >
> > Pls advise! Thank you
> >
> >
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> > Steve,
> >
> > Using sp_addlinkedserverlogin you could have four logins.
> >
> > 1-3 = Bob, Chris, and Doug as such:
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> >
> > 4 = for everybody else
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to
login
> as
> > themselves.
> >
> > Or, you could only use line 4 and then make the sysadmins group (Bob, et
> al)
> > also sysadmins on the other server. If you are letting them in as 'sa'
> then
> > they are being sysadmins. (Insert Here: Standard advice to not use the
> 'sa'
> > account.)
> >
> > Russell Fields
> >
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > > background sql2k on nt5.
> > >
> > > sorry to post this again. didn't get a good response for my last post
> and
> > > really need some advice on this.
> > >
> > > I have read that subject in SQL BOL.
> > > and it doesn't make much sense to me.
> > >
> > > If I were to have a NT group called sqladmins, and i assign Bob,
Chris,
> > and
> > > Doug to that group.
> > > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> > query
> > > ServerB (any database, such as sysobjects in master database) from
> > ServerA,
> > > but not for other users not belong to that group.
> > > What should I do?
> > >
> > > the bottom line is, I'd like to set up linked server securely and only
> > allow
> > > certain users in certain NT group to use it. (since sa password will
be
> > > supplied when set up a linked server, i don't want any user to use an
> > > established linked server as they were sa to other servers). is that
> > > possible?
> > >
> > > Pls advise! Thank you
> > >
> > >
> >
> >
>

How to set Tab as column separator in SQL*Plus?

Any advice or workarounds, greatly appreciated.
Cheers,
PeiOriginally posted by peisiong
Any advice or workarounds, greatly appreciated.

Cheers,

Pei
You could do this:

SQL> column tab new_value tab
SQL> select chr(9) tab from dual;

T
-


SQL> set colsep "&tab"|||[QUOTE][SIZE=1]Originally posted by andrewst
Thanks Andrew!!!|||Hi

I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

Any idea why?

Cheers,

Pei Siong|||Originally posted by peisiong
Hi

I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

Any idea why?

Cheers,

Pei Siong
There are spaces, as well as the delimiting TABs, because of the way SQL Plus formats the data WITHIN the columns, e.g.:

____DEPTNO|DNAME_________|LOC_________
----|-----|----
________10|ACCOUNTING____|NEW_YORK____
________20|RESEARCH______|DALLAS______
________30|SALES_________|CHICAGO_____
________40|OPERATIONS____|BOSTON______

(I have changed spaces to '_' and tabs to '|' so you can see better).

I'm not aware of any way to change this behaviour. A common way to get tab-delimited output without spaces is to select it that way:

SELECT deptno||CHR(9)||dname||CHR(9)||loc AS record
FROM dept;

RECORD
---------------------
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

Use SET TRIMSPOOL ON to remove the trailing spaces on the last column.|||Hi andrew,

Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

Cheers,

Pete|||Originally posted by peisiong
Hi andrew,

Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

Cheers,

Pete
Not as far as I know.

How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!

How to set sheetname on an Excel destination component ?

Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

'runtime Exception here

destDesignTime.SetComponentProperty("OpenRowSet", "functions")

Guess time!

If you post the error details, it normally helps. I'll guess at error HResult 0xC0204006, some notes - http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html

Properties are case sensitive, and it is called OpenRowset not OpenRowSet.

How did i do?

|||Yep, the case sensitivity was it. Thanks!