Underscore & Sp_columns ?!!

Oct 9, 2000

Hi friends,

I got troubles when I tried to pass table name to sp_columns via
@local_variable:

declare @tname as char(50)
set @tname = 'TB_MyTable'
exec sp_columns @tname

result of such script is:

TABLE_QUALIFIER ...[skipped]
-----------------------------------
(0 row(s) affected)

But if I will say:

exec sp_columns 'TB_MyTable'

result will be correct (all neccessary data about columns will be provided)

So, looks like if I pass table name which contains underscore via
@local_variable or SP parameter - result will be wrong.
If table name won't contains underscore - everything works fine and result
of script:

declare @tname as char(50)
set @tname = 'MyTable'
exec sp_columns @tname

will be absolutely correct.

Please, anybody can clarify this situation ???

Thanks in advance,

Michael

View 2 Replies


ADVERTISEMENT

Sp_columns Not Returnign Anything

Oct 13, 2006

Greetings all. I have a bizarre problem. I'm using sp columns to generate an html table with the field information of certain tables in my database. The table names are stored in an SQL Table called 'Manage_Tables' and I have 9 tables shown there. 6 out of the 9 are displaying correctly but the other 3 arent and the problem is coming from teh sp columns procedure that is returning nothing. why does this happen? all the tables are the same and have been created on teh same day by the same user with the rame rights and I have admin rights. Any clue?

View 1 Replies View Related

How To Get One Column From Sp_columns

Feb 27, 2008

Hi,
When I run sp_columns @table_name = someTable, I get many columns like TABLE_QUALIFIER, TABLE_OWNER and so on.
Is there a easy way to get only a COLUMN_NAME column?
Thanks 

View 2 Replies View Related

Sp_columns Into A Variable

Mar 23, 2004

Hello,
I'm using sp_columns and would like to stock the result recordset into a variable (or simply get only the column name into a table variable in sql)

Is there any way to select only the column name and put it into a table variable ?

Thank

View 2 Replies View Related

Sp_columns Slow

Dec 3, 2007

In SQL 2005, I'm getting very slow response times with sp_columns. It takes
around 8 seconds to return the results of calling sp_columns.

Any ideas?

Thanks

-Dave

View 3 Replies View Related

Getting Rid Of Everything After Underscore?

Sep 11, 2013

I have data that looks like this:

CAT_DOG
MICE_CAT
RABBIT_DOG
LION_MICE

I want a select clause that will give me just this:

CAT
MICE
RABBIT
LION

View 5 Replies View Related

Trying To Find The Underscore ...

Aug 17, 2004

I'm attempting to doing a search of the db for all name entries with the underscore character "_".

I use %_% and get all the entries.

What am I doing wrong?

Help .... thanks!

View 2 Replies View Related

Using Sp_columns Stored Procedure Trouble

May 12, 2005

Hello,

I'm trying to use the "sp_columns" stored procedure to pull in some
information about a table in my db, but I'm continuing to get the same
error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_columns' expects parameter '@table_name', which was not supplied.

I'm not sure why, but I've re-written my code several times and can't
figure out just why this error is happening.  Here's a snippet of
the code:

Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String
        Dim con As New
OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection"))
        Dim com As New OdbcCommand("sp_columns", con)
        com.CommandType = CommandType.StoredProcedure

        Dim param As New OdbcParameter("@table_name", table)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        param = New OdbcParameter("@column_name", columnName)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        Dim dr As OdbcDataReader
        Dim name As String

        con.Open()
        dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR
        While dr.Read
            name = dr("TYPE_NAME")
        End While
        dr.Close()
        con.Close()

        Return name
    End Function

Any suggestions would be appreciated.

View 3 Replies View Related

Store Return Result From Sp_columns

Oct 31, 2007

I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah

View 1 Replies View Related

Using LIKE With Underscore And Forward Slash..

Jul 23, 2005

