error in WHERE statement

dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...

I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.

what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...

regards,
kanishkit's because this sql is perfectly valid in terms of syntax:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

The only reason it fails is because there is no column named smallint in that table. That's not a syntax error.

Now this query, on the other hand, works, because both columns exist, and has exactly the same form as yours:

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = LOCAL_TYPE_NAME

is that what you are asking?|||not actually, i was saying that i was looking for a value in the column TYPE_NAME named smallint

I was giving the WHERE clause to look for a value called smallint in the column TYPE_NAME. the column has many vaules in it...smallint is one of them...

maybe i should have written it as:

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

Since i am new to SQL language....what were you refering to...??...Although i quite got my answer...

Can you please tell me...what is the intersection of the column and row called??....for example in ms Excel when the row 7 and column D meet then a cell is formed...called D7

But in SQl server...what is the intersection of the Column and Row called...?? Like the cell in ms Excel...??

regards,
kanishk|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?|||the intersection of row and column is called "the value of column Y for row X"

:cool:

i realize that sounds somewhat flippant, but relational database theory is based upon primary keys, so "row X" means "the row where the primary key value is X" since that's how you tell rows apart, and "column Y" means "the column with 'Y' as the column name" since that's how you tell columns apart

simple, innit ;)|||the original query you wrote,

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals the value in the smallint column"

This query failed because there is no column named smallint in that table.

The query you MEANT to write,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

means:

"give me all rows in spt_datatype_info where the value in the TYPE_NAME column equals 'smallint' "

see the difference?

thanks, i was wondering that when i give the command that you gave up...viz,

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint'

when i run it, then automatically the TYPE_NAME column takes brackets,,,why is that??...i have shown it below...

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint')

regards,
kanishk|||That is because you are running this query in the SQL section of enterprise manager. Enterprise manager will try to bracket all where conditions
so if your query is something like

SELECT *
FROM spt_datatype_info
WHERE TYPE_NAME = 'smallint' OR TYPE_NAME = 'int'

Then on running it you will get

SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = 'smallint') OR (TYPE_NAME = 'int')

Try running the query in SQL Analyzer and you will not get the brackets..... however i guess the brackets are purely for readability and in the end finally makes no difference.....|||dear sir, i have started using the SQL query Analyzer, i have another question,
i just put in the LIKE condition...

SELECT * FROM spt_datatype_info WHERE TYPE_NAME LIKE '%s'

now this will give all the values in the TYPE_NAME column which start with any characters but end with 's' , thats why i have put '%s' , now suppose i want all the values that have 2 letters as starting and the 3rd letter as 's' , so what do symbol do i use??

Like in windows when we used to give in command prompt the ! , exclamation mark for the characters we did not know but were sure of the count of them, and the star '*' for the characters we did not know and were not sure of their count..

Similarly in SQL what would we give for the LIKE condition..??

regards,
kanishk|||... LIKE '__s%'this is all explained nicely in the manual

Error in execute a scheduled package

I'm learning SSIS and ran into this error...

I have a package that has its source as an Oracle DB on another server. This package will feed data from that source to a SQL Server 2005 DB. So far, the package works fine if it is executed manually even in SQL Mgmt Studio. It's only failed when I tried it as a scheduled job. I guess I need to do "Package Configurations" which includes the UserID and password for accessing Oracle DB ... but I don't know how. Please help.

Regards,

dnncpt

-

Here is the error message:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/08/2007 09:49:00,DataFeed,Error,0,<MyDBServer>,DataFeed,(Job outcome),,The job failed. The Job was invoked by Schedule 8 (Schedule DataFeed Grant Tables). The last step to run was step 1 (DataFeed Grant Tables).,00:00:02,0,0,,,,0
05/08/2007 09:49:00,DataFeed,Error,1,<MyDBServer>,DataFeed,DataFeed Grant Tables,,Executed as user: <MyDBServer>\SYSTEM. ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:49:00 AM Error: 2007-05-08 09:49:00.81 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-05-08 09:49:01.86 Code: 0xC0202009 Source: AWARDS Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied". End Error Error: 2007-05-08 09:49:01.86 Code: 0xC020801C Source: Feed data t... The package execution fa... The step failed.,00:00:02,0,0,,,,0

For the error, I think this link may be the answer:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b904800

So the protection level is the reason.

I really love the Microsoft technologies but if MS could provide such a nice software like SQL Server and SSIS why its tech teams don't go a further step as to provide a good how-to document for each application. This could benefit for both MS and its customers. (hope Mr. Gates or Mr. Ballmer read this)

Regards,

dnncpt