Any one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescription
No
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
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/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>
|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:

> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for any
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

Any one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No = dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescriptionNo
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
--
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/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No => dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:
> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for any
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No => dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

Any one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescriptionNo
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
--
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/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description
]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description
]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:

> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for a
ny
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Descriptio
n]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Descriptio
n]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

Any harm in changing nullability while a default exists for a colu

Hi,
In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
changing the table column nullability via the ALTER TABLE ALTER COLUMN while
a default is also defined for that column? If yes, what are they?
--
Many thanks,
OskarYou'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
and re-add the constraint afterwards.
Column nullability doesn't affect the behavior of the default constraint so
I'm not aware of any drawbacks. The default value is used only when the
column is not included in the column list of an INSERT statement. Of
course, an explicit NULL value will be permitted only when the column allows
nulls.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> Hi,
> In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> changing the table column nullability via the ALTER TABLE ALTER COLUMN
> while
> a default is also defined for that column? If yes, what are they?
> --
> Many thanks,
> Oskar
>|||Yes, but when I needed to change a column from NULL to NOT NULL in one of the
tables and a default was defined on that column, the SQL Server parser didn't
complain about that. It allowed me to change the definition without any
warnings. Does this mean that I've done it in the wrong way? If yes, why?
--
Many thanks,
Oskar
"Dan Guzman" wrote:
> You'll need to drop the default constraint before ALTER TABLE...ALTER COLUMN
> and re-add the constraint afterwards.
> Column nullability doesn't affect the behavior of the default constraint so
> I'm not aware of any drawbacks. The default value is used only when the
> column is not included in the column list of an INSERT statement. Of
> course, an explicit NULL value will be permitted only when the column allows
> nulls.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
> > Hi,
> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
> > while
> > a default is also defined for that column? If yes, what are they?
> >
> > --
> > Many thanks,
> > Oskar
> >
> >
>
>|||No, Oskar, you didn't do anything wrong. My comment about dropping and
re-adding the constraint applies when changing the column data type but not
when changing only column nullability. Sorry for the confusion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:3049DDD6-BA64-4A17-BEB6-7B34B9C68D80@.microsoft.com...
> Yes, but when I needed to change a column from NULL to NOT NULL in one of
> the
> tables and a default was defined on that column, the SQL Server parser
> didn't
> complain about that. It allowed me to change the definition without any
> warnings. Does this mean that I've done it in the wrong way? If yes, why?
> --
> Many thanks,
> Oskar
> "Dan Guzman" wrote:
>> You'll need to drop the default constraint before ALTER TABLE...ALTER
>> COLUMN
>> and re-add the constraint afterwards.
>> Column nullability doesn't affect the behavior of the default constraint
>> so
>> I'm not aware of any drawbacks. The default value is used only when the
>> column is not included in the column list of an INSERT statement. Of
>> course, an explicit NULL value will be permitted only when the column
>> allows
>> nulls.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
>> news:D50EF76B-BF46-4B49-9AD9-07364BF75E8A@.microsoft.com...
>> > Hi,
>> > In Microsoft SQL Server 2000 SP3 are there any potential drawbacks of
>> > changing the table column nullability via the ALTER TABLE ALTER COLUMN
>> > while
>> > a default is also defined for that column? If yes, what are they?
>> >
>> > --
>> > Many thanks,
>> > Oskar
>> >
>> >
>>

any automated way of changing pwds manually ?

We have a sql authenticated login that we would like to change pwd through a
script and at the same time , let us know what the pwd is in a text file
that would be located at some share. We need to change pwd every week or 2
for security purposes.. Can we have a job that we can run every 2 weeks and
at the same time, write the new pwd to a text file.. It can be a random
complex pwd... Doesnt matter.. Thanks..Using SQL 2000
hi Hassan
"Hassan" <fatima_ja@.hotmail.com> ha scritto nel messaggio
news:%23NV38FZXEHA.952@.TK2MSFTNGP10.phx.gbl...
> We have a sql authenticated login that we would like to change pwd through
a
> script and at the same time , let us know what the pwd is in a text file
> that would be located at some share. We need to change pwd every week or 2
> for security purposes.. Can we have a job that we can run every 2 weeks
and
> at the same time, write the new pwd to a text file.. It can be a random
> complex pwd... Doesnt matter.. Thanks..Using SQL 2000
>
you can write a Transact-SQL job like
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'change pwd') > 0
PRINT N'The job "change pwd" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'change pwd', @.owner_login_name = N'sa', @.description = N'No
description available.', @.category_name = N'[Uncategorized (Local)]',
@.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'change PWD', @.command = N'DECLARE @.newPwd VARCHAR(128)
SELECT @.newPwd = CONVERT(VARCHAR(20) , CONVERT ( INT, getdate()))
SELECT @.newPwd = USER_NAME() + @.newPwd + CONVERT(VARCHAR(20) , RAND(CONVERT
( INT, getdate())))
SELECT @.newPwd = @.newPwd + REVERSE ( @.newPwd )
SELECT @.newPwd AS [NEW PASSWORD]
EXEC sp_password @.new = @.newPwd
, @.loginame = ''roby''
', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'c:\newpwd.txt',
@.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
which writes to c:\newpwd.txt' file the new generated password... the logic
for password creation is up to you =;-D
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Hassan
"Hassan" <fatima_ja@.hotmail.com> ha scritto nel messaggio
news:%23NV38FZXEHA.952@.TK2MSFTNGP10.phx.gbl...
> We have a sql authenticated login that we would like to change pwd through
a
> script and at the same time , let us know what the pwd is in a text file
> that would be located at some share. We need to change pwd every week or 2
> for security purposes.. Can we have a job that we can run every 2 weeks
and
> at the same time, write the new pwd to a text file.. It can be a random
> complex pwd... Doesnt matter.. Thanks..Using SQL 2000
>
you can write a Transact-SQL job like
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'change pwd') > 0
PRINT N'The job "change pwd" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'change pwd', @.owner_login_name = N'sa', @.description = N'No
description available.', @.category_name = N'[Uncategorized (Local)]',
@.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = N'change PWD', @.command = N'DECLARE @.newPwd VARCHAR(128)
SELECT @.newPwd = CONVERT(VARCHAR(20) , CONVERT ( INT, getdate()))
SELECT @.newPwd = USER_NAME() + @.newPwd + CONVERT(VARCHAR(20) , RAND(CONVERT
( INT, getdate())))
SELECT @.newPwd = @.newPwd + REVERSE ( @.newPwd )
SELECT @.newPwd AS [NEW PASSWORD]
EXEC sp_password @.new = @.newPwd
, @.loginame = ''roby''
', @.database_name = N'master', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'c:\newpwd.txt',
@.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
which writes to c:\newpwd.txt' file the new generated password... the logic
for password creation is up to you =;-D
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Ansi SQL92 question

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?Versions 2000 SP4 and 2005 SP1|||dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Yes, implicit join sytax.

I believe it's a portability issue for me accross database vendors.
It's easier to write the same sql accross vendors. But if development
want to mix and match join syntax accross vendors and products it's
fine with me. As long as it doesn't effect performance.

So for Mssql there is no performance impact for using an implict join
as opposed to the SQL92 standard outer join syntax?|||On Feb 1, 11:14 am, Serge Rielau <srie...@.ca.ibm.comwrote:

Quote:

Originally Posted by

dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

>
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).|||dunleav1 (jmd@.dunleavyenterprises.com) writes:

Quote:

Originally Posted by

I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

In SQL Server, no.

And I would find it difficult to justify to go through all code and
change it to use the newer syntax. (Note that the SQL-89 syntax is
still very much valid.)

However, I tend to rewrite into the newer syntax when I work with old
code, since I find the newer syntax much easier to read and work with.

Quote:

Originally Posted by

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).

If you on the other hand have lots of code with *= int, there is
all reason to rewrite it. *= is deprecated in SQL 2005, and works
only in compatibility mode 80.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I need to justify changing the code to the new standard. Is there any performance issue related to using the old syntax that are documented? <<

Performance is not the real problem ..

Quote:

Originally Posted by

Quote:

Originally Posted by

>Are there any other issues that I use to justify a code upgrade? <<

The old OUTER JOIN syntaxes are not portable, and are being deprecated
by vendors. It does not work the same way in Sybase, SQL Server,
Oracle, Informix and Centura among other products. It is also very
limited and you will probably find that you can re-write old code to
great advantage. Here is a cut&paste on the details:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;