Executing a Procedure through a .bat file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Executing a Procedure through a .bat file

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-02-08 : 11:53:33

Hi

I'm trying to execute a stored procedure through a batch file, which I have done many times before using the sa account.

I have tried a previous batch file using the sa details on another database (same instance) which has worked okay, however on this particular batch file I keep getting the error "login failed for user sa - user is not associated with a trusted SQL server connection" which is weird as it works okay against another database.

The fix for the error from Microsoft is to change the authentication mode to mixed mode which it was already on.

I have logged in as sa and ran the procedure which has worked okay

Does anyone have any idea what could be causing this? as I have said i've done this a few times i've never had any problems in the past.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 11:57:04

"login failed for user sa - user is not associated with a trusted SQL server connection"

A "trusted sql server connection" sounds to me like Windows authentication, so it would be using your Windows Login (and not the SA one).

How are you running the query from a BATCH file? OSQL? if so there are command line parameters for a "Trusted connection" (-E) and different ones for a SQL Login connection (where you have to provide the UserID and Password as separate parameters -U & -P)

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-02-08 : 12:06:05

Hi Kristen

I know it sounds like windows authentication without a doubt but i can't understand what it is doing.

Here is my batch script:

ECHO Off
SQLCMD -SHRSRV01 -Usa -PBearPig01 -dSMS -Q"Exec SMS_LEAVER_EXPORT"
cls

As i've mentioned i've got another batch query that runs without errors which is :
ECHO On
SQLCMD -SHRSRV01 -Usa -PBearPig01 -dHRINT_TEST -Q"Exec TALIS_EMP_LEAVER"
cls

Any ideas?

Thanks

Dave

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 12:17:57

Fake it?


SQLCMD -SHRSRV01 -Usa -PBearPig01 -dHRINT_TEST -Q"Exec SMS.dbo.SMS_LEAVER_EXPORT"

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-02-09 : 04:27:34

Hi Kristen

I've already tried this, it's really strange, I can run similar scripts on other databases on the same instance with no problems its just this database.

I've even tried re-dreating it again and i still get the same message.

I might try another server and create the query and the batch file there to see if it makes any difference, alternatively I've not tried Visual Studio instead of using a batch file which i might have a look at.

Thanks again.

Dave

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-09 : 09:42:50

sa doesn't seem to have permission in SMS database.

is sa truly system administrator on the box? many organizations will remove sysadmin from this account. make sure sa has permission to access SMS db and execute stored proc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources