Aborting CALL to stored procedure

Hello

I am calling a stored procedure in a MSDE/SQLServer DB form within my
Visual C++ 6.0 program along the lines
CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
With
DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
It all works sweet as, but it can take a while and I want to let the
user abort it.
Everything I've tried ends in tears.Hi

You can issue a KILL command on the SQL Server which will terminate the
process. To do this you are going to need a separate thread. More
information in books online.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> Hello
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.|||I have the command running in a separate thread.
I dont want to kill the server, just the CALL. I have tried killing
the thread and using .Abort(), and most other things I can think of,
but everything results in my program crashing.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> Hi
> You can issue a KILL command on the SQL Server which will terminate the
> process. To do this you are going to need a separate thread. More
> information in books online.
> John
> "Mike Brown" <browna@.beer.com> wrote in message
> news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > Hello
> > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > Visual C++ 6.0 program along the lines
> > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > With
> > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > It all works sweet as, but it can take a while and I want to let the
> > user abort it.
> > Everything I've tried ends in tears.|||Hi

I am not sure what you mean by killing the server. Look up the KILL command
in books online.
Killing your thread should not result in the program crashing, but may leave
an orphaned process on the SQL server.

John

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.
> > John
> > "Mike Brown" <browna@.beer.com> wrote in message
> > news:ea197978.0406302059.3f4b8524@.posting.google.c om...
> > > Hello
> > > > I am calling a stored procedure in a MSDE/SQLServer DB form within my
> > > Visual C++ 6.0 program along the lines
> > > CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> > > With
> > > DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> > > It all works sweet as, but it can take a while and I want to let the
> > > user abort it.
> > > Everything I've tried ends in tears.|||John Bell (jbellnewsposts@.hotmail.com) writes:
> I am not sure what you mean by killing the server. Look up the KILL
> command in books online.

And Books Online says:

KILL permissions default to the members of the sysadmin and processadmin
fixed database roles, and are not transferable.

And Mike wants to give his users away to cancel their running commands.

And killing the entire connection would be a huge overkill anyway, when
all you want to do is to cancel the current batch.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Mike Brown (browna@.beer.com) writes:
> I am calling a stored procedure in a MSDE/SQLServer DB form within my
> Visual C++ 6.0 program along the lines
> CCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);
> With
> DEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))
> It all works sweet as, but it can take a while and I want to let the
> user abort it.
> Everything I've tried ends in tears.

You don't say much of what you have tried. Then again, I will have to
admit that I have no experience of OLE DB Consumer templates, although
I've recently started to program against SQLOLEDB.

But I can't see but that to do this, you need to use asynchrounous
execution. The MDAC Books Online says:

Consumers that want to asynchronously open a rowset set the
DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property.
When setting this bit prior to calling ICommand::Execute,
IOpenRowset::OpenRowset, IDBSchemaRowset::GetRowset,
IRowPosition::GetRowset, IColumnsRowset::GetColumnsRowset,
IMultipleResults::GetResult, ISourcesRowset::GetSourcesRowset, or any
other method that returns a rowset, riid must be set to
IID_IDBAsynchStatus, IID_IConnectionPointContainer, or IID_IUnknown.
...
To cancel creation of the rowset, the consumer can call
IDBAsynchStatus::Abort or can simply release all interfaces on the
rowset. Once the rowset's reference count goes to zero, any
asynchronous processing is canceled and the rowset is released. Calling
IDBAsynchStatus::Abort still requires releasing the interface.

If you don't do it asynchrounously... I guess you could start to
release things from another thread, but I'm not surprised if it ends
in tears...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John was referring to the T-SQL 'KILL' command, not the unix kill command.

