I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences. I wondered if there was a cleaner way of doing it?

I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user. So I created a proxy account and gave execute privilege to this user on the xp_cmdshell (practically followed the steps how to run xp_cmdshell with a non sysadmin user ). But when I run the stored procedure I am getting the below error: > error number 15466 severity 16 errorline 1 An error occurred during decryption. I use SQL Server 2008 What could be the problem?

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event notification on a queue, it creates a queue monitor and if receiving a message from that queue causes a poison message the queue monitor disappears. It's not in "RECEIVE" or "INACTIVE" it's just drops. And after enabling the queue with ALTER QUEUE somequeue WITH STATUS = ON; the queue monitor does not re-appear and and I need to drop and recreate event notification in order to have event notification working again. So the question is, is that by design or not? And after I re-enable the queue after poison message is it a common practice to recreate event notifications. Thanks!

I have a query for example select * from sys.databases select * from sys.configurations I believe this information is stored at master database. I would like to have a look at configuration settings in the past. Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries? Looks like select * from restored_master.sys.databases just ignores current database. I know an option to replace master/msdb using startup parameters - that should work, but a bit longer option and need compatible version of server.

I have to deal with the following situatiuon: Currently we have - 1 cluster with 5 nodes running 10 instances (SQL Server 2008 Enterprise edition), and - 1 cluster with 2 nodes running 5 instances (SQL Server 2014 Standard edition) in use. All servers are virtual servers running in VMWare. We want to setup a new cluster (SQL Server 2014 Enterprise). In a first step, 1 instance from the 2008 Enterprise cluster and 1 instance from the 2104 Standard cluster are to be migrated. Therefore, my boss asked 2 questions: 1. How many cores do we need (aim: minimize license costs)? 2. How much RAM do we need? My answer was: "It depends ..." Now I have to deliver hard facts by monitoring the next few weeks. Great! (beware of irony) My approach for question number 1: Using perfmon.exe I plan to monitor - **Processor\% Processor Time** (_total and single cores), - **Processor\% User Time** (_total and single cores), - **Processor\% Interrupt Time** (_total and single cores) - is this really necessary? -, and - **System\Processor Queue Length**. The question is, where to get these data from? From the node? From the SQL Server? In the first case it should be easy: the first instance in question - *vsql2008ent-1\instanceX* for the sake of simplicity - is currently running on a node, let's name it node *sql2008NodeA*. No other instances, nor server should run under normal conditions on this node. So it should not matter where I get the data from, should it? In case of a disaster other instances will be running on this node, too. But we want to have a baseline for normal operation. The second instance - *vsql2014stan-1\instanceY* - shares it's node - *sql2014NodeA* - with 2 other instances. In this case I can never be sure, how much cores the instance will truely need for smooth operation, right? So I can monitor the instance. But what does the result mean? It shows the CPU ressources actually used by this instance, only. But would more cores have been used if they were available? So what would be the answer to the question mentioned above? RAM is the other question. Due to several disasters in the past when all instances landed on the same node I have set an upper limit for the maximum server memory for each instance. This limit depends on the available memory of the node (currently 100GB or 120GB respectively). So how to monitor this? If all memory is used up, everything seems clear: insufficient memory. If all goes slow: insufficient memory. But how much memory do I really need? I try to summarize my questions: 1. Where should I get the measures from (node vs. server)? 2. Do I need to monitor the interrupt time, if I want to know the number of cores required? 3. What should I monitor under the given circumstances to answer the question, how much RAM I need (I know: "The more the better.")? Thank you very much for your help! Best regards!

We would be happy to upgrade our SQL Server environments to 2014, but we have a tight budget for next year. Our BI server (SQL Server 2008 Standard Edition, that is used only for BI purposes) is located separately from the Production Database server (SQL Server 2008 Enterprise Edition). The BI server contains our SSIS and SSRS packages and reports. I was wandering whether it's possible to have a configuration where the BI server will have SQL Server 2014 (Standard of Business Intelligence) but the database server itself will remain on 2008. Thanks for your answers, Roni.

Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this server. It was decided we build a new server and migrate the ReportServer Database. Unfortunately we don't have the Encryption Key. Because the service is down I can't even take the back up now, its too late. I tried backing up the ReportServer and ReportServerTempDB databases and then restoring those to the new servers. Restore went well, I even changed the database from the reporting services configuration manager. But, When I tried to go to the web portal URL, It shows the error , The report server isn't configured properly. Check the report server trace log for details. I found out that logs shows the scale out deployment errors. I believe since I don't have the encryption key file and password I am receiving this error. What can I do to get reporting services up and running...? What should be my approach..? Can I restore the encryption key some how or create a new one with rskeymgmt..? If not then can I delete it (if it is possible) and try to reconfigure all the reports again..? (There are about 350 different reports on this server). Please help.

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented. While I don't agree, I'm having trouble collecting good arguments to use a single database (splitting the tables into logical domains). One argument I have so far is data integrity (I can't use foreign keys between databases). What are good pros/cons for using a single or multiple databases? **[summary so far]** Arguments against multiple databases: - Losing data integrity (can't use foreign keys over databases) - Losing restore integrity - Gaining complexity (db users/roles) - Small odds server/database will go down Solutions: - Use schemas to separate domains. - POC: Use dummy data to prove the point in 7/1 db's execution plans

