1)在SqlServer2000(SP4)上创建了一个视图:
CREATE VIEW vw_TZ_ForTest
AS
SELECT A.ulReserved AS 'TimeZone', B.UseXXX AS 'DaylightSave', B.StartDateType AS 'StartType',
B.StartMonth AS 'Start month', B.StartDay AS 'Start date', B.StartWeekSEQ AS 'Start week sequence',
B.StartWeek AS 'Start week', CAST(replace(B.StartTime,':','&') AS CHAR(8)) AS 'StartTime',
B.EndDateType AS 'EndType', B.EndMonth AS 'End month', B.EndDay AS 'End date', B.EndWeekSEQ AS 'End week sequence',
B.EndWeek AS 'End week', CAST(replace(B.EndTime,':','&') AS CHAR(8)) AS 'EndTime',
B.DaylightBias AS 'AdjustMethod'
FROM GLOBELRESERVEDSTABLE A ,T_DaylightSavingTime B
WHERE A.ulClassIndex = 1000 AND A.ulObjectIndex =1000 AND A.ulFieldName = 1000 AND A.ulFieldValue = 1000 AND 1=1
CREATE VIEW vw_TZ_ForTest
AS
SELECT A.ulReserved AS 'TimeZone', B.UseXXX AS 'DaylightSave', B.StartDateType AS 'StartType',
B.StartMonth AS 'Start month', B.StartDay AS 'Start date', B.StartWeekSEQ AS 'Start week sequence',
B.StartWeek AS 'Start week', CAST(replace(B.StartTime,':','&') AS CHAR(8)) AS 'StartTime',
B.EndDateType AS 'EndType', B.EndMonth AS 'End month', B.EndDay AS 'End date', B.EndWeekSEQ AS 'End week sequence',
B.EndWeek AS 'End week', CAST(replace(B.EndTime,':','&') AS CHAR(8)) AS 'EndTime',
B.DaylightBias AS 'AdjustMethod'
FROM GLOBELRESERVEDSTABLE A ,T_DaylightSavingTime B
WHERE A.ulClassIndex = 1000 AND A.ulObjectIndex =1000 AND A.ulFieldName = 1000 AND A.ulFieldValue = 1000 AND 1=1
解决方案 »
- sql统计问题
- 一个简单的sql语句,帮俺解决一下,在线等!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- SQLServer2000如何判断表是否存在
- 问一个简单的update语句
- 求一省市县查询的sql语句,在线等。
- 如何减少逻辑磁盘读取次数
- 月小计统计问题
- 如何得到SQl数据库的各种信息包括 大小,已用空间,空闲空间
- 请问SQL SERVER 2000中,怎样对结果集加序号字段?
- 运行vb的RDS或运行html与rds结合应用的程序为何要装上sql server7.0才执行?
- mssql 2000 如何修改dbo用户登录名?或者是给新建用户赋予最大dbo权限?
- 如何获得远程服务器的数据库?
CREATE TABLE [T_DaylightSavingTime] (
[UseXXX]xxx [INT] NOT NULL,
[StartDateType] [INT] NOT NULL,
[StartMonth] [INT] NOT NULL,
[StartDay] [INT] NOT NULL,
[StartWeekSEQ] [INT] NOT NULL,
[StartWeek] [INT] NOT NULL,
[EndDateType] [INT] NOT NULL,
[EndMonth] [INT] NOT NULL,
[EndDay] [INT] NOT NULL,
[EndWeekSEQ] [INT] NOT NULL,
[EndWeek] [INT] NOT NULL,
[StartTime] [VARCHAR](100) NOT NULL,
[EndTime] [VARCHAR](100) NOT NULL,
[DaylightBias] [INT] NOT NULL ) ON [PRIMARY]
GO
3)在程序中运行bcp.exe导出数据:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe "SELECT * FROM vw_TZ_forTEST" queryout "D:\TZ.txt" -c -U sa -P -T -q -e "D:\vw_TZ_forTEST.log"
4)该表所在的库创建时也未指定显示排序规则
1)在实例安装时选择了Chinese_PRC_BIN的服务器上运行正常(在中文系统上选择此排序规则)。
2)在实例安装时选择了Latin1_General_BIN的服务器上运行失败,报排序规则问题:
SQLState = 37000, NativeError = 446
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for replace operation.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
3)接上,将StartTime/EndTime显示指定排序规则为Latin1_General_BIN,则运行正确。
ALTER TABLE T_DaylightSavingTime ALTER COLUMN StartTime
varchar(100) COLLATE Latin1_General_BIN NOT NULL
GO
ALTER TABLE T_DaylightSavingTime ALTER COLUMN EndTime
varchar(100) COLLATE Latin1_General_BIN NOT NULL
GO想确认:
1)按照SqlServer2000的说明,如果创建库时为指定排序规则,则使用实例的默认排序规则。创建表时未指定排序规则,则使用库的默认排序规则。
从这里来看,应该没有排序规则冲突啊,为什么会发生Replace排序规则冲突。
2)为什么在排序规则为Chinese_PRC_BIN的实例中不会出错,而在排序规则为Latin1_General_BIN的实例中却会发生错误。
2)从长远来看,想做成库/表都不显示指定排序规则,只根据实例安装的排序规则决定(因为有中英文版本之分),
可是本地测试中,取消了库/表(其他表有指定)的显示指定,最后做出的安装盘安装后,还是发生上述的错误。
那该怎样写创建库/表的Sql文呢?是不是都定义成nvarchar,nchar,ntext会比较好?
ColumnName collate Chinese_PRC_BIN
这样的方式即可,
如:
SELECT (A.ulReserved collate Chinese_PRC_BIN) AS 'TimeZone' ,....
[EndTime] [VARCHAR](100) COLLATE Latin1_General_BIN NOT NULL, 试试