Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.|||Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc. for
> the % sign. Also is there anyway to see a timestamp for the errors returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Could it be that you have something like 'On Error Resume Next' in your VB
app, that is silently ignoring the error?
I am not aware of any step by step instructions for using Profiler but, it
should be quite simple and intuitive once you open it. I have some related
articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
Apart from that do checkout SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not
the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the
mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select
all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the
object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc.
for
> the % sign. Also is there anyway to see a timestamp for the errors
returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on
the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Narayana:
Thanks, I had already opened the profiler and you are right it is pretty
intuitive. I was able to capture the sql statement being sent from the vb app
and it did not cause an exception it just was returning 0 rows affected.
Maybe you guys could take a quick look at the statement 9hah - it is ugly)
and see if you can see the error. It is generated by ASP.Net 2.0 using a
gridview and sqldatasource. All of the code is generated by the asp controls
not me so I was expecting it to work for the simple test i was doing. here is
the generated statement with linebreaks inserted to make it more readable.
exec sp_executesql N
'UPDATE [MSDS]
SET
[MSDSName] = @.MSDSName,
[MSDSLink] = @.MSDSLink,
[MSDSSubmittedBy] = @.MSDSSubmittedBy,
[MSDSApprovedBy] = @.MSDSApprovedBy,
[ProductCategoryID] = @.ProductCategoryID,
[MSDSLanguageID] = @.MSDSLanguageID,
[MSDSApproved] = @.MSDSApproved,
[MSDSSubmittedDate] = @.MSDSSubmittedDate,
[MSDSExpired] = @.MSDSExpired
WHERE [MSDSID] = @.original_MSDSID
AND [MSDSName] = @.original_MSDSName
AND [MSDSLink] = @.original_MSDSLink
AND [MSDSSubmittedBy] = @.original_MSDSSubmittedBy
AND [MSDSApprovedBy] = @.original_MSDSApprovedBy
AND [ProductCategoryID] = @.original_ProductCategoryID
AND [MSDSLanguageID] = @.original_MSDSLanguageID
AND [MSDSApproved] = @.original_MSDSApproved
AND [MSDSSubmittedDate] = @.original_MSDSSubmittedDate
AND [MSDSExpired] = @.original_MSDSExpired',
N'@.MSDSName nvarchar(4000),
@.MSDSLink nvarchar(4000),
@.MSDSSubmittedBy nvarchar(8),
@.MSDSApprovedBy nvarchar(4000),
@.ProductCategoryID int,
@.MSDSLanguageID int,
@.MSDSApproved bit,
@.MSDSSubmittedDate nvarchar(4000),
@.MSDSExpired bit,@.original_MSDSID int,
@.original_MSDSName nvarchar(4000),
@.original_MSDSLink nvarchar(4000),
@.original_MSDSSubmittedBy nvarchar(8),
@.original_MSDSApprovedBy nvarchar(4000),
@.original_ProductCategoryID int,
@.original_MSDSLanguageID int,
@.original_MSDSApproved bit,
@.original_MSDSSubmittedDate nvarchar(4000),
@.original_MSDSExpired bit',
@.MSDSName = NULL,
@.MSDSLink = NULL,
@.MSDSSubmittedBy = N'KMcPhail',
@.MSDSApprovedBy = NULL,
@.ProductCategoryID = 5,
@.MSDSLanguageID = 1,
@.MSDSApproved = 0,
@.MSDSSubmittedDate = NULL,
@.MSDSExpired = 0,
@.original_MSDSID = 133,
@.original_MSDSName = NULL,
@.original_MSDSLink = NULL,
@.original_MSDSSubmittedBy = N'KMcPhail',
@.original_MSDSApprovedBy = NULL,
@.original_ProductCategoryID = 5,
@.original_MSDSLanguageID = 3,
@.original_MSDSApproved = 0,
@.original_MSDSSubmittedDate = NULL,
@.original_MSDSExpired = 0
"Narayana Vyas Kondreddi" wrote:
> Could it be that you have something like 'On Error Resume Next' in your VB
> app, that is silently ignoring the error?
> I am not aware of any step by step instructions for using Profiler but, it
> should be quite simple and intuitive once you open it. I have some related
> articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
> Apart from that do checkout SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
> Narayana:
> Thanks for your suggestions. Can you point me to a resource on tracing the
> message with profiler. The vb app does not seem to be throwing an exception
> so i am trying to figure out what is going on. I am not sure it is even
> trying to write to the db at this point.
> "Narayana Vyas Kondreddi" wrote:
> > What you are seeing in Enterprise Manager is a collection of all the error
> > messages, that are stored in the master..sysmessages table. They are not
> the
> > errors raised by your application. When an error occurs, SQL Server will
> > raise those errors by substituting the parameters with appropriate values.
> >
> > If you think you are experiencing an error in your application, then you
> > should capture the error number and the error description via the
> mechanism
> > provided by your programming language. For example, the Err object in VB.
> >
> > Alternatively you can use SQL Server Profiler to see what statements are
> > being executed by SQL Server. You can trace the Exception event to capture
> > errors.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> > news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> > When I right click on my sql server in the enterprise manager and select
> all
> > tasks --> manage server messages I am not seeing the object the error is
> > referring to. Instead I see a % wherever it should be inserting the
> object
> > name like below.
> >
> > Incorrect value for parameter '%s'.
> >
> > What do i have to do to get the server to substitute the tablename, etc.
> for
> > the % sign. Also is there anyway to see a timestamp for the errors
> returned.
> > It would be helpful to know when the error occured. Basically I am having
> > issues with an ASP.net page that is doing an update on a particular table.
> > It
> > is not throwing an exception on the asp.net side so i wanted to look on
> the
> > sql side to see what happened. But on the sql side i can't tell because
> > there
> > is no indication which table or when a particualr message in the manage
> > server messages tool refers too. Is there a better way to go about this?
> > Server is SQL server 2000.
> >
> >
> >
>
>

Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. I
t
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because ther
e
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.

Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.
What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.

