Login failed error when conneting to mirrored db.

Hi All,

I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

[Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.

I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.

Any sort of pointer is appreciated.

Many thanks.

Sincerely,

Milan G

To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL. When failing over, this will need to be run against all SQL users to tie them back to their logins. If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

|||

Hi M,

Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

cheers,

Milan G

Login failed error when conneting to mirrored db.

Hi All,

I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

[Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.

I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.

Any sort of pointer is appreciated.

Many thanks.

Sincerely,

Milan G

To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL. When failing over, this will need to be run against all SQL users to tie them back to their logins. If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

|||

Hi M,

Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

cheers,

Milan G

Logging Packages Execution

Hello all,

I am struggling around defining a logging mechanism for my packages. I have 2 questions concerning that matter:

I have used event handlers for my loggings (as defined here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx ), but the problem is with packages that failed validation. I cannot find log entry for these cases since no "onerror event" doesn't trigger (for instance when the table I'm loading to doesn't exsist).

And the second question: many of my packages are executed using execute process task (using dtexec command line). I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error…

Thanks,

Liran

Liran R wrote:

... I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error…

Regarding the second issue: output redirection (">") is feature of command shell (cmd.exe). So it works "when executed by myself" because it is executed by cmd.exe, not by itself - cmd.exe intercepts ">" symbol and does the redirection.

When you specify DTExec.exe as your executable you don't get this feature, ">" is simply passed as argument to DTExec.exe and DTExec reports an error as it does not recognize it as valid argument. You need to specify cmd.exe as executable and use /C DTEXEC.EXE /F "MyPackage.dtsx" >MyLog.log as arguments.

|||

Thanks.

Does anybody else misses the DTS2000 Logging feature, or is it just me...

|||

Why not use the Execute Package tasks. All events will get passed up to the parent.

I guess your error is prior to execution, so before logging starts.

For the Execute Process task, could you not capture what you want via the StandardOutput/Error variables?

|||

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

|||

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

|||

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

Besides to expressions I would look at package configurations as well.

Rafael Salas

|||

Jamie Thomson wrote:

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.

|||

Liran R wrote:

Jamie Thomson wrote:

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.

So what? All I'm saying is to use an Execute Package Task rather than an Execute Process Task. I'm not advocating use ofa Foreach loop.

-Jamie

|||

I must understand something:

If I have many Execute Package tasks running in Parrallel, all using the same connection manager (to a specific package file) how can I use a variable to set the connection string using expression if I'm not using a loop container?

Thanks,

Liran

|||

If using the Execute Package Task, how does one pass additional command line values for the package to be executed?

I'm calling my child packges that reside in FileSystem (folder) dynamically but I need to pass them the /CONFIGFILE so that they know where to grab the dtsConfig file. I can't hardcode the dtsConfig because the location of the file/folder is different from environment to env.

These are so far my findings:

1- I can use Parent Configuration Variable in the child but I didn't see an option where I can specify the dtsConfig itself and rather have to create one variable for different config values that I have in my XML.

2- Hardcode the dtsConfig location and force the DBA to let me have a fixed folder for all the environments

3- Instead of using File System, switch to SQL Server and use the manifest to install the child packages and therefore inherit the dtsConfig from the manifest. Change the parent to use SQL Server instead of file system. Limits me now to install every time instead of just having the dba to drop/replace the dtsx in a folder

4- Use alternative methods such as DOS Batch to call dtexec, SQL Agent, SQL stored proc, etc.

5- other workarounds but it start to get out of the child/parent solution where it would great to run the child by itself when needed and also have the parent run the child without touching any code.

I searched your blog as well as others for a solution to pass the command line via Execute Package task and I posted a separate thread with no response.

I've got a little closer by having my Connection Manager concatenating the /CONFIGFILE for my dynamic PackageToRun variable but I get a run time error that either the filename, directory name or volume lable syntax is incorrect. Both dtsx and dtsConfig are in the same folder and the syntax/string looks good (except perhaps maybe it doesn't like the double quotes).

Any suggestions? With so many people advocating Execute Package Task's flexibility and dtsConfig Files (in general), I don't see any solutions to my problem without a big workaround.

Anatole

Logging Packages Execution

Hello all,

I am struggling around defining a logging mechanism for my packages. I have 2 questions concerning that matter:

I have used event handlers for my loggings (as defined here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx ), but the problem is with packages that failed validation. I cannot find log entry for these cases since no "onerror event" doesn't trigger (for instance when the table I'm loading to doesn't exsist).

And the second question: many of my packages are executed using execute process task (using dtexec command line). I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error…

Thanks,

Liran

Liran R wrote:

... I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error…

Regarding the second issue: output redirection (">") is feature of command shell (cmd.exe). So it works "when executed by myself" because it is executed by cmd.exe, not by itself - cmd.exe intercepts ">" symbol and does the redirection.

When you specify DTExec.exe as your executable you don't get this feature, ">" is simply passed as argument to DTExec.exe and DTExec reports an error as it does not recognize it as valid argument. You need to specify cmd.exe as executable and use /C DTEXEC.EXE /F "MyPackage.dtsx" >MyLog.log as arguments.

|||

Thanks.

Does anybody else misses the DTS2000 Logging feature, or is it just me...

|||

Why not use the Execute Package tasks. All events will get passed up to the parent.

I guess your error is prior to execution, so before logging starts.

For the Execute Process task, could you not capture what you want via the StandardOutput/Error variables?

|||

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

|||

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

|||

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

Besides to expressions I would look at package configurations as well.

Rafael Salas

|||

Jamie Thomson wrote:

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.

|||

Liran R wrote:

Jamie Thomson wrote:

Liran R wrote:

I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.

By using the Execute Package task, I will need to create a connection manager to every package.

You can do the same with the Execute Package Task i.e. Make it dynamic.

You do this using expressions. This may help:

Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.

-Jamie

Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.

So what? All I'm saying is to use an Execute Package Task rather than an Execute Process Task. I'm not advocating use ofa Foreach loop.

-Jamie

|||

I must understand something:

If I have many Execute Package tasks running in Parrallel, all using the same connection manager (to a specific package file) how can I use a variable to set the connection string using expression if I'm not using a loop container?

Thanks,

Liran

|||

If using the Execute Package Task, how does one pass additional command line values for the package to be executed?

I'm calling my child packges that reside in FileSystem (folder) dynamically but I need to pass them the /CONFIGFILE so that they know where to grab the dtsConfig file. I can't hardcode the dtsConfig because the location of the file/folder is different from environment to env.

These are so far my findings:

1- I can use Parent Configuration Variable in the child but I didn't see an option where I can specify the dtsConfig itself and rather have to create one variable for different config values that I have in my XML.

2- Hardcode the dtsConfig location and force the DBA to let me have a fixed folder for all the environments

3- Instead of using File System, switch to SQL Server and use the manifest to install the child packages and therefore inherit the dtsConfig from the manifest. Change the parent to use SQL Server instead of file system. Limits me now to install every time instead of just having the dba to drop/replace the dtsx in a folder

4- Use alternative methods such as DOS Batch to call dtexec, SQL Agent, SQL stored proc, etc.

5- other workarounds but it start to get out of the child/parent solution where it would great to run the child by itself when needed and also have the parent run the child without touching any code.

I searched your blog as well as others for a solution to pass the command line via Execute Package task and I posted a separate thread with no response.

I've got a little closer by having my Connection Manager concatenating the /CONFIGFILE for my dynamic PackageToRun variable but I get a run time error that either the filename, directory name or volume lable syntax is incorrect. Both dtsx and dtsConfig are in the same folder and the syntax/string looks good (except perhaps maybe it doesn't like the double quotes).

Any suggestions? With so many people advocating Execute Package Task's flexibility and dtsConfig Files (in general), I don't see any solutions to my problem without a big workaround.

Anatole