SQL Server - 随笔分类 - cw_volcano

查询Table name, Column name, 拼接执行sql文本, 游标, 存储过程, 临时表

摘要：018_Proc_UpdateTranslations.sql: 019_Cur_UpdateTranslations.sql : 阅读全文

posted @ 2016-10-13 14:34 cw_volcano 阅读(416) 评论(0) 推荐(0)

摘要：EXEC sp_addlinkedserver @server='srv_lnk', @srvproduct='', @provider='SQLOLEDB', @datasrc='HELIOS01.us.psav.dom\HELIOSLISTENER'EXEC sp_addlinkedsrvlog... 阅读全文

posted @ 2015-11-05 17:57 cw_volcano 阅读(1016) 评论(0) 推荐(0)

We need the sql script to query the table Ditronics.Kiosk.Journal to find journal with mismatch denom information versus amount.

摘要：CREATE TABLE #MoneyTable ( Id INT IDENTITY(1, 1) PRIMARY KEY , MoneyName VARCHAR(50) ... 阅读全文

posted @ 2014-05-23 16:17 cw_volcano 阅读(401) 评论(0) 推荐(0)

SQL Server触发器以及如何在SQL Server Manager中调试触发器

摘要：·只有inserted表有数据时，当前操作为insert；·inserted和deleted两张表都有数据时，当前操作为update；·只有deleted表有数据时，当前操作为delete。 1. 2. 3.如何调试触发器：一、打开SQL查询分析器二、将以下Sql语句复制到查询窗口并运行use p 阅读全文

posted @ 2014-02-22 13:15 cw_volcano 阅读(15144) 评论(0) 推荐(0)

机器更换登录密码重启，然后SQL Server登录不了

摘要：解决方法：阅读全文

posted @ 2014-02-09 09:49 cw_volcano 阅读(184) 评论(0) 推荐(0)

Never use GetDate() when comparing date timesoffsets, use SYSDATETIMEOFFSET()

摘要：-- my current uk time is 2014-01-09 10:43:00 +0DECLARE @userLocalTimeChina datetimeoffset(4) = '2014-01-09 18:43:00 +08:00';DECLARE @userLocalTimeLasVagas datetimeoffset(4) = '2014-01-09 02:43:00 -08:00';DECLARE @userUTCTime datetimeoffset(4) = '2014-01-09 10:43:00 +00:00';De 阅读全文

posted @ 2014-01-11 13:39 cw_volcano 阅读(318) 评论(0) 推荐(0)

Reason: Server is in single user mode. Only one administrator can connect at this time

摘要：单击Start→All Programs→MicrosoftSQLServer 2008→Configuration Tools→SQLServer Configuration Manager.右键单击面板右边的SQLServer ,然后在下拉菜单中选择Properties ,在SQLServer Properties 界面上，单击Advanced 选项卡。删除“;-m”,如下图所示，保存，重启SQL Server。阅读全文

posted @ 2013-08-21 17:12 cw_volcano 阅读(994) 评论(0) 推荐(0)

SQL Server Profiler

摘要：Then load upClickSetup like soAnd you can see all the sql transactions 阅读全文

posted @ 2013-02-19 09:35 cw_volcano 阅读(165) 评论(0) 推荐(0)

charindex函数介绍, Case函数语法

摘要：一、语法CHARINDEX ( char1 ,string1 [ , start_location ] ) 如果 char1 或 string1 之一是 Unicode 数据类型（nvarchar 或 nchar）而另一个不是，则将另一个转换为 Unicode 数据类型。CHARINDEX 不能与 text、ntext 和 image 数据类型一起使用。如果 char1 或 string1 之一为 NULL，并且数据库兼容级别为 70 或更高，则 CHARINDEX 将返回 NULL。如果数据库兼容级别为 65 或更低，则 CHARINDEX 将仅在 char1 和 string1 都为 NU 阅读全文

posted @ 2012-10-23 08:45 cw_volcano 阅读(440) 评论(0) 推荐(0)

SQL Server evaluation period has expired解决办法

