Database Discussion Boards

Mike Dimmick31-Jul-05 10:27

31-Jul-05 10:27

1. Don't concatenate parameters in your command string - use a SqlParameter object (ADO.NET) or the Parameters collection of the Command object (ADO).

2. If you must use a literal date, use the ISO format 'yyyymmdd'. SQL Server will always accept this format, regardless of locale settings.

Stability. What an interesting concept. -- Chris Maunder

Re: stored procedure and date format

Frank Kerrigan2-Aug-05 6:16

Frank Kerrigan

2-Aug-05 6:16

I never pass any dates to a SQL server I always pass DD MM YY then build the date on the fly in the SQL server Strored Proc. Example below.

###########################################################################################
CREATE PROCEDURE usp_myproc
@YearInt int,
@MonthInt int,
@DayInt int
AS
DECLARE @CD_DATE datetime
DECLARE @TempVarChar varchar(50)

Set @TempVarChar = CAST(@DayInt As Varchar(2) ) + '/' + CAST(@MonthInt As Varchar(2) ) + '/' + CAST(@YearInt As Varchar(4) )
Set @CD_DATE = (Convert( datetime, @TempVarChar, 103 ))

SELECT @CD_DATE
go

Generating ERD.....!

wEb GuRu...29-Jul-05 22:30

wEb GuRu...

29-Jul-05 22:30

Hi Developrs,
I have developed a database in SQL Server 2000. Now I want to generate its ERD which should not contain attributes of Table.

Is it possible with Visio 2002? If it is possible in any way please do reply me its very urgent.

Thanx in advance.

SQL Base

albCode29-Jul-05 21:00

albCode

29-Jul-05 21:00

How can I select one table which exist in another server but actually i am in my server????

Problem with Query

Anonymous29-Jul-05 7:19

Anonymous

29-Jul-05 7:19

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

Re: Problem with Query

Guffa29-Jul-05 8:19

Guffa

29-Jul-05 8:19

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; }

Re: Problem with Query

Anonymous29-Jul-05 10:03

Anonymous

29-Jul-05 10:03

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

Encrypting DDL

mtone29-Jul-05 7:06

mtone

29-Jul-05 7:06

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?

Re: Encrypting DDL

Scott Serl29-Jul-05 14:23

Scott Serl

29-Jul-05 14:23

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

Re: Encrypting DDL

Mike Dimmick31-Jul-05 10:33

Mike Dimmick

31-Jul-05 10:33

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

Re: Encrypting DDL

Scott Serl31-Jul-05 11:31

Scott Serl

31-Jul-05 11:31

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.

Records appear out of order

Andrew Hain28-Jul-05 23:23

Andrew Hain

28-Jul-05 23:23

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.

Re: Records appear out of order

Marc Soleda29-Jul-05 0:55

Marc Soleda

29-Jul-05 0:55

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.

Re: Records appear out of order

Andrew Hain29-Jul-05 5:13

Andrew Hain

29-Jul-05 5:13

Thank you. I am still mystified at how we got away without that for years.

Re: Records appear out of order

Toni7830-Jul-05 16:08

Toni78

30-Jul-05 16:08

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. Smile | :)

Time is the fire in which we burn.

Best Aproach When Working with Crystal Reports .Net?

korso_rogan28-Jul-05 21:34

korso_rogan

28-Jul-05 21:34

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.

Import Data

cberam28-Jul-05 19:10

cberam

28-Jul-05 19:10

Hi,

I have a Foxpro DBF and want to export its data to MYSQL DB.How can this be done
Pls help

Prabha

Date Format in the Database

Anonymous28-Jul-05 19:09

Anonymous

28-Jul-05 19:09

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

Complex update

Luis Alonso Ramos28-Jul-05 9:20

Luis Alonso Ramos

28-Jul-05 9:20

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!

Re: Complex update

Edbert P28-Jul-05 13:25

Edbert P

28-Jul-05 13:25

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

Re: Complex update

Luis Alonso Ramos28-Jul-05 13:29

Luis Alonso Ramos

28-Jul-05 13:29

Wow!! Updates with joins? I was already reading about evil cursors Smile | :)

Let me try this first!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: Complex update

Edbert P28-Jul-05 13:34

Edbert P

28-Jul-05 13:34

LuisR wrote:
Updates with joins? I was already reading about evil cursors

Big Grin | :-D

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

Re: Complex update

Luis Alonso Ramos28-Jul-05 13:42

Luis Alonso Ramos

28-Jul-05 13:42

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!! Smile | :)

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Re: Complex update

Edbert P28-Jul-05 13:54

Edbert P

28-Jul-05 13:54

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 />
)

Re: Complex update

Luis Alonso Ramos28-Jul-05 14:03

Luis Alonso Ramos

28-Jul-05 14:03

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! Cool | :cool:

Thanks!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.