Can't apply transaction logs after restoring a database in enterprise manager

Hi!
I created Full Backup of database and transaction logs in Enterprise manager
.. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.
Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.

Cant allocate space for object

Hi!
I'm running mssql 6.5 and I've got a problem with a pretty basic errormessage. I've managed this scenario before, but I't doesnt seem to work this time...
The message is:

Can't allocate space for object '-482' in database 'tempdb' because the 'system' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment. (Message

What I've done so far is:
dump transaction tempdb with truncate_only
Dump master ...
I've expanded the tempdb device (alter database)

Now I'm starting to run out of ideas...
What's this '-482' (I've also got an error on -386 once)
Anybody have any idea what to do next?

Thanks!!That is the ObjectId of the table in the database.

Can't access SqlServer 2005 using JDBC

Hi;

I can access SqlServer 2000 fine, but not SqlServer 2005. My client is on Windows XP and SqlServer 2005 (dev version) is on Windows 2003. When I run I get:

Error: net.windward.datasource.DataSourceException: JdbcDataSource could not ope
n jdbc:sqlserver://T1-Databases;databaseName=AdventureWorks;integratedSecurity=t
rue;
net.windward.datasource.DataSourceException: net.windward.datasource.DataSourceE
xception: JdbcDataSource could not open jdbc:sqlserver://T1-Databases;databaseNa
me=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more
Exception in thread "main" net.windward.datasource.DataSourceException: net.wind
ward.datasource.DataSourceException: JdbcDataSource could not open jdbc:sqlserve
r://T1-Databases;databaseName=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more

Any ideas?

thanks - dave

Are you using Microsoft SQL Server 2005 JDBC Driver version 1.1?

What platform (OS and version) does your JDBC client run on?

|||

I have similar error:

java.lang.NoClassDefFoundError
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

I do have the JDBC driver version 1.1.

Cocoon 2.0

Tomcat 4.x

|||

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true

It appears as though it has something to do with integratedSecurity. I think the connection string is case sensitive, you did spell it IntegratedSecurity="True" right? The exception thrown seems to be targetted to integratedSecurity specifically. Also, are you authenticating in mixed mode or windows only? Did you configure the server on install correctly to be able to authenticate the kind of connection you are trying to make?

Hope we can figure this one out together,

John

|||

Have you tried enabling the TCP/IP Protocol through the SQL Server Configuration Manager? I seem to recall it being turned off by default in SQL 2005. Enable it and restart the SQL Service.

Good luck,

-Tom

|||You can run SQL Profiler to check if your application is indeed trying to establish a connection or not.|||It looks like you are using the 1.0 dirver which did not support integrated auth property.

cannot write to sql 2000 database after about 2 hours uptime

Hi
I have a sql 2000 sp3 database running on SBS 2003 that seems to
ignore any writes after about 2 hours of it being up. It works fine after i
reboot the server or restart the sql service, but after a couple hours it
will not edit the database again. If i restart the service, it works fine
again. Also, i was looking at the sql logs and they are odd. it says
'starting up database {my database name}' about 20 times in a row when the
database is started, then says 'Configuration option 'show advanced options'
changed from 1 to 1. Run the RECONFIGURE statement to install' and then
gives an error 15457. Please help me fix my database.
Thanks in advance.
Leszek
Hi
Error 15457 is just an informational one. Ig gets generated the Enterprise
Manager and has nothing to do with the Db issue.
If there are no other errors in you event log, it means that SQL is Ok and
your application is the problem.
You may want to make sure that your DB is not set as 'Auto Close' as this
affects performance.
Regards
Mike
"Leszek" wrote:

> Hi
>
> I have a sql 2000 sp3 database running on SBS 2003 that seems to
> ignore any writes after about 2 hours of it being up. It works fine after i
> reboot the server or restart the sql service, but after a couple hours it
> will not edit the database again. If i restart the service, it works fine
> again. Also, i was looking at the sql logs and they are odd. it says
> 'starting up database {my database name}' about 20 times in a row when the
> database is started, then says 'Configuration option 'show advanced options'
> changed from 1 to 1. Run the RECONFIGURE statement to install' and then
> gives an error 15457. Please help me fix my database.
> Thanks in advance.
> Leszek
>
>
|||Hi
Db is not set as 'Auto Close'. I have noticed that problem occured when is
more then 100 process in sql .
Regards
Leszek
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:4F13D7AF-1570-47AC-85B5-0515AD58D2AA@.microsoft.com...[vbcol=seagreen]
> Hi
> Error 15457 is just an informational one. Ig gets generated the Enterprise
> Manager and has nothing to do with the Db issue.
> If there are no other errors in you event log, it means that SQL is Ok and
> your application is the problem.
> You may want to make sure that your DB is not set as 'Auto Close' as this
> affects performance.
> Regards
> Mike
> "Leszek" wrote:
after i[vbcol=seagreen]
it[vbcol=seagreen]
fine[vbcol=seagreen]
the[vbcol=seagreen]
options'[vbcol=seagreen]
then[vbcol=seagreen]
|||The problem here is not going to be an easy one to fix.
There are a couple of things you can look at however to
get a bit closer to the proble.
Firstly have you run the performance monitor to see what
is happening to your server i.e. Disk, Memory and the like.
Secondly you may want to set up a trace to see what
commands are being sent to the server.
Finally another thing you can do is send me a copy of your
database script, I don't need the table definitions, sp,
views or functions, just the configuration options when
you create the database, for security purposes leave the
create database statement in, but change the db name. I
will have a quick look at it and see if I can spot
anything odd.
Finally what is your server specification i.e. ram, hard
disk space ect.
My email if you need it is
peter.nolan@.REMOVETHISpinnacle.co.uk, nb its my work one.
Peter
"The length of this document defends it well against the
risk of its being read."
Winston Churchill

>--Original Message--
>Hi
>Db is not set as 'Auto Close'. I have noticed that
problem occured when is
>more then 100 process in sql .
>Regards
>Leszek
>
>"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in
message[vbcol=seagreen]
>news:4F13D7AF-1570-47AC-85B5-0515AD58D2AA@.microsoft.com...
generated the Enterprise[vbcol=seagreen]
that SQL is Ok and[vbcol=seagreen]
as 'Auto Close' as this[vbcol=seagreen]
2003 that seems to[vbcol=seagreen]
It works fine[vbcol=seagreen]
>after i
after a couple hours[vbcol=seagreen]
>it
service, it works[vbcol=seagreen]
>fine
are odd. it says[vbcol=seagreen]
times in a row when[vbcol=seagreen]
>the
option 'show advanced[vbcol=seagreen]
>options'
to install' and
>then
>
>.
>
|||Aplication works since few monts on other server and there everything is
fine.
Regarding performance monitor. When system works fine and i try to open site
with many difficult query , procesor usage show about 50 % and it take 10
sec - site is loaded. If it doesn't work procesor usage is always about 1%
but i can't load the site.
Sql Profile show that query is sent to server.
Leszek
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:158001c4a221$b242cfc0$a301280a@.phx.gbl...[vbcol=seagreen]
> The problem here is not going to be an easy one to fix.
> There are a couple of things you can look at however to
> get a bit closer to the proble.
> Firstly have you run the performance monitor to see what
> is happening to your server i.e. Disk, Memory and the like.
> Secondly you may want to set up a trace to see what
> commands are being sent to the server.
> Finally another thing you can do is send me a copy of your
> database script, I don't need the table definitions, sp,
> views or functions, just the configuration options when
> you create the database, for security purposes leave the
> create database statement in, but change the db name. I
> will have a quick look at it and see if I can spot
> anything odd.
> Finally what is your server specification i.e. ram, hard
> disk space ect.
> My email if you need it is
> peter.nolan@.REMOVETHISpinnacle.co.uk, nb its my work one.
> Peter
> "The length of this document defends it well against the
> risk of its being read."
> Winston Churchill
>
> problem occured when is
> message
> generated the Enterprise
> that SQL is Ok and
> as 'Auto Close' as this
> 2003 that seems to
> It works fine
> after a couple hours
> service, it works
> are odd. it says
> times in a row when
> option 'show advanced
> to install' and
|||Hi Leszek,
Have you used the enterprise manager to see if anything is locking a table
and preventing the updates? Perhaps a certain function or piece of code is
being executed after about 2 hours and that may look like the database won't
accept updates.
Wayne
"Leszek" <aaa@.addressnotexists.com> wrote in message
news:Odc7a3goEHA.2380@.TK2MSFTNGP14.phx.gbl...
> Hi
>
> I have a sql 2000 sp3 database running on SBS 2003 that seems to
> ignore any writes after about 2 hours of it being up. It works fine after
> i
> reboot the server or restart the sql service, but after a couple hours it
> will not edit the database again. If i restart the service, it works fine
> again. Also, i was looking at the sql logs and they are odd. it says
> 'starting up database {my database name}' about 20 times in a row when the
> database is started, then says 'Configuration option 'show advanced
> options'
> changed from 1 to 1. Run the RECONFIGURE statement to install' and then
> gives an error 15457. Please help me fix my database.
> Thanks in advance.
> Leszek
>

Cannot uninstall SQL Server Express

Hi!

I uninstalled Visual Studio 2005, but then I get this error when trying to uninstall SQL Express:

"The SQL Server System Configuration check cannot be executed due to WMI configuration on the machine XXX: Error: 2147746132 (0x80040154)"

I don't even know what WMI is!

I have these installed (none of which can I uninstall):

Microsoft SQl Server 2005

Microsoft SQl Server Mobile [ENU] Developer Tools

Microsoft SQl Server Native Client

Microsoft SQl Server Setup Support Files (English)

Microsoft SQl Server VSS Writer

Help!

It sounds like WMI is corrupted on your machine. First, make sure you still have .Net Framework 2.0 installed. If this is the case you should search http://support.microsoft.com for WMI errors. There are a few articles out there that discuss how to recover from a corrupted WMI state.|||

After much searching, I found the way to repair WMI here:

"Re-registering WMI"

http://blogs.msdn.com/smguest/archive/2005/03/18/398883.aspx

There are many potential fixes out there, but this one worked for me (fingers crossed).

Then, I had to download Windows Installer 3.1 from:

http://www.microsoft.com/downloads/Search.aspx?displaylang=en

Jess

Cannot start Microsoft SQL Server agent & Enterprise manager

Hi:
I am receiving the following error message while trying to open Enterprise
Manager in MSSQL 2000 Server.
A connection could not be established to ABCD
Reason : I/O error 1117(the request could not performed because of an I/O
device error.) detected during read at offset 0x00000000b1e000 in file
'Drive:\Microsoft SQL Server\MSSQL\data\master.mdf'..
Please verify SQL Server is running and check your SQL Server registration
properties (by right cliking on the ABCD node) and try again.
This is a Windows 2000 server (Service Pack 4) + MSSQL 2000 (service Pack
3). The MSSQL server service SQL Server Agent services are running under
local system. This is a Standalone machine.
Help is required on an urgent basis as this is a production server.
With Regards
Mitul
You have a problem with the mdf file for your master database file. Possibly the file is locked by
some other process (like anti-virus). If that is the case, try again. If you are unlucky, the master
mdf file is corrupt for some reason, and you have to go the rebuildm.exe route.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mitul" <Mitul@.discussions.microsoft.com> wrote in message
news:46466B5B-D821-4C07-BD8C-8996EBAD7915@.microsoft.com...
> Hi:
> I am receiving the following error message while trying to open Enterprise
> Manager in MSSQL 2000 Server.
>
> A connection could not be established to ABCD
> Reason : I/O error 1117(the request could not performed because of an I/O
> device error.) detected during read at offset 0x00000000b1e000 in file
> 'Drive:\Microsoft SQL Server\MSSQL\data\master.mdf'..
> Please verify SQL Server is running and check your SQL Server registration
> properties (by right cliking on the ABCD node) and try again.
>
> This is a Windows 2000 server (Service Pack 4) + MSSQL 2000 (service Pack
> 3). The MSSQL server service SQL Server Agent services are running under
> local system. This is a Standalone machine.
> Help is required on an urgent basis as this is a production server.
> With Regards
> Mitul

