Runtime Error '9' subscript out of range

Our company uses MSDE2000 for a Document Creation System. When a user
gets the [runtime error '9' subscript out of range]I have found no fix
without reinstalling the OS. I have not found the cause for the error.
Backups are unable to complete once this error occures. We have un
installed MSDE and reinstalled (also uninstall the software and
reinstalled it)
The five times I have seen this error everything work until the merge
proccess this along with unable to backup makes me think it is MSDE
related.
HELP
Joe Bullington
TechnologySupport
National Network Technology TEAM
One Valmont Plaza - Fourth Floor
Omaha, NE 68154
Toll Free: (888) 837-9709 ext. 3859
Local: (402) 964-3859
Email: joeb@.nnepa.com
General Support Email: support@.nnepa.comJoe NNEPA (joeb@.nnepa.com) writes:
> Our company uses MSDE2000 for a Document Creation System. When a user
> gets the [runtime error '9' subscript out of range]I have found no fix
> without reinstalling the OS. I have not found the cause for the error.
> Backups are unable to complete once this error occures. We have un
> installed MSDE and reinstalled (also uninstall the software and
> reinstalled it)
> The five times I have seen this error everything work until the merge
> proccess this along with unable to backup makes me think it is MSDE
> related.
This sounds like a Visual Basic error to me, so poking with MSDE
may not resolve the problem easily. You should probably debug
the document-creation system, or talk to the vendor if it is a
3rd party product.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Runtime data source error

Hello

I have a defined data source to an oracle server. I've alredy intalled oracle client, and setup my data source to save the user and password. I'm using .NET provider/OracleClient DataProvider Connection. When I click on "Test Connection" Button, SSIS reports SUCCESS. In Connection Manager TAB I created on connection called "OracleServer" from my oracle data source, described above.

In my package, I defined a DataReaderSource task, I specified "OracleServer" as a connection to it. I can preview data and view oracle's columns name..., so it make me think that everything is fine. But when It execute the task it FAIL and notify logon error and that password can't be blank.

Please help, I need read from the oracle server!!

Thank you.

This may depend on the connection manager, the provider and your ProtectionLevel in the package. Check these.

One of them means you loose the password. If you don't know, between you clicking Execute in the designer and the package running, it is saved, and loaded into a separate execution host. The host then runs the package and passes back the debug messages to BIDS/VS to update the UI with pretty colours for you. So even though the password is there in your current BIDS/VS session, it is probably being lost on the save, prior to being loaded into the execution host. If you close the package in BIDS, and re-open do you still have the password? I would guess not, and this is the issue you see when trying to execute it.

You can play with the various settings, but my favoured approach is to set the package ProtectionLevel to DontSaveSensitive. This means no passwords are ever saved, but you then use a Configuration to set them. Going forward every time the package is loaded, by an execution host or the designer or other tool, it will read the configuration and apply the password or any other configuration you may have set. Problem solved! it may seem like hard work, but if you plan on deploying packages this is really got to be good practise.

|||

Hello,

When I close BIDS and reopen it, I can still see the password. However let me tell you that I already find the solution.

The problem was that my oracle server is 10.1.x and my oracle's drivers installed on SQL Server machine was 10.2.x. Only I unistalled these drivers and install 10.1 version and everything works!.

thanks for your advices.

Hernan.

Running/Starting MSDE Server as Power User automatically

I am able to install MSDE as an Administrator. The server will also
start (with the little icon in system tray showing the white circle
with a green play graphic).
I am able to use the server perfectly fine as a user under the
Administration group.
However, when I created a local account under POWER USER group, the
server does not start or connect to any servers.
I have to manually type in my computer's name at which point it will
start.
I have also had trouble installing the server and having it
successfully run under a POWER USER group.
Anyone have any soultions?
Gautam Lad
hi,
"Gautam Lad" <gautam@.hbfenn.com> ha scritto nel messaggio
news:3a492d07.0501031241.1e0cf675@.posting.google.c om
> I am able to install MSDE as an Administrator. The server will also
> start (with the little icon in system tray showing the white circle
> with a green play graphic).
> I am able to use the server perfectly fine as a user under the
> Administration group.
> However, when I created a local account under POWER USER group, the
> server does not start or connect to any servers.
> I have to manually type in my computer's name at which point it will
> start.
> I have also had trouble installing the server and having it
> successfully run under a POWER USER group.
> Anyone have any soultions?
as SQL Server requires a lot of admin privileges at startup, in order to
register services, install MDAC (if needed) and loo of COM server, the best
way to go is installing it as mmber of admins WinNT role...
as regard running SQL Server, it's services (MSSQLSERVER and SQLSERVERAGENT
for a default instance) usually run under LocalSystem WinNT special account,
but you can run them under any admin member WinNT account, as special
privileges for file system and registry access...
in usual solutions, MSDE is installed from admins and run under their
account (LocalSystem and/or standard WinNT local admin accounts), but WinNT
logged users are (as best practice) traditional non admin users, that's to
say power users or limited users, and you should not see problems this
way... you have to check the account the services are running under...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Running xp_cmdshell

Hi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasiHi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasi
the user should have execute permissions on the xtended procedure xp_cmdshell to run it .

Running trigger t-sql as the sa

How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.

EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.

EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'

sql2k does not support runas. You have to wait for yukon.

Running Stored procedure as....

I need to use SET IDENTITY_INSERT on several tables in one
of the Stored Procedures. The stored procedure is
executed by an application user that can not be given DBO,
db_ddladmin or any higher permissions. This makes the SET
statement fail as the user does not have the need
authority.
My question: Is there a way that I can have this
particular stored procedure run under higher authority?
That is who ever runs this stored procedure it will always
execute under the authority of a fixed given user.Sorry that functionality is not availble in the current version of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Running SSIS package from SQL Job

I'm trying to run a SSIS package (dtsx) from inside an sql job (SQL Server agent). This works fine if the user running (run as) the step is a local admin on the server. If it's not, I get the error message "The package could not be loaded. The step failed". This happens even if the user has all possible serverroles such as "sysadmin" etc in SQL.

So, my question is, is there any way to load an SSIS package without being local admin on the machine? In case it is, what is needed?

regards Andreas

I'm not sure that this will solve the problem, but have a look at PROXY

and CREDENTIALS - I was able to invoke a sample package using them,

using a user that was only a member of the USERS local group.|||

GethWho wrote:

I'm not sure that this will solve the problem, but have a look at PROXY

and CREDENTIALS - I was able to invoke a sample package using them,

using a user that was only a member of the USERS local group.

here's a sample of what I did:

--code

--###################################################### describe

script

############################################################################

PRINT '>>> This script creates the LOGIN,

USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES

<<<'

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] started at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

PRINT ''

PRINT '>>> Create Objects <<<'

PRINT ''

--################################################## Check and Drop

Existing

########################################################################

--################################################## CREATE Login

###################################################################################

SET NOCOUNT ON

USE [master]

IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE LOGIN [LocalMachine\TESTSSISUser] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDatabase]

--################################################## CREATE User and

Grant Rights on DBS

############################################################

USE [MyDatabase]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DataReader], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DataWriter], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [LocalMachine\TESTSSISUser]

GRANT EXECUTE ON [sp_dts_addlogentry] to [LocalMachine\TESTSSISUser]

USE [msdb]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsadmin], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsltduser], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsoperator], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [LocalMachine\TESTSSISUser]

--################################################## CREATE Credential

##############################################################################

USE [master]

IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = 'TestProxy')

CREATE CREDENTIAL [TestProxy] WITH IDENTITY = 'LocalMachine\TestSSISUser', secret = 't3st'

--################################################## CREATE Proxy

###################################################################################

USE [msdb]

DECLARE

@.proxy_name SYSNAME,

@.subsystem_name SYSNAME,

@.UserName SYSNAME

SET @.proxy_name = 'TestSSISUser'

SET @.subsystem_name = 'CmdExec'

SET @.UserName = 'LocalMachine\TestSSISUser'

EXEC sp_enum_proxy_for_subsystem @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

IF @.@.ROWCOUNT = 0

EXEC SP_ADD_PROXY

@.proxy_name=@.proxy_name, @.credential_name='TestProxy'

EXEC sp_enum_login_for_proxy @.proxy_name=@.proxy_name, @.name=@.UserName

IF @.@.ROWCOUNT = 0

EXEC SP_GRANT_LOGIN_TO_PROXY

@.login_name=@.UserName,

@.proxy_name=@.proxy_name

EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

--###################################################### end script

##################################################################################

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] ended at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

--code

.. it isn't perfect - I couldn't find a way to do an existance check

for the PROXY to run the SP_REVOKE_PROXY_FROM_SUBSYSTEM only if it

PROXY had been gtranted.|||

There will be a reason for the package failing to load, and this is what you need to find out. Use the CmdExec step type.

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

Running SQLServer and SQLServer Agent as Power User

We're trying to limit the number of user accounts with Admin level permissio
n
on our Win2K servers, especially SQL servers. We have created a domain level
account to run SQLServer and SQLAgent. We'd like to limit it to Power User
status instead of Admin status on the servers, but we cannot seem to start
and stop the services from SEM with only Power User status. We've checked
registry key permissions and everything seems to be configured properly. Is
this configuration even possible? Or does this account NEED to be local admi
n
on the server? Help would be appreciated. Thanks.The account that starts the services needs to have the "log on as a service"
right. Without this MSSQLServer and MS SQL Server Agent will not start.
It would also be more secure to use a domain account for this rather than a
local account as SQL then benefits from the integrated security of Windows
2000.
Also why would you want end users to have admin rights on the server at all?
This defeats the object of system security and resource accessibility. It
is best that they are Doman Users only then assign access rights to shares
on the servers.
HTH
Regards
Dazza
"gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
> We're trying to limit the number of user accounts with Admin level
> permission
> on our Win2K servers, especially SQL servers. We have created a domain
> level
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly.
> Is
> this configuration even possible? Or does this account NEED to be local
> admin
> on the server? Help would be appreciated. Thanks.|||We've following the instructions in MS article 283811 and ensured that the
account has all necessary extended user rights, such as act as part of
operating system, logon as batch job, logon as service. The fundamental
question is whether the account can run as Power User or does it need to be
Administrator? If it does not NEED to be Administrator, what other
configuration is necessary to let us use that account to stop and start the
SQLServer service, since Power User does not seem to have the rights. Thanks
.
"Dazza" wrote:

> The account that starts the services needs to have the "log on as a servic
e"
> right. Without this MSSQLServer and MS SQL Server Agent will not start.
> It would also be more secure to use a domain account for this rather than
a
> local account as SQL then benefits from the integrated security of Windows
> 2000.
> Also why would you want end users to have admin rights on the server at al
l?
> This defeats the object of system security and resource accessibility. It
> is best that they are Doman Users only then assign access rights to shares
> on the servers.
> HTH
> Regards
> Dazza
>
> "gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
> news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
>
>|||gbledsoe wrote:
> We're trying to limit the number of user accounts with Admin level permiss
ion
> on our Win2K servers, especially SQL servers. We have created a domain lev
el
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly. I
s
> this configuration even possible? Or does this account NEED to be local ad
min
> on the server? Help would be appreciated. Thanks.
It should be, although not all sql feature are available. I am running
multiple instances with different plain domain user accounts. When you are
not sure about registry, user and ntfs permission change the account using
the enterprise manager.
When you need the proxy account to run scheduled dts packages create a
separate account for the sql agent service and make it local admin, unless
someone here can explain how to accomplice this without local admin rights.
have a look at this one:
http://support.microsoft.com/defaul...;283811&sd=tech
Hans

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.
mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>
|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>> hi, I have a job failed for the following error. The job is send an email
>> to me(outlook). any ideas?
>> Thanks
>> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
>> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
>> component is turned off as part of the security configuration for this
>> server. A system administrator can enable the use of 'Ole Automation
>> Procedures' by using sp_configure. For more information about enabling
>> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
>> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||I am having the same problem here.
I have enabled OLE Automation, the services are running under local system
account, but i am still not able to create an object using sp_OACreate
the error message i am getting is
0x80010105 ODSOLE Extended Procedure The server threw an exception.
It is very nice error message that it won't give any idea :)
Thanks
Joko
"mecn" wrote:
> Thanks a lot. I apprecite
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> > mecn,
> >
> > Yes, the idea is to do what the messages says: "enable the use of 'Ole
> > Automation
> > Procedures' by using sp_configure."
> >
> > sp_configure 'Ole Automation Procedures', 1
> > GO
> > RECONFIGURE WITH OVERRIDE
> > GO
> >
> > Or use the Surface Area Configuration tool to do the same thing
> > interactively.
> >
> > RLF
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> >> hi, I have a job failed for the following error. The job is send an email
> >> to me(outlook). any ideas?
> >>
> >> Thanks
> >>
> >> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> >> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> >> component is turned off as part of the security configuration for this
> >> server. A system administrator can enable the use of 'Ole Automation
> >> Procedures' by using sp_configure. For more information about enabling
> >> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> >> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
> >>
> >
> >
>
>

