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)

Reqplication queues up without any errors

I have two publications (with only one article in each) based on the same
source table. Both of these publications have one pull subscription each.
Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
replication queues up for these two articles. How can I check whether this
data has been replicated -- (I do not believe it has).
Also, I am not seeing any errors in replication but the queue keeps growing.
Why are no errors being thrown out?
select * from distribution.dbo.MSdistribution_status and check the number of
undelivered commands.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||The number of rows in MSrepl_commands and MSrepl_transactions will grow
without limitation. They are not removed when they have been applied to
each subscriber. Instead, a clean up process runs at a periodic interval.
This cleanup process removes anything older than the retention interval that
you specified. So, if you have a retention interval of 72 hours, the
cleanup process will remove anything from MSrepl_commands and
MSrepl_transactions that have been successfully applied to all subscribers
needing that transaction which are more than 72 hours old. Just because
data exists in these two tables doesn't mean that it has not been sent to
the subscriber.
MSdistribution_status will tell you how many rows have not been sent. You
can also spot check this using sp_browsereplcmds. It will give you the
exact data for the change sent. You can then simply run a select statement
against the subscriber to verify that the change made it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||Thank you Mike and Hilary. Your answers were very helpful.
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Actually, one more question. How do I check which replication commands /
transactions are not yet delivered? MSdistribution_Status ?
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Check the subscriber and look at the value of transaction_timestamp for
your publication
select transaction_timestamp From MSreplication_subscriptions
Compare this with the xact_Seqno or xact_id in msrepl_transactions in your
publisher's distribution database. Get the xact_seqno - note that this
number will not be an exact match but rather in the range.
The number of rows remaining for that publication are the number of commands
which have to be applied there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:E478A229-D8A4-4511-807B-B977E8DA5E95@.microsoft.com...[vbcol=seagreen]
> Actually, one more question. How do I check which replication commands /
> transactions are not yet delivered? MSdistribution_Status ?
> "Michael Hotek" wrote:

Reprocessing Errors after using the migrationwizard

Hello,

I have converted a AS2000 project to AS2005 using the migrationwizard. All went well, unfortunately I get the following errors when I try to reprocess in AS2005 and I have no idea how to fix them. Does anybody have an idea what I can do ? The first error seems like there is something wrong in my DB with type matching, but it doesn't say where.

Errors in the high-level relational engine. The following exception occurred while an operation was being performed on a data source view: Parent Columns and Child Columns don't have type-matching columns..

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Taak', Name of 'Taak' was being processed.

Errors in the OLAP storage engine: An error occurred while the 'Subtaak attribute' attribute of the 'Taak' dimension from the 'DIMSCUBE1' database was being processed.

Are any of your keys TinyInts with IDENTITY clauses on them?
If so, this is a known restriction of SQLXML (which we are using under the covers). It converts all IDENTITY columns to full integers (and thus you are getting a datatype mismatch. Using either a VIEW or a DSV named query (or computed column) convert the keys (both the PK and the FK parent column) to integers and see what happens.

_-_-_ Dave

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.