ODBC Sql Server Query Error

Hi,

I'm attempting to use the SQLExecute function to make a query to a SQL Server DB through ODBC.

However, the column I am trying to retrieve is called "date". When I try and query, I get back the message, "S0022: Invalid column name 'date'".

I believe I'm getting the error because date is a sql keyword (however if I run the query directly through SQL Interactive, I do not get a problem).

If anyone knows how I can get around this problem, that would be very helpful, as it is not possible to change the column name.

Thanks in advance.
Khalid.I'd use brackets around the offending columns. Something like:SELECT [date] FROM naughtyTable-PatP|||Gave that a try -- gives the same error. Any other ideas?

Thanks,
Khalid.|||try it with doublequotes

select "date" from naughtyTable|||you could also try functionizing it
select dateadd(d,0,[date]) as mydate from naughtyTable|||That will just return "date" for every row.

Post your query.

I have a sneaky suspicion something else is going on here like maybe the column does not exist or there is some imprperly formatted inline SQL.

Otherwise what Pat said should have worked.|||That will just return "date" for every row.wrong

you may try it for yourself --create table datetest
( id tinyint not null primary key
, date datetime
)

insert into datetest (id, date) values ( 1, getdate() )
insert into datetest (id, [date]) values ( 2, '11/11/2005' )
insert into datetest (id, "date") values ( 3, '2005-01-19' )

select id, "date" from datetestwant to know what this produces?

1 2005-01-18 15:32:29.163
2 2005-11-11 00:00:00.000
3 2005-01-19 00:00:00.000|||Alright you win this round Batman.

I was thinking about single qoutes which would spit out Date for every row. I have to stop posting at this during the day. I start getting a little flitty.

ODBC insert of SQL_TYPE_TIMESTAMP

Hi,
I'm trying to insert a time stamp into SQL Server (2005) and can't figure
out the right combination of column type and arguments to SQLBindParameter()
.
My C code currently looks like this:
rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
(SQLPOINTER)&tstamp, 0, NULL);
I've tried different values for the ColumnSize and DecimalDigits parameters,
including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
As for the table definition, I've tried DateTime, char(10), char(19), etc.
Different combinations either give me return codes of 22008 or 42S02.
I definitely need to insert into a single column, as I'm trying to maintain
compatibility with an equivalent DB2 database (which, if it helps, uses the
DB2 TIMESTAMP data type, and has no problem with the above code).
Info on the web for this problem is surprisingly scarce. I've found a few
things but nothing concrete - so I'd really appreciate any help.
Thanks in advance!
Regards,
Josh"LDJ" <LDJ@.discussions.microsoft.com> wrote in message
news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> Hi,
> I'm trying to insert a time stamp into SQL Server (2005) and
> can't figure
> out the right combination of column type and arguments to
> SQLBindParameter().
> My C code currently looks like this:
> rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT,
> SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 10, 0,
> (SQLPOINTER)&tstamp, 0, NULL);
> I've tried different values for the ColumnSize and
> DecimalDigits parameters,
> including 10, 19, 22, 23, 26, and SQL_TIMESTAMP_LEN.
> As for the table definition, I've tried DateTime, char(10),
> char(19), etc.
> Different combinations either give me return codes of 22008 or
> 42S02.
This is probably a little more than you want to see, but ...
We use our own DB objects to encapsulate ODBC access to Oracle,
DB2 and SQL Server (2000). Therefore, some extra code you won't
recognize. But, I think you're just looking for the Bind.
Please see below.
////////////////////////////////////////////////////////////////////////////
/
//
void VParameter::PutDateTime( VDateTime dateTime )
{
SQLRETURN rc;
USHORT msec;
m_LenInd = sizeof( m_timeStamp );
dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
m_timeStamp.day );
dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
m_timeStamp.second, msec );
m_timeStamp.fraction = 0;
rc = SQLBindParameter( m_hStmt, m_paramNumber,
SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
&m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
CHECK_STMT( m_hStmt, rc );
}
m_timeStamp is just the SQL timestamp struct. A VDateTime is our
own object, based on midnight Dec. 30, 1899 (that's a standard).
m_* are class variables.
Good luck,
- Arnie|||Hi,
This is what I used for my SQLBindParameter and it's working, so hope it'll
help you!
SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP,
0, 0, (SQLPOINTER)&value, 0, &cbSize);
Note: columnSize and bufferLength are ignored when the type is
SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignored
for timestamps...
I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
Hope this helps,
Isabella
P.S. you can check out the following on the function if you haven't already:
http://msdn.microsoft.com/library/d...ndparameter.asp
"Arnie" wrote:

