|
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. 
|
|
|
|
|
Hi,
This is what I am trying to do. I have 2 websites running, each with it's database. One is a property website, the other a shopping website. I want to take all the users of both websites and put them into a centralised users table. The reason for this is to have 1 username and password that can be used for both websites. So my logic says to use a separate database that handles just user membership?
I can probably have all the tables (from both the property and shopping websites) in one database, but what happens if many users use the property and shopping website at the same time? Then SQL Server will go into overdrive and get slow? Or should I just get a faster server for this?
How else can this then be done?
Brendan
modified on Sunday, March 15, 2009 3:59 AM
|
|
|
|
|
You could create a shared login-page as a separate website, and redirect them to the appropriate website once authenticated.
What's the relation between the two projects? Why does your logic want a separate database for membership, does it feel like it's easier to maintain?
If the projects aren't intertwined, then by all means, keep 'em separate
I are troll
|
|
|
|
|
Hey,
I have a domain like:
http://shopping.mydomain.com and
http://property.mydomain.com
So I am looking for a solution that has 1 login table that is used for both websites, I could put both websites tables into 1 database, what happens if the traffic to the sites increases? Then database activity increases which slow everything up. That is why I want to have a separate database for each website. And I want users to be able to login once, and access both websites.
Brendan
|
|
|
|
|
Like David[^] suggested, I'd go for replication
I are troll
|
|
|
|
|
Ah, I now understand your issue.
Here is one last suggestion: Replicate your user/authentication tables.
The idea behind this is that it won't cause any change to your existing application logic and if the time comes where you want to sell one of your websites, you could just stop the replication. I'm not too afraid of performance problems because I doubt there is much write activity to these authentication tables and read access won't impact the replication process. The other upside to this is that you can bring a website down for maintenance without impacting the other website.
My 2 cents. 
|
|
|
|
|
Thanks guys!!
So how will my membership tables look like, will I have one in each database?
|
|
|
|
|
i have users Date Of Birth as DOB in sql server table called UsersProfile.
Person DOB
a 3/13/2009
b 3/13/2009
c 3/25/2009
d 4/2/2009
e 4/15/2009
How to select next(upcoming) birthday from this table?
if two persons bday falls on a same date, like Person a and Person b, how to select those two person?
|
|
|
|
|
On top of my head, I can think of something like:
Select min(DOB) from UsersProfile where DOB > GetDate()
for getting persons, we can have:
Select Person from UsersProfile where DOB =
( Select min(DOB) from UsersProfile where DOB > GetDate() )
Hope that helps.
Regards,
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
thnaks for replying, however i am not getting any data with those quaries
|
|
|
|
|
Strange.. It works for me. Here are my test statements:
declare @UsersProfile table
(
Person varchar(20),
DOB datetime
)
insert into @UsersProfile
select 'Person1', '2009-03-11'
union select 'Person2', '2009-03-13'
union select 'Person3', '2009-03-13'
union select 'Person4', '2009-03-16'
union select 'Person5', '2009-03-25'
select * from @UsersProfile
where DOB = ( select min(DOB) from @UsersProfile where DOB>GetDate() )
Are you sure your DOB column is of DateTime type?
Regards,
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
