Any SQL equivalent of InStrRev()?

In VB, there's a string function called InStrRev() which returns the
position of an occurrence of one string within another, from the end of
string.
I have a column containing data such as "/uploads/encrypted/design1.doc". I
want to be able to extract the pure filename from this column (e.g. returns
"design1.doc" from the above example).
In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
and then extract the rest of the string from that position.
Is there a similar way to do this in SQL script?
Thank you.DId you try something using CHARINDEX(REVERSE())?
SELECT CHARINDEX('x', 'foobarxgoo')
SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
"Bob" <noreply@.nospam.com> wrote in message
news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
> In VB, there's a string function called InStrRev() which returns the
> position of an occurrence of one string within another, from the end of
> string.
> I have a column containing data such as "/uploads/encrypted/design1.doc".
> I want to be able to extract the pure filename from this column (e.g.
> returns "design1.doc" from the above example).
> In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
> and then extract the rest of the string from that position.
> Is there a similar way to do this in SQL script?
> Thank you.
>|||Hi Aaron,

> SELECT CHARINDEX('x', 'foobarxgoo')
returns 7

> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
returns 4
The only way I can find this useful is if I REVERSE the string (i.e.
"/uploads/encrypted/design1.doc" to "cod.1ngised/detpyrcne/sdaolpu/"), then
find the first instance of a slash "/". Truncate the rest of the string.
That would get me "cod.1ngised". And finally, REVERSE again to get
"design1.doc".
But that sounds like a lot of calls and could slow down performance!
What do you think?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23CZHh883FHA.2196@.tk2msftngp13.phx.gbl...
> DId you try something using CHARINDEX(REVERSE())?
> SELECT CHARINDEX('x', 'foobarxgoo')
> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
>
> "Bob" <noreply@.nospam.com> wrote in message
> news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
>|||You can directly use the expression:
RIGHT( @.s, CHARINDEX( '/', REVERSE( @.s ) ) - 1 )
Obviously, any nested string manipulation functions done over a large
dataset is going to impact performance ( VB is no different either ). In
many cases it might be negligible. If it is a crucial issue, rather than
putting the entire path in a single column, consider using a separate column
for the file name.
Anith

Any keyboard shortcut for "open table" in Mgt Studio?

Folks, does anyone know if there is a keyboard shortcut to open a database table in Management Studio? I refer to the equivalent to right-clicking on a table to choose "open table", in order to view/edit the data.

I find no reference to an equivalent in the menu, nor even any reference to "open table" in the online help. I've dug around the web, to no avail. Surely other keyboard fans must groan at the need to touch the mouse for this frequent operation. Thanks in advance.

Windows functionality:

