Creating a view to retrieve data from more than one database sql server 2000

Hi everyone,

we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?

Thanks

Have a look at the topic Crossdatabase Ownership chain. It only possible under certain circumstances, the ownership chain cannot be broken.

Jens K. Suessmeyer

http://www.sqlserver2005.de

creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

Create view using UNION

I have 2 tables with exact structure:
Employee:
ID
FirstName
LastName
Position
ExEmployee:
ID
FirstName
LastName
Position
And the reference table
Position:
ID
Name
I want to create a view something like:
CREATE VIEW viewEMPLOEE_ALL
as
SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS LastName,
p.Name AS Position
FROM Employee e
LEFT JOIN Position p ON p.ID = e.Position
ORDER BY e.LastName, e.FirstName
UNION ALL
SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
LastName, p.Name AS Position
FROM Employee ex
LEFT JOIN Position p ON p.ID = ex.Position
ORDER BY ex.LastName, ex.FirstName
This is similar to my actual SQL as my original SQL is much much more table
join.
I got an error when I tried to run this SQL in Query Analyser:
"Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query."Man Utd wrote:
> I have 2 tables with exact structure:
> Employee:
> ID
> FirstName
> LastName
> Position
>
> ExEmployee:
> ID
> FirstName
> LastName
> Position
>
> And the reference table
> Position:
> ID
> Name
> I want to create a view something like:
> CREATE VIEW viewEMPLOEE_ALL
> as
> SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS
> LastName, p.Name AS Position
> FROM Employee e
> LEFT JOIN Position p ON p.ID = e.Position
> ORDER BY e.LastName, e.FirstName
> UNION ALL
> SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
> LastName, p.Name AS Position
> FROM Employee ex
> LEFT JOIN Position p ON p.ID = ex.Position
> ORDER BY ex.LastName, ex.FirstName
> This is similar to my actual SQL as my original SQL is much much more
> table join.
> I got an error when I tried to run this SQL in Query Analyser:
> "Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query."
Remove the ORDER BY in the first SELECT and use the ordinal position for
the ORDER BY in the second only if absolutely necessary. You're better
off not using an ORDER BY at all since it just creates overhead. You can
always add the ORDER BY later on to any queries you write against the
view.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I removed the ORDER BY clause in each SELECT but still got the same error.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Man Utd wrote:
> Remove the ORDER BY in the first SELECT and use the ordinal position for
> the ORDER BY in the second only if absolutely necessary. You're better
> off not using an ORDER BY at all since it just creates overhead. You can
> always add the ORDER BY later on to any queries you write against the
> view.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hi,
Sorry, my careless mistake.
In my second SELECT statement I have a CASE comparing the "ExEmployee" table
field, but I used "Employee" instead.
Solved.
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:Om3fuTr2FHA.3912@.TK2MSFTNGP15.phx.gbl...
> I removed the ORDER BY clause in each SELECT but still got the same error.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
>