摘要：--when you don’t know the structure that the store procedure generates:CREATE TABLE ##1(ID1 INT)CREATE TABLE ##2(ID1 INT)INSERT ##1SELECT 1CREATE PROC UP_TESTAS BEGINSELECT * FROM ##1ENDINSERT I...
阅读全文
随笔分类 - T-SQL
摘要：Detect SQL timeout from ASP & Issue RollbackTransI developed an application that worked fine in development and single user testing, but when multiple users starting using the application, I ran i...
阅读全文
摘要：DROP DATABASE DEMO;GOCREATE DATABASE DEMO;GOUSE DEMO;GO--the database master key is always encrypted (using triple_DES) with user-supplier password--stored in the sys.symmetric_keys; and at the same t...
阅读全文
摘要：刚才在QA环境创建SQL replication 的时候报错，大致如下：Error 14274: 无法添加、更新或删除从MSX服务器上发起的作业.导致Replication不能够成功，如按如下角本解决：For example:tsql02\d2shp01SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE GO use msdb;gobe...
阅读全文
摘要：Encrypting Data With the SQL Server Encrypt Function A common question I'm asked by clients is how to encrypt data and store it in SQL Server. One of the major problems I see in the field is when peop...
阅读全文
摘要：-- This script converts string-based IP addresses to their numericequivalents so that it is easy to do arithmatic on them such as find anIP address that is within a range.-- -- NOTE: This function cal...
阅读全文
摘要：-- ----exec Up_ExportTables 'Customers'-- When you want to export a table structure to T-SQL, everyone alwayspoints you to Enterprise Manager, or a script that calls BCP.-- Sometimes, a developer eith...
阅读全文
摘要：CREATE TABLE [dbo].[DateDim] ( [DateKey] [int] IDENTITY (1, 1) NOT NULL , [ActualDate] [datetime] NOT NULL , [Year] [int] NOT NULL , [Quarter] [int] NOT NULL , [Month] [int] NOT NULL , [Week] [int] NO...
阅读全文
摘要：SET NOCOUNT ONGOuse pubs; --EXEC sp_generate_inserts 'David01' ,@from="from David01 where A='a1'"PRINT 'Using Master database'USE masterGOPRINT 'Checking for the existence of this procedure'IF (SELE...
阅读全文
摘要：/* ***** This entire comment portion is not necessarily part of SP, just notes on subject ***** The purpose of this SP is to provide a method in T-SQL to launch another T-SQL thread without havin...
阅读全文
摘要：Don't Use Select * Something you see in a lot of database access code is a select statement that looks something like this: SELECT * FROM TableName WHERE ... While there's technicall...
阅读全文
摘要：前几天和一个朋友聊天的时候他说当存储非常超过8K数据的时候使用Text,ntext OR Image时，就像把这些东西扔进一个黑箱子里面，感觉这个比喻比较形象,当我们把数据存储在这几种数据类型中后，会有非常多的限制。 我们都知道当使用这三个数据类型时，如果我们需要对其进行操作时，就会发现非常麻烦，比如替换其中的某些字符，进行插入，更新时也比较麻烦。时隔5年，让我们来看一下在SQL Serv...
阅读全文
摘要：XML IN 20 MINUTES! http://www.sqlservercentral.com/columnists/lplatt/20010422115709_1.asp This article will quickly dive head first right into XML. I will take an everyday object and attempt to de...
阅读全文
摘要：the following is the SP demo: usingSystem; usingSystem.Data; usingSystem.Data.Sql; usingSystem.Data.SqlServer; usingSystem.Data.SqlTypes; publicpartialclassStoredProcedures { [SqlPr...
阅读全文
摘要：XQuery is a language for XML data.XQuery syntax includes and extends XPaths 2.0 expressions and makes it possible to perform complex queries against on XML data source. An XQuery path is based on the ...
阅读全文
摘要：there are typed XML and Untyped XML that SQL Server 2005 Support. to use Untyped XML,you must have formet XML.and you can implict and emplict varchar to XML. to use typed XML,you must register XML ...
阅读全文
摘要：In SQL Server 2000, you can use a varchar, nvarchar, text, or ntext variable to generate a document handle using the sp_xml_preparedocument stored procedure. In SQL Server 2005, you can also use an x...
阅读全文
摘要：In SQL Server 2000, RAW mode FOR XML queries can return only attribute-centric XML. In SQL Server 2005, you can use the ELEMENTS directive with RAW mode queries to return element-centric XML. The foll...
阅读全文
摘要：Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0 One of the excellent features of the next release of SQL Server, known as SQL Server 2005, is its deep XML integration with the SQL Se...
阅读全文
摘要：Why use Store procedure 第一:提高性能 第二:减少网络流量 第三:减少注入式攻击 3.1易受注入式攻击的代码: 3.2优化过的代码 3.3使用存储过程来减少注入攻击 第一:提高性能 当存储过程被创建,它要经过以下几步,第一步,它所包含的T-SQL语句将被分析和解析,并被存储.当第一次被执行时,它将被调出并被优化.SQLServer会根...
阅读全文
浙公网安备 33010602011771号