Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.
Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:

> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>
|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.
|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>
|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:

> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (s
ay
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don'
t
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...[vbcol=seagreen]
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exis
ts
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:

> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way wil
l
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
>
>sql

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:

> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>

Referencing tables on a remote server

I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?
Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegro ups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegroups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

referencing linked server in stored procedures

hello, i need to reference a table in a sqlserver instance from a
stored procedure in a different sqlserverinsctance... it's possible
reference the table from the stored procedure?Did you try using full naming:
servername.database.owner.Tablename
MC
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133304999.836436.40130@.g49g2000cwa.googlegroups.com...
> hello, i need to reference a table in a sqlserver instance from a
> stored procedure in a different sqlserverinsctance... it's possible
> reference the table from the stored procedure?
>

Reduce the Database size

Hi,
I have a database with size 20 GB.
The DB contains 550 tables, 2110 stored procedures.
I need to write this DB in a DVD. So I have to reduce the DB size around 6 GB.
It is not necessary that all the data should be in DB. Some sample data is
enough.
Is there any way to do it with less administrative effort?
Please advise me,
Soura
Soura - this is such a propriety requirement, you'll have to code this
yourself. One posibility is to restore a backup of the database and choose a
central table to the schema and do a delete. If you previously turn on
cascading deletes throughout the database then this'll be a convenient way
of removing related data. Do this the required amount of times and then
shrink the database before copying the database to the DVD.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for your reply.
I enabled 'cascade delete' and saved it through Enterprise manager
and closed that window but then i re-opened that the table design the
'cascade delete' is unchecked ie., it is not saved.
Please guide me,
Soura
"Paul Ibison" wrote:

> Soura - this is such a propriety requirement, you'll have to code this
> yourself. One posibility is to restore a backup of the database and choose a
> central table to the schema and do a delete. If you previously turn on
> cascading deletes throughout the database then this'll be a convenient way
> of removing related data. Do this the required amount of times and then
> shrink the database before copying the database to the DVD.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||SouRa,
please can you run the following type of script in query analyser to change
the FK to be a cascade-delete one, and post back any error messages you are
seeing.
ALTER TABLE dbo.Territories WITH NOCHECK ADD CONSTRAINT
FK_Territories_Region FOREIGN KEY
(
RegionID
) REFERENCES dbo.Region
(
RegionID
) ON DELETE CASCADE
NOT FOR REPLICATION
GO
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your reply Paul.
Can i have a query to run for whole database.
Advise me
Soura
"Paul Ibison" wrote:

> SouRa,
> please can you run the following type of script in query analyser to change
> the FK to be a cascade-delete one, and post back any error messages you are
> seeing.
> ALTER TABLE dbo.Territories WITH NOCHECK ADD CONSTRAINT
> FK_Territories_Region FOREIGN KEY
> (
> RegionID
> ) REFERENCES dbo.Region
> (
> RegionID
> ) ON DELETE CASCADE
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There are scripts out there that you could modify slightly to do this
(http://www.sqlservercentral.com/scri...p?scriptid=971) but
I'd get EM to help you out here. Have EM script out all the FKs to a
textfile. Do a find-and-replace on
") NOT FOR REPLICATION" with ") ON DELETE CASCADE NOT FOR REPLICATION"
and ") GO" with ") ON DELETE CASCADE GO".
Have EM also create a drop script for the FKs.
At this stage you should have everything you need.
Obviously try this out on a test database first
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .