Check image in a SQL Server DB

Hello!

I want to read an image from the database. I've this function but she returns me an error that suggest me that the image might be damaged or corrupted.

I would like to know if it's possible and how to check an image in the DB.

Thank you!

i use the datalenght function to check the size of the image in the DB, it returned "1" in some cases and "null".

I assumed that the imagens aren't ok, and i'll see if i can find the original ones to upload them to the BD.

Am i correct? Can i assume that the number one (1), returned from the function "datalength" means that the images have 1byte of size which i know it's false?

Thank you

CHECK DOUBLE RECORD

hi,
I've problem to check row which having duplicate value
for fieldA and fieldC.
Eg.
rec 1
fieldA fieldB fieldC
50000A 123456 A
rec 2
50000A 654321 A
Thanks.
see following example:
create table test(c1 varchar(10), c2 varchar(10), c3 char(1))
insert into test values('50000A','123456','A')
insert into test values('50000A','654321','A')
insert into test values('50000X','654321','A')
query:
select a.*
from test a join (Select c1,c3 from test group by c1,c3 having count(*) >
1) b
on a.c1 = b.c1 and a.c3=b.c3
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Check constraints not being replicated properly

Hello, I'm at a loss with a tough problem I've yet to find a solution for:
SCENARIO:
I am replicating as a form of disaster-recovery. Our published application
uses local partitioned views, partitioned by month... so there is one
database per month. We have a date_created partitioning column in each
table which specifies that records should only go in that month. Example
for our Feb partition on the Processor_File table:
ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
[date_created]<'03/01/2008'))
When I add the tables as articles to the database's publication, I AM
setting the Copy Check Constraints = true option. I WANT this to occur,
because there's this crazy condition with partitioned views whereby the
check constraints can be enabled and yet somehow they are not "confident" in
the status of their checked data and therefore views that include the table
are considered non-updateable (which sucks -- can anybody explain THAT to me
either?!)
Regardless... I WANT the Check Constraints to be copied over AND I want them
enabled for replication. The problem, when the SNAPSHOT is generated to
create the database schema, it looks just like this:
IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
BEGIN
ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
[DF_Processor_File_date_created]
END
Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
want that clause to be set!!! That clause is _NOT_ set in the source
description of my published table. How can I make sure the snapshot does
not create the tables on the subscriber side with that option?
TIA for your support,
Eric Gavin
Alliance Payment Technologies
Do you want this constraint enforced on the subscriber when the DML is
occurring due to a replication process? If so you do not want that
constraint to be not for replication? It sounds like this is likely
something you don't want, but I don't think it is necessarily evil.
Please post your schema of the table with the constraints and I'll show you
how to create your publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
> Hello, I'm at a loss with a tough problem I've yet to find a solution for:
> SCENARIO:
> I am replicating as a form of disaster-recovery. Our published
> application uses local partitioned views, partitioned by month... so there
> is one database per month. We have a date_created partitioning column in
> each table which specifies that records should only go in that month.
> Example for our Feb partition on the Processor_File table:
> ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
> [date_created]<'03/01/2008'))
> When I add the tables as articles to the database's publication, I AM
> setting the Copy Check Constraints = true option. I WANT this to occur,
> because there's this crazy condition with partitioned views whereby the
> check constraints can be enabled and yet somehow they are not "confident"
> in the status of their checked data and therefore views that include the
> table are considered non-updateable (which sucks -- can anybody explain
> THAT to me either?!)
> Regardless... I WANT the Check Constraints to be copied over AND I want
> them enabled for replication. The problem, when the SNAPSHOT is generated
> to create the database schema, it looks just like this:
> IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
> BEGIN
> ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
> ([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
> ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
> [DF_Processor_File_date_created]
> END
> Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
> want that clause to be set!!! That clause is _NOT_ set in the source
> description of my published table. How can I make sure the snapshot does
> not create the tables on the subscriber side with that option?
> TIA for your support,
> Eric Gavin
> Alliance Payment Technologies
>
|||[Note to Microsoft folks -- I still need help please!]
Thanks for trying to help, but part of the frustration I have is that I seem
to WANT to be doing something that nobody else really wants to do. And so
I've scrolled through a million google posts trying to figure out how to do
what nobody else wants to do. I DO want to enforce the constraints at the
DML during replication because of the partitioned view requirements.
Not meaning to sound rude, but my original post has every answer to your
question already in it, so I'll just re-post it as a response:
[Repost]
> occurring due to a replication process?

> constraint to be not for replication?
[vbcol=seagreen]
Lastly, the schema of the constraint in the published table and the schema
that the snapshot generates has already been posted. May I ask why it would
be important to know the whole schema of the table?
Thanks, and I'm sorry if any offense was given. I have been at this for
quite some time now and am still frustrated because after reading the books
I appear to be doing everything right, but the snapshot just "refuses" to
create WITHOUT the "NOT FOR REPLICATION" option set during constraint
creation. I NEED that constraint to NOT be used when the snapshot as
applied at the subscriber.
Thanks,
Eric Gavin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vC48aeWIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Do you want this constraint enforced on the subscriber when the DML is
> occurring due to a replication process? If so you do not want that
> constraint to be not for replication? It sounds like this is likely
> something you don't want, but I don't think it is necessarily evil.
> Please post your schema of the table with the constraints and I'll show
> you how to create your publication.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AACH Developers" <AACHDevs@.community.nospam> wrote in message
> news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
|||Confusion posted by me on the last bit:

> Thanks, and I'm sorry if any offense was given. I have been at this for
> quite some time now and am still frustrated because after reading the
> books I appear to be doing everything right, but the snapshot just
> "refuses" to create WITHOUT the "NOT FOR REPLICATION" option set during
> constraint creation. I NEED that constraint to NOT be used when the
> snapshot as applied at the subscriber.
Please correct this to:
I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
is applied.
For further clarity, I must, want, desire and need the check constraints to
be checked during the application of DML because of replication.
Eric
|||UPDATE: Apparently the schema_option:
0x10000
Replicates CHECK constraints as NOT FOR REPLICATION so that the
constraints are not enforced during synchronization.
Exists, but BOL seems to leave out the small (yet necessary detail) that it
is simply un-settable from the GUI. So I think this problem is solved if
I'm willing to run sp_changearticles for all of my table articles with check
constraints (which I am.)
But if MS-folk read this and thinking I'm headed in the wrong direction, or
misguided in my belief that the UI doesn't allow for this setting to be
updated, I'd appreciate learning so. Thanks!!
Eric Gavin
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:e31zdvfWIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Confusion posted by me on the last bit:
>
> Please correct this to:
> I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
> is applied.
> For further clarity, I must, want, desire and need the check constraints
> to be checked during the application of DML because of replication.
> Eric
>
|||Hi Eric,
I understand that you would like to have the snapshot does not create the
tables on your subscriber with the NOT FOR REPLICATION clause.
If I have misunderstood, please let me know.
The option is a default option for SQL Server 2000/2005 replication when
there is any foreign key constraints or check constraints; however you can
programmatically use T-SQL statements to change the replication schema
options (schema_option) by yourself. Please refer to the following articles:
Frequently Asked Questions for Replication Administrators
-- How do I manage constraints on published tables?
http://msdn2.microsoft.com/en-us/library/ms151740.aspx
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn2.microsoft.com/en-us/library/ms152529.aspx
How to: Specify Schema Options (Replication Transact-SQL Programming)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_addarticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_changearticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175980.aspx
For your other concern, "When I add the tables as articles to the
database's publication, I AM setting the Copy Check Constraints = true
option. I WANT this to occur, because there's this crazy condition with
partitioned views whereby the check constraints can be enabled and yet
somehow they are not "confident" in the status of their checked data and
therefore views that include the table are considered non-updateable (which
sucks -- can anybody explain THAT to me either?!)", I could not understand
it very clearly. To let me better understand your issue, could you please
answer me the following questions:
1. What it the type of your replication?
2. Did you mean that your views on your publisher became non-updatable
after you set the replication option "Copy Check Constraints = true" for
those table articles?
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Character greater than 'z'

I've searched and have not found any good answers to this. Maybe there
isn't one...
I need to have a string that when sorted with an ORDER BY, it comes after
the letter 'z'.
The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
I know it is best to not rely on this and use some sort of surrogate field
to order the rows, but I cannot do that in this case - the data is being
used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
SharePoint will sort one of it's lists by the BDC column value, but it sorts
only by the values in that column.
In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
the results.
Is there any character I could put at the beginning of the string so it
would fall out last in the query results?
(Remember, it is SharePoint generating the query, so I cannot change the SQL
it uses. I only control the values in the column.)
I assume the column is char or varchar? If so, you can use the value
CHAR(208), for example,
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values (Char(208))
Insert #FooBar (Foo) Values ('z')
Select Foo, Ascii(Foo)
From #FooBar
Order By Foo
go
Drop Table #FooBar
You can find all the characters which will sort after 'z' with the following
code
Set NoCount ON
Create Table #FooBar(Foo varchar(10))
Insert #FooBar (Foo) Values ('A')
Insert #FooBar (Foo) Values ('z')
Declare @.i int
Set @.i = 1
While @.i < 256
Begin
Insert #FooBar (Foo) Values (Char(@.i))
Set @.i = @.i + 1
End
Select Foo, Ascii(Foo)
From #FooBar
Where Foo > 'z'
Order By Foo
go
Drop Table #FooBar
Tom
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||JD
select * from #FooBar order by case when foo ='z' then 1 else 2 end
"JD" <yazoo@.newsgroup.nospam> wrote in message
news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
> I've searched and have not found any good answers to this. Maybe there
> isn't one...
> I need to have a string that when sorted with an ORDER BY, it comes after
> the letter 'z'.
> The database is created with the collation SQL_Latin1_General_CP1_CI_AS.
> I know it is best to not rely on this and use some sort of surrogate field
> to order the rows, but I cannot do that in this case - the data is being
> used by SharePoint (MOSS) in a Business Data Catalog (BDC) column.
> SharePoint will sort one of it's lists by the BDC column value, but it
> sorts only by the values in that column.
> In ASCII, I could use the tilde (~), but SQL is putting the tilde first in
> the results.
> Is there any character I could put at the beginning of the string so it
> would fall out last in the query results?
> (Remember, it is SharePoint generating the query, so I cannot change the
> SQL it uses. I only control the values in the column.)
>
|||As I said, I cannot change the query, since Sharepoint is doing the query.
I can only affect the data in the column.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uVGG%2305nIHA.4328@.TK2MSFTNGP03.phx.gbl...
> JD
> select * from #FooBar order by case when foo ='z' then 1 else 2 end
>
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks - it is nvarchar, so I made a few changes to your example and found a
series of Unicode characters that sort after 'z':
set nocount on
create table #FooBar(Foo nvarchar(10))
insert #FooBar (Foo) values ('A')
declare @.i int
set @.i = 900
while (@.i < 1200)
begin
insert #FooBar (Foo) values (nchar(@.i))
set @.i = @.i + 1
end
insert #FooBar (Foo) values ('z')
select Foo, unicode(Foo)
from #FooBar
where Foo > 'z'
order by Foo
drop table #FooBar
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:O6sC0c3nIHA.4832@.TK2MSFTNGP06.phx.gbl...
>I assume the column is char or varchar? If so, you can use the value
>CHAR(208), for example,
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values (Char(208))
> Insert #FooBar (Foo) Values ('z')
> Select Foo, Ascii(Foo)
> From #FooBar
> Order By Foo
> go
> Drop Table #FooBar
> You can find all the characters which will sort after 'z' with the
> following code
> Set NoCount ON
> Create Table #FooBar(Foo varchar(10))
> Insert #FooBar (Foo) Values ('A')
> Insert #FooBar (Foo) Values ('z')
> Declare @.i int
> Set @.i = 1
> While @.i < 256
> Begin
> Insert #FooBar (Foo) Values (Char(@.i))
> Set @.i = @.i + 1
> End
> Select Foo, Ascii(Foo)
> From #FooBar
> Where Foo > 'z'
> Order By Foo
> go
> Drop Table #FooBar
> Tom
> "JD" <yazoo@.newsgroup.nospam> wrote in message
> news:%23uiqZB3nIHA.3556@.TK2MSFTNGP04.phx.gbl...
>