Hi
I am creating a cursor which runs through a table of database names and
execute a script using xp_cmdshell on every database in that table.
Declare @.DataBaseName varchar(255)
Declare DBcursor CURSOR FOR
Select dbname From master..upgradedb
Declare @.command varchar(255)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @.DataBaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Upgrading.... ' + @.DataBaseName
set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
exec master..xp_cmdshell @.command
FETCH NEXT FROM DBCursor INTO @.DataBaseName
END
CLOSE DBCursor
DEALLOCATE DBCursor
I get the followinf results - Can anyone guide me in the right direction
please.
Upgrading.... QFM161R23AMEYAA
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)
Upgrading.... QFM161R23GLSCHOOLS
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
If you had printed out the command before trying to execute it for the first
time, you'd have noticed that there's a syntax error in it. A space is
missing before the -i switch.
But I'm just guessing here.
ML|||Check if sp_MSforeachdb System Stored Procedure can be used.
This will simplify the code. U may not require cursor at all.
Rakesh
"Jaco" wrote:
> Hi
> I am creating a cursor which runs through a table of database names and
> execute a script using xp_cmdshell on every database in that table.
> Declare @.DataBaseName varchar(255)
> Declare DBcursor CURSOR FOR
> Select dbname From master..upgradedb
> Declare @.command varchar(255)
>
> OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Upgrading.... ' + @.DataBaseName
> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
> c:\temp\Complete(7-9).sql'
> exec master..xp_cmdshell @.command
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> END
> CLOSE DBCursor
> DEALLOCATE DBCursor
> I get the followinf results - Can anyone guide me in the right direction
> please.
> Upgrading.... QFM161R23AMEYAA
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
> Upgrading.... QFM161R23GLSCHOOLS
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
>sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Current Database name
in T-SQL how do I check what is the current database name?
In my script, I used:
.
.
.
OPEN DBList
FETCH NEXT FROM DBList INTO @.DB_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQLString = N'USE ' + @.DB_name
EXEC (@.SQLString)
.
.
.
Somehow it always stayed "Master" DB and never on go to the next.
ThanksA1. select db_name()
A2. Dynamic exec runs in separate query/security context, subcontext of current query. If you want to run some code in different database context and you select variable database name, you must put this code together with USE into dynamic code.|||Q1 in T-SQL how do I check what is the current database name?
A3 It is not clear what the purpose of the script is. If what is needed is a list of DBs consider using sp_Databases or the Information_Schema.Schemata view, for example:
Exec sp_Databases
Select Catalog_Name From Information_Schema.Schemata
A4 There are several ways to "check what is the current database name", consider Db_Name(), (as Ispaleny already suggested, which is also the simplest), or the Information_Schema views, for example:
Use Pubs
Go
Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables
Select Db_Name() As 'The Current DB Name using Db_Name() is:'
Use Tempdb
Go
Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables
Select Db_Name() As 'The Current DB Name using Db_Name() is:'
In my script, I used:
.
.
.
OPEN DBList
FETCH NEXT FROM DBList INTO @.DB_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQLString = N'USE ' + @.DB_name
EXEC (@.SQLString)
.
.
.
Somehow it always stayed "Master" DB and never on go to the next.
ThanksA1. select db_name()
A2. Dynamic exec runs in separate query/security context, subcontext of current query. If you want to run some code in different database context and you select variable database name, you must put this code together with USE into dynamic code.|||Q1 in T-SQL how do I check what is the current database name?
A3 It is not clear what the purpose of the script is. If what is needed is a list of DBs consider using sp_Databases or the Information_Schema.Schemata view, for example:
Exec sp_Databases
Select Catalog_Name From Information_Schema.Schemata
A4 There are several ways to "check what is the current database name", consider Db_Name(), (as Ispaleny already suggested, which is also the simplest), or the Information_Schema views, for example:
Use Pubs
Go
Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables
Select Db_Name() As 'The Current DB Name using Db_Name() is:'
Use Tempdb
Go
Select
Top 1 Table_Catalog As 'The Current DB Name using the Information_Schema views is:'
From
Information_Schema.Tables
Select Db_Name() As 'The Current DB Name using Db_Name() is:'
Tuesday, February 14, 2012
Cscript from a SQL Stored Procedure?
Hopefully, someone can help me out with this.
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
CarlosYou may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
>
>|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
>
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
CarlosYou may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
>
>|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
>
Cscript from a SQL Stored Procedure?
Hopefully, someone can help me out with this.
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
Carlos
You may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>
|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
>
>
|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
Carlos
You may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>
|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
>
>
|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
Cscript from a SQL Stored Procedure?
Hopefully, someone can help me out with this.
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
CarlosYou may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
> >Hopefully, someone can help me out with this.
> >
> >I'm trying to run a .vbs script using cscript from a stored procedure that
> >gets called from a powerbuilder application (our commerce software)
> >that uses SQL SERVER 2000 as it's database.
> >
> >Basically we are trying to add code that calls the .vbs script so that when
> >an invoice prints it also calls the .vbs script to print a packing list as
> >well.
> >
> >The .vbs file simply formats some data, and creates a file.
> >
> >I can run everything fine from Query Analyzer, however when the
> >stored procedure gets called from the application. I get this error :
> >
> >CScript Error: Loading your settings failed. (Access is denied.)
> >
> >Here is how the .vbs script is being called :
> >
> >DECLARE @.STR varchar(255)
> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
> >exec master..xp_cmdshell @.STR, NO_OUTPUT
> >
> >2265440 is simply an invoice no passed in as a parameter.
> >
> >I've been able to establish that xp_cmdshell does run. Using :
> >
> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
> >
> >I was able to get the directory listing redirected to the output.txt file
> >successfully.
> >However, when the stored procedure is being called from the application
> >'cscript'
> >doesn't seem to execute.
> >
> >Also our SQL Server is running under an administrator login, versus sa.
> >
> >Any help is appreciated.
> >
> >Carlos
> >
>|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
>> You may want to check the proxy account. When non-sysadmins
>> execute xp_cmdshell, it will run under the security context
>> of the SQL Server Agent proxy account. You'll want to check
>> how the proxy account is configured and what permissions it
>> has.
>> -Sue
>> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
>> <Carlos@.discussions.microsoft.com> wrote:
>> >Hopefully, someone can help me out with this.
>> >
>> >I'm trying to run a .vbs script using cscript from a stored procedure that
>> >gets called from a powerbuilder application (our commerce software)
>> >that uses SQL SERVER 2000 as it's database.
>> >
>> >Basically we are trying to add code that calls the .vbs script so that when
>> >an invoice prints it also calls the .vbs script to print a packing list as
>> >well.
>> >
>> >The .vbs file simply formats some data, and creates a file.
>> >
>> >I can run everything fine from Query Analyzer, however when the
>> >stored procedure gets called from the application. I get this error :
>> >
>> >CScript Error: Loading your settings failed. (Access is denied.)
>> >
>> >Here is how the .vbs script is being called :
>> >
>> >DECLARE @.STR varchar(255)
>> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>> >exec master..xp_cmdshell @.STR, NO_OUTPUT
>> >
>> >2265440 is simply an invoice no passed in as a parameter.
>> >
>> >I've been able to establish that xp_cmdshell does run. Using :
>> >
>> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>> >
>> >I was able to get the directory listing redirected to the output.txt file
>> >successfully.
>> >However, when the stored procedure is being called from the application
>> >'cscript'
>> >doesn't seem to execute.
>> >
>> >Also our SQL Server is running under an administrator login, versus sa.
>> >
>> >Any help is appreciated.
>> >
>> >Carlos
>> >
>>
I'm trying to run a .vbs script using cscript from a stored procedure that
gets called from a powerbuilder application (our commerce software)
that uses SQL SERVER 2000 as it's database.
Basically we are trying to add code that calls the .vbs script so that when
an invoice prints it also calls the .vbs script to print a packing list as
well.
The .vbs file simply formats some data, and creates a file.
I can run everything fine from Query Analyzer, however when the
stored procedure gets called from the application. I get this error :
CScript Error: Loading your settings failed. (Access is denied.)
Here is how the .vbs script is being called :
DECLARE @.STR varchar(255)
SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
exec master..xp_cmdshell @.STR, NO_OUTPUT
2265440 is simply an invoice no passed in as a parameter.
I've been able to establish that xp_cmdshell does run. Using :
SET @.STR = 'dir /p > c:\OUTPUT.TXT'
I was able to get the directory listing redirected to the output.txt file
successfully.
However, when the stored procedure is being called from the application
'cscript'
doesn't seem to execute.
Also our SQL Server is running under an administrator login, versus sa.
Any help is appreciated.
CarlosYou may want to check the proxy account. When non-sysadmins
execute xp_cmdshell, it will run under the security context
of the SQL Server Agent proxy account. You'll want to check
how the proxy account is configured and what permissions it
has.
-Sue
On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Hopefully, someone can help me out with this.
>I'm trying to run a .vbs script using cscript from a stored procedure that
>gets called from a powerbuilder application (our commerce software)
>that uses SQL SERVER 2000 as it's database.
>Basically we are trying to add code that calls the .vbs script so that when
>an invoice prints it also calls the .vbs script to print a packing list as
>well.
>The .vbs file simply formats some data, and creates a file.
>I can run everything fine from Query Analyzer, however when the
>stored procedure gets called from the application. I get this error :
>CScript Error: Loading your settings failed. (Access is denied.)
>Here is how the .vbs script is being called :
>DECLARE @.STR varchar(255)
>SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>exec master..xp_cmdshell @.STR, NO_OUTPUT
>2265440 is simply an invoice no passed in as a parameter.
>I've been able to establish that xp_cmdshell does run. Using :
>SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>I was able to get the directory listing redirected to the output.txt file
>successfully.
>However, when the stored procedure is being called from the application
>'cscript'
>doesn't seem to execute.
>Also our SQL Server is running under an administrator login, versus sa.
>Any help is appreciated.
>Carlos
>|||Sue,
Thank-you for your help. I revisted our security & proxy accout setup.
Turns out the proxy acccount we setup didn't have log-in permissions
to our Sql Server. The application that was calling the stored procedure
and making the xp_cmdshell call was on another server. Once I changed
the proxy account to a valid login on the Sql Server everything worked
fine. Again thanks!
Carlos
"Sue Hoegemeier" wrote:
> You may want to check the proxy account. When non-sysadmins
> execute xp_cmdshell, it will run under the security context
> of the SQL Server Agent proxy account. You'll want to check
> how the proxy account is configured and what permissions it
> has.
> -Sue
> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
> <Carlos@.discussions.microsoft.com> wrote:
> >Hopefully, someone can help me out with this.
> >
> >I'm trying to run a .vbs script using cscript from a stored procedure that
> >gets called from a powerbuilder application (our commerce software)
> >that uses SQL SERVER 2000 as it's database.
> >
> >Basically we are trying to add code that calls the .vbs script so that when
> >an invoice prints it also calls the .vbs script to print a packing list as
> >well.
> >
> >The .vbs file simply formats some data, and creates a file.
> >
> >I can run everything fine from Query Analyzer, however when the
> >stored procedure gets called from the application. I get this error :
> >
> >CScript Error: Loading your settings failed. (Access is denied.)
> >
> >Here is how the .vbs script is being called :
> >
> >DECLARE @.STR varchar(255)
> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
> >exec master..xp_cmdshell @.STR, NO_OUTPUT
> >
> >2265440 is simply an invoice no passed in as a parameter.
> >
> >I've been able to establish that xp_cmdshell does run. Using :
> >
> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
> >
> >I was able to get the directory listing redirected to the output.txt file
> >successfully.
> >However, when the stored procedure is being called from the application
> >'cscript'
> >doesn't seem to execute.
> >
> >Also our SQL Server is running under an administrator login, versus sa.
> >
> >Any help is appreciated.
> >
> >Carlos
> >
>|||You're very welcome Carlos - thanks for posting back the results!
-Sue
On Wed, 27 Oct 2004 12:47:02 -0700, Carlos
<Carlos@.discussions.microsoft.com> wrote:
>Sue,
>Thank-you for your help. I revisted our security & proxy accout setup.
>Turns out the proxy acccount we setup didn't have log-in permissions
>to our Sql Server. The application that was calling the stored procedure
>and making the xp_cmdshell call was on another server. Once I changed
>the proxy account to a valid login on the Sql Server everything worked
>fine. Again thanks!
>Carlos
>
>"Sue Hoegemeier" wrote:
>> You may want to check the proxy account. When non-sysadmins
>> execute xp_cmdshell, it will run under the security context
>> of the SQL Server Agent proxy account. You'll want to check
>> how the proxy account is configured and what permissions it
>> has.
>> -Sue
>> On Mon, 25 Oct 2004 14:27:04 -0700, Carlos
>> <Carlos@.discussions.microsoft.com> wrote:
>> >Hopefully, someone can help me out with this.
>> >
>> >I'm trying to run a .vbs script using cscript from a stored procedure that
>> >gets called from a powerbuilder application (our commerce software)
>> >that uses SQL SERVER 2000 as it's database.
>> >
>> >Basically we are trying to add code that calls the .vbs script so that when
>> >an invoice prints it also calls the .vbs script to print a packing list as
>> >well.
>> >
>> >The .vbs file simply formats some data, and creates a file.
>> >
>> >I can run everything fine from Query Analyzer, however when the
>> >stored procedure gets called from the application. I get this error :
>> >
>> >CScript Error: Loading your settings failed. (Access is denied.)
>> >
>> >Here is how the .vbs script is being called :
>> >
>> >DECLARE @.STR varchar(255)
>> >SET @.STR = 'cscript //nologo c:\PACKING_LIST.vbs 2265440 > c:\OUTPUT.DAT'
>> >exec master..xp_cmdshell @.STR, NO_OUTPUT
>> >
>> >2265440 is simply an invoice no passed in as a parameter.
>> >
>> >I've been able to establish that xp_cmdshell does run. Using :
>> >
>> >SET @.STR = 'dir /p > c:\OUTPUT.TXT'
>> >
>> >I was able to get the directory listing redirected to the output.txt file
>> >successfully.
>> >However, when the stored procedure is being called from the application
>> >'cscript'
>> >doesn't seem to execute.
>> >
>> >Also our SQL Server is running under an administrator login, versus sa.
>> >
>> >Any help is appreciated.
>> >
>> >Carlos
>> >
>>
Subscribe to:
Posts (Atom)