"Mike Brown" <browna@.beer.com> wrote in message
news:ea197978.0407011050.4a44f3c5@.posting.google.c om...
> I have the command running in a separate thread.
> I dont want to kill the server, just the CALL. I have tried killing
> the thread and using .Abort(), and most other things I can think of,
> but everything results in my program crashing.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<AKPEc.694$t8.6278387@.news-text.cableinet.net>...
> > Hi
> > You can issue a KILL command on the SQL Server which will terminate the
> > process. To do this you are going to need a separate thread. More
> > information in books online.

Ability to update multiple tables simultaneously via stored proc

Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

EverettYou can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

Example:

CREATE PROCEDURE sp_ModifyMasterDetail
(
.
.
.
@.msg varchar(255) output
)
AS
SET NOCOUNT ON

DECLARE @.error int
, @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SELECT @.tfTran = 1
BEGIN TRAN
END
ELSE
SELECT @.tfTran = 0

UPDATE tblMaster
.
WHERE ID = @.ID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

UPDATE tblDetail
.
WHERE ID = @.ID
AND SubID = @.SubID

SELECT @.error = @.@.error

IF (@.error <> 0)
GOTO Error_Exit

--
-- Check to see if an error occured during processing. If so then
-- ROLLBACK else COMMIT transactions
--
Error_Exit:

IF (@.error <> 0) BEGIN
IF (@.tfTran = 1)
ROLLBACK TRAN

SELECT @.msg = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@.error),
END
ELSE BEGIN
IF (@.tfTran = 1)
COMMIT TRAN

SELECT @.msg = 'Transaction successful'
END

RETURN @.error
GO|||begin tran
update parent set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
update child set ...
if @.@.error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.|||Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

Everett|||You can create a temp table on the spid, populate it then access it in the SP.

Call the SP repeated times with the values and the SP can populate a table keyed on spid.

Call the sp with comma delimitted strings with the values.

Have lots of parameters - up to the max you think you will need.

Aaargh! Storedproc vs SQL in Gridview update

When I attempt to update using a stored procedure I get the error 'Incorrect syntax near sp_upd_Track_1'. The stored procedure looks like the following when modified in SQLServer:

ALTERPROCEDURE [dbo].[sp_upd_CDTrack_1]

(@.CDTrackNamenvarchar(50),

@.CDArtistKeysmallint,

@.CDTitleKeysmallint,

@.CDTrackKeysmallint)

AS

BEGIN

SETNOCOUNTON;

UPDATE [Demo1].[dbo].[CDTrack]

SET [CDTrack].[CDTrackName]= @.CDTrackName

WHERE [CDTrack].[CDArtistKey]= @.CDArtistKey

AND [CDTrack].[CDTitleKey]= @.CDTitleKey

AND [CDTrack].[CDTrackKey]= @.CDTrackKey

END

But when I use the following SQL coded in the gridview updatecommand it works:

"UPDATE [Demo1].[dbo].[CDTrack]

SET [CDTrack].[CDTrackName] = @.CDTrackName

WHERE [CDTrack].[CDArtistKey] = @.CDArtistKey

AND [CDTrack].[CDTitleKey] = @.CDTitleKey

AND [CDTrack].[CDTrackKey] = @.CDTrackKey"

Whats the difference? The storedproc executes ok in sql server and I guess that as the SQL version works all of my databinds are correct. Any ideas, thanks, James.

Not sure if it's the source of your problem, but shouldn't sp_upd_Track_1 and p_upd_CDTrack_1 be the same or was that a typo?

|||Yeah, typo, the correct name is specified in the storedproc and referenced correctly in the asp.

aaaarghh..errors..someone please help

Hi there,

I'm struggling with trapping errors, I've stripped down my stored proc to it's very minimum:

CREATE PROCEDURE MattTest AS

INSERT INTO tblTestr
SELECT
tblTestIn_Field1
FROM tblTestIn

IF @.@.ERROR <> 0
BEGIN
EXEC master..xp_sendmail 'matt.mcdonald@.aapct.scot.nhs.uk', 'Hello'
RAISERROR ('Matt',16,1) WITH LOG
END

GO

