Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels

|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels
|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does it locks database/tables when Only Select query comes in SQLTransaction

Hi,

I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.

So, Does it locks database/tables when Only Select query comes in SQLTransaction.

If you have another successful way of doing this, Please suggest.

Thanking you.

tatsA select typically only takes shared lock and releases the lock as soon as the select is done. If you want to an exclusive lock, you will have to use "tablockx" hint. Please see book online for detail.|||Thank you for your suggestion.|||

Just to top it....

SELECT COL1 FROM TBL2 WITH(NOLOCK) WHERE COL2 = @.VAL

|||You should avoid this technique or be sure to understand what is going on behind the scenes. Many people beginning with TSQL somewhere read that (NOLOCK) will not produce any problems, because one can read without careing about any locking, on the one side this is true, but you can′t be sure to have the most recent data, because you will do dirty ready on the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

What are you trying to accomplish? Every statement locks something, at least the schema so you can't drop a table while the user is executing a statement :)

Also, EVERY statement is in a transaction already, the difference between starting one using the client and just the implicit one that starts for every statement is that you might never reach the code to close it if the user gets bored and decides they want to go to lunch.

What locks are taken and how long locks are held is determined by the isolation level. The default is read committed (though your client defautl CAN BE DIFFERENT!) Note too that all of the locks I will talk about are shared locks for data that you read NOT updated data. Updated data requires locks to be held until the end of a transaction.

READ UNCOMMITTED - No locks issued or obeyed other than schema locks

READ COMMITTED - Locks are (generally) taken only as long as needed to protect the lowest level thing you are working with (like a row in a table.) Generally speaking it "crabs" through the objects grab a lock, release a lock. Another user can modify data you have already read

REPEATABLE READ - LIke Read Committed, only it doesn't release lock it takes. Prevents another user from modifying data you have already read, doesn't prevent user from creating new rows (referred to as Phantoms).

SERIALIZABLE - Like Repeatable Read, only it also places a lock on Ranges of data, so if you say WHERE between 0 and 100, a range lock is places on 0 and 100 to say no other user can create data in that range either.

SNAPSHOT (2005) - Readers look at only committed data, and if other users have locked portions of the data, they will only see the database as it was when they started their command. Really cool, but uses more tempdb than would otherwise.

So the only real concern about executing your select in a transaction is that you will run the risk of a user failure locking other users until cleanup occurs and the connection is noticed to be stale and not in use.

I generally would suggest that you execute your transactions in a single batch and put the BEGIN TRAN and COMMIT TRAN in the same batch if possible and using TRY...CATCH blocks around statements if you have 2005. Then you lower the risk of hanging a transaction. The connection might still exist, but the commit/rollback will certainly execute.

Does a checkpoint file record a package's state

The state of a package at any one point in time is determined by the values in all of its user variables as these are the only properties in the package that can be changed and persisted throughout the runtime of the package.

Is the package's state stored in a checkpoint file in the event that the package fails? In other words are the user variable values stored in the checkpoint file?

Thanks

Jamie

Can task A update an enivornment variable i.e. persist the state outside the package.

Out of interest viw does package B need to update a variable in package A.

|||

SimonSa wrote:

Can task A update an enivornment variable i.e. persist the state outside the package.

Not in this scenario!

SimonSa wrote:

Out of interest viw does package B need to update a variable in package A.

[I presume that word was "why" :)]

That's a question that needs to be answered at length - preferably over a few beers!

-Jamie

|||

Jamie,

Can you ellaborate on what this variable (PkgA) represents (I.E., some sort of count, threshold, clock/time, etc...)?

We've had scenerios where a variable needed to be updated and couldn't be done using a GLOBAL_ENV_VAR or some other global, it needed to be updated per client(job), and client(job) specific function. So what we did is created and modified text-based files to update the variables values.

Say for instance, you had to update variableA (which was a boolean true/false), you could create/modify a file specifc to that instance/job run, and change the "variableA=" to true of false. Then you could always refer back to that specific file to lookup the values - like creating your own checkpoint files.

|||

Jason,

The particular one one in question was a variable called PackageStack which maintains a comma seperated list of packages that have been called, in the order that they have been called.

Each package has an OnPreExecute event that "pushes" the name of the package into the PackageStack variable and an OnPostExecute event that "pops" it off.

-Jamie

|||

Similarly, you could log this using a log provider, however, you will run into the same problem I am facing - with that logging bug and only logging the immediately inherited pkg!!!

Maybe, whom ever answers this post can solve both our problems...

|||

Yes, the values of the variables are stored in the checkpoint file.

K

|||

I disagree, I believe this is only true for simple variables. I have stored a recordset in a variable and this wasn't stored in the checkpoint file.

Either object types aren't supported or there is something about what is saved.

What happens if a variable is changed in a loop. What is stored in the checkpoint, the variable value at the start of the loop or the value at the point of failure.

|||

Correct, object types are not stored.

ForEach loops start at the beginning of the collection.

For loops continue where they left off.

They all key off of the variables.

K

|||

KirkHaselden wrote:

Correct, object types are not stored.

Why not?

Will this change?

-Jamie

|||

I've just come across a differrent "problem" with this.

I have 2 packages pkgA and pkgB. pkgA calls pkgB

pkgB has 2 tasks taskA and taskB. taskA is a script task that updates a variable (varA) in pkgA. taskB is a data-flow that is failing.

pkgB uses checkpoints

Now, I can correct the error that causes taskB to fail but the problem I have is that when I rerun it using the checkpoint file taskA will not execute and therefore varA will have the wrong value in it.

There isn't a way around this problem currently but is there a way that this could be catered for in the future using checkpoint files? i.e. The checkpoint file for pkgB would have to store the value of varA even though varA is in pkgA.

-Jamie

Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

HERE IS MY PROCEDURE

DECLARE @.MineID int,
@.MineName varchar(80),
@.MineDescription [varchar](80) ,
@.MineLocation varchar(80),
@.Country varchar(80),
@.Northing float,
@.Easting float,
@.Elevation float,
@.Latitude float ,
@.Longitude float,
@.MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo
WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @.MineName
,MineDescription = @.MineDescription
,MineLocation = @.MineLocation
,Country =@.Country
,Northing = @.Northing
,Easting = @.Easting
,Elevation = @.Elevation
,Latitude = @.Latitude
,Longitude = @.Longitude
,MineLogo =@.MineLogo
where MineId = @.MineID

END
IF @.MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@.MineID
,@.MineName
,@.MineDescription
,@.MineLocation
,@.Country
,@.Northing
,@.Easting
,@.Elevation
,@.Latitude
,@.Longitude
,@.MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSORYou don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))

insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine(id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1|||Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @.BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help|||

Quote:

Originally Posted by Sally1053

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test

Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
--
Concerning your particular problem with
IF @.BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@.BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @.BlasterID)