|
My app uses MSDE, though I believe SQL Server is installed on my development box. I'm running XP Pro. I'm running on a stand-alone laptop, no other computers connected.
I do have Enterprise Manager, but I've seen Enterprise Manager itself have an open connection to the database that prevents my app from detaching the database, so I generally close it when I'm running my app.
I've used Enterprise Manager some, but am not familiar with how to find out what hosts/applications/logins are connected to my database, as you suggest. I tried by opening local\Management\Current Activity [datetime]\Process Info in EM, and didn't see any reference to the Northwind database when I ran the sample project I posted. Can you point me in the right direction where to look? Or if you have VB.NET and SQL Server, would you mind creating a new Windows Forms project with one button and dropping my sample code in the form, and see if you get the same error when you click the button?
I'm wondering if the problem might have something to do with the way .NET handles memory - maybe the garbage collection isn't happening when I need it to, so even though my objects are not available in code anymore, the connection is somehow still present in memory?
Any suggestions where to look, what to try, or confirmation that my sample code causes the same error on other boxes, would be much appreciated!
Thanks
David
|
|
|
|
|
I send .xls files to a share on a server. I need to take those .xls files and use the data in them to populate a table on that server.
I'm new at this, any details are welcome.
Thanks.
|
|
|
|
|
I'm a bit stumpt.
The application I am working on needs to produce a basic datamodel of the source database, so I am reading the system tables from from the database to determine the data model. However, I cannot seem to find out how to determine the primary key, the following is the test code I have, and from what I can tell from the documentation sysconstraits is supposed to tell me the primary key, but it doesn't seem to work.
BTW, I'm using SQL Server 2000.
SELECT syscolumns.name as column_name, sysconstraints.status as status
FROM syscolumns
INNER JOIN sysobjects on sysobjects.id = syscolumns.id
LEFT OUTER JOIN sysconstraints on
sysobjects.id = sysconstraints.id AND
syscolumns.colid = sysconstraints.colid
WHERE sysobjects.name like 'test_table'
Any ideas would be appreciated.
TIA.
"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 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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
