Prev: Test format for MCITP (SqlServer2005): 70 - 444
Next: Incorrect syntax near '1' error from sql server 2005
From: JOH on 27 Jun 2008 10:25
SQL Server 2005
Sharepoint with mirroring of all databases to DR box

One of the Sharepoint databases has hyphens in it. This is causing an
error in the Database Mirroring Monitoring Job. The error seems
almost informational, or at least it is not apparently affecting the
mirroring process. When I discontinue mirroring this one database,
the Database Mirroring Monitoring Job stops issuing the error.

When installing Sharepoint, we took care to use a GUID-less name for
each database. For this one particular database, that option did not
appear to be available, and we were forced to use the name with the
GUID still in it. I know I can change the database name on SQL
Server, but I don't know how that will affect Sharepoint. Is there a
way to change that database name, through Sharepoint?

What if Sharepoint cannot be edited so that the database name can be
modified? Is there any way to modify the system stored procedure
(that does the mirroring update) so that this hyphenated database name
no longer causes an issue, such as by wrapping it in [] at the start
of the procedure?

TIA


Here is the error, for those who are interested:
Message
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '-'.
[SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword
'with'. If this statement is a common table expression or an
xmlnamespaces clause, the previous statement must be terminated with a
semicolon. [SQLSTATE 42000] (Error 319). The step failed.
From: Amol Lembhe on 27 Jun 2008 17:17
Hi,

This is an bug, problem when the database name has either a SPACE or a
HYPHEN.
You can try changing the DB name or try calling msdb.sys.sp_dbmmonitorupdate
with a DB Name parameter - so it can be run in a wrapper.

Regards,
Amol Lembhe

"JOH" wrote:

> SQL Server 2005
> Sharepoint with mirroring of all databases to DR box
>
> One of the Sharepoint databases has hyphens in it. This is causing an
> error in the Database Mirroring Monitoring Job. The error seems
> almost informational, or at least it is not apparently affecting the
> mirroring process. When I discontinue mirroring this one database,
> the Database Mirroring Monitoring Job stops issuing the error.
>
> When installing Sharepoint, we took care to use a GUID-less name for
> each database. For this one particular database, that option did not
> appear to be available, and we were forced to use the name with the
> GUID still in it. I know I can change the database name on SQL
> Server, but I don't know how that will affect Sharepoint. Is there a
> way to change that database name, through Sharepoint?
>
> What if Sharepoint cannot be edited so that the database name can be
> modified? Is there any way to modify the system stored procedure
> (that does the mirroring update) so that this hyphenated database name
> no longer causes an issue, such as by wrapping it in [] at the start
> of the procedure?
>
> TIA
>
>
> Here is the error, for those who are interested:
> Message
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '-'.
> [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword
> 'with'. If this statement is a common table expression or an
> xmlnamespaces clause, the previous statement must be terminated with a
> semicolon. [SQLSTATE 42000] (Error 319). The step failed.
>
From: JOH on 3 Jul 2008 16:14
On Jun 27, 5:17 pm, Amol Lembhe <amo...(a)gmail.com> wrote:
> Hi,
>
>  This is an bug, problem when the database name has either a SPACE or  a
> HYPHEN.
> You can try changing the DB name or try calling msdb.sys.sp_dbmmonitorupdate
> with a DB Name parameter - so it can be run in a wrapper.
>
> Regards,
> Amol Lembhe
>
> "JOH" wrote:
> > SQL Server 2005
> > Sharepoint with mirroring of all databases to DR box
>
> > One of the Sharepoint databases has hyphens in it.  This is causing an
> > error in the Database Mirroring Monitoring Job.  The error seems
> > almost informational, or at least it is not apparently affecting the
> > mirroring process.  When I discontinue mirroring this one database,
> > the Database Mirroring Monitoring Job stops issuing the error.
>
> > When installing Sharepoint, we took care to use a GUID-less name for
> > each database.  For this one particular database, that option did not
> > appear to be available, and we were forced to use the name with the
> > GUID still in it.  I know I can change the database name on SQL
> > Server, but I don't know how that will affect Sharepoint.  Is there a
> > way to change that database name, through Sharepoint?
>
> > What if Sharepoint cannot be edited so that the database name can be
> > modified?  Is there any way to modify the system stored procedure
> > (that does the mirroring update) so that this hyphenated database name
> > no longer causes an issue, such as by wrapping it in [] at the start
> > of the procedure?
>
> > TIA
>
> > Here is the error, for those who are interested:
> > Message
> > Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '-'.
> > [SQLSTATE 42000] (Error 102)  Incorrect syntax near the keyword
> > 'with'. If this statement is a common table expression or an
> > xmlnamespaces clause, the previous statement must be terminated with a
> > semicolon. [SQLSTATE 42000] (Error 319).  The step failed.

I tried this script, got the same error (syntax near '-') .. any
suggestions?

declare @mir_name sysname

set @mir_name = 'sharepoint-likes-hyphens'

exec sys.sp_dbmmonitorupdate @mir_name;

From: JOH on 10 Jul 2008 15:55
I was able to deal with this issue, but I'm not sure it's the right
way to fix it.

By the way, the use of a wrapper had no effect so far as I could
tell. I tried using square brackets and such to fool it, and that
didn't work. If anyone knows how to make the sp_dbmmonitorupdate
procedure accept a hyphenated name, please let me know. *update* Now
we see that it's the call to DBCC DBTABLE (in a referenced proc) that
is erring.

What I did to stop the error was to edit the Database Mirroring
Monitor Job. I edited the first step so that it calls the
sp_dbmmonitorupdate procedure for each of the mirrored databases which
do not have hyphenated names. I added a second step to the job, and
pasted in the code from the sp_dbmmonitorupdate procedure, specifying
the name of the database (the one with hyphens) but after commenting
out this one line [180]:
exec @retcode = sys.sp_dbmmonitorMSgetthelatestlsn @database_name,
@end_of_log_lsn output

This is what I'm concerned about. I can't find this stored procedure
anywhere in the system, so I have no way of really knowing what it's
doing. The outputted variable, @end_of_log_lsn, isn't used anywhere
in the code. If it is actually doing something, it appears to be
ensuring that the latest LSN *can* be found, but does nothing else.

Well, there it is .. tucked away in the MSSQLServerResource database.
And the real culprit is in these lines [36-42] ..

set @command = N'dbcc dbtable(' + replace(@database_name,
N'''',N'''''') + N') with tableresults, no_infomsgs'

declare @temp table(parentObject nvarchar(255),Object
nvarchar(255),field nvarchar(255),value nvarchar(255))
-- TO DO: get correct values from SteveLi
-- DONE: They are all nvarchar(255)
insert into @temp exec( @command )
select @str = value from @temp where field=N'm_flushLSN';


This code chunk generates a command that would like something like
this ..
dbcc dbtable ( db-name )

That doesn't fly, but if you use square brackets or quotes, you're
fine. This runs ..
dbcc dbtable ( 'db-name' )

I'm thinking that if Microsoft had wanted to ensure that database
names with spaces and hyphens would process correctly, a small change
to line 36 would have worked. Here's the current line ..
set @command = N'dbcc dbtable(' + replace(@database_name,
N'''',N'''''') + N') with tableresults, no_infomsgs'
And here's the change that would resolve the issue ..
set @command = N'dbcc dbtable(''' + replace(@database_name,
N'''',N'''''') + N''') with tableresults, no_infomsgs'


At this point, I think I'm going to re-work these two procedures into
one chunk of code, and paste that into the job step for monitoring
this particular database's mirroring. It would be nice if Microsoft
would fix this in the next service pack. Seems to me that this is a
tiny detail that could easily get slipped into any update.
From: Aaron Bertrand [SQL Server MVP] on 10 Jul 2008 17:02
Great digging JOH. I filed a bug on your behalf, please validate and vote:

connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=355764







On 7/10/08 3:55 PM, in article
60bf2c5f-79e4-4f19-852d-785234956c1b(a)t54g2000hsg.googlegroups.com, "JOH"
<JOHolloway(a)gmail.com> wrote:

> I was able to deal with this issue, but I'm not sure it's the right
> way to fix it.
>
> By the way, the use of a wrapper had no effect so far as I could
> tell. I tried using square brackets and such to fool it, and that
> didn't work. If anyone knows how to make the sp_dbmmonitorupdate
> procedure accept a hyphenated name, please let me know. *update* Now
> we see that it's the call to DBCC DBTABLE (in a referenced proc) that
> is erring.
>
> What I did to stop the error was to edit the Database Mirroring
> Monitor Job. I edited the first step so that it calls the
> sp_dbmmonitorupdate procedure for each of the mirrored databases which
> do not have hyphenated names. I added a second step to the job, and
> pasted in the code from the sp_dbmmonitorupdate procedure, specifying
> the name of the database (the one with hyphens) but after commenting
> out this one line [180]:
> exec @retcode = sys.sp_dbmmonitorMSgetthelatestlsn @database_name,
> @end_of_log_lsn output
>
> This is what I'm concerned about. I can't find this stored procedure
> anywhere in the system, so I have no way of really knowing what it's
> doing. The outputted variable, @end_of_log_lsn, isn't used anywhere
> in the code. If it is actually doing something, it appears to be
> ensuring that the latest LSN *can* be found, but does nothing else.
>
> Well, there it is .. tucked away in the MSSQLServerResource database.
> And the real culprit is in these lines [36-42] ..
>
> set @command = N'dbcc dbtable(' + replace(@database_name,
> N'''',N'''''') + N') with tableresults, no_infomsgs'
>
> declare @temp table(parentObject nvarchar(255),Object
> nvarchar(255),field nvarchar(255),value nvarchar(255))
> -- TO DO: get correct values from SteveLi
> -- DONE: They are all nvarchar(255)
> insert into @temp exec( @command )
> select @str = value from @temp where field=N'm_flushLSN';
>
>
> This code chunk generates a command that would like something like
> this ..
> dbcc dbtable ( db-name )
>
> That doesn't fly, but if you use square brackets or quotes, you're
> fine. This runs ..
> dbcc dbtable ( 'db-name' )
>
> I'm thinking that if Microsoft had wanted to ensure that database
> names with spaces and hyphens would process correctly, a small change
> to line 36 would have worked. Here's the current line ..
> set @command = N'dbcc dbtable(' + replace(@database_name,
> N'''',N'''''') + N') with tableresults, no_infomsgs'
> And here's the change that would resolve the issue ..
> set @command = N'dbcc dbtable(''' + replace(@database_name,
> N'''',N'''''') + N''') with tableresults, no_infomsgs'
>
>
> At this point, I think I'm going to re-work these two procedures into
> one chunk of code, and paste that into the job step for monitoring
> this particular database's mirroring. It would be nice if Microsoft
> would fix this in the next service pack. Seems to me that this is a
> tiny detail that could easily get slipped into any update.

 |  Next  |  Last
Pages: 1 2
Prev: Test format for MCITP (SqlServer2005): 70 - 444
Next: Incorrect syntax near '1' error from sql server 2005