January 15, 2014 at 6:19 pm
The SQL quert in my execute sql task is correct. It works in SSIS and SSMS also. But, it fails when I deploy my package. The error -
Code: 0xC002F210
Source: MySQLTask Execute SQL Task Description: Executing the query
"UPDATE [MyTable] SET MyCol..."
failed with the following error: "Invalid object name 'MyTable'.".
Possible failure reasons: Problems with the query "ResultSet" property
not set correctly parameters not set correctly or connection not established
correctly.
What could be the reason for this error and how do I fix it ?
Thanks.
January 15, 2014 at 7:52 pm
Maybe change the statement to select db_name() and make sure you have a connection and its pointing to the right db.
January 16, 2014 at 11:14 am
Nevyn (1/15/2014)
Maybe change the statement to select db_name() and make sure you have a connection and its pointing to the right db.
I have never heard of that function before. How do I use it with select db_name() with an sql query ?
January 16, 2014 at 11:17 am
Is the deployment failing when you deploy the package to the MSDB server and manually execute via Rt-Click, or when you schedule it via the Agent?
If it's the Agent, make sure the Agent's login has rights to the target DB. If it doesn't, you get that error instead of saying "I can't login there." It can't see any objects in the source/target.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 11:30 am
Evil Kraig F (1/16/2014)
Is the deployment failing when you deploy the package to the MSDB server and manually execute via Rt-Click, or when you schedule it via the Agent?If it's the Agent, make sure the Agent's login has rights to the target DB. If it doesn't, you get that error instead of saying "I can't login there." It can't see any objects in the source/target.
It is failing when I deploy it into SQL server agent and manually execute via rt-click > start job at step.
How do I find out if the Agent's login has rights to the target DB ?
Thanks.
January 16, 2014 at 11:35 am
blasto_max (1/16/2014)
It is failing when I deploy it into SQL server agent and manually execute via rt-click > start job at step.
Okay, definately running under the agent then, if the job is firing it up. Well, or the proxy if you set one.
How do I find out if the Agent's login has rights to the target DB ?
Thanks.
You have to have security rights on the server/DBs. First, you head to the service that's controlling Agent on the job server and find out the login it's using. If it's Local System Account or something like that you have some other overhead to do. Assuming it's a network account, head to the target system, make sure it's setup in server security. Inside there, confirm that it has access to the target DB (properties). If it doesn't, activate it. Then go to the target DB and give it Data_reader (if it's simply reading) or data_writer if it's going against tables. By preference if you're extracting from procs, it should only get execute rights on the procs it should have (I recommend a role).
It only sounds complex once you know where the service is. Go click in a few places and it'll make sense. 🙂 You'll also probably want to bang around on technet and get an overview of SQL Server Security for the future if you have sa rights on these boxes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 11:49 am
Evil Kraig F (1/16/2014)
You have to have security rights on the server/DBs. First, you head to the service that's controlling Agent on the job server and find out the login it's using. If it's Local System Account or something like that you have some other overhead to do. Assuming it's a network account, head to the target system, make sure it's setup in server security. Inside there, confirm that it has access to the target DB (properties). If it doesn't, activate it. Then go to the target DB and give it Data_reader (if it's simply reading) or data_writer if it's going against tables. By preference if you're extracting from procs, it should only get execute rights on the procs it should have (I recommend a role).
It only sounds complex once you know where the service is. Go click in a few places and it'll make sense. 🙂 You'll also probably want to bang around on technet and get an overview of SQL Server Security for the future if you have sa rights on these boxes.
I don't have any knowledge of administration. Can you point me to some guide which could help me to perform the troubleshooting which you mentioned ?
Eg. how do you do this - head to the service that's controlling Agent on the job server and find out the login it's using.
Thanks.
January 16, 2014 at 11:58 am
blasto_max (1/16/2014)
I don't have any knowledge of administration. Can you point me to some guide which could help me to perform the troubleshooting which you mentioned ?Eg. how do you do this - head to the service that's controlling Agent on the job server and find out the login it's using.
Thanks.
Do you have DBAs on your staff there that you can talk with? It'd be a lot easier than trying to muddle through this on your own. If you're running solo, I'll see if I can't locate a walkthrough blog or something but there's an assumption out there that if you're involved in this you're not starting from scratch, so it might take a bit to find one that's comprehensive. It's not something you'll do quickly though.
Also, you need administrator and RDC rights on the server boxes, and sa rights in the servers themselves. Do you have that?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 12:13 pm
Evil Kraig F (1/16/2014)
blasto_max (1/16/2014)
I don't have any knowledge of administration. Can you point me to some guide which could help me to perform the troubleshooting which you mentioned ?Eg. how do you do this - head to the service that's controlling Agent on the job server and find out the login it's using.
Thanks.
Do you have DBAs on your staff there that you can talk with? It'd be a lot easier than trying to muddle through this on your own. If you're running solo, I'll see if I can't locate a walkthrough blog or something but there's an assumption out there that if you're involved in this you're not starting from scratch, so it might take a bit to find one that's comprehensive. It's not something you'll do quickly though.
Also, you need administrator and RDC rights on the server boxes, and sa rights in the servers themselves. Do you have that?
No, DBA is not available currently. I was trying to do it myself. I am starting from scratch. So, I will need a comprehensive guide.
I don't know if this counts as having admin and RDC rights on the server boxes - I login to a VM. From there, I access the SQL server which I think is on the VM itself.
About SA rights, not sure -
SELECT IS_SRVROLEMEMBER('sysadmin', 'YourLogin') == 1
SELECT IS_SRVROLEMEMBER('sysadmin', 'DOMAIN\user1') == NULL
January 16, 2014 at 12:41 pm
When you login to the VM, find the 'My Computer' or 'Computer' icon. Rt-Click that, go to manage. In the window that comes up, go to "Services and Applications" then "Services". In the list that comes up on the right side, find SQL Server Agent (<InstanceName>). Instance may simply be MSSQLServer if it's the default installation. Rt-Click that, go to properties. Under log on, determine if it's set to Local System Account or This Account. If This Account, it should have a network login. Write/copy that down somewhere.
From there, you have to get into the SQL Server's security, confirm that login is in the security. If not, it's part of an Active Directory group that you'll have to find out from your Network Administrators.
Once you've got that part, you can enter into that security piece and alter the security for the login to have access to the database.
If it's set to Local System Account, and the server you're targetting isn't on the same box as the agent, it won't be able to talk across the network and you'll have to get the login setup via the Network Administrators.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 1:05 pm
Evil Kraig F (1/16/2014)
When you login to the VM, find the 'My Computer' or 'Computer' icon. Rt-Click that, go to manage. In the window that comes up, go to "Services and Applications" then "Services".
Thanks. I don't see the option - Services and Applications. Maybe because I have Windows Server 2008 VM ?
I do see Services under Configuration though. I am following steps for Services now.
This is what I see under Manage >
Server Manager >
> Roles
> Features
> Diagnostics
> Configuration
> Storage
January 16, 2014 at 1:21 pm
Evil Kraig F (1/16/2014)
When you login to the VM, find the 'My Computer' or 'Computer' icon. Rt-Click that, go to manage. In the window that comes up, go to "Services and Applications" then "Services". In the list that comes up on the right side, find SQL Server Agent (<InstanceName>). Instance may simply be MSSQLServer if it's the default installation. Rt-Click that, go to properties. Under log on, determine if it's set to Local System Account or This Account. If This Account, it should have a network login. Write/copy that down somewhere.
Yes, it is set to "this account". But this account ie ThisDomain\ThisAdmin is not the login which I used to get into the VM.
Evil Kraig F (1/16/2014)
From there, you have to get into the SQL Server's security, confirm that login is in the security. If not, it's part of an Active Directory group that you'll have to find out from your Network Administrators.
Under MySQLServer > Security, I see ThisDomain\ThisAdmin and also the login name which I use to login to the target SQL server (ie the one in which I have to execute SQL). It is a different server.
Evil Kraig F (1/16/2014)
Once you've got that part, you can enter into that security piece and alter the security for the login to have access to the database.
What does this mean and how to do it ?
Evil Kraig F (1/16/2014)
If it's set to Local System Account, and the server you're targetting isn't on the same box as the agent, it won't be able to talk across the network and you'll have to get the login setup via the Network Administrators.
OH ! I think this is why. The target DB, ie one on which SQL is to be executed, is not on the same server. It resides on a different server/instance. How do I set the login via Network Administrators ? Is this for me or DBA's to do ?
Also, assuming that the target DB is on the same server, then how does one set it to local system account ?
Thanks.
January 16, 2014 at 1:30 pm
blasto_max (1/16/2014)
Thanks. I don't see the option - Services and Applications. Maybe because I have Windows Server 2008 VM ?I do see Services under Configuration though. I am following steps for Services now.
Yeah, I don't have access to our servers here directly (strict role security, and I'm a dev) so I can't see 2k8's options. Working from memory/local box. You'll have to adjust for environment.
blasto_max (1/16/2014)
Yes, it is set to "this account". But this account ie ThisDomain\ThisAdmin is not the login which I used to get into the VM.
Nope, that's a network account assigned to the server(s). You won't have access to that so they don't have to worry about it during employee loss. It limits the exposure.
Under MySQLServer > Security, I see ThisDomain\ThisAdmin and also the login name which I use to login to the target SQL server (ie the one in which I have to execute SQL). It is a different server.
Right, but the one you care about is the login that's in the service (not your login, the service's login), particularly on the target server.
What does this mean and how to do it ?
Rt-Click the service's login name on the target server and go into properties. It'll make more sense then.
OH ! I think this is why. The target DB, ie one on which SQL is to be executed, is not on the same server. It resides on a different server/instance. How do I set the login via Network Administrators ? Is this for me or DBA's to do ?
It's already set. It's in the service for the agent. Now you just need to get it's security aligned with the tasks it needs to do.
Also, assuming that the target DB is on the same server, then how does one set it to local system account ?
You wouldn't want to. LSA is the baseline. If you've got a network login, it's more flexible and can do everything the LSA can do and more.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 16, 2014 at 1:42 pm
In job history, I see that it is invoked by User1. But, it was executed as user BigDomain\BigAdmin. Could this be the reason for the problems ? I use the login name User1 on the Target server. Maybe my job should be executed by User1 and not by BigDomain\BigAdmin ? Those users are present on Prod server security. Prod is different from Target server.
Thanks.
January 16, 2014 at 1:58 pm
blasto_max (1/16/2014)
In job history, I see that it is invoked by User1. But, it was executed as user BigDomain\BigAdmin. Could this be the reason for the problems ? I use the login name User1 on the Target server. Maybe my job should be executed by User1 and not by BigDomain\BigAdmin ? Those users are present on Prod server security. Prod is different from Target server.Thanks.
Invoking the job is just whoever started the job (can be called from proc, user initiation, scheduling...) It has nothing to do with what the job executes as, which is either the proxy in the step or the SQL Agent if no proxy is set.
The problem is with the SQL Agent's login and security at the target. That is what you have to adjust for and around.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply