What is the difference between using First..Last and 1..count in Oracle? FOR i IN l_collection.FIRST..l_collection.LAST LOOP or FOR i IN 1..l_collection.COUNT LOOP

[The Data Collector](https://learn.microsoft.com/en-us/sql/relational-databases/data-collection/data-collection?view=sql-server-ver16) is the single most obscure SQL Server concept that I know of. I have never even seen it _mentioned_ in a professional or educational context, never mind _used_. I don't even think that it has a tag on this website, although I have made my best guesses. Yet, its [support in dbatools](https://docs.dbatools.io/Copy-DbaDataCollector) is a strong sign that it must have some value. I have read the documentation for the Data Collector with an open mind, but I simply cannot fathom a use case for it. What are some typical use cases in supported (i.e. 2012+) versions of SQL Server?

I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec. Isolating that out, I can reproduce the issue by running: & "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1 I run the above command from a PowerShell window that I've opened with **RunAs Administrator**. The account I am running the above with is a domain account that is a **local administrator** on the server. In addition, the account also has the **sysadmin** role on the SQL instance. The error that I get is: SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540, Subcomponent: Generate T-SQL Package Task, Description: An error occurred with the following error message: "An error occurred while verifying the result set schema against the output table schema. The data collector cannot connect to the management data warehouse. : Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.".. The master package exited with error, previous error messages should explain the cause. That is the same error I see in the history on the SQL Server Agent job. Note that if I execute dcexec with a bad instance name, such as: & "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1 Then, as expected, I get a different error: Failed to get the SQL server instance id from the given instance name. Any ideas on how to successfully login to the SQL instance with dcexec?

I'm a newbie in mongoDB, and I just would like to ask a simple question. In creating tables in MongoDB, do you go ahead in MongoDB compass and create the fields in a collection, or is there a command where you can generate the table fields declared in the model just like Laravel's php artisan migrate?

I configured MDW on an SQL Server instance but when I try to access the reports from the MDW DB, the reports show information until a couple of days ago and I am getting 'collection set is not running. The data displayed here is from a previous run' as per below: Why does this happen?

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However, when I run msdb.dbo.sp_syscollector_update_collection_item on SQL 2012, I get this error: > Msg 14696, Level 16, State 1, Procedure > sp_syscollector_update_collection_item, Line 70 Cannot update or > delete a system collection set, or add new collection items to it. Looking at the code of msdb.dbo.sp_syscollector_update_collection_item, I see differences between the SQL versions: **SQL 2008 R2/SQL 2014** IF (@is_system = 1 AND (@new_name IS NOT NULL)) BEGIN -- cannot update, delete, or add new collection items to a system collection set RAISERROR(14696, -1, -1); RETURN (1) END
**SQL 2012** IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL)) BEGIN -- cannot update, delete, or add new collection items to a system collection set RAISERROR(14696, -1, -1); RETURN (1) END
I'm passing in a non-NULL value for @parameters, so I fully understand why I'm getting the error on SQL 2012. **Questions:** 1. Why is the code different for SQL 2012? 2. Is it safe to manually change the code for msdb.dbo.sp_syscollector_update_collection_item?
**Update:** (version numbers for further clarification)
Ver SP Build Number -------------------------- 2008 R2 SP3 10.50.6000.34 2012 SP2 11.0.5058.0 2014 SP1 12.0.4100.1 **Update2:**
I've updated SQL 2012 to SP3 (11.0.6020.0)
The issue persists.

I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network. Each measurement is lightweight (between 10-20 bytes). Now, I'm wondering what is the best strategy: InfluxDB + Zabbix? Or Telegraf? Or another alternative? 1) InfluxDB + Zabbix: according to https://www.zabbix.com/integrations/influxdb there is a possible integration. However, it seems the integration involves taking data that was collected in a Zabbix database and moving it to an InfluxDB to take advantage of InfuxDB using much less storage. So they meant it to be for archiving large amounts of data. However, we believe Zabbix itself is too slow, no? The integration above would not work for online data collection, right? We need fast storage, and we do not want Zabbix to be a bottleneck. Are there known benchmarks and recommendations about Zabbix + InfluxDB integration, for online collection + storage? 2) InfluxDB + Telegraf: according to https://www.influxdata.com/blog/monitoring-openwrt-with-telegraf/ there is also the possibility of integrating InfluxDB + Telegraf for data collection. Are there best practices and/or benchmarks about data collection with Telegraf + storage with InfluxDB? 3) Are there other alternatives and/or suggestions for large scale data collection and integration with InfluxDB?

I want ti copy my collection including is indexes without the data. how can I do it in the most efficient way? I am using a replica set version 4.0.3

I have SQL Server running on 32-core CPU, and I use Performance Monitor (perfmon.msc) to collect data on Processor: % Processor Time (Instance - _Total) and Process: % Processor Time (Instance - sqlservr.exe) So for example for day 2019/07/21 (Sunday), it shows average values: Processor: % Processor Time (Instance - _Total) - 3.959 Process: % Processor Time (Instance - sqlservr.exe) - 107.312 Why is such a difference ? I expected sqlservr.exe to show values like 2.900 or 3.100 - something like this, but not 107.312 How to correlate _Total to sqlservr.exe ? I am using MaxDOP limited to 8 server level setting on SQL Server, if this is any help

