Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

Help with c# syntax

Hello,

I have at sp that return a value:
setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[sp_getLastActivityDate]

(

@.useridnvarchar(256)

)

AS

DECLARE @.retnvarchar(256)

SET @.ret=(SELECTCAST(LastActivityDateASnvarchar)

FROM aspnet_Users

WHERE UserName= @.userid)

RETURN @.ret

The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @.ret?

// Tomas

I tried to make a very simple try to retrieve the value from sp but I get exception

"String[0]: the Size property has an invalid size of 0." exception

Whats wrong with this code?

ALTERPROCEDURE [dbo].[sp_test1]
(
@.UNnvarchar='AHHHH'OUTPUT
)
as

and the c# code

using (SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["nyconn"].ConnectionString))

{

using (SqlCommand command =newSqlCommand("sp_test1", connection))

{

command.CommandType =CommandType.StoredProcedure;

SqlParameter para = command.Parameters.Add("@.UN",SqlDbType.NVarChar);

para.Direction =ParameterDirection.Output;

connection.Open();

command.ExecuteNonQuery();

string s = (String)command.Parameters["@.UN"].Value;

connnection.Close();|||

Hi,

From your second post, it seems that you are using SqlCommand to execute your stored procedure and declare a output parameter to retrieve the return from your stored procedure, right?

If so, the cause of getting errors like ""String[0]: the Size property has an invalid size of 0." exception" is that you didn't set the size of the output parameter. Since you are declaring an output parameter, it's necessary to declare the size of the parameter explicitly in your code. See the following code snippet:

SqlParameter para = command.Parameters.Add("@.UN", SqlDbType.NVarChar);
para.Direction = ParameterDirection.Output;
para.Size = 50; // 50 for example.

Thanks.

Help with a Query

Am I able to do something like this? The syntax below is obviously
wrong, I'm looking to see if there is a way to accomplish this.
DECLARE @.DBName varchar(5)
SET @.DBName = 'pubs'
SELECT * FROM @.DBName.dbo.authors
I have a master application and database containing data for several
companies. Each company uses a certain web-based sales software. I have
a local copy of those databases. Each of those databases is identical
in structure, they just contain each company's data.
I want to use stored procedures in my main database to run reports on
the data in each of the databases. I don't want to copy my stored
procedures into each database. Instead I want to call them from the
main database, and within the stored procedure, decide on which
database to run the t-sql.
Thanks!"George" <george.durzi@.gmail.com> wrote in message
news:1134592425.040038.242050@.z14g2000cwz.googlegroups.com...
> Am I able to do something like this? The syntax below is obviously
> wrong, I'm looking to see if there is a way to accomplish this.
> DECLARE @.DBName varchar(5)
> SET @.DBName = 'pubs'
> SELECT * FROM @.DBName.dbo.authors
> I have a master application and database containing data for several
> companies. Each company uses a certain web-based sales software. I have
> a local copy of those databases. Each of those databases is identical
> in structure, they just contain each company's data.
> I want to use stored procedures in my main database to run reports on
> the data in each of the databases. I don't want to copy my stored
> procedures into each database. Instead I want to call them from the
> main database, and within the stored procedure, decide on which
> database to run the t-sql.
> Thanks!
>
You will need to use Dynamic SQL.
Try the following:
EXECUTE ('SELECT * FROM ' + @.DBName + '.dbo.authors')
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, the only issue with that is I actually am gonna be writing a
complex stored procedure.
It looks like dynamic sql is going to have to be the way to go though.|||Before diving into the dynamicity pool and drowning, take look up
"distributed partitioned views" in Books Online.
I'd create an extra database and use it as a central reporting data source.
ML
http://milambda.blogspot.com/

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
JeffThis should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:
>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
Jeff
This should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:

>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff
|||Here's an interesting twist of the DELETE statement syntax that I ran across
not too long ago...
DELETE FROM TableA FROM TableB
WHERE TableA.Column1 = TableB.Column1 AND
TableA.Column2 = TableB.Column2 AND
TableA.Column3 = TableB.Column3
The double FROMs look weird, but it's valid syntax and it even works.
Regards,
Jerry
"jeff" wrote:

> Wondering if someone can show me the correct syntax of a delete
> statement where I want to delete rows from table 'A' where say column1
> of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
> 2 of table 'B'. Working within a procedure and would like to do
> something cleaner than having to create a cursor. I'm sure there
> example but not sure exactly what to search for!
> Thanks,
> Jeff
>

Help with a delete statement

Wondering if someone can show me the correct syntax of a delete
statement where I want to delete rows from table 'A' where say column1
of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
2 of table 'B'. Working within a procedure and would like to do
something cleaner than having to create a cursor. I'm sure there
example but not sure exactly what to search for!
Thanks,
JeffThis should give you something to think about.
DELETE FROM A
WHERE EXISTS
(SELECT * FROM B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)
Roy Harvey
Beacon Falls, CT
On 4 Jan 2007 15:20:55 -0800, "jeff" <jeffa@.telect.com> wrote:

>Wondering if someone can show me the correct syntax of a delete
>statement where I want to delete rows from table 'A' where say column1
>of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
>2 of table 'B'. Working within a procedure and would like to do
>something cleaner than having to create a cursor. I'm sure there
>example but not sure exactly what to search for!
>Thanks,
>Jeff|||Here's an interesting twist of the DELETE statement syntax that I ran across
not too long ago...
DELETE FROM TableA FROM TableB
WHERE TableA.Column1 = TableB.Column1 AND
TableA.Column2 = TableB.Column2 AND
TableA.Column3 = TableB.Column3
The double FROMs look weird, but it's valid syntax and it even works.
Regards,
Jerry
"jeff" wrote:

> Wondering if someone can show me the correct syntax of a delete
> statement where I want to delete rows from table 'A' where say column1
> of table 'A' = column 1 of table 'B' and column 2 of table 'A' = column
> 2 of table 'B'. Working within a procedure and would like to do
> something cleaner than having to create a cursor. I'm sure there
> example but not sure exactly what to search for!
> Thanks,
> Jeff
>

Help w/syntax select in a while loop

I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:

'AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop'

Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:

Also I'm trying really hard not to use temp tables in this example

Result from select statement below

sh_serial
----
53565
53597

sh_serial
----
53565
53597

sh_serial
----

sh_serial
----
53588
53597

Desired results:

sh_serial
----
53588
53597
53565

Syntax:

declare @.start int
select @.start = 580
declare @.stop int
select @.stop = 900

while @.start <= @.stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in

(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @.start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @.start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))

select @.start = @.start + 1
end

I'd appreciate any help. Thanks! :oInsert into a table variable and select distinct from that when you leave the loop?|||cause of business logic I cannot use temp tables or tables...

is there a way that I can put this into some sort of a derived table and then do the select distinct?|||I was gonna rewrite it...but I got scared...

SQL 2000?

DECLARE @.start int, @.stop int
SELECT @.start = 580, @.stop = 900

DECLARE @.x TABLE(shSerial int)

WHILE @.start <= @.stop
BEGIN

INSERT INTO @.x(shSerial)
SELECT sh_serial
FROM casemas, schilin
WHERE schi_shser = sh_serial
AND schi_itemno = '004852'
AND sh_serial <> 600000
AND sh_serial in (53565,53588,53597)
AND sh_serial in ( SELECT DISTINCT sh_serial
FROM casemas, schilin
WHERE (schi_shser = sh_serial)
AND (schi_itemno = '004852')
AND sh_serial in (53565,53588,53597)
AND @.start NOT IN ( SELECT sh_istart
FROM casemas
WHERE sh_istart in (SELECT sh_istop
FROM casemas
WHERE sh_serial in (53565,53588,53597)))
AND @.start between sh_istart and sh_istop
GROUP BY sh_serial
HAVING (sum(schi_qty) + 1 < 4 ))

SELECT @.start = @.start + 1
END

SELECT DISTINCT shSerial FROM @.x|||Thanks Brett,

and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time

'AND sh_serial in (53565,53588,53597)'

and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks! :)|||Store those values in a temp table (@. or #) and do WHERE EXISTS (...).

Help using the formulas in report builder

I am really new to this. If anyone knows a place to go to learn about the syntax of the formulas contained in the report builder and filters I would appreciate it.

Also I am studying from a book from apress and they have some junk to download. I am doing this on my home computer and was wondering if MS had a 30 day trial version of pro sql server 2005 that I could use while at home. My work has it instalkled and I am just remoting in but do not want to dl apresses examples on their server. Thanks Travis

The primary documentation for Report Builder is (currently) only available from within the application, using the Help item on the main menu.

Trial versions of SQL Server can be obtained here: http://www.microsoft.com/sql/downloads/trial-software.mspx.

Hope this helps!