Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.Use cliconfg.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.
Use cliconfg.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegrou ps.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>
|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegrou ps.com...
>
>
>
>
> - Show quoted text -
sql

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.Use cliconfg.exe.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>
> >I am trying to create analiasfor a particular SQL Server 2005
> >Instance.
> > I've tried doing this in the host file but this was unsucessful
> > because all the host file seems to do is give you an IP address for
> > the required server (not the acutal SQLinstance).
> > How do I go about setting up thealiascorrectly, do I need to use DNS
> > or something like that?
> > Thealiasis fixed and of the form "dbsrvMyDatabase"
> > Any suggestions would be gratefully received.- Hide quoted text -
> - Show quoted text -

Creating a Stored Proc from a particular template?

Hello,

I'm trying to get to grips with SQL Server 2005.

One of the things I want to do is provide members of my team with a stored procedure template that they can use which has special error handling code in it , etc

I found my way to the template explorer and created a new template that I want to use to create stored procedures in certain circumstances (but not ALL circumstances)

But now I can't figure out how to specify WHICH template to use when creating a stored procedure.

Like, when I click on the Programmability/Stored Procedures node and then right click and select New Stored Procedure... it just uses the Basic Template.. but I'd like to be able to elect to use my alternative template to create the stored proc.

So, what are the correct steps to follow? do i just double click my new template in Template Explorer? And then have to go Query/Specify Values for Template Parameters ? Or what?

If this is the way to do it then it seems very clunky really....

Thanks

>>But now I can't figure out how to specify WHICH template to use when creating a stored procedure.

>>Like, when I click on the Programmability/Stored Procedures node and then right click and select New Stored Procedure... it just uses the Basic Template.. but I'd like to be able to elect to use my alternative template to create the stored proc.

You can create procedures by double-clicking on the template. Or you may just drag-and-drop any template into the body of any query.

Creating a report on a file share from a stored procedure

Hi,
I've seen one or two postings on this, and in particular 2 approaches:
1) running rs.exe from a stored procedure in conjunction with xp_cmdshell.
I have successfully managed to create and test a stored procedure that
renders a report to a file share using this approach - but for some reason
it doesn't work when integrated into the enterprise app I need it too work
with. When the sp is initiated from the app, the report appears to be
generated successfully (according to the entries in the ExecutionLog table
in the ReportServer database), but isn't being written to file on the server
(although it did in testing). No return errors, nothing in the EventLog,
nothing, nothing, nothing. Even when rs.exe is run with the -t parameter.
Very frustrating.
2) So, if I can do this quickly, I thought I'd look at a second approach i.e
accessing the web service directly from the stored procedure via the
sp_OACreate stored procedure. From a posting I've seen from Teo Lachev, he
indicates that the object to reference is MSXML2.ServerXMLHttp, with an
example provided. However what isn't clear to me is how this can be used to
render and write the report to a file share, in a similar manner to the .rss
file I used in conjunction with point 1 above. Is this approach possible?
or can the web service only be used to create an on-demand report back to a
browser?
Anyone with some examples/tips on this?
Thanks
GregWhat user security context is used when run from the app? Try (as a test)
setting Everyone Full Control to the directory where the file is being
written.
Jeff
"Greg Clark" <gclarkmail@.yahoo.com> wrote in message
news:#9IMhzamEHA.648@.tk2msftngp13.phx.gbl...
> Hi,
> I've seen one or two postings on this, and in particular 2 approaches:
> 1) running rs.exe from a stored procedure in conjunction with xp_cmdshell.
> I have successfully managed to create and test a stored procedure that
> renders a report to a file share using this approach - but for some reason
> it doesn't work when integrated into the enterprise app I need it too work
> with. When the sp is initiated from the app, the report appears to be
> generated successfully (according to the entries in the ExecutionLog table
> in the ReportServer database), but isn't being written to file on the
server
> (although it did in testing). No return errors, nothing in the EventLog,
> nothing, nothing, nothing. Even when rs.exe is run with the -t parameter.
> Very frustrating.
> 2) So, if I can do this quickly, I thought I'd look at a second approach
i.e
> accessing the web service directly from the stored procedure via the
> sp_OACreate stored procedure. From a posting I've seen from Teo Lachev,
he
> indicates that the object to reference is MSXML2.ServerXMLHttp, with an
> example provided. However what isn't clear to me is how this can be used
to
> render and write the report to a file share, in a similar manner to the
.rss
> file I used in conjunction with point 1 above. Is this approach possible?
> or can the web service only be used to create an on-demand report back to
a
> browser?
> Anyone with some examples/tips on this?
> Thanks
> Greg
>|||Hi Jeff,
The directory already has Everyone Full Control applied.
What I find so bizarre is that in theory there should be absolutely no
difference as to whether the stored procedure is called from the app
(doesn't create the file) or whether I initiate the stored procedure with a
test in SQL Query Analyzer (creates the file). The rss file createing and
rendering the report is called from within the sp, and the same credentials
are being used irrespective of how it is initiated.
I'm working in a test environment on vmware, and I have the app (which uses
sql server) installed on the same server as reporting services, and the only
clue I have is that in the TimeDataRetrieval field in the ExecutionLog
table, the value is 1000 times higher (e.g. 61315 vs 53) when the stored
procedure is initiated from the app as opposed to query analyzer. As the
Status field holds the value of rsSuccess, perhaps some timeout on the
stream.write() function is occuring' Not sure, which is why I thought I'd
try another approach..
My rss file is as follows:
Public Sub Main()
Dim format as string = "PDF"
Dim fileName as String = "C:\Reportd\yyyy.pdf"
Dim reportPath as String = "/CNSOfficeSystem/Quote"
' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "QuoteFolderID"
parameters(0).Value = QFID
' parameters(0).Value = "VMBP01$VMBP01$00000242"
parameters(1) = New ParameterValue()
parameters(1).Name = "CustomerID"
parameters(1).Value = CUID
' parameters(1).Value = "12809"
parameters(2) = New ParameterValue()
parameters(2).Name = "ContactID"
parameters(2).Value = COID
' parameters(2).Value = "245"
results = rs.Render(reportPath, format, Nothing, Nothing, parameters,
Nothing, Nothing, encoding, mimeType, reportHistoryParameters, warnings,
streamIDs)
' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ePkHhBdmEHA.3336@.TK2MSFTNGP10.phx.gbl...
> What user security context is used when run from the app? Try (as a test)
> setting Everyone Full Control to the directory where the file is being
> written.
> Jeff
> "Greg Clark" <gclarkmail@.yahoo.com> wrote in message
> news:#9IMhzamEHA.648@.tk2msftngp13.phx.gbl...
> > Hi,
> > I've seen one or two postings on this, and in particular 2 approaches:
> >
> > 1) running rs.exe from a stored procedure in conjunction with
xp_cmdshell.
> > I have successfully managed to create and test a stored procedure that
> > renders a report to a file share using this approach - but for some
reason
> > it doesn't work when integrated into the enterprise app I need it too
work
> > with. When the sp is initiated from the app, the report appears to be
> > generated successfully (according to the entries in the ExecutionLog
table
> > in the ReportServer database), but isn't being written to file on the
> server
> > (although it did in testing). No return errors, nothing in the
EventLog,
> > nothing, nothing, nothing. Even when rs.exe is run with the -t
parameter.
> > Very frustrating.
> >
> > 2) So, if I can do this quickly, I thought I'd look at a second approach
> i.e
> > accessing the web service directly from the stored procedure via the
> > sp_OACreate stored procedure. From a posting I've seen from Teo Lachev,
> he
> > indicates that the object to reference is MSXML2.ServerXMLHttp, with an
> > example provided. However what isn't clear to me is how this can be
used
> to
> > render and write the report to a file share, in a similar manner to the
> .rss
> > file I used in conjunction with point 1 above. Is this approach
possible?
> > or can the web service only be used to create an on-demand report back
to
> a
> > browser?
> >
> > Anyone with some examples/tips on this?
> >
> > Thanks
> > Greg
> >
> >
>

Create View Only

Good afternoon,

I have a user that needs read only access to all tables in a particular database - Which is working fine.

He also needs to create views, as well as the above, but nothing else.

Is this possible?

Many thanks.

This can be done, but there are a couple of caveats; hang on. Look at this example:

Code Snippet

create view dbo.v_doWhat
as
select what from doWhat
go

/*

Server: Msg 2760, Level 16, State 1, Procedure v_doWhat, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

*/

alter view kawTest.v_doWhat
as
select what from doWhat

go

delete from v_doWhat

/*
Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'doWhat', database 'kawTest', owner 'dbo'.
*/

Note in the first create that because you do not have DBO privilege that you cannot create DBO views. Secondly, the views that you create will be for a specific schema and in SQL 2000 they will be owned by a specific user.

I am probably leaving out more; hopefully, somebody will pick me up.

Another thing to think about: If the developer or user has permission to create views, should they also have the ability to create functions?

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.developersdex.com ***Try creating the sp without specifying a schema/owner. If that works, you can
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.developersdex.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/