May 22, 2006 at 3:53 am
I created a query in the analyzer that works ok, but when I transfered it to a stored procedure it says I cant use USE, I needed to query a table that is held on another database and I thought the keyword use was the correct way to change, can anyone help?
May 22, 2006 at 4:38 am
I use the "USE" Clause at the Beginning of a Stored Procedure only to specify which database the stored proc will reside.
Example:
USE ThisDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Test_sp' AND type = 'P')
DROP PROCEDURE Test_sp
GO
If I'm selecting data from a Database other than the one the stored proc resides on then I full qualify the it in the From Clause.
Example: SELECT * FROM database.owner.table/view name
Keith...
May 22, 2006 at 4:42 am
I've tried that Keith, unfortunatly it won't work in the query I've posted below
declare @TmpName as varchar (2000)
DECLARE CUR1 CURSOR FAST_FORWARD FOR
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TI_Work_Test'
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName
print @TmpName
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM CUR1 INTO @TmpName
print @TmpName
END
CLOSE CUR1
DEALLOCATE CUR1
May 22, 2006 at 6:17 am
Mick
What does your query fail on? Surely the following will not produce an error message?
SELECT COLUMN_NAME FROM OtherDB.information_schema.columns
John
May 22, 2006 at 6:26 am
Thanks John, it works
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply