Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
Vinoth
It really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>
|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ? So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegr oups.com...
>
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards :)
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>> Hi,
>> I have Instead of Delete Trigger in my Table. When i delete any row
>> from the table it shows the No of Rows affected ex:- 1 Row affected
>> But when i look into the table the Deleted Row is still Present.
>> My Question is If there is Instead of Delete Trigger then is it not
>> possible to delete row from Original table. and if i want to delete the
>> row using Instead of trigger what i have to do?
>> If anybody knows the solution Please let me know?
>>
>> Thanks,
>> Vinoth
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 711 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dksql

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothHi ,
You cannot delete rows from a table which has a instead of delete trigger
configured.
Though, We can put delete statement for the table inside instead of trigger
but we need to keep RECURSIVE_TRIGGERS database option set correctly. This D
B
option will not cause trigger to fire again.
But still I am not sure why do you want to delete row from a trigger which
has instead of delete trigger configured.
--
Vishal Khajuria
9886170165
IBM Bangalore
"vinoth@.gsdindia.com" wrote:

> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>

Problem in Instead of Delete Trigger

Hi,
I have Instead of Delete Trigger in my Table. When i delete any row
from the table it shows the No of Rows affected ex:- 1 Row affected
But when i look into the table the Deleted Row is still Present.
My Question is If there is Instead of Delete Trigger then is it not
possible to delete row from Original table. and if i want to delete the
row using Instead of trigger what i have to do?
If anybody knows the solution Please let me know?
Thanks,
VinothIt really depends on what are you doing in the instead of delete trigger. Do
you actually delete the row in the trigger? Please post sample code from the
trigger if you need more help (desired result and sample data/table would be
helpful...)
MC
<vinoth@.gsdindia.com> wrote in message
news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
> Hi,
> I have Instead of Delete Trigger in my Table. When i delete any row
> from the table it shows the No of Rows affected ex:- 1 Row affected
> But when i look into the table the Deleted Row is still Present.
> My Question is If there is Instead of Delete Trigger then is it not
> possible to delete row from Original table. and if i want to delete the
> row using Instead of trigger what i have to do?
> If anybody knows the solution Please let me know?
>
> Thanks,
> Vinoth
>|||Hi,
if I understand you correct you're deleteing a record and your trigger is
firing reporting the number of rows affected ' So far so good. If this
was
a normal "After" trigger the record would be deleted in the table. An
instead of trigger is different. The delete actually take place in the
table. You have to do that by yourself.
So in your trigger code you could do something like this :
Delete from table where id=(Select id from deleted)
But why are you using an instead of trigger and not an after trigger ?
Normally you use instead of triggers when you want to handle the dml
action
yourself for instance in partitioned views.
Regards
Bobby Henningsen
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:eHrFsqC7FHA.476@.TK2MSFTNGP15.phx.gbl...
> It really depends on what are you doing in the instead of delete
trigger.
> Do you actually delete the row in the trigger? Please post sample code
> from the trigger if you need more help (desired result and sample
> data/table would be helpful...)
>
> MC
>
> <vinoth@.gsdindia.com> wrote in message
> news:1132308519.761801.276040@.g49g2000cwa.googlegroups.com...
>
---
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 711 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Problem in deleting rows in table

hi there!

I have encounter the problem when trying to delete the rows..with the error message

http://i5.photobucket.com/albums/y151/kangalert/untitled.jpg

http://i5.photobucket.com/albums/y151/kangalert/1231234.jpg

with contain no primary key, and no dependency

What task/component are you using to do the deletion?

Please provide as much information as you can, in a concise manner, when posting issues.

-Jamie

|||

i just click on the row/rows i want to delete in SQL Server..

But it wont let me update and delete the row!

|||

Is this at all related to SSIS?

If not then you are in the wrong forum. Try the Tools or T-SQL forum.

-Jamie

sql

Problem executing stored procedure to update all rows of a table

