|
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'test_table'
ORDER BY ORDINAL_POSITION
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for your suggestion.
I had already tried using this view, but it also gives out the foreign keys without distinguishing between them. The constraint name may contain 'PK' in it which could help - but I can't guarantee that some half-crazed database user will keep with that notation.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
select *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'test_table'
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
 So obvious!
Thanks. I can join everything up now. 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
No problem.
I can never remember what's where in the INFORMATION_SCHEMA views, myself. Whenever I want/need to do something with schema, I always have to run back to Books Online and wade through all of the view descriptions to figure out which one(s) have the info I'm looking for.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi everyone,
I have a stored procedure (that I used a tool to convert from SQL Server) that has the following parameter signature:
(
TestVorgangID IN NUMBER DEFAULT NULL,
VersuchID IN NUMBER DEFAULT NULL,
Kommentar IN VARCHAR2 DEFAULT NULL,
EAMID IN NUMBER DEFAULT NULL,
dspaceSection IN NUMBER DEFAULT NULL,
testID IN OUT NUMBER
)
I try to call this procedure from my C++ program with the following SQL:
{call UpdateTestsTable(10008, 590, '', , 1, ?)}
This always worked when I was on SQL Server. However, now it returns an error saying: Syntax error or access violation.
What am I doing wrong?
Thanks,
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Possibly the access violation is occurring because there is no storage for the final parameter. What does the '?' character as the final parameter mean or imply?
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
|
|
|
|
|
I am using a DataSet which contains two DataTables. The 2 Tables are related using a DataRelation and it's a One-to-One Relationship. On a Form, I create a DataView for the Top-Level Parent Table and access the Child table through the DataMember when using the BindingContext.
One my Form, I am using Me.BindingContext(dvData).AddNew() for the Parent Table. I then use Me.BindingContext(dvData, "ChildRelationship").AddNew() to Add a new Record at the Child Level. I do this because the Form edits data in both Tables at the same time so that to the User, they will not have to make 2 edits just to get data in both tables.
When I debug after doing this, everything is great! I can see both new Rows added to the DataView and the Form works as expected since it is data bound.
However, in my Save Routine, I modify a few Columns if the Data wasn't supplied, and lastly call the Me.BindingContext(dvData).EndCurrentEdit() and Me.BindingContext(dvData, "ChildRelationship").EndCurrentEdit(). When I do this, and attempt to save the Data back to the Database, it has the Parent Row, but the Child Table's Row (that was previously there) is no longer there! Filtering the DataTable on only Modified / New Rows results in no data, and also the Row Count is the same as before any Rows were added. It's as if the New Row on the Child Table just disappeared!!! 
Does anyone have any insight?? 
|
|
|
|
|
Hello all,
I want to write a stored procedure in SQL Server that makes the following:
checks every record entered in the database, once the record is entered, it checks a field named: telNum, and checks if it is written in arabic (means Hindi numbers) with arabic character set, it updates it and converts it into English number (with english character set).
this is a trigger which I want to write in SQL Server to make what I mentioned above.
Could someone tell me how is that done please. And how can I start, and from where shall I start.
I appreciate any help from you ...
Sam
|
|
|
|
|
I have two sql databases: master & transactions
I need to know how to write some code so that if I am searching my transactions database for a matching counterpart in the master database, the transactions entries overwrite those in the master database. In addition, if no match is found I need a new entry to be added to the master database.
Thank you.
|
|
|
|
|
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where custid in (
select distinct custid from pending
where custid <> ''
)
)
)
there is no constriants on both the tables.
|
|
|
|
|
Yup - remove Select * FROM
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
This looks a little cleaner. Are you sure there is both id and custid in the customer table? Something doesn't look quite right. You can also get rid of the correlated sub-query by placing it in the INNER JOIN.
<code>SELECT
pending.*
FROM
pending
INNER JOIN
customer cus
ON (pending.custid = cus.id)
WHERE
pending.custid <> '' AND
cus.status = 1 AND
cus.priority = (SELECT
max(priority)
FROM
customer
WHERE
custid = cus.custid)
|
|
|
|
|
Michael Potter wrote:
Are you sure there is both id and custid in the customer table?
Yes you are right, There is no custid its just id in the customer table.
[code]
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where id in (
select distinct custid from pending
where custid <> ''
)
)
)
[/code]
|
|
|
|
|
Dear Michael,
So i modified my query like this
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where id in (
select distinct custid from pending
where custid <> ''
)
)
)
and your query.
SELECT pending.*
FROM pending
INNER JOIN customer cus ON (pending.custid = cus.id)
WHERE pending.custid <> '' AND
cus.status = 1 AND
cus.priority = (
SELECT max(priority)
FROM customer
WHERE id = pending.custid
)
But when in checked the execution plan my query was showing less percentage for all the counters there.
Where as your query is showing more.
So my doubt was, does my lazy query has any advantage or perfomance gain that i am not aware of, may be coz of not using joins.
btw still wondering how you guys are writing such optimized queries, actually i can program(c++) many complex issues, but still cannot optimize the SQL queries like that, is there any tips to get started ??
thanks
|
|
|
|
|
My experience is that joins usually take a shorter time then do in statements. It really depends upon the size of your datasets. There are few hard and fast rules that would work in all instances. Usually, I just follow my nose - If a query takes longer then I desire, I try a different approach or modify my indexes. For example, the following index may help my solution:
CREATE INDEX IX_CUSTOMER_ID_PRIORITY
ON customer
(
id,
priority
)
If the table is not large, than the index would be ignored and a simple table scan would be used.
The rule about not using * when you don't need every column is a rule that works in all instances.
|
|
|
|
|
I am guessing "id" is really custid since you are looking for custid in it.
Are you looking for the max(priority) for EACH customer (1) or the max(priority) for all customers in pending(2)?
1.
Select *
From(
Select custid, max(priority)as Priority
From customer
Where status = 1
and custid <> ''
Group by id
) as subtable Join pending
On subtable.custid= pending.custid
2.
SELECT *
FROM Pending Join Customer
on pending.custid = customer.custid
WHERE priority = (
SELECT max(priority)
FROM customer Join pending
on customer.custid = pending.custid
WHERE customer.custid <> ''
and status = 1
)
Where pending.custid <> ''
michanne
|
|
|
|
|
I am running SQL2000 on windows2003. I often connect to the database through the SQL Server Enterprise Manager on another machine through the company's intranet to administer it. The strange thing is sometimes I can connect to this database but other times cannot. THe error message is "SQL does not exist or not known to be running". I have checked the server status at the host terminal and it is running. I can even register with other database server residing on another machine.
Anyone knows what's the problem for such intermitent connection? Network prob? SQL registration at the client side? ODBC setting?
Thanks.
|
|
|
|
|
Is your login and the SQL Server system on the same domain? If not, you may have to preface the SQL server name with its domain name: TheDomain\TheSqlServer<\b>. Depending upon your previous network activity during the current login, Windows may not know where to look.
Just a thought.
|
|
|
|
|
Hi Micheal,
You are right my client terminal is in different domain from my host terminal. Do you know how can i find out in which domain my SqlServer lies? Is it found in the network setting on the host terminal.
Thanks.
|
|
|
|
|
On 2000 it can be found under "Network Identification". RClick [My Computer], choose properties.
|
|
|
|
|
hi micheal,
My host is in the same workgroup as my client but both are not join to any domain. I have found that by logging in to a shared folder in the host terminal I can succcessfully searched for my sqlserver. Looks like the client needs to establish the path to the host terminal first before it can locate the sqlserver.
Any advise how I can make do without the login yet still can locate the sqlserver?
Thank you.
|
|
|
|
|
I would think it would work the same as a domain.
Try:
<workgroup>\<server name="">
or
\\<workgroup>\<server name="">
If you fiddle around enough, you will find the required connection string.
|
|
|
|
|
Hi All
i have a sp which to querys a database, but each time the sp is run the name of one of the tables
changes, so i want to pass the table name into the query as a string
something like
DECLARE @table_name varchar(100)
set @table_name='mytable'
exec ('select my_id from ' + @table_name+' ORDER BY my_ID')
which works fine when im only using one table
i wanted to do a join across several tables, and do something like
set @sql_stmt = 'select callingnumber,seizedatetime,callednumber,new_action.telephone,new_action.action1,new_action.action2, new_call.student_id, datepart(dd,seizedatetime) as myday, null as sent_it
from new_call,new_action,'+@table_name+'
where new_call.call_ID=new_action.call_id'
EXEC ( @sql_stmt )
but that doesnt work
does ne1 have any ideas 
|
|
|
|
|
I don't see the error. What is the error msg?
You might try printing the result instead of exec right off the bat. Paste the result into another qa window and see what errors you get.
Hope that helps
michanne
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
