Объединить командную строку и T-SQL – SQL Server

Я пытаюсь выполнить некоторые sqlcmd через T-SQL на SQL Server 2008. Есть часть моего кода, где я проверяю размер файла данных, и если размер файла данных не равен 0, тогда начните удалять определенную таблицу, чтобы Я могу использовать BCP в новых данных.

Ниже мой код, который не выполняется:

SET @myVariable = ' SETLOCAL FOR %%R IN (X:\Main Folder\Data\'[email protected]+'_'[email protected]+'.dat) DO SET size=%%~zR IF %size% NEQ 0 ( SQLCMD -E -S my-server-name -Q "DELETE FROM '[email protected]+'.'[email protected]+'.'[email protected]+';" >> X:\Main Folder\Log\Log.txt )' EXEC master..xp_cmdshell @myVariable 

По какой-то причине, когда я выполняю свою хранимую процедуру, приведенный выше код кажется пропущенным, потому что он не отбрасывает никаких сообщений об ошибках.

EDIT: после повторной настройки интервала и моего кода, @myVariable, выполняется сейчас. Тем не менее, он по-прежнему не работает в отношении того, что он по-прежнему удаляет таблицу, даже если размер файла данных = 0. Однако, когда я жестко кодирую его в пакетном файле, он отлично работает. Есть идеи?

Вам нужно использовать одиночный % в цикле for поскольку вы не выполняете код в пакетном файле (для этого требуется %% ), см. Этот пост для дальнейшего уточнения. Итак, ваш цикл for должен быть:

 FOR %R IN (X:\Main Folder\Data\'[email protected]+'_'[email protected]+'.dat) DO SET size=%~zR 

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

Ответ Жако выглядит правильно, и я уверен, что это было частью проблемы. Вероятно, вы должны инициализировать size чтобы быть в безопасности:

 SET @myVariable = ' SETLOCAL SET size=0 FOR %R IN ("X:\Main Folder\Data\'[email protected]+'_'[email protected]+'.dat") DO SET size=%~zR IF %size% NEQ 0 ( SQLCMD -E -S my-server-name -Q "DELETE FROM '[email protected]+'.'[email protected]+'.'[email protected]+';" >> "X:\Main Folder\Log\Log.txt" )' EXEC master..xp_cmdshell @myVariable 

Без кавычек цикл for будет рассматривать, что его «набор» (терминология, используемая for /? ), Как два элемента, разделенных пробелом. Если ваш текущий каталог X:\Main Folder\Data\ он все равно будет работать, поскольку он видит последний в качестве относительного пути к файлу .dat и затем все еще set правильное значение на последнем проходе.

Зачем вам вообще «спускаться» в командную строку? (есть причины, по которым xp_cmdshell отключен по умолчанию)

Не могли бы вы просто зациклиться на своих таблицах ( sys.tables WHERE name LIKE ... ), а затем

  • создать теневую копию таблицы ( SELECT INTO )
  • BULK INSERT из (ожидаемого) файла в теневую таблицу (в TRY..CATCH для обработки ситуаций, когда файл не существует или пуст или поврежден, ..
  • если в теневой таблице есть данные, то DELETE фактические записи таблицы и переместить данные за
  • если в теневой таблице нет данных, тогда DELETE фактические записи таблицы (или оставьте их в том случае, если вы предполагаете, что отсутствует или пустой файл bcp означает, что он поступит позже, и вы застряли с текущей версией на данный момент)
  • DROP теневой стол снова

Не уверен, что это вариант для вас, но поскольку вы находитесь на SQL 2008, вы можете использовать команду powershell:

 DECLARE @File varchar(100), @outputFile varchar(100) DECLARE @cmd varchar(1000) SELECT @File = 'path_to_file' SELECT @outputFile = 'path_to_output_file' SELECT @cmd = 'powershell.exe -command "if((Get-Item '''[email protected]+''').length -gt 0) {&sqlcmd.exe -E -S SERVERNAME -Q ''SELECT name FROM master.sys.databases ;'' -o '[email protected]outputFile+'}"' SELECT @cmd exec master..xp_cmdshell @cmd 

Я проверил и, похоже, работает в зависимости от размера файла.

Я не могу говорить с вашей командой DOS. Однако я могу предложить использовать Ole Automation Procedures для получения размера файла. Таким образом, вам не придется полагаться на запуск командных команд.

Сначала вам необходимо включить Ole Automation Procedures на вашем экземпляре SQL Server следующим образом:

 sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO 

Вам нужно сделать это только один раз.


Далее приведен скрипт, который получает размер файла. В примере предполагается, что есть файл C:\Temp\testfile.txt . Сценарий выбирает размер, если файл существует, или выбирает 0, если это не так. Вы можете взять этот сценарий в качестве примера, чтобы делать то, что вы хотите, на основе размера.

Вот оно:

 DECLARE @hr INT; DECLARE @size INT; DECLARE @obj_file INT; DECLARE @obj_file_system INT; DECLARE @file_name VARCHAR(100)='C:\Temp\testfile.txt'; -- Create a FileSystemObject. Create this once for all subsequent file manipulation. Don't forget to destroy this object once you're done with file manipulation (cf cleanup) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @obj_file_system OUT; IF @hr<>0 GOTO __cleanup; -- Get a handle for the file. Don't forget to release the handle for each file you get a handle for (see cleanup). The return will be different from 0 if the file doesn't exist EXEC @hr = sp_OAMethod @obj_file_system, 'GetFile', @obj_file out, @file_name; IF @hr<>0 GOTO __print_file_size; -- Retrieve the file size. EXEC sp_OAGetProperty @obj_file, 'size', @size OUT; __print_file_size: SELECT ISNULL(@size,0) AS file_size; __cleanup: EXEC sp_OADestroy @obj_file_system; EXEC sp_OADestroy @obj_file; 

Вы используете X:\ в своем коде. Но код работает под учетной записью службы для SQL Server. У этой учетной записи может быть x: нет.

Я бы предложил использовать UNC вместо сопоставленного диска. Кроме того, убедитесь, что ваша служба запущена под учетной записью домена и что учетная запись домена имеет все необходимые разрешения для UNC.

Я понял, что я могу проверить количество таблиц вместо размера файла данных, используя этот метод:

 SET @sqlCheck = 'SQLCMD -E -S ServerA -Q "IF (SELECT COUNT(*) FROM '[email protected]+'.'[email protected]+'.'[email protected]+') > 0 BEGIN DELETE FROM ServerB.'[email protected]+'.'[email protected]+'.'[email protected]+' END;"' EXEC MASTER..xp_cmdshell @sqlcheck 

Кажется, вы уже знаете имена баз данных и таблиц, поэтому вы можете использовать следующее, которое в основном выполняет DIR для файла, который вы ищете, и проверяет, является ли оно « '0 bytes' , если он делает то, что вы хотите , Что нужно отметить:

  • STRING TEMPLATES – При создании строк мне нравится создавать «шаблон», а затем заменять внутри строки. Это хороший способ убедиться, что у вас есть правильное количество цитат, скобок и т. Д. Я сделал это дважды здесь, один раз, чтобы создать команду DIR, а затем снова создать команду TRUNCATE.

  • TRUNCATE – хотя и не является частью вашего вопроса, вы можете использовать TRUNCATE вместо DELETE FROM . Если в вашей таблице было миллион строк, DELETE FROM может занять 2 минуты, где TRUNCATE всегда будет выполнять 0 секунд.

Ваш ответ:

 SET NOCOUNT ON; DECLARE @DatabaseName VARCHAR(50) = 'database1' DECLARE @TableName VARCHAR(50) = 'table1' DECLARE @PathTemplate VARCHAR(50) = 'dir c:\temp\{@DatabaseName}_{@TableName}.txt' SET @PathTemplate = REPLACE(@PathTemplate, '{@DatabaseName}', @DatabaseName); SET @PathTemplate = REPLACE(@PathTemplate, '{@TableName}', @TableName); DECLARE @FileNames AS TABLE (FileNames VARCHAR(100)) INSERT @FileNames (FileNames) exec xp_cmdshell @PathTemplate IF EXISTS ( SELECT 1 FROM @FileNames WHERE FileNames LIKE '%0 bytes') BEGIN PRINT 'No Content/Missing File' END ELSE BEGIN DECLARE @SqlExc VARCHAR(500) = 'TRUNCATE TABLE [{@DatabaseName}].[dbo].[{@TableName}]' SET @SqlExc = REPLACE(@SqlExc, '{@DatabaseName}', @DatabaseName); SET @SqlExc = REPLACE(@SqlExc, '{@TableName}', @TableName); PRINT @SqlExc -- sp_executesql @SqlExc <-- Do this in production END 
  • Переименование базы данных SQL Server из SQLCMD
  • Удалить заголовок столбца из результата запроса SQL Server
  • Можете ли вы заставить SQLCMD сразу запускать каждое утверждение в скрипте без использования «GO»?
  • Sqlcmd: ошибка: собственный клиент Microsoft SQL Server 10.0: произошла ошибка, связанная с сетью или конкретным экземпляром, в то время как
  • SQL Server: INSERT / UPDATE / DELETE не удалось, так как следующие параметры SET имеют неправильные настройки: 'QUOTED_IDENTIFIER'
  • SQLCMD - попытка сбросить базу данных с буквенно-цифровым именем «14Data» дает синтаксическую ошибку
  • Как запустить sqlcmd удаленно на машине, которая не имеет SSMS
  • Не удается запустить Invoke-Sqlcmd в файле PS1, но может на консоли
  • Использование sqlcmd с FOR XML возвращает Недопустимое имя объекта 'dbo.Table', но запрос выполняется в SSMS
  • Переменная Escape в sqlcmd / Invoke-SqlCmd
  • SQLCMD останавливается сначала GO, он достигает скрипта
  • Interesting Posts

    Диспетчер соединений SSIS не хранит пароль SQL

    Восстановить базу данных SQL Server 2008 до SQL Server 2000

    Заполнение текстовых полей из строки базы данных SQL с использованием минимального кода

    SSIS импортирует дополнительные десятичные значения в таблицу sql назначения от excel

    Мастер импорта SQL Server обрабатывает NULL как строку буква «NULL»

    Почему этот курсор вставляет одни и те же значения в каждую строку?

    Медленный поиск данных SQL с помощью SqlDataReader.Read () в C # vs SSMS

    Создать PK для таблицы #temp не удалось, когда скрипт запускается параллельно

    Скремблировать столбец в SQL Server?

    Запрос MS SQL для отображения идентификатора максимальных повторяющихся столбцов

    Медленный запрос при подключении к связанному серверу

    nchar vs nvarchar производительность

    Получение строки, ближайшей к времени для каждой строки в другой таблице

    ВЫБОР ЗАЯВЛЕНИЯ В ПЕРЕМЕННОЙ

    Экзамен SQL – оценка проблемы размера таблицы

    Давайте будем гением компьютера.