Permissions to run a SQL statement

Question

Post reply

Permissions to run a SQL statement

S Hodkinson

SSCertifiable

Points: 7529
More actions
December 14, 2006 at 4:37 am

#98021

We have a database which has moved to a new server. On the old server and on one database, one SQL statement was run as sa.
On the new server, that same statement will be run as another login which has dbowner permissions. However the statement returns an error
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
Does anyone have any ideas?

Madame Artois

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736478

December 14, 2006 at 6:44 am

#678059

What's the statement?

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 2

The statement is:

rs.open"SELECT * FROM rai_ProbIdea where (("&filter&" = '"&SearchOn&"') and ((status <> 'DECISION') and (status <> 'INAPPROPRIATE') and (DATEDIFF(d, CONVERT(datetime, submit_month + '/' + submit_day + '/' + submit_year), getDate()) < 730)))", application("pConnect"), adopenkeyset record_count = rs.RecordCount rs.close

It was written by our famous programmer who wrote all sorts of odd stuff then left!!

Madame Artois

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Something else should have changed. This doesn't seem like a security issue, but more of a connection/ADO problem.

Apart from being poorly written.

If you run the query (parameters subbed) from Query Analyzer, logged in as the dbowner account, does it work?

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 4

I shall discuss with the programmer soonest; he's got someone with him and I haven't got a clue what it is supposed to show.

I stick to DBA; originally the programmer thought there was a problem with the database which there isn't.

I'll be back soon.

Madame Artois

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 5

The programmer ran the code

The actual query (VB replaced) is

SELECT * FROM rai_ProbIdea where ((handler = 'ACFO(FS)') and ((status <> 'DECISION') and (status <> 'INAPPROPRIATE') and (DATEDIFF(d, CONVERT(datetime, submit_month + '/' + submit_day + '/' + submit_year), getDate()) < 730)))

and got the result.

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

Madame Artois

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 6

We delved further with this and discovered that it was the old issue; American dates versus English dates. The programmer re-wrote the select statement. This got him pass the error only to find another one.

Microsoft][ODBC SQL Server Driver][SQL Server]Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.

He is trying to populate a table; again it works on the old server but not on the new.

Madame Artois

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

Thanks for the update and good luck with that. Sounds like you're on the right track now.

vadba SSChampion Points: 11133 More actions · Answer 8

Are you sure that query is causing the latest error? The query you posted does not use any full-text functionality.

Also, to avoid any regional issues with datetime constants, I'd suggest using the form yyyymmdd, like this:

SELECT CONVERT(datetime, @submit_year,4) + Right('00' + @submit_month,2) + Right('00' + @submit_day,2))

This assumes submit_year is a four-digit year.

vadba SSChampion Points: 11133 More actions · Answer 9

Just in case you need to enable full-text searching on the new database/server, you'll need to do something along these lines:

USE <your database>

EXEC sp_fulltext_database 'enable'

EXEC sp_fulltext_catalog '<your_FT_CatName>' , 'create'

EXEC sp_fulltext_table '<your Table>', 'create', '<your_FT_CatName>, 'primaryKeyCol'

EXEC sp_fulltext_column '<your Table>' , '<text column>' , 'add'

EXEC sp_fulltext_catalog '<your_FT_CatName>', 'start_full'

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 10

Dear all

Thanks for keeping going whilst I was on my Christmas break. We are nearly there! The programmer is re-writing the old code so I will need to check with him; I'll pass him the full text search stored procedures.

We're got a couple more bits to sort but I'll start them as new threads.

Madame Artois