Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
Manny
Deferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.c om...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
sql

Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny

Invalid stored procedures are getting created which have errors

Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
--
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny

Invalid Object Name 'OlapObjects'

We are using Microsoft Analysis Service with SP3 on Microsoft SQL Server. The Repository has been migrated to SQL server on the same machine. Recently for no reason, using Microsoft Analysis Manager, we would unable to log in to the repository. We have tried to do reinstallation of the Analysis Service component. After which when using local Analysis Manager to connect, it would show "Cannot connect to the repository. Analysis server: WEBTEST. Error: Not a valid account name or password."

Local Repository Connection String then was: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Analysis Services\Bin\msmdrep.mdb

After confirming that the repository has been moved to SQL server, we changed the repository connection string to the following:

Local Repository Connection String:

Provider=SQLOLEDB.1;Password=sa_password;Persist Security Info=True;User ID=sa;Initial Catalog=DataMart; Data Source=WEBTEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WEBTEST; Use Encryption for Data=False;Tag with column collation when possible=False

where DataMart was the database for the Analysis Service repository and WEBTEST is the server name.

After which when using local Analysis Manager to connect, it would show error "Invalid Object Name 'OlapObjects'".

Interestingly, when the Analysis Services component was just installed, the sample Food Mart also did not displayed.

Please advise on how to solve the problem, thank you.

It sounds as if something went wrong during migration. The error message makes me believe the OlapObjects table does not exist in the DataMart database. I would double-check the contents of this database. It should contain a Server table and an OlapObjects table.

Invalid Descriptor Index

I have a Sql Server 2000 SP3 Publisher that performs merge replication with a group of MSDE 2000 SP3 subscribers.
Everything was working fine. I had to make changes to the publication and reinit the subscribers. Some subscribers are working fine. Others are getting an error. The error is
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Invalid Descriptor Index
(Source: XXXXXXXX(Data source); Error number: 0)
------
Anyone know what might cause this? And the solution for it?
90 % of the failure to enumerat changes errors can be cleared by restarting the agent.
In your case the invalid descriptor index is unusual and points to an ODBC error. SQL Server replication uses ODBC to communicate between servers.
There are some reports that you can clear this error by rearranging your columns returned in your results set.
It sounds like this might be a bug.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||"There are some reports that you can clear this error by rearranging your columns returned in your results set."
I can't rearrange anything since its setup from replication itself. Its wierd because some subscribers to go through fine, others are failing with the error.
|||Is this error a transient error for these subscribers? Are these subscribers at the same MDAC, sp, hot fix level for that the successful subscribers are at?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Invalid Cursor State

All,
SQL 2000 sp3 hotfix 871, Server 2000 sp4
Any hotfix greater than 859 will have this problem. The fix is to get hotfix
876, kb article 831997 is the one you want to tell them to reference. Its f
ree.
Gary Abbott Super DBA, lolThat KB article isn't public, FYI. 878 is (see http://www.aspfaq.com/2515).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:74016FAB-0BBF-4FB8-88C0-4CA6A448E68B@.microsoft.com...
> All,
> SQL 2000 sp3 hotfix 871, Server 2000 sp4
> Any hotfix greater than 859 will have this problem. The fix is to get
hotfix 876, kb article 831997 is the one you want to tell them to reference.
Its free.
> Gary Abbott Super DBA, lol|||They wouldn't give me 878, I did ask. Reading one of your earlier posts.|||Did they give you a reason? How did you come across 876?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:BB74A25F-6B90-44CA-9177-F67574DFC2EF@.microsoft.com...
> They wouldn't give me 878, I did ask. Reading one of your earlier posts.

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
PatrickPatrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick|||Mark,
Thank you for your interest in our problem. The error message is just
"Invalid Connection". The server is running SQL server 2000 with sp3 n
Windows server 2000.
the client are running Windows XP pro.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Patrick,
Connect from Query Analyzer and paste the message here, you will get
something more than "Invalid Connection".
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick Menage wrote:
> Mark,
> Thank you for your interest in our problem. The error message is just
> "Invalid Connection". The server is running SQL server 2000 with sp3 n
> Windows server 2000.
> the client are running Windows XP pro.
> Best Regards
> Patrick
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||> Thank you for your interest in our problem. The error message is just
> "Invalid Connection".
? What software is giving this "error message"?
http://www.aspfaq.com/
(Reverse address to reply.)|||I have just found the solution. There was another SQL server instance
running with the same port number. I changed it and everythings is
working fine.
Thank you for your support.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
PatrickPatrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick

Invalid Connection to SQL2000 after installing SP3

After having installed SP3 on SQL 2000 server standard edition running
on Windows 2000 server, users cannot access the database whether
through Enterprise Manager, Query Analyser, or my application.
It was running fine before. Users are running Windows XP. The database
is a named instance and is used for replication. Funny enough, I may
access the database from my development machine which is running
Windows 2000 server.
Help is most welcome
Patrick
Patrick,
What are the symptoms? What does "users cannot access the database"
mean? Does it time-out, do they get an error? Please paste the error
message.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick wrote:
> After having installed SP3 on SQL 2000 server standard edition running
> on Windows 2000 server, users cannot access the database whether
> through Enterprise Manager, Query Analyser, or my application.
> It was running fine before. Users are running Windows XP. The database
> is a named instance and is used for replication. Funny enough, I may
> access the database from my development machine which is running
> Windows 2000 server.
> Help is most welcome
> Patrick
|||Mark,
Thank you for your interest in our problem. The error message is just
"Invalid Connection". The server is running SQL server 2000 with sp3 n
Windows server 2000.
the client are running windows XP pro.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Patrick,
Connect from Query Analyzer and paste the message here, you will get
something more than "Invalid Connection".
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Patrick Menage wrote:
> Mark,
> Thank you for your interest in our problem. The error message is just
> "Invalid Connection". The server is running SQL server 2000 with sp3 n
> Windows server 2000.
> the client are running windows XP pro.
> Best Regards
> Patrick
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||> Thank you for your interest in our problem. The error message is just
> "Invalid Connection".
? What software is giving this "error message"?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I have just found the solution. There was another SQL server instance
running with the same port number. I changed it and everythings is
working fine.
Thank you for your support.
Best Regards
Patrick
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Invalid buffer received from client.

