immediate updating isnt working

Im doing some testing on Immediate Updating Transactional Replication. I
generated a script to drop the Subscription and Article to a particular
table to add an Identity column on it. After that, I re-added the table to
the Publication and re-snapshotted the table. No problem and all is good at
this point. However now when I insert a record into the Subscriber, the row
isn't getting back to the Publisher *for this table only.* It is working for
other tables though. I scripted out the sp_addarticle for both this table
and a table that the immediate updating is working properly on and they are
identicle with the exception of the names. Whats the dealio? All ideas are
appreciated.
SQL2K SP3
TIA, ChrisR
I figured out whats up. The triggers for Insert, Update, and Deletes arent
being created like they were during the original snapshot. Does anyone know
why?
"ChrisR" <bla@.noemail.com> wrote in message
news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
> Im doing some testing on Immediate Updating Transactional Replication. I
> generated a script to drop the Subscription and Article to a particular
> table to add an Identity column on it. After that, I re-added the table to
> the Publication and re-snapshotted the table. No problem and all is good
at
> this point. However now when I insert a record into the Subscriber, the
row
> isn't getting back to the Publisher *for this table only.* It is working
for
> other tables though. I scripted out the sp_addarticle for both this table
> and a table that the immediate updating is working properly on and they
are
> identicle with the exception of the names. Whats the dealio? All ideas are
> appreciated.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||how did you resync after making changes to your subscription.
I think it would be best to recreate your snapshot - it sounds like your
metadata is out of sync.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I figured out whats up. The triggers for Insert, Update, and Deletes arent
> being created like they were during the original snapshot. Does anyone
know[vbcol=seagreen]
> why?
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> at
> row
> for
table[vbcol=seagreen]
> are
are
>
|||I re-snapshotted the table.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> how did you resync after making changes to your subscription.
> I think it would be best to recreate your snapshot - it sounds like your
> metadata is out of sync.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
arent[vbcol=seagreen]
> know
I[vbcol=seagreen]
particular[vbcol=seagreen]
table[vbcol=seagreen]
> to
good[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
> table
they
> are
>
|||I realize that - but I think you need to re-snapshot the entire publication.
I had problems like this with merge replication, which could only be solved
by a complete resnapshotting and re-synchronization.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I re-snapshotted the table.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...
> arent
Replication.[vbcol=seagreen]
> I
> particular
> table
> good
> the
> working
> they
ideas
>
|||I was afraid of that. Just wanted to make sure we were on the same page.
Thanks Hillary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#BL4sBwLFHA.2796@.tk2msftngp13.phx.gbl...
> I realize that - but I think you need to re-snapshot the entire
publication.
> I had problems like this with merge replication, which could only be
solved[vbcol=seagreen]
> by a complete resnapshotting and re-synchronization.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...
your[vbcol=seagreen]
anyone[vbcol=seagreen]
> Replication.
is[vbcol=seagreen]
Subscriber,[vbcol=seagreen]
this
> ideas
>
sql

Images in SQL Server 2005 Express

Hey all,

I'm working on a database which (I hope) will have photos in it. However, I'm having difficulty how to properly insert photos into an SQL database. I see that it has an 'image' data-type, but I don't know how to make proper use it.

Given that the answer to this question could be quite lengthy, if someone could provide me a link to somewhere, or a book to pick up that could answer this answer, I would be most gratified. Thanks!

It is not that bad at all. Just convert your image to a ByteArray and it is ready to save.

Have a great day.

|||In general, I'm not a fan of storing images in the database directly. There just usually isn't any good reason to.

If you can, just store enough information in the database so you can find the image in question, and store the images on disk some place.

Images in DB for a website

Hi everyone. This is myfirst post here. I did a quick search and didn't find what I aslooking for, so I'm hoping you all can help me.
I'm working on a project that will allow my company to upload images toa database and have the website pull the images from the DB. Weare planning to incorporate some type of cacheing, however I'mskeptical as to if it is really necessary. I imagine that I'mcertianly not the first to do this and I'm wondering if someone can letme know if I truly should expect a performance hit, or if I shouldn'treally worry about it b/c this is a very common mantra.
Thanks all. I've been pretty vague and haven't explained all ofthe details. I can write more, but I'm just looking forgeneralizations right now.
Thanks,
HD
I think most of your questions are answered in the link below and remember to read the MSDN links at the end of the page. Hope this helps.
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=105&rl=1

Images are not showing...

Hi,

I have two different reports and in these two reports one contains images which is working fine at development site as well as server site but when same report is called from another report using linking facility given in sql report images disappears on server site at development site it is working fine.

Can any one help me out.

Thanks in advance.

Wasim.

Is this is SQL 2005 ?

Did you install SP1 ?

|||Yes, SP1 is installed on my server.|||Is the image embedded on the report or come from URL ?|||Images are embedded.

IIS Virtual Directory Management for SQL Server

I got the IIS Virtual Directory Management for SQL Server working but I
deleted the Virtual Directory and then went to reinstate it I got the error
"An Error occurred creating virtual directory [VD Name] The virtual
directory already exists"
Do I need to reinstall anything?
What are the correct service packs to have on a server Windows 2003 and SQL
2000?
"Dean" wrote:

> I got the IIS Virtual Directory Management for SQL Server working but I
> deleted the Virtual Directory and then went to reinstate it I got the error
> "An Error occurred creating virtual directory [VD Name] The virtual
> directory already exists"
> Do I need to reinstall anything?
|||Hi Dean,
There is no special requirements for a specific service pack for Windows
2003. For SQL 2000, you should have SP3 with you.
Are you using the MMC tool from the SQL 2000 installation or have you
installed the latest version of SQLXML from
(http://msdn.microsoft.com/library/de...-us/dnanchor/h
tml/anch_SQLXML.asp)
Generally this kind of error should not happen and is not dependent on the
version of the operating system. In your case you should check using the
Internet Information Services manager on the machine to see if the Virtual
Directory still exists If it is not there and you still see the error you
can use the Metabase edit tool from http://support.microsoft.com/kb/840671
to check and edit the IIS metabase store.
Thank you,
Amar Nalla [MSFT]
"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:EA982781-9011-4121-B266-A45ECF5D864D@.microsoft.com...
> What are the correct service packs to have on a server Windows 2003 and
SQL[vbcol=seagreen]
> 2000?
> "Dean" wrote:
error[vbcol=seagreen]

IIS Authentication for RDA not working

My Code:

rda.InternetLogin = "domain\username"

rda.InternetPassword = "password"

rda.SubmitSql("SELECT * FROM Table", rdaOleDbConnectString)

I have a client trying to use my application through SSL.

https://DOMAINCONTROLLER/sqlmobile/sqlcesa30.dll

they do not have certificiates setup...and it isn't working at all.

Any help?

You cannot use SSL without a public certificate installed on the server, and that certificate's root must be present on the device.

IIS 5.0 isolation mode

I have a Reporting Services setup on a web server on Windows 2003. It is
working fine. There is a web application that is having some problems and we
have set the web server to IIS 5.0 isolation mode. Will Reporting Services
run under 5.0 isolation mode?
At first it looks like a rights issue and giving the ASPNET account the same
rights as the NETWORK SERVICE account got us past the original access denied
type errors.
However now we are getting the error: The report server cannot decrypt the
symmetric key used to access sensitive or encrypted data in a report server
database. You must either restore a backup key or delete all encrypted
content and then restart the service.
What needs to be done to run under 5.0 isolation mode?
--
LehrSJHello,
You may want to try the following steps:
1. open C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config file.
Search for this tag, <InstallationID> and take the GUID (For example, the
GUID for me is {93850a3c-a856-4ce9-af25-6b3cdaa98dd8})
2. Use the GUID that we got before and try to remove the keys in the table
like this
RSKEYMGMT -R "{<guid>}"
3. Then run
RSACTIVATE -C "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config" > (make sure the path is the
one you installed Reporting Services)
If the issue persists, please make sure ASP.NET account has the proper
permission on the SQL server/Reprotserver databases.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: IIS 5.0 isolation mode
| thread-index: AcVm2ZoBIp9L/GQKRtOvDPnUBn1T/g==| X-WBNR-Posting-Host: 70.118.121.7
| From: =?Utf-8?B?TGVoclNK?= <lehr524@.noemail.nospam>
| Subject: IIS 5.0 isolation mode
| Date: Wed, 1 Jun 2005 11:42:02 -0700
| Lines: 17
| Message-ID: <0021FD03-66E4-44D5-9D54-F102D1F6727F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45125
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a Reporting Services setup on a web server on Windows 2003. It is
| working fine. There is a web application that is having some problems
and we
| have set the web server to IIS 5.0 isolation mode. Will Reporting
Services
| run under 5.0 isolation mode?
|
| At first it looks like a rights issue and giving the ASPNET account the
same
| rights as the NETWORK SERVICE account got us past the original access
denied
| type errors.
|
| However now we are getting the error: The report server cannot decrypt
the
| symmetric key used to access sensitive or encrypted data in a report
server
| database. You must either restore a backup key or delete all encrypted
| content and then restart the service.
|
| What needs to be done to run under 5.0 isolation mode?
| --
| LehrSJ
|

IIF X AND Y , why does Y get evaluated?

I have some logic in a report that is not working as I thought it should and
it puzzles me.
Lets says I have this line, (ignoring syntax errors, I forget what is
supposed to be there for days)
IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
Now because in this case ProductType is 5, SomeDate isn't populated, its
null if this language has that concept. But the error I get is telling me
that adding -8 to SomeDate produces something thats not a date. Well of
course thats true because when ProductType is 5 and SomeDate is nothing so
why is it even looking at it.
Is there a way around this?
thanksOn Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should and
> it puzzles me.
>
That's weird, I would not have expected IIF to evaluate both branches,
but maybe if you first check SomeDate to see if it is null, then you
can control when DateAdd is run, like so:
IIF(Fields!ProductType.Value = 5,"Hello there", IIF
IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
HTH
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks|||On Apr 14, 4:59 am, "Jerry H." <boilersr...@.gmail.com> wrote:
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:> I have some logic in a report that is not working as I thought it should and
> > it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
>
I dont know if it resolves your issue or not . as per my experience,in
iif statement, then clause and else clause should have the same
datatype values,in the following case.one is date type and another is
string type .please correct this also
Thanks
Raj deep.A
>
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its
> > null if this language has that concept. But the error I get is telling me
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks|||I've seen today a coworker using IIF to avoid a division by zero error and
it seemed to works fine..
What is the error you get ? I suspect a problem with DateAdd (are you sure
"day" shouldn't be within quotes ?)
--
Patrice
"Coaster" <Coaster@.Coaster.net> a écrit dans le message de news:
ObHp0uNnIHA.1052@.TK2MSFTNGP05.phx.gbl...
>I have some logic in a report that is not working as I thought it should
>and it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
>|||On Apr 12, 4:22=A0pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should a=nd
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8, SomeDa=te))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so=
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||On Apr 14, 12:46=A0pm, "Patrice" <http://www.chez.com/scribe/> wrote:
> I've seen today a coworker using IIF to avoid a division by zero error and=
> it seemed to works fine..
> What is the error you get ? I suspect a problem with DateAdd (are you sure=
> "day" shouldn't be within quotes ?)
> --
> Patrice
> "Coaster" <Coas...@.Coaster.net> a =E9crit dans le message de news:
> ObHp0uNnIHA.1...@.TK2MSFTNGP05.phx.gbl...
>
> >I have some logic in a report that is not working as I thought it should
> >and it puzzles me.
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8,
> > SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its=
> > null if this language has that concept. But the error I get is telling m=e
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing =so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks- Hide quoted text -
> - Show quoted text -
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
I usually use custom code to get around the IIF issue. You could try
something like the following.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by
zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||"toolman" <timd@.infocision.com> wrote in message
news:f3db3d38-59d5-402d-9b4b-e2b59c64f563@.u69g2000hse.googlegroups.com...
On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should
> and
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value = 5,"Hello there", IIF(SomeDate =Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 = 5 Then
DateMinus8 = "Hello There"
Else DateMinus8 = DateAdd("d", -8, SomeDate)
End If
End Function
Then use =code.DateMinus8(Fields!ProductType.Value )
instead of = IIF(Fields!ProductType.Value = 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(Numerator,Denominator)
instead of =IIF(Denominator = 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=IIf(Denominator = 0, "N/A", Numerator / IIf(Denominator = 0, 1,
Denominator))
Thanks alot !!! I 'll check it out tomorrow at work. I didn't even know you
could have functions like that in the report. JerryH's solution didn't work
for me because it still evaluated the date even though it was nested,
hopefully this won't happen using a function.|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:8bd61d4f-5c11-434e-931b-0615c09fd011@.59g2000hsb.googlegroups.com...
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
>> I have some logic in a report that is not working as I thought it should
>> and
>> it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
> HTH
>
>
>> Lets says I have this line, (ignoring syntax errors, I forget what is
>> supposed to be there for days)
>> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
>> SomeDate))
>> Now because in this case ProductType is 5, SomeDate isn't populated, its
>> null if this language has that concept. But the error I get is telling me
>> that adding -8 to SomeDate produces something thats not a date. Well of
>> course thats true because when ProductType is 5 and SomeDate is nothing
>> so
>> why is it even looking at it.
>> Is there a way around this?
>> thanks
>
yeah it is weird and it even evaluated it in your solution. Perhaps the
toolmans solution will work. Find out tomorrow.|||Humm... I gave this a try on another report i'm working on :
=IIf(True,1,0/0)
and it worked fine. If I change True to False I then have a "non numerical
value" string shown in the field...
I'm using RS 2005...

IIF Statements in Reporting Services

Afternoon All,
I'm working on a report and I'm trying to include an IIF Statement
since there's a possiblity that I could get a division by zero error.
Here is my calculation:
=IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
Also tried:
=IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
This seems to work if the calculation is not as complex but doesn't
like this one at all.
I would appreciate any suggestions.
Thanks,
JodyFor complicated expressions I would do such calculations in custom code.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
> Afternoon All,
> I'm working on a report and I'm trying to include an IIF Statement
> since there's a possiblity that I could get a division by zero error.
> Here is my calculation:
> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> Also tried:
> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> This seems to work if the calculation is not as complex but doesn't
> like this one at all.
> I would appreciate any suggestions.
> Thanks,
> Jody
>|||Reporting services equates both sides of an if before it execute it there
for
=IIF( 1=0,0,10/0)
will give an error therefore the only solution I have found is to use custom
code as suggested by the previous poster.
Thanks
Dale
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:O7Rw3sWHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> For complicated expressions I would do such calculations in custom code.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
> news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
>> Afternoon All,
>> I'm working on a report and I'm trying to include an IIF Statement
>> since there's a possiblity that I could get a division by zero error.
>> Here is my calculation:
>> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> Also tried:
>> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> This seems to work if the calculation is not as complex but doesn't
>> like this one at all.
>> I would appreciate any suggestions.
>> Thanks,
>> Jody
>|||Thanks for the help... I created a custom function that fixed my
issues... Here is my code in case it can help someone else down the
road.
Public Function ConvertSecToTime(ByVal NumSec As Double, ByVal Calls As
Double) As String
Dim theTime As String
If NumSec = 0 Or Calls = 0 Then
theTime = "0:00:00"
Else
theTime = (NumSec \ Calls) \ 3600 & Format(((NumSec \ Calls) \ 60) Mod
60, "\:00") & Format((NumSec \ Calls) Mod 60, "\:00")
End If

IIF Statements

On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
>
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
>
> Thanks in advance,
> RS
That IIF() call is pretty complex - is it possible for you to put some
of this logic in the database layer (e.g. by calling a view)?Thank you for your reply. I actually removed the variable "Address" and the
OR from the IIF statement and it is working fine now. Looks like IIF doesn't
work well with variables and those logical operators.
"Tokes" wrote:
> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS
> That IIF() call is pretty complex - is it possible for you to put some
> of this logic in the database layer (e.g. by calling a view)?
>|||IIF() works well "with logical operators and variables", FWIW.
So, here's a guess about why it didn't work, without reading your expression
very closely:
Assuming there was no actual error on your part, it's possible that Crystal
Reports interpreted the segments of your expression in a different order
than RS is doing. (Different compilers are like that <g>.)
To resolve this you can usually add some nested parentheses to make sure
that the order of evaluation is exactly what you expect, explicitly defined,
even though you got this order by default in your old environment.
However... a piece of advice: if you find yourself writing something like
this you may find it worth your while to write a little VB custom function
instead (embed it in the report) and then invoke the function
(=Code.MyFunc()) rather than writing the expression correctly. It's a lot
easier to read and maintain.
Also, you asked a second question about NameFlip... Does this flip two
values based on the appearance of a comma or something? I'm just guessing by
the name, but if so, something like this should work for you:
Function NameFlip(ByVal LastFirst As String) As String
Dim Result As String, Results As String()
Results = LastFirst.Split(",")
If Results.Length = 2 Then
Result = Results(1).Trim() & " " & Results(0).Trim()
Else
' don't make any assumptions if there are
' no commas or more than one comma
Result = LastFirst
End If
Results = Nothing
Return Result
End Function
If I guessed wrong, ask again, and I'll try to write something appropriate
<s>.
Hope this helps,
>L<
"RSub" <RSub@.discussions.microsoft.com> wrote in message
news:EDEAAEF8-8CD1-4518-9AE3-56FE9E714C50@.microsoft.com...
> Thank you for your reply. I actually removed the variable "Address" and
> the
> OR from the IIF statement and it is working fine now. Looks like IIF
> doesn't
> work well with variables and those logical operators.
> "Tokes" wrote:
>> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
>> > Hi All,
>> > The below IIF statement is not working for me.
>> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
>> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " "
>> > &
>> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value &
>> > " " &
>> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
>> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
>> > Fields!State_2.Value
>> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value),
>> > Trim("Address")
>> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United
>> > States",
>> > "Address")
>> >
>> > Could you please let me know where I am going wrong. I tried several
>> > other
>> > options such as writing custom code, switch, choose statements..I am
>> > migrating the report from crystal reports to Reporting services.
>> > Instead of
>> > the format in the above expression it was AddressLine1 in Crystal
>> > reports
>> > that had worked fine. Also does anybody know of an alternative for the
>> > NameFlip function of crystal rpts to use in Reporting svcs'
>> >
>> > Thanks in advance,
>> > RS
>> That IIF() call is pretty complex - is it possible for you to put some
>> of this logic in the database layer (e.g. by calling a view)?
>>

IIF Statements

Hi All,
The below IIF statement is not working for me.
=IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
"Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
& " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
<> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
"Address")
Could you please let me know where I am going wrong. I tried several other
options such as writing custom code, switch, choose statements..I am
migrating the report from crystal reports to Reporting services. Instead of
the format in the above expression it was AddressLine1 in Crystal reports
that had worked fine. Also does anybody know of an alternative for the
NameFlip function of crystal rpts to use in Reporting svcs'
Thanks in advance,
RSAfter seeing the full syntax I think you have to use some more "iif's " in
between before "Address:" , if you can explain in plain language what exactly
you are trying to display. ie something like if the first conditions is true
then what and if false then what...
Amarnath
"RSub" wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS|||Hi Amarnath,
My report uses a SQL query which is very complex and it has joins from
several diff tables. I was trying to add a calculated field(embedded) to the
data source and I need that to display the address which is addressline1, 2,
city state, zip etc based on some criteria which is the first part of the IIF
statement. I removed the variable Address and the OR and it is working fine.
The latter false part of the statement needs to remove US if it finds it in
the address and not display in the report. I'm trying to make that work now.
Thanks,
Roopa
"Amarnath" wrote:
> After seeing the full syntax I think you have to use some more "iif's " in
> between before "Address:" , if you can explain in plain language what exactly
> you are trying to display. ie something like if the first conditions is true
> then what and if false then what...
> Amarnath
>
> "RSub" wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS|||ok, so infact you can nest the iif as well, to get the desired results.
Amarnath
"RSub" wrote:
> Hi Amarnath,
> My report uses a SQL query which is very complex and it has joins from
> several diff tables. I was trying to add a calculated field(embedded) to the
> data source and I need that to display the address which is addressline1, 2,
> city state, zip etc based on some criteria which is the first part of the IIF
> statement. I removed the variable Address and the OR and it is working fine.
> The latter false part of the statement needs to remove US if it finds it in
> the address and not display in the report. I'm trying to make that work now.
> Thanks,
> Roopa
>
> "Amarnath" wrote:
> > After seeing the full syntax I think you have to use some more "iif's " in
> > between before "Address:" , if you can explain in plain language what exactly
> > you are trying to display. ie something like if the first conditions is true
> > then what and if false then what...
> >
> > Amarnath
> >
> >
> > "RSub" wrote:
> >
> > > Hi All,
> > > The below IIF statement is not working for me.
> > > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > > "Address")
> > >
> > > Could you please let me know where I am going wrong. I tried several other
> > > options such as writing custom code, switch, choose statements..I am
> > > migrating the report from crystal reports to Reporting services. Instead of
> > > the format in the above expression it was AddressLine1 in Crystal reports
> > > that had worked fine. Also does anybody know of an alternative for the
> > > NameFlip function of crystal rpts to use in Reporting svcs'
> > >
> > > Thanks in advance,
> > > RS|||Your first problem is that the IIf currently contains four parameters:
1: Trim(Fields!BillType.Value) = "IN" AND (Fields!User9.Value)= 1
2: "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value &
" " & Fields!City.Value & " " & Fields!State.Value & " " & Fields!
Zip.Value & " " & Fields!Country.Value) OR Format(Fields!Addr1_2.Value
& " " & Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
Fields!State_2.Value & " " & Fields!Zip_2.Value & " " & Fields!
Country_2.Value)
3: Trim("Address") <> "US" and Trim("Address") <> "USA" and
Trim("Address") <> "United States"
4: "Address"
Second, parameter 2 is altogether meaningless for several reasons:
* "Address:" = Format(... is testing if the result of your format
statement matches the string "Address:", which it almost certainly
won't.
* Format() takes two parameters, the object and the format type, and
you only pass one parameter each time.
* Format() is generally used to convert numbers, dates, etc to a
string: for example, Format(1.5, "C") returns $1.50 in the US. You
probably don't even need it for the addresses you're putting together.
* OR operates on two boolean values. Though you have one boolean value
from the "Address:" = Format(... comparison (by accident, I suspect),
I don't see what you're hoping to accomplish with the statement.
And third, parameter 3 will always return true - Trim("Address") will
always return "Address" which will never match the variations on "US".
On Apr 23, 5:42 pm, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS

IIF Statement Not Working

I've added the following IIF statement to a table in one of my reports, and
the second IIF statement does not ever evaluate to True. I don't see anything
wrong with the statement. Can anyone help?
=IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))Are you sure that DisplayOrder and Successful are numeric values? Did you
try this:
=IIF( CInt(Fields!DisplayOrder.Value) = 0, Fields!Total.Value, IIF(
CInt(Fields!Successful.Value) = -1, "Successful", Fields!Successful.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Collins" <Mike Collins@.discussions.microsoft.com> wrote in message
news:CC480CA6-B5A7-45DD-BD2E-40F706CA654C@.microsoft.com...
> I've added the following IIF statement to a table in one of my reports,
and
> the second IIF statement does not ever evaluate to True. I don't see
anything
> wrong with the statement. Can anyone help?
> =IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
> IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))|||I've confirmed that DisplayOrder and Successful are defined as integer. I
also tried your suggestion with the same results.
"Robert Bruckner [MSFT]" wrote:
> Are you sure that DisplayOrder and Successful are numeric values? Did you
> try this:
> =IIF( CInt(Fields!DisplayOrder.Value) = 0, Fields!Total.Value, IIF(
> CInt(Fields!Successful.Value) = -1, "Successful", Fields!Successful.Value))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mike Collins" <Mike Collins@.discussions.microsoft.com> wrote in message
> news:CC480CA6-B5A7-45DD-BD2E-40F706CA654C@.microsoft.com...
> > I've added the following IIF statement to a table in one of my reports,
> and
> > the second IIF statement does not ever evaluate to True. I don't see
> anything
> > wrong with the statement. Can anyone help?
> >
> > =IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
> > IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))
>
>

IIF and empty fields

Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty

I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..

The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?

I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="

Please help because its really confusing me, ThanksHi,

IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.

What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?

If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.

But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.

I suspect that you'll find what you need in these T-SQL features.

Don|||ok, thanks a lot. explains why its not working anyway :)

ignoring keys while Truncating tables

Hello everyone.

I'm working with a customers business application which is developed in MSSQL. The system has over 170 tables and there is no documentation by those who created it. Now the problem is that I have to write script that deletes all the data in all the tables but since there are foreign keys defined in the tables I can't delete the data. Ofcourse I can figure it out eventually by testing back and forth in which order I have to delete the data in the tables but since there are over 170 tables that could take a very long time.

Does anyone now how I can solve this?? is there for examaple a way to make SQL server to ignore the foreign key lookup? What can I do?? is there any way which I can see in what order I should delete the data in the tables?

appritiate any help or comments.

Thanks.

\Homan1. script out your deletes and run the script 170 times, eventualy you will clear out all the tables.

2. use Enterprise Manager to generate a diagram of all the tables in the database. This would tell you the exact order you would need to follow to get everything deleted.

3. in Enterprise Manager and Query Analyzer you can look up the dpendencies of any object. In EM right click on any object, select all tasks, dependencies, in QA press "F8" to show the object browser, select and object drill down till you see dependencies.

4. use sp_depends on all 170 tables/view to figure out the parents and or child relationships.|||If you are going to have to reload the database you are going to want to know your foreign keys.|||For reference of cascade delete refer to SQL TEam (http://www.sqlteam.com/item.asp?ItemID=8595) link.

Refer to this Code (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=641&lngWId=5) and modify to accomplish the task.

HTH|||disable your fk checking before deleting data, and re-enable them after.