|
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"));
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.
|
|
|
|
|
I want to store string character to be stored as password in Oracle table.
|
|
|
|
|
Sounds like you want to store the password similar to "*****" into Oracle?
I are troll 
|
|
|
|
|
Yes the password needs to be stored "*****" or any other format in Oracle
|
|
|
|
|
Yes the password needs to be stored "*****" or any other format in Oracle
|
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
Try this
select convert(varchar(8),getdate(),114)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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..
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Why you don't use temporary table instead of sql variable?
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.
|
|
|
|
|
Recently, one of my computers crashed and I managed to recover the DB files, both mdf and ldf.
I setup another box for work and install SQL Server 2005 (Workgroup Edition which is the customer's copy).
Now when I try to reattach I get this:
Database 'RCH' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Suggestions?
|
|
|
|
|
I am trying to load a large XML file into pureXML field in db2 9.5. The XML is too large to fit inside the INSERT statement so I have to use the load or the import tools in db2. However it doesn't work
The statement that executes the load is
CONNECT TO Databasename
LOAD FROM "C:\XMLData\XML1.xml" OF DEL METHOD P (1) MESSAGES "C:\XMLData\log.txt" INSERT INTO XMLTABLE (XMLCOL) COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET
It seems that is successfully executed and I get no errors, however when I open the table empty. No rows at all. The table has only one column the XMLCOL without any indexing.
Please assist
|
|
|
|
|
Hi Guys
I hope someone will be able to give me advice on this. Our client have a service that trancates a particular table every 30 minutes. Then it re-inserts information into this table, run update statements to update certain fields with values from formulas. The problem is that now there is quite a number of unnecessary transactions in the transaction log.
This results into the backup (with the trancate option) file to be about almost 20GB (I know!!!!) per day. So, my question: Is there any way possible that I can exclude this particular table's transactions from being logged? Could someone maybe give me advice on how I can get this transaction log file size smaller.
Your help will be greatly appreciated.
Elizma
|
|
|
|
|
You can shrink the logfile on demand. There's help on how to do that from the Management Studio here[^], or if you want to do this from SQL, look here[^].
That will only help until the file fills up again, or you'll have to hunt down the reason why the logfile is filling up. The documentation on the transaction log can be found here[^].
Good luck
I are troll
|
|
|
|
|
Hi,
I have a couple of databases, and I am trying to separate the users tables from all these databases, and put them into one table. So this means I will have to create a separate database that handles my memberships.
How does it affect performance if i work across databases? And how is it possible to have a foreign key link up to a primary key if both these tables are in separate tables?
How does a website like Amazon have multiple websites, and have products display over multiple websites? Surely they don't capture the same product for each instance of Amazon?
Regards
|
|
|
|
|
Logging in to two servers will take more time than logging in to a single server. You can create a linked server in SQL Server, and get it's contents with a "SELECT * from [server][schema][table]" etc., but that will be a bit slower than a local table.
I guess that they replicate some tables across multiple databases (assuming that they're even using some sort of SQL)
HTH,
I are troll 
|
|
|
|
|
Thanks, but please see below my reply
|
|
|
|
|
You may want to re-think how your application works to simplify the data storage.
If I understand you correctly, do you have the following situation ?
Given multiple companies A,B,C,etc each having employees Tom, Dick and Harry, etc.
Within one table you want to store all employees for all of your companies.
One way to make a primary key would be to use a composite key of CompanyID + EmpID.
If you extend this type of logic across all of your tables, you can have an application that supports many companies all within the same database schema.
This is one way a Software-As-A-Service provider handles this situation. There are many complications to this model, but it could work for you.
Pros:
1) Easy backup. One database, everything is done in one shot.
2) Everyone is using the same schema design level; one application version to support.
Cons:
1) Nearly impossible to provide a rollback for a given company.
2) An applicaiton upgrade will force all of your companies to upgrade.
You can think of a whole pile of other pros and cons.
Hope this gives you something to think about.
Maybe I just ranted on something that has nothing to do with your question. 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
