Create View error

I have one customer's server that when I try to create on particular
view I get the error:
Could not find database ID 104. Database may not be activated yet of may
be in transition.
All other views can be created with no error using the create view
command. And, in query analyzer, the view itself runs with no issues.
Only when I put:
CREATE VIEW dbo.rSOPacking
AS
SELECT ...
That is when it errors out. I have tried not using dbo., using sa., and
another admin user, inxsql. All have the same error.
Help. Any ideas. View is below:
CREATE VIEW rSOPacking
AS
SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
h.ohdr_comments ELSE ' ' END hdr_comments,
CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
dtl_comments, t.ccod_description AS terms_desc,
s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
f.ccod_description AS frght_desc,
h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
ship3,
c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
(i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
+ LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
ordership, fob.ccod_description AS fob_desc, CASE WHEN
p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
d.odtl_orderqty-p.opck_packqty END bo_qty,
CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
% CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
obox_batch=oinv_batch),0) AS shipped_wgt
FROM SOOrderHeader AS h
INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
d.odtl_line=p.opck_line
LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
p.opck_batch=si.oinv_batch
LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
d.odtl_item=l.iloc_item
LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
h.ohdr_order=ser.iser_soorder AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
d.odtl_item=ci.icod_item
LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
Darin
*** Sent via Developersdex http://www.codecomments.com ***Try creating the sp without specifying a schema/owner. If that works, you ca
n
use sp_changeobjectowner to assign a new owner.
I think you are getting a messge saying that the actual userid/login id does
not exist.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Darin" wrote:

> I have one customer's server that when I try to create on particular
> view I get the error:
> Could not find database ID 104. Database may not be activated yet of may
> be in transition.
> All other views can be created with no error using the create view
> command. And, in query analyzer, the view itself runs with no issues.
> Only when I put:
> CREATE VIEW dbo.rSOPacking
> AS
> SELECT ...
> That is when it errors out. I have tried not using dbo., using sa., and
> another admin user, inxsql. All have the same error.
> Help. Any ideas. View is below:
> CREATE VIEW rSOPacking
> AS
> SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
> h.ohdr_comments ELSE ' ' END hdr_comments,
> CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
> dtl_comments, t.ccod_description AS terms_desc,
> s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
> f.ccod_description AS frght_desc,
> h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
> ship3,
> c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
> (i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
> CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
> 'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
> WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
> ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
> WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
> iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
> ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
> opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
> + LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
> ordership, fob.ccod_description AS fob_desc, CASE WHEN
> p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
> iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
> odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
> d.odtl_orderqty-p.opck_packqty END bo_qty,
> CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
> ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
> ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
> lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
> ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
> char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
> AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
> CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
> % CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
> it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
> b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
> bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
> ' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
> dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
> CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
> xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
> SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
> obox_batch=oinv_batch),0) AS shipped_wgt
> FROM SOOrderHeader AS h
> INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
> LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
> d.odtl_line=p.opck_line
> LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
> p.opck_batch=si.oinv_batch
> LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
> d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
> LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
> LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
> d.odtl_item=l.iloc_item
> LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
> LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
> LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
> LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
> LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
> LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
> h.ohdr_order=ser.iser_soorder AND
> dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
> LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
> LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
> LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
> LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
> LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
> LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
> d.odtl_item=ci.icod_item
> LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
>
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>|||I bet you that if you remove this line
AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
the problem will go away
I used to have the same problem with UDF and subqueries a while back
just comment it out for fun
http://sqlservercode.blogspot.com/|||By that I assume you mean don't put dbo. before the veiw name, I tried
that and still have the same error message.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||what happens if you just run the select:
SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
h.ohdr_comments ELSE ' ' END hdr_comments,
CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
dtl_comments, t.ccod_description AS terms_desc,
...
and (1=0)|||It worked fine if I just did the select statement, it returned all of
the rows.
Interestingly enough, I removed one left join ICSerial and the
corresponding area in the select statement and the create view worked
fine. I also noticed this customer isn't on SP4 of SQL Server 2000.
Does that info help anyone in figuring out exactly why the error?
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||I think it has something to do with the nestlevel and number of joins
combined with the UDF
I used to have the same problem however when I moved the UDF out of the
query it was fine
And it worked on QA but not on production (same setup)
Maybe dependencies have something to do with it also
http://sqlservercode.blogspot.com/|||Once I upgraded the server to SQL Server 2000 SP4 it worked fine. But
that sucks because we have customers that aren't up on SP4, so I have to
figure out how to do that.
Darin
*** Sent via Developersdex http://www.codecomments.com ***