Forum Widgets
Latest Discussions
Change SQL Login to AD service account for Link server
Hi Everyone, I tried to change account used to impersonate from SQL account to AD service account for link server however im getting below error. The AD service account has sys admin rights on the SQL servers. Access to the remote server is denied because no login-mapping exists. (Framework Microsoft SqlClient Data Provider) Any ideas how to resolve? Regards
Sql to calculate quarterly/annual aggregation aside of monthly numbers
Hi, I am struggling to calculate amounts based on mtd amounts w/o using a cursor. Any idea? create table #raw(quarter int, name varchar(10), year int, month int, amount decimal(19,2)) insert #raw(quarter, name, year, month, amount) values(1, 'aa', 2025,1,2.0),(1, 'bb', 2025,1,4.0),(1, 'cc', 2025,1,1.0), (1, 'aa', 2025,2,6.0),(1, 'bb', 2025,2,8.0), (1, 'aa', 2025,3,10.0),(1, 'bb', 2025,3,2.0),(1, 'dd', 2025,3,4.0), (2, 'ee', 2025,4,9.0),(2, 'bb', 2025,4,3.0),(2, 'cc', 2025,4,3.0),(2, 'aa', 2025,4,1.0), (2, 'ee', 2025,5,15.0),(2, 'bb', 2025,5,1.0),(2, 'cc', 2025,5,2.0),(2, 'aa', 2025,5,7.0), (2, 'cc', 2025,6,8.0),(2, 'aa', 2025,6,9.0) Annual calc is easy, but not sure how to add quarterly number aside: select mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount, sum(ytd.amount) as ytd from #raw mtd join #raw ytd on mtd.name = ytd.name and mtd.year = ytd.year and mtd.month >= ytd.month and mtd.quarter >= ytd.quarter group by mtd.quarter, mtd.name, mtd.year, mtd.month, mtd.amount order by 1,3,4,2 Goal is to get a report like this:
CSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.
Analysis Server Roles from Entra workstation
We have Analysis Server solution, maintained and deployed from Visual Studio, where the roles defined use AD groups. This worked perfectly as long as long as the machine used was on the same domain as the groups or was hybrid joined. On a new laptop that is Entra-only joined we do see a curious issue. The AD where the groups are located is a fully trusted domain. In SSMS (version 20.2) we now see this behavior in the Object Explorer: Connect to SQL Server Database Engine On-premise server A Navigate to Security -> Logins -> Right-click and select New Login Click on Search Click on Locations Full list of available domains to search is available Connect to SQL Analysis Services On-premise server A (so the same server) Navigate to Databases -> {Database name} -> Roles -> Right-click and select New Role Click on Membership and Add Click on Locations Only the local computer is available This prevents us from creating new roles with an AD group membership. Please note that everything else is working fine. Browsing as another AD user in any way works perfectly. Any ideas to why it behaves differently when connecting to database engine versus analysis services or what we can do to resolve this issue? PS! We see similar issues in Visual Studio if we try to edit roles in a Analysis Services Project. The assumption is that these both rely on the same underlying services to lookup objects on the AD domain. Thus if we resolve the SMSS issue we hope that also resolves the VS AS project issue. Regards, StigPossible GENERATE_SERIES memory leak?
Hey all, We've been experimenting with SQL Server 2022 features and stumbled upon what looks like a memory leak on every execution of the GENERATE_SERIES() function. A script that demonstrates it: SELECT * FROM sys.dm_os_memory_clerks WHERE [type] = 'MEMORYCLERK_SOSNODE' DECLARE @i INT = 1 WHILE @i <= 100000 BEGIN IF (SELECT COUNT(*) FROM GENERATE_SERIES(1, 1, 1)) != 1 BREAK SET @i += 1 END -- ~9ΜΒ leaked, ~96 bytes per iteration, on SOS_Node 0 SELECT * FROM sys.dm_os_memory_clerks WHERE [type] = 'MEMORYCLERK_SOSNODE' It can be reproduced on fresh SQL Server installations, in an empty database with default settings, on: Windows Linux (Ubuntu/docker) SQL Managed Instance on Azure It does NOT reproduce on Azure SQL. Using docker, we've tried every build from RTM up to CU12 that was just released, the behavior is exactly the same. The memory does not seem to ever be released. With enough iterations, SQL Server reaches a point where it cannot execute anything anymore. (when testing, lower the max memory setting to get there faster). Also captured a trace (with Tracking page allocations / freed for MEMORYCLERK_SOSNODE) and using SQLCallStackResolver this call stack appears thousands of times: 00 SqlDK!GenericEvent::PublishAndCallAction 01 SqlDK!XeSosPkg::page_allocated::Publish 02 SqlDK!MemoryClerkInternal::AllocatePagesWithFailureMode 03 SqlDK!MemoryClerkInternal::AllocatePages 04 SqlDK!CMemThread<CMemObj>::PbGetNewPages 05 SqlDK!TVarPageMgr<0>::PbAllocate 06 SqlDK!CMemObj::Alloc 07 SqlDK!CMemThread<CMemObj>::Alloc 08 SqlDK!operator new 09 sqllang!CTVFInfoGenSeries::PtiUserArgumentType 0a sqllang!CSTVFGenSeries::Init 0b sqlmin!CQScanTVFStreamNew::Open 0c sqlmin!CQScanNew::OpenHelper 0d sqlmin!CQScanStreamAggregateNew::Open 0e sqlmin!CQueryScan::UncacheQuery 0f sqllang!CXStmtQuery::SetupQueryScanAndExpression 10 sqllang!CXStmtQuery::InitForExecute 11 sqllang!CXStmtQuery::ErsqExecuteQuery 12 sqllang!CXStmtCondWithQuery::XretExecute 13 sqllang!CExecStmtLoopVars::ExecuteXStmtAndSetXretReturn 14 sqllang!CMsqlExecContext::ExecuteStmts<1,1> 15 sqllang!CMsqlExecContext::FExecute 16 sqllang!CSQLSource::Execute 17 sqllang!process_request 18 sqllang!process_commands_internal 19 sqllang!process_messages 1a SqlDK!SOS_Task::Param::Execute 1b SqlDK!SOS_Scheduler::RunTask 1c SqlDK!SOS_Scheduler::ProcessTasks 1d SqlDK!Worker::EntryPoint Note the CSTVFGenSeries::Init call. There's no corresponding free operation logged. Anyone else seeing this?SQL connector for Azure Key Vault on Linux
Hi everybody, after having successfully configured EKM (to access Azure Key Vault) and encrypted a database with a asymmetric key on a SQL running on a windows VM, i am trying to replicate the same configuration in SQL (2022, CU19) running in a linux container. I cannot find the CRYPTOGRAPHIC PROVIDER (from the documentation it should be built-in), nor create it from file (tried in many ways using the windows dll, i get an error 33027 without additional informations). The guide bellow (see last link) suggest to skip step 3 and 4, and proceed with step 5. But the next steps (create credential, create asymmetric key) requires to refer the cryptographic provider. Can someone supply more information for this step? Thks in adv. Stefano Links: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal) https://techcommunity.microsoft.com/blog/sqlserver/enabling-azure-key-vault-for-sql-server-on-linux/4091248Unable to drop a user on SSISDB
Hi, I am unable to drop the user from the SSISDB, I am getting the below error, has anyone experienced the same issue? "The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Framework Microsoft SqlClient Data Provider)" Regards, Noma
problem restoring a backup
I have a backup file (xxxx.bak) in my c:\trmp directory. When I launch SSMS signed on as "sa" and try to restore this database, i get this window: I am not able to enter anything for "Database" and the dropdown is empty. Then if I switch it to "Device" I get this window: Now, under "Destination" I am able to enter a database name, i.e. the name I want the database to be referred to after I restore it. However, note the message at the top of the window: No backupset selected to be restored. How do I resolve this issue? MurrayTransaction was deadlocked on lock resources with another process ??
Hi, We have a cognos datastage job that updates a table. It fails with an error that the table is locked., see below ini_PerformanceManagement_tblEmployees,0: ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line 1,058) Anyone any ideas about how to resolve this? Thanks for your time, Ollie