Running SQL scripts from Command Prompt

I have a requirment, when a user logs in to the pc, I want to run some SQL scripts which is been saved as a stored procedure on another pc. Could this be possible. I know in oracle you could do it using SQLLDR. Is there any way to do it in SQL?

Please advice. Any help is greatly appreciated.

If you are using SQL 2000, check out OSQL utility. If you have plans to upgrade to 2005 you might also want to check out SQLCMD. OSQL will be removed in later versions.|||I have one more problem, I can only use the osql on the server. I want to run this on the client pc which conatins an odbc connection to the sql server|||Then you need to install SQL Server client tools from the SQL Server installation PC on the client pc. You can choose to install client tool only without installing database engine.

running SQL 2005 under a user account cause servicefail to start

Hi,
I have changed the SQL Server account from local system to .\administrator
and then SQL Server failed to starts
the event log log 5 errors:
event ids: 17120, 17826, 17182, 17182, 26049
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and
the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the
network library. To determine the cause, review the errors immediately
preceding this one in the error log.
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x40.
Server local connection provider failed to listen on [
\\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
my other services works fine under the administrator account (AS2005,
RS2005...)
any idea?
thanks.
Jerome.Hi
This looks like a permission thing, you may want to check the network
configuration. Have you tried removing named pipes?
John
"Jéjé" wrote:

> Hi,
> I have changed the SQL Server account from local system to .\administrator
> and then SQL Server failed to starts
> the event log log 5 errors:
> event ids: 17120, 17826, 17182, 17182, 26049
> SQL Server could not spawn FRunCM thread. Check the SQL Server error log a
nd
> the Windows event logs for information about possible related problems.
> Could not start the network library because of an internal error in the
> network library. To determine the cause, review the errors immediately
> preceding this one in the error log.
> TDSSNIClient initialization failed with error 0x5, status code 0x1.
> TDSSNIClient initialization failed with error 0x5, status code 0x40.
> Server local connection provider failed to listen on [
> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
>
> my other services works fine under the administrator account (AS2005,
> RS2005...)
> any idea?
>
> thanks.
>
> Jerome.
>
>
>|||yes, no changes
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...[vbcol=seagreen]
> Hi
> This looks like a permission thing, you may want to check the network
> configuration. Have you tried removing named pipes?
> John
> "Jj" wrote:
>

running SQL 2005 under a user account cause servicefail to start

Hi,
I have changed the SQL Server account from local system to .\administrator
and then SQL Server failed to starts
the event log log 5 errors:
event ids: 17120, 17826, 17182, 17182, 26049
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and
the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the
network library. To determine the cause, review the errors immediately
preceding this one in the error log.
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x40.
Server local connection provider failed to listen on [
\\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
my other services works fine under the administrator account (AS2005,
RS2005...)
any idea?
thanks.
Jerome.Hi
This looks like a permission thing, you may want to check the network
configuration. Have you tried removing named pipes?
John
"Jéjé" wrote:
> Hi,
> I have changed the SQL Server account from local system to .\administrator
> and then SQL Server failed to starts
> the event log log 5 errors:
> event ids: 17120, 17826, 17182, 17182, 26049
> SQL Server could not spawn FRunCM thread. Check the SQL Server error log and
> the Windows event logs for information about possible related problems.
> Could not start the network library because of an internal error in the
> network library. To determine the cause, review the errors immediately
> preceding this one in the error log.
> TDSSNIClient initialization failed with error 0x5, status code 0x1.
> TDSSNIClient initialization failed with error 0x5, status code 0x40.
> Server local connection provider failed to listen on [
> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
>
> my other services works fine under the administrator account (AS2005,
> RS2005...)
> any idea?
>
> thanks.
>
> Jerome.
>
>
>|||yes, no changes
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...
> Hi
> This looks like a permission thing, you may want to check the network
> configuration. Have you tried removing named pipes?
> John
> "Jéjé" wrote:
>> Hi,
>> I have changed the SQL Server account from local system to
>> .\administrator
>> and then SQL Server failed to starts
>> the event log log 5 errors:
>> event ids: 17120, 17826, 17182, 17182, 26049
>> SQL Server could not spawn FRunCM thread. Check the SQL Server error log
>> and
>> the Windows event logs for information about possible related problems.
>> Could not start the network library because of an internal error in the
>> network library. To determine the cause, review the errors immediately
>> preceding this one in the error log.
>> TDSSNIClient initialization failed with error 0x5, status code 0x1.
>> TDSSNIClient initialization failed with error 0x5, status code 0x40.
>> Server local connection provider failed to listen on [
>> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
>>
>> my other services works fine under the administrator account (AS2005,
>> RS2005...)
>> any idea?
>>
>> thanks.
>>
>> Jerome.
>>
>>|||Hi
I assume going back to local system still works? Have you tried a domain
account or a different local administrator?
John
"Jéjé" wrote:
> yes, no changes
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...
> > Hi
> >
> > This looks like a permission thing, you may want to check the network
> > configuration. Have you tried removing named pipes?
> >
> > John
> >
> > "Jéjé" wrote:
> >
> >> Hi,
> >>
> >> I have changed the SQL Server account from local system to
> >> .\administrator
> >> and then SQL Server failed to starts
> >> the event log log 5 errors:
> >> event ids: 17120, 17826, 17182, 17182, 26049
> >> SQL Server could not spawn FRunCM thread. Check the SQL Server error log
> >> and
> >> the Windows event logs for information about possible related problems.
> >>
> >> Could not start the network library because of an internal error in the
> >> network library. To determine the cause, review the errors immediately
> >> preceding this one in the error log.
> >>
> >> TDSSNIClient initialization failed with error 0x5, status code 0x1.
> >>
> >> TDSSNIClient initialization failed with error 0x5, status code 0x40.
> >>
> >> Server local connection provider failed to listen on [
> >> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
> >>
> >>
> >>
> >> my other services works fine under the administrator account (AS2005,
> >> RS2005...)
> >>
> >> any idea?
> >>
> >>
> >>
> >> thanks.
> >>
> >>
> >>
> >> Jerome.
> >>
> >>
> >>
> >>
> >>
>
>|||Hi
Another thought! Have you tried changing the pipe name in SQL Server
Configuration Manager?
John
"Jéjé" wrote:
> yes, no changes
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...
> > Hi
> >
> > This looks like a permission thing, you may want to check the network
> > configuration. Have you tried removing named pipes?
> >
> > John
> >
> > "Jéjé" wrote:
> >
> >> Hi,
> >>
> >> I have changed the SQL Server account from local system to
> >> .\administrator
> >> and then SQL Server failed to starts
> >> the event log log 5 errors:
> >> event ids: 17120, 17826, 17182, 17182, 26049
> >> SQL Server could not spawn FRunCM thread. Check the SQL Server error log
> >> and
> >> the Windows event logs for information about possible related problems.
> >>
> >> Could not start the network library because of an internal error in the
> >> network library. To determine the cause, review the errors immediately
> >> preceding this one in the error log.
> >>
> >> TDSSNIClient initialization failed with error 0x5, status code 0x1.
> >>
> >> TDSSNIClient initialization failed with error 0x5, status code 0x40.
> >>
> >> Server local connection provider failed to listen on [
> >> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
> >>
> >>
> >>
> >> my other services works fine under the administrator account (AS2005,
> >> RS2005...)
> >>
> >> any idea?
> >>
> >>
> >>
> >> thanks.
> >>
> >>
> >>
> >> Jerome.
> >>
> >>
> >>
> >>
> >>
>
>|||finally its the solution, the named pipe protocol cause the error.
I disable it, and all works fine...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BAC11602-9BC9-490B-868F-C2FDC9E3E1DE@.microsoft.com...
> Hi
> Another thought! Have you tried changing the pipe name in SQL Server
> Configuration Manager?
> John
> "Jéjé" wrote:
>> yes, no changes
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...
>> > Hi
>> >
>> > This looks like a permission thing, you may want to check the network
>> > configuration. Have you tried removing named pipes?
>> >
>> > John
>> >
>> > "Jéjé" wrote:
>> >
>> >> Hi,
>> >>
>> >> I have changed the SQL Server account from local system to
>> >> .\administrator
>> >> and then SQL Server failed to starts
>> >> the event log log 5 errors:
>> >> event ids: 17120, 17826, 17182, 17182, 26049
>> >> SQL Server could not spawn FRunCM thread. Check the SQL Server error
>> >> log
>> >> and
>> >> the Windows event logs for information about possible related
>> >> problems.
>> >>
>> >> Could not start the network library because of an internal error in
>> >> the
>> >> network library. To determine the cause, review the errors immediately
>> >> preceding this one in the error log.
>> >>
>> >> TDSSNIClient initialization failed with error 0x5, status code 0x1.
>> >>
>> >> TDSSNIClient initialization failed with error 0x5, status code 0x40.
>> >>
>> >> Server local connection provider failed to listen on [
>> >> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
>> >>
>> >>
>> >>
>> >> my other services works fine under the administrator account (AS2005,
>> >> RS2005...)
>> >>
>> >> any idea?
>> >>
>> >>
>> >>
>> >> thanks.
>> >>
>> >>
>> >>
>> >> Jerome.
>> >>
>> >>
>> >>
>> >>
>> >>
>>

running SQL 2005 under a user account cause servicefail to start

Hi,
I have changed the SQL Server account from local system to .\administrator
and then SQL Server failed to starts
the event log log 5 errors:
event ids: 17120, 17826, 17182, 17182, 26049
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and
the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the
network library. To determine the cause, review the errors immediately
preceding this one in the error log.
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x40.
Server local connection provider failed to listen on [
\\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
my other services works fine under the administrator account (AS2005,
RS2005...)
any idea?
thanks.
Jerome.
Hi
This looks like a permission thing, you may want to check the network
configuration. Have you tried removing named pipes?
John
"Jéjé" wrote:

> Hi,
> I have changed the SQL Server account from local system to .\administrator
> and then SQL Server failed to starts
> the event log log 5 errors:
> event ids: 17120, 17826, 17182, 17182, 26049
> SQL Server could not spawn FRunCM thread. Check the SQL Server error log and
> the Windows event logs for information about possible related problems.
> Could not start the network library because of an internal error in the
> network library. To determine the cause, review the errors immediately
> preceding this one in the error log.
> TDSSNIClient initialization failed with error 0x5, status code 0x1.
> TDSSNIClient initialization failed with error 0x5, status code 0x40.
> Server local connection provider failed to listen on [
> \\.\pipe\SQLLocal\SQL2005 ]. Error: 0x5
>
> my other services works fine under the administrator account (AS2005,
> RS2005...)
> any idea?
>
> thanks.
>
> Jerome.
>
>
>
|||yes, no changes
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5A1CA870-06E8-4D43-A7E6-A715FD46281E@.microsoft.com...[vbcol=seagreen]
> Hi
> This looks like a permission thing, you may want to check the network
> configuration. Have you tried removing named pipes?
> John
> "Jj" wrote:

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

try define trigger with execute as clause

CREATE TRIGGER YourNAme ON Yourtable
WITH EXECUTE AS 'CompanyDomain\SqlUser1'

AFTER INSERT, UPDATE AS.......

--call your procedure here

GO

Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?

I think you may use openrowset in the trigger to update the other database. it is not the best practice but it is the only solution I know

OPENROWSET('SQLOLEDB','ServerName';'user';'Password',

'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')|||

hello,

if you will be doing this most of the times

you might as well use the sp_addlinkserver options "useself" set to false to create a

linked server

Syntax
sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]

by setting useself to false you can enter the desired username and password

regards

|||

USE DB1

Go

EXEC sp_

Trigger ON INSERT

USE DB2

Go

EXEC sp_

Adamus

|||i wish the new sp for 200 would allow "execute as" feature in 2k5|||

If this is in the trigger, is there any way I can run stored procedure with OpenRowSet and make trigger code invisible to the user who is inserting record since the password is there.
Thanks,