January 7, 2010 at 12:42 pm
Consider the following code snippet:
CREATE FUNCTION HPSITE.ETL_CAREGIVER_GetIdentifierValue (
@aCaregiverId [PROVIDERS.IMREPROV_CODE],
@aCaregiverIdentifierTypeCode int,
@aLocationId [LOCATION.LOCATION_ID]
)
RETURNS varchar(50)...
You will note the use of two user defined data types. Ignoring the nonsensical usage and nonsensical naming convention, the script that creates this function executes without a problem in SSMS; however, when it is executed through an ODBC connection it fails because it can't find the user defined data types. The person trying to run the script through ODBC has tried it using SQL security and with integrated security. The default schema for the SQL user is dbo and the schema for the user defined data types is dbo. As you can see, the function is being created in the HPSITE schema.
I've already recommended that he try a different provider than ODBC but; truthfully, I can't see that the provider would matter since the code is executing on the server anyway. Does anyone have any thoughts?
"Beliefs" get in the way of learning.
January 7, 2010 at 12:50 pm
What is the full error message that is being returned.
January 7, 2010 at 1:33 pm
You may Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
January 7, 2010 at 2:29 pm
is your app user connected to the correct database ???
(Always specify your wanted database at connect time, don't rely o "default database" behaviour)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2010 at 7:47 am
This is the error message being returned:
Column, parameter, or variable #1: Cannot find data type PROVIDERS.IMREPROV_CODE.
Column, parameter, or variable #3: Cannot find data type LOCATION.LOCATION_ID.
Parameter or variable '@aCaregiverId' has an invalid data type.
Parameter or variable '@aLocationId' has an invalid data type.
"Beliefs" get in the way of learning.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply