May 8, 2012 at 12:48 am
Hi,
here i will explain you all a scenario, where i am facing the issue.
1)
I created a schema as follows
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'MyBook')
EXEC sys.sp_executesql N'CREATE SCHEMA [MyBook] AUTHORIZATION [dbo]'
GO
2)
Create procedures, tables with the above schema as follows:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyBook].[tblpdftext]') AND type in (N'U'))
BEGIN
CREATE TABLE [MyBook].[tblpdftext](
[id] [int] IDENTITY(1,1) NOT NULL,
[BookId] [int] NOT NULL,
[PageID] [int] NOT NULL,
[text] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_tblpdftext] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
similarly i created the procedures.
using sa login i tried to execute the procedure which i created with the Schema Name as mentioned above, it is not showing the result, where as if i declare the variables instead of creating a procedure i am able to get the output. please help in this regard
NOTE : earlier for many server and many dbs we have done in a similar fashion but its working. now its not working.
May 8, 2012 at 12:56 am
You only show the create for the schema and a table, nothing for any of the procedures. Can't help you unless you show us everything.
May 8, 2012 at 1:05 am
Sorry for not texting the procedure
procedure is as follows all the tables and sps are in sql2005
CREATE PROCEDURE [MyBook].[uspGetCatelogues]
(
@user-id BIGINT,
@RoleId bigint,
@CodeStatus VARCHAR(6) OUTPUT
)
AS
Begin
SET NOCOUNT ON
BEGIN TRY
SELECT MBk.BookId, MBk.PageID
FROM MyBook.tblpdftext MBk
LEFT OUTER JOIN tblUserBookMapping tub ON tub.BookId = MBk.BookId
WHERE tub.userid = @user-id
END TRY
BEGIN CATCH
SET @CodeStatus = 'E00000'
END CATCH
SET NOCOUNT OFF
End
similar to above if i execute like this after login as sa, it works
Declare @user-id BIGINT,
@RoleId bigint,
@CodeStatus VARCHAR(6)
--select @user-id=1012,@RoleId=2,@CodeStatus='999999'
select @userid=1,@RoleId=2,@CodeStatus='999999'
Begin
SET NOCOUNT ON
BEGIN TRY
SELECT MBk.BookId, MBk.PageID
FROM MyBook.tblpdftext MBk
LEFT OUTER JOIN tblUserBookMapping tub ON tub.BookId = MBk.BookId
WHERE tub.userid = @user-id
END TRY
BEGIN CATCH
SET @CodeStatus = 'E00000'
END CATCH
SET NOCOUNT OFF
End
May 8, 2012 at 1:09 am
How are you executing the stored proceudres and are you getting an error messages when you do?
May 8, 2012 at 1:14 am
I am executing the Procedures as below
exec [MyBook].uspGetCatelogues @userid=1,@RoleId=1,@CodeStatus='999999'
i am not getting any error, the same if i try this way i am getting the output that to in sa login
Declare @user-id BIGINT,
@RoleId bigint,
@CodeStatus VARCHAR(6)
--select @user-id=1012,@RoleId=2,@CodeStatus='999999'
select @userid=1,@RoleId=2,@CodeStatus='999999'
Begin
SET NOCOUNT ON
BEGIN TRY
SELECT MBk.BookId, MBk.PageID
FROM MyBook.tblpdftext MBk
LEFT OUTER JOIN tblUserBookMapping tub ON tub.BookId = MBk.BookId
WHERE tub.userid = @user-id
END TRY
BEGIN CATCH
SET @CodeStatus = 'E00000'
END CATCH
SET NOCOUNT OFF
End
May 8, 2012 at 1:19 am
What schema is this table in? tblUserBookMapping
May 8, 2012 at 1:22 am
tblUserBookMapping is of dbo schema
May 8, 2012 at 1:27 am
Does this table also exist in the MyBook schema?
May 8, 2012 at 1:29 am
Just for S & G's try this:
DROP PROCEDURE [MyBook].[uspGetCatelogues] ;
GO
CREATE PROCEDURE [MyBook].[uspGetCatelogues]
(
@user-id BIGINT,
@RoleId bigint,
@CodeStatus VARCHAR(6) OUTPUT
)
AS
Begin
SET NOCOUNT ON
BEGIN TRY
SELECT
MBk.BookId,
MBk.PageID
FROM
MyBook.tblpdftext MBk
LEFT OUTER JOIN dbo.tblUserBookMapping tub
ON tub.BookId = MBk.BookId
WHERE
tub.userid = @user-id;
END TRY
BEGIN CATCH
SET @CodeStatus = 'E00000'
END CATCH
SET NOCOUNT OFF
End
GO
May 8, 2012 at 1:35 am
I tried, the same way but dint work 🙁
i also tried this way, i removed all the contents of the procedure, i replaced the below 2 statements
Select * from MyBook.tblBooks;
Select * from tblUserBookMapping;
when i tried in this way, i got the result for 1st table but not for the second table, eventhough it has values
May 8, 2012 at 2:13 am
Hey,
thanks a lot, the minute mistake which we had done is, for dbo schema related table we had not added dbo.tblUserBookMapping for the dbo schema related tables.
thanks for making me know in this part.
May 8, 2012 at 5:25 am
You should always specify the schema with the tablename in the from clause. Microsoft recommends this as well.
May 8, 2012 at 5:29 am
Yes, i agree. Normally we do that, but i dint notice that it was not there. Thanks for all your help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply