SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS FirstOfCurrentMonth
Error when create new column and run some things on it
if not exists (select * from sys.columns where object_id = object_id('dbo.MyTable') and name = 'NewColumn')
begin
alter table Customer.Configuration add NewColumn int null
end
do something with the new column - update, alter, etc.
--> Error message:
Msg 207, Level 16, State 1, Server <MyServer>, Line 17 Invalid column name 'NewColumn'.
Solution:
Close the batch pf the creation by adding 'GO' between the column creatuion and usage.
if not exists (select * from sys.columns where object_id = object_id('dbo.MyTable') and name = 'NewColumn')
begin
alter table Customer.Configuration add NewColumn int null
end
GO
do something with the new column - update, alter, etc.
Check the SQL server port number
In order to check the SQL server port number,
open:
Start button (windows) --> Go to All Programs --> Microsoft SQL Server 20XX --> Configuration Tools --> SQL Server Configuration Manager
And there:
SQL Native Client 10.0 Configuration --> Client Protocols --> TCP/IP Properties (double click or Right click)
There you will see the default port number.
Ctrl+R doesn't work in SQL Server 2012 SSMS
Problem: Ctrl+R doesn't work in SQL Server 2012 SSMS!!!
When I started work with SSMS of SQL Server 32012, and tried to hide/display the result panel, using Ctrl+R – it doesn't work for me, and I’v got this error in the bottom of the SSMS:
(Ctrl+R) was pressed. Waiting for second key of chord.
Solution:
Open from the menu: Toolsàoptions, choose KeyboardàKeyboard.
There, choose from the list: “window.ShowResultPane” event, and assign the Ctrl+R to “Use new shortcut in: SQL Query Editor”.
(Don’t forget to press the “Assign” button…)
Script fpr Role creation including permissions
-- set here the role name:
declare @RoleName varchar(50) = 'ROLE_NAME'
declare @RoleScript varchar(max)
SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)
SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS
FROM sys.database_permissions prm
JOIN sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id
WHERE rol.name = @RoleName
PRINT @RoleScript
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim
Error message:
Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
But if I don’t want this process to be the deadlock victim?
Solution:
SET DEADLOCK_PRIORITY HIGH
see here:
Subscribe to:
Posts (Atom)