Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
Thanks
D Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>
|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.
> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.
> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.
> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
ThanksD Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting chang
e
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> > I'm thinking of using transactions but there's something I don't know.
> > Consider that kind of code:
> >
> > BEGIN TRANS
> >
> > INSERT ...
> > INSERT ...
> > UPDATE ...
> > DELETE ...
> > INSERT...
> >
> > COMMIT
> >
> > My question is:
> > do I have to write after *each* insert, update or delete
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > RETURN
> > END
> >
> > for my procedure to work well?
> >
> > It's not a big deal if there are only 2 or 3 operations, but if there
are
> > lots of them...
> > Can you tell me what the minimum code is for a procedure using
transaction
> > to be valid?
> > Thanks
> >
> > Henri
> >
> >
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a =E9crit dans le =message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline =conditions. The following is a general layout that I use, but the one =you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
** */
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode =3D @.@.ERROR
IF @.intErrorCode =3D 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry =3D @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode =3D @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
UPDATE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
DELETE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode =3D 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the =status of @.@.ERROR after each DML statement if you wish to properly trap =errors. Also, it is VERY important that you initialize the appropriate =environmental parameters on code launch since transactions are highly =sensitive to these settings. Being explicit will help you in any =debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Henri" <hmfireball@.hotmail.com> wrote in message =news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't =know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if =there are
lots of them...
Can you tell me what the minimum code is for a procedure using =transaction
to be valid?
Thanks
Henri
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for your help Anthony =:-)
"AnthonyThomas" a =E9crit dans le message de news:O7n%23T9K1EHA.=1076@.TK2MSFTNGP09.phx.gbl...
I like =to defer my exception handling and sometimes have inline conditions. The =following is a general layout that I use, but the one you describe is typical as = well.

CREATE =PROCEDURE DataModificationTransaction1 @.Param1 AS =DataType1 ,@.Param2 AS DataType2 ... ,@.ParamN AS DataTypeN AS

/***** =Procedure Information Comment Block** */

DECLARE @.intTranCountOnEntry AS INT ,@.intErrorCode AS =INT -- Environment Configuration.SET XACT_ABORT OFFSET =IMPLICIT_TRANSACTIONS OFFSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Variable Initialization.SET @.intErrorCode =3D @.@.ERROR

IF =@.intErrorCode =3D 0 BEGIN -- Capture transaction state before =beginning. SET @.intTranCountOnEntry =3D @.@.TRANCOUNT BEGIN =TRANSACTION SET @.intErrorCode =3D @.@.ERROR END -- Only continue =if error free.IF @.intErrorCode =3D 0 BEGIN INSERT ... SET = @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode ==3D 0 BEGIN INSERT ... SET @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN UPDATE ... SET @.intErrorCode =3D =@.@.ERROR END IF @.intErrorCode =3D 0 BEGIN DELETE ... SET =@.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN INSERT ... SET @.intErrorCode =3D =@.@.ERROR END -- Only commit if transaction initiated-- and error free.IF =@.@.TRANCOUNT > @.intTranCountOnEntry BEGIN IF @.intErrorCode =3D 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK =TRANSACTION END END =RETURN @.intErrorCode
You can =also nest the conditional statements but you MUST check the status of @.@.ERROR after =each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters =on code launch since transactions are highly sensitive to these =settings. Being explicit will help you in any debugging situations.

Hope =this helps.

Sincerely,


Anthony = Thomas



--
"Henri" =wrote in message news:eC9loV10EHA.3484=@.TK2MSFTNGP09.phx.gbl...I'm thinking of using transactions but there's something I don't know.Consider that kind of code:BEGIN =TRANSINSERT ...INSERT ...UPDATE ...DELETE ...INSERT...COMMITMy question is:do I have =to write after *each* insert, update or deleteIF @.@.ERROR 0 = BEGIN ROLLBACK RETURNENDfor my =procedure to work well?It's not a big deal if there are only 2 or 3 =operations, but if there arelots of them...Can you tell me what the =minimum code is for a procedure using transactionto be =valid?ThanksHenri

---

--=_NextPart_000_0015_01C4D4EB.600E75E0--

Newbie question

I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?
I don't really use Visual Studio that much. Use Query Analyzer (SQL 2000)
or SQL Server Management Studio (SQL 2005).
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Joe" <,> wrote in message
news:4401a01f$0$6979$ed2619ec@.ptn-nntp-reader02.plus.net...
I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?

Newbie question

I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?I don't really use Visual Studio that much. Use Query Analyzer (SQL 2000)
or SQL Server Management Studio (SQL 2005).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe" <,> wrote in message
news:4401a01f$0$6979$ed2619ec@.ptn-nntp-reader02.plus.net...
I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?sql

Newbie question

I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?I don't really use Visual Studio that much. Use Query Analyzer (SQL 2000)
or SQL Server Management Studio (SQL 2005).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe" <,> wrote in message
news:4401a01f$0$6979$ed2619ec@.ptn-nntp-reader02.plus.net...
I noticed your reply to Alison - having the same problem. How would I enter
the code you gave
select
table_schema
from
information_schema.tables
where
table_name = 'ascis_acadyear_setup'
into visual studio?

newbie needs simple help

my database is on the attachment. Im decent with vb programming but suck with databases! Basically Ive to accomplish the following. I created code for a calendar that allow a user to enter a value for any date in the calendar. I need to take the values they entered into my vb calendar and save the dates with a value to my database. If I were working with one table it wouldnt be a problem. Unfortunately Im using ADO which only allows for 1 table connection at a time. To compensate for this i used 2 ado connections. 1 for each table. The details table shold store the date of an infraction for the employee(infraction-Date that employee screwed their attendance up by a no-call/no-show, coming in late, etc)

My problem is if an infraction is entered it might be the first time that employee got an infraction and i get an error because im moving both tables at the same time. I know basically im supposed to use an if exists clause for this and then next time the details page is available then re synchronize the tables according to employee id. My prob is I only know what it says in a book. Ive no practical experience. I guess what is like to see is a very simple
vb program hooked up to a database with 2 table a main table and a details table. Then I would be fine As i can pick apart the code to see what it does.

Normally Id just attach my project as a whole but as thier are very complicated calculations in it, Im not yet done with my error checking for these calulations.

Also from what I do know of databases and structure I currently have my table setup correctly I wanted to verify that at this point and hopefully ...see a sample program of this as stated above.You can only have one DATABASE per ADO Connection but the connection CAN operate on multiple tables as long as they all reside in the same database.|||Here's a VERY Q&D example that uses the Northwind DB and lists the products ordered for May 1998.

'******************************

