Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts

Sunday, March 11, 2012

Cannot view databases in view list for SQL 2005

I have created a couple of new logons/ users and they are dbo's of a few
databases. However it has become apparent that for some reason the databases
are not "browseable" through the SSMS dropdown. Even running sp_databases
does not return their names. The databases can be accessed through text
(e.g. USE dbname) and do look like full access is available.
What on earth is going on? The only difference between this and another
(working) box is that this non-working one is 64 bit windows with 64bit SQL
(x64) wheras the other is 32 bit on 32 bit. I know this because they both
use automated builds which I created.
Regards,
MarkHi Mark
"Mark Broadbent" wrote:

> I have created a couple of new logons/ users and they are dbo's of a few
> databases. However it has become apparent that for some reason the databas
es
> are not "browseable" through the SSMS dropdown. Even running sp_databases
> does not return their names. The databases can be accessed through text
> (e.g. USE dbname) and do look like full access is available.
> What on earth is going on? The only difference between this and another
> (working) box is that this non-working one is 64 bit windows with 64bit SQ
L
> (x64) wheras the other is 32 bit on 32 bit. I know this because they both
> use automated builds which I created.
> Regards,
> Mark
I assume by dropdown you mean the Object Browser (F8).
Have you changed the permissions for these users at all since they created
the databases?
From BOL:
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY
DEFINITION permission, and must have access permission to the database.
Cannot be denied VIEW ANY DEFINITION permission.
John

Cannot view databases in view list for SQL 2005

I have created a couple of new logons/ users and they are dbo's of a few
databases. However it has become apparent that for some reason the databases
are not "browseable" through the SSMS dropdown. Even running sp_databases
does not return their names. The databases can be accessed through text
(e.g. USE dbname) and do look like full access is available.
What on earth is going on? The only difference between this and another
(working) box is that this non-working one is 64 bit windows with 64bit SQL
(x64) wheras the other is 32 bit on 32 bit. I know this because they both
use automated builds which I created.
Regards,
MarkHi Mark
"Mark Broadbent" wrote:
> I have created a couple of new logons/ users and they are dbo's of a few
> databases. However it has become apparent that for some reason the databases
> are not "browseable" through the SSMS dropdown. Even running sp_databases
> does not return their names. The databases can be accessed through text
> (e.g. USE dbname) and do look like full access is available.
> What on earth is going on? The only difference between this and another
> (working) box is that this non-working one is 64 bit windows with 64bit SQL
> (x64) wheras the other is 32 bit on 32 bit. I know this because they both
> use automated builds which I created.
> Regards,
> Mark
I assume by dropdown you mean the Object Browser (F8).
Have you changed the permissions for these users at all since they created
the databases?
From BOL:
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY
DEFINITION permission, and must have access permission to the database.
Cannot be denied VIEW ANY DEFINITION permission.
John

Thursday, March 8, 2012

Cannot update

Hi,
I have the following update statement but using a linked server
Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a inner
join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType
I am not sure why it doesn't work. Can I not use Linked server in an Update
Statement?
Thanks
Ed
The following is an error message:
Server: Msg 7306, Level 16, State 2, Line 2
Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...What product are you linking to?
ML|||Ed,
Does the LS table have a PK or UNIQUE constraint? If not, might try adding
one and then trying again.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
> Hi,
> I have the following update statement but using a linked server
> Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a
> inner
> join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
> a.ChargSubType = b.ChargSubType
> I am not sure why it doesn't work. Can I not use Linked server in an
> Update
> Statement?
> Thanks
> Ed
> The following is an error message:
> Server: Msg 7306, Level 16, State 2, Line 2
> Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
> generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
>|||Try this
Update txq005dev1.sprint.dbo.becinfo set unit = (SELECT unit from becinfo a
inner join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType)
if you are sure that inner query results only one value
--
Regards
R.D
--Knowledge gets doubled when shared
"Jerry Spivey" wrote:

> Ed,
> Does the LS table have a PK or UNIQUE constraint? If not, might try addin
g
> one and then trying again.
> HTH
> Jerry
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
>
>

Wednesday, March 7, 2012

Cannot transfer schemabound object - error

I am trying to alter the AdventureWorks database to transfer ownership from one schema to another...

Alter schema dbo transfer Person.CountryRegion

I get the following error..

"Cannot transfer a schemabound object."

Any help would be appreciated.Person.CountryRegion has foreign key references and an indexed view referencing it. So you need to resolve those before trying to transfer ownership. In this case, it looks like you have to drop the index on the view, perform the transfer and create the index back.

Friday, February 10, 2012

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
>

Cannot replicate non dbo table when dbo table exists

I attempt to replicate a non dbo table, e.g. user1.table1. In the mean time
dbo.table1 exists in both publisher and subscriber and is replicated. When
the replication ran, it drop the dbo.table1 in subscriber and created new
dbo.table1 from user1.table1. How do I replicate non dbo tables when dbo
tables with same names exist?
Thanks!
right click on your publication, select properties, click on the articles
tab, click the browse button, in the general tab make sure that the
destination owner name is the name of the owner you want on the subscriber.
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
"Charts" <Acharts@.newsgroup.nospam> wrote in message
news:FDF72545-7875-491E-B51F-9CECEE79FB4C@.microsoft.com...
>I attempt to replicate a non dbo table, e.g. user1.table1. In the mean
>time
> dbo.table1 exists in both publisher and subscriber and is replicated. When
> the replication ran, it drop the dbo.table1 in subscriber and created new
> dbo.table1 from user1.table1. How do I replicate non dbo tables when dbo
> tables with same names exist?
> Thanks!
>
|||Thanks so much and I'll follow up.
"Charts" wrote:

> I attempt to replicate a non dbo table, e.g. user1.table1. In the mean time
> dbo.table1 exists in both publisher and subscriber and is replicated. When
> the replication ran, it drop the dbo.table1 in subscriber and created new
> dbo.table1 from user1.table1. How do I replicate non dbo tables when dbo
> tables with same names exist?
> Thanks!
>