I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
Hi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>
|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after[vbcol=seagreen]
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
transaction
>
>
|||I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit dans le message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
Showing posts with label theres. Show all posts
Showing posts with label theres. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
Newbie Alert: Amending all client workstation ODBC's
Hi All,
Newbie question for you all, sorry. I'm currently migrating my DB's to a
new SQL 2000 server and am wondering if there's any way of centrally
changing the ODBC settings of all my workstations? I really don't want to
have the support guys visit each machine in turn and do this manually.
Any advice gratefully received.
Orb.
The ODBC aliases are stored within the registry. Assuming that you are just
changing a server name or IP address of the currently configured database
server you should be able to create a .reg file that your users can "run"
within the logon script.
There is one issue with this method: The users have to have the ability to
write to that part of the registry.
Keith Kratochvil
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:eto7MV0TGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Newbie question for you all, sorry. I'm currently migrating my DB's to a
> new SQL 2000 server and am wondering if there's any way of centrally
> changing the ODBC settings of all my workstations? I really don't want to
> have the support guys visit each machine in turn and do this manually.
>
> Any advice gratefully received.
> Orb.
>
|||Hi Keith,
Thanks for replying. I thought this might be the case, I just wondered if I
was missing any fancy enterprise tools which would do this for me ;o)
Many Thanks,
Orb.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23tn$eu0TGHA.5332@.tk2msftngp13.phx.gbl...
> The ODBC aliases are stored within the registry. Assuming that you are
> just changing a server name or IP address of the currently configured
> database server you should be able to create a .reg file that your users
> can "run" within the logon script.
> There is one issue with this method: The users have to have the ability
> to write to that part of the registry.
> --
> Keith Kratochvil
>
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:eto7MV0TGHA.4952@.TK2MSFTNGP09.phx.gbl...
>
|||We used .reg files in my last shop to update DSNs when needed. This method
worked great!
Keith Kratochvil
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:esSsqa1TGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Hi Keith,
> Thanks for replying. I thought this might be the case, I just wondered if
> I was missing any fancy enterprise tools which would do this for me ;o)
>
> Many Thanks,
> Orb.
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:%23tn$eu0TGHA.5332@.tk2msftngp13.phx.gbl...
>
Newbie question for you all, sorry. I'm currently migrating my DB's to a
new SQL 2000 server and am wondering if there's any way of centrally
changing the ODBC settings of all my workstations? I really don't want to
have the support guys visit each machine in turn and do this manually.
Any advice gratefully received.
Orb.
The ODBC aliases are stored within the registry. Assuming that you are just
changing a server name or IP address of the currently configured database
server you should be able to create a .reg file that your users can "run"
within the logon script.
There is one issue with this method: The users have to have the ability to
write to that part of the registry.
Keith Kratochvil
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:eto7MV0TGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Newbie question for you all, sorry. I'm currently migrating my DB's to a
> new SQL 2000 server and am wondering if there's any way of centrally
> changing the ODBC settings of all my workstations? I really don't want to
> have the support guys visit each machine in turn and do this manually.
>
> Any advice gratefully received.
> Orb.
>
|||Hi Keith,
Thanks for replying. I thought this might be the case, I just wondered if I
was missing any fancy enterprise tools which would do this for me ;o)
Many Thanks,
Orb.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23tn$eu0TGHA.5332@.tk2msftngp13.phx.gbl...
> The ODBC aliases are stored within the registry. Assuming that you are
> just changing a server name or IP address of the currently configured
> database server you should be able to create a .reg file that your users
> can "run" within the logon script.
> There is one issue with this method: The users have to have the ability
> to write to that part of the registry.
> --
> Keith Kratochvil
>
> "Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
> news:eto7MV0TGHA.4952@.TK2MSFTNGP09.phx.gbl...
>
|||We used .reg files in my last shop to update DSNs when needed. This method
worked great!
Keith Kratochvil
"Orbital" <sian.clarke@.newhamhealth.nhs.uk> wrote in message
news:esSsqa1TGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Hi Keith,
> Thanks for replying. I thought this might be the case, I just wondered if
> I was missing any fancy enterprise tools which would do this for me ;o)
>
> Many Thanks,
> Orb.
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:%23tn$eu0TGHA.5332@.tk2msftngp13.phx.gbl...
>
Saturday, February 25, 2012
Newbie - can I do this in a script
I would like my script to check for the existance of a table, and if found
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
JeffYou can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
>
>|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. th
e
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
JeffYou can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
>
>|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. th
e
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>
Newbie - can I do this in a script
I would like my script to check for the existance of a table, and if found
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
JeffYou can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> > You can check the existence of a table using the following syntax:
> >
> > IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
> >
> > eg. Checking if the Northwind Orders table exists
> >
> > IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
>
>|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. the
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>> Jeff
>> I am not sure what you are asking, can you please clarify your question.
>> Thanks
>>
>> - Peter Ward
>
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
JeffYou can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> > You can check the existence of a table using the following syntax:
> >
> > IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
> >
> > eg. Checking if the Northwind Orders table exists
> >
> > IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
>
>|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. the
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>> Jeff
>> I am not sure what you are asking, can you please clarify your question.
>> Thanks
>>
>> - Peter Ward
>
Newbie - can I do this in a script
I would like my script to check for the existance of a table, and if found
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
Jeff
You can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>
|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
>
>
|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward
|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. the
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
Jeff
You can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>
|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
>
>
|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward
|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. the
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>
Subscribe to:
Posts (Atom)