change HH from 24 hrs to 12 hrs

I have a time value that I want to display in 12 hours instead of 24
hours(military time)
DATEPART(HH,@.ACTTIME)
How do I get 1 - 12 and instead of 0-23?Client side? Wouldn't it be nicer to let the end user choose how he/she
prefers to read the time?
Otherwise lookup the style parameter options on the CONVERT function.
David Portas
SQL Server MVP
--|||I hope the following helps you.
Declare @.ActTime DateTime
Set @.ActTime = '01/01/2005 11:15 PM'
Select Case
When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
ELSE DatePart(hh,@.ACTTIME) - 12
End
"LP" wrote:

> I have a time value that I want to display in 12 hours instead of 24
> hours(military time)
> DATEPART(HH,@.ACTTIME)
> How do I get 1 - 12 and instead of 0-23?
>|||It is called ISO-8601 and not "Military Time" and it is used in a ton
of ISO standards. Why do you wish to destroy the portability of your
data and violate standards?
The basic priniciple of a tiered architecture is that the applications
do the display work, not the database.|||No server side. It's for a static dimension table. I don't see the utility
in it but the client request pays the bills. I have most of it compete.
I'll include it tomorrow.
Thanks
"David Portas" wrote:

> Client side? Wouldn't it be nicer to let the end user choose how he/she
> prefers to read the time?
> Otherwise lookup the style parameter options on the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>
>|||No destruction in mind. Simply fulfilling a client request for a static
dimension table. The utility of this field is a bit in question...but it
pays the bills.
Thanks
"--CELKO--" wrote:

> It is called ISO-8601 and not "Military Time" and it is used in a ton
> of ISO standards. Why do you wish to destroy the portability of your
> data and violate standards?
> The basic priniciple of a tiered architecture is that the applications
> do the display work, not the database.
>|||Thanks for getting back with me. I found another way around the issue and
I'll post it tomotrrow because it might be helpfull for others.
Thanks again.
LP
"satheeshks" wrote:
> I hope the following helps you.
> Declare @.ActTime DateTime
> Set @.ActTime = '01/01/2005 11:15 PM'
> Select Case
> When DatePart(hh, @.ACTTIME) <= 12 Then DatePart(hh,@.ACTTIME)
> ELSE DatePart(hh,@.ACTTIME) - 12
> End
> "LP" wrote:
>

Change Graph type at run time

Hi
Can the user not change the graph type at run time the same way you can
change for example background colour. I want to have a parameter for the user
so he can choose line, bar or pie for the same graph.
Thanks
FrancoisSetting chart types dynamically is not supported in the current release.
However, you could approximate the desired behavior by using multiple charts
(of various types) and use an expression to dynamically hide all charts but
the one you want displayed.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Francois" <Francois@.discussions.microsoft.com> wrote in message
news:7CDB8D7C-2444-4C9B-916C-59A2A8946FB2@.microsoft.com...
> Hi
> Can the user not change the graph type at run time the same way you can
> change for example background colour. I want to have a parameter for the
user
> so he can choose line, bar or pie for the same graph.
> Thanks
> Francois

Change Drive Location for Log Files.

Hello,
I am attempting to change the location of the log files for one of the
default SQL instance, every time I click on the ... button under Properties
of the instance/Database settings/default log directory, I only receive the
single drive that the SQL Server instance was installed on, and not any of
the other active drives. Anyone have thoughts or instructions on this for me?
Thanks,
Jeremey
I take you have an additional drive which has been added to the MSCS
configuration as Physical Disk
If so then you must also take the SQLServer resource off-line and add a
dependency to the logs physical disk resource and bring sql server back
on-line. Then you'll be able to see the new disk on the Database Setting
property page.
Nik Marshall-Blank MCSD/MCDBA
"Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
> Hello,
> I am attempting to change the location of the log files for one of the
> default SQL instance, every time I click on the ... button under
> Properties
> of the instance/Database settings/default log directory, I only receive
> the
> single drive that the SQL Server instance was installed on, and not any of
> the other active drives. Anyone have thoughts or instructions on this for
> me?
> Thanks,
> Jeremey
|||That was the problem.
Thanks,
Jeremey
"Nik Marshall-Blank" wrote:

