Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

SQL Overview Part 1 Expand / Collapse
Author
Message
Posted Saturday, March 21, 2009 8:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 18, 2010 11:20 AM
Points: 10, Visits: 30
I am getting the following error message. I just implemented the code that was posted exactly as it was posted. It was ran locally. I should be an administrator on all boxes so I don't understand this:

Error at SQl_Overview_Package [Connection Manager "multiserver]: SSIS error code DTS_E_OLedberrOR. An error has occurred Error code 0x80004005 Description: "Login failed for user 'a662783'..

An OLE DB record is available . Sourdce. "Microsoft SQl Native client" Hresult 0x80004005 DEscription: Cannont open database Master" requeste dby teh login. The logfin failed.


It doesn't give me much to go off of so I am curious as to why it is failing. Please help.
Post #681016
Posted Sunday, April 26, 2009 2:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:22 AM
Points: 22, Visits: 292
Hello,

I just want to say that there is a small error in the Database Status script.

It reads:
CONVERT(sysname,DATABASEPROPERTYEX(Name,'Updatability')) AS Updatability

and it should read:
CONVERT(sysname,DATABASEPROPERTYEX(Name,'Updateability')) AS Updateability

The problem is, that 'Updateability' is misspelled, and the script does not return any data.

Otherwise, I must say that you have done a great job!


Make everything as simple as possible, but not simpler.
Albert Einstein
Post #704549
Posted Monday, August 10, 2009 11:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:02 AM
Points: 65, Visits: 265
cy.harrild (1/7/2008)
This is a great tool but i'm having a problem. It all works fine except that all the databases that the package finds throughout the network are listed against one server. Is this caused by the fact i'm building this on my workstation and not on the server?


I'm having the same issue as cy.harrild. My dbo.database_status table only has info for the server I'm running the package from, instead of info for all servers on this domain. Looks like the loop is working because I'm getting the same 12 lines repeating for each server. Just seems that the loop is not grabbing the next server name into the variable SRV_Conn each time. ANy thoughts?




Dave Coats
Post #768016
Posted Monday, August 10, 2009 11:31 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:02 AM
Points: 65, Visits: 265
Dave Coats (8/10/2009)
cy.harrild (1/7/2008)
This is a great tool but i'm having a problem. It all works fine except that all the databases that the package finds throughout the network are listed against one server. Is this caused by the fact i'm building this on my workstation and not on the server?


I'm having the same issue as cy.harrild. My dbo.database_status table only has info for the server I'm running the package from, instead of info for all servers on this domain. Looks like the loop is working because I'm getting the same 12 lines repeating for each server. Just seems that the loop is not grabbing the next server name into the variable SRV_Conn each time. ANy thoughts?


I figured out what my problem was. I had left out the step about configuring the Multiserver data connection. These are the steps I left out the first time:

Now we need to customize this connection

Right Click on MultiServer
Select Properties
Change the Following Properties

Expressions click ... box

Click Property
Click Drop Down Arrow
Select ServerName
In the expression box type @[User::SRV_Conn]
Click OK
Initial Catalog change to Master





Dave Coats
Post #768021
Posted Tuesday, September 1, 2009 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:06 PM
Points: 9, Visits: 161
Hi. I have had the package working great for sometime now. I have a group of servers which do not live on our domain. Is there a way to get the connection manager to see those servers in addition to the ones I have currently working or is it better to create a seperate database and instance?


Thanks.



Post #780698
Posted Tuesday, September 1, 2009 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
The domain must be accessible from the server running the package. A trust must be establish between domains. When accessing servers in another domain, I use the full name of the server in the table SSIS_ServerList.

Example: servername.domain.com

I do not know how to setup a domain. I let the network team handle it.


David Bird

My PC Quick Reference Guide
Post #780726
Posted Tuesday, September 1, 2009 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:06 PM
Points: 9, Visits: 161
I actually used the ip address but no go.
I can connect to them via machinename\localuser using sql management tools, ideras toolset, windows explorer, ect.

Just hoping for an easier solution.

Thanks anyways. :) Still works great for my main servers.



Post #780848
Posted Thursday, February 4, 2010 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:21 PM
Points: 161, Visits: 751
David,
This article is extremely helpful. Great job and thank you for submitting!

I am running into a problem connecting to some of my servers, not all. I have compared between the environments and cannot find a difference. Can you tell me what all should be open on the server side in order for this connection to be successful? From my troubleshooting, I have found that I had to enable OPENROWSET advanced option on the remote server. I thought this would fix the problem, but apparently there is something else blocking it. I also ensured that named pipes was enabled. I am running this from my machine and can connect to all servers in my ServerList table, so I don't think security is an issue.

Any help would be greatly appreciated. I have been working on this for hours!

The error populating the Errors table is:
SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".".

Post #859733
Posted Thursday, February 4, 2010 11:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
I've experienced the timeout error when SQL Server is down or I VPN from home using a different domain. Some containers fail from being unable to obtain a lock.

Try using the full domain server name in the SSIS Server table

Verify the login being used by SSIS has the needed permission. You can start a profile on the remote servers to see what login is being used on both successful and unsuccessful connections.

Review the error log for the SQL Server having connection failures. Make sure the capture Login Failure setting is enabled on the server.


David Bird

My PC Quick Reference Guide
Post #859843
Posted Thursday, February 4, 2010 2:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:21 PM
Points: 161, Visits: 751
David Bird (2/4/2010)
I've experienced the timeout error when SQL Server is down or I VPN from home using a different domain. Some containers fail from being unable to obtain a lock.

Try using the full domain server name in the SSIS Server table

I am not using VPN, I am directly connected to our network. I verified all servers are online. Most of the servers failing to connect are test servers and I know they are not utilized much, so I don't think locking is an issue. As one of my troubleshooting steps, I already have fully qualified the server name with the domain.

Verify the login being used by SSIS has the needed permission. You can start a profile on the remote servers to see what login is being used on both successful and unsuccessful connections.

All connection managers are setup to use Windows authentication. I am logging into SSIS using my windows ID and running (debugging) from there. My id is SYSADMIN on all servers in the ServerList table.

Review the error log for the SQL Server having connection failures. Make sure the capture Login Failure setting is enabled on the server.

I have reviewed the logs and there are no entries of failed login attempts or any errors for that matter. They are all set to capture failed login attempts only.

I thought maybe it's a firewall issue, but then I would have issues trying to connect to the servers from Management Studio as well. I am at a loss.
Post #859967
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse