Hi All,
We are implementing SharePoint 2007 for an internal document management project. I'm having an issue with configuring the Windows SharePoint Services Search Service.
I enter my domain user accounts, DB Server and DB Name which is all OK. I attempt to start the service and receive 'Could not access the Search service configuration database'
There is nothing in the SharePoint logs which suggest any problem, however when I profile the SQL Server, I get the following error:
Error: 102, Severity: 15, State: 1
The code in question is calling a SP:
declare @p2 nvarchar(64)
set @p2=N'C:\Program Files\Microsoft Office Servers\12.0\Data \Applications'
exec dbo.proc_MSS_GetConfigurationProperty
@Name=N'indexLocation',@Value=@p2 output select @p2
When I run this snippet through SSMS, I get:
Msg 257, Level 16, State 3, Procedure
proc_MSS_GetConfigurationProperty, Line 0 Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
The table structure is:
CREATE TABLE [dbo].[MSSConfiguration](
[Name] [nvarchar](64) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
[Value] [sql_variant] NULL,
[BigValue] [image] NULL,
[LastModified] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The Proc is
CREATE PROCEDURE [dbo].[proc_MSS_GetConfigurationProperty]
(
@Name nvarchar(64),
@Value sql_variant OUTPUT
)
AS
SELECT @Value = Value
FROM dbo.MSSConfiguration WITH(NOLOCK)
WHERE Name = @Name
When I run the following script:
SELECT
Value
FROM
dbo.MSSConfiguration WITH(NOLOCK)
WHERE
Name = 'indexLocation'
It returns the value as expected, so it seems a mismatch between the data-types of SQL_Variant and nvarchar(64) when outputting the results.
Has anyone else had this or can you offer a solution
Thanks in advance.
Steve
MOSS 2007 + Win SPoint Svcs Srch + Implicit Conversion Error
imj
heh, If you look at the value of the assigned parameter it is nvarchar(64):
set @p2=N'C:\Program Files\Microsoft Office Servers\12.0\Data \Applications'
The string is 65 characters long. When it assigns it to the column the sql engine tries to convert it and fails.
So the path is too long for the variable. Heh...
David
MaheshBabu
MueMeister
I am having the same issue. This solution that davidwilk states does not help in the least. Yes, it is a string that is 65 chars long and, yes, the variable only will store 64 but this sql is fired from sharepoint configuration.
Does anyone else have an actual solution to this problem without stating the obvious.
Thanks