Cannot See SQL Server

Hi
i have Windows 2000 with SP 4 and SQL Server 7 installed on it
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation
Desktop Edition Evaluation on Windows NT 5.0 (Build 2195: Service Pack 4)
i am using an API to get all these server info but i cannot get this server
in my list i cannot see this server even in Query Analyser list, however if
i manually write machine's name its gets connected and i can perform all fun
ction
The API i am using is
NetServerEnum
Any one have any ideaHi
Found this!
http://tinyurl.com/36a8w
and
http://tinyurl.com/37mde
HTH
John
"Imran Ali" <Imran Ali@.discussions.microsoft.com> wrote in message
news:F90248A0-EB29-4986-B529-E011FA29A29C@.microsoft.com...
> Hi
> i have Windows 2000 with SP 4 and SQL Server 7 installed on it
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation
> Desktop Edition Evaluation on Windows NT 5.0 (Build 2195: Service Pack 4)
> i am using an API to get all these server info but i cannot get this
server in my list i cannot see this server even in Query Analyser list,
however if i manually write machine's name its gets connected and i can
perform all function
> The API i am using is
> NetServerEnum
> Any one have any idea

Cannot See SQL Server

Hi
i have Windows 2000 with SP 4 and SQL Server 7 installed on it
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation
Desktop Edition Evaluation on Windows NT 5.0 (Build 2195: Service Pack 4)
i am using an API to get all these server info but i cannot get this server in my list i cannot see this server even in Query Analyser list, however if i manually write machine's name its gets connected and i can perform all function
The API i am using is
NetServerEnum
Any one have any idea
Hi
Found this!
http://tinyurl.com/36a8w
and
http://tinyurl.com/37mde
HTH
John
"Imran Ali" <Imran Ali@.discussions.microsoft.com> wrote in message
news:F90248A0-EB29-4986-B529-E011FA29A29C@.microsoft.com...
> Hi
> i have Windows 2000 with SP 4 and SQL Server 7 installed on it
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
> Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation
> Desktop Edition Evaluation on Windows NT 5.0 (Build 2195: Service Pack 4)
> i am using an API to get all these server info but i cannot get this
server in my list i cannot see this server even in Query Analyser list,
however if i manually write machine's name its gets connected and i can
perform all function
> The API i am using is
> NetServerEnum
> Any one have any idea

Cannot resolve the collation conflict between

Hi
I have a new DB, in this new DB I have import and copy some data from the
old DB, among others, a report
When i try to open the report, I get this message
- Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
Any ideas.
ThanksHi
Your report's query contains JOIN on varchar/nvarchar columns which have
different collation
Try
select * from table join anothertable on table.col =anothertable.column
COLLATE danish_norwegian_ci_as
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>|||Thank you
It help, in my plain sql script
But I stil have the problem, because I not realy sure where to put it in my
CrystalReport. The report is predefined.
...? :|
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
> Hi
> Your report's query contains JOIN on varchar/nvarchar columns which have
> different collation
> Try
> select * from table join anothertable on table.col =anothertable.column
> COLLATE danish_norwegian_ci_as
>
>
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
>|||Hi
I have no idea. I don't know Crystal Report
"H.Gjerde" <hg@.norspace.no> wrote in message
news:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl...
> Thank you
> It help, in my plain sql script
> But I stil have the problem, because I not realy sure where to put it in
> my CrystalReport. The report is predefined.
> ...? :|
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
>|||can you create SP and call it in Crystal Report
Regards
Amish shah
http://shahamishm.tripod.com
On Jan 24, 3:31 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> I have no idea. I don't know Crystal Report
> "H.Gjerde" <h...@.norspace.no> wrote in messagenews:eeyQ5F6PHHA.3344@.TK2MSF
TNGP02.phx.gbl...
>
>
>
>
>
>
>
>|||H.Gjerde wrote:
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>
Hi
The new database you have created, seems to be created with a different
collation than the old one. If you can't change the SQL code behind the
report, your best option might be to drop the new database, create a new
one with the correct collation and then import the data again.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||you might find that it is in fact the tempdb has a different collation
than the user database. You might have to rebuild the system databases
using "rebuildm".
On Jan 24, 11:30 am, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> H.Gjerde wrote:
>
he[vbcol=seagreen]
>
>
> The new database you have created, seems to be created with a different
> collation than the old one. If you can't change the SQL code behind the
> report, your best option might be to drop the new database, create a new
> one with the correct collation and then import the data again.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator

Cannot resolve collation conflict for UNION operation

Hi
I am getting the above error with my SP. Why would I get this error?
Many Thanks
Jaco
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
@.EventID varchar(10),
@.Config int
AS
SET NOCOUNT ON
--If we want to add the main event as a row in the sql below (to make it
have a 'from event' equivalent to QFM3),
--then only need to add the @.eventid to the select.
Declare @.parID int
Declare @.GeogID varchar(5)
declare @.sql nvarchar(4000)
set @.sql=' '
if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
--1. find all other details of asb found to the exact same geog (so if found
at site/locn/unit, then exactly this level), including current event.
begin
set @.sql="
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from geography
where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and e.geographyid in
(select geographyid from event where eventid = " + rtrim(@.EventID) + ")
-- select geographyid from event where eventid = 38590)
and c.uniqueid =* esd.materialareaunitid "
end
if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config = 10
or @.Config = 11 or @.Config = 14 or @.Config = 15
--2. if asb was found at at ie bridge court/2s/test, then include all for
bridge court/2s/anything, but not bridge court/2s/nothing
--ie if found at a room on a floor, report on other rooms on same floor
(with the same parent and level)
--Get all geogs for the same level (ie rooms on the same floor) as the geog
of a specific event.
begin
select @.parID = parentid from geography g, event e where g.geographyid =
e.geographyid and e.eventid = rtrim(@.EventID)
if @.parID is not null
begin
--If we are not at the highest level (site) then we can check for a parent
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.geographyid in
(select g2.geographyid
from geography g2, geography g
where g.geographyid in (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and g.parentid = g2.parentid
and g.level = g2.level) and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and c.uniqueid =* esd.materialareaunitid "
-- print 'first'
-- print 'Config=2'
end
else
begin
set @.sql = @.sql + " union
select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found'
as asbestoststaus, es.surveydate, esd.samplenumber,
esd.materialdescription, esda.description + '[' +
ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
ac.description as classification, esd.risk as hazard, esd.remarks,
g.geographyid, g.parentid, g.siteid, g.level, g.region,
ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
(select site + ' ' + location + ' ' + unit + ' ' + partition from
geography where geography.geographyid = es.geographyid) as surveygeog --gja
5.12.04
from geography g, event e, eventsurvey es, eventsurveydetail esd,
asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
where g.geographyid = e.geographyid --gja5.12.04
-- where g.geographyid = es.geographyid --gja5.12.04
and e.eventid = es.currenteventid
and es.eventsurveyid = esd.eventsurveyid
and ac.asbestosclassificationid = esd.classificationid
and esd.analysisid = esda.EventSurveyDetailAnalysisid
and g.parentid is null
and g.geographyid = (select geographyid from event where eventid = " +
rtrim(@.EventID) + ")
and c.uniqueid =* esd.materialareaunitid "
end
end
if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config = 12
or @.Config = 13 or @.Config = 14 or @.Config = 15
--3. if asb was found at at ie red lion court/3rd/, then include all for red
lion court/3rd/anything below.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
--Use the eventid to get the geography level
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
--print 'Config=3'
end
if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config =
12 or @.Config = 13 or @.Config = 14 or @.Config = 15
--4. as 3 only cant have anything below 2s (if unit or part then dont
include)
--ie if asb was found at at ie bridge court/2s/test, then include all geogs
at bridge court/2s/nothing.
--Do we not then include the current event? (as it is obviously not below
the level of geog for the current event)?
--JF says maybe, maybe not, so does.
begin
select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
set @.sql = @.sql + " union
select eventid ,
site ,
location ,
unit ,
partition ,
asbestoststaus ,
surveydate datetime,
samplenumber ,
materialdescription ,
analysis ,
classification ,
hazard ,
remarks ,
geographyid ,
parentid ,
siteid ,
level ,
region ,
extent
,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
AsbestosImagePath,
surveygeog --gja5.12.04
from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
end
if left(@.sql, 7) = ' union'
set @.sql = substring(@.sql, 8, len(@.sql) - 7)
set @.sql = @.sql + " order by 1"
exec sp_executesql @.sql
--end
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOJaco,
The UNION operator will scan the inputs to remove duplicates (comparing all
members of the select list). There should be columns, in the tables involved
,
with different collation. You can use COLLATE apply a collation cast to the
character expression. See this example:
Example:
use northwind
go
create table t1 (
colA varchar(25) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
colA varchar(25) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t1 values('Microsoft')
go
-- this will fail with the error mentioned
select colA from t1
union
select colA from t2
go
select colA from t1
union
select colA collate SQL_Latin1_General_CP1_CI_AS from t2
go
drop table t1, t2
go
AMB
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Hi
Without DDL for the tables it is hard to say where the conflict occurs, but
the way to debug it would be to use profile to get the final SQL statement
and then you can find out which part of the union is causing the conflict an
d
use the COLLATE option to force the collation.
John
"Jaco" wrote:

> Hi
> I am getting the above error with my SP. Why would I get this error?
> Many Thanks
> Jaco
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[qfm_RptgetAsbLevelGeog]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[qfm_RptgetAsbLevelGeog]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE [dbo].[qfm_RptgetAsbLevelGeog]
> @.EventID varchar(10),
> @.Config int
> AS
>
> SET NOCOUNT ON
> --If we want to add the main event as a row in the sql below (to make it
> have a 'from event' equivalent to QFM3),
> --then only need to add the @.eventid to the select.
> Declare @.parID int
> Declare @.GeogID varchar(5)
> declare @.sql nvarchar(4000)
> set @.sql=' '
> if @.Config = 0 or @.Config = 1 or @.Config = 3 or @.Config = 5 or @.Config = 7
> or @.Config = 9 or @.Config = 11 or @.Config = 13 or @.Config = 15
> --1. find all other details of asb found to the exact same geog (so if fou
nd
> at site/locn/unit, then exactly this level), including current event.
> begin
> set @.sql="
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Found
'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from geograp
hy
> where geography.geographyid = es.geographyid) as surveygeog --gja 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and e.geographyid in
> (select geographyid from event where eventid = " + rtrim(@.EventID) + ")
> -- select geographyid from event where eventid = 38590)
> and c.uniqueid =* esd.materialareaunitid "
>
> end
>
> if @.Config = 2 or @.Config = 3 or @.Config = 6 or @.Config = 7 or @.Config =
10
> or @.Config = 11 or @.Config = 14 or @.Config = 15
> --2. if asb was found at at ie bridge court/2s/test, then include all for
> bridge court/2s/anything, but not bridge court/2s/nothing
> --ie if found at a room on a floor, report on other rooms on same floor
> (with the same parent and level)
> --Get all geogs for the same level (ie rooms on the same floor) as the geo
g
> of a specific event.
> begin
> select @.parID = parentid from geography g, event e where g.geographyid =
> e.geographyid and e.eventid = rtrim(@.EventID)
> if @.parID is not null
> begin
> --If we are not at the highest level (site) then we can check for a paren
t
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.geographyid in
> (select g2.geographyid
> from geography g2, geography g
> where g.geographyid in (select geographyid from event where eventid = "
+
> rtrim(@.EventID) + ")
> and g.parentid = g2.parentid
> and g.level = g2.level) and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and c.uniqueid =* esd.materialareaunitid "
>
> -- print 'first'
> -- print 'Config=2'
> end
> else
> begin
> set @.sql = @.sql + " union
> select e.eventid, g.site, g.location, g.unit, g.partition, 'Asbestos Foun
d'
> as asbestoststaus, es.surveydate, esd.samplenumber,
> esd.materialdescription, esda.description + '[' +
> ltrim(str(EventSurveyDetailAnalysisid)) + ']' as analysis,
> ac.description as classification, esd.risk as hazard, esd.remarks,
> g.geographyid, g.parentid, g.siteid, g.level, g.region,
> ltrim(str(esd.materialareaamount)) + ' ' + c.name as extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail
> ,dbo.ConcatAsbestosPath(e.eventid) as AsbestosImagePath, --new
> (select site + ' ' + location + ' ' + unit + ' ' + partition from
> geography where geography.geographyid = es.geographyid) as surveygeog --gj
a
> 5.12.04
> from geography g, event e, eventsurvey es, eventsurveydetail esd,
> asbestosclassification ac, EventSurveyDetailAnalysis esda, commonlookup c
> where g.geographyid = e.geographyid --gja5.12.04
> -- where g.geographyid = es.geographyid --gja5.12.04
> and e.eventid = es.currenteventid
> and es.eventsurveyid = esd.eventsurveyid
> and ac.asbestosclassificationid = esd.classificationid
> and esd.analysisid = esda.EventSurveyDetailAnalysisid
> and g.parentid is null
> and g.geographyid = (select geographyid from event where eventid = " +
> rtrim(@.EventID) + ")
> and c.uniqueid =* esd.materialareaunitid "
>
> end
> end
>
> if @.Config = 4 or @.Config = 5 or @.Config = 6 or @.Config = 7 or @.Config =
12
> or @.Config = 13 or @.Config = 14 or @.Config = 15
> --3. if asb was found at at ie red lion court/3rd/, then include all for r
ed
> lion court/3rd/anything below.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> --Use the eventid to get the geography level
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
> from AsbLowestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> --print 'Config=3'
> end
>
> if @.Config = 8 or @.Config = 9 or @.Config = 10 or @.Config = 11 or @.Config
=
> 12 or @.Config = 13 or @.Config = 14 or @.Config = 15
> --4. as 3 only cant have anything below 2s (if unit or part then dont
> include)
> --ie if asb was found at at ie bridge court/2s/test, then include all geo
gs
> at bridge court/2s/nothing.
> --Do we not then include the current event? (as it is obviously not below
> the level of geog for the current event)?
> --JF says maybe, maybe not, so does.
> begin
> select @.GeogID = GeographyID from event where eventid = rtrim(@.EventID)
> set @.sql = @.sql + " union
> select eventid ,
> site ,
> location ,
> unit ,
> partition ,
> asbestoststaus ,
> surveydate datetime,
> samplenumber ,
> materialdescription ,
> analysis ,
> classification ,
> hazard ,
> remarks ,
> geographyid ,
> parentid ,
> siteid ,
> level ,
> region ,
> extent
> ,dbo.fn_ParamEntry('WS Notification Detail_1016') as asbdetail,
> AsbestosImagePath,
> surveygeog --gja5.12.04
>
> from AsbHighestLevel "+ "(" +rtrim( @.EventID) + "," + @.GeogID +") "
>
> end
>
> if left(@.sql, 7) = ' union'
> set @.sql = substring(@.sql, 8, len(@.sql) - 7)
> set @.sql = @.sql + " order by 1"
>
> exec sp_executesql @.sql
>
>
> --end
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>