http://www.sqlmag.com/authors/author/author/5035567/rsscomment/5035567en-USFri, 25 May 2012 10:27:21 GMTFri, 25 May 2012 10:27:21 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorThu, 22 Sep 2011 10:21:06 GMT
I don't remember if you mentioned it in your article but a sigificant issue with this approach is that even if a job executes multiple times in the specified time window only a single excution of it is displayed.]]>aaThu, 22 Sep 2011 10:21:06 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorFri, 03 Sep 2010 11:51:01 GMT
I have used this before in other jobs. However, i am trying to ryn this new one. When i load the DTSX package into Visual Studios I can not run it.]]>WallerFri, 03 Sep 2010 11:51:01 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorMon, 26 Jul 2010 12:23:53 GMT
In the Parallel load, the query for "NT Groups" is looking for all items that have a status > 9. Do you know what that means or what the code is looking for? I tried to lookup the column and all I got was Microsoft stating that it is for future reference.]]>StanCMon, 26 Jul 2010 12:23:53 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorTue, 13 Jul 2010 16:56:09 GMT
Rodney, thanks for this great tool. Is there a way of getting the disk space for mount points?]]>CockmanTue, 13 Jul 2010 16:56:09 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorTue, 13 Jul 2010 13:39:00 GMT
Thanks so much Rodney happy to see there is an update for this great tool.]]>robertTue, 13 Jul 2010 13:39:00 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorThu, 01 Jul 2010 14:05:28 GMT
Sorry for the delay, James.
Look for a new reports/analysis article on the DBA repository soon to follow.
Glad you like it.
I am always looking for feedback for future enhancements.]]>LANDRUMThu, 01 Jul 2010 14:05:28 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorThu, 01 Jul 2010 13:02:04 GMT
Sorry for the delay, James.
Look for a new reports/analysis article on the DBA repository soon to follow.
Glad you like it.
I am always looking for feedback for future enhancements.]]>LANDRUMThu, 01 Jul 2010 13:02:04 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorThu, 01 Jul 2010 10:34:36 GMT
Thanks for the updated solution! I have used your 2007 solution with great success.]]>James CornellThu, 01 Jul 2010 10:34:36 GMThttp://www.sqlmag.com/article/sql-server/dba-repository-2010#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 08 Jun 2010 15:26:00 GMT
Trying to add more logic to the solution and running into an error. All I am doing is copying one item like fixed drives and building a new query and new table in DBA_REP to match. I am using tempdb for the process just like the item I copied.

Error 18 Validation error. Populate_DBA_REP Connection manager "MultiServer": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ". Populate_DBA_REP.dtsx 0 0
]]>InmonTue, 08 Jun 2010 15:26:00 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorThu, 06 May 2010 17:48:28 GMT
I can’t seem to download the zip file. My browswer says it cannot find the file. Anyone else experiencing this issue?

Thanks,
Jason]]>CheungThu, 06 May 2010 17:48:28 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 04 May 2010 14:56:45 GMT
I really enjoyed the webinar the other day and have been looking forward to downloading the code but I’m having an issue. I’m receiving HTTP Error 404 - The page cannot be found.

- Neal]]>Jon FendenheimTue, 04 May 2010 14:56:45 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorFri, 22 Jan 2010 15:06:51 GMT
I’m still hoping to get a version of the DBA_Repository SSIS package which is able to accomodate SQL 2008 servers. When do you expect this updated solution to be featured in one of your articles?]]>SUEFri, 22 Jan 2010 15:06:51 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorThu, 14 Jan 2010 15:54:17 GMT
"sqlcmd /Lc" works well for me.]]>RODNEYThu, 14 Jan 2010 15:54:17 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorMon, 04 Jan 2010 18:46:21 GMT
the syntax given above is incorrect. Use SQLCMD -Lc and press ]]>CraigMon, 04 Jan 2010 18:46:21 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 23 Oct 2009 06:48:45 GMT
Excellent - have been planning to do something like this myself for some time. Now I can see how much work it would have been and I’d have screwed it up!! Commercial SQL Server documentation tools are very much developer focussed and focus on the nitty gritty of the database schemas. Your soultion focusses mar more clearly on the SLQ Server environment. The (natural) use of SSRS is also excellent. Well done. I suspect there’s a commercial opportunity there for somebody - a SQL Server documentor for the DBA, not the developer]]>BarryFri, 23 Oct 2009 06:48:45 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorMon, 30 Mar 2009 14:31:54 GMT
I contacted Rodney Landrum about the error. He noted that this error can be overcome by eliminating records with a last_run_date value of 0. The following WHERE clause includes criteria to exclude rows of this nature: WHERE (enabled = 1) and server in (@Server) and (Cast(Cast(LEFT(last_run_date, 4) as char(4)) + ’/’ + Cast(SUBSTRING(cast(last_run_date as char(8)), 5, 2) as char(2)) + ’/’ + cast(right(last_run_date, 2) as char(2))as datetime) in (@Last_Run_Date) and last_run_date <> 0 and last_run_date is not NULL) ORDER BY Server Rodney noted that was a slight oversight and that he did run into the situation after the article was posted. In regard to a 2008 version of the DBA_Repository SSIS package, Rodney has a version of the SSIS package that accomodates 2008 servers. This repository solution includes such enhancements as Error Log Reader using MERGE. The article presenting the updated repository solution will be featured in an upcoming issue of SQL Server Magazine. Karen Bemowski, senior editor SQL Server Magazine, Windows IT Pro]]>KarenMon, 30 Mar 2009 14:31:54 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorFri, 06 Mar 2009 11:46:55 GMT
Do you have a 2008 version of the DBA_Repository SSIS Package. I thought it was great.]]>SUEFri, 06 Mar 2009 11:46:55 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 06 Mar 2009 11:32:14 GMT
Do you have a SQL 2008 version of this. I thought it was extremely useful.]]>SUEFri, 06 Mar 2009 11:32:14 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorThu, 05 Mar 2009 18:20:41 GMT
Trying to get this to run, I get this error, any help would be greatly appreciated, Tbanks An error has occurred during report processing. (rsProcessingAborted) Cannot read the next data row for the dataset LastRunDate. (rsErrorReadingNextDataRow) Conversion failed when converting date and/or time from character string.]]>FrankThu, 05 Mar 2009 18:20:41 GMThttp://www.sqlmag.com/article/reporting2/create-a-job-scheduling-advisor#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 06 Jan 2009 19:26:37 GMT
Great article, thank you for sharing the code]]>DavidTue, 06 Jan 2009 19:26:37 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorSat, 06 Dec 2008 11:24:55 GMT
sueboo, A couple of things jump out at me based on your issue. You say that this is Windows 2000 and hosts both a SQL 2000 and 2005 instance. I would be curious to know where you are running the package from, meaning are you running this from a SQL Agent job or directly in Visual Studio/BIDS? If you run the package in design (BIDS) where in the package does the failure occur? You can mail me directly and I would be happy to work out the details with you. My e-mail is posted in my profie on this site. Rodney Landrum (author)]]>RODNEYSat, 06 Dec 2008 11:24:55 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorWed, 03 Dec 2008 11:58:44 GMT
This program is great but I keep getting an error of "Could not obtain information about windows NT group/user ’NT Authority\System’ for 1 server. This is Windows 2000 server and Sql 2000 (sp4). I get no errors with a SQL 2005 instance on the same server.]]>SUEWed, 03 Dec 2008 11:58:44 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorMon, 04 Aug 2008 11:23:36 GMT
Great article]]>AnanthakumarMon, 04 Aug 2008 11:23:36 GMThttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorWed, 28 May 2008 05:56:26 GMT
eyejay, Thank you for your comments. It has been a couple of years since writing this article. Since then I have wanted to revisit this idea and fine tune the DMX queries. I will possibly do this with the release of SQL Server 2008. Still have not hit the lottery, though. Rodney]]>meganbearly Wed, 28 May 2008 05:56:26 GMThttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorhttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorSat, 24 May 2008 17:27:11 GMT
Excellent article, interesting cases are a good way to learn but as a bit of a novice, I was able to follow most of the text although a little more explanation of using the DMX query in Cluster mining would have been really helpful]]>IanSat, 24 May 2008 17:27:11 GMThttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorTue, 06 May 2008 14:24:21 GMT
I am not sure unless maybe you have a combination of security settings that are preventing browsing to the servers...Firewall ports blocked, SQL Browsing disabled, listening on ports other than default, hidden servers?]]>RODNEYTue, 06 May 2008 14:24:21 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorSat, 26 Apr 2008 09:43:16 GMT
gsimmons31, I definitely understand how frustrating it can be to deal with case issues with different collations. I am fixing up these issues in code for myself, as I now have at 5 servers that are of case sensitive collations. Rodney Landrum ~ author]]>RODNEYSat, 26 Apr 2008 09:43:16 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 25 Apr 2008 13:49:33 GMT
Very good stuff. Just an FYI, I had to spend a good bit of time troubleshooting some of the steps in the package. I’m one of those DBAs that has the pleasure of supporting SQL Servers that use Binary Collation. Not all of my servers are Binary so it took me a good while during testing to figure out why the package would die at several points. It turns out that some of the code inside the steps would fail on a Binary Collated machine because variable names differed in case within a script or the case used for a system table column name was incorrect. Other than that, thanks for sharing!]]>GeorgeFri, 25 Apr 2008 13:49:33 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorWed, 23 Apr 2008 14:06:23 GMT
i used sqlcmd /Lc or /L and returned null, but i do have sql installed..??]]>CHANGWed, 23 Apr 2008 14:06:23 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/sqlcmd#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 04 Apr 2008 16:14:31 GMT
Everything looks good, but I tried to make a change to the OLEDB data source used for 2005 job info: I tried to add some field for current date, something like getdate() as TodaysDate. When I press OK I get message Invalid object name sys.sysschedules. I cannot get around this problem with that Multi_Server_9 connection manager. Anybody had the same problem?If so, how did you get over it?]]>GabrielaFri, 04 Apr 2008 16:14:31 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 04 Mar 2008 16:30:23 GMT
Please contact Customer Service 800-793-5697 8AM to 5PM MDT if you are unable to downoad the code. Thank you, Customer Service]]>ColetteTue, 04 Mar 2008 16:30:23 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 03 Mar 2008 10:09:34 GMT
spenumatsa ~ I have noticed that if you are unable to connect to an instance, say because of permissions, the next server in the list is skipped for this step. I have added errror handling for many events and will be adding much more via custom code. For, now, I would run the query from the data flow step directly against the servers to make sure there are no errors. Rodney Landrum]]>RODNEYMon, 03 Mar 2008 10:09:34 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 29 Feb 2008 12:44:57 GMT
I implemented the solution at work and it is excellent. The only issue that i have is that i dont the job details in the Job interval report. FYI my SQL Server is in a clustered environment. I tried every other option but nothing gives me the job interval reports. Just wondering if i am doing something wrong that i dont know of. Any help will be appreciated.]]>SureshFri, 29 Feb 2008 12:44:57 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 22 Feb 2008 08:19:47 GMT
More about the last_run_outcome value: Maybe the quick fix for this is to use the CASE operation in the 2000 data collection to convert to 2005 values, since 2000 should go away first.]]>RickFri, 22 Feb 2008 08:19:47 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 22 Feb 2008 08:07:04 GMT
Caution! in the raw jobs data we’re collecting here, Microsoft seems to have changed the MEANING of the values for last_run_outcome between 2000 and 2005. Checking BOL verifies this. So if you are basing reporting or analysis on job failures, be sure you handle this difference. Apparently the values for Fail and Succeed were reversed, and the value for Cancel was changed.]]>RickFri, 22 Feb 2008 08:07:04 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorWed, 20 Feb 2008 16:12:56 GMT
mrweaver59, yeah, you’re right - free speech lives, and it’s often worth exactly what you pay for it. It doesn’t necessarily indicate that reasonable thinking is involved. Get over it!]]>RickWed, 20 Feb 2008 16:12:56 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorWed, 20 Feb 2008 15:49:17 GMT
I believe we identified a bug in the code for LoadJobsInfo in the 2000 version. active_end_time is missing in the query result set, and thus is not mapped to active_end_time in the field mappings. Is this really a bug, or did we delete it accidentally? I think I’m looking at the original code. We’ve added more archive tables, added a table that is first updated with collection datetime to join as a common timestamp for all tables, added a step at the end to update archive tables from the collection tables using the common timestamp, and can now write sprocs that will compare to earlier collected data so we can report CHANGES. Rodney, great job on the development of this and thanks for sharing with us all.]]>RickWed, 20 Feb 2008 15:49:17 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorThu, 14 Feb 2008 09:03:00 GMT
excellent!]]>GrahamThu, 14 Feb 2008 09:03:00 GMThttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 12 Feb 2008 15:30:59 GMT
I was that DBA... ;) For me was better to get the list of the Available Sql Servers using (c#), to avoid use link servers or MultiServer connection to get the information: using Microsoft.SqlServer.Management.Smo.RegisteredServers; SmoApplication.EnumAvailableSqlServers(false) returns a DataSet Now I want to improve my project. References: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlser vers.aspx Thanks.]]>LudwigTue, 12 Feb 2008 15:30:59 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 12 Feb 2008 11:58:14 GMT
The Borken Link is noreflection of Rodney Landrum This is a very good arctcile. But if you are paying for something and does not work you should have the right to say something. Free speach lives.]]>MikeTue, 12 Feb 2008 11:58:14 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 12 Feb 2008 07:24:51 GMT
Oh yea.. I got the multi script tool... way cool.]]>DonaldTue, 12 Feb 2008 07:24:51 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 12 Feb 2008 07:18:23 GMT
Great article Rodney! This was a big help and eyeopener for me. I am working on adding sp_databases, serverproperty and xp_msver. Thanks for contributing. ;)]]>DonaldTue, 12 Feb 2008 07:18:23 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorWed, 06 Feb 2008 10:32:46 GMT
You know, the more I think about all the above comments beating up on folks for broken links from all of you, the hotter I get. I just have to say if it is that critical for you, go out and do the usual spending of your companys money for a tool instead, or write your own.]]>RickWed, 06 Feb 2008 10:32:46 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorWed, 06 Feb 2008 10:24:37 GMT
OK, all of you perfect DBA’s out there. Lay off the broken links. It ain’t the end of the &^&%$ world. This is good stuff, and another day isn’t going to hurt you a bit. If at first you don’t succeed, remove all evidence you ever tried....]]>RickWed, 06 Feb 2008 10:24:37 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 05 Feb 2008 17:10:25 GMT
Thanks so much for letting us know about this problem. I have passed this information on to our Web team, and we hope to have the problem fixed soon. Megan Bearly Associate Editor, SQL Server Magazine mbearly@sqlmag.com]]>meganbearly Tue, 05 Feb 2008 17:10:25 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 05 Feb 2008 10:21:48 GMT
Fix link soon please......Then usefulness will be higher]]>BrettTue, 05 Feb 2008 10:21:48 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorTue, 05 Feb 2008 01:46:41 GMT
Link is broken.]]>BenoitTue, 05 Feb 2008 01:46:41 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 04 Feb 2008 08:34:54 GMT
The Article is very good but without looking at the code it’s kind of hard to make a final rating I am sure it good, because I have been doing the same thing for sometime now just want to see the differences. Like performance and footprint size on the server when running. And still a broken link. Does this Magazine care about quality? This not the 1st time they have had broken links. Seems to be the norm for a professional magazine I would expect better results to my paying customers. If I published a web site that did not work I would lose my job.]]>MikeMon, 04 Feb 2008 08:34:54 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorSun, 03 Feb 2008 20:25:56 GMT
Link is Broken]]>MikeSun, 03 Feb 2008 20:25:56 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 01 Feb 2008 17:46:47 GMT
Code link still not working."Sorry the page you are trying to reach is temporarily unavailable or the page no longer exists." Good thing this is not a Tech site.]]>DonaldFri, 01 Feb 2008 17:46:47 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorFri, 01 Feb 2008 09:49:58 GMT
Great article. Too bad the site is letting us (subscribers) down... :(]]>DexterFri, 01 Feb 2008 09:49:58 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorThu, 31 Jan 2008 13:49:27 GMT
I have brought this to the their attention. I have several emails from readers asking for the code directly. I will send that along as soon as I can. In the mean time please keep checking back as they are working on the code link. Rodney Landrum - author.]]>RODNEYThu, 31 Jan 2008 13:49:27 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorThu, 31 Jan 2008 10:14:20 GMT
Any idea on when http://www.winnetmag.com/Files/09/97840/97840.zip will be available? Thanks]]>FrankThu, 31 Jan 2008 10:14:20 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorThu, 31 Jan 2008 06:11:40 GMT
Very interesting article with very relevant information. This makes it doubly unfortunate that the download is not available!]]>KeesThu, 31 Jan 2008 06:11:40 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 29 Jan 2008 11:45:26 GMT
dmbranscome, I’m glad you find the article valuable. Check out Rodney’s follow-up article on his SSIS/SSRS solution in the Feb. issue of SQL Server Magazine, online at http://www.sqlmag.com/Article/ArticleID/97840/sql_server_97840.html.]]>AnneTue, 29 Jan 2008 11:45:26 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 28 Jan 2008 16:59:28 GMT
Not for nothing, but where’s the link?]]>FrankMon, 28 Jan 2008 16:59:28 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 28 Jan 2008 11:58:38 GMT
Also the "97840.zip" referenced in the begining of the article is not attached to the article to download.]]>StefanMon, 28 Jan 2008 11:58:38 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 28 Jan 2008 09:40:24 GMT
Good Catch, dguillory. I will let the editors know this morning. Rodney Landrum - author.]]>RODNEYMon, 28 Jan 2008 09:40:24 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorMon, 28 Jan 2008 09:23:14 GMT
fyi.. the links(to gifs) in the article are broken]]>DonaldMon, 28 Jan 2008 09:23:14 GMThttp://www.sqlmag.com/article/sql-server-integration-services-ssis/use-ssrs-and-ssis-to-create-a-dba-repository#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 15 Jan 2008 18:33:42 GMT
at a minimum the article provides further examples of SSIS implementations. This should be good. Thanks dmb]]>DavidTue, 15 Jan 2008 18:33:42 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 21 Nov 2007 09:54:58 GMT
Don’t know if anyone has pointed this out, but we seemed to have trouble with named instances on servers with more than the default. Discovered that we had to use Servername and port instead of Servername\Instance. That seems to work.]]>RickWed, 21 Nov 2007 09:54:58 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 21 Nov 2007 09:16:04 GMT
What do I do if the stored procedure I am running returns more than one recordset? I want to run a system stored procedure on many server (SQL Server 2000 and 2005). I want the data to go into tables that I can analyze on one server.]]>STEPHENWed, 21 Nov 2007 09:16:04 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 18 Sep 2007 07:43:55 GMT
sgreene - I see your point. Though I stated on page 2 (web) how to set the Connection Manager object expression, I had the crucial details @[User::SRV_Conn] in a screen shot (Figure 8) - not in text. I am glad you figured it out. Yes..all SSIS users unite. I am working on a follow up article right at this moment that has many enhancements to this original SSIS package. Look for it in the upcoming few months. Thanks for the kind words. Rodney Landrum - author.]]>RODNEYTue, 18 Sep 2007 07:43:55 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 18 Sep 2007 07:36:05 GMT
rsqlmag, This could be caused by a number of things. Here are a couple to check out. Named Pipes is not enabled by default. If for whatever reason a Named Pipe connection is being tried for the 2005 servers you can enable it via the SQL Server Configuration Manager under SQL Server 2005 Network Configuration. Also, make sure that remote connections are allowed. This can be enabled with the Surface Area Configuration for Services and Connections. Set Remote Connections to use both, or if local connections only is checked select, allow local and remote connections. On SQL Server Express, Local connections only is the default if I am not mistaken. Hope this helps. Rodney Landrum - author.]]>RODNEYTue, 18 Sep 2007 07:36:05 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 12 Sep 2007 11:19:12 GMT
Great article, this is going to be immensly useful! One bit of info, not explicitly stated, that I was having trouble finding, is how the connection is dynamically set. You have the variable set, but never mention that. For those who might be having the same problem finding this vital bit of info(ssis beginners unite!), it is set in the connection manager as an expression: @[User::SRV_Conn].]]>samuelWed, 12 Sep 2007 11:19:12 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorThu, 06 Sep 2007 12:49:52 GMT
Great Article...and appreciate the generosity for the code....:) I did a similar solution and my SSIS package just gets the backup info....But when I connect to SQL2005 servers I get this message...I am executing the SSIS packages from a JOB in SQL2005, the agent account is a domain account and it has access to all servers...I am able to get information for all 2000 servers but 2005 servers have this issue: Any input will be appreciated: Error: 2007-09-06 02:26:48.54 Code: 0xC0202009 Source: DatabaseBackupsForEachServer Connection manager "Source Conn" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "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: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: No process is on the other end of the pipe. ". End Error Error: 2007-09-06 02:26:48.55 Code: 0xC020801C Source: Data Flow Task Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Conn" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error]]>RangaThu, 06 Sep 2007 12:49:52 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorWed, 25 Jul 2007 10:05:41 GMT
Thanks for your feedback! I’ll pass it along to Rodney. Let us know what other topics you’d like to see covered in SQL Server Magazine! Anne Grubb, Web Lead Editor, SQL Server Magazine and Windows IT Pro, agrubb@windowsitpro.com]]>AnneWed, 25 Jul 2007 10:05:41 GMThttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorMon, 23 Jul 2007 16:23:09 GMT
This article along with 95385 were great in allowing me to automate some reports to give quick view of what’s happening on our servers. Cheers]]>StephenMon, 23 Jul 2007 16:23:09 GMThttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 18 Jul 2007 12:05:59 GMT
Awesome! Have been wanting something like this for quite awhile since we have been adding SQL instances at an alarming rate. Seemed like such a huge task because I haven’t used SSIS much, but your instructions were excellent. Added steps to audit logins, roles and database users. In order to do that I did have to add a default value to the SVR_Conn variable. On to the reports! Thanks again.]]>AllisonWed, 18 Jul 2007 12:05:59 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorThu, 28 Jun 2007 05:42:56 GMT
Cool article and use of the SSRS tools]]>BillThu, 28 Jun 2007 05:42:56 GMThttp://www.sqlmag.com/article/business-intelligence-development-studio-bids/sql-server-reporting-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorThu, 14 Jun 2007 11:41:02 GMT
sqlmast, I would be willing to discuss the article, SSIS, Reporting Services et al with you and your DBA team. My e-mail is posted in the bio if you like to contact me directly. Thank You, Rodney Landrum - author.]]>RODNEYThu, 14 Jun 2007 11:41:02 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorWed, 13 Jun 2007 16:00:30 GMT
Hi Rodney, Thanks for the article. I am new to sql but, by doing these types of things, it really helps to broaden my understanding. When I attempt to process the mining model, I get the following error message. OLE DB error: OLE DB or ODBC error: The text data type cannot be selected as DISTINCT because it is not comparable.; 42000. Thanks jjg]]>gray ghost,jjgonzales Wed, 13 Jun 2007 16:00:30 GMThttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 13 Jun 2007 08:12:07 GMT
I know that I can connect to each server and I know that I have the rights to the servers. The two top errors that I get are 1) [OLE DB Source [1]] Error: 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: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "’SERVERPROPERTY’ is not a recognized function name.". 2) [Connection manager "MultiServer"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user ’**********\***DBA’.". -I think that the main problem may be the connection manager in the second part. Maybe the login is wrong. I have a system where I can connect to the servers via windows authentication, but when I remote desktop out to the server, I need to use a special account called ***DBA. I have used ’*’ to denote company information. Could this be the problem with multiserver? If not I am open to suggestions. Finally, if the author would be interested, I would love to have a discussion, aka learn from the author, about SSIS and the configuration database. I know that it would most likely be a treat for my team to be able to arrange this.]]>WaqarWed, 13 Jun 2007 08:12:07 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 12 Jun 2007 09:15:04 GMT
Note from author Make sure that you can connect to each of the server individually via SSMS with the same name as what is in the Serverlist_SSIS table that feeds the variable. Next, make sure you have permissions to access each of the servers in the list. Finally, check the Progress tab for the detailed error and also the Error List and Output windows. Rodney Landrum]]>RODNEYTue, 12 Jun 2007 09:15:04 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorMon, 11 Jun 2007 15:50:19 GMT
I keep getting several errors, seemingly the largest is the problem with the Load Servers task. It always turns red with the OLE DB Source turning red too. I have tried the "delay validation" but it doesn’t seem to work. What could be some possible problems and solutions?]]>WaqarMon, 11 Jun 2007 15:50:19 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorThu, 07 Jun 2007 14:22:16 GMT
I get an error of .....An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x800004005 Description: "Login timeout expired"]]>SUEThu, 07 Jun 2007 14:22:16 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorSat, 26 May 2007 21:27:52 GMT
one issue I see with sqlcmd /Lc is it will only return sql servers listening in its immidiate subnet, which are likely to only be a small subset if all sql servers.]]>BorisSat, 26 May 2007 21:27:52 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorFri, 25 May 2007 07:26:43 GMT
salagheh, Weblisting 3 contains the DDL to create the HoldForEachDB table in TempDB that is required for the validation to complete and for the package to execute locally. Also, after each restart of SQL Server, this table will be deleted because of it’s reseidence in TempDB. This only needs to happen on machine where the SSIS package is executing. It will be dynamically created on each target server at runtime. To avoid this, you can also set each objects in the package to delay validation. I have found that this corrects the issue permanently with no need to recreate the HoldForEachDB table. Rodney Landrum (author)]]>RODNEYFri, 25 May 2007 07:26:43 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorFri, 25 May 2007 02:00:15 GMT
I get the following error on some of my servers, is there a work around for this ? also is there a way to find out which server on the list is generating this error ? Error 1 Validation error. Load Databases: OLE DB Source [1]: 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: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name ’tempdb.dbo.HoldforEachDB’.". Populate_DBA_REP.dtsx 0 0]]>SamanFri, 25 May 2007 02:00:15 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorWed, 16 May 2007 11:25:35 GMT
*****Note from Author**** Another possible resolution to the issue described in the first reader comment "Error at Populate_DBA_REP [Connection manager "MultiServer"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005." There is a property setting on each of the objects in the package that will delay validation until runtime. I suspect that what is happening is that the package is trying to establish the connection to the MultiServer data source server, which is actually a variable set at runtime. If you set the delay validation object property to TRUE in the object properties (Load Servers Task for example), you should get past the validation issues. Set this property on each object that shows invalid and the package should execute.]]>RODNEYWed, 16 May 2007 11:25:35 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorThu, 10 May 2007 11:07:51 GMT
Haywood, The connection to QASRV.DBA_REP will be the connection to the repository database itself created from a script provided in the code download. I assume you have created this DBA_Rep database and the QASRV.DBA_REP is pointing to that database. Also, make sure that you can connect to the servers via Management Studio that are in the ADO recordset populated via sqlcmd /Lc in the ServerList_SSIS table. Let me know if I can be of more help getting this working in your environment. (Also, was their a specific SQL error such as "Server does not exist or access denied" message?) Rodney]]>RODNEYThu, 10 May 2007 11:07:51 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorTue, 08 May 2007 10:51:25 GMT
I can’t seem to get this to run. It complains about the MultiServer connection. I followd the article and everything _should_ work, but it isn’t. Am I the only one who can’t get it to work? I updated the reference to QASRV.DBA_REP to a valid server/db & table on one of my servers. I can populate the ADO recordset successfully, it errors on the ForEach loop... Error at Populate_DBA_REP [Connection manager "MultiServer"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Also, the ForEach tasks that populate the tables fail validation, I assume because the MultiServer obejct hasn’t been populated with a value yet...so it may be acceptable?]]>GORDONTue, 08 May 2007 10:51:25 GMThttp://www.sqlmag.com/article/task-scheduling/sql-server-integration-services#commentsAnchorhttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorFri, 09 Mar 2007 12:48:26 GMT
I would take a look at the content types of the mining model fields making sure that the fields N1 to N6 are set to “discrete”. It might also be worthwhile to try different combinations of field types; the draw date will be the Key field and N1 to N6 can be Input Only or Input and Predict. In the DMX query, then, it is possible to use the number fields themselves in the predict join as well as the dates. For example, you might want to write a DMX query (or use the singleton query designer) that predicts the draw numbers for March, 24, 2007 but should return different results if the criteria was March, 24, 2007 AND N3 was 51 AND N2 was 21. Breaking out the DrawDate field to months, days and years, which I believe I did in the article, should also add more level of prediction. I hope this helps. I will have to build the solution again in order to test these concepts as it has been quite a long time since I wrote the article. I certainly do appreciate the reader’s comments. Thanks. Rodney]]>DianaFri, 09 Mar 2007 12:48:26 GMThttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorhttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorThu, 08 Mar 2007 11:25:02 GMT
Hi Rodney, I put together a mining package similar to the one you feature here but I can’t get the query to return anything different for different input dates. It always returns the exact same set. I did get a different set for the clustering algorithm than I did for the decision tree. Any ideas as to how to modify the query so that the results reflect the day specified in the query?]]>ChrisThu, 08 Mar 2007 11:25:02 GMThttp://www.sqlmag.com/article/sql-server/win-the-lottery-on-your-way-to-analysis-services-2005-mastery#commentsAnchorhttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorMon, 19 Feb 2007 09:07:09 GMT
Very nice. Worked great]]>bob@twelvethree.com Mon, 19 Feb 2007 09:07:09 GMThttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorhttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorTue, 02 May 2006 12:15:50 GMT
Hi, I implemented the above explained "Expand All and Collapse all Grouping" in Territory Sales DrillDown report and its not working. I followed all the steps as written but the value of Expand_All parameter is not passed to lower groupings. Please explain if there is any missing step. Thanks,]]>priyankaTue, 02 May 2006 12:15:50 GMThttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorhttp://www.sqlmag.com/article/tsql3/reporting-services-and-multivalued-parameters#commentsAnchorFri, 23 Dec 2005 12:56:58 GMT
Another scenario I forgot to mention is when the parameter being passed is a GUID. Since guids are by definition a fixed length when part of a string consisting of an array of guids, there is an even more efficient parsing method credited by Sommarskog to Ken Henderson. Here is my implementation of the guid parser using nvarchar(MAX) to allow a basically unlimited number of GUID’s: CREATE FUNCTION dbo.fnParseGUIDs(@str nvarchar(MAX)) RETURNS @ParsedValues TABLE (pk int, GUID uniqueidentifier) AS BEGIN INSERT INTO @ParsedValues (pk, GUID) SELECT Number , CAST(LEFT(SUBSTRING(@str, 37 * (Number - 1) + 1, 37),36) AS uniqueidentifier) FROM Numbers WHERE Number <= DATALENGTH(@str) / (37 * 2) + CASE DATALENGTH(@str) % 37 WHEN 0 THEN 0 ELSE 1 END RETURN END Happy Holidays, Tim]]>TimFri, 23 Dec 2005 12:56:58 GMThttp://www.sqlmag.com/article/tsql3/reporting-services-and-multivalued-parameters#commentsAnchorhttp://www.sqlmag.com/article/tsql3/reporting-services-and-multivalued-parameters#commentsAnchorFri, 23 Dec 2005 12:37:55 GMT
We have definitely run into this issue on our current project. However, I don’t believe the UDF presented is particularly efficient as it basically uses a WHILE loop to do cursor-like row-based processing of the array. Here is an alternative I borrowed from Erland Sommarskog’s excellent article ’Arrays and Lists in SQL Server’ (http://www.sommarskog.se/arrays-in-sql.html#overview). It uses a prepopulated Numbers table which is simply an integer column with values from 1-8000. In order to get a key for ordering the list, I originally added an IDENTITY column, which I replaced with the SQL Server 2005 ROW_NUMBER() OVER syntax. CREATE FUNCTION dbo.fnParseDelimitedList (@list varchar(7998), @delimiter char(1)) RETURNS @ParsedValues TABLE (pk int, Value varchar(7997)) AS BEGIN INSERT INTO @ParsedValues (pk, Value) SELECT ROW_NUMBER() OVER(ORDER BY Number), SUBSTRING(@delimiter + @list + @delimiter, Number + 1, CHARINDEX(@delimiter, @delimiter + @list + @delimiter, Number + 1) - Number - 1) AS Value FROM dbo.Numbers WHERE Number <= LEN(@delimiter + @list + @delimiter) - 1 AND SUBSTRING(@delimiter + @list + @delimiter, Number, 1) = @delimiter RETURN END Tim Overlund, MCDBA, MCSD.NET Database Practice Avanade, Inc]]>TimFri, 23 Dec 2005 12:37:55 GMThttp://www.sqlmag.com/article/tsql3/reporting-services-and-multivalued-parameters#commentsAnchorhttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorFri, 21 Jan 2005 09:12:18 GMT
more useful 4]]>Anonymous User Fri, 21 Jan 2005 09:12:18 GMThttp://www.sqlmag.com/article/reporting2/pushing-the-parameters#commentsAnchorhttp://www.sqlmag.com/article/user-management-and-profiles/winwhatwhere-investigator-3-0#commentsAnchorSun, 02 Jan 2005 23:56:12 GMT
nice program i love it]]>Anonymous User Sun, 02 Jan 2005 23:56:12 GMThttp://www.sqlmag.com/article/user-management-and-profiles/winwhatwhere-investigator-3-0#commentsAnchorhttp://www.sqlmag.com/article/auditing/enterprise-security-reporter-1-75#commentsAnchorThu, 11 Nov 2004 06:33:09 GMT
xxxx]]>Anonymous User Thu, 11 Nov 2004 06:33:09 GMThttp://www.sqlmag.com/article/auditing/enterprise-security-reporter-1-75#commentsAnchorhttp://www.sqlmag.com/article/scripting/sql-compare-1-14#commentsAnchorMon, 29 Jan 2001 10:10:46 GMT
Another product that does database comparisons is DBArtisan Change Manager (CM) from Embarcadero Technologies. I’ve used CM for over a year now, and I’m very happy with it -- although there are shortcomings, like how it won’t compare object permissions. The price is significantly more than the $50 for SQL Compare, but CM supports a long list of database platforms, including MS SQL Server 6.5 and up, Oracle, etc. One big feature of Change Manager is that it can store an "archive" of a live database which you save at any point in time, then compare any archive to a live database. It can also compare two archives, or two live databases. The comparisons against live databases are always real-time, so there is no lengthy registration process required like with SQL Compare. CM also will run the synchronization scripts on the target database, or let you save the scripts (for one, selected, or all objects) so you can run them later. I’ve used CM on MS SQL Server 6.5 databases only. I always keep two live databases, one of which is a copy of the latest production version, and the other is my development version that I make changes to. CM has made my life easier when deploying updates to the production server, but I’ve found that I always need to eyeball the scripts it creates just to make sure it’s doing what I want (I sometimes edit the object creation order and comments added by CM).]]>Steve Fisher Mon, 29 Jan 2001 10:10:46 GMThttp://www.sqlmag.com/article/scripting/sql-compare-1-14#commentsAnchor