Cursor or Several Stored Procs

Would I be better served (ie faster execution & general database sexiness)
using the dreaded, hated, and loathsome cursor or to attempt to write a
series of stored procedures (6-12) to work with result sets of matching
criteria?
I am importing a large number of csv's to update existing information &
execute stored proc's aimed at updating / calculating an individual's data
based with multiple conditions.Hi
It depends on what you are doing! Usually it is better (and quicker) to use
a set based solution. If your code has a significant amount of branching
calling separate procedures may be an advantage.
HTH
John
"Clamps" wrote:

> Would I be better served (ie faster execution & general database sexiness)
> using the dreaded, hated, and loathsome cursor or to attempt to write a
> series of stored procedures (6-12) to work with result sets of matching
> criteria?
> I am importing a large number of csv's to update existing information &
> execute stored proc's aimed at updating / calculating an individual's data
> based with multiple conditions.
>
>sql

Cursor Faster in a TEST Server than on a PROD Server

Hi,
I'm running a cursor (I know not quit good...but). For I don't know why, in one night the execution time step from 1h30 to 3h. I restore my database on a TEST Server (same version), the the execution time is still 1h30...
I monitored both execution, and realise that the CPU time for the CXPACKET process is 4 time higher it the PRODUCTION server than on the TEST Server....
Do you think that can be the problem?
From Neil Pike's FAQ
Q. What does a wait type of CXPacket or Exchange mean?
A. You will get this only with versions of SQL that support parallel queries, i.e. SQL 7 and later. It means that one thread of the query is waiting for a message packet from another, and the one it is waiting on is either blocked by a traditional
cause or has hit some sort of parallelism bug.
CXPacket means it is waiting on a data packet - i.e. the results of an internal query is being passed. Exchange means that it is waiting on a control packet - i.e. waiting for a child/sibling process to tell you that it is finished.
If the query doesn't complete then make sure the latest service pack is applied as there are several parallel query fixes in each one. If it still doesn't fix it then you have run into an unfixed bug and will need to contact Microsoft PSS and raise a bug
report. You should be able to work-around the problem by adding (MAXDOP=1) as a query hint which will prevent the query being parallelized.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Cursor Faster in a TEST Server than on a PROD Server

Hi,
I'm running a cursor (I know not quit good...but). For I don't know why, in
one night the execution time step from 1h30 to 3h. I restore my database o
n a TEST Server (same version), the the execution time is still 1h30...
I monitored both execution, and realise that the CPU time for the CXPACKET p
rocess is 4 time higher it the PRODUCTION server than on the TEST Server...
.
Do you think that can be the problem?From Neil Pike's FAQ
Q. What does a wait type of CXPacket or Exchange mean?
A. You will get this only with versions of SQL that support parallel
queries, i.e. SQL 7 and later. It means that one thread of the query is wait
ing for a message packet from another, and the one it is waiting on is eithe
r blocked by a traditional
cause or has hit some sort of parallelism bug.
CXPacket means it is waiting on a data packet - i.e. the results of an inter
nal query is being passed. Exchange means that it is waiting on a control pa
cket - i.e. waiting for a child/sibling process to tell you that it is finis
hed.
If the query doesn't complete then make sure the latest service pack is appl
ied as there are several parallel query fixes in each one. If it still doesn
't fix it then you have run into an unfixed bug and will need to contact Mic
rosoft PSS and raise a bug
report. You should be able to work-around the problem by adding (MAXDOP=1) a
s a query hint which will prevent the query being parallelized.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Cumulative wait time on server replies

i've got 2 identiical db on 2 servers. the hardware config is different. i
run the same queries on these 2 servers. execution plan, statistics like
reads / writes / cpu time, etc are all the same, the only difference is the
"Cumulative wait time on server replies".
What does it mean? does it have anything to do with the hardware like
memory, paging file or even the speed of the hard disks?
thanks.SQL Books Online says :-
Cumulative amount of time the driver spent waiting for replies from the
server.
It's unlikely this has anything to do with the performance of your query.
You have identical query plans on different servers, do you have the same
volume of data..?
What does DBCC SHOWCONTIG show for the tables, are there differences in the
level of fragmentation.
Is blocking an issue on one of the servers as it's in a Production
environment and the other is Test..
HTH. Ryan
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:E865E508-11BD-4F5D-9568-1C1BAABB4F27@.microsoft.com...
> i've got 2 identiical db on 2 servers. the hardware config is different.
> i
> run the same queries on these 2 servers. execution plan, statistics like
> reads / writes / cpu time, etc are all the same, the only difference is
> the
> "Cumulative wait time on server replies".
> What does it mean? does it have anything to do with the hardware like
> memory, paging file or even the speed of the hard disks?
> thanks.
>

Cumulative wait time on server replies

i've got 2 identiical db on 2 servers. the hardware config is different. i
run the same queries on these 2 servers. execution plan, statistics like
reads / writes / cpu time, etc are all the same, the only difference is the
"Cumulative wait time on server replies".
What does it mean? does it have anything to do with the hardware like
memory, paging file or even the speed of the hard disks?
thanks.SQL Books Online says :-
Cumulative amount of time the driver spent waiting for replies from the
server.
It's unlikely this has anything to do with the performance of your query.
You have identical query plans on different servers, do you have the same
volume of data..?
What does DBCC SHOWCONTIG show for the tables, are there differences in the
level of fragmentation.
Is blocking an issue on one of the servers as it's in a Production
environment and the other is Test..
HTH. Ryan
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:E865E508-11BD-4F5D-9568-1C1BAABB4F27@.microsoft.com...
> i've got 2 identiical db on 2 servers. the hardware config is different.
> i
> run the same queries on these 2 servers. execution plan, statistics like
> reads / writes / cpu time, etc are all the same, the only difference is
> the
> "Cumulative wait time on server replies".
> What does it mean? does it have anything to do with the hardware like
> memory, paging file or even the speed of the hard disks?
> thanks.
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?
my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Servic es\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:
> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> > after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> > SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> > ***********************
> > declare @.script varchar(8000)
> > set @.script = 'cscript.exe'
> > create table #output (
> > output varchar(8000)
> > )
> >
> > insert into #output
> > exec master.dbo.xp_cmdshell @.script
> >
> > select * from #output
> >
> > drop table #output
> > ***********************
> > returns NULL
> >
> > Any ideas?
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checkin
g
the nessessary permissions for sql service accounts to proxy accounts etc. a
t
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MS
SQLServer
HKEY_LOCAL_MACHINE\System\CurrentControl
set\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Wi
ndows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem
-
> I used to run a VBS file that lives on a remote server with cscript throug
h
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread
in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (w
as
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>