SQL-полезное (Сравнение текста процедур, представлений, функций и т.д. в двух базах)

Сравнение текста процедур, представлений, функций и т.д. в двух базах
Коллеги из филиала спросили "как понять, какие процедуры изменились?"

К счастью, на том же сервере присутствует база, которую можно считать "эталоном".

В этом случае не нужно применять сторонние утилиты - достаточно одного запроса:

select

isnull(s1.name+'.'+o1.[name], s2.name+'.'+o2.[name]),

case

when o1.[object_id] isnullthen'только во второй'

when o2.[object_id] isnullthen'только в первой'

else'объект изменен'end

from Base1.sys.sql_modules m1

innerjoin Base1.sys.objects o1

on o1.[object_id] = m1.[object_id]

leftjoin Base1.sys.schemas s1

on s1.[schema_id] = o1.[schema_id]

fulljoin Base2.sys.sql_modules m2

innerjoin Base2.sys.objects o2

on o2.[object_id] = m2.[object_id]

leftjoin Base2.sys.schemas s2

on s2.[schema_id] = o2.[schema_id]

on s1.name+'.'+o1.[name] = s2.name+'.'+o2.[name]

whereisnull(m1.definition,'')<>isnull(m2.definition,'+')

Вместо Base1 и Base2 подставить требуемые базы данных, в том числе можно сравнивать определения объектов на linked серверах.

ASP.NET-полезное (Call Stack)

полезная статья как вытащить всю иерархию вызовов

StackTrace stackTrace = new StackTrace();
StackFrame stackFrame;
MethodBase stackFrameMethod;
int frameCount = 0;
string typeName;
do {
frameCount++;
stackFrame = stackTrace.GetFrame(frameCount);
stackFrameMethod = stackFrame.GetMethod();
typeName = stackFrameMethod.ReflectedType.FullName;
} while ( typeName.StartsWith("System") ||
typeName.EndsWith("CustomTraceListener") );

Полезное (Google-сервисы)

Классная штука!

Google предлагает большое количество сервисов и API. К счастью они также предоставляет простой способ тестирования, изучения и создания любых приложений в Code Playground. Получите доступ к примерам API, сделайте в них изменения, отладьте, сохраните и экспортируйте свою работу.


Ссылка

SQL-ссылки (использование FILESTREAM)

Subj
And....
Best Practices – When Using FILESTREAM Feature of SQL Server 2008

SQL-полезное (Fixing broken users after Database Restore)

Решение
В комментариях прикольно - I've been using this command since 5 years... and I always forget the syntax
Надо ж - таже фигня!!!

USE db_name
GO
EXEC sp_change_users_login 'Auto_Fix', 'user_name', NULL, 'password'
GO

IVR-ссылки (Outbound+MSMQ+Service Unavailable+503)

Долго боролись с проблемой "зависания" приложения (оказалось просто MSMQ "банилась")
в ситуации когда при звонке на некоторые телефоны возвращалась ошибка 503 ServiceUnavailable
Вот как надо это лечить
еще
еще
Кроме того после выставления SipPeerException.ShouldThrottleMsmqCalls = false
у нас прекращались все события от SipPeerException (например обработка от this.TelephonySession.Close()), пришлось сделать "трюк" - заводили свой таймер и после истечения времени выставляли там статус звонка

Далее статья с первой ссылкой выше ...
One of the most common ways of queuing outbound calls with Speech Server is by using the built in MSMQ support. For the most part, using a message queue is extremely straightforward and easy to implement. But there is one gotcha – call throttling.

When an outbound call fails to connect, Speech Server will start throttling down the number of messages it pulls from the queue. The assumption is that failures are the result of your system having insufficient capacity to handle the load. The more failures your have, the more it will throttle the load. This would be acceptable if it were not for two issues:

Almost any failure, even acceptable ones, can result in throttling
It will happy throttle you all the way down to 0, effectively shutting down your application
Disabling call throttling needs to be done on a per-call bases by binding to the TelephonySession.OpenCompleted event prior to the MakeCall Activity. Typically I do this in a Code Activity at the very top of my workflow.

Inside my initWorkflow Activity I bind to the OpenCompleted like so:

TelephonySession.OpenCompleted += new EventHandler(TelephonySession_OpenCompleted);

Then I use the following to disable throttling on any failure:

void TelephonySession_OpenCompleted(object sender, Microsoft.SpeechServer.AsyncCompletedEventArgs e)
{
if (e.Error != null && e.Error is SipPeerException)
{
SipPeerException sipEx = e.Error as SipPeerException;
sipEx.ShouldThrottleMsmqCalls = false;
}
}

ASP.NET - полезное (Five common mistakes in the web.config file)

Five common mistakes in the web.config file

Коротко без описаний:

1. Custom Errors Disabled
customErrors mode="RemoteOnly"
2. Leaving Tracing Enabled in Web-Based Applications
trace enabled="false" localOnly="true"
3. Debugging Enabled
compilation debug="false"
4. Cookies Accessible through Client-Side Script
httpCookies httpOnlyCookies="true"
5. Cookieless Session State Enabled
sessionState cookieless="UseCookies"

SQL-ссылки (восстановление базы fullbackup+LDF))

http://blogs.msdn.com/suhde/archive/2009/05/18/database-corruption-part-4-recovering-from-a-failed-disk.aspx

Database Corruption Part 4 :: Recovering from a failed disk

It was a nice day a few days back - nice sunny day, with moderate temperatures. I got up early and after spending some time reading my favorite articles, made my way to office. In office, I realized I hadn't much work lying ahead; so sat down wondering how to account for my day.

Suddenly the phone on my desk rang - tring! tring!

"Hi, this is Suhas. How can I help you?"
"I... I lost my disk...!"
"What!"
"My disk crashed. It had my database..."
"You mean the database files?"
"Yes. I had two disks, one had the mdf file and the other had the ldf."
"And, you lost both?"
"No, just one..."
"Ok, so which one did you loose?"
"The one that had the mdf."
"Oh boy! Do you have a backup of the database."
"I do, but it's over 6 months old... Please help me get my data back. You know, I will get fired if I loose the data..."

That's how the conversation started that day. Needless to say, this is one of the situations you would not like to see yourselves in. However, in PSS, we do come across situations like this, when going back to the last backup is not an option, and we have to recover as much data as possible. However, as I have already mentioned in my first blog on Database Corruption, Microsoft Product Support Services (PSS) does not guarantee that if you call in with a database corruption issue, PSS would recover all your data. All support that PSS provides in corruption cases is on "best efforts basis", meaning that PSS will provide commercially reasonable efforts to recover your database or data off your corrupted database using documented and undocumented commands and procedures. However, 100% data recovery is not guaranteed.

In this case, however, we were able to recover the database back. There were multiple points of failure; however, luck was on our side. Here is what we did:

First thing that we did is:

RESTORE HEADERONLY FROM DISK = 'Full path to the backup set'

We were basically looking for 2 options, the Recovery Model and the Backup Type.

Had the Recovery Model been "Simple" or "Bulk-Logged", the story would have ended there itself. Moreover, at this point, we are still not sure if the Database Recovery Model had been changed; we were trying our luck. Had it been changed, that would have been the end of the story as well. Also, the Backup Type was "Full", so, we were good to go with this backup.
We now stopped the SQL Server instance and renamed the existing log file (the LDF file). We had actually planned to drop this database, and we did not want the LDF file to get deleted.
Now, we started the SQL Server and dropped the database. Drop completed successfully, leaving behind the LDF file.
At this point, we created a new database, and pointed the LDF file of the new database to the location where the old LDF file existed. This was to save us the task of copying the old LDF file over to the new location; the old LDF file was about 300 GB in size.
We now stopped the SQL Server, and replaced the LDF file of the newly created database by the old LDF file.
We started the SQL Server, and, as expected, the database was in Suspect Mode.
We now, issued the following command:

BACKUP LOG DatabaseName
TO DISK = 'Full path to TRN Backup file'
WITH NO_TRUNCATE
This command completed successfully and we had a Tail Backup of the Log File. I would like to mention here, that we had a point-of-failure here. Had the Recovery Model of the database been changed after the Full Backup, this command would have failed.
Now, we were all set to restore the database. So, we proceeded with restoring the Full Backup. We issued the command:

RESTORE DATABASE NewDatabaseName
FROM DISK = Full path to the FULL Backup'
WITH
MOVE 'Data File Name' TO 'Full path to MDF File location',
MOVE 'Log File Name' TO 'Full path to LDF File location',
NORECOVERY
The Full Backup was restored and the database was in Recovering Mode.
Next step was to restore the Log Backup. We issued the command:

