[mssql] Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I'm trying to get a match between the sysobjects.name and the objname returned by ::fn_listextendedproperty from the extended properties table (or view or wherever they hide that stuff in SQL 2005):

SELECT o.[id] AS tbl_id, o.[name] AS tbl_nm,
       x.value AS col_desc
  FROM sysobjects o (NOLOCK)
  INNER JOIN ::fn_listextendedproperty(
               N'MS_Description',
               N'user', N'dbo',
               N'table', N'MyTable',
               NULL, default) x
  ON x.objname = o.name
  WHERE o.name = 'MyTable'

The script returns an error message like this:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Solution: Rewrite your join like this

ON o.[name] = e.objname COLLATE Latin1_general_CI_AS

Source: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177141&SiteID=1

[mssql] Use ADO, getrows, or a Stored Procedure?

Introduction

Presenting a large set of data in readable pages is a standard task for Web-application developers. Applications that need to produce search results in a digestible manner need to apply paging algorithms. This article will compare the three ways in which paging can be achieved for an ASP/SQL Server 7 application. The first uses ActiveX Data Object (ADO) paging; the second uses getrows and an array; and the third uses a SQL Server 7 stored procedure. Of those, the third is the most efficient because it returns the minimum set of data from the database server to the Web server.

Solution 1: Paging via ADO

To use ADO's paging system, the cursor location of either the recordset or connection object must be set to adUseClient, or a client-side cursor, before the recordset is filled.

rstProjects.CursorLocation = adUseClient


Next, execute a command or query and obtain a recordset. Set the page size property of the recordset to the number of records that a page should show. And set the absolute page property of the recordset to the current page number. These two properties enable ADO to find and present the subset of the recordset that is relevant for the current page. Note that with these properties set, ADO will still return the entire recordset to the client; only the BOF and EOF markers (ADO properties) will be different.

To maintain state information about the current page, either use a query string or a hidden field. In either case, ensure that if the user chooses to move to the next page, the current page variable increases by one, and if the user chooses to move to the previous page, the current page variable decreases by one.

If (Len(Request.QueryString("page")) = 0) Or (IsNumeric(Request.QueryString("page")) = False) Then
  intCurrentPage = 1
Else
  intCurrentPage = CInt(Request.QueryString("page"))
End If
rstProjects.PageSize = 25
rstProjects.AbsolutePage = intCurrentPage

The next step is to construct the loop that will display the records for the current page. As per sound ADO practice, when looping through a recordset to display data, set up local variables that reference the various fields and then access those local variables.

If rstProjects.RecordCount > 0 Then

  Dim fldProject, fldBuyer, fldBidder, fldAverageBid
  Set fldProject = rstProjects.Fields("Project")
  Set fldBuyer = rstProjects.Fields("Buyer")
  Set fldBidder = rstProjects.Fields("Bidder")
  Set fldAverageBid = rstProjects.Fields("AverageBid")

  Dim intCurrentRecord
  intCurrentRecord = 1

  Do
    Response.Write("<p>" & fldProject.Value & "</p>" & vbCrLf)
    Response.Write("<p>" & fldBuyer.Value & "</p>" & vbCrLf)
    Response.Write("<p>" & fldBidder.Value & "</p>" & vbCrLf)
    Response.Write("<p>" & fldAverageBid.Value & "</p>" & vbCrLf)

    rstProjects.MoveNext
    intCurrentRecord = intCurrentRecord + 1
    If rstProjects.EOF Then Exit Do
  Loop While intCurrentRecord <> rstProjects.PageSize

Any kind of loop could achieve the same effect, but I've presented a fairly verbose Do-Loop-While loop for heuristic purposes.

Solution 2: Paging via getrows and an Array

The getrows approach will mimic the same algorithm as ADO paging but will use an array instead of an ADO recordset. By putting all the data into an array shortly after the recordset is filled, the recordset object can be closed and set to nothing, thereby freeing up system resources. As a result, this approach should be more efficient that the ADO approach.