I've then deliberatley made a typo above called the table 'tblTestr' instead of 'tblTest'. I want the procedure to pick up the error and e-mail me and write it to the event log but I can't get it to work!!

Someone please help...pretty please...SQL Server has many different type of ways it handles errors...

Other RDBMS's would not let it compile...in this case sql does..

To check for that you need to check for the existance of the table BEFORE you access...then you can trap that type of error...

Sorry...just the way sql server is...|||Hi there,

I can't seem to trap any errors, I have a new test where I try to insert a char field from one table into an int field of another table.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE MattTest2 AS

BEGIN
INSERT INTO tblTest
SELECT tblTestIn_Field1
FROM tblTestIn

IF @.@.ERROR <> 0
PRINT 'TRANSACTION FAILED'
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

The first few records should work ok as they are ints anyway but then it changes to charcaters - e.g. 1,2,3,4,5,w,t,z SQL Server throws up an error when it reaches the first charcater i.e.w. I would like to trap this and write an error to the Error Log but the proc just ends and displays the following stanadard without displaying my custom message:

Server: Msg 245, Level 16, State 1, Procedure MattTest2, Line 7
Syntax error converting the varchar value 'W ' to a column of data type int.

So I must be doing something wrong as it definately sees it as an error??

Thanks
Matt|||Look up error handling in books online and reference the severity area...

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
CREATE TABLE myTable00(Col1 char(1))
GO

INSERT INTO myTable00(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT 'w' UNION ALL
SELECT 't' UNION ALL
SELECT 'z'
GO

CREATE PROC mySproc99
AS
DECLARE @.Error int, @.Rowcount int
INSERT INTO myTable99(Col1) SELECT Col1 FROM myTable00 --WHERE ISNUMERIC(Col1) = 1
SELECT @.Error = @.@.Error, @.Rowcount = @.@.ROWCOUNT
SELECT '@.Error = ' + CONVERT(varchar(5),@.Error) + ', @.Rowcount =' + CONVERT(varchar(5),@.@.ROWCOUNT)
GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

CREATE PROC mySproc99
AS
DECLARE @.Error int, @.Rowcount int
INSERT INTO myTable99(Col1) SELECT Col1 FROM myTable00 WHERE ISNUMERIC(Col1) = 1
SELECT @.Error = @.@.Error, @.Rowcount = @.@.ROWCOUNT
SELECT '@.Error = ' + CONVERT(varchar(5),@.Error) + ', @.Rowcount =' + CONVERT(varchar(5),@.@.ROWCOUNT)
GO

EXEC mySproc99
GO

DROP PROC mySproc99
DROP TABLE myTable00
DROP TABLE myTable99
GO|||In ms-sql the conversion from varchar to integer is done for you, ms-sql just makes the assumption it can be done. It won't matter if you use a cursor or any other type of insert. When sql does the insert it will try to enforce the conversion, which won't work in all cases. I'd check prior the insert and raise an error when necessary.|||Cheers Brett, I've been trying to get it to work like VB but it doesn't - I now know that you can only trap non-fatal errors in this manner...bit of a bummer really.|||...bit of a bummer really.

yup...btw do you have a farm?

and on this farm do have any cows?

and do they go moo moo here and a moo moo there?

Mr. Mist would be proud

Moo

read this article

http://www.sqlteam.com/item.asp?ItemID=2463|||Aye...like I haven't heard that one before...|||sure enough...but did you read the link?|||Yes thanks. It explains it all quite clearly...I know where I stand with SQL Server now.sql

a where case question

I need to change the criteria of the select so that if @.thstype is 1 then
where a =b else a<> b. This is in a stored proc...
given declare @.thsType as INT
set @.thsType = 1
select *
from t1,t2,t2,...
where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesYou were so close :-)
Use Northwind
DECLARE @.Test INT
SET @.test = 1
Select *
from customers where customerID =
(CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>I need to change the criteria of the select so that if @.thstype is 1 then
> where a =b else a<> b. This is in a stored proc...
> given declare @.thsType as INT
> set @.thsType = 1
> select *
> from t1,t2,t2,...
> where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||i'm not sure that will work. I really need something like this
Select *
from customers where
CASE WHEN @.Test = 1 THEN
customerID = 'ALFKI'
ELSE
customerID <> 'ALFKI'
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jens Sü?meyer" wrote:

> You were so close :-)
> Use Northwind
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers where customerID =
> (CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>
>|||Try this:
DECLARE @.Test INT
SET @.test = 1
Select *
from customers
where (@.Test = 1 and customerID = 'ALFKI') or
(@.Test <> 1 and customerID <> 'ALFKI' )
Perayu
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
> i'm not sure that will work. I really need something like this
> Select *
> from customers where
> CASE WHEN @.Test = 1 THEN
> customerID = 'ALFKI'
> ELSE
> customerID <> 'ALFKI'
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Jens Smeyer" wrote:
>|||well.........., yes you have an answer and thank you!
However, it turned my mega proc of .5 sec into a 6 second proc.
I can write an if else and have two queries in the proc, but i'd like to
avoide that if i could.
Query below, for what is does it's very fast the condition needs to be in
the joined query at the bottom: (i added the or)
select
s.csistkcsisym,
s.csistksym1,
s.csistkcompany,
case s.csistkExchange when 'OTC' THEN 'Nasdaq' Else s.csistkExchange END as
Exchange,
u.unvName,
isnull(r.unvname, '********') as Sector,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status,
case h2.stkhstBuySell WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstBuySell END as PFBuySell,
CASE
WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,
h2.stkhstXO,
CASE
WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFXO,
CASE h2.stkhstLine WHEN 'A' THEN 'Above' WHEN 'B' THEN 'Below' ELSE 'N/A'
END as Trend,
CASE
WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFtrend,
case h2.stkhstRSBS WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstRSBS END as RSBuySell,
CASE
WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
WHEN ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'rdBK'
WHEN ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'gnBK'
ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*50) +
(dbo.fn_rtnRsRStatus(r.i2idxhstRSBSXO)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) +
(dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/4+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as StockRate,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/2+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as ShortTermRate,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status
from
csistk s
join unvmem m on m.unvmemCsiId = s.csistkcsisym
join unv u on u.unvID = m.unvmemUnvID
join stkhst h2 on h2.stkhstcsisym = s.csistkcsisym
join stkhst h1 on h1.stkhstcsisym = s.csistkcsisym
left join
(select u.unvname,
u.unvid,
m.unvmemcsiid,
i1.idxhstStatus as i1idxhstStatus,
i2.idxhstStatus as i2idxhstStatus,
i2.idxhstPosChartPos as i2idxhstPosChartPos,
i2.idxhstRSBSXO as i2idxhstRSBSXO,
i2.idxhstRSXOStatus as i2idxhstRSXOStatus,
i2.idxhstRSXOPos as i2idxhstRSXOPos,
i2.idxhst10Status as i2idxhst10Status,
i2.idxhst10ChartPos as i2idxhst10ChartPos
from unv u
join unvmem m on m.unvmemunvid = u.unvid
join idxhst i1 on i1.idxhstidxid = u.unvID
join idxhst i2 on i2.idxhstidxid = u.unvID
where @.thsOne = 1
and u.unvid <> @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
or
@.thsOne<> 1
and u.unvid = @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
where u.unvid = @.thsUnvID
and h2.stkhstdate = @.thsDate2
and h1.stkhstdate = @.thsDate1
order by s.csistksym1
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Perayu" wrote:

> Try this:
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers
> where (@.Test = 1 and customerID = 'ALFKI') or
> (@.Test <> 1 and customerID <> 'ALFKI' )
> Perayu
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
>
>|||On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

>well.........., yes you have an answer and thank you!
>However, it turned my mega proc of .5 sec into a 6 second proc.
>I can write an if else and have two queries in the proc, but i'd like to
>avoide that if i could.
>Query below, for what is does it's very fast the condition needs to be in
>the joined query at the bottom: (i added the or)
(snip)
> where @.thsOne = 1
> and u.unvid <> @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> or
> @.thsOne<> 1
> and u.unvid = @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
Hi WebBuilder451,
While this and/or condition will work, it is not very maintainable and
not very efficient either. Please remember that many people do not know
the precedence of evaluation for and and or by head. Just adding
brackets would make this code easier to understand!
But the code below, while equivalent, also has a better chance of being
able to use indexes:
where u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
and ((@.thsOne = 1 and u.unvid <> @.thsUnvID)
or (@.thsOne <> 1 and u.unvid = @.thsUnvID))
) r on r.unvmemcsiid = s.csistkcsisym
If that doesn't solve your speed problem and speed is important for you,
than you'll have to duplicate your stored procedure to make two
versions: one for @.thsOne = 1 and one for @.thsOne <> 1. That will allow
SQL Server to create optimized execution plans for both situations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it??
Thx in Adv.
Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:

> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>sql

A View That Join 2 Tables in different Databases

Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>

a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
BalaHave a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
--
Regards
Barry McAuslin
----
--
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
--
David Gugick
Imceda Software
www.imceda.com

a typical proublem related with auto generated id

hi
I am really stuck up with this problem
here is my problems
I am inserting data in 3 tables in a stored procedure
I have a table A with a auto generated id let ID
and I have updated the table A with new record (with ID)
now I have to make use of this id in the corresponding update in table B & C
in the same stored procedure
now how I can get this ID avail for the tables B and C.
one solution is to use max of the ids generated but this doesnt going to
work in case of multiple updated like a lot of users are making the updates
on the database. I am using SQL Server 2000 as DB.
please suggest me any solution
Regards
Bala
Have a look at the @.@.IDENTITY and SCOPE_IDENTITY() functions in Books on
Line.
Regards
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"bala" <bala_at_web@.yahoo.com> wrote in message
news:u7cv2AP3EHA.1408@.TK2MSFTNGP10.phx.gbl...
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in table B &
C
> in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going to
> work in case of multiple updated like a lot of users are making the
updates
> on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
>
|||bala wrote:
> hi
> I am really stuck up with this problem
> here is my problems
> I am inserting data in 3 tables in a stored procedure
> I have a table A with a auto generated id let ID
> and I have updated the table A with new record (with ID)
> now I have to make use of this id in the corresponding update in
> table B & C in the same stored procedure
> now how I can get this ID avail for the tables B and C.
> one solution is to use max of the ids generated but this doesnt going
> to work in case of multiple updated like a lot of users are making
> the updates on the database. I am using SQL Server 2000 as DB.
> please suggest me any solution
> Regards
> Bala
Please don't multi-post. See my comments in the other NG.
David Gugick
Imceda Software
www.imceda.com

A trick query.

Hi all...

I would like to know how it is possible to make my problem below all in ONE
query or stored procedure.

I select some rows from a table where the resultset is one column with some
values. Lets say 1, 4 and 7:

row val
1 1
2 4
3 7

Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)

Lets say the other table looks like this:

id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'

in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in column
'b'.

Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in the
table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row shall
be deleted.

Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.

I hope that it is understandable and someone can help.

- rick -So ,basically, you want to have all values from the table a and matching
values (if they exist) from table b? How does this work for you:

select
Table1.val,
5 as CriteriaValue, --probably variable...
OtherTable.id,
OtherTable.Text
from
Table1
left join OtherTable on table1.val = OtherTable.a and OtherTable.b = 5

Now, if this query works, you can delete all values from OtherTable that
have a value b = 5 and id not in the select. You can insert the rows
returned by query if you add filter WHERE otherTable.ID is null.
If this doesnt work for you, please elaborate...

MC

"Rick" <rickcool22@.hotmail.comwrote in message
news:45d71f80$0$90276$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi all...
>
I would like to know how it is possible to make my problem below all in
ONE query or stored procedure.
>
I select some rows from a table where the resultset is one column with
some values. Lets say 1, 4 and 7:
>
row val
1 1
2 4
3 7
>
Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)
>
Lets say the other table looks like this:
>
id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'
>
in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in
column 'b'.
>
Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in
the table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row
shall be deleted.
>
Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.
>
I hope that it is understandable and someone can help.
>
- rick -
>

A transport-level error has occurred when receiving results from the server

Hi all,

I

am trying to run a stored procedure which retreives 3 lakhs of records

and updates the data and moves few of those records to some tables.

Since the number of records are more , the time taken for the storede

procedure is around 30 minutes when i directly execute it in query

analyzer.

But I need to execute it from Visula Studio.Net 2005

(c#). Whole of the application contains only one form with a single

button.When I click on the button , this stored procure has to be

executed. But I am getting an error as shown below.

"A

transport-level error has occurred when receiving results from the

server. (provider: TCP Provider, error: 0 - The specified network name

is no longer available.)"

Database used is SqlServer 2000

How to solve this error?. Any ideas are really appreciated.

Thanks and Regards,
Sukanya.

The error occured during data operation and the remote server either temporarily offline or close connection due to invalid client operation or your the permission your client to access the remote server resources has been changed, etc.

So, to identify the problem, you need :

1) Assume you were making remote connection, ping <remoteserver>, telnet <remoteserver> <sqlport>, or net view \\<remoteserver> or see firewall setting on the remote server to check whether the network is still good to make sure remote server is still reachable, and contact your network administrator to fix those problems.

2) You can give a retry by running your client app see whether the problem went away.

3) If 1) and 2) passed, you might open sql profile to nail down which client operation to cause sql server terminate connection, and check server errorlog or application event log find out any clue.

If you were making local connection, it is probably reason 3).

HTH.

Ming.

|||Hi,

I just increased the "connect timeout" in connectionstring of app.config to 1 hour. This solved my problem.

Thanks,
Sukanya

A tough nut to crack

I have a class C# which dynamically generates the SQL to create a stored procedure, one of the parameters i write into to the SQL is @.Keyname, and i use it in the following way

WHERE @.KeyName = CONVERT(VARCHAR, @.KeyValue)

However when i run the generated SQL to actually create the stored proc is is created with the line exactly as it is above. I need to be able to take the keyname as specified when the proc is called, which is a quoted string (i.e 'keynamefield' and write it into the SQL as keynamefield without quotes to make the field lookup dynamic based on this keyname field.

I am presuming that i will need to use function against the keyname variable inside the stored procedure (as this cannot be called from C#) something like GetValue(@.KeyName)

??

Any Ideas would be greatly appreciated

You need to use dynamic SQL but that is not what you want to do. You should not write applications that passes column names and table names dynamically for manipulation. There are lot of security risks, performance issues among other things. Best is to create the SP in such a manner that you don't need dynamic SQL. There are many ways to do this. The link below discusses the techniques to do something like this:

http://www.sommarskog.se/dyn-search.html

|||

As Umachandar said, its highly risk to create a object from your code. Your database is widly open to any one. Security Issues..

Comming to your issue, you have to use NVARCHAR instead of VARCHAR for unicode characters(non-english alphabets).

|||

Got it sussed thanks, the procs use the sql in a pre-generated fashion (i.e the compiler calculates the select and then it is added to a stored proc and which sql is executed is controlled by paremeters.) The procs are also encrypted so no one can execute any code they fancy on the database. I wonder though, can you encrypt tables the same way toy can procedures (i.e. WITH ENCRYPTION)?

Thanks

|||No. You can't encrypt the table definitions.

A suggestion that can help SQL Server community

I have noticed that the area of writing stored procedures for muti-user databases is a very specialised field and requires knowledge that's much more than the locking topics covered in 'online books' . I am sure there are some standard tips and tricks that are used in mutil-user databases for writing to tables.Most books have a chapter or two on locking, but I think this topic should be dealt withseparately in a dedicated book to locking with extensive examples on locking. Does anyone know of such a dedicated book out there?

The person who I think goes under SQL Server transaction is Dusan Petkovic, his books are by no means Beginner's books but Osborne gave them that title but your understanding of SQL Server transaction will improve after you read his chapter and do the questions at the end of the chapter.

He also covered ANSI SQL transaction features SQL Server implements but is not documented. Try the link below for his books I have not read the SQL Server 2005 version.

http://www.amazon.com/gp/product/007212587X/102-0765109-8072934?v=glance&n=283155

http://books.mcgraw-hill.com/getbook.php?isbn=0072260939&template

a stored procedures question or two.

My main problem is retrieving an output value. My stored procedure is:

.......................

USE [CyclingClub]
GO
/****** Object: StoredProcedure [dbo].[ValidateMemberUsrPwd] Script Date: 05/20/2007 14:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Poldie
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[ValidateMemberUsrPwd]
-- Add the parameters for the stored procedure here
@.username nvarchar(16) = NULL,
@.password nvarchar(16) = NULL,
@.memberid int output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @.memberid = member_id from members
where @.username = member_username
and @.password= member_password

END
.......................

and when I run it from the management studio app I get the results I expect. When I run it from within Visual Studio 2005 Pro Server Explorer, if I assign a valid value for @.username and @.password but leave @.memberid as <DEFAULT> in the Run Stored Procedure box I get the following output:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = <DEFAULT> ).

Procedure or function 'ValidateMemberUsrPwd' expects parameter '@.memberid', which was not supplied.
No rows affected.
(0 row(s) returned)
@.memberid =
@.RETURN_VALUE =
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is a little odd, as I wouldn't have thought the value of an output parameter would have mattered very much. But I can live with that, and if I try again and give a dummy value of 666 I get the following:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = 666 ).

No rows affected.
(0 row(s) returned)
@.memberid = 1
@.RETURN_VALUE = 0
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is better, as 1 is the correct value. But when I try and retrieve the output value in code I only get what I've assigned as the dummy value. My code is:

.......................
Dim cn As New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Dim cmd As New SqlCommand("ValidateMemberUsrPwd", cn)
cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@.username", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.password", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))

cmd.Parameters("@.memberid").Value = 666
cmd.Parameters("@.username").Value = sUsername
cmd.Parameters("@.password").Value = sPassword

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
.......................

In the Immediate window:

?cmd.Parameters("@.memberid").Value
666 {Integer}
Integer: 666 {Integer}

Any ideas what I'm doing wrong? Is it the whole way in which I'm trying to retrieve data? I know there are all sorts of datagrids and sets and readers etc but I'd like to do it this way initially. I tried using the return value initially and couldn't get that working - could that be for the same reason this isn't working?

Thanks in advance for even reading this far!

Hello my friend,

Working with output parameters is tedious. It would be better if you do not use the output parameter. Do not pass in @.MemberID. Declare it in the procedure, set it and then return it from the procedure like so: -

DECLARE @.MemberID AS INT

SET @.MemberID = (SELECT ...)

SELECT @.MemberID

Then instead of using ExecuteNonQuery(), use Object myID = ExecuteScalar() to return one value; which will be the @.MemberID. Then cast it to an integer if it is not null if you need to.

Kind regards

Scotty

|||

Hi poldie,

I think your code is perfectly fine just check these things

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

This should work..

Satya

|||

Thanks. That works, although I chose output type parameters because I'll later need to return a number of fields! I guess this is as good a time as any to learn a little more about this sort of thing!

|||

Thats good... mark the reply as answered if this helped ...

Satya

|||

satya_tanwar:

Thats good... mark the reply as answered if this helped ...

Does the bestest answerer get sweeties?

|||

No,

But its always good time to help anyone and save some time...

Satya

|||

satya_tanwar:

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

I was doing it after ExecuteNonQuery but before I closed the connection.

|||

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Using cn As SqlConnection = New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Using cmd As SqlCommand = New SqlCommand("dbo.ValidateMemberUsrPwd", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pUsername As New SqlParameter("@.username", SqlDbType.NVarChar, 16)
Dim pPassword As New SqlParameter("@.password", SqlDbType.NVarChar, 16)
Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)

pUsername.Value = sUsername
pPassword.Value = sPassword
pMemberid.Value = 666
pMemberId.ParameterDirection = ParameterDirection.Output

cmd.Parameters.Add( pUsername )
cmd.Parameters.Add( pPassword )
cmd.Parameters.Add( pMemberId )

cn.Open()
cmd.ExecuteNonQuery()

Response.Write( "MemberId: " + pMemberId.Value.ToString() )

'''
''' Output Parameters available now
'''
End Using
End Using

|||

davidpenton:

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Yes, it's not that though. I just tried your code - that works too. It's as if your parameters (pMemberId) are getting updated by the stored procedure, whereas I'm see the original, unchanged parameters that went into the stored procedure. Is it anything like strings, where if you change a string the old memory gets removed after being copied to the memory used by what will become the new string (which is why you should use Append and not just + to build strings)? Perhaps I'm looking at memory which has been marked for removal by the garbage collector later but which hasn't occurred yet?

Anyway, thanks - that's fixed it!

A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds
I've tried recompiling the procedure, as well as drop and create it again,
but it doesn't help.
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsFirst thing: Google for "Parameter sniffing", make sure you understand that
concept.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message news:OerL4M8ZGHA.3880@.TK2MSFTNGP04.phx
.gbl...
> Hi all
> I have a SP that beahves strange. Originally it takes about 20 millisecond
s
> to complete, but sometimes it starts going slow and take about 5-7 seconds
.
> When this happens, it keeps going slow.
> I tried to run the SQL body of the SP in the Query analyzer, and it runs
> fast (20 ms), while the SP takes 5-7 seconds
> I've tried recompiling the procedure, as well as drop and create it again,
> but it doesn't help.
> Does anyone knows what can cause this and what is the soloution ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>|||Could be parameter sniffing, can you show the code?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi Denis
Disabling parameter sniffing seems to work here. If it is still too slow
I'll send the code (which a bit messy). If not, I wouldn't waist your time.
Thanks
Boaz Be-Porat
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145899699.966141.109220@.y43g2000cwc.googlegroups.com...
> Could be parameter sniffing, can you show the code?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?
Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it
,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than b
y
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

a stored procedure in C# creates a file (the file cant be deleted)

hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor

A stored procedure

Hello!
Could anyone help me with this stored procedure...!?
Table Cars:
-Id
-Model
-Make
-Year
...
Table PriceList
-Id
-CarId
-Price
....
I would like to select all fields from "Cars" and only MIN(Price) from
"PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
result set.
Thanks!
James
Hello,
Try with
SELECT id, Model, Make, Year, (SELECT MIN(Price) FROM PriceList WHERE CarId
= c.Id)
FROM Cars c
Regards,
Tomislav Kralj
"James T." <gimenei@.hotmail.com> wrote in message
news:uSyVIOUKFHA.572@.tk2msftngp13.phx.gbl...
> Hello!
> Could anyone help me with this stored procedure...!?
> Table Cars:
> -Id
> -Model
> -Make
> -Year
> ...
> Table PriceList
> -Id
> -CarId
> -Price
> ...
> I would like to select all fields from "Cars" and only MIN(Price) from
> "PriceList" WHERE Cars.Id = PriceList.CarId. and join them into a single
> result set.
> Thanks!
> James
>