Tables Protect In Sqlserver 2000

Jul 31, 2003

I have some tables in the employee database, this database created from sql sever 2000. I build a employee management application by C# and sqlserver 2000.

My goal is after design complete the empployee database by sqlserver 2000, any users can not modify my tables and unkonw table's structure.
help me please

thanks and reagards

View 2 Replies

How To Protect Errorlogs To Tables From Rollbacks

Apr 5, 2002

Hey!

This post contains the code for this thread: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14475

It deals with the problem how to prevent log actions in long running batch jobs from being rolled back. It was heavily inspired by Andy Pope´s approach to error handling (http://www.sqlteam.com/item.asp?ItemID=2290) and in fact you will see much of his code here.

The code:

This procedure dynamically opens a second connection in parallel to the existing connection of the calling procedure using SQL-DMO. So the second connection runs without the scope of transaction of the calling procedure. So no action you take here is rolled back in case the calling proc fails. So be careful! Keeping data integrity is your job here and you could do many weird things to your database.
The procedure dynamically adds a user function that if called just would return the object token of the new DMO connection. So any piece of code in the same batch could reuse the exisiting connection.

LogConstructor
CREATE PROCEDURE LogConstructor AS

if exists (select * from sysobjects where id = object_id (N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject

DECLARE @Error INT
DECLARE @ErrorMsg VARCHAR(255)
DECLARE @oSQLServer INTEGER
DECLARE @Source VARCHAR(255)
DECLARE @Return INTEGER
declare @dynsql nvarchar(3000)

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Create Function with server object
select @dynsql = N'CREATE Function MFF_GetLogObject () RETURNS INT AS BEGIN RETURN ' + cast(@oSQLServer as varchar) + N' END'
EXEC sp_executesql @dynsql

return

OA_Error:
-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'
print @ErrorMsg
return
GO
The next procedure just drops the DMO connection and also drops the user function as the token is invalid by now. This proc should be called within the same batch as the constructor to clean things up properly.

LogDestructor

CREATE PROCEDURE MFP_LogDestructor AS

declare @lo int
select @lo = dbo.MFF_GetLogObject()
exec sp_OADestroy @lo

if exists (select * from sysobjects where id = object_id(N'dbo.MFF_GetLogObject')
and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function dbo.MFF_GetLogObject
GO

View 2 Replies View Related

Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express

Sep 20, 2006

Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))

Insert Data
insert into testunicode (Bez)values('��')
with 2 Unicode characters
�� = 0x2300
�� = 0x2122

Selecting the data
select Bez from testunicode
I see
"?��"

�� = 0x2122 is ok but instead of 0x2300 there is 0x3f

When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'��')

and select again voila i see
"��"
Does anyone have an idea?

Thanks