> I take you have an additional drive which has been added to the MSCS
> configuration as Physical Disk
> If so then you must also take the SQLServer resource off-line and add a
> dependency to the logs physical disk resource and bring sql server back
> on-line. Then you'll be able to see the new disk on the Database Setting
> property page.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Jeremey" <Jeremey@.discussions.microsoft.com> wrote in message
> news:B9C72029-3D58-4486-B485-AE649DCC6E81@.microsoft.com...
>
>

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQL
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.
Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.
|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/default...b;en-us;826161
http://support.microsoft.com/default...b;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:

> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.
|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
[vbcol=seagreen]
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the login
> password issue you are discribing. The latest is 8.00.819, you are probably
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database is
> attached, there is no corresponding DBID to default to. Moreover, those ids,
> although incremental, are reused. So, if you were to bring another database
> online before you reattached the original, the logins may be assigned to the
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/default...b;en-us;826161
> http://support.microsoft.com/default...b;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
sql

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQL
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;826161
http://support.microsoft.com/default.aspx?scid=kb;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the login
> password issue you are discribing. The latest is 8.00.819, you are probably
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database is
> attached, there is no corresponding DBID to default to. Moreover, those ids,
> although incremental, are reused. So, if you were to bring another database
> online before you reattached the original, the logins may be assigned to the
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826161
> http://support.microsoft.com/default.aspx?scid=kb;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
> > I have recently added SP3a to my SQL 2000 system and every time I detach a
> > database and then reattach I lose all my default database settings for my SQL
> > login/users. when I go to change the default database and select OK it
> > requires me to confirm the password.
> > How to I get around this so I do not have to always retype the password.

Change Default database Confirm password required

I have recently added SP3a to my SQL 2000 system and every time I detach a
database and then reattach I lose all my default database settings for my SQ
L
login/users. when I go to change the default database and select OK it
requires me to confirm the password.
How to I get around this so I do not have to always retype the password.Jacci
Why not to use BACKUP/RESTORE ?
"Jacci" <Jacci@.discussions.microsoft.com> wrote in message
news:84BD75D1-B541-4F33-BA63-FFACE2006D63@.microsoft.com...
> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
first messed up the DTSGUI.dll, the second, to fix this, messed up the login
password issue you are discribing. The latest is 8.00.819, you are probably
running build 818. Severice Pack 3a is build 760.
However, as far as the default databases goes, you can not fix this issue
since logins are assigned default databases by DBID. Once that database is
attached, there is no corresponding DBID to default to. Moreover, those ids
,
although incremental, are reused. So, if you were to bring another database
online before you reattached the original, the logins may be assigned to the
new database instead.
Here is the KB for the password issue:
http://support.microsoft.com/defaul...kb;en-us;826161
http://support.microsoft.com/defaul...kb;en-us;821277
Sincerely,
Anthony Thomas
"Jacci" wrote:

> I have recently added SP3a to my SQL 2000 system and every time I detach a
> database and then reattach I lose all my default database settings for my
SQL
> login/users. when I go to change the default database and select OK it
> requires me to confirm the password.
> How to I get around this so I do not have to always retype the password.|||Thank You - this worked ;-)
"AnthonyThomas" wrote:
[vbcol=seagreen]
> There are three version of the MS03-031 "Slammer Worm" virus hot fix. The
> first messed up the DTSGUI.dll, the second, to fix this, messed up the log
in
> password issue you are discribing. The latest is 8.00.819, you are probab
ly
> running build 818. Severice Pack 3a is build 760.
> However, as far as the default databases goes, you can not fix this issue
> since logins are assigned default databases by DBID. Once that database i
s
> attached, there is no corresponding DBID to default to. Moreover, those i
ds,
> although incremental, are reused. So, if you were to bring another databa
se
> online before you reattached the original, the logins may be assigned to t
he
> new database instead.
> Here is the KB for the password issue:
> http://support.microsoft.com/defaul...kb;en-us;826161
> http://support.microsoft.com/defaul...kb;en-us;821277
> Sincerely,
>
> Anthony Thomas
>
> "Jacci" wrote:
>

Change Date Format of Field value

What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.

I only want to remove Time from date and I am using Sql Server 2000.

Thanks

Arvind

Look at the cast and convert functions

I use cast(convert(varchar(10), getdate(), 101) as datetime)

sql

Change date format

HI,

I have noticed that SQL SERVER 2005 uses American date format but i want to change it to UK/European time format i.e. dd-mm-yyyy

can any on tell me how to do it in sql server settings. And is it possible that I can change it only the during the course of my STORED PROCEDURE.

I know it is quite and any one will answer that soon

hoping quick reply

regards,

Anas

I haven't used 2005 yet but assuming it has the same 'problem', make sure the language is set to 'british english' instead of 'english'|||

thanx for ur reply...

other query is that how will I able to change that setting for the course of my stored procedure? is it possible

waiting for your reply

regards,

Anas

|||That shouldn't be necessary once the database has been set to the correct language. But IIRC, you can set the language on a connection basis, but you should be able to locate this info in the SQLServer help file, I can't recall what the setting might be at the moment, probably SET LANGUAGE <language>

sql

Change Data Source at run time

Hi,
I am using a web Report viewer control for viewing reports on a SRS 2005
report server.
Is it possible to change the data source of the report at run-time?
e.g. I have 2 identical databases - the live database and a test
database. I want to be able to set the data source to point to one of
these depending whether I am testing or not.
This was easily achieved with Crystal, but I cannot work out how to do
it using SQL Reporting Services.
Any help apprectiated.
Thanks.
PaulIf you are using the VS 2005 viewer control then I think you can set it in
there. Another option might be to base the report on a stored procedure
that returns records based on a parameter sent. For example, if parameter =1 then select from live database and if = 0 select from test database.
David
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul|||see ths 2005 BOL at
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul

Change Crystal Database in vba

hi,

I have to use vba to display a report.
But this report should work on multiple database without having to change the report each time.
So I would like to change the report on runtime.
I think i can use LogOnServer (method of the application Object) or something.

Now i don't know how to use it. Looked for examples on the kb of crystal and this forum but wasn't succesfull to make it work.

Can somebody help me.

This is the code i use to call the report:

Set crxReport = crxApplication.OpenReport(MyReportFile)

crxReport.ParameterFields(1).AddCurrentValue (DocNumber)

Me!Crviewer1.ReportSource = crxReport

Me!Crviewer1.ViewReport

Crviewer1.Zoom (100)

While Me!Crviewer1.IsBusy
DoEvents
Wendreport.Database.Tables(1).SetLogOnInfo ServerName, DBName, UserID, Pwd

This will set the logon info for the table used in the report.

Change Body Size

Is there a way to change the report body size in execution time(from the
template),
depending on the result of an expression?.
--
Eduardo FonsecaTo decrease body size, decrease input, increase output, and increase
exercise time aka execution time aka CPU ;). In other words, do not
modify your data and do a lot of selects with CPU-hungry UDFs in WHERE
clause. Add WITH RECOMPILE to your stored procedures to further up
execution time. Just kidding. Sorry for the off topic. ;)

Change Body Size

Is there a way to change the report body size in execution time(from the
template),
depending on the result of an expression?.
--
Eduardo FonsecaTo decrease body size, decrease input, increase output, and increase
exercise time aka execution time aka CPU ;). In other words, do not
modify your data and do a lot of selects with CPU-hungry UDFs in WHERE
clause. Add WITH RECOMPILE to your stored procedures to further up
execution time. Just kidding. Sorry for the off topic. ;)

Change an instance of SQL Server 2005

Hi All,

I have two drive, C and D. When first time I installed SQL Server 2005, I think that I point to C drive which is having only 10 GB, instead of drive D which has bigger space. Now, I have a problem of restoring my database due to lack of space on my C drive. Can anyone guide me to change the instance of database from drive C to drive D.

TIA

I am afraid you want to move data from C: to D: .

Stop sqlserver and move your data file from C: to D: .Default data file locates in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

Start sql server and delete old database and right Databases ->choose attach ->choose add and select data file on D: ->click ok to finish.

Hope it helps.

|||Or simply sepecify a new location on D drive when restoring the database, just make sure the "Overwrite the existing database" option is checked.|||Thank you guys. It works and I don't have not enough space problem anymore.

Challenge: Can you optimize this?

This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.

The reason that I want to find records with non-duplicated RegJrnID
values is to create "reversal" records for these such that the reversal
record has identical values for every column except the TaxableAmount
which will contain a negative amount. (see: example data below).

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

/* Example data */

INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)

/* Show what's in the table - just because */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/* Make the results look pretty (optional) */

ORDER BY RegJrnID

/* Housekeeping */

DROP TABLE t1There are many ways to accomplish that. I would start with something
this (untested):

select pos.RegJrnID
from(
select * from t1 where TaxableAmount >0
) pos
left outer join
from(
select * from t1 where TaxableAmount <0
) neg
on pos.RegJrnID = neg.RegJrnID
where neg.RegJrnID is null|||Here's the tested (and slightly modified) version of your code...

SELECT pos.*

FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

WHERE neg.RegJrnID IS NULL

/* Make the results look pretty (optional) */

ORDER BY pos.RegJrnID|||According to the SQL Query analyzer your query is better going head to
head with the original representing 43.43% of the batch and the
original representing 56.57% of the batch.

A 13% improvement!

Thanks!|||On 6 Jun 2006 13:24:54 -0700, octangle wrote:

>This code is attempting to find records that have a RegJrnID that does
>not occur more than one time in the table.
>The reason that I want to find records with non-duplicated RegJrnID
>values is to create "reversal" records for these such that the reversal
>record has identical values for every column except the TaxableAmount
>which will contain a negative amount. (see: example data below).

Hi octangle,

Thanks for providing CREATE TABLE and INSERT statements. This made it
very easy to set up a test DB and fun to find an answer.

What worries me is that there's no primary key in your table. I hope
that you just forgot to include it in the script and that your real
table does have a key!

Here's a much quicker way. Running both your version and my version with
execution plan displayed, yours took 72% and mine 28%. Removing the
ORDER BY changed this to 64% / 36%. Still a nice gain.

SELECT RegJrnID, MAX(InvoiceDate),
MAX(InvoiceNumber), MAX(TaxableAmount)
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1
--ORDER BY RegJrnID

And here's another one, but it's correctness depends on some assumptions
I had to make because you forgot to include the primary key. With ORDER
BY, it's slightly more expensive than the previous version. With the
ORDER BY commented out, it only costs half as much!

SELECT RegJrnID, InvoiceDate,
InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate)
--ORDER BY RegJrnID

(Note - I have compared these queries using the sample data you provided
on a SQL Server 2005 database on my computer. Results will probably vary
on yoour database, especially if your table has indexes, your data
distribution is not like the sample data, and/or you are running another
version of SQL Server. I recommend that you test out the various
suggestions yourself before deciding.)

--
Hugo Kornelis, SQL Server MVP|||Upon further review...

As written in the previous post, records with a TaxableAmount of 0 will
not be found to be reversed... so in an attempt to remedy this I
modified the LEFT OUTER JOIN as follows:

FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID

This succeeds at finding the 0 TaxableAmount records... but once a
companion reversal record is inserted into the database both records
(the original and a the reversal) are found on subsequent queries using
this technique... since these records are retrieved by the query as
records to reverse, these get reversed again (thus making a total of 4
instances of the original record - instead of 2 which is all are
needed). And if we repeat the process a 0 TaxableAmount record will
redouble it instances every time the process is run...

Now the questions are...

Can the above LEFT OUTER JOIN be fixed?

OR

Should 0 TaxableAmounts be processed in their own pass with their own
query (yuck)?

OR

Is the original query really better because it works for all
TaxableAmounts despite the fact that its 13% slower...

OR

Is there another option??|||one more question: what if there is only one row with negative amount?

INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)

and there is no corresponding row with positive amount? Nothing in the
posted DDL prevents you from that. In fact, originally I was
considering the query posted by Hugo, but realized it would return that
single row with negative amount and assumed it incorrect. It looks like
there might be no base for my assumption.|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> one more question: what if there is only one row with negative amount?
> INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
> and there is no corresponding row with positive amount? Nothing in the
> posted DDL prevents you from that. In fact, originally I was
> considering the query posted by Hugo, but realized it would return that
> single row with negative amount and assumed it incorrect. It looks like
> there might be no base for my assumption.

Having watched the thread from aside, I think the real problem is that
the data model needs improvement. I would add a bit column "isbalanceentry"
or some such. And of course add a primary key. (InvoiceNumber,
isbalanceentry) looks like a candidate.

Better get the data model in order, before looking at smart queries.

--
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 6 Jun 2006 17:45:58 -0700, Alexander Kuznetsov wrote:

>one more question: what if there is only one row with negative amount?
>INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
>and there is no corresponding row with positive amount? Nothing in the
>posted DDL prevents you from that. In fact, originally I was
>considering the query posted by Hugo, but realized it would return that
>single row with negative amount and assumed it incorrect. It looks like
>there might be no base for my assumption.

Hi Alexander,

I've seenn nothing in the original post that justifies special treatment
of negative amounts. If these should be excluded, then my version can
still be used - just add AND MAX(TaxableAmount) >= 0 to the HAVING
clause.

However, I agree with Erland that a redesign might be a better choice if
something like that is the case.