SHIFT+F10 Opens a shortcut menu for the selected item (this is the same as right-clicking an object
ALT+underlined letter in menu: Opens the menu

|||Might refer to the keyboard shortcuts on using SSMS ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/toolref9/html/fb4edf3c-71b6-4645-b1d1-ddfdd69f0d7b.htm link. Why do you need to open the table in SSMS which is slow if the table has too many rows or volume based. In thsi case using Query Analyzer is best one to go to view the data.|||Thanks very much. I'm a big keyboard fan, but that's one I'd just missed (or not had reason to notice it before). That does solve the problem for me.|||

Thanks, but that's about creating new shortcuts. I was looking for any existing ones. The previous answer about using shift-f10 will suffice.

As for why one would use the "open table" option, do you realize that it permits editing the data? That's certainly much easier in that interface than using SQL statements in query analyzer. Sure, you're right that if you had a large table it may not make sense. I don't, so it works fine for me.

Any equivalent to Oracle RAC available for SQL Server?

Using Windows clustering requires too long a failover time for my company's
uptime requirements. While SQL 2005's mirroring decreases the failover
time, it also only allows me to mirror to one server, thus putting me in a
rough spot for DR.
Is there any Oracle RAC type equivalent functionality (i.e. shared cache)
planned for future versions of SQL 2005, or available via some 3rd party
today for use with SQL 2005 today? The net is, I need as little downtime as
possible, both within a site, and across to my DR site (and as little to no
data loss as possible to that DR site)...all the while (of course)
minimizing the perf hit all of this requires...though while perf needs to
still fall in acceptable ranges, better perf is not as important as faster
failover and no data loss.
Any help is appeciated.
Thanks
RAC is by no means a High Availability feature so I am confused as to the
subject. There is no SQL Server RAC equivalent in the current release for a
read write database. However there are several HA / DR features that SQL
Server has that people use every day to satisfy their HA / DR needs. How
fast the recovery and how little down time is usually more a factor of how
much money you wish to spend. For instance you can do mirroring to get fast
up time and use the SAN's features to do DR. This is just a simple example
and it will really depend on what you want, what you have to work with and
how much time and money youa re willing to put into it. You might want to
start here:
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
Andrew J. Kelly SQL MVP
"Jim" <ilmm555@.yahoo.com> wrote in message
news:uimO$QJhHHA.4980@.TK2MSFTNGP02.phx.gbl...
> Using Windows clustering requires too long a failover time for my
> company's uptime requirements. While SQL 2005's mirroring decreases the
> failover time, it also only allows me to mirror to one server, thus
> putting me in a rough spot for DR.
> Is there any Oracle RAC type equivalent functionality (i.e. shared cache)
> planned for future versions of SQL 2005, or available via some 3rd party
> today for use with SQL 2005 today? The net is, I need as little downtime
> as possible, both within a site, and across to my DR site (and as little
> to no data loss as possible to that DR site)...all the while (of course)
> minimizing the perf hit all of this requires...though while perf needs to
> still fall in acceptable ranges, better perf is not as important as faster
> failover and no data loss.
> Any help is appeciated.
> Thanks
|||OK, while RAC's original intent is to be a scale out feature, it's also very
much an HA feature...as if a node dies, other nodes press on...there's no
notion of "failover", thus eliminating the need to failover.
I disagree with it being "a factor of how much money you wish to spend"...as
regardless of how much you spend, the best you'll get SQL to is seconds of
downtime (with mirroring, likely 10s of seconds-minutes with
clustering)...thus, if you were to patch once a month (between SQL and
Windows this is certainly not out of the realm of possibility), that's
twelve failovers per year, at (at best...10 seconds of downtime each
failover)...so you're at 120 seconds a year...99.999% availability is 52.256
seconds...so you're already down to under 5 9's availability for the year.
In this shop, there's no notion of "unplanned downtime"...we're 24x7...it's
ALL downtime.
As for mirroring with SAN replicaiton for DR...below I mention no data loss,
so you'd be talking about synchronous mirroing WITH synchronous SAN
replication...while I said perf isn't the highest priority...I wonder how
slow it would be with effectively 3 writes for each write (one to the
primary SQL, one to the secondary SQL, one to the DR site...and since this
is all synchronous...all writes have to be completed before the
transaction's commited)...plus, now you're using SQL failover functionality
and SAN failover functionality...I certainly wonder how (operationally)
feasible this all will be...I'm sure it will be feasible, but certainly not
seamless...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uPErZcShHHA.4064@.TK2MSFTNGP02.phx.gbl...
> RAC is by no means a High Availability feature so I am confused as to the
> subject. There is no SQL Server RAC equivalent in the current release for
> a read write database. However there are several HA / DR features that SQL
> Server has that people use every day to satisfy their HA / DR needs. How
> fast the recovery and how little down time is usually more a factor of how
> much money you wish to spend. For instance you can do mirroring to get
> fast up time and use the SAN's features to do DR. This is just a simple
> example and it will really depend on what you want, what you have to work
> with and how much time and money youa re willing to put into it. You might
> want to start here:
> http://www.microsoft.com/sql/technologies/highavailability/default.mspx
>
> --
> Andrew J. Kelly SQL MVP
> "Jim" <ilmm555@.yahoo.com> wrote in message
> news:uimO$QJhHHA.4980@.TK2MSFTNGP02.phx.gbl...
>
|||While RAC does give some flexibility in terms of a single node failure there
are still plenty of places that are close to single point of failure without
additional effort. And it does nothing for DR. Oracle or anyother rdbms has
no magic solution to guarantee 0 down time. It really boils down to the
limitations of hardware. You are not the 1st company looking for 24X7
operation. Hundreds if not thousands of companies use SQL Server every day
for 5 nines of availability. I disagree with the down time for the number
of patches. How often you patch is often up to you. I had a system running
for almost 3 years. It was secure and did what we wanted so we choose not to
patch it.
Andrew J. Kelly SQL MVP
"Jim" <ilmm555@.yahoo.com> wrote in message
news:eMz1vgVhHHA.1388@.TK2MSFTNGP05.phx.gbl...
> OK, while RAC's original intent is to be a scale out feature, it's also
> very much an HA feature...as if a node dies, other nodes press
> on...there's no notion of "failover", thus eliminating the need to
> failover.
> I disagree with it being "a factor of how much money you wish to
> spend"...as regardless of how much you spend, the best you'll get SQL to
> is seconds of downtime (with mirroring, likely 10s of seconds-minutes with
> clustering)...thus, if you were to patch once a month (between SQL and
> Windows this is certainly not out of the realm of possibility), that's
> twelve failovers per year, at (at best...10 seconds of downtime each
> failover)...so you're at 120 seconds a year...99.999% availability is
> 52.256 seconds...so you're already down to under 5 9's availability for
> the year. In this shop, there's no notion of "unplanned downtime"...we're
> 24x7...it's ALL downtime.
> As for mirroring with SAN replicaiton for DR...below I mention no data
> loss, so you'd be talking about synchronous mirroing WITH synchronous SAN
> replication...while I said perf isn't the highest priority...I wonder how
> slow it would be with effectively 3 writes for each write (one to the
> primary SQL, one to the secondary SQL, one to the DR site...and since this
> is all synchronous...all writes have to be completed before the
> transaction's commited)...plus, now you're using SQL failover
> functionality and SAN failover functionality...I certainly wonder how
> (operationally) feasible this all will be...I'm sure it will be feasible,
> but certainly not seamless...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uPErZcShHHA.4064@.TK2MSFTNGP02.phx.gbl...
>
|||Hello Jim,
I am interested you say that SQL clustering is to slow. I run 8 production
clusters of various sizes, one is 300 GB just for data and fail over takes
about 30 seconds. Is 30 seconds to slow? If so then using a database mirror
would be the way to go. You will need to ensure that the client is using
ADO.NET 2.0 so that your failover is supported. However, there will be no
need to handle the failover as there is when using TAF on RAC.
Having said that currently SQL server has no RAC equivalency for scaling
out. If this is a requirement I would go with a HP Itanium system such as
the 6840 this would allow you to scale up over time rather than scaling out.
Let me know if you need clarification
John Vandervliet.
"Jim" <ilmm555@.yahoo.com> wrote in message
news:uimO$QJhHHA.4980@.TK2MSFTNGP02.phx.gbl...
> Using Windows clustering requires too long a failover time for my
> company's uptime requirements. While SQL 2005's mirroring decreases the
> failover time, it also only allows me to mirror to one server, thus
> putting me in a rough spot for DR.
> Is there any Oracle RAC type equivalent functionality (i.e. shared cache)
> planned for future versions of SQL 2005, or available via some 3rd party
> today for use with SQL 2005 today? The net is, I need as little downtime
> as possible, both within a site, and across to my DR site (and as little
> to no data loss as possible to that DR site)...all the while (of course)
> minimizing the perf hit all of this requires...though while perf needs to
> still fall in acceptable ranges, better perf is not as important as faster
> failover and no data loss.
> Any help is appeciated.
> Thanks
|||Do you run 8 production clusters where all of them are single instance
cluster..i.e one active and one passive or do you have a multi instance
cluster say more than 2 active nodes with 1 passive node for any of the
active nodes to failover.
"John Vandervliet" <jvandervliet@.SJRB.AD> wrote in message
news:7FE2AB87-4B70-44A0-8400-85B209FC6626@.microsoft.com...
> Hello Jim,
> I am interested you say that SQL clustering is to slow. I run 8 production
> clusters of various sizes, one is 300 GB just for data and fail over takes
> about 30 seconds. Is 30 seconds to slow? If so then using a database
> mirror would be the way to go. You will need to ensure that the client is
> using ADO.NET 2.0 so that your failover is supported. However, there will
> be no need to handle the failover as there is when using TAF on RAC.
> Having said that currently SQL server has no RAC equivalency for scaling
> out. If this is a requirement I would go with a HP Itanium system such as
> the 6840 this would allow you to scale up over time rather than scaling
> out.
> Let me know if you need clarification
> John Vandervliet.
>
> "Jim" <ilmm555@.yahoo.com> wrote in message
> news:uimO$QJhHHA.4980@.TK2MSFTNGP02.phx.gbl...
>
|||We run a combination most the instances are active/passive setups, howerver
we have 3 clusters that each of 2 instances of SQL. the servers are exact
matches of each other and can handle the load for both instances when needed.
"Hassan" wrote:

> Do you run 8 production clusters where all of them are single instance
> cluster..i.e one active and one passive or do you have a multi instance
> cluster say more than 2 active nodes with 1 passive node for any of the
> active nodes to failover.
>
> "John Vandervliet" <jvandervliet@.SJRB.AD> wrote in message
> news:7FE2AB87-4B70-44A0-8400-85B209FC6626@.microsoft.com...
>
>
|||On Apr 26, 5:12 pm, John Vandervliet
<JohnVandervl...@.discussions.microsoft.com> wrote:
> We run a combination most the instances are active/passive setups, howerver
> we have 3 clusters that each of 2 instances of SQL. the servers are exact
> matches of each other and can handle the load for both instances when needed.
>
> "Hassan" wrote:
>
>
>
>
>
> - Show quoted text -
Hassan,
My employer SteelEye Technology has a solution called LifeKeeper for
SQL Server. With our solution we can cluster 2-nodes locally with
either shared storage or syncronous host based replication and also
support a 3rd node in the DR site with either sync or async
replication. Of course sync replication will impact performance if
done across a WAN, but it is the only way to guarantee no data loss.
Failover time is similar to MSCS, so if that is too long, you will
have to look elsewhere.
Here is some information.
www.steeleye.com/pdf/literature/lifekeeper_for_sql_server.pdf
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
RamRam,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there any equivalent in SQL server for oracle statement "Create or
> > Replace" such as CREATE OR REPLACE PROCEDURE......
> >
> > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> > DROP and a CREATE can't reside in the same query batch file. I want to
put
> > the whole script (for creating a new procedure or ALTERing an existing
> one)
> > in one file and be able to run it.
> >
> > Any thoughts?
> >
> > Thanks,
> > Ram
> >
> >
>|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > Ram,
> >
> > No, not to my knowledge. The next best way for any objects other than
> tables
> > is:
> >
> > If the object exists
> > drop it
> > GO
> > Create the object
> >
> > I do this also with tables but I have to combine them with ALTER
scripts.
> > You don't want to create a table more than once, normally.
> >
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > Is there any equivalent in SQL server for oracle statement "Create or
> > > Replace" such as CREATE OR REPLACE PROCEDURE......
> > >
> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also
a
> > > DROP and a CREATE can't reside in the same query batch file. I want to
> put
> > > the whole script (for creating a new procedure or ALTERing an existing
> > one)
> > > in one file and be able to run it.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > > Ram
> > >
> > >
> >
> >
>|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
>> Thanks Ron,
>> I found a way to do this taking cue from your advice:
>> IF EXISTS (SELECT * FROM sysobjects WHERE id => OBJECT_ID(N'[MyStoredProc]')
>> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
>> DROP PROCEDURE [MyStoredProc]
>> GO
>> CREATE PROCEDURE MyStoredProc.....
>> GO
>> In this way, I can run this query batch file and change it at any time if
> I
>> want to. This was part of a requirement to do version control on stored
>> procedures and now I am happy with the solution. And you are right, we
> won't
>> be doing this for tables.
>> Thanks,
>> Ram
>> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
>> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
>> > Ram,
>> >
>> > No, not to my knowledge. The next best way for any objects other than
>> tables
>> > is:
>> >
>> > If the object exists
>> > drop it
>> > GO
>> > Create the object
>> >
>> > I do this also with tables but I have to combine them with ALTER
> scripts.
>> > You don't want to create a table more than once, normally.
>> >
>> > Ron
>> > --
>> > Ron Talmage
>> > SQL Server MVP
>> >
>> > "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
>> > news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
>> > > Hi,
>> > >
>> > > Is there any equivalent in SQL server for oracle statement "Create or
>> > > Replace" such as CREATE OR REPLACE PROCEDURE......
>> > >
>> > > As far as I know, SQL Server doesn't support "CREATE OR REPLACE".
>> > > Also
> a
>> > > DROP and a CREATE can't reside in the same query batch file. I want
>> > > to
>> put
>> > > the whole script (for creating a new procedure or ALTERing an
>> > > existing
>> > one)
>> > > in one file and be able to run it.
>> > >
>> > > Any thoughts?
>> > >
>> > > Thanks,
>> > > Ram
>> > >
>> > >
>> >
>> >
>>
>

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
Ram
Ram,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables[vbcol=seagreen]
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
put
> one)
>
|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:

> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>
|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't[vbcol=seagreen]
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> tables
scripts.[vbcol=seagreen]
a
> put
>
|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> OBJECT_ID(N'[MyStoredProc]')
> I
> won't
> scripts.
> a
>

Any equivalent of "Create or Replace"?

Hi,
Is there any equivalent in SQL server for oracle statement "Create or
Replace" such as CREATE OR REPLACE PROCEDURE......
As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
DROP and a CREATE can't reside in the same query batch file. I want to put
the whole script (for creating a new procedure or ALTERing an existing one)
in one file and be able to run it.
Any thoughts?
Thanks,
RamRam,
No, not to my knowledge. The next best way for any objects other than tables
is:
If the object exists
drop it
GO
Create the object
I do this also with tables but I have to combine them with ALTER scripts.
You don't want to create a table more than once, normally.
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing
one)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>|||Thanks Ron,
I found a way to do this taking cue from your advice:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[MyStoredProc
]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [MyStoredProc]
GO
CREATE PROCEDURE MyStoredProc.....
GO
In this way, I can run this query batch file and change it at any time if I
want to. This was part of a requirement to do version control on stored
procedures and now I am happy with the solution. And you are right, we won't
be doing this for tables.
Thanks,
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Ram,
> No, not to my knowledge. The next best way for any objects other than
tables
> is:
> If the object exists
> drop it
> GO
> Create the object
> I do this also with tables but I have to combine them with ALTER scripts.
> You don't want to create a table more than once, normally.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:uzrRg8FuEHA.4040@.TK2MSFTNGP09.phx.gbl...
put[vbcol=seagreen]
> one)
>|||ALTER PROCEDURE would overwrite the existing procedure with the same name.
Actually, as the name suggestion, it would ALTER the existing Sproc.
"Ram P. Dash" wrote:

> Hi,
> Is there any equivalent in SQL server for oracle statement "Create or
> Replace" such as CREATE OR REPLACE PROCEDURE......
> As far as I know, SQL Server doesn't support "CREATE OR REPLACE". Also a
> DROP and a CREATE can't reside in the same query batch file. I want to put
> the whole script (for creating a new procedure or ALTERing an existing one
)
> in one file and be able to run it.
> Any thoughts?
> Thanks,
> Ram
>
>|||Ram,
This way uses a little bit less code and doesn't access a system table:
IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
DROP PROCEDURE dbo.MyStoredProc
GO
If you have good naming conventions there's no need to use the undocumented
'P' parameter:
IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
DROP PROCEDURE dbo.usp_MyStoredProc
GO
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Ram P. Dash" <rampr2@.hotmail.com> wrote in message
news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> Thanks Ron,
> I found a way to do this taking cue from your advice:
> IF EXISTS (SELECT * FROM sysobjects WHERE id =
OBJECT_ID(N'[MyStoredProc]')
> AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [MyStoredProc]
> GO
> CREATE PROCEDURE MyStoredProc.....
> GO
> In this way, I can run this query batch file and change it at any time if
I
> want to. This was part of a requirement to do version control on stored
> procedures and now I am happy with the solution. And you are right, we
won't
> be doing this for tables.
> Thanks,
> Ram
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:%23ME4ZBGuEHA.3156@.TK2MSFTNGP12.phx.gbl...
> tables
scripts.[vbcol=seagreen]
a[vbcol=seagreen]
> put
>|||That's exactly what I want.
Thanks a lot Ron. You Rock!
Ram
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:ebH6Q6IuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Ram,
> This way uses a little bit less code and doesn't access a system table:
> IF OBJECT_ID('dbo.MyStoredProc', 'P') IS NOT NULL
> DROP PROCEDURE dbo.MyStoredProc
> GO
> If you have good naming conventions there's no need to use the
> undocumented
> 'P' parameter:
> IF OBJECT_ID('dbo.usp_MyStoredProc') IS NOT NULL
> DROP PROCEDURE dbo.usp_MyStoredProc
> GO
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Ram P. Dash" <rampr2@.hotmail.com> wrote in message
> news:%231abkMGuEHA.3932@.TK2MSFTNGP10.phx.gbl...
> OBJECT_ID(N'[MyStoredProc]')
> I
> won't
> scripts.
> a
>

Any equivalent for NEXT_DAY fn of Oracle

Haii Friends,
Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query...
SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL;
ur Help in this regard is really appreciated...
Regards,
VickyI'm thinking that this query returns you the upcoming Thursday? There's not an immediate function that will easily do this for you, however, either a proc or function can achieve this easily. This is assuming you always want the upcoming Thursday

CREATE PROCEDURE dbo.getNextThursday AS

DECLARE @.CurrentDate AS DATETIME
DECLARE @.DayOfWeek AS TINYINT

SELECT @.DayOfWeek = DATEPART(WEEKDAY, GETDATE())

IF (@.DayOfWeek < 5) -- Not equal to Thursday
BEGIN
SELECT @.CurrentDate = (GETDATE() + (5-@.DayOfWeek))
END
ELSE
BEGIN
SELECT @.CurrentDate = (GETDATE() + (7-(@.DayOfWeek-5)))
END

SELECT @.CurrentDate

GO

any alternative to getchecksum in SQL7?

Is there an equivalent of CHECKSUM from SQL2k in SQL7? I discovered
getchecksum in SQL7 but I can’t use it since the tables in the subscriber are
denormalized.
Thanks.
-A
Adam,
if you just want to check that the data is fully synchronized, I'd use
DataCompare from Redgate.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

ANSI Equivalent for IsNumeric()?

Hello,
I working with Teradata and need to find some way to identify if a field is numeric or not. I could not find anything in the Teradata documentation I have.
Thank you,
Ken E.There is no ANSI equivalent to IsNumeric(); you may have to code your own function. :shocked: