|
Ali Al Omairi(Abu AlHassan) wrote: Sir, what is your problem with that?
Nothing dude, but what about the lounge where many people there....also it's not a right place to post jokes. That's it.
Anyway come to lounge if you have interesting things to post. Cheers.
|
|
|
|
|
thank you for doing this, Alen.
Help people,so poeple can help you.
|
|
|
|
|
I have a SQL database column that was orignally declared with a not null constraint. (using alter table add column) Turns out, the column needs to be nullable to keep from breaking legacy apps. Should be easy, use alter statement to change to all not null, right? Not. This throws an error complaining that "The object 'xxxx' is dependent on column 'columnName'". So I looked it up in SSMS and there it is under constraints with what appears to be a generated name. This is a distributed application, so I am thinking that the constraint name is not guaranteed to be the same. OK then, just use ADOX or OpenSchema to get the offending constraint name. After googling and trying different approaches for about an hour, and not finding anything that worked, (and getting pretty disgusted in the process) it ocurred to me that the name of the constraint is reported back in the error description, always between the first two quotes! It was right there the whole time! Extracted the name, ran the alter table drop constraint statement, then was able to alter the column to not null. This approach should work regardless of the locale.
|
|
|
|
|
Sir, I don't know what do you want exactly but i think this would help.
select
parobj.name as 'ParentTable',
concol.name as 'ConstrantColumn',
refobj.name as 'ReferencedTable',
refcol.name as 'ReferencedColumn'
from sys.foreign_key_columns
inner join sys.objects as parobj
on parobj.object_id = sys.foreign_key_columns.parent_object_id
inner join sys.columns as concol
on concol.column_id = sys.foreign_key_columns.constraint_column_id and
concol.object_id = sys.foreign_key_columns.parent_object_id
inner join sys.objects as refobj
on refobj.object_id = sys.foreign_key_columns.referenced_object_id
inner join sys.columns as refcol
on refcol.column_id = sys.foreign_key_columns.referenced_column_id and
refcol.object_id = sys.foreign_key_columns.referenced_object_id
or may be this:
SELECT OBJECT_NAME(OBJECT_ID) AS 'NameofConstraint',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType'
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
100 ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif) 
Help people,so poeple can help you.
|
|
|
|
|
Hello folks,
I have this one field, which is MyCode let's just call it, and this field is an INT which can have any value from 1 to 10 inclusive.
I am so frustrated because when I pass this column into an ORDER BY, it does
1
10
2
3
4
5
6
7
8
9
instead of
1
2
3
4
5
6
7
8
9
10
which is what one would want. Basically, I am wondering if there is anything else I can put in my query to teach SQL server the exact order I want my values to be sorted, without having to make a new sort order definitions file.
Sincerely Yours,
Brian Hart
modified on Wednesday, February 9, 2011 4:00 PM
|
|
|
|
|
Seems like the column is defined as some sort of character type.
Do a "sp_help myTable" to get the detailed column definition. (Substitute you actual table name for myTable and don't use the quotes when trying to execute the command).
David
|
|
|
|
|
Yes it is a varchar type. How interesting.
I did a CONVERT(INT, myField) in the SELECT list and that cured it  I am happy now!
Sincerely Yours,
Brian Hart
|
|
|
|
|
It seems that the field may not be an INT, considering the results from the ORDER BY. If you are unable to change the defintion, you might try the following
order by length(MyCode), MyCode
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
The question's been answered. It was a varchar, i did a CONVERT(INT, myField) in the SELECT
Thanks though!
Sincerely Yours,
Brian Hart
|
|
|
|
|
Hi,
Please let me know what is dynamic query in SQL server with example...
|
|
|
|
|
This is a great article on the subject with examples too
Dynamic SQL[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
This may help.[^]
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
Dear all,
I have the following:
set rs = db.openrecordset ("select fname, lname from employee")
while not rs.EOF
with rs
.delete
end with
rs.movenext
loop
When the code is run, data is directly deleted from my actuall table??? i dont want this to happen, i want to initialize this recordset and refill it with another data.
it is deleting the table records directly even without updating (.Update) !!!
I need an empty recordset to work on. The only way (i know) to create a recordset is to create one from a given table through an SQL stat. I couldnt find a way to create an empty recordset from scratsh or a dummy recordset without a predefined data source.
Help !!!!
0 will always beats the 1.
|
|
|
|
|
Try select fname, lname from employee where 1=1 that should get you back an empty recordset.
When deleting data you are changing the content of the collection and usually you start from the end and work forward. If you delete record[0] what is the current record you want to move next from?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i will not movenext after deletion, but i will add new record.
Mycroft Holmes wrote: select fname, lname from employee where 1=1
is returning all records in the table.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 5:54 AM
|
|
|
|
|
My error try 1 = -1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: where 1=1
Since the condtion is true for all rows, won't all rows be returned in the set? 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
My error it should be 1 = -1 or ID = -1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can you explain further what your purpose is for requiring an "empty recordset"?
Your question comes across somewhat odd. You have code that will delete records from the recordset and you express surprise that data is deleted and that you don't intend for that to happen. If you don't want data to be deleted, why do you have a delete statement on the recordset?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Sure....
The delete statement is just to empty the record set, Why is that? it is because it came from an sql statement or a certain data source, and since i need an empty one , so am deleting the records in it.
My purpose is to pass a recordset to a report, but this recordset is a collection or records from different sources ,lets say two coloumns form an sql statement and another coloumn which i need to append and populate. which is in my case the salary coloumn .
.append , didnt work .
so my guess was to start from with a recordset from any sql source, and delete its rows (initializing) , then add my data.
but deleting rows end up in deleting rows from my actual table the recordset came from 
so i need to start with an empty recordset in order to fill it up and pass it to report.
0 will always beats the 1.
modified on Tuesday, February 8, 2011 10:56 PM
|
|
|
|
|
Assuming you're collecting data from several tables in ONE database.
Use a union query[^] instead, to fill the record set with the required data.
|
|
|
|
|
Thanks Andersson , but i think UNION is not the right solution here, since it envolve a lot of constraints  , like number of coloumns of the two SQL stat. should be the same. Other wise i think it save alot of lines of code 
0 will always beats the 1.
|
|
|
|
|
The queries that are unioned together needs to have the same number of columns of the same type with the same names in the same order, that's correct.
But that could be easily fixed using alias and dummy columns.
select a,b,c
from tbl1
union
select x as a,y as b,null as c
from tbl2
|
|
|
|
|
Now this does make sense .... nice work around
It worked fine (even the result data is not that much comprehensive) but it do the job, and save some extra lines of code ... great man, thanks alot 
0 will always beats the 1.
|
|
|
|
|
i just put the sqlstat in a seperate string and it worked fine 
hello all,
when i run this sql stat, in query design, Access 2007 , it works fine and return records.
SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11))
But when i put it in recordset as
set rcset = db.openrecorset("SELECT fname, lname FROM names WHERE (location = 'there' AND id In (select id FROM name_street WHERE street = 11))")
the record set brings back no records ... WHYYY ????
0 will always beats the 1.
modified on Tuesday, February 8, 2011 3:08 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
