Query works - sproc fails

I have a query that works fine but fails as a sproc.

QUERY:

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = 'Brown'

SPROC:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GetProfileId]

@.UserName nvarchar

AS

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = @.UserName

The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0

Set a size to the parameter. That might help.

|||

kerchunk!

That did it. Thanks!

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008
How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query wizard

in sql server 2000, table view, i can right click any table and invoke query
select wizard. At any time I can select spetial icon on toolbar and change
type of the query: select, insert from, insert into, update, delete. I
missed this type of functionality in sql server 2005 and 2008How about this?
Open up your SSMS and connect to your SQL Server instance.
Go and find your database and go to one of the tables. Right click on it and
go to Script Table as and so on...
Is this are you looking for?
Ekrem ?nsoy
"Aleks Kleyn" <AleksKleyn@.discussions.microsoft.com> wrote in message
news:29928A48-A418-49FE-8A08-835E9B1DD913@.microsoft.com...
> in sql server 2000, table view, i can right click any table and invoke
> query
> select wizard. At any time I can select spetial icon on toolbar and change
> type of the query: select, insert from, insert into, update, delete. I
> missed this type of functionality in sql server 2005 and 2008

Query to list all tables does not work in SQL Server 2005

I am trying to execute and SQL Query in SQL Server 2005 in order to return
all tables.
Whenever I execute this query:
select table_name from information_schema.tables
I receive this error each time:
Invalid object name 'information_schema.tables'.
Does anyone know what is happening? This seems to be a pretty standard
query to return all tables from what I have read.
--TR
SQL Server Version Info:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
Maybe you have case sensitive collation. Try this:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
Also, the query below should give you the same info:
SELECT O.name AS table_name
FROM sys.objects AS O
WHERE O.type = 'U';
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Is your database case sensitive? If so, you'll need to execute this query:
select table_name from INFORMATION_SCHEMA.TABLES
-Susan
"Ryan" wrote:

> I am trying to execute and SQL Query in SQL Server 2005 in order to return
> all tables.
> Whenever I execute this query:
> select table_name from information_schema.tables
> I receive this error each time:
> Invalid object name 'information_schema.tables'.
> Does anyone know what is happening? This seems to be a pretty standard
> query to return all tables from what I have read.
>
> --TR
> SQL Server Version Info:
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>

query syntax help

Folks,
Hi, I have the following query:
SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
[BytesSentPerSec] FROM tblHostCapacity001
It returns the following syntax error:
Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
'CardType'.
I understand the reasons for the error - i.e. I need to escape the square
brackets around the [R] in the fieldname...
BUT how do I accomplish this escaping of the [ and ] - normally these chars
delimit a space-contained fieldname. Haven't been able to find any solutions
on the web via google.
Appreciate any advice.
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kYou should really consider renaming the column to something sensible
following the rules of indentifiers. As for a short term workaround, use
double quotes (") instead of square brackets.
The actual alternative to escaping square brackets is to add another closing
square bracket after the existing one like:
CREATE TABLE tbl ( [\\host\NetItfc(Intel [R]] CardType\Bytes Sent/sec] INT )
Note the addition of ']' after [R] but not before it.
Anith|||See if this helps:
-- cast([\\host\NetItfc(Intel[R]] CardType\Bytes Sent/sec] AS float)
create table t1 (
[a[b]]c] int
)
go
select
cast([a[b]]c] as varchar) as c1
from
t1
go
drop table t1
go
AMB
"Neil Evans-Mudie" wrote:

> Folks,
> Hi, I have the following query:
> SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
> [BytesSentPerSec] FROM tblHostCapacity001
> It returns the following syntax error:
> Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
> 'CardType'.
> I understand the reasons for the error - i.e. I need to escape the square
> brackets around the [R] in the fieldname...
> BUT how do I accomplish this escaping of the [ and ] - normally these char
s
> delimit a space-contained fieldname. Haven't been able to find any solutio
ns
> on the web via google.
> Appreciate any advice.
> Cheers,
> Neil Evans-Mudie
> -. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
> ... -...- -- ..- -.. .. .
> e: My@.myorg.com address is a spam sink
> If you wish to email me, try neilevans underscore mudie at hotmail dot com
> w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=k
>
>