creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neil
Hi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default...b;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>
|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default...b;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>

creating views in mssql

hey guys,
can you share an example of how I can create views in MSSQL where the table
is stored in oarcle server.
thanks guys.
neilHi,
For that you have to create a linked server to the Oracle server.
see this link to get info on creating linked Server to ORACLE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
After that create the view from sql server .
Sample:
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
Thanks
Hari
MCDBA
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> hey guys,
> can you share an example of how I can create views in MSSQL where the
> table is stored in oarcle server.
> thanks guys.
> neil
>|||Hi Hari,
Thanks for the link. I'm gonna try it tomorrow.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eEf6BjYqEHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> For that you have to create a linked server to the Oracle server.
> see this link to get info on creating linked Server to ORACLE
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> After that create the view from sql server .
> Sample:
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> Thanks
> Hari
> MCDBA
> "Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
> news:O0gAwMYqEHA.2696@.TK2MSFTNGP15.phx.gbl...
>> hey guys,
>> can you share an example of how I can create views in MSSQL where the
>> table is stored in oarcle server.
>> thanks guys.
>> neil
>

creating user procedures in the master database

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

Thanks...

Moving thread to the DB Engine forum.|||

It is not a best practice to create user stored procs in master. This SP was undocumented and not subject to the 3 release deprecation policy. I think it went away with the move to the resource database in SQL2K5.

With that, what is your specific scenario for creating stored procs in master?

Creating new table from passed variabe in MSSQL Stored procedure

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS
BEGIN

create table @.TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!You need to do the following to get it working

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS

BEGIN
Declare @.SQLStatement nvarchar(4000)

SET @.SQLStatement = 'create table ' + @.TableName + '([id] int identity(1,1),[diller] int)'

Exec (@.SQLStatement )

end
GO

That should do it.

Let me know if you have any questions.|||If it aint to much i would very much like to check for the tables existens before i create it... so basicly i would need to check if the variable exists as a table before i insert it something like

if not exists(Select * From @.TableName)
begin

Stuff...

End

And by the way thk you for the quick reply...|||You can have it as
if not exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = @.TableName)
begin

Stuff...

End

Recently, I wrote article on how to check the objects in database. You can check it at www.aspalliance.com/349 to understand the information_schema views.

Let me know if the above does not work for you.

AP|||Thk you very much been pulling my hair over this... and good article very helpfull... i just startet programming in SQL well my vision is to create websites structured from SQL so that changing stuff around the page is going to be an ease for the user..

thx again...

Jakob