Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-29 : 15:49:35
|
Jason writes "Hi,
I'm trying to rerun a query within a loop. The first time through, it never returns from trying to open the recordset and gives this error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e31'
Microsoft][ODBC SQL Server Driver]Timeout expired
This is my setup:
SQL Server version 7.00.623
Windows NT 4.0, service pack 6
I'm guessing the problem is that the query is a view of two other views. I can't figure out how to streamline the SQL so that wouldn't be necessary. Here is some of the code (NOTE: The views are created before I get to the page with the problem):
'This is a view of the foreign keys in the database
CREATE VIEW dbo.User_foreign_keys AS
SELECT CCU.TABLE_NAME, CCU.COLUMN_NAME, RC.CONSTRAINT_NAME, RC.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
WHERE RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
'This is a view of the primary keys in the database
CREATE VIEW dbo.User_primary_keys AS
SELECT CCU.TABLE_NAME, CCU.COLUMN_NAME, RC.CONSTRAINT_NAME, RC.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
WHERE RC.UNIQUE_CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
'This view has the primary and foreign fields and tables
CREATE VIEW dbo.User_related_tables AS
SELECT UFK.TABLE_NAME AS FK_TABLE, UFK.COLUMN_NAME AS FK_COLUMN, UPK.TABLE_NAME AS PK_TABLE, UPK.COLUMN_NAME AS PK_COLUMN
FROM USER_FOREIGN_KEYS AS UFK, USER_PRIMARY_KEYS AS UPK
WHERE UFK.CONSTRAINT_NAME = UPK.CONSTRAINT_NAME
'I run this query to power the loop through each field
'Sorry about the formating
sqlFields = "SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH " & _
"FROM INFORMATION_SCHEMA.COLUMNS " & _
"WHERE TABLE_NAME = 'Albums' AND " & _
"COLUMN_NAME <> 'id' AND " & _
"COLUMN_NAME <> 'sortID';"
rsFields.Open sqlFields, conn
'Now we enter the loop and try to run the inner query
'It never returns from its first attempt
While Not rsFields.EOF
...
sqlRelated = "SELECT PK_TABLE, PK_COLUMN " & _
"FROM User_Related_Tables " & _
"WHERE FK_TABLE = 'Albums' AND " & _
"FK_COLUMN = '" & field_name & "';"
rsRelated.Open sqlRelated, conn
'Never comes back from here
...
rsFields.MoveNext
Wend
If you can help or any of this is unclear, please email, post, etc.
Thanks in advance,
Jason Reynolds" |
|
|
|
|
|
|
|
Efficient Views