If rstProjects.RecordCount > 0 Then

  Dim arrProjects
  arrProjects = rstProjects.GetRows

  rstProjects.Close
  Set rstProjects = Nothing

  Dim intTotalRecords
  intTotalRecords = UBound(arrProjects,2)

  Dim intPageSize
  intPageSize = 25

  Dim intStart
  intStart = ((intCurrentPage - 1) * intPageSize)

  Dim intEnd
  intEnd = intStart + intPageSize - 1

  Dim fldProject, fldBuyer, fldBidder, fldAverageBid

  Dim intCurrentRecord
  intCurrentRecord = intStart

  Do
    fldProject = arrProjects(0, intCurrentRecord)
    fldBuyer = arrProjects(1, intCurrentRecord)
    fldBidder = arrProjects(2, intCurrentRecord)
    fldAverageBid = arrProjects(3, intCurrentRecord)

    Response.Write("<p>" & fldProject & "</p>" & vbCrLf)
    Response.Write("<p>" & fldBuyer & "</p>" & vbCrLf)
    Response.Write("<p>" & fldBidder & "</p>" & vbCrLf)
    Response.Write("<p>" & fldAverageBid & "</p>" & vbCrLf)

    intCurrentRecord = intCurrentRecord + 1
    If intCurrentRecord >= intTotalRecords Then Exit Do
  Loop While intCurrentRecord < intEnd

When using getrows, avoid misusing the two-dimensional array built by getrows. In particular, it is not uncommon to mistake a column when setting up the fields to display.

Solution 3: Paging via a SQL Server 7 Stored Procedure

The third and final approach involves a stored procedure. This is the most efficient approach because, unlike ADO and getrows which both return the entire set of records to the Web server, the stored procedure returns only the records that are needed for the current page.

Once again, the paging algorithm is roughly the same as ADO, but here the current page and the page size are passed into the stored procedure as input parameters. The stored procedure then selects the set of records needed for the current page by setting up a temporary table with an identity field and using the identity field to determine which records should be returned, given the current page and page size.

CREATE PROCEDURE "sprocInformationTechnologyProjects"
  @Page int,
  @Size int
AS

  DECLARE @Start int, @End int

  BEGIN TRANSACTION GetDataSet

  SET @Start = (((@Page - 1) * @Size) + 1)
  IF @@ERROR <> 0
    GOTO ErrorHandler

  SET @End = (@Start + @Size - 1)
  IF @@ERROR <> 0
    GOTO ErrorHandler

  CREATE TABLE #TemporaryTable
  (
    Row int IDENTITY(1,1) PRIMARY KEY,
    Project varchar(100),
    Buyer int,
    Bidder int,
    AverageBid money
  )
  IF @@ERROR <> 0
    GOTO ErrorHandler

  INSERT INTO #TemporaryTable
  SELECT ...
  // Any kind of select statement is possible with however many joins
  //  as long as the data selected can fit into the temporary table.
  IF @@ERROR <> 0
    GOTO ErrorHandler

  SELECT Project, Buyer, Bidder, AverageBid
  FROM #TemporaryTable
    WHERE (Row >= @Start) AND (Row <= @End)   IF @@ERROR <> 0
    GOTO ErrorHandler

  DROP TABLE #TemporaryTable

  COMMIT TRANSACTION GetDataSet
  RETURN 0

ErrorHandler:
  ROLLBACK TRANSACTION GetDataSet
  RETURN @@ERROR

With SQL Server 2000, the stored procedure for paging described above can be enhanced by using a local table and a "user-defined function."

Conclusion

In summary, programming within a stored procedure is always superior because network traffic is kept to a minimum. Even with ASP.NET's DataGrid with in-built paging around the corner, the stored procedure approach to paging is the best when dealing with large sets of data.

About the Author

Stephen Lian is the principal of Active Data Online Pty Ltd, http://www.activedataonline.com.au.

Source: http://www.15seconds.com/Issue/010308.htm

[mssql] Count Number of Certain Character in A String

Some times we need to know number of certain character in a field, for example to know:

• How deep the directory path by calculating the number of backslash in a path.
• How many item in a list of string by calculating number of comma in the string.

Following the statements for that purpose:

DECLARE @cPath varchar(100)

SET @cPath = 'D:\TEMP\test.txt'
SELECT len(@cPath) - len(replace(@cPath, '\', ''))

[mssql] Uniqueidentifier vs. IDENTITY

Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? thanks!"

Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.

First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:

• Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
  
• That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.
  
• To get a GUID in SQL Server (7.0+), you call the NEWID() function.
  
• The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
  
• This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.

Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:

DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')

So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. You can get it before the insert, however, by using the above code.

The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. GUIDs are also used heavily by SQL Server replication to keep track of rows when they're spread out among multiple SQL Servers.

The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes).

Not only that, but they're just plain hard to read. Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.

Source: http://www.sqlteam.com/article/uniqueidentifier-vs-identity

[mssql] Get Login Name

DECLARE @cLogin varchar(25)     --> declare variable
SELECT @cLogin = suser_sname()  --> store to variable
SELECT @cLogin AS login_name    --> show result

[mssql] Get Client IP Address

DECLARE @cHost varchar(25)     --> Declare variable
SELECT @cHost = host_name()    --> store to variable
SELECT @cHost AS host_name     --> show result

Source:
http://www.dbforums.com/archive/index.php/t-1604725.html
http://msdn2.microsoft.com/en-us/library/ms174427.aspx

[mssql] Bit Manipulations

Introduction

When you use a value in your database or application, the value must be stored somewhere in the computer memory using a certain amount of space. A value occupies space that resembles a group of small boxes. In our human understanding, it is not always easy to figure out how a letter such as as B is stored in 7 seven small boxes when we know that B is only one letter.

Bit manipulation or a bit related operation allows you to control how values are stored in bits. This is not an operation you will need to perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators) are present in all or most programming environments, so much that you should be aware of what they do or what they offer.

Bits Operators: The Bitwise NOT Operator ~

One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~

The bitwise NOT is a unary operator that must be placed on the left side of its operand as in

~Value

Here is an example:

PRINT ~158

To perform this operation, the Transact-SQL interpreter considers each bit that is part of the operand and inverts the value of each bit from 1 to 0 or from 0 to 1 depending on the value the bit is holding. This operation can be resumed in the following table:

Bit	~Bit
1	0
0	1

Consider a number with a byte value such as 248. In our study of numeric systems, we define how to convert numbers from one system to another. Based on this, the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is 0xF8). If you apply the bitwise NOT operator on it to reverse the values of its bits, you would get the following result:

Value	1	1	1	1	1	0	0	0
~Value	0	0	0	0	0	1	1	1

Bits Comparison: The Bitwise AND Operator &

The bitwise & is a binary operator that uses the following syntax

Operand1 & Operand2

This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0. This comparison is resumed as follows:

Bit1	Bit2	Bit1 & Bit2
0	0	0
1	0	0
0	1	0
1	1	1

Imagine you have two byte values represented as 187 and 242. Based on our study of numeric systems, the binary value of decimal 187 is 1011 1011 (and its hexadecimal value is 0xBB). The binary value of decimal 242 is 1111 0010 (and its hexadecimal value is 0xF2). Let’s compare these two values bit by bit, using the bitwise AND operator:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 & N2	1	0	1	1	0	0	1	0	178

Most of the times, you will want the interpreter to perform this operation and use the result in your program. This means that you can get the result of this operation and possibly display it to the user. The above operation can be performed by the following program:

PRINT 187 & 242

This would produce 178

Bits Comparison: The Bitwise OR Operator |

You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:

Value1 | Value2

Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0. This operation is resumed as follows:

Bit1	Bit2	Bit1 | Bit2
0	0	0
1	0	1
0	1	1
1	1	1

Once again, let’s consider decimals 187 and 242. Their bitwise OR comparison would render the following result:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 | N2	1	1	1	1	1	0	1	1	251

You can also let the compiler perform the operation and produce a result. Here is an example:

PRINT 187 | 242

This would produce 251

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:

Value1 ^ Value2

The compiler compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. This operation is resumed as follows:

Bit1	Bit2	Bit1 ^ Bit2
0	0	0
1	0	1
0	1	1
1	1	0

We will again consider decimals 187 and 242. Their bitwise-exclusive XOR comparison would render the following result:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 ^ N2	0	1	0	0	1	0	0	1	73

If the interpreter performs this operation, it can produce a result as in the following example:

PRINT 187 ^ 242;

This would produce 73.