tl;dr: what Windows permission / configuration is needed for the "Server Activity" data collection job to run successfully? --- I'm running SQL Server 2016 SP1 CU6 (13.0.4457.0) on Windows Server 2012 R2 Datacenter. Whenever I try to run the "Server Activity" data collector, the SQL Server Agent job fails, and I see the following three errors in the Application event log on the box. > Unable to open the Server service performance object. The first four bytes (DWORD) of the Data section contains the status code. Source: PerfNet, Event ID: 2004 > The Open Procedure for service "WmiApRpl" in DLL "C:\Windows\system32\wbem\wmiaprpl.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code. Source: Perflib, Event ID: 1008 > The Open Procedure for service "BITS" in DLL "C:\Windows\System32\bitsperf.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code. Source: Perflib, Event ID: 1008 This is happening on all 4 of the SQL Server boxes where I'm running these collectors. The other two collectors (Query Statistics and Disk Usage) are working fine on all 4 servers. Things I have tried that had no effect: - Add SQL Server Agent user (Windows account) to the local "Performance Monitor Users" group (source ) - Reload the performance library with the "lodctr" command (source ) - Turn on the "WMI Performance Adapter" service and set to automatic (source ) - Run the lodctr command at C:\Windows instead of C:\Windows\system32 (source ) Finally, I tried temporarily adding the Windows account that SQL Server Agent runs as to the local administrators group on one of the boxes and rebooting it. **This allowed the job to run successfully** (although the BITS error still appeared in the application event log). Reversing this and rebooting caused the job to start failing again. I don't want that service account to be a local admin on this box, and I'd be shocked if it needs to be. So what are the minimum permissions needed for this account to be able to access the performance counter data needed for this data collection set? I have scripted out the job details, in case that is helpful: USE [msdb] GO /****** Object: Job [collection_set_2_collection] Script Date: 3/1/2018 2:17:35 PM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [Data Collector] Script Date: 3/1/2018 2:17:35 PM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'collection_set_2_collection', @enabled=0, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'Data Collector', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** Object: Step [collection_set_2_collection_collect] Script Date: 3/1/2018 2:17:35 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'collection_set_2_collection_collect', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=3, @retry_interval=5, @os_run_priority=0, @subsystem=N'CmdExec', @command=N'dcexec -c -s 2 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))" -m 0 -e $(ESCAPE_NONE(STOPEVENT))', @flags=80 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** Object: Step [collection_set_2_collection_autostop] Script Date: 3/1/2018 2:17:35 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'collection_set_2_collection_autostop', @step_id=2, @cmdexec_success_code=0, @on_success_action=2, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec dbo.sp_syscollector_stop_collection_set @collection_set_id=2, @stop_collection_job = 0', @database_name=N'msdb', @flags=16 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunAsSQLAgentServiceStartSchedule', @enabled=1, @freq_type=64, @freq_interval=0, @freq_subday_type=0, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20160430, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'ec88e0b9-88cf-454b-a4c1-0397ef849519' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO

My MDW database's performance_counter_instances table has run out of identity values. Has anyone ever encountered this? Is it safe to change the data type of the identity field performance_counter_id from INT to BIGINT without breaking anything? Bonus Question - What is the impact of this field running out of identity values? I can't find anything useful about this issue online. I have converted the data type to BIGINT using [this script](http://pastebin.com/FSbRuu1v) and now all of the collection_set_2_upload jobs are failing with the following error message: >LKU - Lookup into performance_counter_instances to obtain performance_counter_id for all counter paths that get inserted.Outputs[Lookup Match Output].Columns[performance_counter_id] and reference column named "performance_counter_id" have incompatible data types. Please let me know if this is something that can be fixed or if I will have to just eventually recreate the MDW database again. This happens about once every 6 months in my environment. For now I've reseeded the identity to -2147483647 to buy some time. DBCC CHECKIDENT ('snapshots.performance_counter_instances', RESEED, -2147483647)

I have the following query that outputs some good information from MDW data collectors regarding database space usage. I am wondering with the following columns (db size, reserved space, unused space, unallocated space, collection date) how can i properly calculate database growth trend? I am trying to keep whether a db shrinks or grows into consideration so I cannot just take "max size" and go from that. Here is the full query so far... DECLARE @ServerName VARCHAR(MAX); DECLARE @SelectedDatabaseName VARCHAR(MAX); DECLARE @snapshot_id INT; SELECT TOP 1 @snapshot_id = snapshot_id FROM ( SELECT DISTINCT TOP 100 d.snapshot_id FROM snapshots.disk_usage d , core.snapshots ss --WHERE ss.instance_name = @ServerName --AND ss.snapshot_id = d.snapshot_id ORDER BY d.snapshot_id DESC ) AS q ORDER BY snapshot_id ASC; SELECT database_name , ss.instance_name , CONVERT (DATETIME, SWITCHOFFSET(CAST (d.collection_time AS DATETIMEOFFSET), '+00:00')) AS collection_time , d.snapshot_id , ( ( CONVERT(DEC(15, 2), d.dbsize) + CONVERT(DEC(15, 2), d.logsize) ) * 8192 / 1048576.0 ) AS 'database_size_mb' , 'reserved_mb' = ( d.reservedpages * 8192 / 1048576.0 ) , 'data_mb' = CONVERT(DEC(15, 2), d.pages) * 8192 / 1048576.0 , 'index_mb' = ( d.usedpages - d.pages ) * 8192 / 1048576.0 , 'unused_mb' = ( ( CONVERT(DEC(15, 2), d.reservedpages) - CONVERT(DEC(15, 2), d.usedpages) ) * 8192 / 1048576.0 ) , 'unallocated_space_mb' = ( CASE WHEN d.dbsize >= d.reservedpages THEN ( CONVERT (DEC(15, 2), d.dbsize) - CONVERT (DEC(15, 2), d.reservedpages) ) * 8192 / 1048576.0 ELSE 0 END ) FROM snapshots.disk_usage d , core.snapshots ss WHERE --database_name =@SelectedDatabaseName /*AND*/ d.snapshot_id >= @snapshot_id --AND ss.instance_name = @ServerName AND d.snapshot_id = ss.snapshot_id ORDER BY d.database_name ASC , collection_time; Hopefully at the end of it I will be able to see in a year from now how much every instance, database and server grows per year.

Execute immediate call causes 'variable not in select list' error hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code: set serveroutput on; declare type TTabList is table of table1%rowtype index by pls_integer; vtabList TTabList; vstrQuery varchar2(32000); vNum number(10) := 100; vStrs varchar2(100) := '''45'',''11'',''20'',''71'''; begin dbms_output.enable; vstrQuery := 'select field1 from table1 where field1 = '|| vNum ||' and field2 in ('|| vStrs ||')'; execute immediate vstrQuery bulk collect into vtabList; if vtabList.count = 0 then dbms_output.put_line('nothing to show!'); else while vtabList.count > 0 loop for i in vtabList.first .. vtabList.last loop dbms_output.put_line('we have: '|| vtabList(i).field1); end loop; vtabList.delete; end loop; end if; end; But its results following error which i can't resolve: Error report - ORA-01007: variable not in select list ORA-06512: at line 14 01007. 00000 - "variable not in select list" *Cause: *Action: Any ideas to fix this?

I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I select any of the reports from the MDW with Data Collection > Reports > Management Data Warehouse I receive the error: > *Exception has been thrown by the target of an invocation - > Object reference not set to an instance of an object.* This occurs for all 3 reports and after I've waiting some time and data has been uploaded from the data collector. I do not have SSRS running, but read that isn't necessary. Any suggestions?

Let's say I have a table Cars (with columns such as model, make, year, etc.), a table Books (with columns such as name, author, ISBN, etc.) and a table Wines (with columns such as name, appellation, region, vintage, etc.) Now, even if these tables don't seem related at all, I want to make a collection of all these things called "My favorites things in the world". In this list, I will find cars, books and wines. If I only wanted to have lists of cars, I would create a table called Car_Lists with id and name + a table called Car_List_Items with id, car_list_id (foreign key to the id in the table Car_Lists) and car_id (foreign key to the id in the table Cars). Here, as I want to have a list of different types of things, I don't really see how this could be done. So my question: How would I represent that with a relational database?

I have a column in utf8_unicode_ci collation for text type in MySQL. The content is in Unicode, I have used tags like {br}, {v}, {t}, {h} etc.., throught the database. I am now thinking of changing these to single characters from Unicode table like ⓑ, ⓥ, ⓣ, Ⓗ and so on and so forth . Will this have any effect on the size of the table? (Increase or decrease) Should I be worried about any other side effect? Will this have any effect if type of the column was varchar?

In SQL Server 2012 R2 Enterprise, whenever I check Management\Data Collection, right-click --> Configure Management Data Warehouse , I see whatever users-accounts-logins except the one I am logged in I cannot understand what's the point in "Map Logins and Users" in Management\DataCollectiion of SQL Server?

I want to know **any disadvantage can occur by doing collect stats on a column** that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 days) While searching in google, i came to know the only issue that can cause performance degradation is *out of date Stats *. In this case, this is not going to happen as whenever data of table changes, we are going to run collect stats. Any other disadvantage is there because of adding collect stats other than space consumption ?

For SQL Data Collector: Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one that the MDW database is on.

I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management Studio on a different machine (which is using VPN to connect to the network the SQL Server is on). The error message is: > Failed to connect to server x.x.x.x. --> Login failed. The login is > from an untrusted domain and cannot be used with Windows > authentication. The server uses mixed mode authentication. Data Collection is configured to use an SQL Login and the login that tries to generate the report is an SQL Login as well. They've got nothing to do with domains. Why do I get this error?