MS SQL

MS SQL RSS

'CITY' is not a recognized optimizer lock hints option

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Sep 2006

Posts: 88

Reputation: bajanpoet is an unknown quantity at this point

Solved Threads: 0

bajanpoet

Offline

Junior Poster in Training

'CITY' is not a recognized optimizer lock hints option

Jan 27th, 2009

I have created two table valued multi-statement functions using SQL Server 2000 and am now trying to develop a VB.NET front end application where I run the functions and pass string parameters to them.

Below is a code fragment for one of the functions.

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
CREATE FUNCTION ViewCustomers(@Region VARCHAR(6))
RETURNS @CustTable TABLE (CustomerID VARCHAR(20), [Customer Name] VARCHAR(50))
AS
BEGIN
	IF @Region = 'West' 
	 BEGIN
		INSERT @CustTable
		SELECT RTRIM(idcust)[Customer ID], 
                RTRIM(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('4000229', '4002100', '60000', '41700', '98850', 'T00038',
		'7422', 'S00046', '0092375', '0109550', '94770', '0032250',
		'S00049',	'4001118', 'H06830', 'P00060', '5258', '3655',
		'3745', 'SS1107', '5127', '4001820',	'4000926', 'SS1108',
		'R02430',	'P00048',	'4002171', '4001506', 'ZF0017',
                'E00038',	'J00017',	'R06004',	'4000500', 'ZC0060',  
                'C15093',	'ZS0076')
	 END
	ELSE IF @Region = 'South'
     BEGIN
		INSERT @CustTable
		SELECT RTRIM(idcust)[Customer ID], 
                RTRIM(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('G00088' ,'4000360', '4008005', '4001892', 'D04905',  
                 'ZA0009',   '4000310', 'D04895', 'E00041', 'E00014',  
                 'E00024', '7643',  '4000850', '73500', '4000935',    
                 '4000936', '4001300', '86100',  'P00059', 'P00031', '4665', 
                 '4002043', '4001444', '0098690', '1082', '98900', 
                 'S00051', '4342', 'SS1104', '17800', '110400',  '1094',  
                 '4002041', '0092000', '4470', 'ZI0001', '0025300',  
                 'ZC0100',  'ZF0035', 'R06010')
    END
ELSEIF.....
....
END
CREATE FUNCTION ViewCustomers(@Region varchar(6))
returns @CustTable TABLE (CustomerID varchar(20), [Customer Name] varchar(50))
AS
BEGIN
	IF @Region = 'West' 
	 BEGIN
		INSERT @CustTable
		SELECT rtrim(idcust)[Customer ID], 
                rtrim(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('4000229', '4002100', '60000', '41700', '98850', 'T00038',
		'7422', 'S00046', '0092375', '0109550', '94770', '0032250',
		'S00049',	'4001118', 'H06830', 'P00060', '5258', '3655',
		'3745', 'SS1107', '5127', '4001820',	'4000926', 'SS1108',
		'R02430',	'P00048',	'4002171', '4001506', 'ZF0017',
                'E00038',	'J00017',	'R06004',	'4000500', 'ZC0060',  
                'C15093',	'ZS0076')
	 END
	ELSE IF @Region = 'South'
     BEGIN
		INSERT @CustTable
		SELECT rtrim(idcust)[Customer ID], 
                rtrim(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('G00088' ,'4000360', '4008005', '4001892', 'D04905',  
                 'ZA0009',   '4000310', 'D04895', 'E00041', 'E00014',  
                 'E00024', '7643',  '4000850', '73500', '4000935',    
                 '4000936', '4001300', '86100',  'P00059', 'P00031', '4665', 
                 '4002043', '4001444', '0098690', '1082', '98900', 
                 'S00051', '4342', 'SS1104', '17800', '110400',  '1094',  
                 '4002041', '0092000', '4470', 'ZI0001', '0025300',  
                 'ZC0100',  'ZF0035', 'R06010')
    END
ELSEIF.....
....
END

When I run the function in SQL Server Query Analyzer, using SELECT * FROM ViewCustomers('City') it works and displays the customerID and name of the customers associated with the region passed to the function as a parameter.

My next step is to run the function through VB.NET using this code

 Help with Code Tags 
 VB Syntax (Toggle Plain Text) 
    Private Sub ViewCustomers()
        conn.Open()
        Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers(" & vRegion & ")", conn)
        Dim r As SqlDataReader()
        r = SQLComm.ExecuteReader()
        dgCustomers.DataSource = r
        conn.Close()
    End Sub
    Private Sub ViewCustomers()
        conn.Open()
        Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers(" & vRegion & ")", conn)
        Dim r As SqlDataReader()
        r = SQLComm.ExecuteReader()
        dgCustomers.DataSource = r
        conn.Close()
    End Sub

So when I'm trying to run the solution to test the code, the error pointer points to the r = SQLComm.ExecuteReader() line and I get the error ''CITY' is not a recognized optimizer lock hints option". I'm trying to populate a datagrid with data in the function's @CustTable.

How do I get rid of this Optimizer lock hints option error?

(BTW - the original request was placed in the VB.NET forum because the error was in the r = SQLComm.ExecuteReader() line, but I was advised to try here as well.)

Last edited by bajanpoet; Jan 27th, 2009 at 9:16 am. Reason: Making sure the formatting was right.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: 'CITY' is not a recognized optimizer lock hints option

Jan 27th, 2009

If you traced the SQL Command sent, you'll find it doesn't surround the state within single quotes '
What you need is to modify it
Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Sep 2006

Posts: 88

Reputation: bajanpoet is an unknown quantity at this point

Solved Threads: 0

bajanpoet

Offline

Junior Poster in Training

Re: 'CITY' is not a recognized optimizer lock hints option

Jan 27th, 2009

wow... I didn't see that... you're right! I'll modify it and get back to you... give me a sec...

•

Join Date: Sep 2006

Posts: 88

Reputation: bajanpoet is an unknown quantity at this point

Solved Threads: 0

bajanpoet

Offline

Junior Poster in Training

Re: 'CITY' is not a recognized optimizer lock hints option

Jan 27th, 2009

I seem to have gotten past that problem... now it says

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
InvalidCastException
Unable TO cast object of type 'System.Data.SqlClient.SqlDataReader' TO type 'System.Data.SqlClient.SqlDataReader[]'.'
 
InvalidCastException
Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.Data.SqlClient.SqlDataReader[]'.'

looking at the same ExecuteReader command r = SQLComm.ExecuteReader()

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: 'CITY' is not a recognized optimizer lock hints option

Jan 27th, 2009

It works fine

 Help with Code Tags 
 vb.net Syntax (Toggle Plain Text) 
Dim r As SqlClient.SqlDataReader
r = SQLComm.ExecuteReader()
dgCustomers.DataSource = r
Dim r As SqlClient.SqlDataReader
r = SQLComm.ExecuteReader()
dgCustomers.DataSource = r