Thursday, March 29, 2012
Granting Select to an NT Login Group
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names
"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David
|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>
Granting Select to an NT Login Group
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>
Granting Select to an NT Login Group
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> >I have this procdure to grant select permission to a developers group on an
> > SQL Databse. The query executes but never returnes and the permissions
> > are
> > set. Can anyone tell me why the little world keeps spinning and never
> > returns. thanks
> >
> > DECLARE @.cmd as varchar(255)
> >
> > DECLARE table_names CURSOR FOR
> > SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> > DECLARE @.name as varchar(255)
> >
> > OPEN table_names
> > FETCH NEXT FROM table_names INTO @.name
> > WHILE (@.@.FETCH_STATUS <> 1)
> > SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> > --PRINT @.cmd
> > EXEC @.cmd
> > FETCH NEXT FROM table_names INTO @.name
> > DEALLOCATE table_names
> > CLOSE table_names
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>sql
Tuesday, March 27, 2012
Grant using Query
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
Thanks in advance,
Steven
Hi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
Grant using Query
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>sql
Grant using Query
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
grant total for hide duplicate...
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
in report, i use hide duplicate and displayed like:
ItemNo Area Barcode Total
1 J 12345 15
2222
total: 30
the problem is the total...what i wanted is 15 and no 30.i use
"=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
what should i do to get the firgue what i wanted.....
i really appreciate your helps...thank a lot
--
thanksWhat you should do is to have 15 on both and the total is 30. instead if you
show just 15 and total 30, it is a kind of misleading...
so you can hide textbox wise all the values except total.
Amarnath
"kevintts" wrote:
> for my query in dataset after join few tables, i got following data:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> in report, i use hide duplicate and displayed like:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> total: 30
> the problem is the total...what i wanted is 15 and no 30.i use
> "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> what should i do to get the firgue what i wanted.....
> i really appreciate your helps...thank a lot
> --
> thanks|||thanks Amarnath, but what i want is hide or minus the total and the total
become 15.
let say:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
2 K 54321 20
i got:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 50
what i wanted:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 35
The value for '2222' still in the group. any thing wrong with the
exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
thanks a lot...
"Amarnath" wrote:
> What you should do is to have 15 on both and the total is 30. instead if you
> show just 15 and total 30, it is a kind of misleading...
> so you can hide textbox wise all the values except total.
> Amarnath
>
> "kevintts" wrote:
> > for my query in dataset after join few tables, i got following data:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 1 J 2222 15
> >
> > in report, i use hide duplicate and displayed like:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 2222
> > total: 30
> >
> > the problem is the total...what i wanted is 15 and no 30.i use
> > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> >
> > what should i do to get the firgue what i wanted.....
> > i really appreciate your helps...thank a lot
> >
> > --
> > thanks|||Expression is ok but since the text box is hidden, but the value exists so it
sums up.
If possible can you get this through query. e.g the values not present
should be null. in your case '2222' should have Total as Null. so when you
sum it sums up exactly.
Amarnath
"RE: grant total for hide duplicate..." wrote:
> thanks Amarnath, but what i want is hide or minus the total and the total
> become 15.
> let say:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> 2 K 54321 20
> i got:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 50
> what i wanted:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 35
> The value for '2222' still in the group. any thing wrong with the
> exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
> thanks a lot...
> "Amarnath" wrote:
> > What you should do is to have 15 on both and the total is 30. instead if you
> > show just 15 and total 30, it is a kind of misleading...
> >
> > so you can hide textbox wise all the values except total.
> >
> > Amarnath
> >
> >
> > "kevintts" wrote:
> >
> > > for my query in dataset after join few tables, i got following data:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 1 J 2222 15
> > >
> > > in report, i use hide duplicate and displayed like:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 2222
> > > total: 30
> > >
> > > the problem is the total...what i wanted is 15 and no 30.i use
> > > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> > >
> > > what should i do to get the firgue what i wanted.....
> > > i really appreciate your helps...thank a lot
> > >
> > > --
> > > thanks
Grant Showplan to <user>
I was trying to review some query statistics and received the following message:
SHOWPLAN permission denied in database Test
I gave the user permission by the following command:
Grant showplan to user.
I am curious as to how much perfomance does this effect? Is there an alternative?
regards
The SHOWPLAN permission only governs who can run the various SET SHOWPLAN statements. It doesn't impact performance of the server per se. And with some of the SHOWPLAN statement in effect, the statement(s) is not executed and goes through compilation phase only. There is no other alternative though other than granting the required permission to the user.|||Does it have anything to do with "Display estimated execution plan" or "Include actual execution plan" in query analyzer?
Thanks
|||Yes. Those features run one of the SET SHOWPLAN command underneath the covers. See the BOL link below for more details on the permissions:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c18657e-2992-4ee7-ab07-7af3141ec658.htm
The online version of BOL links is:
http://msdn2.microsoft.com/en-us/ms189602(SQL.90).aspx
|||
Thanks for your prompt reply.
Just one last question.
If i have a query that takes about 60 minutes to run and i have the "Show actual estimation plan" selected...that would not effect the server perrformance or clog it up as compare to if i just run the query?
Thanks
|||The actual estimation plan uses "SET STATISTICS XML ON" in SQL Server 2005 and "SET STATISTICS PROFILE ON" in older versions of SQL Server. It will not affect the query performance drastically. You may want to just use SET STATISTICS TIME ON first to see the compilation and execution times. That will give an idea as to where the chunk of query execution time is being spent. You can then use the actual plan output to look for missing indexes, scans etc.Grant select permission on all Tables to a DB role
I got Win2k Advanced Server with SQL 2K running.
I am trying to figure out one single query will give a permission to all
tables under a database. A database has 20 - 30 tables with several custom
roles and it is pain to run a query that give a permission on one table at a
time.
Grant select on my_table to my_role
Grant update on my_table1 to my_role1
Can any one turn a light for me on this'
Thanks in advance.
SangHunThere might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Why can't you just add the role to db_datareader and db_datawriter?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
There might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:
> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||Thanks Addm,
I will try that out on testing env.
SangHun
"Adam Machanic" wrote:
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@.microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:
> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
at[vbcol=seagreen]
> a
>
>
Wednesday, March 21, 2012
Grabbing the Top Nth Records based on another table per group
Hi All,
I am trying to perform a SQl query that I have never attempted before.
I have a table with records such that would look like this
GroupField1 GroupField2 Field1 Amount
AAA BBB Field1Data 23.00
AAA BBB Feld2Data 24.00
AAA BBB Field3Data 8.00
BBB CCC Field1Data 25.00
BBB CCC Field2 Data 23.00
DDD BBH Field1Data 232.00
I have a second table that is like
GroupField1 GroupField2 NumberOfRows
AAA BBB 2
BBB CCC 1
DDD BBH 1
What I am trying to do is create a query that gets data from table 1 based on how many rows table 2 says to get.
I think the query would be something like
Select Top(select number of rows from table 2 where Table1.GroupField1 = Table2.GroupField1 and Table1.GroupField2 = Table2.GroupField2) from Table1
Anybody oput there have any expertise in this area?
Thank you,
Lynn
Wrong forum. Try posting on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
grabbing a value from listbox to query database
hello forum,
I need to grab astring value from alist box in from a web form,
and pass it to a sql select command statement where that value is equal to
all values in a database table(sql 2000).
example
zip code list box
33154
33254
84578
85475
35454
selected value is 85475
I am putting that value in a string like this:
dim string_zip as string
string_zip = zip_ListBox.text
Question, how do i pass that value to sql stament, i am using this but does not work.
SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip =string_zip", SqlConnection1)
You should use this
SqlCommand1 =New SqlCommand("SELECT zip FROM table WHERE zip ='" &string_zip &"'", SqlConnection1)
Regards
|||Actually, you should use this:
SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zip
Using the string concatenation method is a good way to get yourself hacked.
|||
Motley wrote:
Actually, you should use this:
SqlCommand1=new sqlcommand("SELECT zip FROM table WHERE zip=@.zip",SqlConnection1)
SqlCommand1.parameters.add(new sqlparameter("@.zip",sqldbtype.varchar))
SqlCommand1.parameters("@.zip").value=string_zipUsing the string concatenation method is a good way to get yourself hacked.
Yes, this is preferred over my solution becuase it is more secure.
Thanks Motley
|||Thanks for you help, it works, but I encounter another problem.
Problem:
multiple selection from list box is allowed, I am getting a string value from all selected choices like this:
Dim listofstringsAsString
Dim itemAs ListItem
ForEach itemIn listbox.Items
If item.SelectedThen
listofstrings = listofstrings & item.Text & ","
EndIf
Next
so i havelistofstrings = (selectedvalue1,selectedvalue2,selectedvalue3,......)
I need to select all values from a table in database where any of those values corresponds.
NOTE: Values in database can also be in the format of (value1,value2,value3,.....) or just a single (value1,)
|||Search for messages on the UDF named "Split" one was posted recently.
SELECT *
FROM table
WHERE field IN (SELECT * FROM Split(@.listofstrings))
OR
SELECT *
FROM table
JOIN Split(@.listofstrings) s ON (table.field=s.id)
Monday, March 19, 2012
Goofy problem
When I use the "between" statement
in the query analyzer in MRS, it will not poplulate the data fields.
when I remove the between statement, it runs fine. The parameters in
the
between statement are correct, and I tried different paramenters such
as
a date between 2002 and 2007, which I know is in the db same problem.
I have service packed SQL and VizStudio no help.
Thanks,
>Scott
here's a snippette:
No results using this.
Where Year >= 2006
And Month BETWEEN @.BEGINDATE AND @.ENDDATE
Group by etc......you need to convert your begin and enddate parameters to Months for this to
work, you can use MONTH(@.BeginDate) to do this.
"scott" <dunnscott@.gmail.com> wrote in message
news:1185297699.337926.89340@.57g2000hsv.googlegroups.com...
> Hi All,
> When I use the "between" statement
> in the query analyzer in MRS, it will not poplulate the data fields.
> when I remove the between statement, it runs fine. The parameters in
> the
> between statement are correct, and I tried different paramenters such
> as
> a date between 2002 and 2007, which I know is in the db same problem.
> I have service packed SQL and VizStudio no help.
> Thanks,
>>Scott
> here's a snippette:
> No results using this.
> Where Year >= 2006
> And Month BETWEEN @.BEGINDATE AND @.ENDDATE
> Group by etc......
>
Monday, March 12, 2012
Good SQL Query
The column is clustered indexed.
SELECT * FROM MYTABLE WHERE LEFT(ACCOUNT,3)='123'
execution plan shows CLUSTERED INDEX SCAN.
SELECT * FROM MYTABLE WHERE ACCOUNT LIKE '123%'
execution plan shows CLUSTERED INDEX SEEK.
How, why. Why doesn't the optimizer works good for the first query?
Howdy!Can you refraze the question. Can it be built with the query anylzer?|||LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.|||LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.well, that's pretty stupid of it, eh :)
oh, i don't mean in the general sense, i am forever telling people not to do stuff like
... where year(transdate) = year(getdate())
i mean specifically in the case of the LEFT function|||Can you refraze the question. Can it be built with the query anylzer?
create table mytable (account varchar(10))
go
create clustered index myindex on mytable(account)
go
declare @.v int
set @.v=1
while @.v<9000
begin
insert mytable select 'abcdefgh'
set @.v=@.v+1
end
-- table scan
select * from mytable where left(account,3)='abc'
-- index seek
select * from mytable where account like 'abc%'
drop table mytable|||is there an index on that column at all ?|||It's likely that the scan occurs because left has to retrieve the whole thing while the like only retrieves the first three characters. The clustered index is going to organize that column by varchar physically on the disk, so the like statement will retrieve two indexes and everything in between, versus the left which will have to check every record "in between".
Does that make sense ?
Cheers,
-Kilka|||It's NonSargable
http://www.sql-server-performance.com/sql_server_performance_audit8.asp
Good Query Writing......
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hilton
depends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:
> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>
|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
|||Didn't I answer that?
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas
"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
Good Query Writing......
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hiltondepends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:
> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas
"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
Good query goes bad
We have a 3rd party app that uses "sp_executesql" to run a stored procedure.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or two
given the same parameters. It seems to be doing a ton of I/O's when it's slow
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictable
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- Martin
We've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James
Good query goes bad
We have a 3rd party app that uses "sp_executesql" to run a stored procedure
.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or tw
o
given the same parameters. It seems to be doing a ton of I/O's when it's slo
w
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictabl
e
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- MartinWe've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James
Good query goes bad
We have a 3rd party app that uses "sp_executesql" to run a stored procedure.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or two
given the same parameters. It seems to be doing a ton of I/O's when it's slow
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictable
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- MartinWe've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James
Good Query for Paging?
I am wondering what is the suggested query to use when I want to page through a bunch of data. I figure this is a pretty common situation so there should be some good ways to do this.
Basically I have a fixed page size and everytime a new one is requested, I just want to get those records. So if my page size is 10, for the first one I want to get records 1-10 of an ordered set, for page 2 I want to get records 11-20, etc.
The problem is I can only see "TOP" as an option, but that gives me the *first* n records. What if I want a set of records in the middle of the set?
I am also thinking some kind of subquery with an identity column, and then selecting based on the identity column may do the trick.
I would appreciate any any good solutions for this!
Thanks.select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc
For a variable page do it in dynamic sql.
or temp table solution - could use table variable)
create table #a (id int identity(1,1), ...)
set rowcount = @.maxrecs
insert #a select ...
set rowcount 0
select top 10 * from #a order by id desc
drop table #a
Wednesday, March 7, 2012
going over 2 selects in 1 query
i have a code L
;conPubs =New SqlConnection(conStr) conPubs.Open()'**>> building select box for ??? ?????? cmdSelect =New SqlCommand("Select * From course_type;SELECT UploadTrail_id, UploadTrail_select_types_id FROM UploadTrail_select_values WHERE (UploadTrail_id = 54) AND (UploadTrail_select_types_id = 6);", conPubs)Dim ReaderAs SqlDataReader Reader = cmdSelect.ExecuteReader() course_type.DataSource = Reader course_type.DataValueField ="course_id" course_type.DataTextField ="course_name" course_type.DataBind()
if u look u will see 2 select's that are delimited with ; (semicolon)
how can i access the second recordset after i bind the first one on the last line
(i want to get values from the rows in the second "select")
thnaks in advance
peleg
for the second part you use
Reader.NextResult()
then as you would do for the first part.