Problem with identity user in a report

Hi,

I use forms authentication with my Report manager. The problem is that I can't display the same information to all the users into the report. For example, in my report, i have one dataset that give me a list of all the store of a compagny. And i have to display only the store onto the user is working. If the user is a boss, he can see the information of all the stores. So i have to know in my report what is the login he uses in the form authetication and i don't know how to do.

Excuse me for my bad english and thank you.

Regards,

Shaouk.

Well Shaouk what i have understand from your point is that for that you must have your data base structure like that you must know which company is the user associated to. Means your company table should have column of user id Or if user can be associated with mulitiple companies then third table with comanyID and UserID should exist SO you may know that the logged in user is dealing with this or those comapnies.

Then after user logged in send userID to the stored procedure from which you are geting the company list so you may filter them on basis of that userId or if using dataset with already populated companies with userId's associated with them then filter that dataset on the basis of that logged in UserID

I Hope that works

Regards

Nabil Khalid

|||

Hello Shaouk,

From within your report, you can make use of User!UserID to determine who is running the report.

Hope this helps.

Jarret

Problem with identity range

Hi,
I have a table that has different owner than DBO. When I include this table
in publication, SQL Server does not allow to enable automatic identity range
for this article.
Any help would be greatly appreciated.
Leila
If changing the owner of the object is an acceptable solutions, refer to
Microsoft Knowledge Base Article - 275312.
"Leila" wrote:

> Hi,
> I have a table that has different owner than DBO. When I include this table
> in publication, SQL Server does not allow to enable automatic identity range
> for this article.
> Any help would be greatly appreciated.
> Leila
>
>
|||Hi,
Unfortunately I must keep the owners intact. Isn't there any solution to
this problem?
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:F88A75E5-7A4B-42AB-8EFA-F7A48E3290D5@.microsoft.com...[vbcol=seagreen]
> If changing the owner of the object is an acceptable solutions, refer to
> Microsoft Knowledge Base Article - 275312.
>
> "Leila" wrote:
table[vbcol=seagreen]
range[vbcol=seagreen]
|||What exactly do you mean by saying that "SQL Server does not allow to enable
automatic identity range" ?
Do you get an error or it is just somehow not possible to select that option
of automatic identity range handling ?
If you are getting an error then I know what you are talking about and I
know what causes those problems - there are some known bugs in merge
replication, which are very significant in case of objects owned not by dbo.
I have developed fixes for some of those bugs and posted them in this
newsgroup (you can look for my posts). If you will not find them, I can
repost them again.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Leila" <leilas@.hotpop.com> wrote in message
news:%234$ay$hpEHA.868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table that has different owner than DBO. When I include this
table
> in publication, SQL Server does not allow to enable automatic identity
range
> for this article.
> Any help would be greatly appreciated.
> Leila
>
sql

Problem with identity range

Hi,
I have a table that has different owner than DBO. When I include this table
in publication, SQL Server does not allow to enable automatic identity range
for this article.
Any help would be greatly appreciated.
Leila
If changing the owner of the object is an acceptable solutions, refer to
Microsoft Knowledge Base Article - 275312.
"Leila" wrote:

> Hi,
> I have a table that has different owner than DBO. When I include this table
> in publication, SQL Server does not allow to enable automatic identity range
> for this article.
> Any help would be greatly appreciated.
> Leila
>
>
|||Hi,
Unfortunately I must keep the owners intact. Isn't there any solution to
this problem?
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:F88A75E5-7A4B-42AB-8EFA-F7A48E3290D5@.microsoft.com...[vbcol=seagreen]
> If changing the owner of the object is an acceptable solutions, refer to
> Microsoft Knowledge Base Article - 275312.
>
> "Leila" wrote:
table[vbcol=seagreen]
range[vbcol=seagreen]

Problem with identity range

Hi,
I have a table that has different owner than DBO. When I include this table
in publication, SQL Server does not allow to enable automatic identity range
for this article.
Any help would be greatly appreciated.
LeilaIf changing the owner of the object is an acceptable solutions, refer to
Microsoft Knowledge Base Article - 275312.
"Leila" wrote:
> Hi,
> I have a table that has different owner than DBO. When I include this table
> in publication, SQL Server does not allow to enable automatic identity range
> for this article.
> Any help would be greatly appreciated.
> Leila
>
>|||Hi,
Unfortunately I must keep the owners intact. Isn't there any solution to
this problem?
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:F88A75E5-7A4B-42AB-8EFA-F7A48E3290D5@.microsoft.com...
> If changing the owner of the object is an acceptable solutions, refer to
> Microsoft Knowledge Base Article - 275312.
>
> "Leila" wrote:
> > Hi,
> > I have a table that has different owner than DBO. When I include this
table
> > in publication, SQL Server does not allow to enable automatic identity
range
> > for this article.
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >

problem with identity column value

