|
hi
I m passing the date parameter to a stored procedure. The stored procedure not does not accept the date format as 'dd/mm/yyyy'. It gives an error -"error converting data type varchar to datetime".
My machine's regional setting is set to 'UK' i.e. "dd/mm/yyyy".
How can i make a change in SQL server so that it accepts date parameters in the "dd/mm/yyyy".
please help
shelly
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
How can I select one table which exist in another server but actually i am in my server????
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