Hello All,DDL Statements:CREATE TABLE [dbo].[Table1] ([MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOI have a varchar column which represents dates in YYYYMMDD and MM/DD/YYformats. If I query using:SELECT MyDate FROM Table1WHERE DOB LIKE '________'Why aren't the dates in MM/DD/YY returned ? Is the / a specialcharacter in T-SQL ?Thanks in advance

View 1 Replies View Related

Export Sp_columns Result Into A Temp Table?

Mar 1, 2004

Can anyone show me the syntax for exporting the result of sp_columns into a new table?

I've tried using "SELECT EXEC sp_columns...INTO 'column_info'" but I keep getting syntax errors that are unclear to me.

Any help would be greatly appreciated.

ab

View 3 Replies View Related

Find Literal Underscore - Not Wildcard

Mar 21, 2005

In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:

SELECT * FROM foo WHERE bar like 'a\_%'

where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?

I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )

Any other suggestions?

View 1 Replies View Related

Red Underscore Under Procedure Name - Invalid Object Name

Jun 25, 2014

I have made a stored procedure, it makes successfully when created, but when I open it in modify, there is a red underscore under the procedure name.

When I place the courser over the red underscore, the massage is "Invalid object name"

View 1 Replies View Related

Transact SQL :: Get Substring Of File Name Up To Last Underscore

Apr 22, 2015

I have filenames and I want to get a substring of the file name up to the last underscore if it exists and if not up to the last period.

Create table FileNames (fileName varchar(40))
insert into FileNames values ('this_is_a_long_filename.ext')
insert into FileNames values ('this_is_an_even_longer_filename.ext')
insert into FileNames values ('short_filename.ext')
insert into FileNames values ('supershort.ext')

What I want returned is:

this_is_a_long
this_is_an_even_longer
short
supershort

View 5 Replies View Related

Sp_columns In SQL2K5 Gives No Rows For Function That Return Table

May 9, 2006

Some automated tools use sp_columns to find out the columns for a table, view or UDF that returns table.

In SQL Server 2000 it gives columns back but in SQL Server 2005 it does not, compat level set at 80 and 90.

Does anyone have an idea what id going on here?

Repro script below. I expect the script to return information about the two columns in the table defined in fnTestColumnsFromFunctions().

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

create function dbo.fnTestColumnsFromFunctions()
returns @TestTable table
(
ttID int,
ttName varchar(50)
)
as
begin
return;
end
go

declare @dbname sysname;
declare @n int;
set @dbname = db_name();
exec @n = dbo.sp_columns
@table_name = 'fnTestColumnsFromFunctions',
@table_owner = 'dbo',
@table_qualifier = @dbname,
@column_name = null,
@ODBCVer = 2;

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

View 7 Replies View Related

Win NT Integrated Logging: User Names Have Underscore(_)

Jan 8, 1999

I want to establish logging using NT Security. The problem is that user names in NT have underscore(_). When I establish NT Scurity in SQL security manager, SQL Server tries to add blank names as login id and they will not go through. Is it manadatory to remove underscore from username?

Because of structrual hierarchy, I have different groups in NT to be mapped to SQL Server groups and each group has different permission on SQL tables. In some cases we might have same users in more than one group. I read that it is not possible if you use NT integrated security. Is there any thing that can be worked around this and is this problem still exists in SQL 7.

Thank you in advance
Ali Malekshahi

View 3 Replies View Related

How To Write A Function For MS SQL 2005 To Save Value After Underscore?

Apr 14, 2008

Hi Gurus,

How do I write a T-SQL function for a string before a underscore is found?


Thanks Gurus.

View 2 Replies View Related

SQL Server 2008 :: Find Characters Before And After Underscore

Sep 22, 2015

I have data like ABC_Tablename_12345, i just want everything before and after _ also if there is no underscore in the name just return the name.

View 2 Replies View Related

Full-text Search----searchable Term With Underscore Deliminator

Jun 19, 2008

Hello,
I want to search a column with all the words deliminate by underscore. E.g. User_id, Community_name, author_id and etc.
It seems like freetext only deal with string with blank deliminator. How should I do the rull text search on column like this? Here is the code.declare @var varchar(2000)
set @var = 'id'
select [name], definition,version_code
from dbo.base
where freetext([name],@var)
thx

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved