Hi ALL, 

I wrote a program on C++ using OLE DB to create 3 tables, fill it with data and then create 2 foreign key constraints. Everything works fine except foreign key creation. The program just hangs on calling Execute() method of ICommandText object. Here is SQL-query I use to create foreign key:

ALTER TABLE [city] ADD CONSTRAINT [city_ibfk_1] FOREIGN KEY ([CountryCode]) REFERENCES [country]([Code])

If I run it from sqlcmd.exe it works. What do you recommend to do to find out what's causing this hang? Thank you in advance!

The likely cause is that there is another session holding locks on either the City or Country tables.  Creating a foreign key constraint requires a schema modification lock on both tables and will be blocked by activity using either table.

Run sp_who2 while the application is hung to identify the blocking session.  That could be the same program the one creating the foreign key if it has multiple connections.

Hi Dan, 

thank you very much for your suggestion. I've tried to use the command sp_who2 and here is what I got:

1> exec sp_who2
2> go
SPID  Status        Login         HostName BlkBy DBName Command      CPUTime DiskIO ProgramName  SPID  REQUESTID
----- ------------- ------------- -------- ----- ------ ------------ ------- ------ ------------ ----- ----------
53    RUNNABLE      DARKSTAR\vka  DARKSTAR   .   world2 SELECT        110     85    S2MAGENT     53    0
54    SUSPENDED     DARKSTAR\vka  DARKSTAR  53   world2 ALTER TABLE   0       0     S2MAGENT     54    0

As I understadn this means that connection id=54 is blocked by connection id=53. Then I tried to find what exactly caused the lock:

1> DBCC INPUTBUFFER(53);
2> go
EventType      Parameters      EventInfo
--------------    --------------      --------------------------
Language Event          0         select * from [dbo].[city]

Does this mean that query "select * from [dbo].[city]" is the reason of lock while creating foreign keys?

yes, spid 53 is blocking but the as soon as spid 53 finishes, spid 54 should be able to execute the alter table.

so, this means that spid 53 is taking a while to process the request -- how long does it take to return the data when you query "select  * from city" 

if this is fast,you should not see much delay else you would

Does this mean that query "select * from [dbo].[city]" is the reason of lock while creating foreign keys?

Yes, apparently the session that executed this select query has not yet consumed all the results of the query (hence the suspended status).  I see the same program name and host is reported for both sessions so it seems the program may be blocking itself.

Thank you for your help! It was the reason indeed. As soon as I kill blocking query foreign keys creation completes. The only thing I have to find out now is why this SELECT-query does not complete for very long time. Table [dbo].[city] is quite small (4079 records). 

 The only thing I have to find out now is why this SELECT-query does not complete for very long time. Table [dbo].[city] is quite small (4079 records). 

My guess is that the client application hasn't read all of the query results for some reason, not that the query is slow on the server side.  SQL Server streams the result set back to the client until all of the results have been consumed.  If the app code is waiting on something or has a bug that prevents all of the results from being read, you will see the SUSPENDED status on the server side while SQL Server waits for the client to keep reading the results.