RESTORE LOG DatabaseName
FROM DISK = 'Full path to LDF File location'
WITH RECOVERY

The backup restored successfully, and the database was back up online!!

Here, again, we had a point of failure. If the Full Backup that we had restored not been the Last Full Backup; meaning, had there been another Full Backup after the backup set we had restored, the restoration of the Tail Log Backup would have failed!
However, we WERE lucky!!!

ASP.NET-полезное (SQL Lite)

http://sqlite.phxsoftware.com/

System.Data.SQLite is the original SQLite database engine and a complete ADO.NET 2.0/3.5 provider all rolled into a single mixed mode assembly. It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll if you're using it natively).

А так же
http://www.rusdoc.ru/articles/ispolzovanie_sqlite_v_net_prilozhenijax/18467/

ASP.NET-полезное (загрузка больших файлов)

I want to upload large files with FileUpload control. The page with this control located in Admin folder.

In admin folder's web.config file i set:

<httpRuntime maxRequestLength="900000"/>
But this does not effect!

Only if I set this line in root's web.config it works.

Why?

========================================================
Consider using a location section (in the web.config that is located in the root):

http://msdn.microsoft.com/en-us/library/b6x6shw7%28VS.71%29.aspx

Location - Path: The resource that the specified configuration settings apply to. Using location with a missing path attribute applies the configuration settings to the current directory and all child directories. If is used with no path attribute and allowOverride is specified to be false, configuration settings cannot be altered by Web.config files in child directories.

The following example sets the uploaded file size limit to 128KB for only the page specified.

<configuration>
<location path="UploadPage.aspx">
<httpRuntime maxRequestLength="128"/>
</location>
</configuration>
====================================================================

ASP.NET-полезное (TransactionScope)

using System.Transactions; //System.Transactions.dll

using (TransactionScope scope = new TransactionScope(
TransactionScopeOption.Required, new TransactionOptions
{
IsolationLevel = IsolationLevel.RepeatableRead,
Timeout = new TimeSpan(0, 1, 0)
}))
{
using (SqlConnection sqlCon = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
sqlCon.Open();
// Some actions...
scope.Complete();
}
}

ASP.NET-ссылки (Quartz.NET - Enterprise Job Scheduler for .NET Platform)

Quartz.NET - Enterprise Job Scheduler for .NET Platform

http://quartznet.sourceforge.net/

ASP.NET-ссылки (использование MSChart)

http://www.4guysfromrolla.com/articles/081909-1.aspx

SQL-ссылки (Восстановление базы без log-файла)

http://weblogs.asp.net/gunnarpeipman/archive/2009/07/21/attaching-sql-server-database-without-log-file.aspx

I needed to attach SQL Server 2008 database to server. There was no log file, just mdf. I don’t know why but it is not very easy to get this database online. After some digging in internet I found solution.

I suggest you to read and try out example by Paul S. Randal TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database. I also suggest you to bookmark this posting, you never know…. I found simplest solution from stackoverflow: How to recover database from MDF in SQL Server 2005? It works also for SQL Server 2008.

Create database with same name as MDF file you have.
Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
Start SQL Server. Database will be now in suspect state because log file is not correct.
Run the following script:

USE [master]
GO
ALTER DATABASE [MyDatabase] SET EMERGENCY
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER
GO
DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET ONLINE
GO

SQL-ссылки (Send Email from SQL Server Express Using a CLR Stored Procedure)

http://www.mssqltips.com/tip.asp?tip=1795

Imports System.Net
Imports System.Net.Mail


Public Class StoredProcedure
_
Public Shared Sub spSendMail(ByVal recipients As String, ByVal subject As String, ByVal from As String, ByVal body As String)
Dim mySmtpClient As SmtpClient

Using myMessage As New MailMessage(from, recipients)

myMessage.Subject = subject
myMessage.Body = body

myMessage.IsBodyHtml = True

mySmtpClient = New SmtpClient("mail.yourservername.com")
mySmtpClient.Credentials = New NetworkCredential("email@domain", "yourPassword")
mySmtpClient.Send(myMessage)

End Using
End Sub
End Class

как отследить присутсвие юзера за компом

как отследить присутсвие юзера за компом
есть программа которая должна переключать режим своей работы если юзер отошел от компа
надо проверять движение мышкой и нажатие на клавиатуру

