Perguntas[sql-server](coding)

Aqui está uma consulta de procedimento armazenado simplificada que estou usando com Full Text Search:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        cd.Model            AS Model,
        u.Id                AS UserId,
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY]
    WHERE c.Status = 'Active'

Estou passando um parâmetro @SearchTerms, que às vezes pode ser nulo ou vazio. Estou pensando se é possível juntar o CONTAINSTABLEonly quando o parâmetro tem um valor. Eu tentei isso:

CASE 
    WHEN (@SearchTerms IS NOT NULL) OR (LEN(@SearchTerms)) > 0
    THEN JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY] 
END

mas não funcionou. Não tenho certeza se é possível ou se minha sintaxe está errada?

Tenho a seguinte consulta (que funciona e retorna o resultado esperado), mas ela está usando várias subconsultas para obtê-la COUNTe estou preocupado que a consulta seja muito ineficiente da forma como está escrita atualmente:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        (SELECT COUNT(*) FROM CarImage WHERE CarId = c.Id) AS ImageCount,
        (SELECT COUNT(*) FROM CarLike WHERE CarId = c.Id) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Minhas tentativas iniciais foram sem usar as subconsultas para o COUNT, o que consegui fazer funcionar até adicionar o , LEFT JOINo que distorceu os resultados de ambas as contagens:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        COUNT(ci.CarId) AS ImageCount,
        COUNT(cl.CarId) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Imagino que exista uma maneira de fazer a consulta nº 2 funcionar e que ela seja mais eficiente que a consulta nº 1?

Estou enfrentando um erro de referência não resolvido quando tento compilar meu projeto porque ele contém visualizações que fazem referência a outro banco de dados no mesmo servidor.

Quando tento adicionar uma referência de banco de dados, sou forçado a selecionar um aplicativo da camada de dados (.dacpac), um banco de dados do sistema ou um projeto de banco de dados na solução atual.

Por que não posso usar essas opções:

• O banco de dados do sistema não funciona → O banco de dados externo é um banco de dados do usuário, não master, msdb, model ou tempdb, então esta opção não é válida.

• Não quero adicionar o banco de dados à solução → Este banco de dados é gerenciado separadamente, e incluí-lo no repositório significaria manter um projeto adicional inteiro que não é necessário.

• .dacpac não é prático → O banco de dados referenciado muda com frequência, e manter o .dacpac atualizado no repositório AzDO exigiria atualizações manuais constantes.

Movendo dados do SQL Anywhere para o SQL Server e depois de alguns soluços estava indo bem até que recebi o erro

A coluna "A_Future" não pode ser modificada porque é uma coluna computada ou é o resultado de uma UNION'

Justo, eu estava inserindo dados de SELECT * -- então usei isso para retornar apenas colunas que não foram calculadas.

 'Remove computed columns from the SELECT query
 Dim DR() As DataRow = MSSQLDT.Select("Computed = 'N'")
 Dim vSelectedRows As Integer = DR.Count
 Dim vCurrentRow As Integer = 0
 strSQL = "SELECT "
 For Each Row In DR
 strSQL += Row("Name")
 vCurrentRow += 1
 If vCurrentRow = vSelectedRows Then
     strSQL += " "
 Else
     strSQL += ", "
 End If
Next
strSQL += "FROM " & vTable

Isso retorna a seguinte string de consulta...

SELECT Transaction_ID, Debit, Credit, Paid, P_Description, Document_Date, Supplier_ID, Nominal_Transaction, HOA_Code, Document_Saved, Document_ID, Document_No, Supplier_Inv_No, Type, Paid_Date, Part_Paid, Open_Editing, Editing_Name, Updated_Name, Updated, Reserve_Item, Hold, eCheck_Pending, eSig_Required, eSigOne_ID, eSigTwo_ID FROM A_Purchase_Ledger

.. e não inclui nenhuma coluna calculada. Executei novamente, mas ainda apresentou o mesmo erro (para todas as colunas calculadas)

Microsoft.Data.SqlClient.SqlException (0x80131904): The column "A_Future" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Current" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_30" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_60" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_90" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Older" cannot be modified because it is either a computed column or is the result of a UNION operator.

Isso não faz sentido agora, já que não estou tentando inserir dados neles, a menos que haja uma peculiaridade da qual SqlBulkCopyeu não esteja ciente?

