Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
Peter
Peter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>
Showing posts with label instead. Show all posts
Showing posts with label instead. Show all posts
Wednesday, March 7, 2012
DDL Trigger to update Instead Of Insert trigger
Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>
>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>
>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>
>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>
>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>
DDL Trigger to update Instead Of Insert trigger
Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name => @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name => @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>
Tuesday, February 14, 2012
DB-Library vs. OLE DB
Are there any benefits in choosing OLE DB interface instead of using the native DB-Library connectivity?
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...
> Are there any benefits in choosing OLE DB interface instead of using the
> native DB-Library connectivity?
> What is recommended?
> Thanks,
> Adam|||Thanks Brian! Any known loss of available functionality in 2000 that can't be accessed DB-Library?
Adam|||Adam,
> Any known loss of available functionality in 2000
> that can't be accessed DB-Library?
Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX® Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?url=/library/en-us/bldgapps/ba_highprog_56pc.asp?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
--
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||=?Utf-8?B?QWRhbSBUaWNrdGlu?= (anonymous@.discussions.microsoft.com) writes:
> Thanks Brian! Any known loss of available functionality in 2000 that can't
> be accessed DB-Library?
Quite a bit.
I have a list on
http://www.sommarskog.se/mssqlperl/mssql-sqllib.html#restrictions_with_new_datatypes
This text is in the context of two Perl modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...
> Are there any benefits in choosing OLE DB interface instead of using the
> native DB-Library connectivity?
> What is recommended?
> Thanks,
> Adam|||Thanks Brian! Any known loss of available functionality in 2000 that can't be accessed DB-Library?
Adam|||Adam,
> Any known loss of available functionality in 2000
> that can't be accessed DB-Library?
Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX® Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?url=/library/en-us/bldgapps/ba_highprog_56pc.asp?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
--
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||=?Utf-8?B?QWRhbSBUaWNrdGlu?= (anonymous@.discussions.microsoft.com) writes:
> Thanks Brian! Any known loss of available functionality in 2000 that can't
> be accessed DB-Library?
Quite a bit.
I have a list on
http://www.sommarskog.se/mssqlperl/mssql-sqllib.html#restrictions_with_new_datatypes
This text is in the context of two Perl modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
DB-Library vs. OLE DB
Are there any benefits in choosing OLE DB interface instead of using the nat
ive DB-Library connectivity?
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...
e accessed DB-Library?
Adam|||Adam,
Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?...>
p?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
Alan Brewer [MSFT]
Lead programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||examnotes (anonymous@.discussions.microsoft.com) writes:
Quite a bit.
I have a list on
es" target="_blank">http://www.sommarskog.se/mssqlperl/...tatyp
es
This text is in the context of two PERL modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
ive DB-Library connectivity?
What is recommended?
Thanks,
AdamSupportability would be the #1 reason for choosing OLEDB over DB Library.
DB Library is considered an a legacy interface and as such was not updated
from what shipped with SQL Server 6.5 and 7.0.
--Brian
(Please reply to the newsgroups only.)
"Adam Ticktin" <anonymous@.discussions.microsoft.com> wrote in message
news:AD2D6770-2EBF-4886-BD6D-550AF7D6E6E7@.microsoft.com...
quote:|||Thanks Brian! Any known loss of available functionality in 2000 that can't b
> Are there any benefits in choosing OLE DB interface instead of using the
> native DB-Library connectivity?
> What is recommended?
> Thanks,
> Adam
e accessed DB-Library?
Adam|||Adam,
quote:
> Any known loss of available functionality in 2000
> that can't be accessed DB-Library?
Well, basically everything that was added to SQL Server after
version 6.5 is not supported by DB-Library.
DB-Library became obsolete years ago. Don't even think about using
it.
Linda|||I would not recommend using DB-Library. Yukon, the next release of SQL
Server, will not include the files needed to develop DB-Library
applications. The following warning has been in the readme of every SQL
Server 2000 service pack and in every update to the SQL Server 2000 Books
Online:
Warning While the DB-Library API is still supported in Microsoft SQL Server
2000, no future versions of SQL Server will include the files needed to do
programming work on applications that use this API. Connections from
existing applications written using DB-Library will still be supported in
the next version of SQL Server, but this support will also be dropped in a
future release. When writing new applications, avoid using DB-Library. When
modifying existing applications, you are strongly encouraged to remove
dependencies on DB-Library. Instead of DB-Library, you can use Microsoft
ActiveX Data Objects (ADO), OLE DB, or ODBC to access data in SQL Server.
Also, this topic in the SQL Server 2000 Books Online outlines the types of
features not available to DB-Library applications:
http://msdn.microsoft.com/library/?...>
p?frame=true
And a third point against DB-Library: the SQL Server client components used
in ADO.NET, ADO, OLE DB, and ODBC are also native interfaces for SQL Server.
The SQLClient managed provider, the SQLOLEDB provider, and SQL Server ODBC
driver all use the native SQL Server protocol (TDS) to send their requests
to the database engine. DB-Library will not have an inherent performance
advantage over any of these APIs.
Alan Brewer [MSFT]
Lead programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights|||examnotes (anonymous@.discussions.microsoft.com) writes:
quote:
> Thanks Brian! Any known loss of available functionality in 2000 that can't
> be accessed DB-Library?
Quite a bit.
I have a list on
es" target="_blank">http://www.sommarskog.se/mssqlperl/...tatyp
es
This text is in the context of two PERL modules that I have.
DB-Library is a very nice interface, fast and simple that does not
do things behind your back. Unfortunately, Microsoft does not at all
share my opinion on this matter.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)