Re: SQL Question - Database Discussion Boards

RussellT5-Jun-09 10:07

5-Jun-09 10:07

neoghy wrote:
How can i prevent the data from getting vanished without writing commit or set autocommit commands on everytime.

This is like asking how I can eat my cake without opening my mouth.

Cheers,
Russ

Sql Restore Error

kankeyan16-Mar-09 3:53

kankeyan

16-Mar-09 3:53

Dear Friends,
Client database is in the form of "database.Dat" they don't which version they are using, i want to restore it i sqlserver 2000 with service pack 4
Following error occured

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Please give a solution

Thanks & Regards

JK

Re: Sql Restore Error

RyanMorris16-Mar-09 6:02

RyanMorris

16-Mar-09 6:02

Hi JK,

It looks to me like you client is using SQL 2005, which isn't backwards compatible with SQL 2000.

Ryan

Re: Sql Restore Error

kankeyan16-Mar-09 19:22

kankeyan

16-Mar-09 19:22

is it possible to convert sql 2005 database to sql 2000 sp4

Re: Sql Restore Error

RyanMorris16-Mar-09 22:57

RyanMorris

16-Mar-09 22:57

Just using the file you have, not as far as I know. If you had access to the database, I'm told that you could export the data from SQL 2005 and then import it into SQL 2000, but I haven't done this before and so I can't really tell you exactly how to go about it.

When MSAccess Readonly database with password is opened error 0xfffff88f "Not a valid password". Who knows why?

Grebeshkova16-Mar-09 0:48

Grebeshkova

16-Mar-09 0:48

For open database *.mdb I use ADO-construction
try
{
hr = m_pConnection->Open(_bstr_t(lpstrConnection), _bstr_t(lpstrUserID),
_bstr_t(lpstrPassword), NULL);
return hr == S_OK;
}
catch(_com_error &e)
{
}
When mdb-file has not attribute "ReadOnly", Open returns S_OK.
But when I set this attribute, exception is generated with
ErrorNumber = 0x80004005 and NativeError = 0xfffff88f ("Not a valid password").
I don't undenstand why. Pls., explain me this situation.

And other question: what are error codes concerned with error password (for ODBC and SQL-Server)? Or in other words: How may I detected that in connection string invalid password for opened database?

Thanks

SQL Question

Lars Fisker15-Mar-09 13:51

Lars Fisker

15-Mar-09 13:51

Can someone answer why the following isnt returning anything?

string strGetPeriodeSQL = String.Format("SELECT * FROM {0} WHERE SDate BETWEEN {1} AND {2} ORDER BY SDate", strTableName, dtStart.ToShortDateString(), dtEnd.ToShortDateString());

I left out the rest of the funktion, just the SQL part is here.

Lars

Resistance is futile - Data (ST:First Contact)

Re: SQL Question

Mycroft Holmes15-Mar-09 14:05

Mycroft Holmes

15-Mar-09 14:05

Are you saying strGetPeriodeSQL does not get populated?
Does the SQL string not execute?
Is no data returned from SQL?

I suspect the latter so:
Take the content of strGetPeriodeSQL to Management Studio and see what the results are.

If you get results then there is a problem with your connection.

Never underestimate the power of human stupidity
RAH

Re: SQL Question

Lars Fisker15-Mar-09 14:48

Lars Fisker

15-Mar-09 14:48

Thanks for the answer. But I figured out what was wrong. Insted of converting to ShortDateString I needed to convert to ToOADate.

Sorry for the trouble.

Lars

Resistance is futile - Data (ST:First Contact)

Re: SQL Question

Blue_Boy16-Mar-09 2:01

Blue_Boy

16-Mar-09 2:01

or you can use this way:

<br />
string strGetPeriodeSQL = String.Format("SELECT * FROM {0} WHERE SDate BETWEEN {1} AND {2} ORDER BY SDate", strTableName, dtStart.ToString("yyyy.MM.dd"), dtEnd.ToString("yyyy.MM.dd")/*give yout date format*/);

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

How to Store password character in Oracle Table

Saleem Tahiri15-Mar-09 1:23

Saleem Tahiri

15-Mar-09 1:23

I want to store string character to be stored as password in Oracle table.

Re: How to Store password character in Oracle Table

Eddy Vluggen15-Mar-09 2:09

Eddy Vluggen

15-Mar-09 2:09

Sounds like you want to store the password similar to "*****" into Oracle?