http://stackoverflow.com/questions/19185/is-there-a-way-to-check-to-see-if-the-user-is-currently-idle

Альтернативная прошивка для фотоаппапратов Canon

http://chdk.clan.su/

SQL-ссылки (MS SQL 2005: оконные функции)

MSSQL оконные функции
http://www.rsdn.ru/article/db/WindowFunctions.xml

SQL-ссылки (постраничный вывод)

постраничный вывод
http://www.arbinada.com/main/node/463

победитель (но если рассматривать все решения то CLR будет лучше)
DECLARE @handle int, @rows int;

EXEC sp_cursoropen
@handle OUT,
'SELECT O.* FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
WHERE C.country_code = ''IT''
ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC',
1, -- Keyset-driven cursor
1, -- Read-only
@rows OUT SELECT @rows; -- Contains total rows count

EXEC sp_cursorfetch
@handle,
16, -- Absolute row index
400001, -- Fetch from row
100000 -- Rows count to fetch

EXEC sp_cursorclose @handle;

update!
сравнительный анализ

ASP.NET-ссылки (Использование CLR в MSSQL)

хорошее описание на русском
http://www.gotdotnet.ru/LearnDotNet/Misc/339745.aspx
http://technet.microsoft.com/en-us/library/ms187540.aspx

Полезные ссылки (SQL)

Инструменты для сравнения структур и данных в БД

(версии бесплатны для частного некоммерческого использования)
dbForge Schema Compare for SQL Server
dbForge Data Compare for SQL Server
dbForge Studio for MySQL
OraDeveloper Studio

http://www.devart.com/ru/?gclid=CPvQlfbTjZsCFcwUzAodThs98g

CLR функция для T-SQL которая возвращает таблицу

Код функции возвращающий таблицу как результат
Навеяно TSQL функцией которая строку вида '1,2,3,4,5' переворачивает в таблицу
НО! если строка большая, то начинаются дикие тормоза
Это решение подымает скорость!

=================================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
private class TableResult
{
public SqlString Element;
public TableResult(SqlString element)
{
Element = element;
}
}

[SqlFunction(TableDefinition = "Element nvarchar(max)",
DataAccess = DataAccessKind.Read,
FillRowMethodName = "FillRow")]
public static IEnumerable Function1(string p)
{
ArrayList resultCollection = new ArrayList();
var ss = p.Split(new[] { ',' });
foreach (var s in ss)
resultCollection.Add(new TableResult(s));
return resultCollection;

//resultCollection.AddRange
//return (new List(p.Split(new[] { ',' }))
//.ConvertAll(s => new TableResult(s)));
}

public static void FillRow(
object Obj,
out SqlString element)
{
TableResult result = (TableResult)Obj;
element = result.Element;
}

};
===========================================

Поднять все это в MSSQL
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SqlServerProject2')
DROP ASSEMBLY SqlServerProject2
go

CREATE ASSEMBLY SqlServerProject2 FROM 'C:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO

CREATE FUNCTION [dbo].[Function1](@p [nvarchar](max))
RETURNS TABLE (
[Element] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject2].[UserDefinedFunctions].[Function1]
go

select * from dbo.Function1('234567,222,233,123');
go

Обработка ошибок для ASP.NET (elmah, Health Monitoring)

Обработка ошибок для ASP.NET
http://www.hanselman.com/blog/ELMAHErrorLoggingModulesAndHandlersForASPNETAndMVCToo.aspx
Исходники
http://code.google.com/p/elmah/

Альтернатива от MS - Health Monitoring
http://habrahabr.ru/blogs/net/27847/
How To: Use Health Monitoring in ASP.NET 2.0

IVR-ссылки (AnswerDetectMachine)

Улучшатель для AnswerDetectMachine !!!
http://gotspeech.net/forums/1/6419/ShowThread.aspx

IVR-ссылки (счетчик в QA-activity)

Анализ сколько раз пользователь неправильно ввел на одном QA-activity
http://gotspeech.net/forums/thread/8606.aspx

Полезные запросы (размер таблиц)

select object_name(i.object_id) as objectName, i.name as indexName,

sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,

(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB

from sys.indexes i

join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id

join sys.allocation_units a on p.partition_id = a.container_id

group by i.object_id, i.index_id, i.name

order by 1, 2