--
Hugo Kornelis, SQL Server MVP|||Erland Sommarskog wrote:
> Having watched the thread from aside, I think the real problem is that
> the data model needs improvement. I would add a bit column "isbalanceentry"
> or some such. And of course add a primary key. (InvoiceNumber,
> isbalanceentry) looks like a candidate.

I concur that the schema might need some work. I was thinking that a
single nullable column negated_date might be sufficient, so that
instead of inserting one more row one just needs to update negated_date.|||Below is an aggregate script that includes everyone's suggested queries
so far...

Based upon feedback I have beefed up the test records to more
accurately reflect all of the potential scenarios that need to be
handled by this quey.

The original query (Query attempt #1 (Octangle)) generates the desired
result set and therefore is the benchmark of correctness for my
purposes.

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

/* Set up */

CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
VARCHAR(20), TaxableAmount DECIMAL(32,8))

INSERT INTO t1 VALUES (0, '20060120', '0000033', 0.00)
INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
INSERT INTO t1 VALUES (11, '20060324', '3321110', -1200.16)
INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
INSERT INTO t1 VALUES (13, '20051127', '1034501', -77.50)
INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
INSERT INTO t1 VALUES (10, '20060421', '0000033', 0.00)
INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
INSERT INTO t1 VALUES (12, '20060606', '0000001', 4431.55)
INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
INSERT INTO t1 VALUES (10, '20060517', '0000033', 0.00)
INSERT INTO t1 VALUES (11, '20060324', '3321110', 1200.16)
INSERT INTO t1 VALUES (12, '20060606', '0000001', -4431.55)

/* Show what's in the table */

SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate

/* Query for records to reverse */

/* Query attempt #1 (Octangle) */

/* Pros: correct */
/* Cons: slow */

SELECT *
FROM t1 a

/* Ignore records that have already been reversed */

WHERE a.RegJrnID != ALL

/* This subselect finds reversed records (i.e. those that have a
duplicate RegJrnID) */

(
SELECT b.RegJrnID
FROM t1 b
GROUP BY b.RegJrnID
HAVING COUNT(*) > 1
)

/* User selection criteria are appended here */

/* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */

/*ORDER BY RegJrnID; * Make the results look pretty (optional) */

/* Query attempt #2 (Alexander) */

/* Pros: faster */
/* Cons: misses 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount > 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #3 (Alexander - tweaked by Octangle) */

/* Pros: faster */
/* Cons: finds too many 0 TaxableAmounts */

SELECT pos.*
FROM
(
SELECT * FROM t1 WHERE TaxableAmount >= 0
) pos
LEFT OUTER JOIN
(
SELECT * FROM t1 WHERE TaxableAmount < 0
) neg
ON pos.RegJrnID = neg.RegJrnID
WHERE neg.RegJrnID IS NULL
/*ORDER BY pos.RegJrnID * Make the results look pretty (optional) */

/* Query attempt #4 (Hugo) */

/* Pros: correct , fastest, returns results in RegJrnID order with
ORDER BY clause */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
"TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

/* Query attempt #5 (Hugo) */

/* Pros: fast */
/* Cons: not correct */

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND a.InvoiceDate <> b.InvoiceDate
)
/*ORDER BY RegJrnID * Make the results look pretty (optional) */

/* Housekeeping */

DROP TABLE t1

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

Queries as percent of batch (when just executing the queries in the
above script)

Query #1: 22.66% - Correct
Query #2: 19.77%
Query #3: 20.27%
Query #4: 12.63% - Correct
Query #5: 20.67%

Queries as percent when compared to only the original query (Query #1)

Query #1: 50.00% - Correct
Query #2: 42.58%
Query #3: 43.19%
Query #4: 32.14% - Correct
Query #5: 43.67%

At this point it looks like the clear winner is Query #4 by Hugo!

MWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMWMW

To address some of the observations/comments:

1. Negative transactions are possible - I augmented the test data to
include this case.
2. This is for a commercial product that has numerous existing
customers, I inherited the data model that this table is based upon...
my coding constraits are:
- I cannot add any columns (due to how we version a column change would
force this release to be considered a major release and not a minor
release as desired)
- I should not add any indexes/primary keys/uniqueness constriants for
performance reasons (see below)

The purpose of this table to store processed transaction results. It
needs to be as efficient as possible for insertions, so as to not slow
down the transaction processing engine. Reporting (and reversing groups
of transactions) are secondary concerns and it is acceptable for these
functions to be slower.

I sincerely want to thank everyone who chipped in a comment or
suggestion on this...|||1. If negative transactions are possible, than my query is incorrect.
2. You probably need a much larger set of test data to test different
approaches against. Also I would say there are at lest 2 possible
situations:
- most transactions are already negated.
- most transactions have not been negated yet.
In some cases in different situations different queries are the best. I
would try both and see if one and the same query is the best.

Good luck!|||Alexander Kuznetsov wrote:
> 1. If negative transactions are possible, than my query is incorrect.
> 2. You probably need a much larger set of test data to test different
> approaches against. Also I would say there are at lest 2 possible
> situations:
> - most transactions are already negated.
> - most transactions have not been negated yet.
> In some cases in different situations different queries are the best. I
> would try both and see if one and the same query is the best.
> Good luck!

FYI

The normal situation would be that most transactions are not negated in
this table. Negation would only occur if the billing system was
un-doing a billing run for some technical or business reason...
Therefore negating transactions would be rare, theoretically...

I appreciate the notion of getting better test data - this will
naturally accrue as I implement and test this soultion. I will make
sure to compare performance as this project matures...

Thanks again...!|||/* Query attempt #4 (Hugo) */

SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

I talked to a few folks around the office and none of us had ever
though to use MAX() to force values out of a query using a GROUP BY
clause...

e.g. if the query were changed to look like this:

SELECT *
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1

The following error occurs for each column not mentioned in the GROUP
BY clause: "Column 't1.InvoiceDate' is invalid in the select list
because it is not contained in either an aggregate function or the
GROUP BY clause." So MAX() forces these values to participate in the
result set generated by this query...

My question with this is, "Is this technique safe for all major DBs
(Oracle, SQL Server, DB2 and MySQL) and will it work with all column
types?"|||octangle (idea.vortex@.gmail.com) writes:
> /* Query attempt #4 (Hugo) */
> SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
> as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
> FROM t1
> GROUP BY RegJrnID
> HAVING COUNT(*) = 1
> I talked to a few folks around the office and none of us had ever
> though to use MAX() to force values out of a query using a GROUP BY
> clause...
>...
> My question with this is, "Is this technique safe for all major DBs
> (Oracle, SQL Server, DB2 and MySQL) and will it work with all column
> types?"

Yes, it should on any RDBMS worth the name, as it is very plain standard
SQL.

Then again, MySQL has so many funny quirks, I suspect that one should
never take anything for granted with that engine.

--
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|||octangle (idea.vortex@.gmail.com) writes:
> - I should not add any indexes/primary keys/uniqueness constriants for
> performance reasons (see below)
> The purpose of this table to store processed transaction results. It
> needs to be as efficient as possible for insertions, so as to not slow
> down the transaction processing engine. Reporting (and reversing groups
> of transactions) are secondary concerns and it is acceptable for these
> functions to be slower.

There are good changes that a well-considered clustered index can improve
the performance. Not the least, because you can handle fragmentation better.

In any case, having a table without a primary key in order to save some
cycles on insertion is about criminal in my opinion. What to you when
the same data gets inserted twice? (Don't tell me that it never happens!).
And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
8 with datetime or 4 with smalldatetime. Here's is a second risk for
errors. Wonder how many entries for 20060230 you have...

As for the actual challenge, I prefer to stay out. I don't really want
to contribute to something which is obviously flawed.

--
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|||Erland Sommarskog wrote:
> There are good changes that a well-considered clustered index can improve
> the performance. Not the least, because you can handle fragmentation better.
> In any case, having a table without a primary key in order to save some
> cycles on insertion is about criminal in my opinion. What to you when
> the same data gets inserted twice? (Don't tell me that it never happens!).
> And why InvoiceDate as varchar(8)? That's 10 bytes per date, instead of
> 8 with datetime or 4 with smalldatetime. Here's is a second risk for
> errors. Wonder how many entries for 20060230 you have...
> As for the actual challenge, I prefer to stay out. I don't really want
> to contribute to something which is obviously flawed.

Thanks for the tip on clustered index usage.

Criminal? A little severe - I'd call it a trade-off - a trade-off made
based upon the project requirements. Our process is a small part in a
larger customer billing cycle and everything we can do to be as small a
percentage of the overall work effort in the processing of every bill
is critical...

As it turns out the same RegJrnID must not be inserted twice. We have
programmatic control over this and if it were to occur there would be a
bug in the software or a serious proceedural issue on the part of the
user. In either case these represent bigger problems than the structure
of this table.

OK, OK - putting a date in a varchar is kludgy... again I simply
inherited this design... I think the big issue here was compatibility
with other RDBMS... We need to support DB2, Oracle, SQL Server and
MySQL with the same code base... So they all support character data
roughly the same... chalk it up as a rookie mistake... but oddly your
example that the table will allow Feb 30th could be seen as a feature
(i.e. we are fault tolerant of bad dates) - ultimately the generation
of the invoice date is controlled be the external application calling
our API - so allowing a bad date to float through to the DB isn't a big
deal to us...

Flawed? Again trade-offs have been made by SQL beginners... Funny thing
is that it seems to work very well for our customers and they seem
happy, so I'd say that flaws are in the eye of the beholder...

Thanks for holding us to a higher standard...

:-)|||octangle (idea.vortex@.gmail.com) writes:
> Criminal? A little severe - I'd call it a trade-off - a trade-off made
> based upon the project requirements.

I would not call it trade-off, only off.

Then again, I'm the coward kind of guy that always wear a safety belt
when I'm driving(*) and all that.

(*) OK, so it happened once I didn't put it on. That was when I was
drive up for my driving license!

--
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 7 Jun 2006 10:54:44 -0700, octangle wrote:

>/* Query attempt #4 (Hugo) */
>/* Pros: correct , fastest, returns results in RegJrnID order with
>ORDER BY clause */
>SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
>MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
>"TaxableAmount"
>FROM t1
>GROUP BY RegJrnID
>HAVING COUNT(*) = 1

Hi octangle,

I don't know what the remark about returning results in RegJrnID order
with ORDER BY means - all queries will return results in that order if
yoou include an ORDER BY. And without the ORDER BY, some of the queries
might return the results in that order some of the time, maybe even
every time during testing, but there's no guarantee that it will remain
so in production. In shhort - if you need a specific order, use an ORDER
BY - always!

>/* Query attempt #5 (Hugo) */
>/* Pros: fast */
>/* Cons: not correct */

Only because the data you originally provided was not enough to show
what column oor combination of columns makes a row unique. And your
reply still doesn't show it, so my next attempt might well be wrong
again :-((

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND ( a.TaxableAmount <> b.TaxableAmount
OR a.InvoiceDate <> b.InvoiceDate )
)

Without the ORDER BY, this is significantly faster than attempt #4 when
tested with your test data. But with a much larger set of test data,
attempt #4 is faster (though this might be different with your data, as
it might be distributed differently).

>2. This is for a commercial product that has numerous existing
>customers, I inherited the data model that this table is based upon...
>my coding constraits are:
>- I cannot add any columns (due to how we version a column change would
>force this release to be considered a major release and not a minor
>release as desired)
>- I should not add any indexes/primary keys/uniqueness constriants for
>performance reasons (see below)

I concur with everything Erlland says about this. And I'd still like to
know which (combination of) column(s) you can use to uniquely identify a
single row.

--
Hugo Kornelis, SQL Server MVP|||Hi There,
If the following is your requirement.
"
This code is attempting to find records that have a RegJrnID that does
not occur more than one time in the table.
"
Then

Select * from Yourtable where ID in (Select ID from group by ID having
count(1)=1)
-- Give some order by etc as per your requirement
Output:--
9, '20060213', '2130009', 40.01
4, '20060105', '9302221', 612.12
5, '20060105', '0003235', 18.11
7, '20060115', '4234444', 44.52
8, '20060115', '0342222', 95.21
2, '20060101', '5440033', 231.01

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

octangle wrote:
> This code is attempting to find records that have a RegJrnID that does
> not occur more than one time in the table.
> The reason that I want to find records with non-duplicated RegJrnID
> values is to create "reversal" records for these such that the reversal
> record has identical values for every column except the TaxableAmount
> which will contain a negative amount. (see: example data below).
> /* Set up */
> CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumber
> VARCHAR(20), TaxableAmount DECIMAL(32,8))
> /* Example data */
> INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)
> INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)
> INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)
> INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)
> INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)
> INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)
> INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)
> INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)
> INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)
> INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)
> INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)
> INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)
> /* Show what's in the table - just because */
> SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate
> /* Query for records to reverse */
> SELECT *
> FROM t1 a
> /* Ignore records that have already been reversed */
> WHERE a.RegJrnID != ALL
> /* This subselect finds reversed records (i.e. those that have a
> duplicate RegJrnID) */
> (
> SELECT b.RegJrnID
> FROM t1 b
> GROUP BY b.RegJrnID
> HAVING COUNT(*) > 1
> )
> /* User selection criteria are appended here */
> /* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' */
> /* Make the results look pretty (optional) */
> ORDER BY RegJrnID
> /* Housekeeping */
> DROP TABLE t1

Chaging the Min and Max Scale of a Chart at run time

I have a stored procedure which will bring me back the Min, Max and Mean of different result sets. What I want to do with the Y-Axis is set the Min scale value of the chart to be Min -%5 and the Max scale value to be Max + 5%.

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

In RS 2005, the Min/Max/CrossAt/MajorInterval/MinorInterval settings can be expression based. Note that by using an aggregate function such as first you can even reference certain values from another dataset than the chart is bound to. For example, you could use an expression similar to:

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

Chaging the Min and Max Scale of a Chart at run time

I have a stored procedure which will bring me back the Min, Max and Mean of different result sets. What I want to do with the Y-Axis is set the Min scale value of the chart to be Min -%5 and the Max scale value to be Max + 5%.

Is there a way to change the Y-Axis values at report run time without spitting my own RDL?

In RS 2005, the Min/Max/CrossAt/MajorInterval/MinorInterval settings can be expression based. Note that by using an aggregate function such as first you can even reference certain values from another dataset than the chart is bound to. For example, you could use an expression similar to:

=CDbl(First(Fields!MinValue.Value, "StoredProcDataset")) - 0.05

-- Robert

|||That's great Robert but it's not apparent seeing that it doesn't follow the usual standard of having the combo box with the <expression> as a selection. Worked for me though and I thank you.|||

