SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Overview Part 1


SQL Overview Part 1

Author
Message
AshaRRichardson2nd
AshaRRichardson2nd
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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.
sibir1us
sibir1us
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 321
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
Dave Coats
Dave Coats
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
Dave Coats
Dave Coats
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
Sonoma
Sonoma
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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.



David Bird
David Bird
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1224
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
Sonoma
Sonoma
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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. Smile Still works great for my main servers.



SQLdba-473999
SQLdba-473999
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 767
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".".


David Bird
David Bird
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1224
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
SQLdba-473999
SQLdba-473999
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 767
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search