global code: msg

Showing posts with label msg. Show all posts

Friday, March 9, 2012

Function Returns Data Type Error

I am writing my first function and it should be be a very simple one but
I am getting the error:
Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
Syntax error converting the varchar value 'N' to a column of data type int.
Below is the funtion and then the sleect statement that causes the error
----
CREATE FUNCTION InvTypeOther (@.InvoiceID int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @.Type varchar(3)
select @.Type=
(Case InvoiceType.Name
When 'IN' Then 'N'
Else 0
End)
FROM Invoice
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
WHERE (Invoice.ID = @.InvoiceID)
Return @.Type
END
--
Select dbo.InvTypeOther(ID) from Invoice where id = 2525Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||your CASE expression is using type precedence to try to convert 'N' to
the 0 in the else.
not sure what it should be, but it shouldn't be an int. :)
either '0' or '' perhaps [or null]
Mike Harbinger wrote:
> I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type int
.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>|||That was it, thanks guys!
I am used to another programming language where numbers do not have to be
quoted when used in string variables.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OyGttQyEGHA.524@.TK2MSFTNGP09.phx.gbl...
>I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type
> int.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>

Sunday, February 26, 2012

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIA
Did you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/2909...22120121120120 for
more information.
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
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA
|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:

> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/2909...22120121120120 for
> more information.
> --
> 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
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
more information.
--
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
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
> more information.
> --
> 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
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> > Hi,
> > My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> > How do i fix it or do i need to reinstall?How?
> >
> > This is the error msg i get.What file is it looking for?By replacing the
> > file, can i get it back online?
> >
> > "An error occurred during the online operation for instance <SQL Server
> > Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> > specified."
> >
> > TIA
>
>

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified
."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290...122120121120120 for
more information.
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
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:

> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290...122120121120120 f
or
> more information.
> --
> 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
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>

Friday, February 24, 2012

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un

Hello and thank you for your assistance.

I have two instances of SQL Server 2000 Enterprise Edition (SP4) database running on Windows 2000 Enterprise server. The default instance is dev and a named instance for QA. Full-text search is enabled for dev and is functioning properly.

@.@.Version:

I am trying to enable full text search in the QA instance with this command: "sp_fulltext_database 'enable' ", but I am receiving the error "Server: Msg 7635 The Microsoft Search service cannot be administered under the present user account.

I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?

This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.

1. Did this error occur after a service pack upgrade? If so - review the files and folders under \FTDATA (in the sql server installed instance folder) against what is on your SQL Server 2000 CD. If you find any difference - copy the missing or different file from the CD to the problem server.

2. Have you removed the BUILTIN\Administrators login? If so - either add it back in or execute the sp_grantlogin from the code below as the MSSearch service requires this level of access to the MSSQLServer service.

3. Check these:
317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/kb/317746
277549 PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through Control Panel
http://support.microsoft.com/kb/277549

4. Can you run this script and post the results back?

use master
go
SELLECT @.@.version
go
-- many need to set on "show advanced options"
sp_configure 'default full-text language'
go
SELECT FullTextServiceProperty('ResourceUsage') as MSSearch_Resource_Usage
go
xp_logininfo 'BUILTIN\ADMINISTRATORS', 'members'
go

-- if you have removed the BUILTIN\Administrators login, run:

exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin

5. And just to cover all the bases, when you changed the MSSQLServer startup account, did you do it via Win2K's Component Services? If so, could you "re-change" it via the Enterprise Manager's server property security tab?

Filip Skakun

|||

Filip,

Thank you so much for your response. To answer your questions; No we haven't added any service packs recently. Yes, we do have the Builtin/Administer removed and the NT Authority has all the required privileges. Keep in mind that the default instance was working just fine.

Since the MSSQL$Inst2/FTData directory didn't exist; I suspect that FullText search was not installed with the second instance. None the less, I re-installed fulltext search (see http://support.microsoft.com/kb/827449). After doing this I no longer received the "Cannot be administered under the present account" error and the FTData directory was created for the second instance. But, at this point Full text search didn't work for both instances. I wasn't receiving any errors, but the catalogs were not getting populated. We later found that the switch in the KB article was incorrect for our servers. The article had us run "ftsetup.exe SQLServer$Instance 1 0 0 0" (incorrect). After we ran "ftsetup.exe SQLServer$Instance 0 1 0 0 0" (correct) FullText search began to function correctly for both instances.

To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.

|||

I am glad you got it working,

Regards,
Filip

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un

Hello and thank you for your assistance.

@.@.Version:

I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?

This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.

4. Can you run this script and post the results back?

-- if you have removed the BUILTIN\Administrators login, run:

Filip Skakun

|||

Filip,

To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.

|||

I am glad you got it working,

Regards,
Filip

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administere

Hello and thank you for your assistance.

@.@.Version:

I can find any reference to what account it needs to run under. Can you provide me with info on why I am getting this message?

This is a common error with SQL Server 2000 Full-text Search (FTS) and the MSSearch service and there can be a few root reasons for it.

4. Can you run this script and post the results back?

-- if you have removed the BUILTIN\Administrators login, run:

Filip Skakun

|||

Filip,

To be honest, I don't think I had to reinstall everything. I believe I could have gotten away with just running ftsetup.exe for the second instance.

|||

I am glad you got it working,

Regards,
Filip

Friday, March 9, 2012

Function Returns Data Type Error

Sunday, February 26, 2012

FullTextSearch

FullTextSearch

FullTextSearch

Friday, February 24, 2012

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administered un

Full-Text Search Error - Server: Msg 7635 The Microsoft Search service cannot be administere

global code

Blog Archive

About Me