Thursday, November 1, 2007

How to simulate a deadlock for testing purposes?

How to simulate a deadlock for testing purposes?
In Query Analyzer, run the following statements first:
CREATE TABLE t1 (i int)CREATE TABLE t2 (i int)
INSERT t1 SELECT 1INSERT t2 SELECT 9
Open a new window (say Window1) in Query Analyzer, paste the following SQL statements:
BEGIN TRANUPDATE t1 SET i = 11 WHERE i = 1WAITFOR DELAY '00:00:20'UPDATE t2 SET i = 99 WHERE i = 9COMMIT
Open another window (say Window2) in Query Analyzer and paste the following code:
BEGIN TRANUPDATE t2 SET i = 99 WHERE i = 9WAITFOR DELAY '00:00:20'UPDATE t1 SET i = 11 WHERE i = 1COMMIT
Now run the code from Window1, followed by Window2 simultaneously. Briefly after 20 seconds, one of the windows will experience a dead lock!

Thursday, October 18, 2007

Working with queries across databases

When looking to make a cross database query, there are a few options। The most basic method s the fully qualify your table access with the name of the table। In this case, I could access the Subscriber table from the Orders database as follows:

select * from Subscription.dbo.subscriber


I didn't choose this technique, mainly because of the linked server issue. If I were to move this database to it's own server, which could happen if either of these databases or the load on the server grew, then any process that accessed this table would require recording. Not that I'm against the work, but it's easy to miss a function or stored procedure somehow when moving databases. I much prefer to centralize my code changes as much as it makes sense.
For that reason, I chose another method of accessing the table. I created a view, which I could have called subscriber so the access is transparent. However since I like to be aware of when I'm accessing a view, I named it as follows:create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
from subscription.dbo.subscriber
In this way I can develop all code in the Orders database just as if the subscriber or any other table were local. If I move the subscriber database (more likely) or rename it (unlikely), then I only have to change the code in one place: the view.

Tuesday, September 11, 2007

App_Web_g-_xrs2k, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified

Look out which class/aspx.cs file it is giving error.We just gotta get that code to recompile.We can do that by just adding a space in the file somewhere and saving.
the only bad thing about doing that is by default, asp.net applications will restart after like 20 or so dynamic recompiles like that

Monday, May 28, 2007

The object name ... contains more than the maximum number of prefixes. The maximum is 2

Problem:-
update ServerName.AAD.dbo.t_employeeset ServerName.AAD.dbo.t_employee.work_shift = wa.work_shiftfrom t_employee wawhere ServerName.AAD.dbo.t_employee.id = wa.idand wa.id = '105'
I was getting the following:Server: Msg 117, Level 15, State 2, Line 4The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3.

Solution:-
It's a simple, but not obvious, answer. Put the table you are updating in the FROM clause with an alias and update the alias, as follows:
update laset la.work_shift = wa.work_shiftfrom ServerName.AAD.dbo.t_employee la, t_employee wawhere la.id = wa.idand wa.id = '105'

Sunday, May 27, 2007

Using newsequentialid() in the guid column for sorted values

Okay, so this is a really cool new little feature. A lot of concern with using GUIDs a keys was that they were reasonably random, and they made indexes wildly fragment as they were created. So this little feature allow us to make sure that guids created for a given table are always increasing (so we have less fragmentation, though the indexes can get balanced heavily to one end)

You cannot use them in a query to define them, but you can in a table:

select *from sys.tablesorder by newsequentialid()

Msg 302, Level 16, State 0, Line 1The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.go

You can only use them in a DEFAULT (which you would have known if you hadn't skipped the
create table test( testGuid uniqueidentifier default (newsequentialid()), testId int identity(1,1))goinsert into test default valuesgo 10 --this is a handy little device that was added to 2005 SSMS

Now, we will use the new ranking functions to see that the new GUIDs are in fact ordered:

select testGuid, row_number() over (order by testGuid) as sortByTestGuid, testId, row_number() over (order by testId) as sortByTestIdfrom test

testGuid sortByTestGuid testId sortByTestId------------------------------------ -------------------- ----------- ---------------13C2A79A-B4F5-D911-B7C6-000625ACB4F5 1 1 1030CFDA0-B4F5-D911-B7C6-000625ACB4F5 2 2 2040CFDA0-B4F5-D911-B7C6-000625ACB4F5 3 3 3050CFDA0-B4F5-D911-B7C6-000625ACB4F5 4 4 4060CFDA0-B4F5-D911-B7C6-000625ACB4F5 5 5 5070CFDA0-B4F5-D911-B7C6-000625ACB4F5 6 6 6080CFDA0-B4F5-D911-B7C6-000625ACB4F5 7 7 7090CFDA0-B4F5-D911-B7C6-000625ACB4F5 8 8 80A0CFDA0-B4F5-D911-B7C6-000625ACB4F5 9 9 90B0CFDA0-B4F5-D911-B7C6-000625ACB4F5 10 10 10

Dig those new GUID values. Very interesting. My brain is far too simple to keep up with most of the details of guids, but what really makes me wonder is how they will keep them globally unique AND increasing if they are forcing the domain of guids to always grow. Magic, I will bet, the crafty devils...

One last thing, don't expect the results to look sorted right. Look at the first two rows. 13C2... sorts before 0300... You aren't sorting them by the textual representation, but rather the binary. Run this query and keep inserting data and you will see why:

select testGuid, cast(testGuid as binary(16)), row_number() over (order by testGuid) as sortByTestGuid, row_number() over (order by cast(testGuid as varchar(40))) as sortByTestGuid_Text, testId, row_number() over (order by testId) as sortByTestIdfrom testgo

Friday, May 25, 2007

Sql server reporting services 2005 - Adding a calender control for Datetime parameter

Just declare the parameter as datetime and the calender control is privileged to show up aside the text box. :-)
This was non-exixtent in the earlier version of SSRS.