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.
> >> >>
> >>
> >>
> >>
>
>
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Friday, March 30, 2012
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...
>
>
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
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
Tuesday, March 20, 2012
Referencing another table in MS_SQL?
Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
Referencing another table in MS_SQL?
Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks."Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
Referencing another table in MS_SQL?
Hello everyone,
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks.
"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004
|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
We would like to have the user_ID field in the security table reference the
email field in the Cusomer table. Basically, I would like instant updates
of security login information when the email is changed/added in the
customer table. I would prefer not to have periodic updates/sync. The
preferred method would reference the email field rather than update from the
email field.
Example of use: When an end user modifies their personal info (email field
in the customer table), the login will instantly be set/updated in the
user_ID field in the security table.
Any help is appreciated. Thanks.
"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Are the columns compatible datatypes? If so make the user_id column in the
security table a foreign key referencing the email column in the customer
table WITH UPDATE CASCADE. In order for this to work rhw email column must
have a UNIQUE or PRIMARY KEY CONSTRAINT.
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 30/10/2004
|||"Kevin" <kevin_aoki@.NOSPAMhotmail.com123> wrote in message
news:9Oyhd.80$2N2.46980@.news.uswest.net...
> Hello everyone,
> We would like to have the user_ID field in the security table reference
> the
> email field in the Cusomer table. Basically, I would like instant updates
> of security login information when the email is changed/added in the
> customer table. I would prefer not to have periodic updates/sync. The
> preferred method would reference the email field rather than update from
> the
> email field.
> Example of use: When an end user modifies their personal info (email field
> in the customer table), the login will instantly be set/updated in the
> user_ID field in the security table.
> Any help is appreciated. Thanks.
Two words: UPDATE TRIGGER
Monday, March 12, 2012
REFERENCEing two columns to the same key
Hi
I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:
BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp
FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?
My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?
Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .
Terri|||Cool - thanks for your reply
You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?
Great, now I have to learn triggers!!!
Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths
So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).
This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.
Terri
Saturday, February 25, 2012
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?
It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>
|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(90
0).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(90
0).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Reducing column size
I want to reduce the length of a column in one of my database tables
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
from varchar(2048) to varchar(900), basically so I can create an index
on the column to gain much improved performance.
To do this, I am executing the following statement:
ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
Executing this in query analyzer results in the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
If I make this change via Enterprise Manager, it appears as a warning
and I can click OK to ignore and make the change. However, in query
analyzer this is treated as an error and the statement is not
performed.
What i want is to basically ignore this error and make the change
regardless. Is this possible?It basically tell you the data length in that column is more then varchar(900).
You can reduce the length of a column of the length less the length of the
data in that column.
review the data in that column which more the 900 char
Cheers
"jasonatkins2001@.hotmail.com" wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
>|||Notice that you have data that is longer then 900 bytes and by forcing
the modification you will lose data.
The reason that the EM does it and the alter statement in the QA
doesn't work is that the EM doesn't really runs alter table statement.
Instead the EM creates a new table and then runs insert select from the
source table to the new table. Then it drops the old table, renames
the new table, and creates the table constraints and the references
from other tables to the new table.
Also notice that even if you'll have an index on a column that has 900
bytes, the index won't be very effective.
Adi
jasonatkins2001@.hotmail.com wrote:
> I want to reduce the length of a column in one of my database tables
> from varchar(2048) to varchar(900), basically so I can create an index
> on the column to gain much improved performance.
> To do this, I am executing the following statement:
> ALTER TABLE myTable ALTER COLUMN description VARCHAR(900) NULL
> Executing this in query analyzer results in the following error:
> Server: Msg 8152, Level 16, State 9, Line 1
> String or binary data would be truncated.
> The statement has been terminated.
> If I make this change via Enterprise Manager, it appears as a warning
> and I can click OK to ignore and make the change. However, in query
> analyzer this is treated as an error and the statement is not
> performed.
> What i want is to basically ignore this error and make the change
> regardless. Is this possible?
Monday, February 20, 2012
Re-do replication for new release
We alter the database schema that our production application uses with almost every release. Is there a way to basically remove the replication and re-do it for all tables / views at one time? Then we could just "rebuild" the replication with every release. We have about 200 tables and 200 views that have many dependencies. I'm sure it could be done with scripting and the stored procedures, but I'm new at this and not sure where to start. Any ideas? Thanks!
Yes, you could script out all the replication settings (publication, articles, jobs, subscriptions, etc) using the UI, then remove replication completely from the publisher as well as subscriber and after you do the schema changes to the databases, then use the scripts to setup replication again on the new release.But however note that there are always differences between releases. There could be slight differences in the way they are scripted out and the way they are handled in the new release. With that in mind, you may have to slightly edit the scripts before running them on the newest version.
Also, I would first test it out on a test systems before trying it on the production servers.
Also note that With SQL Server 2005, schema changes to the tables are replicated to the subscriber. So for example, if you add a new column to one of the replicated tables, that new column will be replicated to the Subscriber when configured appropriately.|||Thank you Sir, for your suggestions. I will give them a try.
Subscribe to:
Posts (Atom)