Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / July 2008

Tip: Looking for answers? Try searching our database.

hyphen in sharepoint database name

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JOH - 27 Jun 2008 15:25 GMT
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.
Amol Lembhe - 27 Jun 2008 22:17 GMT
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

> SQL Server 2005
> Sharepoint with mirroring of all databases to DR box
[quoted text clipped - 27 lines]
> xmlnamespaces clause, the previous statement must be terminated with a
> semicolon. [SQLSTATE 42000] (Error 319).  The step failed.
JOH - 03 Jul 2008 21:14 GMT
> Hi,
>
[quoted text clipped - 37 lines]
> > 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;
JOH - 10 Jul 2008 20:55 GMT
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.
Aaron Bertrand [SQL Server MVP] - 10 Jul 2008 22:02 GMT
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@t54g2000hsg.googlegroups.com, "JOH"

> I was able to deal with this issue, but I'm not sure it's the right
> way to fix it.
[quoted text clipped - 57 lines]
> 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.
Aaron Bertrand [SQL Server MVP] - 10 Jul 2008 23:59 GMT
Due to more serious implications, I have marked the bug as private.
However, it seems that this issue has been resolved by CU5, which you can
get here:

http://support.microsoft.com/kb/943656

You can get more information about this specific problem here:

http://support.microsoft.com/KB/945907
JOH - 11 Jul 2008 06:03 GMT
On Jul 10, 6:59 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> Due to more serious implications, I have marked the bug as private.
> However, it seems that this issue has been resolved by CU5, which you can
[quoted text clipped - 5 lines]
>
> http://support.microsoft.com/KB/945907

Thanks!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.