Resolving DNS names from extended stored procedures

I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
mailto:rgenter "at" silverlink.com
Are you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>

Resolving DNS names from extended stored procedures

I've developed an extended stored procedure in C and want to resolve a
hostname from within the stored procedure using DNS. I use getaddrinfo() to
try and resolve the name. It never works from within the extended stored
procedure when running within SQL Server 2000 (SP3a applied). A hard-coded
IP address does work. The error I get back is WSAHOST_NOT_FOUND (11001). If
I do an NSLOOKUP of the same hostname on the same machine, it works fine, so
the server is configured OK. SQL Server 2000 is running using an account
that does have network access; when I specify an IP address to my stored
procedure, which turns around and establishes a TCP connection to a
specified port and sends XML over the connection, it works.
Are there any known issues resolving DNS names from within extended stored
procedures?
Thanks in advance.
Rick
--
mailto:rgenter "at" silverlink.comAre you doing a WSAStartup in your XP?
I have no problems doing gethostbyname to resolve the hostname back to an IP
address.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Rick Genter" <rgenter@.silverlink.com> wrote in message
news:eJhtjM%23qEHA.2604@.TK2MSFTNGP10.phx.gbl...
> I've developed an extended stored procedure in C and want to resolve a
> hostname from within the stored procedure using DNS. I use getaddrinfo()
> to try and resolve the name. It never works from within the extended
> stored procedure when running within SQL Server 2000 (SP3a applied). A
> hard-coded IP address does work. The error I get back is WSAHOST_NOT_FOUND
> (11001). If I do an NSLOOKUP of the same hostname on the same machine, it
> works fine, so the server is configured OK. SQL Server 2000 is running
> using an account that does have network access; when I specify an IP
> address to my stored procedure, which turns around and establishes a TCP
> connection to a specified port and sends XML over the connection, it
> works.
> Are there any known issues resolving DNS names from within extended stored
> procedures?
> Thanks in advance.
> Rick
> --
> mailto:rgenter "at" silverlink.com
>

Reserved Words

Is there a method or procedure name that will let me know if a word is a sql
server reserved word?
i.e.
sp_isReservedWord 'Null' --returns true
sp_isReservedWord 'Count' --returns true
sp_isReservedWord 'OrderNumber' --returns falseJI,
If you trust the list in SQL Server 2005 Books Online, this will work.
It may not be what you had in mind, but I hope it helps.
create function usp_reserved(
@.w nvarchar(128)
) returns bit as begin
set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
if len(@.w) = 0 return 0
return
cast(charindex(@.w,
'|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
'|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
'|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
'|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
'|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
'|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
'|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
'|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
'|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
'|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
'|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
'|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
'|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
'|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
'|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
'|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
'|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
'|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
'|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
'|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
'|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
'|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
'|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
'|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
'|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
'|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
'|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
'|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
'|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
'|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
'|ESCAPE|OVER|WRITETEXT|') as bit)
end
go
select dbo.usp_reserved('ESCAPE')
select dbo.usp_reserved('AD')
select dbo.usp_reserved('ADD')
select dbo.usp_reserved('ADD|EXCEPT')
go
-- drop function usp_reserved
-- Steve Kass
-- Drew University
JI wrote:

>Is there a method or procedure name that will let me know if a word is a sq
l
>server reserved word?
>i.e.
>sp_isReservedWord 'Null' --returns true
>sp_isReservedWord 'Count' --returns true
>sp_isReservedWord 'OrderNumber' --returns false
>
>|||Thanks Steve...That saved me some cut and pasting from the BOL.
It would be a nice feature for the SQL team to write, simply to have them
maintain the list.
Thanks again,
ji
"Steve Kass" <skass@.drew.edu> wrote in message
news:%236tHIeaaGHA.3612@.TK2MSFTNGP03.phx.gbl...
> JI,
> If you trust the list in SQL Server 2005 Books Online, this will work.
> It may not be what you had in mind, but I hope it helps.
> create function usp_reserved(
> @.w nvarchar(128)
> ) returns bit as begin
> set @.w = '|'+UPPER(REPLACE(@.w,'|',''))+'|'
> if len(@.w) = 0 return 0
> return
> cast(charindex(@.w,
> '|ADD|EXCEPT|PERCENT|ALL|EXEC|PLAN|ALTER
'+
> '|EXECUTE|PRECISION|AND|EXISTS|PRIMARY|A
NY'+
> '|EXIT|PRINT|AS|FETCH|PROC|ASC|FILE|PROC
EDURE'+
> '|AUTHORIZATION|FILLFACTOR|PUBLIC|BACKUP
|FOR'+
> '|RAISERROR|BEGIN|FOREIGN|READ|BETWEEN|F
REETEXT'+
> '|READTEXT|BREAK|FREETEXTTABLE|RECONFIGU
RE'+
> '|BROWSE|FROM|REFERENCES|BULK|FULL|REPLI
CATION'+
> '|BY|FUNCTION|RESTORE|CASCADE|GOTO|RESTR
ICT'+
> '|CASE|GRANT|RETURN|CHECK|GROUP|REVOKE'+
> '|CHECKPOINT|HAVING|RIGHT|CLOSE|HOLDLOCK
'+
> '|ROLLBACK|CLUSTERED|IDENTITY|ROWCOUNT|C
OALESCE'+
> '|IDENTITY_INSERT|ROWGUIDCOL|COLLATE|IDE
NTITYCOL'+
> '|RULE|COLUMN|IF|SAVE|COMMIT|IN|SCHEMA|C
OMPUTE'+
> '|INDEX|SELECT|CONSTRAINT|INNER|SESSION_
USER'+
> '|CONTAINS|INSERT|SET|CONTAINSTABLE|INTE
RSECT'+
> '|SETUSER|CONTINUE|INTO|SHUTDOWN|CONVERT
|IS'+
> '|SOME|CREATE|JOIN|STATISTICS|CROSS|KEY'
+
> '|SYSTEM_USER|CURRENT|KILL|TABLE|CURRENT
_DATE'+
> '|LEFT|TEXTSIZE|CURRENT_TIME|LIKE|THEN'+
> '|CURRENT_TIMESTAMP|LINENO|TO|CURRENT_US
ER'+
> '|LOAD|TOP|CURSOR|NATIONAL||TRAN|DATABAS
E'+
> '|NOCHECK|TRANSACTION|DBCC|NONCLUSTERED'
+
> '|TRIGGER|DEALLOCATE|NOT|TRUNCATE|DECLAR
E'+
> '|NULL|TSEQUAL|DEFAULT|NULLIF|UNION|DELE
TE'+
> '|OF|UNIQUE|DENY|OFF|UPDATE|DESC|OFFSETS
'+
> '|UPDATETEXT|DISK|ON|USE|DISTINCT|OPEN|U
SER'+
> '|DISTRIBUTED|OPENDATASOURCE|VALUES|DOUB
LE'+
> '|OPENQUERY|VARYING|DROP|OPENROWSET|VIEW
|DUMMY'+
> '|OPENXML|WAITFOR|DUMP|OPTION|WHEN|ELSE|
OR'+
> '|WHERE|END|ORDER|WHILE|ERRLVL|OUTER|WIT
H'+
> '|ESCAPE|OVER|WRITETEXT|') as bit)
> end
> go
> select dbo.usp_reserved('ESCAPE')
> select dbo.usp_reserved('AD')
> select dbo.usp_reserved('ADD')
> select dbo.usp_reserved('ADD|EXCEPT')
> go
> -- drop function usp_reserved
> -- Steve Kass
> -- Drew University
> JI wrote:
>

Re-raising error

In a stored procedure I usually check @.@.ERROR after every
INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
code. A client application does not receive much information
with this code, so it displays a message like "Cannot insert/update record.
Error : NNN".
Is there a way to get more detailed information about an error?
RAISERROR only throws user-defined errors. Any ideas?"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:u6%23Wmxd8FHA.3132@.TK2MSFTNGP12.phx.gbl...
> In a stored procedure I usually check @.@.ERROR after every
> INSERT/UPDATE/DELETE. If any error, then I exit SP with this error
> code. A client application does not receive much information
> with this code, so it displays a message like "Cannot insert/update
> record. Error : NNN".
> Is there a way to get more detailed information about an error?
> RAISERROR only throws user-defined errors. Any ideas?
>
Unless you use TSQL TRY/CATCH the client will recieve both the original
error and the stored procedure return code. Different client libraries
interpret this data differently, but most have a way to grab the error.
David|||Is your client application SQLServer or something else (dotnet, java etc)?
If SQL then @.@.error is fine unless you want to catch the specific error from
sysmessages. I only ever use raiserror(@.text,1,1) for triggers and i don't
use triggers so I don't really use it.
If it is an external App and you are not a GOTOless programmer then try
something like below then just call the entries from the log table in the
external App:
If @.@.ERROR <> 0 or @.@.ROWCOUNT <= 0
begin
select @.text = 'Error -50: Could not update TableAdata for w.'
select @.result = -50
GOTO ERROR_POINT
end
ERROR_POINT:
PRINT 'ERROR_POINT'
GOTO FINISH
FINISH:
select @.resultText = 'RoutineName: ' + @.text + ' from User ' + @.pWho + ' at
' + convert(varchar(15),getdate(),3) + ' ' + convert(varchar(15),getdate(),1
4)
INSERT INTO log VALUES (@.resultText, 'RoutineName', getdate(), 'Y')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO|||No pun intended, but please explain how this one works.
There must be something missing from the sample you posted.
All statements will be executed - even if there is no error and the rowcount
is above 0, and in such a case a null value (or an unexpected one) will be
inserted into the log.
Do you actually use this in your production code?
ML|||Thank you!
But you are both talking about SQL2005? If I'm not mistaken,
SQL2000 does not support TRY/CATCH exception handling. And that
is only reason why I asked for how to re-raise (read "rethrow") an error.
If there was try/catch handling in SQL2000, I had no problem.|||"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:%235tAGhh8FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Thank you!
> But you are both talking about SQL2005? If I'm not mistaken,
> SQL2000 does not support TRY/CATCH exception handling. And that
> is only reason why I asked for how to re-raise (read "rethrow") an error.
> If there was try/catch handling in SQL2000, I had no problem.
>
IN SQL 2000 there is no way to prevent the error message from propagating to
the client. If the client is using, for instance, .NET the calling code
will get a SqlException. Only in SQL 2005 is there a way to stop the error
from going to the client (CATCH), and so only there is there any need to
"rethrow" the error in SQL Server.
David|||> IN SQL 2000 there is no way to prevent the error message from propagating
> to the client. If the client is using, for instance, .NET the calling
> code will get a SqlException.
But how does a client receive an error? For example, a SP executes
INSERT that fails due to some constraint violation:
INSERT ...
IF @.@.ERROR <> 0
...
What happens in this case? SP execution stops before IF @.@.ERROR,
exits with an error notification and the client engine receives a standard
SQL error. Or SQL server remembers this error, the SP continues processing
and when SP exits normally (i.e. RETURN @.some_value) the client engine
receives
this return code (@.some_value), but also it receives the previously saved
error too?|||"Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
news:e2m$$tq8FHA.472@.TK2MSFTNGP15.phx.gbl...
> But how does a client receive an error? For example, a SP executes
> INSERT that fails due to some constraint violation:
> INSERT ...
> IF @.@.ERROR <> 0
> ...
> What happens in this case? SP execution stops before IF @.@.ERROR,
> exits with an error notification and the client engine receives a standard
> SQL error. Or SQL server remembers this error, the SP continues processing
> and when SP exits normally (i.e. RETURN @.some_value) the client engine
> receives
> this return code (@.some_value), but also it receives the previously saved
> error too?
Sort of, yes. The client gets the return code and any errors which occured.
Most client libraries don't expose both through the API, however. If an
error occurs, you will likely not get a return code from the procedure.
David|||Many thanks!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e$Zbolw8FHA.740@.TK2MSFTNGP11.phx.gbl...
> "Tumurbaatar S." <spam_tumur@.magicnet.mn> wrote in message
> news:e2m$$tq8FHA.472@.TK2MSFTNGP15.phx.gbl...
> Sort of, yes. The client gets the return code and any errors which
> occured. Most client libraries don't expose both through the API, however.
> If an error occurs, you will likely not get a return code from the
> procedure.
> David
>
>

required help

I have written the following stored procedure but I am gettig some errors, can anybody help me.
CREATE PROCEDURE <procedurename>
@.tempstr as varchar(20),
@.ntempstr as varchar(20),
@.cdestr as varchar(20),
@.Indstr as varchar(20),
@.prdstr as varchar(20),
@.notpos as numeric,
@.nnotpos as numeric,
@.orapos as numeric
AS
begin
while (@.tempstr !=' ' )
loop
begin
@.orapos=CHARINDEX("OR", @.tmpstr)
if (@.orapos > 0)
begin
@.ntempstr=LTRIM(LEFT(@.tempstr, 2))
@.tempstr=Ltrim(mid(@.tempstr, (@.orpos+2))
end
else
begin
@.ntempstr=LTRIM(@.tempstr)
@.tempstr= ' '

@.notpos=CHARINDEX("NOT", @.tmpstr)
end
If @. @.notpos > 0 Then
@.nnotpos = LTrim(Mid( @.nnotpos, @.notpos + 3))
end
end

for( loop
GO

Quote:

Originally Posted by samb

I have written the following stored procedure but I am gettig some errors, can anybody help me.
CREATE PROCEDURE <procedurename>
@.tempstr as varchar(20),
@.ntempstr as varchar(20),
@.cdestr as varchar(20),
@.Indstr as varchar(20),
@.prdstr as varchar(20),
@.notpos as numeric,
@.nnotpos as numeric,
@.orapos as numeric
AS
begin
while (@.tempstr !=' ' )
loop
begin
@.orapos=CHARINDEX("OR", @.tmpstr)
if (@.orapos > 0)
begin
@.ntempstr=LTRIM(LEFT(@.tempstr, 2))
@.tempstr=Ltrim(mid(@.tempstr, (@.orpos+2))
end
else
begin
@.ntempstr=LTRIM(@.tempstr)
@.tempstr= ' '

@.notpos=CHARINDEX("NOT", @.tmpstr)
end
If @. @.notpos > 0 Then
@.nnotpos = LTrim(Mid( @.nnotpos, @.notpos + 3))
end
end

for( loop
GO

Unfortunately there really are quite a few errors in it. I suggest you start with a simpler version and then expand it as you get experience. A few corrections:it is not complete code snippet (the 'for' loop at the end is obviously incomplete, plus you have variables which you never use)

Require Solution for this SQL problem

Hi,

I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.

So for e.g. say structure of Table A and Table B

Table A Table B

col1 ID RunID col1

Now when I run stored proc I want rows copied as below

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2

The next day when stored prc runs I want data as

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2

So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.

Please help.

Nick"Nick" <nachiket.shirwalkar@.gmail.comwrote in message
news:1189853588.129281.216870@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.
>
So for e.g. say structure of Table A and Table B
>
Table A Table B
>
col1 ID RunID col1
>
Now when I run stored proc I want rows copied as below
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
>
The next day when stored prc runs I want data as
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2
>
So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.
>
Please help.
>
Nick
>

Lookup the ROW_NUMBER() function.

--
David Portas

request Stored procedure filtered by Today

I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date

In SqlServer, today's function is GetDate()|||

Yes in T-SQL we use GETDATE() to get today'date, but remember that the GETDATE() function will return a DATETIME value which also contains time. So if you just want to compare date, you may need something like this (suppose the EXPDATE column is also DATETIME data type):

select * fromyourTable
WHERE DATEDIFF(d,GETDATE(),EXPDATE)>0

request issue

Hi,
I've got two primary keys in a table:

Constraint(QueryId, ConstraintName)

In a stored procedure I select {QueryId, ConstraintName} couples that
match some criteria, and what I want to do is specifying in my a SELECT
statement that I want all of the {QueryId, ConstraintName} that are not
in my stored procedure result. With only one field, it would be easy :

Select * from Constraint where QueryId not in (Select QueryId from
OtherTable)

My explanations are not great but I think it's enough to understand
what I want.

Select * from Constraint where QueryId and ConstraintName not in
(select QueryId ,ConstraintName from OtherTable)
--> of course not correct, but then how can I do that ?

ThxI've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||See this thread:

http://groups.google.ch/group/comp...62403e78dd78646

Simon|||Use NOT EXISTS rather than NOT IN:

SELECT *
FROM [Constraint] AS T
WHERE NOT EXISTS
(SELECT *
FROM OtherTable
WHERE queryid = T.queryid
AND constraintname = T.constraintname)

CONSTRAINT is a reserved word and therefore not a good choice for a
table name.

--
David Portas
SQL Server MVP
--|||Try using a LEFT JOIN:

SELECT a.columnList --don't use *, explicitly name your columns
FROM TableA a LEFT JOIN TableB b ON a.Col1 =b.Col1 AND a.Col2 =b.Col2
WHERE b.Col1 IS NULL

HTH,
Stu|||I've actually solved this problem yesterday. I've done it the way David
suggested, using NOT EXISTS and it works just fine.
David, actually my table is called ConstraintMessages :) I wrote
Constraint as it's quicker to type!

Thx

Repost: Latches that don't release

I have encountered an intermittent problem where a stored procedure or query
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ======================================
LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/defaul...d=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Repost: Latches that don't release

I have encountered an intermittent problem where a stored procedure or query
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ======================================
LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.
Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default...=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Repost: Latches that don't release

I have encountered an intermittent problem where a stored procedure or query
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ====================================== LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know:)
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved:)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know:)
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved:)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are ask
ing if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cach
ed for the stored procedure. In complex stored procedures, it is possible th
at the execution plan will be re-compiled during execution, but you can read
more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The
reason being that the table name is not qualified with the owner/schema. It
is possible to have two tables with the same name but different owners, and
data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was
fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where[vbcol=seagreen]
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedur
e Events of the Profiler. These can show individual statements within the St
ored Procedure being executed and if there has been a Cache Hit or Cache Mis
s. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.
Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>
|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.[vbcol=seagreen]
>"Jimbo" wrote:
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where
>.
>
|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is different enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:
> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.
>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>> Hi,
>> Say if you had a store procedure called p_SelectData
>> defined as follows with the parameter @.WhereClause can
by
>> set to either
>> "@.Where = ' Where Surname = ''Jones''...
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''.
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''
>> and HasDetails = 1
>> You get the idea, a dynamic where clause.
>> The SP is as follows
>> CREATE procedure dbo.p_SelectData
>> @.WhereClause varchar(100) as
>> DECLARE @.SQLString varchar(255)
>> SET @.SQLString = 'Select * From MockTable ' +
@.WhereClause
>> EXECUTE ( @.SQLString)
>> My question is can a proper execution plan be formed
>> internally by SQL Server, or does the exec with the
where
>> clause stop it from forming ?
>> N.B. No I didn't implement this it was there when I got
>> there.
>> Thanks.
>>
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be possible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:
> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> >--Original Message--
> >Hi,
> >
> >Firstly, you will always get an execution plan, but I'm
> guessing you are asking if it will cache it and re-use it.
> >
> >I would say that in your simple example, you will get an
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> >
> >As for the EXEC statment, I don't believe that the plan
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> >
> >"Jimbo" wrote:
> >
> >> Hi,
> >>
> >> Say if you had a store procedure called p_SelectData
> >> defined as follows with the parameter @.WhereClause can
> by
> >> set to either
> >>
> >> "@.Where = ' Where Surname = ''Jones''...
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''.
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''
> >> and HasDetails = 1
> >>
> >> You get the idea, a dynamic where clause.
> >>
> >> The SP is as follows
> >>
> >> CREATE procedure dbo.p_SelectData
> >> @.WhereClause varchar(100) as
> >> DECLARE @.SQLString varchar(255)
> >> SET @.SQLString = 'Select * From MockTable ' +
> @.WhereClause
> >> EXECUTE ( @.SQLString)
> >>
> >> My question is can a proper execution plan be formed
> >> internally by SQL Server, or does the exec with the
> where
> >> clause stop it from forming ?
> >>
> >> N.B. No I didn't implement this it was there when I got
> >> there.
> >>
> >> Thanks.
> >>
> >>
> >>
> >.
> >
>

