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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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