Os dados SELECT são adicionados a e DataTableentão vão para esta função

  Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
  Try
      Using Conn As New Microsoft.Data.SqlClient.SqlConnection
          If IsHASoftware = True Then
              Conn.ConnectionString = HASConString
          Else
              Conn.ConnectionString = ReturnConnStringMS(HOAID)
          End If
          Conn.Open()
          Using vTrans = Conn.BeginTransaction
              Using vBulk As New Microsoft.Data.SqlClient.SqlBulkCopy(Conn, Microsoft.Data.SqlClient.SqlBulkCopyOptions.Default, vTrans)
                  vBulk.DestinationTableName = TableName
                  vBulk.WriteToServer(DT)
              End Using
              vTrans.Commit()

          End Using
          Conn.Close()

      End Using
      Return True
  Catch ex As Exception
      EmailError(ex, 222, PageName)
      Return False
  End Try
 End Function

Estou tentando criar um script para obter o nome do arquivo de log de um banco de dados, definir como recuperação simples, reduzir o arquivo de log desse banco de dados e definir novamente como completo.
Até agora, eu criei este script:

DECLARE @RestoreDbName nvarchar(2000);
declare @Name nvarchar(2000)

set @restoreDbName = 'dbname'


DECLARE cur CURSOR FOR
SELECT name
FROM [sys].[database_files] 
where [type] = 1 and name like @restoreDbName

ALTER DATABASE @RestoreDbName
SET RECOVERY SIMPLE;  
GO 

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur
GO  
       
 ALTER DATABASE @RestoreDbName
 SET RECOVERY FULL;  
 GO  

O script será executado a partir de um PowerShell, não gera nenhuma mensagem de erro, mas quando verifico o arquivo de log do banco de dados continua o mesmo de antes, não foi reduzido.

Alguma sugestão, o que estou fazendo errado?

Tenho uma tabela (vamos chamá-la de Packages) que cresce continuamente e é basicamente alimentada por ordersId, um FK da Orderstabela.

O problema é que, devido à natureza simultânea do processo de pedido, onde só geramos um pacote DEPOIS que você paga seu pedido, a tabela cresce "não diretamente"...
Se você consultar a Packagestabela agora, o último orderIdna tabela será 12345, e se você consultar novamente em alguns minutos SERÁ um número maior, como 12350, mas isso não significa que os registros foram inseridos em ordem... talvez 12350tenha sido a primeira pessoa a pagar e a próxima será 12346...

Este cenário/tabela é um bom candidato para OPTIMIZE_FOR_SEQUENTIAL_KEY?

Tenho uma XMLvariável contendo uma tabela. Esta tabela tem um número de colunas e uma linha. Nas duas primeiras colunas, preciso atualizar seus valores com o resultado de um procedimento armazenado - inserindo um valor se estiver vazio e atualizando o valor se ele já existir.

Isto é o que eu tenho até agora. Por favor, note que os nomes/estrutura dos elementos na tabela são verdadeiros para como eles serão na realidade (ou seja, todas as linhas são chamadas de "Linha" e todas as colunas são chamadas de "Coluna"). Isto é devido a uma restrição do programa que produz a tabela XML.

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
    <Column>some data</Column>
    <Column>some more data</Column>
  </Row>
</Table>'

-- View xml table prior to modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

-- Declare new id and date variables. This will come from a stored procedure. I've made it static to simplify the example.
DECLARE @id int = 24
DECLARE @date datetime2(2) = sysutcdatetime()

