| Here's a few of the new posts today on the forums. To see more, visit the forums. |
| SQL Server 2016 - Administration |
| ExecuteScalar: CommandText property has not been initialized - One ASP.NET project keeps on receiving a message below from "Try...Catch...End try" block. System.InvalidOperationException: ExecuteScalar: CommandText property has not been initialized I searched Google but can't get help. Is this SQL script mistake? Please help. --------------------------------------------------------- It points at a function from app below dim iCount as Int32 = 0 dim SQL as string = […] |
| How to stop database restore that survived reboot showing progress in error log - SQL Server 2016 SP CU7 on Windows Server 2012 R2 Standard. Backstory: Agent job runs daily that performs restore of database in about 30 minutes (360G MDF). All of a sudden one day restore still running hours later. Waited it out. Ran in 18 hours then several hours later early morning restore ran as normal. […] |
| using Powershell scripts to do SQL backups - Hello, hopefully this can be a straight forward question, but at the same time, looking to get some advice and if possible experience from someone that tried this... but as the title says, My company has large databases, about 1.5 tb, and dont want to invest in software, but of course we do have good […] |
| Monitoring CDC - Hi All I've noticed our CDC capture job causing blocking for hours on end at a time. Digging around, I've found the latency using this script to reach 1775 SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 I'm hoping that someone can assist me with really figuring out how far behind it actually is. […] |
| SQL Server 2016 - Development and T-SQL |
| Nested FOR XML Problem - I have the following data and I want to generate XML where the transactions are nested under the position for the relevant asset. CREATE TABLE #temptable ( [bp_sym] varchar(50), [person_key] varchar(100), [bargain_date] date, [disp_acqu_text] varchar(9), [quantity] decimal(12,2), [cumulative_qty] decimal(12,2), [book_cost_change] decimal(12,2), [cumulative_book_cost] decimal(12,2), [swim_refs] varchar(6), [security_code] char(7), [asset_name] nvarchar(4000) ) INSERT INTO #temptable ([bp_sym], [person_key], […] |
| SQL 2012 - General |
| Run same query on multiple servers with sqlcmd and save the output - Hello, I have this 3 lines: sqlcmd -S "Server1" -d -U -P -i query.sql -o result.txt sqlcmd -S "Server2" -d -U -P -i query.sql >> result.txt sqlcmd -S "Server3" -d -U -P -i query.sql >> result.txt If I copy and paste this code to a cmd […] |
| When run query for only part it take too much time so How to solve this issue ? - I work on sql server 2012 query I face issue : when run query return 10 rows for only one part it take 50 second I try to run it in another time may be pc have more load but it take same time 50 second to return 10 rows for only one part . […] |
| SQL Server 2012 - T-SQL |
| Split dates based on Start and End Dates - Hi Team, I'm seeking help on date split, the dates can start between month also. below is the sample code and output. Thank you! Create Table #DATE_SPLIT ( ID INT, StartDate DATE, EndDate DATE ) INSERT INTO #DATE_SPLIT SELECT 10,'01/10/2020','03/21/2020' UNION ALL SELECT 10,'03/22/2020','12/31/9999' --OUTPUT ID StartDate EndDate 10 '01/10/2020' '01/31/2020' 10 '02/01/2020' '02/29/2020' […] |
| SQL Server 2019 - Administration |
| File Paths in sysfiles on Replica Database - Morning Guys, How can I get the correct file path from sysfiles (or anywhere in the database with t-sql) when the db is the read only replica. It appears that sysfiles shows the location of the files on the principle database not on the replica. In this case the are in different locations. Cheers […] |
| SQL Server 2019 - Development |
| create view out of dynamic query from INFORMATION_SCHEMA.COLUMNS - Hi, Is there a way to create a view out if this query below? DECLARE @query nvarchar(max) SELECT @query = STUFF( ( SELECT ' UNION ALL SELECT ''' + TABLE_SCHEMA + ''' AS SCHEMANAME, ' + '''' + TABLE_NAME + ''' AS TABLENAME, ' + 'LEFT(''' + TABLE_NAME + ''', 3) AS SYSTEM, ' + […] |
| Get multiple values out of xml - Hi I have the following Syntax: DECLARE @MyXMLString XML SET @MyXMLString = ' Woonkamer + Eetkamer Woonkamer 1 1 11.3 3.86 43.62 2.5 109.05 Zuluft 151 0 1 1 Keuken Open keuken 6 1 3.82 2.42 9.24 2.5 23.1 […] |
| Reporting Services 2005 Development |
| Average in a Matrix - Hello I'm using the Matrix Wizard and everything is working right except one total that I modified. See image: The detail columns total at the bottom and along the row without any problem. But in my last column 'Avg FTE' I'm trying to get an average. The average works across the row, but not at […] |
| General |
| Looking for custom T-SQL - I've inventoried all our SQL Servers, Service Accts, etc in a home-grown Inventory DB. Simplified here, my Tables\Cols\Data include: TABLE=ServerName w/ Cols: ServerName, Active TABLE=ServiceAccts w/ Cols: ServerName, SvcAcct, Service ServerName Active SvcAcct Service ========== ====== ======= ======= Server123 N SvcAcct123 SQL Service ServerABC N SvcAcctABC <--- SQL Service ServerABC N SvcAcctABC <--- SQL Agent […] |
| SSDT |
| SSIS Expression help - Hi Guys,/ Is anyone can help me convert this SQL to SSIS Expression? SELECT DATEADD(DAY,DATEDIFF(DAY,0,CONVERT(DATE,DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) - 8)), '00:00:00') Thanks in Advance. |
| Design Ideas and Questions |
| Columnstore Index and Nonclustered Index on Table - We have a table definition below with a columnstore index and nonclustered row indices. The data in this table increases 10K per day and in a year can have 400K ColNumber with each having 1 to many ColProperty. This table will be used mostly for reads/reference and hardly have updates. Would it be beneficial to […] |
