7

SQL Server's string handling functions are a bit inconsistent when dealing with long strings, but if you take care to make sure input to be searched is NVARCHAR(MAX) then the REPLACE() function will work over data longer than 8,000 bytes (4,000 characters for 'N'-types). This means that you can manipulate long NTEXT values by casting them to NVARCHAR(MAX) ...


3

In case you could use CLR functions, you can use this one: [Microsoft.SqlServer.Server.SqlFunction] [return: SqlFacet(MaxSize = -1)] public static SqlString fnReplace(SqlString value, SqlString oldValue, SqlString newValue) { return new SqlString(((string)value).Replace(oldValue.ToString(), newValue.ToString())); } Using your sample data this is the ...


2

The question is tagged with SQL Server 2016. This means you have FOR JSON. JSON encoding properly escapes special characters, including linefeeds. If the consumer can accept JSON you're done. If not a bit of PowerShell / Python / whatever will be able to replace \r\n with @@, and convert to CSV, more easily than TSQL will. Here's the setup. I've copied all ...


1

As @Charlieface mentioned in the comments, a part of CHECKDB is creating a snapshot to provide a point in time picture of your database file while checking for consistency. This snapshot is always in the same directory where the database files are located, so this is why your job is getting the error; the account doesn't have permissions to create a new file ...


1

How can we make Non Working server RSA key length to 2048? Use a company certificate that has the key length you'd like. If you're relying on the self generated certificates created by SQL Server as a nicety, then you'll instead want to use your own as there is no way to change that, it's hard coded. For completeness, other items associated with this issue.


1

re-creating the triggers after I run the snap does the trick for me. The way I do it and it has always been worked is the following: Before I run the snapshot and reinitialize the subscriptions, I go to the subscription databases and script out all the permissions, triggers, synonyms, and in some cases the indexes (when indexes in the subscription are ...


1

Testing with a SQL Server login isn't going to help if the problem is with a Windows login. This is due to Windows logins having the ability to authenticate via multiple sources (itself and/or one or more Windows groups). How is the Windows login authenticating? Directly (i.e. the login has an entry in sys.sys.server_principals), via one or more Windows / ...


1

When you backup & restore the encryption key, you need to make sure you are backing it up with a private key & password: BACKUP CERTIFICATE MyDatabaseEncryptionCert TO FILE = 'E:\Backup\MyDatabaseEncryptionCert.cer' WITH PRIVATE KEY( FILE = 'E:\Backup\MyDatabaseEncryptionCert.prvk', ENCRYPTION BY PASSWORD = 'NotPassword1!' ...


1

you could be looking at the wrong version of SQL configuration manager , if multiple versions are installed on your server.


Only top voted, non community-wiki answers of a minimum length are eligible