Hi!

I want to change my y-axis into always showing integers, start with zero and show an y-axis longer than the maximum value of the points.
How do I do that in csharp?

My code is like this now: (Chart looks ok for values between 0 and 25, but makes a chart with a max value 1 have an Y axis that is divided into 0.1 and up to 1.0 ):

chartGraf.Axis.Y.TickmarkStyle = AxisTickStyle.Smart;

chartGraf.Axis.Y.LineThickness = 1;

chartGraf.Axis.Y.RangeMin = 0;

if ( MyMaximumValue < 3) {

chartGraf.Axis.Y.RangeMax = 3;

}

-Heidi

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

CellSetGrid

Richard,
Long time no talk.
I am trying to run your cellsetgrid control but it gives me an error "A
connection cannot be made. Ensure that the server is running."
Any idea what it is?
thanks
SaranyaRichard,
I am getting an error when I try to load cellsetgrid in windows 2003
server/ SQL 2000 in my laptop.
It says something like "a connection could not be established, check if
server is running". It looks like you have set that custom error
message up in the control. Can you tell me what are the pluasible cause
to trigger that error message.
I never had problem figuring out AS 2000 issues before. This time I
give up. Unless the creator can help me.
so please!!!
thanks
Saranya

CE10 - internal error - secLdap plugin

Hello

After installing the CE10 SDK on our IIS Server my program generates a CR report but only about 50% of the time - the error I get (which occurs when the program attampts to logon to the CE10 server) is :-

"An internal error has occurred in the secLdap plugin."

Our templates reside on a CE10 server. Another server contains our SDK and the code that calls the API. Has anyone received this before or does anyone have any ideas ?

My code to logon is as follows :-

SessionMgr ceSessionmgr = null;
EnterpriseSession ceSession;
Response.Expires = 0;
ceSessionmgr = new SessionMgr();
//Get username
user = Request.ServerVariables.Get("HTTP_PROXY_REMOTE_USER");
//Get password/token
password = Request.ServerVariables.Get("HTTP_LDAPTOKEN");
apsName = "NYITDCED001";
string apsAuthType = "secLDAP";
ceSession = ceSessionmgr.Logon(user, password, apsName, apsAuthType);

ceSessionmgr.Dispose();
ceSessionmgr = null;
ceSession.Logoff();
ceSession.Dispose();
ceSession = null;
...
...
...I have not received a reply to this so I am posting in "Business Objects:Crystal Enterprise"|||I have not received a reply to this so I am posting in "Business Objects:Crystal Enterprise"
Good. Hope you find the solution :)

CDC Retention time

According to BOL the default CDC retention time is 3 days and it was mentioned that it configurable.

Can some of point me on how to change the default value.

Thanks in advance.

You can use sp_cdc_add_job or sp_cdc_change_job to set the retention for the cleanup job.

Thanks