> "LDJ" <LDJ@.discussions.microsoft.com> wrote in message
> news:37FA6F9F-2352-4F86-B79C-6C262D8099A0@.microsoft.com...
> This is probably a little more than you want to see, but ...
> We use our own DB objects to encapsulate ODBC access to Oracle,
> DB2 and SQL Server (2000). Therefore, some extra code you won't
> recognize. But, I think you're just looking for the Bind.
> Please see below.
> //////////////////////////////////////////////////////////////////////////
///
> //
> void VParameter::PutDateTime( VDateTime dateTime )
> {
> SQLRETURN rc;
> USHORT msec;
> m_LenInd = sizeof( m_timeStamp );
> dateTime.DecodeDate( m_timeStamp.year, m_timeStamp.month,
> m_timeStamp.day );
> dateTime.DecodeTime( m_timeStamp.hour, m_timeStamp.minute,
> m_timeStamp.second, msec );
> m_timeStamp.fraction = 0;
> rc = SQLBindParameter( m_hStmt, m_paramNumber,
> SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
> sizeof( "yyyy-mm-dd hh:mm:ss" ), 0,
> &m_timeStamp, sizeof( m_timeStamp ), (SQLLEN *) &m_LenInd);
> CHECK_STMT( m_hStmt, rc );
> }
>
> m_timeStamp is just the SQL timestamp struct. A VDateTime is our
> own object, based on midnight Dec. 30, 1899 (that's a standard).
> m_* are class variables.
> Good luck,
> - Arnie
>
>
>|||Oops sorry LDJ
My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
Sorry for the confusion.
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Hi,
> This is what I used for my SQLBindParameter and it's working, so hope it'l
l
> help you!
> SQLBindParamenter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAM
P,
> 0, 0, (SQLPOINTER)&value, 0, &cbSize);
> Note: columnSize and bufferLength are ignored when the type is
> SQL_TIMESTAMP, cbSize is just an SQLINTEGER but asyou know it's also ignor
ed
> for timestamps...
> I'm not sure whether it makes a difference if you use SQL_C_TIMESTAMP as
> opposed to SQL_C_TYPE_TIMESTAMP, but I've been using the former
> Hope this helps,
> Isabella
> P.S. you can check out the following on the function if you haven't alread
y:
> http://msdn.microsoft.com/library/d...ndparameter.asp
>
> "Arnie" wrote:
>|||Apologies to Josh and Arnie! I realised the reason why my code worked was
because I was just inserting a date with no time...
Josh, Arnie was correct, the column size should be 19 as it actually
represents the number of bytes needed to display the yyyy-mm-dd hh:mm:ss
format.. If you wanted to show the fraction, you should use 20 + s as the
columnSize, where s is the precision
http://msdn.microsoft.com/library/d.../>
mn_size.asp
Sorry for my unhelpful posts b4
Isabella
"Isa" wrote:
[vbcol=seagreen]
> Oops sorry LDJ
> My columnsize is set to sizeof(TIMESTAMP_STRUCT), not 0... and i was
> incorrect, ColumnSize is not ignored for SQL_TIMESTAMP!
> Sorry for the confusion.
> Isabella
> "Isa" wrote:
>

ODBC In Stored Procedure

Hey guys,
I'm trying to get the column names from a table to which I'm connected
through OBDC. I don't want to use linked server. I get my data with
Openrowset.
I found the SQLDescribeCol and SQLColumns ODBC functions.
Can anybody tell me how to use those functions in a SQL Server stored
procedure?
Thanks a lotHi Pumkin,
should work as follows
SELECT colId, name,
FROM dbo.sysObjects
WHERE Id = OBJECT_ID('YourTableName')
ORDER BY ColId
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Hey,
I should explain myself better... I need to get the column names of a
table given as parameter. There is COLUMN_NAME from INFORMATION_SCHEMA
for SQL Server. The problem is that my table is not on my server and is
not a SQL Server table either. I need to access it through ODBC.
Can anybody help? Thanks|||Pumkin wrote:
> Hey,
> I should explain myself better... I need to get the column names of a
> table given as parameter. There is COLUMN_NAME from INFORMATION_SCHEMA
> for SQL Server. The problem is that my table is not on my server and
> is not a SQL Server table either. I need to access it through ODBC.
> Can anybody help? Thanks
It is not possible to use the ODBC functions you mentioned from a stored
procedure which can only run queries via openrowset.
If the table is in a relational database, then it should be possible to run
a query against that database to get the column names. Without more specific
information, it is impossible to go into details. Maybe you should try a
newsgroup devoted to the rdbms you are linking to.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hy Bob,
Thanks for the reply. The database that I am connecting to in not
relational. But I found something now...
It works if I do "select * from table where 1=2"
But I was looking for something more dynamically build.|||Pumkin wrote:
> Hy Bob,
> Thanks for the reply. The database that I am connecting to in not
> relational. But I found something now...
> It works if I do "select * from table where 1=2"
> But I was looking for something more dynamically build.
I'm not sure how that helps you: your t-sql code is not going to be able to
read those column names. I was thinking that you needed your code to somehow
use those column names for something.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ODBC in Stored Procedure

Hey guys,

I'm trying to get the column names from a table to which I'm connected
through OBDC. I don't want to use linked server. I get my data with
Openrowset.
I found the SQLDescribeCol and SQLColumns ODBC functions.
Can anybody tell me how to use those functions in a SQL Server stored
procedure?

Thanks a lotPumkin (PopClaudia@.gmail.com) writes:
> I'm trying to get the column names from a table to which I'm connected
> through OBDC. I don't want to use linked server. I get my data with
> Openrowset.
> I found the SQLDescribeCol and SQLColumns ODBC functions.
> Can anybody tell me how to use those functions in a SQL Server stored
> procedure?

You can't. Well, you could write an extended stored procedure or an
OLE object that you could call from a stored procedure, and that would
use these functions.

However, it would be more straight forward to query the system catalog
on the remote server directly.

What exactly are you trying to achieve?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx