Help on Registering SQL Server in Enterprise Admin

Dear All
We want to monitor SQL job history for 50+ servers using id which doesn't
have admin priviliges on server and database. On database front I can
achieveit by assiging TargetServer role but I want to add all servers in
Enterprise Administartor console. What are minimum rights we require to
register server in Enterprise Admin for Windows 2000 & 2003 Servers?
Rahul> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
You will have EM installed on your workstation and to know a login and
password to register the SQL Server
"rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message
news:36C3F6A6-BBF7-41A1-A97B-E5EC3A6AD6F9@.microsoft.com...
> Dear All
> We want to monitor SQL job history for 50+ servers using id which doesn't
> have admin priviliges on server and database. On database front I can
> achieveit by assiging TargetServer role but I want to add all servers in
> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
> --
> Rahul|||The minimum 'rights' to register a server are to be able to log into the ser
ver. It is not necessary to have permissions for any database.
However, in order to view the job history in EM (SQL 2000), I think that it
will be necessary to be in the sysadmin server role for all servers. (I'm a
little fuzzy on this...)
For Management Studio (SQL 2005) you must be in the SQLAgentReaderRole (or s
ysadmin) in order to view Job history of all the jobs.
If you used Query Analyzer and had some form or script, it would be necessar
y to only have SELECT permissions on the various sysjob... tables in the msd
b databases.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message news:36C3F6A6-BBF7-41A1-A97B-
E5EC3A6AD6F9@.microsoft.com...
> Dear All
>
> We want to monitor SQL job history for 50+ servers using id which doesn't
> have admin priviliges on server and database. On database front I can
> achieveit by assiging TargetServer role but I want to add all servers in
> Enterprise Administartor console. What are minimum rights we require to
> register server in Enterprise Admin for Windows 2000 & 2003 Servers?
>
> --
> Rahul|||Dear All
Thanks for your inputrs. But how I will give permission to domain user to
log on to SQL Server? Do you mean by adding user in SQL Login? Also when we
add user to SQL it asks for "Database" name, like master etc or user created
like test,test1 etc.
So which database we need to assign while adding user to sql login and what
will be security implications of same? I.e.if we add user with database as
master or user database test then what rights the user will have on these
databases'
Rahul
"Arnie Rowland" wrote:
[vbcol=seagreen]
> The minimum 'rights' to register a server are to be able to log into the s
erver. It is not necessary to have permissions for any database.
> However, in order to view the job history in EM (SQL 2000), I think that i
t will be necessary to be in the sysadmin server role for all servers. (I'm
a little fuzzy on this...)
> For Management Studio (SQL 2005) you must be in the SQLAgentReaderRole (or
sysadmin) in order to view Job history of all the jobs.
> If you used Query Analyzer and had some form or script, it would be necess
ary to only have SELECT permissions on the various sysjob... tables in the m
sdb databases.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "rahulpt" <rahulpt@.discussions.microsoft.com> wrote in message news:36C3F6
A6-BBF7-41A1-A97B-E5EC3A6AD6F9@.microsoft.com...

Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
ChrisYou are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)[vbc
ol=seagreen]
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>[/vbcol]|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> get
only.
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)[vbc
ol=seagreen]
>|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill

>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message
>news:cohev5$avs$1@.news01.intel.com...
with the values[vbcol=seagreen]
>passed.
http://www.microsoft.com/sql/techin...tdoc/2000/books
.asp[vbcol=seagreen]
overview on what's[vbcol=seagreen]
Procedures in MSDB databases[vbcol=seagreen]
problem is that I can't[vbcol=seagreen]
written on 1 line[vbcol=seagreen]
>only.
-[vbcol=seagreen]
(8),@.Z_BranchID_Z int,@.Z_VS_Z[vbcol=seagreen]
int=null,@.ShowCollections[vbcol=seagreen
]
RTblClassExtension values[vbcol=seagreen]
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,
@.ShowCollections)
is doing...[vbcol=seagreen]
>
>.
>

Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
ChrisYou are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm currently auditing some SQL servers to have an overview on what's
> > running/existing in the different databases.
> >
> > On one of the server, I found User's Stored Procedures in MSDB databases
> > (which, I think is already not a good idea). My problem is that I can't
> get
> > the usage of the SPs.
> > All are built on the same model : all the code is written on 1 line
only.
> >
> > Here is the full code of one the SP:
> >
> > -- beginning of the code --
> > create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> > int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> > bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
> >
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
> > GO
> >
> > -- end of the code --
> >
> > If someone would have any clue abotu what's this SP is doing...
> >
> > Thanks,
> > Chris
> >
> >
>|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill
>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message
>news:cohev5$avs$1@.news01.intel.com...
>> You are trying to insert into the RTblClassExtension
with the values
>passed.
>> What is that you would like to know here
>> --
>> HTH,
>> Vinod Kumar
>> MCSE, DBA, MCAD, MCSD
>> http://www.extremeexperts.com
>> Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books
.asp
>> "Chris V." <tophe_news@.hotmail.com> wrote in message
>> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I'm currently auditing some SQL servers to have an
overview on what's
>> > running/existing in the different databases.
>> >
>> > On one of the server, I found User's Stored
Procedures in MSDB databases
>> > (which, I think is already not a good idea). My
problem is that I can't
>> get
>> > the usage of the SPs.
>> > All are built on the same model : all the code is
written on 1 line
>only.
>> >
>> > Here is the full code of one the SP:
>> >
>> > -- beginning of the code --
-
>> > create procedure <SP_Name> (@.IntID binary
(8),@.Z_BranchID_Z int,@.Z_VS_Z
>> > int,@.IconLibrary varchar(255)=null,@.IconID
int=null,@.ShowCollections
>> > bit=null,@.Z_VE_Z int=2147483647) as insert
RTblClassExtension values
>> >
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,
@.ShowCollections)
>> > GO
>> >
>> > -- end of the code --
>> >
>> > If someone would have any clue abotu what's this SP
is doing...
>> >
>> > Thanks,
>> > Chris
>> >
>> >
>>
>
>.
>sql

Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
Chris
You are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>
|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.[vbcol=seagreen]
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> get
only.
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
>
|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill

>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message[vbcol=seagreen]
>news:cohev5$avs$1@.news01.intel.com...
with the values[vbcol=seagreen]
>passed.
http://www.microsoft.com/sql/techinf...doc/2000/books
..asp[vbcol=seagreen]
overview on what's[vbcol=seagreen]
Procedures in MSDB databases[vbcol=seagreen]
problem is that I can't[vbcol=seagreen]
written on 1 line[vbcol=seagreen]
>only.
-[vbcol=seagreen]
(8),@.Z_BranchID_Z int,@.Z_VS_Z[vbcol=seagreen]
int=null,@.ShowCollections[vbcol=seagreen]
RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,
@.ShowCollections)[vbcol=seagreen]
is doing...
>
>.
>

Help needed to setup replication from SCRATCH

Hi,
I have tried and tried to solve my problems without any success.
Here I go...
I have 2 SBS 2000 Servers (Server A and Server B) in different offices 20
miles from each other.
SQL SP4 on each with ISA Ports open to 14446 (dont like 1433).
ODBC connects from my workstation to each server on port 14446 so
communication to database is ok.
I have only one database (800mb) in size that I need MERGE replication.
I have setup the client utility (in think) in server A (subscriber) with an
alias name of server B. Server B (distributor) simply wont talk to server
A? even though I know the connection is valid.
What I need is from scratch a walkthough on how to setup replication on both
servers. I have searched the net for weeks now, posted questions and read MS
articles but I'm not getting anywhere...
Please help.
TIM
Hopefully this article will help clarify things a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi,
Have read that article and undertand most of it but there are 2 things
missing.
1. Client Network setup (alias setup on the subscriber). Do you have to do
this on the publisher as well?.
2. FTP...Seen a lot of this. Do I need to setup FTP on both servers as
well!...
Sorry but its not sinking in yet...
"Paul Ibison" wrote:

> Hopefully this article will help clarify things a bit:
> http://www.replicationanswers.com/InternetArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think these questions really relate to push/pull. The article refers to a
pull subscription. For push, the merge agent needs to see the subscriber so
set up the alias on the publisher. I'm reasoning this out as I don't have a
test environment here at present. For the FTP, the ports need to be opened
in your firewalls, but the FTP snapshot files reside on the publisher ie the
FTP ServerName referred to is the publisher's server name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK thanks.
Whats the correct way to complete the client network utility (IP/Computer
Name/Server Name etc).
Regards
"Paul Ibison" wrote:

> I think these questions really relate to push/pull. The article refers to a
> pull subscription. For push, the merge agent needs to see the subscriber so
> set up the alias on the publisher. I'm reasoning this out as I don't have a
> test environment here at present. For the FTP, the ports need to be opened
> in your firewalls, but the FTP snapshot files reside on the publisher ie the
> FTP ServerName referred to is the publisher's server name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There's an example on my article - the only thing to be careful of is to set
it up for TCP/IP and use the actual server name as the alias name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply but I've tried everything now and i'm still not any
connection from the subscriber to the publisher!!!!!.
I'm gonna spend a few more days on this and then give up...........
Thanks
TIM
"Paul Ibison" wrote:

> There's an example on my article - the only thing to be careful of is to set
> it up for TCP/IP and use the actual server name as the alias name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It sounds like firewall issues ie ports not opened, or opened in one
direction only, or your IP address not being in the allowed list etc.
Can you get a connection using SSMS/EM using the alias?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
On the ISA firewall i have set port 14446 as my default sql server port and
disabled 1433. I then published the server using this rule to forward to the
external interface (2 nics). I then set a new protocol def (14446) and bound
the rule to this.
Now I can access sql database using ODBC from my XP using this
port/password/username so I can only assume that the correct ports and comms
are getting through.
Now my SBS server computer name is CLIFT.Local and my alias for SQL is
CLIFTSERVER.
I have changed the service startup accounts to a user i created in AD giving
FULL admin rights. All services including the agent is running fine.
I have created a distributor/publisher on CLIFT server.
I have created the alias to CLIFTSERVER on my BROADSERVER (other SBS Server)
and in the client network utility it asks for computer name and server name
but I'v every possible combination and it still wont connect.
My routers are set to not respond to PING but when I disable this function
PING works so there is a connection there.
Is there anything else I need to do before I setup the subscriber?.
Please help...
"Paul Ibison" wrote:

> It sounds like firewall issues ie ports not opened, or opened in one
> direction only, or your IP address not being in the allowed list etc.
> Can you get a connection using SSMS/EM using the alias?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||OK - for the replication setup, the alias needs to be CLIFT. The server name
needs to be the IP address and you also specify the port, the network lib is
tcp/ip. Once set up like that, just try to register in SSMS using CLIFT as
the server name to test.
For the initialization you'll need to set up FTP or do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help needed on SQLServer , Error 18456

Hi All,

I have tried accessing a remote database in one of by stored procs using linked servers and also using OpenDataSource method.
In both the cases , I am getting login failed error.

Following is the stored proc :

CREATE PROCEDURE TEST AS

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=blrkec3432s;User ID=xyz;Password=xyz').LMC.dbo.STATE
GO

It works fine if the userid is 'sa'

Could anyone please tell me the reason for this.

