Msg 6522, предупреждение уровня 16 во время выполнения хранимой процедуры clr

В этом коде есть несколько проблем, которые необходимо решить:

1. Что касается указанного вопроса, когда вы получаете ошибку System.Security.SecurityException, которая относится к коду, пытающемуся выйти за пределы базы данных, что не разрешено в сборке SAFE. Как вы это исправите, зависит от того, чего вы пытаетесь достичь.

   • If you are trying to access the file system, read from the registry, get an environment variable, access the network for a non-SQL Server connection (e.g. http, ftp), etc, then the assembly needs a PERMISSION_SET of EXTERNAL_ACCESS. In order to set your assembly to anything other than SAFE, you need to either:
     • Create a Certificate or Asymmetric Key based on the same key that you used to sign your assembly (i.e. give it a strong name), create a Login based on that Certificate or Asymmetric Key, and then grant the EXTERNAL ACCESS ASSEMBLY permission to that Login. This method is greatly preferred over the other method, which is:
     • Установите базу данных, содержащую сборку, на TRUSTWORTHY ON. Этот метод следует использовать только в крайнем случае, если невозможно подписать сборку. Или для быстрого тестирования. Установка для базы данных TRUSTWORTHY ON открывает ваш экземпляр для потенциальных угроз безопасности, и этого следует избегать, даже если это быстрее / проще, чем другой метод.

   • Если вы пытаетесь получить доступ к экземпляру SQL Server, в который вы уже вошли, у вас есть возможность использовать внутрипроцессное соединение Context Connection = true;, которое может быть выполнено в сборке SAFE. Это то, что предложил @Marc в своем ответе. Хотя использование этого типа подключения определенно дает преимущества, и хотя подключение контекста было подходящим выбором в этом конкретном сценарии, было бы чрезмерно упрощенным и неправильным утверждать, что вы должны всегда использовать этот тип связь. Давайте посмотрим на положительные и отрицательные аспекты Контекстной связи:

     • Positives:
       • Can be done in a SAFE assembly.
       • Очень низкие накладные расходы на соединение, если таковые имеются, поскольку это не дополнительное соединение.
       • Является частью текущего сеанса, поэтому любой SQL, который вы выполняете, имеет доступ к элементам на основе сеанса, таким как локальные временные таблицы и CONTEXT_INFO.

     • Минус:

       • Cannot be used if Impersonation has been enabled.
       • Можно подключиться только к текущему экземпляру SQL Server.
       • При использовании в функциях (скалярных и табличных) он имеет те же ограничения, что и функции T-SQL (например, побочные операции не разрешены), за исключением того, что вы можете выполнять хранимые процедуры только для чтения.
       • Табличным функциям не разрешается передавать свои результаты обратно, если они читают набор результатов.

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

2. Если вы подключаетесь к экземпляру, из которого выполняете этот код, и используете внешнее / обычное соединение, тогда нет необходимости указывать имя сервера или даже использовать localhost. Предпочтительный синтаксис - Server = (local), который использует общую память, тогда как другие могут иногда использовать TCP / IP, что не так эффективно.

3. Если у вас нет особых причин для этого, не используйте Persist Security Info=True;

4. Рекомендуется Dispose() из ваших SqlCommand

5. Более эффективно вызывать insertcommand.Parameters.Add() непосредственно перед циклом for, а затем внутри цикла просто установить значение через firstname.Value =, что вы уже делаете, поэтому просто переместите строки insertcommand.Parameters.Add() непосредственно перед строкой for.

6. tel / @tel / listtelnumber - это INT вместо VARCHAR / string. Телефонные номера, так же как почтовые индексы и номера социального страхования (SSN), не являются номерами, даже если они кажутся таковыми. INT не может хранить начальные 0 или что-то вроде ex. для обозначения "расширения".

7. При этом, даже если все вышеперечисленное будет исправлено, с этим кодом все еще существует огромная проблема, которую следует решить: это довольно упрощенная операция для выполнения в прямом T-SQL, и делать это в SQLCLR слишком сложно, труднее и дороже в обслуживании, и намного медленнее. Этот код выполняет 10 000 отдельных транзакций, тогда как его можно легко сделать как один запрос на основе набора (то есть одна транзакция). Вы можете заключить свой for цикл в транзакцию, которая ускорит его, но он все равно всегда будет медленнее, чем подход T-SQL, основанный на наборах, поскольку он все еще должен выдавать 10 000 отдельных операторов INSERT. Вы можете легко выполнить рандомизацию в T-SQL, используя NEWID() или CRYPT_GEN_RANDOM, который был представлен в SQL Server 2008. (см. раздел ОБНОВЛЕНИЕ ниже)

Если вы хотите узнать больше о SQLCLR, ознакомьтесь с серией статей, которые я пишу для SQL Server Central: Stairway to SQLCLR (требуется бесплатная регистрация).

ОБНОВЛЕНИЕ