Example:
create table test
(
id bigint identity(1000,1) NOT NULL,
x int
)
set IDENTITY_INSERT test on;
insert into test (id,x) values (10,1);
set IDENTITY_INSERT test off;
-- identity value is OK = 1000, but:
set IDENTITY_INSERT test on;
insert into test (id,x) values (2000,1);
set IDENTITY_INSERT test off;
-- identity value isn't OK = 2000, but I need identity value = 1000
-- I know solution, but it is to slow:
DECLARE @.id_seq bigint;
SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
set IDENTITY_INSERT test on;
insert into test (id, x) values (2000, 1);
set IDENTITY_INSERT test off;
DBCC CHECKIDENT ('test', RESEED, @.id_seq);
-- do you know some faster and better solution? Thankssilber wrote:
> Example:
> create table test
> (
> id bigint identity(1000,1) NOT NULL,
> x int
> )
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (10,1);
> set IDENTITY_INSERT test off;
> -- identity value is OK = 1000, but:
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (2000,1);
> set IDENTITY_INSERT test off;
> -- identity value isn't OK = 2000, but I need identity value = 1000
> -- I know solution, but it is to slow:
> DECLARE @.id_seq bigint;
> SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
> set IDENTITY_INSERT test on;
> insert into test (id, x) values (2000, 1);
> set IDENTITY_INSERT test off;
> DBCC CHECKIDENT ('test', RESEED, @.id_seq);
> -- do you know some faster and better solution? Thanks
I don't quite understand the problem. Apparently you want to reset the
current IDENTITY value after inserting some data with IDENTITY_INSERT
on. The only reason I can imagine performance would be in issue here is
if you had to issue DBCC CHECKIDENT on a frequent basis during
user-transactions. But if you had to do that on a frequent basis then
I'd say you would be better off not having the column as an IDENTITY at
all.
Alternatively, you could use only negatives for the IDENTITY_INSERT
values and use positives for the incrementing IDENTITY value. That way
the increment won't be affected.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Problem with IDENTITY column

Hi all

I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:

CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

When I create two new records, the field ID is assigned the next available value.

So far everything works fine.

THE PROBLEM:
If I insert a record with the following SQL statements:

SET IDENTITY_INSERT W_Person ON

INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)

SET IDENTITY_INSERT W_Person OFF

and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.

Please, can anybody help me on this one!

//Peterhi peterand,

the identity-seed is set to 8000 after your insert. so the next value will be 8001...
if you want to re-seed, take a look at bol "DBCC CHECKIDENT"

markus

Problem with Dynamic SQL !

Hi friends,

I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes...

---------------------------------------
CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
EXECUTE( 'SELECT '+ @.nv +' = id from ' + @.seqName )
SET @.nextVal = @.nv
END

---------------------------------------
after i created the table & procedure, i executed the below code:
---------------------------------------
DECLARE @.nextVal1 int
EXECUTE NextVal 'seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
---------------------------------------

but it says

Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
Line 1: Incorrect syntax near '='.
(return status = 0)

Can anyone point out where i went wrong?

JakeDECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||Hi Eniqma,

It didn't solve my prob. same error...
The problem is in the select statement
EXECUTE( 'SELECT '+ @.nv +' = nextVal from ' + @.seqName )
It says Incorrect syntax near '='.

any idea?

Jake|||Oops ... i forgot ... you cannot create a dynamic string inside an execute statement

You will have to do something like

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.query ='SELECT '+ @.nv +' = id from ' + @.seqName
EXECUTE(@.query )
SET @.nextVal = @.nv
END|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= @.@.identity

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= scope_identity()

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1



You should really be using scope_identity()

And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...|||Hi eniqma, when the procedure is executed, it will say

Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
Syntax error converting the varchar value 'SELECT ' to a column of data type int.

as int is concatenated with string.

@.@.IDENTITY approach guides me to the solution. but i didn't use @.@.IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.

so here is the final procedure

---------------------------------------
alter procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
SET @.nextVal = IDENT_CURRENT(@.seqName)
END
---------------------------------------

thanks eniqma & harshal for your time & help.

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake

I think u have already answered the question.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

since the select and insert are not in the scope i think it wont work.|||Hmm ... you are right harshal ... never thought about that

Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...|||i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...

thanks for the clarification, harshal.

Jake|||oh! sorry, i missed that... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
Thanks for your interest.

Jake|||Oh ... so thats what you are trying to do ...

Talking about scope ... its the same in sql as in other languages ...
If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.|||now i understand, enigma... glad to see your reply.

Thanks,
Jake|||Without reading this little lot too deeply, the answer to the original question is:
1: Look at the spaces before and after you + signs '+ @.n +' needs to be:
' + @.n + '
2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.

E.G. 'I am a varchar ' + CAST(@.IntVariable AS Varchar(250)) + ' The rest of the varchar string'

Otherwise you get the converting int to varchar error.

Have fun
Best regards
Steve

P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@.SqlString as Varchar(250)) to check your Sequel statement for errors.