Thanks,
ShanthiResolution from SQLMAG link (http://www.winnetmag.com/SQLServer/Article/ArticleID/8992/8992.html)

Help Needed on DTC

Hi,
I'm having problems while running distributed transactions on two different servers. I
have two servers 'EPOOL5' and 'EPOOL9' respectively running SQl servers.
I have linked both the servers using sp_addlinkedserver 'EPOOL5' and sp_addlinkedserver 'EPOOL9' on EPOOL9 and EPOOL5 respectively. I have created a user PCRCCTEST on both the server databases. The databases being VDPMASTER on EPOOL5 and VDPMASTERTEST on EPOOL9. I have given the user PCRCCTEST proper privileges to access the tables in the respective databases.
I have started DTC on both the servers EPOOL9 and EPOOL5.

Now when I execute a query say select * from epool9.vdpmastertest.dbo.project_master from EPOOL5, the query is successfully executed and the rows are retrieved. When I insert rows similary, the rows are getting inserted. I am facing a problem when I try to run transactions. i.e I have created a stored procedure 'test' on VDPMASTER database on EPOOL5. 'Project_Master' being the table in EPOOL9 VDPMASTERTEST database. The user PCRCCTEST has privileges to insert data into Project_Master.

create procedure test
as
begin distributed transaction

insert into epool9.VDPMASTERTEST.dbo.Project_Master
(project_id,quality_id,project_name,project_client ,start_date,end_date,
project_active,project_master_update_flag,project_ master_updated_by, project_master_updated_on)
values (447,'3433','manufacturing','firstbank','02/22/2002','02/23/2003','Y','I',1,getdate())

if @.@.error=0
begin
commit transaction
print 'commitTest'
end
else
begin
print 'rollbackTest'
rollback transaction
end

When I execute this stored procedure from EPOOL5 server, I get the following error.

Server: Msg 7392, Level 16, State 2, Procedure test, Line 5
Could not start a transaction for OLE DB provider 'SQLOLEDB'.
[OLE/DB provider returned message: Only one transaction can be active on this session.]

I would be grateful if you could help me on this.
Thanks in advance
P.C. VaidyanathanI believe that this error is caused by nested transactions

begin tran
begin tran

commit tran
commit tran

To prevent your stored procedure from creating nested transactions you can check @.@.TRANCOUNT to see if a BEGIN TRAN has already been issued.

CREATE PROCEDURE test
as
DECLARE @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SET @.tfTran = 1
BEGIN DISTRIBUTED TRANSACTION
END
ELSE
SET @.tfTran = 0

INSERT INTO epool9.VDPMASTERTEST.dbo.Project_Master
(project_id,quality_id,project_name,
project_client,start_date,end_date,
project_active,project_master_update_flag,
project_master_updated_by, project_master_updated_on)
values
(447,'3433','manufacturing','firstbank',
'02/22/2002','02/23/2003','Y','I',1,getdate())

IF @.@.error=0 BEGIN
IF (@.tfTran = 1) BEGIN
COMMIT TRAN
PRINT 'Commit Test'
END
END
ELSE BEGIN
IF (@.tfTran = 1) BEGIN
ROLLBACK TRAN
PRINT 'Rollback Test'
END
END

Help Needed Configuring ODBC

In the Office we have a Win2000 Server running MS SQL Server 2000. I know
the servers external IP address (xxx.xxx.xxx.xxx) and we have a sub-domain
set up (subname.mycompserver.com). I have a login and password into the
server as well as the SQL login and password. The firewall is open on port
1433. From my office PC, I can access SQL, using ODBC, with Access or my
Perl programs.
At home, I'm running WinXP Pro. Although it shouldn't have been necessary,
I installed MS SQL Server client. I connect to the Internet using a Comcast
cable modem and do not have a static IP address. Whenever I try to set up
ODBC to access my office's SQL server using ODBC Data Source Administrator
/ Add SQL Server, it fails with the following error message:
Connection Failed
SQL State: '01000'
SQL Server Error: 10060
[Microsoft][ODBC Server Driver][TCP/IP Sockets]Connection Open (Connect())
Connection Failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC Server Driver][TCP/IP Sockets]SQL Server does not exist or
access denied.
Is it possible to establish this ODBC connection into SQL and, if so, what
am I doing wrong. Thanks.
Fred
Hello Fred,
The only thing I can think is that firewall at your work is blocking 1433. The Connect() message means that a TCP/IP
socket was not created. From your office's server machine go to http://aboutmyip.com and see if port is open.
Fred Goldberg wrote:

> In the Office we have a Win2000 Server running MS SQL Server 2000. I know
> the servers external IP address (xxx.xxx.xxx.xxx) and we have a sub-domain
> set up (subname.mycompserver.com). I have a login and password into the
> server as well as the SQL login and password. The firewall is open on port
> 1433. From my office PC, I can access SQL, using ODBC, with Access or my
> Perl programs.
> At home, I'm running WinXP Pro. Although it shouldn't have been necessary,
> I installed MS SQL Server client. I connect to the Internet using a Comcast
> cable modem and do not have a static IP address. Whenever I try to set up
> ODBC to access my office's SQL server using ODBC Data Source Administrator
> / Add SQL Server, it fails with the following error message:
> Connection Failed
> SQL State: '01000'
> SQL Server Error: 10060
> [Microsoft][ODBC Server Driver][TCP/IP Sockets]Connection Open (Connect())
> Connection Failed:
> SQL State: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC Server Driver][TCP/IP Sockets]SQL Server does not exist or
> access denied.
> Is it possible to establish this ODBC connection into SQL and, if so, what
> am I doing wrong. Thanks.
> Fred
|||I truly appreciated your prompt reply to my question. Your answer
implies that this is doable.
I connected to our server using Remote Desktop, brought up IE and ran
http://aboutmyip.com. And, as you expected, Port 1433 is closed. My MIS
guys swore they opened it for me.
I'll have to get the port opened and try again. Thanks so much.
Fred
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!