I'm having seriouos trouble after installing SP4. I have an application writ
ten in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after upgrading
to SP4, the application totally crashed while trying to access the database.
It gives an error saying a
severe error has occued on the server. The
event log on the server has the following logged:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
For more information, see Help and Support Center at http://go.microsoft.com/fwlin
k/events.asp.
I'm sure it's SP4 because after I reinstalled SQL Server with SP3, everythin
g worked fine again. The server is running Windows 2003 SP1.
Anyone else having this problem or have a solution?Sp4 reduces the network packet size to 32767. Consider adjusting your app to
avoid the error.
<quote>
In SP4, the maximum value for the network packet size option (set using
sp_configure) is 32767. This is slightly less than half the previous maximum
of 65536. During upgrade, existing values larger than 32767 will
automatically be adjusted to 32767. If a script attempts to use sp_configure
to set a value larger than 32767 but less than or equal to 65536, the value
will also be set to 32767. Setting the network packet size to a value larger
than 65536 results in an error.
</quote>
There's used to be a bug even when the network packet size was set to
65535...
http://support.microsoft.com/kb/875411
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42813312@.duster.adelaide.on.net...
> I'm having seriouos trouble after installing SP4. I have an application
> written in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after
> upgrading to SP4, the application totally crashed while trying to access
> the database. It gives an error saying a severe error has occued on the
> server. The event log on the server has the following logged:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> I'm sure it's SP4 because after I reinstalled SQL Server with SP3,
> everything worked fine again. The server is running Windows 2003 SP1.
> Anyone else having this problem or have a solution?|||Thanks oj...
Do you have an example of how to change the packet size to 32767 - is it thr
ough the connection string? At the moment, it has packet size set to 4096.
oj wrote:
> Sp4 reduces the network packet size to 32767. Consider adjusting your app
to
> avoid the error.
> <quote>
> In SP4, the maximum value for the network packet size option (set using
> sp_configure) is 32767. This is slightly less than half the previous maxim
um
> of 65536. During upgrade, existing values larger than 32767 will
> automatically be adjusted to 32767. If a script attempts to use sp_configu
re
> to set a value larger than 32767 but less than or equal to 65536, the valu
e
> will also be set to 32767. Setting the network packet size to a value larg
er
> than 65536 results in an error.
> </quote>
> There's used to be a bug even when the network packet size was set to
> 65535...
> http://support.microsoft.com/kb/875411
>|||Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
According to the article, you might want to limit each of your insert batch
to 32767 rows.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:42817d1b$1@.duster.adelaide.on.net...
> Thanks oj...
> Do you have an example of how to change the packet size to 32767 - is it
> through the connection string? At the moment, it has packet size set to
> 4096.
> oj wrote:|||I tried what you suggested but with no vail.
Here's my connection string:
UID=user;PWD=password;Initial Catalog=DatabaseName;Data Source=ServerName;Pa
cket Size=4096;
I tried the packet size 2048, 1024, 768 but they all return the same error.
Any help is greatly appreciated!
oj wrote:
> Yes. You want to specify the "Packet Size=xxx" in your connectionstring.
> According to the article, you might want to limit each of your insert batc
h
> to 32767 rows.
>|||Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
and be sure the length is not exceeding the allowable for the specified
type.
If you post your code (.Net call + sql), someone will take a closer look.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:428293aa$1@.duster.adelaide.on.net...
>I tried what you suggested but with no vail.
> Here's my connection string:
> UID=user;PWD=password;Initial Catalog=DatabaseName;Data
> Source=ServerName;Packet Size=4096;
> I tried the packet size 2048, 1024, 768 but they all return the same
> error.
> Any help is greatly appreciated!
> oj wrote:|||Thanks oj... I changed my code as you suggested and it is now working! :)
But now it runs extremely slow and frequently timeout. Any solution to this
problem as well?
oj wrote:
> Perhaps, you want to explicitly define the sqldbtype for the sqlparameter
> and be sure the length is not exceeding the allowable for the specified
> type.
> If you post your code (.Net call + sql), someone will take a closer look.
>|||Double check your sql to make sure proper indexes installed. Check out these
to see if they help:
http://support.microsoft.com/?id=308049
http://support.microsoft.com/kb/224587/
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282bc9e@.duster.adelaide.on.net...
> Thanks oj... I changed my code as you suggested and it is now working! :)
> But now it runs extremely slow and frequently timeout. Any solution to
> this problem as well?
>
> oj wrote:|||Thanks oj... It's working fine now...
The problem was caused by creating an SQL parameter using DbType instead of
SqlDbType. I was trying to create a generic data provider that connects to b
oth Oracle and SQL Server. Now I think I have to map the individual DbType t
o SqlDbType.
Anyway, thanks for your help!! Much appreciated!
oj wrote:
> Double check your sql to make sure proper indexes installed. Check out the
se
> to see if they help:
> http://support.microsoft.com/?id=308049
> http://support.microsoft.com/kb/224587/
>|||You're very welcome.
-oj
"Watery" <waterydan@.hotmail.com> wrote in message
news:4282ce51@.duster.adelaide.on.net...
> Thanks oj... It's working fine now...
> The problem was caused by creating an SQL parameter using DbType instead
> of SqlDbType. I was trying to create a generic data provider that connects
> to both Oracle and SQL Server. Now I think I have to map the individual
> DbType to SqlDbType.
> Anyway, thanks for your help!! Much appreciated!
> oj wrote:

intra-query parallelism

Hi,
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,
Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>
|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thanks,
> How do I do for The MAXDOP hint
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

intra-query parallelism

Hi,
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I am running sql 7 with sp3.
> >
> > I got an error in running a sp.
> > The error message says, :Intra-query parallelism caused your server
> > command(process ID# 30) to deadlock. Rerun the query without intra-query
> > parallelism by using the query hint option (Maxdop 1).
> >
> > Any ideas for this.
> >
> > Thanks,
> >
> >
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > I do have sp4 installed
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > What I did was increase the value for intra-query-Paralellism.
> > Is that ok instead of trying try the MAXDOP hint?
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> How do I do for The MAXDOP hint
> >that would only affect the one query.
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> > Thanks,
> >
> > How do I do for The MAXDOP hint
> > >that would only affect the one query.
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>