Making the year field 2007 to a 07 instead?

Hi

In my matrix cell I have a cell with the following Expression:

=Fields!monthname.Value & " " & Fields!Year.Value

In one of my datasets I have a SELECT CAST(FCYB.cyb_yearname AS int) AS 'Year' ... amongst other things.

Now, how do I make 'Year' to say 07 instead of 2007? What is the proper way to do it? In the expression or in the SQL code?

Thanks in advanced as always

KR

Use Right(fields!Year.Value,2)|||

Thanks a lot!! Worked perfectly!!

Making SSIS Replace instead of Add on

I am working on a configuration database in SSIS. One of the modules in the package is giving me endless amounts of grief. The module is for some reason set up to add data onto the end of any data already stored in the table. Instead, I would like it to replace this data. I have tried an Execute SQL Task that should delete all of the rows in the table, but this isn't working. Is there a more efficient way to do this?

-Kyle

No, sounds like you're on the right track. Destinations only add new data. If you want to remove the old data first then you should use an Execute SQL Task with a DELETE or TRUNCATE statement. Are you getting an error?
|||

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

|||

EWisdahl wrote:

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

Yes! Provided you have permissions to truncate.

Make Changes to Published Database

Vince,
are you making changes through the GUI (SSMS)? It sounds like you are, but
you'll need to use ALTER TABLE instead (see
http://www.replicationanswers.com/AlterSchema2005.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes, I am using the SSMS gui. It is really the only thing I feel safe
working with.
I read your post and that is the exact error I get when trying to make
changes to my published tables. I may have bitten off more than I can chew
with this. I guess my real question/problem/concern is this:
Let's say I have 500+ subscribers and they all have between 25 and 50 MB of
data in their local store (SQLce). At some point I will want to make
changes to my program which require new fields, field name changes, new
tables, etc. From what I am encountering I have to go into the SSMS gui,
delete the publication and all subscriptions and then make changes to my
tables. At this point all the users still have their data in their local
SQLce file; this is what I want so far. However, when I recreate my
publication on the central server and the subscribers go to synchronize what
will happen? I assume it will not find the subscription on the server. So,
will the user be required to delete the file and start over with a new
initialization or can I just add that subscription back to the central
server and just propogate only the changes to the subscribers? What about
changes that the subscriber makes to their local SQLce file during the time
that I removed the publication and then recreated it?
I understand I can do this stuff using scripts and stored procedures, etc.,
but I have zero experience with that and I am kind of afraid to mess with
anything other than the SSMS gui when I have this in production.
I am only a programmer not a dba. I understand
indexing/relationships/tables/field types and so on, but TSQL is foreign to
me. Thanks in advance for your patience.
Vince
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGC7XcgKHHA.4460@.TK2MSFTNGP03.phx.gbl...
> Vince,
> are you making changes through the GUI (SSMS)? It sounds like you are, but
> you'll need to use ALTER TABLE instead (see
> http://www.replicationanswers.com/AlterSchema2005.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Vincent,
you really don't want to reinitialize for all schema changes. If you are
confortable with the SSMS gui, then by all means use that to add articles.
To chnage an existing article, just study ALTER TABLE in BOL - as you're a
programmer, this will be easy to pick up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
MarkMark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
Mark
Mark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>