Database

SQL Insert Creates multiple records instead of just one

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

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

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

Blue_Boy12-Mar-09 21:49

Blue_Boy

12-Mar-09 21:49

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.

Attaching and detaching a DB from another computer

Mustafa Ismail Mustafa12-Mar-09 7:40

Mustafa Ismail Mustafa

12-Mar-09 7:40

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?

Loading large XML files into db2

Gktony12-Mar-09 0:46

Gktony

12-Mar-09 0:46

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

Transaction Log Backup Too Big

Elizma11-Mar-09 23:40

Elizma

11-Mar-09 23:40

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

Re: Transaction Log Backup Too Big

Eddy Vluggen12-Mar-09 3:50

12-Mar-09 3:50

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

I are troll Smile | :)

Multiple Databases

Brendan Vogt11-Mar-09 20:38

11-Mar-09 20:38

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

Eddy Vluggen12-Mar-09 4:08

12-Mar-09 4:08

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

Brendan Vogt14-Mar-09 20:32

14-Mar-09 20:32

Thanks, but please see below my reply Smile | :)

David Mujica12-Mar-09 4:40

David Mujica

12-Mar-09 4:40

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

Maybe I just ranted on something that has nothing to do with your question. D'Oh! | :doh:

Brendan Vogt14-Mar-09 20:31

14-Mar-09 20:31

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

Eddy Vluggen15-Mar-09 0:40

15-Mar-09 0:40

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

I are troll Smile | :)

Brendan Vogt15-Mar-09 5:25

15-Mar-09 5:25

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

Eddy Vluggen15-Mar-09 5:47

15-Mar-09 5:47

Like David[^] suggested, I'd go for replication Smile | :)

I are troll Smile | :)

Replicate

David Mujica15-Mar-09 5:26

David Mujica

15-Mar-09 5:26

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

Re: Replicate

Brendan Vogt15-Mar-09 5:54