New answers tagged

0

You can colour code the status bar in SQL Server Management Studio Easiest if you use the Registered Servers or Central Management Servers feature and then allocate colours at a group level i.e. Dev (Green) / Test (Yellow) / Prod (Red) https://documentation.red-gate.com/sp/ssms-tab-management/working-with-ssms-tab-management-quick-ref btw You may need to ...


0

If you are working with script files, then you can have the habit of adding something like below in the beginning of the file. I frequently have "scratchpad" files, never meant to execute everything. I.e., I always select the SQL to be executed and want to protect when I by mistake don't mark any text (and hence execute everything). So, I just add ...


0

Since you are working on different instance on the same server, first thing would be to see the header of SSMS as it mentions server name along with instance name as well as port number(if it is specified explicitly). In case, you want to address this through query, you may use below commands to check server name as well as instance name: select @@SERVERNAME ...


1

I can't tell you the why behind Microsoft included GUID and UID in their reserved words list, but they have which is why they're color coded as blue in SSMS. Probably for future proofing in case they do decide to actually implement them as functional keywords or to match other database systems standards, but at this time I don't know of any functional ...


3

You generally do not do it. It is not possible. SQL Server is not forward compatible. 2017 does not know how to handle a 2019 backup. PERIOD - no discussion, it will tell you so. Which is why it is an utterly stupid idea no to use the same versions over the whole application development team cycle - you make copy operations complex, you run into ...


3

A backup is overkill for only one table and I'm not sure what compatibility issues you can run into restoring to a lower version of SQL Server (since backups occur at the database level). Instead you should look to just copy the data between servers. There are a multitude of ways and tools out there to help you do this, but I've always found the SQL Examiner ...


0

Could be this. Part of Security Patch that came out in Nov. & was updated in Dec. This explains the problem: https://support.microsoft.com/en-us/topic/managing-deployment-of-kerberos-s4u-changes-for-cve-2020-17049-569d60b7-3267-e2b0-7d9b-e46d770332ab This contains the patch under Security Updates header: https://msrc.microsoft.com/update-guide/...


2

What you describe is normal behavior for SSMS, at least all the times I've done failover testing. But Management Studio is a poor tool to test this type of thing. Best practice for an application is to have retry logic built into it that will upon error dispose of the current connection and re-establish, which will connect to the now primary side. And SSMS ...


1

First, you need to use the new Microsoft OLEDB Driver for SQL Server, aka "MSOLEDBSQL", not the old Microsoft OLEDB Provider for SQL Server, aka "SQLOLEDB". The old driver doens't support Azure Active Directory Authentication. Second you have to set the "Authentication" connection string keyword: Then when you map the login ...


2

The only way you have here is to use INTEGRATED SECURITY in the connection string. In this way the connecting login is also used against the database but if there is a firewall between database and frontend that recognize windows authentication (it filters in application layer i suppose) every users need to be included in the allow rule. Usually, it is ...


0

There are 2 types of SQL Server installed on a PC: Server based - named instances such as \sqlexpress, \instance name or unnamed instances such as . All of these instances can be stopped by going to services.msc and stopping the service. By default, most people install unnamed instances on PCs unless they fully intend to install a named instance. File-based ...


0

If you installed a SQL server named instance, by defaut, it have to be contacted on a random port not ont the port 1433. Please use 127.0.0.1\MSSQLSERVER01 or change the tcp/ip configuration and set a fixed port. Verify that sql server agent is running. This is how a named instance is translated to the right dynamic or static port.


3

One weakness of MSX is that target servers uses Windows authentication and you can't change that. I.e., your target Agent need a domain account as a service account and that account need to exist as a Windows login on the master SQL Server (and also be a user in the msdb database there and a member of the TargetServersRole).


0

If VS doesn't have access to the remote server, you can build SSIS package, upload it to the remote server and install using SSMS. Build SSIS project to create IPSAC file in BIN folder Copy IPSAC file to the remote server Go to the remote server => SSMS => Integration Services Catalog => create new folder Created folder will have 2 sub-folders, one ...


0

By using this inner join and the link is database_id = principal_id select dp.[name] as username, dp.create_date, dp.modify_date, dp.[type_desc] as [type], dp.authentication_type_desc as authentication_type, dp.principal_id, m.[name] from sys.database_principals dp INNER JOIN master.sys.databases m ON m.database_id = dp.principal_id AND m.name like '%...


Top 50 recent answers are included