"marking" the processed elements in data-driven subscription

I would like to use e-mail delivery for the following scenario: new orders
arrive in a table and i
would like to send confirmation messages which contain the content of the
order. The RS has an "every
5 minutes" schedule and should send e-mail just about the new orders to the
customers.
How could I differentiate the new elements from the old ones?
- or -
How can I "mark" the orders, when the confirmation report was created that
they were processed?
Thanks in advance,
Kolos"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||'
"Bender" <roman.ilic@.avtenta.si> wrote in message
news:%23%23BJFeHgEHA.3416@.TK2MSFTNGP09.phx.gbl...
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> > I would like to use e-mail delivery for the following scenario: new
orders
> > arrive in a table and i
> > would like to send confirmation messages which contain the content of
the
> > order. The RS has an "every
> > 5 minutes" schedule and should send e-mail just about the new orders to
> the
> > customers.
> >
> > How could I differentiate the new elements from the old ones?
> > - or -
> > How can I "mark" the orders, when the confirmation report was created
> that
> > they were processed?
> >
> > Thanks in advance,
> > Kolos
> >
> >
> >
>
>|||Hi Kolos,
We do something similar.
We have an additional field in our subscription Queue which has a Processed
Flag field (just a BIT). We update this field whenever the entry has been
processed via the data driven subscription's query. If you don't want to
add a field to this table, you could create an additional table recording a
log of processed Orders.
HTH
Matt
"Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> I would like to use e-mail delivery for the following scenario: new orders
> arrive in a table and i
> would like to send confirmation messages which contain the content of the
> order. The RS has an "every
> 5 minutes" schedule and should send e-mail just about the new orders to
the
> customers.
> How could I differentiate the new elements from the old ones?
> - or -
> How can I "mark" the orders, when the confirmation report was created
that
> they were processed?
> Thanks in advance,
> Kolos
>
>|||That's on the right track, but I'd like to also point out that an easy way
to update the "flag" field is to use a stored procedure. Within the sproc
you can return the records affected by the email notice and revisit each row
to update the flag value. =)
Matt
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Hi Kolos,
> We do something similar.
> We have an additional field in our subscription Queue which has a
> Processed
> Flag field (just a BIT). We update this field whenever the entry has been
> processed via the data driven subscription's query. If you don't want to
> add a field to this table, you could create an additional table recording
> a
> log of processed Orders.
> HTH
> Matt
> "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
>> I would like to use e-mail delivery for the following scenario: new
>> orders
>> arrive in a table and i
>> would like to send confirmation messages which contain the content of the
>> order. The RS has an "every
>> 5 minutes" schedule and should send e-mail just about the new orders to
> the
>> customers.
>> How could I differentiate the new elements from the old ones?
>> - or -
>> How can I "mark" the orders, when the confirmation report was created
> that
>> they were processed?
>> Thanks in advance,
>> Kolos
>>
>|||lol, actually, I do use a stored procedure. I was being just more general
and wasn't sure of Kolos's particular scenario.
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:10q9p1l72kj6iaa@.corp.supernews.com...
> That's on the right track, but I'd like to also point out that an easy way
> to update the "flag" field is to use a stored procedure. Within the sproc
> you can return the records affected by the email notice and revisit each
row
> to update the flag value. =)
> Matt
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:ucN2OnDrEHA.868@.TK2MSFTNGP10.phx.gbl...
> > Hi Kolos,
> >
> > We do something similar.
> >
> > We have an additional field in our subscription Queue which has a
> > Processed
> > Flag field (just a BIT). We update this field whenever the entry has
been
> > processed via the data driven subscription's query. If you don't want
to
> > add a field to this table, you could create an additional table
recording
> > a
> > log of processed Orders.
> >
> > HTH
> >
> > Matt
> >
> > "Kolos Daniel" <d1974kol@.mailbox.hu> wrote in message
> > news:OeW5p0GgEHA.556@.tk2msftngp13.phx.gbl...
> >> I would like to use e-mail delivery for the following scenario: new
> >> orders
> >> arrive in a table and i
> >> would like to send confirmation messages which contain the content of
the
> >> order. The RS has an "every
> >> 5 minutes" schedule and should send e-mail just about the new orders to
> > the
> >> customers.
> >>
> >> How could I differentiate the new elements from the old ones?
> >> - or -
> >> How can I "mark" the orders, when the confirmation report was created
> > that
> >> they were processed?
> >>
> >> Thanks in advance,
> >> Kolos
> >>
> >>
> >>
> >
> >
>

"Invalid Character value for cast specification" from VC++ when i execute a proc

Hi Everybody,

I have a question regarding the running of a procedure from VC++ in SQL server.

well this is the scenario.

i have a procedure in SQL server and i am running that thru my VC++ code.
:eek:
i have a recordset class and i create an object of it and then do a obj.Open(). the procedure is fetching 24 records correctly in the query analyser and in my while(!(obj.IsEOF())) loop it gets the first record correctly but the moment it encounters obj.MoveNext(); it bombs and gives me the following error..

"Invalid Character value for cast specification ".

i don't know why this is happening

Thanks for you help.Somewhere in your Transact-SQL code you are trying to convert a character value to another datatype. My first guess would be DATETIME, next might be INT or another numeric datatype. This is usually either in a column you are returning or in a WHERE clause, but it can happen anywhere you can refer to a column.

-PatP|||BTW, since this actually appears to be a Transact-SQL problem, I'm moving the thread back to the Microsoft SQL forum.

-PatP

"Filling in the gaps" with a single-line query

Hi,
I've got the following scenario:
Files are being stored in a database, with a number of name-value
pairs associated with each file. This happens by storing the files in
one table (File), the list of property names in another table
(FileMetaDataSchema) and the values of properties in a third table
(FileMetaData), which references both File and FileMetaDataSchema.
The frontend of my application assumes there is a record for each
property of each file in the FileMetaData table, even if the value is
an empty string. In other words, if i have a list of 3 properties and
2 files, FileMetaData will contain 6 records.
Due to a bug in the system, this does not always happen. Suppose one
adds a new property and neglects to insert the "blank" records for the
new properties for all the files, or a new file is added, but the
associated meta-data records are not... (why and how this happens is
not the topic of discussion, so don't worry about that).
I have written a sql script to insert all the missing "blank" records,
but I feel it is very clumsy and intuitively, I just know there must
be a simpler way, my knowledge is just too limited. What it does is,
it iterates (using cursors) through all the files and all the
properties, checks if there is a record for each combination FileID
and FileMetaDataSchemaID and if not, it inserts one. I am looking for
a better way out of curiosity, for my own benefit.
Here's the script and thanks for any input:
DECLARE MetadataSchemaCursor CURSOR FOR
SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
DECLARE @.FileMetaDataSchemaID INT,
@.FileID INT
OPEN MetadataSchemaCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DECLARE FileCursor CURSOR FOR
SELECT FileID FROM [File]
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @.FileID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
FileMetaDataSchemaID = @.FileMetaDataSchemaID)
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
FETCH NEXT FROM FileCursor INTO @.FileID
END
CLOSE FileCursor
DEALLOCATE FileCursor
FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
END
CLOSE MetadataSchemaCursor
DEALLOCATE MetadataSchemaCursor>I just know there must
>be a simpler way, my knowledge is just too limited.
You are correct, there is a simpler way.
INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
SELECT A.FileID, B.FileMetaDataSchemaID, ''
FROM [File] as A
CROSS
JOIN FileMetaDataSchema as B
WHERE NOT EXISTS
(select * from FileMetaData as X
where A.FileID = X.FileID
and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:48:53 -0800, "Velislav" <vgebrev@.gmail.com> wrote:
>Hi,
>I've got the following scenario:
>Files are being stored in a database, with a number of name-value
>pairs associated with each file. This happens by storing the files in
>one table (File), the list of property names in another table
>(FileMetaDataSchema) and the values of properties in a third table
>(FileMetaData), which references both File and FileMetaDataSchema.
>The frontend of my application assumes there is a record for each
>property of each file in the FileMetaData table, even if the value is
>an empty string. In other words, if i have a list of 3 properties and
>2 files, FileMetaData will contain 6 records.
>Due to a bug in the system, this does not always happen. Suppose one
>adds a new property and neglects to insert the "blank" records for the
>new properties for all the files, or a new file is added, but the
>associated meta-data records are not... (why and how this happens is
>not the topic of discussion, so don't worry about that).
>I have written a sql script to insert all the missing "blank" records,
>but I feel it is very clumsy and intuitively, I just know there must
>be a simpler way, my knowledge is just too limited. What it does is,
>it iterates (using cursors) through all the files and all the
>properties, checks if there is a record for each combination FileID
>and FileMetaDataSchemaID and if not, it inserts one. I am looking for
>a better way out of curiosity, for my own benefit.
>Here's the script and thanks for any input:
>DECLARE MetadataSchemaCursor CURSOR FOR
> SELECT FileMetaDataSchemaID FROM FileMetaDataSchema
>DECLARE @.FileMetaDataSchemaID INT,
> @.FileID INT
>OPEN MetadataSchemaCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>WHILE (@.@.FETCH_STATUS = 0)
>BEGIN
> DECLARE FileCursor CURSOR FOR
> SELECT FileID FROM [File]
> OPEN FileCursor
> FETCH NEXT FROM FileCursor INTO @.FileID
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM FileMetaData WHERE FileID = @.FileID AND
>FileMetaDataSchemaID = @.FileMetaDataSchemaID)
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID,
>PropertyValue) SELECT @.FileID, @.FileMetaDataSchemaID, ''
> FETCH NEXT FROM FileCursor INTO @.FileID
> END
> CLOSE FileCursor
> DEALLOCATE FileCursor
>FETCH NEXT FROM MetaDataSchemaCursor INTO @.FileMetaDataSchemaID
>END
>CLOSE MetadataSchemaCursor
>DEALLOCATE MetadataSchemaCursor|||On Feb 22, 5:42 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> You are correct, there is a simpler way.
> INSERT INTO FileMetaData (FileID, FileMetaDataSchemaID, PropertyValue)
> SELECT A.FileID, B.FileMetaDataSchemaID, ''
> FROM [File] as A
> CROSS
> JOIN FileMetaDataSchema as B
> WHERE NOT EXISTS
> (select * from FileMetaData as X
> where A.FileID = X.FileID
> and B.FileMetaDataSchemaID = X.FileMetaDataSchemaID)
> Roy Harvey
> Beacon Falls, CT
>
Thank you :)
Note to self - look up cross joins.