I have a problem with a massive amount of INSERTs that are blocking my SELECT operations. #Schema# I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT NULL, [ValueA] [decimal](18, 2) NULL, [ValueB] [decimal](18, 2) NULL CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [InverterID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON) ) I also have this little helper procedure, that allows me to insert or update (update on conflict) with MERGE command: CREATE PROCEDURE [InsertOrUpdateInverterData] @InverterID bigint, @TimeStamp datetime , @ValueA decimal(18,2), @ValueB decimal(18,2) AS BEGIN MERGE [InverterData] AS TARGET USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB)) AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB]) ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp WHEN MATCHED THEN UPDATE SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB] WHEN NOT MATCHED THEN INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB]) VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]); END #Usage# I now have run service instances on multiple servers that perform massive updates by calling the [InsertOrUpdateInverterData] procedure rapidly. There is also a website that does SELECT queries on the [InverterData] table. #Problem# If I do SELECT queries on the [InverterData] table they are proceeded in different timespans, depended on the INSERT usage of my service instances. If I pause all service instances the SELECT is lightning-fast, if the instance perform rapid insert the SELECTs get really slow or even a timeout cancel. #Attempts# I'm done some SELECTs on the [sys.dm_tran_locks] table to find locking processes, like this SELECT tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName, h1.TEXT AS RequestingText, h2.TEXT AS BlockingText, tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 This is the result: S = Shared. The holding session is granted shared access to the resource. #Question# Why are the SELECTs blocked by the [InsertOrUpdateInverterData] procedure that is only using MERGE commands? Do I have to use some kind of transaction with defined isolation mode inside of [InsertOrUpdateInverterData]? ##Update 1 (related to question from @Paul)## Base on MS-SQL server internal reporting about [InsertOrUpdateInverterData] following statistic: + Average CPU-Time: 0.12ms + Average Read processes: 5.76 per/s + Average Write processes: 0.4 per/s Base on this it looks like the MERGE command is mostly busy with reading operations that will lock the table!(?) ##Update 2 (related to question from @Paul)## The [InverterData] table as has following storage stats: + Data space: 26,901.86 MB + Row count: 131,827,749 + Partitioned: true + Partition count: 62 Here is the (allmost) complete **sp_WhoIsActive** result set: ###SELECT command### + dd hh:mm:ss.mss: 00 00:01:01.930 + session_id: 73 + wait_info: (12629ms)LCK_M_S + CPU: 198 + blocking_session_id: **146** + reads: 99,368 + writes: 0 + status: suspended + open_tran_count: 0 ###Blocking [InsertOrUpdateInverterData] command### + dd hh:mm:ss.mss: 00 00:00:00.330 + session_id: 146 + wait_info: NULL + CPU: 3,972 + blocking_session_id: NULL + reads: 376,95 + writes: 126 + status: sleeping + open_tran_count: 1

I am trying to figure if **SQL Server** has a feature to log when a user fires statements as revoke, delete from, etc. It would be great to see which user has fired the command, date and time, and which command has been run. Is there a way to accomplish this in **SQL Server 2008**? I have seen this but it is not what I am looking for.

I have 3 tables that sore orders, order items and order item comments . I need to copy the data for one order to 3 new tables that have the same structure and maintain the relationship. the order will have a new id for the new table, order items should include this new created id for the order in its values and the comments should also have the id of the newly created order items. Can this be done in one shot?

I have a small GIS table that is is dropped and recreated from a third-party tool called MAPINFO. Each time I drop and recreate that table I grant access to roles created. Is it possible to retain access even after drop and create? I cannot run SQL through Mapinfo. It does not provide *truncate table* - it just drops the table and creates a new one.

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests. Given that sysprocesses is deprecated, I'm curious why anyone would continue to use it, particularly MVPs. Is it just that using sysprocesses is less hassle than joining three DMVs or is there a better reason for using it? The two blog posts I mentioned were: Adam Machanic: Smashing a DMV Myth Tim Chapman: Find blocking processes using recursion in SQL Server 2005

Currently I have hundreds of sp that are in multiple servers, and that coexist with other sp that are in other servers with linked server. The origin of this is that when I do a restore of databases in development I need that these now point to their corresponding test version. Then I already created a script that searches in all the sp and saves in a table the sp with the modifications that I require. Now I can't manage to execute the sp to make the alterations of the SP.

-- Cursor variables
DECLARE @currentId INT;
DECLARE @currentSchema NVARCHAR(128);
DECLARE @currentName NVARCHAR(128);
DECLARE @currentScript NVARCHAR(MAX);
DECLARE @errorMessage NVARCHAR(MAX);
DECLARE @cleanScript NVARCHAR(MAX);

-- Start cursor
DECLARE procedure_cursor CURSOR FOR
SELECT Id, SchemaName, ProcedureName, AlterScript FROM AlterProcedures
ORDER BY Id;

OPEN procedure_cursor;
FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC(@currentScript);
        PRINT 'Updated ' + @currentSchema + '.' + @currentName + ' successfully.';
    END TRY
    BEGIN CATCH
        SET @errorMessage = 'Error in update:  ' + @currentSchema + '.' + @currentName + ': ' + ERROR_MESSAGE();
        PRINT @errorMessage;
        -- continue with the next even if there is wrong
    END CATCH
    
    FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;
END

CLOSE procedure_cursor;
DEALLOCATE procedure_cursor;

--TRUNCATE TABLE AlterProcedures;
PRINT 'Process completed'

Result:

Error al actualizar dbo.SIIF_MotorMantenimientoFechaEjercida_sp: 'CREATE/ALTER PROCEDURE' debe ser la primera instrucción en un lote de consultas.

--> create/alter procedure' must be the first statement in a query batch

I want to recreate the table below in SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it. create Table Listing ( NAME VARCHAR2(30), VALUES NUMBER, LIST_OF_VALUES LOV, id VARCHAR2(30) ); I am new at trying to converting this type of data or data type, in general, your help would be appreciated.

I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found row but the third value is a variable that is different on each insert. Can this be done? Here is a non-functioning SQL statement that, I hope, shows what I am trying to do:

INSERT INTO Routings_Doors_Options
               (HeaderID, Option2, Option1) 
VALUES (Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA1'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA2'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA3'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA4'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA6'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA7'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA8')
SELECT HeaderID, Option2
FROM  Routings_Doors_Options AS Routings_Doors_Options_1
WHERE (Option1 = 'OGGA')

My SQL Server 2008 R2 production database is running on a dedicated Windows server. It has been running for more than 20 years. In the last period we have suffered some problems such as freezing without memory but with more than 100 GB RAM available according to the task manager. In addition, the auto increment indexes are skipped, example of 720 jumps to 723. Every Sunday has an automatic task to rebuild indexes. Last time, the backup went from 22 GB to 37 GB. Between finishing the task for the indexes and completing backup took about 12 hours. Since then, it has grown 1.5 GB in less than 12 hours. I read a bit about rebuilding indexes could be the cause and also that the growth is not in % if not that it grows 1GB above what is needed. An important point is that the tables and data are the same size; that is to say the data does not grow. Could you help me to find the reason for this.

I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query CREATE EVENT SESSION [SA_Monitor] ON SERVER ADD EVENT sqlserver.login( ACTION(sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username) I am getting the following error. > The event name, "sqlserver.login", is invalid, or the object could not > be found. On Initial investigation I found that creating extended events using SSMS was introduced from 2012 and the only method to create EE is through SQL query. Is there a generic query that can help me capture this information in the 2008 server.

I have a user with the following server and database role memberships: 1. securityadmin (server) 2. public (server) 3. db_owner (database) 4. db_accessadmin (database) 5. db_securityadmin (database) When running the below script in SQL 2005, it runs through without any issues: BEGIN TRAN IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Opfix' AND COLUMN_NAME = 'ModifiedBy') BEGIN ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]()) END ROLLBACK TRAN When running the same script on SQL 2008 and SQL 2012, I get the below error: > Cannot find the object "Opfix" because it does not exist or you do > not have permissions. So, my questions are: 1. Does it makes sense that I get different results between SQL 2005 and SQL 2008+, with the same update and the exact same user permissions? 2. What are the minimum requirements for this script to run through? 3. The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?

I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach it I get this error : full message : =================================== Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) ------------------------------ For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.GetOriginalDatabaseName(String primaryFilePath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.PopulatePrimaryFileData(String primaryFilePath, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile..ctor(SqlManagementUserControl parent, CDataContainer dc, String fullPath, String databaseOwner, ServerConnection connectionInfo, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.AddPrimaryFile(String fullPath, String fileName, Boolean isXIPath) at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.buttonBrowse_Click(Object sender, EventArgs e) =================================== An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command) at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query) at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType) at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result) at Microsoft.SqlServer.Management.Smo.PrimaryFile.GetData(EnumResult erParent) at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) =================================== H:\GHAEM\neginsql.mdf is not a primary database file. (.Net SqlClient Data Provider) ------------------------------ For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5171-database-engine-error ------------------------------ Server Name: SERVERX Error Number: 5171 Severity: 16 State: 1 Line Number: 1 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) also I tried with the SQL Server 2008R2 , same error !