Вот чистый метод T-SQL для генерации этих случайных данных с использованием значений из вопроса. Легко добавить новые значения к любой из 4 табличных переменных (чтобы увеличить количество возможных комбинаций), поскольку запрос динамически регулирует диапазон рандомизации, чтобы соответствовать любым данным, содержащимся в каждой табличной переменной (то есть строках 1 - n).

DECLARE @TelNumber TABLE (TelNumberID INT NOT NULL IDENTITY(1, 1),
                          Num VARCHAR(30) NOT NULL);
INSERT INTO @TelNumber (Num) VALUES ('1525407'), ('5423986'), ('1245398'), ('32657891'),
                                    ('123658974'), ('7896534'), ('12354698');

DECLARE @FirstName TABLE (FirstNameID INT NOT NULL IDENTITY(1, 1),
                          Name NVARCHAR(30) NOT NULL);
INSERT INTO @FirstName (Name) VALUES ('Babak'), ('Carolin'), ('Martin'), ('Marie'),
                  ('Susane'), ('Michail'), ('Ramona'), ('Ulf'), ('Dirk'), ('Sebastian');

DECLARE @LastName TABLE (LastNameID INT NOT NULL IDENTITY(1, 1),
                         Name NVARCHAR(30) NOT NULL);
INSERT INTO @LastName (Name) VALUES ('Bastan'), ('Krause'), ('Rosner'),
                  ('Gartenmeister'), ('Rentsch'), ('Benn'), ('Kycik'), ('Leuoth'),
                  ('Kamkar'), ('Kolaee');

DECLARE @Address TABLE (AddressID INT NOT NULL IDENTITY(1, 1),
                        Addr NVARCHAR(100) NOT NULL);
INSERT INTO @Address (Addr) VALUES ('Deutschlan Chemnitz Sonnenstraße 59'), (''),
  ('Deutschland Chemnitz Arthur-Strobel straße 124'),
  ('Deutschland Chemnitz Brückenstraße 3'),
  ('Iran Shiraz Chamran Blvd, Niayesh straße Nr.155'), (''),
  ('Deutschland Berlin Charlotenburg Pudbulesky Alleee 52'),
  ('United State of America Washington DC. Farbod Alle'), ('');

DECLARE @RowsToInsert INT = 10000;

;WITH rowcounts AS
(
  SELECT (SELECT COUNT(*) FROM @TelNumber) AS [TelNumberRows],
         (SELECT COUNT(*) FROM @FirstName) AS [FirstNameRows],
         (SELECT COUNT(*) FROM @LastName) AS [LastNameRows],
         (SELECT COUNT(*) FROM @Address) AS [AddressRows]
), nums AS
(
  SELECT TOP (@RowsToInsert)
         (CRYPT_GEN_RANDOM(1) % rc.TelNumberRows) + 1 AS [RandomTelNumberID],
         (CRYPT_GEN_RANDOM(1) % rc.FirstNameRows) + 1 AS [RandomFirstNameID],
         (CRYPT_GEN_RANDOM(1) % rc.LastNameRows) + 1 AS [RandomLastNameID],
         (CRYPT_GEN_RANDOM(1) % rc.AddressRows) + 1 AS [RandomAddressID]
  FROM   rowcounts rc
  CROSS JOIN msdb.sys.all_columns sac1
  CROSS JOIN msdb.sys.all_columns sac2
)
-- INSERT dbo.Unsprstb(Firstname, Lastname, Tel, Address)
SELECT fn.Name, ln.Name, tn.Num, ad.Addr
FROM   @FirstName fn
FULL JOIN nums
        ON nums.RandomFirstNameID = fn.FirstNameID
FULL JOIN @LastName ln
        ON ln.LastNameID = nums.RandomLastNameID
FULL JOIN @TelNumber tn
        ON tn.TelNumberID = nums.RandomTelNumberID
FULL JOIN @Address ad
        ON ad.AddressID = nums.RandomAddressID;

Примечания:

• FULL JOINs необходимы вместо INNER JOINs, чтобы получить все @RowsToInsert количество строк.
• Дублирующиеся строки возможны из-за самой природы этой рандомизации и отсутствия их фильтрации с помощью DISTINCT. Однако DISTINCT нельзя использовать с заданными образцами данных в вопросе, поскольку количество элементов в каждой переменной массива / таблицы обеспечивает только 6300 уникальных комбинаций, а запрошенное количество строк для генерации составляет 10 000. Если к табличным переменным добавляются дополнительные значения, так что общее количество возможных уникальных комбинаций превышает запрошенное количество строк, то либо ключевое слово DISTINCT может быть добавлено в nums CTE, либо запрос может быть реструктурирован так, чтобы просто CROSS JOIN всю таблицу переменную, включите поле ROW_COUNT() и возьмите TOP(n), используя ORDER BY NEWID().
• INSERT закомментирован, чтобы было легче увидеть, что приведенный выше запрос дает желаемый результат. Просто раскомментируйте INSERT, чтобы запрос выполнял фактическую операцию DML.

Внутри кода SQL CLR C # вам не следует создавать соединение с явным указанием сервера, имени базы данных и учетных данных - вместо этого используйте connnectino "context":

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    // do your stuff here...
}