If I write custom Data and Delivery extensions for RS 2000, will they work with RS 2005?

If not, can you give me an idea how extensive the changes will be?
Thanks,
-SteveExisting interfaces for data / delivery extensions have not been changed. A
few optional data extension interfaces got added in RS 2005, but they won't
affect your existing custom data extension. Your RS2000 data extensions
should just work on RS2005 once configured in the config files.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephen Walch" <swalch@.online.nospam> wrote in message
news:%23RUMT18DFHA.3416@.TK2MSFTNGP09.phx.gbl...
> If not, can you give me an idea how extensive the changes will be?
> Thanks,
> -Steve
>

If exists

Any idea why
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
Regards,
Jamie
You cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:

> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie
|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
|||I read that WHEN EXISTS... is faster than the following logic:
CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey = tol.orderlinekey
where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
WHEN 0 THEN ...
http://www.sql-server-performance.com/transact_sql.asp
Hoping this is correct - am trying to improve performance of the view.
Thanks for help from you both.
Regards,
Jamie
"Plamen Ratchev" wrote:

> Seems like some of the logic here is a bit redundant. Maybe you can simplify
> like this:
> Select
> case when exists(Select * from atoms.dbo.tpreallocations) then
> 'green'
> else
> 'red'
> end
> Regards,
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...
>
>
|||>I read that WHEN EXISTS... is faster than the following logic:
Just because you read it somewhere, doesn't always make it true.

> CASE (select count(*) from atoms.dbo.tpreallocations tp with(nolock) join
> atoms.dbo.torderlines tol with(nolock) on tp.orderlinekey =
> tol.orderlinekey
> where isnull(packstatus,0) = 0 and tol.bol = tbols.bolid)
> WHEN 0 THEN ...
> http://www.sql-server-performance.com/transact_sql.asp
> Hoping this is correct - am trying to improve performance of the view.
Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
**may** be more efficient.
In general, your entire approach to addressing the issue within this thread
isn't helping you. You continue to dribble out information, bit by bit.
Trying to help you with problems involving the syntax you are using, without
knowing exactly how you are using it, is difficult at best. It is likely to
lead to inappropriate suggestions. You started asking about syntax, but the
real issue seems to be performance. The most correct syntax in the world
will not necessarily make your query more efficient. No offense, but
trouble with relatively basic syntax likely means you are in over your head
at this point. Perhaps you should consider some additional expertise for
help with the tuning/management problems you seem to be experiencing.
Improving the query involves looking at the entire query and examining all
aspects of the logic and data to ensure that both the database and the query
have been optimized. Optimization is a two-way street. Often, there are
performance tradeoffs that must be made to provide an overall level of
performance for the entire system. Tuning one particular query to the
maximum degree possible can have a detrimental effect on other aspects of
the system. In general, you shouldn't need locking hints. You should use
them only if you understand the locking architecture of sql server and how
the hints affect the overall system as a whole.
|||The help works fine. Testing shows no measurable difference. Thought maybe
I missed something but thanks for your reply.
Regards,
Jamie
"Scott Morris" wrote:

> Just because you read it somewhere, doesn't always make it true.
>
> Don't hope - test and verify. BTW, the link correctly indicates that EXISTS
> **may** be more efficient.
> In general, your entire approach to addressing the issue within this thread
> isn't helping you. You continue to dribble out information, bit by bit.
> Trying to help you with problems involving the syntax you are using, without
> knowing exactly how you are using it, is difficult at best. It is likely to
> lead to inappropriate suggestions. You started asking about syntax, but the
> real issue seems to be performance. The most correct syntax in the world
> will not necessarily make your query more efficient. No offense, but
> trouble with relatively basic syntax likely means you are in over your head
> at this point. Perhaps you should consider some additional expertise for
> help with the tuning/management problems you seem to be experiencing.
> Improving the query involves looking at the entire query and examining all
> aspects of the logic and data to ensure that both the database and the query
> have been optimized. Optimization is a two-way street. Often, there are
> performance tradeoffs that must be made to provide an overall level of
> performance for the entire system. Tuning one particular query to the
> maximum degree possible can have a detrimental effect on other aspects of
> the system. In general, you shouldn't need locking hints. You should use
> them only if you understand the locking architecture of sql server and how
> the hints affect the overall system as a whole.
>
>
|||Hi Jamie
You are probably not testing on significant numbers of records, the EXISTS
clause is usually more scalable. As per Scotts reply, without the whole
picture it is difficult to know if you are looking at the best solution
anyhow. You may also want to read http://www.aspfaq.com/etiquette.asp?id=5006
which gives you some guidelines on posting.
John
"thejamie" wrote:
[vbcol=seagreen]
> The help works fine. Testing shows no measurable difference. Thought maybe
> I missed something but thanks for your reply.
> --
> Regards,
> Jamie
>
> "Scott Morris" wrote:

If exists

Any idea why
BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
works and not
BEGIN IF EXISTS (select * from mytable) '0' else '1' end
I am trying to put the latter into a case statement.
--
Regards,
JamieYou cannot just use '0' or '1', according to SQL syntax you need a SELECT.
This will work:
IF EXISTS (select * from mytable) SELECT '0' else SELECT '1'
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2C00DCB8-2CEA-45FD-9094-F69D49777DAF@.microsoft.com...
> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||Hi Jamie
It may be clearer if you indented!!
BEGIN
IF EXISTS (SELECT * FROM mytable)
PRINT '0'
ELSE
PRINT '1'
END
This will work, but you can't use it with a PRINT statement:
BEGIN
SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
END
Although you can do:
BEGIN
DECLARE @.output char(1)
SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
THEN '0'
ELSE '1'
END
PRINT @.output
END
HTH
John
"thejamie" wrote:

> Any idea why
> BEGIN IF EXISTS (select * from mytable)print '0' else print '1' end
> works and not
> BEGIN IF EXISTS (select * from mytable) '0' else '1' end
> I am trying to put the latter into a case statement.
> --
> Regards,
> Jamie|||John,
I see part of the error of my ways. This is a condition for a case
statement thus
Begin
Select
case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 else
select 1 end)=0 then
print 'green'
else
print 'red'
end
Still get an error here...
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jamie
> It may be clearer if you indented!!
> BEGIN
> IF EXISTS (SELECT * FROM mytable)
> PRINT '0'
> ELSE
> PRINT '1'
> END
> This will work, but you can't use it with a PRINT statement:
> BEGIN
> SELECT CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> END
> Although you can do:
> BEGIN
> DECLARE @.output char(1)
> SET @.output = CASE WHEN EXISTS (SELECT * FROM mytable)
> THEN '0'
> ELSE '1'
> END
> PRINT @.output
> END
> HTH
> John
> "thejamie" wrote:
>|||Seems like some of the logic here is a bit redundant. Maybe you can simplify
like this:
Select
case when exists(Select * from atoms.dbo.tpreallocations) then
'green'
else
'red'
end
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:33B939B4-8BFA-43CF-805E-0DA0456BD47F@.microsoft.com...[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0
> else
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>|||Hi Jamie
You don't need to do the if, Case can take a boolean expression in the WHEN
clause, but you can not use PRINT with a CASE statement or use print within
a
SELECT statement
To use PRINT
BEGIN
DECLARE @.output char(5)
SET @.output = CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
PRINT @.output
END
To use SELECT to return a result set
BEGIN
SELECT CASE WHEN EXISTS (SELECT *
FROM
atoms.dbo.tpreallocations)
THEN 'green'
ELSE 'ref'
END
END
HTH
John
"thejamie" wrote:
[vbcol=seagreen]
> John,
> I see part of the error of my ways. This is a condition for a case
> statement thus
> Begin
> Select
> case when (if exists(Select * from atoms.dbo.tpreallocations) select 0 els
e
> select 1 end)=0 then
> print 'green'
> else
> print 'red'
> end
> Still get an error here...
> --
> Regards,
> Jamie
>
> "John Bell" wrote:
>

IDENTITY_INSERT, is it a good idea?

Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

One "gotcha" that can occur is that SET IDENTITY_INSERT requires the DDL_ADMIN privilege; this may not be a privilege that you want to give to rank-and-file users.|||

I have primarily used IDENTITY INSERT for DATA Import / Export jobs where i need to preserve the existing values of ID column and I do not want sql server to generate and I also want sql server to let me insert my own values in indentity columns.

|||

You can look at whether or not it is a good idea in two ways.

Technically: No problem whatsoever. If you know the value that was skipped and want to put a value there, it will not harm anything. All the identity property controls is a counter that adds some value to the previous value you used.

Logically: Bad idea. You should use identity valued columns as if you don't know, don't care the value. It would be better if they used a random number in some ways (though that would stink for building clustered indexes :) The idea behind using an identity value is as a surrogate for some value (as a key). Kind of like identifying you by your employee number. If you are employee 10 or employee 20 it will not change your pay. Either could be the CEO or the guy who sweeps up after the CEO.

So use identity_insert to load a table with existing values is perfectly acceptable, but once loaded, if you can accept gaps it is perfect. If not, then generate your own sequence values, looking for gaps.