need help

CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]

INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALL'.

can anyone point out what mistake that i've made here?

The ALL is a parameter that passed to the stored procedure when invoked.

|||

oops, in fact i've left out the CHAR(39) on both side of the parameter...

now the problem is solved.

sql

Need debugging help--stored procedure to move database objects

Hi all--I'm adapting a stored procedure to work on transferring SQL Server 2005 tables and other objects owned under the dbo schema on a database to another non-dbo schema. Given that, I'm trying to use an "if...else" to prevent dbo-owned stored procedures and tables like dtproperties from moving with the rest of the objects to the non-dbo schema. Here's the code:

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

drop procedure [dbo].[chObjOwner]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

CREATE proc chObjOwner( @.usrName varchar(20), @.newUsrName varchar(50))

as

-- @.usrName is the current user

-- @.newUsrName is the new user

set nocount on

declare @.uid int -- UID of the user

declare @.objName varchar(50) -- Object name owned by user

declare @.currObjName varchar(50) -- Checks for existing object owned by new user

declare @.outStr varchar(256) -- SQL command with 'alter schema', 'alter authorization'

set @.uid = user_id(@.usrName)

declare chObjOwnerCur cursor static

for

select name from sys.objects where schema_id = @.uid

open chObjOwnerCur

if @.@.cursor_rows = 0

begin

print 'Error: No objects owned by ' + @.usrName

close chObjOwnerCur

deallocate chObjOwnerCur

return 1

end

fetch next from chObjOwnerCur into @.objName

while @.@.fetch_status = 0

begin

set @.currObjName = @.newUsrName + "." + @.objName

if (object_id(@.currObjName) > 0)

print 'WARNING *** ' + @.currObjName + ' already exists ***'

if @.currObjName in ('dt_addtosourcecontrol','dt_addtosourcecontrol_u','dt_adduserobject','dt_adduserobject_vcs','dt_checkinobject','dt_checkinobject_u','dt_checkoutobject','dt_checkoutobject_u','dt_displayoaerror','dt_displayoaerror_u','dt_droppropertiesbyid','dt_dropuserobjectbyid','dt_generateansiname','dt_getobjwithprop','dt_getobjwithprop_u','dt_getpropertiesbyid','dt_getpropertiesbyid_u','dt_getpropertiesbyid_vcs','dt_getpropertiesbyid_vcs_u','dt_isundersourcecontrol','dt_isundersourcecontrol_u','dt_removefromsourcecontrol','dt_setpropertybyid','dt_setpropertybyid_u','dt_validateloginparams','dt_validateloginparams_u','dt_vcsenabled','dt_verstamp006','dt_verstamp007','dt_whocheckedout','dt_whocheckedout_u','dtproperties')

begin

print 'WARNING *** ' + @.currObjName + ' is a system procedure'

end

else

begin

set @.outStr = "alter authorization on " + @.usrName + "." + @.objName + " to " + @.newUsrName

print @.outStr

set @.outStr = "alter schema " + @.newUsrName + " transfer " + @.usrName + "." + @.objName

print @.outStr

print 'go'

fetch next from chObjOwnerCur into @.objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

GO

Here's the error I get:

Msg 102, Level 15, State 1, Procedure chObjOwner, Line 51

Incorrect syntax near '0'.

I know that the cursor is having a problem closing and deallocating, but am not sure why the error is happening at this point. Any ideas?

Thanks!

One of your 'begin' s has no 'end'.

Need an example of failed CREATE PROC...

This is an example of a working CREATE PROCEDURE.
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>