Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 21st, 2007, 12:56 PM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default trying to run an udf

Hello,

I have and UDF called "Normalizar":

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Normalizar(SqlString AText)
    {
        return new SqlString(NormalizeStr((String)AText));
    }


I am trying to execute it:

select dbo.Normalizar(user);

but next message appears:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Normalizar", or the name is ambiguous.

Anybody can help? thanx

Reply With Quote
  #2 (permalink)  
Old July 21st, 2007, 08:14 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

GRANT EXECUTE ON dbo.Normalizar TO PUBLIC ?

--Jeff Moden
Reply With Quote
  #3 (permalink)  
Old July 22nd, 2007, 05:50 AM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, but I tried it:

Cannot find the object 'Normalizar', because it does not exist or you do not have permission.
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Normalizar", or the name is ambiguous.


I tried also to see the function using SQL Server Management Express, but I didn't find it.

Reply With Quote
  #4 (permalink)  
Old July 22nd, 2007, 06:10 AM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My project is an Sql Server 2005 project. I have the default debug script "test.sql", which contains the grant and select commands, and also all the table creates that works fine, and I can locate with the Sql Server Management Express.

My UDF is a C# file: "Normalizar.cs", but it seems the deployment doesn't build the function in the server, or I don't know how to see it.

Reply With Quote
  #5 (permalink)  
Old July 22nd, 2007, 08:39 AM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I finally succeded. The problem was that my initial catalog in the project's DB connection was the master database. And I created the assembly in this database, but my test.sql also creates and uses a new database, and cannot find the UDF in this DB.

After changing the connection, I can use my UDF in my own database.

Thanks

Reply With Quote
  #6 (permalink)  
Old July 22nd, 2007, 08:43 AM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

But now I have other problem.

My connection:

Data Source=MOSSA-DESA\SQLEXPRESS;Initial Catalog=CDBX;Integrated Security=True

Part of my test.sql code:

USE master;

IF DB_ID (N'CDBX') IS NOT NULL
DROP DATABASE CDBX;

-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE CDBX ON
( NAME = CDBX_dat,
    FILENAME = '''+ @data_path + 'CDBX.mdf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = CDBX_log,
    FILENAME = '''+ @data_path + 'CDBX.ldf'',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )'
);


My new error:

Cannot drop database "CDBX" because it is currently in use.
Database 'CDBX' already exists.


¿¿???

Reply With Quote
  #7 (permalink)  
Old July 22nd, 2007, 09:04 AM
Authorized User
 
Join Date: Nov 2003
Location: Mataro, Barcelona, Spain.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Really, it's not very important, because I can drop and re-create all the tables without problem.

Reply With Quote
  #8 (permalink)  
Old July 22nd, 2007, 12:12 PM
Friend of Wrox
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 473
Thanks: 0
Thanked 7 Times in 7 Posts
Default

Thanks for the feedback on the UDF problem solution.

--Jeff Moden
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
UDF references DavidReese BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 November 2nd, 2008 11:04 AM
how can i execute SQL in UDF vinod_mnr SQL Server 2000 1 March 18th, 2005 12:51 PM
UDF: allowed in one DB but not another??? ea SQL Server 2000 2 January 18th, 2005 03:14 PM
Excel Cell Limitations for UDF pagates Excel VBA 1 November 9th, 2004 04:48 AM
UDF and Stored Procedures hrishimusale SQL Server 2000 1 November 4th, 2003 12:07 PM



All times are GMT -4. The time now is 03:15 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.