Applying Service Packs

Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?I always put them on test machines first for a month or so. Enough time that some applications have had a testing cycle, anyway. If there are no major complaints, the first production guinea pig is selected, and watched for a week. If no major problems come up, then the rest of the machines get it. never trust Microsoft. They are human, too...well, half human.|||That's the way to do it. We have dev, test, and production environments. Dev gets the release 1st, and if no one screams after a couple of weeks, we apply it to test. Again, the absence of any wailing and gnashing of teeth means we move to production.

Plus, an added benefit is that you get to practice the install in case something goes horribly wrong (oh my ... would that ever happen?)|||Definitely agree with the two previous posters. In addition, read the release notes, particularly if you have a clustered environment or one where you use replication (upgrades MUST be done in a certain order when replication is involved)

Lempster|||Thank you all!! Pretty much thought so, but just wanted to see how others were handling it. :)

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

API for controlling replication?

Rather than having merge replication performed on a schedule, I need to
programmatically invoke it. Is this possible, and where can I find
documentation about how to do it?
David
That depends on what you are trying to accomplish.
Some people use Windows Synchronization Manager which can be run on users
laptops. It is a little difficult to deploy and doesn't really support SQL
Authentication (it does, but the user will be prompted to enter the password
each time, which makes it impractical). It has limited scheduling.
Then there is the replication activeX controls, which is great, however you
will have to code your own scheduler here. Another problem with it and WSM
is that you can't set many of the properties on the agents.
WSM and ActiveX controls do support Interactive Conflict resolution.
Finally you can use ado to run sp_startjob, which may be your best option as
it is feature rich..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David" <David@.discussions.microsoft.com> wrote in message
news:4E8C6EAB-D8E2-4A30-A393-49D9E8786AAE@.microsoft.com...
> Rather than having merge replication performed on a schedule, I need to
> programmatically invoke it. Is this possible, and where can I find
> documentation about how to do it?
> David
>