Transactions problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transactions problem

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2010-02-22 : 08:00:51

When i am adding roles on a xp machine, using transactions of SQL it is working fine.
But i am facing problems with a VISTA machine, as the same script with transactions is not creating roles on VISTA machine.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-22 : 11:28:38

Can you give us some insight into the situation? Is there an error thrown? What are the SQL Versions on the two machines?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

boreddy
Posting Yak Master

172 Posts

Posted - 2010-02-23 : 01:24:51

this procedure i am useing here
Create PROCEDURE [dbo].[spGrantPermissions]
@Name varchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @String varchar(1000),
@String1 varchar(1000),
@String2 varchar(1000),
@UserIP varchar(50)
Begin try
begin transaction

SET @String1 = ' CREATE USER ['+ @Name +']'
EXEC (@String1)
SET @String1 = ' EXEC sp_addrolemember'+ '''' + 'db_owner' + '''' + ',' +'[' + @Name +']'
EXEC (@String1)
Commit Transaction
End try
Begin catch
rollback transaction
end catch

END

the below is the exicution(in xp os it is working fine(adding the roles) but in vista it is not working(not adding the roles))

declare @Host varchar(200)
set @Host = (select host_Name())
set @Host = @Host+ '\ASPNET'
--one execution
exec spGrantPermissions @Host

--second executiopn
set @Host = 'NT AUTHORITY\NETWORK SERVICE'
exec spGrantPermissions @Host

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources