Error 8985 cannot locate file xxx in sysfiles

I am attempting to shrink a datafile (I have 2 primary data files) when I
receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.
I have repeatedly verified the data file name and even did a 'cut n paste'
into a query in qa dbcc shrinkfile with the same results. I have no
problem shrinking the second file and the log file through either method.
I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem
file. After removing the second datafile I created a new second datafile
in the primary filegroup and am in the process of moving the data to the
new file. Hopefully I will be able to remove the problem file afterward.
Although I suspect this will still not work because I will have to use the
logical file name and not the number.
Any help will be greatly appreciated!
Thanks
Message posted via http://www.sqlmonster.com
You did a cut and paste from where? What column of what
query? Generally the error is due to referencing the
physical file name and not the logical file name. It's hard
to tell as you example is using the file id which is fine as
well. Also, make sure you are in the correct database when
getting the logical file name or id and when executing the
shrinkfile. If you are selecting from sysfiles or executing
sp_helpdb to get the name, it applies to the current
database only. If it still fails and you aren't using
fileid, try using that instead. It could be an issue with
the logical file name not following the rules for
identifiers.
-Sue
On Wed, 02 Mar 2005 19:49:01 GMT, "andy williams via
SQLMonster.com" <forum@.SQLMonster.com> wrote:

>I am attempting to shrink a datafile (I have 2 primary data files) when I
>receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.
>I have repeatedly verified the data file name and even did a 'cut n paste'
>into a query in qa dbcc shrinkfile with the same results. I have no
>problem shrinking the second file and the log file through either method.
>I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem
>file. After removing the second datafile I created a new second datafile
>in the primary filegroup and am in the process of moving the data to the
>new file. Hopefully I will be able to remove the problem file afterward.
>Although I suspect this will still not work because I will have to use the
>logical file name and not the number.
>Any help will be greatly appreciated!
>Thanks

Error 8985 cannot locate file xxx in sysfiles

I am attempting to shrink a datafile (I have 2 primary data files) when I
receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.
I have repeatedly verified the data file name and even did a 'cut n paste'
into a query in qa dbcc shrinkfile with the same results. I have no
problem shrinking the second file and the log file through either method.
I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem
file. After removing the second datafile I created a new second datafile
in the primary filegroup and am in the process of moving the data to the
new file. Hopefully I will be able to remove the problem file afterward.
Although I suspect this will still not work because I will have to use the
logical file name and not the number.
Any help will be greatly appreciated!
Thanks
Message posted via http://www.droptable.comYou did a cut and paste from where? What column of what
query? Generally the error is due to referencing the
physical file name and not the logical file name. It's hard
to tell as you example is using the file id which is fine as
well. Also, make sure you are in the correct database when
getting the logical file name or id and when executing the
shrinkfile. If you are selecting from sysfiles or executing
sp_helpdb to get the name, it applies to the current
database only. If it still fails and you aren't using
fileid, try using that instead. It could be an issue with
the logical file name not following the rules for
identifiers.
-Sue
On Wed, 02 Mar 2005 19:49:01 GMT, "andy williams via
droptable.com" <forum@.droptable.com> wrote:

>I am attempting to shrink a datafile (I have 2 primary data files) when I
>receive this error message 'Error 8985 cannot locate file xxx in sysfiles'.
>I have repeatedly verified the data file name and even did a 'cut n paste'
>into a query in qa dbcc shrinkfile with the same results. I have no
>problem shrinking the second file and the log file through either method.
>I even ran dbcc shrinkfile (3, emptyfile) moving all data to the problem
>file. After removing the second datafile I created a new second datafile
>in the primary filegroup and am in the process of moving the data to the
>new file. Hopefully I will be able to remove the problem file afterward.
>Although I suspect this will still not work because I will have to use the
>logical file name and not the number.
>Any help will be greatly appreciated!
>Thanks

Error 823

I am attempting to add a windows login to be able to access a database. Whe
n
I try to do this I receive the following error.
Error 823: I/O Error (bad page id) detected during read at offset
0x00000000ae6000 in file e:\program files\microsoft sql
server\mssql\master.mdf
I also receive the following when I run dbcc checkdb on the master database
Server: Msg 8928, Level 16, State 6, Line 1
Object ID 0, index ID 0: Page (1:1331) could not be processed. See other
errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1395). The PageId in the
page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:1332) could not be processed. See other
errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 6, index ID 0: Page (1:1395) could not be processed. See other
errors for details.
Server: Msg 2575, Level 16, State 1, Line 1
IAM page (1:1331) is pointed to by the next pointer of IAM page (0:0) object
ID 40 index ID 2 but was not detected in the scan.
Server: Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 40, index ID 2 due to invalid
allocation (IAM) page(s).
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:1331) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 3407872, index ID 512, page ID (1:1331). The PageId
in the page header = (1:3126).
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:1330) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1111693545, index ID 0, page ID (1:1332). The PageId
in the page header = (0:0).
DBCC results for 'master'.
CHECKDB found 1 allocation errors and 1 consistency errors not associated
with any single object.
DBCC results for 'sysobjects'.
There are 1278 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 103 rows in 4 pages for object 'sysindexes'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysindexes' (object ID 2).
DBCC results for 'syscolumns'.
There are 4958 rows in 94 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2232 rows in 1213 pages for object 'syscomments'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'syscomments' (object ID 6).
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 789 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 16 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5445 rows in 31 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 14 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 46 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 6 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3883 rows in 166 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 3 rows in 1 pages for object 'sysservers'.
CHECKDB found 1 allocation errors and 1 consistency errors in table
'sysservers' (object ID 40).
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 28 rows in 6 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 1 allocation errors and 1 consistency errors in table '(Object
ID 3407872)' (object ID 3407872).
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object
ID 538976288)' (object ID 538976288).
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object
ID 1111693545)' (object ID 1111693545).
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 4 allocation errors and 6 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
How can I correct these errors?Before fixing it, you should check your hardware and look
for issues with your disk subsystem. Check the event logs on
the server and look for any other system errors that may
have occurred around the same time.
After fixing any hardware issues, then you would need to
restore the last known good backup of the master database.
-Sue
On Wed, 29 Mar 2006 10:08:01 -0800, rleamey
<rleamey@.discussions.microsoft.com> wrote:

>I am attempting to add a windows login to be able to access a database. Wh
en
>I try to do this I receive the following error.
>Error 823: I/O Error (bad page id) detected during read at offset
>0x00000000ae6000 in file e:\program files\microsoft sql
>server\mssql\master.mdf
>I also receive the following when I run dbcc checkdb on the master database
>Server: Msg 8928, Level 16, State 6, Line 1
>Object ID 0, index ID 0: Page (1:1331) could not be processed. See other
>errors for details.
>Server: Msg 8909, Level 16, State 1, Line 1
>Table error: Object ID 0, index ID 0, page ID (1:1395). The PageId in the
>page header = (0:0).
>Server: Msg 8928, Level 16, State 1, Line 1
>Object ID 2, index ID 255: Page (1:1332) could not be processed. See other
>errors for details.
>Server: Msg 8928, Level 16, State 1, Line 1
>Object ID 6, index ID 0: Page (1:1395) could not be processed. See other
>errors for details.
>Server: Msg 2575, Level 16, State 1, Line 1
>IAM page (1:1331) is pointed to by the next pointer of IAM page (0:0) objec
t
>ID 40 index ID 2 but was not detected in the scan.
>Server: Msg 7965, Level 16, State 1, Line 1
>Table error: Could not check object ID 40, index ID 2 due to invalid
>allocation (IAM) page(s).
>Server: Msg 8906, Level 16, State 1, Line 1
>Page (1:1331) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1)
,
>but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
>0_PCT_FULL'.
>Server: Msg 8909, Level 16, State 1, Line 1
>Table error: Object ID 3407872, index ID 512, page ID (1:1331). The PageId
>in the page header = (1:3126).
>Server: Msg 8906, Level 16, State 1, Line 1
>Page (1:1330) in database ID 1 is allocated in the SGAM (1:3) and PFS (1:1)
,
>but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
>0_PCT_FULL'.
>Server: Msg 8909, Level 16, State 1, Line 1
>Table error: Object ID 1111693545, index ID 0, page ID (1:1332). The PageId
>in the page header = (0:0).
>DBCC results for 'master'.
>CHECKDB found 1 allocation errors and 1 consistency errors not associated
>with any single object.
>DBCC results for 'sysobjects'.
>There are 1278 rows in 23 pages for object 'sysobjects'.
>DBCC results for 'sysindexes'.
>There are 103 rows in 4 pages for object 'sysindexes'.
>CHECKDB found 0 allocation errors and 1 consistency errors in table
>'sysindexes' (object ID 2).
>DBCC results for 'syscolumns'.
>There are 4958 rows in 94 pages for object 'syscolumns'.
>DBCC results for 'systypes'.
>There are 26 rows in 1 pages for object 'systypes'.
>DBCC results for 'syscomments'.
>There are 2232 rows in 1213 pages for object 'syscomments'.
>CHECKDB found 0 allocation errors and 1 consistency errors in table
>'syscomments' (object ID 6).
>DBCC results for 'sysfiles1'.
>There are 2 rows in 1 pages for object 'sysfiles1'.
>DBCC results for 'syspermissions'.
>There are 789 rows in 4 pages for object 'syspermissions'.
>DBCC results for 'sysusers'.
>There are 16 rows in 1 pages for object 'sysusers'.
>DBCC results for 'sysproperties'.
>There are 0 rows in 0 pages for object 'sysproperties'.
>DBCC results for 'sysdepends'.
>There are 5445 rows in 31 pages for object 'sysdepends'.
>DBCC results for 'sysreferences'.
>There are 0 rows in 0 pages for object 'sysreferences'.
>DBCC results for 'sysfulltextcatalogs'.
>There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
>DBCC results for 'sysfulltextnotify'.
>There are 0 rows in 0 pages for object 'sysfulltextnotify'.
>DBCC results for 'sysdatabases'.
>There are 14 rows in 1 pages for object 'sysdatabases'.
>DBCC results for 'sysxlogins'.
>There are 46 rows in 1 pages for object 'sysxlogins'.
>DBCC results for 'sysdevices'.
>There are 6 rows in 1 pages for object 'sysdevices'.
>DBCC results for 'sysmessages'.
>There are 3883 rows in 166 pages for object 'sysmessages'.
>DBCC results for 'sysconfigures'.
>There are 38 rows in 1 pages for object 'sysconfigures'.
>DBCC results for 'sysservers'.
>There are 3 rows in 1 pages for object 'sysservers'.
>CHECKDB found 1 allocation errors and 1 consistency errors in table
>'sysservers' (object ID 40).
>DBCC results for 'syslanguages'.
>There are 33 rows in 3 pages for object 'syslanguages'.
>DBCC results for 'syscharsets'.
>There are 114 rows in 33 pages for object 'syscharsets'.
>DBCC results for 'sysaltfiles'.
>There are 28 rows in 6 pages for object 'sysaltfiles'.
>DBCC results for 'sysfilegroups'.
>There are 1 rows in 1 pages for object 'sysfilegroups'.
>CHECKDB found 1 allocation errors and 1 consistency errors in table '(Objec
t
>ID 3407872)' (object ID 3407872).
>DBCC results for 'spt_monitor'.
>There are 1 rows in 1 pages for object 'spt_monitor'.
>DBCC results for 'spt_values'.
>There are 730 rows in 7 pages for object 'spt_values'.
>DBCC results for 'spt_fallback_db'.
>There are 0 rows in 0 pages for object 'spt_fallback_db'.
>DBCC results for 'spt_fallback_dev'.
>There are 0 rows in 0 pages for object 'spt_fallback_dev'.
>DBCC results for 'spt_fallback_usg'.
>There are 0 rows in 0 pages for object 'spt_fallback_usg'.
>CHECKDB found 1 allocation errors and 0 consistency errors in table '(Objec
t
>ID 538976288)' (object ID 538976288).
>DBCC results for 'spt_provider_types'.
>There are 25 rows in 1 pages for object 'spt_provider_types'.
>CHECKDB found 0 allocation errors and 1 consistency errors in table '(Objec
t
>ID 1111693545)' (object ID 1111693545).
>DBCC results for 'spt_datatype_info_ext'.
>There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
>DBCC results for 'MSreplication_options'.
>There are 2 rows in 1 pages for object 'MSreplication_options'.
>DBCC results for 'spt_datatype_info'.
>There are 36 rows in 1 pages for object 'spt_datatype_info'.
>DBCC results for 'spt_server_info'.
>There are 29 rows in 1 pages for object 'spt_server_info'.
>CHECKDB found 4 allocation errors and 6 consistency errors in database
>'master'.
>repair_allow_data_loss is the minimum repair level for the errors found by
>DBCC CHECKDB (master ).
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>How can I correct these errors?|||Would there be anyway to fix it if we did not have a good copy of the backup
?
"Sue Hoegemeier" wrote:

> Before fixing it, you should check your hardware and look
> for issues with your disk subsystem. Check the event logs on
> the server and look for any other system errors that may
> have occurred around the same time.
> After fixing any hardware issues, then you would need to
> restore the last known good backup of the master database.
> -Sue
> On Wed, 29 Mar 2006 10:08:01 -0800, rleamey
> <rleamey@.discussions.microsoft.com> wrote:
>
>|||Restoring is the best option. If that's not an option, you
can rebuild master using the rebuildm.exe
You can find more information on how to do this in the books
online topic: Rebuilding the master Database
-Sue
On Thu, 30 Mar 2006 05:09:02 -0800, rleamey
<rleamey@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Would there be anyway to fix it if we did not have a good copy of the backu
p?
>"Sue Hoegemeier" wrote:
>

Error 7391 running distributed transaction

When attempting to run a distributed transaction I get the following
error:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have two servers.
ServerA
Windows 2003
SQL Server 2000 SP3a
MDAC 2.8 RTM
ServerB
Windows 2000
SQL Server 2000 SP3a
MDAC 2.8 RTM
When I open Query Analyzer on ServerA and execute the following
statement, I get the error.
begin distributed transaction
exec ServerB.master.dbo.sp_helpdb
I've already enabled network access for Component Services:
http://support.microsoft.com/default.aspx?kbid=329332
Didn't fix the issue.
Any ideas?
Thanks,
MarkMake sure that the DTC Service is running on both machines as well..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Newton" <mark.newton@.bdsmarketing.com> wrote in message
news:0ia5sv0ifue6po44fbc1j3aipvpas38put@.4ax.com...
> When attempting to run a distributed transaction I get the following
> error:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> I have two servers.
> ServerA
> Windows 2003
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> ServerB
> Windows 2000
> SQL Server 2000 SP3a
> MDAC 2.8 RTM
> When I open Query Analyzer on ServerA and execute the following
> statement, I get the error.
> begin distributed transaction
> exec ServerB.master.dbo.sp_helpdb
>
> I've already enabled network access for Component Services:
> http://support.microsoft.com/default.aspx?kbid=329332
> Didn't fix the issue.
> Any ideas?
> Thanks,
> Mark|||MSDTC is running on both machines.
On Tue, 25 Nov 2003 05:45:42 -0500, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>Make sure that the DTC Service is running on both machines as well..

Error 6522 Trying to Run Assembly from database

Hi,
I am attempting to load an assembly that has been stored in a table as a byte array. I have created a c# class called AssemblyLoader that takes in 2 parameters, the assembly name and the parameters for the assembly (just a query string).
From this it returns the assembly byte array using a simple sql statement within the class using the assembly name to select the assembly bytes to return from the database.

The returned byte array of the assembly is loaded using:
Assembly assembly = Assembly.Load(assemblyBytes);

This seems to be the line that SQL Server 2005 is erroring on. Do I need to add some extra assemblies to the database and if so which ones.

Error:
Msg 6522, Level 16, State 1, Procedure ClientInterface, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ClientInterface':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at Reflector.runQuery(String query, String parameter)
Thanks for your help.

SQL Server does not allow you to load an assembly from file, nor byte array. You can only call Assembly.Load(), in which case the assembly has to be catalogued in the database.

Niels|||Cheers Niels
I am using Assembly.Load with the Full name of the assembly and it works fine.
Thanks|||So does this mean that you can create a Class Library project, compile it and then simply place the assembly inside SQL 2005 to get access to external objects?
Just to confirm, you are not limited to placing the output of SQL Server Project inside SQL Server 2005?

Thank you,

Lubomir|||Thats pretty much all I have done. I have created 3 seperate Class Library Projects that each extend a common interface so that they all have the method

public static string execute(string data)

and are compiled and added to the database. They are loaded depending on the query I pass in and they go off and get data from different interfaces ie, LDAP, HTTP SOAP etc... and return me a simple string.

N
|||Lubomir,
A SQL CLR Project is just a project type in VS, which makes it easier for the developer to create and deploy assemblies to SQL Server. Under the cover the SQL CLR Project is just calling CREATE ASSEMBLY ... etc, in order to deploy.

Niels

Error 6522 Trying to Run Assembly from database

Hi,
I am attempting to load an assembly that has been stored in a table as a byte array. I have created a c# class called AssemblyLoader that takes in 2 parameters, the assembly name and the parameters for the assembly (just a query string).
From this it returns the assembly byte array using a simple sql statement within the class using the assembly name to select the assembly bytes to return from the database.

The returned byte array of the assembly is loaded using:
Assembly assembly = Assembly.Load(assemblyBytes);

This seems to be the line that SQL Server 2005 is erroring on. Do I need to add some extra assemblies to the database and if so which ones.

Error:
Msg 6522, Level 16, State 1, Procedure ClientInterface, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ClientInterface':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at Reflector.runQuery(String query, String parameter)
Thanks for your help.

SQL Server does not allow you to load an assembly from file, nor byte array. You can only call Assembly.Load(), in which case the assembly has to be catalogued in the database.

Niels
|||Cheers Niels
I am using Assembly.Load with the Full name of the assembly and it works fine.
Thanks
|||So does this mean that you can create a Class Library project, compile it and then simply place the assembly inside SQL 2005 to get access to external objects?
Just to confirm, you are not limited to placing the output of SQL Server Project inside SQL Server 2005?

Thank you,

Lubomir|||Thats pretty much all I have done. I have created 3 seperate Class Library Projects that each extend a common interface so that they all have the method

public static string execute(string data)

and are compiled and added to the database. They are loaded depending on the query I pass in and they go off and get data from different interfaces ie, LDAP, HTTP SOAP etc... and return me a simple string.

N
|||Lubomir,
A SQL CLR Project is just a project type in VS, which makes it easier for the developer to create and deploy assemblies to SQL Server. Under the cover the SQL CLR Project is just calling CREATE ASSEMBLY ... etc, in order to deploy.

Niels

Error 5177 attempting to create database

Compaq server with two channel controler, channel 1 is D:
(67GB)drive channel 2 is E: (67GB). Both RAID 1+0. SQL
does not show the E: drive and generates a 5177 error when
attempting to create database. Operating system on C:
mirrored on two drives.
Operating system Windows 2000 Server, SQL 2000 updated to
SP2. Currently version of Best MAS 500 v6.2 won't work
with SP3.
Problem began when one of the database logfiles which had
been operating for 8 months expanded to consume 50GB of
remaining space on the drive. Attempted to Shrink but
didn't work. Removed the databse and attmepted to
reinstall with data on D: and logs on E: but constantly
run into the error.
Was able to successfully add the database and logs by
keeping both on the D: drive. That's not how we want it
configured.Here's a KB article that may be of help:
http://support.microsoft.com/default.aspx?kbid=239759
I assume that the E partition does have space free, and
you can write to it outside of SQL. Be sure to check the
Event log for possible I/O related problems, as this
could be hardware related. If you see general I/O
errors, you'll probably want to contact your hardware
vendor.
You probably already know this by now, but to avoid the
log eating all of the space, be sure to make regular
backups of the log. A full database backup does NOT
clean out the log file. The easiest way of doing this is
to set up a SQL Maintenance plan to backup the log and
maintain a set history of backups (e.g., 2 weeks worth).
Hope that helps.
>--Original Message--
>Compaq server with two channel controler, channel 1 is
D:
>(67GB)drive channel 2 is E: (67GB). Both RAID 1+0. SQL
>does not show the E: drive and generates a 5177 error
when
>attempting to create database. Operating system on C:
>mirrored on two drives.
>Operating system Windows 2000 Server, SQL 2000 updated
to
>SP2. Currently version of Best MAS 500 v6.2 won't work
>with SP3.
>Problem began when one of the database logfiles which
had
>been operating for 8 months expanded to consume 50GB of
>remaining space on the drive. Attempted to Shrink but
>didn't work. Removed the databse and attmepted to
>reinstall with data on D: and logs on E: but constantly
>run into the error.
>Was able to successfully add the database and logs by
>keeping both on the D: drive. That's not how we want it
>configured.
>.
>

Error 444 in UDF

Has anyone seen this error before?
(Am attempting to make my first UDF so is probably very basic.)
Error 444: Select statements within a function cannot return data to a client
This is the code:
CREATE FUNCTION udfActiveCaseSum
/******************************************************************************
** Eagle User Defined Function
** View Name: udfActiveCaseSum
** Description:
** Author:
** Date:
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -- -- ---
**
*******************************************************************************/
(
@.Other INT,
@.asofdate datetime,
@.CourtId int,
@.PersonID int
)
RETURNS INT
AS
BEGIN
SELECT distinct
ViewOrgPerson.PersonId,
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
--ViewOrgPerson.RoleShort,
CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE 'XERROR' END as RoleShort,
Petition.CourtId,
Court.NameShort,
SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B') AND
Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR') AND
Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V') AND
Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP') AND
Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K') AND
Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR') AND
Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
THEN 1 ELSE 0 END) AS Other
FROM ViewOrgPerson with (noexpand)
INNER JOIN
Agent ON
ViewOrgPerson.OrgId = Agent.OrgId AND
ViewOrgPerson.PersonId = Agent.PersonId AND
-- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
ViewOrgPerson.CourtId = @.CourtId
inner join(
Select max (ViewOrgPerson.RoleCodeId) RoleCodeId , ViewOrgPerson.PersonId from
ViewOrgPerson WITH (NOEXPAND)
group by ViewOrgPerson.PersonId
)TopRole
on
TopRole.PersonId = Agent.PersonId
INNER JOIN
Petition ON Agent.PetitionId = Petition.PetitionId
INNER JOIN
Court ON Petition.CourtId = Court.CourtId and
Petition.CourtId = @.courtID
INNER JOIN
PetitionCode ON Petition.PetitionCodeId = PetitionCode.PetitionCodeId INNER JOIN
Appearance ON Petition.PetitionId = Appearance.PetitionId
and Appearance.EVENTDate >@.asofdate
--GROUP BY ViewOrgPerson.PersonId,
WHERE ViewOrgPerson.PersonId = @.PersonID
/**
ViewOrgPerson.FirstName,
ViewOrgPerson.LastName,
ViewOrgPerson.RoleShort,
TopRole.RoleCodeId,
Petition.CourtId,
Court.NameShort
**/
RETURN @.Other
ENDAndrew,
You have a SELECT statement which returns a resultset inside a scalar
function, can't have that. It would be like each invocation of SUBSTRING()
would return a table.
What type of function do you want to write? A scalar or a table function?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Thanks for the reply! Am not clear on the difference between the two - Basically have a large query that produces a report. Need to add functionality to catch all not listed in an "Other" column - Am trying to use a UDF to keep all the "<> this" and "<> that" code out of the calling proc.|||Change your udf to a table valued function ie..
create func myfun (parameters)
returns table
as
return( select.... )
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:8D4D2A43-0490-40CA-901B-B42DFEEA3BA8@.microsoft.com...
> Has anyone seen this error before?
> (Am attempting to make my first UDF so is probably very basic.)
> Error 444: Select statements within a function cannot return data to a
client
> This is the code:
> CREATE FUNCTION udfActiveCaseSum
>
/***************************************************************************
***
> ** Eagle User Defined Function
> ** View Name: udfActiveCaseSum
> ** Description:
> ** Author:
> ** Date:
>
****************************************************************************
***
> ** Change History
>
****************************************************************************
***
> ** Date: Author: Description:
> ** -- -- ---
> **
>
****************************************************************************
***/
> (
> @.Other INT,
> @.asofdate datetime,
> @.CourtId int,
> @.PersonID int
> )
> RETURNS INT
> AS
> BEGIN
> SELECT distinct
> ViewOrgPerson.PersonId,
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> --ViewOrgPerson.RoleShort,
> CASE TopRole.RoleCodeId WHEN 9 THEN 'MGMT' WHEN 8 THEN 'STAFF' ELSE
'XERROR' END as RoleShort,
> Petition.CourtId,
> Court.NameShort,
>
> SUM(CASE WHEN Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort
NOT IN ('N') AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('N')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('DV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('ES')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('NV')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VD')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('VS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('XS')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('B')
AND
> Petition.DocketPrefix <> 'B' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'N' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'NA' AND PetitionCode.PetitionShort NOT IN ('SR')
AND
> Petition.DocketPrefix <> 'V' AND PetitionCode.PetitionShort NOT IN ('V')
AND
> Petition.DocketPrefix <> 'S' AND PetitionCode.PetitionShort NOT IN ('<>S')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('D')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('E')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('DE')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('EP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'D' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('MD')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('TR')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VA')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VC')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('VP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('XP')
AND
> Petition.DocketPrefix <> 'E' AND PetitionCode.PetitionShort NOT IN ('PH')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('K')
AND
> Petition.DocketPrefix <> 'K' AND PetitionCode.PetitionShort NOT IN ('KR')
AND
> Petition.DocketPrefix <> 'L' AND PetitionCode.PetitionShort NOT IN ('L')
>
> THEN 1 ELSE 0 END) AS Other
> FROM ViewOrgPerson with (noexpand)
> INNER JOIN
> Agent ON
> ViewOrgPerson.OrgId = Agent.OrgId AND
> ViewOrgPerson.PersonId = Agent.PersonId AND
> -- ViewOrgPerson.RoleCodeId = Agent.RoleCodeId and
> ViewOrgPerson.RoleShort IN ( 'LGS', 'LG') AND
> ViewOrgPerson.CourtId = @.CourtId
> inner join(
> Select max (ViewOrgPerson.RoleCodeId) RoleCodeId ,
ViewOrgPerson.PersonId from
> ViewOrgPerson WITH (NOEXPAND)
> group by ViewOrgPerson.PersonId
> )TopRole
> on
> TopRole.PersonId = Agent.PersonId
> INNER JOIN
> Petition ON Agent.PetitionId = Petition.PetitionId
> INNER JOIN
> Court ON Petition.CourtId = Court.CourtId and
> Petition.CourtId = @.courtID
> INNER JOIN
> PetitionCode ON Petition.PetitionCodeId =PetitionCode.PetitionCodeId INNER JOIN
> Appearance ON Petition.PetitionId =Appearance.PetitionId
> and Appearance.EVENTDate >@.asofdate
> --GROUP BY ViewOrgPerson.PersonId,
> WHERE ViewOrgPerson.PersonId = @.PersonID
> /**
> ViewOrgPerson.FirstName,
> ViewOrgPerson.LastName,
> ViewOrgPerson.RoleShort,
> TopRole.RoleCodeId,
> Petition.CourtId,
> Court.NameShort
> **/
> RETURN @.Other
> END
>
>
>|||Is you want to return a table so you can use in a FROM clause of a SELECT
statement, then you want a table valued function. Check out Wayne's post.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew" <anonymous@.discussions.microsoft.com> wrote in message
news:88954E4A-74AF-47AE-8F5C-D30CDD04B627@.microsoft.com...
> Thanks for the reply! Am not clear on the difference between the two -
Basically have a large query that produces a report. Need to add
functionality to catch all not listed in an "Other" column - Am trying to
use a UDF to keep all the "<> this" and "<> that" code out of the calling
proc.

Error 4064 after attempting restore backup?!

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

Even though the usernames and passwords are the same the users will have different SIDs (SQL Server's unique identifiers for users). To get a list of the orphaned users run the following on the database you restored:

EXEC sp_change_users_login 'Report'

You can then run the following command for each orphaned user to fix their relationship to the database:

EXEC sp_change_users_login 'Auto_Fix', 'user'

|||

William, can you help me with this:

I have the same scenario as the original posting, and the orpahned user (Webuser) can be fixed with the following:

1 - EXEC sp_change_users_login 'Auto_Fix', 'webuser'

2- I must then create a table, and give webuser rights to it.

Until I do step 2 the user webuser still cannot select from any tables

How can I simplify this and allow webuser to use the db without the second step.

thanks for any help

regards

David

|||

hi David,

step 1 only fixex membership to the database users... being a database user only grants (again) membership to "public" database role, and usually public members do not have lots of permissions on database objects... acutally public members do not have read/write permissions on database tables at all.. you have to manually handle that, or make the desired/required database user member of other privileged database roles, like db_datareader/db_datawriter.. or, even better, define appropriate database roles to have the desired granularity in object's permissions...

regards

|||Hi
Sorry I did not explain adequately.

My user already has rights to all necessary tables and other objects in the db.

I am having to update a remote server with the latest version if a db, I have

no online link to that server the only possible method is manually

overwriting the mdb and ldb files

After I re-attach the updated file to the server, the user cannot select from any of those objects that they formerly had access to.

I run the sp to deal with the orphaned users issue, but then, the user still cannot select etc, even though I can look into all the object permissions and the user is shown there. So they are not an orphan, and they have explicit permissions, but they cannot see or use any of the database.

The only way I have found to resolve it is to create a new object, a table say, grant the user permissions, then hey presto suddenly they can see that, and more to the point they can see and use all the objects they are supposed to see. Then I delete the newly created table object.

The act of creating a new table and giving them rights has fixed the problem. But it cannot be the right way to do it.

regards
David

Error 4064 after attempting restore backup?!

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

Even though the usernames and passwords are the same the users will have different SIDs (SQL Server's unique identifiers for users). To get a list of the orphaned users run the following on the database you restored:

EXEC sp_change_users_login 'Report'

You can then run the following command for each orphaned user to fix their relationship to the database:

EXEC sp_change_users_login 'Auto_Fix', 'user'

|||

William, can you help me with this:

I have the same scenario as the original posting, and the orpahned user (Webuser) can be fixed with the following:

1 - EXEC sp_change_users_login 'Auto_Fix', 'webuser'

2- I must then create a table, and give webuser rights to it.

Until I do step 2 the user webuser still cannot select from any tables

How can I simplify this and allow webuser to use the db without the second step.

thanks for any help

regards

David

|||

hi David,

step 1 only fixex membership to the database users... being a database user only grants (again) membership to "public" database role, and usually public members do not have lots of permissions on database objects... acutally public members do not have read/write permissions on database tables at all.. you have to manually handle that, or make the desired/required database user member of other privileged database roles, like db_datareader/db_datawriter.. or, even better, define appropriate database roles to have the desired granularity in object's permissions...

regards

|||Hi
Sorry I did not explain adequately.

My user already has rights to all necessary tables and other objects in the db.

I am having to update a remote server with the latest version if a db, I have

no online link to that server the only possible method is manually

overwriting the mdb and ldb files

After I re-attach the updated file to the server, the user cannot select from any of those objects that they formerly had access to.

I run the sp to deal with the orphaned users issue, but then, the user still cannot select etc, even though I can look into all the object permissions and the user is shown there. So they are not an orphan, and they have explicit permissions, but they cannot see or use any of the database.

The only way I have found to resolve it is to create a new object, a table say, grant the user permissions, then hey presto suddenly they can see that, and more to the point they can see and use all the objects they are supposed to see. Then I delete the newly created table object.

The act of creating a new table and giving them rights has fixed the problem. But it cannot be the right way to do it.

regards
David

Error 22022

I'm attempting to create a backup schedule for my SQL
Server 2000, and I have created the job to perform the
backups. But, when I try to run the backup to test it, I
get the following error:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
I have checked, SQLServerAgent is running and if I stop
and start SQLServerAgent I get the same error.
What is wrong and how do I fix it?Some things to check - Are you running SQL Server in fiber
mode (lightweight pooling)? Have you ensured that SQL Agent
is truly running and not hung up in a starting status? Have
you checked your sqlagent.out file?
In addition to lightweight pooling being enabled, Agent can
also get hung up due to a bad mail profile.
-Sue
On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:
>I'm attempting to create a backup schedule for my SQL
>Server 2000, and I have created the job to perform the
>backups. But, when I try to run the backup to test it, I
>get the following error:
>Error 22022: SQLServerAgent is not currently running so it
>cannot be notified of this action.
>I have checked, SQLServerAgent is running and if I stop
>and start SQLServerAgent I get the same error.
>What is wrong and how do I fix it?|||Thanks Sue for your reply. I have checked and SQL Mail is
not configured, but I have checked on an older NT systems
and its SQL Mail is not configured either and the backups
work fine. My current system is Windows 2003 server,
could this be causing the problem? Also, is there a way
to configure MSServerAgent to not bother with mail? I
would rather have it configured so no mail is sent out.
Ciao,
Rob.
>--Original Message--
>Some things to check - Are you running SQL Server in fiber
>mode (lightweight pooling)? Have you ensured that SQL
Agent
>is truly running and not hung up in a starting status?
Have
>you checked your sqlagent.out file?
>In addition to lightweight pooling being enabled, Agent
can
>also get hung up due to a bad mail profile.
>-Sue
>On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
><anonymous@.discussions.microsoft.com> wrote:
>>I'm attempting to create a backup schedule for my SQL
>>Server 2000, and I have created the job to perform the
>>backups. But, when I try to run the backup to test it,
I
>>get the following error:
>>Error 22022: SQLServerAgent is not currently running so
it
>>cannot be notified of this action.
>>I have checked, SQLServerAgent is running and if I stop
>>and start SQLServerAgent I get the same error.
>>What is wrong and how do I fix it?
>.
>|||If you don't set a mail profile for SQL Agent then it won't
do anything with mail.
In terms of SQL Agent, you still need to check if the
service is actually running (vs starting), if your server is
configured for lightweight pooling and you need to check
your sqlagent.out file for any errors or problems in
starting SQL Agent.
-Sue
On Wed, 12 May 2004 10:12:18 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue for your reply. I have checked and SQL Mail is
>not configured, but I have checked on an older NT systems
>and its SQL Mail is not configured either and the backups
>work fine. My current system is Windows 2003 server,
>could this be causing the problem? Also, is there a way
>to configure MSServerAgent to not bother with mail? I
>would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
>>--Original Message--
>>Some things to check - Are you running SQL Server in fiber
>>mode (lightweight pooling)? Have you ensured that SQL
>Agent
>>is truly running and not hung up in a starting status?
>Have
>>you checked your sqlagent.out file?
>>In addition to lightweight pooling being enabled, Agent
>can
>>also get hung up due to a bad mail profile.
>>-Sue
>>On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
>><anonymous@.discussions.microsoft.com> wrote:
>>I'm attempting to create a backup schedule for my SQL
>>Server 2000, and I have created the job to perform the
>>backups. But, when I try to run the backup to test it,
>I
>>get the following error:
>>Error 22022: SQLServerAgent is not currently running so
>it
>>cannot be notified of this action.
>>I have checked, SQLServerAgent is running and if I stop
>>and start SQLServerAgent I get the same error.
>>What is wrong and how do I fix it?
>>.|||Hello Rob,
Did you ever get this problem figured out? I'm having the exact same issue and I have verified that SQL Agent is running but still having the error.
Let me know.
Regards,
-greg
"Rob Simac" wrote:
> Thanks Sue for your reply. I have checked and SQL Mail is
> not configured, but I have checked on an older NT systems
> and its SQL Mail is not configured either and the backups
> work fine. My current system is Windows 2003 server,
> could this be causing the problem? Also, is there a way
> to configure MSServerAgent to not bother with mail? I
> would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
> >--Original Message--
> >Some things to check - Are you running SQL Server in fiber
> >mode (lightweight pooling)? Have you ensured that SQL
> Agent
> >is truly running and not hung up in a starting status?
> Have
> >you checked your sqlagent.out file?
> >In addition to lightweight pooling being enabled, Agent
> can
> >also get hung up due to a bad mail profile.
> >
> >-Sue
> >
> >On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
> ><anonymous@.discussions.microsoft.com> wrote:
> >
> >>I'm attempting to create a backup schedule for my SQL
> >>Server 2000, and I have created the job to perform the
> >>backups. But, when I try to run the backup to test it,
> I
> >>get the following error:
> >>
> >>Error 22022: SQLServerAgent is not currently running so
> it
> >>cannot be notified of this action.
> >>
> >>I have checked, SQLServerAgent is running and if I stop
> >>and start SQLServerAgent I get the same error.
> >>
> >>What is wrong and how do I fix it?
> >
> >.
> >
>

Error 22022

I'm attempting to create a backup schedule for my SQL
Server 2000, and I have created the job to perform the
backups. But, when I try to run the backup to test it, I
get the following error:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
I have checked, SQLServerAgent is running and if I stop
and start SQLServerAgent I get the same error.
What is wrong and how do I fix it?
Hi Rob,
What is the startup account for the SQLAgent ?
What is the account used to create the Job ?
Try changing the Account for Job to SA and then try.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Error 22022

I'm attempting to create a backup schedule for my SQL
Server 2000, and I have created the job to perform the
backups. But, when I try to run the backup to test it, I
get the following error:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
I have checked, SQLServerAgent is running and if I stop
and start SQLServerAgent I get the same error.
What is wrong and how do I fix it?
Some things to check - Are you running SQL Server in fiber
mode (lightweight pooling)? Have you ensured that SQL Agent
is truly running and not hung up in a starting status? Have
you checked your sqlagent.out file?
In addition to lightweight pooling being enabled, Agent can
also get hung up due to a bad mail profile.
-Sue
On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:

>I'm attempting to create a backup schedule for my SQL
>Server 2000, and I have created the job to perform the
>backups. But, when I try to run the backup to test it, I
>get the following error:
>Error 22022: SQLServerAgent is not currently running so it
>cannot be notified of this action.
>I have checked, SQLServerAgent is running and if I stop
>and start SQLServerAgent I get the same error.
>What is wrong and how do I fix it?
|||Thanks Sue for your reply. I have checked and SQL Mail is
not configured, but I have checked on an older NT systems
and its SQL Mail is not configured either and the backups
work fine. My current system is Windows 2003 server,
could this be causing the problem? Also, is there a way
to configure MSServerAgent to not bother with mail? I
would rather have it configured so no mail is sent out.
Ciao,
Rob.

>--Original Message--
>Some things to check - Are you running SQL Server in fiber
>mode (lightweight pooling)? Have you ensured that SQL
Agent
>is truly running and not hung up in a starting status?
Have
>you checked your sqlagent.out file?
>In addition to lightweight pooling being enabled, Agent
can[vbcol=seagreen]
>also get hung up due to a bad mail profile.
>-Sue
>On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
><anonymous@.discussions.microsoft.com> wrote:
I[vbcol=seagreen]
it
>.
>
|||If you don't set a mail profile for SQL Agent then it won't
do anything with mail.
In terms of SQL Agent, you still need to check if the
service is actually running (vs starting), if your server is
configured for lightweight pooling and you need to check
your sqlagent.out file for any errors or problems in
starting SQL Agent.
-Sue
On Wed, 12 May 2004 10:12:18 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue for your reply. I have checked and SQL Mail is
>not configured, but I have checked on an older NT systems
>and its SQL Mail is not configured either and the backups
>work fine. My current system is Windows 2003 server,
>could this be causing the problem? Also, is there a way
>to configure MSServerAgent to not bother with mail? I
>would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
>Agent
>Have
>can
>I
>it
|||Hello Rob,
Did you ever get this problem figured out? I'm having the exact same issue and I have verified that SQL Agent is running but still having the error.
Let me know.
Regards,
-greg
"Rob Simac" wrote:

> Thanks Sue for your reply. I have checked and SQL Mail is
> not configured, but I have checked on an older NT systems
> and its SQL Mail is not configured either and the backups
> work fine. My current system is Windows 2003 server,
> could this be causing the problem? Also, is there a way
> to configure MSServerAgent to not bother with mail? I
> would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
> Agent
> Have
> can
> I
> it
>

Error 22022

I'm attempting to create a backup schedule for my SQL
Server 2000, and I have created the job to perform the
backups. But, when I try to run the backup to test it, I
get the following error:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
I have checked, SQLServerAgent is running and if I stop
and start SQLServerAgent I get the same error.
What is wrong and how do I fix it?Some things to check - Are you running SQL Server in fiber
mode (lightweight pooling)? Have you ensured that SQL Agent
is truly running and not hung up in a starting status? Have
you checked your sqlagent.out file?
In addition to lightweight pooling being enabled, Agent can
also get hung up due to a bad mail profile.
-Sue
On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:

>I'm attempting to create a backup schedule for my SQL
>Server 2000, and I have created the job to perform the
>backups. But, when I try to run the backup to test it, I
>get the following error:
>Error 22022: SQLServerAgent is not currently running so it
>cannot be notified of this action.
>I have checked, SQLServerAgent is running and if I stop
>and start SQLServerAgent I get the same error.
>What is wrong and how do I fix it?|||Thanks Sue for your reply. I have checked and SQL Mail is
not configured, but I have checked on an older NT systems
and its SQL Mail is not configured either and the backups
work fine. My current system is Windows 2003 server,
could this be causing the problem? Also, is there a way
to configure MSServerAgent to not bother with mail? I
would rather have it configured so no mail is sent out.
Ciao,
Rob.

>--Original Message--
>Some things to check - Are you running SQL Server in fiber
>mode (lightweight pooling)? Have you ensured that SQL
Agent
>is truly running and not hung up in a starting status?
Have
>you checked your sqlagent.out file?
>In addition to lightweight pooling being enabled, Agent
can
>also get hung up due to a bad mail profile.
>-Sue
>On Tue, 11 May 2004 11:04:14 -0700, "Rob Simac"
><anonymous@.discussions.microsoft.com> wrote:
>
I[vbcol=seagreen]
it[vbcol=seagreen]
>.
>|||If you don't set a mail profile for SQL Agent then it won't
do anything with mail.
In terms of SQL Agent, you still need to check if the
service is actually running (vs starting), if your server is
configured for lightweight pooling and you need to check
your sqlagent.out file for any errors or problems in
starting SQL Agent.
-Sue
On Wed, 12 May 2004 10:12:18 -0700, "Rob Simac"
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Sue for your reply. I have checked and SQL Mail is
>not configured, but I have checked on an older NT systems
>and its SQL Mail is not configured either and the backups
>work fine. My current system is Windows 2003 server,
>could this be causing the problem? Also, is there a way
>to configure MSServerAgent to not bother with mail? I
>would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
>
>Agent
>Have
>can
>I
>it|||Hello Rob,
Did you ever get this problem figured out? I'm having the exact same issue
and I have verified that SQL Agent is running but still having the error.
Let me know.
Regards,
-greg
"Rob Simac" wrote:

> Thanks Sue for your reply. I have checked and SQL Mail is
> not configured, but I have checked on an older NT systems
> and its SQL Mail is not configured either and the backups
> work fine. My current system is Windows 2003 server,
> could this be causing the problem? Also, is there a way
> to configure MSServerAgent to not bother with mail? I
> would rather have it configured so no mail is sent out.
> Ciao,
> Rob.
>
> Agent
> Have
> can
> I
> it
>