Problemm setting permissions

Hello everybody,
I ran into a strange kind of problem in SQL2K. I tryto give a user
select permissions on fn_trace_getinfo, but then I apply the new
permissions I get the message "Invalid object name
system_functionschema.fn_trace_getinfo". How is this possible. I see
the function and as sysadmin I can use it, but when you try to change
permissions it says the object doesn't exist ?
Does anybody know why this is happening and if there is some way
around.
Tia MarkusHi
fn_trace_getinfo is a system function.
What happaned if the user run
SELECT *
FROM ::fn_trace_getinfo(trace_id)
WHERE Property=4
<m.bohse@.quest-consultants.com> wrote in message
news:1136540572.050234.16160@.g14g2000cwa.googlegroups.com...
> Hello everybody,
> I ran into a strange kind of problem in SQL2K. I tryto give a user
> select permissions on fn_trace_getinfo, but then I apply the new
> permissions I get the message "Invalid object name
> system_functionschema.fn_trace_getinfo". How is this possible. I see
> the function and as sysadmin I can use it, but when you try to change
> permissions it says the object doesn't exist ?
> Does anybody know why this is happening and if there is some way
> around.
> Tia Markus
>

Problem with view

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.

Here is the t-sql code I used to create the view:

CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''

Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?When you have UNION the columns in the first SELECT statement define the
datatype of the columns. The columns in the following SELECT statements must
match the datatype or be implicitly convertible. In your case implicit
conversion from varchar to float is allowed. Then the problem is that a
column in your first SELECT statement is of type float and the same column
in the second SELECT is a varchar, but the varchar column has some
characters that cannot be converted to float.

Without seeing the table definitions and just guessing it seems that your
_tblLeads.Zip might be of type float and tblClients.Zip is of type varchar.
Most likely tblClients.Zip has some invalid characters that cannot be
converted to float. One solution is to replace the column
[dbo].[_tblLeads].[Zip] in your first SELECT (if that is the correct column)
with an explicit conversion to varchar, like CAST([dbo].[_tblLeads].[Zip] AS
varchar). To be more precise you can convert to varchar(n) where n is the
exact size of the column.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Hi
What are the field format at each table?
Harry

On Jan 23, 1:34 am, "Chris" <cjscu...@.gmail.comwrote:

Quote:

Originally Posted by

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
>
Here is the t-sql code I used to create the view:
>
CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''
>
Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?