ReportViewer's ReportError event handler not firing

Hi.
I want to intercept any errors generated when the report's stored procedure
executes. I've created a handler for the ReportError event but it never
fires.
My web page is in C# and has AutoEventWireup="true". I even tried explicitly
adding a handler...
theReportViewer.ReportError += new ReportErrorEventHandler(explicitHandler);
...but my handlers don't get run.
What have I missed? Is there a property that needs turning on as well as
creating an event handler?
I'm running with VS sp1.
Thanks,
AndrewForgot to say the report is a server report.
Andrew|||Hi Andrew,
From your description, you're wondering how to capture some error raised in
store-procedure(used in SSRS sever report query) in your client ASP.NET
reportviewer,correct?
Based on my research, the ReportViewer control's ReportError event is
implemented as below:
In ReportViewer's rendering code(such as PreRender, or ReportArea's control
creation and rendering methods...), it will wrapper the code with a
try...catch... block and if any exceptions occur, it will check the
"ReportError" event handler and invoke it if registered.
So for your scenario, I think it problem is possible due to the server-side
report's processing is not in reportviewer(but in the server reportserver's
processing engine). Therefore, the related store-procedure error is not
propagate to the client-side reportviewr control and you didn't get the
event raised. To verify this, you can try using a client report and raise
some error in it to see whether the event work. Also, based on my test, if
the report server(server report) not available, it will also raise error.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Steven.
I had a poke around with Reflector and was coming to the same conclusion.
Thinking about it, when running async the host page is rendered and returned
whilst the report is still running, so even if the server report errors it
wouldn't have anything to report its error to.
I guess we can always look at the ReportServer log file to see what's
occurred.
Thanks,
Andrew|||Thanks for your reply Andrew,
Yes, your further analysis is also reasonable. Actually, exception
propagating is always quite difficult when dealing with distributed
component or service. Anyway, it is a pleasure to discuss with you on this.
If you meet any other problems later, welcome to discuss here.
Have a nice day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.