Tag Archives: SQL

小心 MAX

image

廣告

SSMS 查詢文字結果過長

雖然 Grid 設定 Max 可達 64K,但實際 4 萬多字可能就被截斷了,最好是轉成 XML 呈現。

OPENROWSET 取 Tab 分隔檔

SQL 2017 以前的做法

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

https://dba.stackexchange.com/questions/29306/can-i-query-a-tab-delimited-file-from-ssms

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

髒資料髒進髒出

image

image

是否啟用壓縮

SELECT ix.name,st.name,schema_name(st.schema_id)
FROM sys.partitions SP
JOIN sys.tables ST ON st.object_id = sp.object_id
left JOIN sys.sysindexes IX ON sp.object_id = ix.id and sp.index_id = ix.indid
WHERE sp.data_compression = 0

內縮在 Python 是程式區塊…

這系列的練習錯誤不少,自行修正後,最終可執行…

內縮在 Python 是程式區塊,https://docs.microsoft.com/zh-tw/sql/advanced-analytics/tutorials/sqldev-py5-train-and-save-a-model-using-t-sql 範例中因為有內縮:

image

會造成如下的錯誤:

訊息 39004,層級 16,狀態 19,行 37
執行 ‘sp_execute_external_script’ 時發生 ‘Python’ 指令碼錯誤,HRESULT 為 0x80004004。
訊息 39019,層級 16,狀態 2,行 37
發生外部指令碼錯誤:

Error in execution.  Check the output for more information.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
  File "C:\PROGRA~1\MICROS~1\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\3023566F-9F2F-4E78-823E-5E38CC9EED66\sqlindb.py", line 33
    import numpy
    ^
IndentationError: unexpected indent

SqlSatelliteCall error: Error in execution.  Check the output for more information.
來自外部指令碼的 STDOUT 訊息:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER1000\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER1000\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

只要把內縮部分砍掉,就可以正常執行

下一階段的練習也有相同的問題:https://docs.microsoft.com/zh-tw/sql/advanced-analytics/tutorials/sqldev-py6-operationalize-the-model

且參數有寫錯,修正後皆可執行

將 object owner 非 dbo 的改成 dbo

declare cur cursor for
SELECT ‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + [name] + ‘]’
FROM sys.objects
WHERE USER_NAME(OBJECTPROPERTY(object_id, ‘OwnerId’)) not in(‘dbo’,’sys’) and is_ms_shipped=0
declare @obj nvarchar(1000),@sql nvarchar(1000)
open cur
fetch cur into @obj
while @@FETCH_STATUS=0
begin
    set @sql=’alter authorization on ‘ + @obj  + ‘ to dbo’
    –print @sql
    exec(@sql)
    fetch cur into @obj
end
close cur
deallocate cur

rename 預存程序後,最好再 alter 一遍

聽課的朋友提出的有趣問題,sp rename 後,查詢 sp_helptext 的 create proc 部分卻使用舊的預存程序名稱。實作範例如下:

 

use tempdb
go
create proc sp_a
as
print ‘hello’
go
exec sp_rename ‘sp_b’,’sp_b’

–rename 後保有語法定義 metadata 的內容不會變,所以會回傳如下的警告

Caution: Changing any part of an object name could break scripts and stored procedures.

select object_name(id) objectame,* from syscomments
select object_name(object_id) objectname,* from sys.sql_modules

image

 

–所以取回錯誤的結果

sp_helptext ‘sp_b’
go

image

 

–可以再建同名的 sp

create proc sp_a
as
print ‘hello a’

exec sp_helptext ‘sp_a’
exec sp_helptext ‘sp_b’
go

 

–有趣的是利用 object explorer 會對

image

–但透過 profiler 到的語法如下

exec sp_executesql N’SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)’,N’@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)’,@_msparam_0=N’P’,@_msparam_1=N’RF’,@_msparam_2=N’PC’,@_msparam_3=N’sp_c’,@_msparam_4=N’dbo’

查詢的依然是 sys.sql_modules,所以仍是錯的,顯然 object explorer 會自行依物件名字改正產出的語法

–alter 之後會改成正確的 create proc sp_b

alter proc sp_b
as
print ‘hello’

所以 rename 之後,最好再 alter 一遍,保證 metadata 的正確性

從 Oracle 轉 SQL 資料發生 PK 重複

透過 Linked Server 回 Oracle 查失敗的紀錄,在 Oracle 的資料表內顯然有使用者輸入時,可以敲入 FF 導致眼睛看不到,但卻重複兩筆的 PK

select * from openquery(LinkedServer,’select utl_raw.cast_to_raw(PKCol) b, PKCol from Schema.Table where PKCol like “%DuplicateKey%"‘)

image

無題,效能調教標本

純粹留紀念

image