problem in expression of a check constraint

Hi all,

I wrote the expression below in a table's check constraint

(CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) or

( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null )

It saved it ok, but when I re-opened and viewed the expression again it was converted to:

([CustomerTypeID] = 0 and [ContentSiteID] is null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null

[CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null )

This is a different logic because the OR is not prioritised above the AND.

Any solution or should I move my condition to the table's Update and Insert triggers?

Thanks!

Ofer

You are safe because of the precedence of the operators. A reading from the Books On-Line:

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic (and bitwise) operators are handled before logical operators.

If you swap the Ands and the OR in your expression, you will see that the evaluation order is preserved with parens.

Here is a quick sample with the results shown.

drop table tester

go

create table tester(

one int null,

two int null,

three varchar(10) null

)

go

alter table tester

Add Constraint AndOrAnd Check ( (one = 1 and three is null ) or (two = 0 and three is not null ) )

alter table tester

Add Constraint OrAndOr Check ( (one = 1 or three is null ) and (two = 0 or three is not null ) )

exec sp_helptext AndOrAnd

-->([one] = 1 and [three] is null or [two] = 0 and [three] is not null)

exec sp_helptext OrAndOr

-->(([one] = 1 or [three] is null) and ([two] = 0 or [three] is not null))|||

Thanks for your reply but it doesn't answer this problem.

I need the check constraint to have a codition of ( (x=1 and y is null) or (x=2 and y is not null) )

Replacing the or-s with and-s does a totally different check.

The SQL Server just drops the inner paranthesis, again changing the condition.

Is there a way to check my condition ?

Thanks.

|||

Sorry that my response was not clear. Your expression is being evaluated in the way you desire. It happens because any of the ANDs are being evaluated first. The server is removing the parenthesis precisely because they are redundant to the evaluation.

The example of swapping the conditions was only to show that the server will preserve the parenthesis if they are contrary to normal order of evaluation.

sql

Problem in Context connection of CLR Trigger

Hi,
I'm using June CTP Visual Studio 2005.
I have Created CLR Object. In my CLR Method i have
opened a
connection like below
public partial class Triggers
{
//In my CLR Method i have Opened the connection like below
[Microsoft.SqlServer.Server.Sq­lTrigger(Name="ClrTrigger",
Target="Triplets",Event="FOR INSERT")]
public static void CLRTrigger()
{
Triggers objTriggers = new Triggers();
objTriggers.Connection(true);
...
.....
Test();
// Here i'm calling one more method Test();
....
....
objTriggers.Connection(false);
}
In this test method i'm Executing the command.
Public void Test()
{
....
SqlCommand cmd = new SqlCommand();
string cmdText = "select dirname from Docs where id=45";
cmd.CommandText = cmdText;
cmd.Connection = con;
try
{
sdr = cmd.ExecuteReader();
}
catch (Exception exp)
{
WriteLog("R E ::: "+exp.Message);
}
.......

}
//Connection open and Close method
public void Connection(bool status)
{
if (status)
{
con = new SqlConnection("context connection = true");
con.Open();
}
else
{
con.Close();
}
}
}
I'm getting the error of Connection already in use. I have
properly
opened the connection in begin of

method and Closed in end of method. Inside the execution
of method if i
called some method means then it
can also use the same connection. Then Why its giving this
error.
Please anybody knows solution to my
problem let me know to solve it.
Thanks,
Vinoth
vinoth@.gsdindia.comThere are a couple of possibilities:

1) You can only have one open context connection at a time within a clr procedure ("context connection=true"). Is it possible that in the missing code snippets for your example (...'s) that you are attempting to open another context connection? For instance, calling Triggers.Connection(true) again anywhere prior to the Triggers.Connection(false) call would result in this exception. The call stack where the "Connection already in use" exception is thrown would be useful to track down this cause.

2) June CTP has a couple of bugs where an exception, especially if due to an attention (i.e. cancel) signal, can cause the connection to not properly clean itself up from one invocation of the procedure to the next. This problem would not be apparent on first invocation of the proc, and would temporarily be cleared by running "DBCC FREEPROCCACHE".

One other question I have is how you get the SqlConnection from the CLRTriggers() method to the Test() method. It would appear that the "con" field must be static, true? If this is the case, you will almost certainly run into problems, since your trigger can fire on multiple threads, yet the SqlConnection object is valid only in the procedure in which it was created.

I'd like to note that the recommended use pattern for SqlConnection (and cmd.ExecuteReader) is to put them in a "using" statement to guarantee cleanup and help see the lifetime scope of the connection. i.e.

using (SqlConnection con = new SqlConnection("context connection = true")) {
con.Open();
...
}

and

using (SqlDataReader rdr = cmd.ExecuteReader()) {
...
}

~Alazel

Problem getting security for configuration database

Below is what I have put into an execute sql task container. When I try to run it I have output open and have posted the output. The code runs fine when I have the configuration database set up to only check one server. However, when I add anymore to it, the package will get the security from the first db and then error out. Below is the error output and script. Any help with my problem is greatly appreciated.

**********Begin script*******************

declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

****************End Script***********************

****************Begin Error Output********************

[Execute SQL Task] Error: Executing the query "declare @.dbname varchar(200) declare @.mSql1 varchar(8000) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @.dbname WHILE @.@.FETCH_STATUS = 0 BEGIN Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @.dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @.dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @.mSql1 Execute (@.mSql1) FETCH NEXT FROM DBName_Cursor INTO @.dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor " failed with the following error: "Line 15: Incorrect syntax near '-'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

**************End Error Output**************

Not knowing your database names, I'd suspect that one or more of them have names that need to be bracketed in a SQL statement. The error indicates that one might have a hyphen in the name? Try putting brackets around the @.dbname variable. Ex: "from [' + @.dbName+'].dbo.sysmembers"|||I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.|||

kschlap wrote:

I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.

What you have would work fine for a database named FooBar, but it would fail with the error you posted if the database were named Foo-Bar.
|||

I"m sorry, I made a mistake in my last post. I meant to say it worked for one server, but won't work for any other server.

-Kyle

|||

I think I have figured out one of the problems. I need to change the OLE DB Connection Manager. However, it won't let me do this and hit ok because it says that the table in my FROM clause is not in the database. Well of course its not on the database because the table gets built dynamically and the servers are gathered dynamically. How can I change this?

-Kyle

|||SSIS tries to validate the tasks when you are editing them; so I guess the table have to exists while you are editing the package. At run time though you can set DelayValidation to true; so the package does not fail validation.|||

The problem with this is that the connection I am trying to use is generated dynamically too. Thus, there are no servers until run time that I could add the table to to get around this error.

-Kyle

|||I don't get that. Does this mean you don't have a development environment where you can create oll required stuff? remebember this is just for validation purposes.|||

No, I have the proper development environment. I just get an error that says that the table doesn't exist whenever I try to make a change to the OLE DB connection. Here is the message I sent to the template creator yesterday.

I am having trouble with changing an OLE DB source. The package is set to not validate until run time, but currently it won’t let me change one of the settings. I keep getting an error that the table it is referring to is not valid. However, I know that it isn’t created because it gets created at run-time. Is there a way to change OLE DB sources? As always any help is appreciated.

|||

I fixed the problem. The package needed me to make an advanced edit instead of a normal one? Weird, but I don't care why it worked just because it worked. If anybody else is building a sql dba configuration database, mine is from a template by sqlmag, please PM me and we can work together on them.

-Kyle

Problem for Calling A Stored Procedure, Please help.

I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @.Total ， @.balance， @.A are output values
create proc [MaxTime]

@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30)

as

begin

declare @.balancefloat

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

declare @.A float

declare @.Total float

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = 'select @.A = A FROM' + @.table

+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)

+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +

' ORDER BY LEN(code) DESC'

exec sp_executesql @.sql,N'@.Afloat output',@.Aoutput

set @.Total = @.balance / @.A + @.freetotal

end

return

but the server got nothing, then i wrote another Stored Procedure below:

create proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output,

@.A float output)

--here is the only modified i made

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--@.sql,N\'@.Afloat output\',@.Aoutput --im not sure about this line

set @.Total = @.balance / @.A + @.freetotal

end

return

system error message：

Msg 170, Level 15, State 1, Procedure MaxTime, Line 11

Line 11: Incorrect syntax near \'@.Total \'.

Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21

Must declare the variable \'@.Total \'.

Please help, appreciated

Hi,xxd

If you wanna get data from database without using dataset.
maybe you can try function.

By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.

|||Hi, HutTsai:

thanks for ur reply, as u mentioned about dataset, did you mean in SQL? or on the other server side?

and for the 'exec sp_executesql', it was only for excuting the dynamic@.sql to get those values that i need to calculate in set @.Total = @.balance / @.A + @.freetotal.

Thanks
|||

Hello xxd

as the requirement,I think you need to clac the value @.total return for Client that call sp [MAXTIME]

this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)

why "cursor"?
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.

it's a better method.

Reference: Stored Procedure,Cursor;

Cheers,
Hunt

/* Sample Code by Hunt Begin*/

create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output)
--here is the only modified i made

as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number

set @.sql =
' Declare tmpcur cursor for '
' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'

exec (@.sql)
open tmpcur;
Fetch Next From tmpcur into @.A;
close tmpcur;
Deallocate tmpcur;

select @.total = @.balance / @.A + @.freetotal

/*Sample Code by Hunt End*/

|||Thanks Hunt:

1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @.a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.

based on your code, i modified mine as below:
alter proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output)

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--don't know where did i get those \ from

set @.Total = @.balance / @.A + @.freetotal

end

return @.Total
return @.balance

however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@.Total', which was not supplied.

but one step closed i think

Cheers.
|||

ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.
Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"
Cheers,
Hunt
alter proc [sp_test1]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.A float
declare @.SQL nvarchar(4000);
declare @.SQLparm nvarchar(500);

set @.table ='car'
set @.balance = 3.0
set @.freetotal = 2.0
--the section below will be the most important.
set @.SQLparm = N'@.A float output'
select @.sql = ' select @.A = 2.0 FROM ' + @.table
exec sp_executesql @.sql,@.SQLparm ,@.A output
set @.Total = @.balance / @.A + @.freetotal
go

declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
print ''
print @.tot
print @.free
go

|||thank you for writing all of those code.

however, why i need those below?
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output

and i think it should be like exec [sp_test1] '1','2','3', @.Total output, @.balance output

otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
and there are no more space for @.Total output and @.balance output.

so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @.Total output, @.balance output

hope i explained clearly.

Cheers
|||

this thread got a little problem,i can't post any word on it.

try put default value behind the varity.
@.total float =0 out,@.balance float =0 out

in this sample,you can call sp with no output param.
check it.

Cheer.

|||hi thanks,
i'll try it tomorrow, let you know then|||did you mean that
exec [sp_test1] '1','2','3', @.Total float = 0 output, @.balance float = 0 output

?
or exec [sp_test1] '1','2','3','0','0'

it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @.total float =0 out,@.balance float =0 out for the application case, and that application tells me that it did not get anything.

so is there any better way to solve it out?

many thx
|||

You have to put the default value when you create stored procedure.
as below

Create Proc [MAXTime] (@.numbera varchar(20),@.numberb varchar(20),@.numberc varchar(20),
@.total float =0 out,@.balance float =0 out);

after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@.total out,@.balance out

try it.

|||hi HuntTsai:

thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:

1select @.something
2@.something output
3return@.something and return(0)

all of above are the same(of course not) or for some special using?
thanks

|||

I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.

anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).

1. select @.something
2. @.something output
3. return@.something and return(0)
4. sometimes we also set it up as another type of trigger.

Best Regrads. :)

|||Hi HuntTsai:

Thanks a lot.

Problem emailing report

Hi All,
Below is an extract from my RSReportServer.Config file.
Can anyone see what the problem is with my configuration.
I get the following error in my subscription
"Failure sending mail: The server rejected the sender address. The server
response was: 550 Sender address is missing a domain "
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.registeredsite.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>pop.registeredsite.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>
<Extension Name="NULL"
Thanks
SanjeevThe from tag should include an email address. I used ReportingServices,
so for you the line would be:
<From>ReportingServices@.registeredsite.com</From>
This doesn't have to be a valid address.|||Hi Pete,
I have done as you suggested. Unfortunately, I just a get a different error
message now.
"The e-mail address of one or more recipients is not valid."
Did you get this as well before sorting the problem out?
Thanks
Sanjeev
"Pete" <prahalski@.hotmail.com> wrote in message
news:1131569084.930928.236010@.g43g2000cwa.googlegroups.com...
> The from tag should include an email address. I used ReportingServices,
> so for you the line would be:
> <From>ReportingServices@.registeredsite.com</From>
> This doesn't have to be a valid address.
>

Problem doing Update and Insert to different tables in same procedure.

We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated.

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[AddPhoto]

@.AlbumID

int,

@.Caption

nvarchar(MAX)

AS

INSERT

INTO [Photos](

[AlbumID]

,

[Caption]

,

[Location]

,

[LastModified]

)

VALUES

(

@.AlbumID

,

@.Caption

,

'tmpLocation'

,/* tmpLocation needed because app broke when Location column set to Allow NULLs */

GetDate

())

/* Retrieve generated PhotoID */

DECLARE

@.PhotoIDint

SET

@.PhotoID=SCOPE_IDENTITY()

/* Build unique location path from album and photo ID */

DECLARE

@.Locationnvarchar(MAX)

SET

@.Location='\'+CONVERT(nvarchar(10), @.AlbumID)+'\'+CONVERT(nvarchar(10),@.PhotoID)+'.jpg'

/* Update photo with new location path */

UPDATE

[Photos]

SET

[Location]

= @.Location

WHERE

[PhotoID]

= @.PhotoID

/* Update photo with new location path */

******************************************The code never executes the statement below********************************************

INSERT

INTO [PhotoDefault](

[pidm]

,

[defaultPhoto]

,

[activityDate]

)

VALUES

(

'1234'

,

'test'

,

getdate

()

)

/* Return PhotoID and Location */

SELECT

@.PhotoID, @.Location

RETURN

Thanks,

Jason

Next time, when you post your code please use the Code editor available when you post. Your code is hard to read.

The code (After formatting) looks fine to me. Throw in a couple of PRINT statements before and after the INSERT. There is no reason the INSERT should be skipped.

|||

Sorry about the code post. I didn't know about the code editor.

I have posted print statements after the insert and they are never excuted. I have also tried posting the first insert after with no luck as well. Any other suggestion?

|||

I formatted your code for you. Try this new code if you see the messages:

set ANSI_NULLSON set QUOTED_IDENTIFIERON GOALTER PROCEDURE [dbo].[AddPhoto] @.AlbumIDint, @.Captionnvarchar(MAX)ASINSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified])VALUES ( @.AlbumID, @.Caption,'tmpLocation' ,/* tmpLocation needed because app broke when Location column set to Allow NULLs */GetDate())/* Retrieve generated PhotoID */DECLARE @.PhotoIDint SET @.PhotoID = SCOPE_IDENTITY()/* Build unique location path from album and photo ID */DECLARE @.Locationnvarchar(MAX)SET @.Location ='\' +CONVERT(nvarchar(10), @.AlbumID) +'\' +CONVERT(nvarchar(10),@.PhotoID) +'.jpg'/* Update photo with new location path */UPDATE [Photos]SET [Location] = @.LocationWHERE [PhotoID] = @.PhotoID/* Update photo with new location path */******************************************The code never executes the statement below********************************************SELECT'I am here'INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate])VALUES ('1234','test',getdate() )SELECT'I am here again'/* Return PhotoID and Location */SELECT @.PhotoID, @.LocationRETURNGo

Problem Deleting Duplicate Data

I have a table that contains more than 10,000 rows of

duplicate data. The script below copies the data to a temp table then

deletes from the original table. My problem is that after it runs, I now

have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the

triplicate data and returns 0 rows. I've use three different versions of

delete dup row scripts with the same result. There are no triggers or

constraints on the table, not even a primary key. What am I missing?

-

/**********************************************
Delete Duplicate Data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
[student_test_uniq] [bigint] NULL,
[test_uniq] [int] NULL,
[concept_id] [smallint] NULL,
[test_id] [varchar](12) NULL,
[questions_correct] [smallint] NULL,
[questions_count] [smallint] NULL,
[percentage_correct] [decimal](6, 3) NULL,
[concept_response_count] [smallint] NULL
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @.@.ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM crt_concept_score
FROM crt_concept_score
INNER JOIN #tempduplicatedata
ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq
AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq
AND crt_concept_score.concept_id = #tempduplicatedata.concept_id
AND crt_concept_score.test_id = #tempduplicatedata.test_id
AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct
AND crt_concept_score.questions_count = #tempduplicatedata.questions_count
AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct
AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count

--Insert the delete data back
INSERT INTO crt_concept_score
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Check table
-- SELECT * FROM crt_concept_score

--Drop temp table
DROP TABLE #tempduplicatedata
GO

i've seen something like this in one of Kat's post

here's the link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=746636&SiteID=1