-- If id is empty, insert the id value. Otherwise, update the id value.
IF (SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@id")} into (//Table/Row/Column)[1]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[1] with sql:variable("@id")')

-- If date is empty, insert the date value. Otherwise, update the date value
IF (SELECT tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@date")} into (//Table/Row/Column)[2]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[2] with sql:variable("@date")')

-- View xml table after modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

Isso parece funcionar; no entanto, estou pensando se há uma maneira de escrever a condição (ou seja, se estiver vazio, inserir; senão, substituir valor) dentro da própria função modify? Em outras palavras, posso evitar as IF...ELSEinstruções SQL e usar uma SETinstrução em vez disso?

Além disso, existe uma maneira de atualizar as duas colunas na mesma função de modificação ou isso precisa ser feito separadamente?

Desde já, obrigado!

EDIT Código de exemplo alterado para demonstrar que a tabela pode ter colunas adicionais e para esclarecer o formato da tabela XML.

Estou tentando exportar dados de um banco de dados do SQL Server para arquivos simples (destino como .CSV) para migrações. O problema que estou enfrentando é preservar a vírgula ( ,) no meu texto de conteúdo. Por exemplo, FruitsGroupa coluna contém ( Apple, Mango and Orange), mas .CSVestá criando a divisão dos valores separados por vírgula e bagunçando os arranjos das colunas. Como posso obter os mesmos dados em uma única Remarkscoluna.

Estou esperando esses dados no .csvarquivo (veja abaixo). Há uma solução com o uso ""while changing delimiter, mas não quero usar isso.

Isso está no meu banco de dados e deve refletir no meu .csvarquivo também

Este é o exemplo de dados defeituosos que estou obtendo em .csv:

Tenho uma consulta T-SQL semelhante a esta (um pouco mais complexa, simplificando aqui):

SELECT *
FROM ParticipantTable p
WHERE p.OrganizationId = @p__linq__0
  AND p.ParticipantId IN (
    @p__linq__1,
    @p__linq__2,
    @p__linq__3,
    @p__linq__4,
    @p__linq__5,
    @p__linq__6,
    @p__linq__7,
    @p__linq__8,
    @p__linq__9,
    @p__linq__10
)

Isso resulta em um plano de consulta semelhante a este aqui (recortado na parte relevante):

Fiquei surpreso com o Clustered Index Seek realizando uma comparação maior que/menor que em vez de uma pesquisa "normal" por valor de chave de entrada:

Seek Keys[1]: Prefix: [snip].OrganizationId = Scalar Operator([@p__linq__0]); Start: [snip].ParticipantId > Scalar Operator([Expr1031]); End: [snip].ParticipantId < Scalar Operator([Expr1032])

Por que isso? Buscar um intervalo em não é uniqueidentifierrealmente ineficiente, resultando facilmente na seleção de um intervalo muito amplo de valores? Não seria muito mais eficiente fazer um loop nos valores de entrada e buscar um por um? Ou, com apenas dez valores, puxar os valores diretamente para o operador seek?

Imagino que isso possa ser apenas uma otimização porque a organização em questão tem um número baixo de participantes, mas não seria mais sensato extrair os valores para o operador de busca?

Além disso, apenas por interesse acadêmico, há uma maneira de forçar o SQL Server a buscar nos valores em vez do intervalo? WITH (FORCESEEK([snip](OrganizationId, ParticipantId))não funciona, ele ainda buscará com maior que/menor que. Eu também adicionei OPTIMIZE FOR UNKNOWN, mas realmente não esperava que fizesse nada aqui (e não fez).

Consigo exportar os resultados do procedimento armazenado como CSV para a unidade local no SQL Server, mas estou com dificuldade para exportar os resultados para um servidor externo (ou local de unidade compartilhada).

Só consigo usar BCPo comando do prompt de comando, mas ele tem funcionalidade limitada. Além disso, o BCPcomando exporta apenas ASCII. Quero exportar como formato UTF-8 BOM.

Implementei os seguintes métodos para exportar os resultados como csv:

O sp_configure 'xp_cmdshell'já está ligado

1. Use BCPo comando do SQL Server:

   declare @sql varchar(8000) 
   
   select @sql = 'BCP "EXEC [dbName].[dbo].[StoredProcedureName]
                      @AreaID=NULL" queryout "\\serverlocation\Test\FileExport_Test_1.CSV"-T
                      -S SourceServerName -c -t,'     
   exec master..xp_cmdshell @sql
   

   Este comando está exportando csv ao exportar para o local do servidor

   Recebo este erro quando executo o comando acima:

   Erro = [Microsoft][SQL Server Native Client 11.0]Não é possível abrir o arquivo de dados do host BCP

2. Use o comando BCP no prompt de comando.

   C:\Windows\System32>BCP "EXEC [dbName].[dbo].[StoredProcedureName] @AreaID=NULL" queryout "\\serverlocation\Test\FileExport_Test_1.CSV"-T
         -S SourceServerName -c -t
   

   O arquivo é exportado para o local do servidor externo, mas não quero usar esse método porque quero usar um nome de arquivo dinâmico e passar valores de parâmetros dinâmicos para o procedimento armazenado.

3. Usar o Powershell:

   PS SQLSERVER:\> $filename = "\\serverlocation\Test\FileExport_Test_1"
   Invoke-Sqlcmd -Query "EXEC [dbName].[dbo].[StoredProcedureName] 
          @AreaID=NULL" -ServerInstance "SourceServerName" |
          Export-Csv -Path "$filename.csv" -NoTypeInformation
   

   Recebo o seguinte erro:

   Export-Csv: Não é possível abrir o arquivo porque o provedor atual (Microsoft.SqlServer.Management.PSProvider\SqlServer) não pode abrir um arquivo.
   Na linha:3 char:1

   • Export-Csv -Caminho "$filename.csv" -NoTypeInformation
   • CategoryInfo: Argumento inválido: (:) [Export-Csv], PSInvalidOperationException
   • FullyQualifiedErrorId: ReadWriteFileNotFileSystemProvider, Microsoft.PowerShell.Commands.ExportCsvCommand

4. Exporte o CSV do SSRS como Windows Share. Mas o CSV está adicionando aspas duplas em torno de um campo que tem aspas. Então, não é possível usar esse método para exportar CSV

Não estou muito familiarizado com o SSIS e aprender SSIS levará algum tempo e tenho que fornecer a solução em um prazo muito curto. :(

Por favor ajude