Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
running a batch file
I have a Batch file to use ssh connection. So When I run the batch file
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me pleaseProbably service account and permissions problems. Verify that Agent has the needed permissions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has the needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
> >I have a Batch file to use ssh connection. So When I run the batch file
> > manually its works fine, but when I schedule it on job managment from SQL
> > 2000 it doesnt work.
> >
> > Help me please
>|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it is executed in agents
service user context, not the user who owns the job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...
> ok thanks for yor answer but the user has permission to run the service, In
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
>> Probably service account and permissions problems. Verify that Agent has the needed permissions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
>> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>> >I have a Batch file to use ssh connection. So When I run the batch file
>> > manually its works fine, but when I schedule it on job managment from SQL
>> > 2000 it doesnt work.
>> >
>> > Help me please
>>|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me pleasesql
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me pleaseProbably service account and permissions problems. Verify that Agent has the needed permissions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has the needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
> >I have a Batch file to use ssh connection. So When I run the batch file
> > manually its works fine, but when I schedule it on job managment from SQL
> > 2000 it doesnt work.
> >
> > Help me please
>|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it is executed in agents
service user context, not the user who owns the job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...
> ok thanks for yor answer but the user has permission to run the service, In
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
>> Probably service account and permissions problems. Verify that Agent has the needed permissions.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
>> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>> >I have a Batch file to use ssh connection. So When I run the batch file
>> > manually its works fine, but when I schedule it on job managment from SQL
>> > 2000 it doesnt work.
>> >
>> > Help me please
>>|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me pleasesql
running a batch file
I have a Batch file to use ssh connection. So When I run the batch file
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me please
Probably service account and permissions problems. Verify that Agent has the needed permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has the needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>
|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it is executed in agents
service user context, not the user who owns the job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...[vbcol=seagreen]
> ok thanks for yor answer but the user has permission to run the service, In
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me please
Probably service account and permissions problems. Verify that Agent has the needed permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has the needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>
|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it is executed in agents
service user context, not the user who owns the job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...[vbcol=seagreen]
> ok thanks for yor answer but the user has permission to run the service, In
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
running a batch file
I have a Batch file to use ssh connection. So When I run the batch file
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me pleaseProbably service account and permissions problems. Verify that Agent has the
needed permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p
2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has t
he needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it
is executed in agents
service user context, not the user who owns the job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...[vbcol=seagreen]
> ok thanks for yor answer but the user has permission to run the service, I
n
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -
v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.
1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
>|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
manually its works fine, but when I schedule it on job managment from SQL
2000 it doesnt work.
Help me pleaseProbably service account and permissions problems. Verify that Agent has the
needed permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please|||ok thanks for yor answer but the user has permission to run the service, In
fact the user can run other batch files.
I debug the batch and this is the result:
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -v
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.1p
2
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
-- last line --
This results is when I run from SQL Server, but if a run it from command
console the result is this
Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
-t WEBDSP.TXT
OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
debug1: Reading configuration data /etc/ssh_config
debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
debug1: Connection established.
debug1: identity file id_dsa type 2
debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-cbc hmac-md5 none
debug1: kex: client->server aes128-cbc hmac-md5 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.1' is known and matches the RSA host key.
debug1: Found key in /home/ottod/.ssh/known_hosts:1
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password,keyboard-i
ve
debug1: Next authentication method: publickey
debug1: Offering public key: id_dsa
debug1: Server accepts key: pkalg ssh-dss blen 433
debug1: read PEM private key done: type DSA
debug1: Authentication succeeded (publickey).
debug1: channel 0: new [client-session]
debug1: Entering interactive session.
debug1: Sending command: scp -v -t WEBDSP.TXT
Sending file modes: C0777 3894 webdsp.txt
debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
debug1: channel 0: free: client-session, nchannels 1
debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
debug1: Exit status 0
What do you think?
"Tibor Karaszi" wrote:
> Probably service account and permissions problems. Verify that Agent has t
he needed permissions.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
> news:0DF4CDB5-4C6A-49AA-A798-4B56DFCDA65D@.microsoft.com...
>|||> ok thanks for yor answer but the user has permission to run the service,
I don't understand what you mean by above. When agent executes something, it
is executed in agents
service user context, not the user who owns the job.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jdonaldo" <jdonaldo@.discussions.microsoft.com> wrote in message
news:F10E7461-4A0B-49A5-8087-432269318F23@.microsoft.com...[vbcol=seagreen]
> ok thanks for yor answer but the user has permission to run the service, I
n
> fact the user can run other batch files.
> I debug the batch and this is the result:
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command scp -
v
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.7.
1p2
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> -- last line --
> This results is when I run from SQL Server, but if a run it from command
> console the result is this
> Executing: program /usr/bin/ssh host 10.10.10.1, user dispo, command
> -t WEBDSP.TXT
> OpenSSH_3.8.1p1, OpenSSL 0.9.7d 17 Mar 2004
> debug1: Reading configuration data /etc/ssh_config
> debug1: Connecting to 10.10.10.1 [10.10.10.1] port 22.
> debug1: Connection established.
> debug1: identity file id_dsa type 2
> debug1: Remote protocol version 1.99, remote software version OpenSSH_3.
> debug1: match: OpenSSH_3.7.1p2 pat OpenSSH*
> debug1: Enabling compatibility mode for protocol 2.0
> debug1: Local version string SSH-2.0-OpenSSH_3.8.1p1
> debug1: SSH2_MSG_KEXINIT sent
> debug1: SSH2_MSG_KEXINIT received
> debug1: kex: server->client aes128-cbc hmac-md5 none
> debug1: kex: client->server aes128-cbc hmac-md5 none
> debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<1024<8192) sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
> debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
> debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
> debug1: Host '10.10.10.1' is known and matches the RSA host key.
> debug1: Found key in /home/ottod/.ssh/known_hosts:1
> debug1: ssh_rsa_verify: signature correct
> debug1: SSH2_MSG_NEWKEYS sent
> debug1: expecting SSH2_MSG_NEWKEYS
> debug1: SSH2_MSG_NEWKEYS received
> debug1: SSH2_MSG_SERVICE_REQUEST sent
> debug1: SSH2_MSG_SERVICE_ACCEPT received
> debug1: Authentications that can continue: publickey,password,keyboard-i
> ve
> debug1: Next authentication method: publickey
> debug1: Offering public key: id_dsa
> debug1: Server accepts key: pkalg ssh-dss blen 433
> debug1: read PEM private key done: type DSA
> debug1: Authentication succeeded (publickey).
> debug1: channel 0: new [client-session]
> debug1: Entering interactive session.
> debug1: Sending command: scp -v -t WEBDSP.TXT
> Sending file modes: C0777 3894 webdsp.txt
> debug1: client_input_channel_req: channel 0 rtype exit-status reply 0
> debug1: channel 0: free: client-session, nchannels 1
> debug1: Transferred: stdin 0, stdout 0, stderr 0 bytes in 0.8 seconds
> debug1: Bytes per second: stdin 0.0, stdout 0.0, stderr 0.0
> debug1: Exit status 0
>
> What do you think?
> "Tibor Karaszi" wrote:
>|||the user can run the agent, because I created others job and run fine
"jdonaldo" wrote:
> I have a Batch file to use ssh connection. So When I run the batch file
> manually its works fine, but when I schedule it on job managment from SQL
> 2000 it doesnt work.
> Help me please
Wednesday, March 21, 2012
Run Time Database Connection
Hi
I 've created dsn for sqlserver during run time but while connecting with crystalreport it gives ODBC Error . I need to connect using CRAXDRT.Report,CRAXDRT.Application ,I'm Using vb as front end
Thanks In AdvGenerall the errors are descriptive, so please provide details on which ODBC error it is.
Dave
(Me.HomeTown = DarrenLehmann.HomeTown)
Go Aussies.|||Hi Spring soft
I solved my problem thanx for ur post|||Hi Tony,
Perhaps you could let us know the solution, for enlightenment of others.
On reflection, I suspect the report had a subreport. That's the most common reason (apart from improperly structured formulae) that raises ODBC errors.
Dave|||Ofcourse dave
Private m_crxApp As New CRAXDRT.Application
Private m_crxRep As CRAXDRT.Report
Attribute m_crxRep.VB_VarHelpID = -1
Private m_crxTab As CRAXDRT.DatabaseTable
Implements clsSimpleReport
Public Function clsSimpleReport_RunReport(sReportFileName As String, sTitle As String) As Variant
Dim i As Integer, j As Integer
On Error Resume Next
Screen.MousePointer = 11
m_crxApp.LogOnServer "p2sodbc.dll", "DSN", "DBNAME, "UID", "PWD"
Set m_crxRep = m_crxApp.OpenReport(App.Path & "\Reports\" & sReportFileName)
crvReport.ReportSource = m_crxRep
crvReport.ViewReport
End sub
My mistake was instead of p2sodbc.dll in logon server function i put some other dll file i'm not remembering that nowsql
I 've created dsn for sqlserver during run time but while connecting with crystalreport it gives ODBC Error . I need to connect using CRAXDRT.Report,CRAXDRT.Application ,I'm Using vb as front end
Thanks In AdvGenerall the errors are descriptive, so please provide details on which ODBC error it is.
Dave
(Me.HomeTown = DarrenLehmann.HomeTown)
Go Aussies.|||Hi Spring soft
I solved my problem thanx for ur post|||Hi Tony,
Perhaps you could let us know the solution, for enlightenment of others.
On reflection, I suspect the report had a subreport. That's the most common reason (apart from improperly structured formulae) that raises ODBC errors.
Dave|||Ofcourse dave
Private m_crxApp As New CRAXDRT.Application
Private m_crxRep As CRAXDRT.Report
Attribute m_crxRep.VB_VarHelpID = -1
Private m_crxTab As CRAXDRT.DatabaseTable
Implements clsSimpleReport
Public Function clsSimpleReport_RunReport(sReportFileName As String, sTitle As String) As Variant
Dim i As Integer, j As Integer
On Error Resume Next
Screen.MousePointer = 11
m_crxApp.LogOnServer "p2sodbc.dll", "DSN", "DBNAME, "UID", "PWD"
Set m_crxRep = m_crxApp.OpenReport(App.Path & "\Reports\" & sReportFileName)
crvReport.ReportSource = m_crxRep
crvReport.ViewReport
End sub
My mistake was instead of p2sodbc.dll in logon server function i put some other dll file i'm not remembering that nowsql
run SSIS package with ODBC connection via SQL agent
It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.
I had a lot of problems with this too, the way I was able to resolve it was to configure the SQL Agent to run as a domain service account, and then log into the server under the domain service account and create my SSIS package. Save the package to disk and then import it into SSIS, use the SQL Agent to run a job that points to the SSIS package and it then succeeded. In my case it was a security issue when the SQL Agent was running under a different account than the one I had created the package under. Once I used the same account to do everything I was able to get it to work.
|||
Hey Andy
Thanks for the reply, it seems that the issue is that I'm using a 64 bit OS and 32 bit odbc driver and when sql agent runs it uses the 64bit program files as opposed to the 32 bit program files which means that it couldn't find my odbc dsn. So to get around this had to create a batch file which calls the 'x86' program files.
Have also looked at getting a 64 bit odbc for my postgres database but seems that SSIS does point at 32 bit odbc connectiosn so don't see how to include the 64 bit odbc in SSIS.
Ugly and messy!
|||Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
|||
I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).
When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).
From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first event - packageStart, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.
Please help and thank you all!
|||Jenny,
It sounds like the credentials of your web application pool are being passed to SQL Server, the default account for the application pool is NT AUTHORITY\NETWORK SERVICE. Have you checked that?
|||
I had the same issue: A package using a 32-bit ODBC driver, it ran fine in my laptop but not in my 64-bit server. The work arround was to schedule it through an SQL Agent that used a command line to invoked the 32-bit version of dtsexec. The bottom line: the package runs in a 32-bit mode instead of 64-bit mode
Rafael
|||Hi Andy, thanks for the response.
I have worked on something else these two days. I just checked the web application pool and both my web app and web service are pointed to the DefaultAppPool and its property setting for Identity as Predefined - Network Service. Should I create a new web application pool for both my web app and web service to use? I do need impersonation. How do I configure the new web application pool? Thanks!!!
|||Here, are some more info.
In the SSIS package, there are three connection managers –
· A:Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection
· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection
· C:Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB
After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C. I think the credentials are passed ok but some setting related to the Analysis services are not correct. Any clues?
|||Jenny,
There are a few different ways to go about this so why don't we try the easiest way first. According to the error message you are receiving, the domain\server$ does not have access to the database. So what's happening here is because your web application pool is running with the credentials of network service, SQL Server is viewing the account as ServerName$. So what you need to do is go into SQL Server>Security and add a new user with access permissions to the database. When you add the new user, type in the domain name and the server name with a $ at the end of it, for example: mydomain\myserver$
Make sure under the user mappings you check the box of the database that the user needs access to and also check the datareader box. This will give your web server access to read the database. If you need write permissions you can go back and check the datawriter box.
Typically you should run your web application under a domain service account, provide the domain service account access to the database, and use Windows authentication, something like this for your connection string: "server=MySQLServer; Integrated Security=SSPI; database=MyDatabase"
There are probably specific reasons why you want to use Impersonate, so give the mydomain\myserver$ example a try and see if you can get it to work.
|||Hi Andy,
FYI: The problem was solved. What I did are list as below:
A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).
B. Under SQL server , added this new user and assign the certain rights to it.
C. In the web application and web service, set impersonate=false (instead of true)
D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.
Thank you very much for your kind help!!!
Jenny
|||Had a somewhat different problem... BIDS is running 32 bit and connecting to 32-bit ODBC drivers (Paradox DB in this case), but when executed, the server keeps looking for 64-bit drivers. While I wish it would look for ALL possible drivers and automagically drop down from 64 to 32 if necessary, setting Run64BitRuntime to False fixed it for now (which is too bad, IBM has 64 bit drivers for the AS400). Haven't scheduled the package yet, though.|||Hi,Andy_T wrote:
Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
i'm having the same problem (trying to execute a package located on server A and a web service on server B), but i'm using IIS 5.1, so i think i don't have the web application pool feature.
I also have the package's Package source configured as SSIS Package Store so my path refers to "File System" source type.
from a custom application, i call the web service and i returns "failure" when trying to execute the package (this one is retrieved successfully, started but not executed).
Is there any way to solve this problem on a similar way like Jenny_99?
regards.
rodri|||
I had a similar issue and this is the workaround I found.
We installed SQL2005 with SSIS on a 64-bit windows 2003 server. An IBM ISeries for windows (V5R3M with the latest Service Pack) was used for connecting to AS400. There are actually two versions of ODBC drivers on the 64-bit windows (odbcad32.exe). One is located at Windows\System32. Another is at Windows\SysWOW64. Originally I thought the one in System 32 is 32-bit version, and the other one is 64-bit version. But the driver version number under SysWOW64 is actually consistent with the version number in our 32-bit windows 2003 server. So I am not so sure any more. Anyway, what I found out is that DSN under SysWOW64 is actually used when the package is running in BIDS, or SSIS on the server. The one under System32 is used when the package is scheduled as a job. So I created the same DSN on both drivers to cover all bases since I don't know how to force the SQL agent to use a specific driver.
run SSIS package with ODBC connection via SQL agent
It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.
I had a lot of problems with this too, the way I was able to resolve it was to configure the SQL Agent to run as a domain service account, and then log into the server under the domain service account and create my SSIS package. Save the package to disk and then import it into SSIS, use the SQL Agent to run a job that points to the SSIS package and it then succeeded. In my case it was a security issue when the SQL Agent was running under a different account than the one I had created the package under. Once I used the same account to do everything I was able to get it to work.
|||
Hey Andy
Thanks for the reply, it seems that the issue is that I'm using a 64 bit OS and 32 bit odbc driver and when sql agent runs it uses the 64bit program files as opposed to the 32 bit program files which means that it couldn't find my odbc dsn. So to get around this had to create a batch file which calls the 'x86' program files.
Have also looked at getting a 64 bit odbc for my postgres database but seems that SSIS does point at 32 bit odbc connectiosn so don't see how to include the 64 bit odbc in SSIS.
Ugly and messy!
|||Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
|||
I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).
When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).
From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first event - packageStart, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.
Please help and thank you all!
|||Jenny,
It sounds like the credentials of your web application pool are being passed to SQL Server, the default account for the application pool is NT AUTHORITY\NETWORK SERVICE. Have you checked that?
|||
I had the same issue: A package using a 32-bit ODBC driver, it ran fine in my laptop but not in my 64-bit server. The work arround was to schedule it through an SQL Agent that used a command line to invoked the 32-bit version of dtsexec. The bottom line: the package runs in a 32-bit mode instead of 64-bit mode
Rafael
|||Hi Andy, thanks for the response.
I have worked on something else these two days. I just checked the web application pool and both my web app and web service are pointed to the DefaultAppPool and its property setting for Identity as Predefined - Network Service. Should I create a new web application pool for both my web app and web service to use? I do need impersonation. How do I configure the new web application pool? Thanks!!!
|||Here, are some more info.
In the SSIS package, there are three connection managers –
· A:Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection
· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection
· C:Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB
After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C. I think the credentials are passed ok but some setting related to the Analysis services are not correct. Any clues?
|||Jenny,
There are a few different ways to go about this so why don't we try the easiest way first. According to the error message you are receiving, the domain\server$ does not have access to the database. So what's happening here is because your web application pool is running with the credentials of network service, SQL Server is viewing the account as ServerName$. So what you need to do is go into SQL Server>Security and add a new user with access permissions to the database. When you add the new user, type in the domain name and the server name with a $ at the end of it, for example: mydomain\myserver$
Make sure under the user mappings you check the box of the database that the user needs access to and also check the datareader box. This will give your web server access to read the database. If you need write permissions you can go back and check the datawriter box.
Typically you should run your web application under a domain service account, provide the domain service account access to the database, and use Windows authentication, something like this for your connection string: "server=MySQLServer; Integrated Security=SSPI; database=MyDatabase"
There are probably specific reasons why you want to use Impersonate, so give the mydomain\myserver$ example a try and see if you can get it to work.
|||Hi Andy,
FYI: The problem was solved. What I did are list as below:
A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).
B. Under SQL server , added this new user and assign the certain rights to it.
C. In the web application and web service, set impersonate=false (instead of true)
D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.
Thank you very much for your kind help!!!
Jenny
|||Had a somewhat different problem... BIDS is running 32 bit and connecting to 32-bit ODBC drivers (Paradox DB in this case), but when executed, the server keeps looking for 64-bit drivers. While I wish it would look for ALL possible drivers and automagically drop down from 64 to 32 if necessary, setting Run64BitRuntime to False fixed it for now (which is too bad, IBM has 64 bit drivers for the AS400). Haven't scheduled the package yet, though.|||Hi,Hi there,
Ahhhh yes, I tangled with this one too. What's happening is the SQL Management Studio, the Business Intelligence Studio, and SSIS are compiled as a 32 bit applications, the only 64 bit tool I have been able to find is the x64 DTS Wizard and it needs to be run from a command line or by double clicking it in Explorer. By using this you can connect to 64 bit ODBC drivers but you can't run any job as an SSIS package because there is no 64 bit SSIS. I was using an Oracle provider and found out it has a SQL Net issue with any path names like (x86), so I had to get a patch for that. So although you can install and run SQL 2005 on the x64 platform, 99% of the functionality is 32 bit.
Crazy stuff huh?
i'm having the same problem (trying to execute a package located on server A and a web service on server B), but i'm using IIS 5.1, so i think i don't have the web application pool feature.
I also have the package's Package source configured as SSIS Package Store so my path refers to "File System" source type.
from a custom application, i call the web service and i returns "failure" when trying to execute the package (this one is retrieved successfully, started but not executed).
Is there any way to solve this problem on a similar way like Jenny_99?
regards.
rodri|||
I had a similar issue and this is the workaround I found.
We installed SQL2005 with SSIS on a 64-bit windows 2003 server. An IBM ISeries for windows (V5R3M with the latest Service Pack) was used for connecting to AS400. There are actually two versions of ODBC drivers on the 64-bit windows (odbcad32.exe). One is located at Windows\System32. Another is at Windows\SysWOW64. Originally I thought the one in System 32 is 32-bit version, and the other one is 64-bit version. But the driver version number under SysWOW64 is actually consistent with the version number in our 32-bit windows 2003 server. So I am not so sure any more. Anyway, what I found out is that DSN under SysWOW64 is actually used when the package is running in BIDS, or SSIS on the server. The one under System32 is used when the package is scheduled as a job. So I created the same DSN on both drivers to cover all bases since I don't know how to force the SQL agent to use a specific driver.
sqlFriday, March 9, 2012
Run DDL Task failed when I try to BeginTransaction in it
Just try this:
1.Create a AS Connection Manager in a package, put the RetainSameConnection property into True.
2.Drag a Run DDL Task, and use the Connection in step1.
3.write the DDL query as this:
<Command>
<BeginTransaction />
</Command>
4.Run the package.
Result: It said a mistake like this:
Error: the element Command at 7 row, 38 column(Namespace urn:schemas-microsoft-com:xml-analysis) can not be displayed under Envelope/Body/Execute/Command.
Please help me, what's the hell of this?
Thanks.
Help,Help!
Thanks!
|||
Help, need help!
|||Help! Really need your help!!!|||
Can you provide some more information? what have you tried to debug.
did you try here?
http://www.topxml.com/MS-XML-Analysis/rn-219188_How-to-format-a-NotifyTableChange-command.aspx
what does your XML tag and attribute look like at 7 row, 38 column? can you post it?
|||Thanks, but I dount that it's no business about the XML tag and attribute.
I think perhaps we just CANNOT BeginTransaction in a DDL task.
Please have a try like what I did, you will get the same result, I belive.
Thanks!
|||
Please save me!
Thanks, I am knocked down by this problem.
Run DDL Task failed when I try to BeginTransaction in it
Just try this:
1.Create a AS Connection Manager in a package, put the RetainSameConnection property into True.
2.Drag a Run DDL Task, and use the Connection in step1.
3.write the DDL query as this:
<Command>
<BeginTransaction />
</Command>
4.Run the package.
Result: It said a mistake like this:
Error: the element Command at 7 row, 38 column(Namespace urn:schemas-microsoft-com:xml-analysis) can not be displayed under Envelope/Body/Execute/Command.
Please help me, what's the hell of this?
Thanks.
Help,Help!
Thanks!
|||
Help, need help!
|||Help! Really need your help!!!|||
Can you provide some more information? what have you tried to debug.
did you try here?
http://www.topxml.com/MS-XML-Analysis/rn-219188_How-to-format-a-NotifyTableChange-command.aspx
what does your XML tag and attribute look like at 7 row, 38 column? can you post it?
|||Thanks, but I dount that it's no business about the XML tag and attribute.
I think perhaps we just CANNOT BeginTransaction in a DDL task.
Please have a try like what I did, you will get the same result, I belive.
Thanks!
|||
Please save me!
Thanks, I am knocked down by this problem.
Subscribe to:
Posts (Atom)