I are troll Smile | :)

Re: How to Store password character in Oracle Table

Saleem Tahiri15-Mar-09 2:12

Saleem Tahiri

15-Mar-09 2:12

Yes the password needs to be stored "*****" or any other format in Oracle

Re: How to Store password character in Oracle Table

Saleem Tahiri15-Mar-09 2:12

Saleem Tahiri

15-Mar-09 2:12

Yes the password needs to be stored "*****" or any other format in Oracle

Re: How to Store password character in Oracle Table

Eddy Vluggen15-Mar-09 2:21

Eddy Vluggen

15-Mar-09 2:21

Then how are you going to read the password once you need it? It would return ******, for each and every record in your database Frown | :(

What you are seeing on your screen isn't the same as what one would store in a database. You might want to consider encrypting[^] the string before you send it to a database. It would be preferable to store a hash-value based on the password; you'd calculate an unique number based on the string, and store that. Then, when the user logs in, you compare a new calculation with the number that you stored Smile | :)

I are troll Smile | :)

Access [modified]

udikantz14-Mar-09 19:52

udikantz

14-Mar-09 19:52

hi guys,
i am developing an application which uses access mdb files as database.
untill now i used a connection to the files,
using the following code:

Public Class DBconnector


    Private _dbConnection As OleDbConnection
    Private _dbCommand As OleDbCommand
    Private _dbDataReader As OleDbDataReader
    Private _dbDataAdapter As OleDbDataAdapter
    Private _dbTransaction As OleDbTransaction
    'Private _dbADODBConnection As ADODB.


    Public Sub New(ByVal cs As String)

        _dbConnection = New OleDbConnection(cs)
        _dbCommand = _dbConnection.CreateCommand

    End Sub

where the connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;" 
 & "Data Source=" & TextEditPath.Text

texteditpath is the file path and name.

recently i have encountered an error while trying to create a relationship using "ON DELETE CASCADE" in the constraint clause.

error mentioned theres a syntax error on the constraint clause and there was no real syntax error , after troling microsoft forums , they have told me i need to use ADO connection because this bug results cause of use in DAO ,
also i may add that when i execute the query in the imidiate debug widow of access 2007 it is executed with no errors , how ever when i execute it on the SQL design window of access 2007 i get the same error as i get in the .net frame work

my question is how do i establish an ADO connection to access MDB file
or how do i over come this bug ,
please help im stuck with this for a week now ,
thanks...

Net

modified on Tuesday, March 17, 2009 4:09 AM

Datetime?

Karthick_gc13-Mar-09 0:03

Karthick_gc

13-Mar-09 0:03

Hi, I am using SQL in my project. I want to fetch only time alone. i know to fetch Date. How to fetch time? Can any one tell?

Re: Datetime?

Eddy Vluggen13-Mar-09 0:22

Eddy Vluggen

13-Mar-09 0:22

Something like this?

DECLARE @d1 DATETIME = '12/19/2007 13:43:23.45', @t1 TIME(2)
SELECT @t1 = @d1
SELECT TimeOnly = @t1

I are troll Smile | :)

Re: Datetime?

Ashfield13-Mar-09 2:15

Ashfield

13-Mar-09 2:15

Try this

select convert(varchar(8),getdate(),114)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Datetime?

Mycroft Holmes14-Mar-09 16:17

Mycroft Holmes

14-Mar-09 16:17

SQL (except int 2008) stores the data as a datetime type, this includes both the data and the time components. It is up to you to FORMAT the data to meet your needs as Ashfield pointed out. You will still be dealing with a datetime type.

Never underestimate the power of human stupidity
RAH

SQL Insert Creates multiple records instead of just one

Shrimpersfan12-Mar-09 23:48

Shrimpersfan

12-Mar-09 23:48

Hi there, I have created an insert statement to create a new record in a database. This is used from a task automation program and the client record is mapped using that so records ARE created on the correct client but i only want 1 record to be created, at the moment if a client has 10 quotes then it will insert 10 new quotes instead of just 1. My code is below:

CREATE PROCEDURE [dbo].[ietsp_CreateDODQuotes]

@ContactID AS INT,
@TaxYear AS INT

AS

SELECT
@TaxYear = TaxProYear
FROM
FlightDeckTest.dbo.iet_TaxYears ty
WHERE
IsCurrent = 'T'

/** Add PTM DOD Quote **/
BEGIN
INSERT INTO FlightDeckTest.dbo.Lead
(
ContactID,
Owner,
Status,
EntitySubType,
ietCreateDate,
ietMumbaiStatus,
ietTaxAdmin,
ietTaxYear,
ietQuoteIncl,
ietQuoteExcl,
ietVAT,
ietProdServID,
ietProdServDesc,
ietPayMethod,
ietCardType,
ietCardHolder,
ietCardNumber,
ietAcctNo,
ietSortCode,
ietIssueNumber,
ietSecurityCode,
ietInstallments,
ietMonthRepay,
ietFinalWithIns,
ietFinalInstallment,
ietAcctName,
ietBankName
)

SELECT
l.ContactID,
l.Owner,
'AWAUTH',
l.entitySubType,
GETDATE(),
0,
l.ietTaxAdmin,
@TaxYear,
l.ietQuoteIncl + CONVERT(DECIMAL(7,2),176.25),
l.ietQuoteExcl,
l.ietVAT,
l.ietProdServID,
'PTM DOD - ie taxguard',
'INVTP',
l.ietCardType,
l.ietCardHolder,
l.ietCardNumber,
l.ietAcctNo,
l.ietSortCode,
l.ietIssueNumber,
l.ietSecurityCode,
l.ietInstallments,
l.ietMonthRepay,
l.ietFinalWithIns,
l.ietFinalInstallment,
l.ietAcctName,
l.ietBankName
FROM
FlightdeckTest.dbo.Lead l
INNER JOIN
FlightDeckTest.dbo.Contact c ON
l.ContactID = c.ContactID
WHERE
((l.ietProdServDesc LIKE '%PTM%' OR l.ietProdServDesc LIKE '%Tax Return%')) AND
(l.Status = 'ACT') AND
(l.ietTaxYear IN (SELECT TaxProYear FROM iet_TaxYears WHERE IsCurrent = 'T'))

END
GO

Any help would be greatly appreciated.

Re: SQL Insert Creates multiple records instead of just one

Michael Bookatz13-Mar-09 0:33

Michael Bookatz

13-Mar-09 0:33

your not summing anything to only make one record. It will insert all the records that match your where clause so if it matches more then one will insert more then one record.

and please put your code into a code block it makes it easier to read!

Big Problem in sql server 2000

pubududilena12-Mar-09 22:02

pubududilena

12-Mar-09 22:02

Hi All,

first I installed SQL SERVER 2005 dev edition in my machine and after that I have uninstall it. so I have installed sql server 2000 enterprice in my machine.

BUT when I am going to connect to sql server 2000 through my application, I have received following error message:-

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

This error message is belong to sql server 2005 and NOT FOR sql server 2000 !!!.

but there is no sql server 2005 in my machine at the moment.so how can i get sql server 2005 error message for sql server 2000 ?

please help .

Thanks in advance..

Re: Big Problem in sql server 2000

Reza Raad14-Mar-09 11:11

Reza Raad

14-Mar-09 11:11

firstly what is your machine OS?
Is it Windows XP or VISTA?
You can install sql server enterprise edition on Server OS . none of above OSs allow you to install enterprise.
secondly you can see your sql server services and find that if you have sql server 2000/2005 instances. you must go to Control panel-> administrative tools->services
then find all MSSQL services or something like this.

Human knowledge belongs to the world
http://www.rad.pasfu.com/index.php

How to store all rows of a column in a sql variable

Rajesh_K_Sharma12-Mar-09 21:09

Rajesh_K_Sharma

12-Mar-09 21:09

Hi All,
I have A Table, which contains 40000 records. I want to store values of all rows of a column in a sql variable.
the column daytatpe is Varbinary. there is ID column also.
I need to append these records order by ID (ASC) when I appent rows in a variable.

I did something like this:-
Table Schema is
Create table TempTable
(
ID Identity int,
ColData varbinary(max)
Colchange varbinary(max)
)

DECLARE @ActData varbinary(max)
DECLARE @ActChainage varbinary(max)
SET @ActData = cast('' as varbinary)
SET @ActChainage = cast('' as varbinary)

SELECT @ActData = @ActData + ColData , @ActChainage = @ActChainage + Colchange from TempTable
Order by Id

select cast(@ActChainage as varchar(max))

When I use "Order by ID" then query return only last record, but without using "Order By ID" it returns all records in a sql variable, but not in sort order. I need Order by ID.

Can any help me out to solve this problem?

Regards,
Rajesh

rajesh

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