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
>

Reseting Select Permissions for Public Role

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.I'd use GRANT SELECT ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp) within the script.

-PatP|||That did the trick. Thanks for all the help.sql

rescheduling sql job

Hi,

I need to reschedule a job. SQL Books suggest using sp_update_jobschedule to update the job.

But the stored proc does not exist on my system and returns:
Could not find stored procedure 'sp_update_jobschedule'

Where can i find sp_update_jobschedule or its equivalent??

regards,
Henryyou can also go into SQL enerprise manager, expand your server name, expand management, and double click on jobs, right click on the job in question and click properties.|||Thanks for the reply Thrasymachus... But i need to reschedule on an event... say, when the admin resets the schedule through application.|||fromm Books Online:

"sp_update_jobschedule must be run from the msdb database.

Updating a job schedule increments the job version number."

Are you executing while logged into the msdb or is your sp execution fully qualified like 'EXEC msdb.dbo.sp_update_jobschedule @.parameters = etc...|||Thanks a million... I was looking for it in master

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
>
>

Requires experts help

Hello,
In my application I need to call a HTTP address from the SQL server.Can I call the HTTP address within a stored procedure?
Are there any ways to call HTTP address using SQL server?
Awaiting for the reply.
RegardsOriginally posted by SPathak
Hello,

In my application I need to call a HTTP address from the SQL server.Can I call the HTTP address within a stored procedure?
Are there any ways to call HTTP address using SQL server?

Awaiting for the reply.

Regards

what do u want to do exactly after calling the http address?
do u want to access any data on the page or something?
please clarify a bit more|||Refer to books online for Executing SQL Statements Using HTTP topic.|||I need to call the ISAPI dll in the IIS virtual directory from stored procedure.Can it be possible?|||Originally posted by SPathak
I need to call the ISAPI dll in the IIS virtual directory from stored procedure.Can it be possible?

it is quite possible ,use sp_oacreate,
for details refer to BOL.|||As I am new to this Can you explain me in detail how to create the http object using this procedure.|||As its been suggested twice already..

READ Books On Line for more information.|||Well, for this reason I always suggest to refer to books online for any information. Hope few times you may not get much insight about the issue but it helps you to asses.

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

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS
SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/...&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIA
Start with this:
http://www.microsoft.com/downloads/d...displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net. ..
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

Req code example of selecting MS sql MSDE database stored locally TIA sal

Hello,
I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally
created using MSDE.
TIAStart with this:
http://www.microsoft.com/downloads/details.aspx?familyid=08e3d5f8-033d-420b-a3b1-3074505c03f3&displaylang=en
<sal@.spp.net> wrote in message
news:KK5id.35893$Tq1.10254@.bignews1.bellsouth.net...
> Hello,
> I'm trying to find an example in vb.net of how I can have a user select a
MS SQL database stored locally
> created using MSDE.
> TIA
>
>

reprot subscription error

1).I got an error:
subscription cannot be created because the credentials used to run the
report are not stored, the report is using a user-defined parameter value...
However, can I solve this issue.
2). Is there any step by step guide to configure subscription.
ThanksThis is not complaining about Parameters, but about the data source... Open
the data source (on the report server) that this report uses, and select the
box , credentials stored securely on the server... then choose whichever
suboption suites you..
When a subscription is run, no user is running the report, instead the
server is running the report, so the login has to be stored. The server will
then use that login to run the subscription.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:24A30193-13D9-41E9-8F48-AA572D4101D0@.microsoft.com...
> 1).I got an error:
> subscription cannot be created because the credentials used to run the
> report are not stored, the report is using a user-defined parameter
> value...
> However, can I solve this issue.
> 2). Is there any step by step guide to configure subscription.
> Thanks
>

repost: SqlServer 2005 => SMS => Object Explorer => Programability Filter questio

SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
Contains _aaa_ shows all stored procs that include the string _aaa_, works
great.
SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
Contains _yyy_ shows all stored procs that include the string _yyy_
How does one structure the "contains" filter to show procs that contain both
_aaa_ AND _yyy_ ?
Still don't have a good feeling for Sql Server tools expressions.
Any help would be greatly appreciated !!!
Barry
in Oregon
I don't think that there is any way to combine filter criteria.
You could execute the following query:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ( ROUTINE_NAME LIKE '%_aaa_%'
OR ROUTINE_NAME LIKE '%_yyy_%'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in message news:%23tuas6o$GHA.4024@.TK2MSFTNGP04.phx.gbl...
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _aaa_ shows all stored procs that include the string _aaa_, works
> great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _yyy_ shows all stored procs that include the string _yyy_
> How does one structure the "contains" filter to show procs that contain both
> _aaa_ AND _yyy_ ?
> Still don't have a good feeling for Sql Server tools expressions.
> Any help would be greatly appreciated !!!
> Barry
> in Oregon
>
|||I don't think that there is any way to combine filter criteria.
You could execute the following query:
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ( ROUTINE_NAME LIKE '%_aaa_%'
OR ROUTINE_NAME LIKE '%_yyy_%'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in message news:%23tuas6o$GHA.4024@.TK2MSFTNGP04.phx.gbl...
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _aaa_ shows all stored procs that include the string _aaa_, works
> great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter => Filter:
> Contains _yyy_ shows all stored procs that include the string _yyy_
> How does one structure the "contains" filter to show procs that contain both
> _aaa_ AND _yyy_ ?
> Still don't have a good feeling for Sql Server tools expressions.
> Any help would be greatly appreciated !!!
> Barry
> in Oregon
>
|||frostbb (barry.b.frost@.remove-this-spam-filter.state.or.us) writes:
> SqlServer 2005 => SMS => Object Explorer => Programability Filter =>
> Filter: Contains _aaa_ shows all stored procs that include the string
> _aaa_, works great.
> SqlServer 2005 => SMS => Object Explorer => Programability Filter =>
> Filter: Contains _yyy_ shows all stored procs that include the string
> _yyy_
> How does one structure the "contains" filter to show procs that contain
> both _aaa_ AND _yyy_ ?
I thought that maybe something like [xy]{xy][xy] would take you half-way
there, but it was too smart for me and escaped the brackets.
If you feel that this would be a valueable feature, pay a visit to
https://connect.microsoft.com/SQLServer/feedback/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Erland,
Thanks for the feedback. Its very much appreciated! Also, apologies for
the late acknowledgement.
Good suggestion. I'll ping MS about the issue
https://connect.microsoft.com/SQLServer/feedback/.
I've got a couple hundred stored procs (so far) and the filter is becoming
more and more handy. (we've been migrating our two 'largest' enterprise db's
from UNIX to Sql Server). I name our table management procs to sort by
sub-system / table_name. I also have a number of 'generic tool box' procs
that are accessed by multiple subsystems ... thus the need to display both
_aaa_ and _yyy_ subsystem procs at the same time.
Best Wishes!
Barry
in Oregon
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns986FEE338BC11Yazorman@.127.0.0.1...
> frostbb (barry.b.frost@.remove-this-spam-filter.state.or.us) writes:
> I thought that maybe something like [xy]{xy][xy] would take you half-way
> there, but it was too smart for me and escaped the brackets.
> If you feel that this would be a valueable feature, pay a visit to
> https://connect.microsoft.com/SQLServer/feedback/.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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!
>

Repost: Exporting Information

Hello NG
We have a production database that we have moved the tables to SQLServer and
I have been creating stored procedures to replicate some of the processes
done on the access side - the server does processing extremely faster than
access - Here is my problem I am currently pulling info from SQL SERVER to
Access once a w for wly stats I want to create a stored procedure to
process the wly stats and push them to an access table upon request - we
have multiple warehouses and I would like to have the process coded once in
sql and dump to the access table based off a warehouse variable passed to
the stored procedure - Can SQLServer from a stored procedure export a
recordset into a varible defined Access Table?
TIAFAH
RandyHi Randy,
Yes this is possible...You can do it by using a linked server. You can
find info about linked servers in SQL Server Books Online. I've given
an outline of the general steps you need to do below.
Hope this helps.
=================
1) Open Query Analyzer and log in using an account with SysAdmin
privileges (e.g. sa). Make sure you are using the "master" database.
2) Type in the following:
exec sp_addlinkedserver '<Linked Server Name>', '<MS Access Version>',
'Microsoft.Jet.OLEDB.4.0', '<Full Path To Access DB>'
<Linked Server Name> can be whatever you want it to be, you will use
this to reference the Access DB.
<MS Access Version> is something like 'Access 97' or 'Access 2003' etc
<Full Path To Access DB> is, well, yeah...
3) Execute what you entered in (2) and then clear the Query Analyzer
window of the command
4) Type in the folliwng into Query Analyzer:
sp_addlinkedsrvlogin '<Linked Server Name>', false, '<SQL Server Login
Name>', 'Admin', NULL
<SQL Server Login Name> is the SQL Server login that will access the
Access DB. Make it something like "sa" if you will be executing the
stored proc under your account.
5) Execute the command entered in (4)
OK, after doing that you can now access you MS Access DB from SQL
Server. To reference a table in Access you have to write:
<Linked Server Name>...<Table Name>
You can do Inserts and other things to this table. In your case you
would probably be wanting to Insert values into the table.
Hope that helps.

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.