How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>

How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>sql

How do I identify dependencies before adding a column to a table?

I need to add a new column to a frequently used table in Production.
I want to make sure that the addition of the column does not break any
existing code in stored procs or triggers.
I can run sp_depends to identify potential problems but I believe this
system procedure is not entirely reliable.
Is the best approach a text search of sysobjects/syscomments to identify
dependencies? And other than "SELECT *," what are some things that might
break that I should be looking for?
Thanks
Dave> And other than "SELECT *," what are some things that might
> break that I should be looking for?
Off the top of my head, there is at least one other thing. The following
will now fail with incorrect number of columns specified:
INSERT table VALUES(blah, blah, blah)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In trigger code, the function columns_updated() is positional.
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||Sp_depends only contains info about objects that existed when you created
the sp... So it is possible that sp_depends does NOT show all sp
dependencies... I generally script out all sps and do a find...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||> In trigger code, the function columns_updated() is positional.
True. Though for the benefit of the original poster, code using
columns_updated() would only be affected if you used Enterprise Manager to
inject the column in the middle of the table. If you use ALTER TABLE ...
ADD COLUMN, the ordinal_position is next, so that existing code for
columns_updated should not have any effect...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks to everyone who responded.
I created a stored proc to search the code for potential problems.
This approach helped me find some things that sp_depends did not.
For anyone else who may face this problem, I include the proc below.
Thanks again.
Dave
USE admin
GO
--sample call:
--EXEC checkdepends 'mls', 'select *'
--dbo.checkdepends.PRC
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[checkdepends]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[checkdepends]
GO
CREATE PROC checkdepends
@.objname varchar(128) --object on which to identify dependencies (e.g.,
table name)
,@.str varchar(60) -- string used to search for dependencies (e.g., SELECT
* )
AS
DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
,@.c int -- record counter for coursor
,@.i int -- affected records counter
SET @.c = 0
SET @.i = 0
SET @.objname = '%' + @.objname + '%'
SET @.str = '%' + @.str + '%'
SET NOCOUNT ON
--build a table to hold the list of referencing objects (i.e., any db
object that references the target object #objname)
IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
DROP TABLE #refs
CREATE TABLE #refs
(
objname varchar(128)
,objtype varchar(24)
)
--grab all of the objects that reference the target object in their code
INSERT INTO #refs
SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id WHERE
text LIKE @.objname ORDER BY xtype
SELECT @.i= count(*)
FROM #refs
PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
PRINT ''
--create a table to hold the referencing object's code
IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
DROP TABLE #text
CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
--create a cursor to examine each referencing object in detail
DECLARE curs CURSOR
FOR
SELECT objname, objtype
FROM #refs
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs
INTO @.objname, @.objtype
WHILE @.@.fetch_status = 0
BEGIN
-- Add the proc code to the table (1 line of code = 1 record)
INSERT #text
EXEC sp_helptext @.objname
--check for the presence of search string within the referencing object's
code
SELECT @.i= count(*)
FROM #text
WHERE obj_text LIKE @.str
IF @.i > 0
BEGIN
PRINT
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++'
PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
PRINT ''
-- Select from the lines of code that have the string we are looking for
SELECT *
FROM #text
WHERE obj_text LIKE @.str
--empty the table to get ready for another proc
TRUNCATE TABLE #text
END
--increment the counter
SET @.c=@.c+1
FETCH NEXT FROM curs
INTO @.objname, @.objtype
END
CLOSE curs
DEALLOCATE curs|||Note that their still might be another problem, and that is if you have a
stored procedure > 8kb, a column or table name might actually start at
character 7995 and end at character 8012, which means that the result could
be split across separate columns in syscomments.
Might be safer to script the procedures, functions etc. and do a brute force
file-based search instead of using syscomments.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DaveF" <davef@.comcast.net> wrote in message
news:uG1MTR1wDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Thanks to everyone who responded.
> I created a stored proc to search the code for potential problems.
> This approach helped me find some things that sp_depends did not.
> For anyone else who may face this problem, I include the proc below.
> Thanks again.
> Dave
>
> USE admin
> GO
> --sample call:
> --EXEC checkdepends 'mls', 'select *'
> --dbo.checkdepends.PRC
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id => object_id(N'[dbo].[checkdepends]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[checkdepends]
> GO
> CREATE PROC checkdepends
> @.objname varchar(128) --object on which to identify dependencies (e.g.,
> table name)
> ,@.str varchar(60) -- string used to search for dependencies (e.g.,
SELECT
> * )
> AS
> DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
> ,@.c int -- record counter for coursor
> ,@.i int -- affected records counter
> SET @.c = 0
> SET @.i = 0
> SET @.objname = '%' + @.objname + '%'
> SET @.str = '%' + @.str + '%'
> SET NOCOUNT ON
> --build a table to hold the list of referencing objects (i.e., any db
> object that references the target object #objname)
> IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
> DROP TABLE #refs
> CREATE TABLE #refs
> (
> objname varchar(128)
> ,objtype varchar(24)
> )
>
> --grab all of the objects that reference the target object in their code
> INSERT INTO #refs
> SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id
WHERE
> text LIKE @.objname ORDER BY xtype
> SELECT @.i= count(*)
> FROM #refs
> PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
> PRINT ''
>
> --create a table to hold the referencing object's code
> IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
> DROP TABLE #text
> CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
>
> --create a cursor to examine each referencing object in detail
> DECLARE curs CURSOR
> FOR
> SELECT objname, objtype
> FROM #refs
> FOR READ ONLY
> OPEN curs
>
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> WHILE @.@.fetch_status = 0
> BEGIN
> -- Add the proc code to the table (1 line of code = 1 record)
> INSERT #text
> EXEC sp_helptext @.objname
> --check for the presence of search string within the referencing
object's
> code
> SELECT @.i= count(*)
> FROM #text
> WHERE obj_text LIKE @.str
> IF @.i > 0
> BEGIN
> PRINT
>
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++'
> PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
> PRINT ''
> -- Select from the lines of code that have the string we are looking
for
> SELECT *
> FROM #text
> WHERE obj_text LIKE @.str
> --empty the table to get ready for another proc
> TRUNCATE TABLE #text
> END
> --increment the counter
> SET @.c=@.c+1
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> END
>
> CLOSE curs
> DEALLOCATE curs
>
>
>|||> be split across separate columns in syscomments.
Err, separate rows/tuples, sorry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||hello Dave,
I think the answer to your problem is simple. Build it. If
you do a complete build of all your source any errors that
your change could cause will be raised. Sounds easy when I
say it like this, however if you check out www.dbghost.com
it show how this can be made very simple.
All other code (exe,dll,asp) is commonly built to find
errors due to dependancy. Database code isn't (generally).
We would like to change this and take the guess work out
of making changes to your database code.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>I need to add a new column to a frequently used table in
Production.
>I want to make sure that the addition of the column does
not break any
>existing code in stored procs or triggers.
>I can run sp_depends to identify potential problems but I
believe this
>system procedure is not entirely reliable.
>Is the best approach a text search of
sysobjects/syscomments to identify
>dependencies? And other than "SELECT *," what are some
things that might
>break that I should be looking for?
>Thanks
>Dave
>
>.
>

How do I get Copy Database Wizard to work?

This is SO frustrating. Go through the wizard selecting the db's I want to pull down from production, gives me an option to get logins (great), but only gives option to pull down stored procedures in Master db (I am a DB owner on these database btw), when I execute it creates the database, and the logins, but doesn't pull down any of the tables or the stored procedures or views unless they were ones I created recently. It's like theres some wierd permissions thing, but like I said, I'm a DB on these databases.

Try using Copy Database Wizard (CDW) in the Community Technology Preview for SP2. It has not released yet, but check www.microsoft.com/sql/ for it in the coming months. Many issues were fixed including areas like login/user SID orphaning, permissions and roles. It is hard to tell if we fixed your specific issue, although I have not seen cases where tables, views or SPs inside the database being transferred did not copy in the way you describe. There was also an issue prior to SP2 of trying to copy objects which were already in the model database on the destination server instance.

If you see the problem after trying the CTP, please open a defect against it and include:

1. sufficient T-SQL scripting or attach the .mdf file of a minimal case.
2. copy/paste the Summary page just before the transfer begins with the details of the run.
3. any relevant error lines from the Windows Event Application Log (just the error ones is fine).
4. the <MyDocumentsAndSettings>\Local Settings\Application Data\Microsoft\SQL Server\Smo\TransferDump*.sql files. There will be one file for the Extra Server Objects, and optionally one file for each database if the Smo method of transfer was used.

Hope this helps,

Ed Dudenhoefer
Microsoft SQL Server

How do I get Copy Database Wizard to work?

This is SO frustrating. Go through the wizard selecting the db's I want to pull down from production, gives me an option to get logins (great), but only gives option to pull down stored procedures in Master db (I am a DB owner on these database btw), when I execute it creates the database, and the logins, but doesn't pull down any of the tables or the stored procedures or views unless they were ones I created recently. It's like theres some wierd permissions thing, but like I said, I'm a DB on these databases.

Try using Copy Database Wizard (CDW) in the Community Technology Preview for SP2. It has not released yet, but check www.microsoft.com/sql/ for it in the coming months. Many issues were fixed including areas like login/user SID orphaning, permissions and roles. It is hard to tell if we fixed your specific issue, although I have not seen cases where tables, views or SPs inside the database being transferred did not copy in the way you describe. There was also an issue prior to SP2 of trying to copy objects which were already in the model database on the destination server instance.

If you see the problem after trying the CTP, please open a defect against it and include:

1. sufficient T-SQL scripting or attach the .mdf file of a minimal case.
2. copy/paste the Summary page just before the transfer begins with the details of the run.
3. any relevant error lines from the Windows Event Application Log (just the error ones is fine).
4. the <MyDocumentsAndSettings>\Local Settings\Application Data\Microsoft\SQL Server\Smo\TransferDump*.sql files. There will be one file for the Extra Server Objects, and optionally one file for each database if the Smo method of transfer was used.

Hope this helps,

Ed Dudenhoefer
Microsoft SQL Server