月度归档：2019年04月

excel导入sqlserver时，数据为null的处理方法分享


摘要:
下文讲述sqlserver中导入excel时候，出现null的处理方法
实验环境:sqlserver 2008 R2

今天收到同事的一份客户资料，需要将excel导入至数据库中，但是当我导入进数据库时，发现身份证一栏中，有些导入为null,通过仔细比对发现
身份证号码中，存在字母的数据都导入进数据库了，其它全是数字的身份证号码都变为NULL了。

查阅了很多网上资料，发现sqlserver会对导入的数据做一个自动判断，并建立相应的数据类型,当sqlserver建立数值类型后，字符数据就无法导入数据表中，
此时则会导入null,问题就出在sqlserver数据类型上，知道问题后，此时我们只需保证excel中所有类型都为字符型就可以解决此类异常现象。

下文讲述处理此异常的终极方法，如下所示:

--将获取excel列中的数据类型设置为混合数据类型  IMEX =1,
---当遇到混合数据类型列时，强制使用nvarchar和ntext数据类型

---例: 将C盘中的test.xls文件导入至表tableTest中。
  use [数据库名称]
  go;
   SELECT * INTO tableTest
FROM OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="C:\test.xls";Extended properties="Excel 5.0;HDR=Yes;IMEX=1;"')...[Sheet1$]

---注意：
---[Sheet1$] 表示excel中第一页Sheet


当运行时出现 “消息 15281，级别 16，状态 1，第 1 行”时，此时我们需开启Ad Hoc Distributed Queries 组件，配置方法如下所示:

—开启方法

exec sp_configure ‘show advanced options’,1
reconfigure

exec sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure

go

/*导入excel脚本*/

—关闭方法

exec sp_configure ‘Ad Hoc Distributed Queries’,0
reconfigure
exec sp_configure ‘show advanced options’,0
reconfigure
go

mssql sqlserver 创建非对称加密密钥的方法分享


摘要:
下文讲述使用sql脚本创建非对称密钥的方法
实验环境:sqlserver 2008 R2

在sqlserver中创建非对称的密钥，我们需采用create asymmetric key 系统函数创建密钥。

CREATE ASYMMETRIC KEY 语法说明:

CREATE ASYMMETRIC KEY Asym_Key_Name
[ AUTHORIZATION database_principal_name ]
[ FROM ]
[ WITH ]
[ ENCRYPTION BY ]
[ ; ]
::=
FILE = ‘path_to_strong-name_file’
| EXECUTABLE FILE = ‘path_to_executable_file’
| ASSEMBLY Assembly_Name
| PROVIDER Provider_Name

::=
ALGORITHM =
|
PROVIDER_KEY_NAME = ‘key_name_in_provider’
|
CREATION_DISPOSITION = { CREATE_NEW | OPEN_EXISTING }

::=
{ RSA_4096 | RSA_3072 | RSA_2048 | RSA_1024 | RSA_512 }

::=
PASSWORD = ‘password’
—参数说明——
FROM Asym_Key_Source
指定从中加载非对称密钥对的源。
AUTHORIZATION database_principal_name
指定非对称密钥的所有者。 所有者不能是角色或组。缺省值为当前用户。
FILE =’path_to_strong-name_file’
指定从中加载密钥对的强名称文件所在的路径。

备注

此选项在包含数据库中不可用。
EXECUTABLE FILE =’path_to_executable_file’
指定从中加载公钥的程序集文件。 由 MAX_PATH 根据 Windows API 限制为 260 个字符。
ASSEMBLY Assembly_Name
指定从中加载公钥的程序集的名称。
ENCRYPTION BY 指定密钥的加密方式。 可以为证书、密码或非对称密钥。
KEY_NAME =’key_name_in_provider’
指定来自外部提供程序的密钥名称。
CREATION_DISPOSITION = CREATE_NEW
在可扩展的密钥管理设备上创建新密钥。 PROV_KEY_NAME 必须用于指定设备上的密钥名称。 如果密钥已存在于设备上，此语句将失败，并显示错误。
CREATION_DISPOSITION = OPEN_EXISTING
将 SQL Server 非对称密钥映射到现有可扩展的密钥管理密钥。 PROV_KEY_NAME 必须用于指定设备上的密钥名称。 如果未提供 CREATION_DISPOSITION = OPEN_EXISTING，则默认值为 CREATE_NEW。
ALGORITHM =
可以提供五种算法；RSA_4096、RSA_3072、RSA_2048。
PASSWORD = ‘password’
指定用于对私钥进行加密的密码。 如果未提供该子句，则使用数据库主密钥对私钥进行加密。 password 最多为 128 个字符。 password 必须符合运行 SQL Server 实例的计算机的 Windows 密码策略要求。
备注说明:
“非对称密钥”是数据库级的安全对象实体。 该实体的默认格式包含公钥和私钥。 当未使用 FROM 子句执行时，CREATE ASYMMETRIC KEY 会生成新的密钥对。 当使用 FROM 子句执行时，CREATE ASYMMETRIC KEY 会从文件中导入密钥对，或从程序集中导入公钥。
默认情况下，私钥受数据库主密钥保护。 如果尚未创建任何数据库主密钥，则需要使用密码保护私钥。 如果不存在数据库主密钥，则可以选择性地使用密码。
私钥的长度可以为 512、1024 或 2048 位。


CREATE ASYMMETRIC KEY 创建举例:

例1:
采用 RSA_2048加密算法创建名称为 “testKey”的非对称密钥，并使用密码”abcwewe_AL”保护密钥 

CREATE ASYMMETRIC KEY testKey   
    WITH ALGORITHM = RSA_2048   
    ENCRYPTION BY PASSWORD = 'abcwewe_AL';   
   go

例2:
从指定文件中创建密钥

CREATE ASYMMETRIC KEY testKey AUTHORIZATION sa   
    FROM FILE = 'D:\test\test20190418.tmp'    
    ENCRYPTION BY PASSWORD = 'abcwewe_AL';  
GO

例3:
从 EKMProviderTest密钥管理器中的程序keyTest密钥生成相关密钥 

CREATE ASYMMETRIC KEY testKey   
    FROM PROVIDER EKMProviderTest  
    WITH   
        ALGORITHM = RSA_2048,   
        CREATION_DISPOSITION = CREATE_NEW  
        , PROVIDER_KEY_NAME  = 'keyTest' ;  
GO

mssql sqlserver 使用sql脚本对数据按照1,2,3 1,2,3 一批一批数据进行排序


摘要:
下文讲述使用row_number对数据按照1,2,3类别的排序方法分享，如下所示:
实验环境:sql server 2008 R2

例:
今天领导要求我们对一个数据表进行如下排序:
序号:
1
2
3
1
2
3

实现思路:
下文使用over开窗函数对数据进行群组排序，再结合order by对数据进行二次排序

   create table #maomao365([序号] int)
insert into  #maomao365([序号])
select 1 union all
select 2 union all
select 3 union all
select 1 union all
select 2 union all
select 3
Go
--测试数据结束
SELECT [序号]
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY [序号] ORDER BY [序号]) keyId  FROM #maomao365) t
ORDER BY t.keyId,t.[序号];

go

truncate table   #maomao365 
drop  table #maomao365
mssql_sqlserver_排序应用_8418

mssql_sqlserver_排序应用_8418