СОДЕРЖАНИЕ

1. СЕМИНАР 1.2. XML в RDBMS & Динамика структур & Журналирование изменений 2. СТАТЬИ 2.1. Репликация исполнения хранимых процедур 3. ССЫЛКИ НА СТАТЬИ 3.1. Статьи на русском языке 3.2. Англоязычные статьи 4. ФОРУМ SQL.RU 4.1. Самые популярные темы недели 4.2. Вопросы остались без ответа 5. КНИГИ 5.1. SQL Server, профессионалы для профессионалов

СЕМИНАР

XML в RDBMS & Динамика структур & Журналирование изменений

Дата: 28.02.2005г. 18:30
Место: г. Москва. Чапаевский пер., 14. Представительство Microsoft в России и СНГ.
Доклады:

1. XML в RDBMS. Поддержка XML в Microsoft SQL Server 2000/2005. Алексей Ширшов

2. Подходы к построению консервативного кода для OLTP/OLAP в услолвиях динамики классификационных структур. А. Денисенко, Д. Нижаде-Гавгани

3. Журналирование изменений структуры баз и данных. Наталья Кривонос

Для регистрации на семинар, необходимо заполнить РЕГИСТРАЦИОННУЮ ФОРМУ, с указанием Вашей фамилии, имени, отчества и адреса электронной почты

Количество мест в аудитории семинара ограничено, поэтому прошу Вас не откладывать регистрацию.

В день проведения семинара, всем кто был успешно зарегистрирован, по электронной почте придёт письмо с подтверждением регистрации.

Для того, что бы пройти в помещение проведения семинара, при себе необходимо иметь паспорт или другое удостоверение личности.

Карта проезда в представительство Microsoft

[В начало]

СТАТЬИ

Репликация исполнения хранимых процедур

По материалам статьи Muthusamy Anantha Kumar: Replicating Stored Procedure Execution
Перевод Маргариты Баскаковой

В среде OLTP (on-line transaction processing), Вы часто встречаете пакеты заданий (batch jobs), которые перемещают хронологию данных в архивные таблицы. Кроме того, часто встречаются пакетные задания, которые выполняют очистку OLTP таблиц от устаревших данных.. Задания такого типа могут порождать много транзакций и создавать дополнительную нагрузку на OLTP систему, снижая общую производительность, особенно если операции по переносу в архив или очистки данных выполняются над базой данных, участвующей в репликации транзакций SQL Server.
Существует способ, помогающий снизить такую нагрузку - это репликация исполнения хранимых процедур, он состоит в том, чтобы реплицировать информацию о том, как должна быть выполнена хранимая процедура, вместо того, чтобы реплицировать все транзакции от издателя подписчикам при очистке или архивировании данных.
Давайте рассмотрим два сервера. "EBONY" - основной сервер, и "MAK", которые являются дистрибутором и подписчиком в репликации таблиц базы данных, используя PULL подписку. Предположим, что мы реплицируем все колонки двух статей "Orders" и "Order_Details" из базы данных "Sales" от первого сервера "EBONY" в базу данных "Sales" на втором сервере "MAK". Мы не делаем никакого горизонтального или вертикального разделения таблиц.
Кроме того, мы предполагаем, что из этих двух таблиц будут изыматься устаревшие данные, и затем из них эти данные будут удалены, после чего относящиеся к очистке транзакции будут реплицированы подписчику. Чтобы уменьшить нагрузку, давайте создадим хранимую процедуру с именем "USP_Archive_Purge", которая архивирует данные в таблицах "Orders_Archive" и "Order_Details_Archive", и затем производит очистку данных.
Для демонстрации этого давайте пошагово смоделируем такую OLTP среду с репликацией.

Шаг 1

Предположим, что в базе данных "Sales" на первичном сервере "EBONY" есть таблицы "Orders" и "Order_Details".

Use master
go
Create database Sales
Go
use Sales
go
Create table Orders (Order_id int constraint Orders_PK 
 primary key, custid int, Date datetime, 
 Total_Amount money, ship_address varchar(300))
Go
Create table Order_Details 
 (order_details_id int constraint order_details_PK primary key, 
  Order_id int constraint Orders_FK 
  foreign key references Orders(Order_Id), item_id int, 
quantity int, unit_price money, discount decimal (5,2))
Go

Теперь предположим, что таблицы "Orders" и "Order_Details" уже реплицируются с первого сервера "EBONY" второму серверу "MAK" (используя репликацию транзакций).
Здесь, база данных "Sales" - публикация, "EBONY" - издатель, "MAK" - подписчик, и база данных "Sales" на "MAK" - подписка. [Рис. 1.1]

[Рис. 1.1]

Шаг 2

Теперь, давайте добавим какие-нибудь данные в описанные выше таблицы, и тиражируем их от первого сервера второму.

use Sales
go
insert into Orders select 
1,1,'11/14/2004',100,'xyz st, NJ'
insert into Orders select 
2,1,'11/15/2004',2100,'xyz st, NJ'
insert into Orders select 
3,6,'11/14/2004',100,'sesame st, NY'
insert into Orders select 
6,4,'11/14/2004',100,'Main st, SC'
insert into Orders select 
9,4,'11/15/2004',100,'Main st, SC'
insert into Order_details select 
1,1,12,3,5,0
insert into Order_details select 
2,1,16,3,25,0
insert into Order_details select 
3,1,66,1,10,0
insert into Order_details select 
4,2,112,2,1000,0
insert into Order_details select 
5,2,117,1,100,0
insert into Order_details select 
14,3,112,1,100,0
insert into Order_details select 
15,6,112,1,100,0
insert into Order_details select 
19,9,112,1,100,0

Вскоре после того, как мы добавим вышеупомянутые строки, все транзакции тиражируются в базу данных "Sales" подписчика "MAK" [Рис. 1.2]

[Рис. 1.2]

Шаг 3

Теперь, давайте создадим таблицы для архива и процедуру очистки на издателе "EBONY" [Рис. 1.3]

Use sales
go
Create table Orders_Archive 
(Order_id int constraint Orders_PK1 primary key, 
custid int, Date datetime, Total_Amount money, 
ship_address varchar(300), 
archivedate datetime 
constraint Orders_archive_default default getdate())
Go
Create table Order_Details_Archive 
(order_details_id int constraint 
order_details_PK1 primary key, 
Order_id int constraint Orders_archive_FK1 foreign 
key references Orders_Archive(Order_Id), item_id int, 
quantity int, unit_price money, discount decimal (5,2), 
archivedate datetime constraint Order_details_archive_default 
default getdate())
Go
Create procedure Usp_Archive_Purge as
Insert into Orders_Archive 
(Order_id, custid, Date, Total_Amount, ship_address) 
select Order_id, custid, Date, Total_Amount, 
ship_address from Orders 
where 
convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Insert into Order_Details_Archive 
(order_details_id, Order_id, item_id , quantity , 
unit_price, discount ) 
select order_details_id, Order_id, item_id, quantity, 
unit_price, discount from Order_details
where 
order_id in 
(select order_id from orders 
 where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Order_Details where 
order_id in 
(select order_id from orders 
 where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Orders 
 where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Go

Шаг 4

Теперь, давайте создадим таблицы для архива и процедуру очистки на Подписчике "MAK"

Use sales
go
Create table Orders_Archive 
 (Order_id int constraint Orders_PK1 primary key, 
 custid int, Date datetime, Total_Amount money, 
 ship_address varchar(300), 
 archivedate datetime 
 constraint Orders_archive_default 
 default getdate())
Go
Create table Order_Details_Archive 
 (order_details_id int constraint 
  order_details_PK1 primary key, 
  Order_id int constraint Orders_archive_FK1 
  foreign key references Orders_Archive(Order_Id), 
  item_id int, quantity int, unit_price money, 
  discount decimal (5,2), 
  archivedate datetime 
  constraint Order_details_archive_default 
  default getdate())
Go
Create procedure Usp_Archive_Purge 
as
Insert into Orders_Archive 
 (Order_id, custid, Date, 
  Total_Amount, ship_address) 
select Order_id, custid, Date, Total_Amount, 
 ship_address from Orders 
where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Insert into Order_Details_Archive 
 (order_details_id, Order_id, item_id, quantity, 
  unit_price, discount ) 
select order_details_id, Order_id, item_id, quantity, 
 unit_price, discount from Order_details
where 
 order_id in 
 (select order_id from orders 
  where 
  convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Order_Details 
where 
order_id in 
(select order_id from orders 
 where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Orders 
 where 
 convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Go

Шаг 5

Теперь, давайте добавим хранимую процедуру "USP_Archive_Purge" к репликации.

На издателе "EBONY", выполните следующее:

-- Замените на имя своей базы данных
use Sales
go
sp_addarticle
-- Замените на имя своей публикации
 publication ='Sales',
 pre_creation_cmd='none',
 article = 'USP_Archive_Purge' ,
 destination_table = 'USP_Archive_Purge' ,
 type ='proc exec',
 schema_option=0x01,
 destination_owner ='dbo',
 source_owner ='dbo',
 source_object='USP_Archive_Purge' ,
 force_invalidate_snapshot =0,
 status=0
go

exec sp_addsubscription
-- Замените на имя своей публикации
 publication = N'Sales',
 article = N'USP_Archive_Purge',
-- Замените на имя своего подписчика
 subscriber = N'MAK',
-- Замените на имя своей базы данных
 destination_db = N'Sales',
 sync_type = N'none', 
 update_mode = N'read only' ,
-- Замените на PUSH или PULL в зависимости от своего типа подписки
 subscription_type = 'PULL'
go
--Замените на имя своего издателя
sp_refreshsubscriptions 'Sales'
go

Шаг 6

Выполните хранимую процедуру "USP_Archive_Purge" на издателе "EBONY" как показано ниже.

Use Sales
Go
Exec USP_Archive_Purge
Go

Результаты

Как вы видите, некоторые строки были перемещены из исходной таблицы в архивную таблицу и потом были удалены.

(5 row(s) affected)
(8 row(s) affected)
(8 row(s) affected)
(5 row(s) affected)

Анализируя хронологию сеансов репликации, посмотреть которую можно в Мониторе Репликации из состава Enterprise Manager ,Вы заметите, что была доставлена только одна транзакция. [Рис. 1.3]

[Рис. 1.3]

Если на дистрибуторе выполнить хранимую процедуру "sp_browsereplcmds" Вы увидите, что была выполнена всего одна команда, которая запустила на исполнение процедуру.

{call "dbo"."USP_Archive_Purge" }

Теперь, проверьте архивные таблицы на издателе и подписчике.

На Издателе "EBONY"

Use Sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8

На Подписчике "MAK"

Use sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8

Обратите внимание, что после выполнения процедуры публикуемая и подписанная базы будут синхронны. Вы можете изменить процедуру "USP_Archive_Purge" и наметить её исполнение по отвечающему Вашим бизнес-правилам расписанию.

Заключение

Как было сказано ранее, цель этой статьи состоит в том, чтобы продемонстрировать пошаговую инструкцию применения репликации исполнения хранимых процедур, если используется репликация транзакций. Применение описанного выше метода, уменьшит нагрузку и повысит производительность вашего SQL Sever.

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

SQL Server 2005. Snapshot - изоляция транзакций
Narasimha Rao AV
MSSQLServer: Эта статья описывает разные уровни изоляции, использующиеся в SQL Server, а также освещает одно из новшеств следующей версии этой СУБД под кодовым название Yukon, Snapshot - изоляцию. Описание Snapshot - изоляции будет сопровождаться примерами, и некоторыми фактами, которые могут показаться неожиданными при использовании этого нового для SQL Server уровня изоляции транзакций...
Исследуем возможности CASE
Arthur Fuller
MSSQLServer: Вы имеете прекрасную возможность составлять условия выборки для своих запросов к SQL Server таким образом, что бы получить очень гибкие варианты представления возвращаемых данных. Функция CASE, наверное, одна из наиболее редко используемых в SQL. Чаще всего её используют для генерации вариантов значений поля в выборках, однако, у этой функции есть ещё много полезных применений. Например, Вы можете использовать CASE в предложении WHERE...
Изменение столбца таблицы, участвующей в репликации
Paul Ibison
MSSQLServer: Иногда структура таблицы, участвующей в репликации нуждается в изменении. Для этого может быть несколько причин - изначально неправильный выбор типа данных, отсутствие значения по умолчанию или необходимость переименовать столбец. Попытка изменить структуру таблицы непосредственно закончится ошибкой...
Поддержка XML в Microsoft SQL Server 2005
S. Pal, M. Fussell, I. Dolobowsky
MSSQLServer: В этой статье рассматривается добавленная в SQL Server 2005 поддержка XML. В статье также показано, как эта поддержка интегрируется с программной поддержкой со стороны клиента в .NET Framework V2.0 и в родном коде, как OLEDB и SQLXML...

[В начало]

Англоязычные статьи

Changing SQL Server Passwords
OrcsWeb Team
Most security experts' recommendations include changing passwords frequently to enhance security. It is easy to change the password on a SQL Server Username via Query Analyzer. Changing the password with Query Analyzer makes it easy to coordinate changing the password on the database server at the same time as it is changed in the connection string. This approach provides the flexibility to change the password at the most appropriate time for the application
Data Driven Architecture
Jim Craddock
Traditional development methods typically center on developers writing customized forms, pages or screens for each aspect of user data. Object-oriented development allows today’s developers to reuse code and dramatically lower the overall number of lines of code required for a typical complex application. Data-driven development is a way to further reduce the amount of custom coding required in development by investing time upfront in an architecture that utilizes metadata (data about data)
Driving Ms. Data: Creating Data-Driven Possibilities
Richard Hoffman
Data-driven Web sites help schools and districts maximize their IT resources by making online content more "self-service" for users. Here's how to set up the capacity to create your own data-driven sites
Alternate Rows Coloring in SQL Reporting
Jayarajan S Kulaindevelu
To change the row colors in reports. Like every other report has some or the other way to make it look beautiful, SQL Reporting also has some good features, but here I will be just showing how to make the color change for alternate rows
xp_pcre - Regular Expressions in T-SQL
Dan Farino
An Extended Stored Procedure to use regular expressions in T-SQL
Analysis Services 2000 versus Analysis Services 2005
Kevin Kline
You’re probably like me – you’re a busy person. So you’re constantly looking for ways to improve your productivity or to find a short cut big chunks of work. A big chunk of work for me is trying to understand what’s new in Yukon. Well, I thought you’d like to know about a great shortcut on getting up to speed on Analysis Services 2005
SQL Server 2005. Query Problem Solved with a Little Help from a New Friend
Luciano Caixeta
After reading the excellent Web-exclusive article “Calculating Row Numbers in SQL Server 2005,” April 2004, InstantDoc ID 42302, I started doing some tests and found one case in which I couldn’t think a way to use the ROW_NUMBER() function without using subqueries. Using the Northwind database, I wanted to write a query to create a new identifier for each order and another new identifier for each product that belongs to an order. The result should be similar to
Replicating Identity columns in SQL Server - Customizing replication
Muthusamy Anantha Kumar
When transactional replication is used for high availability purposes, such as if you want the applications to access the replicated server when the primary database server fails, one of the hurdles SQL Server database administrators face when configuring replication is tables with identity columns
SQL Server 2005. ROW_NUMBER() Function
Jamie Thomson's Blog
There is a new feature in SQL Server 2005 that is akin to the ROWNUM function in Oracle which I am going to blog about here. I'm sure alot of people reading will already know about this function and its syntax/behaviour but this post may still server as a useful reference. I'm mainly writing this for my colleagues at http://www.conchango.com that won't have come across it yet
Understanding changes in Yukon MDX Part I
Mosha Pasumansky
When I did a review of “Analysis Services 2000 vs 2005” presentation, I mentioned, that changes to MDX were not covered at all in it, and I also noted, that changes to MDX in Yukon could easily fill several presentations by themselves. And they do. In July, Rob Zare has delivered Webcast called “Understanding changes in Yukon MDX Part I”. This Webcast was mostly based on the presentation that Rob and myself prepared together for the Yukon Technical Preview. When Beta 2 shipped, this Webcast was supposed to become available, but while everybody whom I asked assures me that it is available somewhere (on Betaplace, or perhaps on Beta 2 DVD), nobody was able to point out the exact location. Since we are getting a lot of questions in the newsgroup on this subject, I decided at least to make the slides available. While this is not as good as recorded Webcast - it is still better then nothing :) Note that this is just Part I, and it was impossible to cover everything, so we had to chose what we thought would fit the “introduction“ presentation. We are working now on the “advanced“ presentation - so let me know if you want some particular content to be included there
Microsoft SQL Server 2005. Encryption topic continued; How to search for encrypted values
Larry Chesnut
I recently received a question about my earlier blog and so I thought if one person asked, perhaps there are others who would like to see the answer too. He asked me, 'How do you write a query that will allow you to search for a particular value that is already encrypted?’'
Using Xp_sendmail With the Recipients Generated From a Query
Kunal Das
Xp_sendmail is a great utility that SQL Server provides in order to e-mail messages directly from SQL Server, by using a valid MAPI profile. However, its syntax is rather stringent and if you type in the email address yourself it works fine
Common Mistakes in Data Modeling
Glenn Goodrum
“There is never enough time to do something right the first time, but always enough time to do it over again.” Most readers have probably lived that cliche many times, even though they really want to do what is necessary to avoid rework. Obviously, rework involves lost productivity, and the need for rework implies that the initial results failed to meet the needs of the business or users in some fashion
SQL: Difference between WHERE and HAVING
Rudy Limeback
What is the difference between where clause and having clause?
Integrating InfoPath with SQL Server 2005
Write Ahead Blog
I discovered InfoPath only a few months ago and started wondering how it could be used in enterprise offices. InfoPath does a great job of building forms for publishing XML data quickly. It not only makes the task of building CRUD (create/retrieve/update/delete) applications for Web-services really easy but with the introduction of the .NET extensions for InfoPath, it is possible to build really diverse InfoPath application. So how do you build desktop productivity applications in InfoPath that offer asychronous execution and reliability that traditional web-services over HTTP do not offer?
The Essence of Meta Data: Vision and Reality Hit the Meta Data Environment
R. Todd Stephens
Many people in the IT field look at the meta data repository as an application which provides a limited set of value points for the organization. The reality is that the repository itself is just one part of a much larger collection of products, services, tools, processes and customer support components. Figure 1 provides one view of the repository environment that attempts to pull in some of these components into a single framework
Optimizing Microsoft SQL Server Reporting Services: Execution Log Reporting: Preparation as a Data Source
William E. Pearson, III
To get the most out of the Optimizing MSSQL Server Reporting Services series, we need to have installed Microsoft SQL Server 2000 Reporting Services, together with Microsoft Visual Studio.NET (required to access Report Designer for report creation). Reporting Services ships with MSSQL Server 2000, and many of my articles use MSSQL Server and MSSQL Server Analysis Services components, predominantly as data sources, within them
Queries Run Amuck, Vertical Partitions for Data and More
sswug.org
Writing this daily column, there are many times when you wonder just how many people are out there reading. Yesterday, I found out the answer to that. There are a LOT of you out there reading... wondering... questioning just how a query can "go bad." Yesterday I mentioned that we had a query that suddenly stopped working after being in a production site for quite some time. No code change, no clear issue - so I listed the steps that we went through to narrow down the issues. I made the statement that "Somehow, a where clause had been disconnected." Many wrote in to say that they were relieved that they weren't the only ones that had a query suddenly start functioning differently than it had been. Many more wrote in asking how that could be - how could it start behaving differently without a change to the underlying database tables or code
How to set up application names in SQL Server 2000 Analysis Services
Microsoft
This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base
Non-obvious but true: SQL's XML type instances are not DOM documents by default
Enjoy Every Sandwich
Darshan Sighn, publisher of YukonXML posted an interested question on the SQL2005 XML newsgroup yesterday asking how to force an untyped XML instance to be like an normal XML document where there is a single root element. I'm not sure there's a way to do that just yet, short of a column constraint based in an XQuery that Darshan later posted. But our conversation brought out an interesting question from Roger Jennings; "For typed instances, isn't the document modifier superfluous?"
SQL's XML vs. SQLXML vs SQL XML
Enjoy Every Sandwich
There's so many XML driven features in SQL Server 2000 and SQL Sever 2005 that sometimes its hard to know which part of the product you're talking about if you just say or write SQLXML. So, here's a brief glossary
Managed Stored Procedures and User Defined Functions - Part I
Darshan Singh
By now you might have heard that SQL Server 2005 hosts the .NET 2.0 runtime. This enables writing stored procedures, functions and triggers using any of the managed languages, such as C# and VB .NET. This article is based on my first experience with SQLCLR integration, where I converted some of the documented and un-documented extended stored procedures to managed procedures and functions
A Quick Introduction to SQL Server Management Objects (SMO)
Darshan Singh
Microsoft® SQL Server™ 2000 supports a COM-based object model, called SQL-DMO, which can be used to programmatically manage the SQL Servers. Enterprise Manager uses this API to manage SQL Server. SQL Server 2005 replaces SQL-DMO with two .NET based object libraries: SQL Server Management Objects (SMO) & Replication Management Objects (RMO). In this short tutorial, I'll show you how you can use SMO in your .NET applications. (5 printed pages)
Essential XQuery - The XML Query Language
Darshan Singh
XQuery or XML Query Language is a W3C specification (http://www.w3.org/TR/xquery) designed to provide a flexible and standardized way of searching through (semi-structured) data that is either physically stored as XML or virtualized as XML (such as XML Views over relational data). To give you an analogy, SQL is used to query relational data, XQuery is a standard language for querying XML data. In addition, XQuery also allows general processing of XML (such as creating nodes). Currently in the last call working draft status, the W3C XQuery specification will hopefully get the recommendation status this year. This article is an introduction to XQuery language. It is based on November 2003 working draft. (Click here to see the changes made to 23rd July 2004 XQuery 1.0 Working Draft.) (23 printed pages)
XML in SQL Server 2000 and SQLXML
Darshan Singh
Microsoft® SQL Server™ 2000 (codenamed Shiloh) first introduced native support for XML and HTTP. Since then, to keep up with XML, Web services and .NET advancements, Microsoft has made available add-ons to SQL Server 2000 in the form of SQLXML Web releases. This article begins with explaining the built-in XML and HTTP support in SQL Server 2000 and then it describes the features available in the recent SQLXML Web release (version 3.0, service pack 2)
Inventory Management Calculations in SQL Server Analysis Services 2005
richtk@microsoft.com
A common business problem is how to calculate inventory based on incoming and outgoing counts. At any point in time, inventory is not simply summed from the deltas because inventory on a particular day is the sum of the deltas across time. Additionally, inventory is counted on a regular basis to account for errors in deltas, shrinkage, or other discrepancies
Loading Aggregate Data in Yukon 2005
richtk@microsoft.com
In some cases, the source data is pre-aggregated in one, some or all dimensions. In the simplest example, employee salary is loaded to all employees. Because a manager’s salary is not the aggregate of his reports, it should not be aggregated up the employee dimension. There are several other such examples. This document discusses how pre-aggregated data can be loaded directly into Analysis Services 2005
Cell Annotations
richtk@microsoft.com
A cell annotation is a note on a cell written by a user explaining something of significance. It’s common in financial applications to allow users to annotate cells during, say, the budgeting process to explain some number. This document explains how cell annotations can be incorporated into an Analysis Services 2005 application
Introduction to MDX Scripting in Microsoft SQL Server 2005 Beta 2
Richard Tkachuk
This document describes how Multidimensional Expressions (MDX) for Microsoft SQL Server 2005 Beta 2 can be applied to common business problems. This document assumes some familiarity with MDX. (16 printed pages)
Troubleshooting triggers in SQL Server
Barrie Sosinsky
A trigger is code that is executed when there is a specific data change in a table. Triggers are one way of controlling how your database responds to INSERTs, DELETEs, and UPDATEs and can be used to maintain data integrity. Triggers are used whenever a constraint placed on data modification is not robust enough to serve your purpose. A trigger is similar to a stored procedure, but is automatically executed when one of those three statements are invoked. Triggers are particularly important when you want to enforce your business logic
Problem creating trigger to update reservations table
Greg Robidoux and Jeremy Kadlec
I am having a problem with a trigger to automatically update a table. I have a table, reservations: customerid, checkindate, checkoutdate, servicecharge, totalcharge. And I have a table called transactions: transactionid, customerid, mservicecharge, and servicedescription. Now I want a trigger like
Use SET NOCOUNT ON for better trigger performance
Serdar Yegulalp
Triggers make it easy to automatically synchronize the behavior of data between tables and across databases. But their performance can be drastically affected by things that might not be obvious at first glance. Here I'll identify one command to reduce the impact of some unforeseen problems
Trigger to update table with no foreign keys
Michael Hotek
I am trying to use a trigger on SQL Server 7.0. I have a table, employee, with attributes like cEmployeecode, vFirstname, vLastname, etc., and I also have a table called Positiontable with attributes cPositioncode, icurrentstrength, vdescription, etc. I intend on creating the trigger so that if any value is inserted in the employee table, the currentstrength should be updated and increased by one(icurrentstrength + 1). But the employee table has no primary key that is a foreign key in the position table, so how do I create the trigger to this effect?
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
William Pearson
In this article, we will continue the exploration of distinct counts we began in our previous article, Distinct Count Basics: Two Perspectives. Having discussed why distinct counts are useful, and often required, within the design of robust analysis and reporting applications, we described some of the challenges that are inherent in distinct counts. We then undertook practice exercises to illustrate general solutions to meet example business requirements, providing an approach afforded us by the MSAS user interface, and then an alternative approach we enacted using MDX. Our stated purpose was to lay the framework for this and subsequent articles, where we will focus upon specific scenarios that occur commonly in the business environment, within which the optimal use of distinct counts can become a very real consideration
SQL Server 2005 Security - Part 2 Authorization
Marcin Policht
Following the discussion of new or enhanced authentication-related functionality in SQL Server 2005 Beta 2 in our previous article, we are shifting our focus to authorization features, which determine the level of access rights once the user's logon process is successfully completed. Among topics that will be covered here, are separation of user and schema, modifiable context of module execution, increased permission granularity, and improved catalog security
Introduction to SQL Profiler
Mike Gunderloy
I don't do as much developer support as I used to, but I still answer questions on a few mailing lists. Fairly frequently developers seem to lose track of what's going on when their code calls SQL Server to manipulate data. "I don't know what's going on when I do this," is the typical lament. "Have you looked at the actual SQL that's being sent?," I reply. When this reply generates confusion, I know the original questioner hasn't learned how to use SQL Profiler. If you haven't either, read on for an introduction to this useful troubleshooting tool
Calculate Easter for Any Year
Robert Davis
I needed an easy way to add Easter holiday to an events table for a database supporting a web calendar. After a little research, I found an algorithm to calculate the Western traditional date. It works for any year in the Gregorian calendar, any year after 1582
SQL Server Integration Services 2005 (SSIS). The New ETL Paradigm
Jamie Thomson
Recently I decided I wanted to write an article concerned with SQL Server Integration Services 2005 (SSIS) for publication on SQLServerCentral.com and was trying to decide what the content should be. Checkpoints, event handlers, the new expression language, Foreach Loop enumerators, fuzzy matching, package migration… loads of possibilities and all things that I hope to explore in the future. It occurred to me however that much of the audience for this article wouldn’t be able to relate to it because they hasn’t yet used or even seen SSIS; they would have no frame of reference
Surrounding the ETL Requirements
Ralph Kimball, Margy Ross
Before designing an ETL system, you must first understand all of your business needs. Ideally, the design of your extract, transform, and load (ETL) system begins with one of the toughest challenges: surrounding the requirements. By this we mean gathering in one place all the known requirements, realities, and constraints affecting the ETL system. The list of requirements is pretty overwhelming, but it's essential to lay them on the table before launching a data warehouse project
PredictNodeID - The reason behind the prediction
DMTeam
Use the PredictNodeID DMX function to gain insight into the rule(s) applied by your favorite algorithm to churn out that nifty recommendation
Looking for the Worst is the New Best Practice
Brian Moran
Sometimes focusing on worst practices, rather than best practices, is the best way to tackle a problem. That might sound strange at first glance, but I take that approach more and more often in my consulting work. Here's an example of what I mean
New formatting functionality in Yukon
Chris Webb's BI Blog
Anyone who has looked at the AdventureWorks sample cube will probably have picked up by now that all the intrinsic member properties like FORMAT_STRING can now be defined with an MDX expression (in AS2K it was only FORE_COLOR and BACK_COLOR that you could do this with), but something else caught my eye today when I was looking at the tutorials in BOL: there are new MDX statements which allow you to set these properties on any area within a cube from a script. So you can now do things like
Reporting Services : 21 Report packs released to the web for free download
Mat Stephen's WebLog
Just incase you missed this freebie
Changes in dbcc memorystatus in Yukon
Slava Oks's WebLog
You probably have used dbcc memory status command in SQL2000. In Yukon we have changed output of dbcc memorystatus to reflect new memory manager design, covered in my previous post
Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
Q271624
The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of SQL Server. The output from this command is useful in troubleshooting issues that relate to the memory consumption of SQL Server or to specific out-of-memory errors (many of which automatically print this output in the error log). Microsoft Product Support Services may also request that you run this command during the course of a specific support incident if you are encountering an error that may be associated with a low memory condition
Microsoft SQL Server 2005. Leftovers – pieces of things you may or may not already know
Larry Chesnut
I’m off again for another couple of weeks of on the road work, but before I signed off on my blogging for a while, I thought I would put down some of the little things I have come across recently. By themselves, they do not warrant an entire blog entry, but cumulatively, I thought they were worthwhile
Data Archiving: Problems and Solutions
David Poole
Technology advances allow old-fashioned house keeping tasks to be put off for increasing periods of time. For example, the increase in terms of storage capacity and machine speed allowing you to get away without an archiving strategy for an extended period of time, perhaps even beyond your tenure with a company
SQL: Leading zeros in a field
Rudy Limeback
How do I put leading zeros in a field? The field looks like this: ' 100'. I want the capability to be able to put in a single zero or multiple zeros. This is what I was trying, and it just doesn't like something
Predictive Analytics with Data Mining: How It Works
Eric Siegel
Although you've probably heard many times that predictive analytics will optimize your marketing campaigns, it's hard to envision, in more concrete terms, what it will do. This makes it tough to select and direct analytics technology. How can you get a handle on its functional value for marketing, sales and product directions without necessarily becoming an expert?
Trace Messages Part V: Purging Trace Tables
Andrew Novick
The first four articles in this series have been about writing trace information to the AppSession, AppSessionTrace and AppSession. I've found this a useful technique for developing and using non-trivial stored procedures. If I keep the traces turned on in production, all those rows tend to add up after a while. Usp_AppSession_Purge has the task of cleaning up the AppSession* tables. Here's the CREATE PROC script
SQL Server SP4 Follow-Up, Optimizing SQL Server Views
SQL Server SP4 Webchat Quick Clips
The webchat yesterday was outstanding! We had record numbers attend and answered a huge array of questions from everyone. The Microsoft SQL Server development teams really outdid themselves on this one. Here are just a few of the Q&A items - we'll be posting the full transcripts shortly
Obtaining Current DataTable Row for a DataGrid
Chad Z. Hower aka Kudzu
Its a shame that this requires even a short article to explain this - but some things in .NET are missing "practical" short cuts. This article demonstrates how to obtain the row in a DataTable that the currently selected row in a data grid is linked to
Pattern-based split string
adam machanic
"hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion
Using Microsoft.SqlServer.Broker class library for writing .NET service programs
Rushi Desai
Service Broker programs may be written as stand-alone applications that establish a connection with SQL Server or as stored procs that are activated internally. You can leverage the .NET platform in both scenarios since SQL Server 2005 integrates CLR and provides the ability of writing stored procs in CLR-based languages like C# or Visual Basic
Combining the XmlReader and XmlWriter classes for simple streaming transformations
Mark Fussell's WebLog
The XmlReader and XmlWriter classes can often be combined to provide simple streaming transformations rather than resorting to XSLT which requires a the document to be loaded into memory. This class combination is often faster and uses less memory, although it requires more code and is less flexible in the types of transformations possible. However for many scenarios it is ideal. Say for example you wanted to add a new element in several repeating places to an existing document. The XmlWriter.WriteNode method is useful in pulling from an XmlReader and pushing to an XmlWriter to achieve this, but it does have a limitation in that it writes the current node and all its children to the XmlWriter without providing more fine-grained control
MS Ignoring developer demand for REST tools?
XmlTeam's WebLog
The long running PR battle is heating up again between those who advocate the implementation of service architectures [1] "RESTfully" using XML transferred via HTTP, vs those who work with SOAP, WSDL, and the specs built on them known as "WS-*". The item that finally got me to blog about this painful [2] subject was James Governor's SOAP is boring, wake up Big Vendors or get niched

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
Поймем как работает или Зачем MsSQL использует версии данных ?
Момент Connect'a к SQL-серверу
Посоветуйте как правильно организовать базу данных
Никто случайно не использует WEB Services для обновления данных SQL Server 2000?
Странные дела твоеятся в процедуре
Больше книг хороших и разных!
Не работает бэкап
Хорошо ли объединять несколько хранимых процедур в одну?
MD5,SHA1 на T-SQL
Таки случилось
помогите новичку с селектом
Выборка первых N значений с макс. суммой в каждой группе
Импорт таблици в виде txt файла большого объема (1Гб)
Вопрос вопросов
Select * from ViewV1 where Pole= '0' возвращает все записи !
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.;
Эффективный запрос
Не детские ошибки в логах SQL Server 7.0 !!!?
Error: 17883

[В начало]

Вопросы остались без ответа

Пара вопросов про связку JDBC-MS SQL
MS SQL Server + Intel 7520
DRP solutions for SQL Server
СЕМИНАР: <Специфика работы 1C под Microsoft SQL server DESIGNTIMESP=8497>.
Linked Server через File DSN
Reporting services and check box
Извечный вопрос Sybase+MsSQL 2000
Еще проблема со связанным сервером

[В начало]

КНИГИ

SQL Server, профессионалы для профессионалов (исходники ~930 K)

Составитель: Дмитрий Артёмов ISBN: 5-7502-0087-6, Издательство: Русская редакция, Год выпуска: 2005, Тип переплета: твердый, Тираж книги: 2150, Формат книги: 84х108/32, Количество страниц: 512, Цена: 210 р. После более чем пяти лет выпуска журнала SQL Server для профессионалов, у нас накопился огромный объем очень интересного материала. Часть этого материала представлена в книге, примерное оглавление которой представлено ниже Основную задачу. которую мы ставили при выборе статей из нашего обширного архива - дать Вам практический материал, который может быть полезен при работе с СУБД компании Microsoft. В отличие от "классической" книги, написанной одним, двумя, может быть тремя авторами, в нашем сборнике представлено гораздо больше высококлассных профессионалов. Именно такой большой "авторский коллектив" гарантирует высокое качество предлагаемых решений так как мало кто сможет похвастаться, будто одинаково хорошо владеет всеми аспектами работы с таким сложным программным продуктом, каким является Microsoft SQL Server 2000. Мы подбирали статьи признанных авторитетов в каждой из рассматриваемых областей. При этом, статьи - это не короткие заметки "на тему", это часто серьезные исследования малоизученных областей работы с SQL Server. Мы очень надеемся что советы профессионалов помогут Вам лучше разобраться в архитектуре, принципах работы и программировании флагманской СУБД компании Microsoft. 1. Введение 2. Как правильно подобрать СУБД Microsoft для ваших нужд 3. Архитектура SQL Server 2000 4. Как это устроено 4.1. Введение в структуры данных кластеризованных и некластеризованных индексов 4.2. Использование журнала транзакций сервера SQL для улучшения доступности и производительности баз данных 4.3. Внутреннее устройство и архитектура обработчика запросов Microsoft SQL Server 4.4. Внутри User Mode Scheduler в SQL Server 2000 5. Почему оно так себя ведет 5.1. Системные таблицы 5.2. Автоматическое закрытие БД 5.3. Опции, влияющие на поведение блокирования SQL Server 5.4. Изучаем обновление вдоль и поперек 5.5. Конфигурирование Query Analyzer 6. Проектирование баз данных 6.1. Первая нормальная форма - прежде всего! 7. Программирование 7.1. T-SQL 7.1.1.Десять подсказок по лучшим приемам работы с T-SQL 7.1.2.Лучшие приемы работы с хранимыми процедурами 7.1.3.Системные функции SQL Server 7.1.4.Упрощаем анализ хранимых процедур 7.1.5.Использование блокировок приложений 7.1.6.Вызов CryptoAPI из хранимых процедур 7.1.7.Обработка ошибок в SQL Server: предпосылки 7.1.8.Реализация обработки ошибок в хранимых процедурах 7.1.9.Распределенные транзакции объединяют SQL Server и СУБД Oracle 7.1.10. Надежное управление логикой при обработке курсоров 7.1.11. Использование экзотических соединений в SQL 7.1.12. Вызов хранимых процедур из ADO.NET 7.1.13. Выясним, что они делают с fn_get_sql… 7.2. Расширенные хранимые процедуры 7.2.1.Играем в ODS. Часть 8. Мониторинг и настройка производительности 8.1. Мониторинг 8.1.1.Выполнение запросов к файлам журналов с помощью Log Parser 8.1.2.Фильтруем файлы журналов SQL Server 8.1.3.Проверка работоспособности или как бьется сердце SQL 8.2. Настройка производительности 8.2.1.Планы выполнения 8.2.2. Хранимые процедуры и кэширование 8.2.3. Открываем инструментарий Microsoft для настройки производительности 9. Администрирование 9.1. Отказоустойчивые системы 9.2. Как лучше поддерживать резервный (standby) SQL Server 9.3. Выходим за пределы простой передачи журналов: мощная утилита резервирования на T-SQL 9.4. Передача журналов через брандмауэр - простое решение T-SQL 9.5. Лучшие приемы работы по передаче журналов 10. Вопросы безопасности 10.1. Атаки на SQL Server: хакинг, кракинг и методы защиты 10.2. Регистрационное имя sa - ваш "запасной ключ" от SQL Server 10.3. Автоматическое шифрование хранимых процедур и преодоление ограничения 8 Кб 11. Общие вопросы администрирования 11.1. Осиротевшие сеансы 11.2. Как переместить SQL Server с одного компьютера (сервера) на другой 11.3. Автоматическое конфигурирование SQL Server 11.4. Автоматизация управления SQL Server с помощью WMI 11.5. Интеграция SQL Server 2000 с Active Directory 11.6. Генерация сценария создания хранимых процедур в порядке зависимости 12. Недокументированные возможности 12.1. Недокументированные хранимые процедуры SQL Server 2000 12.2. Недокументированные системные таблицы SQL Server 2000 В первую очередь книга будет продаваться в магазинах, список которых вы сможете найти здесь (http://www.rusedit.ru/sales_rozn.asp)

[В начало]