February 2, 2010 at 10:21 am
My SQL 2000 stored proc was migrated as shown below. All the SQL 2000 tables & procs were built by [dbo] using "sa" login, and all the applications which called procs use "sa" in their connection strings. This proc checks for existence of a table called tCLsearch1. In SSMS it looks like this: [dbo].[tCLsearch1]. If it finds the table, it's supposed to drop it, and build a new one.
When we migrated to SQL '08 we took the opportunity to move away from the sysadmin login ("sa") and set up a new user login "vb6l". Now, when I run this proc, it builds the table as [vb6l].[tCLsearch1]. When I run the proc again, it tells me "there is already a table named tCLsearch1", which indicates my EXIST statement is not working.
Can someone tell me what the correct syntax is now for SQL '08 to check the existence of a table before further processing?
USE [SfiData]
GO
/****** Object: StoredProcedure [dbo].[pGetItemLot] Script Date: 02/02/2010 11:18:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[pGetItemLot] @item char(5),
@lot char(20),
@desc char(50)output
AS
set nocount on
If Exists (SELECT * from sysobjects
WHERE id = object_id('[SfiData].[dbo].[tCLsearch1]') AND
OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE tCLsearch1
declare @ID int,
@season varchar(7),
@CurrentYear int,
@dateused datetime,
@dailylot char(20),
@ingrnum char(5),
@sfilot char (20),
@itemnum char(5),
@EOF INT,
@vennum smallint,
@venname char(30),
@venlot char(20)
Select @EOF = 0
Create Table tCLsearch1
(usedate datetime,
dlotnumber char(20),
itemnumber char(5),
ingrnum char(5),
ingrdesc char(20),
ingrlot char(20),
idesc char(20),
vendornum smallint,
vendorname char(30),
vendorlot char(20),
venrecdate char(6))
Select @CurrentYear = CurrentYear, @season = CurrentSeason
FROM tSystemValues
DECLARE lot_cursor CURSOR FOR
SELECT f.Dateused, f.DailyLotNumber, f.ProductNumber, f.IngredientNumber, f.SFILotNumber, c.Description
From tFinishedProductLotNumbers f, tCurrentItems c
Where f.ProductNumber = @item AND DailyLotNumber = @lot
AND c.ItemNumber = @item and c.CatalogYear = @CurrentYear
IF @item IS NULL
Begin
Set @desc = 'Invalid Product!(pGetItemLot)'
GOTO nomatch
End
Else
Open lot_cursor
FETCH NEXT FROM lot_cursor INTO @dateused, @dailylot, @itemnum, @ingrnum, @sfilot, @desc
SELECT @EOF = @@fetch_status
WHILE (@EOF = 0)
BEGIN
Insert INTO tCLsearch1 (usedate, dlotnumber, itemnumber, ingrnum, ingrlot, idesc)
VALUES (@dateused, @dailylot, @itemnum, @ingrnum, @sfilot, @desc)
FETCH NEXT FROM lot_cursor INTO @dateused, @dailylot, @itemnum, @ingrnum, @sfilot, @desc
SELECT @EOF = @@fetch_status
END
CLOSE lot_cursor
DEALLOCATE lot_cursor
UPDATE tCLsearch1
SET vendornum = v.VendorNumber, vendorlot = v.VendorLot, venrecdate = v.RecvDate
FROM tVendorLotNumbers v
WHERE (v.ProductNumber = ingrnum) AND (v.SFIlot = ingrlot)
UPDATE tCLsearch1
set vendorname = v.Name
FROM tblVendors v
WHERE v.VendorNumber = vendornum
UPDATE tCLsearch1
SET ingrdesc = c.Description
From tCurrentItems c
Where c.ItemNumber = ingrnum AND c.CatalogYear = @CurrentYear
nomatch: [/code]
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
February 2, 2010 at 11:52 am
whouston (2/2/2010)
My SQL 2000 stored proc was migrated as shown below. All the SQL 2000 tables & procs were built by [dbo] using "sa" login, and all the applications which called procs use "sa" in their connection strings. This proc checks for existence of a table called tCLsearch1. In SSMS it looks like this: [dbo].[tCLsearch1]. If it finds the table, it's supposed to drop it, and build a new one.When we migrated to SQL '08 we took the opportunity to move away from the sysadmin login ("sa") and set up a new user login "vb6l". Now, when I run this proc, it builds the table as [vb6l].[tCLsearch1]. When I run the proc again, it tells me "there is already a table named tCLsearch1", which indicates my EXIST statement is not working.
Can someone tell me what the correct syntax is now for SQL '08 to check the existence of a table before further processing?
Create Table tCLsearch1
UPDATE tCLsearch1
UPDATE tCLsearch1
UPDATE tCLsearch1
[/code]
Change those lines to:
Create Table dbo.tCLsearch1
UPDATE dbo.tCLsearch1
UPDATE dbo.tCLsearch1
UPDATE dbo.tCLsearch1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 2, 2010 at 12:02 pm
You could add this to the proc to make sure all the tables are created by the dbo user.
ALTER PROCEDURE [dbo].[pGetItemLot] @item char(5),
@lot char(20),
@desc char(50)output
with execute as 'dbo'
as
...
rest of proc
...
February 5, 2010 at 8:53 pm
1. You could expressly create the table with a schema-qualified name ([dbo].[tablename]).
2. Testing IF OBJECT_ID(N'table') IS NOT NULL is a common test for file existence. To be absolutely certain that the object named is a table, you can add to a test against the sys.objects view or the sys.Tables view. A couple of variations are shown below.
SET NOCOUNT ON;
-- Commonly Seen
IF OBJECT_ID(N'dbo.BrandNewTable') IS NOT NULL DROP TABLE dbo.BrandNewTable
-- Variations that confirm object is a user table
IF OBJECT_ID(N'dbo.BrandNewTable') IS NOT NULL AND
EXISTS (SELECT 1 FROM sys.objects WHERE name = 'BrandNewTable' and TYPE = 'U' )
BEGIN
PRINT 'dbo.BranNewTable Found. Dropping table now'
DROP TABLE dbo.BrandNewTable
END
ELSE
BEGIN
PRINT 'dbo.BrandNewTable not found. Creating table now.'
CREATE TABLE dbo.BrandNewTable (data varchar(max))
END
select * from sys.tables
GO
IF OBJECT_ID(N'dbo.BrandNewTable') IS NOT NULL AND
EXISTS (SELECT 1 FROM sys.objects WHERE name = 'BrandNewTable' and TYPE = 'U' )
BEGIN
PRINT 'dbo.BranNewTable Found. Dropping table now'
DROP TABLE dbo.BrandNewTable
END
ELSE
BEGIN
PRINT 'dbo.BrandNewTable not found. Creating table now.'
CREATE TABLE dbo.BrandNewTable (data varchar(max))
END
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE [object_ID] = OBJECT_ID(N'dbo.BrandNewTable') and TYPE = 'U')
BEGIN
PRINT 'Table Exists.'
END
ELSE
BEGIN
PRINT 'No table Exists.'
END
select * from sys.tables
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 7, 2010 at 4:27 am
Another alternative is to change the DEFAULT_SCHEMA for the new user using ALTER USER - see Books Online (http://msdn.microsoft.com/en-us/library/ms176060.aspx)
Another way to check is:
OBJECT_ID(N'dbo.BrandNewTable', N'U')
The second parameter specifies the type of object to match with. See http://msdn.microsoft.com/en-us/library/ms190328.aspx
I personally think that the second parameter to OBJECT_ID should be compulsory.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 7, 2010 at 4:38 am
never mind. Didn't read the whole story before answering. Sorry.
February 7, 2010 at 9:24 am
You schooled me again, Paul. In all my years I've honestly never looked up OBJECT_ID to see if it even had a second parameter, although it had occurred to me more than once that it was clumsy to have to go look up the type separately. Now, I've gone to BOL and have stored away the useful fact that OBJECT_ID returns NULL when you give it the name of a spatial index. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 7, 2010 at 2:04 pm
Bob,
You're welcome - it's amazing how many people don't know about the second parameter.
I try to include it in all my test rig set-up scripts these days to help people notice.
Handy isn't it? Though, as you rightly point out, it only works for schema-scoped objects.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 7, 2010 at 2:07 pm
It's odd that Microsoft doesn't use that in DROP/CREATE scripts. I guess they rely on the fact that object names have to be unique.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 8, 2010 at 3:07 am
The Dixie Flatline (2/7/2010)
It's odd that Microsoft doesn't use that in DROP/CREATE scripts. I guess they rely on the fact that object names have to be unique.
Microsoft can't write SQL for toffee 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 8, 2010 at 6:15 am
I appreciate all the responses. I used WayneS's suggestion and added 'dbo.' prefix to any mention of a table (or procedure) and that did the trick. I will try MV Jones' suggestion of using 'execute as' at the next opportunity.
I realize now that I was spoiled with SQL 2000 and Enterprise Manager (which seemed much easier to work with BTW).
Thanx for the Help!
Wallace
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply