double vs decimal in high-frequency db

I am storing financial data (stock prices, profit and loss figures, etc..) i
n
a SQL 2005 database. The database will be updated with a high frequency. I a
m
debating whether to use decimal or double/float to store these values and I
am concerned that using decimal will slow down the database. Several
questions:
If I am updating the db with prices at 1000 times/second, will there be a
significant performance difference if I use decimal to store the prices?
If I am performing calculations on another table with the P&L data using a
stored procedure at 1 time/ second, will there be a significant performance
difference if I use decimal to store the P&L data?
If I went with double over decimal, can I round after the calculation? Will
that be faster than just using decimal?
Thank you,
JasonCP Developer wrote:
> I am storing financial data (stock prices, profit and loss figures, etc..)
in
> a SQL 2005 database. The database will be updated with a high frequency. I
am
> debating whether to use decimal or double/float to store these values and
I
> am concerned that using decimal will slow down the database. Several
> questions:
> If I am updating the db with prices at 1000 times/second, will there be a
> significant performance difference if I use decimal to store the prices?
> If I am performing calculations on another table with the P&L data using a
> stored procedure at 1 time/ second, will there be a significant performanc
e
> difference if I use decimal to store the P&L data?
> If I went with double over decimal, can I round after the calculation? Wil
l
> that be faster than just using decimal?
> Thank you,
> Jason
There is no built-in type called DOUBLE.
It's certainly possible that the datatype will affect performance but
that's something best tested for yourself with your own data and
queries.
FLOAT is surely going to be less suitable for financial data because
it's an inexact numeric. You'll lose decimal precision that way. My
inclination would be to choose DECIMAL unless you find a compelling
reason to do otherwise.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas wrote:
> FLOAT is surely going to be less suitable for financial data because
> it's an inexact numeric. You'll lose decimal precision that way. My
> inclination would be to choose DECIMAL unless you find a compelling
> reason to do otherwise.
Just out of interest, what would be a situation where FLOAT would be
preferable to DECIMAL?|||I think you are getting the cart before the horse. First define the
data requirement, then choose the data type among those that fit the
requirement.
FLOAT is imprecise for storing decimal fractions - read the Books on
Line. It seems unlikely that "pretty close" is sufficient for
financial data, but you are the one who has to make that
determination. Only after you are certain that such imprecision is of
no consequence to the system can questions of performance be a factor.
That said, I do not believe you will have a measureable difference
either way. When taken as a percentage of the overall processing to
perform an UPDATE, the difference will probably not be noticeable.
But just to keep me honest, I just set up two tables of over 1 million
rows each (1048576 to be precise). One has a column declared FLOAT,
the other has one declared DECIMAL(30,10). I ran mass UPDATEs against
both tables, with "SET nbr = nbr * 1.111" as the change. The
estimated execution plans for the two updates favored the FLOAT
version 46.27% against 53.73% for the DECIMAL. And execution times
for the 1048576 updates were consistently 5 seconds for FLOAT and 6
seconds for DECIMAL.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 14:46:02 -0700, CP Developer
<steved@.newsgroup.nospam> wrote:

>I am storing financial data (stock prices, profit and loss figures, etc..)
in
>a SQL 2005 database. The database will be updated with a high frequency. I
am
>debating whether to use decimal or double/float to store these values and I
>am concerned that using decimal will slow down the database. Several
>questions:
>If I am updating the db with prices at 1000 times/second, will there be a
>significant performance difference if I use decimal to store the prices?
>If I am performing calculations on another table with the P&L data using a
>stored procedure at 1 time/ second, will there be a significant performance
>difference if I use decimal to store the P&L data?
>If I went with double over decimal, can I round after the calculation? Will
>that be faster than just using decimal?
>Thank you,
>Jason|||CP Developer (steved@.newsgroup.nospam) writes:
> I am storing financial data (stock prices, profit and loss figures,
> etc..) in a SQL 2005 database. The database will be updated with a high
> frequency. I am debating whether to use decimal or double/float to store
> these values and I am concerned that using decimal will slow down the
> database. Several questions:
> If I am updating the db with prices at 1000 times/second, will there be a
> significant performance difference if I use decimal to store the prices?
As long as you are only storing the data, I cannot see that it matters.
But the problem with decimal is that you have to decide for a number
of decimals. Say that you settle for six decimals. You can bet that
one day there is a fund which is quoted in eight decimals.

> If I am performing calculations on another table with the P&L data using
> a stored procedure at 1 time/ second, will there be a significant
> performance difference if I use decimal to store the P&L data?
The only way to find out, is to benchmark.

> If I went with double over decimal, can I round after the calculation?
> Will that be faster than just using decimal?
Float sounds more appealing to me, for the simple reason that you will
have less problem with losing precision in intermediate results.
Yes, you can round float values, but keep in mind that an exact number
with three decimals, say 7.234 cannot be exactly represented in float,
so that you in reality have something like 7.2340000000000000001 or
7.23399999999999999989 depening on how you arrived to that number.
But all that is manageable.
I work with system for securities trading, including P%L computation,
and we use float for prices, amounts, quantities etc. You have to under-
stand the pitfalls, but after all these years, I think I do. I have
less feeling for the pitfalls with decimals, so I don't think I want
to change.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 31 May 2006 15:21:42 -0700, Chris Lim wrote:

>David Portas wrote:
>Just out of interest, what would be a situation where FLOAT would be
>preferable to DECIMAL?
Hi Chris,
Usually in scientific calculations.
Hugo Kornelis, SQL Server MVP

double unique index

Hello.

I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:

Row 1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 2

this shouldn't be possible.

Row1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 3

this should be possible

is there any way I can do this?

thanks in advance

You can create a UNIQUE constraint or a unique INDEX.

ALTER TABLE YourTable ADD CONSTRAINT UK_YourTable_ColumnA_ColumnB UNIQUE (ColumnA,ColumnB)

Or

CREATE UNIQUE INDEX IX_YourTable_ColumnA_ColumnB ON YourTable (ColumnA,ColumnB)

The constraint or index can also be clustered if you do not have a clustered PK/index/constraint yet.

Double underlines

Using the Bottom Border Style Double seems to have effect. I still get a
single underline. This is the case when rendering through the Report Manager.
If I remember correctly it did work in RS2000. Anybody know how to accomplish
this in RS2005 ?I found that in I needed to set the width to at least 3pt before I could see
the double lines. 1pt for the top, 1pt for the space, and 1pt for the
bottom.
...kind of makes sense.
"Claude" <Claude@.discussions.microsoft.com> wrote in message
news:335726B4-BC8B-4D32-AB86-E1EE27E7BEEC@.microsoft.com...
> Using the Bottom Border Style Double seems to have effect. I still get a
> single underline. This is the case when rendering through the Report
> Manager.
> If I remember correctly it did work in RS2000. Anybody know how to
> accomplish
> this in RS2005 ?|||Great stuff. Cheers Joe.
Claude
"Joe L" wrote:
> I found that in I needed to set the width to at least 3pt before I could see
> the double lines. 1pt for the top, 1pt for the space, and 1pt for the
> bottom.
> ...kind of makes sense.
>
> "Claude" <Claude@.discussions.microsoft.com> wrote in message
> news:335726B4-BC8B-4D32-AB86-E1EE27E7BEEC@.microsoft.com...
> > Using the Bottom Border Style Double seems to have effect. I still get a
> > single underline. This is the case when rendering through the Report
> > Manager.
> > If I remember correctly it did work in RS2000. Anybody know how to
> > accomplish
> > this in RS2005 ?
>
>

double threading a procedure?

Over some time now, I've been developing a fairly hefty stored procedure, that does a lot of computations, and fairly few table lookups.

When I look at the performance on my server (a dual Xeon HT) I can see that it only uses 1 out of 4 possible "cpus" to work on the calculations, while the three others idle out, and was wondering if I can somehow force it to use max available CPU power?The optimizer is usually pretty good about choosing the execution plan. But it can be influenced.

Have you checked your MAXDOP setting? If it is set to 1, you can bump it up. But as with all advice, caveat executor!|||Me personally, I would have compartmentalized the sproc into smaller units of work, i.e., I would have made many sprocs instead of a mongo one.

To get the to run independantly, we have in the past set these many sprocs to be executed as jobs, then have a sproc that launches all of the jobs. The launch will be serial, but the execution would not be.

With one big sproc, everything is serial, and you are not pushing the CPU hard enough anyway. If you want additional ways to thread, you should set up your tables as partitioned views. then the CPU can thread out.

Other than that, with your methodology, there is no way where it will thread, or use additional cpus

MOO

Double Table Insert

Tables :

EmailUsers
ID int - PK
Email nvarchar(256)

ListsUsers
ListID int - FK to List Table - Combo PK
UserID int - FK to EmailUsers Table - Combo PK

When a person adds a user I need to:
A. insert them as a new entry into EmailUsers - no problem
B. insert their EmailUsers.ID from step A and ListID (passed in parameter) into ListsUsers - not so easy
C. if they're already in EmailUsers don't insert them but pass their existing EmailUsers.ID to part B

Any thoughts or examples I can follow? Maybe it's easier to do two seperate queries and control the if exists logic in asp.net?I would check out the starter kits--such as the TimeTracker. They illustrate the following:

if select Count(*) from emailusers where .. = 0
Begin
Begin Transaction

insert into Email users

select @.id = @.@.identity

if no error
continue

insert into ListUsers
(x, @.id)

Commit Trans
end
else
select @.id = id from emailusers

insert into ListUsers
(x, @.id)

Hope this points you in the right direction|||

CREATE PROCEDURE SaveUser
 @.EmailAddress VARCHAR(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 SELECT @.UserID = ID FROM EmailUsers WHERE Email = @.EmailAddress
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO EmailUsers (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 END
 INSERT INTO ListUsers (ListID, UserID) VALUES (@.ListID, @.UserID)

|||I'd say use SCOPE_IDENTITY() rather than @.@.IDENTITY.. if there was another insert at the same time @.@.IDENTITY will return the id of that insert. SCOPE_IDENTITY() will work within the scope of the insert..

hth|||ndinakar: Thanks for that little tidbit, I didn't realize there was a potential for scope issues with @.@.IDENTITY.|||it should be ok to use @.@.IDENTITY as there could be 1/1000 ( or more or less...just a guesstimate) chance of your insert coinciding with another one but that could very well runi your day...so...check out BOL for more info on them...

hth|||I was in Vegas for the past week. Will give the suggestions here a try on Monday morning.

Thanks!|||Works great but I'm slightly confused on my return value

CREATE PROCEDURE [dbo].[Add_List_Users]
 @.EmailAddress nvarchar(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 DECLARE @.returnCode INT
 SELECT @.UserID = ID FROM Email_Users WHERE Email = @.EmailAddress
 SET @.returnCode = 0
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO Email_Users (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 SET @.returnCode = @.returnCode + 1
 END
if not exists (select * from Lists_Users where ListID = @.ListID and UserID = @.UserID)
 INSERT INTO Lists_Users (ListID, UserID) VALUES (@.ListID, @.UserID)
 SET @.returnCode = @.returnCode + 2
return @.returnCode
GO

Scenarios:
User Added to Users Table and List Table - returnCode 3
User exists in Users Table and Added to List Table - return code 2
User exists in both tables - return code 0

Actual Return Values:
2
1
-1

That's fine, I just check for 2, 1, -1 instead of 3, 2, 0 but I'd like to know why the values returned aren't logically what they should be?

SET @.returnCode = 0
SET @.returnCode = @.returnCode + 1
SET @.returnCode = @.returnCode + 2
shouldn't that = 3??

It's as if @.returnCode starts off at -1 since all the return values are 1 less than I expect them to be.|||The SCOPE_IDENTITY really comes into play if you've got hidden inserts. For example, another insert triggered from your original insert. That's where simply returning @.@.IDENTIY becomes totally wrong. Like the poster said, use SCOPE_IDENTITY and you can sleep at night!|||plus..you might want to look at OUTPUT Parameters to return the id ..check out BOL for more info on OUTPUT parameters..

hth

Double summation

I have some data -- counts ID'd by location and grid East like this --
Loc East N
CA 100 3
CA 103 5
CA 109 2
CA 110 3

I'm interested in the total of N on either side of the largest gap in
Eastings.
In this case the largest gap is 6 (between 103 and 109), and the sum of
N for the 2 rows below the gap is 8, and for the 2 above the gap it's
5.

The problem is to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings
per location, but only one largest gap. (If there are two largest
gaps, it
does't matter which one is used for the sums.)

I can do this with multiple passes -- first locate the largest gap,
then go
back and locate the Eastings on either side, then sum up the Ns.
That's
realy clumsy, I can't figure out how to do it more quickly, and I'm not
sure
what I'm doing is right. Any help would be appreciated.

Thanks,
Jim GeissmanJim,

CREATE TABLE a(Loc CHAR(2), East INT, N INT)
go
INSERT a VALUES('CA', 100, 3)
INSERT a VALUES('CA', 103, 5)
INSERT a VALUES('CA', 109, 2)
INSERT a VALUES('CA', 110, 3)
INSERT a VALUES('OR', 100, 3)
INSERT a VALUES('OR', 108, 5)
INSERT a VALUES('OR', 109, 2)
INSERT a VALUES('OR', 110, 3)
INSERT a VALUES('WA', 108, 5)
INSERT a VALUES('WA', 109, 2)
INSERT a VALUES('WA', 110, 3)
INSERT a VALUES('WA', 115, 3)

SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1

SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1

Loc East BeforeGap AfterGap GapSize rn
-- ---- ---- ---- ----
-------
CA 103 8 5 6 1
OR 100 3 10 8 1
WA 110 10 3 5 1

(3 row(s) affected)

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thank you very much, Alex. That's tremendous.
I learned three new things -- row_number, over and partition.
No wonder I was having trouble.

Thanks again,
Jim

Quote:

Originally Posted by

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'
You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
Anith
|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id =
'1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo =
'soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
thank
francesco
Anith Sen ha scritto:

> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith
|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'[vbcol=seagreen]
What if there are more than one row in tLabel for Label_id value 1?
[vbcol=seagreen]
What if there are more than one row in tGenere for generi_tipo value 'soul'?
[vbcol=seagreen]
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
Anith

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
--
Anith|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id ='1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo ='soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
--
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
--
thank
francesco
Anith Sen ha scritto:
> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'
>> "
What if there are more than one row in tLabel for Label_id value 1?
>> cantanti_genere_id=the value coming "from tGenere where generi_tipo =>> 'soul'"
What if there are more than one row in tGenere for generi_tipo value 'soul'?
>> to do this i want to use the subquerys!
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
--
Anith

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
Anith|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id =
'1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo =
'soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
--
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
--
thank
francesco
Anith Sen ha scritto:

> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'[vbcol=seagreen]
What if there are more than one row in tLabel for Label_id value 1?
[vbcol=seagreen]
What if there are more than one row in tGenere for generi_tipo value 'soul'?
[vbcol=seagreen]
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
Anith

Double SQL Join Problem through ODBC

I have experienced problems issuing SQL containing multiple joins through an ODBC connection. I read a thread here, which suggested an alternative to using multiple joins, this I tried and it appears to work. The code is as follows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, MAX(CASE WHEN SalesRep.Rowid = tblCust.SalesMan THEN SalesRep.Name END) AS SalesManNa, MAX(CASE WHEN SalesRep.Rowid = tblCust.AccManager THEN SalesRep.Name END) AS AccManNa
FROM Customers tblCust INNER JOIN SalesRep ON tblCust.SalesMan = SalesRep.Rowid OR tblCust.AccManager = SalesRep.Rowid
WHERE (RowID = '1126318')
GROUP BY tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager

However, I would like to know how efficient such code is compared to my original (and problematic) joining.

The following is a summary of my original problem

I have one view (Customers) and a table (SalesRep). Customers contains a list of customer companies, while SalesRep lists internal staff. Each company record has fields to identify a Sales contact and an Account Manager, both of which would be listed within the SalesRep table and could be the same person.

It seems fairly straightforward, we are using two INNER JOIN's between the tables, as follows simplified query shows:

SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')

This works fine when executed on SQL Server, as following result:

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--John Smith--Carol Flood

However, when issued using ASP through an ODBC connection to an Intersystems Cache database - SalesManNa is showing the result expected for AccManNa, as...

AccountNo--SalesMan--AccManager--SalesManNa--AccManNa
1234567--100--106--Carol Flood--Carol Flood

Interestingly, the above query does not return any results when executed through Vis Web Dev 2005 Express.

We are using a DSN for the connection, which uses the InterSystems Cache ODBC driver (5.2.0.329.0). I have tried many variations on the join and different syntax but no success and I'm not privy to any specific ODBC restrictions.

Any pointers as to what may be causing the problem through ODBC would be greatly appreciated as would any opinion of the efficiency of the working solution.

Many thanks,
CliffSELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager, salesRepTbl1.Name AS SalesManNa, salesRepTbl2.Name AS AccManNa
FROM Customers tblCust
INNER JOIN SalesRep salesRepTbl1 ON tblCust.SalesMan = salesRepTbl1.Rowid
INNER JOIN SalesRep salesRepTbl2 ON tblCust.AccManager = salesRepTbl2.Rowid
WHERE (tblCust.Rowid = '1126318')
In terms of performance, I would suggest to try the following instead:SELECT tblCust.AccountNo, tblCust.SalesMan, tblCust.AccManager,
s1.Name AS SalesManNa, s2.Name AS AccManNa
FROM (SELECT AccManager, SalesMan
FROM Customers
WHERE Rowid = '1126318') AS tblCust
INNER JOIN SalesRep s1 ON tblCust.SalesMan = s1.Rowid
INNER JOIN SalesRep s2 ON tblCust.AccManager = s2.Rowid(Sorry, I've no hands-on experience with SqlServer, so I might be wrong about the performance improvement.)|||Many thanks for your reply Peter.

I have tried your suggestion and similar to my own 2x INNER JOIN code, it also returns incorrect data when ran through ASP to Cache ODBC data source...it gets the correct salesman and account manager ids from the Customer table but the join to the SalesRep table returns the account manager name for both!|||I believe that your original code executed on SQL Server would be slightly more efficient, however there are often problems with ODBC drivers which are reluctant to join to the same table more than once.... I think it is an issue with labelling tables within joins... and so you're original SQL code would not return the correct values through the ODBC connection.

I think you're amended code would be the most efficient way to get this resultant recordset using the current ODBC connection.

:)|||The most efficient way, as always, is to create this as a view or stored procedure. I'm betting that would solve your mysterious problem as well.|||I used to date a girl that was double jointed

Double space replaced with single space by dbms ?!

This is driving me bananas. Can't find any info on this anywhere...
SQL 2000 seems to replace double space with a single space when I set
a varchar field to " " (2spaces), it only stores " " (1space). Why
on earth would microsoft do this? If I save 2 spaces - I WANT TO SEE
2 SPACES!!!!

Can anyone help? Is this a database setting? Is this due to using
varchar?

Any help appreciated.

Colin Hale[posted and mailed, please reply in news]

Colin Hale (colinhale@.radiusplc.co.uk) writes:
> This is driving me bananas. Can't find any info on this anywhere...
> SQL 2000 seems to replace double space with a single space when I set
> a varchar field to " " (2spaces), it only stores " " (1space). Why
> on earth would microsoft do this? If I save 2 spaces - I WANT TO SEE
> 2 SPACES!!!!
> Can anyone help? Is this a database setting? Is this due to using
> varchar?

Could you please post a repro that demonstrates the problem? That is
a script with a CREATE TABLE statement, INSERT statements of the data.
Or if you add the data through some client code, please include
that client code.

I get double spaces from this script:

CREATE TABLE a(a varchar(10))
go
INSERT a VALUES ('a a')
go
select * from a

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It is trailing spaces that are the problem.
However I have found the answer Microsoft Knowledge Base Article -
296559. Even though ansi padding set to on for the database, once an
ALTER COLUMN command has been issued against the column, ansi padding
is then switched off !!! Seems the only way around this is to
recreate the table and transfer the data, unless anyone else has any
ideas?

Double Space Character

Hi,
I have table having a column whose value is the following expression.
="' " & Fields!AgencyNumber.Value & " ' ' " & Fields!MessageNumber.Value
I want to keep the double " " space characters but when the report is
rendered all is converted to single spaces.
Am I missing anything?
Thanks...You may be running into this IE limitation:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=25f13795-932d-4eaa-adf0-464c07398865&sloc=en-us.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:07ED8D5D-C98F-4C47-9016-135B161D5A72@.microsoft.com...
> Hi,
> I have table having a column whose value is the following expression.
> ="' " & Fields!AgencyNumber.Value & " ' ' " &
Fields!MessageNumber.Value
> I want to keep the double " " space characters but when the report is
> rendered all is converted to single spaces.
> Am I missing anything?
> Thanks...

Double Sided?

Hi,

I am using SQL Reporting Services 2000 - is it possible to make a report that prints double sided?This has nothing to do with report settings. This is a printer setting. If it supports double sided priniting then reporting services reports can be printed double sided.|||Hi, yes the printer does support duplex printing, but I can't see how to make a report that prints on two sides.

Double Sided Query

ListingLink
ID1 ID2 Active Date
9 2 1 2007-10-22 17:47:31.230
9 7 1 2007-10-22 17:47:32.137
9 6 1 2007-10-22 17:47:32.540
9 8 1 2007-10-22 17:47:33.010
3 9 1 2007-10-22 18:31:15.980

I have this table of Data which will Link to topics together, Problem is I need a Query that will produce 5 Rows if the Input is ID 9. Or One Row if the ID was 2.
Not Conditional just example inputs and outputs.

This will only give one side of the Results
SELECT Listing.sTitle,Listing.iListingID
FROM Listing
INNER JOIN ListingLink
ON listingLink.ID2 = listing.ilistingID
WHERE ListingLink.bIsActive = 1 AND ListingLink.ID1 =9 OR ListingLink.ID2 = 9hi nate Try this one::::

SELECT Listing.sTitle,Listing.iListingID
FROM Listing
INNER JOIN Listinglink
ON listing.ilistingID = listinglink.id1 or listing.ilistingid=listinglink.id2
WHERE ListingLink.bIsActive = 1 and ListingLink.ID1 =9 OR ListingLink.ID2 = 9|||Thanks that works a treat,

SELECT Distinct(Listing.sTitle),Listing.iListingID
FROM Listing
INNER JOIN Listinglink
ON listing.ilistingID = listinglink.iLIstingOneID or listing.ilistingid=listinglink.iLIstingtwoID
WHERE ListingLink.bIsActive = 1 and ListingLink.iLIstingOneID =@.ListingID OR ListingLink.iLIstingtwoID = @.LisitngID

I wrote another query which retrieves the Values except a particular ListingID (9) Turns up as Null so when I print it, the set is correct and requires no application Logic, just got to check which query is faster, will put the reply down.

Though I have a Feeling My Query is Slower

SELECT Distinct(Listing.sTitle),Listing.iListingID FROM Listing,ListingLInk Where Listing.iListingID = ListingLink.iLIstingtwoID AND
iLIstingOneID = 9
Union
SELECT Distinct(Listing.sTitle),Listing.iListingID FROM Listing Right JOIN ListingLink ON Listing.iListingID = ListingLink.iLIstingOneID AND
iLIstingTwoID = 9|||SELECT Distinct(Listing.sTitle) ... please don't do that

DISTINCT is not a function

DISTINCT applies to all columns in the SELECT, not just the one with parentheses around it|||On the back of Rudy's post; this article (http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx) is well worth a read.|||Always makes a lot more sense when you step back and look at it, thanks won't make that mistake again.|||Double sided?

Swedish Airline stewardesses?

Double replication

I have merge replication set up on Server A with the distributor also on Server A. I want to set up transactional replication on this same Server A, but with the subscribing Server B set up as the distributor. Is this possible? I've tried creating a ne
w publication, but it won't give me the option to use Server B as the distributor--I think probably because Server A is already configured. Is there anyway to achieve this? Thank you.
no each Publisher server has a single distributor.
Multiple publishers can share the same distribution database on a single
distributor, or they can have their own seperate distribution databases on a
single distributor, but a single publisher can only have 1 distribution
database which can be on the publisher, distributor or subscriber.
"Andrea Worley" <k_rage@.hotmail.com> wrote in message
news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
> I have merge replication set up on Server A with the distributor also on
Server A. I want to set up transactional replication on this same Server A,
but with the subscribing Server B set up as the distributor. Is this
possible? I've tried creating a new publication, but it won't give me the
option to use Server B as the distributor--I think probably because Server A
is already configured. Is there anyway to achieve this? Thank you.
|||So, is it possible to set up trans replication to another server even though the existing publication is for merge replication? (And by your answer, I would be using the same distributor) And if so, will it be possible to disable the trans replication in
the future without messing up the existing merge publication? Thank you for your answer.
-- Hilary Cotter wrote: --
no each Publisher server has a single distributor.
Multiple publishers can share the same distribution database on a single
distributor, or they can have their own seperate distribution databases on a
single distributor, but a single publisher can only have 1 distribution
database which can be on the publisher, distributor or subscriber.
"Andrea Worley" <k_rage@.hotmail.com> wrote in message
news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
> I have merge replication set up on Server A with the distributor also on
Server A. I want to set up transactional replication on this same Server A,
but with the subscribing Server B set up as the distributor. Is this
possible? I've tried creating a new publication, but it won't give me the
option to use Server B as the distributor--I think probably because Server A
is already configured. Is there anyway to achieve this? Thank you.
|||yes, you can replicate a table that is part of a merge publication using
transactional publication as long as its not transactional publication with
queued updating.
You can drop the table from the merge publication or the transactional
publication without breaking either publication.
The publications are completely independent of each other (except when you
are using queued with merge).
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
> So, is it possible to set up trans replication to another server even
though the existing publication is for merge replication? (And by your
answer, I would be using the same distributor) And if so, will it be
possible to disable the trans replication in the future without messing up
the existing merge publication? Thank you for your answer.
>
> -- Hilary Cotter wrote: --
> no each Publisher server has a single distributor.
> Multiple publishers can share the same distribution database on a
single
> distributor, or they can have their own seperate distribution
databases on a
> single distributor, but a single publisher can only have 1
distribution[vbcol=seagreen]
> database which can be on the publisher, distributor or subscriber.
> "Andrea Worley" <k_rage@.hotmail.com> wrote in message
> news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
also on
> Server A. I want to set up transactional replication on this same
Server A,
> but with the subscribing Server B set up as the distributor. Is this
> possible? I've tried creating a new publication, but it won't give
me the
> option to use Server B as the distributor--I think probably because
Server A
> is already configured. Is there anyway to achieve this? Thank you.
>
>
|||Hilary, I tried creating another publication for trans rep using the same distributor that is set up for the merge rep. I got the error: Error 14294 Supply either @.job_id or @.job_name to identify the job.
Searching on this error, I saw in a different post that you wrote that it is caused by having duplicate agent names (your post is below). How do I resolve this issue if I am setting up trans replication using the wizards? I don't see how I can change th
e names. Do I have to script out the replication for this to work? Thanks.
[vbcol=seagreen]
if you are keeping the existing publications/subscriptions intact and
creating new subscriptions/publications based on the scripts you have to
modify the publication name, the log reader agent name, the snapshot agent
name, and the distribution agent name. Otherwise you will get errors like
this.[vbcol=seagreen]
-- Hilary Cotter wrote: --
yes, you can replicate a table that is part of a merge publication using
transactional publication as long as its not transactional publication with
queued updating.
You can drop the table from the merge publication or the transactional
publication without breaking either publication.
The publications are completely independent of each other (except when you
are using queued with merge).
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
> So, is it possible to set up trans replication to another server even
though the existing publication is for merge replication? (And by your
answer, I would be using the same distributor) And if so, will it be
possible to disable the trans replication in the future without messing up
the existing merge publication? Thank you for your answer.[vbcol=seagreen]
single
> distributor, or they can have their own seperate distribution
databases on a
> single distributor, but a single publisher can only have 1
distribution[vbcol=seagreen]
> database which can be on the publisher, distributor or subscriber.
> "Andrea Worley" <k_rage@.hotmail.com> wrote in message
> news:C05E8AD2-6D42-4CE0-BF50-245381D2E841@.microsoft.com...
also on
> Server A. I want to set up transactional replication on this same
Server A,
> but with the subscribing Server B set up as the distributor. Is this
> possible? I've tried creating a new publication, but it won't give
me the
> option to use Server B as the distributor--I think probably because
Server A[vbcol=seagreen]
> is already configured. Is there anyway to achieve this? Thank you.
|||create another publication perhaps in your pubs database and replicate to
your northwind database. If this works you have a localized problem with
your publication database.
Script out your publication and subscriber and then drop it.
Change the publication name throughout your script to something you have not
used before.
Go through your entire script and remove all references to
@.snapshot_job_name, @.logreader_job_name, @.qreader_job_name if you have one.
Re run your script.
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:F356A1A1-E061-4F90-8905-3D41ECC16DA4@.microsoft.com...
> Hilary, I tried creating another publication for trans rep using the same
distributor that is set up for the merge rep. I got the error: Error 14294
Supply either @.job_id or @.job_name to identify the job.
> Searching on this error, I saw in a different post that you wrote that it
is caused by having duplicate agent names (your post is below). How do I
resolve this issue if I am setting up trans replication using the wizards?
I don't see how I can change the names. Do I have to script out the
replication for this to work? Thanks.
> if you are keeping the existing publications/subscriptions intact and
> creating new subscriptions/publications based on the scripts you have to
> modify the publication name, the log reader agent name, the snapshot agent
> name, and the distribution agent name. Otherwise you will get errors like
> this.
> -- Hilary Cotter wrote: --
> yes, you can replicate a table that is part of a merge publication
using
> transactional publication as long as its not transactional
publication with
> queued updating.
> You can drop the table from the merge publication or the
transactional
> publication without breaking either publication.
> The publications are completely independent of each other (except
when you
> are using queued with merge).
> "Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:C97EA05A-916E-4713-8319-05D794082EF1@.microsoft.com...
even
> though the existing publication is for merge replication? (And by
your
> answer, I would be using the same distributor) And if so, will it be
> possible to disable the trans replication in the future without
messing up[vbcol=seagreen]
> the existing merge publication? Thank you for your answer.
on a[vbcol=seagreen]
> single
> databases on a
> distribution
subscriber.[vbcol=seagreen]
> also on
same[vbcol=seagreen]
> Server A,
Is this[vbcol=seagreen]
give[vbcol=seagreen]
> me the
because[vbcol=seagreen]
> Server A
Thank you.[vbcol=seagreen]
|||The create publication does not work in the pubs database either. I think for now I am going to just set up some DTS jobs to bulk copy the data to a new database. Thanks for your help.
|||In this case you need to disable publishing (through the tools menu, select
Replication and then click disable publishing).
Then re-install and try again. If this fails try to reapply sp3.
"Andrea Worley" <anonymous@.discussions.microsoft.com> wrote in message
news:F5126947-6463-4AD5-9220-42B2FAE2CB7A@.microsoft.com...
> The create publication does not work in the pubs database either. I think
for now I am going to just set up some DTS jobs to bulk copy the data to a
new database. Thanks for your help.

Double Quotes!

What is the normal procedure for enclosing string variables in Dynamic TSQL.
I've tried using:
SET QUOTED_IDENTIFIER ON
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = "o" ' + //problem here!!!
Cheers,
Adam'qtype = ''o'''
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||Use 2 single-quotes when one is desired in the resultant string. For
example:
SET QUOTED_IDENTIFIER ON
declare @.sqlquery varchar(1000)
declare @.oqs int
SET @.oqs = 0
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
SELECT @.sqlquery
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eSkxjO8xFHA.2008@.TK2MSFTNGP10.phx.gbl...
> What is the normal procedure for enclosing string variables in Dynamic
> TSQL.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
>|||On Mon, 3 Oct 2005 12:39:00 +1000, Adam Knight wrote:

> What is the normal procedure for enclosing string variables in Dynamic TSQ
L.
> I've tried using:
> SET QUOTED_IDENTIFIER ON
> SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
> ' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
> 'FROM ' +
> 'asmt_v2_questions ' +
> 'WHERE ' +
> 'qtype = "o" ' + //problem here!!!
> Cheers,
> Adam
Use two single-quotes instead of the double-quotes.
Like
SET @.sqlquery = 'SELECT TOP ' + CAST(@.oqs As varchar) +
' NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url ' +
'FROM ' +
'asmt_v2_questions ' +
'WHERE ' +
'qtype = ''o'' '
Ayyappan Nair

Double quotes replacement

Hi,

It seems to be simple, however, it stumbles me.
how to replace all the double quotes (") within the following
sentence (or a column) with single quotes ('),

colA = this is a freaking "silly" thing to do
into
colA this is a freaking 'silly' thing to do

Select Replace(colA,'"',''')
>From tblXYZ

won't work,

Select Replace(colA,'"',"'")
>From tblXYZ

won't work neither.

How come? Thanks.You need to specify 2 single quotes within the literal string when 1 quote
is desired. Try:

Select Replace(colA,'"','''')
From tblXYZ

--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@.rock.com> wrote in message
news:1125889758.581518.99700@.g49g2000cwa.googlegro ups.com...
> Hi,
> It seems to be simple, however, it stumbles me.
> how to replace all the double quotes (") within the following
> sentence (or a column) with single quotes ('),
> colA = this is a freaking "silly" thing to do
> into
> colA this is a freaking 'silly' thing to do
> Select Replace(colA,'"',''')
>>From tblXYZ
> won't work,
> Select Replace(colA,'"',"'")
>>From tblXYZ
> won't work neither.
> How come? Thanks.|||Thank you very much, Dan, works perfect.

On a related note, BOL does not cover it, how could one find a solution
to a problem similar to this one without resorting to this NG?|||I found this from the 'quotation marks' entry in the Books Online index.
From the 'Using char and varchar data' topic:

<Excerpt href="http://links.10026.com/?link=acdata.chm::/ac_8_con_03_7mch.htm">
When using single quotation marks to delimit a character constant that
contains an embedded single quotation mark, use two single quotation marks
to represent the embedded single quotation mark, for example:

SET @.MyCharVar = 'O''Leary'
</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP

"NickName" <dadada@.rock.com> wrote in message
news:1125967013.923190.247670@.g14g2000cwa.googlegr oups.com...
> Thank you very much, Dan, works perfect.
> On a related note, BOL does not cover it, how could one find a solution
> to a problem similar to this one without resorting to this NG?|||Man, I can't read, ok, not careful, thanks.

Double quotes in error messages!

I have a problem when trying to display an error message from sql server 2005 on a web page with an alert (javascript command).

The Sql server 2005 returns a message like:

Insert statement conflicted with foreign key constraint "bla bla". The conflict occured indatabase "databasename", table "tablename", column 'columnname'.

In sql server 2000 the error message is the same except all names (constraint, database, table) is in single quotation marks; just like the columnname in the above example.

Is it a configurable issue on the sql server. I would prefer not to solve this issue on a number of different web pages!

Thanks in advance.

Futte

hi,

you should set this:

SET QUOTED_IDENTIFIER { ON | OFF }

When on, then identifiers can be delimited by double quotation marks.

Regards,

Janos

|||

I all ready tried that without any luck.......

I want to avoid the double quotation marks.

Thanks though

Futte

|||

If you wish to 'embed' single quotes in your message, you need to double them up -use two single quotes in each place of one single quote.)

Try this:

SELECT 'This is my ''message'' with quotes.'

|||

I think my question might be a little misunderstood.

My question is not about query syntax in sql regarding single or double quotation marks or two single quotation marks.

It's about the error message sql server is returning. I suddenly experienced that after upgrading sql server from 2000 to 2005 the response from sql 2005 contained double quotation marks around all object names except for one; namely the column name:

2000:

Insert statement conflicted with foreign key constraint 'constraint name'. The conflict occurred in database 'database name', table 'table name', column 'column name'.

2005:

Insert statement conflicted with foreign key constraint "constraint name". The conflict occurred in database "database name", table "table name", column 'column name'.

This is a problem when displaying the error message on a web page with javascript (alert). So if it's a configurable issue on the sql server where is it set? Otherwise I have to handle this error message somewhere else replacing the double quotes with single quotes.

Kind Regards

Futte

|||

Thanks for the clarification. I better understand the situation.

As far as I am aware, there is no user configurable setting to alter the presentation of the error message.

Double quotes in ASCII file

I've an issue with double-quotes in CSV file. One of the columns may contain this kind of value: "STATUS ""H"" "

I've got quote set to "

The file source fails on such records.

I found this thread and Scott tells us there that the file can't contain " in data.

Is this 100% correct?

I've got mutliple text columns and the pain is that I don't know which column might have these cases in future. To create a script means to write my own file parser for all files I use.

Any ideas?

Dima.

Hi,

If you have a CSV file, do you actually need to specify the text delimeter - or do you have some text that has commas in the string as well?

If your source file contains things like

"Status ""H"" ", "Next string", 1234, etc,

then I would try to solve the problem at source. That formatting suggests to me that the data is from another database where the actual data is ,Status "H", and the CSV export is trying to escape the " character by doubling it up.

Perhaps if you can export the CSV with no text delimiters, or with a character that is guaranteed not to appear, you may be OK.

Hope this helps,

Richard

|||

Richard,

you are correct in your gueses. I do have data like this:

"Status ""H"" ", "Next string", 1234, etc,

I can't remove quoation mark since I may have data like this:

"Status ""H"" ", "Next,string", 1234, etc,

So, if I removed " I'd have another error like this:

Status "H", Next,string, 1234, etc,

So, it wouldn't parse it correctly.

I can't change quotation, symbol since I don't control source system...

Now, what I could change is data within the column. So, I could replace:

Status "H"

with something like

Status &quot;H&quot;

Then when I read this - I'd convert it back to:

Status "H"

What do you think?

Dima

|||

Dima,

That looks like a solution to me - assuming you are going to make this change prior to bringing the data into the database, ie by pre-processing the CSV file.

What process are you going to use to make the changes? I'm sure you've thought of this, but if you were just to globally change "" to &quot then it will fall over when you three or more " in a row.

eg. """H"" is my status" should become "&quotH&quot is my status" , not &quot"H&quot is my status"

I've not tried it, but you can probably do this with an SSIS script of some sort, passing regular expressions. That would be better than having an external process change the file.

Regards,

Rich

|||

The file I'm dumping is on Unix. So, I was thinking about creating sed script, using regular expressions to post-process the file after it's dumped...

Thank for the feedback BTW it helped me think this thru

Double Quotes and Print

Hi:
This is a very simple thing but some how i have a mental block. what I am
trying to
do is concatenate a string that gets passed with double quotes to a single
quoted string and trying to print but I get an error. here is what I am
trying to do:
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
I get an error when i execute the above.
what i am looking is to print the following when i execute the above code.
so basically i want md printed with the drive name and the folder name in
double quotes besides it.
md "c:\bak"
Can anyone please fix what I am doing wrong. looks like i am missing few
quotes.
Thanks
M> SET @.cmd='md ' + @.drive
Enclose this string in single-quotes:
SET @.drive='"c:\bak"'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
> Hi:
> This is a very simple thing but some how i have a mental block. what I am
> trying to
> do is concatenate a string that gets passed with double quotes to a single
> quoted string and trying to print but I get an error. here is what I am
> trying to do:
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> I get an error when i execute the above.
> what i am looking is to print the following when i execute the above code.
> so basically i want md printed with the drive name and the folder name in
> double quotes besides it.
> md "c:\bak"
> Can anyone please fix what I am doing wrong. looks like i am missing few
> quotes.
> Thanks
> M
>
>|||Hi Dan:
Thanks for the reply. I tried that already before but my problem is the
drive name is passed as a parameter @.drive name from the stored procedure an
d
so when I concatenate the variable with single quotes i get an error.
something i did like this
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.drive=''+@.drive+''
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
Which still throws an error. The issue here is the drive name is passed as a
paramater to the sproc with double quotes like
Exec testprocedure @.drive="C:\bak"
So i would either need to replace the quotes i guess.
Any suggestions?
Thanks
"Dan Guzman" wrote:

> Enclose this string in single-quotes:
> SET @.drive='"c:\bak"'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meher" <Meher@.discussions.microsoft.com> wrote in message
> news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
>
>|||Hi,
How's this?
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive='c:\bak'
SET @.cmd='md "' + @.drive + '"'
PRINT (@.cmd)
Robert
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
> Hi:
> This is a very simple thing but some how i have a mental block. what I am
> trying to
> do is concatenate a string that gets passed with double quotes to a single
> quoted string and trying to print but I get an error. here is what I am
> trying to do:
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> I get an error when i execute the above.
> what i am looking is to print the following when i execute the above code.
> so basically i want md printed with the drive name and the folder name in
> double quotes besides it.
> md "c:\bak"
> Can anyone please fix what I am doing wrong. looks like i am missing few
> quotes.
> Thanks
> M
>
>|||Hi Robert:
The variable is passed in double quotes to the sproc and so i can have
single quotes around the c:\bak. Anyway I figured it out. I turn off the
Quoted_Identifier off and turn it back on and then perform the concatenation
.
Here is what I do:
SET QUOTED_IDENTIFIER OFF
DECLARE @.drive varchar(255)
DECLARE @.cmd varchar(200)
SET @.drive="c:\bak"
SET @.drive=""""+@.drive+""""
SET QUOTED_IDENTIFIER ON
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
"Robert Ellis" wrote:

> Hi,
> How's this?
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive='c:\bak'
> SET @.cmd='md "' + @.drive + '"'
> PRINT (@.cmd)
> Robert
>
> "Meher" <Meher@.discussions.microsoft.com> wrote in message
> news:93C59AF3-9C22-483E-BD96-9EF5BD3C1825@.microsoft.com...
>
>|||> Exec testprocedure @.drive="C:\bak"
> So i would either need to replace the quotes i guess.
No need to guess. There will be no quotes around the @.drive value with the
code you posted. The quotes (double or single) around the procedure
parameter value are used only as string enclosures and will not be included
in the value passed to the proc. All you need to do is add double-quotes
around the @.drive value:
ALTER PROC testprocedure
@.drive varchar(255)
AS
DECLARE @.cmd varchar(200)
SET @.drive='"'+@.drive+'"'
SET @.cmd='md ' + @.drive
PRINT (@.cmd)
GO
EXEC testprocedure @.drive="C:\bak"
GO
By the way, it's a good practice to use only single-quotes to encclose
string literals. You'll get the same result with:
EXEC testprocedure @.drive='C:\bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:4B8493D1-412F-4DA9-B1C1-BCCE0A8C126C@.microsoft.com...
> Hi Dan:
> Thanks for the reply. I tried that already before but my problem is the
> drive name is passed as a parameter @.drive name from the stored procedure
> and
> so when I concatenate the variable with single quotes i get an error.
> something i did like this
> DECLARE @.drive varchar(255)
> DECLARE @.cmd varchar(200)
> SET @.drive="c:\bak"
> SET @.drive=''+@.drive+''
> SET @.cmd='md ' + @.drive
> PRINT (@.cmd)
> Which still throws an error. The issue here is the drive name is passed as
> a
> paramater to the sproc with double quotes like
> Exec testprocedure @.drive="C:\bak"
> So i would either need to replace the quotes i guess.
> Any suggestions?
> Thanks
> "Dan Guzman" wrote:
>