|
My problem is that I have 1 table that is in the sql server, and another table that is in memory. The table in memory is defined as follows:
Name: outputTable
computer_id int
dracver string
dracip string
user string
password string
serial_num = string
the table in the sql server is defined as follows:
NAME computer
computer_id int
serial_num var
my question is how do I compare the serial_num column in the outputTable to the serial_num column in the computer table using all the rows in the outputTable.
Here is what I have so far
SELECT computer_id, serial_num FROM computer WHERE dbo.computer.[serial_num]="+FailureTable.Columns["serial_num"];
The problem is that it returns everything from the computer table.
can someone PLEASE HELP!!!!
thanxs
connie
|
|
|
|
|
That's a bit strange. It should only return the computer corresponding to the sigle record from the memory table that you use to create the query.
Loop through the memory table to create a string containing a comma separated list of id's. Then you can use that string in a query:
"select computer_id, serial_num FROM computer WHERE serial_num in (" + idList + ")"
---
b { font-weight: normal; }
|
|
|
|
|
The problem with that is that I also need the dracip string from the outputTable that goes with the serial_num being compared.
Any ideas on how I can also get that data as well??
Thanxs
Connie
|
|
|
|
|
Working in Microsoft Sql Server 2000
Is there a way to encrypt a database object like a stored procedure so the end user can not see the text of SP in their database management tool like Enterprise Manager. Maybe a third party tool or Does Sql Server 2000 provide this somewhere?
|
|
|
|
|
Use the WITH ENCRYPTION option in your stored procedure. Remember, once it is encrypted, there is no way to get it back. You must keep a text copy of the procedure somewhere so you can modify it later if needed.
So a stored proc definition would look like:
CREATE PROC Test WITH ENCRYPTION
AS
your code here
|
|
|
|
|
How to decrypt stored procedures[^].
The SQL Server 2000 stored procedure encryption suffers a fundamental flaw - if you have a known plaintext and the corresponding ciphertext, you can deduce the key. You can then decrypt any other ciphertext using the same key. When you use ALTER PROCEDURE, the same key is used as for the original procedure.
You can save the encrypted procedure text you're trying to decrypt by accessing the syscomments table. You can then ALTER PROCEDURE to a known plaintext, read back the new ciphertext and deduce the key. You can then decrypt the original.
I don't know if this is improved in SQL Server 2005.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for the info! I had remembered reading that SQL Server 2000 encryption was better than before, and that the old ways to decrypt no longer worked. Obviously, a way was quickly found. Fortunately, I don't need absolute encryption, since I just use it to prevent casual tampering. Maybe I will have to add a clause to my support contracts dealing with intentional tampering.
|
|
|
|
|
Our application reads information from a Jet database distributed as part of the program. We have recently added a new block of records to the
database. They appear in Access in the order that we expect, but our application returns an assorted few of the records first, followed by the rest in order.
We are still using the MFC interface to DAO, now through MFC 7.1. We read the database using the MFC/DAO MoveFirst and MoveNext calls. The SQL query is "( [SECT_TYPE_NUM] = 70 )", checking a single field.
|
|
|
|
|
Andrew Hain wrote:
They appear in Access in the order that we expect
Ordered by which column?
Do you define the query with "order by" statement? Like "SELECT * FROM your_table ORDER BY your_table_id".
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Thank you. I am still mystified at how we got away without that for years.
|
|
|
|
|
Andrew Hain wrote:
Thank you. I am still mystified at how we got away without that for years.
Actually, I got away with something similar. While I had the table opened in Access I hit the sort button on the toolbar so after that the records appeared to be always sorted. Somehow it worked for a while and I thought that I didn't have to write a single line of code to keep the records sorted. Well, it stopped working at some point. 
Time is the fire in which we burn.
|
|
|
|
|
Hello to all the community I am working on a fairly big App and now i am using Crystal to build loads of reports... And my question is about the optimal way to work when using crystal. Now i am creating 1 datadapter and table in a dataset (on design time) for each Report (excepting the ones who are very similar)
How do you think about this, ppl?
Thanks for reading.
|
|
|
|
|
Hi,
I have a Foxpro DBF and want to export its data to MYSQL DB.How can this be done
Pls help
Prabha
|
|
|
|
|
Hi,
In a FOXPRO Database,I need to change the date format from yyyy/dd/mm to dd/mm/yyyy.Pls help..
Is it possible to change the Dateformat in a table when it has values?
Pls help
Ram
|
|
|
|
|
Hello,
I want to perform a query like this:
UPDATE Payment SET Week = (SELECT Week FROM Receipt WHERE Payment = Payment.ID) Basically, the idea is that I have a table of payments, and a table of receipts, each receipt marked with a week number.
Now, I want to get the week number of the receipt for each payment, and put it in the Payments table.
To make things harder, there are two different receipts tables, and only one of the contains a receipt for a specific payment.
In pseudocode, it would be something like this:
foreach(paymentRow in Payments table)
{
WeekNum = SELECT Week FROM Receipts where ID_payment = paymentRow.ID_payment
IF WeekNum = NULL
{
WeekNum = SELECT Week FROM OtherReceipts WHERE ID_payment = paymentRow.ID_payment
}
IF WeekNum != NULL
UPDATE paymentRow SET Week = WeekNum
}It a once-in-a-lifetime update, so it doesn't have to be the best, performance-speaking.
So, any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Ok, here's what might work:
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN Receipts ON Payment.ID_payment = Receipts.ID_payment
WHERE Receipts.WeekNum != NULL
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN OtherReceipts ON Payment.ID_payment = OtherReceipts .ID_payment
WHERE OtherReceipts.WeekNum != NULL
Basically each query tries to update Payment only when the WeekNum is not null.
Hope that helps,
Edbert
|
|
|
|
|
Wow!! Updates with joins? I was already reading about evil cursors
Let me try this first!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
LuisR wrote:
Updates with joins? I was already reading about evil cursors
I can tell you that updates with joins are much more efficient than using cursors or looping via code.
You should learn how to update using joins as this will be your best friend for most of the time.
Edbert
|
|
|
|
|
Cool!! 
I tweaked it a bit, and it worked!
I didn't know updates with joins existed. But now I'll have a new trick!! I know cursors are not efficient, but this is a once in a lifetime update.
So, basically it's UPDATE table_to_update SET column = new_value FROM table_to_update like in a SELECT? This query should UPDATE all rows in the table. And if I add an INNER JOIN there, then the UPDATE will only work on the rows that would result from the same SELECT FROM statement?
Thanks!!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Yep.
The only limitation you need to remember is that UPDATE can only update 1 (one) table at a time (I'll be glad if somebody proves me wrong).
Therefore:
UPDATE <table1><br />
SET <specify table1 columns to update from values in all other tables specified below><br />
FROM <tables with JOIN and WHERE conditions>
Just for your information, do you know that you can also do
INSERT INTO (<columns>)<br />
(<br />
SELECT <columns><br />
FROM <tables><br />
)
|
|
|
|
|
Great!!
Edbert P. wrote:
Just for your information, do you know that you can also do
INSERT INTO ()
(
SELECT
FROM <tables>
)
Yes. I didn't but sometime ago someone taught me about it here (was it you maybe? or Colin?) and a few weeks ago I answered a question here with that INSERT INTO... SELECT. He didn't believe me until I said, "well, go and try it" and he was amazed!
SQL sometimes is so cool!
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Does anyone maybe know how I can open a sql server 2005 database in sql server 2000? I can't use "view all rows" in 2005, and need to use 2000 from now on.
Thanks
|
|
|
|
|
"Open Table" I believe is the equivalent menu option in SQL Server 2005
|
|
|
|
|
Hello!
I don't know how to connect to SqlServer when this server is not in the localmachine.
I'm using VB.Net and the server can only be joined on the internet.
My connect string looks this :
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine"
The computer where the server is running can be reached by a domaine name
Thank you for your help (and sorry for my bad english)
SeLoRBIS
|
|
|
|
|
SeLoRBIS wrote:
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine
Why is the data source the name of your machine?? It should be pointing to the server.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
