Showing posts with label access. Show all posts
Showing posts with label access. Show all posts

Tuesday, March 27, 2012

Delete and update

Hi All,

I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.

I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?

Any suggestions?

RoyAre you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Roy" <praish1998@.yahoo.comwrote in message
news:1173148692.840874.72780@.c51g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.
>
I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?
>
Any suggestions?
>
Roy
>

|||Jack Vamvas wrote:

Quote:

Originally Posted by

Are you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?


What SQL statements does Profiler show as being attempted?
How are the relationships enforced (which ON DELETE option is in use)?

Delete All Tables from MS Access Database

HI

I want to delete all tables from an MS Access database.

i cannot use the designer . i have to do it thru an sql statement

a bunch of statements will also do . .

any body has a solution ?

P.s: All replies will be appreacited

Hi,

this is the SQL Server forum, perhaps you might try a Access forum, which might give you a wider variety of answers and experiences.

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.desql

Wednesday, March 21, 2012

Delay affecting key violation?!

Hello!
I'm having a little problem with SQL Server (this doesn't happen in Access):
Using ADO:
I begin a transaction
I insert a row into table X, but the primary key already exists so(an error
should be raised immediately)
I insert a related row into a related table X.
I do some more things
Hey! Error?! Key violation?! Where? At table X (my first insert)...
uhhhhhhhhh!!!
Is there any way to make SQL Server send an error immediately if the insert
violate the primary key?
I just don't get this :(
Thanks for all your help.
Jorge Carvalho
rdc02271@.yahoo.comset xact_abort on
"Jorge C" wrote:

> Hello!
> I'm having a little problem with SQL Server (this doesn't happen in Access
):
> Using ADO:
> I begin a transaction
> I insert a row into table X, but the primary key already exists so(an erro
r
> should be raised immediately)
> I insert a related row into a related table X.
> I do some more things
> Hey! Error?! Key violation?! Where? At table X (my first insert)...
> uhhhhhhhhh!!!
> Is there any way to make SQL Server send an error immediately if the inser
t
> violate the primary key?
> I just don't get this :(
> Thanks for all your help.
> Jorge Carvalho
> rdc02271@.yahoo.com
>
>|||Thanks but little problem:
If I set transaction mode to explicit the entire transaction will be rolled
back, neglecting my commit or rollback statement.
So what's the point of having explicit transaction mode when you are unable
to: control the error the minute it happens + make necessary changes and try
again without rolling back the transaction?
Thanks.
Jorge Carvalho
rdc02271@.yahoo.com
"Ray D" <RayD@.discussions.microsoft.com> escreveu na mensagem
news:F78827F1-F1BC-4ADF-B25A-F517529B9C12@.microsoft.com...
> set xact_abort on
> "Jorge C" wrote:
>
>|||You are correct that an error should be raised as soon as you insert a
row that violates a constraint. However, what you haven't told us is
the exact "key violation" error message you are getting? Was it the
foreign key or primary key constraint? It seems like the second insert
caused the error so maybe the message is "INSERT statement conflicted
with COLUMN FOREIGN KEY constraint", which might explain why you see it
as an error "at table X".
Also, check if there are any triggers on these tables. Maybe a trigger
is at fault.
David Portas
SQL Server MVP
--|||Hi!
The primary key at the first table is being violated.
Our scheme is like this: (if you have a better system please share :) )
1)The user fills out an invoice (header+details+some other info)
2)Then we try to save the invoice using the latest invoice number+1;but
since we have more one application doing the same with the same database
(multi-user) the insert for the first table may fail (invoice header). So we
need to change the invoice number and try again.
The primary key violation message refers to this first operation.
Thanks.
Jorge Carvalho
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escreveu na mensagem
news:1112358100.042970.307850@.z14g2000cwz.googlegroups.com...
> You are correct that an error should be raised as soon as you insert a
> row that violates a constraint. However, what you haven't told us is
> the exact "key violation" error message you are getting? Was it the
> foreign key or primary key constraint? It seems like the second insert
> caused the error so maybe the message is "INSERT statement conflicted
> with COLUMN FOREIGN KEY constraint", which might explain why you see it
> as an error "at table X".
> Also, check if there are any triggers on these tables. Maybe a trigger
> is at fault.
> --
> David Portas
> SQL Server MVP
> --
>|||That isn't an efficient strategy for generating an incrementing key. Also,
as a matter of design principle it's not a good idea to build a system that
generates errors as part of its correct behaviour.
Max invoice number + 1:
INSERT INTO Invoices (invoice_num,... /* other columns */)
SELECT COALESCE(MAX(invoice_num),0)+1, ... /* other data */
FROM Invoices
If it isn't a problem to have gaps in the sequence of invoice numbers then
you might also consider using the IDENTITY property to generate the
sequence.
Don't forget to declare your table's natural key. Maybe (customer,
invoice_date) for example.
David Portas
SQL Server MVP
--

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> > Hi group,
> >
> > I am seeing degraded performance after applying SP4 on SQL 2000.
> >
> > My specific queries (they are in stored proc) access linked server. When
> > the process is run, I keep seeing blocks on the linked server side among
> > processes of the same spid spun off from the calling process like this:
> >
> > A. a process (say spid 58) on initiating server runs, some times shows
> > blocked by a process (say spid 111) on the linked server, some times not
> > displaying the block but just continue to run slow;
> >
> > B. a few processes (spid 111) on the linked server running, often showing
> > one or two of them blocked by another spid 111.
> >
> > I have googled and found that there are reports of such slowed down
> > performance after SP4 application, not necessarily related to the use of
> > linked server. There is an explanation by Microsoft why processes could
> > show being blocked by process of the same spid. Problem is that it looks
> > like no solution to the problem and nor explanation why the switch of
> > locks is so slow.
> > From what I read other people report, this blocking among processes of the
> > same spid repeats itself so often and is so slow that you can easily catch
> > the display of
> > such blocks in EM. This is the same case by me: when the process is
> > running, I
> > easily see such blocks with refresh of EM.
> >
> > My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> > only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> > checked on my systems and is set up properly.
> >
> > Wish to hear from you.
> >
> > Quentin
> >
> >
> >|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>> The parallel process blocking occurred long before you applied SP4.
>> Microsoft added visibility to that blocking in SP4 so DBAs could manage
>> parallelism better. Reduce the degree of parallelism on your query, or
>> even
>> server-wide, and you will see the self-blocking go away.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
>> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> > Hi group,
>> >
>> > I am seeing degraded performance after applying SP4 on SQL 2000.
>> >
>> > My specific queries (they are in stored proc) access linked server.
>> > When
>> > the process is run, I keep seeing blocks on the linked server side
>> > among
>> > processes of the same spid spun off from the calling process like this:
>> >
>> > A. a process (say spid 58) on initiating server runs, some times shows
>> > blocked by a process (say spid 111) on the linked server, some times
>> > not
>> > displaying the block but just continue to run slow;
>> >
>> > B. a few processes (spid 111) on the linked server running, often
>> > showing
>> > one or two of them blocked by another spid 111.
>> >
>> > I have googled and found that there are reports of such slowed down
>> > performance after SP4 application, not necessarily related to the use
>> > of
>> > linked server. There is an explanation by Microsoft why processes
>> > could
>> > show being blocked by process of the same spid. Problem is that it
>> > looks
>> > like no solution to the problem and nor explanation why the switch of
>> > locks is so slow.
>> > From what I read other people report, this blocking among processes of
>> > the
>> > same spid repeats itself so often and is so slow that you can easily
>> > catch
>> > the display of
>> > such blocks in EM. This is the same case by me: when the process is
>> > running, I
>> > easily see such blocks with refresh of EM.
>> >
>> > My process used to run about 30 sec on SP3a, now it runs some 15 min.
>> > The
>> > only troubleshoot hint I saw sofar is the correct setup of MS DTC,
>> > which I
>> > checked on my systems and is set up properly.
>> >
>> > Wish to hear from you.
>> >
>> > Quentin
>> >
>> >
>> >
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
Quentin
The parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>
|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...94&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegr oups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...94&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>
|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.
|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archi...02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or ev
en
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.orgsql

Friday, March 9, 2012

Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

Wednesday, March 7, 2012

Define Printer in Report

I have a client who tells me in Access you could assign the printer directly to the report using PrtDevNames and PrtDevMode. Apparently it's true.

Can this be done in Reporting Services?

check out the section "Direct Client Printing" in this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

Shyam

|||

Thank you for the response.

Although that is a nice feature in 2005 over 2000. It's not quite what I am looking for I don't think.

I want to be able to store which printer the report should be sent to when printed so the user does not have to, I am looking for the same functionality that is in Access I mentioned above.

define a default value ?

hallo

i am on the very beginning with sql.s,

how can i define a default value for the table field like its possible in access,

for example: the if the user not gonna enter a val so there is a default val predefined in the table field,

for example i would like to predefine the date now on the smalldate field....

thanks

maybe something like:

Code Snippet

create table dbo.yourTable

( anInteger int default (0)

)

Give a look to books online.

|||

so if i would like to predefine the date now on the smalldate field its will look like this

table dbo.myTable

( anInteger smalldate default (0)=new

)

|||

No, that would be more like:

createtable dbo.myTable

( anInteger smalldate default ( getdate() )

)

|||thanks