摘要：问题现象：本地计算机上的 MSSQLSERVER 服务启动后又停止了。一些服务自动停止，如果它们没有什么可做的，例如“性能日志和警报”服务查看SQL日志发现：SQL Server evaluation period has expired.问题分析：估计是SQL SERVER2000评估板过期的原因，知道了原因，这就好办了！！如何在 SQL Server 2000 评估版过期后升级到 SQL Server 2000 零售版当 SQL Server 2000 评估版的 120 天评估期过期后，SQL Server 将无法启动。在 Windows 事件查看器应用程序日志中，您可能会看到下面的错误阅读全文

posted @ 2012-10-08 11:25 cw_volcano 阅读(9389) 评论(0) 推荐(0)

摘要：转：http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/To enable remote connection on SQL Server 2008 Express, see the step below:Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resou 阅读全文

posted @ 2012-10-08 11:04 cw_volcano 阅读(1990) 评论(0) 推荐(0)

MSDTC on server ‘Tim-GUO-PC’ is unavailable.

摘要：MSDTC on server ‘Tim-GUO-PC’ is unavailable.Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details:System.Data.SqlClient.SqlException: MSDTC 阅读全文

posted @ 2012-08-17 14:32 cw_volcano 阅读(300) 评论(0) 推荐(0)

HowTo Convert Table Values to Comma-Delimited String

摘要：We have 2 tables related as one-to-many. How can we fetch parent table field, and child table values comma-delimited ? The function below demonstrates a technique how to convert table values into comma-delimited string with one query.ALTER FUNCTION F_GET_STR(@p_order_id int)RETURNS varchar(1000)AS . 阅读全文

posted @ 2012-08-08 12:12 cw_volcano 阅读(172) 评论(0) 推荐(0)

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

摘要：Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。 1.INSERT INTO SELECT语句语句形式为：Insert into Table2(field1,field2,...) select v 阅读全文

posted @ 2012-08-01 11:36 cw_volcano 阅读(147) 评论(0) 推荐(0)

Error were encountered during the save process. Some database objects were not saved

摘要：Have you ever faced a situation where you wanted to modify your existing table and was bugged with following error popup.Error were encountered during the save process. Some database objects were not saved.This is because by default saving changes to the table that requires table re-creation is disa 阅读全文

posted @ 2012-06-26 17:10 cw_volcano 阅读(2579) 评论(0) 推荐(0)

UPSERT Functionality in SQL Server 2008

摘要：The UPSERT command inserts rows that don’t exist and updates the rows that do exist. The Word UPSERT is a fusion of the words UPDATE and INSERT. UPSERT was officially introduced in the SQL:2003 standard.IF FOUND THEN UPDATEELSE INSERT;In SQL Server 2008, Microsoft introduces the UPSERT functiona... 阅读全文

posted @ 2012-06-14 10:15 cw_volcano 阅读(250) 评论(0) 推荐(0)

SQL Server UPSERT equivalent

摘要：UPSERT functionality refers to either updating table rows based on some search condition, or inserting new rows if the search condition is not satisfied. This is intuitively seen from the word UPSERT - a combination of UPDATE (needed rows are present) or INSERT (needed rows are not present).The most 阅读全文

posted @ 2012-06-14 10:09 cw_volcano 阅读(465) 评论(0) 推荐(0)

从内存中查询表字段定义的长度大小

摘要：SELECT TABLE_NAME AS TableName , COLUMN_NAME As ColumnName, CHARACTER_MAXIMUM_LENGTH As MaxCharacters FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN (SELECT name FROM sys.Tables Where name like '%Translation') AND ... 阅读全文

posted @ 2012-06-02 22:17 cw_volcano 阅读(254) 评论(0) 推荐(0)

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to

摘要：恢复客户发过来的备份数据库后发现其Collation为Latin1_General_CI_AS，但自己的sql2008默认实例的Collation为SQL_Latin1_General_CP1_CI_AS，于是运行程序报错:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.解决方法：创建新的sql server实例，创建过程中选择Server C 阅读全文

posted @ 2012-03-27 14:44 cw_volcano 阅读(1466) 评论(0) 推荐(0)

SQL脚本修改表结构

摘要：SQL脚本修改表结构新建表：create table [表名]([自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,[字段1] nVarChar(50) default '默认值' null ,[字段2] ntext null ,[字段3] datetime,[字段4 阅读全文

posted @ 2012-02-01 17:05 cw_volcano 阅读(566) 评论(0) 推荐(0)

随笔分类 - SQL Server