Hi,
I got a stored procedure which interacts with the pubs database. The
following is the code for the stored procedure:
CREATE PROCEDURE usp_UpdatedPrices_para
@.Type char(12)= '%',
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type
Now, if I execute the above stored procedure(from QA) in the following manne
r:
exec usp_UpdatedPrices_para 'Business' , 1
all four rows of the price field of titles table corresponding to the
'Business' type gets updated.
Now I need to execute the stored procedure so that all the rows get the
updates for all the various types. Since I have a default value for the type
as '%', I am
executing the stored procedure as
exec usp_UpdatedPrices_para , 1
to which I am getting a syntax error as the following:
Line 1: Incorrect syntax near ','
If anybody could suggest me something about the error, it would be helpful.
Thanks.Hello, Jack
You can call the procedure like this:
EXEC usp_UpdatedPrices_para DEFAULT, 1
or:
EXEC usp_UpdatedPrices_para @.Percent=1
However, this will not get you the expected result, because your
condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
the default for the @.Type parameter (leave it to be NULL) and use a
condition like this:
WHERE Type=@.Type OR @.Type IS NULL
Razvan

Problem executing stored procedure to update all rows of a tab

Thanks Razvan for your help. As per your advise I have changed the stored
procedure as follows:
CREATE PROCEDURE usp_UpdatedPrices_para1
@.Type char(12),
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
Now I am trying to execute the stored procedure as:
exec usp_UpdatedPrices_para1 @.percent = 10
To the above command I am getting the following error message:
Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was not
supplied.
Do you have any further ideas for resolution. Thanks.
"Razvan Socol" wrote:

> Hello, Jack
> You can call the procedure like this:
> EXEC usp_UpdatedPrices_para DEFAULT, 1
> or:
> EXEC usp_UpdatedPrices_para @.Percent=1
> However, this will not get you the expected result, because your
> condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
> the default for the @.Type parameter (leave it to be NULL) and use a
> condition like this:
> WHERE Type=@.Type OR @.Type IS NULL
> Razvan
>When passing parameters to a stored procedure using the @.variable=value
syntax, you must explicity set a valure for all input parameters that don't
have a default value assigned in the procedure defenition; even if you want
it to be NULL.
Try
EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
Alternatively, you could define the procedure to use a default of NULL for
the variable @.Type
CREATE PROCEDURE usp_UpdatedPrices_para2
@.Type char(12)=NULL,
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
And then execute as
EXEC sp_UpdatedPrices_para2 @.Percent=1
"Jack" wrote:
> Thanks Razvan for your help. As per your advise I have changed the stored
> procedure as follows:
> CREATE PROCEDURE usp_UpdatedPrices_para1
> @.Type char(12),
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> Now I am trying to execute the stored procedure as:
> exec usp_UpdatedPrices_para1 @.percent = 10
> To the above command I am getting the following error message:
> Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was n
ot
> supplied.
> Do you have any further ideas for resolution. Thanks.
> "Razvan Socol" wrote:
>|||Thanks a lot Mark for your generous help. I tried executing both the
procedures and both worked great. Best Regards.
"Mark Williams" wrote:
> When passing parameters to a stored procedure using the @.variable=value
> syntax, you must explicity set a valure for all input parameters that don'
t
> have a default value assigned in the procedure defenition; even if you wan
t
> it to be NULL.
> Try
> EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
> Alternatively, you could define the procedure to use a default of NULL for
> the variable @.Type
> CREATE PROCEDURE usp_UpdatedPrices_para2
> @.Type char(12)=NULL,
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> And then execute as
> EXEC sp_UpdatedPrices_para2 @.Percent=1
>
> "Jack" wrote:
>

Problem executing a stored proc, please help

Hi All,
I have stored proc that processes about 60,000 rows using a cursor. When I
call the SP from Query Analyzer, I get the following error message after
processing about 12,000 records :
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
12614 records
What can i do to make this SP run sucessfully ? I even tried using a table
variable instead of a cursor, but got the same result. Please suggest .
THE output from SP_CONFIG on database server is
Option
config_value
------
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 0
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0
Hi
It would help if you posted DDL and example data such as
http://www.aspfaq.com/etiquettXXe.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.XXhtm#inserts
Connection broken implies a network failure/disconnection, possibly a
timeout but you do not indicate how long this process takes.
Check your SQL server version and service pack level, you may also want to
check MDAC version and consistancy along with the SQL Server log and event
log to see if there is any more information that may help.
Other posts on this http://tinyurl.com/4ebus may be helpful.
John
"rajeshlh" <rajeshlh@.discussions.microsoft.com> wrote in message
news:F1F399D6-BB17-4FC9-BF10-4F7D3A31F7B1@.microsoft.com...
> Hi All,
> I have stored proc that processes about 60,000 rows using a cursor. When I
> call the SP from Query Analyzer, I get the following error message after
> processing about 12,000 records :
>
> Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
>
> Connection Broken
>
>
> 12614 records
>
> What can i do to make this SP run sucessfully ? I even tried using a table
> variable instead of a cursor, but got the same result. Please suggest .
>
> THE output from SP_CONFIG on database server is
> Option
> config_value
> ------
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 0
> scan for startup procs 0
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
>
>

Problem executing a stored proc, please help

Hi All,
I have stored proc that processes about 60,000 rows using a cursor. When I
call the SP from Query Analyzer, I get the following error message after
processing about 12,000 records :
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
12614 records
What can i do to make this SP run sucessfully ? I even tried using a table
variable instead of a cursor, but got the same result. Please suggest .
THE output from SP_CONFIG on database server is
Option
config_value
------
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 0
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0Hi
It would help if you posted DDL and example data such as
http://www.aspfaq.com/etiquett­­e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.­­htm#inserts
Connection broken implies a network failure/disconnection, possibly a
timeout but you do not indicate how long this process takes.
Check your SQL server version and service pack level, you may also want to
check MDAC version and consistancy along with the SQL Server log and event
log to see if there is any more information that may help.
Other posts on this http://tinyurl.com/4ebus may be helpful.
John
"rajeshlh" <rajeshlh@.discussions.microsoft.com> wrote in message
news:F1F399D6-BB17-4FC9-BF10-4F7D3A31F7B1@.microsoft.com...
> Hi All,
> I have stored proc that processes about 60,000 rows using a cursor. When I
> call the SP from Query Analyzer, I get the following error message after
> processing about 12,000 records :
>
> Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
>
> Connection Broken
>
>
> 12614 records
>
> What can i do to make this SP run sucessfully ? I even tried using a table
> variable instead of a cursor, but got the same result. Please suggest .
>
> THE output from SP_CONFIG on database server is
> Option
> config_value
> ------
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 0
> scan for startup procs 0
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
>
>

Problem executing a stored proc, please help

Hi All,
I have stored proc that processes about 60,000 rows using a cursor. When I
call the SP from Query Analyzer, I get the following error message after
processing about 12,000 records :
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidP
aram()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
12614 records
What can i do to make this SP run sucessfully ? I even tried using a table
variable instead of a cursor, but got the same result. Please suggest .
THE output from SP_CONFIG on database server is
Option
config_value
----
---
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 0
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0Hi
It would help if you posted DDL and example data such as
http://www.aspfaq.com/etiquett__e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.__htm#inserts
Connection broken implies a network failure/disconnection, possibly a
timeout but you do not indicate how long this process takes.
Check your SQL server version and service pack level, you may also want to
check MDAC version and consistancy along with the SQL Server log and event
log to see if there is any more information that may help.
Other posts on this http://tinyurl.com/4ebus may be helpful.
John
"rajeshlh" <rajeshlh@.discussions.microsoft.com> wrote in message
news:F1F399D6-BB17-4FC9-BF10-4F7D3A31F7B1@.microsoft.com...
> Hi All,
> I have stored proc that processes about 60,000 rows using a cursor. When I
> call the SP from Query Analyzer, I get the following error message after
> processing about 12,000 records :
>
> Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
>
> Connection Broken
>
>
> 12614 records
>
> What can i do to make this SP run sucessfully ? I even tried using a table
> variable instead of a cursor, but got the same result. Please suggest .
>
> THE output from SP_CONFIG on database server is
> Option
> config_value
> ----
---
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 0
> scan for startup procs 0
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
>
>

Problem executing a stored proc

Hi All,

I have stored proc that processes about 60,000 rows using a cursor. When I call the SP from Query Analyzer, I get the following error message after processing about 12,000 records :

Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).

Server: Msg 11, Level 16, State 1, Line 0

General network error. Check your network documentation.

ODBC: Msg 0, Level 16, State 1

Communication link failure

Connection Broken

12614 records

What can i do to make this SP run sucessfully ? I even tried using a table variable instead of a cursor, but got the same result.

THE output from SP_CONFIG is

Option config_value

---------------------------------------

affinity mask	0
allow updates	0
awe enabled	0
c2 audit mode	0
cost threshold for parallelism	5
Cross DB Ownership Chaining	0
cursor threshold	-1
default full-text language	1033
default language	0
fill factor (%)	0
index create memory (KB)	0
lightweight pooling	0
locks	0
max degree of parallelism	0
max server memory (MB)	2147483647
max text repl size (B)	65536
max worker threads	255
media retention	0
min memory per query (KB)	1024
min server memory (MB)	0
nested triggers	1
network packet size (B)	4096
open objects	0
priority boost	0
query governor cost limit	0
query wait (s)	-1
recovery interval (min)	0
remote access	1
remote login timeout (s)	20
remote proc trans	0
remote query timeout (s)	0
scan for startup procs	0
set working set size	0
show advanced options	1
two digit year cutoff	2049
user connections	0
user options	0

Try rewriting the sproc, only without the cursor...

Problem Deleting Rows

I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
WaynePlease post the full DDL for your table, as well as the DELETE statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e6dSRMHPGHA.3984@.TK2MSFTNGP14.phx.gbl...
I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
Wayne|||> What is it trying to tell me here?
Two things. First, EM is not a good editing tool. Second, a table without
a primary key is, by definition, not a table.|||Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>|||You can add an identity column to the target table and add a primary key
constraint on it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ORjDEQIPGHA.812@.TK2MSFTNGP10.phx.gbl...
Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>

problem deleting large no. of records

Hi all,
I have a table with 6 million rows which takes up about 2GB of memory on
hard disk. So we have decided to clean this table up. We have decided to
delete all records that have syncstamp and logstamp field values less than
the value correspoing '20040131'. This will probably delete 5.5 million rows
out of total 6 million.
When I try to delete records using following script, it is very slow. The
script did not finish executing in three hours. So we had to cancel the
execution of the script. Also the users were not able to use conttlog table
when this query was executing although I am using ROWLOCK table hint.
Is there any other way to fix the speed and concurrency issues with this
script? I know I can't use a loop to delete 5.5 million rows because it will
probably take days to execute it.
Thanks in advance.
-- ****************************************
*******
-- Variable declaration
-- ****************************************
*******
DECLARE @.Date datetime,
@.syncstamp varchar(7)
-- ****************************************
*******
-- Assign variable values
-- ****************************************
*******
SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
-- ****************************************
*******
-- Delete conttlog records
-- ****************************************
*******
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
DELETE
FROM conttlog with(rowlock)
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstampsql
Do you have Primary key on the table?
I'd try to divide the 'big' transaction/deletion into small ones
See this example
SET ROWCOUNT 1000 --Set the value
WHILE 1 = 1
BEGIN
UPDATE MyTable WHERE col <= datetimecolumn
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory
on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
rows
> out of total 6 million.
> When I try to delete records using following script, it is very slow.
The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
table
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with
this
> script? I know I can't use a loop to delete 5.5 million rows because it
will
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||I would recommend doing this in smaller batches -- maybe 10,000 rows at
once:
-- ****************************************
*******
-- Variable declaration
-- ****************************************
*******
DECLARE @.Date datetime,
@.syncstamp varchar(7)
-- ****************************************
*******
-- Assign variable values
-- ****************************************
*******
SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
-- ****************************************
*******
-- Delete conttlog records
-- ****************************************
*******
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
SET ROWCOUNT 10000
DELETE
FROM conttlog
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE
FROM conttlog
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory
on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
rows
> out of total 6 million.
> When I try to delete records using following script, it is very slow.
The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
table
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with
this
> script? I know I can't use a loop to delete 5.5 million rows because it
will
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||Hi
Do it in smaller batches. This will help with performance.
SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
SET ROWCOUNT 10000
DELETE
FROM conttlog with(rowlock)
WHERE syncstamp < @.syncstamp
AND logstamp < @.syncstamp
Regards
Mike
"sql" wrote:

> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory o
n
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million ro
ws
> out of total 6 million.
> When I try to delete records using following script, it is very slow. Th
e
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog tabl
e
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with th
is
> script? I know I can't use a loop to delete 5.5 million rows because it wi
ll
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>
>|||Uri,
One comment on this; I would personally be wary of doing that many
checkpoints during the process, as I believe that it would bring overall
system performance down quite a bit due to the constant disk activity. Is
there a reason you'd recommend doing a checkpoint on every iteration?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OvmficbGFHA.3608@.TK2MSFTNGP14.phx.gbl...
> sql
> Do you have Primary key on the table?
> I'd try to divide the 'big' transaction/deletion into small ones
> See this example
> SET ROWCOUNT 1000 --Set the value
> WHILE 1 = 1
> BEGIN
> UPDATE MyTable WHERE col <= datetimecolumn
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
>|||Thank you all for your answers. I will set the ROWCOUNT at the beginning of
the script to either 1000 or 10000. Do you think it is worth creating a
clusterd index on SYNCSTAMP and LOGSTAMP to improve the performance of this
script and then drop it. If so how long do you think it will take to create
such an index. both fields are varchar(7).
Thanks.
"sql" <donotspam@.nospaml.com> wrote in message
news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory on
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million
> rows out of total 6 million.
> When I try to delete records using following script, it is very slow. The
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog
> table when this query was executing although I am using ROWLOCK table
> hint.
> Is there any other way to fix the speed and concurrency issues with
> this script? I know I can't use a loop to delete 5.5 million rows because
> it will probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>|||Hi
Creating a clustetred index will cause the whole table to be re-written.
This will take longer to do than running the delete.
Regards
Mike
"sql" wrote:

> Thank you all for your answers. I will set the ROWCOUNT at the beginning o
f
> the script to either 1000 or 10000. Do you think it is worth creating a
> clusterd index on SYNCSTAMP and LOGSTAMP to improve the performance of thi
s
> script and then drop it. If so how long do you think it will take to creat
e
> such an index. both fields are varchar(7).
> Thanks.
> "sql" <donotspam@.nospaml.com> wrote in message
> news:uUV2eXbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi
My Sfr 0.02
It is in an implicit transaction, so until the whole batch completes,
nothing is committed.
Regards
Mike
"Adam Machanic" wrote:

> Uri,
> One comment on this; I would personally be wary of doing that many
> checkpoints during the process, as I believe that it would bring overall
> system performance down quite a bit due to the constant disk activity. Is
> there a reason you'd recommend doing a checkpoint on every iteration?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OvmficbGFHA.3608@.TK2MSFTNGP14.phx.gbl...
>
>|||sql wrote:
> Thank you all for your answers. I will set the ROWCOUNT at the
> beginning of the script to either 1000 or 10000. Do you think it is
> worth creating a clusterd index on SYNCSTAMP and LOGSTAMP to improve
> the performance of this script and then drop it. If so how long do
> you think it will take to create such an index. both fields are
> varchar(7).
If you do this, understand that SQL Server will have to rebuild all
non-clustered indexes. So do it off-hours, if at all. I would recommend
you not mess with the indexes since you are working on live data and
use the small batch size.
Is there any way you can do all this testing on a dev/test server?
David Gugick
Imceda Software
www.imceda.com|||What I've done in the past is stuff the records I wish to retain into a new
table and do a rename. The table will need to be offline during this
operation, simply run a test to see how long it takes to populate the new
table with the half million rows. The rename takes less than a second. If yo
u
try this technique I'd recommend doing a create table instead of a select
into so you can double check the referential integrity, default values,
index(s) and all else.
Dan
"sql" wrote:

> Hi all,
> I have a table with 6 million rows which takes up about 2GB of memory o
n
> hard disk. So we have decided to clean this table up. We have decided to
> delete all records that have syncstamp and logstamp field values less than
> the value correspoing '20040131'. This will probably delete 5.5 million ro
ws
> out of total 6 million.
> When I try to delete records using following script, it is very slow. Th
e
> script did not finish executing in three hours. So we had to cancel the
> execution of the script. Also the users were not able to use conttlog tabl
e
> when this query was executing although I am using ROWLOCK table hint.
> Is there any other way to fix the speed and concurrency issues with th
is
> script? I know I can't use a loop to delete 5.5 million rows because it wi
ll
> probably take days to execute it.
> Thanks in advance.
> -- ****************************************
*******
> -- Variable declaration
> -- ****************************************
*******
> DECLARE @.Date datetime,
> @.syncstamp varchar(7)
> -- ****************************************
*******
> -- Assign variable values
> -- ****************************************
*******
> SET @.Date = '20040131' -- yyyymmdd -> purge logs upto this date
> -- ****************************************
*******
> -- Delete conttlog records
> -- ****************************************
*******
> SET @.syncstamp = dbo.WF_GetSyncStamp(@.Date)
> DELETE
> FROM conttlog with(rowlock)
> WHERE syncstamp < @.syncstamp
> AND logstamp < @.syncstamp
>
>

Problem Deleting Duplicate Data

I have a table that contains more than 10,000 rows of

duplicate data. The script below copies the data to a temp table then

deletes from the original table. My problem is that after it runs, I now

have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the

triplicate data and returns 0 rows. I've use three different versions of

delete dup row scripts with the same result. There are no triggers or

constraints on the table, not even a primary key. What am I missing?

-

/**********************************************
Delete Duplicate Data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
[student_test_uniq] [bigint] NULL,
[test_uniq] [int] NULL,
[concept_id] [smallint] NULL,
[test_id] [varchar](12) NULL,
[questions_correct] [smallint] NULL,
[questions_count] [smallint] NULL,
[percentage_correct] [decimal](6, 3) NULL,
[concept_response_count] [smallint] NULL
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @.@.ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM crt_concept_score
FROM crt_concept_score
INNER JOIN #tempduplicatedata
ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq
AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq
AND crt_concept_score.concept_id = #tempduplicatedata.concept_id
AND crt_concept_score.test_id = #tempduplicatedata.test_id
AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct
AND crt_concept_score.questions_count = #tempduplicatedata.questions_count
AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct
AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count

--Insert the delete data back
INSERT INTO crt_concept_score
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Check table
-- SELECT * FROM crt_concept_score

--Drop temp table
DROP TABLE #tempduplicatedata
GO

i've seen something like this in one of Kat's post

here's the link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=746636&SiteID=1

problem creating Self Join query - is this the correct approac

Thank you for your response. Actually, your suggestion was the original
subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use Max(subDate
)
from the main table but I had to add a Group By Clause and include all of th
e
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
RichLooks like you're on the right. Might I suggest a cup of caffeine? Works
for me. ;-)
Just a note of caution about your query. It will give the max(subDate),
even if that subDate does not correspond to a row that has a mainDate =
2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
2006-01-25 and then s out the max (subDate) corresponding to those rows.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
Thank you for your response. Actually, your suggestion was the original
subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use
Max(subDate)
from the main table but I had to add a Group By Clause and include all of
the
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
Rich|||Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low fo
r
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:

> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s out the max (subDate) corresponding to those rows
.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each rec
ID
> has to be unique and has to be the Max(maindate) for a given date and has
to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all mess
ed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional field
s
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully, thi
s
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
month. I used to stay away from coffee on the same day as my medical. Now,
I don't. I've been getting very good BP and resting pulse rates in spite of
it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low
for
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:

> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s out the max (subDate) corresponding to those
> rows.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
> to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each
> recID
> has to be unique and has to be the Max(maindate) for a given date and has
> to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all
> messed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional
> fields
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully,
> this
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||This reply is in lieu of the caffeine :). I did the whole route in aviation
,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for D
B
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP. S
o
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:

> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical. No
w,
> I don't. I've been getting very good BP and resting pulse rates in spite
of
> it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation pilot
)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I started life as a scientist. One of my first discoveries was that there's
no money in scientific research, so I made a "lateral" move to IT. I had a
lot of computing background while an undergrad and as a grad. It's amazing
how many people are in IT that didn't start there.
Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
I'm inclined. ;-)
I hear ya about the data. I had an assignment with a police database. They
delete nothing - they just buy a bigger server!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
This reply is in lieu of the caffeine :). I did the whole route in
aviation,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for
DB
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP.
So
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:

> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical.
> Now,
> I don't. I've been getting very good BP and resting pulse rates in spite
> of
> it.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation
> pilot)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l thin
k
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid, bu
t
no go. So I am tearing apart the DB on a Dev server and finding a variety o
f
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:

> I started life as a scientist. One of my first discoveries was that there
's
> no money in scientific research, so I made a "lateral" move to IT. I had
a
> lot of computing background while an undergrad and as a grad. It's amazi
ng
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database. Th
ey
> delete nothing - they just buy a bigger server!
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much headwa
y
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91 flying
.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last bun
ch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean. Nothi
ng
> gets deleted, just updated, added, and moved around to various tables. Th
e
> architecture is OK, normalizatin OK (data gets replicated - that's OK). B
ut
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>|||I hear ya. Haven't seen the movie, but a scientist with a nice house and
big grant doesn't sound very credible. My cousin took a voluntary pay cut
so he could get lab equipment. Then, he moved from Canada to Wisconsin and
things improved, though I wouldn't call it lucrative.
Good luck on the data cleansing!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:02B08CB1-0C9F-4674-8539-A3A5DD3B076B@.microsoft.com...
I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l
think
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid,
but
no go. So I am tearing apart the DB on a Dev server and finding a variety
of
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:

> I started life as a scientist. One of my first discoveries was that
> there's
> no money in scientific research, so I made a "lateral" move to IT. I had
> a
> lot of computing background while an undergrad and as a grad. It's
> amazing
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database.
> They
> delete nothing - they just buy a bigger server!
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much
> headway
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91
> flying.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last
> bunch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean.
> Nothing
> gets deleted, just updated, added, and moved around to various tables.
> The
> architecture is OK, normalizatin OK (data gets replicated - that's OK).
> But
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>

Problem converting rows to a string

I try to accomplish the following:

I have two tables which are connected via a third table (N:N
relationship):

Table 1 "Locations"
LocationID (Primary Key)

Table 2 "Specialists"
SpecialistID (Primary Key)
Name (varchar)

Table 3 "SpecialistLocations"
SpecialistID (Foreign Key)
LocationID (Foreign Key)
(both together are the primary key for this table)

Issuing the following command

SELECT
L.LocationID , S.[Name]
FROM
Locations AS L
LEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationID
LEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistID

results in the following table:

LocationID | Name
1Specialist 1
1Specialist 2
2Specialist 3
2Specialist 4
3Specialist 1
4Specialist 4

Now my problem: I would like to have the following output:

LocationID | Names
1Specialist 1, Specialist 2
2Specialist 3, Specialist 4
3Specialist 1
4Specialist 4

...which is grouping by LocationID and concatenating the specialist
names.
Any idea on how to do this?

Thank you very much,
Dennis(dnsstaiger@.gmx.net) writes:
> Now my problem: I would like to have the following output:
> LocationID | Names
> 1 Specialist 1, Specialist 2
> 2 Specialist 3, Specialist 4
> 3 Specialist 1
> 4 Specialist 4
> ...which is grouping by LocationID and concatenating the specialist
> names.
> Any idea on how to do this?

This is one of the rare cases where you need to set up a cursor and
iterate. In SQL 2000 there is no defined way to do this. (There is
a shortcut, but it relies on undefined behaviour, so I don't recommend it.)

In SQL 2005, currently in beta, the story is different. There you
actually have a way to this in a set-based statement, although the
syntax is somewhat bewildering. (It's actually a by-product, of all
the XML stuff they thrown in.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I typically do things like this in my application code, i.e. while the
locationid is the same, keep tacking values onto the other column's
display in a comma delim format.|||pb648174 (google@.webpaul.net) writes:
> I typically do things like this in my application code, i.e. while the
> locationid is the same, keep tacking values onto the other column's
> display in a comma delim format.

Yes, that is also a very common advice. But people insists on asking
about how doing this in SQL, that I've given up telling them to use
application code. (And sometimes the application is not any more
sophisticated than Query Analyzer.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Can you post the SQL 2005 code that will do this? I've been hoping SQL
2005 would have an aggregate function for strings that would turn it
into a delimited string.|||pb648174 (google@.webpaul.net) writes:
> Can you post the SQL 2005 code that will do this? I've been hoping SQL
> 2005 would have an aggregate function for strings that would turn it
> into a delimited string.

Sure, here it is:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

This gives you an output like:

ALFKI 10643,10692,10702,10835,10952,11011
ANATR 10308,10625,10759,10926
ANTON 10365,10507,10535,10573,10677,10682,10856

Now, I did definitely come with this on my own, but I got it from one
of the SQL Server developers.

The part that produces the comma separated list, is the text() function,
which is activated by the XML PATH('') at the bottom. The real point
of text() is probably not to produce a comma separated list, but it's
possible to do it.

Then then comma-separated list is combined with Customers through
CROSS APPLY. APPLY is another operator I have not fully digested
yet, but you use it when you want to call a table-valued functions
with parameters from other columns in the query; something you can't
do in SQL 2000.

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

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