Dim ado As New ADODB.Connection

Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

Dim sql As String

Private Sub Form_Load()

ado.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=GRAHAMT"

ado.Open

sql = "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate >= '1998/05/01' Order By OrderID"

rs1.Open sql, ado, adOpenForwardOnly, adLockReadOnly

If Not rs1.EOF Then
While Not rs1.EOF
sql = "SELECT ProductID From [Order Details] Where OrderID=" & CStr(rs1!OrderID)
rs2.Open sql, ado, adOpenForwardOnly, adLockReadOnly

If Not rs2.EOF Then
While Not rs2.EOF
Debug.Print rs1!OrderID, rs2!ProductID
rs2.MoveNext
Wend
End If

rs2.Close

rs1.MoveNext
Wend
End If

rs1.Close
ado.Close

Set rs1 = Nothing
Set rs2 = Nothing
Set ado = Nothing

End Sub|||... and before anybody says it, I know the example is lousy coding, it's just to show that you CAN have multiple tables open through one connection. Obviously a JOIN would be more efficient in the select but I'll leave the syntax of that for one of the SQL experts.

:)

newbie needs dataset-sqltable update code

I have dataadapter and dataset that reads/writes to SQL tables.
I can read. I can create "new" records.
However, I have not been able to master the "updating" of an existing row.
Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below.
The code I using is below. I don't get error, but changes do not get written to SQL dbase.
For starters, I think I "not" supposed to use the 2nd line(...NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row.
thanks to any who can help

Dim drow As DataRow
drow = Me.dsRequests1.Tables("REQUESTS").NewRow
drow.BeginEdit()
drow.Item("Request_Name") = Me.txtRequestName.Text
drow.Item("Request_Comments_Txt") = Me.txtRequestComments.Text
drow.Item("Requestor_Contact_Id") = Me.txtRequestor.Text
drow.Item("Request_BigX_Status_Type_Cd") = Me.ddlBigXStatus
drow.Item("Request_Action_Type_Cd") = Me.ddlRequestActionRequested.SelectedItem.Text
drow.EndEdit()
Me.DaREQUESTS.Update(Me.dsRequests1.Tables("REQUESTS"))
Me.dsRequests1.AcceptChanges()You're right
NewRow is only for new rows,
To update an existing row you should find the row in your dataset/datatable - using your table's primary key and find method or by index - change it's values , and update your dataset/datatable
Something like this (if you're using a typed dataset)

Dim drow as DataRow = dsRequests.Requests.findbyprimarykey(myParimaryKeyValue)
drow.property = newValue
daRequests.update(myTable)

hth

Newbie needs code pages for SQL Server 2000 access from asp.net page using vb.net

I am only trying to connect to a local host .
I am on Windows 2000 Server with sql 2000 server.
My error is the classic "SQL server does not exist or access denied"
I went to the MS site & they tell me what I know....."some" permissioning
issue.
I had this code working 2 months ago on a different server but now I cannot
get it going now on a different
machine
I can setup ODBC connections every which way to this local server using
Integrated mode access
using different connectivity methods such as by using "local" or
<machinename> or 127.0.0.1 or <machine IP address>. I can also connect
using SQL authentication for user sa or some other new user I created.
So I am not sure about this access denied BS.
I need to connect to Northwind & pubs dbs (the sample dbs that come with sql
2000)
Please post the complete page in (without code behind crap for now).
I went to different sites & they have partial code & they cause different
errors ( I am not a Vb.net guru)
The page I used is something similar to below.
I am just trying to connect & print the server name & SQL version etc
'================ Sub Page_Load(Source As Object, E As EventArgs)
Dim strConnection1 As String = "server=localhost; database=Northwind; " & _
"integrated security=true"
Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
"FROM Employees;"
Dim objCommand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
Response.Write("ServerVersion: " & objConnection.ServerVersion & _
vbCRLF & "Datasource: " & objConnection.DataSource & _
vbCRLF & "Database: " & objConnection.Database)
dgNameList.DataSource = objCommand.ExecuteReader()
dgNameList.DataBind()
objConnection.Close()
End Sub
'==================
Can someone tell me what is wrong & also ALL the authentication settings
step by step I need in Windows 2000 server / SQL 2000 server ?
This is just the freaking local machine & server. I cannot believe this is
so hard.
Last time someone in some newsgroup had me play with registry settings to
make this work
in addition to some other Windows 2000 user changes
(Sorry I did not save it ...did not know this would be so bad)
I would prefer a complete code page that does both Integrated Auth (as
above) and also
SQL auth (using Username / PW).
(I know the actual call is a one or two line code but the exact format
without syntax or other errors is the key)
I don't have VS-7 so I cannot drag & drop the SQL connector control as
someone suggested.Lori,
When you are connecting to SQL server in integrated security mode IIS is
passing SQL server the account used to run the website. If you haven't
changed the web site to use impersonation (you would do that in the
web.config file) then the site is passing SQL server the anonymous login
account which the website would normally run under. Depending on your needs
their are multiple ways to configure this.
Here's a good article to get you started:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
Sincerely,
--
S. Justin Gengo, MCP
Web Developer
Free code library at:
www.aboutfortunate.com
"Out of chaos comes order."
Nietzche
"Lori" <__--LoriG--_@.yahoo.com> wrote in message
news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
>
> I am only trying to connect to a local host .
> I am on Windows 2000 Server with sql 2000 server.
>
> My error is the classic "SQL server does not exist or access denied"
> I went to the MS site & they tell me what I know....."some" permissioning
> issue.
> I had this code working 2 months ago on a different server but now I
cannot
> get it going now on a different
> machine
> I can setup ODBC connections every which way to this local server using
> Integrated mode access
> using different connectivity methods such as by using "local" or
> <machinename> or 127.0.0.1 or <machine IP address>. I can also connect
> using SQL authentication for user sa or some other new user I created.
> So I am not sure about this access denied BS.
> I need to connect to Northwind & pubs dbs (the sample dbs that come with
sql
> 2000)
> Please post the complete page in (without code behind crap for now).
> I went to different sites & they have partial code & they cause different
> errors ( I am not a Vb.net guru)
> The page I used is something similar to below.
> I am just trying to connect & print the server name & SQL version etc
> '================> Sub Page_Load(Source As Object, E As EventArgs)
> Dim strConnection1 As String = "server=localhost; database=Northwind; " &
_
> "integrated security=true"
> Dim objConnection As New SqlConnection(strConnection)
> Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> "FROM Employees;"
> Dim objCommand As New SqlCommand(strSQL, objConnection)
> objConnection.Open()
> Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> vbCRLF & "Datasource: " & objConnection.DataSource & _
> vbCRLF & "Database: " & objConnection.Database)
> dgNameList.DataSource = objCommand.ExecuteReader()
> dgNameList.DataBind()
> objConnection.Close()
> End Sub
> '==================> Can someone tell me what is wrong & also ALL the authentication settings
> step by step I need in Windows 2000 server / SQL 2000 server ?
> This is just the freaking local machine & server. I cannot believe this is
> so hard.
> Last time someone in some newsgroup had me play with registry settings to
> make this work
> in addition to some other Windows 2000 user changes
> (Sorry I did not save it ...did not know this would be so bad)
> I would prefer a complete code page that does both Integrated Auth (as
> above) and also
> SQL auth (using Username / PW).
> (I know the actual call is a one or two line code but the exact format
> without syntax or other errors is the key)
>
> I don't have VS-7 so I cannot drag & drop the SQL connector control as
> someone suggested.
>
>
>
>|||"S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> If you haven't
> changed the web site to use impersonation (you would do that in the
> web.config file) then the site is passing SQL server the anonymous login
> account which the website would normally run under.
I have no idea what you mean. Is there an error in logic such as your
sttement should read
======================> If you haven't
> changed the web site to use impersonation (you would do that in the
> web.config file) then the site is NOT passing SQL server the anonymous
login
> account which the website would normally run under
=============================
> Depending on your needs
> their are multiple ways to configure this.
> Here's a good article to get you started:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
The above site shows how to do a DSN connection.
I already said I am able to do a DSN conncection (but am not using it)
If you look at my sample code, it is a DSNless conncection, is it not ?|||And another thing.
The Microsoft error message is so misleading.
If there is a problem with IIS permissions why the hell does it say
"SQL server does not exist ?" Very helpful if troubleshooting is it not , by
misleading you ?
Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS ?
Can they be anymore vague ?
"S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> Lori,
> When you are connecting to SQL server in integrated security mode IIS is
> passing SQL server the account used to run the website. If you haven't
> changed the web site to use impersonation (you would do that in the
> web.config file) then the site is passing SQL server the anonymous login
> account which the website would normally run under. Depending on your
needs
> their are multiple ways to configure this.
> Here's a good article to get you started:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> Sincerely,
> --
> S. Justin Gengo, MCP
> Web Developer
> Free code library at:
> www.aboutfortunate.com
> "Out of chaos comes order."
> Nietzche
>
> "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> >
> >
> > I am only trying to connect to a local host .
> > I am on Windows 2000 Server with sql 2000 server.
> >
> >
> > My error is the classic "SQL server does not exist or access denied"
> > I went to the MS site & they tell me what I know....."some"
permissioning
> > issue.
> >
> > I had this code working 2 months ago on a different server but now I
> cannot
> > get it going now on a different
> > machine
> >
> > I can setup ODBC connections every which way to this local server using
> > Integrated mode access
> > using different connectivity methods such as by using "local" or
> > <machinename> or 127.0.0.1 or <machine IP address>. I can also connect
> > using SQL authentication for user sa or some other new user I created.
> > So I am not sure about this access denied BS.
> >
> > I need to connect to Northwind & pubs dbs (the sample dbs that come with
> sql
> > 2000)
> > Please post the complete page in (without code behind crap for now).
> >
> > I went to different sites & they have partial code & they cause
different
> > errors ( I am not a Vb.net guru)
> >
> > The page I used is something similar to below.
> >
> > I am just trying to connect & print the server name & SQL version etc
> >
> > '================> > Sub Page_Load(Source As Object, E As EventArgs)
> >
> > Dim strConnection1 As String = "server=localhost; database=Northwind; "
&
> _
> >
> > "integrated security=true"
> >
> > Dim objConnection As New SqlConnection(strConnection)
> >
> > Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> >
> > "FROM Employees;"
> >
> > Dim objCommand As New SqlCommand(strSQL, objConnection)
> >
> > objConnection.Open()
> >
> > Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> >
> > vbCRLF & "Datasource: " & objConnection.DataSource & _
> >
> > vbCRLF & "Database: " & objConnection.Database)
> >
> > dgNameList.DataSource = objCommand.ExecuteReader()
> >
> > dgNameList.DataBind()
> >
> > objConnection.Close()
> >
> > End Sub
> >
> > '==================> >
> > Can someone tell me what is wrong & also ALL the authentication settings
> > step by step I need in Windows 2000 server / SQL 2000 server ?
> > This is just the freaking local machine & server. I cannot believe this
is
> > so hard.
> > Last time someone in some newsgroup had me play with registry settings
to
> > make this work
> > in addition to some other Windows 2000 user changes
> > (Sorry I did not save it ...did not know this would be so bad)
> >
> > I would prefer a complete code page that does both Integrated Auth (as
> > above) and also
> > SQL auth (using Username / PW).
> > (I know the actual call is a one or two line code but the exact format
> > without syntax or other errors is the key)
> >
> >
> > I don't have VS-7 so I cannot drag & drop the SQL connector control as
> > someone suggested.
> >
> >
> >
> >
> >
> >
> >
>|||What I am trying say is this
It would make more sense if the error message described that permission was
denied at one of the
possible 3 layers . Even the KB article does not make any references to the
IIS layer.
That said, I am not sure what user to add where in IIS
And another thing.
How would the SQL Auth. work ? Does it not go thru IIS also anyway,
regardless ?
"Lori" <__--LoriG--_@.yahoo.com> wrote in message
news:bjq07b$ljgdk$1@.ID-158805.news.uni-berlin.de...
> And another thing.
> The Microsoft error message is so misleading.
> If there is a problem with IIS permissions why the hell does it say
> "SQL server does not exist ?" Very helpful if troubleshooting is it not ,
by
> misleading you ?
> Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS ?
> Can they be anymore vague ?
> "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > Lori,
> >
> > When you are connecting to SQL server in integrated security mode IIS is
> > passing SQL server the account used to run the website. If you haven't
> > changed the web site to use impersonation (you would do that in the
> > web.config file) then the site is passing SQL server the anonymous login
> > account which the website would normally run under. Depending on your
> needs
> > their are multiple ways to configure this.
> >
> > Here's a good article to get you started:
> >
> > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> >
> > Sincerely,
> >
> > --
> > S. Justin Gengo, MCP
> > Web Developer
> >
> > Free code library at:
> > www.aboutfortunate.com
> >
> > "Out of chaos comes order."
> > Nietzche
> >
> >
> > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> > >
> > >
> > > I am only trying to connect to a local host .
> > > I am on Windows 2000 Server with sql 2000 server.
> > >
> > >
> > > My error is the classic "SQL server does not exist or access denied"
> > > I went to the MS site & they tell me what I know....."some"
> permissioning
> > > issue.
> > >
> > > I had this code working 2 months ago on a different server but now I
> > cannot
> > > get it going now on a different
> > > machine
> > >
> > > I can setup ODBC connections every which way to this local server
using
> > > Integrated mode access
> > > using different connectivity methods such as by using "local" or
> > > <machinename> or 127.0.0.1 or <machine IP address>. I can also
connect
> > > using SQL authentication for user sa or some other new user I created.
> > > So I am not sure about this access denied BS.
> > >
> > > I need to connect to Northwind & pubs dbs (the sample dbs that come
with
> > sql
> > > 2000)
> > > Please post the complete page in (without code behind crap for now).
> > >
> > > I went to different sites & they have partial code & they cause
> different
> > > errors ( I am not a Vb.net guru)
> > >
> > > The page I used is something similar to below.
> > >
> > > I am just trying to connect & print the server name & SQL version etc
> > >
> > > '================> > > Sub Page_Load(Source As Object, E As EventArgs)
> > >
> > > Dim strConnection1 As String = "server=localhost; database=Northwind;
"
> &
> > _
> > >
> > > "integrated security=true"
> > >
> > > Dim objConnection As New SqlConnection(strConnection)
> > >
> > > Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> > >
> > > "FROM Employees;"
> > >
> > > Dim objCommand As New SqlCommand(strSQL, objConnection)
> > >
> > > objConnection.Open()
> > >
> > > Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> > >
> > > vbCRLF & "Datasource: " & objConnection.DataSource & _
> > >
> > > vbCRLF & "Database: " & objConnection.Database)
> > >
> > > dgNameList.DataSource = objCommand.ExecuteReader()
> > >
> > > dgNameList.DataBind()
> > >
> > > objConnection.Close()
> > >
> > > End Sub
> > >
> > > '==================> > >
> > > Can someone tell me what is wrong & also ALL the authentication
settings
> > > step by step I need in Windows 2000 server / SQL 2000 server ?
> > > This is just the freaking local machine & server. I cannot believe
this
> is
> > > so hard.
> > > Last time someone in some newsgroup had me play with registry
settings
> to
> > > make this work
> > > in addition to some other Windows 2000 user changes
> > > (Sorry I did not save it ...did not know this would be so bad)
> > >
> > > I would prefer a complete code page that does both Integrated Auth (as
> > > above) and also
> > > SQL auth (using Username / PW).
> > > (I know the actual call is a one or two line code but the exact format
> > > without syntax or other errors is the key)
> > >
> > >
> > > I don't have VS-7 so I cannot drag & drop the SQL connector control as
> > > someone suggested.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||Lori,
S. Justin Gengo, MCP
Web Developer
Free code library at:
www.aboutfortunate.com
"Out of chaos comes order."
Nietzche
"Lori" <__--LoriG--_@.yahoo.com> wrote in message
news:bjq07b$ljgdk$1@.ID-158805.news.uni-berlin.de...
> And another thing.
> The Microsoft error message is so misleading.
> If there is a problem with IIS permissions why the hell does it say
> "SQL server does not exist ?" Very helpful if troubleshooting is it not ,
by
> misleading you ?
> Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS ?
> Can they be anymore vague ?
> "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > Lori,
> >
> > When you are connecting to SQL server in integrated security mode IIS is
> > passing SQL server the account used to run the website. If you haven't
> > changed the web site to use impersonation (you would do that in the
> > web.config file) then the site is passing SQL server the anonymous login
> > account which the website would normally run under. Depending on your
> needs
> > their are multiple ways to configure this.
> >
> > Here's a good article to get you started:
> >
> > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> >
> > Sincerely,
> >
> > --
> > S. Justin Gengo, MCP
> > Web Developer
> >
> > Free code library at:
> > www.aboutfortunate.com
> >
> > "Out of chaos comes order."
> > Nietzche
> >
> >
> > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> > >
> > >
> > > I am only trying to connect to a local host .
> > > I am on Windows 2000 Server with sql 2000 server.
> > >
> > >
> > > My error is the classic "SQL server does not exist or access denied"
> > > I went to the MS site & they tell me what I know....."some"
> permissioning
> > > issue.
> > >
> > > I had this code working 2 months ago on a different server but now I
> > cannot
> > > get it going now on a different
> > > machine
> > >
> > > I can setup ODBC connections every which way to this local server
using
> > > Integrated mode access
> > > using different connectivity methods such as by using "local" or
> > > <machinename> or 127.0.0.1 or <machine IP address>. I can also
connect
> > > using SQL authentication for user sa or some other new user I created.
> > > So I am not sure about this access denied BS.
> > >
> > > I need to connect to Northwind & pubs dbs (the sample dbs that come
with
> > sql
> > > 2000)
> > > Please post the complete page in (without code behind crap for now).
> > >
> > > I went to different sites & they have partial code & they cause
> different
> > > errors ( I am not a Vb.net guru)
> > >
> > > The page I used is something similar to below.
> > >
> > > I am just trying to connect & print the server name & SQL version etc
> > >
> > > '================> > > Sub Page_Load(Source As Object, E As EventArgs)
> > >
> > > Dim strConnection1 As String = "server=localhost; database=Northwind;
"
> &
> > _
> > >
> > > "integrated security=true"
> > >
> > > Dim objConnection As New SqlConnection(strConnection)
> > >
> > > Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> > >
> > > "FROM Employees;"
> > >
> > > Dim objCommand As New SqlCommand(strSQL, objConnection)
> > >
> > > objConnection.Open()
> > >
> > > Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> > >
> > > vbCRLF & "Datasource: " & objConnection.DataSource & _
> > >
> > > vbCRLF & "Database: " & objConnection.Database)
> > >
> > > dgNameList.DataSource = objCommand.ExecuteReader()
> > >
> > > dgNameList.DataBind()
> > >
> > > objConnection.Close()
> > >
> > > End Sub
> > >
> > > '==================> > >
> > > Can someone tell me what is wrong & also ALL the authentication
settings
> > > step by step I need in Windows 2000 server / SQL 2000 server ?
> > > This is just the freaking local machine & server. I cannot believe
this
> is
> > > so hard.
> > > Last time someone in some newsgroup had me play with registry
settings
> to
> > > make this work
> > > in addition to some other Windows 2000 user changes
> > > (Sorry I did not save it ...did not know this would be so bad)
> > >
> > > I would prefer a complete code page that does both Integrated Auth (as
> > > above) and also
> > > SQL auth (using Username / PW).
> > > (I know the actual call is a one or two line code but the exact format
> > > without syntax or other errors is the key)
> > >
> > >
> > > I don't have VS-7 so I cannot drag & drop the SQL connector control as
> > > someone suggested.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||Lori,
Sorry about that link. I copied and pasted the wrong one from my list.
Here's the right one:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetch12.asp
Sincerely,
--
S. Justin Gengo, MCP
Web Developer
Free code library at:
www.aboutfortunate.com
"Out of chaos comes order."
Nietzche
"Lori" <__--LoriG--_@.yahoo.com> wrote in message
news:bjq0u6$kldqf$1@.ID-158805.news.uni-berlin.de...
> What I am trying say is this
> It would make more sense if the error message described that permission
was
> denied at one of the
> possible 3 layers . Even the KB article does not make any references to
the
> IIS layer.
> That said, I am not sure what user to add where in IIS
> And another thing.
> How would the SQL Auth. work ? Does it not go thru IIS also anyway,
> regardless ?
>
> "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> news:bjq07b$ljgdk$1@.ID-158805.news.uni-berlin.de...
> > And another thing.
> > The Microsoft error message is so misleading.
> > If there is a problem with IIS permissions why the hell does it say
> > "SQL server does not exist ?" Very helpful if troubleshooting is it not
,
> by
> > misleading you ?
> > Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS ?
> > Can they be anymore vague ?
> >
> > "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> > news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > Lori,
> > >
> > > When you are connecting to SQL server in integrated security mode IIS
is
> > > passing SQL server the account used to run the website. If you haven't
> > > changed the web site to use impersonation (you would do that in the
> > > web.config file) then the site is passing SQL server the anonymous
login
> > > account which the website would normally run under. Depending on your
> > needs
> > > their are multiple ways to configure this.
> > >
> > > Here's a good article to get you started:
> > >
> > > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> > >
> > > Sincerely,
> > >
> > > --
> > > S. Justin Gengo, MCP
> > > Web Developer
> > >
> > > Free code library at:
> > > www.aboutfortunate.com
> > >
> > > "Out of chaos comes order."
> > > Nietzche
> > >
> > >
> > > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > > news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> > > >
> > > >
> > > > I am only trying to connect to a local host .
> > > > I am on Windows 2000 Server with sql 2000 server.
> > > >
> > > >
> > > > My error is the classic "SQL server does not exist or access denied"
> > > > I went to the MS site & they tell me what I know....."some"
> > permissioning
> > > > issue.
> > > >
> > > > I had this code working 2 months ago on a different server but now I
> > > cannot
> > > > get it going now on a different
> > > > machine
> > > >
> > > > I can setup ODBC connections every which way to this local server
> using
> > > > Integrated mode access
> > > > using different connectivity methods such as by using "local" or
> > > > <machinename> or 127.0.0.1 or <machine IP address>. I can also
> connect
> > > > using SQL authentication for user sa or some other new user I
created.
> > > > So I am not sure about this access denied BS.
> > > >
> > > > I need to connect to Northwind & pubs dbs (the sample dbs that come
> with
> > > sql
> > > > 2000)
> > > > Please post the complete page in (without code behind crap for now).
> > > >
> > > > I went to different sites & they have partial code & they cause
> > different
> > > > errors ( I am not a Vb.net guru)
> > > >
> > > > The page I used is something similar to below.
> > > >
> > > > I am just trying to connect & print the server name & SQL version
etc
> > > >
> > > > '================> > > > Sub Page_Load(Source As Object, E As EventArgs)
> > > >
> > > > Dim strConnection1 As String = "server=localhost;
database=Northwind;
> "
> > &
> > > _
> > > >
> > > > "integrated security=true"
> > > >
> > > > Dim objConnection As New SqlConnection(strConnection)
> > > >
> > > > Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> > > >
> > > > "FROM Employees;"
> > > >
> > > > Dim objCommand As New SqlCommand(strSQL, objConnection)
> > > >
> > > > objConnection.Open()
> > > >
> > > > Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> > > >
> > > > vbCRLF & "Datasource: " & objConnection.DataSource & _
> > > >
> > > > vbCRLF & "Database: " & objConnection.Database)
> > > >
> > > > dgNameList.DataSource = objCommand.ExecuteReader()
> > > >
> > > > dgNameList.DataBind()
> > > >
> > > > objConnection.Close()
> > > >
> > > > End Sub
> > > >
> > > > '==================> > > >
> > > > Can someone tell me what is wrong & also ALL the authentication
> settings
> > > > step by step I need in Windows 2000 server / SQL 2000 server ?
> > > > This is just the freaking local machine & server. I cannot believe
> this
> > is
> > > > so hard.
> > > > Last time someone in some newsgroup had me play with registry
> settings
> > to
> > > > make this work
> > > > in addition to some other Windows 2000 user changes
> > > > (Sorry I did not save it ...did not know this would be so bad)
> > > >
> > > > I would prefer a complete code page that does both Integrated Auth
(as
> > > > above) and also
> > > > SQL auth (using Username / PW).
> > > > (I know the actual call is a one or two line code but the exact
format
> > > > without syntax or other errors is the key)
> > > >
> > > >
> > > > I don't have VS-7 so I cannot drag & drop the SQL connector control
as
> > > > someone suggested.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
news:O6EeMLHeDHA.3224@.tk2msftngp13.phx.gbl...
> Lori,
>
?
Yes ?|||I appreciate the link but honestly, it is a typical Microsoft link.
They just thorough bits & pieces here & there.
What I need is this (to achieve the simple goal)
1. What I need to do at the Windows 2000 Server level ( User security
settings, Registry or whatever)
2. What I need to do at the IIS-5 level
3. The asp.net code page using vb.net (or even c# is fine).
I was hoping someone would already have the code page & tell me the
corresponding settings
for items 2 & 3 above on their machine to use the code.
I have done enough "fishing" on this & getting tired of it.
"S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
news:eCg4FNHeDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Lori,
> Sorry about that link. I copied and pasted the wrong one from my list.
> Here's the right one:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetch12.asp
>
> Sincerely,
> --
> S. Justin Gengo, MCP
> Web Developer
> Free code library at:
> www.aboutfortunate.com
> "Out of chaos comes order."
> Nietzche
>
> "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> news:bjq0u6$kldqf$1@.ID-158805.news.uni-berlin.de...
> > What I am trying say is this
> > It would make more sense if the error message described that permission
> was
> > denied at one of the
> > possible 3 layers . Even the KB article does not make any references to
> the
> > IIS layer.
> >
> > That said, I am not sure what user to add where in IIS
> >
> > And another thing.
> > How would the SQL Auth. work ? Does it not go thru IIS also anyway,
> > regardless ?
> >
> >
> > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > news:bjq07b$ljgdk$1@.ID-158805.news.uni-berlin.de...
> > > And another thing.
> > > The Microsoft error message is so misleading.
> > > If there is a problem with IIS permissions why the hell does it say
> > > "SQL server does not exist ?" Very helpful if troubleshooting is it
not
> ,
> > by
> > > misleading you ?
> > > Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS
?
> > > Can they be anymore vague ?
> > >
> > > "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> > > news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > > Lori,
> > > >
> > > > When you are connecting to SQL server in integrated security mode
IIS
> is
> > > > passing SQL server the account used to run the website. If you
haven't
> > > > changed the web site to use impersonation (you would do that in the
> > > > web.config file) then the site is passing SQL server the anonymous
> login
> > > > account which the website would normally run under. Depending on
your
> > > needs
> > > > their are multiple ways to configure this.
> > > >
> > > > Here's a good article to get you started:
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> > > >
> > > > Sincerely,
> > > >
> > > > --
> > > > S. Justin Gengo, MCP
> > > > Web Developer
> > > >
> > > > Free code library at:
> > > > www.aboutfortunate.com
> > > >
> > > > "Out of chaos comes order."
> > > > Nietzche
> > > >
> > > >
> > > > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > > > news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> > > > >
> > > > >
> > > > > I am only trying to connect to a local host .
> > > > > I am on Windows 2000 Server with sql 2000 server.
> > > > >
> > > > >
> > > > > My error is the classic "SQL server does not exist or access
denied"
> > > > > I went to the MS site & they tell me what I know....."some"
> > > permissioning
> > > > > issue.
> > > > >
> > > > > I had this code working 2 months ago on a different server but now
I
> > > > cannot
> > > > > get it going now on a different
> > > > > machine
> > > > >
> > > > > I can setup ODBC connections every which way to this local server
> > using
> > > > > Integrated mode access
> > > > > using different connectivity methods such as by using "local" or
> > > > > <machinename> or 127.0.0.1 or <machine IP address>. I can also
> > connect
> > > > > using SQL authentication for user sa or some other new user I
> created.
> > > > > So I am not sure about this access denied BS.
> > > > >
> > > > > I need to connect to Northwind & pubs dbs (the sample dbs that
come
> > with
> > > > sql
> > > > > 2000)
> > > > > Please post the complete page in (without code behind crap for
now).
> > > > >
> > > > > I went to different sites & they have partial code & they cause
> > > different
> > > > > errors ( I am not a Vb.net guru)
> > > > >
> > > > > The page I used is something similar to below.
> > > > >
> > > > > I am just trying to connect & print the server name & SQL version
> etc
> > > > >
> > > > > '================> > > > > Sub Page_Load(Source As Object, E As EventArgs)
> > > > >
> > > > > Dim strConnection1 As String = "server=localhost;
> database=Northwind;
> > "
> > > &
> > > > _
> > > > >
> > > > > "integrated security=true"
> > > > >
> > > > > Dim objConnection As New SqlConnection(strConnection)
> > > > >
> > > > > Dim strSQL As String = "SELECT FirstName, LastName, Country " & _
> > > > >
> > > > > "FROM Employees;"
> > > > >
> > > > > Dim objCommand As New SqlCommand(strSQL, objConnection)
> > > > >
> > > > > objConnection.Open()
> > > > >
> > > > > Response.Write("ServerVersion: " & objConnection.ServerVersion & _
> > > > >
> > > > > vbCRLF & "Datasource: " & objConnection.DataSource & _
> > > > >
> > > > > vbCRLF & "Database: " & objConnection.Database)
> > > > >
> > > > > dgNameList.DataSource = objCommand.ExecuteReader()
> > > > >
> > > > > dgNameList.DataBind()
> > > > >
> > > > > objConnection.Close()
> > > > >
> > > > > End Sub
> > > > >
> > > > > '==================> > > > >
> > > > > Can someone tell me what is wrong & also ALL the authentication
> > settings
> > > > > step by step I need in Windows 2000 server / SQL 2000 server ?
> > > > > This is just the freaking local machine & server. I cannot believe
> > this
> > > is
> > > > > so hard.
> > > > > Last time someone in some newsgroup had me play with registry
> > settings
> > > to
> > > > > make this work
> > > > > in addition to some other Windows 2000 user changes
> > > > > (Sorry I did not save it ...did not know this would be so bad)
> > > > >
> > > > > I would prefer a complete code page that does both Integrated Auth
> (as
> > > > > above) and also
> > > > > SQL auth (using Username / PW).
> > > > > (I know the actual call is a one or two line code but the exact
> format
> > > > > without syntax or other errors is the key)
> > > > >
> > > > >
> > > > > I don't have VS-7 so I cannot drag & drop the SQL connector
control
> as
> > > > > someone suggested.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Actually it should read like this
1. What I need to do at the Windows 2000 Server level ( User security
settings, Registry or whatever)
2. What I need to set at the SQL server 2000 level
3. What I need to do at the IIS-5 level
4. The asp.net code page using vb.net (or even c# is fine) & the project
file including web.config file if any
I ran into 100's code pages ( Item 4 ) that tells you how to connect but
none of them address other layers
(Items 1 thru 3 above)
I am hoping some of you can tell me your machine configs for items 1 thru 3
above.
I think item 4 is ok with what I have. (And I suspect item 2 is OK 2 for me)
It is the OS level or IIS settings that are always a pain.
It is amazing how painful it is for even everything local to same machine
"Lori" <__--LoriG--_@.yahoo.com> wrote in message
news:bjqbou$ltkeg$1@.ID-158805.news.uni-berlin.de...
> I appreciate the link but honestly, it is a typical Microsoft link.
> They just thorough bits & pieces here & there.
> What I need is this (to achieve the simple goal)
> 1. What I need to do at the Windows 2000 Server level ( User security
> settings, Registry or whatever)
> 2. What I need to do at the IIS-5 level
> 3. The asp.net code page using vb.net (or even c# is fine).
> I was hoping someone would already have the code page & tell me the
> corresponding settings
> for items 2 & 3 above on their machine to use the code.
> I have done enough "fishing" on this & getting tired of it.
>
>
> "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> news:eCg4FNHeDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > Lori,
> >
> > Sorry about that link. I copied and pasted the wrong one from my list.
> >
> > Here's the right one:
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetch12.asp
> >
> >
> > Sincerely,
> >
> > --
> > S. Justin Gengo, MCP
> > Web Developer
> >
> > Free code library at:
> > www.aboutfortunate.com
> >
> > "Out of chaos comes order."
> > Nietzche
> >
> >
> > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > news:bjq0u6$kldqf$1@.ID-158805.news.uni-berlin.de...
> > > What I am trying say is this
> > > It would make more sense if the error message described that
permission
> > was
> > > denied at one of the
> > > possible 3 layers . Even the KB article does not make any references
to
> > the
> > > IIS layer.
> > >
> > > That said, I am not sure what user to add where in IIS
> > >
> > > And another thing.
> > > How would the SQL Auth. work ? Does it not go thru IIS also anyway,
> > > regardless ?
> > >
> > >
> > > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > > news:bjq07b$ljgdk$1@.ID-158805.news.uni-berlin.de...
> > > > And another thing.
> > > > The Microsoft error message is so misleading.
> > > > If there is a problem with IIS permissions why the hell does it say
> > > > "SQL server does not exist ?" Very helpful if troubleshooting is it
> not
> > ,
> > > by
> > > > misleading you ?
> > > > Also "access denied" by whom by SQL server ? By 2000 Server ? By
IIS
> ?
> > > > Can they be anymore vague ?
> > > >
> > > > "S. Justin Gengo" <sjgengo@.aboutfortunate.com> wrote in message
> > > > news:eNMgDwGeDHA.1748@.TK2MSFTNGP10.phx.gbl...
> > > > > Lori,
> > > > >
> > > > > When you are connecting to SQL server in integrated security mode
> IIS
> > is
> > > > > passing SQL server the account used to run the website. If you
> haven't
> > > > > changed the web site to use impersonation (you would do that in
the
> > > > > web.config file) then the site is passing SQL server the anonymous
> > login
> > > > > account which the website would normally run under. Depending on
> your
> > > > needs
> > > > > their are multiple ways to configure this.
> > > > >
> > > > > Here's a good article to get you started:
> > > > >
> > > > > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378
> > > > >
> > > > > Sincerely,
> > > > >
> > > > > --
> > > > > S. Justin Gengo, MCP
> > > > > Web Developer
> > > > >
> > > > > Free code library at:
> > > > > www.aboutfortunate.com
> > > > >
> > > > > "Out of chaos comes order."
> > > > > Nietzche
> > > > >
> > > > >
> > > > > "Lori" <__--LoriG--_@.yahoo.com> wrote in message
> > > > > news:bjpt0d$li3vt$1@.ID-158805.news.uni-berlin.de...
> > > > > >
> > > > > >
> > > > > > I am only trying to connect to a local host .
> > > > > > I am on Windows 2000 Server with sql 2000 server.
> > > > > >
> > > > > >
> > > > > > My error is the classic "SQL server does not exist or access
> denied"
> > > > > > I went to the MS site & they tell me what I know....."some"
> > > > permissioning
> > > > > > issue.
> > > > > >
> > > > > > I had this code working 2 months ago on a different server but
now
> I
> > > > > cannot
> > > > > > get it going now on a different
> > > > > > machine
> > > > > >
> > > > > > I can setup ODBC connections every which way to this local
server
> > > using
> > > > > > Integrated mode access
> > > > > > using different connectivity methods such as by using "local" or
> > > > > > <machinename> or 127.0.0.1 or <machine IP address>. I can also
> > > connect
> > > > > > using SQL authentication for user sa or some other new user I
> > created.
> > > > > > So I am not sure about this access denied BS.
> > > > > >
> > > > > > I need to connect to Northwind & pubs dbs (the sample dbs that
> come
> > > with
> > > > > sql
> > > > > > 2000)
> > > > > > Please post the complete page in (without code behind crap for
> now).
> > > > > >
> > > > > > I went to different sites & they have partial code & they cause
> > > > different
> > > > > > errors ( I am not a Vb.net guru)
> > > > > >
> > > > > > The page I used is something similar to below.
> > > > > >
> > > > > > I am just trying to connect & print the server name & SQL
version
> > etc
> > > > > >
> > > > > > '================> > > > > > Sub Page_Load(Source As Object, E As EventArgs)
> > > > > >
> > > > > > Dim strConnection1 As String = "server=localhost;
> > database=Northwind;
> > > "
> > > > &
> > > > > _
> > > > > >
> > > > > > "integrated security=true"
> > > > > >
> > > > > > Dim objConnection As New SqlConnection(strConnection)
> > > > > >
> > > > > > Dim strSQL As String = "SELECT FirstName, LastName, Country " &
_
> > > > > >
> > > > > > "FROM Employees;"
> > > > > >
> > > > > > Dim objCommand As New SqlCommand(strSQL, objConnection)
> > > > > >
> > > > > > objConnection.Open()
> > > > > >
> > > > > > Response.Write("ServerVersion: " & objConnection.ServerVersion &
_
> > > > > >
> > > > > > vbCRLF & "Datasource: " & objConnection.DataSource & _
> > > > > >
> > > > > > vbCRLF & "Database: " & objConnection.Database)
> > > > > >
> > > > > > dgNameList.DataSource = objCommand.ExecuteReader()
> > > > > >
> > > > > > dgNameList.DataBind()
> > > > > >
> > > > > > objConnection.Close()
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > '==================> > > > > >
> > > > > > Can someone tell me what is wrong & also ALL the authentication
> > > settings
> > > > > > step by step I need in Windows 2000 server / SQL 2000 server ?
> > > > > > This is just the freaking local machine & server. I cannot
believe
> > > this
> > > > is
> > > > > > so hard.
> > > > > > Last time someone in some newsgroup had me play with registry
> > > settings
> > > > to
> > > > > > make this work
> > > > > > in addition to some other Windows 2000 user changes
> > > > > > (Sorry I did not save it ...did not know this would be so bad)
> > > > > >
> > > > > > I would prefer a complete code page that does both Integrated
Auth
> > (as
> > > > > > above) and also
> > > > > > SQL auth (using Username / PW).
> > > > > > (I know the actual call is a one or two line code but the exact
> > format
> > > > > > without syntax or other errors is the key)
> > > > > >
> > > > > >
> > > > > > I don't have VS-7 so I cannot drag & drop the SQL connector
> control
> > as
> > > > > > someone suggested.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Newbie mystery

Try running the code below in QA

USE Northwind
--The quesy below produces the correct numbers.
SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--The query below produces 0 values and are wrong.
SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--This is the total
SELECT COUNT(*) AS TotalCount FROM Products
--The totals of the groupings
SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.Dear,

In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

Example :

select 1/3 returns 0

select 1/cast(3 as numeric) returns .33333

That is just the way it is, and it is documented in BOL.

Regards,

CVM.

NewBie Here: Need Help Please (SQLCommand)

Hi Guyz, im currently studying asp.net, i need a code that retrive data from SQL database and post it using Label (web from), i dont know how to do it, need help... Thanks in advance !!!!

Hi There,

First of all import sqlclient namespace

using System.Data.SqlClient;

Read data from sql database and set value to Label1 and Label2

protectedvoid Page_Load(object sender,EventArgs e)

{

// Create database connection

SqlConnection connection =newSqlConnection("YourConnectionString");

// Create instance of command

SqlCommand command =newSqlCommand("Select Field1, Fields2 From TableName", connection);

// Open database connection

command.Connection.Open();

// Execute command and get datareader

System.Data.SqlClient.SqlDataReader datareader = command.ExecuteReader();

// Check whether or not datareader has soemthing

if (datareader.Read())

{

Label1.Text = datareader.GetString(0);// get Field1

Label2.Text = datareader.GetString(1);// get Field2

}

// Close database connection

command.Connection.Close();

}

Newbie help needed with MDX expression

I have the following MDX Expression:

Code Snippet

select
{Measures.Members} on columns,
[AccountNumber].Members on rows
from
BudgetCube

This gives me a nice tabular output with both account numbers and measures in each row.

When I run the query in MDX sample application its work like a charm.

However when I run the query from SQL Server 2000 via a Linked Server like this:

Code Snippet

SELECT
*
FROM
OPENQUERY
(
SERVER_OLAP,
'
select
{Measures.Members} on columns,
[AccountNumber].Members on rows
from
BudgetCube
'
)

I get the following error:

Server: Msg 7358, Level 16, State 1, Line 1
Could not execute query. The OLE DB provider 'MSOLAP' did not provide an appropriate interface to access the text, ntext, or image column '[MSOLAP].[AccountNumber].[AccountNumber].[MEMBER_CAPTION]'.
OLE DB error trace [Non-interface error: Could not get a storage interface for BLOB the column: ProviderName='MSOLAP' TableName='[MSOLAP]', ColumnName='[AccountNumber].[AccountNumber].[MEMBER_CAPTION]'].

How do I correct the error? I suspect I need to create a caption for AccountNumber?

I would like to remove the "All AccountNumber" row from the result, how can I do this in MDX?

I need to filter the results based on a Date Dimention, as a range how can I do this?

Thanks

1: was fixed via http://support.microsoft.com/kb/916287

Newbie - Whats wrong with following?

Hi,

This simple code using the Northwind db and SQL 2000...when I have the 2nd
from botton line commented out as I do now it works well and give me a
summary of the orders and totals from the [order details] table, nothing
special there.

However, if I uncomment the 'where ordervalue > 500' line and run it I get
an error that says "Invalid column name 'ordervalue' "

Any ideas?

Thanks,

td.

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
--where ordervalue > 500
group by o.orderid, c.companyname, o.orderdate"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:3b0pjhF6d9fg9U1@.individual.net...
> Hi,
> This simple code using the Northwind db and SQL 2000...when I have the 2nd
> from botton line commented out as I do now it works well and give me a
> summary of the orders and totals from the [order details] table, nothing
> special there.
> However, if I uncomment the 'where ordervalue > 500' line and run it I get
> an error that says "Invalid column name 'ordervalue' "
> Any ideas?
> Thanks,
> td.
>
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(unitprice * quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> --where ordervalue > 500
> group by o.orderid, c.companyname, o.orderdate

it is a bit complicated, but to get a useful error message try this instead
when joining you cant use a where on an agregate like that because the
column may not exist, it is based on the results

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
where sum(unitprice * quantity) > 500
group by o.orderid, c.companyname, o.orderdate

and here is how to make it work;

select
o.orderid
,o.orderdate
,c.companyname
,sum(od.unitprice * od.quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid

group by o.orderid, c.companyname, o.orderdate
having sum(od.unitprice * od.quantity) > 500

|||Thanks a lot, done the trick.

td.

"Lefty" <synergysynergy@.hotmail.com> wrote in message
news:424b6a22$0$57123$c30e37c6@.lon-reader.news.telstra.net...
> "toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
> news:3b0pjhF6d9fg9U1@.individual.net...
>> Hi,
>>
>> This simple code using the Northwind db and SQL 2000...when I have the
>> 2nd
>> from botton line commented out as I do now it works well and give me a
>> summary of the orders and totals from the [order details] table, nothing
>> special there.
>>
>> However, if I uncomment the 'where ordervalue > 500' line and run it I
>> get
>> an error that says "Invalid column name 'ordervalue' "
>>
>> Any ideas?
>>
>> Thanks,
>>
>> td.
>>
>>
>> select
>> o.orderid
>> ,o.orderdate
>> ,c.companyname
>> ,sum(unitprice * quantity) as ordervalue
>> from
>> orders o
>> join
>> customers c
>> on
>> o.customerid = c.customerid
>> join
>> [order details] od
>> on o.orderid = od.orderid
>> --where ordervalue > 500
>> group by o.orderid, c.companyname, o.orderdate
>>
>>
> it is a bit complicated, but to get a useful error message try this
> instead
> when joining you cant use a where on an agregate like that because the
> column may not exist, it is based on the results
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(unitprice * quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> where sum(unitprice * quantity) > 500
> group by o.orderid, c.companyname, o.orderdate
>
> and here is how to make it work;
>
> select
> o.orderid
> ,o.orderdate
> ,c.companyname
> ,sum(od.unitprice * od.quantity) as ordervalue
> from
> orders o
> join
> customers c
> on
> o.customerid = c.customerid
> join
> [order details] od
> on o.orderid = od.orderid
> group by o.orderid, c.companyname, o.orderdate
> having sum(od.unitprice * od.quantity) > 500
>
>
>
>
>|||toedipper (send_rubbish_here734@.hotmail.com) writes:
> This simple code using the Northwind db and SQL 2000...when I have the 2nd
> from botton line commented out as I do now it works well and give me a
> summary of the orders and totals from the [order details] table, nothing
> special there.
> However, if I uncomment the 'where ordervalue > 500' line and run it I get
> an error that says "Invalid column name 'ordervalue' "

The only place in the query where you can use an alias is in the ORDER
BY clause.

However, you can use a derived table:

SELECT orderid, orderdate, companyname, ordervalue
FROM (select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
group by o.orderid, c.companyname, o.orderdate) AS x
where ordervalue > 500

Another way is to write the query as:

select
o.orderid
,o.orderdate
,c.companyname
,sum(unitprice * quantity) as ordervalue
from
orders o
join
customers c
on
o.customerid = c.customerid
join
[order details] od
on o.orderid = od.orderid
group by o.orderid, c.companyname, o.orderdate
having SUM(UnitPrice * Quantity) > 500

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp