Here's a tutorial site I like.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
If you get this error and all else is as it should be, try this.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
Run this and then the TestSQL output to make sure you've really got the correct Profile name.
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
,'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + name + ''',
@recipients = ''me@test.com'',
@subject = ''Test'',
@body = ''Message'',
@body_format = ''HTML'';' AS TestSQL
FROM [msdb].[dbo].[sysmail_profile]
Msg 229 … EXECUTE permission was denied on the object ‘xp_cmdshell’
–xp_cmdshell 'dir e:\mssql\reports'
–Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
–The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
EXEC sp__cmdshell 'dir e:\mssql\reports'
Works!
Here's the procedure:
CREATE PROCEDURE [dbo].[sp__cmdshell] (
@vcCmd VARCHAR(8000),
@bNoOutput bit = 0
)
AS
SET nocount ON
DECLARE @RC INT
IF @bNoOutput = 0
EXEC @RC = master.dbo.xp_cmdshell @vcCmd
ELSE
EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output
IF @RC <> 0 OR @@ERROR <> 0
PRINT 'ERROR: ' + @vcCmd
RETURN @RC
GO
You probably also need to give permissions to a login or a role to run the new procedure.
GRANT EXECUTE ON [dbo].[sp__cmdshell] TO [db_executor]
Create a linked server to a local directory path
Here's what I did to successfully set up a linked server to a directory on a SQL 2000 or SQL 2005 server.
/* Remove any previous references to the linked server */
USE [master]
GO
/****** Object: LinkedServer [LOCAL_PATH] Script Date: 01/06/2009 14:04:46 ******/
EXEC master.dbo.sp_dropserver @server=N'LOCAL_PATH', @droplogins='droplogins'
GO
EXEC sp_addlinkedserver 'LOCAL_PATH',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\MSSQL\Reports', NULL, 'Text'
GO
EXEC sp_tables_ex
@table_server='LOCAL_PATH'
—
— If you get this error
—
—
–OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH" returned message "Unspecified error".
–Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
–Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH".
–Simply log onto the server as the same account that the sql services run as.
–Then under my computer properties changes the TEMP and TMP environment variables to something like
C:\temp
or
C:\TempSQL
–which should have security permissions changed to allow local users to have RWC permissions.
You might also lookup this on the Microsoft site.
PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet