CAL & MSSQL Security Question (by an ignorant)

Hi!

This is a very basic question.. don't make fun of me please!
I'm confused by all this logins, users, roles etc..

What I want to do it is to have an ASP.NET application that uses MSSQL as a
backend.
I'm using SQL Authentication and e.g. Each employee for Comapny A will have
a login e.g. Employee1, Employee2 with separate passwords for each employe
e.g. Pass1, Pass2 etc.. Makes Sense?

As far I understand, logins map to users so is it possible that Employee1
and Employee2 map to the same user called e.g. 'Employee'? Or how does it
work? Any dummy tutorial on this out there you can recommend?

And how do I know how many MSSQL CALs I need?
Two (for each employee login) or only one for the user 'Employee'?

Thanks very much for your efforts!

MartinMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> What I want to do it is to have an ASP.NET application that uses MSSQL
> as a backend. I'm using SQL Authentication and e.g. Each employee for
> Comapny A will have a login e.g. Employee1, Employee2 with separate
> passwords for each employe e.g. Pass1, Pass2 etc.. Makes Sense?

Yes, but it's better to use Windows Authentication. Now, exactly how
you do that over ASP .Net, I don't know. But that means that the web
page would recognize who the user is logged into to Window as, and
then SQL Server would pick up those credentials.

> As far I understand, logins map to users so is it possible that Employee1
> and Employee2 map to the same user called e.g. 'Employee'?

No, all users must map to different logins. (But it can happen that
users map no login at all.)

> And how do I know how many MSSQL CALs I need?
> Two (for each employee login) or only one for the user 'Employee'?

Licensing questions always leaves me in a maze, so i decline to answer
that bit.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> And how do I know how many MSSQL CALs I need?
> Two (for each employee login) or only one for the user 'Employee'?

Licensing is an entirely separate thing from security. CALs are required per
user (an actual person, not a user name) or per physical device. That's my
understanding but for the definitive answer contact Microsoft.

--
David Portas
SQL Server MVP
--

C# MSSQL - number of recordsets

Hi,
I've got a problem which might be a little bit tricky.
I need to find out if selected stored procedure can return more than
one recordset at execution. I know that that might depend on the
parameter values, but it will be perfect if it would be possible just
to count select statements within the procedure code that are actually
return as a recordsets. Following my idea the following procedure
returns 2 recordsets (it isn't i know, but taht will be much easier to
do and that's fine by me so).
Any help will be appreciated.
CREATE PROCEDURE SP_TEST_PROCEDURE
@.PARAM1 INT
AS
BEGIN
IF @.PARAM1 = 1
BEGIN
SELECT * FROM PUBS..SALES
END
IF @.PARAM1 = 2
BEGIN
SELECT * FROM PUBS..JOBS
END
IF @.PARAM1 = 3
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
Yes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegro ups.com...
> Hi,
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
|||Yes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegro ups.com...
> Hi,
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
|||Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:[vbcol=seagreen]
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in messagenews:1164837566.847747.152580@.16g2000cwy.go oglegroups.com...
>
|||Hi there,
Now, obviously I dont know the particulars and there could be a good
reason as to why you need to do this but its seems like quite a messy
solution.
Are you sure there's no other way to do what your attempting to do?
Perhaps you could use C# to build up dynamic SQL and submit it to the
stored procedure. It would be much easier to figure out how many RS
you're getting back if you built the sql in C#. It could be that you
can't actually do it this way though.
If you need a hand submitting dynamic sql to an SPROC then let me know.
My general advice would be to find another way of what you're doing.
Kindest Regards
Simon
|||If you want to know how many it 'can' return, then it seems that is
something you should know when you write the code.
If you want to know how many it 'did' (not counting empty sets) return, you
could easily accumulate an output parameter after checking the @.@.ROWCOUNT.
Otherwise, you request just doesn't make sense.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message
news:1164870186.154846.289960@.l39g2000cwd.googlegr oups.com...
Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:[vbcol=seagreen]
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in
> messagenews:1164837566.847747.152580@.16g2000cwy.go oglegroups.com...
>
|||Unless you can correctly parse T-SQL, you can't just count the number of
SELECT to determine the number of resultsets. Consider the cases with
subqueries and SELECT can be arbitrarily nested within other SELECT's.
Linchi
"sajberek@.gmail.com" wrote:

> Hi,
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>

C# MSSQL - number of recordsets

Hi,
I've got a problem which might be a little bit tricky.
I need to find out if selected stored procedure can return more than
one recordset at execution. I know that that might depend on the
parameter values, but it will be perfect if it would be possible just
to count select statements within the procedure code that are actually
return as a recordsets. Following my idea the following procedure
returns 2 recordsets (it isn't i know, but taht will be much easier to
do and that's fine by me so).
Any help will be appreciated.
CREATE PROCEDURE SP_TEST_PROCEDURE
@.PARAM1 INT
AS
BEGIN
IF @.PARAM1 = 1
BEGIN
SELECT * FROM PUBS..SALES
END
IF @.PARAM1 = 2
BEGIN
SELECT * FROM PUBS..JOBS
END
IF @.PARAM1 = 3
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
ENDThis is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Yes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 =3D 1 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 =3D 2 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 =3D 3 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message =news:1164837566.847747.152580@.16g2000cwy.googlegroups.com...
> Hi,
> > I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> > CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes, a stored procedure can return =multiple resultsets.
For example, using your test =code.
CREATE =PROCEDURE dbo.SP_TEST_PROCEDURE ( @.Param1 INT =)AS BEGIN IF ( @.Param1 =3D 1 ) OR ( =@.Param1 =3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * INTO #TEST_TABLE FROM PUBS.JOBS END END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
-- Arnie Rowland, Ph.D.Westwood Consulting, =Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
=wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegroups.com=...> =Hi,> > I've got a problem which might be a little bit tricky.> =I need to find out if selected stored procedure can return more than> =one recordset at execution. I know that that might depend on the> =parameter values, but it will be perfect if it would be possible just> to =count select statements within the procedure code that are actually> =return as a recordsets. Following my idea the following procedure> returns =2 recordsets (it isn't i know, but taht will be much easier to> do =and that's fine by me so).> Any help will be appreciated.> => CREATE PROCEDURE SP_TEST_PROCEDURE> @.PARAM1 INT> =AS> BEGIN> IF @.PARAM1 =3D 1> BEGIN> SELECT * FROM PUBS..SALES> END> IF @.PARAM1 =3D 2> BEGIN> SELECT =* FROM PUBS..JOBS> END> IF @.PARAM1 =3D 3> BEGIN> SELECT =* INTO #TEST_TABLE FROM PUBS.JOBS> END> END>

---

--=_NextPart_000_0297_01C713F1.B92E3DD0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Yes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 =3D 1 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 =3D 2 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 =3D 3 ) OR ( @.Param1 =3D 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message =news:1164837566.847747.152580@.16g2000cwy.googlegroups.com...
> Hi,
> > I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> > CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 =3D 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 =3D 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 =3D 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>
--=_NextPart_000_0297_01C713F1.B92E3DD0
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes, a stored procedure can return =multiple resultsets.
For example, using your test =code.
CREATE =PROCEDURE dbo.SP_TEST_PROCEDURE ( @.Param1 INT =)AS BEGIN IF ( @.Param1 =3D 1 ) OR ( =@.Param1 =3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..SALES END IF ( @.Param1 =3D 2 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * FROM =PUBS..JOBS END IF ( @.Param1 =3D 3 ) OR ( @.Param1 ==3D 4 ) BEGIN &nbs=p; SELECT * INTO #TEST_TABLE FROM PUBS.JOBS END END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
-- Arnie Rowland, Ph.D.Westwood Consulting, =Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
=wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegroups.com=...> =Hi,> > I've got a problem which might be a little bit tricky.> =I need to find out if selected stored procedure can return more than> =one recordset at execution. I know that that might depend on the> =parameter values, but it will be perfect if it would be possible just> to =count select statements within the procedure code that are actually> =return as a recordsets. Following my idea the following procedure> returns =2 recordsets (it isn't i know, but taht will be much easier to> do =and that's fine by me so).> Any help will be appreciated.> => CREATE PROCEDURE SP_TEST_PROCEDURE> @.PARAM1 INT> =AS> BEGIN> IF @.PARAM1 =3D 1> BEGIN> SELECT * FROM PUBS..SALES> END> IF @.PARAM1 =3D 2> BEGIN> SELECT =* FROM PUBS..JOBS> END> IF @.PARAM1 =3D 3> BEGIN> SELECT =* INTO #TEST_TABLE FROM PUBS.JOBS> END> END>

---

--=_NextPart_000_0297_01C713F1.B92E3DD0--|||Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 =3D 1 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 =3D 2 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 =3D 3 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to t=he top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in messagenews:1164837566.847747.152580@.16g200=0cwy.googlegroups.com...
> > Hi,
> > I've got a problem which might be a little bit tricky.
> > I need to find out if selected stored procedure can return more than
> > one recordset at execution. I know that that might depend on the
> > parameter values, but it will be perfect if it would be possible just
> > to count select statements within the procedure code that are actually
> > return as a recordsets. Following my idea the following procedure
> > returns 2 recordsets (it isn't i know, but taht will be much easier to
> > do and that's fine by me so).
> > Any help will be appreciated.
> > CREATE PROCEDURE SP_TEST_PROCEDURE
> > @.PARAM1 INT
> > AS
> > BEGIN
> > IF @.PARAM1 =3D 1
> > BEGIN
> > SELECT * FROM PUBS..SALES
> > END
> > IF @.PARAM1 =3D 2
> > BEGIN
> > SELECT * FROM PUBS..JOBS
> > END
> > IF @.PARAM1 =3D 3
> > BEGIN
> > SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> > END
> > END- Ukryj cytowany tekst -- Poka=BF cytowany tekst -|||Hi there,
Now, obviously I dont know the particulars and there could be a good
reason as to why you need to do this but its seems like quite a messy
solution.
Are you sure there's no other way to do what your attempting to do?
Perhaps you could use C# to build up dynamic SQL and submit it to the
stored procedure. It would be much easier to figure out how many RS
you're getting back if you built the sql in C#. It could be that you
can't actually do it this way though.
If you need a hand submitting dynamic sql to an SPROC then let me know.
My general advice would be to find another way of what you're doing.
Kindest Regards
Simon|||If you want to know how many it 'can' return, then it seems that is
something you should know when you write the code.
If you want to know how many it 'did' (not counting empty sets) return, you
could easily accumulate an output parameter after checking the @.@.ROWCOUNT.
Otherwise, you request just doesn't make sense.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message
news:1164870186.154846.289960@.l39g2000cwd.googlegroups.com...
Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in
> messagenews:1164837566.847747.152580@.16g2000cwy.googlegroups.com...
> > Hi,
> > I've got a problem which might be a little bit tricky.
> > I need to find out if selected stored procedure can return more than
> > one recordset at execution. I know that that might depend on the
> > parameter values, but it will be perfect if it would be possible just
> > to count select statements within the procedure code that are actually
> > return as a recordsets. Following my idea the following procedure
> > returns 2 recordsets (it isn't i know, but taht will be much easier to
> > do and that's fine by me so).
> > Any help will be appreciated.
> > CREATE PROCEDURE SP_TEST_PROCEDURE
> > @.PARAM1 INT
> > AS
> > BEGIN
> > IF @.PARAM1 = 1
> > BEGIN
> > SELECT * FROM PUBS..SALES
> > END
> > IF @.PARAM1 = 2
> > BEGIN
> > SELECT * FROM PUBS..JOBS
> > END
> > IF @.PARAM1 = 3
> > BEGIN
> > SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> > END
> > END- Ukryj cytowany tekst -- Poka¿ cytowany tekst -

C# MSSQL - number of recordsets

Hi,
I've got a problem which might be a little bit tricky.
I need to find out if selected stored procedure can return more than
one recordset at execution. I know that that might depend on the
parameter values, but it will be perfect if it would be possible just
to count select statements within the procedure code that are actually
return as a recordsets. Following my idea the following procedure
returns 2 recordsets (it isn't i know, but taht will be much easier to
do and that's fine by me so).
Any help will be appreciated.
CREATE PROCEDURE SP_TEST_PROCEDURE
@.PARAM1 INT
AS
BEGIN
IF @.PARAM1 = 1
BEGIN
SELECT * FROM PUBS..SALES
END
IF @.PARAM1 = 2
BEGIN
SELECT * FROM PUBS..JOBS
END
IF @.PARAM1 = 3
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
ENDYes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegroups.
com...
> Hi,
>
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
>
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>|||Yes, a stored procedure can return multiple resultsets.
For example, using your test code.
CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
( @.Param1 INT )
AS
BEGIN
IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..SALES
END
IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * FROM PUBS..JOBS
END
IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
BEGIN
SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
END
END
EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message news:1164837566.847747.152580@.16g2000cwy.googlegroups.
com...
> Hi,
>
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
>
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>|||Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 =3D 1 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 =3D 2 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 =3D 3 ) OR ( @.Param1 =3D 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT =3D 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to t=
he top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in messagenews:1164837566.847747.152580@.16g200=
0cwy.googlegroups.com...[vbcol=seagreen]
>
>|||Hi there,
Now, obviously I dont know the particulars and there could be a good
reason as to why you need to do this but its seems like quite a messy
solution.
Are you sure there's no other way to do what your attempting to do?
Perhaps you could use C# to build up dynamic SQL and submit it to the
stored procedure. It would be much easier to figure out how many RS
you're getting back if you built the sql in C#. It could be that you
can't actually do it this way though.
If you need a hand submitting dynamic sql to an SPROC then let me know.
My general advice would be to find another way of what you're doing.
Kindest Regards
Simon|||If you want to know how many it 'can' return, then it seems that is
something you should know when you write the code.
If you want to know how many it 'did' (not counting empty sets) return, you
could easily accumulate an output parameter after checking the @.@.ROWCOUNT.
Otherwise, you request just doesn't make sense.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sajberek@.gmail.com> wrote in message
news:1164870186.154846.289960@.l39g2000cwd.googlegroups.com...
Yes I know, but how to count how many of them can it return at once?
On 30 Lis, 05:05, "Arnie Rowland" <a...@.1568.com> wrote:[vbcol=seagreen]
> Yes, a stored procedure can return multiple resultsets.
> For example, using your test code.
> CREATE PROCEDURE dbo.SP_TEST_PROCEDURE
> ( @.Param1 INT )
> AS
> BEGIN
> IF ( @.Param1 = 1 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF ( @.Param1 = 2 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF ( @.Param1 = 3 ) OR ( @.Param1 = 4 )
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
> EXECUTE dbo.SP_TEST_PROCEDURE @.Param1 INT = 4
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> <sajbe...@.gmail.com> wrote in
> messagenews:1164837566.847747.152580@.16g2000cwy.googlegroups.com...
>
>|||Unless you can correctly parse T-SQL, you can't just count the number of
SELECT to determine the number of resultsets. Consider the cases with
subqueries and SELECT can be arbitrarily nested within other SELECT's.
Linchi
"sajberek@.gmail.com" wrote:

> Hi,
> I've got a problem which might be a little bit tricky.
> I need to find out if selected stored procedure can return more than
> one recordset at execution. I know that that might depend on the
> parameter values, but it will be perfect if it would be possible just
> to count select statements within the procedure code that are actually
> return as a recordsets. Following my idea the following procedure
> returns 2 recordsets (it isn't i know, but taht will be much easier to
> do and that's fine by me so).
> Any help will be appreciated.
> CREATE PROCEDURE SP_TEST_PROCEDURE
> @.PARAM1 INT
> AS
> BEGIN
> IF @.PARAM1 = 1
> BEGIN
> SELECT * FROM PUBS..SALES
> END
> IF @.PARAM1 = 2
> BEGIN
> SELECT * FROM PUBS..JOBS
> END
> IF @.PARAM1 = 3
> BEGIN
> SELECT * INTO #TEST_TABLE FROM PUBS.JOBS
> END
> END
>

C# code for saving data from excel to mssql database

Hello everyone,

I am trying to find some code or documentation that I can use to create a web page that will save data from an excel file to a mssql databaseAfter you save the Excel file, open it and read records from it using this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

And then use normal ADO.NET procedures to update the SQL Server database.|||Thanks a lot I will take a look at this article now|||Thanks for the tip all worked well on my local machine but when I uploaded it to an external server and tested I keep getting this error:

System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at summitPortal.controls.bulk.insertdata(String sSheetPath) at summitPortal.controls.bulk.ImageButton1_Click(Object sender, ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()

The file is definately not in use because I even restarted the computer and tried again.
I also checked the permissions and they seem to be ok.

Any ideas anyone?|||I'm not so sure about this but did you check yourExcel file is read-only or not? sometimes I get some error when I open read-only files. anyway, I will try what you're doin right now.|||Check out this Microsoft troubleshooting article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306269

C program access to MSSQL database

I need to write a C program on UNIX that will access an MSSQL database (NOT C++, NOT C#, NOT Visual C). Is this possible? (yes/no) Now, if you could oblige me; can you point me in the web direction where i can find out how to do this?Hi there,

Yes this is indeed possible. I have no real acquaintance with this myself, but from things I have read it is indeed possible. If you are using the C programming language, I believe the only option for you is to use ODBC.

You will probably need to download certain components for this to work because, as far as I'm aware, standard UNIX does not include ODBC (been a while since I checked though). Anyway, chances are you will need to download things (like an ODBC driver and an ODBC driver manager).

Here is a couple of tutorials on how to use ODBC from C on UNIX to connect to a database hosted on something like SQL Server. These tutorials also contain links to downloads so that you can get an ODBC driver and ODBC driver manager:

Tutorial Part 1
Tutorial Part 2

In case those aren't enough, throughout my travels I remember seeing other tutorials related to using C and ODBC on UNIX. I don't remember what they are at the moment (too long ago) but they should be available if you search for them (e.g. through Google). Use terms like UNIX, ODBC and SQL Server and you should get some links that point you in the right direction.

Hope that gives you enough help to set you on your way,